Release specific deployments on directory based with some conditional requirements on Release day

General Discussion
1

Dear Experts,

I am planning to use Liquibase open source deployment in our org as part of DB changes.
I have below requirements or queries from my end. Can someone help me on this please.

We have a Prodcution database and database deployments will get applied on release specific like Release_1.1,Release_1.2

In Relase_1.1 we have bunch of SQL files like below from different developpers and which specific to only one DB

Release_1.1 (dir)
file1_tab1.sql (Author:ID will be user1:file1_tab1)
file2_tab1.sql (Author:ID will be user1:file2_tab1)
file1_tab2.sql (Author:ID will be user2:file1_tab2)
file2_tab2.sql (Author:ID will be user2:file2_tab2)
file3.sql (Author:ID will be user3:file3)
file4_tab1.sql (Author:ID will be user1:file4_tab1)
file4_tab2.sql (Author:ID will be user1:file4_tab2)
file5.sql (Author:ID will be user3:file5)
file7.sql (Author:ID will be user3:file7)
file8.sql (Author:ID will be user3:file8)
file9.sql (Author:ID will be user3:file9)

How can I update Liquibase changelog to execute includeALL to specific directory(Release_1.1) with below 3 conditional ways

Step1. sequence order required for the below files

file1_tab1.sql
file2_tab1.sql
file4_tab1.sql
file4_tab2.sql

Ex: file1_tab1.sql ==> file2_tab1.sql ==> file4_tab1.sql ==> file4_tab2.sql (In this order it should execute)

Step2. We want to skip some files in the same directory as below

file7.sql
file8.sql

Step3. The other remaining files will be execute in any order is fine

file1_tab2.sql
file2_tab2.sql
file3.sql
file5.sql
file9.sql

Also if I want to execute the Step2 later after my applocation deployments

Assume like Step1,3 is in Prd-deployment and Step2 is in post deployment order.

Please do the needful

2

includeAll is not going to work if you want to execute the files in a certain sequence. It will only execute the files in the specified directory in alphabetical sort order.

You will need to use “include” to include the files in your master changelog, in the desired sequence.

3

Hi Daryldoak,

Thank you for your update, it really helps me a lot. I have another question here. We have to deploy the SQL files in Pre-Deployment, Deployment and Post Deployment sequence. May I know how do I manage the same with one changelog.XML file please? This requirement really will helps to my requirement.

Pre-Deployment changes:

file1_tab1.sql (Author:ID will be user1:file1_tab1)
file2_tab1.sql (Author:ID will be user1:file2_tab1)
file1_tab2.sql (Author:ID will be user2:file1_tab2)
file2_tab2.sql (Author:ID will be user2:file2_tab2)
file3.sql (Author:ID will be user3:file3)
file4_tab1.sql (Author:ID will be user1:file4_tab1)
file4_tab2.sql (Author:ID will be user1:file4_tab2)

Deployment Changes:

file5.sql (Author:ID will be user3:file5)
file7.sql (Author:ID will be user3:file7)

Post Deployment Changes:

file8.sql (Author:ID will be user3:file8)
file9.sql (Author:ID will be user3:file9)

Regards

Srinivas V

4

Add pre-deployment .sql files to the changelog.xml, run liquibase “update”.

Add deployment .sql files to the changelog.xml, run liquibase “update”.

Add post-deployment .sql files to the changelog.xml, run liquibase “update”.

You could also add all the .sql files at the same time, and add “tag” commands in-between each set of files, then you could run “update-to-tag”. This would require less change to the changelog.xml.

5

@daryldoak Could you please help me how can I add tag option in changelog.xml? if yes may I know how we can add it? also please let me know how we can run liquibase command if we have the tag option

Below is my requirement:

All the Dev team share the SQL files like peerson1.sql ,peerson2.sql etc…, in this case how I can use single ChangeLog XML with tags option.

Changelog.xml

<?xml version="1.0" encoding="UTF-8"?>

peerson1.sql [Part of Pre-Deployment]

–liquibase formatted sql

–changeset user1:peerson1

create table contact1(
id varchar(50) not null ,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE contact1;

peerson2.sql [Part of Pre-Deployment]

–liquibase formatted sql

–changeset user1:peerson2

create table contact2(
id varchar(50) not null ,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE contact2;

peerson3.sql [Part of Post-Deployment]

–liquibase formatted sql

–changeset user1:peerson3

create table contact3(
id varchar(50) not null ,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE contact3;

peerson4.sql [Part of Post-Deployment]

–liquibase formatted sql

–changeset user1:peerson4

create table contact4(
id varchar(50) not null ,
name varchar(50) not null,
address1 varchar(50),
address2 varchar(50),
city varchar(30)
)
–rollback DROP TABLE contact4;

Regards,
Srinivas V

6 
<changeSet id="tag_database_v1" author="BOB">
  <tagDatabase tag="v1"/>
</changeSet>

You can add that between each sql file if you want, change the tag name of course.