Skip to content

expected.md

Latest commit

 

History

History
220 lines (171 loc) · 9.2 KB

expected.md

File metadata and controls

220 lines (171 loc) · 9.2 KB
Raw

SQLite fixtures: what each file proves

All fixtures are synthetic, generated by make_fixtures.py using only the Python standard library (the sqlite3 module is a binding to SQLite itself, so the writer is the reference implementation). Content is fixed; reruns are byte-identical for a given SQLite library version (the header embeds the library version number at offset 96). Regenerate with:

python3 make_fixtures.py

To test, load a fixture through the reader:

let
    db = Sqlite3.Database(File.Contents("...\sqlite3\test\types.db"))
in
    db

The decode algorithm has been cross-checked against SQLite on every fixture: a byte-level mirror of the reader's exact logic (header parse, b-tree walk, varints, serial types, overflow X/M/K math, DDL column parsing, rowid aliasing) produces cell-identical results to the reference implementation reading the same files. The tables below are the expected Power Query output.

For every fixture, the navigation table must list the tables named below, in creation order, and nothing else. System objects (sqlite_sequence, sqlite_autoindex_*), indexes, and views must never appear.

types.db: serial types, rowid aliasing, dynamic typing

Default 4096-byte pages, UTF-8. Tables Types (9 rows) and Empty (0 rows).

Types(id INTEGER PRIMARY KEY, iv INTEGER, rv REAL, tv TEXT, bv BLOB, nv INTEGER, av). The iv column walks every integer serial type: constants 0/1 (serial types 8/9), then 1, 2, 3, 4, 6, and 8-byte two's complement. av has no declared type and holds a different storage class in every row. nv is NULL in every row. The id column is stored as serial type 0 (NULL) and must be populated from the rowid; the last rowid is 2^40, proving multi-byte varint rowids.

id iv rv tv bv nv av
1 0 0 (empty) 0 bytes null 42
2 1 1.5 héllo ✓ 00 FF null text
3 -1 -2.75 line(lf)break 01 null 01 02 (binary)
4 127 1E+300 tab(tab)here "SQLite" null 3.5
5 -32768 -1E-300 日本語 00 01 .. 0F null null
6 8388607 3.141592653589793 🚀 rocket null null 0
7 -2147483648 2 null 7F 80 null 1
8 140737488355327 0 ascii 0 bytes null -1
1099511627776 9007199254740993 0.5 big rowid FE null 9223372036854775807

Notes:

  • rv rows 1, 7, 8 were written as 0.0, 2.0, -0.0. SQLite's integral-real optimization stores them as integers on disk (serial types 8 and 1), and the sign of -0.0 is lost by SQLite itself before the reader is involved. They must read back as the numbers 0, 2, 0.
  • Row 9 iv was written as 9007199254740993 (2^53 + 1) and av as 9223372036854775807 (max int64). Both read back exactly: 8-byte integer cells decode through BinaryFormat.SignedInteger64, which preserves the full signed 64-bit range. (The earlier accumulate-to-double path rounded iv down to 9007199254740992 and rounded av up to 2^63 — tripping the two's-complement branch and flipping it to -9223372036854775808; the fixture pins the corrected values so a regression is noticed.)
  • 140737488355327 is 2^47 - 1, the largest 6-byte value: exact, no loss.

Empty(id INTEGER PRIMARY KEY, note TEXT) has no rows. It must materialize as a 0-row table that still has both columns, not an error.

empty.db: database with no tables at all

A table was created, dropped, and the file vacuumed, leaving page 1 as a leaf with zero cells. The navigation table must have zero rows (and its usual four columns), not an error.

overflow.db: payloads spanning overflow pages

512-byte pages, so with usable size U = 512 the spec thresholds are X = U - 35 = 477 (max inline payload) and M = ((U - 12) * 32 / 255) - 23 = 39 (minimum inline when K overshoots), with K = M + ((P - M) mod (U - 4)).

Blobs(id INTEGER PRIMARY KEY, data BLOB), 153 rows. Rowid r holds exactly r bytes with byte i equal to (i + r) mod 251, so every cell is self-describing: length and content are both functions of the id. The record header is 4 bytes for this shape, so total payload P = r + 4. The rowid sweep 466..596 and 971..991 plus 100000 covers, verified against the formulas:

  • P <= 477: fully inline, no overflow (8 rows).
  • P in 478..546: K > X, so only M = 39 bytes stay inline and the rest moves to overflow pages (the counterintuitive minimal-inline branch; 79 rows total across both sweep windows, the second window proving the second wrap of the mod at P around 985).
  • P in 547..985: K <= X, K bytes inline plus overflow (66 rows).
  • Rowid 100000: an overflow chain of about 197 pages, and a 3-byte varint for the blob's serial type (2 * 100000 + 12).

Texts(id INTEGER PRIMARY KEY, data TEXT), 5 rows. Multi-byte UTF-8 sequences straddle overflow page boundaries, so the chain must be reassembled before decoding (decoding chunk-by-chunk would tear characters):

id data
1 "a" repeated 400 times (inline)
2 "β" repeated 300 times (600 bytes, 2-byte chars)
3 "✓" repeated 2000 times (6000 bytes, 3-byte chars)
4 "x" repeated 20000 times
5 3000 chars cycling through the 80 code points U+3041..U+3090

btree.db: multi-level table b-tree

512-byte pages. Many(id INTEGER PRIMARY KEY, val TEXT): 5000 rows row-00001..row-05000 were inserted, then every id divisible by 500 was deleted (500, 1000, .. 5000; ten rows). Expected: exactly 4990 rows, ids ascending with those ten gaps, val always equal to row- plus the zero-padded id.

The generator asserts the tree is three levels deep (interior root over interior pages over leaves), so this proves recursive descent through interior pages, the rightmost pointer, and skipping freed pages/slots.

ddl.db: CREATE TABLE parsing edge cases

Default 4096-byte pages. Navigation table must list exactly: Quoted, Constraints, AddedCols, Seq, Wide, NoRowid. The sqlite_sequence system table (created by AUTOINCREMENT), the sqlite_autoindex_Constraints_1 index entry (created by UNIQUE), and the SeqView view must all be filtered out.

Quoted, 2 rows

All four identifier quoting styles in one DDL. Expected column names, exactly: double quoted, bracketed, backticked, single quoted.

double quoted bracketed backticked single quoted
dq 1 1.5 AB
null -2 null 0 bytes

Constraints, 2 rows

The DDL contains a parenthesised type DECIMAL(10,2), a CHECK with nested parens and a comparison, a DEFAULT ('a,b(c)') whose literal contains a comma and parens inside quotes, and four table-level clauses (CONSTRAINT pk PRIMARY KEY (id, other), UNIQUE, FOREIGN KEY, CHECK). Expected columns, exactly: id, amount, label, other; none of the table-level clauses may surface as a column. The composite PRIMARY KEY is not an INTEGER PRIMARY KEY, so no rowid aliasing: id is stored explicitly.

id amount label other
1 12.5 x 10
2 0.01 a,b(c) 20

AddedCols, 3 rows

Created with columns a, b; rows 1 and 2 inserted; then ALTER TABLE ADD COLUMN c REAL and d TEXT; then row 3 inserted. Rows 1 and 2 are stored as short (2-value) records and must be padded with nulls.

a b c d
1 one null null
2 two null null
3 three 3.5 full

Seq, 2 rows

id INTEGER PRIMARY KEY AUTOINCREMENT (multi-line DDL, exercising whitespace normalisation in the parser). Rows with ids 1 and 2 were inserted, id 2 deleted, and one more row inserted, which takes id 3 because AUTOINCREMENT never reuses ids. Expected: (1, first), (3, third). Both ids come from rowid aliasing.

Wide, 3 rows

Columns id plus c01..c64, every value a 60-char text like r1c01.......... (the cell tag dot-padded to 60). 64 two-byte serial-type varints make the record header 131 bytes long, so the header-length varint itself needs two bytes: this is the fixture for header-varint parsing, and for tables wide enough that a serial-type miscount shifts every later column.

NoRowid, 1 row stored

Declared WITHOUT ROWID. Its Data cell must be an error (index b-tree layout is not supported), not wrong data. The table row itself must still be present in the navigation table.

utf16le.db and utf16be.db: non-UTF-8 database encodings

512-byte pages, header encoding ids 2 and 3. Identical logical content, table U(id INTEGER PRIMARY KEY, txt TEXT):

id txt
1 plain ascii
2 héllo ✓
3 日本語テキスト
4 emoji 🚀🌍 (surrogate pairs in UTF-16)
5 (empty)
6 null

The CREATE TABLE text in sqlite_master is stored in the database encoding too, so getting any row back at all proves DDL decoding; the byte-order proof is that both files yield identical tables.

wal.db: WAL-mode database

512-byte pages, journal_mode=WAL, checkpointed and cleanly closed, so the main file holds all committed data and no -wal file ships. The header read/write version bytes are 2. Expected: table Log with rows (1, entry 1) .. (5, entry 5), and the navigation table metadata must report the mode:

Value.Metadata(Sqlite3.Database(File.Contents("...\wal.db")))[Sqlite3.WalMode] = true

The other seven fixtures must report Sqlite3.WalMode = false.