PSOrvia

Exemplos

Pequenos programas que rodam como estão. Cada um é compilado e verificado contra sua saída esperada no ambiente de testes.

Olá, notas

Abra um arquivo, crie uma tabela, insira linhas, atualize uma delas e leia tudo de volta.

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;
}

Saída esperada

second 5
first 3
third 2

Mais linguagens

O mesmo pequeno programa pela interface padrão de banco de dados de cada linguagem: DB-API 2.0 em Python, database/sql em Go, PDO em PHP e as interfaces nativas para Node.js, .NET, Java, Ruby e 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)
	}
}

Saída esperada (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";
}

Saída esperada (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);
        }
    }
}

Saída esperada (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

Saída esperada (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();
}

Saída esperada (Dart)

second 5
first 3
third 2
rejected: code 1 PSOB_ERROR
async sum 6

Execute todos

Um script compila e executa cada exemplo e compara a saída.

examples/run.sh all

Um programa maior, com 100 000 linhas e busca de texto completo, está na página da vitrine. Abrir a vitrine