How Do I initialize/deploy data to production using Liquibase?

 I encountered a situation recently where I added a field to a table, and needed to update data due to that field.

I realized manually doing this when I deployed was obviously a bad idea, so I did some research into how liquibase works, and my solution was pretty easy using this method 

But, I had some challenges

1. How do I ensure that my script won't get overwritten when someone is working on a separate branch?
2. How do I ensure that my script will only be run once and not multiple times?

Here is my solution:

We have a requirement that all branches be named after the support/dev ticket # you're working on.   So, for argument sake, let's pretend I'm working on Ticket 1234, and my branch is called Ticket_1234.

Our back-end liquibase changelogs are stored in GIT in CHANGELOGS\DB folder. 

There are 4 steps I did

1. Created a folder within my CHANGELOGS\DB called SCRIPTS
2. Added a line at the end of my controller.xml
   
   <include file="scripts.xml"/>
3. To ensure uniqueness, our format for a script file is script_<ticket#>_<script#>.sql.  So in this example, my first script would be script_1234_1.sql.  If I made a second one, script_1234_2.sql, etc., and we save the script in the SCRIPTS directory
4. Created a new file called scripts.xml.  We match the changeset ID # to the script #, so when liquibase runs it, it stores it in the DBCHANGELOG table, and this way, we won't encounter any duplications when deploying.  Sample File Below
   
   
   <?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-latest.xsd">

    

     <changeSet author="scott" id="script_1234_1">

           <sqlFile dbms="!h2, oracle, mysql"

               encoding="UTF-8"

               endDelimiter="/"

               path="SCRIPTS/script_1234_1.sql"

               relativeToChangelogFile="true"

               splitStatements="true"

               stripComments="true"/>

     </changeSet>

   </databaseChangeLog>

Happy Coding!

Discipline, Patience and Confidence or How I Learned to Stop Worrying and Love Git and Liquibase

It's been a while since I updated for 2 reasons

1. I'm extremely lazy with posting and keeping up with things
2. I really haven't done anything that warranted a blog post

That all changed a few months ago, but before we get into that, let me bring you up to speed.  

I've been working with the Oracle Database since 2004.  I co-created an application for the mortgage industry utilizing Oracle Application Express (then HtmlDB version 4) and Oracle Database 10g.  We didn't use source control at all with that application, and honestly, at that time, I wasn't even sure what it was.

My next career path took me to an ISV with an Apparel ERP.   We were using MS Visual Sourcesafe on our code base, but we didn't really have a great way of promoting code in an easy way.  We also had hundreds of customers with customization and custom code that really made auto-deployment extremely difficult.   I also wasn't the best with remembering to check-in/check-out code, and had to be reminded frequently.

For the last several years, I've been a member of a small team working on replacing a legacy system.  Our application is an "evolutionary" approach to replacement. 

Sometimes during this process, I'd feel more like a firefighter than a developer.  Fixing errors as quickly as possible.   I'd rush changes out to production without remembering to update development or doing proper testing.  This would work for the immediate, but with the next update, bam that fix would be lost for obvious reasons.

Obviously, this is not a great way to work.   We had been mandated by management to enforce version control, and deployment logging.  As, we were using some Oracle Cloud products, we were given free access to Oracle's Visual Builder Studio Cloud Service.  This provided us with a hosted GIT service similar to Github.   So, I proceeded to set us up with GIT for managing our code base.

Honestly, understanding GIT terminology was a bit difficult for me.  "Checking Out" has a completely different meaning on SourceSafe than it does in GIT.   And, I wasn't too familiar with the command-line interface, so I began using the GIT integration in Oracle SQL Developer.   However, I didn't understand branches and merges, so all I was doing was making a change, and committing it to the Master, just for basic source control.  The other issue was that the other developers on the team weren't using GIT, so the potential for disaster was real.

Obviously, there was a need for change... So, earlier this year, I was poking around on Twitter (I refuse to call it "x"), and saw this post from SQL Product Manager Jeff Smith 

Having nothing to lose, I sent an email asking for some assistance with CI/CD (Continuous Integration/Continuous Deployment).   Within 30 minutes, Jeff replied to me, and introduced me to Zachary Talke, another Product Manager on the Database Tools team.   Zach setup a video call with my development team, and demonstrated Liquibase to us, and how we can use it to promote our changes.

Of course, as things always do, I got busy for a few months, so didn't pick this up again for 2 more months.   And, by then, I had forgotten everything I had learned.  Zach had sent me a few Workshops that I'm linking here, which were very useful

• SQLcl Liquibase Concept Overview Workshop
• Deploying a Full Stack Development Project With SQLcl Liquibase Workshop

So, I did what everyone does when they need to learn something, they go on YouTube.  I found a pretty detailed video specifically regarding Oracle APEX and Liquibase that I also highly recommend.

• Continuous Integration & Delivery (CI/CD) for Oracle Database and APEX Development

During the setup for our implement, I had many questions arise that looked weird to me.  Zach and Jeff would typically reply to emails within 1 to 2 business days, which is a great turn-around for basically "free support"

Our process while it seems complicated, actually became fairly simple

• Ticket must be created in our separate ticketing system
• Branch name must be ticket_<ticket_number>
• all DDL types (packages,views,functions,triggers,etc) must be saved as .sql files in the file system and commited on save
• LIQUIBASE changelogs must be generated for all objects to move between DBS (and committed to GIT)
• If new additions, update changelog.xml to include any new liquibase changelogs
• End-User Testing must be done on separate test system
• Once testing is complete, merge request must be made on cloud
• Merge can only be done after another developer and manager approve request
• Only way to deploy to production is via Liquibase

Here are some helpful Liquibase command

• lb generate-db-object -object-type <OBJECT_TYPE> -object-name <NAME> -ovf
• replace <OBJECT_TYPE> with PACKAGE_SPEC,PACKAGE_BODY,VIEW,TABLE,ETC
• replace <NAME> with the name of the object
• -ovf is for overwriting files..this will update an existing file instead of creating a new one
• lb generate-apex-object -applicationid <ID#>-skipexportdate -exporiginalids -split -ovf -runalways
• replace <ID#> with your applicationid
• -skipexportdate will not add an export date in your files, making sure only updated ones have changes
• -exporiginalids will emit ids as they were when app was imported, again to only update files that have changed
• -split will split the application into several files so you have 1 file per page, and will only update that page instead of the whole app
• -ovf same as above
• -runalways - whenever you export apex, it has the same changelog ID INSTALL_ID#.. this will ensure the app will run no matter if the changelog id is in the table

Before going live, I created a new branch for setup and did an "LB generate-schema" from our production system.. this created a changelog for every object in the schema.   I then did a datapump export/import from production to our live and test systems.   Once the import was done.. to ensure everything was "in sync" ... I performed lb -changelog-sync -chf controller.xml... this updated the databasechangelog table to ensure that all changelogs in live were applied to test and dev, this way only future changelogs would be affected

As of now, our system stability has increased significantly... I no longer fear deploying software.  I can view where mods are without having to read code, and just check the commit logs.  We have full auditability for deployment, which is great.

Here are a few challenges I still face

• I have subscribed my APEX plugin settings to a master app, but when I install my app from dev->live, it takes the plugin settings, and then I have to unsubscribe, change the settings, and re-subscribe
• There seems to be inconsistent behavior when I change a page that has user saved Interactive Reports and Interactive Grids.  I'm trying to isolate the exact situation here to report it, but I have had a few reports of saved reports/settings disappearing
• We are doing all our liquibase commands manually and have to type them in.. I want to investigate a full automatic deployment system to simplify this process

I also need to give a shout out to my friend Mark Wrynn.  He's the one that hired me 20 years ago, and shaped my career with Oracle.  His advice and help was invaluable specifically on the GIT side.