Liquibase Stored Proc's management

Question

I read liquibase's best practices, specifically for managing stored procedures:

Managing Stored Procedures: Try to maintain separate changelog for Stored Procedures and use runOnChange=”true”. This flag forces LiquiBase to check if the changeset was modified. If so, liquibase executes the change again.

What do they mean by "maintain separate changelog for stored procedures"?

I typically have a directory of changelogs that are linked to releases. Each changelog file is included in the master.xml.

What would the directory structure be when following their advice?

Answer 1

What we do is something like this:

\---liquibase
    |   changelog.xml
    |   procedures.xml
    |   
    +---procedures
            procedure_one.sql
            procedure_two.sql
            

changelog.xml simply includes procedures.xml. Inside procedures.xml we then have something like this:

<changeSet author="arthur" id="1" runOnChange="true" runInTransaction="true">
    <sqlFile path="procedures/procedure_one.sql"
             encoding="UTF-8"
             relativeToChangelogFile="true"
             endDelimiter=";"
             splitStatements="true"/>

</changeSet>

<changeSet author="arthur" id="2" runOnChange="true" runInTransaction="true">
    <sqlFile path="procedures/procedure_two.sql"
             encoding="UTF-8"
             relativeToChangelogFile="true"
             endDelimiter=";"
             splitStatements="true"/>

</changeSet>

Of course runInTransaction="true" only makes sense if your DBMS supports transactional DDL.

Each SQL script for the procedures is self contained and re-creates the procedure using create or replace. For DBMS that do not support create or replace we usually do a (conditional) drop procedure; create procedure ... in there.

By explicitly including the files (instead of using includeAll) we have control over the order in which the procedures and functions are created (important if one uses another).

If you add a new procedure, you add a new SQL script and a new changeSet to the procedures.xml