How to conditionally include/apply a whole changelog?

General Discussion
1

Hi everyone,

I need to deploy Liquibase for an existing database and used generate-changelog to generate the necessary file. Afterwards one can use changelog-sync to mark that changelog as executed and continue with other changesets. The problem I’m facing is that the generated changelog works for this one RDBMS only, I need to adopt it to support other RDBMS, and that for various organisational reasons I’m unable to issue changelog-sync manually. Instead, I have a Spring Boot application which applies Liquibase migrations automatically during startup and I wasn’t easily able to see how to customize that process to support changelog-sync.

The easier solution seemed to me to simply make use of pre-conditions, because for individual changesets they support a MARK_RUN. So I simply check some condition in those pre-conditions and the default migration process is handling everything else. But I have a lot of individual changesets and copy&pasting the same pre-condition is error-prone especially if it needs to be changed at some point.

So, what is the supported way to check a similar pre-condition on the level of the changelog and mark ALL contained changesets as run?

From what I’ve found so far, at the level of the changelog, I only can choose between aborting or continueing the current changelog. But the former doesn’t mark the changelog as ran, so execution repeats, and the latter breaks the whole process because of the wrong changesets being executed. Would be easy if MARK_RUN could simply be used at the changelog level as well and would be inherited to the changesets or something like that.

Can one workaround the limitations using a custom pre-condition?

Or is there some label one can calculate during app-startup and Liquibases uses that afterwards?

Thanks for your ideas!

2

Didn’t try yet, but looks like an interesting idea:

Start by running the updateSQL command against a clean database. This will generate all the SQL that would be applied to a database to deploy all the changesets. In that SQL file that is generated will be a set of insert statements to the DATABASECHANGELOG table.

Extract all those insert statements into a single file. Call it something like “MarkAllMyTableRelatedAsRan.sql”, and then in your first changeset use the <sqlFile> tag to run those inserts, using the precondition.

3

I was going to suggest using sqlFile also, but there are potential problems that can arise when you have multiple DDL statement inside of one changeset, depending on your DBMS platform, and whether it has transactional DDL or not. For example: Oracle does not, PostgreSQL does.

4

I’ve gave that approach a try and the problem is with the file-global pre-condition and its HALT value. That doesn’t only exit the current changelog, but ALL subsequent ones and is not what I need. I need to ignore the current one changelog out of multiples only and the docs read a little bit that way.

If that would work, one could create one changelog checking some criteria if to apply or not and a different changelog simply looking for status in DATABASECHANGELOG. If the first is not applied, that status in DATABASECHANGELOG is missing and one can simply add it using INSERT or loading CSV or whatever.

<databaseChangeLog [...]>
    <include file="db/migration/changelog-0.9.7-db2i-01-apply_if_empty.xml"     />
    <include file="db/migration/changelog-0.9.7-db2i-02-apply_if_non_empty.xml" />
</databaseChangeLog>

<databaseChangeLog [...]
                   logicalFilePath="db/migration/changelog-0.9.7-db2i-01-apply_if_empty.xml">

    <preConditions onFail="HALT" onFailMessage="DB2i already set-up -> OK">
        <dbms type="db2i" />
        <not><tableExists tableName="[...]" /></not>
    </preConditions>

    <changeSet author="[...]" id="[...]">
    </changeSet
    [...]
</changeLog>

<databaseChangeLog [...]
                   logicalFilePath="db/migration/changelog-0.9.7-db2i-02-apply_if_non_empty.xml">

    <changeSet author="[...]" id="[...]" dbms="db2i">
        <preConditions onFail="MARK_RAN" onFailMessage="DB2i populated by Liquibase -> OK">
            <rowCount tableName="DATABASECHANGELOG" expectedRows="0" />
        </preConditions>

        <loadData
                tableName="DATABASECHANGELOG"
                file="db/migration/0.9.7/DATABASECHANGELOG.csv"
                quotchar='"'
                separator=",">
            <column name="ID"               type="STRING"   />
            <column name="AUTHOR"           type="STRING"   />
            <column name="FILENAME"         type="STRING"   />
            <column name="DATEEXECUTED"     type="DATE"     />
            <column name="ORDEREXECUTED"    type="NUMERIC"  />
            <column name="EXECTYPE"         type="STRING"   />
            <column name="MD5SUM"           type="STRING"   />
            <column name="DESCRIPTION"      type="STRING"   />
            <column name="COMMENTS"         type="STRING"   />
            <column name="TAG"              type="STRING"   />
            <column name="LIQUIBASE"        type="STRING"   />
            <column name="CONTEXTS"         type="STRING"   />
            <column name="LABELS"           type="STRING"   />
            <column name="DEPLOYMENT_ID"    type="STRING"   />
        </loadData>
    </changeSet>
</databaseChangeLog>

So I still need a solution to skip the one current changelog only.

5

The topic is well-known already:

Precondition in databaseChangeLog should allow to skip the whole file · Issue #1200 · liquibase/liquibase (github.com)

[CORE-1134] Support changelog-level preconditions that just apply to it’s changelog - liquibase.jira.com