From 796c4dcf56a1aafc58773bedc82ddef5242a108c Mon Sep 17 00:00:00 2001 From: danielhanrahan Date: Thu, 13 Apr 2023 21:01:58 +0100 Subject: Remove 32K limit from queries with collection parameters SQL queries taking collection parameters currently create a seperate query parameter for each collection element. There is a limit of around 2^15 (32,768) query parameters. Postgres DB natively supports array types, in which the whole array is transmitted in binary as a single parameter. Changing queries to use arrays removes the 32K limit on queries. - Add support for Postgres arrays to queries - Change repository methods to use arrays, and provide overloaded versions taking collection parameters - Update tests to reflect 32K limit being lifted Issue-ID: CPS-1573 Signed-off-by: danielhanrahan Change-Id: I64f2aeaedbe54bfe12e3079cba0f2216759142c3 --- .../spi/impl/CpsModulePersistenceServiceImpl.java | 2 +- .../onap/cps/spi/repository/AnchorRepository.java | 50 ++++++++++++--- .../spi/repository/FragmentNativeRepository.java | 49 --------------- .../repository/FragmentNativeRepositoryImpl.java | 72 ---------------------- .../cps/spi/repository/FragmentRepository.java | 58 +++++++++++++---- .../cps/spi/repository/SchemaSetRepository.java | 27 ++++---- .../cps/spi/repository/YangResourceRepository.java | 15 ++++- .../impl/CpsModulePersistenceServiceSpec.groovy | 2 +- 8 files changed, 114 insertions(+), 161 deletions(-) delete mode 100644 cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java delete mode 100644 cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java (limited to 'cps-ri') diff --git a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java index b4366de75b..cd1457e359 100755 --- a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java @@ -159,7 +159,7 @@ public class CpsModulePersistenceServiceImpl implements CpsModulePersistenceServ @Override public Collection getSchemaSetsByDataspaceName(final String dataspaceName) { final DataspaceEntity dataspaceEntity = dataspaceRepository.getByName(dataspaceName); - final List schemaSetEntities = schemaSetRepository.getByDataspace(dataspaceEntity); + final List schemaSetEntities = schemaSetRepository.findByDataspace(dataspaceEntity); return schemaSetEntities.stream() .map(CpsModulePersistenceServiceImpl::toSchemaSet).collect(Collectors.toList()); } diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java index f7b586d7b3..fe9ff9e2f0 100755 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java @@ -27,6 +27,7 @@ import org.onap.cps.spi.entities.DataspaceEntity; import org.onap.cps.spi.entities.SchemaSetEntity; import org.onap.cps.spi.exceptions.AnchorNotFoundException; import org.springframework.data.jpa.repository.JpaRepository; +import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository; @@ -45,11 +46,27 @@ public interface AnchorRepository extends JpaRepository { Collection findAllBySchemaSet(SchemaSetEntity schemaSetEntity); - Collection findAllByDataspaceAndNameIn(DataspaceEntity dataspaceEntity, - Collection anchorNames); + @Query(value = "SELECT * FROM anchor WHERE dataspace_id = :dataspaceId AND name = ANY (:anchorNames)", + nativeQuery = true) + Collection findAllByDataspaceIdAndNameIn(@Param("dataspaceId") int dataspaceId, + @Param("anchorNames") String[] anchorNames); - Collection findAllByDataspaceAndSchemaSetNameIn(DataspaceEntity dataspaceEntity, - Collection schemaSetNames); + default Collection findAllByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity, + final Collection anchorNames) { + return findAllByDataspaceIdAndNameIn(dataspaceEntity.getId(), anchorNames.toArray(new String[0])); + } + + @Query(value = "SELECT a.* FROM anchor a" + + " LEFT OUTER JOIN schema_set s ON a.schema_set_id = s.id" + + " WHERE a.dataspace_id = :dataspaceId AND s.name = ANY (:schemaSetNames)", + nativeQuery = true) + Collection findAllByDataspaceIdAndSchemaSetNameIn(@Param("dataspaceId") int dataspaceId, + @Param("schemaSetNames") String[] schemaSetNames); + + default Collection findAllByDataspaceAndSchemaSetNameIn(final DataspaceEntity dataspaceEntity, + final Collection schemaSetNames) { + return findAllByDataspaceIdAndSchemaSetNameIn(dataspaceEntity.getId(), schemaSetNames.toArray(new String[0])); + } Integer countByDataspace(DataspaceEntity dataspaceEntity); @@ -57,12 +74,29 @@ public interface AnchorRepository extends JpaRepository { + "JOIN schema_set_yang_resources ON schema_set_yang_resources.yang_resource_id = yang_resource.id\n" + "JOIN schema_set ON schema_set.id = schema_set_yang_resources.schema_set_id\n" + "JOIN anchor ON anchor.schema_set_id = schema_set.id\n" - + "WHERE schema_set.dataspace_id = :dataspaceId AND module_name IN (:moduleNames)\n" + + "WHERE schema_set.dataspace_id = :dataspaceId AND module_name = ANY (:moduleNames)\n" + "GROUP BY anchor.id, anchor.name, anchor.dataspace_id, anchor.schema_set_id\n" + "HAVING COUNT(DISTINCT module_name) = :sizeOfModuleNames", nativeQuery = true) Collection getAnchorsByDataspaceIdAndModuleNames(@Param("dataspaceId") int dataspaceId, - @Param("moduleNames") Collection moduleNames, @Param("sizeOfModuleNames") int sizeOfModuleNames); + @Param("moduleNames") String[] moduleNames, + @Param("sizeOfModuleNames") int sizeOfModuleNames); + + default Collection getAnchorsByDataspaceIdAndModuleNames(final int dataspaceId, + final Collection moduleNames, + final int sizeOfModuleNames) { + final String[] moduleNamesArray = moduleNames.toArray(new String[0]); + return getAnchorsByDataspaceIdAndModuleNames(dataspaceId, moduleNamesArray, sizeOfModuleNames); + } + + @Modifying + @Query(value = "DELETE FROM anchor WHERE dataspace_id = :dataspaceId AND name = ANY (:anchorNames)", + nativeQuery = true) + void deleteAllByDataspaceIdAndNameIn(@Param("dataspaceId") int dataspaceId, + @Param("anchorNames") String[] anchorNames); + + default void deleteAllByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity, + final Collection anchorNames) { + deleteAllByDataspaceIdAndNameIn(dataspaceEntity.getId(), anchorNames.toArray(new String[0])); + } - void deleteAllByDataspaceAndNameIn(DataspaceEntity dataspaceEntity, - Collection anchorNames); } diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java deleted file mode 100644 index bad68f7e58..0000000000 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java +++ /dev/null @@ -1,49 +0,0 @@ -/* - * ============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========================================================= - */ - -package org.onap.cps.spi.repository; - -import java.util.Collection; - -/** - * This interface is used in delete fragment entity by id with child using native sql queries. - */ -public interface FragmentNativeRepository { - - /** - * Delete fragment entities for each supplied xpath. - * This method will delete list elements or other data nodes, but not whole lists. - * Non-existing xpaths will not result in an exception. - * @param anchorId the id of the anchor - * @param xpaths xpaths of data nodes to remove - */ - void deleteByAnchorIdAndXpaths(int anchorId, Collection xpaths); - - /** - * Delete fragment entities that are list elements of each supplied list xpath. - * For example, if xpath '/parent/list' is provided, then list all elements in '/parent/list' will be deleted, - * e.g. /parent/list[@key='A'], /parent/list[@key='B']. - * This method will only delete whole lists by xpath; xpaths to list elements or other data nodes will be ignored. - * Non-existing xpaths will not result in an exception. - * @param anchorId the id of the anchor - * @param listXpaths xpaths of whole lists to remove - */ - void deleteListsByAnchorIdAndXpaths(int anchorId, Collection listXpaths); -} diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java deleted file mode 100644 index 04b7080def..0000000000 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java +++ /dev/null @@ -1,72 +0,0 @@ -/* - * ============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========================================================= - */ - -package org.onap.cps.spi.repository; - -import java.util.Collection; -import java.util.Collections; -import java.util.stream.Collectors; -import javax.persistence.EntityManager; -import javax.persistence.PersistenceContext; -import javax.persistence.Query; -import lombok.RequiredArgsConstructor; - -@RequiredArgsConstructor -public class FragmentNativeRepositoryImpl implements FragmentNativeRepository { - - @PersistenceContext - private final EntityManager entityManager; - - @Override - public void deleteByAnchorIdAndXpaths(final int anchorId, final Collection xpaths) { - final String queryString = - "DELETE FROM fragment f WHERE f.anchor_id = ? AND (f.xpath IN (:parameterPlaceholders))"; - executeUpdateWithAnchorIdAndCollection(queryString, anchorId, xpaths); - } - - @Override - public void deleteListsByAnchorIdAndXpaths(final int anchorId, final Collection listXpaths) { - final Collection listXpathPatterns = - listXpaths.stream().map(listXpath -> listXpath + "[%").collect(Collectors.toSet()); - final String queryString = - "DELETE FROM fragment f WHERE f.anchor_id = ? AND (f.xpath LIKE ANY (array[:parameterPlaceholders]))"; - executeUpdateWithAnchorIdAndCollection(queryString, anchorId, listXpathPatterns); - } - - // Accept security hotspot as placeholders in SQL query are created internally, not from user input. - @SuppressWarnings("squid:S2077") - private void executeUpdateWithAnchorIdAndCollection(final String sqlTemplate, final int anchorId, - final Collection collection) { - if (!collection.isEmpty()) { - final String parameterPlaceholders = String.join(",", Collections.nCopies(collection.size(), "?")); - final String queryStringWithParameterPlaceholders = - sqlTemplate.replaceFirst(":parameterPlaceholders\\b", parameterPlaceholders); - - final Query query = entityManager.createNativeQuery(queryStringWithParameterPlaceholders); - query.setParameter(1, anchorId); - int parameterIndex = 2; - for (final String parameterValue : collection) { - query.setParameter(parameterIndex++, parameterValue); - } - query.executeUpdate(); - } - } - -} 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 d486a39c7e..d25832ba54 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 @@ -37,8 +37,7 @@ import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository; @Repository -public interface FragmentRepository extends JpaRepository, FragmentRepositoryCpsPathQuery, - FragmentNativeRepository { +public interface FragmentRepository extends JpaRepository, FragmentRepositoryCpsPathQuery { Optional findByAnchorAndXpath(AnchorEntity anchorEntity, String xpath); @@ -52,13 +51,39 @@ public interface FragmentRepository extends JpaRepository, @Query("SELECT f FROM FragmentEntity f WHERE anchor = :anchor") List findAllExtractsByAnchor(@Param("anchor") AnchorEntity anchorEntity); - List findAllByAnchorAndXpathIn(AnchorEntity anchorEntity, Collection xpath); + @Query(value = "SELECT * FROM fragment WHERE xpath = ANY (:xpaths)", nativeQuery = true) + List findAllByXpathIn(@Param("xpaths") String[] xpath); - List findAllByXpathIn(Collection xpath); + default List findAllByXpathIn(final Collection xpaths) { + return findAllByXpathIn(xpaths.toArray(new String[0])); + } + + @Modifying + @Query(value = "DELETE FROM fragment WHERE anchor_id = ANY (:anchorIds)", nativeQuery = true) + void deleteByAnchorIdIn(@Param("anchorIds") int[] anchorIds); + + default void deleteByAnchorIn(final Collection anchorEntities) { + deleteByAnchorIdIn(anchorEntities.stream().map(AnchorEntity::getId).mapToInt(id -> id).toArray()); + } @Modifying - @Query("DELETE FROM FragmentEntity WHERE anchor IN (:anchors)") - void deleteByAnchorIn(@Param("anchors") Collection anchorEntities); + @Query(value = "DELETE FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths)", nativeQuery = true) + void deleteByAnchorIdAndXpaths(@Param("anchorId") int anchorId, @Param("xpaths") String[] xpaths); + + default void deleteByAnchorIdAndXpaths(final int anchorId, final Collection xpaths) { + deleteByAnchorIdAndXpaths(anchorId, xpaths.toArray(new String[0])); + } + + @Modifying + @Query(value = "DELETE FROM fragment f WHERE anchor_id = :anchorId AND xpath LIKE ANY (:xpathPatterns)", + nativeQuery = true) + void deleteByAnchorIdAndXpathLikeAny(@Param("anchorId") int anchorId, + @Param("xpathPatterns") String[] xpathPatterns); + + default void deleteListsByAnchorIdAndXpaths(int anchorId, Collection xpaths) { + final String[] listXpathPatterns = xpaths.stream().map(xpath -> xpath + "[%").toArray(String[]::new); + deleteByAnchorIdAndXpathLikeAny(anchorId, listXpathPatterns); + } @Query("SELECT f FROM FragmentEntity f WHERE anchor = :anchor" + " AND (xpath = :parentXpath OR xpath LIKE CONCAT(:parentXpath,'/%'))") @@ -80,9 +105,15 @@ public interface FragmentRepository extends JpaRepository, List quickFindWithDescendants(@Param("anchorId") int anchorId, @Param("xpathRegex") String xpathRegex); - @Query("SELECT xpath FROM FragmentEntity WHERE anchor = :anchor AND xpath IN :xpaths") - List findAllXpathByAnchorAndXpathIn(@Param("anchor") AnchorEntity anchorEntity, - @Param("xpaths") Collection xpaths); + @Query(value = "SELECT xpath FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths)", + nativeQuery = true) + List findAllXpathByAnchorIdAndXpathIn(@Param("anchorId") int anchorId, + @Param("xpaths") String[] xpaths); + + default List findAllXpathByAnchorAndXpathIn(final AnchorEntity anchorEntity, + final Collection xpaths) { + return findAllXpathByAnchorIdAndXpathIn(anchorEntity.getId(), xpaths.toArray(new String[0])); + } boolean existsByAnchorAndXpathStartsWith(AnchorEntity anchorEntity, String xpath); @@ -93,7 +124,7 @@ public interface FragmentRepository extends JpaRepository, = "WITH RECURSIVE parent_search AS (" + " SELECT id, 0 AS depth " + " FROM fragment " - + " WHERE anchor_id = :anchorId AND xpath IN :xpaths " + + " WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths) " + " UNION " + " SELECT c.id, depth + 1 " + " FROM fragment c INNER JOIN parent_search p ON c.parent_id = p.id" @@ -104,9 +135,14 @@ public interface FragmentRepository extends JpaRepository, nativeQuery = true ) List findExtractsWithDescendants(@Param("anchorId") int anchorId, - @Param("xpaths") Collection xpaths, + @Param("xpaths") String[] xpaths, @Param("maxDepth") int maxDepth); + default List findExtractsWithDescendants(final int anchorId, final Collection xpaths, + final int maxDepth) { + return findExtractsWithDescendants(anchorId, xpaths.toArray(new String[0]), maxDepth); + } + @Query(value = "SELECT id, anchor_id AS anchorId, xpath, parent_id AS parentId," + " CAST(attributes AS TEXT) AS attributes" + " FROM FRAGMENT WHERE xpath ~ :xpathRegex", diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java index 3263f34473..3c5f973cb0 100644 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java @@ -24,7 +24,6 @@ package org.onap.cps.spi.repository; import java.util.Collection; import java.util.List; import java.util.Optional; -import java.util.stream.Collectors; import org.onap.cps.spi.entities.DataspaceEntity; import org.onap.cps.spi.entities.SchemaSetEntity; import org.onap.cps.spi.exceptions.SchemaSetNotFoundException; @@ -44,7 +43,7 @@ public interface SchemaSetRepository extends JpaRepository findByDataspace(DataspaceEntity dataspaceEntity); + List findByDataspace(DataspaceEntity dataspaceEntity); Integer countByDataspace(DataspaceEntity dataspaceEntity); @@ -61,24 +60,20 @@ public interface SchemaSetRepository extends JpaRepository new SchemaSetNotFoundException(dataspaceEntity.getName(), schemaSetName)); } - /** - * Gets all schema sets for a given dataspace. - * - * @param dataspaceEntity dataspace entity - * @return list of schema set entity - * @throws SchemaSetNotFoundException if SchemaSet not found - */ - default List getByDataspace(final DataspaceEntity dataspaceEntity) { - return findByDataspace(dataspaceEntity).stream().collect(Collectors.toList()); - } + @Modifying + @Query(value = "DELETE FROM schema_set WHERE dataspace_id = :dataspaceId AND name = ANY (:schemaSetNames)", + nativeQuery = true) + void deleteByDataspaceIdAndNameIn(@Param("dataspaceId") final int dataspaceId, + @Param("schemaSetNames") final String[] schemaSetNames); /** * Delete multiple schema sets in a given dataspace. * @param dataspaceEntity dataspace entity * @param schemaSetNames schema set names */ - @Modifying - @Query("DELETE FROM SchemaSetEntity s WHERE s.dataspace = :dataspaceEntity AND s.name IN (:schemaSetNames)") - void deleteByDataspaceAndNameIn(@Param("dataspaceEntity") DataspaceEntity dataspaceEntity, - @Param("schemaSetNames") Collection schemaSetNames); + default void deleteByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity, + final Collection schemaSetNames) { + deleteByDataspaceIdAndNameIn(dataspaceEntity.getId(), schemaSetNames.toArray(new String[0])); + } + } diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java index fff0a6a037..7584ff65c0 100644 --- a/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java +++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java @@ -35,7 +35,11 @@ import org.springframework.stereotype.Repository; public interface YangResourceRepository extends JpaRepository, YangResourceNativeRepository, SchemaSetYangResourceRepository { - List findAllByChecksumIn(Set checksum); + List findAllByChecksumIn(String[] checksums); + + default List findAllByChecksumIn(final Collection checksums) { + return findAllByChecksumIn(checksums.toArray(new String[0])); + } @Query(value = "SELECT DISTINCT\n" + "yang_resource.module_name AS module_name,\n" @@ -86,9 +90,14 @@ public interface YangResourceRepository extends JpaRepository findAllModuleReferencesByDataspaceAndModuleNames( - @Param("dataspaceName") String dataspaceName, @Param("moduleNames") Collection moduleNames); + @Param("dataspaceName") String dataspaceName, @Param("moduleNames") String[] moduleNames); + + default Set findAllModuleReferencesByDataspaceAndModuleNames( + final String dataspaceName, final Collection moduleNames) { + return findAllModuleReferencesByDataspaceAndModuleNames(dataspaceName, moduleNames.toArray(new String[0])); + } @Modifying @Query(value = "DELETE FROM yang_resource yr WHERE NOT EXISTS " diff --git a/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy b/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy index 9ef9732681..811c3290b9 100644 --- a/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy +++ b/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy @@ -84,7 +84,7 @@ class CpsModulePersistenceServiceSpec extends Specification { def 'Store schema set error scenario: #scenario.'() { given: 'no yang resource are currently saved' - yangResourceRepositoryMock.findAllByChecksumIn(_) >> Collections.emptyList() + yangResourceRepositoryMock.findAllByChecksumIn(_ as Collection) >> Collections.emptyList() and: 'persisting yang resource raises db constraint exception (in case of concurrent requests for example)' yangResourceRepositoryMock.saveAll(_) >> { throw dbException } when: 'attempt to store schema set ' -- cgit 1.2.3-korg