Skip to content

Deprecated

This page is deprecated and may contain information that is no longer up to date.

Tools

Liquibase

Liquibase is an open source tool for database schema change management. CTA uses Liquibase to perform catalogue schema upgrades.

The version of Liquibase in CentOS 7 is an older version which does not have all the required features. It is therefore necessary to download and install Liquibase from upstream sources as described below.

Note that although a snap install of Liquibase exists, it does not support Oracle JDBC out-of-the-box, so the method below is preferred.

Check that the JDBC Oracle driver is installed

rpm -qf /usr/lib/oracle/19.3/client64/lib/ojdbc8.jar
# oracle-instantclient19.3-basic-19.3.0.0.0-2.x86_64

Check that /etc/tnsnames.ora is installed

This file contains the Oracle nameserver configuration, to map the connection string in the CTA Catalogue configuration file to the Oracle DB.

rpm -qf /etc/tnsnames.ora
# oracle-instantclient-tnsnames.ora-1.4.4-1.el7.cern.noarch

Install Liquibase

  1. Download Liquibase 4.x from the download page. Choose Linux then Just the files and download the tar file.

  2. Extract liquibase-4.x.y.tar.gz and add the directory path to your $PATH:

# Insert this line in your .bashrc file
export PATH=${PATH}:~/programs/liquibase
  1. Verify the version of liquibase you are running
liquibase --version

Configure Liquibase

Configuration file

In order to run Liquibase against a specific database, a configuration file has to be provided so that the tool will know how to connect to the database-to-migrate and which driver it has to use.

The configuration file is a .properties file that looks like this:

url: jdbc:oracle:thin:@HOST:PORT/SERVICE_NAME
username: USERNAME
password: PASSWORD
driver: oracle.jdbc.OracleDriver
classpath: ../drivers/ojdbc8.jar

Some examples of .properties files for Oracle and PostgreSQL can be found in the catalogue/migrations/liquibase/properties-examples directory.

The classpath attribute

The classpath attribute is the path of the database-specific driver that will be used by Liquibase to establish the connection and run the SQL statements for the upgrade.

The different drivers can be downloaded here:

Liquibase core concepts

Changelog file

A changelog file is a .sql text file that contains SQL Statements and Liquibase-related metadata. It should start with --liquibase formatted sql.

--liquibase formatted sql

--changeset ccaffy:4 failOnError:true dbms:oracle
ALTER INDEX TEMP_T_F_I_B_ARCHIVE_FILE_ID_I RENAME TO TEMP_T_F_I_B_AFI_IDX;
--rollback ALTER INDEX TEMP_T_F_I_B_AFI_IDX RENAME TO TEMP_T_F_I_B_ARCHIVE_FILE_ID_I;
ChangeSet

A ChangeSet is a set of SQL statements that Liquibase executes within one transaction.

It is identified by the author of the changeset and an id (that must be unique within the same changelog file).

Optionally, the ChangeSet can be made rollbackable with the --rollback command.

Tracking tables

Liquibase will add two tables to the database it will run against:

  • DATABASECHANGELOG that stores information about the ChangeSets applied to the database
  • DATABASECHANGELOGLOCK that ensures only one instance of Liquibase will run an upgrade at a time

Command-line tool

The syntax of the command line to run Liquibase is the following :

liquibase --defaultsFile=path_to_configuration_properties --changeLogFile=path_to_change_log_file <command>

Several commands can be executed. They can be found in the documentation of the liquibase command line tool.

Test the correct installation of liquibase

In order to test your installation/configuration of Liquibase, you can run the following command that will dump the database schema to a schema.xml file:

liquibase --defaultsFile=path_to_configuration_properties --changeLogFile=./schema.xml generateChangeLog

This command will create a schema.xml file in the current working directory.

Liquibase documentation

The full documentation of Liquibase is available here.