朱子纯 / erp-workflow-plugin

// lib/validate-ddl.test.mjs — 单测：docs/03 表格 ↔ DDL 5 维 diff
import { test } from 'node:test'
import assert from 'node:assert/strict'
import { parseDocsTables, parseDDL, diffSchema } from './validate-ddl.mjs'

const DOCS = `## \`t_user\`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n| sName | varchar(50) |\n`
const DDL = `CREATE TABLE t_user ( iId bigint PRIMARY KEY, sName varchar(50) );`

test('matching schema yields empty diff', () => {
  const d = diffSchema(parseDocsTables(DOCS), parseDDL(DDL))
  assert.deepEqual(d.missingTables, [])
  assert.deepEqual(d.columnMismatches, [])
})

test('missing column is reported', () => {
  const ddl2 = `CREATE TABLE t_user ( iId bigint );`
  const d = diffSchema(parseDocsTables(DOCS), parseDDL(ddl2))
  assert.ok(d.columnMismatches.some(m => m.table === 't_user' && m.column === 'sName'))
})

// ── parseDocsTables ──────────────────────────────────────────────
test('parseDocsTables: 列名/类型 from markdown rows under ## `table` header', () => {
  const tables = parseDocsTables(DOCS)
  assert.equal(tables.size, 1)
  const t = tables.get('t_user')
  assert.ok(t, 'table t_user parsed')
  assert.deepEqual([...t.columns.keys()], ['iId', 'sName'])
  assert.equal(t.columns.get('iId'), 'bigint')
  assert.equal(t.columns.get('sName'), 'varchar(50)')
})

test('parseDocsTables: real docs/03 format — ## `t` — purpose + ### 字段 + backtick cols', () => {
  const docs = [
    '## `t_order` — 订单主表',
    '',
    '### 字段',
    '',
    '| 字段 | 类型 | Nullable | 默认 | 业务含义 |',
    '|---|---|---|---|---|',
    '| `iIncrement` | int | 否 | 自增 | 主键 |',
    '| `sId` | varchar(100) | 是 | uuid | 业务ID |',
    '',
    '### 索引',
    '- `pk` (PRIMARY): iIncrement',
    '',
    '## `t_item` — 明细',
    '',
    '| 列 | 类型 |',
    '|---|---|',
    '| iId | bigint |',
    '',
  ].join('\n')
  const tables = parseDocsTables(docs)
  assert.deepEqual([...tables.keys()].sort(), ['t_item', 't_order'])
  const order = tables.get('t_order')
  assert.deepEqual([...order.columns.keys()], ['iIncrement', 'sId'])
  assert.equal(order.columns.get('iIncrement'), 'int')
  assert.equal(order.columns.get('sId'), 'varchar(100)')
  // header separator row and header label row must be skipped
  assert.equal(order.columns.has('字段'), false)
  assert.equal(order.columns.has('---'), false)
})

// 全链路：模板格式 docs/03（### 字段 + ### 索引 + ### 外键 bullet）→ parseDocsTables 必须
// 把索引/外键解析进 Set（回归 C2：此前 parseDocsTables 从不写 indexes/foreignKeys）。
const DOCS_FULL = [
  '## `t_order` — 订单主表',
  '',
  '### 字段',
  '| 字段 | 类型 | Nullable | 默认 | 业务含义 |',
  '|---|---|---|---|---|',
  '| `iId` | bigint | 否 | 自增 | 主键 |',
  '| `sUserId` | varchar(100) | 否 | — | 用户ID |',
  '',
  '### 索引',
  '- `pk` (PRIMARY): iId',
  '- `idx_user` (index): sUserId',
  '',
  '### 外键',
  '- `fk_user`: sUserId → t_user.sId (CASCADE)',
  '',
].join('\n')
const DDL_FULL = [
  'CREATE TABLE `t_order` (',
  '  `iId` bigint NOT NULL AUTO_INCREMENT,',
  '  `sUserId` varchar(100) NOT NULL,',
  '  PRIMARY KEY (`iId`),',
  '  KEY `idx_user` (`sUserId`),',
  '  CONSTRAINT `fk_user` FOREIGN KEY (`sUserId`) REFERENCES `t_user` (`sId`) ON DELETE CASCADE',
  ') ENGINE=InnoDB;',
].join('\n')

test('parseDocsTables: parses ### 索引 / ### 外键 bullets into sets (C2 regression)', () => {
  const t = parseDocsTables(DOCS_FULL).get('t_order')
  assert.ok(t)
  assert.ok(t.indexes.has('PRIMARY'), 'PRIMARY index normalized')
  assert.ok(t.indexes.has('idx_user:INDEX:sUserId'),
    'named index normalized to name:kind:cols — got: ' + [...t.indexes])
  assert.ok(t.foreignKeys.has('sUserId->t_user(sId):CASCADE'),
    'FK normalized to parseDDL form with on-delete — got: ' + [...t.foreignKeys])
})

test('full chain: matching docs/03 (with indexes+FK) ↔ DDL yields no diff (C2 regression)', () => {
  const d = diffSchema(parseDocsTables(DOCS_FULL), parseDDL(DDL_FULL))
  assert.deepEqual(d.indexMismatches, [], 'index dimension clean')
  assert.deepEqual(d.foreignKeyMismatches, [], 'FK dimension clean')
  assert.equal(d.hasDiff, false, 'no spurious diff on a faithful schema')
})

test('full chain: a real FK present in docs but missing from DDL is caught', () => {
  const ddlNoFk = [
    'CREATE TABLE `t_order` (',
    '  `iId` bigint NOT NULL AUTO_INCREMENT,',
    '  `sUserId` varchar(100) NOT NULL,',
    '  PRIMARY KEY (`iId`),',
    '  KEY `idx_user` (`sUserId`)',
    ') ENGINE=InnoDB;',
  ].join('\n')
  const d = diffSchema(parseDocsTables(DOCS_FULL), parseDDL(ddlNoFk))
  assert.ok(d.foreignKeyMismatches.some(m => m.side === 'docs' && m.foreignKey === 'sUserId->t_user(sId):CASCADE'))
  assert.equal(d.hasDiff, true)
})

test('parseDDL: CREATE TABLE inside a comment is NOT counted as a table (L4)', () => {
  const ddl = [
    '-- CREATE TABLE ghost_line ( x int );',
    '/* CREATE TABLE ghost_block ( y int ); */',
    '# CREATE TABLE ghost_hash ( z int );',
    'CREATE TABLE real_one ( a int );',
  ].join('\n')
  const tables = parseDDL(ddl)
  assert.deepEqual([...tables.keys()], ['real_one'])
})

test('parseDocsTables: top-level ## headers like "## 一、全局约定" are NOT tables', () => {
  const docs = [
    '## 一、全局约定（人工填）',
    '- 数据库名: erp',
    '',
    '## `t_user`',
    '| 列 | 类型 |',
    '|---|---|',
    '| iId | bigint |',
    '',
  ].join('\n')
  const tables = parseDocsTables(docs)
  assert.deepEqual([...tables.keys()], ['t_user'])
})

// ── parseDDL ─────────────────────────────────────────────────────
test('parseDDL: columns, types, indexes, foreign keys (backtick-quoted)', () => {
  const ddl = [
    'CREATE TABLE `t_order` (',
    '  `iIncrement` int NOT NULL AUTO_INCREMENT,',
    '  `sId` varchar(100) DEFAULT NULL,',
    '  `sUserId` varchar(100) DEFAULT NULL,',
    '  PRIMARY KEY (`iIncrement`),',
    '  UNIQUE KEY `uk_sid` (`sId`),',
    '  KEY `idx_user` (`sUserId`),',
    '  CONSTRAINT `fk_user` FOREIGN KEY (`sUserId`) REFERENCES `t_user` (`sId`)',
    ') ENGINE=InnoDB;',
  ].join('\n')
  const tables = parseDDL(ddl)
  const t = tables.get('t_order')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['iIncrement', 'sId', 'sUserId'])
  assert.equal(t.columns.get('sId'), 'varchar(100)')
  // index keys (named) collected; PRIMARY collected too
  assert.ok(t.indexes.has('uk_sid:UNIQUE:sId'), 'unique index normalized — got: ' + [...t.indexes])
  assert.ok(t.indexes.has('idx_user:INDEX:sUserId'), 'named index normalized — got: ' + [...t.indexes])
  assert.ok([...t.indexes].some(i => i.toUpperCase().includes('PRIMARY')))
  // foreign key collected
  assert.ok([...t.foreignKeys].some(fk => fk.includes('sUserId') && fk.includes('t_user')))
})

test('parseDDL: unquoted identifiers and inline PRIMARY KEY', () => {
  const tables = parseDDL(DDL)
  const t = tables.get('t_user')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['iId', 'sName'])
  assert.equal(t.columns.get('iId'), 'bigint')
})

test('parseDDL: multiple tables', () => {
  const ddl = 'CREATE TABLE a (x int); CREATE TABLE b (y bigint);'
  const tables = parseDDL(ddl)
  assert.deepEqual([...tables.keys()].sort(), ['a', 'b'])
})

// ── diffSchema 5 dimensions ──────────────────────────────────────
test('diffSchema: missing table (in docs, not in DDL) reported', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE other ( z int );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.missingTables.includes('t_user'))
  assert.ok(d.extraTables.includes('other'))
})

test('diffSchema: type mismatch reported', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE t_user ( iId int );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.typeMismatches.some(m => m.table === 't_user' && m.column === 'iId' && m.docsType === 'bigint' && m.ddlType === 'int'))
})

test('diffSchema: extra column in DDL reported as columnMismatch', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE t_user ( iId bigint, extra varchar(10) );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.columnMismatches.some(m => m.table === 't_user' && m.column === 'extra' && m.side === 'ddl'))
})

test('diffSchema: index dimension diff reported', () => {
  const docs = new Map([['t', { columns: new Map([['c', 'int']]), indexes: new Set(['idx_c:INDEX:c']), foreignKeys: new Set() }]])
  const ddl = parseDDL('CREATE TABLE t ( c int );') // no indexes
  const d = diffSchema(docs, ddl)
  assert.ok(d.indexMismatches.some(m => m.table === 't' && m.index === 'idx_c:INDEX:c'))
})

test('diffSchema: foreign-key dimension diff reported', () => {
  const docs = new Map([['t', { columns: new Map([['c', 'int']]), indexes: new Set(), foreignKeys: new Set(['c->other']) }]])
  const ddl = parseDDL('CREATE TABLE t ( c int );') // no FKs
  const d = diffSchema(docs, ddl)
  assert.ok(d.foreignKeyMismatches.some(m => m.table === 't' && m.foreignKey === 'c->other'))
})

test('diffSchema: hasDiff is false when everything matches, true otherwise', () => {
  const ok = diffSchema(parseDocsTables(DOCS), parseDDL(DDL))
  assert.equal(ok.hasDiff, false)
  const bad = diffSchema(parseDocsTables(DOCS), parseDDL('CREATE TABLE t_user ( iId bigint );'))
  assert.equal(bad.hasDiff, true)
})

// ── 字符串字面量感知（回归）──────────────────────────────────────
test('parseDDL: DEFAULT \'a--b\' 字面量中的 -- 不应被当行注释剥离', () => {
  const ddl = "CREATE TABLE t ( s varchar(10) DEFAULT 'a--b', x int );"
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['s', 'x'], '字面量内 -- 不应吞掉后续列 x')
})

test('parseDDL: DEFAULT \'#tag\' 字面量中的 # 不应被当行注释剥离', () => {
  const ddl = "CREATE TABLE t ( s varchar(10) DEFAULT '#tag', x int );"
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['s', 'x'], '字面量内 # 不应吞掉后续列 x')
})

test('parseDDL: DEFAULT \')\' 字面量中的右括号不应提前截断表体', () => {
  const ddl = "CREATE TABLE t ( s varchar(10) DEFAULT ')', x int );"
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['s', 'x'], '字面量内 ) 不应让 depth 提前归零截断表体')
})

test('parseDDL: DEFAULT \'(a,b)\' 字面量中的逗号不应被当顶层分隔', () => {
  const ddl = "CREATE TABLE t ( s varchar(10) DEFAULT '(a,b)', x int );"
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['s', 'x'])
})

// ── schema 限定表名（回归）───────────────────────────────────────
test('parseDDL: CREATE TABLE db.t 与 `db`.`t` 都应解析（取末段为表名）', () => {
  const tables1 = parseDDL('CREATE TABLE mydb.t_user ( id int );')
  assert.deepEqual([...tables1.keys()], ['t_user'])
  const tables2 = parseDDL('CREATE TABLE `mydb`.`t_user` ( id int );')
  assert.deepEqual([...tables2.keys()], ['t_user'])
})

// ── 复合外键 docs↔DDL 对称（回归）────────────────────────────────
test('parseDocsTables: 复合外键 - colA, colB → other.idA, idB 应平铺成 colA,colB->other(idA,idB)', () => {
  const docs = [
    '## `t_link`',
    '### 字段',
    '| 列 | 类型 |',
    '|---|---|',
    '| `colA` | int |',
    '| `colB` | int |',
    '### 外键',
    '- `fk_x`: colA, colB → other.idA, idB (CASCADE)',
  ].join('\n')
  const t = parseDocsTables(docs).get('t_link')
  assert.ok(t)
  assert.ok(t.foreignKeys.has('colA,colB->other(idA,idB):CASCADE'),
    'docs-side composite FK should normalize the same way as parseDDL — got: ' + [...t.foreignKeys])
})

test('full chain: 复合外键 docs ↔ DDL 一致时不应误报双向 mismatch', () => {
  const docs = [
    '## `t_link`',
    '### 字段',
    '| 列 | 类型 |',
    '|---|---|',
    '| `colA` | int |',
    '| `colB` | int |',
    '### 外键',
    '- `fk_x`: colA, colB → other.(idA, idB)',
  ].join('\n')
  const ddl = [
    'CREATE TABLE `t_link` (',
    '  `colA` int NOT NULL,',
    '  `colB` int NOT NULL,',
    '  CONSTRAINT `fk_x` FOREIGN KEY (`colA`, `colB`) REFERENCES `other` (`idA`, `idB`)',
    ') ENGINE=InnoDB;',
  ].join('\n')
  const d = diffSchema(parseDocsTables(docs), parseDDL(ddl))
  assert.deepEqual(d.foreignKeyMismatches, [],
    '复合 FK 一致时不应误报 — got: ' + JSON.stringify(d.foreignKeyMismatches))
})

// ── 未加引号的保留字列名（回归）─────────────────────────────────
test('parseDDL: 未加引号的保留字列名 `key varchar(...)` 不应被误判为索引也不应制造幽灵列（fix #2）', () => {
  // 列名 key 未加反引号，且后面跟的是 `varchar(`（一个类型而非 `key <name> (`）。
  // 新策略：未加反引号的保留字列名一律被跳过；用户需用反引号包裹保留字列名。
  const ddl = 'CREATE TABLE t ( id int, key varchar(10) );'
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.equal(t.columns.has('key'), false, '未加反引号的 key 应被跳过，不入 columns')
  assert.equal(t.indexes.size, 0, '也不应被当索引')
})

test('parseDDL: 反引号包裹的保留字列名应正常解析（fix #2）', () => {
  const ddl = 'CREATE TABLE t ( id int, `key` varchar(10) );'
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.ok(t.columns.has('key'), '加了反引号的 key 应被解析为普通列')
  assert.equal(t.columns.get('key'), 'varchar(10)')
})

// ── #2 KEY/INDEX 启发式 fallthrough 不应制造幽灵列 ──────────────
test('parseDDL: `KEY varchar (id)` 不应制造名为 `KEY` 的幽灵列（fix #2）', () => {
  const ddl = 'CREATE TABLE t ( id int, KEY varchar (id) );'
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['id'], '不应出现 KEY 列')
  // varchar 是类型关键字，启发式跳过该项 → 既不入列也不入索引
  assert.equal(t.indexes.size, 0, '保留字 + 类型名时该项应被跳过')
})

test('parseDDL: `UNIQUE KEY double (c)` 不应被解析为列（fix #2/#20）', () => {
  const ddl = 'CREATE TABLE t ( c int, UNIQUE KEY double (c) );'
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['c'], '不应出现 UNIQUE/KEY 列')
})

test('parseDDL: `KEY decimal (c)` 不应被解析为列（fix #2/#20）', () => {
  const ddl = 'CREATE TABLE t ( c int, KEY decimal (c) );'
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['c'])
})

// ── #3 REFERENCES schema-qualified table ─────────────────────────
test('parseDDL: FK REFERENCES mydb.users(id) 归一化为 uid->users(id)（fix #3）', () => {
  const ddl = [
    'CREATE TABLE t (',
    '  uid int NOT NULL,',
    '  FOREIGN KEY (uid) REFERENCES mydb.users(id)',
    ');',
  ].join('\n')
  const t = parseDDL(ddl).get('t')
  assert.ok(t)
  assert.ok(t.foreignKeys.has('uid->users(id):RESTRICT'),
    'FK 表名应取末段 users 并附默认 on-delete — got: ' + [...t.foreignKeys])
})

// ── #4 extractType 保留 unsigned/signed 修饰 ─────────────────────
test('extractType: `int unsigned` vs `int unsigned` 匹配，`int` vs `int unsigned` 报 mismatch（fix #4）', () => {
  const docsOk = parseDocsTables('## `t`\n| 列 | 类型 |\n|---|---|\n| id | int unsigned |\n')
  const ddlOk = parseDDL('CREATE TABLE t ( id int unsigned );')
  const ok = diffSchema(docsOk, ddlOk)
  assert.deepEqual(ok.typeMismatches, [], 'unsigned 两侧一致不应报错 — got: ' + JSON.stringify(ok.typeMismatches))

  const docsMix = parseDocsTables('## `t`\n| 列 | 类型 |\n|---|---|\n| id | int unsigned |\n')
  const ddlMix = parseDDL('CREATE TABLE t ( id int );')
  const bad = diffSchema(docsMix, ddlMix)
  assert.ok(bad.typeMismatches.some(m => m.column === 'id' && m.docsType === 'int unsigned' && m.ddlType === 'int'),
    '一侧带 unsigned 一侧不带应报 mismatch — got: ' + JSON.stringify(bad.typeMismatches))
})

// ── #9 散文 bullet 不应被当 FK / 索引 ────────────────────────────
test('parseDocsTables: ### 外键 下的散文 bullet (含 `>`) 不应被当外键（fix #9）', () => {
  const docs = '## `t`\n### 外键\n- note: a > users.id\n'
  const t = parseDocsTables(docs).get('t')
  assert.ok(t)
  assert.equal(t.foreignKeys.size, 0, 'bare `>` 不再作为外键箭头 — got: ' + [...t.foreignKeys])
})

test('parseDocsTables: ### 索引 下纯散文 bullet 不应被当索引（fix #9）', () => {
  const docs = '## `t`\n### 索引\n- This bullet is not an index entry\n'
  const t = parseDocsTables(docs).get('t')
  assert.ok(t)
  assert.equal(t.indexes.size, 0, '散文 bullet 不再制造幽灵索引 — got: ' + [...t.indexes])
})

// ── #10 索引比较包含列与 UNIQUE-ness ────────────────────────────
test('diffSchema: 同名索引列不同应报 mismatch（fix #10）', () => {
  const docs = parseDocsTables([
    '## `t`',
    '### 字段',
    '| 列 | 类型 |',
    '|---|---|',
    '| user_id | int |',
    '| wrong_col | int |',
    '### 索引',
    '- `idx_user` (index): user_id',
  ].join('\n'))
  const ddl = parseDDL([
    'CREATE TABLE `t` (',
    '  `user_id` int,',
    '  `wrong_col` int,',
    '  KEY `idx_user` (`wrong_col`)',
    ') ENGINE=InnoDB;',
  ].join('\n'))
  const d = diffSchema(docs, ddl)
  assert.ok(d.indexMismatches.length > 0, '同名但列不同应报 — got: ' + JSON.stringify(d.indexMismatches))
})

test('diffSchema: 同名索引 UNIQUE vs 非 UNIQUE 应报 mismatch（fix #10）', () => {
  const docs = parseDocsTables([
    '## `t`',
    '### 字段',
    '| 列 | 类型 |',
    '|---|---|',
    '| c | int |',
    '### 索引',
    '- `uk_c` (unique): c',
  ].join('\n'))
  const ddl = parseDDL([
    'CREATE TABLE `t` (',
    '  `c` int,',
    '  KEY `uk_c` (`c`)',
    ') ENGINE=InnoDB;',
  ].join('\n'))
  const d = diffSchema(docs, ddl)
  assert.ok(d.indexMismatches.length > 0, 'UNIQUE vs INDEX 应报 — got: ' + JSON.stringify(d.indexMismatches))
})

// ── #11 ON DELETE actions differentiated ─────────────────────────
test('diffSchema: FK ON DELETE CASCADE vs 缺省 RESTRICT 应报 mismatch（fix #11）', () => {
  const docs = parseDocsTables([
    '## `t`',
    '### 字段',
    '| 列 | 类型 |',
    '|---|---|',
    '| `uid` | int |',
    '### 外键',
    '- `fk_uid`: uid → users.id (CASCADE)',
  ].join('\n'))
  const ddl = parseDDL([
    'CREATE TABLE `t` (',
    '  `uid` int,',
    '  FOREIGN KEY (`uid`) REFERENCES `users`(`id`)',
    ') ENGINE=InnoDB;',
  ].join('\n'))
  const d = diffSchema(docs, ddl)
  assert.ok(d.foreignKeyMismatches.length > 0, 'CASCADE vs RESTRICT 应报 — got: ' + JSON.stringify(d.foreignKeyMismatches))
})

// ── #16 CREATE TEMPORARY TABLE 也应被识别 ─────────────────────────
test('parseDDL: CREATE TEMPORARY TABLE 也应被解析（fix #16）', () => {
  const tables = parseDDL('CREATE TEMPORARY TABLE t_tmp ( id int );')
  assert.deepEqual([...tables.keys()], ['t_tmp'], 'TEMPORARY 表应入 Map — got: ' + [...tables.keys()])
})