Best Independent SQL DDL Database?

Question

I am working on a project for the .net platform and want to support several types of databases. I would like to keep the DDL under source control in a common format, and then convert it to DDL for a specific database for deployment.

So, I am looking for utilities that will convert a common DDL to a DDL database. Ideally, it would support MSSQL 05/08, MySQL, Oracle, Postgres, and DB2 out of the box.

Here are the tools I've found so far:

• XML for DDL
  • MSSQL support is not supported.
• Ddlutils
  • There is no command line utility. Must be called from java or ant scripts.
• ActiveRecord :: Migration
  • Foreign Key Support
  • Not sure how to integrate with a .net project.

Does anyone have experience working with those I mentioned or know about others?

Answer 1

The only thing I know about this is SQL Server SQLFairy support . It is written in Perl and is quite rich. XML2DDL is also very good, but if it does not support your DBMS of choice, it is not very viable.

Answer 2

NHibernate The SchemaExport tool can generate the appropriate DDL from OR mappings for any of the DBMS dialects supported by NHibernate. However, as others suggest, if you work at this level, you are really limited by the rather subtle common denominator between the DBMS.

Answer 3

I have successfully used Ruby / Rails ' ActiveRecord Migrations for Oracle, SQL Server 2005, MySQL and SQLite. I think I may have been able to use it in Access as well, but it could be a bad memory. It also supports PostgreSQL and db2, which I know, either "out of the box" or by additional download. And you can always write your own adapter if you want something more exotic and want to create a truly self-sufficient DIY project ...

It works very well, but you must admit that this is a concept that will limit your access to the platform features. Not only with AR, but with - in all likelihood - any cross-platform tool that is not worth the bazillions: for example, what will you do if your target platform does not support triggers? Or stored procedures? (E.g. MySQL 4.0 or SQLite). Any cross-platform system must deal with such problems (I gained the hatred of Crystal Reports life, for example, after struggling with a version that tried - disastrously - to use an external Oracle statement in a SQL Server query).

If you stick to tables, indexes, and simpler constraints, I would expect many different platforms to be available to you. There is an argument in favor of the fact that you should usually look for something else to go outside of your database. I will not go further than here - this is a few religious debates ...

Answer 4

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

Answer 5

Is it a required feature that your application is fully aggregated in the database? It looks like you need to work hard to maintain this database. In addition, your data level will be incredibly complex because there are slight differences in each DBMS.

Answer 6

You may be able to get an abstract representation of tables and their relationships, but how do you feel about views, stored procedures, triggers, etc.?

Answer 7

DDLUtils seems to be the right choice. It works very well, even if the project is currently inactive.

The coding is great, and I had no problems with it (work on MySQL, Oracle, H2DB, ...).

Indeed, for the built-in command line, now it’s gone, but you can run ant tasks from the command line or write your own command-line shell (this is really not that difficult ... I did it on my side, actually).

Answer 8

I did some basic experimentation with DDLUtils. I tried Mysql, MSSql and oracle, it works fine. checked for table creation, constraints and indexes. Failure to use it because it is inactive and blocker errors have been open since ancient times.