I liked plantuml tool for UML diagrams but use it
also for visualizing structure of relational database.
This script loads plantuml class diagram and generates
DDL for MySQL SQL dialect. You may define primary keys
with # prefix in field name (it means protected field
in PlantUML) and define index fields with + (public field
in PlantUML) prefix.
Field type noted after field name as is. Also you may
use comments after --.
For example class definition:
@startuml
class dummy {
Sample table.
==
#id int(10) -- A comment
field1 int(10)
.. Comment line, ignored ..
field2 varchar(128)
}
@enduml
will be converted to SQL:
CREATE TABLE IF NOT EXISTS `dummy` (
id INT(10) COMMENT 'A comment',
field1 INT(10),
field2 VARCHAR(128),
PRIMARY KEY (id));
Text between class name and == is table description.
The description of the table is mandatory.
I was too lazy to check for absence of descriptions but
not lazy to write them in each table of my databases.
A line starting with .. or __, used as a separator
into a class definition, will be ignored.
The HTML markup in comments (after --) is stripped.
See below the result of a more complicated sample from database.plu:
./plantuml2mysql.py database.plu sampledb
CREATE DATABASE sampledb CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; USE sampledb; CREATE TABLE IF NOT EXISTS `user` ( id SERIAL, login VARCHAR(16), mail VARCHAR(64), docsRef INT(10) COMMENT 'referenced docs for a user', created INT(11), sesid INT(11), PRIMARY KEY (id), INDEX (login), INDEX (mail) ); CREATE TABLE IF NOT EXISTS `session` ( id SERIAL, uid INT(10) UNSIGNED, remoteip INT(10) UNSIGNED, useragent VARCHAR(255), data LONGTEXT COMMENT 'serialized session data', lastseen INT(11), PRIMARY KEY (id), INDEX (uid), INDEX (lastseen) ); CREATE TABLE IF NOT EXISTS `docs` ( id INT(10), fid INT(10) COMMENT 'link to a file', aunthorid INT(10), created INT(11), PRIMARY KEY (id, fid), INDEX (aunthorid), INDEX (created) ); CREATE TABLE IF NOT EXISTS `files` ( id SERIAL, docId INT(10), title VARCHAR(255), path VARCHAR(255), hash INT(32) UNSIGNED, PRIMARY KEY (id), INDEX (docId) );
The script not uses external dependencies. If you have installed Python 3
properly then just download plantuml2mysql.py to appropriate location and
run as any other Python script.
I just satisfied with this code as is but new features and fixes are welcome. Code is public domain.
Thank for contributions:
- Benoît Bailleux for a lot of features.
- github.com/achiku/planter — generate ER diagrams from PostgreSQL tables