Database change tracking and upgrades

User story

As a BMS user (developer, tester, data manager, systems administrator responsible for installations, breeder)

I want to have my BMS database schema upgraded automatically when I deploy new release versions of BMS. I also want to keep a clear track record of changes made to the database schema over time.

So that I do not need to manually track or apply database changes which is very error prone. I can upgrade from older versions to newer versions of BMS easily.

Liquibase

As of BMS v4.0.0.BETA.12 we are introducing Liquibase to satisfy the key user story of systematic database change tracking and automation of upgrades with every release.

Liquibase is a popular open source tool in its genre. It comes with a DSL - XML, YAML, JSON or SQL format - to define all database modifications as changesets organised as changelogs. This along with the command line tools and integration hooks allows automation for the process of applying database changes.

Key advantages and business value

Liquibase enables automation of database upgrades. Manual upgrade tracking with confluence pages and sql scripts, is error prone.
Also provides generation of diff scripts for manual application if needed for example in production environments where auto updates are often not allowed or recommended as a best practice

due to admin access and policy restrictions
due to compliance requirements such as SOX

It is am open source tool with active development community, good documentation and support.
Pretty much an industry standard. Our guru Mr. JHipster also agrees.
Enforces best practices for database change tracking.
Database change tracking is embedded into the development process.
Changelog serves as a clearly readable history of database changes. Liquibase database change tracker table DATABASECHANGELOG provides the same clear visibility of what changes have been applied since initial installation.

Approach

Adopting Liqiuibase in existing projects can be done in different ways as described in http://www.liquibase.org/documentation/existing_project.html

We have chosen to use BMS v 4.0.0.BETA6 as a baseline version and start developing the changelogs for each release after that. All existing manual SQL tracked on confluence page have been written as Liquibase changelogs and tested already as part of initial integration work.

Adopting a new tool such as Liquibase is not only a learning exercise, but a cultural change for everyone involved in the process. Following sections covers some key aspects in this regard for various stakeholders.

How to enable Liquibase?

Liquibase is not enabled by default as a best practice for production environment safety. Enabling Liquibase auto update for production environments is violation of the intended use of the software. There are separate instructions below on how database changes should be applied to production environments.

In all other deploys such as those used for UAT, QA testing, staging, development etc. enabling Liquibase is highly recommended so that database changes are applied automatically. Following instructions show how to enable Liquibase for all such non-production deployments of BMS:

For Windows Installations of BMS

Locate and run C\BMS4\infrastructure\tomcat\bin\BMSTomcatw.exe.
Under the Java tab there should be a Java Options section.
Append -Dspring.profiles.active=development at the end of existing parameters already present, in a new line.
See the example screenshot below Right-click the image and open in new tab to see full resolution image:
Restart the BMSTomcat service using the Windows services manager.

For Mac OSX Linux and Development Environments

Add -Dspring.profiles.active=development in your BMS runtime environment as a system property to activate automatic upgrades (application of changesets) on startup.
In Tomcat launch configuration in case of running from within Eclipse. Run -> Run Configurations and choose your Tomcat run configuration. Click the Arguments tab and add -Dspring.profiles.active=development at the end of VM arguments.
When running on external Tomcat, add JAVA_OPTS="$JAVA_OPTS -Dspring.profiles.active=development in setenv.sh for Linux. Add JAVA_OPTS=%JAVA_OPTS% -Dspring.profiles.active=development in setenv.bat for Windows.

Docker

Set LIQUIBASE_PARAM environment variable

Manual Production Database Upgrade

Take a complete backup of all your BMS databases before any change is applied.
Shut down BMS if it is running.
Ensure -Dspring.profiles.active=development is never applied to production environments.
Process for production deploys is to generate a "diff" SQL script using Liquibase command line tools and apply the generated SQL manually to each BMS database. To generate the diff script, see the "Scripts" section below, and use updateSQL instead of update to generate the diff. Parameter --outputFile can be used to save the sql to a file.

Scripts

These scripts are also available here: https://github.com/IntegratedBreedingPlatform/DBScripts/tree/master/setuputils/scripts#liquibase

They can be used to apply the changelogs manually.

Linux:

# Example setup
user=root
password=
port=3306
mysql_connector=/opt/mysql-connector-java-5.1.44.jar
mysql_path=/usr/bin/mysql
liquibase_folder=/opt/ibp/bms4/Middleware/src/main/resources

passcommand=-p$password
if [ -z $password ]; then
    passcommand=""
fi

# workbench

eval "java -jar /opt/liquibase-core-3.5.0.jar \\
  --classpath=$mysql_connector \\
  --driver=com.mysql.jdbc.Driver \\
  --changeLogFile=$liquibase_folder/liquibase/workbench_master.xml \\
  --url='jdbc:mysql://localhost:3306/workbench' \\
  --logLevel=debug \\
  --username=$user \\
  --password=$password \\
  update"

# crops
$mysql_path -N -u $user $passcommand --port=$port -e "SELECT db_name FROM workbench.workbench_crop" | while read DB_NAME; do
  eval "java -jar /opt/liquibase-core-3.5.0.jar \\
  --classpath=$mysql_connector \\
  --driver=com.mysql.jdbc.Driver \\
  --changeLogFile=$liquibase_folder/liquibase/crop_master.xml \\
  --url='jdbc:mysql://localhost:3306/$DB_NAME' \\
  --logLevel=debug \\
  --username=$user \\
  --password=$password \\
  update"
done

Windows:

:: Example setup
set USER=root
set PASSWORD=
set PORT=3306
set MYSQL_CONNECTOR="C:\Program Files (x86)\MySQL\Connector.J 5.1\mysql-connector-java-5.1.44-bin.jar"
:: No spaces, no quotes. use mklink /J if neccessary
set MYSQL_PATH=C:/mysql/bin/mysql

set PASSCOMMAND=-p%PASSWORD%
if "%PASSWORD%"=="" set PASSCOMMAND=""

:: workbench

java -jar liquibase-core-3.5.0.jar^
    --classpath=%MYSQL_CONNECTOR%^
    --driver=com.mysql.jdbc.Driver^
    --changeLogFile=liquibase/workbench_master.xml^
    --url="jdbc:mysql://localhost:%PORT%/workbench"^
    --logLevel=debug^
    --username=%USER%^
    --password=%PASSWORD%^
    update

:: crops

for /F "delims=" %%G in (
        '%MYSQL_PATH% -N -u %USER% %PASSCOMMAND% --port=%PORT% -e "SELECT db_name FROM workbench.workbench_crop"'
    ) do (
        java -jar liquibase-core-3.5.0.jar^
            --classpath=%MYSQL_CONNECTOR%^
            --driver=com.mysql.jdbc.Driver^
            --changeLogFile=liquibase/crop_master.xml^
            --url="jdbc:mysql://localhost:%PORT%/%%G"^
            --logLevel=debug^
            --username=%USER%^
            --password=%PASSWORD%^
            update
    )

Running changelogs from BMSAPI classpath

This method can be used to test changelogs in a BMS testing instance without running any scripts or modifying the existing changelogs inside the middleware.jar. By creating the file bmsapi/WEB-INF/classes/liquibase/crop_master.xml, it will override the crop_master changelog in middleware (will run instead of that one) because LiquibaseInitBean loads the file from the classpath.

Developers

Read the online documentation to learn core concepts - such as changelog, changeset, precondition - and how Liquibase works in detail.
Get familiar to changelog format. We have chosen to use the de facto XML.
Understand how Liquibase is integrated in BMS codebase. See org.generationcp.middleware.liquibase.LiquibaseInitBean in Middleware and the applicationContext.xml of the BMSAPI application.
LiquibaseInitBean wakes up on BMSAPI application startup and knows what changes to apply to all crop and workbench databses, and applies them as needed. It knows what is applied and what is not, using its own DATABASECHANGELOG table in each database.
Understand best practices of writing database change logs and follow them. Enforce those in code reviews. e.g. Some of the "must follow" best practices are:
- Each release version must have its own changelog file. Follow the existing structure of how the changelogs are organized.
- Always write pre-conditions for each database change e.g. use a precondition to check that index exists, before a drop index type change. See existing examples.
- Pay special attention to what should be set as the action on precondition failure.
- Never update changelog of a version that is considered "released" to clients. Liquibase will detect if changes are made to previous versions of changelogs and throw error and prevent statrup if this happens.
- Never put organization specific data/changes in the product change logs. They should remain manual scripts. Example : CIMMYT specific data/inserts.
Author and identifiers must be assigned to ensure uniqueness within a changelog.
If you are using a separate complete BMS environment for testing / verification purposes, make sure that you re-deploy all WAR files if a Liquibase change has been made to avoid checksum errors during startup.

Current implementation structure

Files are located within the Middleware project, inside src/main/resources/liquibase.
This folder contains two subdirectories and two main changelog files, one of each type for workbench and crop related changes.
Each subdirectory is meant to store changelog files for its type; i.e., workbench_changelog contains changelog files meant for the workbench database. Changes are split per release version using the version name as the file name, so you can expect files named 4_0_0_BETA_9.xml, 4_0_0_BETA_12.xml inside.
The main changelog files in src/main/resources/liquibase maintains a list of the individual, per-release changelog files as imports. If a new changelog file needs to be added due to preparation for a new release, this file needs to be listed within the main changelog file as well.

Testers

Read the online documentation to understand at high level the concepts and how Liquibase works.
When auto update is enabled, test that the changesets are applied on startup of BMSAPI application without any errors or manual interventions. Example of log output when Liquibase does its work successfully on startup looks like:

INFO 5/05/16 5:04 PM: liquibase: Successfully acquired change log lock
INFO 5/05/16 5:04 PM: liquibase: Reading from workbench.DATABASECHANGELOG
INFO 5/05/16 5:04 PM: liquibase: Successfully released change log lock
INFO 5/05/16 5:04 PM: liquibase: Successfully acquired change log lock
INFO 5/05/16 5:04 PM: liquibase: Reading from ibdbv2_maize_merged.DATABASECHANGELOG
INFO 5/05/16 5:04 PM: liquibase: Successfully released change log lock
INFO 5/05/16 5:04 PM: liquibase: Successfully acquired change log lock
INFO 5/05/16 5:04 PM: liquibase: Reading from ibdbv2_wheat_merged.DATABASECHANGELOG
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::1::naymesh: Unique constraint added to udflds(ftable, ftype, fcode)
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::1::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::1::naymesh ran successfully in 64ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::2::naymesh: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::2::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_10.xml::2::naymesh ran successfully in 5ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::1::naymesh: Table key_sequence_register created
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::1::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::1::naymesh ran successfully in 128ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::2::aldrin: Index unique dropped from table phenotype_outlier
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::2::aldrin: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::2::aldrin ran successfully in 2013ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::3::aldrin: Columns date_modified(TIMESTAMP) added to phenotype_outlier
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::3::aldrin: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::3::aldrin ran successfully in 53ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::4::abhishek: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::4::abhishek: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::4::abhishek ran successfully in 3ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::5::abhishek: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::5::abhishek: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::5::abhishek ran successfully in 3ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::6::abhishek: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::6::abhishek: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::6::abhishek ran successfully in 3ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::7::naymesh: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::7::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::7::naymesh ran successfully in 3ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::8::naymesh: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::8::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::8::naymesh ran successfully in 4ms
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::9::naymesh: Custom SQL executed
INFO 5/05/16 5:04 PM: liquibase: classpath:liquibase/crop_master.xml: classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::9::naymesh: ChangeSet classpath:liquibase/crop_changelog/4_0_0_BETA_11.xml::9::naymesh ran successfully in 3ms
INFO 5/05/16 5:04 PM: liquibase: Successfully released change log lock

Data managers

Read the online documentation to understand high level concepts and how it works.
Coordinate database changes with development team so that changesets can be developed and added to Liquibase config for each change in every release. This applies to each and every change made in DBScripts master.
If there is a need to make changes or add to existing DBScripts, make them as one small set of related changes in one commit at a time. This helps a lot in developing Liquibase changesets.

Misc other things

What are other alternatives to Liquibase?

Breeding Management System Documentation

Provide feedback

Saved searches

Use saved searches to filter your results more quickly