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: 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
|
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
| 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 |
...
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
...
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
...
2 new records have been added to the JpaPdpPolicyDeploymentAudit table
Test 12: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.0
...
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
...
Only changeSet with label release1.0 has been applied
Test 15: Apply second label
Run mvn liquibase:update -Dliquibase.labels=release1.0,release1.1
...
ChangeSet with label release1.1 has been applied
Test 16: Test contexts
Add dbchangelog-sql4.yaml
...
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] 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;
+-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+-----------------------------+--------------------------------+
| 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
| 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
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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
We can use all the liquibase functions with SQL and include rollback:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
|
| 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 information