1 // ER diagram description parser
4 constructor(description
)
7 this.inheritances
= [ ];
8 this.associations
= [ ];
10 this.mcdParsing(description
);
13 console
.log(this.tables
);
15 // Cache SVG graphs returned by server (in addition to server cache = good perfs)
33 ///////////////////////////////
34 // PARSING STAGE 1: text to MCD
35 ///////////////////////////////
37 // Parse a textual description into a json object
40 let lines
= text
.split("\n");
41 lines
.push(""); //easier parsing: always empty line at the end
43 for (let i
=0; i
< lines
.length
; i
++)
45 lines
[i
] = lines
[i
].trim();
47 if (lines
[i
].length
== 0)
49 if (start
>= 0) //there is some group of lines to parse
51 this.parseThing(lines
, start
, i
);
55 else //not empty line: just register starting point
63 // Parse a group of lines into entity, association, ...
64 parseThing(lines
, start
, end
) //start included, end excluded
66 switch (lines
[start
].charAt(0))
69 // Entity = { name: { attributes, [weak] } }
70 let name
= lines
[start
].match(/[^\[\]"\s]+/)[0];
71 let entity
= { attributes: this.parseAttributes(lines
, start
+1, end
) };
72 if (lines
[start
].charAt(1) == '[')
74 this.entities
[name
] = entity
;
76 case 'i': //inheritance (arrows)
77 this.inheritances
= this.inheritances
.concat(this.parseInheritance(lines
, start
+1, end
));
79 case '{': //association
80 // Association = { [name], [attributes], [weak], entities: ArrayOf entity indices }
81 let relationship
= { };
82 let nameRes
= lines
[start
].match(/[^{}"\s]+/);
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
)));
92 // attributes: ArrayOf {name, [isKey], [type], [qualifiers]}
93 parseAttributes(lines
, start
, end
)
96 for (let i
=start
; i
<end
; i
++)
100 if (line
.charAt(0) == '#')
103 line
= line
.slice(1);
105 field
.name
= line
.match(/[^()"\s]+/)[0];
106 let parenthesis
= line
.match(/\((.+)\)/);
107 if (parenthesis
!== null)
109 let sqlClues
= parenthesis
[1];
110 field
.type
= sqlClues
.match(/[^\s]+/)[0]; //type is always the first indication (mandatory)
111 field
.qualifiers
= sqlClues
.substring(field
.type
.length
).trim();
113 attributes
.push(field
);
118 // GroupOf Inheritance: { parent, children: ArrayOf entity indices }
119 parseInheritance(lines
, start
, end
)
121 let inheritance
= [];
122 for (let i
=start
; i
<end
; i
++)
124 let lineParts
= lines
[i
].split(" ");
126 for (let j
=1; j
<lineParts
.length
; j
++)
127 children
.push(lineParts
[j
]);
128 inheritance
.push({ parent:lineParts
[0], children: children
});
133 // Association (parsed here): {
134 // entities: ArrayOf entity names + cardinality,
135 // [attributes: ArrayOf {name, [isKey], [type], [qualifiers]}]
137 parseAssociation(lines
, start
, end
)
144 if (lines
[i
].charAt(0) == '-')
146 assoce
.attributes
= this.parseAttributes(lines
, i
+1, end
);
151 // Read entity name + cardinality
152 let lineParts
= lines
[i
].split(" ");
153 entities
.push({ name:lineParts
[0], card:lineParts
[1] });
157 assoce
.entities
= entities
;
161 //////////////////////////////
162 // PARSING STAGE 2: MCD to MLD
163 //////////////////////////////
165 // From entities + relationships to tables
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
=> {
176 qualifiers: attr
.qualifiers
,
179 this.tables
[name
] = newTable
;
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]))
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
)
191 e2
.attributes
.forEach( attr
=> {
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
,
198 qualifiers: "foreign key references " + e2
.name
+ " " + (e
.card
[0]=='1' ? "not null" : ""),
199 ref: e2
.name
, //easier drawMld function (fewer regexps)
207 // Add all keys in current entity
208 let fields
= this.entities
[e
.name
].attributes
.filter( attr
=> { return attr
.isKey
; });
215 if (newTableAttrs
.length
> 1)
217 // Ok, really create a new table
219 name: a
.name
|| newTableAttrs
.map( item
=> { return item
.entity
; }).join("_"),
222 newTableAttrs
.forEach( item
=> {
223 item
.fields
.forEach( f
=> {
224 newTable
.fields
.push({
225 name: item
.entity
+ "_" + f
.name
,
228 qualifiers: (f
.qualifiers
+" " || "") + "foreign key references " + item
.entity
+ " not null",
233 // Add relationship potential own attributes
234 a
.attributes
.forEach( attr
=> {
235 newTable
.fields
.push({
239 qualifiers: attr
.qualifiers
,
242 this.tables
[newTable
.name
] = newTable
.fields
;
247 /////////////////////////////////
248 // DRAWING + GET SQL FROM PARSING
249 /////////////////////////////////
251 static AjaxGet(dotInput
, callback
)
253 let xhr
= new XMLHttpRequest();
254 xhr
.onreadystatechange = function() {
255 if (this.readyState
== 4 && this.status
== 200)
256 callback(this.responseText
);
258 xhr
.open("GET", "scripts/getGraphSvg.php?dot=" + encodeURIComponent(dotInput
), true);
262 // "Modèle conceptuel des données". TODO: option for graph size
263 // NOTE: randomizing helps to obtain better graphs (sometimes)
264 drawMcd(id
, mcdStyle
) //mcdStyle: bubble, or compact
266 let element
= document
.getElementById(id
);
267 mcdStyle
= mcdStyle
|| "compact";
268 if (this.mcdGraph
.length
> 0)
270 element
.innerHTML
= this.mcdGraph
;
273 // Build dot graph input
274 let mcdDot
= 'graph {\n';
275 mcdDot
+= 'rankdir="LR";\n';
277 if (mcdStyle
== "compact")
278 mcdDot
+= 'node [shape=plaintext];\n';
279 _
.shuffle(Object
.keys(this.entities
)).forEach( name
=> {
280 if (mcdStyle
== "bubble")
282 mcdDot
+= '"' + name
+ '" [shape=rectangle, label="' + name
+ '"';
283 if (this.entities
[name
].weak
)
284 mcdDot
+= ', peripheries=2';
286 if (!!this.entities
[name
].attributes
)
288 this.entities
[name
].attributes
.forEach( a
=> {
289 let label
= (a
.isKey
? '#' : '') + a
.name
;
290 let attrName
= name
+ '_' + a
.name
;
291 mcdDot
+= '"' + attrName
+ '" [shape=ellipse, label="' + label
+ '"];\n';
292 if (Math
.random() < 0.5)
293 mcdDot
+= '"' + attrName
+ '" -- "' + name
+ '";\n';
295 mcdDot
+= '"' + name
+ '" -- "' + attrName
+ '";\n';
301 mcdDot
+= '"' + name
+ '" [label=<';
302 if (this.entities
[name
].weak
)
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';
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
)
312 this.entities
[name
].attributes
.forEach( a
=> {
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';
317 mcdDot
+= '</table>';
318 if (this.entities
[name
].weak
)
319 mcdDot
+= '</td></tr></table>';
324 _
.shuffle(this.inheritances
).forEach( i
=> {
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/
329 _
.shuffle(i
.children
).forEach( c
=> {
330 if (Math
.random() < 0.5)
331 mcdDot
+= '"' + c
+ '":name -- "' + i
.parent
;
333 mcdDot
+= '"' + i
.parent
+ '":name -- "' + c
;
334 mcdDot
+= '":name [dir="forward", arrowhead="vee", style="dashed"];\n';
338 if (mcdStyle
== "compact")
339 mcdDot
+= 'node [shape=rectangle, style=rounded];\n';
340 let assoceCounter
= 0;
341 _
.shuffle(this.associations
).forEach( a
=> {
342 let name
= a
.name
|| "_assoce" + assoceCounter
++;
343 if (mcdStyle
== "bubble")
345 mcdDot
+= '"' + name
+ '" [shape="diamond", style="filled", color="lightgrey", label="' + name
+ '"';
347 mcdDot
+= ', peripheries=2';
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';
358 mcdDot
+= '"' + name
+ '" -- "' + attrName
+ '";\n';
364 let label
= '<' + name
+ '>';
367 a
.attributes
.forEach( attr
=> {
368 let attrLabel
= (attr
.isKey
? '#' : '') + attr
.name
;
369 label
+= '\\n' + attrLabel
;
372 mcdDot
+= '"' + name
+ '" [color="lightgrey", label="' + label
+ '"';
374 mcdDot
+= ', peripheries=2';
377 _
.shuffle(a
.entities
).forEach( e
=> {
378 if (Math
.random() < 0.5)
379 mcdDot
+= '"' + e
.name
+ '":name -- "' + name
+ '"';
381 mcdDot
+= '"' + name
+ '" -- "' + e
.name
+ '":name';
382 mcdDot
+= '[label="' + ErDiags
.CARDINAL
[e
.card
] + '"];\n';
387 ErDiags
.AjaxGet(mcdDot
, graphSvg
=> {
388 this.mcdGraph
= graphSvg
;
389 element
.innerHTML
= graphSvg
;
393 // "Modèle logique des données", from MCD without anomalies
394 // TODO: this one should draw links from foreign keys to keys (port=... in <TD>)
397 let element
= document
.getElementById(id
);
398 if (this.mldGraph
.length
> 0)
400 element
.innerHTML
= this.mcdGraph
;
403 // Build dot graph input (assuming foreign keys not already present...)
404 let mldDot
= 'graph {\n';
405 mldDot
+= 'node [shape=plaintext];\n';
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';
410 this.tables
[name
].forEach( f
=> {
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';
416 if (Math
.random() < 0.5)
417 links
+= '"' + f
.ref
+ '":__key -- "' + name
+'":"'+f
.name
+'"\n';
419 links
+= '"'+name
+'":"'+f
.name
+'" -- "' + f
.ref
+ '":__key\n';
422 mldDot
+= '</table>>];\n';
424 mldDot
+= links
+ '\n';
427 ErDiags
.AjaxGet(mldDot
, graphSvg
=> {
428 this.mldGraph
= graphSvg
;
429 element
.innerHTML
= graphSvg
;
435 let element
= document
.getElementById(id
);
436 if (this.sqlText
.length
> 0)
438 element
.innerHTML
= this.sqlText
;
442 Object
.keys(this.tables
).forEach( name
=> {
443 sqlText
+= "CREATE TABLE " + name
+ " (\n";
445 this.tables
[name
].forEach( f
=> {
446 sqlText
+= f
.name
+ " " + (f
.type
|| "TEXT") + (" "+f
.qualifiers
|| "") + ",\n";
448 key
+= (key
.length
>0 ? "," : "") + f
.name
;
450 sqlText
+= "PRIMARY KEY (" + key
+ ")\n";
453 //console.log(sqlText);
454 this.sqlText
= sqlText
;
455 element
.innerHTML
= "<pre><code>" + sqlText
+ "</code></pre>";