CODEWITHSUNDEEP
liquibase
user9524367
user9524367

Reputation: 33

How to add condition to changeSet

I created database ChangeLog for liquibase and I have "CREATE TABLE" query. I want to add condition to column "STATUS". 

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">


    <changeSet id="1" author="xxx">
        <comment>Create Asset table</comment>
        <sql>
            CREATE TABLE `TASK`(
            `TASK_ID` bigint(20) NOT NULL,
            `STATUS` VARCHAR(10), //(where status not equal to "pending")
            `FILE_ID` varchar(100),
            PRIMARY KEY (`DOC_ID`)
            )ENGINE=InnoDB DEFAULT CHARSET=utf8;
        </sql>
    </changeSet>
</databaseChangeLog>

Upvotes: 3

Views: 9718

Answers (1)

htshame
htshame

Reputation: 7330

If you want to update the columns datatype depending on certain conditions, you can use <preConditions> with <sqlCheck>, and then use <modifyDataType>.

So supposing that there's already a table with name TASK and it contains a column named STATUS, you can modify it's datatype as such:

<changeSet id="1" author="xxx">
    <comment>Update Asset table</comment>
    <preConditions onFail="MARK_RAN">
        <and>
            <columnExists tableName="TASK" columnName="STATUS"/>
            <sqlCheck expectedResult="0"> SELECT COUNT(*) FROM TASK WHERE STATUS = "pending";</sqlCheck>
        </and>
    </preConditions>
    <modifyDataType tableName="TASK" columnName="STATUS" newDataType="VARCHAR(10)"/>
</changeSet>

Or perhaps you'd like another onFail-behaviour. Check out this link

HALT - Immediately halt execution of entire change log [default]

CONTINUE - Skip over change set. Execution of change set will be attempted again on the next update. Continue with change log.

MARK_RAN - Skip over change set, but mark it as ran. Continue with change log

WARN - Output warning and continue executing change set as normal.

Upvotes: 3

Related Questions