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 --- .../org/onap/cps/spi/entities/FragmentEntity.java | 4 - .../cps/spi/entities/FragmentEntityArranger.java | 7 +- .../spi/impl/CpsDataPersistenceServiceImpl.java | 1 - .../cps/spi/repository/FragmentQueryBuilder.java | 23 ++--- .../cps/spi/repository/FragmentRepository.java | 5 +- .../main/resources/changelog/changelog-master.yaml | 2 + ...te-not-required-dataspace-id-from-fragment.yaml | 109 +++++++++++++++++++++ .../19-repopulate-dataspace-id-for-rollback.sql | 25 +++++ 8 files changed, 150 insertions(+), 26 deletions(-) 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') diff --git a/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntity.java b/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntity.java index 62c8c19cb1..f2cfe6322f 100755 --- a/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntity.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntity.java @@ -81,10 +81,6 @@ public class FragmentEntity implements Serializable { private String attributes; @NotNull - @ManyToOne(fetch = FetchType.LAZY) - @JoinColumn(name = "dataspace_id") - private DataspaceEntity dataspace; - @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "anchor_id") @EqualsAndHashCode.Include diff --git a/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntityArranger.java b/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntityArranger.java index b7ce98e1ac..c1b1322bb5 100644 --- a/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntityArranger.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/entities/FragmentEntityArranger.java @@ -1,6 +1,6 @@ /* * ============LICENSE_START======================================================= - * Copyright (C) 2022 Nordix Foundation + * Copyright (C) 2022-2023 Nordix Foundation * Modifications Copyright (C) 2023 TechMahindra Ltd. * ================================================================================ * Licensed under the Apache License, Version 2.0 (the "License"); @@ -71,10 +71,7 @@ public class FragmentEntityArranger { private static FragmentEntity toFragmentEntity(final AnchorEntity anchorEntity, final FragmentExtract fragmentExtract) { final FragmentEntity fragmentEntity = new FragmentEntity(); - if (anchorEntity != null) { - fragmentEntity.setAnchor(anchorEntity); - fragmentEntity.setDataspace(anchorEntity.getDataspace()); - } + fragmentEntity.setAnchor(anchorEntity); fragmentEntity.setId(fragmentExtract.getId()); fragmentEntity.setXpath(fragmentExtract.getXpath()); fragmentEntity.setAttributes(fragmentExtract.getAttributes()); diff --git a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsDataPersistenceServiceImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsDataPersistenceServiceImpl.java index d0154e1163..890941bf33 100644 --- a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsDataPersistenceServiceImpl.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsDataPersistenceServiceImpl.java @@ -218,7 +218,6 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService private FragmentEntity toFragmentEntity(final AnchorEntity anchorEntity, final DataNode dataNode) { return FragmentEntity.builder() - .dataspace(anchorEntity.getDataspace()) .anchor(anchorEntity) .xpath(dataNode.getXpath()) .attributes(jsonObjectMapper.asJsonString(dataNode.getLeaves())) diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java index 76cfaa89f8..72750dcc92 100644 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentQueryBuilder.java @@ -88,8 +88,14 @@ public class FragmentQueryBuilder { final StringBuilder sqlStringBuilder = new StringBuilder(); final Map queryParameters = new HashMap<>(); - sqlStringBuilder.append("SELECT * FROM fragment WHERE "); - addDataspaceOrAnchor(sqlStringBuilder, queryParameters, dataspaceEntity, anchorEntity); + if (anchorEntity == ACROSS_ALL_ANCHORS) { + sqlStringBuilder.append("SELECT fragment.* FROM fragment JOIN anchor ON anchor.id = fragment.anchor_id" + + " WHERE dataspace_id = :dataspaceId"); + queryParameters.put("dataspaceId", dataspaceEntity.getId()); + } else { + sqlStringBuilder.append("SELECT * FROM fragment WHERE anchor_id = :anchorId"); + queryParameters.put("anchorId", anchorEntity.getId()); + } addXpathSearch(cpsPathQuery, sqlStringBuilder, queryParameters); addLeafConditions(cpsPathQuery, sqlStringBuilder); addTextFunctionCondition(cpsPathQuery, sqlStringBuilder, queryParameters); @@ -100,19 +106,6 @@ public class FragmentQueryBuilder { return query; } - private static void addDataspaceOrAnchor(final StringBuilder sqlStringBuilder, - final Map queryParameters, - final DataspaceEntity dataspaceEntity, - final AnchorEntity anchorEntity) { - if (anchorEntity == ACROSS_ALL_ANCHORS) { - sqlStringBuilder.append("dataspace_id = :dataspaceId"); - queryParameters.put("dataspaceId", dataspaceEntity.getId()); - } else { - sqlStringBuilder.append("anchor_id = :anchorId"); - queryParameters.put("anchorId", anchorEntity.getId()); - } - } - private static void addXpathSearch(final CpsPathQuery cpsPathQuery, final StringBuilder sqlStringBuilder, final Map queryParameters) { diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java index a2763184a6..2691be9844 100755 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java @@ -54,7 +54,10 @@ public interface FragmentRepository extends JpaRepository, return findByAnchorIdAndXpathIn(anchorEntity.getId(), xpaths.toArray(new String[0])); } - List findByDataspaceIdAndXpathIn(int dataspaceId, String[] xpaths); + @Query(value = "SELECT fragment.* FROM fragment JOIN anchor ON anchor.id = fragment.anchor_id " + + "WHERE dataspace_id = :dataspaceId AND xpath = ANY (:xpaths)", nativeQuery = true) + List findByDataspaceIdAndXpathIn(@Param("dataspaceId") int dataspaceId, + @Param("xpaths") String[] xpaths); default List findByDataspaceAndXpathIn(final DataspaceEntity dataspaceEntity, final Collection xpaths) { diff --git a/cps-ri/src/main/resources/changelog/changelog-master.yaml b/cps-ri/src/main/resources/changelog/changelog-master.yaml index bf172d8eae..40e23e2e30 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 0000000000..7b91bd5b38 --- /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 0000000000..9c60db0b59 --- /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