# onelite docs, all pages (OneLite 0.2.0-preview.2) Every page of https://recursiveai.org/onelite/docs/ and https://recursiveai.org/onelite/cookbook/ as markdown, getting started first. Index: https://recursiveai.org/onelite/llms.txt # Getting started with OneLite Install OneLite 0.2.0-preview.2 for .NET 10, create a SQLite database, write to it and query it, in C# with no native library. OneLite is an embedded SQL database written on the .NET base class library alone that reads and writes SQLite files. > **Fixed in 0.2.0-preview.2: upgrade.** From the Known issues section of [the README at v0.2.0-preview.2](https://github.com/recursive-ai-oss/onelite-csharp/blob/v0.2.0-preview.2/README.md#known-issues): > > “**0.2.0-preview.1 can silently drop a write (data loss); upgrade to 0.2.0-preview.2.** In a rollback-journal mode (`journal_mode` DELETE, TRUNCATE or PERSIST, the default for `Database.Open` and `OneLiteConnection`), an `INSERT`, `UPDATE` or `DELETE` in autocommit mode returned success with no change while another connection held a read, in the same process or another one. It now fails with `SQLITE_BUSY` (‘database is locked’) as SQLite does; WAL mode was never affected. Remaining differences between several connections on one file are listed in [docs/engine.md](https://recursiveai.org/onelite/docs/engine/#known-differences-with-several-connections-on-one-file). > > “**0.2.0-preview.1 could silently lose a key change; upgrade to 0.2.0-preview.2.** A key change could be silently lost: `PRAGMA rekey` or `PRAGMA hexrekey` run through a call that reads one row and stops (`ExecuteScalar`, a reader closed early, `Statement.Step` once, the happy-path scalar and query methods) answered `ok` but kept the old key. `ExecuteNonQuery` and running the statement to completion were not affected. Upgrade to 0.2.0-preview.2, or after a rekey in preview.1 reopen the file with the new key to confirm it.” ## Before you use it - **What works.** From the README’s Status section: “Preview (`0.2.0-preview.2`). The storage layer, the SQL engine, `RecursiveAI.OneLite.Data` and the happy-path API work, as do FTS3/4/5, R*Tree, `vec0` (OneLite’s own vector table: it follows sqlite-vec’s SQL API but its files are not interchangeable with sqlite-vec’s, see [docs/vec.md](https://recursiveai.org/onelite/docs/vec/)), the backup API and SQLCipher-4-compatible encryption.” - **Not yet.** In the same section’s words: “Still to come before 1.0: `BEGIN CONCURRENT`, the `Microsoft.Data.Sqlite`/EF Core adapter, the `onelite` command-line tool, and published benchmarks. APIs may change between previews.” So `Microsoft.Data.Sqlite` and EF Core code can’t run on OneLite yet, and there are no speed figures. - **How compatible.** OneLite is measured against one pinned release, SQLite 3.53.4, on query results, result codes and error messages. Its [compatibility page](https://recursiveai.org/onelite/docs/compatibility/) has the numbers and every known difference; in summary, sqllogictest “622 files, 7,420,713 records: 5,939,877 pass, 0 wrong answers, 1 explained failure”, and differential testing against `sqlite3` “4 seeds, 22 minutes each, about 12,000 generated cases and 0.8 M checks: 0 unexplained differences”. - **Encryption.** SQLCipher-4-compatible encryption works, in the README’s words. It has had [the project’s own review](https://recursiveai.org/onelite/docs/encryption-review/) (not an external audit) and has not been independently audited. ## Install The packages target `net10.0`, so you need the .NET 10 SDK, and `dotnet add package` needs `--prerelease` or the exact version. ```shell dotnet new console -o OneLiteStart && cd OneLiteStart dotnet add package RecursiveAI.OneLite --version 0.2.0-preview.2 ``` Two packages are on NuGet: | Package | What it is | |---|---| | `RecursiveAI.OneLite` | the database: storage layer, SQL engine and the happy-path API; no dependency beyond .NET | | `RecursiveAI.OneLite.Data` | an ADO.NET provider (`DbConnection`, `DbCommand`, `DbDataReader`…) with real async; depends on the core only | ## A first program Put this in `Program.cs` and run `dotnet run`: ```csharp using RecursiveAI.OneLite; await using var db = await Database.OpenAsync("todo.db"); // Script n runs once, when PRAGMA user_version is below n. await db.MigrateAsync(""" create table todos (id integer primary key, title text not null, done integer not null) """); await db.WriteAsync(async tx => { var todos = tx.Table(); await todos.InsertAsync(new Todo { Title = "Install OneLite", Done = true }); await todos.InsertAsync(new Todo { Title = "Read the cookbook" }); }); bool open = false; foreach (Todo t in await db.QueryAsync( $"select * from todos where done = {open}")) Console.WriteLine($"To do: {t.Title}"); Console.WriteLine($"{await db.ScalarAsync($"select count(*) from todos")} rows"); [OneLiteRow] public class Todo { public long Id { get; set; } public string Title { get; set; } = ""; public bool Done { get; set; } } ``` ```text To do: Read the cookbook 2 rows ``` - `Database.OpenAsync` creates the file if it is missing and opens it with write-ahead logging (WAL), foreign keys on and a 5-second busy timeout. One `Database` is one connection, and it serializes its own calls. - `MigrateAsync` brings the schema up to date, counting with `PRAGMA user_version`. Append scripts; never edit one that has shipped. - `WriteAsync` runs the delegate in one transaction and commits it, or rolls it back if it throws. - Every `{value}` in a `$"…"` query is a bound parameter, never pasted into the SQL. - `[OneLiteRow]` has a source generator write the mapping, so it works with trimming and NativeAOT. Booleans are stored as integers. The file is an ordinary SQLite database in WAL mode. The `sqlite3` shell (3.53.4, from sqlite.org) reads it: ```shell sqlite3 todo.db "select * from todos" "pragma user_version" "pragma journal_mode" \ "pragma integrity_check" ``` ```text 1|Install OneLite|1 2|Read the cookbook|0 1 wal ok ``` ## ADO.NET instead `RecursiveAI.OneLite.Data` gives the same database to code written against `DbConnection`. Add the package and replace `Program.cs`: ```shell dotnet add package RecursiveAI.OneLite.Data --version 0.2.0-preview.2 ``` ```csharp using RecursiveAI.OneLite.Data; using var conn = new OneLiteConnection("Data Source=todo.db"); conn.Open(); using var cmd = conn.CreateCommand(); cmd.CommandText = "select title from todos where done = @done"; cmd.Parameters.Add(new OneLiteParameter("@done", true)); Console.WriteLine(cmd.ExecuteScalar()); ``` ```text Install OneLite ``` `todo.db` stays in WAL mode: the journal mode is stored in the file. A new file the provider creates starts in SQLite’s rollback-journal default. See [Use SQLite with ADO.NET](https://recursiveai.org/onelite/cookbook/sqlite-ado-net-csharp/) for a complete program and the [provider docs](https://recursiveai.org/onelite/docs/data/) for connection strings, parameter and type rules. ## What else is in the box - **Full-text search.** FTS5, FTS3 and FTS4 on the documented shadow-table layouts: [FTS5](https://recursiveai.org/onelite/docs/fts5/), [FTS3 and FTS4](https://recursiveai.org/onelite/docs/fts3/). - **Spatial and vector.** R*Tree and `dbstat` ([R*Tree](https://recursiveai.org/onelite/docs/rtree/)), and `vec0` for exact k-nearest-neighbour search ([vec0](https://recursiveai.org/onelite/docs/vec/)). `SearchAsync` on the happy path fuses full-text and vector results ([the happy path](https://recursiveai.org/onelite/docs/happy-path/)). - **Backup and blobs.** The backup API, `VACUUM INTO` and incremental blob I/O ([backup and blob I/O](https://recursiveai.org/onelite/docs/backup-blob/)). - **Clearer errors.** Exceptions keep SQLite’s code and message and add the position in the SQL with a caret and suggestions ([errors](https://recursiveai.org/onelite/docs/errors/)). - **In the browser.** Storage backends for browser OPFS ([in the browser](https://recursiveai.org/onelite/docs/browser/)). ## Next - [Cookbook](https://recursiveai.org/onelite/cookbook/): SQLite without native dependencies, create and query, open an existing file, ADO.NET, transactions. - [Compatibility with SQLite 3.53.4](https://recursiveai.org/onelite/docs/compatibility/): what was measured and every known gap. - [Overview](https://recursiveai.org/onelite/docs/overview/) (the README) and the [changelog](https://recursiveai.org/onelite/docs/changelog/). OneLite is not affiliated with or endorsed by the SQLite developers; “SQLite” is a registered trademark of Hipp, Wyrick & Company. # OneLite OneLite is a pure-managed, SQLite-compatible embedded SQL database for .NET. It is written in C# on the base class library alone, with no native code, so it runs wherever .NET runs: trimmed and NativeAOT builds, and the browser. The goal is a drop-in for SQLite at four layers, behaving identically by default: - **File format.** Database, rollback journal, WAL and shared-memory files are read and written both ways, and can be used live beside `sqlite3` processes. - **SQL semantics.** Type affinity, collation, `NULL` handling, result text, result and extended codes, and error messages. - **C-API semantics.** Prepare and step, locking, busy handling, hooks. - **Extensions that live in files.** FTS3/4/5 and R*Tree. Improvements are opt-in and keep files standard: row-level `BEGIN CONCURRENT`, SQLCipher-4-compatible encryption, a resumable VM with sync and async drivers over a pluggable VFS, an exact-nearest-neighbour `vec` table, clearer errors, and a read-only forensic recovery API. ## Status Preview (`0.2.0-preview.2`). The storage layer, the SQL engine, `RecursiveAI.OneLite.Data` and the happy-path API work, as do FTS3/4/5, R*Tree, `vec0` (OneLite's own vector table: it follows sqlite-vec's SQL API but its files are not interchangeable with sqlite-vec's, see [docs/vec.md](https://recursiveai.org/onelite/docs/vec/)), the backup API and SQLCipher-4-compatible encryption. Measured compatibility with SQLite 3.53.4, including every known difference, is in [docs/compatibility.md](https://recursiveai.org/onelite/docs/compatibility/). Still to come before 1.0: `BEGIN CONCURRENT`, the `Microsoft.Data.Sqlite`/EF Core adapter, the `onelite` command-line tool, and published benchmarks. APIs may change between previews. ## Known issues **0.2.0-preview.1 can silently drop a write (data loss); upgrade to 0.2.0-preview.2.** In a rollback-journal mode (`journal_mode` DELETE, TRUNCATE or PERSIST, the default for `Database.Open` and `OneLiteConnection`), an `INSERT`, `UPDATE` or `DELETE` in autocommit mode returned success with no change while another connection held a read, in the same process or another one. It now fails with `SQLITE_BUSY` ("database is locked") as SQLite does; WAL mode was never affected. Remaining differences between several connections on one file are listed in [docs/engine.md](https://recursiveai.org/onelite/docs/engine/#known-differences-with-several-connections-on-one-file). **0.2.0-preview.1 could silently lose a key change; upgrade to 0.2.0-preview.2.** A key change could be silently lost: `PRAGMA rekey` or `PRAGMA hexrekey` run through a call that reads one row and stops (`ExecuteScalar`, a reader closed early, `Statement.Step` once, the happy-path scalar and query methods) answered `ok` but kept the old key. `ExecuteNonQuery` and running the statement to completion were not affected. Upgrade to 0.2.0-preview.2, or after a rekey in preview.1 reopen the file with the new key to confirm it. ## How it is built OneLite is a clean-room implementation. It is written from SQLite's public documentation (the file format, locking, atomic commit, WAL, SQL language and C API pages on sqlite.org), Zetetic's published SQLCipher design, and the observed behaviour of the `sqlite3` shell, which serves as a black-box test oracle. No source code of SQLite, SQLCipher or other SQLite implementations was read, ported or translated. ## Packages | Package | What it is | |---|---| | `RecursiveAI.OneLite` | The core: storage layer, engine layer and the happy path. No third-party dependencies. | | `RecursiveAI.OneLite.Data` | ADO.NET provider with real async. Depends on the core only. | | `RecursiveAI.OneLite.SQLitePCLRaw` | An `ISQLite3Provider` so `Microsoft.Data.Sqlite` and EF Core can run on OneLite. Depends on the core and `SQLitePCLRaw.core`. Not published yet. | | `RecursiveAI.OneLite.Cli` | The `onelite` command-line tool (`dotnet tool install`). Not published yet. | ## Building Requires the .NET SDK pinned in `global.json` (or a later 10.0 feature band). ```sh dotnet build -c Release dotnet test -c Release dotnet pack -c Release ``` Build output goes to `artifacts/` (ignored by git), or to the directory named by the `ONELITE_ARTIFACTS` environment variable; give each checkout its own. `build/ci.sh` runs what CI runs on Linux x64: build, test, a NativeAOT smoke publish, a browser-wasm smoke build and pack. The `Checked` configuration (`dotnet build -c Checked`) is Release code with the `CHECKED` symbol defined, for extra bounds assertions; tests and fuzzing run in it. ## Layout - `src/` the packages. `src/OneLite.Generators` is the source generator, shipped inside the core package as an analyzer. - `tests/` unit tests and the AOT and WASM smoke apps. - `tools/` test-support command-line tools (oracle driver, sqllogictest runner, differential fuzzer, interop, durability and history checkers). Built, never packed. - `fuzz/` and `bench/` fuzz targets and benchmarks. `docs/` behaviour notes. `testdata/` test data. ## Licence MIT OR Apache-2.0, at your option. See `LICENSE-MIT` and `LICENSE-APACHE`. Copyright (c) Recursive AI, LLC. OneLite is an independent project and is not affiliated with, endorsed by, or sponsored by the SQLite developers or Hipp, Wyrick & Company. SQLite is a registered trademark of Hipp, Wyrick & Company. # The happy path An opinionated layer in the core package (`RecursiveAI.OneLite`) for application code: interpolated queries, write transactions as functions, a thin typed table, source-generated mapping and migrations. It sits on the engine layer (`Database`, `Connection`, `Statement`) and uses nothing else. ```csharp await using var db = await Database.OpenAsync("app.db"); // WAL, foreign_keys=ON, 5 s busy timeout var users = await db.QueryAsync($"select * from users where age > {age}"); await db.WriteAsync(async tx => { await tx.Table().InsertAsync(cleo); }); ``` ## `$"..."` binds parameters **Every `{hole}` in an interpolated string passed to `QueryAsync`, `ExecuteAsync`, `ScalarAsync`, `Where` and their twins is a bound parameter, never pasted into the SQL.** `$"select * from users where name = {name}"` runs `select * from users where name = ?1` with `name` bound, so it is safe against SQL injection. A hole is one value (number, string, blob, `Guid`, date, null...); it cannot be a table name, a column name or an SQL fragment. To run text as written, use `ExecuteScript(string)`, which takes no parameters; never build that text from user input. A plain `string` is deliberately not accepted by the query methods. ## Opening `Database.OpenAsync(path)` / `Database.OpenDefault(path)` open (creating) a database with write-ahead logging, `foreign_keys=ON` and a 5 s busy timeout, and return when the connection is ready. Pass `DatabaseOptions` to choose otherwise (they are used as given); `":memory:"` is a private in-memory database. **WAL is persistent:** opening an existing rollback-journal file with `OpenAsync`/`OpenDefault` switches that file to WAL for good (it is a property of the file, as `PRAGMA journal_mode=WAL` is in SQLite), so other programs that open it, `sqlite3` included, see a WAL database and need write access to the directory for the `-wal` and `-shm` files; pass `DatabaseOptions` to keep the file's journal mode. `Database.Open` is unchanged and keeps the reference defaults. One `Database` serializes its calls on one connection; open more connections with `Connect()` for parallel readers. ## Queries `QueryAsync` returns a `List`. `T` is a type marked `[OneLiteRow]` or a single value type (`long`, `string`, `Guid`...), read from the first column. `ScalarAsync` returns the first column of the first row, or `default`. `ExecuteAsync` returns the rows changed. Every method has a blocking twin without the suffix. Errors keep SQLite's code and message (`OneLiteException`). A cached `QueryAsync` completes synchronously and allocates the result objects and the list, about 16 bytes per row for a `long` list and 104 bytes per row for a four-column record, plus a fixed cost per call for the SQL text, the parameters and the compiled statement. ## `WriteAsync` ```csharp await db.WriteAsync(async tx => { ... }); // returns when committed var id = await db.WriteAsync(async tx => await tx.Table().InsertAsync(d)); ``` Runs the delegate in a write transaction (`BEGIN IMMEDIATE` today) and commits. If the database is busy (`SQLITE_BUSY`, including a busy snapshot) the transaction rolls back and **the delegate runs again** after a jittered backoff (about 10 ms doubling to 500 ms), up to `MaxWriteAttempts` (default 8). **The delegate must therefore have no side effects outside the database.** An exception from the delegate rolls back and propagates. Use the `WriteTransaction` it receives; calling the `Database` inside the delegate throws, because the database's lock is held by the transaction. The transaction object is invalid after the delegate returns. ## Mapping Mark a class, record or struct with `[OneLiteRow]`; the build generates the mapper (no reflection, trimming and NativeAOT safe). Result columns match constructor parameters and settable or `init` properties by name, ignoring case; extra columns are ignored. `[OneLiteColumn("name")]` renames, `[OneLiteIgnore]` skips, `[OneLiteKey]` marks the key (without one, a property named `Id`). A missing column for a required member (constructor parameter without a default, or `required`) and a NULL for a non-nullable member throw `OneLiteMappingException` naming the column, the member and the type. Properties with a getter that cannot be set when reading (get-only) are written but not read. Diagnostics `ONELITE001`-`ONELITE003` name the type and the member at build time (unsupported property type, no accessible constructor, type not public or internal). Supported property types: `bool`, integers, enums (stored as integers), `float`, `double`, `decimal`, `char`, `string`, `byte[]`, `Guid`, `DateTime`, `DateTimeOffset`, `TimeSpan`, `DateOnly`, `TimeOnly` and `Nullable<>` of these (stored as the same text forms the ADO.NET provider uses). ## `Table` `db.Table()` (or `tx.Table()`) is a thin view: `EnsureCreatedAsync` (a `STRICT` table with a column per mapped property, NOT NULL where the C# type is not nullable, the key as `PRIMARY KEY`), `InsertAsync` (returns the rowid; a single integer key of 0 is left to the database), `UpsertAsync` (`ON CONFLICT (key) DO UPDATE`), `GetAsync(key)`, `DeleteAsync(key)` and `WhereAsync($"fragment")`. There is no LINQ provider and no change tracking. Raw SQL creates exactly what it says. ## `SearchAsync`: full-text and vector search together ```csharp var hits = await db.SearchAsync("docs", "overdue invoice", embedding, k: 10); foreach (var h in hits) Console.WriteLine($"{h.Item.Title} score {h.Score:F4} text #{h.TextRank} vector #{h.VectorRank}"); var recent = await db.SearchAsync("docs", "overdue invoice", embedding, $"year >= {2024}", k: 5); // {2024} is a bound parameter ``` **What to index.** The convention for a content table `docs` (a rowid table; the key should be `INTEGER PRIMARY KEY`): - `docs_fts`: an FTS5 table (`create virtual table docs_fts using fts5(title, body)`), with `rowid` = the row's id in `docs`; - `docs_vec`: a `vec0` table (`create virtual table docs_vec using vec0(embedding float[768])`), with `rowid` = the same id. You keep the index tables in step yourself (insert them in the same `WriteAsync` as the content row, or use FTS5's external-content mode and triggers). `SearchOptions` renames the tables and the vector column. Table names are trusted identifiers: never build them from user input. Embeddings are your job; OneLite does not generate them. **How it works.** Inside one read transaction (one snapshot) the call runs up to two queries: the FTS5 table ordered by `rank` (bm25), and the `vec0` table's exact nearest neighbours. Each list is cut to `Depth` entries (default `max(100, 4 * k)`). The lists are fused with reciprocal rank fusion (Cormack, Clarke and Büttcher, SIGIR 2009): a row's score is the sum, over the lists it appears in, of `weight / (RrfConstant + rank)` with ranks counted from 1, constant 60 and weights 1 by default. Rows that only one list finds still score. Higher is better; equal scores order by the smaller row id. The top `k` rows are then read from the content table with the generated mapper and returned as `SearchHit`: the row, its row id, the fused score and the 1-based rank in each list (null where the row is absent). Only ranks matter, so the bm25 and distance scales never need normalising. **Text is words, not syntax.** The text is split on white space and every word is searched as a quoted FTS5 string, so `AND`, `NEAR`, `*`, `^`, `col:`, parentheses and quotes in user input are plain words and cannot raise a syntax error or reach other columns. Words are joined with OR (rows with more of them rank higher by bm25); `MatchAllWords = true` requires all of them. FTS5 tokenizes the words as the index does, so a word made only of punctuation matches nothing. **Choosing the mode.** Pass `text` only (empty embedding) for full-text, `embedding` only (`text` null) for vector search, or both. With neither, the call throws `ArgumentException`. **Filter.** The `$"..."` overload adds a condition on the content table's columns. It is applied inside both lists, so you get the best `k` rows that satisfy it, not the best rows with some removed afterwards. **Also** on `WriteTransaction` (it sees the transaction's own writes) and as blocking `Search`. Errors keep SQLite's code and message (a missing index table, a vector of the wrong dimension). It runs on `StepAsync`, so it suspends on cache misses of a slow backend. An async-only backend still needs a warm schema, because `Prepare` is synchronous. ## Migrations `MigrateAsync("create table ...", "create index ...")` brings the file up to date using `PRAGMA user_version` as the counter: script *n* runs when the version is below *n*, all pending scripts commit together, and a failing script leaves the version and schema as they were. Append scripts; never edit one that has shipped. A file whose version is higher than the number of scripts throws. Files written through the happy path are ordinary SQLite files: `integrity_check` passes in `sqlite3` and a `.dump` matches the same statements run in the shell. # ADO.NET provider (`RecursiveAI.OneLite.Data`) A standard ADO.NET provider on the engine layer: `DbConnection`, `DbCommand`, `DbDataReader`, `DbTransaction`, `DbBatch`, `DbDataSource`. It references only the core package. Dapper and other `DbConnection` consumers work unchanged. Where ADO.NET SQLite providers document a behaviour, this one matches it, and the test project checks each such behaviour against `Microsoft.Data.Sqlite` on the native library (the oracle). ## Real async Every `...Async` method awaits the storage layer: `OpenAsync`, `ExecuteReaderAsync`, `ReadAsync`, `NextResultAsync`, `ExecuteNonQueryAsync`, `ExecuteScalarAsync`, `CommitAsync`, `RollbackAsync`, `CloseAsync`, `DisposeAsync`. When a page is not cached the call returns an incomplete task and resumes when the backend completes the read; when everything is cached it completes synchronously without an async state machine. A token cancels a query that is waiting for I/O (`OperationCanceledException`; the connection stays usable). Cancellation is observed at I/O waits only: a fully cached query does not wait. `ReadAsync` over a cached database allocates nothing; reading numbers allocates nothing per row; `GetString` allocates the string; `GetUtf8Span` and `GetBlobSpan` return spans into the page cache that stay valid until the next `Read`. Over a local file on Linux or macOS, async does not make disk reads faster; it frees the thread while a slow backend (browser storage, HTTP range) answers. ## Connection strings | Key | Meaning | |---|---| | `Data Source` (`Filename`) | A path, `:memory:`, or `file:name?mode=memory`. With `Mode=Memory;Cache=Shared`, connections that use the same name share one in-memory database. | | `Mode` | `ReadWriteCreate` (default), `ReadWrite` (file must exist), `ReadOnly`, `Memory`. Writing on a read-only connection fails with SQLITE_READONLY (8); opening a missing file read-only or read-write fails with SQLITE_CANTOPEN (14). | | `Cache` | `Default`, `Private`, `Shared` (see above). | | `Password` | The passphrase of an encrypted (SQLCipher-4-compatible) database. | | `Foreign Keys` | `True`/`False` sets the foreign-key enforcement of the connection; absent keeps SQLite's default (off). | | `Default Timeout` | Seconds to wait for a lock before SQLITE_BUSY. Default 30. | | `Vfs` | A storage backend registered with `OneLiteConnection.RegisterVfs(name, vfs)`; the data source is then a path in that backend. | | `Pooling` | Accepted and ignored: connections are not pooled. | ## Commands and parameters * The text may hold several statements. `ExecuteReader` runs them in order: statements that return no columns run immediately, and the reader stops at the first that returns columns; `NextResult` moves on. Closing a reader early still runs the statements it did not reach, as executing the command would. * `RecordsAffected` is -1 when no `INSERT`, `UPDATE` or `DELETE` (or other statement that may write) ran, else the sum of `changes()` after each of them (so an `UPDATE` that matched nothing reports 0). * Parameters: `@x`, `:x`, `$x` match a parameter named `x` with or without its prefix (case-sensitive). `?NNN` matches a parameter named `?NNN`, else the NNNth parameter of the collection; a plain `?` takes the parameter at its position. A statement parameter with no value throws `InvalidOperationException` ("Must add values for the following parameters: ..."). A `null` `Value` throws ("Value must be set."): use `DBNull.Value`. `double.NaN` cannot be stored. * Types bind as `long`/`int`/`short`/`byte`/`bool`/enums → INTEGER; `double`/`float` → REAL; `string`/`char` → TEXT; `byte[]`, `ReadOnlyMemory` → BLOB; `Guid` → upper-case TEXT; `DateTime` → `yyyy-MM-dd HH:mm:ss.FFFFFFF`; `DateTimeOffset` with offset; `TimeSpan` → `c`; `decimal` → invariant TEXT without trailing zeros. * `GetValue` returns `long`, `double`, `string`, `byte[]` or `DBNull`. Typed getters on NULL throw `InvalidOperationException`. `GetDataTypeName` and `GetFieldType` describe the current value's storage class (declared column types are not exposed). * `Cancel()` does nothing: cancel an `...Async` call with its token. * The text is never rewritten; there is no `CommandType.StoredProcedure`. ## Transactions `BeginTransaction()` runs `BEGIN DEFERRED`; `BeginTransaction(deferred: false)` runs `BEGIN IMMEDIATE`. One transaction per connection (a second throws "OneLiteConnection does not support nested transactions."). `CreateCommand` hands the command the connection's current transaction; a command created earlier must have `Transaction` set, or execution throws (as `Microsoft.Data.Sqlite` does). Disposing an uncommitted transaction rolls it back. ## Batches `CreateBatch()` returns a `DbBatch`. `ExecuteNonQuery` and `ExecuteScalar` run the commands atomically (in a transaction of their own when the connection has none). `ExecuteReader` returns each command's result sets in turn and adds no transaction. ## Errors Engine errors surface as `OneLiteDataException : DbException` with `ResultCode`, `ExtendedResultCode` (also named `SqliteErrorCode`/`SqliteExtendedErrorCode`), `Detail` (what OneLite adds, for corruption the page and cause) and the message `SQLite Error 1: 'no such table: t'.` The inner exception is the engine's `OneLiteException`. ## Limits A connection is not thread-safe. `GetSchemaTable` is minimal (name, ordinal, type of the current value, type name). `GetSchema` and `CommandType` other than `Text` are not supported. # Clearer errors OneLite's errors say the same thing SQLite-compatible libraries say, and then say more. Nothing about the default behaviour changes. ## What stays identical `OneLiteException.Message`, `Code` (the primary result code) and `ExtendedCode` equal what the pinned SQLite release reports for the same statement on the same data: the same text, the same codes. `OneLiteDataException.Message` is `SQLite Error : ''.` as before. A test (`tests/OneLite.Tests/Diagnostics/ErrorDiagnosticsTests.cs`) runs more than 150 failing statements (syntax, names, arity, constraints, STRICT types, misuse) on OneLite and on the pinned library and compares code, extended code and message. ## What is added All members are read-only properties of `OneLiteException` (and are repeated on `OneLiteDataException`). They are empty or null when the error has nothing to add. They are filled in only when an error happens, so a statement that succeeds pays nothing. | Member | Meaning | |---|---| | `Sql` | The statement text the error is about. | | `Position` | `SqlPosition`: UTF-16 `Offset` and `Length` of the offending text, 1-based `Line` and `Column` (UTF-16 characters), and `ByteOffset` (UTF-8, the unit `sqlite3_error_offset` uses). | | `Caret` | The line holding the position, then a line with `^~~` under it. Long lines are cut around the position. | | `Identifier` | The name the error is about: the unknown table, column or function, the ambiguous column, the token a syntax error stopped at, the constrained column. | | `Suggestions` | Up to three names close to what was written, closest first (see below). | | `Constraint` | For a constraint failure: `Kind` (unique, primary key, not null, check, foreign key, STRICT data type), `Table`, `Columns`, `Name` (a CHECK's text) and, for unique and primary-key failures, `KeyValues`, the clashing key as SQL literals. | | `Explain()` / `ToString()` | A readable account: the message with its line and column, the SQL line with the caret, the constraint details, "did you mean". `ToString()` is the usual exception text followed by this. | Example: ``` no such column: nme at line 1, column 8: no such column: nme select nme from users ^~~ did you mean "name"? ``` ## How the pieces are found * **Position.** A syntax error uses the position the parser reports. For a name error, the statement text is tokenised again and the first token that spells the name in the role the message implies is taken (after `table.` when the message names a table; followed by `(` for a function; after `COLLATE` for a collation). A name that appears several times in the statement can therefore be reported at its first use. * **Suggestions.** Candidates come from the schema in scope: table and view names plus the names a `WITH` clause defines (unknown table), the columns of the tables the statement mentions (unknown column), the registered scalar function names and the aggregate and window function names (unknown function), the three built-in collations, and the common SQL keywords (a syntax error that stopped at a misspelt keyword). A candidate counts when its edit distance (insertions, deletions, substitutions, swaps of neighbours; case ignored) is at most a third of the written name's length (at least 1, at most 3), when the written name is a prefix of it, or, for names of two or more letters, when its letters appear in order inside the candidate (`nm` suggests `name`). For an unknown column that another table has, the suggestion is `table.column`; for an ambiguous column, the list of `table.column` that have it. For a wrong number of arguments, the single suggestion says how many the function takes. * **Constraints.** Kind, table and columns come from the message. The key values are copied from the registers that held the new row's key when the uniqueness probe failed, and are formatted only on the error path. ## Limits Runtime errors that are not constraint failures (an integer overflow, a bad `LIMIT`) carry the statement text but no position in it: the virtual machine does not track which expression a failing instruction came from. A name that does not appear in the statement text itself (for example one inside a view body) has no position. Suggestions never change which error is reported. # Readable plans `Connection.Explain(sql, analyze)`, `Connection.ExplainAsync` and `Statement.Plan` return a `QueryPlan`: a tree of `PlanNode`s with a text rendering (`Render()`) and a structured form (`Root`, `Children`, `Kind`, `Table`, `Index`, `Detail`, `Reason`, `Rejected`, `Runs`, `EstimatedRows`, `ActualRows`, `Loops`). The `EXPLAIN` and `EXPLAIN QUERY PLAN` statements are separate and not covered here. Plans are not part of the compatibility promise and their wording may change. ## The plan is the program's structure The compiler records each step as it generates the program: the access-path object that drives a loop is the same object the loop is emitted from, the index candidates are the ones the planner scored, and subqueries, materializations, sorts and aggregation are noted where their code is made. Nothing re-derives a choice afterwards, so the plan cannot disagree with the program. Compiling a statement without asking for a plan builds no recorder; every hook is a null check. ## Shape The root is the statement (`SELECT`, `INSERT`, `UPDATE` or `DELETE`; statements without table access, such as schema changes, pragmas and transaction control, have no plan and `Explain` returns null). A node's children are the steps made inside its loop, in the order they run: * `Scan` and `Search` read one table, index or materialized result. A join nests: the inner table is the child of the outer table's node, and the join order is the nesting order. `Detail` shows how, for example `USING INDEX orders_user (user_id=u.id)` or `USING INTEGER PRIMARY KEY (id>10 AND id<=20)`. `Reason` says in words which terms of the WHERE clause the access path answers, why a covering index was read instead of the table, or that an `ORDER BY` or `GROUP BY` is satisfied by its order. `Rejected` lists the other indexes the planner scored and why each was not used. * `Materialize` (a subquery in FROM, a view or a common table expression), `RecursiveCte`, `TableFunction`, `Subquery` (scalar, `EXISTS`, `IN`), `Compound` with an `Arm` per SELECT. `Runs` says whether a subquery or materialization runs `once` or `per outer row`. A subquery sits under the node whose loop it was compiled in, even when it runs only once. * After the loops: `Window`, `Aggregate` (`GROUP BY` keys; whether groups arrive in key order or go through a sorter), `Distinct`, `Sort` (`ORDER BY`). ## Estimates Estimates are the planner's guess, not statistics: `sqlite_stat1` is not read. A table is assumed to hold about a million rows; an equality on `k` columns of an index leaves 10 / k rows (one row for the full key of a unique index or a rowid); each range bound divides by 4 (16 for both bounds). Nodes that are not table access carry none. ## Actual rows `Connection.Prepare(sql, collectActualRows: true)` compiles one counter instruction at the start of each table loop and one at the top of its body; a statement prepared otherwise carries none, so the running loop is unchanged (the allocation tests show 0 bytes per step either way; the counting op is an array increment). `ActualRows` is the number of rows the loop yielded in all (before the rest of the WHERE clause filters them), `Loops` the number of times the loop was started; for an inner table of a join, the totals over all outer rows. The counters accumulate over executions of the same statement object. `Explain(sql, analyze: true)` prepares with counting, runs the statement with no values bound, and returns the plan; a statement that changes the database runs inside a savepoint that is rolled back. # ATTACH, DETACH and per-database pragmas Normative notes for the engine layer. Behaviour is that of the pinned `sqlite3` (lang_attach, lang_detach, pragma, atomiccommit §5); where the documentation is silent the text below records what the shell does, found by running it. ## Databases of a connection | Slot | Name | Notes | |---|---|---| | 0 | `main` | The file the connection opened. | | 1 | `temp` | In memory, created by the first statement that needs it. | | 2 and up | attached | `ATTACH file AS name`; the lowest free slot is used, so `PRAGMA database_list` is in slot order. | `TableInfo.Db` holds the slot. An unqualified name is looked up in `temp`, then `main`, then the attached databases in slot order; `schema.name` names one database. `schema.sqlite_master` (or `sqlite_schema`) is that database's schema table. ## ATTACH and DETACH - `ATTACH [DATABASE] expr AS name [KEY expr]`. The file name and the name are expressions evaluated when the statement runs (`?` works); a bare word stands for itself. NULL or an empty file name is a private temporary database; `:memory:` is a private in-memory database (journal mode `memory`). `file:` URIs are read for the path and `mode=ro|rw|rwc|memory`. - Errors: `too many attached databases - max 10` (checked first), `database NAME is already in use` (any case; also `main` and `temp`), `unable to open database: FILE` (read-only open of a missing file), `file is not a database` (the file is read at ATTACH, so a wrong key fails here). - `DETACH [DATABASE] name`: `cannot detach database main`, `no such database: NAME` (also for `temp`), `database NAME is locked` while the database is in a transaction of this connection (a read counts). - ATTACH and DETACH work inside a transaction. Both bump the schema cookie the statements compile against, so prepared statements recompile. - An attached database is opened like the main one (read-only if main is), with the connection's `synchronous`, the main database's text encoding and default page size for a new file, journal mode `delete`, and a 2000 KiB cache. With a `KEY` clause it is encrypted with that key (`KEY ''`: plain); without one it uses main's key when main is encrypted (SQLCipher's rule). ## Transactions across databases - A statement starts the transactions of the attached databases it reads (shared lock) and writes (reserved lock or WAL write lock) with the statement. Writes that reach an attached database through a trigger or a foreign-key action start its transaction when its first cursor opens. Statements of an explicit transaction hold the locks until COMMIT or ROLLBACK; the read locks of an autocommit statement end with it. - `BEGIN`, `SAVEPOINT`, `ROLLBACK TO` and statement rollbacks open and unwind a pager savepoint on every attached database that writes. - COMMIT writes the databases that changed together. One changed database: an ordinary commit. Two or more, all in rollback-journal mode with `synchronous` above OFF: the super-journal protocol of atomiccommit §5 (`Storage/Pager/PagerSync.cs`, `MultiCommit`): write and sync the super-journal, record its name in each journal and sync, write and sync each database, delete the super-journal (the commit point), retire each journal. In PERSIST mode the reused journal is cut right after the super-journal's name (a reader looks for the name at the end of the file) and retired by truncation, not by zeroing its header (both observed in the reference with `strace`; found by the kill campaign below, which left the two files out of step before). A database in WAL mode, or `synchronous=OFF`, or `journal_mode=MEMORY/OFF`, commits on its own as in SQLite. - Main always starts a write transaction when a statement writes, even if only an attached database changes (one more RESERVED lock than SQLite takes). - **Async driver:** the attached databases' I/O is satisfied inline, as the temp database's is, so a statement that touches one blocks the thread on that file instead of returning *pending*, and a multi-database commit runs inline as a whole. Main's cache misses still suspend. ## Pragmas Schema-qualified forms (`PRAGMA aux.user_version`) answer for that database: `journal_mode`, `synchronous`, `page_size`, `auto_vacuum`, `encoding`, `user_version`, `application_id`, `schema_version`, `freelist_count`, `page_count`, `default_cache_size`, `wal_autocheckpoint`, `wal_checkpoint`, `cache_size`, `locking_mode`, `secure_delete`, `max_page_count` (the last four keep their value on the connection), `integrity_check`, `quick_check` (structure only), `table_info`, `table_xinfo`, `index_list`, `index_info`, `index_xinfo`, `foreign_key_list`, `table_list`. An unknown schema is `unknown database NAME`. `temp.journal_mode` moves among the rollback modes and refuses WAL; `temp.synchronous` stays 0. `ANALYZE` of an attached database or of `temp` is rejected (`unsupported`). `VACUUM schema`, `REINDEX schema.name` and DDL on attached databases work. ## pragma_* table-valued functions `pragma_table_info(arg [, schema])`, `pragma_table_xinfo`, `pragma_index_list`, `pragma_index_info`, `pragma_index_xinfo`, `pragma_foreign_key_list`, `pragma_table_list` return the rows of the pragma, with the hidden columns `arg` and `schema`; `pragma_database_list`, `pragma_collation_list`, `pragma_module_list`, `pragma_pragma_list` and `pragma_compile_options` take no argument. Rows are made from the connection's schema when the scan starts, so a correlated `JOIN pragma_table_info(m.name)` works. No argument (or NULL) gives no rows; an unknown schema is `unknown database 'NAME'`. Known differences from the shell: `collation_list` lacks the shell's `decimal` and `uint`; `table_list` rows are sorted by name where the shell uses hash order; the value pragmas (`pragma_page_count`, `pragma_journal_mode`, ...) have no function form yet. ## Checks `tests/OneLite.Tests/Engine/AttachTests.cs` compares scripts with the shell (results, error text, `.dump` and `integrity_check` of every file). `tools/scripts/pragma_cases.py` and `pragma_diff.py` compare the pragma surface; `cipher_pragma_cases.txt` includes ATTACH with and without a KEY against SQLCipher. `tools/scripts/attach_kill_campaign.sh` kills a writer that commits to two databases in every transaction, and one that switches the journal mode between commits; both files must hold the same transactions after recovery by OneLite and by the pinned library. # Backup, VACUUM INTO and incremental blob I/O Normative notes for the three features. The reference behaviour is the pinned SQLite release; every rule below was checked against it by running the same script on its native library and on this engine (`tests/OneLite.Tests/Engine/BackupTests.cs`, `VacuumIntoTests.cs`, `BlobTests.cs`). ## Online backup (`Backup`) `Backup.Create(destination, destinationName, source, sourceName)` then `Step(n)` / `StepAsync(n)` until `Done`; `Dispose` ends it (sqlite3_backup_init / step / finish). - **Locks.** The destination's write transaction is opened by the first step and held until the last step commits or the backup is disposed. The source is read under a read transaction only for the length of one step (or the connection's own transaction, if it has one), so other connections can read and write it between steps. While the backup is unfinished its destination connection refuses statements on its main database with SQLITE_LOCKED (the reference leaves this undefined). - **Source changes.** - A commit by another connection or process is noticed at the next step (the pager counts changes made by others: the file change counter in rollback modes, the wal-index snapshot in WAL) and the copy restarts at page 1; `Remaining` jumps back up. - A commit by the *source's own connection* is applied to the destination at once: the pages already copied are compared with the source and rewritten where they differ, and the copy carries on. No restart. - **Commit.** Nothing of the destination changes until the final step: pages go into the destination pager's journal/WAL transaction, and the commit is the destination's ordinary commit. A crash at any point leaves the old content or the complete new content (`BackupBlobDurabilityTests`). - **Page 1.** Copied from the source, except that the destination's schema cookie becomes its old cookie + 1 (so other connections reload their schema), and that a WAL-mode destination keeps format-version bytes 2/2. A rollback-mode destination that receives a WAL source's header becomes a WAL file, as in the reference. - **Geometry.** Page size and reserved bytes must match, unless the destination has no pages yet and is not in WAL mode: then it takes the source's. Otherwise the step throws SQLITE_READONLY "attempt to write a readonly database" (the reference's answer for WAL destinations). **Known difference:** the reference also converts a non-empty rollback-mode destination to the source's page size; here that case throws the same SQLITE_READONLY. An empty source leaves the destination as it is. - **Encryption.** Pages are copied decrypted and re-encrypted by the destination's pager: an encrypted source into an encrypted destination keeps the destination's key; into a plaintext destination the reserved-byte mismatch applies (SQLITE_READONLY). - **Results.** `Step` returns `More`, `Done`, `Busy` (retry) or `Locked` (retry); any other failure throws with the reference's code and message, rolls the destination back and ends the backup. `Remaining` and `PageCount` are those of the last step (0 before the first). - **Driver.** `Step` satisfies page I/O inline; `StepAsync` awaits the storage backend per page. Both run the same code. The step observes the cancellation token between pages only; a cancelled step keeps the pages already written. ## VACUUM INTO `VACUUM [schema] INTO 'file'` writes the compacted image of the database to a new file and only reads the source (a read transaction; it works on a read-only connection). - The copy keeps the page size, text encoding, auto-vacuum mode, user_version and application_id of the source; it is a rollback-journal-format file even when the source is WAL (format-version bytes 1/1), and has an empty freelist. An encrypted source gives an encrypted copy under the same key and settings. - Errors, text as in the reference: `cannot VACUUM from within a transaction`, `non-text filename` (NULL, numbers, blobs), `output file already exists` (the target exists and is not empty; an empty file is fine), `unable to open database: NAME` (unwritable path, `file:` URI with `mode=ro`). An empty name or `:memory:` writes nothing. - **Method.** Every table and index is read in key order and written again with full pages; sqlite_schema is written last by ordinary inserts, because its rows carry the new root pages. Pages are handed to the target's pager as soon as their tree has made them, so memory holds one open leaf per tree plus a small per-page table (about 5 bytes per page), plus the values of the row being copied. A single value (one row's overflow chain) is held whole while its row is written. Encrypted databases never touch a temporary file. - The target is written with the journal off; if the statement fails, the half-written target is deleted. It is synced at the end according to `synchronous`. - Page counts match the reference's own `VACUUM INTO` to within a few pages (pages are packed as full as the cells allow; the reference inserts row by row). ## Incremental blob I/O (`Blob`) `Connection.OpenBlob(schema, table, column, rowid, writable)` (sqlite3_blob_open). Reads and writes touch only the pages that hold the requested bytes: the local part of the cell and the overflow chain pages (`BtCursor.ReadPayloadRange` / `WritePayloadRange`, with an overflow hint so sequential access does not rewalk the chain). Writes are made in place through the pager's journal or WAL like any page change. - **Open errors** (all SQLITE_ERROR, text as in the reference): `no such table: S.T`, `no such column: "C"`, `no such rowid: N`, `cannot open value of type integer|real|null`, `cannot open view: V`, `cannot open virtual table: T`, `cannot open table without rowid: T`, `cannot open table with generated columns: T`, and for a writable handle `cannot open indexed column for writing` and `cannot open foreign key column for writing`. The rowid alias column holds NULL in the record and so fails with `type null`. - **Range rules.** A read or write outside the value is SQLITE_ERROR "SQL logic error", checked first. Then an expired handle gives SQLITE_ABORT "query aborted". Then a write on a read-only handle gives SQLITE_READONLY. Size never changes. - **Expiry.** The handle expires when a statement on the same connection changes or deletes its row (an UPDATE of any column of that row, a REPLACE, a DELETE, or a DELETE of the whole table). Other rows of the table can change freely. `Length` is then 0. An expired handle cannot be revived by `Reopen`. A ROLLBACK aborts every open handle with SQLITE_ABORT_ROLLBACK (516) and `Length` keeps its value. A failed `Reopen` leaves the handle expired. - **Locks.** The handle holds a read transaction on the database until it is closed (a read statement kept at its row), and a writable one also the write transaction: other connections cannot write while it is open in rollback modes. DROP TABLE and DROP INDEX on the connection fail with SQLITE_LOCKED "database table is locked" while any handle is open; COMMIT fails with SQLITE_BUSY "cannot commit transaction - SQL statements in progress" while a writable handle is open. - **Autocommit.** A writable handle opened in autocommit mode starts the transaction itself (BEGIN IMMEDIATE) and `Close` commits it; until then `AutoCommit` is false. **Known difference:** in the reference the connection stays in autocommit mode and the handle's own statement holds the write lock. - **Suspend points.** Each page read of a chain walk is a suspend point (`Overflow.Walk`); a write adds `MakeWritable` of the page (`Blob.Write`). The sync methods satisfy them inline, the async twins (`ReadAsync`, `WriteAsync`, and the `Stream` returned by `AsStream`) await the pager. A walk interrupted by a pending read restarts from the hint; a repeated write of the same bytes is idempotent. - Offsets and lengths count bytes as stored: a TEXT value in a UTF-16 database counts two bytes per code unit. # OneLite in the browser Normative notes for the browser storage backends (`RecursiveAI.OneLite.Storage.Vfs.Browser`) and the headless-browser test host. Sources: the File System Standard (fs.spec.whatwg.org: `FileSystemFileHandle`, `FileSystemWritableFileStream`, `FileSystemSyncAccessHandle`, the origin private file system), the Web Locks API (w3.org/TR/web-locks), MDN's pages for both, the .NET documentation for `[JSImport]`/`[JSExport]`, and from sqlite.org `lockingv3`, `atomiccommit`, `wal` and `walformat`. The VFS contract these backends implement is in `vfs.md`. ## What exists | Type | Context | Driver | `Caps` | |---|---|---|---| | `OpfsAsyncVfs` | any (page main thread, worker) | async only | `Async` | | `OpfsSyncVfs` | dedicated worker | sync (async calls complete inline) | `Sync` | | `DatabaseTransfer` | any backend | async | n/a (`.db` import and export) | | `BrowserStorage` | any | n/a | loads the JavaScript module, reports what the context offers | Both file systems store files in the origin's private file system (`navigator.storage.getDirectory()`). A path is relative to its root; `/` separates directories, which are created as needed. Temporary files (`TempDb`, `TempJournal`, `SubJournal`, empty names) live in memory in both. The JavaScript half is `onelite-opfs.js`, a static web asset of the `RecursiveAI.OneLite` package (`_content/RecursiveAI.OneLite/onelite-opfs.js`). It has no dependencies. `BrowserStorage.LoadAsync()` imports it with `JSHost.ImportAsync`; `OpfsAsyncVfs.CreateAsync()` and `OpfsSyncVfs.CreateAsync()` do so themselves. If the app serves the file elsewhere, pass the URL. The interop is the BCL's `System.Runtime.InteropServices.JavaScript`; the browser-only types are marked `[SupportedOSPlatform("browser")]`, so native callers get a compile-time warning, and the NativeAOT build of the core is unchanged. The runtime's `[JSImport]` generator writes pointer-using stubs, which compile under the `AllowUnsafeBlocks` the core already has for `ShmAccessor` and `NativeFile`; no hand-written code in `Browser/` is unsafe. ## The driver rules (vfs.md §2.1) as built * **`OpfsAsyncVfs` is async-only.** `Open`, `Access`, `Delete` and every file call except `Unlock` and `Dispose` throw `VfsException` with `SQLITE_MISUSE` and the message `backend requires the async driver`, before any work starts. Use `Pager.OpenAsync` and the pager's `...Async` methods. `Unlock(None)` and `Dispose` are allowed because releasing a Web Lock only needs a call, not an answer; `Dispose` also starts closing the file in the background, and the next `OpenAsync`, `AccessAsync` or `DeleteAsync` waits for those closes (`DrainAsync()` waits explicitly). `Pager.Dispose` is synchronous, so in WAL mode it cannot run its closing checkpoint on this backend: the `-wal` file stays, which is exactly what a crash would leave. The next open recovers from it and the data is complete. * **`OpfsSyncVfs` is sync, with one asynchronous step.** A `FileSystemSyncAccessHandle` can only be created by an awaited call, so a database is *prepared* first. `PrepareAsync(path)` (called by `OpenAsync` for a main database) opens handles for the database, `path-journal` and `path-wal`. After that every call is a plain synchronous call: `Pager.Open`, the `...Sync` pager methods and `ExecuteSync` all work, and the `ValueTask` flavours return completed tasks. A synchronous `Open` of a path that was not prepared throws MISUSE (`'x.db' was not prepared; await PrepareAsync first`). Closing the last connection to a database closes its handles. The type is usable only in a dedicated worker; `CreateAsync` throws `CANTOPEN` elsewhere. * A file *exists* in the worker backend when it is not empty (an empty database, journal or WAL is the same as none for SQLite), because the three handles are created together. `Delete` of a journal or WAL truncates it and flushes it; `Access` answers from the size. ## Locks SQLite's lock levels (lockingv3 §2.0) are built from three named locks per database, standing for the three byte ranges lockingv3 locks in the file: PENDING, RESERVED and SHARED. Each is taken with `navigator.locks.request(name, { mode, ifAvailable: true })`, which never waits, so it is the VFS's try-lock; waiting is the pager's busy loop. A granted Web Lock is held by parking a promise, and released by resolving it. | Move | Taken | |---|---| | None → Shared | shared PENDING, shared SHARED, release PENDING | | Shared → Reserved | exclusive RESERVED | | → Pending | exclusive PENDING | | Pending → Exclusive | release the holder's own shared SHARED, request it exclusive; if refused, take the shared hold back and stay at Pending | | Exclusive → Shared | exclusive SHARED to shared, release PENDING and RESERVED | A Web Lock cannot be upgraded in place, hence the release in the Exclusive move (lockingv3 §4.0 allows the failed attempt to leave the holder at Pending, which keeps new readers out). Connections in one page are different holders to the lock manager, so they exclude each other exactly as connections in two tabs do. * **`OpfsAsyncVfs`** uses Web Locks for every level, so two contexts of one origin (two tabs, a tab and a worker, two workers) contend per level. Tested: the second context gets BUSY for RESERVED while the first holds it, for SHARED while the first is EXCLUSIVE, and succeeds after the first releases. * **`OpfsSyncVfs`** cannot wait on a Web Lock (the API is asynchronous, and a worker that blocks cannot receive its own result). Its levels are kept in a table in the worker for the connections of that worker. Across contexts the platform's own rule applies: one context holds a file's sync access handle, so a second context's `PrepareAsync` fails with BUSY until the first has closed its last connection. This is whole-file exclusion rather than per-level exclusion, which is the v1 contract (one connection per origin). * **Do not mix the two backends on one file at the same time.** A file with an open sync handle cannot be written through `createWritable`. * The browser releases a context's Web Locks and sync handles when the context goes away, so a killed tab never leaves a stale lock. ## Durability, backend by backend The Standard defines what the APIs do, and says little about the storage underneath. What follows is what OneLite relies on, and what it does not. * **`OpfsAsyncVfs`.** Writes go to a `FileSystemWritableFileStream`. By the Standard the changes are applied to the file atomically when the stream is closed; a stream that is never closed changes nothing. `Sync` closes the stream and so is the commit of everything written since the last `Sync`; the next write opens a new stream. Reads close an open stream first (a read must see the writes before it), so data can reach the file earlier than `Sync` does; the atomic-commit sequence tolerates that, because the pager syncs the journal before it overwrites database pages (atomiccommit §3.7). * Tab killed or crashed: the file is the state at its last completed close. Within one transaction that means the database file shows all or none of the writes made between two `Sync`s, which is at least as strong as the sector-level guarantees SQLite assumes. * Power loss or OS crash: the Standard does not say the close reaches the device. A close that returned is as durable as the browser's file writes are; OneLite cannot ask for more. Do not rely on this backend for power-loss durability. * **`OpfsSyncVfs`.** Writes go to a `FileSystemSyncAccessHandle`, `Sync` is its `flush()`. Written bytes are in the file for readers at once; `flush()` is the platform's request to persist them. After a tab kill, bytes written since the last `flush()` may or may not be there, and possibly torn at page granularity, exactly the model of the fault-injecting VFS (`vfs.md`); the pager's journal and WAL sequences are written for it (`durability.md`). Power loss: same caveat as above. * **Directory syncs** are no-ops on both: the origin private file system has no directory entries to flush apart from the file itself. * **Both:** `synchronous=OFF` and `journal_mode=MEMORY|OFF` weaken this as they do anywhere. A killed tab leaves a hot `-journal` or an un-checkpointed `-wal` that the next open recovers from, in either backend (`pager.hot-journal-recovery`, `pager.wal-left-behind-is-recovered` in the browser suite). Kill-the-tab campaigns are not part of this build; the test host can run them (below). ## Import and export `DatabaseTransfer` moves whole database files: * `ImportAsync(vfs, path, bytes | stream)`: checks the 100-byte header (magic, valid page size, length a whole number of pages for bytes; for a stream the length is checked at the end and a partial file is emptied), takes the exclusive lock (BUSY if any connection has the database open), deletes a stale `-journal` and `-wal` (they would be replayed over the new pages), truncates, writes in 64 KiB chunks, syncs. It is not atomic: import to a new name and open that if an interrupted import must not touch the old file. * `ExportAsync(vfs, path[, stream])`: reads through a pager inside a read transaction, so the result is the committed state: a hot journal is rolled back first and, in WAL mode, committed frames of the log are included. The output is a standalone file. It copies page by page and does not hold a write lock, so writers are not blocked beyond the reader's shared lock. ## The headless test host The browser tests are `tests/OneLite.BrowserTests` (a `Microsoft.NET.Sdk.WebAssembly` app with a small harness; xUnit does not run in a page) and `tools/scripts/browser_tests.py`, which drives headless Chrome for Testing through Playwright's Python driver. Nothing is installed system-wide. ``` tools/scripts/fetch_browser.sh # Chrome for Testing 156.0.8078.80 (SHA-256 pinned) and playwright==1.55.0 into ~/onelite/browser CORPUS="$HOME/onelite/corpus/realworld" tools/scripts/browser_tests.sh [--only SUBSTRING] ``` `browser_tests.sh` publishes the test app, serves it from a local port on 127.0.0.1 (sent with the cross-origin isolation headers; the backends do not need them), and runs: 1. every test the page (main thread) and a dedicated worker list: VFS behaviour (read, write, truncate, size, sync, reopen, try-locks, open flags, delete, driver rules), pager (commit and reopen in each journal mode and WAL, roll back, hot-journal recovery, cache spill, page sizes 512 and 65536), B-tree insert and scan round trips (table, index, overflow chains) in rollback and WAL mode, and the import and export cases; 2. two contexts of one origin contending for locks (page and page, page and worker, worker and second page; two workers for the sync handle); 3. each corpus database imported through each backend, exported (bytes must equal the source) and walked through the B-tree (digest must equal a walk of the source image); 4. databases the browser wrote, exported to the host and checked with the pinned `sqlite3`: `PRAGMA integrity_check` and a `.dump` equal to the dump of the same content built by `sqlite3`. Results go to `results.json` and a per-group table. To run a kill-the-tab campaign later, drive the same page with `page.close()` between `ol.call(...)` steps; the commands (`db.build`, `db.verify`, `lock.*`) are written to be called that way. # FTS5 `CREATE VIRTUAL TABLE t USING fts5(...)` is built into the core (`RecursiveAI.OneLite.Extensions.Fts5`). Its shadow tables (`t_data`, `t_idx`, `t_content`, `t_docsize`, `t_config`) have the layout the reference writes, so a database is read and modified by both engines. Tokenizers (`unicode61`, `ascii`, `porter`, `trigram`) are internal code in `RecursiveAI.OneLite.Internal.Text`, free of engine dependencies. ## What is covered - Table options: `content`, `content_rowid`, `contentless`, `columnsize`, `detail` (`full`, `column`, `none`), `prefix`, `tokenize`, `UNINDEXED` columns, hidden `rank`. - Queries: phrases, `+`, `AND`/`OR`/`NOT`, parentheses, column filters, `^`, prefix `*`, `NEAR(...)`, table-valued form `t('query')`, `col MATCH 'query'`. - Auxiliary functions: `bm25` (k1 1.2, b 0.75), `highlight`, `snippet`, also on a scan with no `MATCH` (bm25 is 0). - Special commands: `rebuild`, `optimize`, `merge`, `automerge`, `usermerge`, `crisismerge`, `pgsz`, `hashsize`, `secure-delete`, `deletemerge`, `insttoken`, `rank`, `integrity-check`, `delete`, `delete-all`. Invalid values fail with the bare `SQL logic error`. - `fts5vocab` (`row`, `col`, `instance`). - Index format: structure record, segments, doclists, position lists, `t_idx` keys, `t_docsize` varints, `t_config` — written and read both ways. ## Behaviour settled by running the pinned shell - **Unicode tables** are Unicode 6.1 (the pinned build): unassigned code points are token characters except U+FFFE/U+FFFF; the marks in the diacritic set (U+0300–0304, 0306–030C, 030F, 0311, 031B, 0323–0328, 032D–032E, 0330–0331) are token characters even with `remove_diacritics 0`. - **Porter** works on UTF-8 bytes: digits and every byte of a multi-byte character are consonants, and nothing is skipped. - **detail=column/none**: `phrase queries are not supported (detail!=full)` for a multi-token phrase or `^`, `NEAR queries are not supported (detail!=full)` for `NEAR` with two or more phrases, `column queries are not supported (detail=none)` for a column filter (raised after the filtered primary parsed) and for `col MATCH` (raised after the whole query parsed). Instances for `bm25`/`highlight`/`snippet` come from tokenizing the row text, for every column including unindexed ones, and only for phrases that took part in the match. `fts5vocab` counts are NULL without positions. - **Empty phrases** (a one-letter word under `trigram`) are dropped from an implicit AND, from `OR`, `NEAR` and the right of `NOT`; an explicit `AND` with one matches nothing. - **Syntax errors** report the token the reference reports: `x (y)` names `x`, `{a b` reports the unknown column before the missing brace, an unterminated string is `unterminated string`, `* a` is `unknown special query: a`, `NEAR` is a word unless a `(` follows. - **Contentless tables**: the `'delete'` command also drops the `t_docsize` row; without `t_docsize` (`columnsize=0`) a scan is `t: table does not support scanning` and `bm25` runs with a column size of -1. - **snippet** (window of `n` tokens, `n` clamped to 1..64): the best anchor is the instance whose window holds the most instances; the window starts at `anchor - (n - span)/2` clamped to the text, except that it starts at the latest *sentence start* before the first match that still reaches it, and at the text start when the first match lies in the first `n` tokens. A sentence start is a token whose preceding gap ends in `.` or `:` followed by white space (`. `, `: `, ` . `; not `.` alone, `?`, `!`, `;`). A phrase that began before the window is not marked. A snippet that reaches the end of the text keeps the text after its last token; one starting at token 0 starts at the text start. Unindexed columns are returned whole. For `snippet(t, -1, ...)` the column is the one whose best anchor scores most, where an instance with no more than `n` tokens after it to the end of its text scores 0; ties keep the first column. ## Known differences (open) - `snippet` when several matches lie near sentence starts and the span of the first match equals the window (a few percent of random differential cases), and the window around trigram phrases of different lengths. - `snippet()` of an UNINDEXED column in a `detail=column` or `detail=none` table: for some rows the reference shell fails with "database disk image is malformed" (SQLITE_CORRUPT) on files that pass its own integrity check; we return the snippet text. Seen twice in 1,450 random cases (seed 21). - `bm25`/`highlight`/`snippet` quirks that are matched on purpose: when the left side of a NOT matches no row at all, the right side keeps its first row's instances (they show in that row if it matches another way); a segment recorded with first and last page 0 and no page 0 is empty; a combining mark continues a token but never starts one; a contentless table without size records reads each indexed column's size as -1. - The NOT quirk below is matched when the left side is empty because its terms are absent, or because the phrase never occurs; in one of 373 random cases (seed 41, a prefix on the right of the NOT, a two-token phrase on the left) the reference does not keep the right side's instances, and `highlight` and `bm25` differ by one marked token or 1e-6. - Where the NOT quirk above applies, `highlight` and `snippet` show the kept instances but `bm25` does not count them (two of 647 random cases, seed 61); in one of 1,450 (seed 21, a phrase that also occurs in an `AND` branch) the reference does count them. - Query nesting: the reference parser stack holds 100 entries and reports "fts5: parser stack overflow". Ours counts one entry for a bare parenthesis and three for one that follows an operator or a column filter, and agrees on plain, `AND`/`OR`/`NOT` and column-filter chains; the exact limit differs by a level or so in chains that mix several pending operators. - `contentless_delete`, `tokendata`, `locale` and `fts5_locale()` are not implemented. UTF-8 and UTF-16 databases both work. - `bm25(t)` inside an aggregate such as `sum(bm25(t))` reports "unable to use function bm25 in the requested context". ## Method `tools/fts/fts_diff.py` generates seeded scripts (random options, corpus rows, updates, deletes, special commands, every query form, auxiliary functions, vocab tables) and compares rows and error text per statement with the shell, then runs the shell's `integrity-check` and `PRAGMA integrity_check` on the file OneLite wrote and OneLite's `integrity-check` on the shell's file. `tests/OneLite.Tests/Engine/Fts5Tests.cs` runs the same comparisons over a fixed corpus. # FTS3 and FTS4 `CREATE VIRTUAL TABLE t USING fts3(...)`, `fts4(...)` and `fts4aux(t)` are built into the core (`RecursiveAI.OneLite.Extensions.Fts3`). The shadow tables (`t_content`, `t_segments`, `t_segdir`, `t_docsize`, `t_stat`) have the layout the reference writes (sqlite.org/fts3.html, "Data Structures"): segment b-trees, doclist and position-list varints, prefix indexes, language id levels. A database is read, written and `integrity-check`ed by both engines. Tokenizers are shared code in `RecursiveAI.OneLite.Internal.Text` (engine-independent): `simple` is the ASCII tokenizer FTS5 also uses, `unicode61` (`remove_diacritics`, `tokenchars`, `separators`) is the FTS5 one, `porter` is `Fts3Tokenizers.cs` (FTS3's stemmer differs from FTS5's in which characters make a token). `icu` is not built into the pinned reference and is not offered. ## What is covered - Options: `content=` (external table, or `''` for contentless), `compress=`/`uncompress=` (SQL functions, called as `f(?)`), `languageid=`, `matchinfo=fts3`, `notindexed=`, `order=desc`, `prefix=`, `tokenize=`. Errors for bad options carry the reference's text. - Queries: the standard syntax of the pinned build (no parentheses; `OR`, implicit `AND`, `-` for NOT, `NEAR`, `NEAR/n`, `"phrase"`, prefix `*`, `col:term`, FTS4 `^` anchor), `docid`/`rowid`, `col MATCH`, `lid = n`. - Auxiliary functions: `offsets`, `snippet` (all arities), `matchinfo` (`p c n a l s x y b`), `optimize(t)`. - Special commands: `optimize`, `rebuild`, `integrity-check`, `merge=X,Y`, `automerge=N`. - Writes: insert, update (including `docid`), delete; the oracle's `integrity-check` and `PRAGMA integrity_check` pass on our files. - `fts4aux(t)` and `fts4aux(db, t)` (the two-argument form only for a table created in `temp`, as in the reference). ## Behaviour settled by running the pinned shell - **Operators** `OR` and `NEAR` are recognised only right after white space that follows the previous word or phrase, and only when followed by white space, `(`, `)`, a quote or the end. So `alpha, OR beta`, `(a) OR b`, `a OR,b` and `naive NEAR x` under `separators=e` are all words. - **Expression size**: at most 4095 phrases in a query and 13 operands in a `NEAR` chain; beyond that `FTS expression tree is too large (maximum depth 12)`. Query text is lexed in linear time. - **`^`** anchors the word directly after it (`^ word` does not); before a quote it does nothing; a `^` right before the first word inside the quotes anchors the phrase (`"^one two"`). A `-` before a `^` is malformed. - **`col:"phrase"`** with a space after the colon ignores the column (as the predecessor test showed); see the open differences for the form without a space. - **matchinfo `s`** counts runs of consecutive phrases (a phrase that did not match breaks the run). - **snippet** (token window `N`, default 15 though the page says -15; `M` fragments of `ceil(N/M)` tokens, `M` = 1..4): window start is `floor((first + last + 1 - size) / 2)` clamped to the column; fragments print in the order they were chosen (most new phrases, then most hits, first), not in text order; text after the last token is kept when the window reaches the end. - **`fts4aux`**: rows are one `*` row per term and one per column with hits; a `languageid` constraint is taken by the reference as an absolute segment-directory index, so with prefix indexes or other languages around `languageid = 1` reads the first prefix index (reproduced); a term that occurs in a column after the second makes the scan fail with `database disk image is malformed` after the rows before it (reproduced, including that term bounds narrow the scan and a lone `ORDER BY term` streams). - **UPDATE** with `docid` set to its current value succeeds (an engine fix made with this module). ## Differences from the reference (documented, not planned) - **Not resumable.** The module reads and writes its shadow tables through nested statements on the sync driver. On a backend that is async-only a cold read fails with `SQLITE_MISUSE` ("backend requires the async driver") instead of suspending; it never hangs or corrupts. Over `BlockingOk` backends and the memory and file backends it works under both drivers. - **Segment layout.** Each statement that changes rows writes one level-0 segment (the reference writes one per transaction); a level is merged when it holds 16 segments. `merge=X,Y` and `automerge=N` merge fully instead of a little at a time, so segment counts and what `optimize()` merges differ. File validity is unaffected: `integrity-check` passes in both directions. - **`col:"phrase"` written without a space** returns no rows in the reference (the form with a space ignores the column); we ignore the column in both. - **`matchinfo(t, 'x')`** totals (hits and documents over all rows) of a query with several phrases: the reference does not read a later phrase's doclist to its end, so the totals depend on how the file's segments are laid out and, for a `NEAR` group ANDed with other terms, on the query. Ours are always the full totals. They agree in most cases (no difference in 400 random cases, one in 301 queries over a 1,500-document table) and the differential and interop runs compare `x` for one-phrase queries only. - **`fts4aux` over `order=desc` tables** counts entries per segment without merging a document's older versions (the reference's counts rise after a delete until the next merge); stale entries of an external-content table whose content changed before the delete are counted by the reference and not by us. `GROUP BY term` over a scan that fails does not stream the rows before the failure. - **Contentless FTS4**: the reference fails `integrity-check` and `PRAGMA integrity_check` on its own contentless tables; ours answer ok. - **pragma `table_xinfo`** on a virtual table is a separate engine gap. ## Method `tools/fts/fts3_diff.py` generates seeded scripts (options, rows, updates, deletes, special commands, every query form, `fts4aux`) and compares rows and error text per statement with the shell, then runs the shell's `integrity-check` and `PRAGMA integrity_check` on our file and ours on the shell's file. `tools/fts/fts3_interop.py` builds the same tables in both engines (eight option combinations, four page sizes, UTF-8 and UTF-16, thousands of documents), checks all four engine/file pairings and then lets each engine modify the other's file. `tests/OneLite.Tests/Engine/Fts3Tests.cs` runs the comparisons over a fixed corpus. `fuzz/OneLite.Fuzz/Fts3Target.cs` (`fts3-query`, `fts3-data`) fuzzes the query parser and hostile `t_segments`/`t_segdir`/`t_content`/`t_docsize`/`t_stat` contents. The kill campaign runs the workload with `WRITER_EXTRA="fts3=1"`. # R*Tree and dbstat Behaviour of `rtree`, `rtree_i32` and `dbstat` as OneLite implements it, with what was settled by observing the pinned shell. Results are compared with the shell by `tools/OneLite.RTreeDiff` (seeded random scripts, statement by statement, then the shell's own checks on the file OneLite wrote and OneLite's checks on the file the shell wrote) and by `tests/OneLite.Tests/Engine/RTreeTests.cs`. ## R*Tree - **Shadow tables** are created in the order `%_rowid`, `%_node`, `%_parent` after the virtual table's own schema row (`rootpage` 0): `%_rowid(rowid INTEGER PRIMARY KEY,nodeno,a0,a1,...)` (one `aN` per auxiliary column), `%_node(nodeno INTEGER PRIMARY KEY,data)`, `%_parent(nodeno INTEGER PRIMARY KEY,parentnode)`. `%_node` starts with node 1, the root, as `pageSize - 64` zero bytes. - **Node blob**: bytes 0-1 tree depth (meaningful in the root), bytes 2-3 cell count, then cells of `8 + 8 * dims` bytes: big-endian int64 rowid (child node number above the leaves) and per dimension `min`, `max` as big-endian float32 (`rtree`) or int32 (`rtree_i32`). Observed: a node holds at most 51 cells whatever its size allows (the shell reports a node with more as corrupt); OneLite splits at 52. - **Columns**: the first column is an INTEGER rowid (NULL assigns `max(rowid)+1`; other values go through the integer conversion, so `'7'` is 7 and `'x'` is 0); `rowid` named in an INSERT or UPDATE is ignored in favour of the first column. `rtree` coordinates are REAL on the way out, `rtree_i32` INTEGER. Column names are the first token of each argument; `+name` marks an auxiliary column and must come last. - **Coordinates in**: `rtree_i32` takes the low 32 bits of the integer conversion (observed: `1e300` and `9223372036854775807` both fail the `min <= max` check against a normal minimum). `rtree` converts to REAL; a value that is not exactly a float32 is moved outward by a relative 2^-23 (lower bounds down, upper bounds up) and rounded to the nearest float32 (observed against the shell on 0.1, 3.7, 559.936, 592.297 and 16777217; this is the "0.000012%" of the documentation). - **Errors** (messages as the shell): `Too few columns for an rtree table`, `Too many columns for an rtree table` (more than 11 coordinate columns or 100 columns), `Wrong number of columns for an rtree table` (even number of coordinate columns), `Auxiliary rtree columns must be last`, `rtree constraint failed: T.(minCol<=maxCol)` (SQLITE_CONSTRAINT), `UNIQUE constraint failed: T.id` (message compared; the extended code is OneLite's choice, 1555, and has not been compared). - **MATCH** with a geometry callback is not supported: no `rtree_geometry` or query callbacks are registered, and `x MATCH y` is not usable (the shell fails it with `SQL logic error`). - **Scans**: constraints on coordinate columns (`=`, `<`, `<=`, `>`, `>=`) prune subtrees; an equality on the first column goes through `%_rowid`. The engine tests every term again on the returned rows (observed: `a = 0.1` finds nothing in a table holding the rounded value, as in the shell), so pruning only needs to keep every row that could match. Row order is the tree order and is not promised. - **Writes**: the algorithm of Beckmann et al.: ChooseSubtree by least overlap enlargement above the leaves (the 32 best by area enlargement when there are more) and least area enlargement elsewhere; one forced reinsertion of the 30% of entries farthest from the node centre per level and insertion (closest first); otherwise the margin-sum axis and least-overlap distribution split with a minimum fill of 40%. A delete condenses as Guttman's: nodes under a third of capacity are dissolved and their entries reinserted, and a root with one child is lifted. The root stays node 1. **The shape of the tree, and so the shadow tables' bytes, differ from the shell's for the same SQL**; both are valid and answer every query alike, so `.dump` of two files built by the two engines matches for the table's content but not for `%_node`. - **Suspension**: a change is planned in memory (reads of nodes, parent links and rowid rows that miss the cache throw an internal exception after starting the read, `Update` returns Pending, and the next call plans again from the start with the finished reads remembered), then written with one shadow-table write per step, each repeated with the same arguments after Pending. Scans keep their position in a stack of nodes and cell indexes, and load a child before moving past its cell. - **Functions**: `rtreecheck([schema,] table)` returns `ok` or one line per problem (boxes ordered and inside the parent's, leaf cells and `%_rowid`, child cells and `%_parent`, row counts); the wording of problem lines is OneLite's own. `rtreedepth(blob)` and `rtreenode(ndim, blob)` as in the shell (`%g` coordinates). - **Not done**: reading and writing at once (the shell fails the second with SQLITE_LOCKED; OneLite plans from a drained scan, so its UPDATE and DELETE do not meet that), geometry callbacks. ## dbstat - Columns `name, path, pageno, pagetype, ncell, payload, unused, mx_payload, pgoffset, pgsize` and hidden `schema, aggregate`; eponymous, with `dbstat('schema', aggregate)` arguments; `CREATE VIRTUAL TABLE x USING dbstat(schema)` sets the default schema. - B-trees in `sqlite_schema` row order, starting with the schema table itself (named `sqlite_schema`). Rows of a B-tree: a page, then for each cell its overflow chain (`path` `/001/000+000002`), then (for interior pages) the child; the right-most child last. The path of a child is the parent's path plus the 3-digit hexadecimal cell index and `/`. - `payload` is the bytes of cell payload held on the page (the local part only), `mx_payload` the largest total payload length of a cell on the page, `unused` the gap before the cell content area, the freeblocks and the fragmented bytes. An overflow page reports the payload bytes it holds and `usable - 4 - payload` unused. Aggregate mode sums these per B-tree; `pageno` becomes the page count, `pgsize` pages times page size. - Observed quirk reproduced: an overflow page's `pgoffset` is the offset of the page listed just before it. - A scan reads all pages of the schema when it starts; unlike the R*Tree it is not suspendable (it waits on I/O inside `Filter`). # The `vec0` table and the distance kernels > **Not interchangeable with sqlite-vec.** `vec0` follows sqlite-vec's documented SQL API, but stores vectors in OneLite's own shadow-table layout. A `vec0` table written by OneLite can't be used by sqlite-vec, and one written by sqlite-vec can't be used by OneLite. Without OneLite, `sqlite3` reports "no such module: vec0" for the table and reads every other table normally. `vec0` is an opt-in virtual table for exact k-nearest-neighbour search. Its SQL surface follows sqlite-vec's documented API (`vec0`, `vec_*` functions, `vec_each`); its storage is OneLite's own. A database that holds a `vec0` table is a standard SQLite file. A `sqlite3` without the module passes `PRAGMA integrity_check`, uses every other table, and fails on the vec table itself with `no such module: vec0` and nothing else. ## SQL surface - **Table:** `CREATE VIRTUAL TABLE v USING vec0(id integer primary key, e float[768] distance_metric=cosine, tag text partition key, year integer, +note text, chunk_size=256)`. - Vector columns: `float[N]`, `int8[N]`, `bit[N]` (`bit` N a multiple of 8). Metrics: `l2` (default), `cosine`, `l1` (int8 and float), `hamming` (bit). - Primary key: integer or text, optional. Without one, the rowid is the key. - Metadata columns (`integer`, `float`, `text`, `boolean`) can be filtered in a KNN query; `partition key` columns split the index; `+` columns are stored apart and can only be read. - `chunk_size` is a multiple of 8 from 8 to 4096; default: about 32 KiB of the widest vector column per chunk, rounded to a multiple of 8 and clamped to 8..1024. - **Query:** `WHERE e MATCH :query AND k = 10`, or `LIMIT 10` instead of `k`. The result column `distance` is the metric. Constraints on the primary key (`=`, `IN`, ranges) and metadata (`=`, `<`, `<=`, `>`, `>=`, `!=`, `BETWEEN`, `IN`) narrow the candidates. Without `MATCH` the table is a plain scan. - **Writes:** `INSERT`, `UPDATE` (vector, metadata and auxiliary columns), `DELETE`, `INSERT ... SELECT`. - **Functions:** `vec_f32`, `vec_int8`, `vec_bit`, `vec_length`, `vec_type`, `vec_to_json`, `vec_add`, `vec_sub`, `vec_normalize`, `vec_slice`, `vec_quantize_binary`, `vec_quantize_int8`, `vec_distance_l2|l1|cosine|hamming`, `vec_version`, `vec_debug`, and the table function `vec_each`. A vector value is a blob with a subtype; a blob with no subtype is read as float32. - **Errors:** texts follow the reference extension's (for instance `Dimension mismatch for inserted vector for the "e" column. Expected 3 but received 2.`, `UPDATEs on vec0 primary key values are not allowed.`). Checked against the extension's binary by `tests/OneLite.Tests/Vec` and `testdata/vec/scalar_golden.jsonl`. ## Differences from the reference extension - JSON text for a vector is parsed strictly; the lenient forms the reference accepts are rejected with an error. - A `MATCH` operand may be a scalar subquery, a bound parameter or a column of an earlier FROM item, as in the reference. - An `UPDATE` that leaves an int8 or bit vector alone reads it back as an untyped blob; a blob of exactly the column's size is taken as the column's type. - Updating a partition key column is not supported (delete and insert again). - The shadow tables are OneLite's, so a file written by the reference extension is not read, and ours is not readable by it. - Text primary keys live in a key table (slot to text to rowid) that is probed by the key text, not a hash of the reference's. ## Storage layout All rows are ordinary B-tree rows in shadow tables named `_`; they take part in the transaction, journal, WAL and encryption like any table. | Table | Columns | Meaning | |---|---|---| | `_chunks` | `chunk_id integer primary key, size, partitionNN..., validity blob, rowids blob` | One row per chunk of `size` slots. `validity` is a bitmap (bit i of byte i/8: slot i is live); `rowids` is `size` little-endian int64 rowids. | | `_rowids` | `rowid integer primary key, id, chunk_id, chunk_offset, mdNN...` | Where each vector lives, plus its metadata values. | | `_vector_chunksNN` | `rowid integer primary key (= chunk_id), vectors blob` | `size * dimension * element bytes`, slot after slot; float32 little endian, int8, or packed bits. One table per vector column. | | `_auxiliary` | `rowid, value00...` | The `+` columns. | | `_ids` | `slot integer primary key, id text, vrowid` | Only for text primary keys. | Chunk fill policy: an insert goes to the newest chunk of its partition if it has a free slot, otherwise to the first chunk with a free slot in that partition, otherwise to a new chunk. A delete clears the validity bit; a chunk with no live slot is removed. A chunk row larger than a page spans overflow pages, which is why every shadow-table write is Pending-safe: a `Put` that returns Pending is repeated alone, because moving the cursor between resumes would abandon the half-done replace. ## Distance kernels `RecursiveAI.OneLite.Internal.Simd.VectorKernels` has no engine dependencies. float32 vectors accumulate in `double` over 16 fixed partial sums, so a distance is bit-identical on the scalar, `Vector128`, `Vector256` and `Vector512` paths and on x64 and arm64. The path is chosen once from `IsSupported` (`DOTNET_EnableAVX512F=0` / `DOTNET_EnableAVX2=0` force narrower ones). int8 and hamming results are exact integers. A KNN scan allocates nothing per vector or per chunk; ties break by rowid. ## Verified by `VectorKernelTests` (lane widths agree), `VecScalarTests` (155 golden cases against the reference binary), `VecKnnTests` (random tables against a brute-force double-precision scan, with deletes, updates, partitions, metadata filters and reopen), `VecInteropTests` (shell round trip, 512 to 65536 byte pages, rollback and WAL), `VecAllocationTests`, and the kill campaign `WRITER_EXTRA="vec=1" tools/scripts/engine_kill_campaign.sh`, which judges the shadow tables with plain SQL. # Virtual tables Normative notes for the virtual-table interface (`RecursiveAI.OneLite.VirtualTables`), built on SQLite's documented virtual-table model (`vtab`, `c3ref/index_info`, `c3ref/c_index_constraint_eq`). Results follow the pinned SQLite release; plans are OneLite's own. ## Two kinds of module | | user module | built-in module | |---|---|---| | contract | `IVirtualTableModule` / `IVirtualTable` / `IVirtualTableCursor`, synchronous | `BuiltinModule` / `VTab` / `VCursor`, resumable | | registered | `Connection.RegisterModule(name, module)` | static table `VirtualModules` (`rtree`, `rtree_i32`, `dbstat`) | | state | whatever the module keeps; it may run SQL on `VirtualTableContext.Connection` (sync driver) | shadow tables in the database file, read and written through `ShadowTree` | | transactions | `ITransactionalVirtualTable` hooks | none needed: the pager gives atomicity, rollback and savepoints | | creation | `Create` runs inside `CREATE VIRTUAL TABLE` (`VtCreate`) | the compiler emits the shadow tables from `BuiltinModule.ShadowTables` as ordinary DDL in the same program | Built-in modules follow the suspend-point rules of the engine: every method that can touch a page returns `ModStatus.Ok` or `ModStatus.Pending`; on `Pending` the VM does not move `pc` and later calls the same method with the same arguments, so a module keeps progress in fields, never in locals. Errors are thrown as `OneLiteException`. ## Life of a table - `CREATE VIRTUAL TABLE [IF NOT EXISTS] [schema.]name USING module(arg, ...)`. Text of the statement is stored in `sqlite_schema` (type `table`, `rootpage` 0) first, then the module's shadow tables follow in module order, each a normal `table` row. Errors: `no such module: X`; `table N already exists`. - A catalog entry becomes a connected `VTab` lazily (first name resolution). `Connect` must call `DeclareTable("CREATE TABLE x(...)")` once; a column whose declared type ends in `HIDDEN` is hidden (not in `*`, not in the default `INSERT` column list, settable as a table-function argument or by an equality). Not declaring fails with `vtable constructor did not declare schema: N`. - The instance is dropped when the connection's schema is invalidated (DDL, schema cookie change, rollback), except while it still owes the current transaction a Commit or Rollback. - `DROP TABLE` calls `Destroy` on the table, deletes the table's own row and (built-in) its shadow tables' rows and frees their pages. - The built-in table-valued functions `generate_series`, `json_each`, `json_tree`, `jsonb_each` and `jsonb_tree` are eponymous-only modules (`Engine/VirtualTables/FunctionModules.cs`) on this interface: their arguments and `WHERE json = x` style equalities on the hidden columns reach `Filter` through `BestIndex` (`IndexNumber` bit k = hidden column k), and `CREATE VIRTUAL TABLE ... USING generate_series` fails with `no such module`. A user module registered with `IsEponymous` works the same way: `FROM f(1, 2)` fills the first hidden columns. - An *eponymous* module (`IsEponymous` / `BuiltinModule.Eponymous`, e.g. `dbstat`) can be named in FROM without `CREATE VIRTUAL TABLE`, with its hidden columns as table-function arguments. ## Planning: `BestIndex` For each FROM item the planner collects the WHERE (and, for inner joins, ON) terms of the form `column OP expr`, with the column on the left (a term written `5 < x` is offered as `x > 5`), and calls `BestIndex` once at the item's place in the loop nest. - `Constraints[i]`: `Column` (declared position; -1 for `rowid`), `Op` (`ConstraintOp`, the documented numeric codes: EQ 2, GT 4, LE 8, LT 16, GE 32, MATCH 64, LIKE 65, GLOB 66, REGEXP 67, NE 68, ISNOT 69, ISNOTNULL 70, ISNULL 71, IS 72), `Usable`: true when `expr` reads only constants, bound parameters, columns of outer queries, subqueries whose correlated columns come from items placed before this one, and FROM items placed before this one (`WHERE v MATCH (SELECT q FROM t)`, `ON r.id = t.rid`). `BETWEEN` (as `>=` and `<=`), `IN` lists and `LIMIT` are offered to modules that accept extended constraints (`vec0` does). - Arguments of a table-valued call (`FROM t(a, b)`) are offered as equalities on the first hidden columns, always usable. - Outputs: `ConstraintUsage[i].ArgvIndex` (0 = unused; else 1-based, unique, no gaps, usable constraints only) and `Omit` (the engine does not test the term again; otherwise it still tests it on every returned row, so a plan can only change speed, never the answer, unless `Omit` is set); `IndexNumber`, `IndexString`, `EstimatedCost` (default 5e98), `EstimatedRows` (25), `ScanIsUnique`. A malformed answer fails the statement with `xBestIndex malfunction`. Throwing `ResultCode.Constraint` fails with `no query solution`. - `OrderBy` / `OrderByConsumed`: for a SELECT over one virtual table (no join, grouping, aggregate, window or DISTINCT), the ORDER BY terms are offered when every one is a plain column of the table (or `rowid`) without COLLATE or NULLS FIRST/LAST. If the module sets `OrderByConsumed` no sorter runs and the module's order is the result order, ties included; otherwise the engine sorts as usual. The planner asks twice (once to decide about the sorter, once when planning the loop) and a module must give the same answer; a different one fails with `xBestIndex malfunction`. The built-in modules (R*Tree, dbstat, vec) are offered the order and do not consume it: the pinned shell sorts their results too (checked for dbstat). - **Join order.** The planner asks a virtual table in the nest for its plan with what the items before it make usable. A plan that takes an argument ranks like an indexed equality (50), one that returns a single row (`ScanIsUnique` or `EstimatedRows <= 1`) like a rowid lookup (100), and a module that throws (a required constraint is not usable yet) is placed later. Table-valued calls and modules with hidden columns wait for the items their arguments read. ## Scanning `VtFilter` (opens the cursor on first use, passes `IndexNumber`, `IndexString` and the arguments, in `ArgvIndex` order, as `Mem` values exactly as evaluated: no affinity applied), then `Eof` is checked; `VNext` calls `Next` then checks `Eof`; `VColumn`/`VRowid` read the current row. A `Column` call for a hidden column reads what the module returns. Cursors are disposed when the statement ends or is reset. ## Writing `INSERT`, `UPDATE` and `DELETE` on a table whose module is writable (`IWritableVirtualTable`; built-in: `IsWritable`) read the rows they change first (a drained scan, as for views), then call `Update` once per row with the documented `xUpdate` argument layout: `[oldRowid, newRowid, col0, col1, ...]` (all columns, hidden included), a delete passes `[oldRowid]`; an insert has `oldRowid` NULL and `newRowid` NULL unless `rowid` was named. `changes()` counts each call; an insert sets `last_insert_rowid()` to the returned rowid. A table that is not writable fails with `table N may not be modified`. `RETURNING`, upserts and `UPDATE ... FROM` on a virtual table are unsupported. ## Transactions and savepoints (user modules) A module that implements `ITransactionalVirtualTable` gets `Begin` before its first write in a transaction, then at commit `Sync` (an exception rolls the transaction back) and `Commit`, or on rollback `Rollback`. `ISavepointVirtualTable` also gets `Savepoint(n)`, `Release(n)` and `RollbackTo(n)` with `n` the 1-based depth of the SQL savepoint. A failing statement inside an explicit transaction does not call `RollbackTo` (the statement's own undo is the module's concern). Built-in modules need none of this: their pages are in the pager. ## Shadow tables Shadow tables are ordinary tables: queryable and, as in the reference, writable by SQL (which can corrupt the module's structure). `ShadowTree` is the built-in modules' access to one: a rowid B-tree cursor on `BTree.Table` of the table's current root (re-resolved from the catalog at the start of every scan or update, because auto-vacuum can move roots). ## R*Tree (`Extensions/RTree`) `rtree`: coordinates are float32 (lower bounds rounded down, upper bounds rounded up to the next float32), the first column an INTEGER rowid; `rtree_i32`: int32 coordinates. 1 to 5 dimensions (3 to 11 columns), then `+aux` columns (stored in `_rowid` columns `a0`, `a1`, ...; at most 100 columns in all). Shadow tables, created in this order: `%_rowid(rowid INTEGER PRIMARY KEY,nodeno,a0,...)`, `%_node(nodeno INTEGER PRIMARY KEY,data)`, `%_parent(nodeno INTEGER PRIMARY KEY,parentnode)`; `%_node` starts with node 1 (the root) zero-filled. A node's `data` is `pageSize - 64` bytes: bytes 0-1 the tree depth (root only, big-endian), bytes 2-3 the cell count, then cells of `8 + 4 * 2 * dims` bytes: rowid (or child node number) big-endian int64, then `min,max` per dimension as big-endian float32 or int32. See `docs/rtree.md` for the algorithm and the observed behaviour. ## dbstat (`Engine/VirtualTables/DbStat.cs`) Eponymous `dbstat` with the documented columns `name, path, pageno, pagetype, ncell, payload, unused, mx_payload, pgoffset, pgsize` and hidden `schema`, `aggregate`; results equal the pinned shell's. See `docs/rtree.md` ("dbstat"). # Encryption: the SQLCipher-compatible page codec Status: the page codec, key derivation and a whole-file transform are verified against files written by SQLCipher, and the pager uses them: a database opened with a key is encrypted in the database file, the rollback journal and the WAL, and can be rekeyed online (see [In the pager](#in-the-pager)). Keys can be given through `DatabaseOptions` / `PagerOptions`, through SQL (`PRAGMA key`, `hexkey`, `rekey`, `hexrekey` and the `cipher_*` compatibility pragmas), or through `Password=` in `RecursiveAI.OneLite.Data`'s connection string. The format is SQLCipher's, from Zetetic's published design and API documentation (the "SQLCipher Design" and "SQLCipher API" pages). The pages leave several byte-level facts unsaid; those were settled by writing databases with the reference shell (community editions 4.5.6 and 4.20.0) and inspecting the bytes, and are marked **observed** below. The primitives are FIPS 197 (AES), NIST SP 800-38A (CBC), RFC 2104 (HMAC) and RFC 8018 (PBKDF2), all from the .NET base class library (`Aes`, `IncrementalHash` HMAC, `Rfc2898DeriveBytes.Pbkdf2`, `RandomNumberGenerator`, `CryptographicOperations`). Nothing is hand-rolled. ## Page layout For page size `P` and reserve `R` (`R = 16 + hmacSize`, rounded up to a multiple of 16: 80 for SHA-512, 48 for SHA-256 and SHA-1), a stored page is: | Bytes | Content | |---|---| | `[0, P-R)` | AES-256-CBC ciphertext, no padding | | `[P-R, P-R+16)` | the IV, fresh from `RandomNumberGenerator` on every write | | `[P-R+16, P-R+16+hmacSize)` | HMAC of `ciphertext ‖ IV ‖ pageNumber` (page number: 4 bytes, little-endian) | | rest | zero padding, not authenticated | - The plaintext page the caller hands over has `P` bytes; its last `R` bytes are not stored, and `Decode` returns them zeroed. - The database header's reserved-bytes field (fileformat2 §1.3.8, offset 20) must be **at least** `R`, so no cell reaches the IV and MAC. **Observed:** SQLCipher writes 80 there for every digest, including SHA-1 (where `R` is 48), and opens files whose header says 48 or 80. - **Page 1** holds the 16-byte salt where the plaintext has `"SQLite format 3\0"`. Its ciphertext and MAC cover bytes `[16, …)`, so the salt is not authenticated (Zetetic's forum notes this; it is the reference behaviour). Decoding restores the magic string. - **Plaintext header** (`cipher_plaintext_header_size = N`, N a multiple of 16, 16 ≤ N < P-R): the first `N` bytes of page 1 are stored as they are, the ciphertext and MAC start at `N`, and the salt is not in the file: the application keeps it (`PRAGMA cipher_salt`) or supplies it inside a 96-hex raw key. **Observed:** the MAC starts at `N`. - **All-zero pages** fail their MAC like any other damaged page. **Observed** (`tools/scripts/cipher_zero_probe.py`, SQLCipher 4.5.6 and 4.20.0): with a page of a small database overwritten by zeros or by random bytes, the shell's `cipher_integrity_check` reports "HMAC verification failed for page N" for both, a zeroed free-list trunk or table root is "unable to get the page" or malformed exactly as with random bytes, and `VACUUM` fails the same way. There is no zero-page exemption. A page past the end of the file is not a page of the file: the pager reads it as zeros without calling the codec, which is what SQLite does for a short file. - The MAC is checked first, in constant time (`CryptographicOperations.FixedTimeEquals`), before anything is decrypted. The page number is part of the MAC, so pages cannot be swapped. ## Keys - **Passphrase:** its UTF-8 bytes go through PBKDF2 (`kdf_iter` iterations, the KDF digest, the file's 16-byte salt) to a 32-byte AES key. - **Raw key** `x'<64 hex>'`: the 32 bytes are the AES key; no PBKDF2 step. The salt is read from the file (or generated for a new file). - **Raw key with salt** `x'<96 hex>'`: the first 32 bytes are the key, the last 16 the salt. - **MAC key:** PBKDF2 of the AES key, with salt = file salt with every byte XORed with `0x3a`, 2 iterations, 32 bytes. **Observed:** the digest of this step is the *KDF* digest (`cipher_kdf_algorithm`), not the HMAC digest; the two only differ when they are set apart, and a file with KDF SHA-256 and HMAC SHA-1 settles it. - Text parsing follows `PRAGMA key`: `x'` + 64 or 96 hex digits + `'` is raw; anything else, including malformed hex, is a passphrase. ## Parameters Not stored in the file, so the opener must know them (as with SQLCipher's pragmas). | Setting | v4 default (`cipher_compatibility = 4`) | v3 (`= 3`) | Range | |---|---|---|---| | page size | 4096 | 1024 | power of two, 512 to 65536 | | KDF iterations | 256,000 | 64,000 | ≥ 1 | | KDF digest | SHA-512 | SHA-1 | SHA-1, SHA-256, SHA-512 | | HMAC digest | SHA-512 | SHA-1 | same | | plaintext header | 0 | 0 | 0 or multiple of 16 | SQLCipher 1 and 2 formats (no HMAC, 4,000 iterations) are not supported. A 512-byte page cannot hold the 480 usable bytes SQLite needs once 80 bytes are reserved, so the reference (and SQLite) raise such a database to 1024-byte pages; the codec itself accepts 512. ## Errors - A wrong key, a wrong setting or a file that is not encrypted fails page 1: `SQLITE_NOTADB`, "file is not a database" (identical to the reference). - With a plaintext header the file still parses, and the reference reports `SQLITE_CORRUPT` "database disk image is malformed" (4.20.0; 4.5.6 says "SQL logic error"). We follow 4.20.0. - A later page whose MAC fails: `SQLITE_CORRUPT`, "database disk image is malformed", with the page number in `Detail`. **Observed** as the reference's behaviour. - Wrong buffer sizes or page 0 are programmer errors (`ArgumentException`), not file errors. - The browser build has no BCL AES: `PageCodec.IsSupported` is false there and creating a codec throws `SQLITE_ERROR` "encryption is not supported in the browser build". ## Key handling and its limit `CipherKey` and `PageCodec` keep key bytes (passphrase or raw key, the AES key, the MAC key) in arrays on the pinned object heap and zero them on `Dispose`. The `Aes` object and the HMAC object receive their own copy of the key inside the runtime; disposing them is all we can do. A caller's `string` passphrase, the runtime's copies before pinning and any swap or core dump are outside this guarantee: managed memory cannot promise that no copy of a secret exists. ## In the pager Code: `Storage/Pager/Pager.Crypto.cs`, plus the seam sites listed below. Without a key none of it runs: each seam is one `_cipher is null` branch. **Options** (`PagerOptions`, also `StorageEngine` and, read-only, `DatabaseOptions.Key` / `Cipher`): | Option | Meaning | |---|---| | `Key` | a `CipherKey`: passphrase, raw key `x'64hex'`, raw key with salt `x'96hex'` (`CipherKey.Parse` reads the `PRAGMA key` text). The pager keeps its own pinned copy; the caller may dispose theirs. | | `KeyProvider` | a callback that returns the key the first time it is needed (once per pager). The pager owns the key it returns. Set `Key` or `KeyProvider`, not both. | | `Cipher` | `CipherSettings`: page size, KDF iterations, KDF and HMAC digests, plaintext header size. Default `CipherSettings.V4`; `ForCompatibility(3)` is `cipher_compatibility = 3`. These are not stored in the file. | | `CipherSalt` | the 16-byte salt of a database with a plaintext header (the file does not carry it); `Pager.CipherSalt` gives it for a new one. | With a key, `PageSize` and `ReservedBytes` of the options are ignored: the page size is the cipher's and a new file's header reserve is 80 (what SQLCipher writes for every digest). A page size below 1024 is refused (`SQLITE_MISUSE`) because 480 usable bytes must remain. `TrySetPageSize` returns false. The browser build reports `SQLITE_ERROR` "encryption is not supported in the browser build" at open. **Where the codec runs.** The page cache holds plaintext; the stored form exists only in scratch buffers. | Site | Action | |---|---| | read a page from the database file or a WAL frame (`GetCore`) | read into a scratch page, `Decode` into the frame; a short read is zero-padded and then fails its MAC | | first read of page 1 (`BeginRead`) | read the whole stored page 1; the codec is made from its first 16 bytes (the salt) and the key, so the first `Decode` proves the key; the header is read from the decoded page | | spill and commit write of the database file | `Encode` into a scratch page (spill) or a pool of pages (a gather write of up to 64 pages) | | rollback journal record | the record holds the encoded page and its checksum runs over those stored bytes | | WAL frame | the frame holds the encoded page and the WAL checksums run over those stored bytes | | hot-journal playback, WAL recovery, checkpoint | none: they copy and checksum stored bytes, so recovery needs no key | | `journal_mode = MEMORY` rollback | the in-memory original is encoded when it is written back | **Journal and WAL, observed** (`tools/scripts/cipher_probe.py`, 4.5.6): a journal left hot by a killed shell has the usual header (sector 512) and records of `page number ‖ P stored bytes ‖ checksum`; the checksum matches the fileformat2 algorithm over the stored bytes and the record is not the plaintext. A WAL left by a killed shell has an ordinary header (checksum valid) and frames whose cumulative checksum matches over the stored bytes. The journal header, the super-journal trailer and the frame headers stay plaintext. A journal record carries a fresh IV, so it differs from the database file's copy of the same page. **New and existing files.** A zero-length file is a new database: the codec gets a random salt (or the key's or `CipherSalt`) at the first transaction. A file with content: page 1 must decode under the key, else `SQLITE_NOTADB` "file is not a database" (wrong key, wrong settings, or an unencrypted file); with a plaintext header the file parses, so it is `SQLITE_CORRUPT` "database disk image is malformed" (4.20.0). After decoding, the header's page size must equal the cipher's and its reserve must be at least the cipher's reserve, else `SQLITE_CORRUPT`. A later page that fails its MAC is `SQLITE_CORRUPT` naming the page in `Detail`. If another process changes the file's salt (a rekey), the next transaction derives a new codec from the new salt and the same key. ## Rekey (online, crash-safe) `Pager.Rekey(newKey, newSettings?)` (re-entrant, inside a write transaction), `RekeySync` and `RekeyAsync` (their own transaction). The page size and plaintext header size cannot change; digests and iterations can. The salt is kept unless the new key brings one, so the file stays recognisable and a plaintext header keeps matching its salt. Protocol: a rekey is one ordinary write transaction in which every page of the database is made writable (so its old form is journaled) and dirtied. While it is under way - pages are **written to the database file and to the WAL with the new key** (spill and commit); - **journal records are written with the old key** (the journal holds the old file, which is what playback restores); - pages already written with the new key are tracked in a bit set, so a page that was spilled and is read again in the same transaction decodes with the new key; - at commit the new codec becomes the codec (`EndRekey`), at rollback it is discarded. Crash safety needs nothing beyond the usual commit rules, because playback and checkpoint copy stored bytes. Rollback mode: before the journal is retired the file is the old file plus new-key pages that the hot journal overwrites with old-key pages (all of them were journaled), so recovery returns the old key's file entire; after the commit point it is the new file. WAL mode: the rekey commit's frames are either published (new key; older frames of the same pages are shadowed, and a checkpoint that stops half way leaves the WAL intact over a mixed database file) or never published (old key). Either way the file opens under exactly one key and holds every committed transaction. `journal_mode = OFF` and `MEMORY` are refused (`SQLITE_MISUSE`): their recovery promises nothing. A rekey of a database that has no page on disk yet just replaces the key. A reader that holds the old key and an older WAL snapshot keeps reading its snapshot; its next transaction fails with `SQLITE_NOTADB`, as a wrong key does. Tests: `EncryptedPagerTests.CrashDuringRekeyOpensUnderExactlyOneKey` (every storage call of a rekey is a crash point under drop-unsynced, keep-all, reordered and torn-sector policies; rollback and WAL modes, small caches that force spills), and the SIGKILL campaign `rekey-kill` in `tools/OneLite.Durability`, judged by the SQLCipher shell. ## ATTACH with a different key Each attached database is its own pager with its own `Key`; the pager has no connection-wide key. `MultiCommit.CommitSync` commits encrypted and plain databases together through the super-journal (its content is file names, not data). The SQL `ATTACH ... KEY` form belongs to the engine. ## Limits - Managed memory cannot promise there are no copies of a key: see Key handling. The page cache holds plaintext by design; so do the scratch buffers (the cache and the scratch buffers are zeroed when the pager closes). - The runtime's `Aes.EncryptCbc` / `DecryptCbc` allocate 64 bytes per call (the HMAC allocates nothing), so a read that misses the cache allocates 64 bytes; a cached read allocates nothing (`EncryptedPagerTests`). - Opening a database with a PBKDF2 count of 256,000 spends tens of milliseconds in the first `BeginRead` on the calling thread, also under the async driver. - A wrong key on a hot-journal or WAL database is not noticed by recovery (it copies stored bytes); the first read after recovery fails. - **Sorting is memory-only.** Sort runs hold whole rows and temporary files are not encrypted, so a connection to an encrypted database never spills a sort to a temporary file (`ORDER BY`, `GROUP BY`, `DISTINCT`); a very large sort uses a lot of memory. - Browser: unsupported. ## Whole-file transform `CipherFile.Encrypt` and `CipherFile.Decrypt` convert a whole database page by page. Encrypting needs a plaintext database that already reserves the bytes (header offset 20 ≥ `R`) and whose page size equals the setting; build one with `.filectrl reserve_bytes 80` followed by `VACUUM` in the `sqlite3` shell. `tools/OneLite.Cipher` wraps both. ## Checks - `tests/OneLite.Tests/Crypto`: SP 800-38A and RFC 6070 vectors; seven oracle-written files in `testdata/cipher` (`tools/scripts/gen_cipher_vectors.sh`) decoded and their MACs recomputed independently; wrong key, tamper, page-swap, zero page, random pages, round trips for every digest pair and page sizes 512 to 65536. - `tests/OneLite.Tests/Crypto/EncryptedPagerTests.cs`: round trips in every journal mode, stored bytes hold no plaintext, wrong key / unencrypted file / damaged page errors, raw key, V3 settings, plaintext header with salt, key provider, crash sweeps of rollback and WAL commits, rekey sweeps, rollback of a rekey, ATTACH-style two keys, allocation. - `tools/scripts/cipher_pager_interop.py`: interop through the pager both ways and SQLCipher crash images; `OneLite.Durability cipher-kill` / `rekey-kill`: SIGKILL campaigns judged by the SQLCipher shell. - `tools/scripts/cipher_interop.sh`: read interop (SQLCipher creates, we decrypt, `integrity_check` ok, `.dump` equal to `sqlcipher_export`) and write interop (we encrypt, SQLCipher opens, `integrity_check` ok, dump equal to the source), against both oracles. - `fuzz/OneLite.Fuzz/CipherFuzz.cs`: `OneLite.Fuzz cipher SECONDS [SEED]`; `CipherPagerFuzz.cs`: `OneLite.Fuzz cipher-pager SECONDS [SEED]` mutates encrypted file sets (database, hot journal, WAL, cut-off rekey) and opens them with the key: only typed errors allowed. See [encryption-review.md](https://recursiveai.org/onelite/docs/encryption-review/) for the project's own review (not an external audit) of this path (findings, evidence and residual risks). `EncryptionReviewTests` (IV freshness, plaintext and passphrase scans over crash images and real files, MAC-first, key wiping) and `EncryptionKnownAnswerTests` (pages built with OpenSSL and Python by `tools/scripts/gen_cipher_kat.py`) add to the checks above. Files that touch crypto: `src/OneLite/Storage/Crypto/{CipherSettings,CipherKey,PageCodec,CipherFile}.cs` and `src/OneLite/Storage/Pager/Pager.Crypto.cs`. No unsafe code, no native calls. # Encryption review The project's own review (not an external audit) of OneLite's SQLCipher-compatible encryption: `Storage/Crypto/*`, `Storage/Pager/Pager.Crypto.cs` and the places where the pager encodes and decodes pages (cache reads, spills, commits, journal records, WAL frames, playback, checkpoint). The reviewer had not written any of it. Format description: [encryption.md](https://recursiveai.org/onelite/docs/encryption/). Sources used: Zetetic's published "SQLCipher Design" and "SQLCipher API" pages, FIPS 197, NIST SP 800-38A, RFC 2104 and RFC 8018, and black-box runs of the SQLCipher community shell (4.5.6 on the Linux worker; 4.20.0 behaviour is the one recorded in encryption.md). No SQLCipher source was read. ## Summary | Question | Verdict | |---|---| | Format conformance | Conforms. Known-answer pages built by OpenSSL and Python's standard library decode; 422 of 422 interop cases against the SQLCipher shell pass. | | IV freshness | Holds on every write path. Tested for the database file, journal records, WAL frames and rekey. | | MAC before decrypt | Holds. Every decode checks the MAC first, in constant time, and hands back no plaintext on failure. Recovery paths never decrypt. | | Key hygiene | Two findings, fixed (a stray copy of the passphrase, and decrypted pages left in the cache after close). Limits listed below. | | Rekey crash safety | Held in 400 kills (200 rollback-journal, 200 WAL): every image opened under exactly one key with all acknowledged data. | | Plaintext leakage | No plaintext in the database, journal or WAL at any instant. Sort spill found and fixed: encrypted databases sort in memory (below). | | BCL-only primitives | Yes. | | Browser | Reports encryption unsupported. | Also found and fixed: the "WAL" variants of the encrypted pager tests did not use a WAL (below). ## Format conformance Checked against the design page and the byte layout in encryption.md: - PBKDF2-HMAC-SHA512 with 256,000 iterations and the file's 16-byte salt gives the AES-256 key (SQLCipher 3 settings: SHA-1, 64,000, 1024-byte pages, reserve 48). - The page MAC key is PBKDF2 of the AES key over the salt with every byte XORed with `0x3a`, 2 iterations, using the KDF digest. - A page is AES-256-CBC ciphertext without padding, then the 16-byte IV, then HMAC over ciphertext, IV and the 4-byte little-endian page number, then zeros. The codec needs 16 + the digest size rounded up to 16 bytes (80 for SHA-512, 48 for SHA-256 and SHA-1); the reference writes 80 in the database header for every digest and OneLite does the same. - Page 1 carries the salt in its first 16 bytes and encrypts from byte 16; the plaintext-header variant keeps its first N bytes as they are. - Raw keys `x'<64 hex>'` and `x'<96 hex>'` skip only the first PBKDF2 step. Evidence: - `EncryptionKnownAnswerTests` (new): three pages (SQLCipher 4 page 2 and page 1; SQLCipher 3 page 2) whose bytes were produced by `tools/scripts/gen_cipher_kat.py` with OpenSSL's `enc -aes-256-cbc -nopad` and Python's `hashlib.pbkdf2_hmac` and `hmac`, with no OneLite code involved. They decode to the expected plaintext, fail under another passphrase, another page number, or one flipped bit, and a re-encode carries a new IV. - `PageCodecTests`: the NIST SP 800-38A and RFC 6070 vectors, seven files written by the SQLCipher shell (`testdata/cipher`) decoded with their MACs recomputed independently. - `tools/scripts/cipher_worker.sh interop all` against SQLCipher 4.5.6: `passed 422 failed 0 {read 66, write 56, crash 300}` (300 crash images, 150 with a hot journal and 150 with a WAL). **Test vectors and terms.** Zetetic publishes no numeric test vectors: the design page has prose and one illustrative hexdump of a version-2-era file whose full key material is not given, so it cannot be turned into a test. The known-answer pages above are our own, derived from the prose. Nothing from Zetetic's pages is copied into the repository, so nothing is added to THIRD-PARTY-NOTICES. The page is marked "© Zetetic, LLC. All rights reserved"; the repository cites it and does not reproduce it. **Authenticated and not.** The MAC covers the ciphertext, the IV and the page number. It does not cover the salt (page 1, bytes 0-15), the plaintext header of a plaintext-header database, the zero padding after the MAC, or anything about the file as a whole. Consequences, all inherent in the format: - An attacker who can write the file can replace a page with an older valid copy of the same page number (a rollback or replay of single pages) and the page will pass its MAC. The reference behaves the same. Whole-file freshness needs something outside the file. - With a plaintext header the first N bytes can be changed. OneLite then checks the decrypted header against the settings (page size and reserved bytes) and reports corruption on mismatch, but fields outside those checks are the attacker's to change. - Changing the salt makes every page fail its MAC; it cannot be used to forge anything. SQLCipher 1 and 2 formats are not supported. ## IV freshness `PageCodec.Encode` fills the IV from `RandomNumberGenerator` on every call; nothing else produces an IV. The pager calls `Encode` once for each write event: a database page on spill or commit, a journal record, a WAL frame, and the in-memory-journal restore. The code never copies one encryption to a second place for new content: playback and checkpoint copy stored bytes, which are copies of a single encryption, not a second encryption under the same IV. (A checkpointed page therefore has the same IV as the WAL frame it came from; that is the same ciphertext, not a reuse.) Tests (`EncryptionReviewTests`): - `CodecGivesEveryEncodeOfTheSamePageAnotherIv`: 3,000 encodes of one page give 3,000 distinct IVs and bodies; page 1 too. - `RewritingIdenticalContentUsesNewIvsInDatabaseJournalAndWal`: the same plaintext written three times. Rollback journal (persist mode, so the record bodies stay readable) and WAL (24 frames for three commits of eight pages): every IV in the database file, journal records and WAL frames is distinct from every other. - `RekeyToTheSameKeyStillWritesNewIvs`: after a rekey every page's IV differs from before. ## MAC before decrypt - `TryDecode` computes the HMAC over the stored bytes and compares with `CryptographicOperations.FixedTimeEquals` before it calls the cipher; on a mismatch it clears the caller's buffer and returns false. There is no early exit for an all-zero page (checked against the shell, encryption.md "All-zero pages"). - Reads of the database file, of WAL frames and of page 1 all go through `DecodeStored`. A failed MAC is `SQLITE_NOTADB` ("file is not a database") for page 1 and `SQLITE_CORRUPT` ("database disk image is malformed") naming the page for any other, as the shell reports. - The WAL probe at open (`ResumeState.WalProbe`) only asks whether the file exists. Hot-journal playback, WAL recovery and checkpoint never decrypt: they validate the journal or WAL checksum chain, which runs over the stored bytes, and copy stored bytes. A forged journal can therefore put arbitrary bytes into the database file, but the next read of such a page fails its MAC, so it can cost availability and cannot make OneLite use attacker-chosen plaintext. A wrong key is not noticed by recovery; the first read afterwards fails. - Tests: `AnyChangedBitFailsTheMacAndReleasesNoPlaintext` (a bit flipped in page 1's ciphertext, in a later page's ciphertext, in the IV and in the MAC; also the wrong page number; the output buffer is all zero after the failure), `DamagedStoredPagesAreCorruptNotExceptions` (a file cut mid-page; a zeroed page 1), the existing damaged-page, tamper, page-swap and zero-page tests, and the cipher-pager fuzz target. ## Key hygiene Findings, both fixed: 1. `CipherKey.FromPassphrase(string)` encoded the string with `Encoding.UTF8.GetBytes`, which leaves an unpinned `byte[]` copy of the passphrase on the heap that is never wiped. It now encodes straight into the pinned array. Test: `ThePassphraseConstructorLeavesOnlyThePinnedCopy` (same bytes as the span overload, including multi-byte text and the empty string). 2. Closing an encrypted pager wiped the keys and the I/O scratch buffers, but not the cached decrypted pages or the page-1 scratch. `Pager.Dispose` now wipes them. Test: `ClosingAnEncryptedPagerWipesKeysAndCachedPlaintext`. Checked and fine: `CipherKey` and `PageCodec` hold the passphrase, the AES key and the MAC key in pinned arrays and zero them on `Dispose` (`KeysAreZeroedOnDispose` reads the private arrays); a codec whose construction fails disposes itself (a disposed key is refused); rekey disposes the old codec and key on commit and the new ones on rollback; `NoSecretAppearsInErrorTextOrToString` opens a database with a wrong passphrase and a wrong raw key and checks the exception text, `Detail` and `ToString()` for both, and `ToString` of `CipherKey` and `PagerOptions`. Messages name pages and settings, never key material. The `onelite` command-line tool has no key option yet. Limits (accurate as documented in encryption.md, plus): - The runtime's `Aes`, `IncrementalHash` and `Rfc2898DeriveBytes` hold their own copies of key material that we can only dispose; nothing is `mlock`ed (the reference locks its memory when it can). - A caller's `string` passphrase stays in managed memory until collected. - The page cache holds decrypted pages by design. Cache frames are not pinned, so the garbage collector may have left older copies behind before the wipe at close. Swap files and core dumps are outside any guarantee. - A pager in `journal_mode=MEMORY` keeps decrypted original pages in memory until the transaction ends. ## Rekey crash safety The protocol (encryption.md, "Rekey") is a single ordinary write transaction in which every page is rewritten under the new key while the journal or older WAL frames keep the old key's pages; the commit point is the usual one. It was read against atomiccommit and wal and has no step of its own that could leave a mixture: journal records are always written with the key the page is stored under, playback copies stored bytes, and the new key becomes the codec only after the commit. Evidence: - `EncryptedPagerTests.CrashDuringRekeyOpensUnderExactlyOneKey` (every storage call of a rekey is a crash point; four crash policies; rollback and WAL, small caches). These WAL cases did not run in WAL mode before this review (see "Test defect found"); they do now, and pass. - SIGKILL campaigns judged by the SQLCipher 4.5.6 shell (`cipher_worker.sh rekey`, seed 2302, 1024-byte pages, cache 50, 1,000 iterations): | Campaign | Kills | Result | |---|---|---| | rekey, rollback journal | 200 (hot journal at kill: 141) | 0 corruptions, 0 lost, 0 partial, 0 not-exactly-one-key | | rekey, WAL | 200 (hot WAL at kill: 159) | 0 corruptions, 0 lost, 0 partial, 0 not-exactly-one-key | | writer, rollback journal (seed 2301) | 200 (hot: 123) | 0 corruptions, 0 lost | | writer, WAL (seed 2301) | 200 (hot: 164) | 0 corruptions, 0 lost | Residual: a reader holding the old key and an older WAL snapshot keeps reading its snapshot; its next transaction fails with `SQLITE_NOTADB`, as a wrong key does. A rekey keeps the salt unless the new key brings one, which is fine for secrecy (the keys differ) but means a file's salt identifies the file across rekeys. ## Plaintext leakage Where OneLite writes files for an encrypted database: the database file, the rollback journal and super-journal, the WAL and `-shm`. The pager creates no other file: no statement journal (savepoints are kept in the page cache), no temporary database. - `NoFileEverHoldsPlaintextOrThePassphrase`: a workload that spills (cache of 6 pages, 24 pages), grows the file and then rekeys, in delete, truncate, persist and WAL modes, with a crash image taken after every storage call that keeps everything written. No image of any file holds the canary text, the passphrase text or the `SQLite format 3` magic. It also asserts the journal (or the log) really appeared in the images. - `NoFileOnDiskHoldsPlaintextWhileATransactionIsOpen`: the same on real files: while a spilling write transaction is open, after its commit and after close, every file in the directory. - The `-shm` file holds the wal-index (page numbers and frame numbers, no page content). The journal and WAL headers, the super-journal's file names and the frame headers are plaintext, as in the reference. **Sort spill (finding, fixed).** The reference leaves sorter and temporary files unencrypted and tells users to keep temporary storage in memory. OneLite's sorter writes runs of whole rows to a temporary file once a sort passes its memory budget (the page-cache size, never less than 256 KiB). With the first merge of the SELECT surface that became a plaintext leak for encrypted databases. Fix: `Connection.SortVfs`, the one place the engine gets a VFS for temporary files, is null whenever the connection's pager is encrypted (a key or a key provider), so `ORDER BY`, `GROUP BY`, `DISTINCT` and compound sorts keep every row in memory, however large. Every other place in the engine that could write a temporary file was checked: no code other than the sorter opens a temporary file (the aggregate sorters are memory-only by construction, and the pager has no statement journals or temporary databases). **Limit: memory-only sorting for encrypted databases.** A sort over more rows than fit in memory uses memory for all of them; there is no external sort for an encrypted database. Encrypting spill runs is a possible later feature. Tests (`EncryptedSortTests`): a 3,500-row table of canary text (about 700 KB, past the 256 KiB sort budget), `ORDER BY`, `DISTINCT`, `GROUP BY` and `GROUP BY ... group_concat` at the smallest cache size. The control run on a plain copy opens temporary files holding the canary (so the test can see a spill); the encrypted copy (SQLCipher 4 pages written by `CipherFile`) opens none. With the fix reverted the encrypted test fails with four temporary files open after the first row of the four queries; with it the test passes. ## BCL-only primitives Every use of `System.Security.Cryptography` under `src/` was listed: `Aes` (CBC, no padding, `EncryptCbc`/`DecryptCbc`), `IncrementalHash.CreateHMAC`, `Rfc2898DeriveBytes.Pbkdf2`, `RandomNumberGenerator`, `CryptographicOperations.FixedTimeEquals` and `ZeroMemory` in the codec and key types; elsewhere only `RandomNumberGenerator` for `randomblob`, journal nonces and temporary file names. No `AesManaged`, no ECB, no hand-written primitive, no `==` or `SequenceEqual` on a MAC. ## Browser `PageCodec.IsSupported` is false when `OperatingSystem.IsBrowser()`; creating a codec, or opening a pager with a key, throws `SQLITE_ERROR`, "encryption is not supported in the browser build". The browser build is compiled in the CI step "browser-wasm smoke". ## Test defect found The encrypted pager tests that were meant to cover WAL mode opened the database with the `Wal` journal-mode option and then called `ChangeJournalMode(Wal)`. A pager opened with that option already reports WAL, so the call was a no-op, the file stayed in rollback mode, and "WAL" cases (round trip, crash sweeps, rekey sweeps) were exercising the rollback journal; the round-trip check read the `-wal` file after close, when it no longer exists. The helper now opens in rollback mode first, and `NoFileEverHoldsPlaintextOrThePassphrase` fails if the log or journal never appears. All of the affected tests pass in real WAL mode. The kill campaigns use `mode=delete` and then `ChangeJournalMode(Wal)`, so they were not affected. ## Not covered - SQLCipher 4.20.0 was not re-run in this review (its recorded behaviour is in encryption.md). - Side channels other than the MAC comparison (cache timing in the BCL's AES, for example) are the platform's. - `PRAGMA key`/`rekey` as SQL, browser encryption and performance are out of scope. # Compatibility OneLite aims to behave exactly like one pinned SQLite release: **SQLite 3.53.4** (the `sqlite3` command-line shell from sqlite.org, with the compile options it reports through `PRAGMA compile_options`). Results, result codes, extended codes and error messages are compared against that shell. `EXPLAIN` and `EXPLAIN QUERY PLAN` output are not part of the promise; query results are. This table lists what has been measured and what is known to differ. Gaps are listed, not hidden. OneLite is not affiliated with or endorsed by the SQLite developers; "SQLite" is a registered trademark of Hipp, Wyrick & Company. How the numbers are produced: [sqllogictest.md](https://recursiveai.org/onelite/docs/sqllogictest/), [differential.md](https://recursiveai.org/onelite/docs/differential/), [interop-and-durability.md](https://recursiveai.org/onelite/docs/interop-and-durability/). ## Summary (0.2.0-preview.1) | Area | sqllogictest | Differential testing against `sqlite3` | Known gaps | |---|---|---|---| | SQL engine (all groups) | 622 files, 7,420,713 records: 5,939,877 pass, 0 wrong answers, 1 explained failure; 1,480,834 records are written for other database engines and skipped | 4 seeds, 22 minutes each, about 12,000 generated cases and 0.8 M checks: 0 unexplained differences | documented classes below | | Expressions, affinity, collation, casts | `random/expr` 1,200,366 pass | 0 unexplained | text to REAL with 20 or more significant digits can differ in the last bit; UTF-16 text cast from a BLOB that spills to overflow pages | | Aggregates, GROUP BY, window functions | `random/aggregates` 1,292,350, `random/groupby` 133,338 pass | 0 unexplained | ties inside a window stage may come out in a different order; floating-point cancellation in windowed `sum` | | Joins, subqueries, CTEs, compound queries, views | `random/select` 1,244,323, `index/view` 109,229 pass | 0 unexplained | a correlated comparison such as `max(b.c) ... WHERE b.c = a.x` can return the text spelling of a value where `sqlite3` returns the converted number (plan-dependent in `sqlite3`) | | Indexes, ORDER BY, IN, BETWEEN, DELETE | `index/*` all pass (1,730,910 records) | 0 unexplained | rows returned before a runtime error can differ when the plans differ; which of two runtime errors is reported can differ when one is in an ORDER BY or GROUP BY term | | DDL, ALTER TABLE, triggers, generated columns, STRICT | covered by `evidence/*` (487 pass, 1 explained) | 0 unexplained | | | Pragmas, ATTACH | — | 0 unexplained | | | JSON, math functions, `generate_series` | — | 0 unexplained | | | FTS5 | — | 25-minute run: 19 of 647 cases differ, all in the window `snippet` chooses or a malformed-query quirk of the shell (see [fts5.md](https://recursiveai.org/onelite/docs/fts5/)) | `contentless_delete`, `tokendata` and locale options are not implemented | | FTS3 and FTS4 | — | 25-minute run: 264 cases, 0 differing | see [fts3.md](https://recursiveai.org/onelite/docs/fts3/): `matchinfo` hit totals for multi-phrase queries depend on file layout; `col:"phrase"` without a space | | R*Tree, `dbstat`, `vec0` | — | — | R*Tree node layout differs from the shell's (results and integrity checks match) | The one explained sqllogictest failure: the script expects an empty result for `SUM` over integers that overflow, and SQLite 3.53.4 raises `integer overflow`, as OneLite does. ## Files | Check | Result | |---|---| | Files written by `sqlite3` (every page type, overflow, freelists, auto-vacuum modes, encodings, page sizes, WAL states, hot journals) read by OneLite | all identical | | Files written by OneLite pass `sqlite3`'s `PRAGMA integrity_check` and give the same `.dump` | all pass | | Real-world databases (Chinook, Northwind, Sakila) | read and written both ways | | FTS5 and FTS3/4 tables, both directions | FTS3/4: 32 option, page-size and encoding combinations, 32,000 documents, all pass | | Live coexistence with `sqlite3` processes in WAL mode | 10,000 interleaved commits, consistent both ways | | SQLCipher 4 files (SQLCipher 4.5.6 and 4.20.0) | read and written both ways | ## Durability Process kills (SIGKILL at random moments) and fault injection through the VFS (torn writes, lost and reordered syncs), judged by `sqlite3`: 0 corruptions and 0 lost acknowledged commits in every campaign so far, across rollback-journal modes, WAL, encryption and rekey, backup, blob I/O, `vec0`, FTS5 and FTS3/4 workloads. ## Not yet compatible - Native extensions (`load_extension`) are not supported; managed extensions register through the core's interfaces. - Geopoly, sessions and changesets, `carray` and the shell's miscellaneous extensions are not implemented. - The `RecursiveAI.OneLite.SQLitePCLRaw` adapter and the `onelite` command-line tool are placeholders in this preview. - `PRAGMA optimize` and `PRAGMA incremental_vacuum` are accepted and do nothing: no statistics are gathered and no free pages are returned. Use `ANALYZE` and a full `VACUUM` instead. # sqllogictest runner `tools/OneLite.SqlLogicTest` runs sqllogictest scripts against an engine and reports, per file and per directory, how many records passed. It is our own implementation of the script format as the project describes it (the `about.wiki` page of the corpus mirror) plus what the scripts themselves show. The corpus is data: scripts are fetched by `tools/scripts/fetch_corpora.py` into `~/onelite/corpus/sqllogictest/` and never committed. Where the format description is silent, the runner's behaviour was settled by running the corpus's own expectations against the pinned `sqlite3` 3.53.4. Those decisions are listed under "Rendering decisions". ## Running ``` sqllogictest run --corpus DIR --out DIR [--engine sqlite|onelite] [--shard i/n] [--filter GLOB[,GLOB]] [--max-minutes N] [--force] [--scratch DIR] sqllogictest report --out DIR [--corpus DIR] [--known FILE] sqllogictest check-printf [COUNT [DUMP_FILE]] ``` - `--corpus` defaults to `/corpus/sqllogictest`. - `--filter` takes comma-separated globs (only `*`) over paths relative to the corpus, for example `select*,evidence/*,index/in/10/*`. - `--shard i/n` (1 ≤ i ≤ n) splits the filtered files by size: largest first onto the lightest shard, ties by name. The split depends only on the file list and sizes, so it is the same on every machine. - Each finished script writes `OUT/files/.json` (renamed into place, so a killed run leaves whole files only). A rerun skips scripts that have a result, which makes a sweep resumable; `--force` redoes them. `--max-minutes` stops starting new scripts after that long and exits with code 3, so a long sweep runs as chunks that fit a time limit. - `report` merges the per-file results into `OUT/summary.json` and `OUT/summary.md` (header, totals, a table per directory, explained and unexplained failures with SQL, expected and actual). The exit code is 0 only when every record passed or is explained, no corpus file is missing and no file stopped early. - `tools/scripts/sqllogictest_sweep.sh OUT [SHARDS]` runs the shards as parallel processes and then the report. Groups in the report: `select*` (the top-level `select1..5.test`), `evidence`, `index/`, `random/`. **Counting.** A *record* is a `statement` or `query`; `halt` and `hash-threshold` are not counted. A record is *skipped* when its `skipif`/`onlyif` lines exclude it, *failed* when it ran and did not match (a malformed record counts as failed), *passed* otherwise. A file whose engine throws something other than a statement error (the OneLite stub throws "not implemented") stops with a `fatal` note instead of recording every remaining record as a failure. **Known differences.** `testdata/sqllogictest/known-differences.json` lists failures that are understood and accepted, each as `{file, line, reason}`. `report --known` counts them as explained. An entry that does not occur in a run is listed as stale. ## The format as implemented A script is a sequence of records separated by blank lines. Lines starting with `#` are comments: they are dropped, and they do not separate records (so a comment may sit inside a statement). A record is optionally prefixed by `skipif NAME` / `onlyif NAME` lines, then one of: | Record | Meaning | |---|---| | `statement ok` / `statement error` | The following lines, up to a `----` line, are one SQL command. It must succeed / must fail. Text after a `----` is kept but not compared (the error text is not part of the format). | | `query [] [