Table of Contents |
---|
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
...
title | Work in progress |
---|
Prerequisites
...
(ie. create proposal page for the required schema change e.g. with proposed yang file)
Example: CPS-677: Support 'public' Cm Handle Properties
Build Docker for Postgres
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
Note |
---|
These steps are applied first to load the already existing change-logs within CPS. |
To start an Postgres database instance using To start an Postgres database instance using docker issue the following command
Code Block | ||
---|---|---|
| ||
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
Code Block | ||
---|---|---|
| ||
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 |
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→dbri→ src→ main→ resources→ changelog→db.changes directory.
Note |
---|
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 |
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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.
...
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
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
Code Block | ||||||
| ||||||
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 |
...
Table once created within SqlDeveloper
Adding/Editing Columns
Note |
---|
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.
...
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.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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→dmiri→ resources→ changelog→db.changes→ data→dmi.
Code Block | ||||
---|---|---|---|---|
| ||||
renamed_column|new_column1|new_column2 test_table_column_name|columnValue|3 |
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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
Loading data for Yang Resource
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 resourceTo load a yang resource, create a new yang resource csv file to load the data (as outlined above) in the following format 'yang_resource_@*date-of-creation*'@<revision>' (the revision is usually the date of the change in the format yyyy-mm-dd)
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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 |
Note |
---|
Before adding the checksum in the file above, first generate the checksum within the yang_resource table following the step below |
Generating Checksum For New Yang Resource
First copy the content from the CSV file above and create a YANG file locally with it with the format 'dmi-registry@*date-of-creation*'.
Run the following command to insert a new yang resource into the yang_resource table with the checksum generated.
Note |
---|
CPS Application must be deployed from before running this command. |
Code Block | ||
---|---|---|
| ||
curl --location --request POST 'http://localhost:8080/cps/api/v1/dataspaces/*dataspacename*/schema-sets' --header 'Authorization: Basic Y3BzdXNlcjpjcHNyMGNrcyE=' --form 'file=*PATH-TO-YANG-FILE-ON-LOCAL*' --form 'schema-set-name='*NAME-OF-SCEHMA-SET-DEFINED-IN-YANG_RESOURCE*' |
Add the generated checksum into the yang resource csv above.
Once this is done, then add the change-set for yang resource you have created to load the data into the liquibase DB.
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
databaseChangeLog:
| ||||||
Code Block | ||||||
| ||||||
- changeSet: author: cps label: dmi-registry-schema-preload-delete-revision-YYYY-MM-DD id: 9.7X loadUpdateData:sql: delete from schema_set_yang_resources encoding: UTF-8 file: 'changelog/db/changes/data/dmi/yang_resource@2021-12-13.csv'where schema_set_id = (select id from schema_set where name = 'ncmp-dmi-registry-model') onlyUpdate: 'false' primaryKey: 'id'and yang_resource_id = (select id from yang_resource where name = 'dmi-registry@YYYY-MM-DD.yang') quotchar: '"'rollback: separatorloadUpdateData: '|' tableName: 'yang_resource' columns: - column: encoding: UTF-8 header: name file: 'changelog/db/changes/data/dmi/schema_set_yang_resources@YYYY-MM-DD.csv' quotchar: '"' name: name primaryKey: 'schema_set_id,yang_resource_id' typeseparator: STRING'|' - column:tableName: 'schema_set_yang_resources' usePreparedStatements: true |
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
- changeSet:
author: cps
label: dmi-registry-schema-delete-revision-YYYY-MM-DD
id: X.1
sql: delete from yang_resource where name = 'dmi-registry@YYYY-MM-DD.yang'
rollback:
loadUpdateData:
encoding: UTF-8
file: 'changelog/db/changes/data/dmi/dmi-registry@YYYY-MM-DD.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
- column:
header: module_name
name: module_name
type: STRING
- column:
header: revision
name: revision
type: STRING |
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
- changeSet: author: cps label: dmi-registry-schema-load-revision-YYYY-MM-DD id: X.2 loadUpdateData: encoding: UTF-8 file: 'changelog/db/changes/data/dmi/generated-csv/generated_yang_resource_dmi-registry@YYYY-MM-DD.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 header: content name: contentchecksum type: STRING - column: header: checksummodule_name name: checksummodule_name type: STRING rollback: - column: header: revision - sql: name: revision 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@*date-of-creation*'
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
schema_set_id|yang_resource_id (select id from schema_set where name='ncmp-dmi-registry-model')|(select id type: STRING rollback: - sql: sql: delete from yang_resource where name = 'dmi-registry@2021registry@YYYY-12MM-13DD.yang') |
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:Then add the change-set to the relevent change-log YAML file
Code Block | |||||||||
---|---|---|---|---|---|---|---|---|---|
| |||||||||
- changeSet - changeSet: author: cps label: dmi-registry-schema-preload id: 9.8 loadUpdateData: encodingauthor: UTF-8cps filelabel: 'changelog/db/changes/data/dmi/schema_set_yang_resources@2021-12-13.csv'dmi-registry-schema-load-revision-YYYY-MM-DD quotcharid: '"'X.3 sql: primaryKey: 'INSERT INTO schema_set_yang_resources (schema_set_id, yang_resource_id') separator: '|' tableName: 'schema_set_yang_resources' usePreparedStatements: true rollback:VALUES ((select id from schema_set where name = 'ncmp-dmi-registry-model'), (select id from yang_resource where name = 'dmi-registry@YYYY-MM-DD.yang')); - sqlrollback: 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 yang_resource where name = 'dmi-registry@2021-12-23.yang')where name = 'dmi-registry@YYYY-MM-DD.yang') |
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.
...
Code Block | ||||
---|---|---|---|---|
| ||||
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackCount=*number-of-change-sets-to-rollback*<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 |
...
Code Block | ||||
---|---|---|---|---|
| ||||
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackTag=*liquibase-tag-to-rollback-to*<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 |
...
Code Block | ||||
---|---|---|---|---|
| ||||
mvn compile org.liquibase:liquibase-maven-plugin:4.3.1:rollback -Dliquibase.rollbackDate=*date-to-rollback-to*<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:
...