Sample Liquibase Schema Refactorings

changelog-template.xml

<?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"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

</databaseChangeLog>

create-table.xml

<createTable tableName="contact">
    <column name="id" type="bigint" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>
    <column name="first_name" type="varchar(50)" />
    <column name="middle_initial" type="varchar(5)" />
    <column name="last_name" type="varchar(50)" />
    <column name="gender" type="varchar(50)" />
    <column name="email_address" type="varchar(100)" />
    <column name="address_1" type="varchar(50)" />
    <column name="city" type="varchar(50)" />
    <column name="state_province" type="varchar(50)" />
    <column name="postal_code" type="varchar(50)" />
    <column name="country" type="varchar(50)" />
    <column name="birthday" type="datetime" />
    <column name="occupation" type="varchar(50)" />
    <column name="national_id" type="varchar(50)" />
</createTable>
<createTable tableName="security_info">
    <column name="id" type="bigint" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>
    <column name="password" type="varchar(50)" />
    <column name="mothers-maiden-name" type="varchar(50)" />
</createTable>
<createTable tableName="credit_card">
    <column name="id" type="bigint" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>
    <column name="contact_id" type="bigint" />
    <column name="card_type" type="varchar(15)" />
    <column name="card_number" type="varchar(25)" />
    <column name="expiration" type="datetime" />
    <column name="cvv" type="varchar(3)" />
</createTable>

<sql>
    INSERT INTO contact (first_name, middle_initial, last_name, gender, email_address, address_1, city, state_province, postal_code, country, birthday, occupation, national_id)
    SELECT 
        givenname AS first_name, 
        middleinitial AS middle_initial, 
        surname AS last_name, 
        gender AS gender, 
        emailaddress AS email_address, 
        streetaddress AS address_1, 
        city AS city, 
        state AS state_province, 
        zipcode AS postal_code, 
        country AS country, 
        birthday AS birthday, 
        occupation AS occupation, 
        nationalid AS national_id
        FROM contact_ball_of_mud;
</sql>

create_organization.xml

<changeset id="20111117-0832__create_organization" author="wilmoore">
    <comment>create the organization table</comment>

    <createtable tablename="organization">
        <column name="partyId" type="BIGINT UNSIGNED">
            <constraints nullable="false">
        </constraints></column>
    </createtable>

    <addforeignkeyconstraint constraintname="fk___organization_partyId__party__partyId" basetablename="organization" basecolumnnames="partyId" referencedtablename="party" referencedcolumnnames="partyId">
</addforeignkeyconstraint></changeset>

create_party.xml

<changeset id="20111117-0831__create_party" author="wilmoore">
    <comment>create the party table</comment>
    <createtable tablename="party">
        <column name="partyId" type="SERIAL" autoincrement="true">
            <constraints primarykey="true" nullable="false">
        </constraints></column>
    </createtable>
</changeset>

create_person_sql.xml

<changeset id="20111117-0835__create_person" author="wilmoore">
    <comment>create the person table</comment>

    <sql>
    CREATE TABLE IF NOT EXISTS `person` (
    `partyId`   BIGINT(20) UNSIGNED NOT NULL,

    KEY `fk___person_partyId__party__partyId` (`partyId`),
    CONSTRAINT `fk___person_partyId__party__partyId`
        FOREIGN KEY (`partyId`)
        REFERENCES `party` (`partyId`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    </sql>

    <rollback>
        <sql>DROP TABLE `person`;</sql>
    </rollback>
</changeset>

drop-table.xml

<dropTable tableName="jobLogType" />

modify-column.xml

<ext:modifyColumn tableName="jobQueue">
    <column name="status"
           type="ENUM('CANCELLED', 'COMPLETED', 'NEW', 'READY', 'SKIPPED', 'SUSPENDED')"
           defaultValue="NEW">
        <constraints nullable="false"/>
    </column>
</ext:modifyColumn>

redefine-or-drop-and-create-table.xml

<dropTable tableName="jobLog" />

<createTable tableName="jobLog">
    <column name="jobLogID" type="SERIAL" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>

    <column name="jobID" type="BIGINT UNSIGNED">
        <constraints nullable="false" />
    </column>

    <column name="jobHistoryID" type="BIGINT UNSIGNED">
        <constraints nullable="true" />
    </column>

    <column name="level" type="enum('EMERG','ALERT','CRIT','ERR','WARN','NOTICE','INFO','DEBUG')" />

    <column name="message" type="varchar(200)">
        <constraints nullable="true" />
    </column>

    <column name="dateTimeCreated" type="DATETIME">
        <constraints nullable="false" />
    </column>
</createTable>

<addForeignKeyConstraint    baseTableName="jobLog"
                            baseColumnNames="jobID"
                            constraintName="FK_jobLog_jobID_job_jobID"
                            referencedTableName="job"
                            referencedColumnNames="jobID"
                            onDelete="CASCADE"
/>
<addForeignKeyConstraint    baseTableName="jobLog"
                            baseColumnNames="jobHistoryID"
                            constraintName="FK_jobLog_jobHistoryID_jobHistory_jobHistoryID"
                            referencedTableName="jobHistory"
                            referencedColumnNames="jobHistoryID"
                            onDelete="SET NULL"
/>

seed-data.xml

<changeset id="20111117-0832__seed-party-organization" context="seed" author="wilmoore">
    <sql>
    -- seed party and organization tables
    INSERT INTO `party` (`partyId`) VALUES (1);
    INSERT INTO `organization` (`partyId`) VALUES (1);
    </sql>
</changeset>