Liquibase Bootstrap

db-changelog.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"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">

<!--
  <include file=".+(.sql|.xml)" />
-->

</databaseChangeLog>

liquibase.properties

#liquibase.properties
driver:        org.postgresql.Driver
classpath:     postgresql-9.2-1002.jdbc3.jar
url:           jdbc:postgresql://localhost:5432/timetracker
changeLogFile: db-changelog.xml

resources.md

Books

• SQL Pocket Guide
• SQL Cookbook
• SQL Antipatterns

Helpful SQL

Review list of change-sets applied to current database:

SELECT * FROM `DATABASECHANGELOG`;

Remove the MD5SUM from a change-log record in order to force that change-log to be run again:

    UPDATE
        `DATABASECHANGELOG` SET MD5SUM = NULL
    WHERE
        ID = 'some-ID-string-that-should-exist-in-your-DATABASECHANGELOG-table';

Other Runners

• phing

Documentation Links

• liquibase.properties
• Contexts
• Schema evolution
• Formatted SQL Changelogs
• Liquibase Formatted SQL - how to include other changeset file
• Liquibase Best Practices
• Rolling Back ChangeSets
• Commandline Options

Resources

• What are the alternatives to LiquiBase?
• Active Record Standalone Migrations
• DBDeploy Article listing of alternatives
• Database Version Control
• Liquibase Quickstart

story-001.sql

--liquibase formatted sql

--changeset wilmoore:create-work-table
CREATE TABLE work (
  id          SERIAL,
  hours       DECIMAL(5,2)  NOT NULL  DEFAULT 0,
  date        DATE          NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  archived    BOOLEAN       NOT NULL  DEFAULT false,
  description TEXT          NULL,

  PRIMARY KEY (id)
);

--rollback drop table work;

--changeset wilmoore:work-id-to-uuid
ALTER TABLE work
  DROP COLUMN id,
  ADD COLUMN id UUID NOT NULL,
  ADD PRIMARY KEY (id);

--changeset wilmoore:add-demo-data context:demo
INSERT INTO work
  (id, hours, date, archived, description)
VALUES
  (
    '14ef9300-a133-484b-91e8-2f47c89dd0c3',
    3,
    '2003-02-03',
    false,
    'de-linting of client JS/CSS and added a JS module system'
  );

--changeset wilmoore:add-QA-data context:qa
INSERT INTO work
  (id, hours, date, description)
VALUES
  (
    '5f28486a-8cf7-49bf-acd9-15d334b494f0',
    5,
    '2012-09-01',
    'added static analysis to codebase'
  );