From 3789126f0dc7d0b751d99ded86a97c2b258afe80 Mon Sep 17 00:00:00 2001 From: Benjamin Auder <benjamin.auder@somewhere> Date: Sat, 3 Feb 2018 01:47:18 +0100 Subject: [PATCH] Better foreign key handling: correct SQL output --- README.md | 5 +- example.html => examples/example.html | 0 examples/example2.html | 83 +++++++++++++++++++++ example_MCD.svg => examples/example_MCD.svg | 0 example_MLD.svg => examples/example_MLD.svg | 0 parser.js | 45 +++++------ 6 files changed, 110 insertions(+), 23 deletions(-) rename example.html => examples/example.html (100%) create mode 100644 examples/example2.html rename example_MCD.svg => examples/example_MCD.svg (100%) rename example_MLD.svg => examples/example_MLD.svg (100%) diff --git a/README.md b/README.md index ef279e6..b57f840 100644 --- a/README.md +++ b/README.md @@ -56,10 +56,11 @@ To indicate an inheritance relation, proceed as follow Animal Cat Fish Planet Mars Venus -Finally, blocks must be separated by new lines. For a usage example, see example.html (it should render as seen in example\_\*.svg) +Finally, blocks must be separated by new lines. For a usage example, see example.html (it should render as seen in example\_\*.svg); +or example2.html for a bigger, more realistic illustration (small social network). Note that the "drawMcd" method can take a second argument, which indicates the type of graph. - * "bubble" draws the standard graph, as seen [here](https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#/media/File:ER_Diagram_MMORPG.png) for example + * "bubble" draws the standard graph, as seen [here](https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#/media/File:ER_Diagram_MMORPG.png) * "compact" (default) use the same box for an entity and its attributes ----- diff --git a/example.html b/examples/example.html similarity index 100% rename from example.html rename to examples/example.html diff --git a/examples/example2.html b/examples/example2.html new file mode 100644 index 0000000..9d424c2 --- /dev/null +++ b/examples/example2.html @@ -0,0 +1,83 @@ +<h2>MCD graph:</h3> +<div id="mcd"></div> + +<h2>MLD graph:</h2> +<div id="mld"></div> + +<h2>SQL instructions:</h2> +<div id="sql"></div> + +<script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.3/underscore-min.js"></script> +<script src="parser.js"></script> +<script> + let er = + new ErDiags(` + [Users] + +id + name VARCHAR NOT NULL + email VARCHAR + location VARCHAR + birthdate DATE + gender CHARACTER + avatar BLOB + + [Groups] + +id + name VARCHAR NOT NULL + description TEXT + + [Events] + +id + name VARCHAR NOT NULL + description TEXT + + [Messages] + +id + date DATE NOT NULL + content TEXT NOT NULL + receiver INTEGER REFERENCES Users(id) NOT NULL + + [Posts] + +id + content TEXT NOT NULL + date DATE NOT NULL + type VARCHAR NOT NULL DEFAULT "Wall" + reference INTEGER NOT NULL + + {publish} + Posts 1 + Users * + + {send} + Messages 1 + Users * + + {like} + Users * + Posts * + + {follow} + Users * + Users * + + {friend_with} + Users * + Users * + + {participate} + Events * + Users * + -- + degree VARCHAR NOT NULL DEFAULT "sure" + creator BOOLEAN + + {belong_to} + Groups + + Users * + -- + creator BOOLEAN + `); + er.drawMcd("mcd"); //,"bubble" + er.drawMld("mld"); + er.fillSql("sql"); +</script> diff --git a/example_MCD.svg b/examples/example_MCD.svg similarity index 100% rename from example_MCD.svg rename to examples/example_MCD.svg diff --git a/example_MLD.svg b/examples/example_MLD.svg similarity index 100% rename from example_MLD.svg rename to examples/example_MLD.svg diff --git a/parser.js b/parser.js index 65ac9ec..5ef2b3f 100644 --- a/parser.js +++ b/parser.js @@ -172,8 +172,11 @@ class ErDiags isKey: attr.isKey, qualifiers: attr.qualifiers, }; - if (!!attr.qualifiers && !!attr.qualifiers.match(/foreign/i)) + if (!!attr.qualifiers && !!attr.qualifiers.match(/references/i)) + { Object.assign(newField, {ref: attr.qualifiers.match(/references ([^\s]+)/i)[1]}); + attr.qualifiers = attr.qualifiers.replace(/references [^\s]+/, ""); + } newTable.push(newField); }); this.tables[name] = newTable; @@ -187,8 +190,8 @@ class ErDiags name: inh.parent + "_id", type: this.tables[inh.parent][idx].type, isKey: true, - qualifiers: (this.tables[inh.parent][idx].qualifiers || "") + " foreign key references " + inh.parent, - ref: inh.parent, + qualifiers: this.tables[inh.parent][idx].qualifiers || "", + ref: inh.parent + "(" + this.tables[inh.parent][idx].name + ")", }); }); }); @@ -213,8 +216,8 @@ class ErDiags isKey: isKey, name: e2.name + "_" + attr.name, type: attr.type, - qualifiers: "foreign key references " + e2.name + " " + (!isKey && e.card[0]=='1' ? "not null" : ""), - ref: e2.name, //easier drawMld function (fewer regexps) + qualifiers: !isKey && e.card[0]=='1' ? "not null" : "", + ref: e2.name + "(" + attr.name + ")", }); } }); @@ -243,8 +246,8 @@ class ErDiags name: item.entity + "_" + f.name, isKey: true, type: f.type, - qualifiers: (f.qualifiers || "") + " foreign key references " + item.entity, - ref: item.entity, + qualifiers: f.qualifiers || "", + ref: item.entity + "(" + f.name + ")", }); }); }); @@ -447,20 +450,11 @@ class ErDiags mldDot += '<tr><td port="' + f.name + '"' + ' BGCOLOR="#FFFFFF" BORDER="0" ALIGN="LEFT"><font COLOR="#000000" >' + label + '</font></td></tr>\n'; if (!!f.ref) { - // Need to find a key attribute in reference entity (the first...) - let keyInRef = ""; - for (let field of this.tables[f.ref]) - { - if (field.isKey) - { - keyInRef = field.name; - break; - } - } + const refPort = f.ref.slice(0,-1).replace('(',':'); if (Math.random() < 0.5) - links += '"' + f.ref + '":"' + keyInRef + '" -- "' + name+'":"'+f.name + '" [dir="forward",arrowhead="dot"'; + links += refPort + ' -- "' + name+'":"'+f.name + '" [dir="forward",arrowhead="dot"'; else - links += '"'+name+'":"'+f.name+'" -- "' + f.ref + '":"' + keyInRef + '" [dir="back",arrowtail="dot"'; + links += '"'+name+'":"'+f.name+'" -- ' + refPort + ' [dir="back",arrowtail="dot"'; links += ']\n;'; } }); @@ -487,14 +481,23 @@ class ErDiags Object.keys(this.tables).forEach( name => { sqlText += "CREATE TABLE " + name + " (\n"; let key = ""; + let foreignKey = [ ]; this.tables[name].forEach( f => { let type = f.type || (f.isKey ? "INTEGER" : "TEXT"); + if (!!f.ref) + foreignKey.push({name: f.name, ref: f.ref}); sqlText += "\t" + f.name + " " + type + " " + (f.qualifiers || "") + ",\n"; if (f.isKey) key += (key.length>0 ? "," : "") + f.name; }); - sqlText += "\tPRIMARY KEY (" + key + ")\n"; - sqlText += ");\n"; + sqlText += "\tPRIMARY KEY (" + key + ")"; + foreignKey.forEach( f => { + let refParts = f.ref.split("("); + const table = refParts[0]; + const field = refParts[1].slice(0,-1); //remove last parenthesis + sqlText += ",\n\tFOREIGN KEY (" + f.name + ") REFERENCES " + table + "(" + field + ")"; + }); + sqlText += "\n);\n"; }); //console.log(sqlText); this.sqlText = sqlText; -- 2.44.0