default_config : ConfigRead/write/create, a five second busy timeout, sixteen megabytes of result payload.
Sqlite
SQLite connections, prepared statements, queries, and typed outcomes.
All database effects wait. They are legal in init!, where they block
startup, and in tasks, where they park the task. They are refused in
update! and render!.
Use db.query! and db.execute! for one-off SQL. Retain a prepared
Stmt from db.prepare! when reusing the same query or command. Transactions use
explicit BEGIN, COMMIT, and ROLLBACK SQL.
Db and Stmt are reference-counted host resources. Final release closes
them automatically; Db.close! provides deliberate early closure.
A database file is named beneath a Files directory handle, so it lives
where the app may write -- usually io.files().app_data!() -- and nowhere
else. open_memory! creates a private in-memory database, which needs no
handle. ATTACH is disabled on every connection: a database cannot open a
second file.
At most eight connections and sixty-four statements may be open. Queries
refuse results above one million cells or Config.max_result_bytes rather
than truncating them. The default byte limit is sixteen megabytes.
errcode_to_str : ErrCode -> StrDescribe an error code, for a log line or an error screen.
A value stored in, or read out of, a database column.
ValueKind : [Null, Integer, Real, String, Bytes]The five things a Value can be, without the payload. Reported by
UnexpectedType when a column held something other than what a decoder
asked for.
A named parameter binding. name includes SQLite's parameter prefix, so
{ name: ":id", value: Integer(42) } binds :id.
A binding whose name the statement does not mention is a
SqliteErr(OutOfRange, _) rather than a silent no-op: a typo in a
parameter name would otherwise run the query with a NULL nobody asked
for.
ErrCode : [
Error,
Internal,
Perm,
Abort,
Busy,
Locked,
NoMem,
ReadOnly,
Interrupt,
IOErr,
Corrupt,
NotFound,
Full,
CanNotOpen,
Protocol,
Empty,
Schema,
TooBig,
Constraint,
Mismatch,
Misuse,
NoLFS,
AuthDenied,
Format,
OutOfRange,
NotADatabase,
Notice,
Warning,
Row,
Done,
Unknown(I64),
]SQLite's own result codes.
Extended result codes are reduced to the primary code they extend, so a
UNIQUE violation is Constraint rather than a number an app would have
to know. The accompanying Str holds SQLite's message, which is where
the detail went.
Interrupt is what shutdown looks like from inside a query. Rather than
making the window wait for a long statement to finish, the host
interrupts every connection with a statement still running, and each of
those calls returns SqliteErr(Interrupt, _). A task that treats it as a database
failure will report one on the way out; a task that is about to be
cancelled anyway has nothing to report.
Why a connection was not opened.
PathInvalid is a path that is not plainly relative, or that meets a
symbolic link, as for a Files read. PermissionDenied is a stub
directory handle, which gives no access. TooManyConnections means
eight are already open; releasing a Db the app no longer needs frees
a slot.
PrepareErr : [SqliteErr(ErrCode, Str), TooManyStatements, MultipleStatements]Why a statement was not prepared.
MultipleStatements is a query string holding more than one statement;
exec_script! is the call that runs several. TooManyStatements means
sixty-four are already prepared.
Why a query produced no rows to decode.
ResultTooLarge is the per-connection cap on a single result, and it is
a refusal: nothing is delivered, because a truncated result set is wrong
data rather than an error. Narrow the query, or raise
Config.max_result_bytes.
ExecuteErr : [SqliteErr(ErrCode, Str), ResultTooLarge, MultipleStatements, RowsReturnedUseQueryInstead]Why a statement that should change data did not.
RowsReturnedUseQueryInstead is a SELECT handed to execute!, which
has nowhere to put the rows.
ExactlyOneErr : [SqliteErr(ErrCode, Str), ResultTooLarge, MultipleStatements, NoRowsReturned, TooManyRowsReturned]Why a query did not produce exactly one row.
Why a column did not decode.
NoSuchField names a column the row does not have -- usually a name
that does not match the SELECT. UnexpectedType reports what was
actually there. IntOutOfBounds is an integer that does not fit the
width asked for.
Mode : [ReadWriteCreate, ReadWrite, ReadOnly]How a connection is opened.
ReadWriteCreate creates the file if it is not there. ReadOnly opens
an existing database for reading only, and the host locks that
connection down further: schema-rewriting tricks are disabled and
ATTACH cannot open a second file, so a connection opened to visualize
someone else's data cannot be talked into writing.
Per-connection limits.
busy_timeout_ms is how long a statement waits for another process's
write lock before returning SqliteErr(Busy, _).
max_result_bytes caps the text and blob payload of one query. A query
that would exceed it fails with ResultTooLarge rather than returning
part of its rows.
A plain record; build one with
{ ..Sqlite.default_config, mode: ReadOnly } rather than a chain of
with_* calls.
What a statement that changes data did.
changes counts the rows the statement inserted, updated, or deleted.
last_insert_rowid is the rowid of the most recent successful insert on
this connection, and is only meaningful after one.
Db
Sqlite.Db :: # (opaque)An open database connection.
The host owns the connection; this is a reference-counted handle to it. Keep it in the model, copy it freely, and let the last reference close it.
Close this connection now rather than when its last handle is released.
Releasing the final handle closes the connection anyway, so this is never required for safety. It exists because closing a database in WAL mode may checkpoint, which is real disk work: doing it here, on a task, keeps it off the frame the last handle happened to be dropped in.
Statements prepared against this connection keep working until they are released; SQLite defers the close until the last one is gone.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
Compile one statement for repeated use.
The string must hold exactly one statement; several is
MultipleStatements, and exec_script! is the call that runs those.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
Run one statement that changes data and does not return rows.
This does not occupy a prepared-statement slot: the statement is
compiled, run, and finalized inside the call. Use prepare! when the
same SQL runs many times.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
Run one query and decode every row it returns.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
query_exactly_one! : Db, Str, List(Binding) => Try(Row, ExactlyOneErr)Run one query that must return exactly one row.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
exec_script! : Db, Str => Try({}, [SqliteErr(ErrCode, Str)])Run every statement in a script, for schema setup and migrations.
Takes no bindings and returns no rows, because a script is SQL the app wrote rather than SQL assembled from input. Anything that needs a parameter, or returns data, is a query.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
stub : DbResource-free connection value for pure tests.
The handle never resolves to an open database, so every call through
it fails as SqliteErr(Misuse, _), the same way a call through a
released handle does. It exists for the app that keeps a Db in its
model, to let a pure expect build that model. Do not use it to
test queries or resource lifetime.
Row
:= { names : List(Str), values : List(Value) }One row of a result, with its column names.
A row is ordinary Roc data by the time an app sees it: the whole result crossed the boundary at once, so reading a column is a lookup rather than an effect. Decode with the methods below.
is_eq : _Two rows are equal when their names and values are. Worth having so a decoded result can be compared to an expected one in a test.
This row's column names, in the order the query selected them.
This row's values, in column order.
The value in a named column, still tagged.
Decode a TEXT column.
Decode a BLOB column.
Decode a REAL column.
Decode an INTEGER column.
Decode an INTEGER column that must fit in an I32.
Decode an INTEGER column that must fit in an I16.
Decode an INTEGER column that must fit in an I8.
Decode a non-negative INTEGER column.
Decode an INTEGER column that must fit in a U32.
Decode an INTEGER column that must fit in a U16.
Decode an INTEGER column that must fit in a U8.
nullable_str : Row, Str -> Try([NotNull(Str), Null], DecodeErr)Decode a nullable TEXT column.
nullable_bytes : Row, Str -> Try([NotNull(List(U8)), Null], DecodeErr)Decode a nullable BLOB column.
nullable_f64 : Row, Str -> Try([NotNull(F64), Null], DecodeErr)Decode a nullable REAL column.
nullable_i64 : Row, Str -> Try([NotNull(I64), Null], DecodeErr)Decode a nullable INTEGER column.
Build a row without a database, for pure tests.
Row decoding is where an app's own logic usually sits -- which
column means what, and what to do when one is missing -- so it is
worth an expect. names and values are paired by position.
Stmt
Sqlite.Stmt :: # (opaque)A compiled statement, ready to run again with different bindings.
Preparing once and running many times skips re-parsing the SQL, which is what a per-frame or per-record write wants. The host owns the compiled statement; the last handle released finalizes it, and the connection it came from stays open at least that long.
Run this statement, which must not return rows.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
Run this statement and decode every row it returns.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
query_exactly_one! : Stmt, List(Binding) => Try(Row, ExactlyOneErr)Run this statement, which must return exactly one row.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
stub : StmtResource-free statement value for pure tests. See Db.stub.
Service
Sqlite.Service :: # (opaque)Opaque SQLite authority supplied by App.Io. Opening a file also takes the directory handle it is in.
for_host : Authority -> ServicePrivate platform construction; no application can manufacture the argument.
Open or create a database beneath a writable directory, under
default_config. Missing directories on the way are created.
data = io.files().app_data!()?
db = io.sqlite().open!(data, "scores.db")?A path that is not plainly relative, or that meets a symbolic link,
is PathInvalid, as for a Files read. Legal in init!, where it blocks startup, and in
tasks, where it parks the task; refused in update! and render!.
open_with! : Service, Dir, Str, Config => Try(Db, OpenErr)Open a database beneath a writable directory with explicit limits and access mode.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
open_read! : Service, ReadDir, Str => Try(Db, OpenErr)Open an existing database read-only beneath any directory handle. The connection is locked down so it cannot be turned into a writer.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.
open_memory! : Service => Try(Db, OpenErr)Open a private in-memory database, gone when its last reference is.
Legal in init!, where it blocks startup, and in tasks, where it
parks the task; refused in update! and render!.