CPS-NCMP: Slow cmHandle registration when we use moduleSetTag, alternateId and dataProducerIdentifier

- Created a new repo. service for fragment table that executes a native
  sql query to find first ready cm handle id based on moduleset tag and
  then returns list of module references.
- Exposed a new interface into module service that is used by
  module sync service to get list of midule refs by module set tag.

Issue-ID: CPS-2353
Change-Id: I438dbd1ed37c1ff4e15f792e93a095aa604120bc
Signed-off-by: sourabh_sourabh <sourabh.sourabh@est.tech>

4 files changed, 112 insertions, 11 deletions

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 17f13b81ad..2c4cc7486b 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
@@ -241,6 +241,15 @@ public class CpsModulePersistenceServiceImpl implements CpsModulePersistenceServ
         return moduleReferenceRepository.identifyNewModuleReferences(moduleReferencesToCheck);
     }
 
+    @Override
+    public Collection<ModuleReference> getModuleReferencesByAttribute(final String dataspaceName,
+                                                                      final String anchorName,
+                                                                      final Map<String, String> parentAttributes,
+                                                                      final Map<String, String> childAttributes) {
+        return moduleReferenceRepository.findModuleReferences(dataspaceName, anchorName, parentAttributes,
+                childAttributes);
+    }
+
     private Set<YangResourceEntity> synchronizeYangResources(
         final Map<String, String> moduleReferenceNameToContentMap) {
         final Map<String, YangResourceEntity> checksumToEntityMap = moduleReferenceNameToContentMap.entrySet().stream()
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryCpsPathQueryImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryCpsPathQueryImpl.java
index 78e0f08c44..9c98f7f7d9 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryCpsPathQueryImpl.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepositoryCpsPathQueryImpl.java
@@ -1,6 +1,6 @@
 /*-
  * ============LICENSE_START=======================================================
- *  Copyright (C) 2021-2023 Nordix Foundation.
+ *  Copyright (C) 2021-2024 Nordix Foundation.
  *  Modifications Copyright (C) 2023 TechMahindra Ltd.
  * ================================================================================
  * Licensed under the Apache License, Version 2.0 (the "License");
@@ -21,8 +21,6 @@
 
 package org.onap.cps.spi.repository;
 
-import jakarta.persistence.EntityManager;
-import jakarta.persistence.PersistenceContext;
 import jakarta.persistence.Query;
 import jakarta.transaction.Transactional;
 import java.util.List;
@@ -38,9 +36,6 @@ import org.onap.cps.spi.entities.FragmentEntity;
 @Slf4j
 public class FragmentRepositoryCpsPathQueryImpl implements FragmentRepositoryCpsPathQuery {
 
-    @PersistenceContext
-    private EntityManager entityManager;
-
     private final FragmentQueryBuilder fragmentQueryBuilder;
 
     @Override
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceQuery.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceQuery.java
index 00e53aa00f..4082307384 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceQuery.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceQuery.java
@@ -1,6 +1,6 @@
 /*-
  * ============LICENSE_START=======================================================
- *  Copyright (C) 2022 Nordix Foundation.
+ *  Copyright (C) 2022-2024 Nordix Foundation.
  * ================================================================================
  * Licensed under the Apache License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
@@ -21,6 +21,7 @@
 package org.onap.cps.spi.repository;
 
 import java.util.Collection;
+import java.util.Map;
 import org.onap.cps.spi.model.ModuleReference;
 
 /**
@@ -29,4 +30,8 @@ import org.onap.cps.spi.model.ModuleReference;
 public interface ModuleReferenceQuery {
 
     Collection<ModuleReference> identifyNewModuleReferences(final Collection<ModuleReference> moduleReferencesToCheck);
+
+    Collection<ModuleReference> findModuleReferences(final String dataspaceName, final String anchorName,
+                                                     final Map<String, String> parentAttributes,
+                                                     final Map<String, String> childAttributes);
 }
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceRepositoryImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceRepositoryImpl.java
index 454848b98f..6cc8234c90 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceRepositoryImpl.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/ModuleReferenceRepositoryImpl.java
@@ -22,12 +22,15 @@ package org.onap.cps.spi.repository;
 
 import jakarta.persistence.EntityManager;
 import jakarta.persistence.PersistenceContext;
+import jakarta.persistence.Query;
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.HashSet;
 import java.util.List;
-import lombok.AllArgsConstructor;
+import java.util.Map;
+import java.util.stream.Collectors;
+import lombok.RequiredArgsConstructor;
 import lombok.SneakyThrows;
 import lombok.extern.slf4j.Slf4j;
 import org.onap.cps.spi.model.ModuleReference;
@@ -35,13 +38,13 @@ import org.springframework.transaction.annotation.Transactional;
 
 @Slf4j
 @Transactional
-@AllArgsConstructor
+@RequiredArgsConstructor
 public class ModuleReferenceRepositoryImpl implements ModuleReferenceQuery {
 
     @PersistenceContext
     private EntityManager entityManager;
 
-    private TempTableCreator tempTableCreator;
+    private final TempTableCreator tempTableCreator;
 
     @Override
     @SneakyThrows
@@ -66,6 +69,96 @@ public class ModuleReferenceRepositoryImpl implements ModuleReferenceQuery {
         return identifyNewModuleReferencesForCmHandle(tempTableName);
     }
 
+    /**
+     * Finds module references based on specified dataspace, anchor, and attribute filters.
+     * This method constructs and executes a SQL query to retrieve module references. The query applies filters to
+     * parent and child fragments using the provided attribute maps. The `parentAttributes` are used to filter
+     * parent fragments, while `childAttributes` filter child fragments.
+     *
+     * @param dataspaceName    the name of the dataspace to filter on.
+     * @param anchorName       the name of the anchor to filter on.
+     * @param parentAttributes a map of attributes for filtering parent fragments.
+     * @param childAttributes  a map of attributes for filtering child fragments.
+     * @return a collection of {@link ModuleReference} objects that match the specified filters.
+     */
+    @Transactional
+    @SuppressWarnings("unchecked")
+    @Override
+    public Collection<ModuleReference> findModuleReferences(final String dataspaceName, final String anchorName,
+                                                            final Map<String, String> parentAttributes,
+                                                            final Map<String, String> childAttributes) {
+
+        final String parentFragmentWhereClause = buildWhereClause(childAttributes, "parentFragment");
+        final String childFragmentWhereClause = buildWhereClause(parentAttributes, "childFragment");
+
+        final String moduleReferencesSqlQuery = buildModuleReferencesSqlQuery(parentFragmentWhereClause,
+                childFragmentWhereClause);
+
+        final Query query = entityManager.createNativeQuery(moduleReferencesSqlQuery);
+        setQueryParameters(query, parentAttributes, childAttributes, anchorName, dataspaceName);
+        return processQueryResults(query.getResultList());
+    }
+
+    private String buildWhereClause(final Map<String, String> attributes, final String alias) {
+        return attributes.keySet().stream()
+                .map(attributeName -> String.format("%s.attributes->>'%s' = ?", alias, attributeName))
+                .collect(Collectors.joining(" AND "));
+    }
+
+    private void setQueryParameters(final Query query, final Map<String, String> parentAttributes,
+                                    final Map<String, String> childAttributes, final String anchorName,
+                                    final String dataspaceName) {
+        final String childAttributeValue = childAttributes.entrySet().iterator().next().getValue();
+        query.setParameter(1, childAttributeValue);
+
+        final String parentAttributeValue = parentAttributes.entrySet().iterator().next().getValue();
+        query.setParameter(2, parentAttributeValue);
+
+        query.setParameter(3, anchorName);
+        query.setParameter(4, dataspaceName);
+    }
+
+    private String buildModuleReferencesSqlQuery(final String parentFragmentClause, final String childFragmentClause) {
+        return """
+                WITH Fragment AS (
+                    SELECT childFragment.attributes->>'id' AS schema_set_name
+                    FROM fragment parentFragment
+                    JOIN fragment childFragment ON parentFragment.parent_id = childFragment.id
+                    JOIN anchor anchorInfo ON parentFragment.anchor_id = anchorInfo.id
+                    JOIN dataspace dataspaceInfo ON anchorInfo.dataspace_id = dataspaceInfo.id
+                    WHERE %s
+                    AND %s
+                    AND anchorInfo.name = ?
+                    AND dataspaceInfo.name = ?
+                    LIMIT 1
+                ),
+                SchemaSet AS (
+                    SELECT id
+                    FROM schema_set
+                    WHERE name = (SELECT schema_set_name FROM Fragment)
+                )
+                SELECT yangResource.module_name, yangResource.revision
+                FROM yang_resource yangResource
+                JOIN schema_set_yang_resources schemaSetYangResources
+                ON yangResource.id = schemaSetYangResources.yang_resource_id
+                WHERE schemaSetYangResources.schema_set_id = (SELECT id FROM SchemaSet);
+                """.formatted(parentFragmentClause, childFragmentClause);
+    }
+
+    private Collection<ModuleReference> processQueryResults(final List<Object[]> queryResults) {
+        if (queryResults.isEmpty()) {
+            log.info("No module references found for the provided attributes.");
+            return Collections.emptyList();
+        }
+        return queryResults.stream()
+                .map(queryResult -> {
+                    final String name = (String) queryResult[0];
+                    final String revision = (String) queryResult[1];
+                    return new ModuleReference(name, revision);
+                })
+                .collect(Collectors.toList());
+    }
+
     private Collection<ModuleReference> identifyNewModuleReferencesForCmHandle(final String tempTableName) {
         final String sql = String.format(
                 "SELECT %1$s.module_name, %1$s.revision"
@@ -81,7 +174,6 @@ public class ModuleReferenceRepositoryImpl implements ModuleReferenceQuery {
         for (final Object[] row : resultsAsObjects) {
             resultsAsModuleReferences.add(new ModuleReference((String) row[0], (String) row[1]));
         }
-
         return resultsAsModuleReferences;
     }
 }