Skip to content

Commit 42e2bcf

Browse files
albertoarenaalbertoarena
committed
test: add round-trip tests and CI workflow
Add the Phase 4 import -> render -> import round-trip: for each of the six dialects, parse a CREATE TABLE + foreign key schema into a model, render it back to DDL, parse the rendered DDL again, and assert the two models are equal. Comparison is on a normalized model (table and column names, resolved type names, key/constraint flags, relationships), not raw SQL, since formatting and identifier quoting legitimately differ. A small in-memory adapter assembles the DatabaseType the renderer consumes from the importer output, which the app normally reconstructs via the database. Primary keys are canonicalized to non-nullable in the comparison: SQL Server emits its primary key as a table-level constraint and the importer only forces NOT NULL for inline primary keys, so the flag would otherwise differ on the round trip although the schemas are equivalent. Add a GitHub Actions workflow running npm ci + npm test on pushes to main and on pull requests. Typecheck and lint are intentionally left out for now: the current codebase does not pass a strict project typecheck or a clean lint, so gating on them would fail CI on pre-existing, unrelated issues.
1 parent 91fe5d6 commit 42e2bcf

2 files changed

Lines changed: 262 additions & 0 deletions

File tree

‎.github/workflows/ci.yml‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
name: CI
2+
3+
on:
4+
pull_request:
5+
push:
6+
branches: [main]
7+
8+
jobs:
9+
test:
10+
runs-on: ubuntu-latest
11+
steps:
12+
- uses: actions/checkout@v4
13+
- uses: actions/setup-node@v4
14+
with:
15+
node-version: 20
16+
cache: npm
17+
- run: npm ci
18+
- run: npm test

‎src/utils/round-trip.test.ts‎

Lines changed: 244 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,244 @@
1+
import { describe, it, expect, beforeAll } from "vitest";
2+
import { init } from "@guanmingchiu/sqlparser-ts";
3+
4+
import { DatabaseDialect } from "@/lib/database";
5+
import { getImporter } from "@/utils/import/import-utils";
6+
import { getRenderer } from "@/utils/render/render-uttils";
7+
import { getDataTypes } from "@/test/fixtures/data-types";
8+
import { DatabaseType } from "@/lib/schemas/database-schema";
9+
import { TableType } from "@/lib/schemas/table-schema";
10+
import { IndexType } from "@/lib/schemas/index-schema";
11+
import { RelationshipType } from "@/lib/schemas/relationship-schema";
12+
import { FieldType } from "@/lib/schemas/field-schema";
13+
14+
// Round-trip: parse DDL -> model -> render DDL -> parse again, and assert the
15+
// two models are equal. This is the strongest integration check of the
16+
// import/render pipeline. We compare a normalized model (names, resolved type
17+
// names, key/constraint flags, relationships), not raw SQL, since formatting
18+
// and identifier quoting legitimately differ.
19+
20+
type ParseResult = ReturnType<ReturnType<typeof getImporter>["parseSql"]>;
21+
22+
// Adapter: assemble a DatabaseType (what renderDDL consumes) from parseSql
23+
// output (tables + id-based relationships). The app normally round-trips this
24+
// through the SQLite database; here we build it in memory. Relationship
25+
// source/target objects are embedded because the SQLite renderer reads them off
26+
// the raw database when ordering tables.
27+
const toDatabase = (
28+
dialect: DatabaseDialect,
29+
result: ParseResult,
30+
): DatabaseType => {
31+
const tables = result.tables.map(
32+
(t) => ({ ...t, indices: [] as IndexType[] }) as TableType,
33+
);
34+
const table = (id: string) => tables.find((t) => t.id === id);
35+
const relationships = result.relationships.map((r) => {
36+
const source = table(r.sourceTableId);
37+
const target = table(r.targetTableId);
38+
39+
return {
40+
...r,
41+
databaseId: "db",
42+
sourceTable: source,
43+
targetTable: target,
44+
sourceField: source?.fields?.find((f) => f.id === r.sourceFieldId),
45+
targetField: target?.fields?.find((f) => f.id === r.targetFieldId),
46+
} as RelationshipType;
47+
});
48+
49+
return {
50+
id: "db",
51+
name: "roundtrip",
52+
dialect,
53+
numOfTables: tables.length,
54+
createdAt: null,
55+
tables,
56+
relationships,
57+
} as DatabaseType;
58+
};
59+
60+
const byName = (a: { name?: string | null }, b: { name?: string | null }) =>
61+
(a.name ?? "").localeCompare(b.name ?? "");
62+
63+
const normalize = (dialect: DatabaseDialect, result: ParseResult) => {
64+
const types = getDataTypes(dialect);
65+
const typeName = (id?: string | null) =>
66+
types.find((t) => t.id === id)?.name ?? null;
67+
const tableName = (id: string) =>
68+
result.tables.find((t) => t.id === id)?.name ?? id;
69+
const fieldName = (tableId: string, fieldId: string) =>
70+
result.tables
71+
.find((t) => t.id === tableId)
72+
?.fields?.find((f: FieldType) => f.id === fieldId)?.name ?? fieldId;
73+
74+
return {
75+
tables: [...result.tables].sort(byName).map((t) => ({
76+
name: t.name,
77+
columns: [...(t.fields ?? [])].sort(byName).map((f: FieldType) => ({
78+
name: f.name,
79+
type: typeName(f.typeId),
80+
isPrimary: !!f.isPrimary,
81+
// a primary key is non-nullable in every SQL dialect; canonicalize it.
82+
// (SQL Server renders the PK as a table constraint, and the importer
83+
// only forces NOT NULL for inline primary keys, so without this the
84+
// round-tripped nullable flag would spuriously differ for MSSQL.)
85+
nullable: f.isPrimary ? false : !!f.nullable,
86+
unique: !!f.unique,
87+
autoIncrement: !!f.autoIncrement,
88+
maxLength: f.maxLength ?? null,
89+
defaultValue: f.defaultValue ?? null,
90+
})),
91+
})),
92+
relationships: result.relationships
93+
.map((r) => ({
94+
source: `${tableName(r.sourceTableId)}.${fieldName(
95+
r.sourceTableId,
96+
r.sourceFieldId,
97+
)}`,
98+
target: `${tableName(r.targetTableId)}.${fieldName(
99+
r.targetTableId,
100+
r.targetFieldId,
101+
)}`,
102+
cardinality: r.cardinality,
103+
onDelete: r.onDelete ?? null,
104+
}))
105+
.sort((a, b) => (a.source + a.target).localeCompare(b.source + b.target)),
106+
};
107+
};
108+
109+
interface RoundTripCase {
110+
name: string;
111+
dialect: DatabaseDialect;
112+
sql: string;
113+
}
114+
115+
const cases: RoundTripCase[] = [
116+
{
117+
name: "MySQL",
118+
dialect: DatabaseDialect.MYSQL,
119+
sql: `
120+
CREATE TABLE users (
121+
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
122+
email VARCHAR(255) NOT NULL UNIQUE,
123+
status VARCHAR(20) NOT NULL DEFAULT 'active'
124+
);
125+
CREATE TABLE posts (
126+
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
127+
user_id INTEGER NOT NULL,
128+
title VARCHAR(255) NOT NULL,
129+
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
130+
);`,
131+
},
132+
{
133+
name: "MariaDB",
134+
dialect: DatabaseDialect.MARIADB,
135+
sql: `
136+
CREATE TABLE users (
137+
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
138+
email VARCHAR(255) NOT NULL UNIQUE,
139+
status VARCHAR(20) NOT NULL DEFAULT 'active'
140+
);
141+
CREATE TABLE posts (
142+
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
143+
user_id INTEGER NOT NULL,
144+
title VARCHAR(255) NOT NULL,
145+
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
146+
);`,
147+
},
148+
{
149+
name: "PostgreSQL",
150+
dialect: DatabaseDialect.POSTGRES,
151+
sql: `
152+
CREATE TABLE users (
153+
id integer PRIMARY KEY,
154+
email varchar(255) NOT NULL UNIQUE,
155+
status varchar(20) NOT NULL DEFAULT 'active'
156+
);
157+
CREATE TABLE posts (
158+
id integer PRIMARY KEY,
159+
user_id integer NOT NULL,
160+
title varchar(255) NOT NULL,
161+
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
162+
);`,
163+
},
164+
{
165+
name: "SQLite",
166+
dialect: DatabaseDialect.SQLITE,
167+
sql: `
168+
CREATE TABLE users (
169+
id INTEGER PRIMARY KEY AUTOINCREMENT,
170+
email TEXT NOT NULL UNIQUE,
171+
status TEXT NOT NULL DEFAULT 'active'
172+
);
173+
CREATE TABLE posts (
174+
id INTEGER PRIMARY KEY AUTOINCREMENT,
175+
user_id INTEGER NOT NULL,
176+
title TEXT NOT NULL,
177+
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
178+
);`,
179+
},
180+
{
181+
name: "Oracle",
182+
dialect: DatabaseDialect.ORACLE,
183+
sql: `
184+
CREATE TABLE users (
185+
id NUMBER PRIMARY KEY,
186+
email VARCHAR2(255) NOT NULL UNIQUE,
187+
status VARCHAR2(20) DEFAULT 'active' NOT NULL
188+
);
189+
CREATE TABLE posts (
190+
id NUMBER PRIMARY KEY,
191+
user_id NUMBER NOT NULL,
192+
title VARCHAR2(255) NOT NULL,
193+
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id)
194+
);`,
195+
},
196+
{
197+
name: "SQL Server",
198+
dialect: DatabaseDialect.MSSQL,
199+
sql: `
200+
CREATE TABLE users (
201+
id INT IDENTITY(1,1) PRIMARY KEY,
202+
email NVARCHAR(255) NOT NULL UNIQUE,
203+
status NVARCHAR(20) NOT NULL DEFAULT 'active'
204+
);
205+
CREATE TABLE posts (
206+
id INT IDENTITY(1,1) PRIMARY KEY,
207+
user_id INT NOT NULL,
208+
title NVARCHAR(255) NOT NULL,
209+
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id)
210+
);`,
211+
},
212+
];
213+
214+
describe("import -> render -> import round-trip", () => {
215+
beforeAll(async () => {
216+
await init();
217+
});
218+
219+
for (const c of cases) {
220+
it(`${c.name} model is stable`, async () => {
221+
const first = getImporter(c.dialect, getDataTypes(c.dialect)).parseSql(
222+
c.sql,
223+
);
224+
225+
// sanity: the seed actually produced the model we intend to round-trip
226+
expect(first.errors).toHaveLength(0);
227+
expect(first.tables).toHaveLength(2);
228+
expect(first.relationships).toHaveLength(1);
229+
230+
const rendered = await getRenderer(
231+
c.dialect,
232+
getDataTypes(c.dialect),
233+
)!.renderDDL(toDatabase(c.dialect, first));
234+
235+
const second = getImporter(c.dialect, getDataTypes(c.dialect)).parseSql(
236+
rendered,
237+
);
238+
239+
expect(second.errors).toHaveLength(0);
240+
241+
expect(normalize(c.dialect, second)).toEqual(normalize(c.dialect, first));
242+
});
243+
}
244+
});

0 commit comments

Comments
 (0)