Skip to content

Latest commit

 

History

History
160 lines (121 loc) · 5.82 KB

sqlite.md

File metadata and controls

160 lines (121 loc) · 5.82 KB

sqlite

SQLite from Zig and from pages. The plugin uses the system's library, loaded when the first database opens. No C code is compiled, and nothing is linked or shipped.

platform library
Windows 10 and later winsqlite3.dll (part of Windows), or sqlite3.dll
macOS /usr/lib/libsqlite3.dylib
Linux libsqlite3.so.0 (packages libsqlite3-0, sqlite-libs)
  • MYZIG_SQLITE_LIBRARY names another build.
  • Without a library, opening fails with error.SqliteMissing.

The package is plugins/sqlite (module sqlite). See Plugins for adding it to an app.

From Zig

const sqlite = @import("sqlite");

var db = try sqlite.Db.open("notes.db", .{}); // or ":memory:"
defer db.close();
try db.execScript("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT, done INTEGER)");
const r = try db.exec("INSERT INTO notes(title, done) VALUES (?, ?)", .{ "Hello", false });
// r.changes == 1, r.last_insert_id == 1

const Note = struct { id: i64, title: []const u8, done: bool };
const notes = try db.all(Note, arena, "SELECT id, title, done FROM notes WHERE done = ?", .{false});
const first = try db.one(Note, arena, "SELECT * FROM notes WHERE id = ?", .{1}); // ?Note

try db.transaction(ctx, struct {
    fn body(d: *sqlite.Db, c: @TypeOf(ctx)) !void {
        _ = try d.exec("INSERT INTO notes(title, done) VALUES (?, 0)", .{c.title});
    }
}.body); // committed, or rolled back when body fails

Db.open(path, options) takes OpenOptions:

OpenOptions Default
read_only false open without writing
busy_timeout_ms 5000 how long a statement waits for another connection's lock; 0 fails at once
guarded false refuse SQL that controls transactions, attaches files or loads extensions (for SQL pages send)

Statements and values

One statement. exec, all and one take one statement and a tuple of arguments.

  • A trailing ; and comments are fine. More statements fail with error.MultipleStatements. execScript runs several.
  • Arguments bind to ?1, ?2... :name, $name and @name bind by their index too.
  • Every parameter needs a value (error.WrongParameterCount).

Bound values: null, bools (0 or 1), integers (within i64), finite floats, text ([]const u8, valid UTF-8), sqlite.blob(bytes), enums (their names), sqlite.Value, and optionals of these.

Rows read into structs by column name.

  • Field types: integers, floats, bools, enums (from text), []const u8 (copied), sqlite.Value and optionals (NULL).
  • A field with a default may have no column, or NULL in it.

Lower level

  • prepare gives a Statement. You can bind (or bindAll), step, read (int, float, text, bytes, value, read(T)), reset for reuse and finalize.
  • query returns dynamic Rows of Values. execValues binds a slice of Values.
  • begin, commit, rollback and inTransaction are there for transactions that transaction doesn't fit.

Errors and threads

  • Errors are Zig errors: error.Constraint, error.Busy, error.Interrupted, error.InvalidSql, error.Sqlite, and others named in sqlite.Error.
  • After error.Sqlite, db.diagnostics has SQLite's extended code and diagnostics.text() its message.
  • A Db is used by one thread at a time. interrupt() stops a running statement from another.

From pages

Bind the service and serve the client:

var service: sqlite.Service = .init(gpa, .{}); // .directory, .busy_timeout_ms
defer service.deinit();
try app.bind(&service);
// ... .content = .{ .page = .{ .url = "/", .assets = &.{ index_html, sqlite.asset } } }
import { open } from "/plugins/sqlite.js";

const db = await open("app.db"); // a file name in the app's data directory, or ":memory:"
await db.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT)");
const { changes, lastInsertId } = await db.execute("INSERT INTO notes(title) VALUES (?)", ["Hi"]);
const { columns, rows } = await db.query("SELECT id, title FROM notes WHERE id = ?", [lastInsertId]);
await db.transaction([{ sql: "DELETE FROM notes WHERE id = ?", params: [1] }]);
await db.close();
sqlite.ServiceOptions Default
directory null where pages' databases are; null is the app's data directory
busy_timeout_ms 5000

Values

  • null, numbers, strings, booleans (0 or 1), and Uint8Arrays (any ArrayBuffer view) for blobs.
  • bigints for integers past 2^53. Results give them as bigints, and lastInsertId always is one.
  • What JSON can't carry travels as strings with its kind. The client turns it back.

Names, never paths. Pages open file names in the service's directory.

  • No directories, .., :, Windows' reserved names (CON, COM1...) or symbolic links.
  • The directory is created for writable databases.
  • open(name, { readOnly: true }) opens one read-only.

Guarded SQL. One statement per call. These are refused:

  • BEGIN, COMMIT or SAVEPOINT. Use transaction, which is BEGIN IMMEDIATE and rolls back on any error.
  • ATTACH, DETACH or VACUUM INTO.

Per page. A connection belongs to the page that opened it.

  • Other windows can't use its id.
  • It closes when the page navigates, reloads or closes, or the service deinits.
  • A reloaded page opens its databases again.

The main thread. Calls run there, as all bound methods do. Keep queries short (LIMIT).

Example and tests

example/ is a notes page. zig build run shows it.

cd plugins/sqlite
zig build test               # the engine against the system's SQLite on temporary databases, and the page
zig build test -Dpage=false  # without the page (no webview needed)
zig build test-page          # the example's page alone, hidden, through the real webview
zig build check -Dtarget=x86_64-linux-gnu