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