. "$BST_LIB" # # sqlite3 3.45.3 — the ADMIN half: the operations an operator runs ON a database # rather than queries they run IN one. 77-sqlite3.test covers the engine # (`:memory:` only) and 77-sqlite3-file.test covers durability and locking; this # file covers backup, dump/reload, VACUUM, ANALYZE, the multi-file transaction, # and the shell's file-facing dot commands. # # WHY THESE ARE WORTH THEIR OWN CASES ON THIS ARM. Each one is a filesystem shape # the query path never produces: # # - .backup drives the sqlite3_backup_* C API — a page-by-page copy between two # open connections — which is a completely different code path from .dump's # "print SQL and re-parse it". Both are asserted, because a port can have one # and not the other. # - VACUUM rebuilds the database into a temporary file and then swaps the # content back in, so it is the heaviest single filesystem operation SQLite # performs. VACUUM INTO is its cheaper sibling that writes a fresh file. # - A TRANSACTION SPANNING TWO ATTACHED FILES makes SQLite write a MASTER # JOURNAL — a separate file naming both databases' journals — because the # commit has to be atomic across both. Nothing else in these suites produces # one, and it needs create, write, fsync and unlink of a third file in the same # directory as the databases. # - .import / .output / .read / readfile / writefile are the shell's own file # I/O, independent of the VFS: plain fopen/fread/fwrite through glibc. # # ONE SHAPE FACT worth stating up front, measured on the native control: .import # NEEDS ITS MODE SET FIRST. `sqlite3 db '.import f.csv t'` with the default list # mode splits on the list separator, not on commas, so the CSV lands in one # column; `-cmd '.mode csv'` before it is the working spelling and is what the # case below uses. The natural-looking form is the broken one. # require sqlite3 sqlite3/admin-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-sqlite3a.$$ rm -rf "$T"; mkdir -p "$T" check sqlite3/admin-tmp-writable sh -c "printf x > $T/.w && rm -f $T/.w" # The source database every operation below copies, rebuilds or exports. Built # once, with an index and a blob so the copies have something structural to lose. B=$(sqlite3 "$T/src.db" 2>&1 <<'SQL' .mode list .separator | .headers off CREATE TABLE t(id INTEGER PRIMARY KEY, s TEXT, b BLOB); CREATE INDEX i_s ON t(s); CREATE VIEW v AS SELECT id FROM t WHERE id < 100; BEGIN; WITH RECURSIVE r(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM r WHERE x<2000) INSERT INTO t(id,s,b) SELECT x, 'v'||x, zeroblob(32) FROM r; COMMIT; -- leave free pages behind so VACUUM has something to reclaim DELETE FROM t WHERE id % 2 = 0; SELECT 'rows', count(*) FROM t; SELECT 'sum', sum(id) FROM t; SELECT 'free', (SELECT * FROM pragma_freelist_count()) > 0; SQL ) check_eq sqlite3/src-rows 1000 "$(sq_v rows "$B")" check_eq sqlite3/src-sum 1000000 "$(sq_v sum "$B")" check_eq sqlite3/src-freelist 1 "$(sq_v free "$B")" # ------------------------------------------------------------- 1. .backup / API # One process: back up, then ATTACH the copy and read it. The ATTACH is what makes # this an assertion about the FILE rather than about the command's exit status. BK=$(sqlite3 "$T/src.db" 2>&1 < "$T/dump.sql" 2>"$T/dump.err" check sqlite3/dump-wrote test -s "$T/dump.sql" check sqlite3/dump-no-stderr sh -c "! test -s '$T/dump.err'" check_out sqlite3/dump-transaction "BEGIN TRANSACTION;" cat "$T/dump.sql" check_out sqlite3/dump-schema "CREATE TABLE t(id INTEGER PRIMARY KEY, s TEXT, b BLOB);" cat "$T/dump.sql" check_out sqlite3/dump-index "CREATE INDEX i_s ON t(s);" cat "$T/dump.sql" check_out sqlite3/dump-view "CREATE VIEW v AS" cat "$T/dump.sql" check_out sqlite3/dump-commit "COMMIT;" cat "$T/dump.sql" # Blobs must come out as X'..' literals; a dump that stringified them would # reload without error and silently corrupt every blob column. check_out sqlite3/dump-blob-literal "X'0000" cat "$T/dump.sql" check sqlite3/dump-row-count sh -c \ "[ \$(grep -c '^INSERT INTO' '$T/dump.sql') -eq 1000 ]" # Reload into a fresh database in ONE process, then verify it there. RL=$(sh -c "sqlite3 '$T/rl.db' < '$T/dump.sql'" 2>&1 && sqlite3 "$T/rl.db" 2>&1 <&1)" # ------------------------------------------------------ 3. VACUUM / VACUUM INTO # VACUUM rebuilds through a temp database and swaps the content back, so it is the # heaviest filesystem operation here. Measured before and after: the file must # actually shrink, otherwise the free pages were never reclaimed and VACUUM was a # no-op that returned 0. SZ_BEFORE=$(wc -c < "$T/src.db") V=$(sqlite3 "$T/src.db" 2>&1 </dev/null 2>&1" # --------------------------------------------------------------- 4. ANALYZE A=$(sqlite3 "$T/rl.db" 2>&1 <<'SQL' .mode list .separator | .headers off ANALYZE; SELECT 'stat', (SELECT count(*) FROM sqlite_stat1); SELECT 'statidx', (SELECT count(*) FROM sqlite_stat1 WHERE idx='i_s'); SELECT 'planused', (SELECT count(*) FROM pragma_index_list('t') WHERE name='i_s'); SELECT 'integrity', (SELECT * FROM pragma_integrity_check()); SQL ) check_eq sqlite3/analyze-stat1 1 "$(sq_v stat "$A")" check_eq sqlite3/analyze-index 1 "$(sq_v statidx "$A")" check_eq sqlite3/analyze-integrity ok "$(sq_v integrity "$A")" # ------------------------------- 5. a transaction across TWO attached databases # This is the master-journal path: an atomic commit spanning two separate files. # SQLite writes a third file naming both journals, fsyncs it, then unlinks it on # success — so a green here means create/write/fsync/unlink all work on a path the # single-file cases never produce, and the rollback half proves the master journal # is actually consulted rather than merely written. M=$(sqlite3 "$T/m1.db" 2>&1 </dev/null 2>&1" # --------------------------------------------- 6. the shell's own file commands # .import with the mode set first (the shape fact above), and a header row that # must become column names rather than a data row. printf 'p,q\n1,2\n3,4\n5,6\n' > "$T/in.csv" # The import and the verification are two processes on purpose, and not only for # durability: given a SQL/dot-command ARGUMENT the shell never reads stdin, so a # heredoc bolted onto this invocation would be silently discarded and every case # below would go red against a working .import. (It did, in a first draft.) sqlite3 "$T/imp.db" -cmd '.mode csv' ".import '$T/in.csv' imp" >/dev/null 2>&1 I=$(sqlite3 "$T/imp.db" 2>&1 <<'SQL' .mode list .separator | .headers off SELECT 'rows', count(*) FROM imp; SELECT 'cols', (SELECT count(*) FROM pragma_table_info('imp')); SELECT 'colname', (SELECT name FROM pragma_table_info('imp') WHERE cid=0); SELECT 'value', (SELECT q FROM imp WHERE p='3'); SQL ) check_eq sqlite3/import-rows 3 "$(sq_v rows "$I")" check_eq sqlite3/import-cols 2 "$(sq_v cols "$I")" check_eq sqlite3/import-header p "$(sq_v colname "$I")" check_eq sqlite3/import-value 4 "$(sq_v value "$I")" # .output redirects the shell's own stdout to a file it opens itself. sqlite3 "$T/imp.db" ".output '$T/out.txt'" "select 'redirected-'||count(*) from imp;" >/dev/null 2>&1 check sqlite3/output-file test -s "$T/out.txt" check_out sqlite3/output-content "redirected-3" cat "$T/out.txt" # .read executes a script from a file — the shell reading SQL rather than being # handed it, which is a different path from stdin. printf 'select 7*6;\nselect count(*) from imp;\n' > "$T/script.sql" check_out sqlite3/read-script-1 "42" sqlite3 "$T/imp.db" ".read '$T/script.sql'" check_out sqlite3/read-script-2 "3" sqlite3 "$T/imp.db" ".read '$T/script.sql'" check_rc sqlite3/read-missing 1 sqlite3 :memory: ".read $T/nosuch.sql" # readfile()/writefile() are shell-provided SQL functions, so this is binary file # I/O driven from inside a query — the round trip must be byte-exact. printf 'BLOB-PAYLOAD-\001\002\003' > "$T/bin.in" RW=$(sqlite3 "$T/rw.db" 2>&1 <