Fetch fragment entities using recursive SQL query

- Add SQL query that can fetch needed fragments to any given depth
- Update getFragmentEntities to use new query
- Remove now unused FragmentRepositoryMultiPathQuery
- Remove unused TempTableCreator method
- Result: getDataNodesForMultipleXpaths is up to 10 times faster

Issue-ID: CPS-1525
Signed-off-by: danielhanrahan <daniel.hanrahan@est.tech>
Change-Id: I07cbc9da5ab994ce7e0c2b02d7ca05089f05dab0

5 files changed, 39 insertions, 131 deletions

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 3ea388242e..82e6388d8b 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
@@ -256,12 +256,14 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
                                                               final Collection<String> xpaths,
                                                               final FetchDescendantsOption fetchDescendantsOption) {
         final AnchorEntity anchorEntity = getAnchorEntity(dataspaceName, anchorName);
-        final Collection<FragmentEntity> fragmentEntities = getFragmentEntities(anchorEntity, xpaths);
+        final Collection<FragmentEntity> fragmentEntities =
+            getFragmentEntities(anchorEntity, xpaths, fetchDescendantsOption);
         return toDataNodes(fragmentEntities, fetchDescendantsOption);
     }
 
     private Collection<FragmentEntity> getFragmentEntities(final AnchorEntity anchorEntity,
-                                                           final Collection<String> xpaths) {
+                                                           final Collection<String> xpaths,
+                                                           final FetchDescendantsOption fetchDescendantsOption) {
         final Collection<String> nonRootXpaths = new HashSet<>(xpaths);
         final boolean haveRootXpath = nonRootXpaths.removeIf(CpsDataPersistenceServiceImpl::isRootXpath);
 
@@ -273,15 +275,15 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
                 log.warn("Error parsing xpath \"{}\": {}", xpath, e.getMessage());
             }
         }
-        final Collection<FragmentEntity> fragmentEntities =
-            new HashSet<>(fragmentRepository.findByAnchorAndMultipleCpsPaths(anchorEntity.getId(), normalizedXpaths));
-
         if (haveRootXpath) {
-            final List<FragmentExtract> fragmentExtracts = fragmentRepository.findAllExtractsByAnchor(anchorEntity);
-            fragmentEntities.addAll(FragmentEntityArranger.toFragmentEntityTrees(anchorEntity, fragmentExtracts));
+            normalizedXpaths.addAll(fragmentRepository.findAllXpathByAnchorAndParentIdIsNull(anchorEntity));
         }
 
-        return fragmentEntities;
+        final List<FragmentExtract> fragmentExtracts =
+            fragmentRepository.findExtractsWithDescendants(anchorEntity.getId(), normalizedXpaths,
+                fetchDescendantsOption.getDepth());
+
+        return FragmentEntityArranger.toFragmentEntityTrees(anchorEntity, fragmentExtracts);
     }
 
     private FragmentEntity getFragmentEntity(final AnchorEntity anchorEntity, final String xpath) {
@@ -325,7 +327,8 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
         }
         fragmentEntities = fragmentRepository.findByAnchorAndCpsPath(anchorEntity.getId(), cpsPathQuery);
         if (cpsPathQuery.hasAncestorAxis()) {
-            fragmentEntities = getAncestorFragmentEntities(anchorEntity.getId(), cpsPathQuery, fragmentEntities);
+            final Collection<String> ancestorXpaths = processAncestorXpath(fragmentEntities, cpsPathQuery);
+            fragmentEntities = getFragmentEntities(anchorEntity, ancestorXpaths, fetchDescendantsOption);
         }
         return createDataNodesFromProxiedFragmentEntities(fetchDescendantsOption, anchorEntity, fragmentEntities);
     }
@@ -346,19 +349,12 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
             fragmentRepository.quickFindWithDescendants(anchorEntity.getId(), xpathRegex);
         fragmentEntities = FragmentEntityArranger.toFragmentEntityTrees(anchorEntity, fragmentExtracts);
         if (cpsPathQuery.hasAncestorAxis()) {
-            fragmentEntities = getAncestorFragmentEntities(anchorEntity.getId(), cpsPathQuery, fragmentEntities);
+            final Collection<String> ancestorXpaths = processAncestorXpath(fragmentEntities, cpsPathQuery);
+            fragmentEntities = getFragmentEntities(anchorEntity, ancestorXpaths, fetchDescendantsOption);
         }
         return createDataNodesFromFragmentEntities(fetchDescendantsOption, fragmentEntities);
     }
 
-    private Collection<FragmentEntity> getAncestorFragmentEntities(final int anchorId,
-                                                                   final CpsPathQuery cpsPathQuery,
-                                                                   final Collection<FragmentEntity> fragmentEntities) {
-        final Collection<String> ancestorXpaths = processAncestorXpath(fragmentEntities, cpsPathQuery);
-        return ancestorXpaths.isEmpty() ? Collections.emptyList()
-            : fragmentRepository.findByAnchorAndMultipleCpsPaths(anchorId, ancestorXpaths);
-    }
-
     private List<DataNode> createDataNodesFromProxiedFragmentEntities(
                                             final FetchDescendantsOption fetchDescendantsOption,
                                             final AnchorEntity anchorEntity,
@@ -497,7 +493,8 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
             .collect(Collectors.toMap(DataNode::getXpath, dataNode -> dataNode));
 
         final Collection<String> xpaths = xpathToUpdatedDataNode.keySet();
-        final Collection<FragmentEntity> existingFragmentEntities = getFragmentEntities(anchorEntity, xpaths);
+        final Collection<FragmentEntity> existingFragmentEntities =
+            getFragmentEntities(anchorEntity, xpaths, FetchDescendantsOption.INCLUDE_ALL_DESCENDANTS);
 
         for (final FragmentEntity existingFragmentEntity : existingFragmentEntities) {
             final DataNode updatedDataNode = xpathToUpdatedDataNode.get(existingFragmentEntity.getXpath());
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 996e55e777..426a4601c7 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,7 +37,7 @@ import org.springframework.stereotype.Repository;
 

 @Repository

 public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>, FragmentRepositoryCpsPathQuery,

-        FragmentRepositoryMultiPathQuery, FragmentNativeRepository {

+        FragmentNativeRepository {

 

     Optional<FragmentEntity> findByAnchorAndXpath(AnchorEntity anchorEntity, String xpath);

 

@@ -68,9 +68,30 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
     List<FragmentExtract> quickFindWithDescendants(@Param("anchorId") int anchorId,

                                                    @Param("xpathRegex") String xpathRegex);

 

-    @Query("SELECT xpath FROM FragmentEntity f WHERE anchor = :anchor AND xpath IN :xpaths")

+    @Query("SELECT xpath FROM FragmentEntity WHERE anchor = :anchor AND xpath IN :xpaths")

     List<String> findAllXpathByAnchorAndXpathIn(@Param("anchor") AnchorEntity anchorEntity,

                                                 @Param("xpaths") Collection<String> xpaths);

 

     boolean existsByAnchorAndXpathStartsWith(AnchorEntity anchorEntity, String xpath);

+

+    @Query("SELECT xpath FROM FragmentEntity WHERE anchor = :anchor AND parentId IS NULL")

+    List<String> findAllXpathByAnchorAndParentIdIsNull(@Param("anchor") AnchorEntity anchorEntity);

+

+    @Query(value

+        = "WITH RECURSIVE parent_search AS ("

+        + "  SELECT id, 0 AS depth "

+        + "    FROM fragment "

+        + "   WHERE anchor_id = :anchorId AND xpath IN :xpaths "

+        + "   UNION "

+        + "  SELECT c.id, depth + 1 "

+        + "    FROM fragment c INNER JOIN parent_search p ON c.parent_id = p.id"

+        + "   WHERE depth <= (SELECT CASE WHEN :maxDepth = -1 THEN " + Integer.MAX_VALUE + " ELSE :maxDepth END) "

+        + ") "

+        + "SELECT f.id, anchor_id AS anchorId, xpath, f.parent_id AS parentId, CAST(attributes AS TEXT) AS attributes "

+        + "FROM fragment f INNER JOIN parent_search p ON f.id = p.id",

+        nativeQuery = true

+    )

+    List<FragmentExtract> findExtractsWithDescendants(@Param("anchorId") int anchorId,

+                                                      @Param("xpaths") Collection<String> xpaths,

+                                                      @Param("maxDepth") int maxDepth);

 }

diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQuery.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQuery.java
deleted file mode 100644
index 9c34a459e9..0000000000
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQuery.java
+++ /dev/null
@@ -1,31 +0,0 @@
-/*-
- * ============LICENSE_START=======================================================
- *  Copyright (C) 2022 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.List;
-import org.onap.cps.spi.entities.FragmentEntity;
-
-public interface FragmentRepositoryMultiPathQuery {
-
-    List<FragmentEntity> findByAnchorAndMultipleCpsPaths(Integer anchorId, Collection<String> cpsPathQuery);
-
-}
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQueryImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQueryImpl.java
deleted file mode 100644
index 151fe97b34..0000000000
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryMultiPathQueryImpl.java
+++ /dev/null
@@ -1,59 +0,0 @@
-/*-
- * ============LICENSE_START=======================================================
- *  Copyright (C) 2022-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.List;
-import javax.persistence.EntityManager;
-import javax.persistence.PersistenceContext;
-import javax.transaction.Transactional;
-import lombok.RequiredArgsConstructor;
-import lombok.extern.slf4j.Slf4j;
-import org.onap.cps.spi.entities.FragmentEntity;
-
-@Slf4j
-@RequiredArgsConstructor
-public class FragmentRepositoryMultiPathQueryImpl implements FragmentRepositoryMultiPathQuery {
-
-    @PersistenceContext
-    private final EntityManager entityManager;
-
-    private final TempTableCreator tempTableCreator;
-
-    @Override
-    @Transactional
-    public List<FragmentEntity> findByAnchorAndMultipleCpsPaths(final Integer anchorId,
-                                                                final Collection<String> cpsPathQueryList) {
-        if (cpsPathQueryList.isEmpty()) {
-            return Collections.emptyList();
-        }
-        final String tempTableName = tempTableCreator.createTemporaryTable(
-            "xpathTemporaryTable", cpsPathQueryList, "xpath");
-        final String sql = String.format(
-            "SELECT * FROM FRAGMENT WHERE anchor_id = %d AND xpath IN (select xpath FROM %s);",
-            anchorId, tempTableName);
-        final List<FragmentEntity> fragmentEntities = entityManager.createNativeQuery(sql, FragmentEntity.class)
-            .getResultList();
-        log.debug("Fetched {} fragment entities by anchor and cps path.", fragmentEntities.size());
-        return fragmentEntities;
-    }
-}
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/TempTableCreator.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/TempTableCreator.java
index d798932c02..139a8b3063 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/TempTableCreator.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/TempTableCreator.java
@@ -20,10 +20,8 @@
 
 package org.onap.cps.spi.repository;
 
-import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collection;
-import java.util.Collections;
 import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
@@ -66,24 +64,6 @@ public class TempTableCreator {
         return tempTableName;
     }
 
-    /**
-     * Create a uniquely named temporary table with a single column.
-     *
-     * @param prefix     prefix for the table name (so you can recognize it)
-     * @param sqlData    data to insert (strings only); each entry is a single row of data
-     * @param columnName column name
-     * @return a unique temporary table name with given prefix
-     */
-    public String createTemporaryTable(final String prefix,
-                                       final Collection<String> sqlData,
-                                       final String columnName) {
-        final Collection<List<String>> tableData = new ArrayList<>(sqlData.size());
-        for (final String entry : sqlData) {
-            tableData.add(Collections.singletonList(entry));
-        }
-        return createTemporaryTable(prefix, tableData, columnName);
-    }
-
     private static void defineColumns(final StringBuilder sqlStringBuilder, final String[] columnNames) {
         sqlStringBuilder.append('(');
         final Iterator<String> it = Arrays.stream(columnNames).iterator();