Liquibase: Difference between revisions
Jump to navigation
Jump to search
(5 intermediate revisions by the same user not shown) | |||
Line 123: | Line 123: | ||
! Porfile !! Note !! Diff Types | ! 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 | | default || Exclude Data || tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints | ||
Line 133: | Line 133: | ||
! Porfile !! Database !! Type !! JDBC !! Notes | ! 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 || | | default || H2 || h2 || jdbc:h2:file || No Issues | ||
|} | |} | ||
Line 159: | Line 159: | ||
! Porfile !! Context !! Notes | ! Porfile !! Context !! Notes | ||
|- | |- | ||
| | | staging || Staging || Development Environment | ||
|- | |- | ||
| uat || User Acceptance Test || '''UAT''' Environment | | uat || User Acceptance Test || '''UAT''' Environment | ||
|- | |- | ||
| | | production || Production || Production Environment | ||
|- | |- | ||
| default || | | default || Staging || Development Environment | ||
|} | |} | ||
Line 179: | Line 175: | ||
| log || generateChangeLog || Generate Change Log | | 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 | ||
|} | |} | ||
Line 256: | Line 252: | ||
<configuration> | <configuration> | ||
<changeSetAuthor>chorke</changeSetAuthor> | <changeSetAuthor>chorke</changeSetAuthor> | ||
<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 374: | Line 370: | ||
* [https://docs.liquibase.com/tools-integrations/springboot/springboot.html Liquibase configuration options] | * [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] | * [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:
- Diff (
dat
,default
) - Database (
der
,h2d
,hsq
,mys
,ora
,pos
,default
) - Context (
dev
,dmo
,qac
,uat
,pro
,default
) - Command (
Change Log(log)
,Different(dif)
,Synchronise(syn)
,Update(upd)
,Clear Checksum(cle)
,Rollback(rol)
,Empty DB(del)
) - labels (
ddl
,dml
)- DDL:
- Operations:
ddl
,create(new)
,alter(alt)
,drop(dro)
- Constraints:
Foreign(fkc)
,Not Null(nnc)
,Primary(pkc)
,Unique(ukc)
- Objects:
table(tbl)
,sequence(sqn)
,column(col)
,index(idx)
,view(vue)
,function(fun)
,procedure(pro)
- Operations:
- DML:
dml
,csv
,insert(ins)
,update(upd)
,delete(del)
- DDL:
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
| ||