PSOrvia

Παραδείγματα

Μικρά προγράμματα που τρέχουν ως έχουν. Καθένα μεταγλωττίζεται και ελέγχεται με την αναμενόμενη έξοδό του στο περιβάλλον δοκιμών.

Γεια σου, σημειώσεις

Ανοίξτε ένα αρχείο, δημιουργήστε έναν πίνακα, εισαγάγετε γραμμές, ενημερώστε μία και διαβάστε τις ξανά.

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

Αναμενόμενη έξοδος

second 5
first 3
third 2

Περισσότερες γλώσσες

Το ίδιο μικρό πρόγραμμα μέσω της τυπικής διεπαφής βάσης δεδομένων κάθε γλώσσας: DB-API 2.0 στην Python, database/sql στη Go, PDO στην PHP και οι εγγενείς διεπαφές για Node.js, .NET, Java, Ruby και 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)
	}
}

Αναμενόμενη έξοδος (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";
}

Αναμενόμενη έξοδος (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);
        }
    }
}

Αναμενόμενη έξοδος (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

Αναμενόμενη έξοδος (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();
}

Αναμενόμενη έξοδος (Dart)

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

Εκτελέστε τα όλα

Ένα σενάριο μεταγλωττίζει και εκτελεί κάθε παράδειγμα και συγκρίνει την έξοδο.

examples/run.sh all

Ένα μεγαλύτερο πρόγραμμα, με 100 000 γραμμές και αναζήτηση πλήρους κειμένου, βρίσκεται στη σελίδα επίδειξης. Ανοίξτε την επίδειξη