References

CPS-1886 - Getting issue details... STATUS

Scope

Currently we have Liquibase running db setup for CPS and NCMP with many steps

We want to

  1. condense the steps
  2. separate out the NCMP steps so that they can be run by a model loader instead.


Intended method

Currently we have 16 Liquibase change set files which we want to condense down to 1 or 2 files.

In order to save the database state I exported the current database to sql statements which can be used instead of the sequential liquibase steps. This was achieved using DBeaver export functionality. 

For creating the tables we want to retain the first change set file 01-createCPSTables.yaml. However there is a constraint in this file which we can remove as it is removed in liquibase step 06. Therefore remove change set 1-21.

We can then add steps to add the sql exported data. This can be achieved with the following:

databaseChangeLog:
  - changeSet:
      author: cps
      label: test2.1
      id: 2.1
      changes:
      -  sqlFile:
           path: 'changelog/db/changes/data/cpsSqlStatements/anchor_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.2
      id: 2.2
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/dataspace_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.3
      id: 2.3
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/fragment_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.4
      id: 2.4
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/relation_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.5
      id: 2.5
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/relation_type_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.6
      id: 2.6
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/schema_node_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.7
      id: 2.7
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/schema_set_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.8
      id: 2.8
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/schema_set_yang_resources_202309191355.sql'


  - changeSet:
      author: cps
      label: test2.9
      id: 2.9
      changes:
        -  sqlFile:
             path: 'changelog/db/changes/data/cpsSqlStatements/yang_resource_202309191355.sql'

NB: There is an issue with this current implementation as an error is generated due to the foreign key constraints either existing too early or too late. The data insert may need to be separated around the foreign key constraints.

Checksum

This implementation also doesn't yet account for the checksum issues which will occur. Liquibase generates a checksum for every changeset which it enacts. If the checksum does not match for each step during start up /upgrade then an error will occur and it will fail. 

The validCheckSum should solve this issue as we can define that the error checksum is valid. This should only need to be done on the first changeset of the new changesets. It should look something like this I think (The only examples I have seen are in XML and I'm not sure it translates 1-1 for YAML):

- changeSet:
      id: 1-1
      author: cps
      validCheckSum:"8:b4fd16a20425fe377b00d81df722d604" (EXAMPLE CHECKSUM see below for how to get actual checksum)
      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 get the actual checksum it is necessary to run a liquibase upgrade and get the checksum from the error message. 

For more on valid check sum: https://www.liquibase.com/blog/what-affects-changeset-checksums



  • No labels