. "$BST_LIB"
#
# sqlite3 3.45.3 — the FILE-BACKED half. 77-sqlite3.test proves the SQL engine
# against `:memory:` databases and never opens a file; this file is the part that
# only a real filesystem can answer, and on this arm that is the interesting part.
#
# WHY THE SPLIT IS THE POINT, NOT JUST A BUDGET TRICK. An in-memory database
# exercises the parser, the VDBE and the b-tree and touches no syscall beyond
# malloc. A file-backed one goes through SQLite's unix VFS: open/pread/pwrite,
# fcntl(F_SETLK) advisory locking on every transaction, fsync at commit, ftruncate
# and unlink for the rollback journal, and — for WAL — a second file plus a shared
# -shm mapping. Each of those is a wasm32-hwjs syscall that may or may not be
# wired, so keeping the two halves in separate files makes a red ATTRIBUTABLE:
# "77-sqlite3 green, 77-sqlite3-file red" says the VFS, not the engine, and the
# layers below narrow it further. Nothing here is a fixture for the engine's sake;
# every case is a durability or locking question.
#
# THE PROCESS BOUNDARY IS THE ASSERTION, not an implementation detail. A database
# written and read inside one process proves almost nothing about durability — the
# page cache would answer. So the writes and the reads are deliberately in
# DIFFERENT invocations, with the connection closed in between, and the read
# process is the one that must find the bytes on disk. /usr/bin/sqlite3 is a
# ~16 MB wasm module and the runner budgets 60 s per test file, so the reads are
# batched through ATTACH — one process reading five databases — rather than one
# process per file.
#
# THREE SHAPE FACTS, all measured on a native x86_64 control of the identical
# 3.45.3 source before anything ran on target:
#
#   1. `PRAGMA journal_mode=off` REPORTS `delete`, on this build and on the
#      control alike, so the five modes this file covers are delete, truncate,
#      persist, memory and wal. Asserting `off` would have gone red against a
#      correct engine; the measured value is asserted instead.
#
#   2. truncate AND persist LEAVE A -journal FILE BEHIND after a clean exit —
#      that is what distinguishes them from delete, which unlinks it. So the
#      journal-file assertions are per-mode and directional; a blanket "no
#      -journal after exit" would be wrong for two of the five.
#
#   3. WAL COLLAPSES ON THE LAST CONNECTION CLOSE. A cleanly-closed WAL database
#      checkpoints and removes both -wal and -shm, so after the writer exits only
#      the main file is on disk and `PRAGMA journal_mode` still reads `wal` on
#      reopen (the mode is recorded in the file header). The -shm mapping is
#      therefore visible only WHILE a connection is open, which is why the WAL
#      probe here is a second process that both reads and writes the database
#      rather than a directory listing.
#
require sqlite3 sqlite3/file-present

sq_v()  { printf '%s\n' "$2" | awk -F'|' -v k="$1" '$1==k{print $2; exit}'; }
sq_v2() { printf '%s\n' "$2" | awk -F'|' -v k="$1" '$1==k{print $3; exit}'; }
out_of() { "$@" 2>&1 || true; }

T=${TMPDIR:-/tmp}/bst-sqlite3f.$$
rm -rf "$T"; mkdir -p "$T"
# Every case below is relative to $T, so a temp directory that is not writable
# would report as ~40 confusing reds. Say it once instead.
check sqlite3/tmp-writable sh -c "printf x > $T/.w && rm -f $T/.w"

# ------------------------------------------- 1. write a real database (process A)
# A schema with enough shape to make the read-back meaningful: a rowid table with
# an index, a WITHOUT ROWID table, a blob, 5000 rows inserted inside one explicit
# transaction (so this is a single commit, i.e. one fsync + one journal cycle),
# and a persisted user_version and application_id in the file header.
W=$(sqlite3 "$T/main.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
PRAGMA user_version=7;
PRAGMA application_id=305419896;
CREATE TABLE kv(k TEXT PRIMARY KEY, v INTEGER) WITHOUT ROWID;
CREATE TABLE big(id INTEGER PRIMARY KEY, payload TEXT, b BLOB);
CREATE INDEX i_payload ON big(payload);
BEGIN;
INSERT INTO kv VALUES('alpha',1),('beta',2),('gamma',3);
WITH RECURSIVE s(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM s WHERE x<5000)
  INSERT INTO big(id,payload,b) SELECT x, 'row-'||x, zeroblob(64) FROM s;
COMMIT;
SELECT 'wrote', (SELECT count(*) FROM big), (SELECT count(*) FROM kv);
SELECT 'wsum', (SELECT sum(id) FROM big);
SELECT 'wintegrity', (SELECT * FROM pragma_integrity_check());
SELECT 'wpages', (SELECT * FROM pragma_page_count()) > 20;
SQL
)
check_eq sqlite3/write-rows      5000      "$(sq_v wrote "$W")"
check_eq sqlite3/write-norowid   3         "$(sq_v2 wrote "$W")"
check_eq sqlite3/write-sum       12502500  "$(sq_v wsum "$W")"
check_eq sqlite3/write-integrity ok        "$(sq_v wintegrity "$W")"
check_eq sqlite3/write-grew-file 1         "$(sq_v wpages "$W")"
check     sqlite3/db-file-exists test -s "$T/main.db"
# The bytes on disk are a SQLite database, read independently of the tool that
# wrote them: the format-3 magic is the first 16 bytes of the header.
check_out sqlite3/db-file-magic "SQLite format 3" sh -c "head -c 15 '$T/main.db'"
# A clean exit in the default journal mode unlinks the rollback journal. A
# leftover -journal here means the commit path did not complete its cycle.
check sqlite3/no-journal-left sh -c "! test -e '$T/main.db-journal'"

# --------------------------------- 2. read it back in a NEW process (process B)
# This is the durability assertion the whole file exists for: a different process,
# a fresh connection, reading pages that only the filesystem can still hold.
Rd=$(sqlite3 "$T/main.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
SELECT 'rows', count(*) FROM big;
SELECT 'sum', sum(id) FROM big;
SELECT 'kv', v FROM kv WHERE k='beta';
SELECT 'payload', payload FROM big WHERE id=4999;
SELECT 'bloblen', length(b) FROM big WHERE id=1;
SELECT 'uv', (SELECT * FROM pragma_user_version());
SELECT 'appid', (SELECT * FROM pragma_application_id());
SELECT 'integrity', (SELECT * FROM pragma_integrity_check());
SELECT 'idxscan', (SELECT id FROM big WHERE payload='row-1234');
SELECT 'idxlist', (SELECT count(*) FROM pragma_index_list('big') WHERE name='i_payload');
SELECT 'norowid', (SELECT count(*) FROM pragma_table_list WHERE name='kv' AND wr=1);
SELECT 'jmode', (SELECT * FROM pragma_journal_mode());
SELECT 'pagesize', (SELECT * FROM pragma_page_size());
-- A write from this second process: reopening read-only would leave the
-- fcntl(F_SETLK) write-lock path untested, and that is the syscall most likely
-- to be missing on a young arm.
BEGIN;
UPDATE big SET payload='updated' WHERE id=1;
DELETE FROM big WHERE id=5000;
COMMIT;
SELECT 'afterwrite', (SELECT count(*) FROM big), (SELECT payload FROM big WHERE id=1);
SQL
)
check_eq sqlite3/reopen-rows      5000     "$(sq_v rows "$Rd")"
check_eq sqlite3/reopen-sum       12502500 "$(sq_v sum "$Rd")"
check_eq sqlite3/reopen-norowid-v 2        "$(sq_v kv "$Rd")"
check_eq sqlite3/reopen-payload   row-4999 "$(sq_v payload "$Rd")"
check_eq sqlite3/reopen-blob      64       "$(sq_v bloblen "$Rd")"
# user_version and application_id live in the file header, not in a table, so
# these two witness that the header survived the write cycle intact.
check_eq sqlite3/reopen-user-ver  7        "$(sq_v uv "$Rd")"
check_eq sqlite3/reopen-app-id    305419896 "$(sq_v appid "$Rd")"
check_eq sqlite3/reopen-integrity ok       "$(sq_v integrity "$Rd")"
# The index must be USED, not merely present: a lookup by payload with 5000 rows
# is the b-tree on disk answering.
check_eq sqlite3/reopen-index-hit 1234     "$(sq_v idxscan "$Rd")"
check_eq sqlite3/reopen-index-kept 1       "$(sq_v idxlist "$Rd")"
check_eq sqlite3/reopen-without-rowid 1    "$(sq_v norowid "$Rd")"
check_eq sqlite3/reopen-jmode     delete   "$(sq_v jmode "$Rd")"
check_eq sqlite3/reopen-pagesize  4096     "$(sq_v pagesize "$Rd")"
check_eq sqlite3/second-proc-write 4999    "$(sq_v afterwrite "$Rd")"
check_eq sqlite3/second-proc-update updated "$(sq_v2 afterwrite "$Rd")"

# --------------------------- 3. the second process's writes are on disk too (C)
C=$(sqlite3 "$T/main.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
SELECT 'rows', count(*) FROM big;
SELECT 'updated', payload FROM big WHERE id=1;
SELECT 'deleted', count(*) FROM big WHERE id=5000;
SELECT 'integrity', (SELECT * FROM pragma_integrity_check());
SQL
)
check_eq sqlite3/third-proc-rows    4999    "$(sq_v rows "$C")"
check_eq sqlite3/third-proc-update  updated "$(sq_v updated "$C")"
check_eq sqlite3/third-proc-delete  0       "$(sq_v deleted "$C")"
check_eq sqlite3/third-proc-integrity ok    "$(sq_v integrity "$C")"

# --------------------------------------- 4. the five journal modes (process D)
# One process, five databases through ATTACH, one commit each. The journal is the
# rollback mechanism, so each mode is a different sequence of filesystem calls:
# delete unlinks the journal, truncate zeroes it, persist zeroes its header and
# keeps it, memory never creates one, and wal writes a separate log plus a -shm.
J=$(sqlite3 "$T/j-main.db" 2>&1 <<SQL
.mode list
.separator |
.headers off
ATTACH '$T/jd.db' AS jd; ATTACH '$T/jt.db' AS jt; ATTACH '$T/jp.db' AS jp;
ATTACH '$T/jm.db' AS jm; ATTACH '$T/jw.db' AS jw;
PRAGMA jt.journal_mode=truncate;
PRAGMA jp.journal_mode=persist;
PRAGMA jm.journal_mode=memory;
PRAGMA jw.journal_mode=wal;
SELECT 'mdelete',   (SELECT * FROM pragma_journal_mode('jd'));
SELECT 'mtruncate', (SELECT * FROM pragma_journal_mode('jt'));
SELECT 'mpersist',  (SELECT * FROM pragma_journal_mode('jp'));
SELECT 'mmemory',   (SELECT * FROM pragma_journal_mode('jm'));
SELECT 'mwal',      (SELECT * FROM pragma_journal_mode('jw'));
CREATE TABLE jd.t(a); INSERT INTO jd.t VALUES(1);
CREATE TABLE jt.t(a); INSERT INTO jt.t VALUES(2);
CREATE TABLE jp.t(a); INSERT INTO jp.t VALUES(3);
CREATE TABLE jm.t(a); INSERT INTO jm.t VALUES(4);
CREATE TABLE jw.t(a); INSERT INTO jw.t VALUES(5);
SELECT 'jsum', (SELECT a FROM jd.t)+(SELECT a FROM jt.t)+(SELECT a FROM jp.t)
              +(SELECT a FROM jm.t)+(SELECT a FROM jw.t);
SQL
)
check_eq sqlite3/jmode-delete   delete   "$(sq_v mdelete "$J")"
check_eq sqlite3/jmode-truncate truncate "$(sq_v mtruncate "$J")"
check_eq sqlite3/jmode-persist  persist  "$(sq_v mpersist "$J")"
check_eq sqlite3/jmode-memory   memory   "$(sq_v mmemory "$J")"
# Expected RED on wasm32, and left red on purpose. The image builds sqlite3 with
# SQLITE_DEFAULT_UNIX_VFS="unix-dotfile" because the default unix VFS cannot lock
# a database file here (brintos/linux#16 — sys_fcntl64 unwired, so every LFS
# F_SETLK64 is EINVAL). Dotfile locking has no xShmMap, so WAL cannot engage and
# journal_mode reports `delete`. This case and wal-mode-sticky below are the
# signal that the workaround in recipes-support/sqlite/sqlite3_%.bbappend can be
# removed: they go green by themselves once the kernel grants the lock.
check_eq sqlite3/jmode-wal      wal      "$(sq_v mwal "$J")"
check_eq sqlite3/jmode-all-wrote 15      "$(sq_v jsum "$J")"
# Shape fact 2: truncate and persist keep their journal, delete and memory do not.
# Directional on purpose — this is where a VFS that cannot unlink, or cannot
# ftruncate, shows up as a specific pair rather than a vague failure.
check sqlite3/jfile-delete-gone   sh -c "! test -e '$T/jd.db-journal'"
check sqlite3/jfile-memory-gone   sh -c "! test -e '$T/jm.db-journal'"
check sqlite3/jfile-truncate-kept test -e "$T/jt.db-journal"
check sqlite3/jfile-persist-kept  test -e "$T/jp.db-journal"
# Shape fact 3: a cleanly closed WAL database has checkpointed and removed both
# sidecars, so their absence here is correctness rather than a missing write.
check sqlite3/wal-checkpointed    sh -c "! test -e '$T/jw.db-wal'"
check sqlite3/wal-shm-removed     sh -c "! test -e '$T/jw.db-shm'"

# ---------------------- 5. every journal mode survives its process (process E)
# The read-back for all five, again through one ATTACH set. jw is the WAL case and
# it is also this file's -shm probe: a WAL reopen must create and map <db>-shm to
# read the log index, and this process then WRITES through WAL as well, which is
# the path that needs the shared mapping to be genuinely shared.
K=$(sqlite3 "$T/k-main.db" 2>&1 <<SQL
.mode list
.separator |
.headers off
ATTACH '$T/jd.db' AS jd; ATTACH '$T/jt.db' AS jt; ATTACH '$T/jp.db' AS jp;
ATTACH '$T/jm.db' AS jm; ATTACH '$T/jw.db' AS jw;
SELECT 'rdelete',   (SELECT a FROM jd.t);
SELECT 'rtruncate', (SELECT a FROM jt.t);
SELECT 'rpersist',  (SELECT a FROM jp.t);
SELECT 'rmemory',   (SELECT a FROM jm.t);
SELECT 'rwal',      (SELECT a FROM jw.t);
SELECT 'rwalmode',  (SELECT * FROM pragma_journal_mode('jw'));
INSERT INTO jw.t VALUES(50);
SELECT 'rwalwrite', (SELECT sum(a) FROM jw.t);
SELECT 'rwalcheck', (SELECT * FROM pragma_integrity_check(NULL,'jw'));
SELECT 'rdcheck',   (SELECT * FROM pragma_integrity_check(NULL,'jd'));
SELECT 'rtcheck',   (SELECT * FROM pragma_integrity_check(NULL,'jt'));
SELECT 'rpcheck',   (SELECT * FROM pragma_integrity_check(NULL,'jp'));
SELECT 'rmcheck',   (SELECT * FROM pragma_integrity_check(NULL,'jm'));
SQL
)
check_eq sqlite3/persist-delete   1  "$(sq_v rdelete "$K")"
check_eq sqlite3/persist-truncate 2  "$(sq_v rtruncate "$K")"
check_eq sqlite3/persist-persist  3  "$(sq_v rpersist "$K")"
check_eq sqlite3/persist-memory   4  "$(sq_v rmemory "$K")"
check_eq sqlite3/persist-wal      5  "$(sq_v rwal "$K")"
# The mode is recorded in the database header, so it outlives the connection that
# set it. Expected RED here for the same reason as jmode-wal above: the header
# never recorded `wal`, because the dotfile VFS this image defaults to
# (brintos/linux#16) cannot engage WAL in the first place.
check_eq sqlite3/wal-mode-sticky  wal "$(sq_v rwalmode "$K")"
check_eq sqlite3/wal-second-write 55  "$(sq_v rwalwrite "$K")"
check_eq sqlite3/wal-integrity    ok  "$(sq_v rwalcheck "$K")"
check_eq sqlite3/jd-integrity     ok  "$(sq_v rdcheck "$K")"
check_eq sqlite3/jt-integrity     ok  "$(sq_v rtcheck "$K")"
check_eq sqlite3/jp-integrity     ok  "$(sq_v rpcheck "$K")"
check_eq sqlite3/jm-integrity     ok  "$(sq_v rmcheck "$K")"

# ------------------------------------------- 6. rollback really rolls back (F)
# An aborted transaction must leave the FILE untouched, which is the journal's
# only job. Split across two processes so the evidence is on disk, not in a cache:
# F writes and rolls back, G reads what survived.
F=$(sqlite3 "$T/rb.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
CREATE TABLE t(a INTEGER PRIMARY KEY, s TEXT);
INSERT INTO t VALUES(1,'keep'),(2,'keep');
BEGIN;
INSERT INTO t VALUES(3,'gone'),(4,'gone');
UPDATE t SET s='clobbered' WHERE a=1;
DELETE FROM t WHERE a=2;
SELECT 'inside', count(*) FROM t;
ROLLBACK;
SELECT 'after', count(*) FROM t;
SELECT 'restored', s FROM t WHERE a=1;
SQL
)
check_eq sqlite3/rollback-inside   3      "$(sq_v inside "$F")"
check_eq sqlite3/rollback-after    2      "$(sq_v after "$F")"
check_eq sqlite3/rollback-restored keep   "$(sq_v restored "$F")"
G=$(sqlite3 "$T/rb.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
SELECT 'rows', count(*) FROM t;
SELECT 'val', s FROM t WHERE a=1;
SELECT 'gone', count(*) FROM t WHERE s='gone';
SELECT 'integrity', (SELECT * FROM pragma_integrity_check());
SQL
)
check_eq sqlite3/rollback-ondisk-rows 2    "$(sq_v rows "$G")"
check_eq sqlite3/rollback-ondisk-val  keep "$(sq_v val "$G")"
check_eq sqlite3/rollback-ondisk-gone 0    "$(sq_v gone "$G")"
check_eq sqlite3/rollback-ondisk-ok   ok   "$(sq_v integrity "$G")"

# ------------------------------------------------- 7. locking and permissions
# locking_mode=exclusive keeps the fcntl write lock for the whole session instead
# of taking and dropping it per transaction — a different use of the same syscall.
L=$(sqlite3 "$T/lk.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
PRAGMA locking_mode=exclusive;
SELECT 'mode', (SELECT * FROM pragma_locking_mode());
CREATE TABLE t(a); INSERT INTO t VALUES(1),(2);
SELECT 'rows', count(*) FROM t;
SQL
)
check_eq sqlite3/locking-exclusive exclusive "$(sq_v mode "$L")"
check_eq sqlite3/locking-wrote     2         "$(sq_v rows "$L")"
check_out sqlite3/locking-reopen "2" sqlite3 "$T/lk.db" 'select count(*) from t;'

# A read-only database must still READ, and a write through it must fail with
# SQLITE_READONLY (8) rather than succeeding or crashing. The pair is the point:
# either half alone would pass on a VFS that ignores read-only state entirely.
#
# The read half is the mode bits; the write half is NOT. This runner is uid 0 and
# root holds CAP_DAC_OVERRIDE, so opening a 0444 file O_RDWR succeeds — correctly,
# per POSIX — and a chmod-based write assertion tests the kernel's DAC rather than
# SQLite, then fails on any all-root image. -readonly asks SQLite itself to refuse,
# which is the behaviour actually under test and holds at every uid.
chmod 444 "$T/lk.db"
check_out sqlite3/readonly-read "2" sqlite3 "$T/lk.db" 'select count(*) from t;'
chmod 644 "$T/lk.db"
check_rc  sqlite3/readonly-write-rc 8 sqlite3 -readonly "$T/lk.db" 'insert into t values(3);'
check_out sqlite3/readonly-write-msg "attempt to write a readonly database" \
          out_of sqlite3 -readonly "$T/lk.db" 'insert into t values(3);'
check_out sqlite3/readwrite-restored "3" \
          sh -c "sqlite3 '$T/lk.db' 'insert into t values(3); select count(*) from t;'"

# ------------------------------------------------------- 8. temp-file spilling
# temp_store=FILE forces the sorter to spill to a temporary file instead of the
# heap, so this exercises create-write-read-unlink of an unnamed temp file in the
# VFS's temp directory — a path nothing else in these suites touches. 20000 rows
# is well past the sorter's in-memory budget.
Sp=$(sqlite3 "$T/sp.db" 2>&1 <<'SQL'
.mode list
.separator |
.headers off
PRAGMA temp_store=FILE;
SELECT 'store', (SELECT * FROM pragma_temp_store());
CREATE TABLE big(a INTEGER, t TEXT);
WITH RECURSIVE s(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM s WHERE x<20000)
  INSERT INTO big SELECT x, 'p'||x FROM s;
SELECT 'sorted', (SELECT count(*) FROM (SELECT a FROM big ORDER BY t DESC));
SELECT 'first', (SELECT t FROM big ORDER BY t DESC LIMIT 1);
SELECT 'distinct', (SELECT count(*) FROM (SELECT DISTINCT t FROM big));
SELECT 'grouped', (SELECT count(*) FROM (SELECT a%97 g, count(*) FROM big GROUP BY g));
SQL
)
check_eq sqlite3/tempstore-file  1     "$(sq_v store "$Sp")"
check_eq sqlite3/temp-spill-sort 20000 "$(sq_v sorted "$Sp")"
check_eq sqlite3/temp-spill-first p9999 "$(sq_v first "$Sp")"
check_eq sqlite3/temp-spill-distinct 20000 "$(sq_v distinct "$Sp")"
check_eq sqlite3/temp-spill-group 97   "$(sq_v grouped "$Sp")"
# The temp files must be gone once the process is: a sorter that leaks them fills
# a browser machine's tmpfs on the second query.
check sqlite3/no-temp-litter sh -c "! ls '$T'/etilqs_* >/dev/null 2>&1"

rm -rf "$T"