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