A simple liquibase sample #db

(README).md

A simple liquibase sample

liquibase (http://www.liquibase.org/ ) is a database Refactor tool. This gist is a simple liquibase sample.

JDBC Driver

1. Download your Database JDBC Driver. (e.g. SQLite3 JDBC http://www.liquibase.org/sqlite.html )
2. Add the driver name and .jar file to liquibase command line. (e.g. SQLite3: liquibase --classpath=sqlitejdbc-v<version>.jar --driver=org.sqlite.JDBC )

Database Schema and change log

• This simple sample creates 2 tables: MACHINE & MACHINE_STATUS.
• The change log file is db.change-master.xml
• MACHINE Table

+------------------------------------------------+
| ID            [ PK, INTEGER, auto-increments ] |
|================================================|
| MACHINE_NAME  [ varchar(255), UNIQUE, INDEX ]  |
| HOST_NAME     [ varchar(255) ]                 |
+------------------------------------------------+

• MACHINE_STATUS Table

+---------------------------------+
| MACHINE_ID    [ INTEGER ]       |
|=================================|
| STATUS        [ varchar(255) ]  |
+---------------------------------+

Liquibase command lines (SQLite3 DB client )

• Liquibase command lines documentation: http://www.liquibase.org/documentation/command_line.html
• migration
  liquibase --classpath=sqlite-jdbc-3.8.5-pre1.jar --driver=org.sqlite.JDBC --url="jdbc:sqlite:sample.sqlite" migrate
• rollback to change set 1
  liquibase --classpath=sqlite-jdbc-3.8.5-pre1.jar --driver=org.sqlite.JDBC --url="jdbc:sqlite:sample.sqlite" rollbackCount 1

db.change-master.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- db change log -->
<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.1.xsd">

<!-- change set 1 -->
<changeSet id="1" author="xiongjia">
  <!-- MACHINE Table -->
  <createTable tableName="MACHINE">
    <column name="ID" type="int">
      <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="MACHINE_NAME" type="varchar(255)">
      <constraints unique="true" nullable="false"/>
    </column>
    <column name="HOST_NAME" type="varchar(255)"/>
  </createTable>
  <createIndex indexName="IDX_MACHINE"
    tableName="MACHINE" unique="true">
    <column name="MACHINE_NAME" type="varchar(255)"/>
  </createIndex>
  <rollback>
    <dropIndex indexName="IDX_MACHINE" tableName="MACHINE_STATUS"/>
    <dropTable tableName="MACHINE"/>
  </rollback>
</changeSet>

<!-- change set 2 -->
<changeSet id="2" author="xiongjia">
  <!-- MACHINE_STATUS Table -->
  <createTable tableName="MACHINE_STATUS">
    <column name="MACHINE_ID" type="int" />
    <column name="STATUS" type="varchar(255)" />
  </createTable>
  <rollback>
    <dropTable tableName="MACHINE_STATUS"/>
  </rollback>
</changeSet>

</databaseChangeLog>