Change SQL query syntax for delete resource/template

The delete query does not work with Mariadb version 10.1.24.
Changed the syntax so that it will work with both older and newer
versions. Typo in method names has been fixed as well.

Issue-ID: CCSDK-3735
Change-Id: I225752d62068e5aa44354624aa6542a4f4bae73b
Signed-off-by: kuldipr <kuldip.rai@amdocs.com>

4 files changed, 35 insertions, 21 deletions

diff --git a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBService.kt b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBService.kt
index 3041fa773..aa7b61f6a 100644
--- a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBService.kt
+++ b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBService.kt
@@ -303,7 +303,7 @@ class ResourceResolutionDBService(private val resourceResolutionRepository: Reso
         if (lastNOccurrences < 0) {
             throw IllegalArgumentException("last N occurrences must be a positive integer")
         }
-        resourceResolutionRepository.deleteLastNOccurences(
+        resourceResolutionRepository.deleteLastNOccurrences(
             blueprintName,
             blueprintVersion,
             artifactName,
@@ -341,7 +341,7 @@ class ResourceResolutionDBService(private val resourceResolutionRepository: Reso
         if (lastNOccurrences < 0) {
             throw IllegalArgumentException("last N occurrences must be a positive integer")
         }
-        resourceResolutionRepository.deleteLastNOccurences(
+        resourceResolutionRepository.deleteLastNOccurrences(
             blueprintName,
             blueprintVersion,
             artifactName,
diff --git a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionRepository.kt b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionRepository.kt
index 30969f1f6..9317a71cd 100644
--- a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionRepository.kt
+++ b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionRepository.kt
@@ -179,13 +179,17 @@ interface ResourceResolutionRepository : JpaRepository<ResourceResolution, Strin
         WHERE resolution_key = :resolutionKey AND blueprint_name = :blueprintName
             AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName
             AND occurrence > (
-                SELECT max(occurrence) - :lastN FROM RESOURCE_RESOLUTION
-                WHERE resolution_key = :resolutionKey AND blueprint_name = :blueprintName
-                    AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName)
+                SELECT MAX(occurrence) - :lastN FROM (
+                    SELECT occurrence from RESOURCE_RESOLUTION
+                    WHERE resolution_key = :resolutionKey
+                        AND blueprint_name = :blueprintName
+                        AND blueprint_version = :blueprintVersion
+                        AND artifact_name = :artifactName) AS o
+                )
     """,
         nativeQuery = true
     )
-    fun deleteLastNOccurences(
+    fun deleteLastNOccurrences(
         @Param("blueprintName") blueprintName: String,
         @Param("blueprintVersion") blueprintVersion: String,
         @Param("artifactName") artifactName: String,
@@ -202,14 +206,18 @@ interface ResourceResolutionRepository : JpaRepository<ResourceResolution, Strin
             AND blueprint_name = :blueprintName
             AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName
             AND occurrence > (
-                SELECT max(occurrence) - :lastN FROM RESOURCE_RESOLUTION
-                WHERE resource_type = :resourceType AND resource_id = :resourceId
-                    AND blueprint_name = :blueprintName
-                    AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName)
+                SELECT MAX(occurrence) - :lastN FROM (
+                    SELECT occurrence FROM RESOURCE_RESOLUTION
+                    WHERE resource_type = :resourceType
+                        AND resource_id = :resourceId
+                        AND blueprint_name = :blueprintName
+                        AND blueprint_version = :blueprintVersion
+                        AND artifact_name = :artifactName) AS o
+                )
     """,
         nativeQuery = true
     )
-    fun deleteLastNOccurences(
+    fun deleteLastNOccurrences(
         @Param("blueprintName") blueprintName: String,
         @Param("blueprintVersion") blueprintVersion: String,
         @Param("artifactName") artifactName: String,
diff --git a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/TemplateResolutionRepository.kt b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/TemplateResolutionRepository.kt
index 1ee9f7999..049e713ce 100644
--- a/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/TemplateResolutionRepository.kt
+++ b/ms/blueprintsprocessor/functions/resource-resolution/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/TemplateResolutionRepository.kt
@@ -189,9 +189,12 @@ interface TemplateResolutionRepository : JpaRepository<TemplateResolution, Strin
             AND blueprint_name = :blueprintName AND blueprint_version = :blueprintVersion
             AND artifact_name = :artifactName
             AND occurrence > (
-                SELECT MAX(occurrence) - :lastN FROM TEMPLATE_RESOLUTION
-                WHERE resolution_key = :resolutionKey AND blueprint_name = :blueprintName
-                    AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName
+                SELECT MAX(occurrence) - :lastN FROM (
+                    SELECT occurrence FROM TEMPLATE_RESOLUTION
+                    WHERE resolution_key = :resolutionKey
+                        AND blueprint_name = :blueprintName
+                        AND blueprint_version = :blueprintVersion
+                        AND artifact_name = :artifactName) AS o
                 )
     """,
         nativeQuery = true
@@ -212,11 +215,14 @@ interface TemplateResolutionRepository : JpaRepository<TemplateResolution, Strin
             AND resource_id = :resourceId AND artifact_name = :artifactName
             AND blueprint_name = :blueprintName AND blueprint_version = :blueprintVersion
             AND occurrence > (
-                SELECT MAX(occurrence) - :lastN FROM TEMPLATE_RESOLUTION
-                WHERE resource_type = :resourceType
-                    AND resource_id = :resourceId AND blueprint_name = :blueprintName
-                    AND blueprint_version = :blueprintVersion AND artifact_name = :artifactName
-            )
+                SELECT MAX(occurrence) - :lastN FROM (
+                    SELECT occurrence FROM TEMPLATE_RESOLUTION
+                    WHERE resource_type = :resourceType
+                        AND resource_id = :resourceId
+                        AND blueprint_name = :blueprintName
+                        AND blueprint_version = :blueprintVersion
+                        AND artifact_name = :artifactName) AS o
+                )
     """,
         nativeQuery = true
     )
diff --git a/ms/blueprintsprocessor/functions/resource-resolution/src/test/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBServiceTest.kt b/ms/blueprintsprocessor/functions/resource-resolution/src/test/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBServiceTest.kt
index 69e7a646a..825b86e7f 100644
--- a/ms/blueprintsprocessor/functions/resource-resolution/src/test/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBServiceTest.kt
+++ b/ms/blueprintsprocessor/functions/resource-resolution/src/test/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/functions/resource/resolution/db/ResourceResolutionDBServiceTest.kt
@@ -336,7 +336,7 @@ open class ResourceResolutionDBServiceTest {
     @Test
     fun deleteResourcesResolutionKeyLastN() {
         every {
-            resourceResolutionRepository.deleteLastNOccurences(blueprintName, blueprintVersion, artifactPrefix, resolutionKey, 1)
+            resourceResolutionRepository.deleteLastNOccurrences(blueprintName, blueprintVersion, artifactPrefix, resolutionKey, 1)
         } returns 4
         runBlocking {
             val res = resourceResolutionDBService.deleteResources(
@@ -362,7 +362,7 @@ open class ResourceResolutionDBServiceTest {
     @Test
     fun deleteResourcesResourceIdAndTypeLastN() {
         every {
-            resourceResolutionRepository.deleteLastNOccurences(blueprintName, blueprintVersion, artifactPrefix, resourceType, resourceId, 2)
+            resourceResolutionRepository.deleteLastNOccurrences(blueprintName, blueprintVersion, artifactPrefix, resourceType, resourceId, 2)
         } returns 6
         runBlocking {
             val res = resourceResolutionDBService.deleteResources(