105 lines
5.2 KiB
TypeScript
105 lines
5.2 KiB
TypeScript
// One-shot generator: introspect the live SQLite and emit a Drizzle pg schema
|
|
// (control-server/src/schema.ts). Fields are camelCase (matching the app's TS
|
|
// domain); with casing:"snake_case" on the client, Drizzle maps them to
|
|
// snake_case columns. INTEGER→bigint(mode:number), REAL→doublePrecision, else
|
|
// text. Run once; the emitted schema.ts is then the hand-maintained source.
|
|
//
|
|
// STREAMLINE_DB_PATH=~/.streamline/data.sqlite bun scripts/gen_drizzle_schema.ts
|
|
import { Database } from "bun:sqlite";
|
|
import { homedir } from "node:os";
|
|
import path from "node:path";
|
|
import { writeFileSync } from "node:fs";
|
|
|
|
const SRC = process.env.STREAMLINE_DB_PATH || path.join(homedir(), ".streamline", "data.sqlite");
|
|
const sq = new Database(SRC, { readonly: true });
|
|
|
|
type Col = { name: string; type: string; notnull: number; dflt_value: string | null; pk: number };
|
|
|
|
const tables = sq.query(
|
|
"SELECT name, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
|
|
).all() as { name: string; sql: string }[];
|
|
|
|
// field name (camelCase) → snake_case column name, matching Drizzle's casing.
|
|
const snake = (s: string) => s.replace(/([a-z0-9])([A-Z])/g, "$1_$2").toLowerCase();
|
|
|
|
// map a sqlite decl type → a drizzle column constructor call with an EXPLICIT
|
|
// snake_case column name (self-documenting; independent of the casing config).
|
|
function drizzleType(t: string, col: string): string {
|
|
const u = (t || "").toUpperCase();
|
|
const c = JSON.stringify(col);
|
|
if (u.includes("INT")) return `bigint(${c}, { mode: "number" })`;
|
|
if (u.includes("REAL") || u.includes("FLOA") || u.includes("DOUB")) return `doublePrecision(${c})`;
|
|
return `text(${c})`;
|
|
}
|
|
function defaultLiteral(dflt: string, isText: boolean): string {
|
|
// sqlite stores text defaults quoted ('x'); numbers bare.
|
|
if (/^'.*'$/.test(dflt)) return JSON.stringify(dflt.slice(1, -1));
|
|
if (/^-?\d+(\.\d+)?$/.test(dflt)) return dflt;
|
|
return JSON.stringify(dflt.replace(/^'|'$/g, "")); // fallback
|
|
}
|
|
|
|
const importedTypes = new Set<string>(["pgTable"]);
|
|
const tableBlocks: string[] = [];
|
|
|
|
for (const { name, sql } of tables) {
|
|
const cols = sq.query(`PRAGMA table_info("${name}")`).all() as Col[];
|
|
const fks = sq.query(`PRAGMA foreign_key_list("${name}")`).all() as { table: string; from: string; to: string; on_delete: string }[];
|
|
const idxList = sq.query(`PRAGMA index_list("${name}")`).all() as { name: string; unique: number; origin: string }[];
|
|
const uniqueCols = new Set<string>();
|
|
for (const ix of idxList) if (ix.origin === "u" && ix.unique) {
|
|
const uc = sq.query(`PRAGMA index_info("${ix.name}")`).all() as { name: string }[];
|
|
if (uc.length === 1) uniqueCols.add(uc[0].name); // single-col unique → inline .unique()
|
|
}
|
|
const autoinc = /AUTOINCREMENT/i.test(sql || "");
|
|
const pkCols = cols.filter((c) => c.pk > 0).sort((a, b) => a.pk - b.pk).map((c) => c.name);
|
|
const singleAiPk = autoinc && pkCols.length === 1;
|
|
const fkByCol = new Map(fks.map((f) => [f.from, f]));
|
|
|
|
const lines: string[] = [];
|
|
for (const c of cols) {
|
|
const dt = drizzleType(c.type, snake(c.name));
|
|
importedTypes.add(dt.split("(")[0]);
|
|
let chain = ` ${c.name}: ${dt}`;
|
|
if (singleAiPk && pkCols[0] === c.name) {
|
|
chain = ` ${c.name}: bigint(${JSON.stringify(snake(c.name))}, { mode: "number" }).generatedByDefaultAsIdentity().primaryKey()`;
|
|
lines.push(chain + ",");
|
|
continue;
|
|
}
|
|
if (pkCols.length === 1 && pkCols[0] === c.name) chain += ".primaryKey()";
|
|
if (c.notnull) chain += ".notNull()";
|
|
if (uniqueCols.has(c.name)) chain += ".unique()";
|
|
if (c.dflt_value !== null) chain += `.default(${defaultLiteral(c.dflt_value, dt.startsWith("text"))})`;
|
|
const fk = fkByCol.get(c.name);
|
|
if (fk) {
|
|
const od = fk.on_delete && fk.on_delete.toUpperCase() !== "NO ACTION" ? `, { onDelete: "${fk.on_delete.toLowerCase()}" }` : "";
|
|
chain += `.references(() => ${fk.table}.${fk.to}${od})`;
|
|
}
|
|
lines.push(chain + ",");
|
|
}
|
|
|
|
// table-level: composite PK
|
|
const extras: string[] = [];
|
|
if (pkCols.length > 1) {
|
|
importedTypes.add("primaryKey");
|
|
extras.push(` primaryKey({ columns: [${pkCols.map((c) => `t.${c}`).join(", ")}] })`);
|
|
}
|
|
const cb = extras.length ? `, (t) => [\n${extras.join(",\n")},\n]` : "";
|
|
tableBlocks.push(`export const ${name} = pgTable("${name}", {\n${lines.join("\n")}\n}${cb});`);
|
|
}
|
|
|
|
const pgImports = [...importedTypes].filter((t) => t !== "pgTable" && t !== "primaryKey").sort();
|
|
const coreImports = ["pgTable", ...(importedTypes.has("primaryKey") ? ["primaryKey"] : []), ...pgImports];
|
|
|
|
const out =
|
|
`// AUTO-GENERATED once by scripts/gen_drizzle_schema.ts from the live SQLite, then\n` +
|
|
`// hand-maintained. Drizzle entities for the Postgres (snake_case) database.\n` +
|
|
`// Fields are camelCase (app domain); casing:"snake_case" on the client maps\n` +
|
|
`// them to snake_case columns. int8 columns use mode:"number" so timestamps/\n` +
|
|
`// counts come back as JS numbers (values are all < 2^53).\n` +
|
|
`import { ${coreImports.join(", ")} } from "drizzle-orm/pg-core";\n\n` +
|
|
tableBlocks.join("\n\n") + "\n";
|
|
|
|
const dest = path.join(import.meta.dir, "..", "control-server", "src", "schema.ts");
|
|
writeFileSync(dest, out);
|
|
console.log(`[gen] wrote ${dest} (${tables.length} tables, ${[...importedTypes].join("/")})`);
|