From b740156bb50784fc461e2fbda13cd91e3f208b38 Mon Sep 17 00:00:00 2001 From: danielhanrahan Date: Thu, 4 May 2023 22:34:41 +0100 Subject: 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 Change-Id: I467cccba25ac5d884ec790064ca58150281b7e1d --- .../main/resources/changelog/changelog-master.yaml | 2 + ...te-not-required-dataspace-id-from-fragment.yaml | 109 +++++++++++++++++++++ .../19-repopulate-dataspace-id-for-rollback.sql | 25 +++++ 3 files changed, 136 insertions(+) create mode 100644 cps-ri/src/main/resources/changelog/db/changes/19-delete-not-required-dataspace-id-from-fragment.yaml create mode 100644 cps-ri/src/main/resources/changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql (limited to 'cps-ri/src/main/resources/changelog') diff --git a/cps-ri/src/main/resources/changelog/changelog-master.yaml b/cps-ri/src/main/resources/changelog/changelog-master.yaml index bf172d8ea..40e23e2e3 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 @@ databaseChangeLog: 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 000000000..7b91bd5b3 --- /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 000000000..9c60db0b5 --- /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; -- cgit 1.2.3-korg