Versions Compared

Old Version 38

changes.mady.by.user Dylan Byrne

Saved on

compared with

New Version Current

changes.mady.by.user Luke Gleeson

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

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.

...

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.

...

Code Block
languageyml
titleAdd Columns within test table
collapsetrue
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.

...

Code Block
languageyml
titleLoad Loading Data into Test Table
collapsetrue
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.

...

Code Block
languagebash
titleyang_resource@2021-12-13.csv
collapsetrue
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 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@<revision>'.

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
languagebash
curl --location --request POST 'http://localhost:8080/cps/api/v1/dataspaces/<dataspace name>/schema-sets' --header 'Authorization: Basic Y3BzdXNlcjpjcHNyMGNrcyE=' --form 'file=<path to local file>' --form 'schema-set-name='<schema set name>'

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

...

titleLoading newly created yang resource csv into local db directly

...

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.

Image Added

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

Image Added


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
languageyml
titleYang resource Change set exampleRemove Yang Resource relation from schema set yang resources
collapsetrue
databaseChangeLog:
  - changeSet:
      author: cps
      label: dmi-registry-schema-preload-delete-revision-YYYY-MM-DD
        id: 9.7X
      loadUpdateDatasql:
 delete from schema_set_yang_resources
     encoding: UTF-8
  where schema_set_id = (select id from  file: 'changelog/db/changes/data/dmi/yang_resource@2021-12-13.csv'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:
 '|'
         tableNameencoding: 'yang_resource'UTF-8
        columns:  file: 'changelog/db/changes/data/dmi/schema_set_yang_resources@YYYY-MM-DD.csv'
          - column:quotchar: '"'
              header:  nameprimaryKey: 'schema_set_id,yang_resource_id'
              nameseparator:  name'|'
              type:  STRINGtableName: 'schema_set_yang_resources'
          - columnusePreparedStatements:
   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
languageyml
titleRemove Yang Resource from Yang Resource Table
collapsetrue
  - changeSet:            header:  content
              name: content
      author: cps
       typelabel: STRINGdmi-registry-schema-delete-revision-YYYY-MM-DD
          - column:id: X.1
      sql: delete from yang_resource where name   header:  checksum= 'dmi-registry@YYYY-MM-DD.yang'
      rollback:
        nameloadUpdateData: checksum
          encoding: UTF-8
   type: STRING
      rollback:
file: 'changelog/db/changes/data/dmi/dmi-registry@YYYY-MM-DD.csv'
         - sqlonlyUpdate: 'false'
          primaryKey: 'id'
  sql: delete from yang_resource where name = 'dmi-registry@2021-12-13.yang'

See also Liquibase Change Log (sets) Testing#Stepstocreatechecksumforyang-resource

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

        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
languageyml
titleLoad Yang Resource to yang_resource table
collapsetrue
- 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
              name: checksum
              type: STRING
          - column:
              header: module_name
              name: module_name
              type: STRING
          - column:
              header: revision
              name: revision
              type: STRING
      rollback:
        - sql:
            sql: delete
Code Block
languagebash
titleschema_set_yang_resource@2021-12-13.csv
collapsetrue
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@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 relevant change-log YAML file

Code Block
languageyml
titleYang resource Change set exampleResources Schema Set relation
collapsetrue
  - 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' changeSet:
        quotcharauthor: '"'cps
        primaryKeylabel: 'schema_set_id,yang_resource_id'dmi-registry-schema-load-revision-YYYY-MM-DD
        separatorid: '|'X.3
      sql: INSERT tableName:INTO 'schema_set_yang_resources' (schema_set_id, yang_resource_id)
        usePreparedStatements:VALUES ((select true
id from schema_set where name  rollback:
        - sql:= 'ncmp-dmi-registry-model'), (select id from yang_resource where name = 'dmi-registry@YYYY-MM-DD.yang'));
            sql: >rollback:
              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@2021registry@YYYY-12MM-23DD.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.

...