Last active
November 7, 2018 06:01
-
-
Save agea/6591881 to your computer and use it in GitHub Desktop.
Generate Graphml ER Diagram from SQL Connection
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import groovy.sql.* | |
def tables = [:] | |
def visitTable = { dbmd, schema, tableName -> | |
if (!tables[tableName]) { | |
tables[tableName] = new HashSet() | |
} | |
def keyRS = dbmd.getExportedKeys(null, schema, tableName) | |
while (keyRS.next()) { | |
tables[tableName] << keyRS.getString("FKTABLE_NAME") | |
} | |
keyRS.close() | |
} | |
def config = [ | |
host: "localhost", port: 3306, | |
dbname: "imagact", username: "root", password: "root", | |
driver: "com.mysql.jdbc.Driver", | |
schema: "imagact" ] | |
def url = "jdbc:mysql://${config.host}/${config.dbname}" | |
def sql = Sql.newInstance(url, config.username, config.password, config.driver) | |
def dbmd = sql.connection.metaData | |
def tableRS = dbmd.getTables(null, config.schema, null, "TABLE") | |
while (tableRS.next()) { | |
visitTable(dbmd, config.schema, tableRS.getString("TABLE_NAME")) | |
System.err.print "." | |
} | |
System.err.println "" | |
tableRS.close() | |
sql.connection.close() | |
println """<?xml version="1.0" encoding="UTF-8" standalone="no"?> | |
<graphml xmlns="http://graphml.graphdrawing.org/xmlns/graphml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:y="http://www.yworks.com/xml/graphml" xsi:schemaLocation="http://graphml.graphdrawing.org/xmlns/graphml http://www.yworks.com/xml/schema/graphml/1.0/ygraphml.xsd"> | |
<key for="node" id="d0" yfiles.type="nodegraphics"/> | |
<key attr.name="description" attr.type="string" for="node" id="d1"/> | |
<key for="edge" id="d2" yfiles.type="edgegraphics"/> | |
<key attr.name="description" attr.type="string" for="edge" id="d3"/> | |
<key for="graphml" id="d4" yfiles.type="resources"/> | |
<graph id="${config.schema}" edgedefault="directed">""" | |
tables.each { k,v -> | |
nodeId = "${config.schema}_${k}" | |
println """<node id="${nodeId}"> | |
<data key="d0"> | |
<y:ShapeNode> | |
<y:Geometry height="30.0" width="${nodeId.length() * 8}.0" x="0.0" y="0.0"/> | |
<y:Fill color="#E8EEF7" color2="#B7C9E3" transparent="false"/> | |
<y:BorderStyle color="#000000" type="line" width="1.0"/> | |
<y:NodeLabel alignment="center" autoSizePolicy="content" fontFamily="Dialog" fontSize="13" fontStyle="plain" hasBackgroundColor="false" hasLineColor="false" height="19.92626953125" modelName="internal" modelPosition="c" textColor="#000000" visible="true" width="37.0" x="5.5" y="5.036865234375">${k}</y:NodeLabel> | |
<y:Shape type="rectangle"/> | |
<y:DropShadow color="#B3A691" offsetX="2" offsetY="2"/> | |
</y:ShapeNode> | |
</data> | |
</node>""" | |
} | |
tables.each { k,v -> | |
v.each { referer -> | |
edgeId = "${config.schema}_${referer}_${k}" | |
println """<edge id="${edgeId}" source="${config.schema}_${referer}" target="${config.schema}_${k}"> | |
<data key="d2"> | |
<y:PolyLineEdge> | |
<y:Path sx="0.0" sy="13.5" tx="0.0" ty="-15.0"/> | |
<y:LineStyle color="#000000" type="line" width="1.0"/> | |
<y:Arrows source="none" target="crows_foot_many_mandatory"/> | |
<y:EdgeLabel alignment="center" distance="2.0" fontFamily="Dialog" fontSize="12" fontStyle="plain" hasBackgroundColor="false" hasLineColor="false" height="4.0" modelName="six_pos" modelPosition="tail" preferredPlacement="anywhere" ratio="0.5" textColor="#000000" visible="true" width="4.0" x="2.0000069969042897" y="18.5"/> | |
<y:BendStyle smoothed="false"/> | |
</y:PolyLineEdge> | |
</data> | |
</edge>""" | |
} | |
} | |
println """<data key="d4"> | |
<y:Resources/> | |
</data> | |
</graph> | |
</graphml>""" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is awesome, thanks for posting!
For anyone like me who's not familiar with Groovy, you can resolve the dependency by putting mysql-connector.jar in
~/.groovy/lib
. For some reason, using Groovy's Grape failed.Untested script: