Príklady
Malé programy, ktoré bežia tak, ako sú. Každý sa v testovacom prostredí zostaví a skontroluje voči očakávanému výstupu.
Ahoj, poznámky
Otvor súbor, vytvor tabuľku, vlož riadky, jeden uprav a prečítaj ich späť.
Rust
// SPDX-License-Identifier: Apache-2.0
// Opens a database file, stores three notes and reads them back with SQL.
use psob::sql::conn::Connection;
use psob::sql::eval::to_text;
use psob::{Database, OpenOptions};
fn main() -> psob::Result<()> {
let path = std::env::temp_dir().join(format!("psob-hello-{}.db", std::process::id()));
let db = Database::open(&path, OpenOptions::default())?;
let mut conn = Connection::with_mode(db, psob::sql::SqlMode::Sqlite)?;
conn.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)")?;
conn.execute("INSERT INTO notes(title, stars) VALUES ('first', 3), ('second', 5), ('third', 1)")?;
conn.execute("UPDATE notes SET stars = stars + 1 WHERE title = 'third'")?;
let rows = conn.query("SELECT title, stars FROM notes WHERE stars >= 2 ORDER BY stars DESC")?;
for row in &rows.rows {
println!("{} {}", to_text(&row[0]), to_text(&row[1]));
}
let _ = std::fs::remove_file(&path);
Ok(())
}
C
/* SPDX-License-Identifier: Apache-2.0 */
/* Opens a database, stores three notes with bound parameters and reads them back. */
#include <stdio.h>
#include <stdlib.h>
#include "psob.h"
#define CHECK(c, rc) \
do { \
int rc_ = (rc); \
if (rc_ != PSOB_OK) { \
fprintf(stderr, "error %d: %s\n", rc_, c ? psob_errmsg(c) : ""); \
return 1; \
} \
} while (0)
int main(int argc, char **argv) {
const char *path = argc > 1 ? argv[1] : ":memory:";
psob_db *db;
psob_conn *c;
psob_open_options opt = {0};
opt.size = sizeof opt;
opt.sql_mode = 1; /* sqlite */
CHECK(NULL, psob_open(path, &opt, &db));
CHECK(NULL, psob_connect(db, &c));
CHECK(c, psob_exec(c, "CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)"));
psob_stmt *ins;
CHECK(c, psob_prepare(c, "INSERT INTO notes(title, stars) VALUES (?, ?)", (size_t)-1, &ins, NULL));
const char *titles[] = {"first", "second", "third"};
const int stars[] = {3, 5, 2};
for (int i = 0; i < 3; i++) {
CHECK(c, psob_bind_text(ins, 1, titles[i], (size_t)-1, NULL));
CHECK(c, psob_bind_int64(ins, 2, stars[i]));
if (psob_step(ins) != PSOB_DONE) { fprintf(stderr, "insert failed: %s\n", psob_errmsg(c)); return 1; }
CHECK(c, psob_reset(ins));
}
CHECK(c, psob_finalize(ins));
psob_stmt *sel;
CHECK(c, psob_prepare(c, "SELECT title, stars FROM notes WHERE stars >= ? ORDER BY stars DESC", (size_t)-1, &sel, NULL));
CHECK(c, psob_bind_int64(sel, 1, 2));
while (psob_step(sel) == PSOB_ROW) printf("%s %lld\n", psob_column_text(sel, 0), (long long)psob_column_int64(sel, 1));
CHECK(c, psob_finalize(sel));
CHECK(c, psob_disconnect(c));
CHECK(NULL, psob_close(db));
return 0;
}
C++
// SPDX-License-Identifier: Apache-2.0
// The same program with the C++ wrapper: RAII handles, exceptions instead of return codes.
#include <iostream>
#include "psob.hpp"
int main() {
try {
psob::Connection c(":memory:", 1); // 1 = sqlite mode
c.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)");
{
auto tx = c.transaction();
auto ins = c.prepare("INSERT INTO notes(title, stars) VALUES (?, ?)");
const char *titles[] = {"first", "second", "third"};
const int stars[] = {3, 5, 2};
for (int i = 0; i < 3; i++) {
ins.bind(1, titles[i]).bind(2, stars[i]);
ins.execute();
ins.reset();
}
tx.commit();
}
for (auto &row : c.query("SELECT title, stars FROM notes WHERE stars >= 2 ORDER BY stars DESC"))
std::cout << row[0].text << ' ' << row[1].integer << '\n';
} catch (const psob::Error &e) {
std::cerr << "error " << e.code() << ": " << e.what() << '\n';
return 1;
}
return 0;
}
Očakávaný výstup
second 5
first 3
third 2
Ďalšie jazyky
Ten istý malý program cez štandardné databázové rozhranie každého jazyka: DB-API 2.0 v Pythone, database/sql v Go, PDO v PHP a natívne rozhrania pre Node.js, .NET, Java, Ruby a Dart.
Python
# SPDX-License-Identifier: Apache-2.0
"""Opens a database, stores three notes with bound parameters and reads them back (DB-API 2.0 / PEP 249)."""
import sys
import psob
path = sys.argv[1] if len(sys.argv) > 1 else ":memory:"
con = psob.connect(path)
con.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)")
with con: # commits on success, rolls back on an exception
con.executemany("INSERT INTO notes(title, stars) VALUES (?, ?)", [("first", 3), ("second", 5), ("third", 2)])
con.execute("UPDATE notes SET stars = stars + :bonus WHERE title = :title", {"bonus": 0, "title": "first"})
con.row_factory = psob.Row
for row in con.execute("SELECT title, stars FROM notes WHERE stars >= ? ORDER BY stars DESC", (2,)):
print(row["title"], row["stars"])
try:
con.execute("INSERT INTO notes(id, title) VALUES (1, 'duplicate')")
except psob.IntegrityError as exc:
print("rejected:", exc.name)
con.close()
Go
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with bound parameters and reads them back (database/sql).
package main
import (
"database/sql"
"errors"
"fmt"
"io"
"os"
psob "example.com/psorvia/base-go"
)
func run(w io.Writer, path string) error {
db, err := sql.Open("psob", path)
if err != nil {
return err
}
defer db.Close()
if _, err := db.Exec("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)"); err != nil {
return err
}
tx, err := db.Begin() // commits at the end, rolls back on any error
if err != nil {
return err
}
defer tx.Rollback()
for _, n := range []struct {
title string
stars int
}{{"first", 3}, {"second", 5}, {"third", 2}} {
if _, err := tx.Exec("INSERT INTO notes(title, stars) VALUES (?, ?)", n.title, n.stars); err != nil {
return err
}
}
if _, err := tx.Exec("UPDATE notes SET stars = stars + :bonus WHERE title = :title",
sql.Named("bonus", 0), sql.Named("title", "first")); err != nil {
return err
}
if err := tx.Commit(); err != nil {
return err
}
rows, err := db.Query("SELECT title, stars FROM notes WHERE stars >= ? ORDER BY stars DESC", 2)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var title string
var stars int
if err := rows.Scan(&title, &stars); err != nil {
return err
}
fmt.Fprintln(w, title, stars)
}
if err := rows.Err(); err != nil {
return err
}
_, err = db.Exec("INSERT INTO notes(id, title) VALUES (1, 'duplicate')")
var pe *psob.Error
if errors.As(err, &pe) {
fmt.Fprintln(w, "rejected:", pe.Name())
}
return nil
}
func main() {
path := ":memory:"
if len(os.Args) > 1 {
path = os.Args[1]
}
if err := run(os.Stdout, path); err != nil {
fmt.Fprintln(os.Stderr, err)
os.Exit(1)
}
}
Očakávaný výstup (Python, Go)
second 5
first 3
third 2
rejected: PSOB_ERROR
PHP
<?php
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with bound parameters and reads them back.
// Uses the compiled PDO driver when it is loaded, otherwise the FFI package (same calls either way).
declare(strict_types=1);
$path = $argv[1] ?? ':memory:';
$dsn = 'psob:' . ($path === ':memory:' ? ':memory:' : $path);
if (extension_loaded('pdo_psob')) {
$db = new PDO($dsn);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} else {
require __DIR__ . '/../../bindings/php/src/autoload.php';
$db = new Psob\Connection($dsn);
}
$db->exec('CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)');
$db->beginTransaction();
$insert = $db->prepare('INSERT INTO notes(title, stars) VALUES (?, ?)');
foreach ([['first', 3], ['second', 5], ['third', 2]] as [$title, $stars]) {
$insert->bindValue(1, $title);
$insert->bindValue(2, $stars, $db::PARAM_INT);
$insert->execute();
}
$db->prepare('UPDATE notes SET stars = stars + :bonus WHERE title = :title')->execute([':bonus' => 0, ':title' => 'first']);
$db->commit();
$select = $db->prepare('SELECT title, stars FROM notes WHERE stars >= :min ORDER BY stars DESC');
$select->bindValue(':min', 2, $db::PARAM_INT);
$select->execute();
foreach ($select->fetchAll($db::FETCH_ASSOC) as $row) {
echo $row['title'], ' ', $row['stars'], "\n";
}
try {
$db->exec("INSERT INTO notes(id, title) VALUES (1, 'duplicate')");
} catch (Exception $e) { // PDOException or Psob\Exception
echo 'rejected: SQLSTATE ', $e->errorInfo[0], ' code ', $e->errorInfo[1], "\n";
}
Očakávaný výstup (PHP)
second 5
first 3
third 2
rejected: SQLSTATE 23000 code 1
JavaScript (Node.js)
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with bound parameters and reads them back (synchronous API, then Promises).
'use strict';
const { Database, PsobError, connect } = require('../../bindings/node');
async function run(out, file = ':memory:') {
const db = new Database(file);
db.exec('CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)');
const add = db.prepare('INSERT INTO notes(title, stars) VALUES (?, ?)');
const addAll = db.transaction((notes) => { // commits at the end, rolls back when the function throws
for (const [title, stars] of notes) add.run(title, stars);
});
addAll([['first', 3], ['second', 5], ['third', 2]]);
db.prepare('UPDATE notes SET stars = stars + :bonus WHERE title = :title').run({ bonus: 0, title: 'first' });
for (const row of db.prepare('SELECT title, stars FROM notes WHERE stars >= ? ORDER BY stars DESC').iterate(2)) {
out(`${row.title} ${row.stars}`);
}
try {
db.prepare("INSERT INTO notes(id, title) VALUES (1, 'duplicate')").run();
} catch (e) {
if (!(e instanceof PsobError)) throw e;
out(`rejected: ${e.codeName}`);
}
db.close();
// The same file through the Promise interface: the work runs on a worker thread.
if (file !== ':memory:') {
const adb = await connect(file);
const { n } = await adb.get('SELECT count(*) AS n FROM notes');
out(`promise: ${n} notes`);
await adb.close();
}
}
if (require.main === module) {
run((line) => console.log(line), process.argv[2]).catch((e) => {
console.error(e);
process.exit(1);
});
}
module.exports = { run };
C#
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with parameters in a transaction and reads them back (ADO.NET).
using System;
using System.IO;
using System.Threading.Tasks;
using PSOrvia.Base;
namespace PSOrvia.Base.Examples;
public static class Hello
{
public static async Task RunAsync(TextWriter w, string path)
{
await using var db = new PsobConnection($"Data Source={path}");
await db.OpenAsync();
using (PsobCommand create = db.CreateCommand())
{
create.CommandText = "CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)";
await create.ExecuteNonQueryAsync();
}
using (PsobTransaction tx = db.BeginTransaction()) // rolls back on dispose unless committed
{
using PsobCommand insert = db.CreateCommand();
insert.CommandText = "INSERT INTO notes(title, stars) VALUES (?, ?)";
foreach ((string title, int stars) in new[] { ("first", 3), ("second", 5), ("third", 2) })
{
insert.Parameters.Clear();
insert.Parameters.AddPositional(title);
insert.Parameters.AddPositional(stars);
await insert.ExecuteNonQueryAsync();
}
using PsobCommand bonus = db.CreateCommand();
bonus.CommandText = "UPDATE notes SET stars = stars + :bonus WHERE title = :title";
bonus.Parameters.Add(":bonus", 0);
bonus.Parameters.Add(":title", "first");
await bonus.ExecuteNonQueryAsync();
tx.Commit();
}
using (PsobCommand query = db.CreateCommand())
{
query.CommandText = "SELECT title, stars FROM notes WHERE stars >= @min ORDER BY stars DESC";
query.Parameters.Add("@min", 2);
await using var reader = await query.ExecuteReaderAsync();
while (await reader.ReadAsync())
w.WriteLine($"{reader.GetString(0)} {reader.GetInt64(1)}");
}
try
{
using PsobCommand bad = db.CreateCommand();
bad.CommandText = "INSERT INTO notes(id, title) VALUES (1, 'duplicate')";
await bad.ExecuteNonQueryAsync();
}
catch (PsobException e)
{
w.WriteLine("rejected: " + (e.Code == PsobCode.Error ? "PSOB_ERROR" : "code " + e.Code));
}
}
public static async Task<int> Main(string[] args)
{
try
{
await RunAsync(Console.Out, args.Length > 0 ? args[0] : ":memory:");
return 0;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return 1;
}
}
}
Java
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with parameters in a transaction and reads them back (JDBC).
package io.psorvia.base.examples;
import io.psorvia.base.jdbc.PsobPreparedStatement;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class Hello {
private Hello() {}
public static void run(PrintStream out, String path) throws SQLException {
try (Connection db = DriverManager.getConnection("jdbc:psob:" + path)) {
try (Statement create = db.createStatement()) {
create.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)");
}
db.setAutoCommit(false); // a transaction starts with the first statement
try (PreparedStatement insert = db.prepareStatement("INSERT INTO notes(title, stars) VALUES (?, ?)")) {
String[] titles = {"first", "second", "third"};
int[] stars = {3, 5, 2};
for (int i = 0; i < titles.length; i++) {
insert.setString(1, titles[i]);
insert.setInt(2, stars[i]);
insert.executeUpdate();
}
}
try (PreparedStatement bonus = db.prepareStatement("UPDATE notes SET stars = stars + :bonus WHERE title = :title")) {
PsobPreparedStatement named = bonus.unwrap(PsobPreparedStatement.class); // named parameters: the driver's own API
named.setInt("bonus", 0);
named.setString("title", "first");
bonus.executeUpdate();
}
db.commit();
db.setAutoCommit(true);
try (PreparedStatement query = db.prepareStatement("SELECT title, stars FROM notes WHERE stars >= ? ORDER BY stars DESC")) {
query.setInt(1, 2);
try (ResultSet rs = query.executeQuery()) {
while (rs.next()) out.println(rs.getString(1) + " " + rs.getLong(2));
}
}
try (Statement bad = db.createStatement()) {
bad.executeUpdate("INSERT INTO notes(id, title) VALUES (1, 'duplicate')");
} catch (SQLException e) {
out.println("rejected: " + (e.getErrorCode() == 1 ? "PSOB_ERROR" : "code " + e.getErrorCode()));
}
}
}
public static void main(String[] args) {
try {
run(System.out, args.length > 0 ? args[0] : ":memory:");
} catch (SQLException e) {
System.err.println(e.getMessage());
System.exit(1);
}
}
}
Očakávaný výstup (JavaScript, C#, Java)
second 5
first 3
third 2
rejected: PSOB_ERROR
Ruby
# frozen_string_literal: true
# SPDX-License-Identifier: Apache-2.0
# Opens a database, stores three notes with bound parameters and reads them back.
# Run: ruby -Ibindings/ruby/lib examples/ruby/hello.rb [file] (PSOB_LIBDIR points at the folder of libpsob_c)
require "psorvia/base"
db = PSOrvia::Base::Database.new(ARGV[0] || ":memory:")
db.execute("CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)")
db.transaction do
insert = db.prepare("INSERT INTO notes(title, stars) VALUES (?, ?)")
[["first", 3], ["second", 5], ["third", 2]].each { |title, stars| insert.execute(title, stars) }
insert.close
db.execute("UPDATE notes SET stars = stars + :bonus WHERE title = :title", bonus: 0, title: "first")
end
db.execute("SELECT title, stars FROM notes WHERE stars >= :min ORDER BY stars DESC", min: 2) do |title, stars|
puts "#{title} #{stars}"
end
begin
db.execute("INSERT INTO notes(id, title) VALUES (1, 'duplicate')")
rescue PSOrvia::Base::Error => e
puts "rejected: code #{e.code} #{e.code_name}"
end
db.close
Očakávaný výstup (Ruby)
second 5
first 3
third 2
rejected: code 1 PSOB_ERROR
Dart
// SPDX-License-Identifier: Apache-2.0
// Opens a database, stores three notes with bound parameters and reads them back, then does the same through the isolate API.
// Run: PSOB_LIBDIR=target/release dart run --packages=bindings/dart/.dart_tool/package_config.json examples/dart/hello.dart [file]
import 'dart:io';
import 'package:psorvia_base/psorvia_base.dart';
Future<void> main(List<String> args) async {
final path = args.isNotEmpty ? args[0] : ':memory:';
final db = Database.open(path);
db.execute('CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL, stars INT DEFAULT 0)');
db.transaction(() {
final insert = db.prepare('INSERT INTO notes(title, stars) VALUES (?, ?)');
for (final (title, stars) in [('first', 3), ('second', 5), ('third', 2)]) {
insert.execute([title, stars]);
}
insert.close();
db.execute('UPDATE notes SET stars = stars + :bonus WHERE title = :title', {'bonus': 0, 'title': 'first'});
});
for (final row in db.execute('SELECT title, stars FROM notes WHERE stars >= :min ORDER BY stars DESC', {'min': 2})) {
stdout.writeln('${row[0]} ${row[1]}');
}
try {
db.execute("INSERT INTO notes(id, title) VALUES (1, 'duplicate')");
} on PsobException catch (e) {
stdout.writeln('rejected: code ${e.code} ${e.codeName}');
}
db.close();
// The same connection model in an isolate of its own: the event loop stays free while a statement runs.
final async = await AsyncDatabase.open(':memory:');
await async.executeBatch('CREATE TABLE t(a INTEGER); INSERT INTO t VALUES (1), (2), (3)');
final total = await async.execute('SELECT sum(a) FROM t', null, const Duration(seconds: 5));
stdout.writeln('async sum ${total.single[0]}');
await async.close();
}
Očakávaný výstup (Dart)
second 5
first 3
third 2
rejected: code 1 PSOB_ERROR
async sum 6
Spusti všetky
Jeden skript zostaví a spustí každý príklad a porovná výstup.
examples/run.sh all
Väčší program so 100 000 riadkami a fulltextovým vyhľadávaním je na stránke ukážky. Otvoriť ukážku