README

pas-sqlite3

A faithful AI made port of SQLite 3.53.0 (D. Richard Hipp et al.) from C to Free Pascal (FPC 3.2.2+) targeting x86-64 Linux.

Status: Phases 0–5 complete; Phases 6–8 ported — in testing. The Pascal port now opens databases, parses SQL, generates VDBE bytecode, and runs queries end-to-end against its own pager / B-tree / VDBE. The full Test* regression gate reports 111 / 112 binaries passing and 5264 / 5264 assertions passing (only TestFuzzDiff still red). TestExplainParity reports 1026 / 1026 SQL statements producing byte-identical VDBE bytecode versus the C reference. Differential probes (DiagOps, DiagCast, DiagDate, DiagFunctions, DiagMoreFunc, DiagFeatureProbe, ...) drive the remaining runtime gaps.

What this is

A behavioural and on-disk-parity port of SQLite. The Pascal build must:

• produce byte-identical .db files for the same SQL input as the C reference,
• return identical query results, and
• emit the same VDBE bytecode for the same SQL.

This is not a wrapper around libsqlite3.so (FPC already ships sqlite3dyn / sqlite3conn for that). The goal is a pure-Pascal implementation for study, hardening, and embedded use cases where a C toolchain is unavailable.

Prerequisites

• Free Pascal Compiler ≥ 3.2.2
• GCC (to build the C reference oracle libsqlite3.so)
• GNU Make
• Tcl (for SQLite's own Tcl test suite in Phase 9)
• The upstream SQLite split source tree at ../sqlite3/ (version 3.53.0 or later; not the amalgamation)

Run the dependency checker:

./install_dependencies.sh

Build

src/tests/build.sh

This will:

1. Build libsqlite3.so from ../sqlite3/ via upstream's own build system (with SQLITE_DEBUG, SQLITE_ENABLE_EXPLAIN_COMMENTS, and SQLITE_ENABLE_API_ARMOR for differential-testing fidelity).
2. Compile all Pascal test binaries into bin/.

Compile-time feature parity with the oracle

Because correctness is defined differentially against the C oracle, the Pascal port and the Tcl harness must expose the same compile-time feature set as the oracle build. A mismatch makes tests fail (or pass) for the wrong reason, even when the engine itself is correct.

Inspect the oracle's flags at any time with:

../sqlite3/sqlite3 :memory: "SELECT name FROM pragma_compile_options;"

As of 3.53.0 the oracle build enables ENABLE_FTS3/FTS4, ENABLE_RTREE, ENABLE_PERCENTILE, ENABLE_OFFSET_SQL_FUNC, ENABLE_DBPAGE_VTAB, ENABLE_DBSTAT_VTAB, ENABLE_BYTECODE_VTAB, ENABLE_STMTVTAB, ENABLE_UNKNOWN_SQL_FUNCTION, ENABLE_MATH_FUNCTIONS, STRICT_SUBTYPE, DQS=0; and does not define ENABLE_PREUPDATE_HOOK, ENABLE_SNAPSHOT, ENABLE_SESSION, ENABLE_MEMORY_MANAGEMENT, ENABLE_UNLOCK_NOTIFY, ENABLE_COLUMN_METADATA, ENABLE_NULL_TRIM, ENABLE_FTS5, or ENABLE_UPDATE_DELETE_LIMIT. The core on-disk defaults (auto_vacuum=0, page_size=4096, encoding=UTF-8, journal=delete, cache_size=-2000, synchronous=2) match the port.

Two mechanisms keep the harness aligned:

• ifcapable flags live in src/tests/tcl/tester_min.tcl (mirroring upstream test_config.c). Any capability not explicitly pinned there defaults to 1, so a feature the oracle omits must be pinned to 0 or its tests will run a feature path the engine lacks (symptom: ... was omitted at compile-time, no such function, or a wrong-branch result). Currently pinned to 0: columnmetadata, null_trim, preupdate, snapshot, session, memorymanage, unlock_notify, fts3/fts5, stat4, … Before "fixing" a compile-flag-gated test, check sqlite_compileoption_used on the real oracle .so rather than implementing the feature.
• Engine defines live in src/passqlite3.inc. Keep these in lockstep with the oracle. Known divergence: the port's parser is built SQLITE_UDL_CAPABLE_PARSER, so it accepts DELETE/UPDATE … LIMIT whereas the oracle (no ENABLE_UPDATE_DELETE_LIMIT) rejects it as a syntax error — this diverges e_delete, upfrom2, wherelimit2. See tasklist.md 6.40 for the full feature-gap inventory.

Running the smoke test

LD_LIBRARY_PATH=src/ bin/TestSmoke

Expected output:

sqlite3 version : 3.53.0
csq_open_v2     : OK
csq_prepare_v2  : OK
csq_step        : SQLITE_ROW
column value    : 1
csq_step (done) : SQLITE_DONE
csq_finalize    : OK
csq_close       : OK

TestSmoke PASSED.

Regression testing

The formal regression gate runs every Test* binary under bin/ and treats each binary's exit code as its pass/fail signal (exit 0 = pass, non-zero = fail). All Test* binaries follow this convention, so the gate auto-discovers new tests by construction — adding a Test*.pas to src/tests/build.sh is enough to put it under the gate.

Diag* binaries are differential probes (port vs. C oracle) rather than pass/fail tests, and many legitimately emit non-zero divergence counts while the port matures. They are deliberately not part of the binary pass/fail gate; their divergence counts are tracked in tasklist.md instead.

To run the test:

src/tests/run_regression.sh    # run every bin/Test* and aggregate results

Each test runs in its own temporary working directory under a per-test timeout (default 60s, override with REGRESSION_TIMEOUT=<seconds>).

Do not run Test* binaries (or TclTestDriver shards) in parallel. Concurrent runs exhaust RAM on a typical workstation — page caches, per-driver libpassqlite3tcl.so instances, and tclsh interpreters stack up and one of the runs gets OOM-killed silently, leaving you with a partial log that looks clean. run_regression.sh is already sequential by design; keep manual invocations the same way.

Per-test logs are retained under a temporary directory only when at least one binary fails; an all-green run cleans them up. The script exits non-zero if any binary fails, so it can be wired into CI directly.

• Known failures: Failures are deliberately left visible rather than quarantined so they cannot be silently ignored.

Full SQL corpus differential (TestSQLCorpus)

TestSQLCorpus harvests SQL string literals embedded in every Diag*.pas / Test*.pas source file (the existing differential probes), then runs each statement through both pascal and C oracles in isolated workdirs and byte-compares stdout, stderr, rc, and the on-disk db blob (with documented non-deterministic bytes masked).

LD_LIBRARY_PATH=src/ bin/TestSQLCorpus

The summary line reports scripts run / ok / diverge. Any divergence is catalogued in src/tests/DIVERGENCES.md (per-file rollup + first 16-byte window for db-blob mismatches) so each one is bisectable against the C oracle — see Phase 9.1 in tasklist.md for the workflow. Supporting artefacts:

• src/tests/corpus/MANIFEST.txt — tier-1 / tier-2 source-file inventory
• src/tests/corpus/MASK.md — masked db-header byte ranges + C cites
• src/tests/SQLLiteralExtractor.pas — the literal-extraction scanner
• src/tests/CorpusOracle.pas — Pascal-port + libsqlite3 oracle plumbing

The harness exits rc=0 even when divergences exist (catalogue-only by design); promotion to a hard CI gate is tracked under 9.1.5.

Upstream Tcl test suite (bin/TclTestDriver)

Current status

$ bin/TclTestDriver --timeout 2400000 --fail-log-dir bin/tcl-failure-logs
...
Total: 948 pass / 11 fail / 0 skip / 959 total in 29647113 ms

How the integration works

SQLite ships a large .test corpus written in Tcl. Each test file drives the engine through a Tcl command named sqlite3, which upstream defines in C in tclsqlite.c. Rather than rewrite those tests, this project makes the Pascal engine impersonate that C shim, so the unmodified upstream .test files run against pas-sqlite3 as a differential acceptance gate.

Four layers cooperate (full design in src/tests/tcl/PLAN.md):

bin/TclTestDriver (Pascal)   forks system tclsh once per .test file,
        ???                    collects PASS / FAIL / SKIP + timing
        ???
tclsh (system Tcl 8.6)       load bin/libpassqlite3tcl.so Sqlite3
        ???                    source tester_min.tcl   (harness shim)
        ???                    source <name>.test       (UNMODIFIED upstream test)
        ???
libpassqlite3tcl.so          FPC-built bridge: registers the `sqlite3` Tcl
        ???                    command (PasTclBridge.pas + PasTclSqlite.pas,
        ???                    a faithful port of tclsqlite.c) + test-only
        ???                    vtab/extension modules (testmodules/)
        ???
passqlite3 core              the pure-Pascal engine, called via the C ABI

Key points:

• The driver never links Tcl itself — it shells out to /usr/bin/tclsh, and the bridge .so is the only artefact that imports libtcl symbols. This keeps the production engine build free of any Tcl dependency.
• src/tests/tcl/tester_min.tcl is a trimmed port of upstream tester.tcl, supplying the procs every test assumes (do_test, do_execsql_test, execsql, ifcapable, integrity_check, finalize_testing, …) plus the ifcapable capability map described under Compile-time feature parity above.
• The bridge is built separately by src/tests/build_tcl_lib.sh (with -dSQLITE_TEST), not by src/tests/build.sh. Re-run it after any engine edit, or the sweep silently tests a stale .so.
• Each test's verdict is tracked in src/tests/tcl/STATUS.txt as pas-strict (must pass — the CI gate), pas-soft (a documented, tracked divergence), or pas-skip (not yet exercisable). src/tests/tcl/check_status_regression.sh fails only when a pas-strict test regresses.

The minimal build → run → gate sequence:

./src/tests/build_tcl_lib.sh          # → bin/libpassqlite3tcl.so  (rerun after engine edits)
./src/tests/build_tcl_driver.sh       # → bin/TclTestDriver
bin/TclTestDriver --filter select1    # run a subset (or omit --filter for all)
bin/TclTestDriver --filter select1 | src/tests/tcl/check_status_regression.sh /dev/stdin

Running the full sweep

TclTestDriver walks src/tests/tcl/MANIFEST.txt (~959 entries) and runs each .test file under a 20 s per-test watchdog. Read this before launching a run — picking the wrong invocation costs 25+ minutes.

• Run the shards sequentially — never in parallel. Running shards (or multiple Test* binaries) concurrently exhausts RAM on a typical workstation: each driver loads its own libpassqlite3tcl.so
  • tclsh and many tests allocate multi-MB page caches. Run one shard at a time:

rm bin/shard-*.log
rm bin/shard-*.err
./src/tests/build_tcl_lib.sh
./src/tests/build_tcl_driver.sh
for s in 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15; do
  echo "=== shard $s/16 ==="
  timeout 3600 bin/TclTestDriver --gate strict \
    --shard $s/16 --fail-log-dir bin/tcl-failure-logs \
    > bin/shard-$s.log 2> bin/shard-$s.err
done
cat bin/shard-*.log | src/tests/tcl/check_status_regression.sh /dev/stdin

Running a single .test file

To iterate on one test, pass --filter SUBSTR — only manifest entries whose path contains SUBSTR are run. Combine with --limit 1 if the substring still matches more than one entry:

./src/tests/build_tcl_lib.sh
./src/tests/build_tcl_driver.sh
bin/TclTestDriver --filter select1            # every entry matching "select1"
bin/TclTestDriver --filter select1 --limit 1  # just the first match

Other flags accepted by the driver (see src/tests/TclTestDriver.pas):

• --manifest PATH — point at an alternate manifest
• --shard I/N — slice the (post-filter) entry list; I is 0-based
• --fail-log-dir DIR — write a per-test log on FAIL
• --build PROFILE — load an alternate engine .so (e.g. threadsafe, memdebug)
• --permutation NAME — apply a named runtime permutation
• --gate strict — diff results against the committed STATUS.txt
• --coverage — opt-in opcode-coverage mode

Quick start examples

Two ways to drive the Pascal port: link against the passqlite3* units from your own program, or pipe SQL through the passqlite3 shell binary. Both examples match the canonical SQLite quickstart at https://www.sqlite.org/quickstart.html; see src/tests/DiagSampleProg.pas for a fuller sample (prepare/step, bind, callback) that is run side-by-side against the C reference for byte-identical transcripts.

1. From Pascal code

{$I passqlite3.inc}
program QuickStart;

uses
  SysUtils,
  passqlite3types, passqlite3util, passqlite3os,
  passqlite3pcache, passqlite3pager, passqlite3wal,
  passqlite3btree, passqlite3vdbe, passqlite3codegen,
  passqlite3parser, passqlite3vtab,
  passqlite3main;

function PrintRow(pArg: Pointer; nCol: i32;
  argv, colv: PPAnsiChar): i32; cdecl;
var
  i: i32;
  pCol, pVal: PPAnsiChar;
begin
  pCol := colv; pVal := argv;
  for i := 0 to nCol - 1 do begin
    if pVal^ = nil then
      WriteLn(pCol^, ' = NULL')
    else
      WriteLn(pCol^, ' = ', pVal^);
    Inc(pCol); Inc(pVal);
  end;
  WriteLn;
  Result := 0;
end;

var
  db: PTsqlite3;
  pErr: PAnsiChar;
begin
  if sqlite3_open(':memory:', @db) <> SQLITE_OK then Halt(1);
  pErr := nil;

  sqlite3_exec(db,
    'CREATE TABLE t(a INTEGER, b TEXT);'#10 +
    'INSERT INTO t VALUES(1, ''one''), (2, ''two''), (3, NULL);',
    nil, nil, @pErr);
  if pErr <> nil then begin sqlite3_free(pErr); pErr := nil; end;

  sqlite3_exec(db, 'SELECT a, b FROM t ORDER BY a;',
               @PrintRow, nil, @pErr);
  if pErr <> nil then sqlite3_free(pErr);

  sqlite3_close(db);
end.

Compile alongside the rest of the suite via src/tests/build.sh, or copy the fpc invocation from there. No libsqlite3.so is required at run time — the binary is pure Pascal.

For a prepared-statement / bind / step variant, see RunSample_Pas_BindInsert in src/tests/DiagSampleProg.pas:186.

2. From the CLI

src/tests/build.sh produces bin/passqlite3, the Pascal port of SQLite's shell.c (Phase 10 — basic skeleton; meta-commands beyond -help / -version are still being landed):

# Run a one-shot SQL string against an on-disk database:
bin/passqlite3 demo.db \
  "CREATE TABLE t(a INTEGER, b TEXT);
   INSERT INTO t VALUES(1,'one'),(2,'two'),(3,NULL);
   SELECT * FROM t;"

Output:

1|one
2|two
3|

Or pipe SQL into an in-memory database:

echo "SELECT 1+1, 'hello';" | bin/passqlite3 :memory:

Project layout

pas-sqlite3/
├── [src/](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/)
│   ├── [passqlite3.inc](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3.inc)          # compiler directives (included first in every unit)
│   ├── [passqlite3types.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3types.pas)     # primitive types, result codes, open flags, limits
│   ├── [passqlite3internal.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3internal.pas)  # shared constants from sqliteInt.h
│   ├── [passqlite3util.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3util.pas)      # hash, varint, printf glue, mprintf, UTF helpers
│   ├── [passqlite3printf.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3printf.pas)    # sqlite3_snprintf / %!.*g / sqlite3RenderNumF
│   ├── [passqlite3os.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3os.pas)        # VFS, POSIX file locks, mutexes
│   ├── [passqlite3pcache.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3pcache.pas)    # page cache
│   ├── [passqlite3pager.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3pager.pas)     # pager + journal
│   ├── [passqlite3wal.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3wal.pas)       # write-ahead log
│   ├── [passqlite3btree.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3btree.pas)     # B-tree
│   ├── [passqlite3vdbe.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3vdbe.pas)      # VDBE bytecode interpreter
│   ├── [passqlite3codegen.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3codegen.pas)   # SQL → VDBE code generators (build/select/expr/where/...)
│   ├── [passqlite3parser.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3parser.pas)    # tokenizer + Lemon grammar
│   ├── [passqlite3main.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3main.pas)      # public sqlite3_* API surface
│   ├── [passqlite3backup.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3backup.pas)    # online backup API
│   ├── [passqlite3vtab.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3vtab.pas)      # virtual-table interface
│   ├── [passqlite3json.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3json.pas)      # JSON1 extension
│   ├── [passqlite3jsoneach.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3jsoneach.pas)  # json_each / json_tree table-valued fns
│   ├── [passqlite3carray.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3carray.pas)    # carray() table-valued function
│   ├── [passqlite3dbpage.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3dbpage.pas)    # sqlite_dbpage virtual table
│   ├── [passqlite3dbstat.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/passqlite3dbstat.pas)    # sqlite_dbstat virtual table
│   ├── [csqlite3.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/csqlite3.pas)            # cdecl bindings to libsqlite3.so (tests only)
│   └── [tests/](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/)
│       ├── [build.sh](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/build.sh)                  # build script
│       ├── [vectors/](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/vectors/)                  # canonical .db files and .sql scripts
│       ├── [TestSmoke.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestSmoke.pas)             # build-system health check
│       ├── [TestExplainParity.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestExplainParity.pas)     # primary VDBE-bytecode parity gate
│       ├── [TestParser.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestParser.pas) / [TestSelectBasic.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestSelectBasic.pas) / [TestDMLBasic.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestDMLBasic.pas) /
│       │   [TestWhereBasic.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestWhereBasic.pas) / [TestVdbeAgg.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestVdbeAgg.pas) / [TestSchemaBasic.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestSchemaBasic.pas) /
│       │   [TestVdbeRecord.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestVdbeRecord.pas) / [TestBtreeCompat.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestBtreeCompat.pas) / [TestPager*.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestPager.pas) /
│       │   [TestPCache.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestPCache.pas) / [TestOSLayer.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestOSLayer.pas) / [TestPrepareBasic.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/TestPrepareBasic.pas) / ...
│       │     per-layer differential tests
│       └── [Diag*.pas](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagOps.pas)                 # focused runtime-divergence probes
│                                     ([DiagOps](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagOps.pas), [DiagCast](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagCast.pas), [DiagDate](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagDate.pas), [DiagFunctions](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagFunctions.pas),
│                                      [DiagMoreFunc](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagMoreFunc.pas), [DiagFeatureProbe](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/src/tests/DiagFeatureProbe.pas), ...)
├── bin/                        # compiled test binaries (git-ignored)
├── [install_dependencies.sh](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/install_dependencies.sh)
├── [LICENSE](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/LICENSE)                      # public domain (matching upstream SQLite)
├── [README.md](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/README.md)                    # this file
└── [tasklist.md](https://github.com/joaopauloschuler/pas-sqlite3/blob/HEAD/tasklist.md)                  # detailed phase-by-phase task list

Porting phases

See tasklist.md for the full per-task breakdown.

References

• SQLite upstream: https://sqlite.org/src/
• SQLite file format: https://sqlite.org/fileformat.html
• SQLite VDBE opcodes: https://sqlite.org/opcode.html
• D. Richard Hipp et al., SQLite, public domain.