sqlite

sqlite is the database in a file, and the library most applications reach for first. The Mica library for it is one unit, sqlite.mica, over the system’s libsqlite3, with no C of its own: everything it needs from sqlite’s C interface is stated in two contracts, sqlite3.external, which your program consumes too, and sqlite3-internal.external, the library’s own. You copy its directory into your project and own it.

This is version 2.0.0. The library’s mica.project states it, and every program built against the library names it: its build prints Using library 'sqlite' 2.0.0, and mica --inspect lists it in the program’s manifest.

The library lives at mica-container/libraries/sqlite: make run builds it and runs its demo, and make check runs its test against sqlite under both string encodings.

The daily road

database := Open(":memory:") on fail code do Stop("open", code);
defer Close(address database);

query := Prepare(database, "select title, score from notes where tag = :tag order by title")
    on fail code do Stop("prepare", code);
defer Finalize(address query);
BindText(query, 1, "home") on fail code do Stop("bind", code);
titleColumn := Column(query, "title") on fail code do Stop("column", code);
scoreColumn := Column(query, "score") on fail code do Stop("column", code);

for row in Rows(query) do
begin
    if IsNull(row, scoreColumn) then
        WriteLn("  %ls, not scored", Text(row, titleColumn))
    else
        WriteLn("  %ls, scored %.1lf", Text(row, titleColumn), Float64(row, scoreColumn));
end
on fail code do Stop("the query", code);

The notes chapter builds a whole application on it.

How it works

A database and a statement are values. Open answers a Database and Prepare a Statement, each holding the handle sqlite answered for it. Close and Finalize give the handle back and leave the record empty, so a defer right after the open or the prepare releases it however the block ends, and a second release does nothing.

A row is the statement itself. Rows(statement) streams the rows, each element the statement positioned on the row, which the column readers take. However the loop ends, at the last row, by a leave or by a failure, the statement is reset, so it runs again from its first row with its bindings kept. A failure of a step, such as a database another connection has locked, reaches the loop’s own on fail, and can never pass for a clean end of rows.

Positions, and names looked up once. Columns count from zero and parameters from one, as sqlite counts them. Column(statement, "title") and Parameter(statement, ":tag") turn a name into its position, once, before the loop.

sqlite’s types, and NULL. Kind answers what a column of the current row holds (IntegerKind, FloatKind, TextKind, BlobKind or NullKind), and IsNull whether it is NULL. Int64, Float64, Text and Blob convert as sqlite converts, and read NULL as their zero. Every text and blob they answer is a copy in your program’s own memory.

Failures carry sqlite’s code. Every call that can fail fails with sqlite’s result code on the failure channel: SqliteBusy, SqliteConstraint, SqliteCantOpen and the rest of the SqliteCode domain. Message(database) answers sqlite’s own text for the most recent failure:

Add("buy milk", 1.0, "home") on fail code do
    if code = SqliteConstraint then
        WriteLn("not stored twice: %ls", Message(database));

not stored twice: UNIQUE constraint failed: notes.title

Nothing that releases can fail: Close, Finalize and Reset are quiet, because what sqlite answers there only repeats a failure already reported where it happened.

Transactions. Begin, Commit and Rollback; RollbackUnlessCommitted is the call to defer right after Begin, which rolls back a transaction still open when the block ends and does nothing after a Commit.

The routines

The library’s README has each routine’s signature and what it answers on failure.

Threads and memory

A connection is opened in sqlite’s serialized mode, and its contract states it thread_safe, so a Database may be handed to tasks on other carriers. One of sqlite’s functions is not safe that way: sqlite3_errmsg answers text another task’s call on the connection may replace and free. It stands in the library’s own contract, which your program never consumes, and Message, the one road to it, holds the connection’s own mutex while it copies the text. A statement stays with the task that prepared it: a column’s text lives in the statement until its next step, so the compiler refuses a Statement on every road into another task, and a task that reads rows prepares its own statement on the shared connection.

sqlite owns its handles, and Mica never frees them: Close and Finalize give them back, and the compiler refuses a dispose of either. What a bind hands over, sqlite copies before the bind returns. Under valgrind, the library’s test shows no invalid read or write and no lost memory; its one record is the thread storage of the four carriers its tasks run on, which live until the program ends.

A contract with no shim

sqlite3.external and sqlite3-internal.external state sqlite’s C interface in the contract vocabulary every C library uses:

• sqlite3_open_v2 answers its connection through a sqlite3 **. The parameter is call_by_result, so the Mica call answers the connection and the C status becomes the failure. sqlite hands back a connection even when the open fails, so the contract names sqlite3_close_v2 as its release_on_failure.
• sqlite3_bind_text takes a byte count of -1 and the destructor SQLITE_TRANSIENT. Both are fixed arguments, which no call writes.
• sqlite3_column_text and sqlite3_errmsg answer c_text, copied into a string at the call.
• Every status-answering function states status_code with its success statuses, and the domain lists sqlite’s codes with their values.
• The connection’s type states thread_safe, and the statement’s does not. The one function that would make the statement about the connection untrue, sqlite3_errmsg, is in the internal contract, beside the mutex that makes it safe.

The contracts reference states each of these fields.

Building it into your project

cd sqlite
mica --compile --link --output-modifier archive --emit-contract --external static \
    --project . --source sqlite.mica --build ../build/sqlite
cd ..

mica --compile --link \
    --external-contract build/sqlite/mica.external,sqlite/sqlite3.external --external static \
    --source your-program.mica --build build

The library compiles once into an archive with its own contract, through its mica.project, which supplies sqlite’s contract and the library’s version; the source flag keeps the demo, which the project lists for your editor, out of the library. The emitted contract carries the archive, sqlite’s link flag and the version to your program. It needs libsqlite3-dev, sqlite 3.7.14 or later, which the container image carries.