Liquibase

Path Settings

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

Configuration

mkdir -p $HOME/.chorke/academia/etc/liquibase/init
cp ./src/test/resources/liquibase/*.properties $HOME/.chorke/academia/etc/liquibase/init
cd $HOME/.chorke/academia/etc/liquibase/init
explorer .

Migration Commands

Command Syntax

This syntax developed by considering four types of profiles. Please find the details in Profiles. Where default profiles are blank. Need not to mention in Maven build.

# mvn clean install -P[[[diff]database,]context,]command
# mvn clean install -P[[[dat,]mys,]dev,]log

mvn clean install -Pmys,dev,log      # mysql , development
mvn clean install -Pora,pro,log      # oracle, production
mvn clean install -Pdev,log          # h2    , development
mvn clean install -Plog              # h2    , development

Generate Change Log

# exclude data mvn clean install -Pmys,dev,log # mysql , development mvn clean install -Pora,pro,log # oracle, production mvn clean install -Pdev,log # h2 , development mvn clean install -Plog # h2 , development

# include data mvn clean install -Pdat,mys,dev,log # mysql , development mvn clean install -Pdat,ora,pro,log # oracle, production mvn clean install -Pdat,dev,log # h2 , development mvn clean install -Pdat,log # h2 , development

Differential Change Log

# exclude data mvn clean install -Pmys,dev,dif # mysql , development mvn clean install -Pora,pro,dif # oracle, production mvn clean install -Pdev,dif # h2 , development mvn clean install -Pdif # h2 , development

# include data mvn clean install -Pdat,mys,dev,dif # mysql , development mvn clean install -Pdat,ora,pro,dif # oracle, production mvn clean install -Pdat,dev,dif # h2 , development mvn clean install -Pdat,dif # h2 , development

Synchronize Change Log

mvn clean install -Pmys,dev,syn                        # mysql , development
mvn clean install -Pora,pro,syn                        # oracle, production
mvn clean install -Pdev,syn                            # h2    , development
mvn clean install -Psyn                                # h2    , development

Migrate/Update Database

mvn clean install -Pmys,dev,upd                        # mysql , development
mvn clean install -Pora,pro,upd                        # oracle, production
mvn clean install -Pdev,upd                            # h2    , development
mvn clean install -Pupd                                # h2    , development

Rollback Change Log

mvn clean install -Pmys,dev,rol -Dchorke.tag=20131216  # mysql , development
mvn clean install -Pora,pro,rol -Dchorke.tag=20131216  # oracle, production
mvn clean install -Pdev,rol -Dchorke.tag=20131216      # h2    , development
mvn clean install -Prol -Dchorke.tag=20131216          # h2    , development

Drop All/Clean Database

mvn clean install -Pmys,dev,del                        # mysql , development
mvn clean install -Pora,pro,del                        # oracle, production
mvn clean install -Pdev,del                            # h2    , development
mvn clean install -Pdel                                # h2    , development

Profiles

Four types of profiles are considerate for Maven build. Please find those profiles details are as respectively as below:

1. Diff (dat, default)
2. Database (der, h2d, hsq, mys, ora, pos, default)
3. Context (dev, dmo, qac, uat, pro, default)
4. Command (Change Log(log), Different(dif), Synchronise(syn), Update(upd), Clear Checksum(cle), Rollback(rol), Empty DB(del))
5. labels (ddl,dml)
   1. DDL:
      1. Operations: ddl, create(new), alter(alt), drop(dro)
      2. Constraints: Foreign(fkc), Not Null(nnc), Primary(pkc), Unique(ukc)
      3. Objects: table(tbl), sequence(sqn), column(col), index(idx), view(vue), function(fun), procedure(pro)
   2. DML: dml, csv, insert(ins), update(upd), delete(del)

Where default profiles are blank. Need not to mention in Maven build.

Diff

Porfile	Note	Diff Types
dat	Include Data	tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data
default	Exclude Data	tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints

Database

Porfile	Database	Type	JDBC	Notes
der	Apache Derby	derby	jdbc:derby	No Issues
h2d	H2	h2	jdbc:h2:file	No Issues
hsq	HSQL	hsqldb	jdbc:hsqldb:file	No Issues
mys	MySQL	mysql	jdbc:mysql	No Issues
mar	MariaDB	mariadb	jdbc:mariadb	No Issues
ora	Oracle	oracle	jdbc:oracle:thin	11g driver for diff with AL32UTF8 or AL16UTF16
pos	PostgreSQL	postgresql	jdbc:postgresql	8.2+ for drop all database objects
sql	Sql Server	sqlserver	jdbc:sqlserver	No Issues
ite	SQLite	sqlite	jdbc:sqlite	No Issues
default	MySQL	mysql	jdbc:mysql	No Issues

Context

Porfile	Context	Notes
dev	Development (Staging)	Development Environment
dmo	Demo	Demo Environment
qac	Quality Assurance & Control	QAC Environment
uat	User Acceptance Test	UAT Environment
pro	Production	Production Environment
default	Development	Development Environment

Command

Porfile	Commands	Notes
log	generateChangeLog	Generate Change Log
dif	diff	Generate Database Diff Change Log
syn	changelogSync	Synchronize Database with Change Log
upd	update	Migration/Update Database
cle	clearCheckSums	Removes current checksums, on next run recomputed
rol	rollback	Rollback Database to the Date/Tag
del	dropAll	Empty/Clean Database

Types

Liquibase	SQL Server	Oracle	MySQL	PostgreSQL
bigint	bigint	number(38,0)	bigint	bigint/bigserial
blob	varbinary(max)	blob	blob	oid
boolean	bit	number(1)	bit	bit
char	char	char	char	character
clob	nvarchar(max)	clob	longtext	text
currency	money	number(15,2)	decimal	decimal
datetime	datetime	timestamp	timestamp	timestamp
date	date	date	date	date
decimal	decimal	decimal	decimal	decimal
double	float	float(24)	double	double precision
float	float	float	float	float
int	int	integer	int	integer/serial
mediumint	int	mediumint	mediumint	mediumint
nchar	nchar	nchar	nchar	nchar
nvarchar	nvarchar	nvarchar2	nvarchar	varchar
number	numeric	number	numeric	numeric
smallint	smallint	number(5)	smallint	smallint/smallserial
time	time	date	time	time
timestamp	datetime	timestamp	timestamp	timestamp
tinyint	tinyint	number(3)	tinyint	smallint
uuid	uniqueidentifier	raw(16)	char(36)	uuid
varchar	varchar	varchar2	varchar	varchar/character (varying)

Load Data Types
BLOB     , BOOLEAN , CLOB 
COMPUTED , DATE    , NUMERIC 
SEQUENCE , SKIP    , STRING
UUID

Notes

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <configuration>
        <changeSetAuthor>chorke</changeSetAuthor>
        <changeSetContext>dev,qac,uat,pro</changeSetContext>
        <dataDir>${project.build.directory}/${change.on}</dataDir>
        <changeLogFile>META-INF/migrations/db.changelog-master.xml</changeLogFile>
        <propertyFile>${user.home}/.chorke/academia/etc/liquibase/init/${chorke.env}.properties</propertyFile>
        <diffTypes>tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data</diffTypes>
        <outputChangeLogFile>${project.build.directory}/db.changelog-V${version.chorke.version}_${change.on}.xml</outputChangeLogFile>
        <diffIncludeObjects>table:T.*,</diffIncludeObjects>
        <contexts>${chorke.ctx}</contexts>
    </configuration>
    <executions>
        <execution>
            <phase>prepare-package</phase>
            <goals><goal>generateChangeLog</goal></goals>
        </execution>
    </executions>
</plugin>

Challenges

Database	Auto Increment	Sequence	Usable	Chorke
DB2/LUW	✓	✓	✓	✕
DB2/z	✓	✓	✓	✕
PostgreSQL	✓	✓	✓	✓
Derby	✕	✓	✓	✓
Firebird	✕	✓	✓	✕
H2	✓	✓	✓	✓
HyperSQL	✓	✓	✓	✓
INGRES	✓	✕	✕	✕
Informix	✓	✓	✓	✕
MariaDB	✓	✓	✓	✓
MySQL	✓	✕	✕	✕
Oracle	✕	✓	✓	✓
Sql Server	✕	✓	✓	✓
SQLite	✓	✕	✕	✕
Sybase	✓	✕	✕	✕
Sybase Anywhere	✓	✓	✓	✕

References

Database Independent Script in Liquibase Rename Liquibase changelog tables Liquibase Migration Example Java Persistence/Inheritance Liquibase Documentation Generate Change Log Liquibase Ant Tasks Diff Exclude Objects Diff Include Objects Epoch Converter	Liquibase 3.6.x data types mapping table Liquibase 3.3.x data types mapping table SQLite3 Doesn't Provide a Date Type Liquibase Supported Databases Convention for Database Tables JPA & SQLite3 - Wrong Date Liquibase Load Data Types MyBatis Locale JPA	Create Sequence Supported Database Auto Increment Supported Database Insert Date in SQLite through Java Understanding Contexts vs. Labels List all Liquibase SQL Types Java Date vs Calendar Liquibase Contexts Liquibase Labels MapStruct Lombok
Liquibase configuration options Liquibase types vs. SQL & Java