Liquibase preconditions: How do I check for a column being non-nullable?

liquibase, mysql, preconditions

Solution

Can be done with sqlCheck.

For MySql

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="NO">
        SELECT is_Nullable
        FROM  INFORMATION_SCHEMA.COLUMNS 
        WHERE table_name='<table_name>' 
        AND column_name='<column_name>' 
    </sqlCheck>   
</preConditions>

For Oracle:

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="N">
        SELECT Nullable
        FROM user_tab_columns
        WHERE table_name = '<table_name>'
        AND column_name = '<column_name>'
    </sqlCheck>
</preConditions>

For SQL Server:

<preConditions onFail="MARK_RAN" onError="CONTINUE">
    <sqlCheck expectedResult="0">
      SELECT is_nullable 
      FROM sys.columns
      WHERE  object_id = OBJECT_ID('<table_name>')  
      AND name = '<column_name>' 
    </sqlCheck>
</preConditions>

Problem

I have a db upgrade script to remove the non-null constraint on a column. I want to do a precondition check, and call `ALTER TABLE` only when it is non-null. The master.xml script is a progressive one where I keep adding scripts and the entire thing runs everytime. After the first time my Alter Table script has run, I do not want it to be run again. Couldn't find a predefined precondition for this, and could not write an sqlcheck either.

Original source