Liquibase

From Chorke Wiki
Revision as of 19:07, 9 March 2022 by Shahed (talk | contribs) (→‎Notes)
Jump to navigation Jump to search

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
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
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 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

Retrieved from "https://cdn.chorke.org/weke/index.php?title=Liquibase&oldid=7311"