spike: 002 read a real SQLite file end-to-end

[iheitlager]

Goal

De-risk the Tier 0 READ CORE before committing to the V1 ticket breakdown. Prove we can walk a real SQLite file from raw bytes to typed row values — the smallest experiment that validates the file-format understanding in 001-architecture.

Branch: spike/002_file_reading

Hypothesis

The file format as documented (100-byte header → b-tree pages → cells → records with serial types) is sufficient to dump sqlite_master and one user table from a stock-created database, with no undocumented surprises.

Experiment (Atomic — smallest valid test)

Throwaway code, no architecture, single main.rs allowed:

1. Generate a fixture with stock sqlite3:

   sqlite3 fixture.db "CREATE TABLE t(a INTEGER, b TEXT, c REAL, d BLOB, e); \
     INSERT INTO t VALUES(42,'hello',3.14,X'DEADBEEF',NULL); \
     INSERT INTO t VALUES(-1,'unicode: héllo→',2.5e300,X'',7);"

2. Parse the 100-byte header (page size, encoding, page count)
3. Walk page 1's table b-tree → decode sqlite_master records
4. Find table t's root page, walk its b-tree
5. Decode all serial types via varint header
6. Print rows; diff against sqlite3 fixture.db "SELECT * FROM t"

Stretch (only if cheap): one row large enough to force an overflow chain; peek at a WAL-mode fixture's -wal header.

Falsification criteria (Rigor — decide, don't confirm)

The spike FAILS (valuable outcome!) if:

• Serial type decoding doesn't match documented format
• Cell/payload layout has surprises the docs don't cover
• Varint or text encoding behaves unexpectedly

Either way: findings go back into 001-architecture/spec.md and the V1 ticket breakdown.

Timebox

1-2 days. Code is disposable — the learning is the deliverable.

Exit criteria

• Dump of sqlite_master matches expectation
• All 5 value types of table t decoded correctly (both rows)
• Written findings: what the docs didn't tell us
• Go/no-go on the 9-ticket V1 breakdown (adjust if needed)

References

• SQLite file format
• .openspec/plan.md — Tier 0 READ CORE

---

🤖 Generated with Claude Code