thzxx
0dba1abf2a
工作流 C-1 / C-3 前半 + 测试代码类型检查。 【故障注入基座】新增 tests/helpers/storage-harness.ts + faulty-backend.ts - TransactionalFileStore：忠实 OPFS 提交语义（close 才可见）+ 字节级故障注入 （failNextWrite/Append/Delete、truncateAppendTo 撕裂写、crashPending 真崩溃） - 删除旧 opfs-mock：读返回内部引用、keepExistingData:false 不截断、close 空实现 导致"提交前可见"等真实缺陷无法被测出（31 个测试文件迁移至新 harness） - 删除 aria-opfs-backend 内的第三份重复 mock（含从未被断言使用的 writeCalls 死代码 与 entry.content.subarray 恒等分支） - FaultyBackend：包装任意 IStorageBackend 注入故障；crash() 明确区别于 close() （后者是优雅停机，会刷完写队列 —— 这正是此前所有"崩溃恢复"测试的真相） - 16 条基座自测证明注入真的生效（含 close 不能当崩溃的对照组） 【覆盖率口径】jest.config.cjs - 移除 '!src/**/index.ts'（该 glob 把 AriaEngine 主实现等 15 个实现文件整体 排除出统计，与 v0.2.6 曾承认过的问题同源），改为只排除纯类型声明文件并附理由 - 新增 coverageThreshold 门禁（此前完全不存在） - 真实基线：语句 90.66% / 分支 82.94% / 函数 94.36% / 行 93.43% - 修正 testMatch 使 tests/helpers 下的测试可被发现 【测试代码类型检查】tsconfig.test.json + npm run typecheck:tests - 修复 103 个测试代码类型错误（此前 babel 剥离类型 + tsconfig 排除 tests，全部隐藏） - 新增 tests/helpers/assertions.ts：nonNull/decode/rows/object/engineMethod/expectCode 以断言收窄替代 as any - 消除 21 个 lint warning（含 v043-hardening 中定义后从未调用的 mockOPFS 死代码） - parser.test.ts 12 处 toBeDefined() 空断言升级为结构断言（并新增 AND/OR 优先级用例， 当前红灯，对应总账第 11 项，将在工作流 A 修复） 【版本契约】新增 tests/version-contract.test.ts - 校验 src VERSION / package.json / dist 三者一致，替代两处硬编码版本字面量 【CI 门禁】.gitea/workflows/ci.yml - lint 去掉 continue-on-error（此前永远不让 CI 变红） - 新增 tests 类型检查、--coverage 覆盖率门禁、dist 与源码同步校验 - 版本 0.7.4 升至 0.8.0
376 lines
15 KiB
TypeScript
376 lines
15 KiB
TypeScript
/**
|
||
* v0.3.0 SQL 功能扩展测试
|
||
* @module tests/sql-ext
|
||
*
|
||
* 覆盖：多语句 / 事务语句 / INSERT...SELECT / UNION / CREATE INDEX / EXISTS
|
||
*/
|
||
import { MetonaSqlark } from '../src/core';
|
||
import '../src/connection-manager';
|
||
import { parse, parseAll } from '../src/sql/parser';
|
||
|
||
import { resetOPFSMock } from './helpers/storage-harness';
|
||
|
||
beforeEach(() => { resetOPFSMock(); });
|
||
|
||
async function createDb(mode: 'memory' | 'disk' | 'aria' = 'memory') {
|
||
const db = new MetonaSqlark({ name: `sql-ext-${mode}-${Date.now()}-${Math.random()}`, mode, diskEngine: 'opfs' });
|
||
await db.init();
|
||
await db.defineTable('users', {
|
||
id: { type: 'string', primaryKey: true },
|
||
name: { type: 'string' },
|
||
age: { type: 'number' },
|
||
city: { type: 'string' },
|
||
});
|
||
await db.defineTable('orders', {
|
||
id: { type: 'string', primaryKey: true },
|
||
user_id: { type: 'string' },
|
||
amount: { type: 'number' },
|
||
});
|
||
await db.query(`INSERT INTO users VALUES ('u1', 'Alice', 30, 'Beijing')`);
|
||
await db.query(`INSERT INTO users VALUES ('u2', 'Bob', 25, 'Shanghai')`);
|
||
await db.query(`INSERT INTO users VALUES ('u3', 'Carol', 35, 'Beijing')`);
|
||
await db.query(`INSERT INTO users VALUES ('u4', 'Dave', 28, 'Shenzhen')`);
|
||
await db.query(`INSERT INTO orders VALUES ('o1', 'u1', 100)`);
|
||
await db.query(`INSERT INTO orders VALUES ('o2', 'u1', 200)`);
|
||
await db.query(`INSERT INTO orders VALUES ('o3', 'u2', 50)`);
|
||
return db;
|
||
}
|
||
|
||
// ===================================================================
|
||
// 多语句 parseAll
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] 多语句支持', () => {
|
||
test('parseAll 解析分号分隔的多条语句', () => {
|
||
const stmts = parseAll('SELECT * FROM a; INSERT INTO b VALUES (1); DELETE FROM c WHERE id = 1');
|
||
expect(stmts).toHaveLength(3);
|
||
expect(stmts[0].type).toBe('SELECT');
|
||
expect(stmts[1].type).toBe('INSERT');
|
||
expect(stmts[2].type).toBe('DELETE');
|
||
});
|
||
|
||
test('parseAll 忽略多余分号与尾部空语句', () => {
|
||
const stmts = parseAll(';;SELECT * FROM a;;;');
|
||
expect(stmts).toHaveLength(1);
|
||
expect(stmts[0].type).toBe('SELECT');
|
||
});
|
||
|
||
test('parseAll 支持事务语句', () => {
|
||
const stmts = parseAll('BEGIN; INSERT INTO a VALUES (1); COMMIT');
|
||
expect(stmts.map((s) => s.type)).toEqual(['BEGIN', 'INSERT', 'COMMIT']);
|
||
});
|
||
|
||
test('parse 保持单语句兼容', () => {
|
||
expect(parse('SELECT * FROM a').type).toBe('SELECT');
|
||
});
|
||
|
||
test('parseAll 语句间缺分号报错', () => {
|
||
expect(() => parseAll('SELECT * FROM a SELECT * FROM b')).toThrow(/Expected ';'/);
|
||
});
|
||
|
||
test('core.query 顺序执行多语句', async () => {
|
||
const db = await createDb();
|
||
await db.query('CREATE TABLE logs (id STRING PRIMARY KEY, msg STRING); INSERT INTO logs VALUES (\'l1\', \'hello\'); INSERT INTO logs VALUES (\'l2\', \'world\')');
|
||
const rows = await db.query('SELECT * FROM logs') as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
});
|
||
|
||
// ===================================================================
|
||
// 事务语句 BEGIN / COMMIT / ROLLBACK
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] 事务语句', () => {
|
||
test('BEGIN + INSERT + COMMIT 持久化', async () => {
|
||
const db = await createDb();
|
||
await db.query('BEGIN');
|
||
await db.query(`INSERT INTO users VALUES ('u5', 'Eve', 22, 'Beijing')`);
|
||
const visible = await db.query('SELECT * FROM users') as Record<string, unknown>[];
|
||
expect(visible).toHaveLength(5); // 事务内可见
|
||
await db.query('COMMIT');
|
||
const after = await db.query('SELECT * FROM users') as Record<string, unknown>[];
|
||
expect(after).toHaveLength(5);
|
||
await db.close();
|
||
});
|
||
|
||
test('BEGIN + INSERT + ROLLBACK 回滚', async () => {
|
||
const db = await createDb();
|
||
await db.query('BEGIN');
|
||
await db.query(`INSERT INTO users VALUES ('u5', 'Eve', 22, 'Beijing')`);
|
||
await db.query('ROLLBACK');
|
||
const after = await db.query('SELECT * FROM users') as Record<string, unknown>[];
|
||
expect(after).toHaveLength(4);
|
||
await db.close();
|
||
});
|
||
|
||
test('BEGIN 嵌套报错', async () => {
|
||
const db = await createDb();
|
||
await db.query('BEGIN');
|
||
await expect(db.query('BEGIN')).rejects.toThrow(/TX_ACTIVE|Transaction already/);
|
||
await db.query('ROLLBACK');
|
||
await db.close();
|
||
});
|
||
|
||
test('无事务时 COMMIT 报错', async () => {
|
||
const db = await createDb();
|
||
await expect(db.query('COMMIT')).rejects.toThrow(/TX_NONE|No active transaction/);
|
||
await db.close();
|
||
});
|
||
|
||
test('事务语句在 Aria 引擎上可用', async () => {
|
||
const db = await createDb('aria');
|
||
await db.query('BEGIN');
|
||
await db.query(`INSERT INTO users VALUES ('u5', 'Eve', 22, 'Beijing')`);
|
||
await db.query('ROLLBACK');
|
||
const after = await db.query('SELECT * FROM users') as Record<string, unknown>[];
|
||
expect(after).toHaveLength(4);
|
||
await db.close();
|
||
});
|
||
});
|
||
|
||
// ===================================================================
|
||
// INSERT INTO ... SELECT
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] INSERT INTO ... SELECT', () => {
|
||
test('INSERT SELECT 全列复制', async () => {
|
||
const db = await createDb();
|
||
await db.defineTable('users_backup', {
|
||
id: { type: 'string', primaryKey: true },
|
||
name: { type: 'string' },
|
||
age: { type: 'number' },
|
||
city: { type: 'string' },
|
||
});
|
||
await db.query('INSERT INTO users_backup SELECT * FROM users');
|
||
const rows = await db.query('SELECT * FROM users_backup') as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(4);
|
||
expect(rows[0].name).toBe('Alice');
|
||
await db.close();
|
||
});
|
||
|
||
test('INSERT SELECT 带 WHERE 过滤', async () => {
|
||
const db = await createDb();
|
||
await db.defineTable('beijing_users', {
|
||
id: { type: 'string', primaryKey: true },
|
||
name: { type: 'string' },
|
||
age: { type: 'number' },
|
||
city: { type: 'string' },
|
||
});
|
||
await db.query(`INSERT INTO beijing_users SELECT * FROM users WHERE city = 'Beijing'`);
|
||
const rows = await db.query('SELECT * FROM beijing_users') as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('INSERT SELECT 指定列映射', async () => {
|
||
const db = await createDb();
|
||
await db.defineTable('names', { id: { type: 'string', primaryKey: true }, n: { type: 'string' } });
|
||
await db.query('INSERT INTO names (id, n) SELECT id, name FROM users');
|
||
const rows = await db.query('SELECT * FROM names') as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(4);
|
||
expect(rows[0].n).toBe('Alice');
|
||
await db.close();
|
||
});
|
||
|
||
test('INSERT SELECT 语法解析', () => {
|
||
const stmt = parse('INSERT INTO a (x, y) SELECT p, q FROM b WHERE r > 1') as any;
|
||
expect(stmt.type).toBe('INSERT');
|
||
expect(stmt.select).toBeDefined();
|
||
expect(stmt.select.type).toBe('SELECT');
|
||
expect(stmt.columns).toEqual(['x', 'y']);
|
||
});
|
||
});
|
||
|
||
// ===================================================================
|
||
// UNION / UNION ALL
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] UNION / UNION ALL', () => {
|
||
test('UNION 去重合并', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT name FROM users WHERE city = 'Beijing' UNION SELECT name FROM users WHERE age < 30`) as Record<string, unknown>[];
|
||
// Beijing: Alice, Carol（Alice 同时 age30 不重复）；age<30: Bob, Dave
|
||
expect(rows).toHaveLength(4);
|
||
await db.close();
|
||
});
|
||
|
||
test('UNION ALL 不去重', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT city FROM users WHERE city = 'Beijing' UNION ALL SELECT city FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(4); // 2 + 2
|
||
await db.close();
|
||
});
|
||
|
||
test('UNION 对相同行去重', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT city FROM users WHERE city = 'Beijing' UNION SELECT city FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
// 左右各 2 行同值 → 去重后 1 行
|
||
expect(rows).toHaveLength(1);
|
||
expect(rows[0].city).toBe('Beijing');
|
||
await db.close();
|
||
});
|
||
|
||
test('UNION 链式（三表合并）', async () => {
|
||
const db = await createDb();
|
||
await db.query(`INSERT INTO users VALUES ('u5', 'Eve', 22, 'Beijing')`);
|
||
const rows = await db.query(`SELECT city FROM users WHERE city = 'Beijing' UNION SELECT city FROM users WHERE city = 'Shanghai' UNION SELECT city FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
// Beijing(3) + Shanghai(1) + Beijing(3 重复去重) → 2 个城市
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('UNION 语法解析', () => {
|
||
const stmt = parse('SELECT a FROM t1 UNION SELECT b FROM t2') as any;
|
||
expect(stmt.type).toBe('SELECT_UNION');
|
||
expect(stmt.all).toBeUndefined();
|
||
const stmtAll = parse('SELECT a FROM t1 UNION ALL SELECT b FROM t2') as any;
|
||
expect(stmtAll.type).toBe('SELECT_UNION');
|
||
expect(stmtAll.all).toBe(true);
|
||
});
|
||
|
||
test('UNION 在 Aria 引擎上可用', async () => {
|
||
const db = await createDb('aria');
|
||
const rows = await db.query(`SELECT city FROM users WHERE city = 'Beijing' UNION SELECT city FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(1);
|
||
await db.close();
|
||
});
|
||
});
|
||
|
||
// ===================================================================
|
||
// CREATE INDEX / DROP INDEX
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] CREATE INDEX / DROP INDEX', () => {
|
||
test('CREATE INDEX 后索引查询可用（Memory）', async () => {
|
||
const db = await createDb();
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)');
|
||
const rows = await db.query(`SELECT * FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE INDEX 后索引查询可用（Aria）', async () => {
|
||
const db = await createDb('aria');
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)');
|
||
const rows = await db.query(`SELECT * FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE INDEX 对已有数据立即生效（索引填充）', async () => {
|
||
const db = await createDb();
|
||
await db.query('INSERT INTO users VALUES (\'u5\', \'Eve\', 22, \'Beijing\')');
|
||
await db.query('CREATE INDEX idx_users_age ON users (age)');
|
||
const rows = await db.query(`SELECT * FROM users WHERE age = 22`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(1);
|
||
expect(rows[0].id).toBe('u5');
|
||
await db.close();
|
||
});
|
||
|
||
test('DROP INDEX 后查询回退全表扫描', async () => {
|
||
const db = await createDb();
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)');
|
||
await db.query('DROP INDEX idx_users_city ON users (city)');
|
||
const rows = await db.query(`SELECT * FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE INDEX 重复创建幂等', async () => {
|
||
const db = await createDb();
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)');
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)'); // 不抛错
|
||
const rows = await db.query(`SELECT * FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE INDEX 不存在列报错', async () => {
|
||
const db = await createDb();
|
||
const err = await db.query('CREATE INDEX idx_bad ON users (nonexistent)').catch((e: unknown) => e);
|
||
expect(err).toBeInstanceOf(Error);
|
||
expect((err as { code?: string }).code).toBe('COLUMN_NOT_FOUND');
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE INDEX 后插入新数据索引同步更新', async () => {
|
||
const db = await createDb();
|
||
await db.query('CREATE INDEX idx_users_city ON users (city)');
|
||
await db.query('INSERT INTO users VALUES (\'u5\', \'Eve\', 22, \'Beijing\')');
|
||
const rows = await db.query(`SELECT * FROM users WHERE city = 'Beijing'`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(3);
|
||
await db.close();
|
||
});
|
||
|
||
test('DROP INDEX Aria 主键索引受保护', async () => {
|
||
const db = await createDb('aria');
|
||
await expect(db.query('DROP INDEX pk ON users (id)')).rejects.toThrow(/Cannot drop primary key/);
|
||
await db.close();
|
||
});
|
||
|
||
test('CREATE UNIQUE INDEX 语法解析', () => {
|
||
const stmt = parse('CREATE UNIQUE INDEX idx_u ON users (email)') as any;
|
||
expect(stmt.type).toBe('CREATE_INDEX');
|
||
expect(stmt.unique).toBe(true);
|
||
expect(stmt.column).toBe('email');
|
||
});
|
||
|
||
test('CREATE INDEX / DROP INDEX 语法解析', () => {
|
||
const stmt = parse('CREATE INDEX idx_c ON users (city)') as any;
|
||
expect(stmt).toMatchObject({ type: 'CREATE_INDEX', name: 'idx_c', table: 'users', column: 'city' });
|
||
const drop = parse('DROP INDEX idx_c ON users (city)') as any;
|
||
expect(drop).toMatchObject({ type: 'DROP_INDEX', name: 'idx_c', table: 'users', column: 'city' });
|
||
});
|
||
});
|
||
|
||
// ===================================================================
|
||
// EXISTS / NOT EXISTS
|
||
// ===================================================================
|
||
|
||
describe('[v0.3.0] EXISTS / NOT EXISTS', () => {
|
||
test('EXISTS 子查询为真返回行', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)`) as Record<string, unknown>[];
|
||
// 有订单的用户：u1, u2
|
||
expect(rows).toHaveLength(2);
|
||
expect(rows.map((r) => r.id).sort()).toEqual(['u1', 'u2']);
|
||
await db.close();
|
||
});
|
||
|
||
test('NOT EXISTS 返回无关联行', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)`) as Record<string, unknown>[];
|
||
// 无订单用户：u3, u4
|
||
expect(rows).toHaveLength(2);
|
||
expect(rows.map((r) => r.id).sort()).toEqual(['u3', 'u4']);
|
||
await db.close();
|
||
});
|
||
|
||
test('EXISTS 与 AND 组合', async () => {
|
||
const db = await createDb();
|
||
const rows = await db.query(`SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 150) AND u.city = 'Beijing'`) as Record<string, unknown>[];
|
||
// o2 金额 200 > 150 属于 u1（Beijing）
|
||
expect(rows).toHaveLength(1);
|
||
expect(rows[0].id).toBe('u1');
|
||
await db.close();
|
||
});
|
||
|
||
test('EXISTS 语法解析', () => {
|
||
const stmt = parse(`SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders)`) as any;
|
||
expect(stmt.where.$exists).toBeDefined();
|
||
expect(stmt.where.$exists.$subquery.type).toBe('SELECT');
|
||
|
||
const notStmt = parse(`SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders)`) as any;
|
||
expect(notStmt.where.$exists).toBeDefined();
|
||
expect(notStmt.where.$exists.$negate).toBe(true);
|
||
});
|
||
|
||
test('EXISTS 在 Aria 引擎上可用', async () => {
|
||
const db = await createDb('aria');
|
||
const rows = await db.query(`SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)`) as Record<string, unknown>[];
|
||
expect(rows).toHaveLength(2);
|
||
await db.close();
|
||
});
|
||
});
|