Databases

Every database module of Hilde binds values apart from the statement's text: a value never becomes SQL. Connections, transactions and cursors are linear values, so a programme that forgets to close one does not compile.

SQLite

Standard.Sqlite checks every @query and @execute against the schema that the migration files build, when the programme is compiled. A query that names a missing column, or SQL outside the accepted forms, does not compile; these are the H8 error codes, see the SQL section of the error codes. Standard.Search adds full-text search with FTS5.

Standard.SqlCipher is the same with an encrypted file; its key is a linear value, wiped when it is closed.

Distributed SQL: SQLite replicated through Raft

Standard.SqliteReplica runs one SQLite database on several machines and keeps them the same, after the pattern of rqlite but written from scratch in Hilde. It is built on Standard.Raft: every write is an entry of the Raft log, and every node applies the entries in the same order to its own SQLite file. Standard.RaftTcp carries the Raft messages between the nodes over TCP.

• Writes go through the leader. proposeWrite checks the statement, prepares it against the leader's database and puts it into the log. It runs on every node once the entry is committed. A follower refuses a write.
• Only bound values. A write is one statement whose values are all parameters; a quoted literal in it is refused, so nothing in its text is data.
• No answers that differ between nodes. random, randomblob, the current time and date, and a time function given now are refused. The caller binds the time or the random number it chose as a value instead. attach, detach, pragma, vacuum and transaction statements are refused too; each write stands alone.
• The same result everywhere. A constraint that refuses a write refuses it on every node with the same SQLite code. resultOf gives the result of a write by its log index: whether it ran, the rows it changed, and the code when it did not. The last 1024 results are kept.
• Two kinds of reads. queryLocal reads what the node has applied, possibly behind the leader. A linearizable read asks the leader for a barrier, an empty entry in its term; once the leader has applied it and is still the leader in that term, queryLinearizable reads, and no other leader can have committed anything this node does not hold.
• Snapshots are the database copied with SQLite's online backup; a node that fell behind is brought back from one.

The tests run a cluster of three nodes on files, with partitions: a leader cut off from the others never gets its write applied and never makes a linearizable read, while the leader on the other side does. Because Raft owns no network, disk or clock, every run of these tests is the same.

case proposeWrite(&!node, &store, "INSERT INTO kv (k, v) VALUES (?, ?)", &values) of
    when Right(right as index: Nat64) do
        skip;
    when Left(left as refusal: Nat8) do
        skip;
end case;

PostgreSQL

Standard.Postgres is a client over the system's libpq, with the types and the shape of Standard.Sqlite.

• Connection. openWriter opens a connection that may write, openReader one whose transactions are all read only, so a write fails on the server as well. Both take host, port, database, user, password and a file of CA certificates.
• TLS. Every connection runs over TLS with sslmode verify-full: the server's certificate must chain to the given CA file and carry the host name. There is no way to open a connection without this check.
• Values. Values are bound as $1, $2 and travel in the binary form of their type, so a text that looks like SQL stays text.
• Types. Int64, Float64, Bool, text, bytes, null, and timestamps as microseconds since 1970-01-01 UTC, sent as timestamptz. A column that may be null is read with its OrNone reader; a reader of the wrong type stops the programme, because that is a mistake in the programme, not in the data.
• Transactions. begin gives a linear Transaction that only commit or rollback ends. A nested begin is refused before anything is sent. When a statement in the transaction failed, the commit rolls back and fails with 25P02.
• Migrations. migrate applies the migrations embedded with @migrations, each in its own transaction, and records them in the table hilde_migrations.
• Errors carry the SQLSTATE; isUniqueViolation, isConstraintViolation and isBusy classify them.

case openWriter(host, 5432, "app", "app", asSpan(&password), caFile) of
    when Right(right as made: Connection[Writing]) do
        var db: Connection[Writing] := made;
        let values: Values := withText(withInt64(noValues(), 42), "Ada");
        let changed: Either[PgError, Nat64] :=
            execute(&!db, "INSERT INTO people (id, name) VALUES ($1, $2)", values);
        closeDatabase(db);
    when Left(left as error: PgError) do
        logLine(describe(error));
end case;

MariaDB

Standard.MariaDB is a client over the system's libmariadb with the same types and functions as Standard.Postgres, so a programme moves between the two with little change.

• Connection. openWriter and openReader, with the same arguments as for PostgreSQL; a reading connection makes every transaction read only.
• TLS. Every connection requires TLS, and the server's certificate is verified against the CA file, host name included.
• Values. A statement is prepared and its values are bound to its question marks, apart from its text.
• Types. The same as for PostgreSQL. A timestamp is microseconds since 1970-01-01 UTC, sent as a UTC date and time between the years 0 and 9999. Columns are read as libmariadb converts them; a conversion it refuses stops the programme.
• Transactions. begin, commit and rollback as for PostgreSQL.
• Migrations. MariaDB commits a change to a table's form at once, so a migration that fails half way is not undone; its number is recorded only when it ran whole.
• Errors carry the MariaDB error number, classified by the same three functions.

case openWriter("db.local", 3306, "shop", "app", asSpan(&password), "ca.pem") of
    when Right(right as db: Connection[Writing]) do
        let values: Values := withText(withInt64(noValues(), 7), "apple");
        let changed: Either[MariaError, Nat64] :=
            execute(&!db, "insert into stock (id, name) values (?, ?)", values);
        closeDatabase(db);
    when Left(left as failed: MariaError) do
        printLn(describe(failed));
end case;

DuckDB

Standard.DuckDB opens a database in a file, with external access and extensions switched off and the configuration locked. SCHLUCKI keeps its tables in SQLite or DuckDB.

One interface

Standard.Database is one interface over SQLite, SQLCipher, PostgreSQL and MariaDB; the backend is chosen when the database is opened, and the code that reads and writes stays the same. Statements use question marks on every backend; for PostgreSQL they become $1, $2. A driver is in a programme only when its module is imported: Standard.Database.Sqlite, Standard.Database.Postgres or Standard.Database.MariaDB. An error is classified the same way on every backend.

Source: github.com/heikozim/hilde.