Database Migration with Liquibase using different DBMS

Question

Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a changeset because of the db specific sql and different column types like NUMBER <-> BIGINT or VARCHAR <-> VARCHAR2

Am I using these tools wrong? How to solve this type of problem?

Answer 1

Liquibase will attempt to convert standard types like "varchar", "int", "boolean", "datetime" to the correct datatype for the database. If you define a column as type="VARCHAR(100)", when you run against oracle it will generate SQL with VARCHAR2(100).

The mapping of standard types to database-specific types is not as well documented as it should be, unfortunately.

Alternately, if you can stick to SQL-standard data types, they are generally fairly cross-database.

When you need to force a particular type, you can use a changelog parameter like the example in http://www.liquibase.org/documentation/changelog_parameters.html

<column name="notes" type="${clob.type}"/>

and define clob.type per database:

<property name="clob.type" value="clob" dbms="oracle"/>
<property name="clob.type" value="longtext" dbms="mysql"/>

Answer 2

The best approach is to use changelog parameters and the dbms tag.

At the top of your changeset you can include:

<property name="autoIncrement" value="true" dbms="mysql"/>
<property name="autoIncrement" value="false" dbms="oracle"/>
<property name="chartype" value="VARCHAR" dbms="mysql"/>
<property name="chartype" value="VARCHAR2" dbms="oracle"/>

Then you can have changeSets like this: Copy code

<changeSet id="1" author="a">
    <createTable name="x">
        <column name="id" datatype="int" autoincrement="${autoIncrement}"/>
        <column name="name" datatype="${chartype}(255)" />
        ....
</changeSet>
<changeSet id="2" author="a" dbms="oracle">
    <createSequence name="seq_x"/>
</changeSet>

EDIT source: http://forum.liquibase.org/topic/auto-increment-vs-sequences-using-liquibase-with-oracle-and-mysql