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