#!/usr/bin/env bash
# Reproduce rollback journal behavior for SQLite kernel article 07:
# default journal_mode, DELETE/TRUNCATE/PERSIST commit points, and
# hot-journal crash recovery after a cache spill.
# Environment: Linux/WSL2, sqlite3 3.53.2 CLI + python3 sqlite3 module (same libsqlite3 version).
set -euo pipefail
DIR="$(cd "$(dirname "$0")" && pwd)"
cd "$DIR"

echo "=== version ==="
sqlite3 --version
python3 -c "import sqlite3; print('python sqlite3 module ->', sqlite3.sqlite_version)"

echo
echo "=== default journal_mode on a fresh database ==="
rm -f sk-jr-default.db sk-jr-default.db-*
sqlite3 sk-jr-default.db "PRAGMA journal_mode;"

echo
echo "=== DELETE mode: journal file appears mid-transaction, gone after commit ==="
rm -f sk-jr-delete.db sk-jr-delete.db-*
python3 - <<'PY'
import sqlite3, os
con = sqlite3.connect("sk-jr-delete.db", isolation_level=None)
con.execute("PRAGMA journal_mode=DELETE;")
con.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT);")
con.execute("BEGIN IMMEDIATE;")
con.execute("INSERT INTO t VALUES(1,'a');")
print("mid-transaction files:", sorted(f for f in os.listdir('.') if f.startswith('sk-jr-delete.db')))
con.execute("COMMIT;")
print("post-commit files:   ", sorted(f for f in os.listdir('.') if f.startswith('sk-jr-delete.db')))
con.close()
PY

echo
echo "=== TRUNCATE vs PERSIST commit points (file stays, header cleared) ==="
for MODE in DELETE TRUNCATE PERSIST; do
  rm -f sk-jr-mode.db sk-jr-mode.db-*
  python3 - "$MODE" <<'PY'
import sqlite3, os, sys
mode = sys.argv[1]
con = sqlite3.connect("sk-jr-mode.db", isolation_level=None)
got = con.execute(f"PRAGMA journal_mode={mode};").fetchone()[0]
con.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT);")
con.execute("BEGIN IMMEDIATE;")
con.execute("INSERT INTO t VALUES(1,'a');")
con.execute("COMMIT;")
post = sorted(f for f in os.listdir('.') if f.startswith('sk-jr-mode.db'))
size = os.path.getsize("sk-jr-mode.db-journal") if os.path.exists("sk-jr-mode.db-journal") else None
print(f"{got:9s} post-commit files={post} journal_size_after_commit={size}")
con.close()
PY
done

echo
echo "=== hot journal crash recovery (small cache_size forces a cache spill) ==="
rm -f sk-jr-crash.db sk-jr-crash.db-*
sqlite3 sk-jr-crash.db "PRAGMA page_size=4096; CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT); INSERT INTO t VALUES(1,'orig');"
cat > /tmp/sk_crash_writer.py <<'PY'
import sqlite3, sys, time
con = sqlite3.connect("sk-jr-crash.db", isolation_level=None)
con.execute("PRAGMA journal_mode=DELETE;")
con.execute("PRAGMA cache_size=2;")
con.execute("BEGIN IMMEDIATE;")
for i in range(2, 500):
    con.execute("INSERT INTO t VALUES(?, ?)", (i, "y" * 100))
con.execute("UPDATE t SET v='mid-crash' WHERE id=1;")
sys.stdout.write("wrote-uncommitted-many-rows\n")
sys.stdout.flush()
time.sleep(30)
PY
python3 /tmp/sk_crash_writer.py &
WPID=$!
sleep 1
echo "-- files while writer holds an uncommitted, spilled transaction --"
ls -la sk-jr-crash.db*
echo "-- journal header magic (should be the 8-byte file-format magic, not zero) --"
xxd -l 8 sk-jr-crash.db-journal
echo "-- kill -9 the writer to simulate a crash mid-commit --"
kill -9 "$WPID"
sleep 1
echo "-- files right after the crash (hot journal left behind) --"
ls -la sk-jr-crash.db*
echo "-- next open triggers automatic rollback recovery before the read returns --"
sqlite3 sk-jr-crash.db "SELECT v FROM t WHERE id=1; SELECT count(*) FROM t;"
echo "-- files after recovery (journal deleted, database back to pre-crash size) --"
ls -la sk-jr-crash.db*