QIWEI-skill/scripts/sqlite-engine-probe.mjs

/**
 * SQLite 引擎能力探针
 *
 * 工作台库用 node:sqlite 的 DatabaseSync，而 bun 编译产物里没有这个内置模块，只有 bun:sqlite。
 * 本脚本用同一组断言分别在两个引擎上跑，输出能力矩阵，用来判断适配层是否可行、差异在哪。
 *
 * 用法：
 *   node scripts/sqlite-engine-probe.mjs
 *   bun  scripts/sqlite-engine-probe.mjs
 */

import fs from 'node:fs';
import os from 'node:os';
import path from 'node:path';

const isBun = typeof globalThis.Bun !== 'undefined';
const engine = isBun ? 'bun:sqlite' : 'node:sqlite';

async function openDatabase(file, options = {}) {
  if (isBun) {
    const { Database } = await import('bun:sqlite');
    // bun 用全小写 readonly，node 用驼峰 readOnly；且传空对象会因为没有任何 open flag 报 SQLITE_MISUSE
    return new Database(file, options.readOnly ? { readonly: true } : { create: true, readwrite: true });
  }
  const { DatabaseSync } = await import('node:sqlite');
  return new DatabaseSync(file, options.readOnly ? { readOnly: true } : {});
}

const results = [];
function check(name, fn) {
  try {
    const detail = fn();
    results.push({ name, ok: true, detail: detail === undefined ? '' : String(detail) });
  } catch (error) {
    results.push({ name, ok: false, detail: error.message.slice(0, 120) });
  }
}

const dir = fs.mkdtempSync(path.join(os.tmpdir(), 'sqlite-probe-'));
const file = path.join(dir, 'probe.db');
const db = await openDatabase(file);

check('exec 多语句 PRAGMA', () => {
  db.exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;');
});

check('WAL 已生效', () => {
  const row = db.prepare('PRAGMA journal_mode').get();
  const mode = String(row.journal_mode || row['journal_mode'] || '').toLowerCase();
  if (mode !== 'wal') throw new Error(`journal_mode=${mode}`);
  return mode;
});

check('busy_timeout 已设置', () => {
  const row = db.prepare('PRAGMA busy_timeout').get();
  const value = Number(row.timeout ?? row.busy_timeout ?? 0);
  if (value !== 5000) throw new Error(`busy_timeout=${value}`);
  return value;
});

check('建表含 CHECK 与外键', () => {
  db.exec(`
    CREATE TABLE parent (
      id TEXT PRIMARY KEY,
      mode TEXT NOT NULL DEFAULT 'review' CHECK(mode IN ('review','auto','human')),
      created_at TEXT NOT NULL
    );
    CREATE TABLE child (
      id TEXT PRIMARY KEY,
      parent_id TEXT NOT NULL REFERENCES parent(id) ON DELETE CASCADE,
      body TEXT NOT NULL
    );
  `);
});

check('run 位置参数并返回 changes', () => {
  const result = db.prepare('INSERT INTO parent(id,mode,created_at) VALUES(?,?,?)')
    .run('p1', 'review', new Date().toISOString());
  if (Number(result.changes) !== 1) throw new Error(`changes=${result.changes}`);
  return `changes=${result.changes} lastInsertRowid=${result.lastInsertRowid}`;
});

check('get 命中返回行对象', () => {
  const row = db.prepare('SELECT * FROM parent WHERE id=?').get('p1');
  if (!row || row.mode !== 'review') throw new Error(JSON.stringify(row));
  return `mode=${row.mode}`;
});

check('get 未命中的返回值', () => {
  const row = db.prepare('SELECT * FROM parent WHERE id=?').get('missing');
  // 生产代码写法是 .get(...)?.value ?? fallback，null 与 undefined 都能走通
  if (row !== undefined && row !== null) throw new Error(`返回了 ${JSON.stringify(row)}`);
  return row === null ? 'null' : 'undefined';
});

check('all 返回数组', () => {
  const rows = db.prepare('SELECT * FROM parent').all();
  if (!Array.isArray(rows) || rows.length !== 1) throw new Error(`len=${rows?.length}`);
  return `len=${rows.length}`;
});

check('PRAGMA table_info 走 prepare.all', () => {
  const rows = db.prepare('PRAGMA table_info(parent)').all();
  const names = rows.map(item => item.name);
  if (!names.includes('mode')) throw new Error(names.join(','));
  return names.join(',');
});

check('CHECK 约束生效', () => {
  try {
    db.prepare('INSERT INTO parent(id,mode,created_at) VALUES(?,?,?)').run('bad', 'illegal', 'now');
  } catch {
    return '违规插入已被拒绝';
  }
  throw new Error('CHECK 未拦截非法值');
});

check('外键约束生效', () => {
  try {
    db.prepare('INSERT INTO child(id,parent_id,body) VALUES(?,?,?)').run('c1', 'nonexistent', 'x');
  } catch {
    return '孤儿行已被拒绝';
  }
  throw new Error('外键未拦截孤儿行');
});

check('显式事务 COMMIT', () => {
  db.exec('BEGIN');
  db.prepare('INSERT INTO parent(id,mode,created_at) VALUES(?,?,?)').run('p2', 'auto', 'now');
  db.exec('COMMIT');
  const row = db.prepare('SELECT * FROM parent WHERE id=?').get('p2');
  if (!row) throw new Error('提交后查不到');
});

check('显式事务 ROLLBACK', () => {
  db.exec('BEGIN');
  db.prepare('INSERT INTO parent(id,mode,created_at) VALUES(?,?,?)').run('p3', 'auto', 'now');
  db.exec('ROLLBACK');
  const row = db.prepare('SELECT * FROM parent WHERE id=?').get('p3');
  if (row) throw new Error('回滚后仍查得到');
});

check('ON CONFLICT DO UPDATE', () => {
  db.exec('CREATE TABLE settings(key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at TEXT NOT NULL)');
  const sql = `INSERT INTO settings(key,value,updated_at) VALUES(?,?,?)
    ON CONFLICT(key) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at`;
  db.prepare(sql).run('k', 'v1', 'now');
  db.prepare(sql).run('k', 'v2', 'later');
  const row = db.prepare('SELECT value FROM settings WHERE key=?').get('k');
  if (row.value !== 'v2') throw new Error(`value=${row.value}`);
  return 'upsert 生效';
});

check('WAL 附属文件已落盘', () => {
  const walFile = `${file}-wal`;
  if (!fs.existsSync(walFile)) throw new Error('未生成 -wal 文件');
  return path.basename(walFile);
});

let readOnlyDetail = '';
try {
  const ro = await openDatabase(file, { readOnly: true });
  const row = ro.prepare('SELECT COUNT(*) AS n FROM parent').get();
  readOnlyDetail = `可读，count=${row.n}`;
  try {
    ro.prepare('INSERT INTO parent(id,mode,created_at) VALUES(?,?,?)').run('ro', 'auto', 'now');
    readOnlyDetail += '；但写入未被拒绝';
    results.push({ name: '只读模式', ok: false, detail: readOnlyDetail });
  } catch {
    results.push({ name: '只读模式', ok: true, detail: `${readOnlyDetail}；写入已拒绝` });
  }
  ro.close();
} catch (error) {
  results.push({ name: '只读模式', ok: false, detail: error.message.slice(0, 120) });
}

db.close();

const failed = results.filter(item => !item.ok);
process.stdout.write(`\n引擎：${engine}（${isBun ? `bun ${Bun.version}` : `node ${process.version}`}）\n\n`);
for (const item of results) {
  process.stdout.write(`  ${item.ok ? '[ok]  ' : '[FAIL]'} ${item.name}${item.detail ? `  — ${item.detail}` : ''}\n`);
}
process.stdout.write(`\n${results.length - failed.length}/${results.length} 通过\n`);

// Windows 下句柄可能尚未完全释放，清理失败不影响探测结论
try {
  fs.rmSync(dir, { recursive: true, force: true });
} catch {
  process.stdout.write(`（临时目录未能清理，可手动删除：${dir}）\n`);
}

process.exit(failed.length ? 1 : 0);