/** * The module worker behind johnmcm.com's /sqlite-repair. One request at a * time; the page ends the worker to stop one (Stop, or its own time limit), * and every request carries what it needs to rebuild the worker's state, so * the next request after a restart simply opens the file again. * * Message in: { id, op, input: { key, db: File, wal?: File, journal?: File }, * result?: { key, which, blob }, ...op-specific } * Message out: { id, progress: {...} } now and then, then * { id, ok: true, ...result } or { id, ok: false, error, code } * * Files. The database the page was given is mounted with WORKERFS, which * reads the File a slice at a time, and opened read-only and immutable, so * it is never copied into memory and nothing can write to it. With a -wal or * -journal file it has to be opened for writing (SQLite applies the WAL, or * rolls back the unfinished transaction, as it reads), so both are copied * into memory (MEMFS) first, up to COPY_LIMIT. A recovered or cleaned copy * is built in memory, handed to the page as a Blob, removed from memory, and * the Blob mounted read-only for previews and the SQL download. * * Written for johnmcm.com (2026); published at /source/sqlite-recover/. */ import createSqliteRecover from './sqlite-recover.mjs'; import { Engine, SqlError, STAGES, readHeader, entropy, OPEN_READONLY, OPEN_READWRITE } from './engine.mjs'; /** The most a -wal or -journal file and its database may add up to (they are copied into memory). */ const COPY_LIMIT = 1024 * 1024 * 1024; /** Counting rows when a file is opened stops after this long in all. */ const COUNT_BUDGET_MS = 30_000; const COUNT_ONE_MS = 8_000; const LAF = 'lost_and_found'; /** A recovery stops when its output grows past OUTPUT_FACTOR times the input (and at least OUTPUT_FLOOR). */ const OUTPUT_FACTOR = 4; const OUTPUT_FLOOR = 256 * 1024 * 1024; let ready = null; let eng = null; let FS = null; let WORKERFS = null; let mounts = 0; /** The open input: { key, db, dir, copied, info }. */ let input = null; /** Recovered and cleaned copies, mounted from their Blobs: which -> { key, db, dir }. */ const results = new Map(); let reqId = 0; /** Ends a request with an error code the page has a sentence for. */ function fail(code, message) { const e = new Error(message || code); e.jmCode = code; throw e; } function boot() { ready ??= createSqliteRecover({ print() {}, printErr() {}, locateFile: (f) => new URL(f, import.meta.url).href }).then((m) => { FS = m.FS; WORKERFS = m.WORKERFS; eng = new Engine(m); for (const d of ['/w', '/out']) FS.mkdir(d); return m; }); return ready; } const reader = new FileReaderSync(); const readBytes = (blob, start, end) => new Uint8Array(reader.readAsArrayBuffer(blob.slice(start, end))); function mount(blobs) { const dir = `/m${++mounts}`; FS.mkdir(dir); FS.mount(WORKERFS, { blobs }, dir); return dir; } function unmount(dir) { try { FS.unmount(dir); FS.rmdir(dir); } catch { /* already gone */ } } function rmQuiet(path) { try { FS.unlink(path); } catch { /* not there */ } } function closeInput() { if (!input) return; eng.close(input.db); if (input.dir) unmount(input.dir); for (const f of ['/w/main.db', '/w/main.db-wal', '/w/main.db-journal', '/w/main.db-shm']) rmQuiet(f); input = null; } function closeResult(which) { const r = results.get(which); if (!r) return; eng.close(r.db); unmount(r.dir); results.delete(which); } /** Opens the input described by spec unless it is already open. */ function useInput(spec) { if (input && input.key === spec.key) return input; closeInput(); for (const which of [...results.keys()]) closeResult(which); const file = spec.db; const head = readBytes(file, 0, 4096); const header = readHeader(head.subarray(0, 100), file.size); const info = { header, size: file.size, encrypted: !header.magicOk && head.length >= 1024 && entropy(head) > 7.5, applied: null, extraSize: (spec.wal ?? spec.journal)?.size ?? 0, }; const extra = spec.wal ?? spec.journal ?? null; let db; let dir = null; if (extra) { if (file.size + extra.size > COPY_LIMIT) fail('copy-limit'); FS.writeFile('/w/main.db', readBytes(file, 0, file.size)); FS.writeFile(spec.wal ? '/w/main.db-wal' : '/w/main.db-journal', readBytes(extra, 0, extra.size)); db = eng.open('file:/w/main.db', OPEN_READWRITE); try { // The WAL's index kept in this connection's memory: no -shm file is needed. eng.exec(db, 'PRAGMA locking_mode = EXCLUSIVE'); eng.limit(60_000, () => eng.scalar(db, 'SELECT count(*) FROM sqlite_schema')); info.applied = spec.wal ? 'wal' : 'journal'; } catch (e) { info.applied = null; info.applyError = e instanceof SqlError ? e.message : String(e); } } else { dir = mount([{ name: 'main.db', data: file }]); db = eng.open(`file:${dir}/main.db?immutable=1`, OPEN_READONLY); } input = { key: spec.key, db, dir, info, uri: extra ? 'file:/w/main.db' : `file:${dir}/main.db?immutable=1`, flags: extra ? OPEN_READWRITE : OPEN_READONLY, exclusive: !!extra }; return input; } /** * Opens the input again on a fresh connection. The recovery extension leaves * the connection it read with in an odd state (its schema cache reset, with * writable_schema on), so later previews and checks use a new one. */ function reopenInput() { if (!input) return; eng.close(input.db); input.db = eng.open(input.uri, input.flags); if (input.exclusive) eng.exec(input.db, 'PRAGMA locking_mode = EXCLUSIVE'); } /** Mounts a recovered or cleaned copy the page holds, unless it already is. */ function useResult(spec) { const cur = results.get(spec.which); if (cur && cur.key === spec.key) return cur; closeResult(spec.which); const dir = mount([{ name: 'r.db', data: spec.blob }]); const db = eng.open(`file:${dir}/r.db?immutable=1`, OPEN_READONLY); const r = { key: spec.key, db, dir }; results.set(spec.which, r); return r; } /** Tables and views from the schema, counted within the budget. */ function listTables(db, { count = true, budget = COUNT_BUDGET_MS } = {}) { let schema; try { schema = eng.limit(20_000, () => eng.schema(db)); } catch (e) { if (!(e instanceof SqlError)) throw e; return { tables: [], schemaError: e.message, damaged: true }; } const start = performance.now(); const tables = []; for (const it of schema.items) { if (it.type !== 'table' && it.type !== 'view') continue; const t = { name: it.name, type: it.virtual ? 'virtual' : it.type, sql: it.sql.slice(0, 4000), rows: null, error: null }; if (count && it.type === 'table' && !it.virtual) { const left = budget - (performance.now() - start); if (left > 50) { const c = eng.count(db, it.name, Math.min(COUNT_ONE_MS, left)); if ('rows' in c) t.rows = c.rows; else t.error = c.slow ? 'slow' : c.error; } else { t.error = 'slow'; } } tables.push(t); } const indexes = schema.items.filter((i) => i.type === 'index').length; const triggers = schema.items.filter((i) => i.type === 'trigger').length; return { tables, damaged: schema.damaged, indexes, triggers }; } /** Takes a file built in /out into a Blob and frees its memory. */ function takeFile(path, type) { const node = FS.lookupPath(path).node; const bytes = node.contents ? node.contents.subarray(0, node.usedBytes) : new Uint8Array(0); const blob = new Blob([bytes], { type }); rmQuiet(path); rmQuiet(path + '-journal'); return blob; } function fileSize(path) { try { return FS.stat(path).size; } catch { return 0; } } function post(id, msg) { self.postMessage({ id, ...msg }); } /** Progress from inside long statements: elapsed time, and bytes written to out if given. */ function ticker(id, extra) { const start = performance.now(); let last = 0; return (now) => { if (now - last < 400) return; last = now; post(id, { progress: { ms: now - start, ...(extra ? extra() : {}) } }); }; } const ops = { open(msg, id) { const inp = useInput(msg.input); eng.onTick = ticker(id); const list = listTables(inp.db, { count: msg.count !== false }); let journalMode = null; let pageCount = null; let freelist = null; try { journalMode = eng.limit(5_000, () => eng.scalar(inp.db, 'PRAGMA journal_mode')); pageCount = eng.limit(5_000, () => eng.scalar(inp.db, 'PRAGMA page_count')); freelist = eng.limit(5_000, () => eng.scalar(inp.db, 'PRAGMA freelist_count')); } catch { /* a damaged header: the parts read without SQLite still show */ } return { info: inp.info, ...list, journalMode, pageCount, freelist, version: eng.version }; }, check(msg, id) { const inp = useInput(msg.input); eng.onTick = ticker(id); return { check: eng.check(inp.db, msg.kind === 'integrity' ? 'integrity' : 'quick', { max: msg.max ?? 100, ms: msg.ms ?? 300_000 }) }; }, recover(msg, id) { const inp = useInput(msg.input); closeResult('recovered'); const out = '/out/recovered.db'; rmQuiet(out); const start = performance.now(); let stage = 0; let table = ''; let steps = 0; const progress = ticker(id, () => ({ stage: STAGES[stage] ?? '', table, steps, written: fileSize(out) })); // A real recovery writes about as much as the file holds. One that keeps // growing is SQL from a crafted schema (CREATE TABLE ... AS SELECT over an // endless query) filling this tab's memory: stop it well before that. const cap = Math.max(OUTPUT_FLOOR, OUTPUT_FACTOR * (inp.info.size + (inp.info.extraSize ?? 0))); let tooBig = false; let lastSize = 0; const tick = (now) => { if (now - lastSize > 200) { lastSize = now; if (fileSize(out) > cap) { tooBig = true; eng.stopped = true; } } progress(now); }; eng.onTick = tick; let r; try { r = eng.recover(inp.db, out, { laf: msg.lostAndFound === false ? null : LAF, freelistCorrupt: !!msg.freelistCorrupt, ms: msg.ms ?? 1_800_000, progress: (s, t, n) => { stage = s; table = t; steps = n; tick(performance.now()); }, }); } finally { eng.stopped = false; reopenInput(); } if (!r.ok) { rmQuiet(out); rmQuiet(out + '-journal'); fail(tooBig ? 'too-big' : r.interrupted ? 'timeout' : 'recover', r.error); } stage = 5; table = ''; let search = { rebuilt: [], failed: [] }; const w = eng.open(`file:${out}`, OPEN_READWRITE); try { search = eng.rebuildSearch(w); } finally { eng.close(w); } return { ...summarise(out, 'recovered', performance.now() - start, id), search }; }, vacuum(msg, id) { const inp = useInput(msg.input); closeResult('clean'); const out = '/out/clean.db'; rmQuiet(out); const start = performance.now(); eng.onTick = ticker(id, () => ({ written: fileSize(out) })); try { eng.vacuumInto(inp.db, out, { ms: msg.ms ?? 1_800_000 }); } catch (e) { rmQuiet(out); rmQuiet(out + '-journal'); throw e; } return summarise(out, 'clean', performance.now() - start, id); }, preview(msg, id) { const db = msg.result ? useResult(msg.result).db : useInput(msg.input).db; eng.onTick = null; return { preview: eng.preview(db, msg.table, { rows: msg.rows ?? 20, maxText: msg.maxText ?? 200, ms: 10_000 }) }; }, /** Counts one table that was too slow to count when the file was opened. */ count(msg, id) { const db = msg.result ? useResult(msg.result).db : useInput(msg.input).db; eng.onTick = ticker(id); return { count: eng.count(db, msg.table, msg.ms ?? 120_000) }; }, dump(msg, id) { const { db } = useResult(msg.result); const parts = []; let bytes = 0; eng.onTick = ticker(id, () => ({ written: bytes })); const r = eng.dump(db, (s) => { const b = new Blob([s]); bytes += b.size; parts.push(b); }, { laf: null, ms: msg.ms ?? 1_800_000 }); // As after a recovery, the connection it read is not used again. closeResult(msg.result.which); if (!r.ok) fail(r.interrupted ? 'timeout' : 'dump', r.error); return { blob: new Blob(parts, { type: 'application/sql' }), size: bytes }; }, }; /** Opens a finished copy, lists and counts its tables, and hands it over as a Blob. */ function summarise(out, which, ms, id) { eng.onTick = ticker(id, () => ({ stage: 'Counting rows' })); const db = eng.open(`file:${out}?immutable=1`, OPEN_READONLY); let list; let check; try { list = listTables(db, { budget: 120_000 }); try { check = eng.check(db, 'quick', { max: 20, ms: 120_000 }); } catch (e) { check = { ok: false, messages: [e instanceof SqlError ? e.message : String(e)], capped: false }; } } finally { eng.close(db); } const blob = takeFile(out, 'application/vnd.sqlite3'); return { blob, size: blob.size, ms, ...list, check }; } // Stop ends the worker from the page, so a statement the worker sees // interrupted (SqlError.interrupted, or a recovery that reports it) ran past // its time limit: those are reported as 'timeout'. self.onmessage = async (e) => { const msg = e.data ?? {}; const id = msg.id ?? ++reqId; try { await boot(); } catch (err) { ready = null; post(id, { ok: false, error: String(err?.message ?? err), code: 'load' }); return; } try { const fn = ops[msg.op]; if (!fn) throw new Error('unknown request'); eng.stopped = false; const r = fn(msg, id); eng.onTick = null; post(id, { ...r, ok: true }); } catch (err) { if (eng) eng.onTick = null; const code = err?.jmCode ? err.jmCode : err instanceof SqlError ? (err.interrupted ? 'timeout' : `sqlite-${err.code & 0xff}`) : 'crash'; post(id, { ok: false, error: String(err?.message ?? err), code }); } };