CPS-2061 Liquibase Steps Condensing
- David McWeeney
References:
CPS-2061: Liquibase Steps Condensing and CleanupClosed
CPS-1886 Spike Liquibase Steps Condensing
Prerequisite:
Step through liquibase steps
Check if can be removed (compare with integration tests)
Check if NCMP to be seperated
Check if data (model) update or db update
How - Option 1:
Export current database tables to sql statements
Table definition
Data (no. Removing redundant data)
Merge SQL statements into single SQL file in an order by considering constraints between DB tables
Create DatabaseChangeLog to add exported sql statement
How - Option 2:
Remove redundant data (table below).
Merge subsequent (post tables creation) yaml files (2-21) into createCPSTables yaml file.
Test schemas create successfully.
Add dataspaces (NCMP-Admin, NCP-Operational) and associated schemas, anchors etc (if they do not lready exist) through the model loader.
Step Analysis:
Step | Date | Filename | Action | Comments |
|---|---|---|---|---|
01 | 2021-06-16 | 01-createCPSTables.yaml | Keep | This creates the tables for CPS |
02 | 2021-06-16 | 02-loadData-dataspace.yaml | Drop | This pre-loads the DB with obsolete / currently invalid test data |
03 | 2021-06-16 | 03-loadData-schema-set.yaml | Drop | Obsolete test data - see 02 |
04 | 2021-06-16 | 04-loadData-anchor.yaml | Drop | Obsolete test data - see 02 |
05 | 2021-06-16 | 05-loadData-fragment.yaml | Drop | Obsolete test data - see 02 |
06 | 2021-03-23 | 06-delete-not-required-fragment-index.yaml | Drop | Can this be removed if we remove where that index is created in 1-21 |
07 | 2021-04-07 | 07-update-yang-resource-checksums.yaml | Drop | Obsolete test data - see 02 |
08 | 2021-05-05 | 08-update-yang-resources.yaml | Drop | Obsolete test data - see 02 |
09 | 2021-05-24 | 09-loadData-dmi-registry-schema-set.yaml | Drop | Redundant; model loader create DMI registry at NCMP startup |
10 | 2021-07-02 | 10-loadData-dmi-registry-fragment.yaml | Drop | Redundant; model loader create DMI registry at NCMP startup |
11 | 2021-08-04 | 11-add-column-to-yang-resources-table.yaml | Keep (merge) | Change to DB schema - could be merged with 01 |
12 | 2022-02-10 | 12-delete-all-previous-dmi-registry-schema-set.yaml | Drop | Redundant; model loader can create DMI registry at NCMP startup |
13 | 2022-02-10 | 13-insert-dmi-registry-2022-02-10-schema-set.yaml | Drop | Redundant; model loader can create DMI registry at NCMP startup |
14 | 2022-05-10 | 14-loadData-dmi-registry-2022-05-10-schema-set.yaml | Drop | Redundant; model loader can create DMI registry at NCMP startup |
15 | 2022-08-04 | 15-rename-column-yang-resource-table.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
16 | 2022-10-04 | 16-insert-cm-handle-state.yaml | Drop | This one seems to add cm-handle state to existing cm-handle data. |
17 | 2023-03-07 | 17-add-index-to-schema-set-yang-resources.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
18 | 2023-03-03 | 18-cascade-delete-fragment-children.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
19 | 2023-05-04 | 19-delete-not-required-dataspace-id-from-fragment.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
20 | 2023-05-17 | 20-change-foreign-key-id-types-to-integer.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
21 | 2023-06-28 | 21-escape-quotes-in-xpath.yaml | Keep (merge) | Change to DB schema - could be merged to 01 |
22 | 2024-01-08 | 22-fragment-id-sequence.yaml | Keep (separate) | We need to support upgrade/rollback for DB schema changes within the 6 months at least, to allow reasonable upgrade window. |
Option 2 Process:
Why no Checksum issue:
After removing the redundant data and consolidated the remaining required steps, I expected an issue with the checkSum having changed. However, it never arose.
Why? the databaseChangelog table does not have a primary key. It takes a combination of the id, author and filename as being unique.
However, condensing the changeSets means moving some of them to a different file, thus creating a new entry in the databaseChangelog table, as opposed to updating the existing one.
The checksum however, remained the same as previous.
To work around this and prevent the below issue on duplicate liquibase steps being attempted, you can add the "logicalFilePath" attribute in the changeSet. This essentially points it to the original changeset file, thus keeps track by using the same entry in the databasechangelog database, instead of creating a new one.
Duplicate liquibase steps:
We do however encounter a different issue. Because a new (checksum) entry is added to the table, liquibase cannot see that nothing changed when the step was previously applied.
It therefore tries to reapply some steps, resulting in errors such as "column does not exist".
This is because when trying to rename a column, the old column name no longer exists.
To work around this, I added a "preCondition" to any impacted changeSet, to check that a column, index etc exists first, before we apply the relevant step.
Initial NCMP data:
Previously, liquibase added some NCMP data such as and the model loader subsequently expected it to be there.
Since it may not be there now, things like "NCMP-Admin" would have to be added in the modelLoader if it did not exist already.
Hence some additions to the NCMP ModelLoader to ensure some initial data exists.