Best database independent SQL DDL utility?

Question

I'm working on a project for the .net platform and would like to support multiple database types. I would like to keep the DDL under source control in a generic format then convert it to database specific DDL for deployment.

So I'm looking for utilities that will convert generic DDL into database specific DDL. Ideally it would support MSSQL 05/08, MySQL, Oracle, Postgres, & DB2 out of the box.

Here are the tools that I've found so far:

1. XML to DDL
   • No built in support for MSSQL.
2. DdlUtils
   • No command line utility. Must be called from java or ant scripts.
3. ActiveRecord::Migration
   • No support for foreign keys
   • Not sure how to integrate with .net project.

Does anyone have experience with those I've mentioned or know of others?

Answer 1

The only one that I know of that has support for SQL Server is SQLFairy. It's written in Perl and is pretty feature rich. XML2DDL is pretty good too, but if it doesn't support your DBMS of choice it's not really viable.

Answer 2

Is it a must-have feature that your application is completely database agnostic? It seems a far stretch to believe that you need to put this much work into supporting this many databases. On top of the fact your data layer is going to be incredibly complex as there are subtle differences in each RDBMS.

Answer 3

NHibernate's SchemaExport tool can generate an appropriate DDL from the OR mappings for any of NHibernate's supported DBMS dialects. However, as others have implied, if you're working at that level you're really restricted to the rather thin common denominator between DBMSes.

Answer 4

You may succeed in getting an abstract representation of tables and their relationships - but how do you deal with views, stored procedures, triggers etc?

Answer 5

I've successfully used Ruby/Rails' ActiveRecord Migrations on Oracle, SQL Server 2005, MySQL and SQLite. I think I may have managed to use it on Access too, but that may a faulty memory. It also supports PostgreSQL and db2 that I know of, either "out of the box" or by additional download. And you can always write your own adapter if you want something more exotic and have a desire for a really self-flagellatory DIY project...

It works really well, but you have to accept that this is a concept that will limit your access to platform-specific features. Not just with AR, but with - in all probability - any cross-platform tool that doesn't cost bazillions: for example, what do you do if your target platform doesn't support triggers? Or stored procedures? (MySQL 4.0, for example, or SQLite). Any cross-platform system has to deal with issues like this (I acquired a lifetime hatred of Crystal Reports, for example, after wrestling with a version that tried - catastrophically - to apply an Oracle outer-join operator in a SQL Server query).

If you stick to tables, indexes and the simpler constraints, I'd expect a wide variety of platforms to be available to you. There's an argument for suggesting that you perhaps should generally be looking for anything further to be handled outside your DB. I won't go further than that here - it's a somewhat religious debate...

Answer 6

DDLUtils seems to be the right choice. Works very well, even if the project is not really active these days.

The coding is fine, and I had no issues with it (working on MySQL, Oracle, H2DB, ...).

Indeed there is for the now no command-line built-in, but you can either launch ant tasks from command-line, or write your own command-line wrapper (it's really not so hard ... i did it on my side, in fact).

Answer 7

I have done basic experiments with DDLUtils. I tried for Mysql,MSSql and oracle ,it works fine. checked for table creation ,constraints and indexes. Refused to use it as it is not active and blocker bugs are open from long time.

Answer 8

I believe that Liquibase will do what you want, and will also manage database changes over time.