DB Schema Changes (Liquibase Change Logs)

Prerequisites

Identify if the change required is

a Postgres DB Schema update (ie. CPS Core hardcoded schema)
OR
a change in a deployed a Yang Schema set (module)
In this case

In either case the proposed change should be discussed and agreed with the team first (ie. create proposal page for the required schema change e.g. with proposed yang file)
Example: CPS-677: Support 'public' Cm Handle Properties

Getting Started

The best way to start working with Liquibase change logs is to load the current CPS change logs into Postgress.

Build Docker for Postgres

These steps are applied first to load the already existing change-logs within CPS.

To start an Postgres database instance using docker issue the following command

docker run --name postgres -p 5432:5432 -d -e POSTGRES_DB=cpsdb -e POSTGRES_USER=cps -e POSTGRES_PASSWORD=cps postgres:13.2-alpine

Applying Pending Changes

To apply any changes made to the changelog within CPS, run the following command from the cps-ri directory

mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:update  -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb  -Dliquibase.username=cps  -Dliquibase.password=cps  -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml

Change-Log Master Order

The change-log will then be updated running the following yaml files in order, based on the changelog-master.yaml file within the cps-ri→ src→ main→ resources→ changelog→db.changes directory.

Anytime a new change-log YAML is added, it will need to be included in this file for the changes to be applied within CPS

changelog-master.yaml

databaseChangeLog:
  - include:
      file: changelog/db/changes/01-createCPSTables.yaml
  - include:
      file: changelog/db/changes/02-loadData-dataspace.yaml
  - include:
      file: changelog/db/changes/03-loadData-schema-set.yaml
  - include:
      file: changelog/db/changes/04-loadData-anchor.yaml
  - include:
      file: changelog/db/changes/05-loadData-fragment.yaml
  - include:
      file: changelog/db/changes/06-delete-not-required-fragment-index.yaml
  - include:
      file: changelog/db/changes/07-update-yang-resource-checksums.yaml
  - include:
      file: changelog/db/changes/08-update-yang-resources.yaml
  - include:
      file: changelog/db/changes/09-loadData-dmi-registry-schema-set.yaml
  - include:
      file: changelog/db/changes/10-loadData-dmi-registry-fragment.yaml
  - include:
      file: changelog/db/changes/11-add-column-to-yang-resources-table.yaml

Adding Liquibase Changes

Adding New Tables

To add new tables to tables within CPS using the Liquibase change-log go to the 01-createCPSTables.yaml file.

Under the databaseChangeLog object each change-set is defined with a unique id (Similar to how an array is defined within YAML).

Here the columns are also defined, along with the constraints of a specific column.

Here is the Change-Log for a tabel that has been previously added CPS

Anchor Table

databaseChangeLog:
  - changeSet:
      id: 1-1
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: anchor_pkey
                  name: id
                  type: BIGINT
              - column:
                  name: name
                  type: TEXT
              - column:
                  name: schema_set_id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: dataspace_id
                  type: INTEGER
            tableName: anchor

To add a new table, under the latest change-set add a new change-set, incrementing the ID by one.

The id of the change-set is based on the change-log YAML file number, and the change-set order. Eg: 1-1, 1-2, 2-1, 2-2, 3-1, 3-2 etc.

- changeSet:
    id: 1-38
    author: cps

Define a changes object, all new edits will go under this section. Create a new createTable object, and under here the table name can be defined along with the column information/constraints as stated above.

Test Table Example

  - changeSet:
      id: 1-38
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: test_table_pkey
                  name: id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: column_1
                  type: TEXT
            tableName: test_table

Table once created within SqlDeveloper

Adding/Editing Columns

If modifying deployed yang module(s) refer to Modifying CPS Client-Tables (Yang Modules) instead

Once the table is created, columns can be added and edited using a similar syntax to above.

Foreign Key Contraints.

To add foreign key constraints, within the 01-createCPSTables.yaml file create a new change-set and increment the change-set id as done above.

In the following example, I have created a second test table, which will contain a foreign key shared with the primary key of the first test_table.

Test Table With Foreign Key Example

  - changeSet:
      id: 1-39
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: test_table__with_fk_pkey
                  name: test_table_with_fk_id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: column_1
                  type: TEXT
              - column:
                  constraints:
                    nullable: false
                  name: test_table_pk
                  type: INTEGER
            tableName: test_table_with_fk

From the table above we have created a column of type integer called test_table_pk. This is a reference to the primary key within the first test table created above, which will be our foreign key in this table.

Below is the syntax within YAML for creating a foreign key relation between both tables, the base table and column being the table we are creating the constraint on, and the referenced table and column being the table which contains the PK (id) column we are creating our FK relationship with.

Creating Foreign Key Relation Example

  - changeSet:
      id: 1-40
      author: cps
      changes:
        - addForeignKeyConstraint:
            baseColumnNames: test_table_pk
            baseTableName: test_table_with_fk
            constraintName: test_table_with_fk_fkey
            deferrable: false
            initiallyDeferred: false
            onDelete: NO ACTION
            onUpdate: NO ACTION
            referencedColumnNames: id
            referencedTableName: test_table
            validate: true

test table with fk model.

Rename Column

To rename a column use the following syntax, specifying the table and the oldColumnName, along with the new Column Name.

There is currently no YAML file within CPS specifically for renaming table columns, this example is done under the presumption that a new file has been created for renaming columns as seen by the updated id from the example.

Rename Column within test table

databaseChangeLog:
  - changeSet:
      id: 12-1
      author: cps
      changes:
        - renameColumn:
            newColumnName: renamed_column
            oldColumnName: column_1
            tableName: test_table

Add New Column

To add a column use the following syntax.

Add Columns within test table

databaseChangeLog:
  - changeSet:
      id: 13-1
      author: cps
      changes:
        - addColumn:
            tableName: test_table
            columns:
              - column:
                  name: new_column1
                  type: TEXT
              - column:
                  name: new_column2
                  type: INTEGER

Loading Data

The data for each table is defined using the CSV files located within cps-ri→ resources→ changelog→db.changes→ data→dmi.

test_table.csv

renamed_column|new_column1|new_column2
test_table_column_name|columnValue|3

Then each table has a specific YAML change-set which loads the CSV using the following syntax.

Load Data into Test Table

databaseChangeLog:
  - changeSet:
      author: cps
      label: xnf-data-preload
      id: 14.1
      loadUpdateData:
        encoding: UTF-8
        file: 'changelog/db/changes/data/test_table.csv'
        onlyUpdate: 'false'
        primaryKey: 'id'
        quotchar: '"'
        separator: '|'
        tableName: 'test_table'

test table with data

Modifying CPS Client-Tables (Yang Modules)

Because of the generic way CPS stores any client data using Yang Modules often schema changes only affect these yang modules and the associated data not the actual Postgress DB schema!
The following sections describe how such changes could be implemented using Liquibase.

Loading data for Yang Resource

To load a yang resource, create a new yang resource csv file to load the data (as outlined above) in the following format 'yang_resource_@<revision>' (the revision is usually the date of the change in the format yyyy-mm-dd)

yang_resource@2021-12-13.csv

name|content|checksum
dmi-registry@2021-12-13.yang|"module dmi-registry {

  yang-version 1.1;

  namespace \"org:onap:cps:ncmp\";

  prefix dmi-reg;

  organization \"Nordix Foundation\";

  contact \"rahul.tyagi@est.tech\";

  revision \"2021-12-13\" {
   description
   \"Added new list of public additonal properties for a Cm-Handle which are exposed to clients of the NCMP interface. \";
  }

  container dmi-registry {

    list cm-handles {

      key \"id\";

      leaf id {
        type string;
      }

      leaf dmi-service-name {
        type string;
      }

      leaf dmi-data-service-name {
        type string;
      }

      leaf dmi-model-service-name {
        type string;
      }

      list additional-properties {
        key \"name\";
        leaf name {
          type string;
        }
        leaf value {
          type string;
        }
      }
      list public-additional-properties {
        key \"name\";
        leaf name {
          type string;
        }
        leaf value {
          type string;
        }
      }
    }
  }
}
"|4899c384835ec48194ab5e3837549d32b844e02cfc81e9205234fa36354f3fe4

Generating new Yang Resource

First create a new yang file and its contents within 'cps-ri/src/main/resources/changelog/db/changes/data/yang-models/' and give it the name format of 'yang_resource_name@<revision>'. eg: dmi-registry@2021-12-13.yang.

Once this is done, within the cps-parent pom file add the yang resource you have just created as an argument as seen below.

Then, run a mvn clean install on your machine, and it will generate a csv file in the following directory 'cps-ri/src/main/resources/changelog/db/changes/data/dmi/generated-csv'.

Once this is done, then add the change-set for yang resource you have created to load the data into the DB.

Yang resource Change set example

  - changeSet:
      author: cps
      label: dmi-registry-schema-preload
      id: 9.7
      loadUpdateData:
        encoding: UTF-8
        file: 'changelog/db/changes/data/dmi/generated_yang_resource_dmi-registry@2021-12-13.csv'
        onlyUpdate: 'false'
        primaryKey: 'id'
        quotchar: '"'
        separator: '|'
        tableName: 'yang_resource'
        columns:
          - column:
              header:  name
              name:  name
              type:  STRING
          - column:
              header:  content
              name: content
              type: STRING
          - column:
              header:  checksum
              name: checksum
              type: STRING
      rollback:
        - sql:
            sql: delete from yang_resource where name = 'dmi-registry@2021-12-13.yang'

Yang Resource Schema Set

Data will also need to be inserted into the yang_resource_schema_set table to create a mapping between the new yang resource and the necessary schema set.

Create a csv to define the data in the format of 'schema_set_yang_resources@<revision>'

schema_set_yang_resource@2021-12-13.csv

schema_set_id|yang_resource_id
(select id from schema_set where name='ncmp-dmi-registry-model')|(select id from yang_resource where name='dmi-registry@2021-12-13.yang')

Then add the change-set to the relevant change-log YAML file

Yang resource Change set example

  - changeSet:
      author: cps
      label: dmi-registry-schema-preload
      id: 9.8
      loadUpdateData:
        encoding: UTF-8
        file: 'changelog/db/changes/data/dmi/schema_set_yang_resources@2021-12-13.csv'
        quotchar: '"'
        primaryKey: 'schema_set_id,yang_resource_id'
        separator: '|'
        tableName: 'schema_set_yang_resources'
        usePreparedStatements:  true
      rollback:
        - sql:
            sql: >
              delete from schema_set_yang_resources
              where schema_set_id = (select id from schema_set where name = 'ncmp-dmi-registry-model')
              and yang_resource_id = (select id from yang_resource where name = 'dmi-registry@2021-12-23.yang')

Rollback

As seen above, the rollback tag is included within the change-set in the case that and updates made need to be reverted.

Types of Rollback

To run the rollback to revert a specified number of change-sets sequentially run the following command with the number of change-sets you want to run back.

Rollback Count

mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback  -Dliquibase.rollbackCount=<number of change sets to rollback> -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb  -Dliquibase.username=cps  -Dliquibase.password=cps  -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml

To run the rollback to revert all changes to a database that were made after a specific tag

Rollback Tag

mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback  -Dliquibase.rollbackTag=<liquibase tag to rollback to> -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb  -Dliquibase.username=cps  -Dliquibase.password=cps  -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml

To run the rollback to revert all changes back to a specific date run.

Rollback Date

mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback  -Dliquibase.rollbackDate=<date to rollback to> -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb  -Dliquibase.username=cps  -Dliquibase.password=cps  -Dliquibase.changeLogFile=src/main/resources/changelog/changelog-master.yaml

Potential Issues

If the following issue arises:

Caused by: liquibase.exception.ValidationFailedException: Validation Failed:

Issue the following command to clear checksums:

mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:clearCheckSums  -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb  -Dliquibase.username=cps  -Dliquibase.password=cps