#MoonBase — Pure-MoonBit SQLite File-Format Reader & Writer

Pivot note: the project originally started as a from-scratch embedded SQL engine (see git history, 22 early commits). After review feedback about overlap with existing pure-MoonBit engines, it was redirected to the concrete, well-specified SQLite file-format compatibility problem, which is verifiable byte-for-byte against the reference implementation.

#Status

#Why this direction

#Architecture

moonbase/
├── src/sqlite/            Pure-MoonBit SQLite file-format layer (zero FFI)
│   ├── value.mbt          Value model, DbError, SQLite collation (NULL<num<text<blob)
│   ├── varint.mbt         SQLite varint encode/decode (7-bit groups, ≤9 bytes)
│   ├── pager.mbt          100-byte DB header + page slicing / usable size
│   ├── record.mbt         Record serial types: int(1..6), float64, text(UTF-8/16), blob, const 0/1
│   ├── btree_file.mbt     B-tree page parse (leaf/interior table & index), overflow chains
│   ├── lexer.mbt          Tolerant DDL lexer for schema text inside the file
│   ├── schema.mbt         sqlite_master scan + CREATE TABLE parser (rowid alias, WITHOUT ROWID)
│   ├── db.mbt             Db facade: open, table lookup, row materialisation
│   ├── query.mbt          Read-only SELECT: projection, COUNT(*), WHERE, ORDER BY, LIMIT (3-valued logic)
│   ├── writer.mbt         **Writer**: make_db — generate valid .db from scratch (header, records, leaves, overflow chains, sqlite_master)
│   ├── writer_tree.mbt    Writer: bottom-up multi-level table & index B-tree construction, page arena, page-size fan-out
│   ├── verify.mbt         Structure self-check: page ownership, page types, key ordering, overflow chains, freelist
│   ├── export.mbt         Export a whole database as a replayable SQL script (export_sql)
│   ├── value_wbtest.mbt   10 reader unit tests
│   ├── writer_wbtest.mbt  14 writer tests (round-trip, float64, overflow, multi-leaf, multi-table, index ordering/separators, 3-level trees, page sizes)
│   ├── verify_wbtest.mbt  5 self-check tests (clean db, overflow, multi-level, two corruption cases)
│   └── export_wbtest.mbt  6 export / SQL-literal tests
└── cmd/moonbase-cli/      CLI demo (js backend; Node fs for real file I/O)

#Features

#Repository

• GitHub (primary): https://github.com/Derk2006/moonbase
• Gitee (mirror): https://gitee.com/EchoDerk/moonbase

#Quick Start

# Run the test suite (works on default backend — no FFI needed)
moon test

# Build the CLI demo (JavaScript backend, runs on Node.js — real file I/O via Node fs)
moon build --target js
node _build/js/debug/build/cmd/moonbase-cli/moonbase-cli.js

# Build for other backends (CLI uses a stub there; the library is fully cross-target)
moon build            # default backend, builds & tests clean

#CLI usage

moonbase-cli header <file.db>               print the 100-byte database header
moonbase-cli schema <file.db>               list tables / indexes / columns
moonbase-cli tree   <file.db> <table>       show the table B-tree root + row count
moonbase-cli query  <file.db> "SELECT ..."  run a read-only query
moonbase-cli dump   <file.db> <table>       export the whole table (pipe-separated)
moonbase-cli dump-sql <file.db>             export the whole database as a SQL script
moonbase-cli index  <file.db> <index>       list every entry of an index (key… + rowid)
moonbase-cli check  <file.db>               structural self-check (no sqlite3 needed)
moonbase-cli vacuum <in.db> <out.db> [page] compact a database (drop freelist / dead space)
moonbase-cli make   <out.db> [rows] [page] [enc]  generate a legal .db from scratch, with an index
                                               enc: 1=UTF-8 (default), 2=UTF-16le, 3=UTF-16be

#Library usage

// ---- read ----
let db = @sqlite.Db::open(bytes)                       // bytes: Bytes of a .db file
let t  = db.table("users")
for row in db.rows(t) { /* row.values : Array[Value] */ }
let rs = @sqlite.query(db, "SELECT name FROM users ORDER BY name")

// ---- write: generate a brand-new .db from scratch ----
let rows : Array[@sqlite.RowSpec] = [
  @sqlite.RowSpec::new(None, [
    @sqlite.VInt(Int64::from_int(1)),
    @sqlite.VText("Alice"),
  ]),
]
let tables : Array[@sqlite.TableSpec] = [
  @sqlite.TableSpec::new(
    "users",
    "CREATE TABLE users(id integer primary key, name text)",
    rows,
  ),
]
let out_bytes = @sqlite.make_db(tables)                  // page size 4096, UTF-8
let small     = @sqlite.make_db_with_page_size(tables, 512)  // 512 .. 65536
let utf16     = @sqlite.make_db_with_encoding(tables, 4096, 2)  // 1=UTF-8, 2=UTF-16le, 3=UTF-16be

// ---- self-check: walk the file structure without sqlite3 ----
let report = @sqlite.verify_db(db)
if !report.passed() { for p in report.problems() { println(p) } }

// ---- export: dump the whole DB as a replayable SQL script ----
let sql = @sqlite.export_sql(db)

#Documentation

#Example (against a real sqlite3-generated file)

# create a fixture with the official tool
python -c "import sqlite3; c=sqlite3.connect('demo.db'); \
  c.execute('create table t(id integer primary key, name text, score real)'); \
  c.executemany('insert into t values(?,?,?)', [(1,'Alice',91.5),(2,'Bob',88.0),(3,'陈小明',77.25)]); \
  c.commit()"

node _build/js/debug/build/cmd/moonbase-cli/moonbase-cli.js schema demo.db
# table t (root=2, 3 列)
#     id  integer  [INTEGER PRIMARY KEY -> rowid 别名]
#     name  text
#     score  real

node _build/js/debug/build/cmd/moonbase-cli/moonbase-cli.js query demo.db \
  "SELECT name, score FROM t WHERE score > 80 ORDER BY score DESC"
# name|score
# Alice|91.5
# Bob|88

#Verification methodology

1. Generate fixtures with the official sqlite3 (via Python's sqlite3 module) across page sizes, encodings and schemas.
2. Parse the same files with MoonBase and compare header fields, schema and dumped rows byte-for-byte with sqlite3 output.
3. Unit tests cover the tricky format corners: 9-byte varint (2^56), signed-integer serial types, float64, UTF-8/16 text, BLOB, the NULL<number<text<BLOB ordering, and code-point text collation.

1. make_db(...) produces a .db from scratch (no sqlite3 involved in creation).
2. The same in-tree parser re-opens the generated bytes and asserts every value decodes exactly (round-trip). This is writer_wbtest.mbt.
3. The generated file is then opened by the official sqlite3 and queried — proving it is a real SQLite database, not a private format. Verified across: small tables, exact IEEE-754 float64 round-trip (incl. -0.125, π, 0.0), >4 KB overflow-page chains, 300-row trees that force a leaf→interior-page upgrade, and multiple tables in one file. 3a. Multi-level verification: make deep.db 20000 512 builds a database whose table tree is 3 levels deep and whose index tree is 4 levels deep. integrity_check still returns ok, count(*) matches, and the check script walks the pages itself and asserts depth >= 3 for both trees — a wrong separator promotion at the second interior level would either lose keys or duplicate them. 3b. Index verification: make out.db 400 produces an index that needs an interior page. PRAGMA integrity_check must return ok (this catches a wrong leaf/interior split), PRAGMA index_list must list it, SELECT count(*) ... INDEXED BY must equal the row count, and EXPLAIN QUERY PLAN must report USING COVERING INDEX. MoonBase also reads indexes written by official sqlite3 (moonbase-cli index) and returns the same entries in the same order. 3c. Structural self-check: moonbase-cli check is an independent walker (it does not reuse the query path). It is also run against files created by official sqlite3 (with a freelist, overflow pages and a WITHOUT ROWID table) and must report 结构自检通过; deliberately corrupted copies (illegal page type, page referenced twice, ncell beyond page capacity) must be rejected.
4. Export round-trip: dump-sql emits a SQL script from that database, which the official sqlite3 replays into a fresh file. Every row is compared value-by-value and PRAGMA integrity_check must return ok. This also covers 64-bit integer extremes (±2^63) and multi-column records containing REAL followed by BLOB.

#Example — generate a .db with the pure-MoonBit writer

# Build the CLI (js backend) and generate a legal .db from scratch
moon build --target js
node _build/js/debug/build/cmd/moonbase-cli/moonbase-cli.js make demo_out.db
# generated demo_out.db (users: id INTEGER PK, name TEXT, score REAL, memo BLOB)

# (a) the in-tree reader can read it back
node _build/js/debug/build/cmd/moonbase-cli/moonbase-cli.js query demo_out.db \
  "SELECT name, score FROM users ORDER BY score DESC"

# (b) the OFFICIAL sqlite3 can open it too — proof it is a real SQLite file
python -c "import sqlite3; c=sqlite3.connect('demo_out.db'); \
  print(c.execute('SELECT name, score FROM users ORDER BY score DESC').fetchall())"

#License