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
data	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
derby	Apache Derby	derby	jdbc:derby	No Issues
h2	H2	h2	jdbc:h2:file	No Issues
hsqldb	HSQL	hsqldb	jdbc:hsqldb:file	No Issues
mysql	MySQL	mysql	jdbc:mysql	No Issues
mariadb	MariaDB	mariadb	jdbc:mariadb	No Issues
oracle	Oracle	oracle	jdbc:oracle:thin	11g driver for diff with AL32UTF8 or AL16UTF16
postgresql	PostgreSQL	postgresql	jdbc:postgresql	8.2+ for drop all database objects
sqlserver	Sql Server	sqlserver	jdbc:sqlserver	No Issues
sqlite	SQLite	sqlite	jdbc:sqlite	No Issues
default	H2	h2	jdbc:h2:file	No Issues

Context

Porfile	Context	Notes
staging	Staging	Development Environment
uat	User Acceptance Test	UAT Environment
production	Production	Production Environment
default	Staging	Development Environment

Command

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

Types

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

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>staging,uat,production</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
Map a JSON column with H2 & Spring JPA Database release using Liquibase Liquibase configuration options Liquibase types vs. SQL & Java Enum Type » H2 Database Enum Type » PostgreSQL Enum Type » Oracle Enum Type » MySQL