metona-ai-desktop/electron/services/__tests__/database-migration.test.ts

/**
 * 数据库 schema 版本化测试（v0.6.4 P3-3）
 *
 * 契约：
 * 1. 全新库 initialize() 后 PRAGMA user_version 盖章为 DatabaseService.SCHEMA_VERSION；
 * 2. 已盖章的库再次 initialize（新实例、同文件）→ 走快速路径，探测批次跳过，
 *    且版本号保持不变；
 * 3. user_version 与数据共存：盖章不会丢失/改变已有数据。
 *
 * 运行要求：better-sqlite3 需为 Electron ABI 构建（test:electron 模式），
 * 系统 Node 下自动跳过。
 */

import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { mkdtempSync, mkdirSync, rmSync } from 'fs';
import { tmpdir } from 'os';
import { join } from 'path';

let dbAvailable = true;
try {
  // eslint-disable-next-line @typescript-eslint/no-require-imports
  const probe = require('better-sqlite3');
  const p = new probe(':memory:');
  p.close();
} catch {
  dbAvailable = false;
}

import { DatabaseService } from '../database.service';

/** 预置 legacy 库：先建 .metona 目录再开库（与 DatabaseService 目录布局一致） */
function openLegacyDb(dir: string): any {
  const metonaDir = join(dir, '.metona');
  mkdirSync(metonaDir, { recursive: true });
  // eslint-disable-next-line @typescript-eslint/no-require-imports
  const raw = require('better-sqlite3');
  return new raw(join(metonaDir, 'agent.db'));
}

describe.skipIf(!dbAvailable)('DatabaseService — PRAGMA user_version 版本化', () => {
  let dir: string;
  let svc1: DatabaseService;
  let svc2: DatabaseService;

  beforeAll(async () => {
    dir = mkdtempSync(join(tmpdir(), 'metona-dbmig-'));
    const mod = await import('../database.service');
    const Svc = mod.DatabaseService;
    svc1 = new Svc(dir);
    svc1.initialize();
    svc2 = new Svc(dir); // 模拟第二次启动
    svc2.initialize();
  });

  afterAll(() => {
    try {
      svc2?.close();
      svc1?.close();
    } catch {
      /* ignore */
    }
    rmSync(dir, { recursive: true, force: true });
  });

  it('initialize 后 user_version 已盖章为 SCHEMA_VERSION', () => {
    const version = svc2.getDB().pragma('user_version', { simple: true }) as number;
    // v0.7.4 P4-4: SCHEMA_VERSION 1 → 2（迁移 9 trigram 纳入版本化）
    expect(version).toBe(DatabaseService.SCHEMA_VERSION);
  });

  it('盖章库的探测批次被跳过（重复 initialize 不报错且配置 seed 保持存在）', () => {
    // 核心断言在日志侧难以捕获 —— 这里验证幂等性的可观察结果：
    // 种子默认值仍然存在（INSERT OR IGNORE），表结构与首次一致
    const row = svc2.getDB().prepare(`SELECT COUNT(*) AS n FROM app_config`).get() as { n: number };
    expect(row.n).toBeGreaterThan(0);
  });
});

describe.skipIf(!dbAvailable)('DatabaseService — 全量表结构与索引', () => {
  let dir: string;
  let svc: DatabaseService;
  let db: any;

  beforeAll(async () => {
    const { DatabaseService } = await import('../database.service');
    dir = mkdtempSync(join(tmpdir(), 'metona-migtables-'));
    svc = new DatabaseService(dir);
    svc.initialize();
    db = svc.getDB();
  });

  afterAll(() => {
    try {
      svc?.close();
    } catch {
      /* ignore */
    }
    rmSync(dir, { recursive: true, force: true });
  });

  it('messages 表包含 FTS 所需的全部列（content 可 NULL + attachments + reasoning_content）', () => {
    const cols = db.prepare('PRAGMA table_info(messages)').all() as Array<{
      name: string;
      notnull: number;
    }>;
    const names = cols.map((c) => c.name);
    for (const expected of [
      'id',
      'session_id',
      'role',
      'content',
      'reasoning_content',
      'tool_calls',
      'tool_result',
      'attachments',
      'iteration',
      'created_at',
    ]) {
      expect(names).toContain(expected);
    }
    const contentCol = cols.find((c) => c.name === 'content');
    expect(contentCol!.notnull).toBe(0); // C-6: content 允许 NULL
  });

  it('messages_fts 虚表使用 trigram tokenizer（迁移 9 目标 schema）', () => {
    const sqlRow = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { sql: string };
    expect(sqlRow.sql).toContain('trigram');
  });

  it('FTS 触发器存活：insert/delete/update 三个触发器', () => {
    const triggers = db
      .prepare(
        `SELECT name FROM sqlite_master WHERE type='trigger' AND tbl_name='messages' ORDER BY name`,
      )
      .all() as Array<{ name: string }>;
    const names = triggers.map((t) => t.name);
    expect(names).toContain('messages_fts_insert');
    expect(names).toContain('messages_fts_delete');
    expect(names).toContain('messages_fts_update');
  });

  it('FTS 实时同步：插入消息后索引可见、删除后索引同步', () => {
    db.prepare(
      `INSERT INTO sessions (id, title, created_at, updated_at) VALUES ('mig_s1', 's', 1, 1)`,
    ).run();
    db.prepare(
      `INSERT INTO messages (id, session_id, role, content, created_at) VALUES ('mig_m1', 'mig_s1', 'user', 'migftshit 内容', 1)`,
    ).run();
    const hit = db
      .prepare(`SELECT COUNT(*) AS c FROM messages_fts WHERE messages_fts MATCH 'migftsh'`)
      .get() as { c: number };
    expect(hit.c).toBe(1);
    db.prepare(`DELETE FROM messages WHERE id = 'mig_m1'`).run();
    const miss = db
      .prepare(`SELECT COUNT(*) AS c FROM messages_fts WHERE messages_fts MATCH 'migftsh'`)
      .get() as { c: number };
    expect(miss.c).toBe(0);
  });

  it('audit_logs 的 INSERT-ONLY 触发器存活（audit_no_update / audit_no_delete）', () => {
    const triggers = db
      .prepare(
        `SELECT name FROM sqlite_master WHERE type='trigger' AND tbl_name='audit_logs' ORDER BY name`,
      )
      .all() as Array<{ name: string }>;
    const names = triggers.map((t) => t.name);
    expect(names).toContain('audit_no_update');
    expect(names).toContain('audit_no_delete');
    // 更新被拒绝
    db.prepare(
      `INSERT INTO audit_logs (session_id, event_type, actor, target, created_at) VALUES ('s', 'error', 'agent', 't', 1)`,
    ).run();
    expect(() =>
      db.prepare(`UPDATE audit_logs SET target = 'hacked' WHERE event_type = 'error'`).run(),
    ).toThrow();
  });

  it('episodic/semantic/working 记忆表均含 tf_cache 列（P2-12）', () => {
    for (const table of ['episodic_memories', 'semantic_memories', 'working_memories']) {
      const cols = db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>;
      expect(cols.map((c) => c.name)).toContain('tf_cache');
    }
  });

  it('session_summaries 外键为 ON DELETE CASCADE（迁移 8）', () => {
    const fks = db.prepare('PRAGMA foreign_key_list(session_summaries)').all() as Array<{
      table: string;
      on_delete: string;
    }>;
    expect(fks.some((fk) => fk.table === 'sessions' && fk.on_delete === 'CASCADE')).toBe(true);
  });

  it('mcp_servers transport CHECK 含 streamable-http（迁移 6）', () => {
    const sqlRow = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='mcp_servers'`)
      .get() as { sql: string };
    expect(sqlRow.sql).toContain('streamable-http');
  });

  it('CONFIG_DEFAULTS 全部 seed 入库（每个默认 key 一行）', async () => {
    const { CONFIG_DEFAULTS } = await import('../database.service');
    const total = db.prepare('SELECT COUNT(*) AS c FROM app_config').get() as { c: number };
    expect(total.c).toBeGreaterThanOrEqual(CONFIG_DEFAULTS.length);
    // 抽查默认 key 存在
    const keys = db.prepare('SELECT key FROM app_config').all() as Array<{ key: string }>;
    const keySet = new Set(keys.map((k) => k.key));
    for (const d of CONFIG_DEFAULTS) {
      expect(keySet.has(d.key)).toBe(true);
    }
  });

  it('索引覆盖常用查询路径（idx_messages_session 复合索引存在）', () => {
    const idx = db.prepare(`PRAGMA index_list(messages)`).all() as Array<{ name: string }>;
    expect(idx.map((i) => i.name)).toContain('idx_messages_session');
  });
});

describe.skipIf(!dbAvailable)('DatabaseService — 多版本迁移矩阵（user_version 0/1/2）', () => {
  it('user_version=0 的旧库：初始化后完整迁移并盖章为 SCHEMA_VERSION', async () => {
    const { DatabaseService } = await import('../database.service');
    const dir = mkdtempSync(join(tmpdir(), 'metona-migv0-'));
    // 预置一个"旧版"库：只有消息表 + 非 trigram FTS（模拟迁移 9 之前）
    const legacy = openLegacyDb(dir);
    legacy.exec(`
      CREATE TABLE messages (
        id TEXT PRIMARY KEY, session_id TEXT NOT NULL, role TEXT NOT NULL,
        content TEXT NOT NULL, reasoning_content TEXT, tool_calls TEXT,
        tool_result TEXT, attachments TEXT, iteration INTEGER, created_at INTEGER NOT NULL
      );
      CREATE VIRTUAL TABLE messages_fts USING fts5(content, content=messages, content_rowid=rowid, tokenize='unicode61');
    `);
    legacy.pragma('user_version = 0');
    legacy.close();

    const svc = new DatabaseService(dir);
    svc.initialize();
    const db = svc.getDB();
    expect(db.pragma('user_version', { simple: true })).toBe(DatabaseService.SCHEMA_VERSION);
    // 迁移 9：FTS 重建为 trigram
    const ftsSql = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { sql: string };
    expect(ftsSql.sql).toContain('trigram');
    // 迁移 5：messages content 允许 NULL
    const cols = db.prepare('PRAGMA table_info(messages)').all() as Array<{
      notnull: number;
      name: string;
    }>;
    const contentCol = cols.find((c) => c.name === 'content');
    expect(contentCol!.notnull).toBe(0);
    svc.close();
    rmSync(dir, { recursive: true, force: true });
  });

  it('user_version=1 的库（迁移 9 之前盖章）：重新初始化补齐 trigram', async () => {
    const { DatabaseService } = await import('../database.service');
    const dir = mkdtempSync(join(tmpdir(), 'metona-migv1-'));
    const legacy = openLegacyDb(dir);
    legacy.exec(`
      CREATE TABLE messages (
        id TEXT PRIMARY KEY, session_id TEXT NOT NULL, role TEXT NOT NULL,
        content TEXT NOT NULL, reasoning_content TEXT, tool_calls TEXT,
        tool_result TEXT, attachments TEXT, iteration INTEGER, created_at INTEGER NOT NULL
      );
      CREATE VIRTUAL TABLE messages_fts USING fts5(content, content=messages, content_rowid=rowid, tokenize='unicode61');
    `);
    legacy.pragma('user_version = 1'); // 旧版本盖章但缺迁移 9
    legacy.close();

    const svc = new DatabaseService(dir);
    svc.initialize();
    const db = svc.getDB();
    expect(db.pragma('user_version', { simple: true })).toBe(DatabaseService.SCHEMA_VERSION);
    const ftsSql = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { sql: string };
    expect(ftsSql.sql).toContain('trigram');
    svc.close();
    rmSync(dir, { recursive: true, force: true });
  });

  it('user_version=2 的库：增量迁移至 SCHEMA_VERSION（v0.8.0 补 sessions.deleted_at）', async () => {
    const { DatabaseService } = await import('../database.service');
    const dir = mkdtempSync(join(tmpdir(), 'metona-migv2-'));
    const legacy = openLegacyDb(dir);
    // v0.8.0 注: 真实 v2 库必含 sessions 表 —— fixture 补齐以覆盖迁移 10
    legacy.exec(`
      CREATE TABLE messages (
        id TEXT PRIMARY KEY, session_id TEXT NOT NULL, role TEXT NOT NULL,
        content TEXT, created_at INTEGER NOT NULL
      );
      CREATE TABLE sessions (
        id TEXT PRIMARY KEY, title TEXT DEFAULT '新会话', created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL, message_count INTEGER DEFAULT 0,
        pinned INTEGER DEFAULT 0, archived INTEGER DEFAULT 0, metadata TEXT DEFAULT '{}'
      );
      CREATE VIRTUAL TABLE messages_fts USING fts5(content, content=messages, content_rowid=rowid, tokenize='trigram');
    `);
    legacy.pragma('user_version = 2');
    legacy.close();

    const svc = new DatabaseService(dir);
    svc.initialize();
    const db = svc.getDB();
    // v0.8.0 P2-1 契约更新: SCHEMA_VERSION 升至 3（迁移 10 sessions.deleted_at），
    // user_version=2 的库不再是最新版 —— 会执行增量迁移并盖章到 3；
    // FTS 已是 trigram（迁移 9 幂等跳过），增量迁移只补 sessions.deleted_at 列。
    expect(db.pragma('user_version', { simple: true })).toBe(DatabaseService.SCHEMA_VERSION);
    const delCol = db
      .prepare("SELECT COUNT(*) AS c FROM pragma_table_info('sessions') WHERE name='deleted_at'")
      .get() as { c: number };
    expect(delCol.c).toBe(1);
    // 快速路径不重建 FTS（保持原有 trigram 表）
    const ftsSql = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { sql: string };
    expect(ftsSql.sql).toContain('trigram');
    svc.close();
    rmSync(dir, { recursive: true, force: true });
  });

  it('迁移 9 幂等：重复 initialize 不重复重建（SQL 始终为 trigram 单份定义）', async () => {
    const { DatabaseService } = await import('../database.service');
    const dir = mkdtempSync(join(tmpdir(), 'metona-migidem-'));
    const svc = new DatabaseService(dir);
    svc.initialize();
    svc.close();
    const svc2 = new DatabaseService(dir);
    svc2.initialize();
    const db = svc2.getDB();
    const ftsRows = db
      .prepare(`SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { c: number };
    expect(ftsRows.c).toBe(1);
    const sqlRow = db
      .prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name='messages_fts'`)
      .get() as { sql: string };
    expect(sqlRow.sql).toContain('trigram');
    svc2.close();
    rmSync(dir, { recursive: true, force: true });
  });

  it('迁移 7 FTS 回填：已有消息在 initialize 后进入索引（rebuild 语义）', async () => {
    const { DatabaseService } = await import('../database.service');
    const dir = mkdtempSync(join(tmpdir(), 'metona-migbackfill-'));
    const legacy = openLegacyDb(dir);
    // 旧库：messages 有数据但 messages_fts 空（无回填）
    legacy.exec(`
      CREATE TABLE messages (
        id TEXT PRIMARY KEY, session_id TEXT NOT NULL, role TEXT NOT NULL,
        content TEXT NOT NULL, reasoning_content TEXT, tool_calls TEXT,
        tool_result TEXT, attachments TEXT, iteration INTEGER, created_at INTEGER NOT NULL
      );
      CREATE TABLE sessions (
        id TEXT PRIMARY KEY, title TEXT DEFAULT '新会话', created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL, message_count INTEGER DEFAULT 0,
        pinned INTEGER DEFAULT 0, archived INTEGER DEFAULT 0, metadata TEXT DEFAULT '{}'
      );
      CREATE VIRTUAL TABLE messages_fts USING fts5(content, content=messages, content_rowid=rowid, tokenize='unicode61');
    `);
    legacy.prepare(`INSERT INTO sessions (id, created_at, updated_at) VALUES ('s1', 1, 1)`).run();
    legacy
      .prepare(
        `INSERT INTO messages (id, session_id, role, content, created_at) VALUES ('m1', 's1', 'user', 'backfillkeyword 数据', 1)`,
      )
      .run();
    legacy.close();

    const svc = new DatabaseService(dir);
    svc.initialize();
    const db = svc.getDB();
    // 迁移 9 重建 + rebuild 后，存量消息应可被搜索
    const hit = db
      .prepare(`SELECT COUNT(*) AS c FROM messages_fts WHERE messages_fts MATCH 'backfillkeyw'`)
      .get() as { c: number };
    expect(hit.c).toBe(1);
    svc.close();
    rmSync(dir, { recursive: true, force: true });
  });
});

describe.skipIf(!dbAvailable)('DatabaseService — 种子配置与连接语义', () => {
  let dir: string;
  let svc: DatabaseService;

  beforeAll(async () => {
    const { DatabaseService } = await import('../database.service');
    dir = mkdtempSync(join(tmpdir(), 'metona-migseed-'));
    svc = new DatabaseService(dir);
    svc.initialize();
  });

  afterAll(() => {
    try {
      svc?.close();
    } catch {
      /* ignore */
    }
    rmSync(dir, { recursive: true, force: true });
  });

  it('敏感默认 key 以明文空串 seed（新用户未配置），category 正确', () => {
    const row = svc
      .getDB()
      .prepare(`SELECT value, category FROM app_config WHERE key = 'llm.apiKey'`)
      .get() as { value: string; category: string };
    expect(JSON.parse(row.value)).toBe('');
    expect(row.category).toBe('llm');
  });

  it('数字/布尔默认值 seed 为 JSON 类型', () => {
    const row = svc
      .getDB()
      .prepare(`SELECT value FROM app_config WHERE key = 'agent.maxIterations'`)
      .get() as { value: string };
    expect(JSON.parse(row.value)).toBe(20);
  });

  it('WAL 模式已开启（journal_mode=wal）', () => {
    const mode = svc.getDB().pragma('journal_mode', { simple: true }) as string;
    expect(mode).toBe('wal');
  });
});