📋Steps in this guide1/4

Liquibase and Source Control : Changes to Scripts Over Time

A discussion of how to manage changes to scripts over time with Liquibase.

oracle miscconfigurationintermediate

by OracleDba

14 views

The Scenario

If you come from the Oracle world, you probably put everything into SQL scripts. All your database code is in scripts in source control (I hope), along with all your DDL and DML changes. During the lifespan of a piece of code in Git, the code may change several times, but ultimately it will always be the same file in Git. If we are using these scripts from Liquibase, we'll see is a similar change set repeated numerous times in the master change log over time. Here's a silly example. Imagine we have a function called stored in a file called "amend_pay_function.sql". In January we create the function to add 10% on to pay for all employees, check the file into Git and create a change log called "amend_pay_function_2020_01_01.xml" with the following contents. In April there is a 10% pay cut for all staff, so we amend the function in the "amend_pay_function.sql" file, check it into Git and create a second change log called "amend_pay_function_2020_04_01.xml". It's similar to the previous one, but the change set has a different change set ID, so it is not mistaken for the previous change set. The master change log may look like this. If we are applying the change log to a customer who has not had any changes applied since before January what happens? - We pull the current version of the code from Git. - We use Liquibase to apply the master change log. - The current version of the "amend_pay_function.sql" script is applied twice. Once by the "/amend_pay_function_2020_01_01.xml" change log and again by the "amend_pay_function_2020_04_01.xml" change log. Remember, it's a reference to the *same* script on the file system. We end up with the correct version of the function definition at the end of the process, but depending on what other changes happened along the way, this could present a problem.

Code/Command (click line numbers to comment):

<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <changeSet author="tim"
id="amend_pay_function_v1"
runOnChange="true" failOnError="false">
      <sqlFile dbms="oracle"
               endDelimiter=";"
path="../scripts/amend_pay_function.sql"
relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <changeSet author="tim"
id="amend_pay_function_v2"
runOnChange="true" failOnError="false">
      <sqlFile dbms="oracle"
               endDelimiter=";"
path="../scripts/amend_pay_function.sql"
relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">

  ... Lots of other changes ...

  <include file="./amend_pay_function_2020_01_01.xml" relativeToChangelogFile="true"/> 
  
  ... Lots of other changes ...
  
  <include file="./amend_pay_function_2020_04_01.xml" relativeToChangelogFile="true"/> 
  
  ... Lots of other changes ...
  
</databaseChangeLog>

The Problem

Imagine there was a change log that called a script that did the following DML after each change to the function definition. At location 1, who apply changes on a monthly basis, they see this. - Version 1 of is loaded. - Pay is increased by 10%. - Version 2 of is loaded. - Pay is decreased by 10%. - Version 2 of is loaded. - Pay is decreased by 10%. - Version 2 of is loaded. - Pay is decreased by 10%. If locations aren't rapidly moving changes to production, the two locations run the same change log but get very different results.

Code/Command (click line numbers to comment):

1
2
3

UPDATE employees
SET    pay = amend_pay_function(pay);
COMMIT;

How do we prevent this?

Unfortunately, the way people like to work with scripts and the way Liquibase attempts to apply them can be at odds in situations like this. As I see it, there are two approaches to this problem. Use a different source file per version of the code... We can use a different file for each version of the code, so the change log is definitely loading the correct version of the source. This could be a separate script with a version included in the file name, or the code could be included inline as part of the change log itself. You are definitely going to get the correct sequence of events, but this is a source control disaster. You loose all the power of source control and tracking change over time in files. Pull and apply one release at a time... Rather than pulling the latest version of the code with the latest change log from Git, you pull a series of intermediate releases (version/steps) and apply them one at a time in order. These releases could be based on release branches, tags or just a list of known commits. Provided you do it in a sufficiently granular manner, you will get the result you need, whilst retaining the power of source control. The important point here is *you* must guarantee you've not allowed such an event described above to happen within an individual release step.

Conclusion

This problem is not unique to Liquibase. You can encounter similar issues in a manual deployment process. If you are only tracking schema changes, life is easy. Once you include data into the mix, which you have to, life becomes more complicated. Both methods proposed here have their pros and cons, but this is the reality of working on complex systems. Liquibase isn't magic. You would have to plan to avoid this type of situation in a manual operation, just the same way you have to with Liquibase. It all comes down to culture change and discipline. For more information see: - Liquibase : All Articles - Liquibase : Automating Your SQL and PL/SQL Deployments - Liquibase : Deploying Oracle Application Express (APEX) Applications - SQLcl : Automating Your SQL and PL/SQL Deployments using the SQLcl implementation of Liquibase - SQLcl : Deploying Oracle Application Express (APEX) Applications using the SQLcl implementation of Liquibase - SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications - SQLcl and Liquibase : Automating Your SQL and PL/SQL Deployments - Liquibase : Deploying Oracle Application Express (APEX) Applications Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!