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>

11 files changed, 211 insertions, 55 deletions

diff --git a/cps-application/src/main/resources/application.yml b/cps-application/src/main/resources/application.yml
index 83494d6545..f61a09ba17 100644
--- a/cps-application/src/main/resources/application.yml
+++ b/cps-application/src/main/resources/application.yml
@@ -215,7 +215,7 @@ ncmp:
         advised-modules-sync:
             sleep-time-ms: 5000
         locked-modules-sync:
-            sleep-time-ms: 300000
+            sleep-time-ms: 60000
         cm-handle-data-sync:
             sleep-time-ms: 30000
         subscription-forwarding:
diff --git a/cps-ncmp-service/src/main/java/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncService.java b/cps-ncmp-service/src/main/java/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncService.java
index d2bc3ada86..ca0f1c6a6d 100644
--- a/cps-ncmp-service/src/main/java/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncService.java
+++ b/cps-ncmp-service/src/main/java/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncService.java
@@ -29,23 +29,17 @@ import static org.onap.cps.ncmp.impl.inventory.NcmpPersistence.NFP_OPERATIONAL_D
 import java.time.OffsetDateTime;
 import java.util.Collection;
 import java.util.Collections;
-import java.util.List;
 import java.util.Map;
 import lombok.AllArgsConstructor;
 import lombok.RequiredArgsConstructor;
 import lombok.extern.slf4j.Slf4j;
-import org.apache.commons.lang3.StringUtils;
 import org.onap.cps.api.CpsAnchorService;
 import org.onap.cps.api.CpsDataService;
 import org.onap.cps.api.CpsModuleService;
-import org.onap.cps.ncmp.impl.inventory.CmHandleQueryService;
 import org.onap.cps.ncmp.impl.inventory.models.CmHandleState;
 import org.onap.cps.ncmp.impl.inventory.models.YangModelCmHandle;
-import org.onap.cps.ncmp.impl.utils.YangDataConverter;
 import org.onap.cps.spi.CascadeDeleteAllowed;
-import org.onap.cps.spi.FetchDescendantsOption;
 import org.onap.cps.spi.exceptions.SchemaSetNotFoundException;
-import org.onap.cps.spi.model.DataNode;
 import org.onap.cps.spi.model.ModuleReference;
 import org.onap.cps.utils.ContentType;
 import org.onap.cps.utils.JsonObjectMapper;
@@ -58,7 +52,6 @@ public class ModuleSyncService {
 
     private final DmiModelOperations dmiModelOperations;
     private final CpsModuleService cpsModuleService;
-    private final CmHandleQueryService cmHandleQueryService;
     private final CpsDataService cpsDataService;
     private final CpsAnchorService cpsAnchorService;
     private final JsonObjectMapper jsonObjectMapper;
@@ -113,34 +106,25 @@ public class ModuleSyncService {
     }
 
     private ModuleDelta getModuleDelta(final YangModelCmHandle yangModelCmHandle, final String targetModuleSetTag) {
-        final Collection<ModuleReference> allModuleReferences;
         final Map<String, String> newYangResources;
-
-        final YangModelCmHandle cmHandleWithSameModuleSetTag = getAnyReadyCmHandleByModuleSetTag(targetModuleSetTag);
-        if (cmHandleWithSameModuleSetTag == null) {
+        Collection<ModuleReference> allModuleReferences = getModuleReferencesByModuleSetTag(targetModuleSetTag);
+        if (allModuleReferences.isEmpty()) {
             allModuleReferences = dmiModelOperations.getModuleReferences(yangModelCmHandle);
             newYangResources = dmiModelOperations.getNewYangResourcesFromDmi(yangModelCmHandle,
                     cpsModuleService.identifyNewModuleReferences(allModuleReferences));
         } else {
             log.info("Found other cm handle having same module set tag: {}", targetModuleSetTag);
-            allModuleReferences = cpsModuleService.getYangResourcesModuleReferences(
-                    NFP_OPERATIONAL_DATASTORE_DATASPACE_NAME, cmHandleWithSameModuleSetTag.getId());
             newYangResources = NO_NEW_MODULES;
         }
         return new ModuleDelta(allModuleReferences, newYangResources);
     }
 
-    private YangModelCmHandle getAnyReadyCmHandleByModuleSetTag(final String moduleSetTag) {
-        if (StringUtils.isBlank(moduleSetTag)) {
-            return null;
+    private Collection<ModuleReference> getModuleReferencesByModuleSetTag(final String moduleSetTag) {
+        if (moduleSetTag == null || moduleSetTag.trim().isEmpty()) {
+            return Collections.emptyList();
         }
-        final String escapedModuleSetTag = moduleSetTag.replace("'", "''");
-        final List<DataNode> dataNodes = cmHandleQueryService.queryNcmpRegistryByCpsPath(
-                NCMP_DMI_REGISTRY_PARENT + "/cm-handles[@module-set-tag='" + escapedModuleSetTag + "']",
-                FetchDescendantsOption.DIRECT_CHILDREN_ONLY);
-        return dataNodes.stream().map(YangDataConverter::toYangModelCmHandle)
-                .filter(cmHandle -> cmHandle.getCompositeState().getCmHandleState() == CmHandleState.READY)
-                .findFirst().orElse(null);
+        return cpsModuleService.getModuleReferencesByAttribute(NCMP_DATASPACE_NAME, NCMP_DMI_REGISTRY_ANCHOR,
+                Map.of("module-set-tag", moduleSetTag), Map.of("cm-handle-state", CmHandleState.READY.name()));
     }
 
     private void setCmHandleModuleSetTag(final YangModelCmHandle yangModelCmHandle, final String newModuleSetTag) {
@@ -149,4 +133,5 @@ public class ModuleSyncService {
         cpsDataService.updateNodeLeaves(NCMP_DATASPACE_NAME, NCMP_DMI_REGISTRY_ANCHOR, NCMP_DMI_REGISTRY_PARENT,
                 jsonForUpdate, OffsetDateTime.now(), ContentType.JSON);
     }
+
 }
diff --git a/cps-ncmp-service/src/test/groovy/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncServiceSpec.groovy b/cps-ncmp-service/src/test/groovy/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncServiceSpec.groovy
index c3a01a7393..6030e5debf 100644
--- a/cps-ncmp-service/src/test/groovy/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncServiceSpec.groovy
+++ b/cps-ncmp-service/src/test/groovy/org/onap/cps/ncmp/impl/inventory/sync/ModuleSyncServiceSpec.groovy
@@ -30,8 +30,6 @@ import org.onap.cps.ncmp.impl.inventory.models.CmHandleState
 import org.onap.cps.ncmp.impl.inventory.models.YangModelCmHandle
 import org.onap.cps.spi.CascadeDeleteAllowed
 import org.onap.cps.spi.exceptions.SchemaSetNotFoundException
-import org.onap.cps.spi.model.DataNode
-import org.onap.cps.spi.model.DataNodeBuilder
 import org.onap.cps.spi.model.ModuleReference
 import org.onap.cps.utils.JsonObjectMapper
 import spock.lang.Specification
@@ -49,13 +47,9 @@ class ModuleSyncServiceSpec extends Specification {
     def mockJsonObjectMapper = Mock(JsonObjectMapper)
 
     def objectUnderTest = new ModuleSyncService(mockDmiModelOperations, mockCpsModuleService,
-            mockCmHandleQueries, mockCpsDataService, mockCpsAnchorService, mockJsonObjectMapper)
+            mockCpsDataService, mockCpsAnchorService, mockJsonObjectMapper)
 
     def expectedDataspaceName = NFP_OPERATIONAL_DATASTORE_DATASPACE_NAME
-    def static cmHandleWithModuleSetTag = new DataNodeBuilder()
-            .withXpath("/dmi-registry/cm-handles[@id='otherId']")
-            .withLeaves(['id': 'otherId', 'module-set-tag': 'tag-1'])
-            .withAnchor('otherId').build()
 
     def 'Sync model for a NEW cm handle using module set tags: #scenario.'() {
         given: 'a cm handle state to be synced'
@@ -70,8 +64,8 @@ class ModuleSyncServiceSpec extends Specification {
             mockDmiModelOperations.getNewYangResourcesFromDmi(yangModelCmHandle, identifiedNewModuleReferences) >> newModuleNameContentToMap
         and: 'the module service identifies #identifiedNewModuleReferences.size() new modules'
             mockCpsModuleService.identifyNewModuleReferences(moduleReferences) >> identifiedNewModuleReferences
-        and: 'system contains other cm handle with "same tag" (that is READY)'
-            mockCmHandleQueries.queryNcmpRegistryByCpsPath(*_) >> existingCmHandlesWithSameTag
+        and: 'the service returns a list of module references when queried with the specified attributes'
+            mockCpsModuleService.getModuleReferencesByAttribute(*_) >> existingModuleReferences
         when: 'module sync is triggered'
             objectUnderTest.syncAndCreateSchemaSetAndAnchor(yangModelCmHandle)
         then: 'create schema set from module is invoked with correct parameters'
@@ -79,10 +73,10 @@ class ModuleSyncServiceSpec extends Specification {
         and: 'anchor is created with the correct parameters'
             1 * mockCpsAnchorService.createAnchor(NFP_OPERATIONAL_DATASTORE_DATASPACE_NAME, 'ch-1', 'ch-1')
         where: 'the following parameters are used'
-            scenario                  | existingModuleResourcesInCps         | identifiedNewModuleReferences         | newModuleNameContentToMap     | moduleSetTag | existingCmHandlesWithSameTag
-            'one new module, new tag' | [['module2': '2'], ['module3': '3']] | [new ModuleReference('module1', '1')] | [module1: 'some yang source'] | ''           | []
-            'no new module, new tag'  | [['module1': '1'], ['module2': '2']] | []                                    | [:]                           | 'new-tag-1'  | []
-            'same tag'                | [['module1': '1'], ['module2': '2']] | []                                    | [:]                           | 'same-tag'   | [cmHandleWithModuleSetTag]
+            scenario                  | identifiedNewModuleReferences         | newModuleNameContentToMap     | moduleSetTag | existingModuleReferences
+            'one new module, new tag' | [new ModuleReference('module1', '1')] | [module1: 'some yang source'] | ''           | []
+            'no new module, new tag'  | []                                    | [:]                           | 'new-tag-1'  | []
+            'same tag'                | []                                    | [:]                           | 'same-tag'   | [new ModuleReference('module1', '1'), new ModuleReference('module2', '2')]
     }
 
     def 'Upgrade model for an existing cm handle with Module Set Tag where the modules are #scenario'() {
@@ -101,8 +95,8 @@ class ModuleSyncServiceSpec extends Specification {
             mockCpsModuleService.identifyNewModuleReferences(_) >> []
         and: 'CPS-Core returns list of existing module resources for TBD'
             mockCpsModuleService.getYangResourcesModuleReferences(*_) >> [ new ModuleReference('module1','1') ]
-        and: 'system contains #existingCmHandlesWithSameTag.size() cm handles with same tag'
-            mockCmHandleQueries.queryNcmpRegistryByCpsPath(*_) >> existingCmHandlesWithSameTag
+        and: 'the service returns a list of module references when queried with the specified attributes'
+            mockCpsModuleService.getModuleReferencesByAttribute(*_) >> existingModuleReferences
         and: 'the other cm handle is a state ready'
             mockCmHandleQueries.cmHandleHasState('otherId', CmHandleState.READY) >> true
         when: 'module sync is triggered'
@@ -114,9 +108,9 @@ class ModuleSyncServiceSpec extends Specification {
         and: 'No anchor is created for the upgraded cm handle'
             0 * mockCpsAnchorService.createAnchor(*_)
         where: 'the following parameters are used'
-            scenario      | existingCmHandlesWithSameTag
+            scenario      | existingModuleReferences
             'new'         | []
-            'in database' | [cmHandleWithModuleSetTag]
+            'in database' | [new ModuleReference('module1', '1')]
     }
 
     def 'upgrade model for a existing cm handle'() {
@@ -130,9 +124,8 @@ class ModuleSyncServiceSpec extends Specification {
         and: 'the module service returns some module references'
             def moduleReferences = [new ModuleReference('module1', '1'), new ModuleReference('module2', '2')]
             mockCpsModuleService.getYangResourcesModuleReferences(*_)>> moduleReferences
-        and: 'a cm handle with the same moduleSetTag can be found in the registry'
-            mockCmHandleQueries.queryNcmpRegistryByCpsPath(*_) >> [new DataNode(xpath: '/dmi-registry/cm-handles[@id=\'cmHandleId-1\']', leaves: ['id': 'cmHandleId-1'],
-                    childDataNodes: [new DataNode(xpath: '/dmi-registry/cm-handles[@id=\'cmHandleId-1\']/state', leaves: ['cm-handle-state': 'READY'])])]
+        and: 'the service returns a list of module references when queried with the specified attributes'
+            mockCpsModuleService.getModuleReferencesByAttribute(*_) >> moduleReferences
         when: 'module upgrade is triggered'
             objectUnderTest.syncAndUpgradeSchemaSet(yangModelCmHandle)
         then: 'the upgrade is delegated to the module service (with the correct parameters)'
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;
     }
 }
diff --git a/cps-service/src/main/java/org/onap/cps/api/CpsModuleService.java b/cps-service/src/main/java/org/onap/cps/api/CpsModuleService.java
index bdd361458e..931209c998 100644
--- a/cps-service/src/main/java/org/onap/cps/api/CpsModuleService.java
+++ b/cps-service/src/main/java/org/onap/cps/api/CpsModuleService.java
@@ -155,4 +155,37 @@ public interface CpsModuleService {
     Collection<ModuleReference> identifyNewModuleReferences(
         Collection<ModuleReference> moduleReferencesToCheck);
 
+    /**
+     * Retrieves module references based on the provided dataspace name, anchor name and attribute filters
+     * for both parent and child fragments.
+
+     * This method constructs and executes a SQL query to find module references from a database, using
+     * the specified `dataspaceName`, `anchorName` and two sets of attribute filters: one for parent fragments
+     * and one for child fragments. The method applies these filters to identify the appropriate fragments
+     * and schema sets, and then retrieves the corresponding module references.
+
+     * The SQL query is dynamically built based on the provided attribute filters:
+     * - The `parentAttributes` map is used to filter the parent fragments. The entries in this map are
+     * converted into a WHERE clause for the parent fragments.
+     * - The `childAttributes` map is used to filter the child fragments. This is applied to the child fragments
+     * after filtering the parent fragments.
+     *
+     * @param dataspaceName    the name of the dataspace to filter on. It is used to locate the relevant dataspace
+     *                         in the database.
+     * @param anchorName       the name of the anchor to filter on. It is used to locate the relevant anchor within
+     *                         the dataspace.
+     * @param parentAttributes a map of attributes to filter parent fragments. Each entry in this map represents
+     *                         an attribute key-value pair used in the WHERE clause for parent fragments.
+     * @param childAttributes  a map of attributes to filter child fragments. Each entry in this map represents
+     *                         an attribute key-value pair used in the WHERE clause for child fragments.
+     * @return a collection of {@link ModuleReference} objects that match the given criteria. Each
+     * {@code ModuleReference} contains information about a module's name and revision.
+     * @implNote The method assumes that both `parentAttributes` and `childAttributes` maps contain at least
+     *     one entry. The first entry from `parentAttributes` is used to filter parent fragments,
+     *     and the first entry from `childAttributes` is used to filter child fragments.
+     */
+    Collection<ModuleReference> getModuleReferencesByAttribute(final String dataspaceName, final String anchorName,
+                                                               final Map<String, String> parentAttributes,
+                                                               final Map<String, String> childAttributes);
+
 }
diff --git a/cps-service/src/main/java/org/onap/cps/api/impl/CpsModuleServiceImpl.java b/cps-service/src/main/java/org/onap/cps/api/impl/CpsModuleServiceImpl.java
index e6ad9a8bb8..34610f3455 100644
--- a/cps-service/src/main/java/org/onap/cps/api/impl/CpsModuleServiceImpl.java
+++ b/cps-service/src/main/java/org/onap/cps/api/impl/CpsModuleServiceImpl.java
@@ -171,6 +171,17 @@ public class CpsModuleServiceImpl implements CpsModuleService {
         return cpsModulePersistenceService.identifyNewModuleReferences(moduleReferencesToCheck);
     }
 
+    @Timed(value = "cps.module.service.module.reference.query",
+            description = "Time taken to query list of module references")
+    @Override
+    public Collection<ModuleReference> getModuleReferencesByAttribute(final String dataspaceName,
+                                                                      final String anchorName,
+                                                                      final Map<String, String> parentAttributes,
+                                                                      final Map<String, String> childAttributes) {
+        return cpsModulePersistenceService.getModuleReferencesByAttribute(dataspaceName, anchorName, parentAttributes,
+                childAttributes);
+    }
+
     private boolean isCascadeDeleteProhibited(final CascadeDeleteAllowed cascadeDeleteAllowed) {
         return CascadeDeleteAllowed.CASCADE_DELETE_PROHIBITED == cascadeDeleteAllowed;
     }
diff --git a/cps-service/src/main/java/org/onap/cps/spi/CpsModulePersistenceService.java b/cps-service/src/main/java/org/onap/cps/spi/CpsModulePersistenceService.java
index eeaaa47991..793f38e4bc 100755
--- a/cps-service/src/main/java/org/onap/cps/spi/CpsModulePersistenceService.java
+++ b/cps-service/src/main/java/org/onap/cps/spi/CpsModulePersistenceService.java
@@ -153,4 +153,20 @@ public interface CpsModulePersistenceService {
     Collection<ModuleReference> identifyNewModuleReferences(
         Collection<ModuleReference> moduleReferencesToCheck);
 
+    /**
+     * Retrieves module references based on the specified dataspace, anchor, and attribute filters.
+
+     * Constructs and executes a SQL query to find module references by applying filters for parent and child fragments.
+     * Uses `parentAttributes` for filtering parent fragments and `childAttributes` for filtering 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 matching the criteria.
+     */
+    Collection<ModuleReference> getModuleReferencesByAttribute(final String dataspaceName, final String anchorName,
+                                                               final Map<String, String> parentAttributes,
+                                                               final Map<String, String> childAttributes);
+
 }
diff --git a/cps-service/src/test/groovy/org/onap/cps/api/impl/CpsModuleServiceImplSpec.groovy b/cps-service/src/test/groovy/org/onap/cps/api/impl/CpsModuleServiceImplSpec.groovy
index ad8c54bf27..62eba0c397 100644
--- a/cps-service/src/test/groovy/org/onap/cps/api/impl/CpsModuleServiceImplSpec.groovy
+++ b/cps-service/src/test/groovy/org/onap/cps/api/impl/CpsModuleServiceImplSpec.groovy
@@ -238,6 +238,23 @@ class CpsModuleServiceImplSpec extends Specification {
             1 * mockCpsModulePersistenceService.identifyNewModuleReferences(moduleReferencesToCheck)
     }
 
+    def 'Get module references when queried by attributes'() {
+        given: 'a valid dataspace name and anchor name'
+            def dataspaceName = 'someDataspace'
+            def anchorName = 'someAnchor'
+        and: 'a set of parent attributes and child attributes used for filtering'
+            def parentAttributes = ['some-property-key1': 'some-property-val1']
+            def childAttributes = ['some-property-key2': 'some-property-val2']
+        and: 'a list of expected module references returned by the persistence service'
+            def expectedModuleReferences = [new ModuleReference(moduleName: 'some-name', revision: 'some-revision')]
+            mockCpsModulePersistenceService.getModuleReferencesByAttribute(dataspaceName, anchorName, parentAttributes, childAttributes) >> expectedModuleReferences
+        when: 'the method is invoked to retrieve module references by attributes'
+            def actualModuleReferences = objectUnderTest.getModuleReferencesByAttribute(dataspaceName, anchorName, parentAttributes, childAttributes)
+        then: 'the retrieved module references should match the expected module references'
+            assert actualModuleReferences == expectedModuleReferences
+    }
+
+
     def 'Getting module definitions with module name'() {
         given: 'module persistence service returns module definitions for module name'
             def moduleDefinitionsFromPersistenceService = [ new ModuleDefinition('name', 'revision', 'content' ) ]