How to maintain SQL scripts when developing an application working against many databases

Imagine an application which is supposed to work with different database vendors. As we all know the syntax for SQLs (especially DDL) is not portable. How do you deal with maintaing the SQL scripts?

Until now I see three options:

• to store SQLs in format of one of the databases and have a tool which automatically converts from one sy开发者_运维知识库ntax do another (do you know such tools?)

• to store SQLs in some artificial language and a have a tool which is able to generate vendor-specific SQLs on demand (any recommendation here?)

• to store SQLs in many database formats neglecting the redundancy (this is the worst one, isn't it?)

  Do you recommend any of them? Do you have a better idea?

The development environment tries to follow the continuous integration principles, so automation is a key feature here.

Have a look at Liquibase (that's essentially your second item on the list)

http://www.liquibase.org

It's not perfect (e.g. it does not support check constraints) but it is quite useful

This video shows a solution using the Subsonic project http://subsonicproject.com/docs/Using_SimpleRepository and its data migration capabilities. The strategy is to use a general language and apply it to different databases.

Hope this is what you were looking for

Use some kind of ORM framework with schema generation capability.