Simplified/shorthand SQL Data Definition Languages?

Question

Some people like describing their database structure in a simple textual way instead of using CREATE TABLE statements. A few example:

Do you know about any software which converts this kind of shorthand notation to actual SQL statements?

There are some tools that can do the opposite (as an image, I don't know for shorthand notation). Create a database (with SQL statements or with GUI) and the tool makes a drawing of the tables. See [this question](http://stackoverflow.com/questions/8895806/how-to-implement-polymorphic-associations-in-an-existing-database/9449031#9449031). I think the OP there has used Visio. The image in my answer was made my MySQL Workbench. — ypercubeᵀᴹ, Mar 10 '12 at 00:29
Yes, there are so many click'n'draw-your-database solution, but so few (if any?) which works without using your mouse. — biziclop, Mar 10 '12 at 09:15
Most tools I've used can read good old plain SQL. And many tools have reverse-engineering that retrieve the create tables SQL from existing databases. And can draw nice diagrams. — ypercubeᵀᴹ, Mar 10 '12 at 11:03
http://sqlfiddle.com/ has a very simple Text to DDL feature. — biziclop, Mar 19 '12 at 07:18
Simplified/streamlined `SELECT`ing (it's awesome): http://htsql.org/ — biziclop, Mar 21 '12 at 06:00
The problem with such a tool would be that it would be inherently limited in features it supports. If it could support all the sql features, it would be nearly as verbose as sql. So, you may find people's custom script (see answer below) but it'll be tailored for that person's preferred feature set, and will require that you conform to their notation / design conventions — Alex Weitzer, Mar 21 '12 at 19:46
http://www.llblgen.com/documentation/3.0/Designer/Functionality%20Reference/QuickModelEditor.htm — biziclop, Apr 03 '12 at 08:28

biziclop · Accepted Answer · 2014-11-06T22:31:59.443

7

Actually, I just finished creating a php script, which does exactly this, but I hope there is something more professional out there...

Demo of my converter:

http://simpleddl.orgfree.com

Example input:

= ID id P AI

person
  ID
  mother_id -> person
  father_id -> person
  !FK mother_id, father_id -> family U:C, D:C

family
  P female_id -> person
  P male_id   -> person

Output:

CREATE TABLE IF NOT EXISTS person (
   id         INT NOT NULL AUTO_INCREMENT,
   mother_id  INT NOT NULL,
   father_id  INT NOT NULL,
   PRIMARY KEY ( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS family (
   female_id  INT NOT NULL,
   male_id    INT NOT NULL,
   PRIMARY KEY ( female_id, male_id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE person ADD FOREIGN KEY ( mother_id ) REFERENCES person( id );
ALTER TABLE person ADD FOREIGN KEY ( father_id ) REFERENCES person( id );
ALTER TABLE person ADD FOREIGN KEY ( mother_id, father_id ) REFERENCES family( female_id, male_id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE family ADD FOREIGN KEY ( female_id ) REFERENCES person( id );
ALTER TABLE family ADD FOREIGN KEY ( male_id ) REFERENCES person( id );

edited Nov 06 '14 at 22:31

answered Mar 09 '12 at 20:35

biziclop

14,466
3
49
65

1

That's pretty cool. Does it support other DBMS (e.g. PostgreSQL) as well? – Mar 11 '12 at 09:15
The above link is expired meanwhile. If you are interested, drop a comment here :) – biziclop Sep 19 '13 at 18:42
biziclop I'd like to see your code and notation ideas (I'm actually interested in ddl for oracle at the moment), you have this in a public repository anywhere? – plockc Nov 06 '14 at 14:49
@plockc You can try it here: http://simpleddl.orgfree.com At the bottom of that page you can download the source code, which is a piece of very unprofessional mess, not worthy of any repository. But at least you can play with it :) – biziclop Nov 06 '14 at 22:25

score 3 · Answer 2 · answered Mar 29 '12 at 20:08

I see you mentioned the text-to-ddl tool that is available on http://sqlfiddle.com. This is actually a tool I built (sqlfiddle.com is my site) in JavaScript specifically to try to make it easier to take the text tables people post in their StackOverflow questions and more quickly translate them into real DDL. I think it works fairly well for a fair amount of common variation of text table formats, but I'm sure it could use some work to handle a greater variety. I support the different DDL types via separate handlebars.js templates for each one (MS SQL, Oracle, PostgreSQL, MySQL and SQLite).

The whole library is written as JavaScript, so if anyone would like to help me make it better, I would love your contributions. Fork me on github and look for the file javascripts/ddl_builder.js. I would love to have some pull requests!

Simplified/shorthand SQL Data Definition Languages?

2 Answers2