DB Schema Changes (Liquibase Change Logs)

Prerequisites

Identify if the change required is

1. a Postgres DB Schema update (ie. CPS Core hardcoded schema)
   OR

2. 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

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

Applying Pending Changes

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

see also Liquibase Change Log (sets) Testing

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. 

changelog-master.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

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

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

Table once created within SqlDeveloper

Open

Adding/Editing Columns

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

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

test table with fk model.

Open

Rename Column

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

Rename Column within test table

Add New Column

To add a column use the following syntax.

Add Columns within test table

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

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

Loading Data into 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

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'.

Loading a new dmi-registry Yang Resource and dmi-registry Yang Resource Schema Set

In order to load a new Yang Resource, we want to first remove the previous Yang Resource. Create a new Changelog file preferably following this naming convention: X-insert-dmi-registry-YYYY-MM-DD-schema-set.yaml where X is +1 to the previous changelog number.
Within this changelog we are going to add 4 changesets. The first changeset we need to remove the previous yang resource relation from the yang_resource_schema_set table to remove the mapping between this yang resource and the related schema set. We also need to implement the rollback so that this can be added back in a rollback scenario.

Please see example and change the YYYY-MM-DD of the relevant file to that of the Yang Resource which is currently in the yang_reource/schema_set_yang_resources table and the X in id to that of the number of the changelog:

Remove Yang Resource relation from schema set yang resources

Now that we have deleted the yang resource from the schema_set_yang_resources table, we can remove the yang resource from the yang_resource table using the second changeset. We must also add a rollback function to undo this change.

Please see example and again update the YYYY-MM-DD of relevant file to that of the yang resource in the table and update the X in changeset id to that of the changelog + .1:

Remove Yang Resource from Yang Resource Table

At this point it is recommended to apply the pending changes and check that the relevant resource has been removed from the yang_resource and schema_set_yang_resources tables as well as rollback to ensure it is reloaded afterwards.
If this is successful we can implement the changesets to add a new yang resource to the yang_resource table with relevant rollback function to remove the resource.

Please see example where YYYY-MM-DD is for the new Yang Resource and the X in id should be that of the changelog id:

Load Yang Resource to yang_resource table

Now that the Yang Resource is in the yang_resource table, we will add the relation of the Yang Resource and the Schema Set using the schema_set_yang_resources table with the necessary rollback to undo.

Please see example and change YYYY-MM-DD to that of the loaded yang_resource and X to the id of the changelog:

Yang Resources Schema Set relation

The Yang Resource should now be present in the yang_resource table and the relation should be visible in the schema_set_yang_resources table. Rollbacking these changesets should revert the tables to the state they were in before any changes were made. 

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

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

Rollback Tag

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

Rollback Date

Potential Issues

If the following issue arises:

Issue the following command to clear checksums: