Commit | Line | Data |
---|---|---|
525c4d2a BA |
1 | // ER diagram description parser |
2 | class ErDiags | |
3 | { | |
4 | constructor(description) | |
5 | { | |
4ef6cded BA |
6 | this.entities = { }; |
7 | this.inheritances = [ ]; | |
8 | this.associations = [ ]; | |
19addd10 BA |
9 | this.tables = { }; |
10 | this.mcdParsing(description); | |
11 | this.mldParsing(); | |
351d7a84 BA |
12 | |
13 | console.log(this.tables); | |
14 | ||
525c4d2a BA |
15 | // Cache SVG graphs returned by server (in addition to server cache = good perfs) |
16 | this.mcdGraph = ""; | |
17 | this.mldGraph = ""; | |
18 | this.sqlText = ""; | |
19 | } | |
20 | ||
525c4d2a BA |
21 | static get CARDINAL() |
22 | { | |
23 | return { | |
24 | "*": "0,n", | |
25 | "+": "1,n", | |
26 | "?": "0,1", | |
c728aeca BA |
27 | "1": "1,1", |
28 | "?R": "(0,1)", | |
29 | "1R": "(1,1)", | |
525c4d2a BA |
30 | }; |
31 | } | |
32 | ||
19addd10 BA |
33 | /////////////////////////////// |
34 | // PARSING STAGE 1: text to MCD | |
35 | /////////////////////////////// | |
525c4d2a BA |
36 | |
37 | // Parse a textual description into a json object | |
19addd10 | 38 | mcdParsing(text) |
525c4d2a BA |
39 | { |
40 | let lines = text.split("\n"); | |
41 | lines.push(""); //easier parsing: always empty line at the end | |
42 | let start = -1; | |
43 | for (let i=0; i < lines.length; i++) | |
44 | { | |
45 | lines[i] = lines[i].trim(); | |
46 | // Empty line ? | |
47 | if (lines[i].length == 0) | |
48 | { | |
49 | if (start >= 0) //there is some group of lines to parse | |
50 | { | |
51 | this.parseThing(lines, start, i); | |
52 | start = -1; | |
53 | } | |
54 | } | |
55 | else //not empty line: just register starting point | |
56 | { | |
57 | if (start < 0) | |
58 | start = i; | |
59 | } | |
60 | } | |
61 | } | |
62 | ||
63 | // Parse a group of lines into entity, association, ... | |
64 | parseThing(lines, start, end) //start included, end excluded | |
65 | { | |
66 | switch (lines[start].charAt(0)) | |
67 | { | |
68 | case '[': | |
69 | // Entity = { name: { attributes, [weak] } } | |
006d95a3 | 70 | let name = lines[start].match(/[^\[\]"\s]+/)[0]; |
525c4d2a BA |
71 | let entity = { attributes: this.parseAttributes(lines, start+1, end) }; |
72 | if (lines[start].charAt(1) == '[') | |
73 | entity.weak = true; | |
74 | this.entities[name] = entity; | |
75 | break; | |
76 | case 'i': //inheritance (arrows) | |
77 | this.inheritances = this.inheritances.concat(this.parseInheritance(lines, start+1, end)); | |
78 | break; | |
79 | case '{': //association | |
80 | // Association = { [name], [attributes], [weak], entities: ArrayOf entity indices } | |
81 | let relationship = { }; | |
006d95a3 | 82 | let nameRes = lines[start].match(/[^{}"\s]+/); |
525c4d2a BA |
83 | if (nameRes !== null) |
84 | relationship.name = nameRes[0]; | |
85 | if (lines[start].charAt(1) == '{') | |
86 | relationship.weak = true; | |
87 | this.associations.push(Object.assign({}, relationship, this.parseAssociation(lines, start+1, end))); | |
88 | break; | |
89 | } | |
90 | } | |
91 | ||
92 | // attributes: ArrayOf {name, [isKey], [type], [qualifiers]} | |
93 | parseAttributes(lines, start, end) | |
94 | { | |
95 | let attributes = []; | |
96 | for (let i=start; i<end; i++) | |
97 | { | |
d6c9499e BA |
98 | let field = { }; |
99 | let line = lines[i]; | |
100 | if (line.charAt(0) == '#') | |
101 | { | |
525c4d2a | 102 | field.isKey = true; |
d6c9499e BA |
103 | line = line.slice(1); |
104 | } | |
006d95a3 | 105 | field.name = line.match(/[^()"\s]+/)[0]; |
d6c9499e | 106 | let parenthesis = line.match(/\((.+)\)/); |
525c4d2a BA |
107 | if (parenthesis !== null) |
108 | { | |
109 | let sqlClues = parenthesis[1]; | |
19addd10 BA |
110 | field.type = sqlClues.match(/[^\s]+/)[0]; //type is always the first indication (mandatory) |
111 | field.qualifiers = sqlClues.substring(field.type.length).trim(); | |
525c4d2a BA |
112 | } |
113 | attributes.push(field); | |
114 | } | |
115 | return attributes; | |
116 | } | |
117 | ||
118 | // GroupOf Inheritance: { parent, children: ArrayOf entity indices } | |
119 | parseInheritance(lines, start, end) | |
120 | { | |
121 | let inheritance = []; | |
122 | for (let i=start; i<end; i++) | |
123 | { | |
124 | let lineParts = lines[i].split(" "); | |
125 | let children = []; | |
126 | for (let j=1; j<lineParts.length; j++) | |
127 | children.push(lineParts[j]); | |
128 | inheritance.push({ parent:lineParts[0], children: children }); | |
129 | } | |
130 | return inheritance; | |
131 | } | |
132 | ||
c728aeca BA |
133 | // Association (parsed here): { |
134 | // entities: ArrayOf entity names + cardinality, | |
135 | // [attributes: ArrayOf {name, [isKey], [type], [qualifiers]}] | |
136 | // } | |
525c4d2a BA |
137 | parseAssociation(lines, start, end) |
138 | { | |
139 | let assoce = { }; | |
140 | let entities = []; | |
141 | let i = start; | |
142 | while (i < end) | |
143 | { | |
144 | if (lines[i].charAt(0) == '-') | |
145 | { | |
146 | assoce.attributes = this.parseAttributes(lines, i+1, end); | |
147 | break; | |
148 | } | |
149 | else | |
150 | { | |
151 | // Read entity name + cardinality | |
152 | let lineParts = lines[i].split(" "); | |
153 | entities.push({ name:lineParts[0], card:lineParts[1] }); | |
154 | } | |
155 | i++; | |
156 | } | |
157 | assoce.entities = entities; | |
158 | return assoce; | |
159 | } | |
160 | ||
19addd10 BA |
161 | ////////////////////////////// |
162 | // PARSING STAGE 2: MCD to MLD | |
163 | ////////////////////////////// | |
164 | ||
165 | // From entities + relationships to tables | |
166 | mldParsing() | |
167 | { | |
168 | // Pass 1: initialize tables | |
169 | Object.keys(this.entities).forEach( name => { | |
170 | let newTable = [ ]; //array of fields | |
171 | this.entities[name].attributes.forEach( attr => { | |
172 | newTable.push({ | |
173 | name: attr.name, | |
174 | type: attr.type, | |
175 | isKey: attr.isKey, | |
176 | qualifiers: attr.qualifiers, | |
177 | }); | |
178 | }); | |
179 | this.tables[name] = newTable; | |
180 | }); | |
181 | // Pass 2: parse associations, add foreign keys when cardinality is 0,1 or 1,1 | |
182 | this.associations.forEach( a => { | |
183 | let newTableAttrs = [ ]; | |
184 | a.entities.forEach( e => { | |
185 | if (['?','1'].includes(e.card[0])) | |
186 | { | |
187 | // Foreign key apparition (for each entity in association minus current one, for each identifying attribute) | |
188 | a.entities.forEach( e2 => { | |
189 | if (e2.name == e.name) | |
190 | return; | |
191 | e2.attributes.forEach( attr => { | |
192 | if (attr.isKey) | |
193 | { | |
194 | this.tables[e.name].push({ | |
195 | isKey: e.card.length >= 2 && e.card[1] == 'R', //"weak tables" foreign keys become part of the key | |
196 | name: "#" + e2.name + "_" + attr.name, | |
197 | type: attr.type, | |
351d7a84 | 198 | qualifiers: "foreign key references " + e2.name + " " + (e.card[0]=='1' ? "not null" : ""), |
19addd10 BA |
199 | ref: e2.name, //easier drawMld function (fewer regexps) |
200 | }); | |
201 | } | |
202 | }); | |
203 | }); | |
204 | } | |
205 | else | |
206 | { | |
207 | // Add all keys in current entity | |
351d7a84 | 208 | let fields = this.entities[e.name].attributes.filter( attr => { return attr.isKey; }); |
19addd10 BA |
209 | newTableAttrs.push({ |
210 | fields: fields, | |
211 | entity: e.name, | |
212 | }); | |
213 | } | |
351d7a84 | 214 | }); |
19addd10 BA |
215 | if (newTableAttrs.length > 1) |
216 | { | |
217 | // Ok, really create a new table | |
218 | let newTable = { | |
351d7a84 | 219 | name: a.name || newTableAttrs.map( item => { return item.entity; }).join("_"), |
19addd10 BA |
220 | fields: [ ], |
221 | }; | |
222 | newTableAttrs.forEach( item => { | |
223 | item.fields.forEach( f => { | |
224 | newTable.fields.push({ | |
225 | name: item.entity + "_" + f.name, | |
226 | isKey: true, | |
351d7a84 | 227 | type: f.type, |
19addd10 BA |
228 | qualifiers: (f.qualifiers+" " || "") + "foreign key references " + item.entity + " not null", |
229 | ref: item.entity, | |
230 | }); | |
231 | }); | |
232 | }); | |
233 | // Add relationship potential own attributes | |
234 | a.attributes.forEach( attr => { | |
235 | newTable.fields.push({ | |
236 | name: attr.name, | |
237 | isKey: false, | |
238 | type: attr.type, | |
239 | qualifiers: attr.qualifiers, | |
240 | }); | |
241 | }); | |
242 | this.tables[newTable.name] = newTable.fields; | |
243 | } | |
244 | }); | |
245 | } | |
246 | ||
247 | ///////////////////////////////// | |
248 | // DRAWING + GET SQL FROM PARSING | |
249 | ///////////////////////////////// | |
525c4d2a BA |
250 | |
251 | static AjaxGet(dotInput, callback) | |
252 | { | |
253 | let xhr = new XMLHttpRequest(); | |
254 | xhr.onreadystatechange = function() { | |
255 | if (this.readyState == 4 && this.status == 200) | |
256 | callback(this.responseText); | |
257 | }; | |
258 | xhr.open("GET", "scripts/getGraphSvg.php?dot=" + encodeURIComponent(dotInput), true); | |
259 | xhr.send(); | |
260 | } | |
261 | ||
262 | // "Modèle conceptuel des données". TODO: option for graph size | |
48a55161 | 263 | // NOTE: randomizing helps to obtain better graphs (sometimes) |
525c4d2a BA |
264 | drawMcd(id, mcdStyle) //mcdStyle: bubble, or compact |
265 | { | |
266 | let element = document.getElementById(id); | |
267 | mcdStyle = mcdStyle || "compact"; | |
268 | if (this.mcdGraph.length > 0) | |
269 | { | |
270 | element.innerHTML = this.mcdGraph; | |
271 | return; | |
272 | } | |
273 | // Build dot graph input | |
274 | let mcdDot = 'graph {\n'; | |
48a55161 | 275 | mcdDot += 'rankdir="LR";\n'; |
525c4d2a | 276 | // Nodes: |
48a55161 | 277 | if (mcdStyle == "compact") |
c728aeca | 278 | mcdDot += 'node [shape=plaintext];\n'; |
48a55161 | 279 | _.shuffle(Object.keys(this.entities)).forEach( name => { |
525c4d2a BA |
280 | if (mcdStyle == "bubble") |
281 | { | |
006d95a3 | 282 | mcdDot += '"' + name + '" [shape=rectangle, label="' + name + '"'; |
525c4d2a BA |
283 | if (this.entities[name].weak) |
284 | mcdDot += ', peripheries=2'; | |
285 | mcdDot += '];\n'; | |
286 | if (!!this.entities[name].attributes) | |
287 | { | |
b74cfe41 | 288 | this.entities[name].attributes.forEach( a => { |
525c4d2a | 289 | let label = (a.isKey ? '#' : '') + a.name; |
48a55161 | 290 | let attrName = name + '_' + a.name; |
006d95a3 | 291 | mcdDot += '"' + attrName + '" [shape=ellipse, label="' + label + '"];\n'; |
48a55161 | 292 | if (Math.random() < 0.5) |
006d95a3 | 293 | mcdDot += '"' + attrName + '" -- "' + name + '";\n'; |
48a55161 | 294 | else |
006d95a3 | 295 | mcdDot += '"' + name + '" -- "' + attrName + '";\n'; |
525c4d2a BA |
296 | }); |
297 | } | |
298 | } | |
299 | else | |
300 | { | |
006d95a3 | 301 | mcdDot += '"' + name + '" [label=<'; |
525c4d2a BA |
302 | if (this.entities[name].weak) |
303 | { | |
304 | mcdDot += '<table port="name" BORDER="1" ALIGN="LEFT" CELLPADDING="0" CELLSPACING="3" CELLBORDER="0">' + | |
305 | '<tr><td><table BORDER="1" ALIGN="LEFT" CELLPADDING="5" CELLSPACING="0">\n'; | |
306 | } | |
307 | else | |
308 | mcdDot += '<table port="name" BORDER="1" ALIGN="LEFT" CELLPADDING="5" CELLSPACING="0">\n'; | |
309 | mcdDot += '<tr><td BGCOLOR="#ae7d4e" BORDER="0"><font COLOR="#FFFFFF">' + name + '</font></td></tr>\n'; | |
310 | if (!!this.entities[name].attributes) | |
311 | { | |
b74cfe41 | 312 | this.entities[name].attributes.forEach( a => { |
525c4d2a BA |
313 | let label = (a.isKey ? '<u>' : '') + a.name + (a.isKey ? '</u>' : ''); |
314 | mcdDot += '<tr><td BGCOLOR="#FFFFFF" BORDER="0" ALIGN="LEFT"><font COLOR="#000000" >' + label + '</font></td></tr>\n'; | |
315 | }); | |
316 | } | |
317 | mcdDot += '</table>'; | |
318 | if (this.entities[name].weak) | |
319 | mcdDot += '</td></tr></table>'; | |
320 | mcdDot += '>];\n'; | |
321 | } | |
322 | }); | |
323 | // Inheritances: | |
006d95a3 | 324 | _.shuffle(this.inheritances).forEach( i => { |
a2d8ba72 BA |
325 | // TODO: node shape = triangle fill yellow. See |
326 | // https://merise.developpez.com/faq/?page=MCD#CIF-ou-dependance-fonctionnelle-de-A-a-Z | |
327 | // https://merise.developpez.com/faq/?page=MLD#Comment-transformer-un-MCD-en-MLD | |
328 | // https://www.developpez.net/forums/d1088964/general-developpement/alm/modelisation/structure-agregation-l-association-d-association/ | |
48a55161 BA |
329 | _.shuffle(i.children).forEach( c => { |
330 | if (Math.random() < 0.5) | |
006d95a3 | 331 | mcdDot += '"' + c + '":name -- "' + i.parent; |
48a55161 | 332 | else |
006d95a3 BA |
333 | mcdDot += '"' + i.parent + '":name -- "' + c; |
334 | mcdDot += '":name [dir="forward", arrowhead="vee", style="dashed"];\n'; | |
525c4d2a BA |
335 | }); |
336 | }); | |
337 | // Relationships: | |
c728aeca BA |
338 | if (mcdStyle == "compact") |
339 | mcdDot += 'node [shape=rectangle, style=rounded];\n'; | |
525c4d2a | 340 | let assoceCounter = 0; |
48a55161 | 341 | _.shuffle(this.associations).forEach( a => { |
19addd10 | 342 | let name = a.name || "_assoce" + assoceCounter++; |
c728aeca BA |
343 | if (mcdStyle == "bubble") |
344 | { | |
345 | mcdDot += '"' + name + '" [shape="diamond", style="filled", color="lightgrey", label="' + name + '"'; | |
346 | if (a.weak) | |
347 | mcdDot += ', peripheries=2'; | |
348 | mcdDot += '];\n'; | |
349 | if (!!a.attributes) | |
350 | { | |
351 | a.attributes.forEach( attr => { | |
352 | let label = (attr.isKey ? '#' : '') + attr.name; | |
353 | mcdDot += '"' + name + '_' + attr.name + '" [shape=ellipse, label="' + label + '"];\n'; | |
354 | let attrName = name + '_' + attr.name; | |
355 | if (Math.random() < 0.5) | |
356 | mcdDot += '"' + attrName + '" -- "' + name + '";\n'; | |
357 | else | |
358 | mcdDot += '"' + name + '" -- "' + attrName + '";\n'; | |
359 | }); | |
360 | } | |
361 | } | |
362 | else | |
363 | { | |
4ef6cded | 364 | let label = '<' + name + '>'; |
c728aeca BA |
365 | if (!!a.attributes) |
366 | { | |
367 | a.attributes.forEach( attr => { | |
368 | let attrLabel = (attr.isKey ? '#' : '') + attr.name; | |
4ef6cded | 369 | label += '\\n' + attrLabel; |
c728aeca BA |
370 | }); |
371 | } | |
372 | mcdDot += '"' + name + '" [color="lightgrey", label="' + label + '"'; | |
373 | if (a.weak) | |
374 | mcdDot += ', peripheries=2'; | |
375 | mcdDot += '];\n'; | |
376 | } | |
48a55161 BA |
377 | _.shuffle(a.entities).forEach( e => { |
378 | if (Math.random() < 0.5) | |
006d95a3 | 379 | mcdDot += '"' + e.name + '":name -- "' + name + '"'; |
48a55161 | 380 | else |
006d95a3 | 381 | mcdDot += '"' + name + '" -- "' + e.name + '":name'; |
48a55161 | 382 | mcdDot += '[label="' + ErDiags.CARDINAL[e.card] + '"];\n'; |
525c4d2a | 383 | }); |
525c4d2a BA |
384 | }); |
385 | mcdDot += '}'; | |
48a55161 | 386 | console.log(mcdDot); |
525c4d2a BA |
387 | ErDiags.AjaxGet(mcdDot, graphSvg => { |
388 | this.mcdGraph = graphSvg; | |
389 | element.innerHTML = graphSvg; | |
d6c9499e | 390 | }); |
525c4d2a BA |
391 | } |
392 | ||
19addd10 | 393 | // "Modèle logique des données", from MCD without anomalies |
48a55161 | 394 | // TODO: this one should draw links from foreign keys to keys (port=... in <TD>) |
525c4d2a BA |
395 | drawMld(id) |
396 | { | |
397 | let element = document.getElementById(id); | |
398 | if (this.mldGraph.length > 0) | |
399 | { | |
400 | element.innerHTML = this.mcdGraph; | |
401 | return; | |
402 | } | |
4ef6cded | 403 | // Build dot graph input (assuming foreign keys not already present...) |
e2610c05 | 404 | let mldDot = 'graph {\n'; |
19addd10 BA |
405 | mldDot += 'node [shape=plaintext];\n'; |
406 | let links = ""; | |
407 | _.shuffle(Object.keys(this.tables)).forEach( name => { | |
408 | mldDot += '"' + name + '" [label=<<table BORDER="1" ALIGN="LEFT" CELLPADDING="5" CELLSPACING="0">\n'; | |
409 | mldDot += '<tr><td BGCOLOR="#ae7d4e" BORDER="0"><font COLOR="#FFFFFF">' + name + '</font></td></tr>\n'; | |
351d7a84 | 410 | this.tables[name].forEach( f => { |
19addd10 BA |
411 | let label = (f.isKey ? '<u>' : '') + (!!f.qualifiers && f.qualifiers.indexOf("foreign")>=0 ? '#' : '') + f.name + (f.isKey ? '</u>' : ''); |
412 | mldDot += '<tr><td port="' + f.name + '"' + (f.isKey ? ' port="__key"' : '') | |
413 | + ' BGCOLOR="#FFFFFF" BORDER="0" ALIGN="LEFT"><font COLOR="#000000" >' + label + '</font></td></tr>\n'; | |
414 | if (!!f.ref) | |
4ef6cded | 415 | { |
19addd10 | 416 | if (Math.random() < 0.5) |
351d7a84 | 417 | links += '"' + f.ref + '":__key -- "' + name+'":"'+f.name+'"\n'; |
19addd10 BA |
418 | else |
419 | links += '"'+name+'":"'+f.name+'" -- "' + f.ref + '":__key\n'; | |
420 | } | |
e2610c05 | 421 | }); |
19addd10 | 422 | mldDot += '</table>>];\n'; |
e2610c05 | 423 | }); |
19addd10 BA |
424 | mldDot += links + '\n'; |
425 | mldDot += '}\n'; | |
351d7a84 | 426 | console.log(mldDot); |
d6c9499e BA |
427 | ErDiags.AjaxGet(mldDot, graphSvg => { |
428 | this.mldGraph = graphSvg; | |
429 | element.innerHTML = graphSvg; | |
430 | }); | |
525c4d2a BA |
431 | } |
432 | ||
433 | fillSql(id) | |
434 | { | |
435 | let element = document.getElementById(id); | |
436 | if (this.sqlText.length > 0) | |
437 | { | |
438 | element.innerHTML = this.sqlText; | |
439 | return; | |
440 | } | |
19addd10 BA |
441 | let sqlText = ""; |
442 | Object.keys(this.tables).forEach( name => { | |
443 | sqlText += "CREATE TABLE " + name + " (\n"; | |
444 | let key = ""; | |
445 | this.tables[name].forEach( f => { | |
446 | sqlText += f.name + " " + (f.type || "TEXT") + (" "+f.qualifiers || "") + ",\n"; | |
447 | if (f.isKey) | |
448 | key += (key.length>0 ? "," : "") + f.name; | |
449 | }); | |
450 | sqlText += "PRIMARY KEY (" + key + ")\n"; | |
451 | sqlText += ");\n"; | |
452 | }); | |
453 | //console.log(sqlText); | |
454 | this.sqlText = sqlText; | |
455 | element.innerHTML = "<pre><code>" + sqlText + "</code></pre>"; | |
525c4d2a BA |
456 | } |
457 | } |