Setup
Create a new java project
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.liquibase.mariadb.app</groupId>
<artifactId>LiquibaseMariadbp</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
<dependencies>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.4.6.Final</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</pluginManagement>
</build>
</project> |
Test 1: Update database with changeLog in XML format
From the command line in the project directory
...
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] SELECT count(1) FROM information_schema.tables
WHERE table_name = 'PDPSTATISTICS'
[INFO] INSERT INTO PDPSTATISTICS
(ID, NAME, VERSION, PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT,
POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT,
POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP)
VALUES (3, 'TestName', '1.0.0', 'TestGroup', 'TestSubGroup', 1, 1, 1, 1, 1, 1, now())
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog.xml::policy-1::policy ran successfully in 7ms
Test 2: Rollback update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
...
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] Rolling Back Changeset:dbchangelog.xml::policy-1::policy
[INFO] DELETE FROM PDPSTATISTICS WHERE ID=3
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-1' AND AUTHOR = 'policy' AND FILENAME = 'dbchangelog.xml'
Test 3: Generate database dump chageLog in YAML format
Add the following 2 lines to liquibase.properties
...
The file dbchangelog.mariadb.yaml will contain the DDL for your database in YAML format
| View file | ||||
|---|---|---|---|---|
|
Test 4: Generate database dump chageLog in SQL format
change outputChangeLogFile: dbchangelog.mariadb.yaml to outputChangeLogFile: dbchangelog.mariadb.sql
The file dbchangelog.mariadb.sql will contain the DDL for your database in SQL format
| View file | ||||
|---|---|---|---|---|
|
Test 5: Update database with changeLog in YAML format
Add dbchangelog.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: 1policy-2
author: admin
changes:
- createTable:
tableName: personJpaPdpPolicyDeploymentAudit
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
nameconstraints:
firstname
typeprimaryKey: varchar(50)true
- columnnullable: false
name: lastnamename
type: varchar(50120)
- constraintscolumn:
name: nullable:version
false
- column:type: varchar(20)
nameconstraints:
state
typeprimaryKey: char(2)
true
- changeSet:
id: 2
authornullable: adminfalse
changes:
- addColumncolumn:
tableName: person
name: pdpGroup
columns:
type: - column: varchar(255)
nameconstraints:
username
typenullable: varchar(8)
true
- changeSetcolumn:
id name: 3
pdpType
author type: admin
varchar(255)
changes constraints:
- addLookupTable:
nullable: true
existingTableName: - column:
person
existingColumnNamename: stateaction
newTableName type: stateint
newColumnName: id
constraints:
newColumnDataType: char(2) |
Add a new plugin dependency to pom.xml
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.28</version>
</dependency>
mvn package
mvn liquibase:update
3 new entries in the databasechangelog table
nullable: true
- column:
name: timeStamp
type: datetime
constraints:
nullable: true
- changeSet:
id: policy-3
author: admin
changes:
- addColumn:
tableName: JpaPdpPolicyDeploymentAudit
columns:
- column:
name: changedByUser
type: varchar(255)
constraints:
nullable: true
|
Add a new plugin dependency to pom.xml
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.28</version>
</dependency>
mvn package
mvn liquibase:update
2 new entries in the databasechangelog table
| Code Block | ||||
|---|---|---|---|---|
| ||||
select * from databasechangelog;
+----+--------+ | ||||
| Code Block | ||||
| ||||
select * from databasechangelog; +----+--------+------------------+---------------------+---------------+----------+------------------------------+------+---------------+---------------+----------+------------------------------------+-----------------------------+----------+------+-----------+----------+--------+---------------+ | ID | ----------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | policy-2 | admin | dbchangelog.yaml | 2021-05-24 15:04:49 | 10 | EXECUTED | 8:f5bd9cec4e2374254cc1985e8e9bfaae | createTable tableName=JpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | 1865089400 | | policy-3 | admin | dbchangelog.yaml | 2021-05-24 15:04:49 | 11 | EXECUTED | 8:4c9ec93973102be9b6d966cf6bc724b4 | addColumn tableName=JpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | 1865089400 | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ |
Test 6: Rollback last update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] Rolling Back Changeset:dbchangelog.yaml::policy-3::admin
[INFO] ALTER TABLE JpaPdpPolicyDeploymentAudit DROP COLUMN changedByUser
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-3' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 7: Rollback last update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] Rolling Back Changeset:dbchangelog.yaml::policy-2::admin
[INFO] DROP TABLE JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 8: Update database with changeLog in YAML format which includes raw SQL
Add dbchangelog-sql.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-4
author: admin
changes:
- sql:
comment: create JpaPdpPolicyDeploymentAudit table
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
);
stripComments: true
- changeSet:
id: policy-5
author: admin
changes:
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit
(id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values
(1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true |
update liquibase.properties
changeLogFile: dbchangelog-sql.yaml
mvn liquibase:update
A new table called JpaPdpPolicyDeploymentAudit with one entry has been added to the database
Test 9: Rollback 2 previous updates
Run mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-5::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 1
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-5' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'
[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-4::admin
[INFO] drop table JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'
Test 10: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs
Add JpaPdpPolicyDeploymentAudit-changelog-1.sql
| Code Block | ||||
|---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:policy-6
CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
);
-- rollback drop table JpaPdpPolicyDeploymentAudit
|
Add JpaPdpPolicyDeploymentAudit-changelog-2.sql
| Code Block | ||||
|---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:policy-7
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin');
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin');
-- rollback delete from JpaPdpPolicyDeploymentAudit where id in(1,2); |
Add dbchangelog-master.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- include:
file: JpaPdpPolicyDeploymentAudit-changelog-1.sql
- include:
file: JpaPdpPolicyDeploymentAudit-changelog-2.sql |
Update liquibase.properties → changeLogFile: dbchangelog-master.yaml
Run mvn liquibase:update
[INFO] CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
)
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-1.sql::policy-6::admin ran successfully in 329ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-6', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-1.sql', NOW(), 13, '8:c5d0bc1cbc6a6875136d754002802e6f', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-2.sql::policy-7::admin ran successfully in 15ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-7', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-2.sql', NOW(), 14, '8:5b683eb6e17f177268ed50b9d2829bda', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')
The a new table called customer has been created and 2 records have been inserted.
Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state
Test 11: Update database with changeLog in YAML format which includes raw SQL and tags
Add Add dbchangelog-sql2.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-8
author: admin
changes:
- tagDatabase:
tag: 1.0
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (3, 'TestName3', '1.0.0', 'TestGroup3', 'TestType3', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 3;
- changeSet:
id: policy-9
author: admin
changes:
- tagDatabase:
tag: 1.1
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 4; |
Update liquibase.properties → changeLogFile: dbchangelog-sql2.yaml
Run mvn:liquibase update
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-8', 'admin', 'dbchangelog-sql2.yaml', NOW(), 15, '8:f83378476fe49386b479e3bdaf33c8f0', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.0')
[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 4ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-9', 'admin', 'dbchangelog-sql2.yaml', NOW(), 16, '8:3012add458c8a5395af9f2797779af1e', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.1')
2 new records have been added to the JpaPdpPolicyDeploymentAudit table
Test 12: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.0
[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::policy-9::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-9' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'
Last insert has been deleted
Test 13: Re-run last update
mvn liquibase:update
[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 7ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE,
Only the second insert statement ran.
Test 14: Add labels to changeSets
Add dbchangelog-sql3.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-10
author: admin
labels: release1.0
changes:
- tagDatabase:
tag: 1.2
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 5;
- changeSet:
id: polict-11
author: admin
labels: release1.1
changes:
- tagDatabase:
tag: 1.3
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 6; |
Run mvn liquibase:update -Dliquibase.labels=release1.0
[INFO] Tag '1.2' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::policy-10::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-10', 'admin', 'dbchangelog-sql3.yaml', NOW(), 17, '8:71e4400a0bf77f08304ea92c3a989657', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1870546128', '1.2')
Only changeSet with label release1.0 has been applied
Test 15: Apply second label
Run mvn liquibase:update -Dliquibase.labels=release1.0,release1.1
[INFO] Tag '1.3' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::polict-11::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('polict-11', 'admin', 'dbchangelog-sql3.yaml', NOW(), 18, '8:cab3ec22e42eb1be367a62d0a86c80dc', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1870657144', '1.3')
ChangeSet with label release1.1 has been applied
Test 16: Test contexts
Add dbchangelog-sql4.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-12
author: admin
context: qa
labels: release1.2
changes:
- tagDatabase:
tag: 1.4
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (7, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 7;
- changeSet:
id: policy-13
author: admin
context: test
labels: release1.2
changes:
- tagDatabase:
tag: 1.9
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 8; |
Run mvn liquibase:update -Dliquibase.contexts=test
[INFO] Tag '1.9' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::policy-13::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-13', 'admin', 'dbchangelog-sql4.yaml', NOW(), 19, '8:2c2fb879e12ca3547180d53dbe43c53a', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '1871103966', '1.9')
Only the changeSet with context test has run
To see more about contexts and labels please visit: Understanding Contexts vs. Labels
Test 17: Test YAML changelog
Add dbchangelog-sql5.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-14
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 20
- createTable:
columns:
- column:
name: PDPGROUPNAME
type: VARCHAR(120)
- column:
name: PDPSUBGROUPNAME
type: VARCHAR(120)
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYFAILCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYSUCCESSCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDFAILCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDSUCCESSCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: TIMESTAMP
type: datetime
- column:
constraints:
primaryKey: true
name: ID
type: BIGINT
- column:
constraints:
primaryKey: true
name: name
type: VARCHAR(120)
- column:
constraints:
primaryKey: true
name: version
type: VARCHAR(20)
tableName: pdpstatistics
- changeSet:
id: policy-15
author: admin
label: release1.2
changes:
- tagDatabase:
tag: 21
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 3
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 4
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 5
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 6
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 7
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 8
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 9
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 10
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics |
Update liquibase.properties to point to dbchangelog-sql5.yaml
Run mvn liquibase:update
[INFO] Marking ChangeSet: dbchangelog-sql5.yaml::policy-14::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql5.yaml : Not precondition failed
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-14', 'admin', 'dbchangelog-sql5.yaml', NOW(), 20, '8:09312d85176b6fb93520e5d8f9702e96', 'tagDatabase', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1931263740', '20')
[INFO] Tag '21' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 3, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 4, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 5, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 6, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 7, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 8, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 9, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 10, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] ChangeSet dbchangelog-sql5.yaml::policy-15::admin ran successfully in 21ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-15', 'admin', 'dbchangelog-sql5.yaml', NOW(), 21, '8:70aa4c084fd72dba46a76ce8fe5b5732', 'tagDatabase; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tab...', '', 'EXECUTED', NULL, NULL, '3.8.0', '1931263740', '21')
mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog-sql5.yaml::policy-15::admin
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.454 s
[INFO] Finished at: 2021-05-25T09:33:29+01:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.8.0:rollback (default-cli) on project LiquibaseMariadbp: Error setting up or running Liquibase: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.InsertDataChange created -> [Help 1]
Cannot rollback.
Test 18: Test YAML changelog with embedded SQL
Add dbchangelog-sql6.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-16
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 22
- sql:
comment: create pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: CREATE TABLE pdpstatistics (
PDPGROUPNAME VARCHAR(120) NULL,
PDPSUBGROUPNAME VARCHAR(120) NULL,
POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL,
POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL,
POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
ID BIGINT NOT NULL,
name VARCHAR(120) NOT NULL,
version VARCHAR(20) NOT NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version));
stripComments: true
- changeSet:
id: policy-17
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 23
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');
rollback:
- sql:
sql: delete from pdpstatistics where id in (11,12,13,14,15,16,17,18,19,20); |
Update liquibase.properties to point to dbchangelog-sql6.yaml
Run mvn liquibase:update
[INFO] Marking ChangeSet: dbchangelog-sql6.yaml::policy-16::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql6.yaml : Not precondition failed
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-16', 'admin', 'dbchangelog-sql6.yaml', NOW(), 22, '8:9d2fcf472209118ad3760ca621ab787e', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1934277203', '22')
[INFO] Tag '23' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql6.yaml::policy-17::admin ran successfully in 52ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-17', 'admin', 'dbchangelog-sql6.yaml', NOW(), 23, '8:0dfbbf124f39d3e3ae9a0b8a41fb62d0', 'tagDatabase; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1934277203', '23')
Test 19: Test rollup table creation
Add dbchangelog-sql7.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-18
author: admin
label: release1.3
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics_rollup
changes:
- tagDatabase:
tag: 24
- sql:
comment: create pdpstatistics_rollup
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: CREATE TABLE pdpstatistics_rollup (
name VARCHAR(120) NOT NULL,
version VARCHAR(20) NOT NULL,
PDPGROUPNAME VARCHAR(120) NULL,
PDPSUBGROUPNAME VARCHAR(120) NULL,
YEAR BIGINT NOT NULL,
MONTH BIGINT NOT NULL,
AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));
rollback:
- sql:
sql: drop table pdpstatistics_rollup
- changeSet:
id: policy-19
author: admin
label: release1.3
preConditions:
- onFail: MARK_RAN
- tableExists:
tableName: pdpstatistics_rollup
changes:
- tagDatabase:
tag: 25
- sql:
comment: insert pdpstatistics_rollup
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics_rollup (
name,
version,
PDPGROUPNAME,
PDPSUBGROUPNAME,
YEAR,
MONTH,
AVG_POLICYDEPLOYCOUNT,
AVG_POLICYDEPLOYFAILCOUNT,
AVG_POLICYDEPLOYSUCCESSCOUNT,
AVG_POLICYEXECUTEDCOUNT,
AVG_POLICYEXECUTEDFAILCOUNT,
AVG_POLICYEXECUTEDSUCCESSCOUNT
)
select name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
EXTRACT(YEAR FROM timestamp) as YEAR,
EXTRACT(MONTH FROM timestamp) as MONTH,
avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT,
avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT,
avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT,
avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT,
avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT,
avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT
from pdpstatistics
group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
EXTRACT(YEAR FROM timestamp),
EXTRACT(MONTH FROM timestamp);
rollback:
- sql:
sql: delete from pdpstatistics_rollup |
Update liquibase.properties to point to dbchangelog-sql7.yaml
Run mvn liquibase:update
[INFO] Tag '24' applied to database
[INFO] CREATE TABLE pdpstatistics_rollup ( name VARCHAR(120) NOT NULL, version VARCHAR(20) NOT NULL, PDPGROUPNAME VARCHAR(120) NULL, PDPSUBGROUPNAME VARCHAR(120) NULL, YEAR BIGINT NOT NULL, MONTH BIGINT NOT NULL, AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-18::admin ran successfully in 34ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-18', 'admin', 'dbchangelog-sql7.yaml', NOW(), 24, '8:3c8089ad83c121f40c403ccf596b6196', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '24')
[INFO] Tag '25' applied to database
[INFO] INSERT INTO pdpstatistics_rollup ( name, version, PDPGROUPNAME, PDPSUBGROUPNAME, YEAR, MONTH, AVG_POLICYDEPLOYCOUNT, AVG_POLICYDEPLOYFAILCOUNT, AVG_POLICYDEPLOYSUCCESSCOUNT, AVG_POLICYEXECUTEDCOUNT, AVG_POLICYEXECUTEDFAILCOUNT, AVG_POLICYEXECUTEDSUCCESSCOUNT ) select name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp) as YEAR, EXTRACT(MONTH FROM timestamp) as MONTH, avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT, avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT, avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT, avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT, avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT, avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT from pdpstatistics group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp), EXTRACT(MONTH FROM timestamp);
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-19::admin ran successfully in 20ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-19', 'admin', 'dbchangelog-sql7.yaml', NOW(), 25, '8:23ed645fde97a2c2a7306e1e47f63646', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '25')
| Code Block | ||||
|---|---|---|---|---|
| ||||
MariaDB [policy]> select * from pdpstatistics_rollup; +-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+---------| LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +----+--------+--------------------+--------------------------------+ | name | version | PDPGROUPNAME | PDPSUBGROUPNAME | YEAR | MONTH | AVG_POLICYDEPLOYCOUNT | AVG_POLICYDEPLOYFAILCOUNT | AVG_POLICYDEPLOYSUCCESSCOUNT | AVG_POLICYEXECUTEDCOUNT | AVG_POLICYEXECUTEDFAILCOUNT | AVG_POLICYEXECUTEDSUCCESSCOUNT | +-------+---------+----------+----+-----------------+------+-------+--+---------------------+---------------------------+------------------------------+-------------------------+--+----------+------+-----------+----------+--------+---------------+ | 1 name2 | 0.0.0 | group | adminsubgroup | dbchangelog.yaml2004 | 2021-05-20 16:07:58 3 | 1 | EXECUTED | 8:f022085620a9dd87ae9ca1f93da51fd02 | createTable tableName=person 1 | 1 | 2 | | NULL | 3.8.0 | NULL | NULL 1 | 1523278917 | | 2 | admin | dbchangelog.yaml | 2021-05-20 16:07:58 | 1 | | 2name2 | EXECUTED 0.0.0 | 8:e3f0f50eb245f66c68ec504db93b6717 | addColumn tableName=person group | subgroup | 2021 | 5 | 2 | 1 | | | NULL | 3.8.0 1 | NULL | NULL | 1523278917 | | 3 | admin | dbchangelog.yaml2 | 2021-05-20 16:07:59 | 3 | EXECUTED | 8:12a8bb9e438047d9332c8aec4f90a76e | addLookupTable existingColumnName=state, existingTableName=person, newColumnName=id, newTableName=state 1 | | NULL | 3.8.0 | NULL | NULL | 1523278917 1 | +----+--------+------------------+---------------------+---------+------+----------+-----------------+------+-------+------+-----------------+---------------------------+------------------------------+-------------------------+------+----------+------+-----------+----------+--------+---------------+ |
Test
...
20: Test preconditions
Add dbchangelog-sql8.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-20
author: admin
label: release1.4
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: toscaservicetemplate
changes:
- tagDatabase:
tag: 25
- sql:
comment: create toscaservicetemplate |
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] Rolling Back Changeset:dbchangelog.yaml::3::admin
[INFO] ALTER TABLE person DROP FOREIGN KEY FK_PERSON_STATE
[INFO] DROP TABLE state
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '3' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 7: Rollback last 2 updates
mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog.yaml::2::admin
[INFO] ALTER TABLE person DROP COLUMN username
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
[INFO] Rolling Back Changeset:dbchangelog.yaml::1::admin
[INFO] DROP TABLE person
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '1' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 8: Update database with changeLog in YAML format which includes raw SQL
Add dbchangelog-sql.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog: - changeSet: id: sql-yaml-1 dbms: '!h2, oracle, mysql, mariadb' author endDelimiter: admin\nGO changes splitStatements: true - sqlstripComments: true comment: create person table sql: CREATE TABLE toscaservicetemplate ( dbms: '!h2, oracle, mysql, mariadb' `DESCRIPTION` VARCHAR(255) NULL, endDelimiter: \nGO TOSCADEFINITIONSVERSION splitStatements: true VARCHAR(255) NULL, derived_from_name VARCHAR(255) NULL, sql: create table person (name varchar(30)) derived_from_version VARCHAR(255) NULL, name VARCHAR(120) NOT NULL, stripComments: true - changeSet: version VARCHAR(20) NOT NULL, capabilityTypesVersion VARCHAR(20) NULL, id: sql-yaml-2 author: admin capabilityTypesName VARCHAR(120) NULL, changes: dataTypesName VARCHAR(120) NULL, - sql: dataTypesVersion VARCHAR(20) NULL, nodeTypesVersion VARCHAR(20) NULL, comment: insert name dbms: '!h2nodeTypesName VARCHAR(120) NULL, oracle, mysql, mariadb' policyTypesName VARCHAR(120) NULL, endDelimiter: \nGO policyTypesVersion VARCHAR(20) NULL, relationshipTypesVersion VARCHAR(20) NULL, splitStatements: true relationshipTypesName VARCHAR(120) NULL, sql: insert into person (name) values ('Liquibase Test') topologyTemplateLocalName VARCHAR(120) NULL, stripComments: true |
update liquibase.properties
changeLogFile: dbchangelog-sql.yaml
mvn liquibase:update
A new table called person with one entry has been added to the database
Test 9: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs
Add customer-table-changelog-1.sql
| Code Block | ||||
|---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:cust-1
create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
);
-- rollback drop table customer; |
Add customer-table-changelog-2.sql
| Code Block | ||||
|---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:cust-2
insert into customer(id, name, role) values (1, 'Robert', 'Software Developer');
insert into customer(id, name, role) values (2, 'John', 'Dev Ops');
-- rollback delete from customer where id in(1,2); |
Add dbchangelog-master.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- include:
file: customer-table-changelog-1.sql
- include:
file: customer-table-changelog-2.sql |
Update liquibase.properties → changeLogFile: dbchangelog-master.yaml
Run mvn liquibase:update
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
)
[INFO] Custom SQL executed
[INFO] ChangeSet customer-table-changelog-1.sql::cust-1::admin ran successfully in 306ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('cust-1', 'admin', 'customer-table-changelog-1.sql', NOW(), 3, '8:6952c30a86e91b5f32a65e313f52b64b', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599263670')
[INFO] insert into customer(id, name, role) values (1, 'Robert', 'Software Developer')
[INFO] insert into customer(id, name, role) values (2, 'John', 'Dev Ops')
[INFO] Custom SQL executed
[INFO] ChangeSet customer-table-changelog-2.sql::cust-2::admin ran successfully in 11ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('cust-2', 'admin', 'customer-table-changelog-2.sql', NOW(), 4, '8:52f9c3c97a509dc22f109be204fc8dfd', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599263670')
The a new table called customer has been created and 2 records have been inserted.
Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state
Test 10: Update database with changeLog in YAML format which includes raw SQL and tags
Add Add dbchangelog-sql2.yaml
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog: - changeSet: id: cust-3 author: admin changes: - tagDatabase: tag: 1.2 - sql: comment: insert customer topologyTemplateParentKeyName VARCHAR(120) NULL, topologyTemplateParentKeyVersion VARCHAR(15) NULL, topologyTemplateParentLocalName VARCHAR(120) NULL, CONSTRAINT PK_TOSCASERVICETEMPLATE PRIMARY KEY (name, version)); - changeSet: id: policy-21 author: admin label: release1.4 preConditions: - onFail: MARK_RAN - sqlCheck: expectedResult: 0 sql: select count(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and version='1.0.0'; changes: - tagDatabase: tag: 26 - sql: comment: insert toscaservicetemplate; dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true stripComments: true sql: INSERT INTO toscaservicetemplate (`DESCRIPTION`, TOSCADEFINITIONSVERSION, derived_from_name, derived_from_version, name, version, capabilityTypesVersion, capabilityTypesName, dataTypesName, dataTypesVersion, nodeTypesVersion, nodeTypesName, policyTypesName, policyTypesVersion, relationshipTypesVersion, relationshipTypesName, topologyTemplateLocalName, topologyTemplateParentKeyName, topologyTemplateParentKeyVersion, topologyTemplateParentLocalName) dbms:VALUES '!h2, oracle, mysql, mariadb' endDelimiter: \nGO (NULL, 'tosca_simple_yaml_1_1_0', NULL, NULL, 'ToscaServiceTemplateSimple', '1.0.0', NULL, NULL, 'ToscaDataTypesSimple', '1.0.0', NULL, NULL, splitStatements: true sql: insert into customer (id, name, role) values (3, 'Pete', 'Payroll') 'ToscaPolicyTypesSimple', '1.0.0', NULL, NULL, NULL, NULL, NULL, NULL); - changeSet: id: policy-22 stripCommentsauthor: trueadmin label: release1.4 rollbackpreConditions: - sqlonFail: MARK_RAN - sqlsqlCheck: delete from customer where id = 3; expectedResult: 0 - changeSet: idsql: cust-4 author: admin changes: select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName'; changes: - tagDatabase: tag: 1.327 - sql: comment: insert customeradd constraint toscaservicetemplate; dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql: insertstripComments: into customertrue (id, name, role) sql: ALTER TABLE valuestoscaservicetemplate (4, 'James', 'Payroll')ADD CONSTRAINT stripComments: true FK_ToscaServiceTemplate_capabilityTypesName FOREIGN KEY rollback: (capabilityTypesName, capabilityTypesVersion) -REFERENCES sql: sql: delete from customer where idtoscacapabilitytypes = 4; (name, version) ON UPDATE RESTRICT ON DELETE RESTRICT; |
Update liquibase.properties → changeLogFile: dbchangelog-sql2.yamlRun mvn:liquibase updateliquibase.properties to point to dbchangelog-sql8.yaml
Run mvn liquibase:update
[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-20::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : Not precondition failed
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-320', 'admin', 'dbchangelog-sql2sql8.yaml', NOW(), 526, '8:a660ebc91d2fa7b7e4c2287bf17257072954c7c4f467425b3ea5d35bf45ef4a9', 'tagDatabase; sql', '', 'EXECUTEDMARK_RAN', NULL, NULL, '3.8.0', '15996497601942893174', '1.225')
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3msselect count(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and version='1.0.0'
[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-21::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : SQL Precondition failed. Expected '0' got '1'
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-421', 'admin', 'dbchangelog-sql2sql8.yaml', NOW(), 627, '8:cb8dbdef98c88b2e99d42a6591f88498d86e5dad1fce9bb955d7cb0558b29707', 'tagDatabase; sql', '', 'EXECUTEDMARK_RAN', NULL, NULL, '3.8.0', '15996497601942893174', '1.326')
2 new records have been added to the customer table
Test 11: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.2
[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::cust-4::admin
[INFO] delete from customer where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'cust-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'
Last insert has been deleted
Test 12: Re-run last update
mvn liquibase:update
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3ms
Only the second insert statement ran.
Test 13: Add labels to changeSets
Add dbchangelog-sql3.yaml
[INFO] select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName'
[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-22::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : SQL Precondition failed. Expected '0' got '1'
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-22', 'admin', 'dbchangelog-sql8.yaml', NOW(), 28, '8:af557b428391cfcdd55e7f554c3e5cad', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '27')
None of the SQL statements ran as all the preconditions failed
The above YAML can be rewriiten like this:
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-23
author: admin
label: release1.4
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: toscaservicetemplate
| ||||
| Code Block | ||||
| ||||
databaseChangeLog: - changeSet: id: cust-5 author: admin labels: release1.0 changes: - tagDatabase: tag: 1.628 - sqlsqlFile: comment: create toscaservicetemplate dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: inserttrue customer dbmsstripComments: '!h2,true oracle, mysql, mariadb' endDelimiter: \nGO path: sql/create_table_toscaservicetemplate.sql splitStatements: true relativeToChangelogFile: true sql: insert- into customerchangeSet: (id, name, role) values (5, 'Jane', 'Payroll') id: policy-24 stripCommentsauthor: trueadmin label: release1.4 rollbackpreConditions: - sqlonFail: MARK_RAN - sqlsqlCheck: delete from customer where id = 5; - changeSetexpectedResult: 0 id: cust-6 authorsql: select admincount(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and labels: release1.1 version='1.0.0'; changes: - tagDatabase: tag: 1.729 - sqlsqlFile: comment: insert customertoscaservicetemplate; dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql: insert into customer (id, name, role) values (6, 'Jean', 'Dev OPS') stripComments: true rollback: - sql: sql: delete from customer where id = 6;path: sql/insert_toscaservicetemplate.sql relativeToChangelogFile: true - |
Add labels to liquibase.properties
labels: release1.0
Run mvn liquibase:update
[INFO] Tag '1.6' applied to database
[INFO] insert into customer (id, name, role) values (5, 'Jane', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-5::admin ran successfully in 3ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-5', 'admin', 'dbchangelog-sql3.yaml', NOW(), 7, '8:ce6fde026140f9831fa66b60662e842d', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1851627465', '1.6')
Only changeSet with label release1.0 has been applied
Test 14: Apply second label
update liquibase.properties
labels: release1.0, release1.1
Run mvn liquibase:update
[INFO] Tag '1.7' applied to database
[INFO] insert into customer (id, name, role) values (6, 'Jean', 'Dev OPS')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-6::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-6', 'admin', 'dbchangelog-sql3.yaml', NOW(), 8, '8:96a8060554eef1d1f4cf4f43dd2c376c', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1852080035', '1.7')
ChangeSet with label release1.1 has been applied
Test 15: Test contexts
Remove labels: release1.0, release1.1 from liquibase.properties
Add dbchangelog-sql4.yaml
changeSet:
id: policy-25
author: admin
label: release1.4
preConditions:
- onFail: MARK_RAN
- sqlCheck:
expectedResult: 0
sql: select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName';
changes:
- tagDatabase:
tag: 28
- sqlFile:
comment: add constraint toscaservicetemplate;
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
path: sql/add_toscaservicetemplate_constraint.sql
relativeToChangelogFile: true |
The embedded SQL has been removed from the changeLog and put in separate SQL files.
Common tests used in liquibase are available here: common.tests.changelog.yaml
We can use all the liquibase functions with SQL and include rollback:
| Code Block | ||||
|---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet | ||||
| Code Block | ||||
| ||||
databaseChangeLog: - changeSet: id: cust-7 author: admin context: qa labels: release1.2 changes: - tagDatabaseid: policy-26 tagauthor: 1.8admin - sqllabel: release1.5 commentpreConditions: insert customer - onFail: HALT dbms: '!h2, oracle, mysql, mariadb'- tableExists: endDelimiter: \nGO tableName: JpaPdpPolicyDeploymentAudit splitStatements: true changes: - sqltagDatabase: insert into customer (id, name, role) values (7, 'Jo', 'Payroll') tag: 31 stripComments: true - sqlFile: rollback: comment: insert - sql:JpaPdpPolicyDeploymentAudit sql: delete fromdbms: customer where'!h2, id =oracle, 7; mysql, mariadb' - changeSet: idendDelimiter: cust-8 \nGO author: admin contextsplitStatements: test true labels: release1.2 changesstripComments: true - tagDatabase: tag: 1.9 - sql:path: sql/insert_JpaPdpPolicyDeploymentAudit.sql commentrelativeToChangelogFile: true insert customer dbms: '!h2, oracle, mysql, mariadb' rollback: - endDelimitersqlFile: \nGO splitStatementscomment: truerollback JpaPdpPolicyDeploymentAudit sql: insert intodbms: customer (id'!h2, nameoracle, role) mysql, mariadb' values (8, 'Kate', 'Dev OPS') endDelimiter: \nGO stripComments: true splitStatements: true rollbackstripComments: true - sql: sql: delete from customer where id = 8; path: sql/rollback_insert_JpaPdpPolicyDeploymentAudit.sql relativeToChangelogFile: true |
Run liquibase:update -Dliquibase.contexts=test
[INFO] Tag '1.9' applied to database
[INFO] insert into customer (id, name, role) values (8, 'Kate', 'Dev OPS')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::cust-8::admin ran successfully in 4ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-8', 'admin', 'dbchangelog-sql4.yaml', NOW(), 9, '8:cea7bb92b5c448e1c217b9ed69fce75e', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '1856798669', '1.9')
Only the changeSet with context test has run
Note: labels can be applied in a similar fashion: liquibase:update -Dliquibase.labels=release1.2
|
| Code Block | ||||
|---|---|---|---|---|
| ||||
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values
(9, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin'),
(10, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin'); |
| Code Block | ||||
|---|---|---|---|---|
| ||||
delete from JpaPdpPolicyDeploymentAudit where id in (9,10); |
Important concepts to be considered
- Labels and Context - to separate environments and releases
- Tags - to set a rollback check point
- Prechecks - to check if a particular changeSet needs to be executed
- Different formats - decide the best one for the team to work with
- Include rollback statement with changeSet to allow user to undo changes.
See Some best practices to keep in mind when using Liquibase for more informationTo see more about contexts and labels please visit: Understanding Contexts vs. Labels