Database type differences

General Discussion
1

hi,
i’m using liquibase to create a diff of 2 postgres databases and generating a changelog.xml.
next step is to use this diff file to upgrade an existing oracle database instance to the desired state.
oracle and postgres are using different data types in some cases, so the import is going to fail with the generated postgres changelog.
i would like to specify something like typedef for database oracle and set eg.: in value “bigint” to output type “number”.
there is an old ticket showing that this should be implemented in liquibase:
https://liquibase.jira.com/browse/CORE-422
but i didn’t found the typedef in the liquibase xsd and it’s not working :wink:

question: am i missing something or using liquibase wrong and is there an option to use the typedef?
another option would be to replace the types in the changelog file before importing, but I would like to avoid that …

thx, br
jmu

2

Hi @jmu,

Welcome to the forum group, thanks for joining!

To answer your question, let me make sure I am understanding you properly, you want to be able to change certain keywords in a generated changelog that will make it compatible with the new target to be updated right?

As CORE-422 suggests, you can do token replacement and mapping using properties and tags (labels).
https://docs.liquibase.com/concepts/basic/changelog-parameters.html
https://docs.liquibase.com/concepts/advanced/labels.html

This is preferred to updating the xsd.

Does that help?

Ronak

3

Hi Ronak,

thanks for your reply … - to be honest - I’m not really sure :wink: - probably a short example helps:

diff between two postgres databases produces a changelog file containing:

…

<changeSet author="xy (generated)" id="1591082717861-15">
<modifyDataType columnName="VERSION" newDataType="BIGINT" tableName=DATA_TYPES"/>
</changeSet>

…
now i’d like to use this changelog file to update an existing postgresql database, but some types are not existing. So it is necessary to replace the value of newDataType=“BIGINT” to a valid postgresql datatype of “number19”, like:

…

<modifyDataType columnName="VERSION" newDataType="number(19)" tableName=DATA_TYPES"/>
…

so, if there is a possibility to add something like a type match - if dmbs=“oracle” then datatype BIGINT is modified to NUMBER, it would be possible to specify the datatypes and use the same changelog file for different dmbs eg. oracle and postgres.

Hope this clarifies my question :slight_smile:
BR
JMU

4

Hi @jmu,

Thanks for the clarification. I think the same thing could be accomplished using changelog parameters.

Here’s an example of the workflow.

  1. Generate the changelog

  2. Manually update the changelog with a search or sed/awk. changing all instances of say “BIGINT” with a variable name, like ${new.type}

  3. Then add another line defining ${new.type} in your changelog at the top like this: <property name="new.type" value="number(19)"/>

Your changelog should be up to date for the new platform, and future changes would just be an update to the property definition (step 3 above)

Hope this gets us closer to resolving :slight_smile:
Ronak

5

Hi @ronak,
ok, so I understand that there is no liquibase function for this task and a scripted replacement is necessary!

-> solved for me!

thank you again for answer!

BR JMU

1 Like
6

@jmu, happy to help.

Yes, I think of liquibase more like a ledger for db changes that are applied within a CICD pipeline. So just like a web server will already be created for the latest webapp deployment in a CICD pipeline, the target database is assumed to be there.