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:

• Foreign key as Primary key
• Multiple Primary Keys as single foreign key
• How can I set up a database schema where there are two concurrent many-many relationships?
• primary key and foreign key
• How should I set up database tables for this order situation

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

Effrontery

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. I think the OP there has used Visio. The image in my answer was made my MySQL Workbench. – Guy

Yes, there are so many click'n'draw-your-database solution, but so few (if any?) which works without using your mouse. – Effrontery

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. – Guy

sqlfiddle.com has a very simple Text to DDL feature. – Effrontery

Simplified/streamlined SELECTing (it's awesome): htsql.org – Effrontery

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 – Bagger

llblgen.com/documentation/3.0/Designer/… – Effrontery

andromeda-project.org/databasestructure.html – Effrontery

Answer 1

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 );

Effrontery

That's pretty cool. Does it support other DBMS (e.g. PostgreSQL) as well? – Lubbi

The above link is expired meanwhile. If you are interested, drop a comment here :) – Effrontery

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? – Knott

@Knott You can try it here: 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 :) – Effrontery

Answer 2

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!

Shirtwaist