Skip to content

Instantly share code, notes, and snippets.

@wilmoore
Forked from tlberglund/gist:727521
Created February 5, 2011 05:57
Show Gist options
  • Save wilmoore/812253 to your computer and use it in GitHub Desktop.
Save wilmoore/812253 to your computer and use it in GitHub Desktop.
Sample Liquibase Schema Refactorings
<?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>
<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>
<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>
<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>
<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>
<dropTable tableName="jobLogType" />
<ext:modifyColumn tableName="jobQueue">
<column name="status"
type="ENUM('CANCELLED', 'COMPLETED', 'NEW', 'READY', 'SKIPPED', 'SUSPENDED')"
defaultValue="NEW">
<constraints nullable="false"/>
</column>
</ext:modifyColumn>
<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"
/>
<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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment