Remove dataspace_id column from Fragment table
- Remove dataspace_id column from fragment table and drop associated
index, foreign key constraint and uniqueness constaint.
- Add uniqueness constraint using only fragment xpath and anchor_id.
- Add not-null constraint to anchor_id.
- Update code and test data to remove references to dataspace_id.
- Repopulate the dataspace_id in Liquibase rollback
(verified with manual testing of update/rollback).
Issue-ID: CPS-1677
Signed-off-by: danielhanrahan <daniel.hanrahan@est.tech>
Change-Id: I467cccba25ac5d884ec790064ca58150281b7e1d
diff --git a/cps-ri/src/main/resources/changelog/changelog-master.yaml b/cps-ri/src/main/resources/changelog/changelog-master.yaml
index bf172d8..40e23e2 100644
--- a/cps-ri/src/main/resources/changelog/changelog-master.yaml
+++ b/cps-ri/src/main/resources/changelog/changelog-master.yaml
@@ -52,3 +52,5 @@
file: changelog/db/changes/17-add-index-to-schema-set-yang-resources.yaml
- include:
file: changelog/db/changes/18-cascade-delete-fragment-children.yaml
+ - include:
+ file: changelog/db/changes/19-delete-not-required-dataspace-id-from-fragment.yaml
diff --git a/cps-ri/src/main/resources/changelog/db/changes/19-delete-not-required-dataspace-id-from-fragment.yaml b/cps-ri/src/main/resources/changelog/db/changes/19-delete-not-required-dataspace-id-from-fragment.yaml
new file mode 100644
index 0000000..7b91bd5
--- /dev/null
+++ b/cps-ri/src/main/resources/changelog/db/changes/19-delete-not-required-dataspace-id-from-fragment.yaml
@@ -0,0 +1,109 @@
+# ============LICENSE_START=======================================================
+# Copyright (C) 2023 Nordix Foundation.
+# ================================================================================
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+# SPDX-License-Identifier: Apache-2.0
+# ============LICENSE_END=========================================================
+
+databaseChangeLog:
+
+ - changeSet:
+ id: 19-1
+ author: cps
+ changes:
+ - dropIndex:
+ indexName: FKI_FRAGMENT_DATASPACE_ID_FK
+ tableName: fragment
+ rollback:
+ - createIndex:
+ columns:
+ - column:
+ name: dataspace_id
+ indexName: FKI_FRAGMENT_DATASPACE_ID_FK
+ tableName: fragment
+
+ - changeSet:
+ id: 19-2
+ author: cps
+ changes:
+ - dropUniqueConstraint:
+ constraintName: fragment_dataspace_id_anchor_id_xpath_key
+ tableName: fragment
+ - addUniqueConstraint:
+ columnNames: anchor_id, xpath
+ constraintName: fragment_anchor_id_xpath_key
+ tableName: fragment
+ rollback:
+ - dropUniqueConstraint:
+ constraintName: fragment_anchor_id_xpath_key
+ tableName: fragment
+ - addUniqueConstraint:
+ columnNames: dataspace_id, anchor_id, xpath
+ constraintName: fragment_dataspace_id_anchor_id_xpath_key
+ tableName: fragment
+
+ - changeSet:
+ id: 19-3
+ author: cps
+ changes:
+ - dropForeignKeyConstraint:
+ baseTableName: fragment
+ constraintName: fragment_dataspace_id_fkey
+ rollback:
+ - addForeignKeyConstraint:
+ baseColumnNames: dataspace_id
+ baseTableName: fragment
+ constraintName: fragment_dataspace_id_fkey
+ deferrable: false
+ initiallyDeferred: false
+ onDelete: NO ACTION
+ onUpdate: NO ACTION
+ referencedColumnNames: id
+ referencedTableName: dataspace
+ validate: true
+
+ - changeSet:
+ id: 19-4
+ author: cps
+ changes:
+ - dropColumn:
+ columnName: dataspace_id
+ tableName: fragment
+ rollback:
+ - addColumn:
+ tableName: fragment
+ columns:
+ - column:
+ name: dataspace_id
+ type: INTEGER
+ - sqlFile:
+ path: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
+ - addNotNullConstraint:
+ tableName: fragment
+ columnName: dataspace_id
+ columnDataType: INTEGER
+
+ - changeSet:
+ id: 19-5
+ author: cps
+ changes:
+ - addNotNullConstraint:
+ tableName: fragment
+ columnName: anchor_id
+ columnDataType: BIGINT
+ rollback:
+ - dropNotNullConstraint:
+ tableName: fragment
+ columnName: anchor_id
+ columnDataType: BIGINT
diff --git a/cps-ri/src/main/resources/changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql b/cps-ri/src/main/resources/changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
new file mode 100644
index 0000000..9c60db0
--- /dev/null
+++ b/cps-ri/src/main/resources/changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
@@ -0,0 +1,25 @@
+/*
+ ============LICENSE_START=======================================================
+ Copyright (C) 2023 Nordix Foundation.
+ ================================================================================
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+ http://www.apache.org/licenses/LICENSE-2.0
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+ SPDX-License-Identifier: Apache-2.0
+ ============LICENSE_END=========================================================
+*/
+
+UPDATE
+ fragment AS f
+SET
+ dataspace_id = a.dataspace_id
+FROM
+ anchor AS a
+WHERE
+ f.anchor_id = a.id;