References:
CPS-2061 - Getting issue details... STATUS
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 table creation) yaml files (2-21) into createCPSTables yaml file.
- Test schemas create successfully.
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:
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, we have just moved the changeSets 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.
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 step.