Standardbibliothek

Antra::SQLite

import Antra::SQLite

SQLite for Antra, in both interpreters: antra-zig talks to the SQLite library through Antra::FFI, the Perl interpreter uses DBI with DBD::SQLite (cpanm DBD::SQLite). Both backends are Antra::Native::SQLite; this package is the shared public API.

The API is functional and pipe-friendly: every function takes the database first and returns a Result, so calls chain with |> and unwrap with .! or .^:

import Antra::SQLite as DB

val db = DB.open(":memory:").!
db |> DB.exec("CREATE TABLE users (name TEXT, age INTEGER)").!
   |> DB.run("INSERT INTO users VALUES (?, ?)", ["Ada", 36]).!

db |> DB.query("SELECT name FROM users WHERE age > ?", [30]).!
   |> map { _.name }
   |> println                      # ['Ada']

Rows are maps with keyword keys (%{ :name => 'Ada', :age => 36 }). Parameters are an array (? placeholders) or a map (:name/@name/$name placeholders). Value mapping: Number <-> INTEGER/REAL, String <-> TEXT, ByteString <-> BLOB, undef <-> NULL, Bool -> 1/0. Concurrent writers (several processes): set a wait with exec("PRAGMA busy_timeout = 2000"); PRAGMA journal_mode = WAL lets readers run alongside one writer.

Überblick#

SQLite-Datenbanken: das Paket spricht die SQLite-Bibliothek (libsqlite3) über Antra::FFI an. Jede Funktion nimmt die Datenbank zuerst und liefert ein Result, Aufrufe lassen sich also mit |> verketten:

import Antra::SQLite as DB

val db = DB.open(":memory:").!
db |> DB.exec("CREATE TABLE users (name TEXT, age INTEGER)").!
   |> DB.run("INSERT INTO users VALUES (?, ?)", ["Ada", 36]).!

println(DB.query(db, "SELECT name FROM users WHERE age > :min", %{min: 30}).!)
# [%{ :name => 'Ada' }]

Zeilen sind Maps mit Keyword-Schlüsseln. Parameter sind ein Array (für ?) oder eine Map (für :name; Platzhalter @x/$x brauchen den Schlüssel mit Präfix). Werte: Number ↔ INTEGER/REAL, String ↔ TEXT, ByteString ↔ BLOB, undef ↔ NULL, Bool → 1/0.

Funktion	Ergebnis
`open(path)`, `open(path, [readonly: true, create: false])`	`%Ok[db]`; `":memory:"` für eine Datenbank im Speicher
`exec(db, sql)`	eine oder mehrere Anweisungen ohne Parameter; `%Ok[db]`
`run(db, sql, params)`	eine Anweisung, Zeilen werden verworfen; `%Ok[db]`
`query(db, sql, params)`	`%Ok[[zeilen]]`
`one(db, sql, params)`, `value(...)`	erste Zeile bzw. deren erste Spalte als `%Some[...]`/`%None[]`
`changes(db)`, `last_id(db)`	geänderte Zeilen der letzten Anweisung, letzte Rowid
`transaction(db, f)`	`f(db)` zwischen BEGIN und COMMIT; bei `%Err` oder Exception ROLLBACK
`close(db)`, `version()`

Fehler kommen als %Err["SQLite: ..."]. Schreiben mehrere Prozesse in dieselbe Datei, lässt exec(db, "PRAGMA busy_timeout = 2000") sie warten statt sofort mit „database is locked“ zu scheitern; PRAGMA journal_mode = WAL erlaubt Lesern, parallel zu einem Schreiber zu arbeiten.

changesfn</>

fn changes(%SQLite{handle:, ..})

Rows changed by the last INSERT/UPDATE/DELETE.

closefn</>

fn close(%SQLite{handle:, ..})

Closes the database.

execfn</>

fn exec(%SQLite{handle:, ..} as db, sql)

Runs one or more SQL statements without parameters (DDL, scripts). Returns %Ok[db], so it chains: db |> exec(a).! |> exec(b).!.

last_idfn</>

fn last_id(%SQLite{handle:, ..})

Rowid of the last INSERT.

onefn</>

fn one(db, sql)
fn one(db, sql, params)

The first row as %Some[row], or %None[].

openfn</>

fn open(path)
fn open(path, [readonly: = false, create: = true])

Opens (and by default creates) a database file; ":memory:" gives an in-memory database. Options: readonly: true, create: false.

queryfn</>

fn query(db, sql)
fn query(%SQLite{handle:, ..}, sql, params)

All rows of a query as an array of maps.

runfn</>

fn run(db, sql)
fn run(%SQLite{handle:, ..} as db, sql, params)

Runs one statement with parameters. Returns %Ok[db] for chaining; use changes/last_id for row counts and generated ids.

transactionfn</>

fn transaction(db, f)

Runs f(db) inside BEGIN/COMMIT. If f returns %Err[...] or throws, the transaction is rolled back and the error is returned/re-thrown.

valuefn</>

fn value(db, sql)
fn value(db, sql, params)

The first column of the first row as %Some[value], or %None[].

versionfn</>

fn version()

Version of the SQLite library, e.g. "3.45.1".

Quelle dieser Seite