22

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?

Dave
  • 1,645
  • 2
  • 23
  • 39

2 Answers2

27

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

  • This is very helpful. If you add a new procedure, you just add a new changeset to procedures.xml? I think that's what messed me up, I had never added a change set to a changelog after it had been run. Also, follow up question, is your changlog.xml your "master" or top level changelog file? – Dave Oct 12 '16 at 16:36
  • @Dave: yes, `changelog.xml` is the "master" that includes everything else –  Oct 12 '16 at 17:13
  • Thanks @a_horse_with_no_name , I would say we can remove `runInTransaction="true"` [since](https://docs.liquibase.com/concepts/basic/sql-format.html?Highlight=runInTransaction) the default value is true. In terms of run stored procedure in order, since version 3.5 new `runOrder` [attribute](https://docs.liquibase.com/concepts/basic/changeset.html?Highlight=runOrder) had been introduced, you can find more details [here](https://liquibase.jira.com/browse/CORE-3328). – Mohamed AbdElRazek Jun 04 '20 at 15:53
  • How do you do a rollback if you are using a single file for each procedure with 'runOnChange=true'? If you revert to a previous version of the changesets in order to get the previous version of procedures then Liquibase will not know how to roll back the changes in the normal changeset. – gsteiner Aug 13 '20 at 19:53
3

@a_horse_with_no_name answer correct only, along with you also check your version which I missed. I am adding what he missed.

Here are sample Procedure file (procedure_one.sql) which you can directly use.

CREATE PROCEDURE `ivi_alter_column`() 
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
   ALTER TABLE `tableName` ADD COLUMN  `columnName` BIGINT(20) NULL;
END;

procedures.xml will look like this

<changeSet id="alter_column_version_1" author="auther" dbms="mysql" runInTransaction="true">

 <createProcedure dbms="mysql" encoding="UTF-8"
            path="../changelog/procedures/procedure_one.sql"
            procedureName="sample_alter_procedure" relativeToChangelogFile="true">
    </createProcedure>

   // Make sure You are calling 
    <sql>call sample_alter_procedure() </sql>

   // then dropping procedure also
    <dropProcedure procedureName="sample_alter_procedure" />

</changeSet>
Rohit Jain
  • 127
  • 3
  • 9