SQL database visualisation
If you want to visualise database structure you can use reverse engineering, i.e. SchemaSpy Graphical Database Schema Metadata Browser or mysqlviz (for MySQL only). SchemaSpy is written in Java and support several types of databases, mysqlviz is in PHP. Both use Graphviz which you have to install separately.
There is a GUI for SchemaSpy - SchemaSpyGUI. To work with MySQL you need MySQL Connector/J - it's a little bit tricky because it's install in x86 patch even if you install 64 bit version of MySQL - at least in MS Windows.
In my case "Patch to DB driver" looks like this:
C:\Program Files (x86)\MySQL\Connector J 5.1.28\mysql-connector-java-5.1.28-bin.jar
The output is browsable "web page" about structure of analysed database.
Mysqlviz is simple PHP script - you have to prepare database dump in special format - special means different than generated by MySQL Workbench. You have to omit schema in dump (it is simple option in export options), but you have also check if CONSTRAINT are in single line. Dump generated by Workbench divide it into few lines. So you have to (I did it manually but I would prefer sed/awk) change this:
CONSTRAINT `categories_ibfk_1`
FOREIGN KEY (`parent_id`)
REFERENCES `categories` (`category_id`)
ON DELETE SET NULL
ON UPDATE CASCADE,
into (long) single-line entry:
CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE
then you can generate DOT file:
./mysqlviz -f model.sql > output.dot
and open created in Graphviz.
Kommentare
Bitte melden Sie sich Logan oder registrieren Sie sich um kommentieren zu können.