Setup
Create a new java project
...
Code Block | ||||
---|---|---|---|---|
| ||||
<?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="adminpolicy"> <createTable<preConditions tableNameonFail="departmentHALT"> <column<sqlCheck nameexpectedResult="id1">SELECT type="int"> count(1) FROM information_schema.tables <constraints primaryKey="true" nullable="false"/> </column> WHERE table_name = 'PDPSTATISTICS' <column name="name" type="varchar(50)"> </sqlCheck> </preConditions> <constraints <sql nullabledbms="false"/>mariadb" endDelimiter=";" splitStatements="true" </column> stripComments="true"> INSERT INTO PDPSTATISTICS (ID, <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> 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> |
...
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
...
mvn liquibase:update
Check you database, table department a new record has been added
Test 2: Rollback update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
table department has been dropped
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
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
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:
| ||||
Code Block | ||||
| ||||
databaseChangeLog: - changeSet: id: 1 author: admin changes: - createTable: tableName: person columns: - column: name: id type: int autoIncrement: true - constraintscreateTable: tableName: JpaPdpPolicyDeploymentAudit primaryKey: true columns: - nullablecolumn: false - columnname: id nametype: firstnameint typeautoIncrement: varchar(50)true - column: constraints: name primaryKey: lastnametrue type nullable: varchar(50)false - constraintscolumn: constraints: nullable: false - columnprimaryKey: true name nullable: statefalse typename: char(2) name - changeSet: id: 2 authortype: adminvarchar(120) changes: - addColumncolumn: tableName: person name: version columns: type: - column:varchar(20) nameconstraints: username typeprimaryKey: varchar(8) true - changeSet: id: 3 authornullable: adminfalse changes: - addLookupTablecolumn: existingTableName: person name: pdpGroup existingColumnName: state newTableNametype: state varchar(255) constraints: newColumnName nullable: idtrue newColumnDataType: char(2) |
Add a new plugin dependency to pom.xml
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.28</version>
</dependency>
mvn package
mvn liquibase:update
3 new entries in the databasechangelog table
- 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
Code Block | ||||
---|---|---|---|---|
| ||||
select * from databasechangelog;
+ | ||||
Code Block | ||||
| ||||
select * from databasechangelog; +----+--------+------------------+---------------------+---------------+----------+------------------------------------+--------+------------------+---------------------+---------------+----------+---------------------------------+---+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | 1policy-2 | admin | dbchangelog.yaml | 2021-05-2024 1615:0704:5849 | 110 | EXECUTED | 8:f022085620a9dd87ae9ca1f93da51fd0f5bd9cec4e2374254cc1985e8e9bfaae | createTable tableName=personJpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | 1865089400 | NULL | | policy-3.8.0 | NULL admin | NULLdbchangelog.yaml | 1523278917 | | 2 | admin | dbchangelog.yaml | 2021-05-2024 1615:0704:5849 | 11 2 | EXECUTED | 8:e3f0f50eb245f66c68ec504db93b67174c9ec93973102be9b6d966cf6bc724b4 | addColumn tableName=personJpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | | | NULL | 3.8.0 | NULL | NULL | 1523278917 | | 3 | admin | dbchangelog.yaml | 2021-05-20 16:07:59 | 3 | EXECUTED | 8:12a8bb9e438047d9332c8aec4f90a76e | addLookupTable existingColumnName=state, existingTableName=person, newColumnName=id, newTableName=state | | NULL | 3.8.0 | NULL | NULL | 1523278917 | +----+--------+---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 person DROP FOREIGN KEY FK_PERSON_STATE
[INFO] DROP TABLE state
[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=21
[INFO] Rolling Back Changeset:dbchangelog.yaml::policy-2::admin
[INFO] ALTER TABLE person DROP COLUMN usernameDROP TABLE JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
[INFO] Rolling Back Changeset:dbchangelog.yaml::1::admin
[INFO] DROP TABLE person
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '1policy-2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 8: Update database with changeLog in YAML format which includes raw SQL
Add dbchangelog-sql.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-4
author: admin
changes:
- sql:
comment: create JpaPdpPolicyDeploymentAudit table
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
);
stripComments: true
- changeSet:
id: policy-5
author: admin
changes:
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit
(id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values
(1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true |
update liquibase.properties
changeLogFile: dbchangelog-sql.yaml
mvn liquibase:update
A new table called JpaPdpPolicyDeploymentAudit with one entry has been added to the database
Test 9: Rollback 2 previous updates
Run mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-5::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 1
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-5' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'
[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-4::admin
[INFO] drop table JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'
Test 10: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs
Add JpaPdpPolicyDeploymentAudit-changelog-1.sql
Code Block | ||||
---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:policy-6
CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
);
-- rollback drop table JpaPdpPolicyDeploymentAudit
|
Add JpaPdpPolicyDeploymentAudit-changelog-2.sql
Code Block | ||||
---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:policy-7
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin');
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin');
-- rollback delete from JpaPdpPolicyDeploymentAudit where id in(1,2); |
Add dbchangelog-master.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- include:
file: JpaPdpPolicyDeploymentAudit-changelog-1.sql
- include:
file: JpaPdpPolicyDeploymentAudit-changelog-2.sql |
Update liquibase.properties → changeLogFile: dbchangelog-master.yaml
Run mvn liquibase:update
[INFO] CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
)
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-1.sql::policy-6::admin ran successfully in 329ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-6', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-1.sql', NOW(), 13, '8:c5d0bc1cbc6a6875136d754002802e6f', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-2.sql::policy-7::admin ran successfully in 15ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-7', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-2.sql', NOW(), 14, '8:5b683eb6e17f177268ed50b9d2829bda', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')
The a new table called customer has been created and 2 records have been inserted.
Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state
Test 11: Update database with changeLog in YAML format which includes raw SQL and tags
Add Add dbchangelog-sql2.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-8
author: admin
changes:
- tagDatabase:
tag: 1.0
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (3, 'TestName3', '1.0.0', 'TestGroup3', 'TestType3', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 3;
- changeSet:
id: policy-9
author: admin
changes:
- tagDatabase:
tag: 1.1
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 4; |
Update liquibase.properties → changeLogFile: dbchangelog-sql2.yaml
Run mvn:liquibase update
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-8', 'admin', 'dbchangelog-sql2.yaml', NOW(), 15, '8:f83378476fe49386b479e3bdaf33c8f0', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.0')
[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 4ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-9', 'admin', 'dbchangelog-sql2.yaml', NOW(), 16, '8:3012add458c8a5395af9f2797779af1e', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.1')
2 new records have been added to the JpaPdpPolicyDeploymentAudit table
Test 12: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.0
[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::policy-9::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-9' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'
Last insert has been deleted
Test 13: Re-run last update
mvn liquibase:update
[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 7ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE,
Only the second insert statement ran.
Test 14: Add labels to changeSets
Add dbchangelog-sql3.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-10
author: admin
labels: release1.0
changes:
- tagDatabase:
tag: 1.2
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 5;
- changeSet:
id: polict-11
author: admin
labels: release1.1
changes:
- tagDatabase:
tag: 1.3
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 6; |
Run mvn liquibase:update -Dliquibase.labels=release1.0
[INFO] Tag '1.2' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::policy-10::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-10', 'admin', 'dbchangelog-sql3.yaml', NOW(), 17, '8:71e4400a0bf77f08304ea92c3a989657', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1870546128', '1.2')
Only changeSet with label release1.0 has been applied
Test 15: Apply second label
Run mvn liquibase:update -Dliquibase.labels=release1.0,release1.1
[INFO] Tag '1.3' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::polict-11::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('polict-11', 'admin', 'dbchangelog-sql3.yaml', NOW(), 18, '8:cab3ec22e42eb1be367a62d0a86c80dc', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1870657144', '1.3')
ChangeSet with label release1.1 has been applied
Test 16: Test contexts
Add dbchangelog-sql4.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-12
author: admin
context: qa
labels: release1.2
changes:
- tagDatabase:
tag: 1.4
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (7, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 7;
- changeSet:
id: policy-13
author: admin
context: test
labels: release1.2
changes:
- tagDatabase:
tag: 1.9
- sql:
comment: insert JpaPdpPolicyDeploymentAudit
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser)
values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
stripComments: true
rollback:
- sql:
sql: delete from JpaPdpPolicyDeploymentAudit where id = 8; |
Run mvn liquibase:update -Dliquibase.contexts=test
[INFO] Tag '1.9' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::policy-13::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-13', 'admin', 'dbchangelog-sql4.yaml', NOW(), 19, '8:2c2fb879e12ca3547180d53dbe43c53a', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '1871103966', '1.9')
Only the changeSet with context test has run
To see more about contexts and labels please visit: Understanding Contexts vs. Labels
Test 17: Test YAML changelog
Add dbchangelog-sql5.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-14
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 20
- createTable:
columns:
- column:
name: PDPGROUPNAME
type: VARCHAR(120)
- column:
name: PDPSUBGROUPNAME
type: VARCHAR(120)
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYFAILCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYDEPLOYSUCCESSCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDFAILCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: POLICYEXECUTEDSUCCESSCOUNT
type: BIGINT
- column:
defaultValueComputed: 'NULL'
name: TIMESTAMP
type: datetime
- column:
constraints:
primaryKey: true
name: ID
type: BIGINT
- column:
constraints:
primaryKey: true
name: name
type: VARCHAR(120)
- column:
constraints:
primaryKey: true
name: version
type: VARCHAR(20)
tableName: pdpstatistics
- changeSet:
id: policy-15
author: admin
label: release1.2
changes:
- tagDatabase:
tag: 21
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 3
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 4
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 5
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 6
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 7
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 8
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 9
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics
- insert:
columns:
- column:
name: PDPGROUPNAME
value: group
- column:
name: PDPSUBGROUPNAME
value: subgroup
- column:
name: POLICYDEPLOYCOUNT
valueNumeric: 2
- column:
name: POLICYDEPLOYFAILCOUNT
valueNumeric: 1
- column:
name: POLICYDEPLOYSUCCESSCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDCOUNT
valueNumeric: 2
- column:
name: POLICYEXECUTEDFAILCOUNT
valueNumeric: 1
- column:
name: POLICYEXECUTEDSUCCESSCOUNT
valueNumeric: 1
- column:
name: TIMESTAMP
valueDate: NOW()
- column:
name: ID
valueNumeric: 10
- column:
name: name
value: name2
- column:
name: version
value: 0.0.0
tableName: pdpstatistics |
Update liquibase.properties to point to dbchangelog-sql5.yaml
Run mvn liquibase:update
[INFO] Marking ChangeSet: dbchangelog-sql5.yaml::policy-14::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql5.yaml : Not precondition failed
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-14', 'admin', 'dbchangelog-sql5.yaml', NOW(), 20, '8:09312d85176b6fb93520e5d8f9702e96', 'tagDatabase', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1931263740', '20')
[INFO] Tag '21' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 3, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 4, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 5, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 6, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 7, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 8, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 9, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 10, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] ChangeSet dbchangelog-sql5.yaml::policy-15::admin ran successfully in 21ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-15', 'admin', 'dbchangelog-sql5.yaml', NOW(), 21, '8:70aa4c084fd72dba46a76ce8fe5b5732', 'tagDatabase; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tab...', '', 'EXECUTED', NULL, NULL, '3.8.0', '1931263740', '21')
mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog-sql5.yaml::policy-15::admin
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.454 s
[INFO] Finished at: 2021-05-25T09:33:29+01:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.8.0:rollback (default-cli) on project LiquibaseMariadbp: Error setting up or running Liquibase: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.InsertDataChange created -> [Help 1]
Cannot rollback.
Test 18: Test YAML changelog with embedded SQL
Add dbchangelog-sql6.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-16
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 22
- sql:
comment: create pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: CREATE TABLE pdpstatistics (
PDPGROUPNAME VARCHAR(120) NULL,
PDPSUBGROUPNAME VARCHAR(120) NULL,
POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL,
POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL,
POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL,
POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
ID BIGINT NOT NULL,
name VARCHAR(120) NOT NULL,
version VARCHAR(20) NOT NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version));
stripComments: true
- changeSet:
id: policy-17
author: admin
label: release1.2
preConditions:
- onFail: MARK_RAN
- tableExists:
tableName: pdpstatistics
changes:
- tagDatabase:
tag: 23
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
- sql:
comment: insert pdpstatistics
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');
rollback:
- sql:
sql: delete from pdpstatistics where id in (11,12,13,14,15,16,17,18,19,20); |
Update liquibase.properties to point to dbchangelog-sql6.yaml
Run mvn liquibase:update
[INFO] Marking ChangeSet: dbchangelog-sql6.yaml::policy-16::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql6.yaml : Not precondition failed
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-16', 'admin', 'dbchangelog-sql6.yaml', NOW(), 22, '8:9d2fcf472209118ad3760ca621ab787e', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1934277203', '22')
[INFO] Tag '23' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql6.yaml::policy-17::admin ran successfully in 52ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-17', 'admin', 'dbchangelog-sql6.yaml', NOW(), 23, '8:0dfbbf124f39d3e3ae9a0b8a41fb62d0', 'tagDatabase; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1934277203', '23')
Test 19: Test rollup table creation
Add dbchangelog-sql7.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- changeSet:
id: policy-18
author: admin
label: release1.3
preConditions:
- onFail: MARK_RAN
- not:
- tableExists:
tableName: pdpstatistics_rollup
changes:
- tagDatabase:
tag: 24
- sql:
comment: create pdpstatistics_rollup
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: CREATE TABLE pdpstatistics_rollup (
name VARCHAR(120) NOT NULL,
version VARCHAR(20) NOT NULL,
PDPGROUPNAME VARCHAR(120) NULL,
PDPSUBGROUPNAME VARCHAR(120) NULL,
YEAR BIGINT NOT NULL,
MONTH BIGINT NOT NULL,
AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL,
AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));
rollback:
- sql:
sql: drop table pdpstatistics_rollup
- changeSet:
id: policy-19
author: admin
label: release1.3
preConditions:
- onFail: MARK_RAN
- tableExists:
tableName: pdpstatistics_rollup
changes:
- tagDatabase:
tag: 25
- sql:
comment: insert pdpstatistics_rollup
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
stripComments: true
sql: INSERT INTO pdpstatistics_rollup (
name,
version,
PDPGROUPNAME,
PDPSUBGROUPNAME,
YEAR,
MONTH,
AVG_POLICYDEPLOYCOUNT,
AVG_POLICYDEPLOYFAILCOUNT,
AVG_POLICYDEPLOYSUCCESSCOUNT,
AVG_POLICYEXECUTEDCOUNT,
AVG_POLICYEXECUTEDFAILCOUNT,
AVG_POLICYEXECUTEDSUCCESSCOUNT
)
select name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
EXTRACT(YEAR FROM timestamp) as YEAR,
EXTRACT(MONTH FROM timestamp) as MONTH,
avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT,
avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT,
avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT,
avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT,
avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT,
avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT
from pdpstatistics
group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
EXTRACT(YEAR FROM timestamp),
EXTRACT(MONTH FROM timestamp);
rollback:
- sql:
sql: delete from pdpstatistics_rollup |
Update liquibase.properties to point to dbchangelog-sql7.yaml
Run mvn liquibase:update
[INFO] Tag '24' applied to database
[INFO] CREATE TABLE pdpstatistics_rollup ( name VARCHAR(120) NOT NULL, version VARCHAR(20) NOT NULL, PDPGROUPNAME VARCHAR(120) NULL, PDPSUBGROUPNAME VARCHAR(120) NULL, YEAR BIGINT NOT NULL, MONTH BIGINT NOT NULL, AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-18::admin ran successfully in 34ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-18', 'admin', 'dbchangelog-sql7.yaml', NOW(), 24, '8:3c8089ad83c121f40c403ccf596b6196', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '24')
[INFO] Tag '25' applied to database
[INFO] INSERT INTO pdpstatistics_rollup ( name, version, PDPGROUPNAME, PDPSUBGROUPNAME, YEAR, MONTH, AVG_POLICYDEPLOYCOUNT, AVG_POLICYDEPLOYFAILCOUNT, AVG_POLICYDEPLOYSUCCESSCOUNT, AVG_POLICYEXECUTEDCOUNT, AVG_POLICYEXECUTEDFAILCOUNT, AVG_POLICYEXECUTEDSUCCESSCOUNT ) select name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp) as YEAR, EXTRACT(MONTH FROM timestamp) as MONTH, avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT, avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT, avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT, avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT, avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT, avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT from pdpstatistics group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp), EXTRACT(MONTH FROM timestamp);
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-19::admin ran successfully in 20ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-19', 'admin', 'dbchangelog-sql7.yaml', NOW(), 25, '8:23ed645fde97a2c2a7306e1e47f63646', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '25')
Code Block | ||||
---|---|---|---|---|
| ||||
MariaDB [policy]> select * from pdpstatistics_rollup;
+-------+---------+--------------+-----------------+------+-------+-----------------------+---------------------------+------------------------------+-------------------------+-----------------------------+--------------------------------+
| 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: 0databaseChangeLog: - changeSet: id: sql-yaml-1 author: admin changes: - sql: comment: create person table dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql: select count(*) createfrom information_schema.table_constraints personwhere (name varchar(30))constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName'; changes: stripComments: true - changeSettagDatabase: id: sql-yaml-2 authortag: admin 27 changes: - sql: comment: add insertconstraint nametoscaservicetemplate; dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatementsendDelimiter: true\nGO sqlsplitStatements: insert into person (name) values ('Liquibase Test')true stripComments: true |
update liquibase.properties
changeLogFile: dbchangelog-sql.yaml
mvn liquibase:update
A new table called person with one entry has been added to the database
Test 9: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs
Add customer-table-changelog-1.sql
Code Block | ||||
---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:cust-1
create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
);
-- rollback drop table customer; |
Add customer-table-changelog-2.sql
Code Block | ||||
---|---|---|---|---|
| ||||
--liquibase formatted sql
--changeset admin:cust-2
insert into customer(id, name, role) values (1, 'Robert', 'Software Developer');
insert into customer(id, name, role) values (2, 'John', 'Dev Ops');
-- rollback delete from customer where id in(1,2); |
Add dbchangelog-master.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog:
- include:
file: customer-table-changelog-1.sql
- include:
file: customer-table-changelog-2.sql |
Update liquibase.properties → changeLogFile: dbchangelog-master.yaml
Run mvn liquibase:update
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 * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
)
[INFO] Custom SQL executed
[INFO] ChangeSet customer-table-changelog-1.sql::cust-1::admin ran successfully in 306ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-120', 'admin', 'customer-table-changelog-1.sqldbchangelog-sql8.yaml', NOW(), 326, '8:6952c30a86e91b5f32a65e313f52b64b2954c7c4f467425b3ea5d35bf45ef4a9', 'tagDatabase; sql', '', 'EXECUTEDMARK_RAN', NULL, NULL, '3.8.0', '1599263670')
[INFO] insert into customer(id, name, role) values (1, 'Robert', 'Software Developer')
[INFO] insert into customer(id, name, role) values (2, 'John', 'Dev Ops', '1942893174', '25')
[INFO] Custom SQL executedselect count(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and version='1.0.0'
[INFO] Marking ChangeSet customer-table-changelog-2.sql::cust-2::admin ran successfully in 11ms: dbchangelog-sql8.yaml::policy-21::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : SQL Precondition failed. Expected '0' got '1'
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-221', 'admin', 'customer-table-changelog-2.sqldbchangelog-sql8.yaml', NOW(), 427, '8:52f9c3c97a509dc22f109be204fc8dfdd86e5dad1fce9bb955d7cb0558b29707', 'tagDatabase; sql', '', 'EXECUTEDMARK_RAN', NULL, NULL, '3.8.0', '1599263670')
The a new table called customer has been created and 2 records have been inserted.
Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state
Test 10: Update database with changeLog in YAML format which includes raw SQL and tags
Add Add dbchangelog-sql2.yaml
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
| ||||
Code Block | ||||
| ||||
databaseChangeLog: - changeSet: id: cust-3 author: admin changes: - tagDatabase: tag: 1.2 - sql: comment: insert customer dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql: insert into customer (id, name, role) values (3, 'Pete', 'Payroll') stripCommentsauthor: trueadmin label: release1.4 rollbackpreConditions: - sqlonFail: MARK_RAN - sqlnot: delete from customer where id = 3; - tableExists: changeSet: id: cust-4 authortableName: toscaservicetemplate admin changes: - tagDatabase: tag: 28 1.3 - sqlsqlFile: comment: insertcreate customertoscaservicetemplate dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql: insert into customer (id, name, role) endDelimiter: \nGO values (4, 'James', 'Payroll')splitStatements: true stripComments: true rollback: - sql: path: sql/create_table_toscaservicetemplate.sql sqlrelativeToChangelogFile: deletetrue from customer where id- = 4;changeSet: id: policy-24 |
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 ('cust-3', 'admin', 'dbchangelog-sql2.yaml', NOW(), 5, '8:a660ebc91d2fa7b7e4c2287bf1725707', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599649760', '1.2')
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-4', 'admin', 'dbchangelog-sql2.yaml', NOW(), 6, '8:cb8dbdef98c88b2e99d42a6591f88498', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599649760', '1.3')
2 new records have been added to the customer table
Test 11: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.2
[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::cust-4::admin
[INFO] delete from customer where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'cust-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'
Last insert has been deleted
Test 12: Re-run last update
mvn liquibase:update
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3ms
Only the second insert statement ran.
Test 13: Add labels to changeSets
Add dbchangelog-sql3.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
databaseChangeLog: 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 cust-5 - sqlCheck: expectedResult: 0 author: admin labels: release1.0 sql: select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName'; changes: - tagDatabase: tag: 1.628 - sqlsqlFile: comment: add insertconstraint customertoscaservicetemplate; dbms: '!h2, oracle, mysql, mariadb' endDelimiter: \nGO splitStatements: true sql stripComments: insert into customer (id, name, role) values (5, 'Jane', 'Payroll') true path: sql/add_toscaservicetemplate_constraint.sql stripComments relativeToChangelogFile: true 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 rollback: - sql: - sql: delete from customer where id = 5; - changeSet: changeSet: id: custpolicy-626 author: admin labels label: release1.15 changespreConditions: - tagDatabaseonFail: HALT - tagtableExists: 1.7 - sqltableName: JpaPdpPolicyDeploymentAudit comment: insert customer changes: - dbmstagDatabase: '!h2, oracle, mysql, mariadb' tag: 31 endDelimiter: \nGO - splitStatementssqlFile: true sqlcomment: insert into customer (id, name, role) JpaPdpPolicyDeploymentAudit dbms: '!h2, valuesoracle, (6, 'Jean' mysql, mariadb'Dev OPS') stripComments: true endDelimiter: \nGO rollbacksplitStatements: true - sql: stripComments: true sql: delete from customer where id = 6; |
Add labels to liquibase.properties
labels: release1.0
Run mvn liquibase:update
[INFO] Tag '1.6' applied to database
[INFO] insert into customer (id, name, role) values (5, 'Jane', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-5::admin ran successfully in 3ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-5', 'admin', 'dbchangelog-sql3.yaml', NOW(), 7, '8:ce6fde026140f9831fa66b60662e842d', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1851627465', '1.6')
Only changeSet with label release1.0 has been applied
Test 14: Apply second label
update liquibase.properties
labels: release1.0, release1.1
Run mvn liquibase:update
[INFO] Tag '1.7' applied to database
[INFO] insert into customer (id, name, role) values (6, 'Jean', 'Dev OPS')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-6::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-6', 'admin', 'dbchangelog-sql3.yaml', NOW(), 8, '8:96a8060554eef1d1f4cf4f43dd2c376c', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1852080035', '1.7')
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 informationChangeSet with label release1.1 has been applied