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