/
Liquibase changeLog Testing

Liquibase changeLog Testing

Owned by Kevin Timoney
Last updated: May 28, 2021

Setup

Create a new java project

Add liquibase.properties

liquibase.properties
 changeLogFile: dbchangelog.xml
 url: jdbc:mariadb://localhost:3306/policy
 username: policy
 password: *****


Add dbchangelog.xml

dbchangelog.xml
<?xml version="1.0" encoding="UTF-8"?>
 <databaseChangeLog  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
                    xmlns:pro="http://www.liquibase.org/xml/ns/pro"  
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
                    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">  
 

     <changeSet  id="policy-1"  author="policy"> 
         <preConditions  onFail="HALT">  
             <sqlCheck  expectedResult="1">SELECT count(1) FROM  information_schema.tables 
                                           WHERE table_name = 'PDPSTATISTICS'
             </sqlCheck>  
         </preConditions>  
		 <sql dbms="mariadb"
		      endDelimiter=";"
		      splitStatements="true"
		      stripComments="true">
		    INSERT INTO PDPSTATISTICS 
		    (ID, NAME, VERSION, PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, 
		    POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, 
		    POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP) 
		    VALUES  (3, 'TestName', '1.0.0', 'TestGroup', 'TestSubGroup', 1, 1, 1, 1, 1, 1, now()) 
		 </sql> 
		 <rollback>
		   DELETE FROM PDPSTATISTICS WHERE ID=3
		</rollback>
    </changeSet>  
 </databaseChangeLog>


Add pom.xml

pom.xml
 <project  xmlns="http://maven.apache.org/POM/4.0.0"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  
   <modelVersion>4.0.0</modelVersion>  
 
   <groupId>com.liquibase.mariadb.app</groupId>  
   <artifactId>LiquibaseMariadbp</artifactId>  
   <version>1.0-SNAPSHOT</version>  
   <build>  
       <pluginManagement>  
           <plugins>  
               <plugin>  
                   <groupId>org.liquibase</groupId>  
                   <artifactId>liquibase-maven-plugin</artifactId>  
                   <version>3.8.0</version>  
                   <configuration>  
                       <propertyFile>liquibase.properties</propertyFile>  
                   </configuration>  
                   <dependencies>  
					<dependency>
					    <groupId>org.mariadb.jdbc</groupId>
					    <artifactId>mariadb-java-client</artifactId>
					    <version>2.7.3</version>
					</dependency>					
				   <dependency>  
                       <groupId>org.hibernate</groupId>  
                       <artifactId>hibernate-core</artifactId>  
                       <version>5.4.6.Final</version>  
                   </dependency>  
                 </dependencies>  
               </plugin>  
           </plugins>  
       </pluginManagement>  
   </build>  
 </project>

Test 1: Update database with changeLog in XML format

From the command line in the project directory

mvn package

mvn liquibase:update

Check you database, a new record has been added to pdpstatistics

[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] SELECT count(1) FROM information_schema.tables
WHERE table_name = 'PDPSTATISTICS'
[INFO] INSERT INTO PDPSTATISTICS
(ID, NAME, VERSION, PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT,
POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT,
POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP)
VALUES (3, 'TestName', '1.0.0', 'TestGroup', 'TestSubGroup', 1, 1, 1, 1, 1, 1, now())
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog.xml::policy-1::policy ran successfully in 7ms

Test 2: Rollback update

mvn liquibase:rollback -Dliquibase.rollbackCount=1

new record has been removed has been dropped

[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] Rolling Back Changeset:dbchangelog.xml::policy-1::policy
[INFO] DELETE FROM PDPSTATISTICS WHERE ID=3
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-1' AND AUTHOR = 'policy' AND FILENAME = 'dbchangelog.xml'

Test 3: Generate database dump chageLog in YAML format

Add the following 2 lines to liquibase.properties

outputChangeLogFile: dbchangelog.mariadb.yaml
diffTypes: catalog,tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data,storedprocedure


mvn liquibase:generateChangeLog

The file dbchangelog.mariadb.yaml will contain the DDL for your database in YAML format

Test 4: Generate database dump chageLog in SQL format

change outputChangeLogFile: dbchangelog.mariadb.yaml to outputChangeLogFile: dbchangelog.mariadb.sql

The file dbchangelog.mariadb.sql will contain the DDL for your database in SQL format

Test 5: Update database with changeLog in YAML format

Add dbchangelog.yaml

dbchangelog.yaml
databaseChangeLog:

  -  changeSet:
      id:  policy-2
      author:  admin
      changes:
        -  createTable:
            tableName:  JpaPdpPolicyDeploymentAudit
            columns:
              -  column:
                  name:  id
                  type:  int
                  autoIncrement:  true
                  constraints:
                    primaryKey:  true
                    nullable:  false
              -  column:
                  constraints:
                    primaryKey:  true
                    nullable:  false
                  name:  name
                  type:  varchar(120)
              -  column:
                  name:  version
                  type:  varchar(20)
                  constraints:
                    primaryKey:  true
                    nullable:  false
              -  column:
                  name:  pdpGroup
                  type:  varchar(255)
                  constraints:
                    nullable:  true
              -  column:
                  name:  pdpType
                  type:  varchar(255)
                  constraints:
                    nullable:  true    
              -  column:
                  name:  action
                  type:  int
                  constraints:
                    nullable:  true  
              -  column:
                  name:  timeStamp
                  type:  datetime
                  constraints:
                    nullable:  true                       

  -  changeSet:
      id:  policy-3
      author:  admin
      changes:
        -  addColumn:
            tableName:  JpaPdpPolicyDeploymentAudit
            columns:
              -  column:
                  name:  changedByUser
                  type:  varchar(255)
                  constraints:
                    nullable:  true


Add a new plugin dependency to pom.xml

<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.28</version>
</dependency>


mvn package

mvn liquibase:update

2 new entries in the databasechangelog table

databasechangelog 
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

dbchangelog-sql.yaml
 databaseChangeLog:
  -  changeSet:  
       id:  policy-4 
       author:  admin  
       changes:  
        -  sql:  
            comment:  create JpaPdpPolicyDeploymentAudit table  
            dbms:  '!h2,  oracle,  mysql, mariadb'  
            endDelimiter:  \nGO  
            splitStatements:  true  
            sql:  CREATE TABLE JpaPdpPolicyDeploymentAudit (
                                 ID BIGINT NOT NULL, 
                                 NAME VARCHAR(120) NOT NULL, 
                                 VERSION VARCHAR(20) NOT NULL,
                                 PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
                                 PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
                                 ACTION BIGINT DEFAULT NULL NULL, 
                                 TIMESTAMP datetime DEFAULT NULL NULL,  
                                 CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
                   CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
                   );             
            stripComments:  true
  -  changeSet:  
       id:  policy-5
       author:  admin  
       changes:  
        -  sql:  
            comment:  insert JpaPdpPolicyDeploymentAudit 
            dbms:  '!h2,  oracle,  mysql, mariadb'  
            endDelimiter:  \nGO  
            splitStatements:  true  
            sql:  insert into JpaPdpPolicyDeploymentAudit 
                  (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
                  values 
                  (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')  
            stripComments:  true


update liquibase.properties

changeLogFile: dbchangelog-sql.yaml


mvn liquibase:update

A new table called JpaPdpPolicyDeploymentAudit with one entry has been added to the database

Test 9: Rollback 2 previous updates

Run mvn liquibase:rollback -Dliquibase.rollbackCount=2

[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-5::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 1
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-5' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'
[INFO] Rolling Back Changeset:dbchangelog-sql.yaml::policy-4::admin
[INFO] drop table JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql.yaml'

Test 10: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs

Add JpaPdpPolicyDeploymentAudit-changelog-1.sql

JpaPdpPolicyDeploymentAudit-changelog-1.sql
--liquibase formatted sql
	
--changeset admin:policy-6		
CREATE TABLE JpaPdpPolicyDeploymentAudit (
                                 ID BIGINT NOT NULL, 
                                 NAME VARCHAR(120) NOT NULL, 
                                 VERSION VARCHAR(20) NOT NULL,
                                 PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
                                 PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
                                 ACTION BIGINT DEFAULT NULL NULL, 
                                 TIMESTAMP datetime DEFAULT NULL NULL,  
                                 CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
                   CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
                   );

-- rollback drop table JpaPdpPolicyDeploymentAudit

Add JpaPdpPolicyDeploymentAudit-changelog-2.sql

JpaPdpPolicyDeploymentAudit-changelog-2.sql
--liquibase formatted sql
			
--changeset admin:policy-7
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin');
insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin');

-- rollback delete from JpaPdpPolicyDeploymentAudit where id in(1,2);

Add dbchangelog-master.yaml

dbchangelog-master.yaml
databaseChangeLog:
- include:
    file:  JpaPdpPolicyDeploymentAudit-changelog-1.sql  
- include:
    file:  JpaPdpPolicyDeploymentAudit-changelog-2.sql

Update liquibase.properties →  changeLogFile: dbchangelog-master.yaml

Run mvn liquibase:update

[INFO] CREATE TABLE JpaPdpPolicyDeploymentAudit (
ID BIGINT NOT NULL,
NAME VARCHAR(120) NOT NULL,
VERSION VARCHAR(20) NOT NULL,
PDPGROUP VARCHAR(255) DEFAULT NULL NULL,
PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
ACTION BIGINT DEFAULT NULL NULL,
TIMESTAMP datetime DEFAULT NULL NULL,
CHANGEDBYUSER VARCHAR(255) DEFAULT NULL NULL,
CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, NAME, VERSION)
)
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-1.sql::policy-6::admin ran successfully in 329ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-6', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-1.sql', NOW(), 13, '8:c5d0bc1cbc6a6875136d754002802e6f', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet JpaPdpPolicyDeploymentAudit-changelog-2.sql::policy-7::admin ran successfully in 15ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('policy-7', 'admin', 'JpaPdpPolicyDeploymentAudit-changelog-2.sql', NOW(), 14, '8:5b683eb6e17f177268ed50b9d2829bda', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1868752479')

The a new table called customer has been created and 2 records have been inserted.

Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state


Test 11: Update database with changeLog in YAML format which includes raw SQL and tags

Add Add dbchangelog-sql2.yaml

dbchangelog-sql.yaml
databaseChangeLog:
-  changeSet:  
     id:  policy-8
     author:  admin  
     changes:  
     - tagDatabase:  
        tag:  1.0    
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (3, 'TestName3', '1.0.0', 'TestGroup3', 'TestType3', 1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 3;    
-  changeSet:  
     id:  policy-9
     author:  admin  
     changes:  
     - tagDatabase:  
        tag:  1.1   
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 4;


Update liquibase.properties →  changeLogFile: dbchangelog-sql2.yaml

Run mvn:liquibase update

[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-8', 'admin', 'dbchangelog-sql2.yaml', NOW(), 15, '8:f83378476fe49386b479e3bdaf33c8f0', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.0')
[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 4ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-9', 'admin', 'dbchangelog-sql2.yaml', NOW(), 16, '8:3012add458c8a5395af9f2797779af1e', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1869831922', '1.1')

2 new records have been added to the JpaPdpPolicyDeploymentAudit table

Test 12: Rollback update using tag

mvn liquibase:rollback -Dliquibase.rollbackTag=1.0

[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::policy-9::admin
[INFO] delete from JpaPdpPolicyDeploymentAudit where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-9' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'

Last insert has been deleted


Test 13: Re-run last update

mvn liquibase:update

[INFO] Tag '1.1' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::policy-9::admin ran successfully in 7ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, 

Only the second insert statement ran.


Test 14: Add labels to changeSets

Add dbchangelog-sql3.yaml

dbchangelog-sql2.yaml
databaseChangeLog:
-  changeSet:  
     id:  policy-10
     author:  admin 
     labels: release1.0
     changes:  
     - tagDatabase:  
        tag:  1.2   
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')   
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 5;    
-  changeSet:  
     id:  polict-11
     author:  admin  
     labels: release1.1
     changes:  
     - tagDatabase:  
        tag:  1.3    
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 6;


Run mvn liquibase:update -Dliquibase.labels=release1.0

[INFO] Tag '1.2' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (5, 'TestName5', '1.0.0', 'TestGroup5', 'TestType5', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::policy-10::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-10', 'admin', 'dbchangelog-sql3.yaml', NOW(), 17, '8:71e4400a0bf77f08304ea92c3a989657', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1870546128', '1.2')

Only changeSet with label release1.0 has been applied


Test 15: Apply second label

Run mvn liquibase:update -Dliquibase.labels=release1.0,release1.1


[INFO] Tag '1.3' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (6, 'TestName6', '1.0.0', 'TestGroup6', 'TestType6', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::polict-11::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('polict-11', 'admin', 'dbchangelog-sql3.yaml', NOW(), 18, '8:cab3ec22e42eb1be367a62d0a86c80dc', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1870657144', '1.3')


ChangeSet with label release1.1 has been applied


Test 16: Test contexts

Add dbchangelog-sql4.yaml

dbchangelog-sql4.yaml
databaseChangeLog:
-  changeSet:  
     id:  policy-12
     author:  admin 
     context: qa
     labels: release1.2
     changes:  
     - tagDatabase:  
        tag:  1.4 
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (7, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 7;    
-  changeSet:  
     id:  policy-13
     author:  admin  
     context: test   
     labels: release1.2
     changes:  
     - tagDatabase:  
        tag:  1.9    
     -  sql:  
         comment:  insert JpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')   
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 8;

Run mvn liquibase:update -Dliquibase.contexts=test

[INFO] Tag '1.9' applied to database
[INFO] insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (8, 'TestName', '1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::policy-13::admin ran successfully in 6ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-13', 'admin', 'dbchangelog-sql4.yaml', NOW(), 19, '8:2c2fb879e12ca3547180d53dbe43c53a', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '1871103966', '1.9')

Only the changeSet with context test has run


To see more about contexts and labels please visit: Understanding Contexts vs. Labels

Test 17: Test YAML changelog

Add dbchangelog-sql5.yaml


dbchangelog-sql5.yaml
databaseChangeLog:
  -  changeSet:  
       id:  policy-14 
       author:  admin  
       label: release1.2
       preConditions:
         - onFail: MARK_RAN
         - not:
           - tableExists:
               tableName: pdpstatistics
       changes:
       - tagDatabase:  
          tag:  20         
  - createTable:
      columns:
      - column:
          name: PDPGROUPNAME
          type: VARCHAR(120)
      - column:
          name: PDPSUBGROUPNAME
          type: VARCHAR(120)
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYDEPLOYCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYDEPLOYFAILCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYDEPLOYSUCCESSCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYEXECUTEDCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYEXECUTEDFAILCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: POLICYEXECUTEDSUCCESSCOUNT
          type: BIGINT
      - column:
          defaultValueComputed: 'NULL'
          name: TIMESTAMP
          type: datetime
      - column:
          constraints:
            primaryKey: true
          name: ID
          type: BIGINT
      - column:
          constraints:
            primaryKey: true
          name: name
          type: VARCHAR(120)
      - column:
          constraints:
            primaryKey: true
          name: version
          type: VARCHAR(20)
      tableName: pdpstatistics  
      
  -  changeSet:  
       id:  policy-15
       author:  admin  
       label: release1.2
       changes:
       - tagDatabase:  
          tag:  21         
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 3
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 4
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 5
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 6
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 7
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 8
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 9
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics
       - insert:
            columns:
            - column:
                name: PDPGROUPNAME
                value: group
            - column:
                name: PDPSUBGROUPNAME
                value: subgroup
            - column:
                name: POLICYDEPLOYCOUNT
                valueNumeric: 2
            - column:
                name: POLICYDEPLOYFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYDEPLOYSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDCOUNT
                valueNumeric: 2
            - column:
                name: POLICYEXECUTEDFAILCOUNT
                valueNumeric: 1
            - column:
                name: POLICYEXECUTEDSUCCESSCOUNT
                valueNumeric: 1
            - column:
                name: TIMESTAMP
                valueDate: NOW()
            - column:
                name: ID
                valueNumeric: 10
            - column:
                name: name
                value: name2
            - column:
                name: version
                value: 0.0.0
            tableName: pdpstatistics

Update liquibase.properties to point to dbchangelog-sql5.yaml

Run mvn liquibase:update


[INFO] Marking ChangeSet: dbchangelog-sql5.yaml::policy-14::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql5.yaml : Not precondition failed

[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-14', 'admin', 'dbchangelog-sql5.yaml', NOW(), 20, '8:09312d85176b6fb93520e5d8f9702e96', 'tagDatabase', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1931263740', '20')
[INFO] Tag '21' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 3, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 4, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 5, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 6, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 7, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 8, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 9, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 10, 'name2', '0.0.0')
[INFO] New row inserted into pdpstatistics
[INFO] ChangeSet dbchangelog-sql5.yaml::policy-15::admin ran successfully in 21ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-15', 'admin', 'dbchangelog-sql5.yaml', NOW(), 21, '8:70aa4c084fd72dba46a76ce8fe5b5732', 'tagDatabase; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tableName=pdpstatistics; insert tab...', '', 'EXECUTED', NULL, NULL, '3.8.0', '1931263740', '21')


mvn liquibase:rollback -Dliquibase.rollbackCount=2


[INFO] Rolling Back Changeset:dbchangelog-sql5.yaml::policy-15::admin
[INFO] Successfully released change log lock
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.454 s
[INFO] Finished at: 2021-05-25T09:33:29+01:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.8.0:rollback (default-cli) on project LiquibaseMariadbp: Error setting up or running Liquibase: liquibase.exception.RollbackImpossibleException: No inverse to liquibase.change.core.InsertDataChange created -> [Help 1]


Cannot rollback.


Test 18: Test YAML changelog with embedded SQL

Add dbchangelog-sql6.yaml


dbchangelog-sql6.yaml
databaseChangeLog:
  -  changeSet:  
       id:  policy-16
       author:  admin  
       label: release1.2
       preConditions:
         - onFail: MARK_RAN
         - not:
           - tableExists:
               tableName: pdpstatistics
       changes:
       - tagDatabase:  
          tag:  22        
       -  sql:  
           comment:  create pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           sql:  CREATE TABLE pdpstatistics (
                              PDPGROUPNAME VARCHAR(120) NULL, 
                              PDPSUBGROUPNAME VARCHAR(120) NULL, 
                              POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, 
                              POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, 
                              POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, 
                              POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, 
                              POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, 
                              POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, 
                              TIMESTAMP datetime DEFAULT NULL NULL, 
                              ID BIGINT NOT NULL, 
                              name VARCHAR(120) NOT NULL, 
                              version VARCHAR(20) NOT NULL, 
                              CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version));  
           stripComments:  true            
  -  changeSet:  
       id:  policy-17 
       author:  admin  
       label: release1.2
       preConditions:
         - onFail: MARK_RAN
         - tableExists:
             tableName: pdpstatistics
       changes:
       - tagDatabase:  
          tag:  23        
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');   
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
       -  sql:  
           comment:  insert pdpstatistics 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');                                                                                                              
       rollback:    
       - sql:
           sql: delete from pdpstatistics where id in (11,12,13,14,15,16,17,18,19,20);


Update liquibase.properties to point to dbchangelog-sql6.yaml

Run mvn liquibase:update


[INFO] Marking ChangeSet: dbchangelog-sql6.yaml::policy-16::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql6.yaml : Not precondition failed

[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-16', 'admin', 'dbchangelog-sql6.yaml', NOW(), 22, '8:9d2fcf472209118ad3760ca621ab787e', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1934277203', '22')
[INFO] Tag '23' applied to database
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 11, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 12, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 13, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 14, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 15, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 16, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 17, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 18, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 19, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] INSERT INTO pdpstatistics (PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT, POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT, POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP, ID, name, version) VALUES ('group', 'subgroup', 2, 1, 1, 2, 1, 1, NOW(), 20, 'name2', '0.0.0');
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql6.yaml::policy-17::admin ran successfully in 52ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-17', 'admin', 'dbchangelog-sql6.yaml', NOW(), 23, '8:0dfbbf124f39d3e3ae9a0b8a41fb62d0', 'tagDatabase; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1934277203', '23')



Test 19: Test rollup table creation

Add dbchangelog-sql7.yaml


dbchangelog-sql7.yaml
databaseChangeLog:
  -  changeSet:  
       id:  policy-18
       author:  admin  
       label: release1.3
       preConditions:
         - onFail: MARK_RAN
         - not:
           - tableExists:
               tableName: pdpstatistics_rollup
       changes:
       - tagDatabase:  
          tag:  24        
       -  sql:  
           comment:  create pdpstatistics_rollup 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true               
           sql:  CREATE TABLE pdpstatistics_rollup (
                              name VARCHAR(120) NOT NULL, 
                              version VARCHAR(20) NOT NULL, 
                              PDPGROUPNAME VARCHAR(120) NULL, 
                              PDPSUBGROUPNAME VARCHAR(120) NULL, 
                              YEAR BIGINT NOT NULL,   
                              MONTH BIGINT NOT NULL,                                
                              AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, 
                              AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, 
                              AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, 
                              AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, 
                              AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, 
                              AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, 
                              CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));  
     rollback:    
       - sql:
           sql: drop table pdpstatistics_rollup    
  -  changeSet:  
       id:  policy-19 
       author:  admin  
       label: release1.3
       preConditions:
         - onFail: MARK_RAN
         - tableExists:
             tableName: pdpstatistics_rollup
       changes:
       - tagDatabase:  
          tag:  25        
       -  sql:  
           comment:  insert pdpstatistics_rollup 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO pdpstatistics_rollup (
                              name, 
                              version, 
                              PDPGROUPNAME, 
                              PDPSUBGROUPNAME, 
                              YEAR,   
                              MONTH,                                
                              AVG_POLICYDEPLOYCOUNT, 
                              AVG_POLICYDEPLOYFAILCOUNT, 
                              AVG_POLICYDEPLOYSUCCESSCOUNT, 
                              AVG_POLICYEXECUTEDCOUNT, 
                              AVG_POLICYEXECUTEDFAILCOUNT, 
                              AVG_POLICYEXECUTEDSUCCESSCOUNT
                              ) 
                select name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
                       EXTRACT(YEAR FROM timestamp) as YEAR, 
                       EXTRACT(MONTH FROM timestamp) as MONTH,                 
                       avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT, 
                       avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT, 
                       avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT, 
                       avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT, 
                       avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT, 
                       avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT                      
                       from pdpstatistics 
                       group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME,
                       EXTRACT(YEAR FROM timestamp), 
                       EXTRACT(MONTH FROM timestamp);  
     rollback:    
       - sql:
           sql: delete from pdpstatistics_rollup


Update liquibase.properties to point to dbchangelog-sql7.yaml

Run mvn liquibase:update


[INFO] Tag '24' applied to database
[INFO] CREATE TABLE pdpstatistics_rollup ( name VARCHAR(120) NOT NULL, version VARCHAR(20) NOT NULL, PDPGROUPNAME VARCHAR(120) NULL, PDPSUBGROUPNAME VARCHAR(120) NULL, YEAR BIGINT NOT NULL, MONTH BIGINT NOT NULL, AVG_POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, AVG_POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (YEAR, MONTH, name, version));
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-18::admin ran successfully in 34ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-18', 'admin', 'dbchangelog-sql7.yaml', NOW(), 24, '8:3c8089ad83c121f40c403ccf596b6196', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '24')
[INFO] Tag '25' applied to database
[INFO] INSERT INTO pdpstatistics_rollup ( name, version, PDPGROUPNAME, PDPSUBGROUPNAME, YEAR, MONTH, AVG_POLICYDEPLOYCOUNT, AVG_POLICYDEPLOYFAILCOUNT, AVG_POLICYDEPLOYSUCCESSCOUNT, AVG_POLICYEXECUTEDCOUNT, AVG_POLICYEXECUTEDFAILCOUNT, AVG_POLICYEXECUTEDSUCCESSCOUNT ) select name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp) as YEAR, EXTRACT(MONTH FROM timestamp) as MONTH, avg(POLICYDEPLOYCOUNT) as AVG_POLICYDEPLOYCOUNT, avg(POLICYDEPLOYFAILCOUNT) as AVG_POLICYDEPLOYFAILCOUNT, avg(POLICYDEPLOYSUCCESSCOUNT) as AVG_POLICYDEPLOYSUCCESSCOUNT, avg(POLICYEXECUTEDCOUNT) as AVG_POLICYEXECUTEDCOUNT, avg(POLICYEXECUTEDFAILCOUNT) as AVG_POLICYEXECUTEDFAILCOUNT, avg(POLICYEXECUTEDSUCCESSCOUNT) as AVG_POLICYEXECUTEDSUCCESSCOUNT from pdpstatistics group by name, version, PDPGROUPNAME, PDPSUBGROUPNAME, EXTRACT(YEAR FROM timestamp), EXTRACT(MONTH FROM timestamp);
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql7.yaml::policy-19::admin ran successfully in 20ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-19', 'admin', 'dbchangelog-sql7.yaml', NOW(), 25, '8:23ed645fde97a2c2a7306e1e47f63646', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1938616271', '25')

PDPSTATISTICS_ROLLUP
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


dbchangelog-sql8.yaml
databaseChangeLog:
  -  changeSet:  
       id:  policy-20
       author:  admin  
       label: release1.4
       preConditions:
         - onFail: MARK_RAN
         - not:
           - tableExists:
               tableName: toscaservicetemplate
       changes:
       - tagDatabase:  
          tag:  25       
       -  sql:  
           comment:  create toscaservicetemplate 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true               
           sql:  CREATE TABLE toscaservicetemplate (
                 `DESCRIPTION` VARCHAR(255) NULL, 
                 TOSCADEFINITIONSVERSION VARCHAR(255) NULL, derived_from_name VARCHAR(255) NULL, 
                 derived_from_version VARCHAR(255) NULL, name VARCHAR(120) NOT NULL, 
                 version VARCHAR(20) NOT NULL, capabilityTypesVersion VARCHAR(20) NULL, 
                 capabilityTypesName VARCHAR(120) NULL, dataTypesName VARCHAR(120) NULL, 
                 dataTypesVersion VARCHAR(20) NULL, nodeTypesVersion VARCHAR(20) NULL, 
                 nodeTypesName VARCHAR(120) NULL, policyTypesName VARCHAR(120) NULL, 
                 policyTypesVersion VARCHAR(20) NULL, relationshipTypesVersion VARCHAR(20) NULL, 
                 relationshipTypesName VARCHAR(120) NULL, 
                 topologyTemplateLocalName VARCHAR(120) NULL, 
                 topologyTemplateParentKeyName VARCHAR(120) NULL, 
                 topologyTemplateParentKeyVersion VARCHAR(15) NULL, 
                 topologyTemplateParentLocalName VARCHAR(120) NULL, 
                 CONSTRAINT PK_TOSCASERVICETEMPLATE PRIMARY KEY (name, version));   
  -  changeSet:  
       id:  policy-21
       author:  admin  
       label: release1.4
       preConditions:
        - onFail: MARK_RAN
        - sqlCheck:
            expectedResult: 0
            sql: select count(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and version='1.0.0';
       changes:
       - tagDatabase:  
          tag:  26        
       -  sql:  
           comment:  insert toscaservicetemplate; 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  INSERT INTO toscaservicetemplate (`DESCRIPTION`, TOSCADEFINITIONSVERSION, 
                 derived_from_name, derived_from_version, name, version, capabilityTypesVersion, 
                 capabilityTypesName, dataTypesName, dataTypesVersion, nodeTypesVersion, 
                 nodeTypesName, policyTypesName, policyTypesVersion, relationshipTypesVersion, 
                 relationshipTypesName, topologyTemplateLocalName, topologyTemplateParentKeyName, 
                 topologyTemplateParentKeyVersion, topologyTemplateParentLocalName) 
                 VALUES (NULL, 'tosca_simple_yaml_1_1_0', NULL, NULL, 'ToscaServiceTemplateSimple', 
                 '1.0.0', NULL, NULL, 'ToscaDataTypesSimple', '1.0.0', NULL, NULL, 
                 'ToscaPolicyTypesSimple', '1.0.0', NULL, NULL, NULL, NULL, NULL, NULL); 
  -  changeSet:  
       id:  policy-22
       author:  admin  
       label: release1.4
       preConditions:
        - onFail: MARK_RAN
        - sqlCheck:
            expectedResult: 0
            sql: select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName';
       changes:
       - tagDatabase:  
          tag:  27       
       -  sql:  
           comment:  add constraint toscaservicetemplate; 
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true    
           sql:  ALTER TABLE toscaservicetemplate ADD CONSTRAINT 
                 FK_ToscaServiceTemplate_capabilityTypesName FOREIGN KEY 
                 (capabilityTypesName, capabilityTypesVersion) REFERENCES 
                 toscacapabilitytypes (name, version) ON UPDATE RESTRICT ON DELETE RESTRICT;


Update liquibase.properties to point to dbchangelog-sql8.yaml

Run mvn liquibase:update


[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-20::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : Not precondition failed

[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-20', 'admin', 'dbchangelog-sql8.yaml', NOW(), 26, '8:2954c7c4f467425b3ea5d35bf45ef4a9', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '25')
[INFO] select count(*) from toscaservicetemplate where name='ToscaServiceTemplateSimple' and version='1.0.0'
[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-21::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : SQL Precondition failed. Expected '0' got '1'

[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-21', 'admin', 'dbchangelog-sql8.yaml', NOW(), 27, '8:d86e5dad1fce9bb955d7cb0558b29707', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '26')
[INFO] select count(*) from information_schema.table_constraints where constraint_name ='FK_ToscaServiceTemplate_capabilityTypesName'
[INFO] Marking ChangeSet: dbchangelog-sql8.yaml::policy-22::admin ran despite precondition failure due to onFail='MARK_RAN':
dbchangelog-sql8.yaml : SQL Precondition failed. Expected '0' got '1'

[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('policy-22', 'admin', 'dbchangelog-sql8.yaml', NOW(), 28, '8:af557b428391cfcdd55e7f554c3e5cad', 'tagDatabase; sql', '', 'MARK_RAN', NULL, NULL, '3.8.0', '1942893174', '27')


None of the SQL statements ran as all the preconditions failed

The above YAML can be rewriiten like this:

dbchangelog-sql9.yaml
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:



dbchangelog-sql10.yaml
databaseChangeLog:
  -  changeSet:  
       id:  policy-26
       author:  admin  
       label: release1.5
       preConditions:
         - onFail: HALT
         - tableExists:
             tableName: JpaPdpPolicyDeploymentAudit
       changes:
       - tagDatabase:  
          tag:  31       
       -  sqlFile:  
           comment:  insert JpaPdpPolicyDeploymentAudit
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true               
           path: sql/insert_JpaPdpPolicyDeploymentAudit.sql
           relativeToChangelogFile: true  
       rollback:    
       -  sqlFile:  
           comment:  rollback JpaPdpPolicyDeploymentAudit
           dbms:  '!h2,  oracle,  mysql, mariadb'  
           endDelimiter:  \nGO  
           splitStatements:  true  
           stripComments:  true               
           path: sql/rollback_insert_JpaPdpPolicyDeploymentAudit.sql
           relativeToChangelogFile: true


insert_JpaPdpPolicyDeploymentAudit.sql
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');


rollback_insert_JpaPdpPolicyDeploymentAudit.sql
delete from JpaPdpPolicyDeploymentAudit where id in (9,10);


Important concepts to be considered

  1. Labels and Context - to separate environments and releases
  2. Tags - to set  a rollback check point
  3. Prechecks - to check if a particular changeSet needs to be executed
  4. Different formats - decide the best one for the team to work with
  5. 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


Related content

Liquibase Docker Testing
Liquibase Docker Testing
ONAP Wiki
More like this
Study on removing DB-based statistics from the Policy Framework
Study on removing DB-based statistics from the Policy Framework
ONAP Wiki
More like this
Liquibase Docker Compose Testing
Liquibase Docker Compose Testing
ONAP Wiki
More like this
ACM state table
ACM state table
ONAP Wiki
More like this
Database additions for Pdp group policy deploy/undeploy audit
Database additions for Pdp group policy deploy/undeploy audit
ONAP Wiki
More like this
Allow underlying database to be configured: MariaDB or Postgres
Allow underlying database to be configured: MariaDB or Postgres
ONAP Wiki
More like this