Functions, Stored Proc, and the Like

Liquibase Development
1

Looking at the history of database stored procedures and functions and the like is notoriously difficult.  It occurs to me that I could put all our stored procedures and functions in git and just reference them using the sqlFile attribute in a new changeSet whenever a new change would need to be applied.  However, from what I’ve read, it seems as if any changeSet that reference a sqlFile that has been changed, would then have a different md5 and liquibase would then try to apply it.

We are using the best practice approach where we have one master changelog file per schema and from there we reference a new changelog file per new release.


<databaseChangeLog xmlns="<a href='http://www.liquibase.org/xml/ns/dbchangelog"' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog"</a> xmlns:xsi="<a href='http://www.w3.org/2001/XMLSchema-instance"' target='_blank' rel='noreferrer'>http://www.w3.org/2001/XMLSchema-instance"</a> xsi:schemaLocation="<a href='http://www.liquibase.org/xml/ns/dbchangelog' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog</a> <a href='http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd"></a></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2017.Q3.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2017.Q3.R2.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2017.Q4.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2018.Q1.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2018.Q2.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2018.Q3.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2018.Q4.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2019.Q1.R1.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2019.Q1.R2.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2019.Q1.R3.xml"/></div>

&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-2019.Q2.R1.xml"/></div>

</databaseChangeLog></div></div>

It occurs to me that maybe the right approach for having an individual sqlFile per function/stored proc would be to have a single include that would look something like this:


&nbsp; &nbsp; <include relativeToChangelogFile="true" file="catalog.db.changelog-functions.xml"/></div></div></div>

And in there, I’d have a bunch of changeSets that reference the individual sqlFiles and then any time one of the referenced sqlFiles changes, the changeSet would be reapplied.  Is that how it would work?

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

<databaseChangeLog</div>

&nbsp; &nbsp; &nbsp; &nbsp; xmlns="<a href='http://www.liquibase.org/xml/ns/dbchangelog"' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog"</a></div>

&nbsp; &nbsp; &nbsp; &nbsp; xmlns:xsi="<a href='http://www.w3.org/2001/XMLSchema-instance"' target='_blank' rel='noreferrer'>http://www.w3.org/2001/XMLSchema-instance"</a></div>

&nbsp; &nbsp; &nbsp; &nbsp; xsi:schemaLocation="<a href='http://www.liquibase.org/xml/ns/dbchangelog' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog</a></div>

&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <a href='http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">' target='_blank' rel='noreferrer'>http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd"></a></div>

</div>

&nbsp; &nbsp; <changeSet id="function1" author="vrodrigu"></div>

&nbsp; &nbsp; &nbsp; &nbsp; <sqlFile path="path/to/function1.sql"/></div>

&nbsp; &nbsp; </changeSet></div>

</div>

&nbsp; &nbsp; <changeSet id="function2" author="vrodrigu"></div>

&nbsp; &nbsp; &nbsp; &nbsp; <sqlFile path="path/to/function2.sql"/></div>

&nbsp; &nbsp; </changeSet></div>

</div>

&nbsp; &nbsp; <changeSet id="function3" author="vrodrigu"></div>

&nbsp; &nbsp; &nbsp; &nbsp; <sqlFile path="path/to/function3.sql"/></div>

&nbsp; &nbsp; </changeSet></div>

</div>

</databaseChangeLog></div>

```</div>
2

This seems a working solution. Except that you are correct, the presented code would fail on function changeYou can use runOnChange attribute on the changeset tags defining the functions and the like (and / or runAlways, just to be on the safe side). 

If stored procedures are written in an idempotent way (e.g. CREATE OR REPLACE in Postgres, or DROP IF EXISTS … ; CREATE … in SQL Server) then the code would just run on change and it would be possible to use neat git diffs to see the actual change.

Problem here is that it becomes a bit hard to see which changes have been applied to the database and which weren’t.

The only thing you know is that "when sproc A was last replaced at , the SQL code hashed to a value of ".

Since you don’t have a ready hash of the resulting changeset, it might be a bit hard to identify in case of any discrepancies. This could be a moot point if you have other ways of tracking deployed versions, of course…