MetonaSqlark/tests/v070-features.test.ts

/**
 * v0.7.0 功能与性能回归测试
 *
 * 覆盖：
 * - 复合主键显式拒绝（defineTable / SQL / ALTER ADD 主键列）
 * - 参数化查询 db.query(sql, params)（安全编码/参数计数/注入防护/多语句）
 * - KVStoreEngine 事务增量 flush（大表事务 O(改动行)，非整表重写）
 * - EXPLAIN 真实索引命中信息（pk / index:col / none）
 */

import { MetonaSqlark } from '../src/core';
import { KVStoreEngine } from '../src/engine/kvstore_engine';
import { createSchema } from '../src/table/schema';
import { DatabaseError } from '../src/constants';
import { parseLogRecords, KVLogOp } from '../src/engine/kvstore/log';
import { SharedMemoryBackend } from '../src/engine/kvstore/shared_memory_medium';
import type { KVStore } from '../src/engine/kvstore/index';

function uniqueDB(): string {
  return `v070-${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
}

async function expectCode(promise: Promise<unknown>, code: string): Promise<void> {
  try {
    await promise;
  } catch (e) {
    expect((e as { code?: string }).code).toBe(code);
    return;
  }
  throw new Error(`Expected rejection with code "${code}", but promise resolved`);
}

describe('v0.7.0 — 复合主键显式拒绝', () => {
  it('defineTable 多主键抛 SCHEMA_ERROR（不再静默取第一个）', () => {
    expect(() => createSchema('t', {
      a: { type: 'string', primaryKey: true },
      b: { type: 'string', primaryKey: true },
    })).toThrow('Composite primary keys are not supported');
  });

  it('SQL CREATE TABLE 多主键抛 SCHEMA_ERROR', async () => {
    const db = new MetonaSqlark({ name: uniqueDB(), mode: 'memory' });
    await db.init();
    await expectCode(
      db.query('CREATE TABLE t (a STRING PRIMARY KEY, b STRING PRIMARY KEY)'),
      'SCHEMA_ERROR',
    );
    await db.close();
  });

  it('ALTER TABLE ADD 主键列（已有主键）抛 SCHEMA_ERROR', async () => {
    const db = new MetonaSqlark({ name: uniqueDB(), mode: 'memory' });
    await db.init();
    await db.defineTable('t', { id: { type: 'string', primaryKey: true } });
    await expectCode(
      db.query('ALTER TABLE t ADD COLUMN other STRING PRIMARY KEY'),
      'SCHEMA_ERROR',
    );
    await db.close();
  });
});

describe('v0.7.0 — 参数化查询', () => {
  async function setup(): Promise<MetonaSqlark> {
    const db = new MetonaSqlark({ name: uniqueDB(), mode: 'memory' });
    await db.init();
    await db.defineTable('users', {
      id: { type: 'string', primaryKey: true },
      name: { type: 'string' },
      age: { type: 'number' },
      active: { type: 'boolean' },
      profile: { type: 'json' },
    });
    return db;
  }

  it('位置参数绑定（字符串/数字/布尔/null）', async () => {
    const db = await setup();
    await db.query("INSERT INTO users VALUES ('1', 'Alice', 30, TRUE, NULL)");
    await db.query('INSERT INTO users VALUES (?, ?, ?, ?, ?)', ['2', "O'Brien", 25, false, null]);

    const rows = await db.query('SELECT * FROM users WHERE id = ?', ['2']) as Record<string, unknown>[];
    expect(rows).toHaveLength(1);
    expect(rows[0].name).toBe("O'Brien");
    expect(rows[0].age).toBe(25);
    expect(rows[0].active).toBe(false);
    expect(rows[0].profile).toBeNull();

    const byName = await db.query("SELECT * FROM users WHERE name = ?", ["O'Brien"]) as Record<string, unknown>[];
    expect(byName).toHaveLength(1);
    await db.close();
  });

  it('对象参数显式拒绝（PARAM_ERROR，防静默错配）', async () => {
    const db = await setup();
    await expectCode(
      db.query('INSERT INTO users VALUES (?, ?, ?, ?, ?)', ['1', 'N', 1, true, { city: 'BJ' }]),
      'PARAM_ERROR',
    );
    await db.close();
  });

  it('注入防护：字符串参数含 SQL 片段被当作字面量', async () => {
    const db = await setup();
    await db.query('INSERT INTO users VALUES (?, ?, ?, ?, NULL)', ['1', "x'; DROP TABLE users; --", 1, true]);
    const rows = await db.query('SELECT * FROM users') as Record<string, unknown>[];
    expect(rows).toHaveLength(1);
    expect(rows[0].name).toBe("x'; DROP TABLE users; --");
    await db.close();
  });

  it('字符串字面量内的 ? 不被替换', async () => {
    const db = await setup();
    await db.query("INSERT INTO users VALUES ('1', 'what?', 1, TRUE, NULL)");
    const rows = await db.query("SELECT * FROM users WHERE name = 'what?'") as Record<string, unknown>[];
    expect(rows).toHaveLength(1);
    await db.close();
  });

  it('参数数量不匹配抛 PARAM_ERROR', async () => {
    const db = await setup();
    await expectCode(db.query('SELECT * FROM users WHERE id = ?', []), 'PARAM_ERROR');
    await expectCode(db.query('SELECT * FROM users WHERE id = ?', ['1', '2']), 'PARAM_ERROR');
    await db.close();
  });

  it('多语句 + 参数', async () => {
    const db = await setup();
    await db.query("INSERT INTO users VALUES (?, 'A', 1, TRUE, NULL); INSERT INTO users VALUES (?, 'B', 2, TRUE, NULL)", ['1', '2']);
    const rows = await db.query('SELECT * FROM users') as Record<string, unknown>[];
    expect(rows).toHaveLength(2);
    await db.close();
  });

  it('NaN/Infinity 参数编码为 NULL', async () => {
    const db = await setup();
    await db.query('INSERT INTO users VALUES (?, ?, ?, ?, NULL)', ['1', 'N', Number.NaN, true]);
    const rows = await db.query('SELECT * FROM users WHERE id = ?', ['1']) as Record<string, unknown>[];
    expect(rows).toHaveLength(1);
    expect(rows[0].age).toBeNull();
    await db.close();
  });
});

describe('v0.7.0 — KVStoreEngine 事务增量 flush', () => {
  it('大表事务改 1 行：日志仅含该行（非整表重写）', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'number', primaryKey: true },
      v: { type: 'string' },
    }));
    const rows = Array.from({ length: 1000 }, (_, i) => ({ id: i, v: `v${i}` }));
    await eng.insert('t', rows);
    // 基线 checkpoint（日志清空）
    const kv0 = (eng as any).kv as KVStore;
    await kv0.checkpoint();

    await eng.beginTransaction();
    await eng.update('t', { table: 't', where: { id: 500 } }, { v: 'CHANGED' });
    await eng.commitTransaction();

    // commit 后日志应只有 1 条记录、1 个 PUT 条目（增量 flush，非 1000 行重写）
    const medium = (eng as any).kv.medium as SharedMemoryBackend;
    const raw = await medium.read('__kv_log');
    const records: { entries: { op: KVLogOp }[] }[] = [];
    parseLogRecords(new Uint8Array(raw!), (r) => records.push(r as never));
    expect(records).toHaveLength(1);
    expect(records[0].entries).toHaveLength(1);
    expect(records[0].entries[0].op).toBe(KVLogOp.PUT);

    await eng.close();

    // 重开后数据完整
    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const all = await eng2.find('t', { table: 't' });
    expect(all).toHaveLength(1000);
    expect(all.find((r) => r.id === 500)!.v).toBe('CHANGED');
    await eng2.close();
  });

  it('事务混合操作（insert/update/delete/clear 语义）持久化正确', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
      v: { type: 'string' },
    }));
    await eng.insert('t', [{ id: '1', v: 'a' }, { id: '2', v: 'b' }, { id: '3', v: 'c' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.insert('t', [{ id: '4', v: 'd' }]);
    await eng.update('t', { table: 't', where: { id: '1' } }, { v: 'A' });
    await eng.delete('t', { table: 't', where: { id: '3' } });
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows.map((r) => r.id).sort()).toEqual(['1', '2', '4']);
    expect(rows.find((r) => r.id === '1')!.v).toBe('A');
    await eng2.close();
  });

  it('事务内 delete 后 reinsert 同主键 → put 最终态', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
      v: { type: 'string' },
    }));
    await eng.insert('t', [{ id: '1', v: 'old' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.delete('t', { table: 't', where: { id: '1' } });
    await eng.insert('t', [{ id: '1', v: 'new' }]);
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows).toHaveLength(1);
    expect(rows[0].v).toBe('new');
    await eng2.close();
  });

  it('事务内 clear 后 insert → 最终态仅新行', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
    }));
    await eng.insert('t', [{ id: '1' }, { id: '2' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.clear('t');
    await eng.insert('t', [{ id: '9' }]);
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows).toHaveLength(1);
    expect(rows[0].id).toBe('9');
    await eng2.close();
  });

  it('事务内主键变更 → 整表 diff 兜底路径正确', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
    }));
    await eng.insert('t', [{ id: '1' }, { id: '2' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.update('t', { table: 't', where: { id: '1' } }, { id: '10' });
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows.map((r) => r.id).sort()).toEqual(['10', '2']);
    await eng2.close();
  });

  it('多表事务 + 级联（SET NULL）持久化正确', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('users', {
      id: { type: 'string', primaryKey: true },
    }));
    await eng.createTable(createSchema('orders', {
      id: { type: 'string', primaryKey: true },
      user_id: { type: 'string', references: 'users.id', onDelete: 'SET NULL' },
    }));
    await eng.insert('users', [{ id: 'u1' }]);
    await eng.insert('orders', [{ id: 'o1', user_id: 'u1' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.delete('users', { table: 'users', where: { id: 'u1' } });
    await eng.insert('users', [{ id: 'u2' }]);
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const users = await eng2.find('users', { table: 'users' });
    expect(users.map((r) => r.id)).toEqual(['u2']);
    const orders = await eng2.find('orders', { table: 'orders' });
    expect(orders).toHaveLength(1);
    expect(orders[0].user_id).toBeNull();
    await eng2.close();
  });

  it('事务回滚后不落盘（增量追踪状态重置）', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
    }));
    await eng.insert('t', [{ id: '1' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.insert('t', [{ id: '2' }]);
    await eng.rollbackTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows).toHaveLength(1);
    await eng2.close();
  });

  it('连续事务互不串扰（第二事务增量正常）', async () => {
    const name = uniqueDB();
    const eng = new KVStoreEngine();
    await eng.open(name, 1);
    await eng.createTable(createSchema('t', {
      id: { type: 'string', primaryKey: true },
      v: { type: 'string' },
    }));
    await eng.insert('t', [{ id: '1', v: 'a' }, { id: '2', v: 'b' }]);
    await (eng as any).kv.checkpoint();

    await eng.beginTransaction();
    await eng.update('t', { table: 't', where: { id: '1' } }, { v: 'A' });
    await eng.commitTransaction();

    await eng.beginTransaction();
    await eng.update('t', { table: 't', where: { id: '2' } }, { v: 'B' });
    await eng.commitTransaction();
    await eng.close();

    const eng2 = new KVStoreEngine();
    await eng2.open(name, 1);
    const rows = await eng2.find('t', { table: 't' });
    expect(rows.find((r) => r.id === '1')!.v).toBe('A');
    expect(rows.find((r) => r.id === '2')!.v).toBe('B');
    await eng2.close();
  });
});

describe('v0.7.0 — EXPLAIN 真实索引信息', () => {
  it('主键/索引/无索引条件分别报告 pk/index:col/none', async () => {
    const db = new MetonaSqlark({ name: uniqueDB(), mode: 'aria', diskEngine: 'memory' });
    await db.init();
    await db.defineTable('t', {
      id: { type: 'string', primaryKey: true },
      tag: { type: 'string', index: true },
      other: { type: 'string' },
    });

    const byPk = await db.query("EXPLAIN SELECT * FROM t WHERE id = '1'") as Record<string, unknown>;
    expect(byPk.usingIndex).toBe('pk');

    const byIdx = await db.query("EXPLAIN SELECT * FROM t WHERE tag = 'x'") as Record<string, unknown>;
    expect(byIdx.usingIndex).toBe('index:tag');

    const none = await db.query("EXPLAIN SELECT * FROM t WHERE other = 'x'") as Record<string, unknown>;
    expect(none.usingIndex).toBe('none');
    await db.close();
  });

  it('EXPLAIN DELETE 同样报告索引信息（不执行）', async () => {
    const db = new MetonaSqlark({ name: uniqueDB(), mode: 'memory' });
    await db.init();
    await db.defineTable('t', { id: { type: 'string', primaryKey: true } });
    await db.query('INSERT INTO t VALUES (\'1\'), (\'2\')');
    const plan = await db.query('EXPLAIN DELETE FROM t WHERE id = \'1\'') as Record<string, unknown>;
    expect(plan.usingIndex).toBe('pk');
    expect(plan.estimatedRows).toBe(1);
    const rows = await db.query('SELECT * FROM t') as Record<string, unknown>[];
    expect(rows).toHaveLength(2);
    await db.close();
  });
});

describe('v0.7.0 — 错误码导出检查', () => {
  it('DatabaseError code 可访问', () => {
    const e = new DatabaseError('m', 'X');
    expect(e.code).toBe('X');
  });
});