1// ER Diagram â SQL DDL Generator 2// 3// Generates a SQL DDL comparison output from ER diagram data. 4// This is intentionally minimal â it exists to show developers the limitations 5// of SQL compared to native ZIM relationships. 6// 7// ZIM relationships carry explicit join conditions and are first-class objects. 8// SQL needs extra FK columns, JOIN clauses, and does not encode the link itself. 9// 10// generateSqlDdl() â SQL DDL (CREATE TABLE statements + relationship comments) 11 12/** 13 * Attempt to derive a SQL JOIN fragment from a simple ZIM condition like "eA.f1 = eB.f2". 14 * Returns join details or null if the condition can't be parsed. 15 * @param {string} condition 16 * @returns {{ table1:string, col1:string, table2:string, col2:string }|null} 17 */ 18function parseSimpleCondition(condition) { 19 if (!condition) return null; 20 const m = condition.trim().match(/^(\w+)\.(\w+)\s*=\s*(\w+)\.(\w+)$/); 21 if (!m) return null; 22 return { table1: m[1], col1: m[2], table2: m[3], col2: m[4] }; 23} 24 25function normalizeCardinality(relType) { 26 const rt = String(relType || "").trim(); 27 if (rt === "one-to-one") return "1:1"; 28 if (rt === "many-to-many") return "N:M"; 29 if (rt === "one-to-many") return "1:N"; 30 return rt || "1:N"; 31} 32 33/** 34 * Map an ER diagram type to a SQL column type string. 35 * @param {Object} field 36 * @returns {string} 37 */ 38function toSqlType(field) { 39 const t = (field.type || "VARCHAR").toUpperCase().replace(/\s*\(.*\)/, ""); 40 const len = field.length ? `(${field.length})` : ""; 41 if (t === "INT" || t === "INTEGER" || t === "SMALLINT" || t === "TINYINT") return `INT`; 42 if (t === "BIGINT") return "BIGINT"; 43 if (t === "BOOLEAN" || t === "BOOL") return "BOOLEAN"; 44 if (t === "FLOAT" || t === "DOUBLE" || t === "REAL") return "FLOAT"; 45 if (t === "DECIMAL" || t === "NUMERIC") return `NUMERIC${field.length ? `(${field.length}${field.decimals ? `,${field.decimals}` : ""})` : ""}`; 46 if (t === "DATE") return "DATE"; 47 if (t === "DATETIME" || t === "TIMESTAMP") return "TIMESTAMP"; 48 if (t === "TEXT") return "TEXT"; 49 if (t === "BLOB") return "BLOB"; 50 if (t === "CHAR") return `CHAR${len}`; 51 if (t === "UUID") return "VARCHAR(36)"; 52 return `VARCHAR${len || "(255)"}`; 53} 54 55/** ISO timestamp for SQL script comments. */ 56function sqlTimestamp() { 57 return new Date().toISOString().replace("T", " ").slice(0, 19) + " UTC"; 58} 59 60/** 61 * Build a SQL relationship example (JOIN query + foreign-key suggestion) for a 62 * single relationship. This is the SQL comparison artifact offered as a 63 * downloadable document â it is NOT provisioned to the zimserver. 64 * 65 * Shared between generateSqlDdl() and the Relationship Inspector popup so both 66 * render identical SQL output. 67 * 68 * @param {{ name?: string, condition?: string }} rel 69 * @param {string} [_fromName] Source entity name (accepted for call-site symmetry). 70 * @param {string} [_toName] Target entity name (accepted for call-site symmetry). 71 * @returns {string} A multi-line SQL example, or a note when it cannot be derived. 72 */ 73export function buildSqlRelationshipExample(rel, _fromName, _toName) { 74 const fromName = String(_fromName || "").trim(); 75 const toName = String(_toName || "").trim(); 76 const relLabel = rel.name || (fromName && toName ? `${fromName}_${toName}` : "Relationship"); 77 const cardinality = normalizeCardinality(rel.relType); 78 const condition = String(rel.condition || "").trim(); 79 const lines = []; 80 lines.push(`-- Relationship: ${relLabel}`); 81 if (fromName && toName) lines.push(`-- ${fromName} â ${toName} (${cardinality})`); 82 if (condition) { 83 lines.push(`-- ZIM condition: ${condition}`); 84 const parsed = parseSimpleCondition(condition); 85 if (parsed) { 86 const fkName = `fk_${parsed.table2}_${parsed.col2}_${parsed.table1}_${parsed.col1}`; 87 lines.push(`SELECT * FROM ${parsed.table1} JOIN ${parsed.table2}`); 88 lines.push(` ON ${parsed.table1}.${parsed.col1} = ${parsed.table2}.${parsed.col2};`); 89 lines.push(``); 90 lines.push(`-- Foreign key suggestion (SQL approximation):`); 91 lines.push(`ALTER TABLE ${parsed.table2}`); 92 lines.push(` ADD CONSTRAINT ${fkName}`); 93 lines.push(` FOREIGN KEY (${parsed.col2}) REFERENCES ${parsed.table1}(${parsed.col1});`); 94 if (cardinality === "N:M") { 95 lines.push(`-- NOTE: N:M usually requires a junction table in SQL.`); 96 } 97 } else { 98 lines.push(`-- SQL JOIN cannot be derived automatically from this condition.`); 99 lines.push(`-- Implement manually: ${condition}`); 100 } 101 } else { 102 lines.push(`-- No RelCondition provided; SQL FK/JOIN cannot be generated automatically.`); 103 if (fromName && toName) { 104 lines.push(`-- Add a ZIM RelCondition, e.g. ${fromName}.<key> = ${toName}.<key>.`); 105 } else { 106 lines.push(`-- Add a ZIM RelCondition in the form <Entity>.<field> = <Entity>.<field>.`); 107 } 108 } 109 return lines.join("\n"); 110} 111 112/** 113 * Generate a SQL DDL script from ER diagram data. 114 * Relationship conditions become JOIN hints/comments. 115 * Missing conditions are flagged as warnings. 116 * 117 * @param {Array<{id:string, name:string, fields:Array}>} entities 118 * @param {Array<{id:string, from:string, to:string, relType:string, name?:string, condition?:string, fields?:Array}>} relationships 119 * @param {{ appName?: string, dbName?: string }} [opts] 120 * @returns {string} SQL DDL script. 121 */ 122export function generateSqlDdl(entities, relationships, opts = {}) { 123 const appName = (opts.appName || "").trim(); 124 const dbName = (opts.dbName || appName || "my_database").trim(); 125 const entityById = new Map(entities.map(e => [e.id, e])); 126 const ts = sqlTimestamp(); 127 128 const L = []; 129 const w = (s = "") => L.push(s); 130 131 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 132 w(`-- SQL DDL â generated by ZimX ER Diagram Designer`); 133 w(`-- â COMPARISON OUTPUT: SQL requires FK columns + JOIN boilerplate.`); 134 w(`-- â ZIM relationships (relType: "Zim") are primary and more expressive.`); 135 if (appName) w(`-- Application: ${appName}`); 136 if (dbName) w(`-- Database: ${dbName}`); 137 w(`-- Generated: ${ts}`); 138 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 139 w(); 140 141 // ââ CREATE DATABASE âââââââââââââââââââââââââââââââââââââââââââââââââââââââ 142 w(`CREATE DATABASE IF NOT EXISTS ${dbName};`); 143 w(`USE ${dbName};`); 144 w(); 145 146 // ââ CREATE TABLE per entity âââââââââââââââââââââââââââââââââââââââââââââââ
147 entities.forEach(entity => { 148 const fields = entity.fields || []; 149 w(`-- ââ ${entity.name} ${"â".repeat(Math.max(0, 50 - entity.name.length))}`); 150 w(`CREATE TABLE IF NOT EXISTS ${entity.name} (`); 151 const colLines = []; 152 const pkFields = fields.filter(f => f.pk && !f.virtual); 153 fields.filter(f => !f.virtual).forEach(f => { 154 const sqlType = toSqlType(f); 155 const notNull = f.pk ? " NOT NULL" : ""; 156 const pk = ""; 157 colLines.push(` ${f.name} ${sqlType}${notNull}`); 158 }); 159 if (pkFields.length > 0) { 160 colLines.push(` PRIMARY KEY (${pkFields.map(f => f.name).join(", ")})`); 161 } 162 w(colLines.join(",\n")); 163 w(`);`); 164 w(); 165 }); 166 167 // ââ Relationship comments / FK hints âââââââââââââââââââââââââââââââââââââ 168 if (relationships.length > 0) { 169 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 170 w(`-- RELATIONSHIPS`); 171 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 172 w(`-- ZIM relationships are first-class objects; SQL approximates them`); 173 w(`-- with foreign-key constraints and JOIN queries.`); 174 w(); 175 176 const missingCondition = []; 177 178 relationships.forEach(rel => { 179 const fromEnt = entityById.get(rel.from); 180 const toEnt = entityById.get(rel.to); 181 if (!fromEnt || !toEnt) return; 182 183 const rn = rel.name || `${fromEnt.name}_${toEnt.name}`; 184 const card = rel.relType === "one-to-one" ? "1:1" : 185 rel.relType === "many-to-many" ? "N:M" : "1:N"; 186 187 w(`-- ââ Relationship: ${rn} (${card}) ${fromEnt.name} â ${toEnt.name}`); 188 buildSqlRelationshipExample(rel, fromEnt.name, toEnt.name) 189 .split("\n") 190 .forEach((line) => w(line.startsWith("--") ? line : `-- ${line}`)); 191 192 if (!String(rel.condition || "").trim()) { 193 missingCondition.push(rn); 194 } 195 196 // Relationship-owned fields â junction table hint for many-to-many 197 if (Array.isArray(rel.fields) && rel.fields.length > 0) { 198 w(`-- Relationship-owned fields (${rel.fields.map(f => f.name).join(", ")}):`); 199 w(`-- Consider a junction table or ZIM RelFields for these attributes.`); 200 } 201 202 w(); 203 }); 204 205 if (missingCondition.length > 0) { 206 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 207 w(`-- â MISSING CONDITIONS â relationships below have no ZIM condition:`); 208 missingCondition.forEach(rn => w(`-- ${rn}`)); 209 w(`-- Add RelCondition expressions (<Entity>.<field> = <Entity>.<field>) to provide join semantics.`); 210 w(`-- ââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââââ`); 211 w(); 212 } 213 } 214 215 return L.join("\n"); 216}
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.