We are trying to take an existing app and integrate liquibase. Our db is currently created by hibernate annotations. We need to support hsqldb, h2, msql, sqlserver, and oracle. The approach we have attempted to take is to first let hibernate create a db in one of the vendor specific databases. Then we use liquibase to generate a changelog.xml. Then we wipe out the db and let liquibase recreate it using the just created changelog.xml.
We have found the following:
the changelog.xml is not portable across any of the databases. In some cases minor tweaks are needed and in others big tweaks are needed. Seems very difficult to get a changelog.xml that is portable across all vendors’ dbs.
hibernate validation fails when letting liquibase create the db first and then letting hibernate validate the db even though the same vendors db is used to both generate the changelog.xml and recreate the database using the changelog.xml. We can turn off hibernate validation and the app seems to work, but it is odd that liquibase generates a db with types that are dissimilar enough to cause hibernate validation to fail.
What are other peoples experience with the portability of changelogs across database vendors? Are we expecting too much from liquibase?
At Jenzabar we support H2, PostgreSQL, Informix and SQL Server using the same changelogs. There are only a couple of database-specific changesets we’ve had to author.
(All of this may change once we start working on Oracle.)
A couple things we’ve found:
Informix is the last of the databases to have a 255 character limit on VARCHAR. So we wrote an extension that silently uses LVARCHAR under the covers if necessary.
Do not rely on automatically-generated constraint names. Always add all constraints after the fact and name them.
SQL Server requires you to drop all constraints before dropping a column. (See my second point. :-))
Informix alterations are atomic, not incremental, so using things like modifyDataType will simply blow away all other attributes that may have accrued to the column in question.
Always always always always always use the logicalFilePath attribute, both in its documented location (the databaseChangeLog element) and its undocumented location (the changeSet element). Otherwise moving changesets around between files is impossibly nasty.
Don't let your JPA provider generate your database. Ever. Do force it to validate it if it supports it (OpenJPA does).
When modifying Liquibase, note that database-specific behavior is sprayed all over the code base, not just in database-specific classes, but in hundreds of "if the current Database object is an instance ofInformixDatabase..." clauses.
Many aspects of the Liquibase documentation are incorrect or missing. For example, the --driverClassargument to the command line is not required, and the logicalFilePath attribute may (should) be specified on changeSet elements.
I think that's about it; message me if you need more information.
