# graphitesql roadmap
This document is the plan for **graphitesql**: a single-crate, pure, safe,
`no_std` Rust implementation of SQLite with byte-for-byte compatibility with the
SQLite 3 file format.
The foundation (the file format, the storage/btree/pager stack, and a broad SQL
engine) is **done** — see §3 for a capability summary. The rest of this document
is the forward plan for closing the remaining gap with SQLite: §4 the work
tracks, §5 the cross-cutting concerns, §6 the testing strategy.
---
## 1. Architecture
SQLite has a famously clean layered design. We mirror it, because the layering is
what makes the file format and the SQL semantics tractable to re-implement
independently. Data flows top-to-bottom on writes and bottom-to-top on reads:
```
┌──────────────────────────────────────────────┐
SQL text │ api Connection / Statement / Row │ public API
├──────────────────────────────────────────────┤
│ sql::token tokenizer │
│ sql::parser parser ──► sql::ast │ front end
├──────────────────────────────────────────────┤
│ planner query planning (join/index) │
│ exec iterator executor (+ future VDBE)│ execution
│ func collate built-in functions, collations │
├──────────────────────────────────────────────┤
│ btree table & index B-trees, cursors │ data model
├──────────────────────────────────────────────┤
│ pager page cache, transactions, │ storage
│ rollback journal, WAL, locking │
├──────────────────────────────────────────────┤
│ format on-disk byte layout (the spec) │ format
├──────────────────────────────────────────────┤
│ vfs Vfs / File traits (mem, std, …) │ OS boundary
└──────────────────────────────────────────────┘
```
| `vfs` | OS abstraction: open/read/write/sync/lock | `os_unix.c`, `os.c` |
| `format` | byte layout of header, pages, cells, records, freelist | `fileformat2.html`, `btreeInt.h` |
| `pager` | page cache, atomic commit, journal, WAL, locking | `pager.c`, `wal.c`, `pcache.c` |
| `btree` | table/index B-trees, cursors, balancing | `btree.c`, `btreeInt.h` |
| `value` / `record` | storage classes, serial types, affinity | `vdbemem.c`, `vdbeaux.c` |
| `sql::token` | tokenizer | `tokenize.c`, `keywordhash.h` |
| `sql::parser`/`ast`| grammar → parse tree | `parse.y`, `expr.c`, `resolve.c` |
| `exec` | name resolution, execution, DDL/DML, triggers, functions | `select.c`, `where.c`, `insert.c`, `vdbe.c` |
| `planner` *(in `exec`)* | index selection, join order (future: cost-based) | `where.c`, `analyze.c` |
| `func` / `collate` | scalar/aggregate funcs, collations | `func.c`, `date.c`, `callback.c` |
| `schema` | parse `sqlite_schema`, build the catalog | `build.c`, `prepare.c` |
| `api` | `Connection`/`Statement` and (later) C-API shim | `main.c`, `vdbeapi.c` |
**Executor vs. bytecode.** The engine today is an *operational, iterator-style
executor* with the same observable semantics as SQLite, not a VDBE bytecode VM.
That was the pragmatic path to a correct, testable engine. Adopting a VDBE IR is
now an internal refactor (it changes how queries are represented, not their
results) and is scheduled in Track B — it unblocks real `EXPLAIN` output and a
cost-based planner.
---
## 2. Design principles
- **`#![forbid(unsafe_code)]`, no exceptions.** Enforced in `Cargo.toml` lints.
- **`no_std` + `alloc` is the baseline.** `std` is an additive feature (real
files, `std::error::Error`). Nothing core may depend on `std`.
- **Near-zero dependencies.** No crates in the default build. The one sanctioned
exception is the in-house `timezone-data` crate, behind an opt-in feature, for
`localtime`/`utc` date modifiers. Optional dev/test deps behind `cfg(test)` are
fine.
- **The VFS is the only I/O boundary.** All file access goes through the `Vfs`
and `File` traits — what makes `:memory:`, std files, and wasm uniform.
- **Compatibility is verified, not assumed.** Every feature lands with a
differential test against the real `sqlite3` CLI, and anything we write must
pass `PRAGMA integrity_check` (see §6).
- **Fail loud while young.** Unimplemented paths return `Error::Unsupported`
rather than silently producing wrong results.
---
## 3. Foundation ✅ *(done)*
The layered foundation and a broad SQL engine are complete and differentially
verified against `sqlite3` (a 1,600+ query corpus plus 140+ focused test suites).
Detailed history lives in `CHANGELOG.md`; in summary, graphitesql today:
**Reads & writes real SQLite files.** Opens `sqlite3`-written databases
(including WAL-mode) and **creates** databases whose files `sqlite3` opens with
`PRAGMA integrity_check = ok`. Storage covers rowid and **`WITHOUT ROWID`**
tables, automatic/secondary/`UNIQUE` indexes (incl. `sqlite_autoindex_*`),
overflow pages, the freelist with **page merging on delete**, real **`VACUUM`**,
the full **`auto_vacuum`** track (read, write, FULL auto-truncate, INCREMENTAL
reclaim), and the **WAL read *and* write** path (`journal_mode=WAL`,
`wal_checkpoint`).
**Runs a broad SQL dialect.** `SELECT` with `WHERE`/`GROUP BY`/`HAVING` (incl.
without `GROUP BY`)/`ORDER BY`/`LIMIT`/`OFFSET`/`DISTINCT` and SELECT-list
aliases resolved in WHERE/GROUP BY/HAVING; `INNER`/`LEFT`/`RIGHT`/`FULL`/cross/
comma **joins** plus **`NATURAL`** and **`USING`** (with column coalescing),
nested-loop + a hash join for equi-joins; compound queries
(`UNION`/`INTERSECT`/`EXCEPT`, collation-aware); (recursive) **CTEs** with
`LIMIT`; correlated subqueries, `[NOT] EXISTS`, derived tables; views & CTEs as
sources; **window functions** (`ROWS`/`RANGE`/`GROUPS`, `EXCLUDE`, `FILTER`,
named windows); `INSERT … SELECT`, `UPDATE … FROM`, `UPDATE OR
IGNORE/REPLACE/…`, UPSERT, `RETURNING`, row values, `STRICT` tables, generated
columns; a broad scalar/aggregate function library incl. **date/time**
(+ `CURRENT_DATE`/`TIME`/`TIMESTAMP`, `timediff`), `printf`/`format` (16-sig-digit
float cap like sqlite), `random`/`randomblob`, `unistr`/`unistr_quote`,
`subtype`, **JSON** (`json_*`, `json_group_array`/`object`, `json_pretty`,
`json_each`/`json_tree`, **JSON5 input** with strict `json_valid`, verbatim
number-text preservation) and the **JSONB binary family** (`jsonb`,
`jsonb_array`/`object`/`extract`/`set`/`insert`/`replace`/`remove`/`patch`,
`jsonb_group_array`/`object`, and JSONB-blob input to every `json_*`), **virtual
tables** (`CREATE VIRTUAL TABLE … USING module`), `iif`/`if`,
`sqlite_version`, math (pure-`core`); **type affinity** and SQLite-exact real
formatting; column names matching sqlite's verbatim source spans; collation
(`BINARY`/`NOCASE`/`RTRIM`) honored across comparisons,
`IN`/`BETWEEN`/`CASE`, `min`/`max`, set ops,
`ORDER BY`/`GROUP BY`/`DISTINCT`/`UNIQUE`/index keys; `EXPLAIN QUERY PLAN` with
an index-driven planner (equality/range/`IN`/OR-union seeks, **inner-join seeks**
incl. the full **`WITHOUT ROWID`** PK/secondary-index seek family, **comma-join**
`WHERE`-equality promotion, **automatic-index** reporting for unindexed
equi-joins, **index-driven `ORDER BY`** with **covering-index reads**,
stats-driven choice via `ANALYZE`/`sqlite_stat1`); constraint enforcement
(`NOT NULL`, `CHECK`,
`UNIQUE`/`PK`, standalone/partial/expression UNIQUE indexes; **foreign keys**
enforced at runtime under `PRAGMA foreign_keys=ON` — child INSERT/UPDATE parent
checks and parent DELETE/UPDATE actions NO ACTION/RESTRICT/CASCADE/SET NULL/SET
DEFAULT, composite + self-referential; *deferred:* DEFERRABLE/INITIALLY DEFERRED);
**triggers** (`BEFORE`/`AFTER`/`INSTEAD OF`, `UPDATE OF`,
`WHEN`, recursive, `NEW`/`OLD` incl. rowid); `SAVEPOINT`/`RELEASE`/`ROLLBACK TO`;
**`ATTACH`/`DETACH`/`TEMP`** multi-schema (cross-database reads, writes, joins,
qualified DDL, view reads, transactions & savepoints); DDL with full CREATE-time
and ALTER validation (incl. `DROP TABLE` cascading the table's triggers, and
`RENAME COLUMN` propagating into the table's own CHECK/generated/DEFAULT
expressions); the schema catalog queryable as `sqlite_schema`/`sqlite_master`
(incl. `table_info` over a view and over the catalog itself, the composite-PK
`pk` ordinal, and `index_list` `pk`/`u` origins), with
`table_list`/`collation_list`/`database_list` and bare `pragma_*` table-valued
functions.
What remains is breadth and depth toward full SQLite parity, below.
---
## 4. Forward plan — closing the gap with SQLite
Four tracks. Completed work is summarized; **remaining work is broken into
numbered, independently-shippable pieces** (each one lands with a differential
test and keeps `master` green). Tracks can progress in parallel.
### Track A — SQL language & functions breadth *(substantially complete)*
Done: outer joins + `NATURAL`/`USING` (coalescing), generated columns,
collations, UPSERT, `UPDATE OR …`, `RETURNING`, row values, `ORDER BY` modifiers,
`STRICT` tables, `CREATE TABLE … AS SELECT`, `INSERT … SELECT`, `UPDATE … FROM`,
HAVING without GROUP BY, SELECT-list aliases in WHERE/GROUP BY/HAVING,
`CURRENT_DATE`/`TIME`/`TIMESTAMP`, `iif`/`if`, `sqlite_version`, the
window-function suite; the math + `printf`/`format` (16-sig-digit float cap) +
JSON libraries incl. JSON5 input, `timediff` (A5), `json_error_position` (A6),
`random`/`randomblob`, `unistr`/`unistr_quote`/`subtype`, the **JSONB binary
family**, and JSON verbatim-number preservation; partial/expression index
*equality* seeks (A3); column names matching sqlite's verbatim source spans;
type names keeping their `(len[,scale])`; and DDL validation incl. `DROP TABLE`
cascading triggers and `RENAME COLUMN` propagating into the table's own
expressions.
**Remaining pieces** (small, each function/clause-scoped):
- **A2 — DESC index columns honored in seeks.** A `DESC` index gives correct
results today by scan/superset; teach the seek paths
(`try_index_lookup`/`try_index_range`, `index_range_rowids`) to walk a `DESC`
b-tree in the right direction. *Perf-only; acceptance: `EXPLAIN QUERY PLAN`
shows the seek and results unchanged.*
- **A3b — partial/expression index for range/IN seeks.** Equality seeks already
use them (A3); extend `try_index_range`/`try_index_in` to consult
`partial_expr_seek` the same way, with `eqp_access` in lockstep.
- **A4 — `NULLIF` collation in `func.rs`.** The IN/CASE/BETWEEN literal-left
collation cases are done; `nullif(x, y)` should still honor an explicit
`COLLATE` on either operand. *Ref:* `resolve.c` collation-of rules.
- **A7 — multi-statement `execute_batch(sql)` API.** The CLI shell already has a
`BEGIN`/`CASE`/`END`-depth-aware splitter; lift it into a public API that runs
a `;`-separated script (like `sqlite3_exec`), running each slice through
`execute_params` so per-statement CREATE text is preserved. *(`execute()` stays
single-statement.)*
- **A8 — JSONB of JSON5-form numbers.** `jsonb('.5')`/`jsonb('0xFF')` emit a
normalized `FLOAT`/`INT` element; sqlite uses the `FLOAT5`/`INT5` tags with the
raw text. Needs the parser to keep the raw JSON5 number text and `to_jsonb` to
pick the `*5` tag for it. *Tiny edge; round-trips correctly today.*
*ALTER rename — cross-object propagation* (a column/table rename must reach
*other* schema objects, not just the table's own definition; today those break
with "no such table/column" after a rename). Build bottom-up:
- **A-rn1 — table-rename AST walker.** A `rename_table_in(stmt, old, new)` that
walks a `Select`/trigger body renaming every `FROM`/`JOIN` `TableRef.name == old`
and every qualified `old.col` reference, recursing into subqueries and CTEs
(respecting a CTE that shadows the name). The reusable primitive for A-rn2/3.
- **A-rn2 — RENAME TABLE rewrites dependent view bodies.** Using A-rn1, on
`ALTER TABLE t RENAME TO t2` rewrite every view whose body references `t`
(triggers already work — they fire via the repointed `tbl_name`). *Acceptance:
`SELECT … FROM v` works after the rename.*
- **A-rn3 — RENAME COLUMN reaches dependent objects.** Extend `rename_column_ref`
use to dependent view/trigger bodies (scope-aware: only references resolving to
the renamed table's column) and to foreign keys in *other* tables that name the
renamed parent column.
- **A-rn4 — text-preserving schema edits** *(cosmetic, lower priority).* graphite
reprints the affected CREATE from its AST (quoted/canonical), so
`SELECT sql FROM sqlite_master` after an ALTER differs from sqlite's
text-preserving token edit. Match it by editing the stored text in place. *Not
in the differential corpus.*
### Track B — Query planner, statistics & the VDBE
Done: `ANALYZE` + `sqlite_stat1` (byte-compatible) with stats-driven index
choice; equality/range/`IN`/OR-union seeks; **inner-join seeks** — rowid/IPK
(**B1a**), secondary-index (**B1a²**), and the **complete `WITHOUT ROWID` seek
family** (PK equality + range, PK joins, secondary-index equality + range, with
the named-index-vs-autoindex covering rule); a **hash join** for unindexed
equi-joins with **B3 automatic-index** EQP (`BLOOM FILTER` + `SEARCH … USING
AUTOMATIC COVERING INDEX`); **comma-join `WHERE`-equality promotion** (`FROM a, b
WHERE a.x=b.y` planned like an explicit `JOIN … ON`); **B0** index-driven
`ORDER BY` (rowid + secondary, ASC+DESC); **B2/B2b covering reads** (ordered scan,
`count(*)` via index, and equality/range/`IN` seeks reading straight from a
covering index); and the VDBE spike (`exec::vdbe`) covering constant projections,
single-table scan + `WHERE`/`ORDER BY`/`DISTINCT`/`LIMIT`, whole-table aggregates,
single-table `GROUP BY`, and grouped `HAVING` + aggregate `ORDER BY` (**B6**) —
all matching the tree-walker via `query_vdbe`.
**Remaining optimizer pieces** *(perf-only — results already correct; acceptance:
the plan matches sqlite3's `EXPLAIN QUERY PLAN` and execution stays in lockstep):*
- **B0b-i — multi-term `ORDER BY` via a multi-column index prefix.** Extend
`order_index_scan` to satisfy an `ORDER BY (c1, c2)` from an index whose leading
columns are `(c1, c2, …)` (skip the sort).
- **B0b-ii — `GROUP BY` over an indexed prefix.** Consume groups in index order
(no hash) when `GROUP BY` is a prefix of an available index.
- **B0b-iii — `ORDER BY` from a `WHERE`-chosen index.** Today B0 fires only with
no `WHERE`; reuse the index a `WHERE` seek already picked to also skip the sort.
- **B1b — Join reordering.** Beyond the comma-join promotion (done), reorder
`FROM` tables by a simple cost model (most-selective indexed table inner)
instead of textual order; results identical, order verified via EQP. Preserve
LEFT/RIGHT/FULL semantics. *Ref:* `where.c`.
- **B1c — RIGHT/FULL join inner seeks.** B1a/B1a²/WITHOUT-ROWID seeks cover
INNER/LEFT; RIGHT/FULL joins still materialize the inner table.
- **B4 — `sqlite_stat4` histograms.** Extend `ANALYZE` to gather per-index sample
histograms (byte-compatible `sqlite_stat4` rows) and use them for range
selectivity. Split: **B4a** write/read the `sqlite_stat4` rows; **B4b** consult
them in the seek-cost chooser. *Ref:* `analyze.c`.
*VDBE migration* (the largest internal refactor — changes representation, not
results; keep the differential corpus green at every step). Done so far: the
spike covers single-table scans, aggregates, `GROUP BY`, and grouped `HAVING` +
aggregate `ORDER BY` (**B6**), all parity-checked via `query_vdbe`. Remaining,
each additive behind `query_vdbe` until B7:
- **B5a — VDBE two-table nested-loop join.** `OpenRead`/`Rewind`/`Column`/`Next`
per cursor for a single INNER join + `ON`.
- **B5b — VDBE join: index/PK inner seek + outer-join NULL-extend.** Add the
seek-driven inner cursor and LEFT NULL-extension to the join opcodes.
- **B5c — VDBE: subqueries / compound / window** shapes still on the tree-walker.
- **B7a — route `query()` onto the VDBE behind a flag** (opt-in), corpus green.
- **B7b — flip the default** to the VDBE once parity holds across the suite.
- **B8 — Real `EXPLAIN` (bytecode).** Emit the `addr|opcode|p1|p2|p3|p4|p5`
listing from a compiled `Program` (today `Error::Unsupported`). *Ref:*
`vdbe.c`, `opcodes.h`.
### Track C — Storage engine, transactions, concurrency & multi-schema
Done: the `Vfs` locking contract (`SHARED`/`RESERVED`/`PENDING`/`EXCLUSIVE`,
process-local), rollback-journal writer serialization, `SAVEPOINT` family,
transaction-state validation, the introspection PRAGMAs (`index_list`,
`index_info`, `foreign_key_list`/`_check`, `integrity_check`/`quick_check`,
`freelist_count`, `application_id`, `data_version`, `table_list`,
`collation_list`, the `pragma_*` TVFs incl. the no-paren form), and the **entire
`ATTACH`/`DETACH`/`TEMP` multi-schema track** (C1–C5): the multi-database
registry + `PRAGMA database_list`, in-memory and file attachments (cross-engine
both directions), schema-qualified reads/writes/`DROP`, cross-database joins
(+ 3-part `aux.t.c` names, `WITHOUT ROWID` sources), qualified DDL
(`ALTER`/`CREATE INDEX|TRIGGER|VIEW`, stored bare-named), cross-database view
reads (via a `read_default` context cell), `TEMP` tables, and cross-database
transactions + savepoints. **`auto_vacuum`** is fully read/write now (C6a read +
C6b-1 empty-db header + C6b-2 ptrmap maintenance): graphite reads and **writes**
auto_vacuum databases that sqlite3 reads with `integrity_check = ok` (only the
optional space-reclaim — FULL truncate C6b-3, `incremental_vacuum` C6b-4 —
remains).
The **multi-schema track is complete** (C1–C5 + C-ms1 `CREATE TEMP
VIEW/TRIGGER` catalog placement), and the **whole `auto_vacuum` track is complete**
(C6a read; C6b-1 empty-db header; C6b-2 commit-time `rebuild_ptrmap`; C6b-3 FULL
auto-truncate; C6b-4 `incremental_vacuum(N)` on-demand reclaim — all cross-checked
with sqlite3 `integrity_check = ok`). Tuning PRAGMAs (`cache_size`, `synchronous`,
`busy_timeout`, `locking_mode`, …) now *report* sqlite's defaults; honoring them
is C8b/C8c.
**Remaining pieces** *(storage / durability / concurrency — each independent):*
- **C7 — SQLite-format rollback journal.** Match the on-disk journal byte layout
(ours is a private, recoverable format today) so a crash mid-write is
recoverable by `sqlite3`. Pairs with the crash-recovery harness (§6). Split:
**C7a** write the sqlite journal header + page records; **C7b** recover from a
sqlite-format journal on open.
- **C8a — `secure_delete`.** Zero freed cell/page content (`PRAGMA
secure_delete=ON`).
- **C8b — honor `PRAGMA cache_size` / `mmap_size`.** Getters report defaults
today; store the set value and bound the page cache accordingly.
- **C8c — a real `pcache` with LRU eviction.** Replace the keep-everything page
map with a bounded cache (depends on C8b's size). *Perf, not correctness.*
- **C9a — reader `SHARED`-lock enforcement / multi-reader.** Let multiple readers
share while a writer is excluded, per the locking contract.
- **C9b — OS-level file locks.** Cross-process locking via `std::fs::File::lock`
(needs MSRV 1.89) behind the std VFS, or a host-provided VFS.
- **C9c — WAL `-shm` wal-index.** The shared-memory index for multi-connection
WAL readers.
- **C9d — thread-safe `Connection`.** `Send`/`Sync` story for sharing a
connection (or a documented per-thread model).
### Track D — Virtual tables & ecosystem extensions
Done: the **read-only** virtual-table foundation — a table-valued-function
mechanism (`generate_series`, `json_each`, `json_tree`); the `VTabModule` trait +
`VTabRegistry` (**D1a**); `CREATE VIRTUAL TABLE … USING module[(args)]` parsing,
persistence, and `FROM`-source integration incl. joins and `DROP` (**D1b**); and
`best_index`/`filter` constraint pushdown (**D1b²**). Today a vtab is read-only
(INSERT/UPDATE/DELETE rejected) and stateless (modules get no storage).
**The blocker for FTS5/R-Tree is a *writable, persistent* vtab.** Both store data
that must survive in the file, byte-compatibly. SQLite backs them with **shadow
tables** (ordinary b-trees) — which graphite already writes byte-compatibly — so
the path is: give modules writable shadow storage, then build the two modules on
top. Build bottom-up (each step lands testable on `:memory:` first):
- **W1 — writable-vtab trait + DML routing.** Add an `update` method to
`VTabModule` (the `xUpdate` analog: insert / update / delete a row, default =
the current read-only error), and route `INSERT`/`UPDATE`/`DELETE` on a vtab to
it in the executor (today rejected).
- **W2 — shadow-table storage for modules.** A helper so a module can create and
read/write backing *regular* tables (`<name>_data`, …) on `connect`/`update` —
reusing graphite's normal table machinery, so persistence is byte-compatible for
free. Give the module access to the connection (the trait is stateless today).
- **D3 — R-Tree** (smaller of the two; build on W1/W2). *Ref:* `rtree.c`.
- **D3a — module + correct results.** Parse `rtree(id, minX, maxX[, …])`,
store rows in shadow tables, answer queries by scan + filter (functionally
correct, differentially testable). *(Test graphite directly — confirm the CI
sqlite3 build has R-Tree before differential-comparing.)*
- **D3b — `best_index` spatial pushdown** of the coordinate constraints.
- **D3c — byte-compatible node format.** Pack bounding boxes into the
`<name>_node` blob layout sqlite uses, so a graphite-written R-Tree round-trips
through sqlite3. *(Required for file compatibility; large.)*
- **D2 — FTS5** full-text search (build on W1/W2; the larger module). *Ref:*
`fts5*.c`. Break out: **D2a** tokenizer (unicode61/ascii); **D2b** inverted
index in shadow tables + `INSERT`; **D2c** `MATCH` query; **D2d** `bm25()`
ranking; **D2e** byte-compatible on-disk segment format.
- **D4 — User-defined functions from Rust.** Register scalar/aggregate/window
functions and custom collations through a safe public API (the read side of
what `func`/`collate` already do internally). Pairs with a public vtab-module
registration API.
- **D5 — `sqlite3_session`** — changesets/patchsets for replication.
- **D6 — Async VFS for wasm** — non-blocking I/O over IndexedDB/OPFS.
- **D7 — C-API shim** — a `libsqlite3`-compatible surface as a *separate* crate.
**Blocked:** requires `extern "C"` + raw pointers, incompatible with this
crate's `#![forbid(unsafe_code)]`; would live in a sibling crate that opts out.
---
## 5. Cross-cutting concerns
- **MSRV** is pinned at **1.88** (`Cargo.toml`); revisit before 1.0 (C9 wants 1.89
for `File::lock`).
- **Numeric model** — reals are `f64` to match SQLite; no extended decimal/bignum.
- **Parser** stays hand-written (no build-time codegen, friendlier errors);
`parse.y` remains the source of truth for precedence and accepted forms.
- **Performance** is deliberately secondary to correctness until the VDBE +
planner land; the iterator executor is `O(n)` in places (some constraint and
`WITHOUT ROWID` paths rebuild on write) that the planner work will revisit.
---
## 6. File-format compatibility & testing strategy
This is the project's whole reason to exist, so it gets first-class testing.
- **Differential tests.** Run the same SQL through both `sqlite3` and graphitesql
and diff results; a large generated corpus (`tests/differential.rs`) plus a
per-feature suite. Every new feature adds to one of these.
- **`integrity_check` as a gate.** Any database graphitesql writes must pass
`sqlite3`'s `PRAGMA integrity_check` (and, with FKs on, `foreign_key_check`).
- **Round-trip & cross-engine.** graphitesql reads what `sqlite3` writes and vice
versa, for every storage feature (rowid, `WITHOUT ROWID`, WAL, post-VACUUM).
- **Probing the corpus blind spots.** The result-diff corpus is blind to
rejection-based behavior, boundary values, `Error::Unsupported` gaps, and
introspection/error-message detail; these are covered by targeted suites driven
by probing each semantic dimension against the `sqlite3` CLI.
- **Fuzzing** — a deterministic corruption-robustness harness
(`tests/fuzz_corruption.rs`, ~50k malformed-file variants;
`tests/fuzz_sql.rs`, ~3.3k malformed/deeply-nested SQL) asserts the readers
return an error and never panic. It already caught real reader panics
(`btree/page.rs` assert/bounds/arithmetic, `sql/parser.rs` recursion depth),
now fixed. *(Expand toward a coverage-guided fuzzer when a no-dep path exists.)*
- **Crash-recovery** *(planned, pairs with C7)* — a fault-injecting `Vfs` that
truncates / fails at chosen fsync points, asserting recovery to a consistent
state.
- **SQLite's own suite** *(planned)* — run a curated slice of SQLite's `test/`
TCL assertions (the SQL-level ones) as an additional oracle.
### Known sources of legitimate file divergence
Two SQLite-compatible writers can produce different bytes for the same logical
content; we document and accept these rather than chase them: free-page reuse
order and exact balancing splits, `change_counter`/`version_valid_for` values,
the embedded `SQLITE_VERSION_NUMBER`, and unused/reserved bytes left from
deletions. **Compatibility means both engines read each other's files and agree
on contents**, not byte-identical independently-built databases.
---
## 7. Immediate next steps
The bounded SQL-language, function, and planner correctness items are essentially
closed (the whole `WITHOUT ROWID` seek family, comma-join promotion, automatic-
index EQP, JSONB, JSON number-provenance, `random`/`randomblob`, `unistr`/
`subtype`, view `table_info`, the `DROP`/`RENAME COLUMN` DDL fixes, etc.). What's
left is **bigger, multi-step work** — each track above is now broken into the
smaller pieces to ship it. Suggested order:
1. **W1 + W2 — writable, persistent vtabs.** The single unlock for the two
headline modules. Small, self-contained, and testable on its own (a trivial
writable module proves it before D2/D3).
2. **D3 — R-Tree** on top of W1/W2 (D3a correct results → D3b pushdown → D3c
byte-compatible nodes). Smaller and more bounded than FTS5; do it first.
3. **A-rn2/A-rn3 — cross-object ALTER rename** (view/trigger/FK propagation), the
one remaining *functional* correctness gap; needs the A-rn1 walker.
4. **Planner leftovers** (perf-only, EQP-gated) — **B0b-i/ii/iii**, **B1b** join
reordering, **A2** DESC seek direction, **A3b** partial/expr range·IN seeks,
**B4** `sqlite_stat4`.
5. **D2 — FTS5** (D2a–D2e) — the larger module, once W1/W2 and R-Tree have
exercised the writable-vtab path.
6. **B5/B7/B8 — the executor→VDBE migration** — the largest internal refactor;
unblocks real bytecode `EXPLAIN`.
7. **Smaller gaps** — **A4** (`nullif` collation), **A7** (`execute_batch` API),
**A8** (JSONB JSON5 numbers), **C8a/b/c** (secure_delete, cache honoring).
Deferred / blocked: **C7/C9** (SQLite-format journal + cross-process
locks/concurrency — durability depth), **D5/D6** (sessions, async wasm VFS),
**D7** (C-API — blocked by `#![forbid(unsafe_code)]`), **A-rn4** (cosmetic
text-preserving schema edits). The **SQLite TCL suite** (§6) isn't runnable
against a Rust crate — the differential corpus + `integrity_check` remain the
green proxy.