Skip to content

Create the liquibase changelog file#

The Changelog file has to be put into the catalogue/migrations/liquibase/databasetype directory (where databasetype is oracle or postgres).

Please respect the following naming convention of the Changelog file: CURRENT_VERSION_NUMBERToNEW_VERSION_NUMBER.sql. Example: for a schema upgrade from version 1.0 to 1.1, the Changelog file should be named 1.0to1.1.sql

1. Update the CTA_CATALOGUE table#

Update the CTA_CATALOGUE table with the status "UPGRADING" and the NEXT_SCHEMA_VERSION_MAJOR/MINOR numbers. The NEXT_* numbers are the future schema version numbers.

--changeset ccaffy:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;

UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=1;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=1;

--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';

Danger

This ChangeSet is very important as it will prevent CTA components from starting during an upgrade of the Catalogue schema

2. Create your own ChangeSets#

Each changeset should be preceded by the following statements:

--changeset <author_name>:<id> failOnError:true dbms:<databasetype>
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
  • Replace the <author_name> with your cern username
  • Replace the <id> with the number of the changeset (from 1 to N)
  • Replace <databasetype> by either oracle or postgresql or mariadb. This will prevent the ChangeSet to be executed if the database to migrate file.

The precondition-sql-check is useful to check that the Catalogue schema version is the one expected before executing the update.

3. Update the CTA_CATALOGUE table with the new version of the schema#

--changeset ccaffy:12 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=1;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0

4. Update the CTA_CATALOGUE table with the status 'PRODUCTION'#

--changeset ccaffy:13 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';

--rollback UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=1;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=1;

Tip

You help yourself by having a look at what is already done in the CTA/catalogue/migrations/liquibase/oracle directory.