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