Our team has the following problem. We would like to start using Liquibase and start developing with local databases. Our existing application uses a oracle database and the local databases will be postgresql.
We have the following plan:
- use Liquibase to generate an initial changelog from the oracle database
- use the initial changelog to populate the postgresql databases.
Question 1: Is this a sound plan, or are we missing something?
The changelog generated at step 1 contains type information that are not compatible for step 2. For example there is a column tag with type=“VARCHAR2(255 BYTE)”. The SQL then contains this literal type information.
Question 2: Why are these non-generic types generated?
Question 2a: Could this be a jdbc driver problem?
Any help would be appreciated.
Your plan to populate postgres should work fine. There is a diffTypes=DATA param you would need to pass to include any data you have as well. Also, the diff tool doesn’t always convert everything exactly as you may want, so you will want to read through the changelog to see if there are any changes you want to make before running it on postgresuch as your 255 BYTE example.
What to output in the diff result from a given database snapshot is a balancing act. We want to be generic to support use cases like yours, but you also need to make sure that it is capturing all the nuances of the data type on the orignal datatype (like 255 BYTE vs 255 CHAR). IIRC, your oracle database is set up to use unicode and/or nvarchar and so we need to output that it is 255 bytes instead of the default (255) that would be characters.
What you will probably need to do is do a (possibly automated) custom post-processing where you look for datatypes of varchar((\d+) bytes) and replace it with varchar(\1) or something similar.
Going forward, there are ways to make sure changeSets you make are compatible with both by using generic types, modifySql, custom SqlGenerators, etc.
Nathan
Thank you for answering my questions. I have little experience with Liquibase, so I wanted to make sure that I have a correct use case in mind.
I understand that their is a fine line between a to generic and a to specific generated change-log. Post-processing the change-log is no problem. Could the modifySql tag be used in this case to change varchar(255 bytes) to varchar(255) instead of the use of regular expressions?
Daan
Yes, you can use the literal value as well. I didn’t know how generic your modifySql would need to be.
Nathan
I have chosen to post-process the change-log without resorting to the modifySql tag. When I am finished I will post my results here to share my experiences.
In this reply I would like to share my experiences with post-processing the change-log file. First of all, since version Liquibase 2.0.0 was released a short while ago, we started using the new version.
We had to resort to the modifySql tag because the generated sql was incorrect formed. The type information in the change-log was i.e. “TIMESTAMP(6)”. The generated sql was “TIMESTAMP WITH TIME ZONE (6)”. The correct postgres syntax is “TIMESTAMP (6) WITH TIME ZONE”. (see http://www.postgresql.org/docs/8.0/static/datatype-datetime.html) In our use case the time zone information was not necessary. We ended up with the modifySql replacing “TIMESTAMP WITH TIME ZONE” with “TIMESTAMP”.
The following type changes were made by transforming the generated change-log file by a post-processing script.
]
| Generated Type |
Converted Type |
| VARCHAR2(255 BYTE) |
VARCHAR(255) |
| NUMBER |
NUMERIC |
| FLOAT(126) |
FLOAT(53) |
| 20-aug-2009 |
'20-aug-2009' |
| SYSTEMTIMESTAMP |
NOW() |
Last but not least their were some idiosyncrasies in our schema which are too specific to be of any interest.
I would like to add on piece of information about the conversion. When we started to fill the database we discovered that hibernate expects the boolean datatype for postgres databases. An Oracle does not know a boolean data type. The hibernate maps boolean to NUMERIC(1,0) for oracle database. So we needed to chance this to boolean in our changelog.
Hope this will help somebody.
Kind regards.
