Page History

...

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:  integerint
                  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 

...

[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

...

 databaseChangeLog:
  -  changeSet:  
       id:  sqlpolicy-yaml-14 
       author:  admin  
       changes:  
        -  sql:  
            comment:  create personJpaPdpPolicyDeploymentAudit table  
            dbms:  '!h2,  oracle,  mysql, mariadb'  
            endDelimiter:  \nGO  
            splitStatements:  true  
            sql:  createCREATE table person (name varchar(30))TABLE JpaPdpPolicyDeploymentAudit (
            stripComments:  true
  -  changeSet:  
       id:  sql-yaml-2
    ID BIGINT  author:  admin  NOT NULL, 
       changes:  
        -  sql:  
            comment:  insert nameNAME VARCHAR(120) NOT NULL, 
            dbms:  '!h2,  oracle,  mysql, mariadb'  
            endDelimiter:  \nGO  VERSION VARCHAR(20) NOT NULL,
            splitStatements:   true  
            sql:  insert into personPDPGROUP VARCHAR(name) values ('Liquibase Test')  
255) DEFAULT NULL NULL,
                                 PDPTYPE VARCHAR(255) DEFAULT NULL NULL,
                    stripComments:  true            

update liquibase.properties

changeLogFile: dbchangelog-sql.yaml

mvn liquibase:update

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

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

Add customer-table-changelog-1.sql

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

update liquibase.properties

changeLogFile: dbchangelog-sql.yaml

mvn liquibase:update

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

Test 9: Rollback 2 previous updates

Run mvn liquibase:rollback -Dliquibase.rollbackCount=2

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

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

Add JpaPdpPolicyDeploymentAudit-changelog-1.sql

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

--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;JpaPdpPolicyDeploymentAudit

Add customer-tableJpaPdpPolicyDeploymentAudit-changelog-2.sql

--liquibase formatted sql
			
--changeset admin:custpolicy-27
insert into customer(id, name, roleinto JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'Robert'TestName', '1.0.0', 'TestGroup', 'Software Developer'TestType', 1, now(), 'Admin');
insert into JpaPdpPolicyDeploymentAudit customer(id, name, version, pdpGroup, PdpType, roleaction, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'JohnTestGroup2', 'Dev Ops'TestType2', 1, now(), 'Admin');

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

Add dbchangelog-master.yaml

databaseChangeLog:
- include:
    file:  customer-tableJpaPdpPolicyDeploymentAudit-changelog-1.sql  
- include:
    file:  customer-tableJpaPdpPolicyDeploymentAudit-changelog-2.sql  

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

Run mvn liquibase:update

[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255CREATE 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 customerJpaPdpPolicyDeploymentAudit-table-changelog-1.sql::custpolicy-16::admin ran successfully in 306ms329ms
[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 ('custpolicy-16', 'admin', 'customer-tableJpaPdpPolicyDeploymentAudit-changelog-1.sql', NOW(), 313, '8:6952c30a86e91b5f32a65e313f52b64bc5d0bc1cbc6a6875136d754002802e6f', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '15992636701868752479')
[INFO] insert into customerJpaPdpPolicyDeploymentAudit (id, name, roleversion, pdpGroup, PdpType, action, timeStamp, changedByUser) values (1, 'TestName', '1.0.0', 'RobertTestGroup', 'Software DeveloperTestType', 1, now(), 'Admin')
[INFO] insert into customerJpaPdpPolicyDeploymentAudit (id, name, role) values (2, 'John', 'Dev Ops, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (2, 'TestName2', '1.0.0', 'TestGroup2', 'TestType2', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet customer-tableJpaPdpPolicyDeploymentAudit-changelog-2.sql::custpolicy-27::admin ran successfully in 11ms15ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('custpolicy-27', 'admin', 'customerJpaPdpPolicyDeploymentAudit-table-changelog-2.sql', NOW(), 414, '8:52f9c3c97a509dc22f109be204fc8dfd5b683eb6e17f177268ed50b9d2829bda', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '15992636701868752479')

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

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

Test

...

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

Add Add dbchangelog-sql2.yaml

databaseChangeLog:
-  changeSet:  
     id:  custpolicy-3 8
     author:  admin  
     changes:  
     - tagDatabase:  
        tag:  1.2 0    
     -  sql:  
         comment:  insert customer JpaPdpPolicyDeploymentAudit
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into JpaPdpPolicyDeploymentAudit (id, name, version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
           sql:  insert into customervalues (id3, name'TestName3', role) values (3'1.0.0', 'TestGroup3', 'PeteTestType3', 'Payroll1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from customerJpaPdpPolicyDeploymentAudit where id = 3;    
-  changeSet:  
     id:  custpolicy-49
     author:  admin  
     changes:  
     - tagDatabase:  
        tag:  1.3 1   
     -  sql:  
         comment:  insert customer JpaPdpPolicyDeploymentAudit
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into customerJpaPdpPolicyDeploymentAudit (id, name, roleversion, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (4, 'James'4, 'TestName4', '1.0.0', 'TestGroup4', 'TestType4', 1, now(), 'PayrollAdmin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from customerJpaPdpPolicyDeploymentAudit where id = 4;                  

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

...

[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-38', 'admin', 'dbchangelog-sql2.yaml', NOW(), 515, '8:a660ebc91d2fa7b7e4c2287bf1725707f83378476fe49386b479e3bdaf33c8f0', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '15996497601869831922', '1.20')
[INFO] Tag '1.31' applied to database
[INFO] insert into customer JpaPdpPolicyDeploymentAudit (id, name, role, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'JamesTestGroup4', 'PayrollTestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::custpolicy-49::admin ran successfully in 3ms4ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('custpolicy-49', 'admin', 'dbchangelog-sql2.yaml', NOW(), 616, '8:cb8dbdef98c88b2e99d42a6591f884983012add458c8a5395af9f2797779af1e', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '15996497601869831922', '1.31')

2 new records have been added to the customer JpaPdpPolicyDeploymentAudit table

Test

...

12: Rollback update using tag

mvn liquibase:rollback -Dliquibase.rollbackTag=1.20

[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::custpolicy-49::admin
[INFO] delete from customer JpaPdpPolicyDeploymentAudit where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'custpolicy-49' 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.31' applied to database
[INFO] insert into customer JpaPdpPolicyDeploymentAudit (id, name, roleversion, pdpGroup, PdpType, action, timeStamp, changedByUser) values (4, 'TestName4', '1.0.0', 'JamesTestGroup4', 'PayrollTestType4', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::custpolicy-49::admin ran successfully in 3ms7ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, 

Only the second insert statement ran.

Test

...

14: Add labels to changeSets

Add dbchangelog-sql3.yaml

...

databaseChangeLog:
-  changeSet:  
     id:  custpolicy-5 10
     author:  admin 
     labels: release1.0
     changes:  
     - tagDatabase:  
        tag:  1.6 2   
     -  sql:  
         comment:  insert customerJpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into customerJpaPdpPolicyDeploymentAudit (id, name, role) version, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (5, 'TestName5', '1.0.0', 'JaneTestGroup5', 'PayrollTestType5', 1, now(), 'Admin')   
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from customerJpaPdpPolicyDeploymentAudit where id = 5;    
-  changeSet:  
     id:  custpolict-611
     author:  admin  
     labels: release1.1
     changes:  
     - tagDatabase:  
        tag:  1.73    
     -  sql:  
         comment:  insert customerJpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into customerJpaPdpPolicyDeploymentAudit (id, name, version, rolepdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (6, 'Jean'TestName6', '1.0.0', 'TestGroup6', 'Dev OPS'TestType6', 1, now(), 'Admin')  
         stripComments:  true            
     rollback:    
     - sql:
         sql: delete from customerJpaPdpPolicyDeploymentAudit where id = 6;                                   

Add labels to liquibase.properties

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

Run mvn liquibase:update

[INFO] Tag '1.62' applied to database
[INFO] insert into customer JpaPdpPolicyDeploymentAudit (id, name, role, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (5, 'TestName5', '1.0.0', 'TestGroup5', 'JaneTestType5', 1, now(), 'PayrollAdmin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::custpolicy-510::admin ran successfully in 3ms5ms
[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-510', 'admin', 'dbchangelog-sql3.yaml', NOW(), 717, '8:ce6fde026140f9831fa66b60662e842d71e4400a0bf77f08304ea92c3a989657', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '18516274651870546128', '1.62')

Only changeSet with label release1.0 has been applied

Test

...

15: Apply second label

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

[INFO] Tag '1.73' applied to database
[INFO] insert into customer JpaPdpPolicyDeploymentAudit (id, name, role, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (6, 'TestName6', '1.0.0', 'TestGroup6', 'JeanTestType6', 'Dev OPS1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::custpolict-611::admin ran successfully in 5ms6ms
[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 ('custpolict-611', 'admin', 'dbchangelog-sql3.yaml', NOW(), 818, '8:96a8060554eef1d1f4cf4f43dd2c376ccab3ec22e42eb1be367a62d0a86c80dc', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '18520800351870657144', '1.73')

ChangeSet with label release1.1 has been applied

Test

...

16: Test contexts

Remove labels: release1.0, release1.1 from liquibase.properties

Add dbchangelog-sql4.yaml

...

databaseChangeLog:
-  changeSet:  
     id:  custpolicy-712
     author:  admin 
     context: qa
     labels: release1.2
     changes:  
     - tagDatabase:  
        tag:  1.84  
     -  sql:  
         comment:  insert customerJpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into customerJpaPdpPolicyDeploymentAudit (id, name, role) values (7, 'Jo', 'Payroll', 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 customerJpaPdpPolicyDeploymentAudit where id = 7;    
-  changeSet:  
     id:  custpolicy-813
     author:  admin  
     context: test   
     labels: release1.2
     changes:  
     - tagDatabase:  
        tag:  1.9    
     -  sql:  
         comment:  insert customerJpaPdpPolicyDeploymentAudit 
         dbms:  '!h2,  oracle,  mysql, mariadb'  
         endDelimiter:  \nGO  
         splitStatements:  true  
         sql:  insert into customerJpaPdpPolicyDeploymentAudit (id, name, roleversion, pdpGroup, PdpType, action, timeStamp, changedByUser) 
               values (8, 'TestName', '1.0.0', 'KateTestGroup', 'Dev OPS'TestType', 1, now(), 'Admin')   
         stripComments:  true            
     rollback:    
     - sql:
 :
         sql: delete from JpaPdpPolicyDeploymentAudit where id = 8;        sql: delete from customer where id = 8;                  

Run liquibaseRun mvn liquibase:update -Dliquibase.contexts=test

[INFO] Tag '1.9' applied to database
[INFO] insert into customer JpaPdpPolicyDeploymentAudit (id, name, role, version, pdpGroup, PdpType, action, timeStamp, changedByUser) values (8, 'TestName', 'Kate', 'Dev OPS1.0.0', 'TestGroup', 'TestType', 1, now(), 'Admin')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::custpolicy-813::admin ran successfully in 4ms6ms
[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-813', 'admin', 'dbchangelog-sql4.yaml', NOW(), 919, '8:cea7bb92b5c448e1c217b9ed69fce75e2c2fb879e12ca3547180d53dbe43c53a', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '18567986691871103966', '1.9')

Only the changeSet with context test has runNote: labels can be applied in  a similar fashion: liquibase:update -Dliquibase.labels=release1.2

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

...