Setup
Create a new java project
Add liquibase.properties
changeLogFile: dbchangelog.xml url: jdbc:mariadb://localhost:3306/policy username: policy password: *****
Add dbchangelog.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<changeSet id="policy-1" author="policy">
<preConditions onFail="HALT">
<sqlCheck expectedResult="1">SELECT count(1) FROM information_schema.tables
WHERE table_name = 'PDPSTATISTICS'
</sqlCheck>
</preConditions>
<sql dbms="mariadb"
endDelimiter=";"
splitStatements="true"
stripComments="true">
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())
</sql>
<rollback>
DELETE FROM PDPSTATISTICS WHERE ID=3
</rollback>
</changeSet>
</databaseChangeLog>
Add pom.xml
<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
mvn package
mvn liquibase:update
Check you database, a new record has been added to pdpstatistics
[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
new record has been removed has been dropped
[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
outputChangeLogFile: dbchangelog.mariadb.yaml
diffTypes: catalog,tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data,storedprocedure
mvn liquibase:generateChangeLog
The file dbchangelog.mariadb.yaml will contain the DDL for your database in YAML format
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
Test 5: Update database with changeLog in YAML format
Add dbchangelog.yaml
databaseChangeLog:
- changeSet:
id: policy-2
author: admin
changes:
- createTable:
tableName: JpaPdpPolicyDeploymentAudit
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
constraints:
primaryKey: true
nullable: false
name: name
type: varchar(120)
- column:
name: version
type: varchar(20)
constraints:
primaryKey: true
nullable: false
- column:
name: pdpGroup
type: varchar(255)
constraints:
nullable: true
- column:
name: pdpType
type: varchar(255)
constraints:
nullable: true
- column:
name: action
type: int
constraints:
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
select * from databasechangelog; +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | 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
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
--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
--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
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
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
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
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
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
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
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')
MariaDB [policy]> select * from pdpstatistics_rollup; +-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+-----------------------------+--------------------------------+ | name | version | PDPGROUPNAME | PDPSUBGROUPNAME | YEAR | MONTH | AVG_POLICYDEPLOYCOUNT | AVG_POLICYDEPLOYFAILCOUNT | AVG_POLICYDEPLOYSUCCESSCOUNT | AVG_POLICYEXECUTEDCOUNT | AVG_POLICYEXECUTEDFAILCOUNT | AVG_POLICYEXECUTEDSUCCESSCOUNT | +-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+-----------------------------+--------------------------------+ | name2 | 0.0.0 | group | subgroup | 2004 | 3 | 2 | 1 | 1 | 2 | 1 | 1 | | name2 | 0.0.0 | group | subgroup | 2021 | 5 | 2 | 1 | 1 | 2 | 1 | 1 | +-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+-----------------------------+--------------------------------+
Test 20: Test preconditions
Add dbchangelog-sql8.yaml
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
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: CREATE TABLE toscaservicetemplate (
`DESCRIPTION` VARCHAR(255) NULL,
TOSCADEFINITIONSVERSION VARCHAR(255) NULL, derived_from_name VARCHAR(255) NULL,
derived_from_version VARCHAR(255) NULL, name VARCHAR(120) NOT NULL,
version VARCHAR(20) NOT NULL, capabilityTypesVersion VARCHAR(20) NULL,
capabilityTypesName VARCHAR(120) NULL, dataTypesName VARCHAR(120) NULL,
dataTypesVersion VARCHAR(20) NULL, nodeTypesVersion VARCHAR(20) NULL,
nodeTypesName VARCHAR(120) NULL, policyTypesName VARCHAR(120) NULL,
policyTypesVersion VARCHAR(20) NULL, relationshipTypesVersion VARCHAR(20) NULL,
relationshipTypesName VARCHAR(120) NULL,
topologyTemplateLocalName VARCHAR(120) NULL,
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)
VALUES (NULL, 'tosca_simple_yaml_1_1_0', NULL, NULL, 'ToscaServiceTemplateSimple',
'1.0.0', NULL, NULL, 'ToscaDataTypesSimple', '1.0.0', NULL, NULL,
'ToscaPolicyTypesSimple', '1.0.0', NULL, NULL, NULL, NULL, NULL, NULL);
- changeSet:
id: policy-22
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: 27
- sql:
comment: add constraint toscaservicetemplate;
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: ALTER TABLE toscaservicetemplate ADD CONSTRAINT
FK_ToscaServiceTemplate_capabilityTypesName FOREIGN KEY
(capabilityTypesName, capabilityTypesVersion) REFERENCES
toscacapabilitytypes (name, version) ON UPDATE RESTRICT ON DELETE RESTRICT;
Update liquibase.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 ('policy-20', 'admin', 'dbchangelog-sql8.yaml', NOW(), 26, '8:2954c7c4f467425b3ea5d35bf45ef4a9', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '25')
[INFO] select 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 ('policy-21', 'admin', 'dbchangelog-sql8.yaml', NOW(), 27, '8:d86e5dad1fce9bb955d7cb0558b29707', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '26')
[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:
databaseChangeLog:
- changeSet:
id: policy-23
author: admin
label: release1.4
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: toscaservicetemplate
changes:
- tagDatabase:
tag: 28
- sqlFile:
comment: create toscaservicetemplate
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
path: sql/create_table_toscaservicetemplate.sql
relativeToChangelogFile: true
- changeSet:
id: policy-24
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: 29
- sqlFile:
comment: insert toscaservicetemplate;
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
path: sql/insert_toscaservicetemplate.sql
relativeToChangelogFile: true
- 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
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.
Proposed Liquibase Format
Something like the following example might work best for us.
We can use all the liquibase functions with SQL at the same time:
databaseChangeLog:
- changeSet:
id: policy-26
author: admin
label: release1.5
preConditions:
- onFail: HALT
- tableExists:
tableName: JpaPdpPolicyDeploymentAudit
changes:
- tagDatabase:
tag: 31
- sqlFile:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
path: sql/insert_JpaPdpPolicyDeploymentAudit.sql
relativeToChangelogFile: true
rollback:
- sqlFile:
comment: rollback JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
path: sql/rollback_insert_JpaPdpPolicyDeploymentAudit.sql
relativeToChangelogFile: true
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');
delete from JpaPdpPolicyDeploymentAudit where id in (9,10);
See Some best practices to keep in mind when using Liquibase for more information