5

I'm trying to find information about the correct RDBMS SQL queries for open source databases like MySQL, PostgreSQL, SQLite, and others. Are their any pre-assembled lists out there or do I just need to comb the documentation for each database engine (and in some cases guess as to the correct implementation? ALTER TABLE vs CREATE INDEX)

For example, so far I have this (partial) list for MySQL:

CREATE TABLE `%s` (...);
DROP TABLE IF EXISTS `%s` %s;
ALTER TABLE `%s` RENAME TO `%s`;

ALTER TABLE `%s` ADD COLUMN %s;
ALTER TABLE `%s` DROP COLUMN `%s` %s;
ALTER TABLE `%s` RENAME COLUMN `%s` to `%s`;

ALTER TABLE `%s` ADD CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `%s` ADD CONSTRAINT `%s` UNIQUE (`%s`);
ALTER TABLE `%s` DROP CONSTRAINT `%s` %s;

CREATE INDEX `%s` USING BTREE ON `%s` (`%s`);
DROP INDEX IF EXISTS `%s` %s;
Xeoncross
  • 55,620
  • 80
  • 262
  • 364
  • "July 1992", are you sure? That's SQL from 20 years ago. – Frank Heikens Sep 18 '12 at 14:46
  • @FrankHeikens, ANSI-92 is still in wide use and covers this question, though I don't mind using another standard like ANSI-99 or even newer. – Xeoncross Sep 18 '12 at 14:48
  • 1
    Are you looking for a cheat sheet or are you trying to implement a DDL generation tool, or something else? – Ants Aasma Sep 19 '12 at 00:17
  • I'm interested in trying to implement a DDL generation tool – Xeoncross Aug 06 '13 at 16:04
  • There are some good websites online that have summaries of the differences between various database systems. This topic is very similar to [this one](http://stackoverflow.com/questions/4442233/good-reference-feature-syntax-comparison-matrix-for-sql-databases). I suggest you take a look there. – bvdb Aug 12 '13 at 16:47

3 Answers3

4

I never see some similar, but you can get EBNF for ANSI SQL

http://savage.net.au/SQL/

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
0

You can take a look at some ActiveRecord implementation for clues. They try to implement a commom layer for queries. Most frameworks provide some kind of abstration for SQL engine. Win portability lose the special implementation details on each database vendor.

If you have time you can take a look at each database documentation and create the parser. For Postgresql database you can check command constructs at the manual: http://www.postgresql.org/docs/9.0/static/sql-commands.html.

Postgresql for a small example adds schema name on table. Using the public schema as default.

CREATE TABLE "%s"."%s" (...);
cavila
  • 7,834
  • 5
  • 21
  • 19
0

One dirty trick that comes to my mind is to model this using an O/R engine like Hibernate. Develop annotated classes or .hbm.xml files and simply turn on hbm2ddl=auto and run it against your list of databases. Use the show_sql property to log all the DML/DDL that hibernate is producing...

I am assuming that O/R frameworks like hibernate have seen the world and can handle all the nuances of established RDBMS platforms. I am not sure however if hibernate attempts to generate DDL scriptlets that are as generic as possible or as native to the dialect as possible.

hope this helps ;)

arajashe
  • 281
  • 1
  • 9