Looking for feedback on cross-database support

General Discussion
1

I’m trying to better understand how the Liquibase cross-database support is used. If you are using the same changelog file across multiple databases, can you comment on what has worked well and what could be improved?

Has it helped? Or been more trouble than it’s worth?

Nathan

2

Hi Nathan,

I can share my recent experience for using changlog file generated from Oracle and used it for HANA database, the following adjustments I had to make to use the same changlog successfullly,  in the end they seem minor changes and worth time spent as the changes applied to HANA went as expected.

Tables :

  • removed default value “sysdate” from the column definition

  • changed data type: binary_double to double

  • changed data type: Long raw to blob

DATA file :

  • No change required, all data loads successfully including CLOB fields

Sequences:

Sequence creation worked after removing clauses : cache and order

Primary key constraints:

Removed the following pattern as HANA does not create physical index for primary key

  • <createIndex.*?/createIndex>

  • forIndexName.*?PK1"

Unique key constraints:

Removed the following pattern as HANA does not create physical index for unique key

  • <createIndex.*?/createIndex>

  • forIndexName.*?UK1"

     

Foreign Key constraints:

  • corrected datatype from CHAR to VARCHAR  (HANA does not support constraints on CHAR columns)

   

Indexes

  • corrected the following line as HANA does not support “SYS_OP_C2C”

     

In addition to above, we do have few procedures to be created on HANA.   Since they are less, i have created xml files to be loaded into HANA, however, did not succeed so far as “dbms=hana” does not seem to work well with HANA and procedure is not able to be created.   

Let me know if you need more information.

Thanks & regards,

Viren

3

We manage changelogs that are targeted against Oracle and Postgres and sometimes SQL Server. 

One thing that we had to take care of is Oracle’s “char semantic”. In order to ensure that VARCHAR(30) is indeed created as VARCHAR(30 Char) in Oracle we put the following at the start of each changelog:


 
 
 

 
 
 
 
 
   
     
       
     
   
 

The rules when Liquibase quotes object names are also not helpful. Consider e.g. the following:

   

For Oracle Liquibase does not quote the table name and creates a table named FOOBAR. For Postgres Liquibase quotes the table name and thus a table with a quoted identifier is created “FooBar”. For SQL Server the name is also not quoted. We finally created our own implementation of PostgresDatabase that only quotes names if they contain special characters or if they are reserved words. If you are interested, I am willing to share this implementation. I have seen some questions on Stackoverflow regarding this as well, so we are not the only ones.

Not really a DBMS topic, but: we run changelogs from the commandline and through Maven alike (depending on the developer and the environment). In that case the “current directory” is different which means that Liquibase will put a different value into the column “filename” of the databasechangelog table. Which in turn means that a second run of the changelog in a different environment will fail because the filename is part of the primary key of that table and Liquibase doesn’t consider the changeSets as executed.

We wind up using “logicalFilePath” in all our changeLogs that only contains the filename, not the path and thus always uses the same value regardless of the environment. That however is error prone as it requires manual steps when creating new changelogs.

From my perspective, this (use the file name, not the full path) should be the default behavior of Liquibase.