validate-ddl.test.mjs
6.76 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
// 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)
})
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'))
assert.ok(t.indexes.has('idx_user'))
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']), 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'))
})
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)
})