Merge "Use recursive SQL to fetch descendants in CpsPath queries (CPS-1664 #4)"

4 files changed, 46 insertions, 108 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 5b77072a7..c26cd2fea 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
@@ -65,7 +65,6 @@ import org.onap.cps.spi.model.DataNode;
 import org.onap.cps.spi.model.DataNodeBuilder;
 import org.onap.cps.spi.repository.AnchorRepository;
 import org.onap.cps.spi.repository.DataspaceRepository;
-import org.onap.cps.spi.repository.FragmentQueryBuilder;
 import org.onap.cps.spi.repository.FragmentRepository;
 import org.onap.cps.spi.utils.SessionManager;
 import org.onap.cps.utils.JsonObjectMapper;
@@ -300,13 +299,6 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
         return fragmentEntity;
     }
 
-    private Collection<FragmentEntity> buildFragmentEntitiesFromFragmentExtracts(final AnchorEntity anchorEntity,
-                                                                                 final String normalizedXpath) {
-        final List<FragmentExtract> fragmentExtracts =
-            fragmentRepository.findByAnchorAndParentXpath(anchorEntity, normalizedXpath);
-        return FragmentEntityArranger.toFragmentEntityTrees(anchorEntity, fragmentExtracts);
-    }
-
     @Override
     @Timed(value = "cps.data.persistence.service.datanode.query",
             description = "Time taken to query data nodes")
@@ -323,16 +315,11 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
         }
 
         Collection<FragmentEntity> fragmentEntities;
-        if (canUseRegexQuickFind(fetchDescendantsOption, cpsPathQuery)) {
-            return getDataNodesUsingRegexQuickFind(fetchDescendantsOption, dataspaceEntity, anchorEntity, cpsPathQuery);
-        }
-
         if (anchorEntity == ALL_ANCHORS) {
             fragmentEntities = fragmentRepository.findByDataspaceAndCpsPath(dataspaceEntity, cpsPathQuery);
         } else {
             fragmentEntities = fragmentRepository.findByAnchorAndCpsPath(anchorEntity, cpsPathQuery);
         }
-
         if (cpsPathQuery.hasAncestorAxis()) {
             final Collection<String> ancestorXpaths = processAncestorXpath(fragmentEntities, cpsPathQuery);
             if (anchorEntity == ALL_ANCHORS) {
@@ -341,8 +328,9 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
                 fragmentEntities = fragmentRepository.findByAnchorAndXpathIn(anchorEntity, ancestorXpaths);
             }
         }
-
-        return createDataNodesFromProxiedFragmentEntities(fetchDescendantsOption, anchorEntity, fragmentEntities);
+        fragmentEntities = prefetchDescendantsForFragmentEntities(fetchDescendantsOption, anchorEntity,
+            fragmentEntities);
+        return createDataNodesFromFragmentEntities(fetchDescendantsOption, fragmentEntities);
     }
 
     @Override
@@ -351,30 +339,20 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
         return queryDataNodes(dataspaceName, QUERY_ACROSS_ANCHORS, cpsPath, fetchDescendantsOption);
     }
 
-    private static boolean canUseRegexQuickFind(final FetchDescendantsOption fetchDescendantsOption,
-                                                final CpsPathQuery cpsPathQuery) {
-        return fetchDescendantsOption.equals(FetchDescendantsOption.INCLUDE_ALL_DESCENDANTS)
-            && !cpsPathQuery.hasAncestorAxis()
-            && !cpsPathQuery.hasLeafConditions()
-            && !cpsPathQuery.hasTextFunctionCondition()
-            && !cpsPathQuery.hasContainsFunctionCondition();
-    }
+    private Collection<FragmentEntity> prefetchDescendantsForFragmentEntities(
+                                            final FetchDescendantsOption fetchDescendantsOption,
+                                            final AnchorEntity anchorEntity,
+                                            final Collection<FragmentEntity> proxiedFragmentEntities) {
+        if (FetchDescendantsOption.OMIT_DESCENDANTS.equals(fetchDescendantsOption)) {
+            return proxiedFragmentEntities;
+        }
 
-    private List<DataNode> getDataNodesUsingRegexQuickFind(final FetchDescendantsOption fetchDescendantsOption,
-                                                           final DataspaceEntity dataspaceEntity,
-                                                           final AnchorEntity anchorEntity,
-                                                           final CpsPathQuery cpsPathQuery) {
-        final String xpathRegex = FragmentQueryBuilder.getXpathSqlRegexForQuickFindWithDescendants(cpsPathQuery);
-        final List<FragmentExtract> fragmentExtracts = (anchorEntity == ALL_ANCHORS)
-            ? fragmentRepository.quickFindWithDescendantsAcrossAnchors(dataspaceEntity.getId(), xpathRegex)
-            : fragmentRepository.quickFindWithDescendants(anchorEntity.getId(), xpathRegex);
-        final Collection<FragmentEntity> fragmentEntities =
-            createFragmentEntitiesFromFragmentExtracts(anchorEntity, fragmentExtracts);
-        return createDataNodesFromFragmentEntities(fetchDescendantsOption, fragmentEntities);
-    }
+        final List<Long> fragmentEntityIds = proxiedFragmentEntities.stream()
+            .map(FragmentEntity::getId).collect(Collectors.toList());
+
+        final List<FragmentExtract> fragmentExtracts =
+            fragmentRepository.findExtractsWithDescendantsByIds(fragmentEntityIds, fetchDescendantsOption.getDepth());
 
-    private Collection<FragmentEntity> createFragmentEntitiesFromFragmentExtracts(
-                    final AnchorEntity anchorEntity, final Collection<FragmentExtract> fragmentExtracts) {
         if (anchorEntity == ALL_ANCHORS) {
             final Collection<Integer> anchorIds = fragmentExtracts.stream()
                 .map(FragmentExtract::getAnchorId).collect(Collectors.toSet());
@@ -386,28 +364,6 @@ public class CpsDataPersistenceServiceImpl implements CpsDataPersistenceService
         return FragmentEntityArranger.toFragmentEntityTrees(anchorEntity, fragmentExtracts);
     }
 
-    private List<DataNode> createDataNodesFromProxiedFragmentEntities(
-                                            final FetchDescendantsOption fetchDescendantsOption,
-                                            final AnchorEntity anchorEntity,
-                                            final Collection<FragmentEntity> proxiedFragmentEntities) {
-        final List<DataNode> dataNodes = new ArrayList<>(proxiedFragmentEntities.size());
-        for (final FragmentEntity proxiedFragmentEntity : proxiedFragmentEntities) {
-            if (FetchDescendantsOption.OMIT_DESCENDANTS.equals(fetchDescendantsOption)) {
-                dataNodes.add(toDataNode(proxiedFragmentEntity, fetchDescendantsOption));
-            } else {
-                final String normalizedXpath = getNormalizedXpath(proxiedFragmentEntity.getXpath());
-                final AnchorEntity anchorEntityForFragmentExtract = (anchorEntity == ALL_ANCHORS)
-                        ? proxiedFragmentEntity.getAnchor() : anchorEntity;
-                final Collection<FragmentEntity> unproxiedFragmentEntities =
-                    buildFragmentEntitiesFromFragmentExtracts(anchorEntityForFragmentExtract, normalizedXpath);
-                for (final FragmentEntity unproxiedFragmentEntity : unproxiedFragmentEntities) {
-                    dataNodes.add(toDataNode(unproxiedFragmentEntity, fetchDescendantsOption));
-                }
-            }
-        }
-        return Collections.unmodifiableList(dataNodes);
-    }
-
     private List<DataNode> createDataNodesFromFragmentEntities(final FetchDescendantsOption fetchDescendantsOption,
                                                                final Collection<FragmentEntity> fragmentEntities) {
         final List<DataNode> dataNodes = new ArrayList<>(fragmentEntities.size());
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 515bbd616..76cfaa89f 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
@@ -46,7 +46,6 @@ public class FragmentQueryBuilder {
     private static final String REGEX_ABSOLUTE_PATH_PREFIX = "^";
     private static final String REGEX_DESCENDANT_PATH_PREFIX = "^.*\\/";
     private static final String REGEX_OPTIONAL_LIST_INDEX_POSTFIX = "(\\[@(?!.*\\[).*?])?$";
-    private static final String REGEX_FOR_QUICK_FIND_WITH_DESCENDANTS = "(\\[@.*?])?(\\/.*)?$";
     private static final AnchorEntity ACROSS_ALL_ANCHORS = null;
 
     @PersistenceContext
@@ -77,31 +76,12 @@ public class FragmentQueryBuilder {
         return getQueryForDataspaceOrAnchorAndCpsPath(dataspaceEntity, ACROSS_ALL_ANCHORS, cpsPathQuery);
     }
 
-    /**
-     * Create a regular expression (string) for matching xpaths based on the given cps path query.
-     *
-     * @param cpsPathQuery the cps path query to determine the required regular expression
-     * @return a string representing the required regular expression
-     */
-    public static String getXpathSqlRegex(final CpsPathQuery cpsPathQuery) {
+    private static String getXpathSqlRegex(final CpsPathQuery cpsPathQuery) {
         final StringBuilder xpathRegexBuilder = getRegexStringBuilderWithPrefix(cpsPathQuery);
         xpathRegexBuilder.append(REGEX_OPTIONAL_LIST_INDEX_POSTFIX);
         return xpathRegexBuilder.toString();
     }
 
-    /**
-     * Create a regular expression (string) for matching xpaths with (all) descendants
-     * based on the given cps path query.
-     *
-     * @param cpsPathQuery the cps path query to determine the required regular expression
-     * @return a string representing the required regular expression
-     */
-    public static String getXpathSqlRegexForQuickFindWithDescendants(final CpsPathQuery cpsPathQuery) {
-        final StringBuilder xpathRegexBuilder = getRegexStringBuilderWithPrefix(cpsPathQuery);
-        xpathRegexBuilder.append(REGEX_FOR_QUICK_FIND_WITH_DESCENDANTS);
-        return xpathRegexBuilder.toString();
-    }
-
     private Query getQueryForDataspaceOrAnchorAndCpsPath(final DataspaceEntity dataspaceEntity,
                                                          final AnchorEntity anchorEntity,
                                                          final CpsPathQuery cpsPathQuery) {
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 d20e4d35a..a2763184a 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
@@ -93,25 +93,6 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
         deleteByAnchorIdAndXpathLikeAny(anchorId, listXpathPatterns);

     }

 

-    @Query("SELECT f FROM FragmentEntity f WHERE anchor = :anchor"

-        + " AND (xpath = :parentXpath OR xpath LIKE CONCAT(:parentXpath,'/%'))")

-    List<FragmentExtract> findByAnchorAndParentXpath(@Param("anchor") AnchorEntity anchorEntity,

-                                                     @Param("parentXpath") String parentXpath);

-

-    @Query(value = "SELECT id, anchor_id AS anchorId, xpath, parent_id AS parentId,"

-        + " CAST(attributes AS TEXT) AS attributes"

-        + " FROM FRAGMENT WHERE anchor_id = :anchorId AND xpath ~ :xpathRegex",

-        nativeQuery = true)

-    List<FragmentExtract> quickFindWithDescendants(@Param("anchorId") int anchorId,

-                                                   @Param("xpathRegex") String xpathRegex);

-

-    @Query(value = "SELECT id, anchor_id AS anchorId, xpath, parent_id AS parentId,"

-        + " CAST(attributes AS TEXT) AS attributes"

-        + " FROM FRAGMENT WHERE dataspace_id = :dataspaceId AND xpath ~ :xpathRegex",

-        nativeQuery = true)

-    List<FragmentExtract> quickFindWithDescendantsAcrossAnchors(@Param("dataspaceId") int dataspaceId,

-                                                                @Param("xpathRegex") String xpathRegex);

-

     @Query(value = "SELECT xpath FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths)",

         nativeQuery = true)

     List<String> findAllXpathByAnchorIdAndXpathIn(@Param("anchorId") int anchorId,

@@ -150,4 +131,25 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
         return findExtractsWithDescendants(anchorId, xpaths.toArray(new String[0]), maxDepth);

     }

 

+    @Query(value

+        = "WITH RECURSIVE parent_search AS ("

+        + "  SELECT id, 0 AS depth "

+        + "    FROM fragment "

+        + "   WHERE id = ANY (:ids) "

+        + "   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> findExtractsWithDescendantsByIds(@Param("ids") long[] ids,

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

+

+    default List<FragmentExtract> findExtractsWithDescendantsByIds(final Collection<Long> ids, final int maxDepth) {

+        return findExtractsWithDescendantsByIds(ids.stream().mapToLong(id -> id).toArray(), maxDepth);

+    }

+

 }

diff --git a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/QueryPerfTest.groovy b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/QueryPerfTest.groovy
index 0af01ac77..ecc44ff9d 100644
--- a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/QueryPerfTest.groovy
+++ b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/QueryPerfTest.groovy
@@ -46,9 +46,9 @@ class QueryPerfTest extends CpsPerfTestBase {
         where: 'the following parameters are used'
             scenario                     | anchor       | cpsPath                                                             || durationLimit | expectedNumberOfDataNodes
             'top element'                | 'openroadm1' | '/openroadm-devices'                                                || 250           | 50 * 86 + 1
-            'leaf condition'             | 'openroadm2' | '//openroadm-device[@ne-state="inservice"]'                         || 650           | 50 * 86
+            'leaf condition'             | 'openroadm2' | '//openroadm-device[@ne-state="inservice"]'                         || 250           | 50 * 86
             'ancestors'                  | 'openroadm3' | '//openroadm-device/ancestor::openroadm-devices'                    || 250           | 50 * 86 + 1
-            'leaf condition + ancestors' | 'openroadm4' | '//openroadm-device[@status="success"]/ancestor::openroadm-devices' || 500           | 50 * 86 + 1
+            'leaf condition + ancestors' | 'openroadm4' | '//openroadm-device[@status="success"]/ancestor::openroadm-devices' || 250           | 50 * 86 + 1
     }
 
     def 'Query complete data trees across all anchors with #scenario.'() {
@@ -63,9 +63,9 @@ class QueryPerfTest extends CpsPerfTestBase {
             recordAndAssertPerformance("Query across anchors ${scenario}", durationLimit, durationInMillis)
         where: 'the following parameters are used'
             scenario                     | cpspath                                                             || durationLimit | expectedNumberOfDataNodes
-            'top element'                | '/openroadm-devices'                                                || 1             | 5 * (50 * 86 + 1)
-            'leaf condition'             | '//openroadm-device[@ne-state="inservice"]'                         || 2500          | 5 * (50 * 86)
-            'ancestors'                  | '//openroadm-device/ancestor::openroadm-devices'                    || 12000         | 5 * (50 * 86 + 1)
+            'top element'                | '/openroadm-devices'                                                || 1000          | 5 * (50 * 86 + 1)
+            'leaf condition'             | '//openroadm-device[@ne-state="inservice"]'                         || 1000          | 5 * (50 * 86)
+            'ancestors'                  | '//openroadm-device/ancestor::openroadm-devices'                    || 1000          | 5 * (50 * 86 + 1)
             'leaf condition + ancestors' | '//openroadm-device[@status="success"]/ancestor::openroadm-devices' || 1000          | 5 * (50 * 86 + 1)
     }
 
@@ -82,8 +82,8 @@ class QueryPerfTest extends CpsPerfTestBase {
         where: 'the following parameters are used'
             scenario             | fetchDescendantsOption  | anchor       || durationLimit | expectedNumberOfDataNodes
             'no descendants'     | OMIT_DESCENDANTS        | 'openroadm1' || 100           | 50
-            'direct descendants' | DIRECT_CHILDREN_ONLY    | 'openroadm2' || 400           | 50 * 2
-            'all descendants'    | INCLUDE_ALL_DESCENDANTS | 'openroadm3' || 500           | 50 * 86
+            'direct descendants' | DIRECT_CHILDREN_ONLY    | 'openroadm2' || 150           | 50 * 2
+            'all descendants'    | INCLUDE_ALL_DESCENDANTS | 'openroadm3' || 200           | 50 * 86
     }
 
     def 'Query ancestors with #scenario.'() {
@@ -99,8 +99,8 @@ class QueryPerfTest extends CpsPerfTestBase {
         where: 'the following parameters are used'
             scenario             | fetchDescendantsOption  | anchor       || durationLimit | expectedNumberOfDataNodes
             'no descendants'     | OMIT_DESCENDANTS        | 'openroadm1' || 100           | 1
-            'direct descendants' | DIRECT_CHILDREN_ONLY    | 'openroadm2' || 250           | 1 + 50
-            'all descendants'    | INCLUDE_ALL_DESCENDANTS | 'openroadm3' || 400           | 1 + 50 * 86
+            'direct descendants' | DIRECT_CHILDREN_ONLY    | 'openroadm2' || 200           | 1 + 50
+            'all descendants'    | INCLUDE_ALL_DESCENDANTS | 'openroadm3' || 300           | 1 + 50 * 86
     }
 
 }