Liquibase: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
 
(29 intermediate revisions by the same user not shown)
Line 89: Line 89:
===Rollback Change Log===
===Rollback Change Log===
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
mvn clean install -Pmys,dev,rol -chorke.tag=20131216   # mysql , development
mvn clean install -Pmys,dev,rol -Dchorke.tag=20131216 # mysql , development
mvn clean install -Pora,pro,rol -chorke.tag=20131216   # oracle, production
mvn clean install -Pora,pro,rol -Dchorke.tag=20131216 # oracle, production
mvn clean install -Pdev,rol -chorke.tag=20131216       # h2    , development
mvn clean install -Pdev,rol -Dchorke.tag=20131216     # h2    , development
mvn clean install -Prol -chorke.tag=20131216           # h2    , development
mvn clean install -Prol -Dchorke.tag=20131216         # h2    , development
</syntaxhighlight>
</syntaxhighlight>


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


Where <code>default</code> profiles are blank. Need not to mention in Maven build.
Where <code>default</code> profiles are blank. Need not to mention in Maven build.
Line 117: Line 123:
! Porfile !! Note !! Diff Types
! Porfile !! Note !! Diff Types
|-
|-
| dat || Include Data || tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data
| data || Include Data || tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data
|-
|-
| default || Exclude Data || tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints
| default || Exclude Data || tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints
Line 127: Line 133:
! Porfile !! Database !! Type  !! JDBC !! Notes
! Porfile !! Database !! Type  !! JDBC !! Notes
|-
|-
| der || Apache Derby || derby || jdbc:derby || No Issues
| derby || Apache Derby || derby || jdbc:derby || No Issues
|-
|-
| h2d || H2 || h2 || jdbc:h2:file || No Issues
| h2 || H2 || h2 || jdbc:h2:file || No Issues
|-
|-
| hsq || HSQL || hsqldb || jdbc:hsqldb:file || No Issues
| hsqldb || HSQL || hsqldb || jdbc:hsqldb:file || No Issues
|-
|-
| mys || MySQL || mysql || jdbc:mysql || No Issues
| mysql || MySQL || mysql || jdbc:mysql || No Issues
|-
|-
| ora || Oracle || oracle || jdbc:oracle:thin || 11g driver for '''diff''' with '''AL32UTF8''' or '''AL16UTF16'''
| mariadb || MariaDB || mariadb || jdbc:mariadb || No Issues
|-
|-
| pos || PostgreSQL || postgresql || jdbc:postgresql || 8.2+ for '''drop all database objects'''
| oracle || Oracle || oracle || jdbc:oracle:thin || 11g driver for '''diff''' with '''AL32UTF8''' or '''AL16UTF16'''
|-
|-
| sql || Sql Server || sqlserver || jdbc:sqlserver || No Issues
| postgresql || PostgreSQL || postgresql || jdbc:postgresql || 8.2+ for '''drop all database objects'''
|-
|-
| ite || SQLite || sqlite || jdbc:sqlite || No Issues
| sqlserver || Sql Server || sqlserver || jdbc:sqlserver || No Issues
|-
|-
| default || MySQL || mysql || jdbc:mysql || No Issues
| sqlite || SQLite || sqlite || jdbc:sqlite || No Issues
|-
| default || H2 || h2 || jdbc:h2:file || No Issues
|}
|}


Line 151: Line 159:
! Porfile !! Context !! Notes
! Porfile !! Context !! Notes
|-
|-
| dev || Development || Development Environment
| staging || Staging || Development Environment
|-
| qac || Quality Assurance & Control || '''QAC''' Environment
|-
|-
| uat || User Acceptance Test || '''UAT''' Environment
| uat || User Acceptance Test || '''UAT''' Environment
|-
|-
| pro || Production || Production Environment
| production || Production || Production Environment
|-
|-
| default || Development || Development Environment
| default || Staging || Development Environment
|}
|}


Line 169: Line 175:
| log || generateChangeLog || Generate Change Log
| log || generateChangeLog || Generate Change Log
|-
|-
| dif || diff || Generate Database Diff Change Log
| diff || diff || Generate Database Diff Change Log
|-
|-
| syn || changelogSync || Synchronize Database with Change Log
| sync || changelogSync || Synchronize Database with Change Log
|-
|-
| upd || update || Migration/Update Database
| update || update || Migration/Update Database
|-
|-
| cle || clearCheckSums || Removes current checksums, on next run recomputed
| clear || clearCheckSums || Removes current checksums, on next run recomputed
|-
|-
| rol || rollback || Rollback Database to the Date/Tag
| rollback || rollback || Rollback Database to the Date/Tag
|-
|-
| del || dropAll || Empty/Clean Database
| dropall || dropAll || Empty/Clean Database
|}
|}


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


Line 246: Line 252:
     <configuration>
     <configuration>
         <changeSetAuthor>chorke</changeSetAuthor>
         <changeSetAuthor>chorke</changeSetAuthor>
         <changeSetContext>dev,qac,uat,pro</changeSetContext>
         <changeSetContext>staging,uat,production</changeSetContext>
         <dataDir>${project.build.directory}/${change.on}</dataDir>
         <dataDir>${project.build.directory}/${change.on}</dataDir>
         <changeLogFile>META-INF/migrations/db.changelog-master.xml</changeLogFile>
         <changeLogFile>META-INF/migrations/db.changelog-master.xml</changeLogFile>
Line 263: Line 269:
</plugin>
</plugin>
</syntaxhighlight>
</syntaxhighlight>
==Challenges==
{| class="wikitable sortable"
|-
! Database !! Auto Increment !! Sequence !! Usable !! Chorke
|-
! DB2/LUW
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✕</code>
|-
! DB2/z
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✕</code>
|-
! PostgreSQL
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! Derby
| <code>✕</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! Firebird
| <code>✕</code> || <code>✓</code> || <code>✓</code> || <code>✕</code>
|-
! H2
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! HyperSQL
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! INGRES
| <code>✓</code> || <code>✕</code> || <code>✕</code> || <code>✕</code>
|-
! Informix
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✕</code>
|-
! MariaDB
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! MySQL
| <code>✓</code> || <code>✕</code> || <code>✕</code> || <code>✕</code>
|-
! Oracle
| <code>✕</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! Sql Server
| <code>✕</code> || <code>✓</code> || <code>✓</code> || <code>✓</code>
|-
! SQLite
| <code>✓</code> || <code>✕</code> || <code>✕</code> || <code>✕</code>
|-
! Sybase
| <code>✓</code> || <code>✕</code> || <code>✕</code> || <code>✕</code>
|-
! Sybase Anywhere
| <code>✓</code> || <code>✓</code> || <code>✓</code> || <code>✕</code>
|}


==References==
==References==
Line 289: Line 349:
* [[Locale]]
* [[Locale]]
* [[JPA]]
* [[JPA]]
| valign="top" |
* [https://docs.liquibase.com/change-types/community/create-sequence.html#:~:text=Database%20support Create Sequence Supported Database]
* [https://docs.liquibase.com/change-types/community/add-auto-increment.html#:~:text=Database%20support Auto Increment Supported Database]
* [https://stackoverflow.com/questions/2881321/ Insert Date in SQLite through Java]
* [https://www.liquibase.org/blog/contexts-vs-labels Understanding Contexts vs. Labels]
* [https://stackoverflow.com/questions/16890723/ List all Liquibase SQL Types]
* [https://stackoverflow.com/questions/1404210/ Java Date vs Calendar]
* [https://docs.liquibase.com/concepts/advanced/contexts.html Liquibase Contexts]
* [https://docs.liquibase.com/concepts/advanced/labels.html Liquibase Labels]
* [[MapStruct]]
* [[Lombok]]
|-
| colspan="3" |
----
|-
| valign="top" |
* [https://stackoverflow.com/questions/39620317/ Map a JSON column with H2 & Spring JPA]
* [https://kariera.future-processing.pl/blog/database-release-using-liquibase/ Database release using Liquibase]
* [https://docs.liquibase.com/tools-integrations/springboot/springboot.html Liquibase configuration options]
* [https://xenovation.com/blog/development/java/java-professional-developer/liquibase-related-sql-java-types Liquibase types vs. SQL & Java]
* [http://www.h2database.com/html/datatypes.html#enum_type Enum Type » H2 Database]
* [https://www.postgresql.org/docs/current/datatype-enum.html Enum Type » PostgreSQL]
* [https://stackoverflow.com/questions/203469/ Enum Type » Oracle]
* [https://dev.mysql.com/doc/refman/8.0/en/enum.html Enum Type » MySQL]
| valign="top" |
| valign="top" |


|}
|}

Latest revision as of 22:57, 14 August 2022

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

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