/** * The SQLite work behind johnmcm.com's /sqlite-repair, over the module built * from binding.c. Used by worker.mjs in the browser and by * tests/sqlite-repair.test.mjs in Node; it only sees paths in the module's * file system, so it does not care whether a file came from WORKERFS (the * browser: the File read a slice at a time) or MEMFS (tests, and copies made * to apply a -wal or -journal file). * * The database is untrusted, so: * - table names are never pasted into SQL except through quoteIdent(); * - every statement runs under a time limit (the progress handler set by * jm_watch asks tick() every few thousand instructions); * - rows and text handed back are capped (maxRows, maxText); * - the input is opened read-only, and views and triggers in it may only * call functions without side effects (SQLITE_DBCONFIG_TRUSTED_SCHEMA off). * * Written for johnmcm.com (2026); published at /source/sqlite-recover/. */ export const OPEN_READONLY = 0x01; export const OPEN_READWRITE = 0x02; export const OPEN_CREATE = 0x04; export const OPEN_URI = 0x40; const OK = 0; const ROW = 100; const DONE = 101; const INTERRUPT = 9; const T_INTEGER = 1; const T_FLOAT = 2; const T_TEXT = 3; const T_BLOB = 4; /** The recovery extension's stages, as jm_recover_state() numbers them. */ export const STAGES = ['Starting', 'Copying tables', 'Looking for lost rows', 'Looking for lost rows', 'Saving lost rows', 'Rebuilding indexes', 'Done']; /** A name as an SQL identifier: in double quotes, with any double quote doubled. */ export function quoteIdent(name) { return '"' + String(name).replace(/"/g, '""') + '"'; } /** A string as an SQL literal. */ export function quoteString(s) { return "'" + String(s).replace(/'/g, "''") + "'"; } export class SqlError extends Error { constructor(message, code) { super(message); this.code = code; this.name = 'SqlError'; } /** True when the time limit (or Stop) ended the statement. */ get interrupted() { return (this.code & 0xff) === INTERRUPT; } } /** * The 100-byte database header, read without SQLite so that a file whose * header is damaged can still be described. fileSize is the file's length. */ export function readHeader(bytes, fileSize) { const b = bytes instanceof Uint8Array ? bytes : new Uint8Array(bytes); const magic = 'SQLite format 3\0'; let magicOk = b.length >= 16; for (let i = 0; magicOk && i < 16; i++) if (b[i] !== magic.charCodeAt(i)) magicOk = false; if (b.length < 100) return { magicOk, short: true, fileSize }; const dv = new DataView(b.buffer, b.byteOffset, 100); const raw = dv.getUint16(16); const pageSize = raw === 1 ? 65536 : raw; const pageSizeOk = pageSize >= 512 && pageSize <= 65536 && (pageSize & (pageSize - 1)) === 0; const pageCount = dv.getUint32(28); const changeCounter = dv.getUint32(24); const validFor = dv.getUint32(92); // The page count in the header is only trusted when written by SQLite 3.7.0 // or later, which also stores the change counter at offset 92. const pageCountValid = pageCount > 0 && validFor === changeCounter; const expected = pageSizeOk && pageCountValid ? pageSize * pageCount : null; const enc = dv.getUint32(56); const versionNumber = dv.getUint32(96); return { magicOk, short: false, fileSize, pageSize: pageSizeOk ? pageSize : null, rawPageSize: raw, writeVersion: b[18], readVersion: b[19], wal: b[18] === 2 || b[19] === 2, reserved: b[20], pageCount: pageCountValid ? pageCount : null, expectedSize: expected, truncated: expected !== null && fileSize < expected, freelistPages: dv.getUint32(36), schemaFormat: dv.getUint32(44), encoding: enc === 1 ? 'UTF-8' : enc === 2 ? 'UTF-16le' : enc === 3 ? 'UTF-16be' : null, userVersion: dv.getInt32(60), autoVacuum: dv.getUint32(52) !== 0, applicationId: dv.getUint32(68), sqliteVersion: versionNumber ? `${Math.floor(versionNumber / 1000000)}.${Math.floor(versionNumber / 1000) % 1000}.${versionNumber % 1000}` : null, }; } /** * Shannon entropy of a sample in bits per byte: an encrypted database (SQLCipher, * WhatsApp .crypt files) has no header and reads as close to 8. */ export function entropy(bytes) { const counts = new Uint32Array(256); for (const x of bytes) counts[x]++; let h = 0; for (const c of counts) { if (!c) continue; const p = c / bytes.length; h -= p * Math.log2(p); } return h; } export class Engine { /** * m is the module from createSqliteRecover(). onTick, if set, is called * from inside long statements with performance.now(), for progress. */ constructor(m) { this.m = m; this.deadline = Infinity; this.stopped = false; this.onTick = null; m.jmTick = () => this.tick(); m._jm_init(); } tick() { const now = performance.now(); if (this.onTick) { try { this.onTick(now); } catch { /* progress is best effort */ } } return this.stopped || now > this.deadline ? 1 : 0; } /** Runs fn with a time limit; statements still running when it passes fail as interrupted. */ limit(ms, fn) { const before = this.deadline; this.deadline = Math.min(before, performance.now() + ms); try { return fn(); } finally { this.deadline = before; } } get version() { return this.m.UTF8ToString(this.m._sqlite3_libversion()); } cstr(s) { const m = this.m; const n = m.lengthBytesUTF8(s) + 1; const p = m._malloc(n); m.stringToUTF8(s, p, n); return p; } open(uri, flags) { const m = this.m; const rcp = m._malloc(4); const u = this.cstr(uri); try { const db = m._jm_open(u, flags | OPEN_URI, rcp); const rc = m.HEAP32[rcp >> 2]; if (rc !== OK) { const msg = db ? m.UTF8ToString(m._sqlite3_errmsg(db)) : 'out of memory'; if (db) m._sqlite3_close_v2(db); throw new SqlError(msg, rc); } m._jm_watch(db, 1); return db; } finally { m._free(u); m._free(rcp); } } close(db) { if (db) this.m._sqlite3_close_v2(db); } error(db, rc) { return new SqlError(this.m.UTF8ToString(this.m._sqlite3_errmsg(db)), rc ?? this.m._sqlite3_extended_errcode(db)); } /** * Runs one statement and returns { columns, rows, more }: at most maxRows * rows (more is true if there were others), text cut to maxText bytes * (shown with a trailing ellipsis), blobs as { blob: length }, integers * beyond what a JavaScript number holds as strings. */ query(db, sql, { maxRows = Infinity, maxText = Infinity } = {}) { const m = this.m; const pp = m._malloc(4); const s = this.cstr(sql); let stmt = 0; try { const rc = m._sqlite3_prepare_v2(db, s, -1, pp, 0); stmt = m.HEAP32[pp >> 2]; if (rc !== OK) throw this.error(db, rc); if (!stmt) return { columns: [], rows: [], more: false }; const n = m._sqlite3_column_count(stmt); const columns = []; for (let i = 0; i < n; i++) columns.push(m.UTF8ToString(m._sqlite3_column_name(stmt, i))); const rows = []; let more = false; for (;;) { const r = m._sqlite3_step(stmt); if (r === DONE) break; if (r !== ROW) throw this.error(db, r); if (rows.length >= maxRows) { more = true; break; } const row = new Array(n); for (let i = 0; i < n; i++) row[i] = this.value(stmt, i, maxText); rows.push(row); } return { columns, rows, more }; } finally { if (stmt) m._sqlite3_finalize(stmt); m._free(s); m._free(pp); } } value(stmt, i, maxText) { const m = this.m; switch (m._sqlite3_column_type(stmt, i)) { case T_INTEGER: { const v = m._sqlite3_column_int64(stmt, i); const big = typeof v === 'bigint' ? v : BigInt(v); return big >= BigInt(Number.MIN_SAFE_INTEGER) && big <= BigInt(Number.MAX_SAFE_INTEGER) ? Number(big) : big.toString(); } case T_FLOAT: return m._sqlite3_column_double(stmt, i); case T_TEXT: { const p = m._sqlite3_column_text(stmt, i); const len = m._sqlite3_column_bytes(stmt, i); if (len <= maxText) return m.UTF8ToString(p, len); return m.UTF8ToString(p, maxText).replace(/�$/, '') + '…'; } case T_BLOB: return { blob: m._sqlite3_column_bytes(stmt, i) }; default: return null; } } /** Runs statements that return nothing worth keeping. */ exec(db, sql) { this.query(db, sql, { maxRows: 0 }); } /** The first value of the first row, or null. */ scalar(db, sql) { const r = this.query(db, sql, { maxRows: 1, maxText: 4096 }); return r.rows.length ? r.rows[0][0] : null; } /** * The schema: tables, views, indexes and triggers. When it cannot be read * as it is (a damaged entry stops SQLite parsing any of it), it is read * again with PRAGMA writable_schema on, which skips the damaged entries; * damaged is then true. */ schema(db) { const sql = 'SELECT type, name, tbl_name, rootpage, sql FROM sqlite_schema'; let damaged = false; let r; try { r = this.query(db, sql, { maxRows: 100_000, maxText: 1 << 20 }); } catch (e) { if (e instanceof SqlError && e.interrupted) throw e; damaged = true; this.exec(db, 'PRAGMA writable_schema = ON'); r = this.query(db, sql, { maxRows: 100_000, maxText: 1 << 20 }); } const items = r.rows.map(([type, name, tbl, rootpage, s]) => ({ type: String(type ?? ''), name: String(name ?? ''), table: String(tbl ?? ''), rootpage: typeof rootpage === 'number' ? rootpage : 0, sql: typeof s === 'string' ? s : '', virtual: typeof s === 'string' && /^\s*create\s+virtual\s+table/i.test(s), })); return { items, damaged, more: r.more }; } /** count(*) of one table, or { error } when it cannot be read to the end. */ count(db, table, ms) { try { const n = this.limit(ms, () => this.scalar(db, `SELECT count(*) FROM ${quoteIdent(table)}`)); return { rows: typeof n === 'number' ? n : Number(n) }; } catch (e) { if (!(e instanceof SqlError)) throw e; return e.interrupted && !this.stopped ? { error: 'too slow to count', slow: true } : { error: e.message, code: e.code }; } } /** The first rows of a table, with its rowid where it has one. */ preview(db, table, { rows = 20, maxText = 200, ms = 10_000 } = {}) { return this.limit(ms, () => this.query(db, `SELECT * FROM ${quoteIdent(table)} LIMIT ${Math.max(0, Math.floor(rows)) + 1}`, { maxRows: rows, maxText })); } /** * PRAGMA quick_check or integrity_check, keeping the first max messages. * ok is true when SQLite reports nothing wrong. */ check(db, kind, { max = 100, ms = 300_000 } = {}) { const pragma = kind === 'integrity' ? 'integrity_check' : 'quick_check'; const n = Math.max(1, Math.min(10_000, Math.floor(max))); let r; try { r = this.limit(ms, () => this.query(db, `PRAGMA ${pragma}(${n})`, { maxRows: n, maxText: 2000 })); } catch (e) { // Damage in the schema or a root page can stop the check itself. const code = e instanceof SqlError ? e.code & 0xff : 0; if (code === 11 || code === 26) return { ok: false, messages: [`The check could not finish: ${e.message}`], capped: false, failed: true }; throw e; } // Newer versions of SQLite put all the messages in one row, a line each. const messages = r.rows.flatMap((x) => String(x[0] ?? '').split(/\r?\n/)).filter((x) => x && x !== '*** in database main ***'); return { ok: messages.length === 1 && messages[0] === 'ok', messages, capped: messages.length >= n }; } /** * Recovers db into a new database at outPath with the recovery extension: * every row it can still read, put back in its table; rows whose table * cannot be told go to a table named laf. progress(stage, table, steps) is * called now and then. Returns { ok, error?, interrupted? }; on failure the * output is not complete. */ recover(db, outPath, { laf = 'lost_and_found', freelistCorrupt = false, ms = 1_800_000, progress } = {}) { const m = this.m; const out = this.cstr(outPath); const lafp = laf ? this.cstr(laf) : 0; const p = m._jm_recover_db(db, out, lafp, freelistCorrupt ? 1 : 0); m._free(out); if (!p) { if (lafp) m._free(lafp); return { ok: false, error: 'out of memory' }; } return this.limit(ms, () => this.drive(p, lafp, progress)); } /** Writes db out as SQL statements, handed to onChunk a few megabytes at a time. */ dump(db, onChunk, { laf = 'lost_and_found', ms = 1_800_000, progress, chunk = 4 << 20 } = {}) { const m = this.m; let parts = []; let size = 0; m.jmSql = (s) => { parts.push(s, ';\n'); size += s.length + 2; if (size >= chunk) { onChunk(parts.join('')); parts = []; size = 0; } }; const lafp = laf ? this.cstr(laf) : 0; const p = m._jm_recover_sql(db, lafp); if (!p) { if (lafp) m._free(lafp); m.jmSql = null; return { ok: false, error: 'out of memory' }; } try { const r = this.limit(ms, () => this.drive(p, lafp, progress)); if (parts.length) onChunk(parts.join('')); return r; } finally { m.jmSql = null; } } drive(p, lafp, progress) { const m = this.m; let rc = OK; let steps = 0; let last = 0; try { while ((rc = m._jm_recover_step(p)) === OK) { steps++; if ((steps & 63) === 0) { const now = performance.now(); if (now > this.deadline || this.stopped) break; if (progress && now - last > 150) { last = now; const t = m._jm_recover_table(p); progress(m._jm_recover_state(p), t ? m.UTF8ToString(t) : '', steps); } } } const stoppedHere = rc === OK; const msg = m.UTF8ToString(m._sqlite3_recover_errmsg(p)); const code = m._sqlite3_recover_errcode(p); if (stoppedHere) return { ok: false, interrupted: true, error: 'stopped' }; if (rc !== DONE) return { ok: false, interrupted: (code & 0xff) === INTERRUPT, error: msg || `error ${rc}` }; return { ok: true, steps }; } finally { m._sqlite3_recover_finish(p); if (lafp) m._free(lafp); } } /** * Rebuilds the full-text search indexes (FTS3, FTS4 and FTS5 tables) of a * recovered database from the text they index. Recovery copies an index's * own pages as they were, so after damage it can disagree with the rows * that came back. Returns the names rebuilt and those that could not be * (a contentless table has no text to rebuild from). */ rebuildSearch(db, { ms = 600_000 } = {}) { const rebuilt = []; const failed = []; for (const it of this.schema(db).items) { if (it.type !== 'table' || !/^\s*create\s+virtual\s+table\s[\s\S]*?\busing\s+fts[345]\b/i.test(it.sql)) continue; const t = quoteIdent(it.name); try { this.limit(ms, () => this.exec(db, `INSERT INTO ${t}(${t}) VALUES ('rebuild')`)); rebuilt.push(it.name); } catch (e) { if (!(e instanceof SqlError) || (e.interrupted && this.stopped)) throw e; failed.push({ name: it.name, error: e.message }); } } return { rebuilt, failed }; } /** VACUUM INTO a new file: a clean copy of a database that reads without errors. */ vacuumInto(db, outPath, { ms = 1_800_000 } = {}) { this.limit(ms, () => this.exec(db, `VACUUM INTO ${quoteString(outPath)}`)); } }