Object oriented changelog subfolder structure vs Custom order of SQL execution

General Discussion
1

Hi.

For our project purposes we have decided to go for the object oriented changelog structure.

The idea is to have data/triggers/stored procedures/views/tables etc subfolders in the changelog folder for readability purposes, and then have the main XML changelog file include all of them via includeAll from these folders.

Also, we would name SQL changelogs within those subfolders in accordance with best practices, starting with alphanum sequences so they are run in correct order. Of course, we will go for one command per one changeset file rule.

Since Liquibase runs the files in alphanum order but only within the current folder (right?) we have actually no control over the order of operations in our usual use case, which is a set of changes that has different types of changes (schema/data/triggers/keys/ etc) which are split in corresponding subfolders based on a type of change. In this case the order will correlate firstly with the subfolders, for ex:

<includeAll path="/triggers/" />
<includeAll path="/tables/" />
<includeAll path="/sproc/" />
<includeAll path="/keys/" />

and only then it will run them in their alphanum naming order. In our case that does not suffice as we will usually need to have direct control over the exact ordering of operation, especially with more nuanced and complicated changes like adding triggers, stored procedures, data etc, and in this case the running order would first take the triggers folder into account, followed by tables and stored procedures.

What if we, say, need to create a trigger, then a table, then a sproc and then another trigger, in this exact order? Can we somehow make peace with our includeAll && subfolder split && alphanum ordering intended way of doing things? if so, please advise.

If not, I see some alternatives, which are less preferable for us.

  1. Abandon subfolder structure, and keep them all in a single folder referenced via includeAll. Not preferred because very soon there will be hundreds of files.

  2. Keep subfolder structure for organization purposes, but have a directly controlled running order in the main xml changelog file, via include of specific files instead of includeAll. Seems like unnecessary extra work.

2

The issue I see with using includeAll with an object-type folder structure is: how can you ensure the proper execution order when there are dependencies between objects?

Assuming folders in this order:

01PROCS
02FUNCTIONS
03TABLES
04VIEWS

Views depend on tables, so that is fine.

Procs and functions depend on tables and/or views, so now this order is needed:

01TABLES
02VIEWS
03PROCS
04FUNCTIONS

But what happens if a view uses function in it’s definition? Now I’m stuck since functions depend on tables, and tables depend on functions.

Because of this issue, I recommend people using “include”, and not “includeAll”.

3

Programmers themselves will split and order these separate changes themselves, after they coded the entire piece of code. So the code at that point would already be safe in this regard.

My question exactly comments on the fact that we cannot control the order of changes if they are split in subfolders. So to answer your question, this exactly is my problem with it: I want to be able to order execution of that view sql AFTER the function definition (if it depends on it), but it seems it’s not possible if they are split in subfolders. This is silly, why can’t we have alphanum ordering work across subfolders, so that say 00050_view.sql runs after 00040_function.sql , even when they are split in diff subfolders ? Just a flag when running update, or a param option within includeAll would suffice.

4

I see your point, but unfortunately each includeAll folder is run one-at-a-time.

I’ll let someone at Liquibase comment on your suggestion.

1 Like
5

Thank you! Very much appreciated.

6

Could you split out the views that depend on functions into another folder, like 05Views? Just a suggestion.

I do see value in your request for ordering across folders.