CPS-1886 Spike Liquibase Steps Condensing
- Luke Gleeson (Unlicensed)
- David McWeeney
References
CPS-1886: Spike: Liquibase Steps CondensingClosed
Scope
Currently we have Liquibase running db setup for CPS and NCMP with many steps
We want to
condense the steps
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: anchorTo 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