Extract query

Issue-ID: DCAEGEN2-2019
Signed-off-by: Zebek Bogumil <bogumil.zebek@nokia.com>
Change-Id: I5f22589af67e11bf5bf7292f95df9f416007a15f

4 files changed, 466 insertions, 86 deletions

diff --git a/src/main/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImpl.java b/src/main/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImpl.java
index 13f1540..3a08daa 100644
--- a/src/main/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImpl.java
+++ b/src/main/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImpl.java
@@ -3,6 +3,7 @@
  * dcae-inventory
  * ================================================================================
  * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2020 Nokia Intellectual Property. All rights reserved.
  * ================================================================================
  * Licensed under the Apache License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
@@ -88,86 +89,15 @@ public class DcaeServiceTypesApiServiceImpl extends DcaeServiceTypesApiService {
         DateTime createdCutoff = DateTime.now(DateTimeZone.UTC);
 
         try (Handle jdbiHandle = InventoryDAOManager.getInstance().getHandle()) {
-            StringBuilder sb = new StringBuilder("select * from ");
+            final String queryStatement = DcaeServiceTypesQueryStatement.create(typeName, onlyLatest, onlyActive,
+                    vnfType, serviceId, serviceLocation, asdcServiceId, asdcResourceId, owner, application, component);
 
-            if (onlyLatest) {
-                // Use the view which filters types that are of only the latest versions
-                sb.append("dcae_service_types_latest");
-            } else {
-                sb.append("dcae_service_types");
-            }
-
-            List<String> whereClauses = new ArrayList<>();
-            
-            if (typeName != null) {
-                if (!typeName.contains("*")) {
-                    whereClauses.add(":typeName = type_name");
-                }
-                else {
-                    typeName = typeName.replaceAll("\\*", "%");
-                    whereClauses.add("type_name LIKE :typeName");
-                }                
-            }
-
-            if (vnfType != null) {
-                whereClauses.add("lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[])");
-            }
-
-            if (serviceId != null) {
-                whereClauses.add("(:serviceId = any(service_ids) or service_ids = \'{}\' or service_ids is null)");
-            }
-
-            if (serviceLocation != null) {
-                whereClauses.add("(:serviceLocation = any(service_locations) or service_locations = \'{}\' or service_locations is null)");
-            }
-            
-            if (asdcServiceId != null) {
-                if (asdcServiceId.equalsIgnoreCase("NONE")) {
-                    whereClauses.add("asdc_service_id is null");
-                } else {
-                    whereClauses.add(":asdcServiceId = asdc_service_id");
-                }
-            }                       
-
-            if (asdcResourceId != null) {
-                if (asdcResourceId.equalsIgnoreCase("NONE")) {
-                    whereClauses.add("asdc_resource_id is null");
-                } else {
-                    whereClauses.add(":asdcResourceId = asdc_resource_id");
-                }
-            }
-            
-            if (owner != null) {
-                whereClauses.add(":owner = owner");
-            }
-            
-            if (application != null) {
-                whereClauses.add(":application = application");
-            }
-
-            if (component != null) {
-                whereClauses.add(":component = component");
-            }
-
-            whereClauses.add("created < :createdCutoff");
-
-            if (onlyActive) {
-                whereClauses.add("deactivated is null");
-            }
-
-            if (!whereClauses.isEmpty()) {
-                sb.append(" where ");
-                sb.append(String.join(" and ", whereClauses));
-            }
+            metricsLogger.info("Query created as: {}." + queryStatement);
 
-            // Sort by created timestamp - always descending.
-            sb.append(" order by created desc");
-            
-            metricsLogger.info("Query created as: {}." + sb.toString());
-
-            Query<DCAEServiceTypeObject> query = jdbiHandle.createQuery(sb.toString()).map(new DCAEServiceTypeObjectMapper());
+            Query<DCAEServiceTypeObject> query = jdbiHandle.createQuery(queryStatement).map(new DCAEServiceTypeObjectMapper());
 
             if (typeName != null) {
+                typeName = resolveTypeName(typeName);
                 query.bind("typeName", typeName);
             }
 
@@ -190,15 +120,15 @@ public class DcaeServiceTypesApiServiceImpl extends DcaeServiceTypesApiService {
             if (asdcResourceId != null && !"NONE".equalsIgnoreCase(asdcResourceId)) {
                 query.bind("asdcResourceId", asdcResourceId);
             }
-            
+
             if (application != null) {
                 query.bind("application", application);
             }
-            
+
             if (component != null) {
                 query.bind("component", component);
             }
-            
+
             if (owner != null) {
                 query.bind("owner", owner);
             }
@@ -246,7 +176,7 @@ public class DcaeServiceTypesApiServiceImpl extends DcaeServiceTypesApiService {
         response.setLinks(navigationLinks);
 
         debugLogger.debug("Response: {}." + response.toString());
-        
+
         return Response.ok().entity(response).build();
     }
 
@@ -263,6 +193,13 @@ public class DcaeServiceTypesApiServiceImpl extends DcaeServiceTypesApiService {
         return Response.ok().entity(createDCAEServiceType(serviceTypeObject, uriInfo)).build();
     }
 
+    static String resolveTypeName(String typeName){
+        if (typeName.contains("*")) {
+            return typeName.replaceAll("\\*", "%");
+        }
+        return typeName;
+    }
+
     /**
      * Create a DCAE service type database object
      *
diff --git a/src/main/java/io/swagger/api/impl/DcaeServiceTypesQueryStatement.java b/src/main/java/io/swagger/api/impl/DcaeServiceTypesQueryStatement.java
new file mode 100644
index 0000000..5a1f201
--- /dev/null
+++ b/src/main/java/io/swagger/api/impl/DcaeServiceTypesQueryStatement.java
@@ -0,0 +1,184 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * dcae-inventory
+ * ================================================================================
+ * Copyright (C) 2020 Nokia Intellectual Property. All rights reserved.
+ * ================================================================================
+ * 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.
+ * ============LICENSE_END=========================================================
+ */
+package io.swagger.api.impl;
+
+import org.glassfish.jersey.internal.util.Producer;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.function.Function;
+
+public class DcaeServiceTypesQueryStatement {
+    private final StringBuilder sb = new StringBuilder("select * from ");
+    private final List<String> whereClauses = new ArrayList<>();
+
+    private static DcaeServiceTypesQueryStatement create() {
+        return new DcaeServiceTypesQueryStatement();
+    }
+
+
+    public static String create(String typeName, Boolean onlyLatest, Boolean onlyActive, String vnfType,
+                                String serviceId, String serviceLocation, String asdcServiceId, String asdcResourceId,
+                                String owner, String application, String component) {
+
+        String from = resolveFrom(onlyLatest);
+
+        final DcaeServiceTypesQueryStatement queryStatement = DcaeServiceTypesQueryStatement.create();
+        queryStatement.from(from)
+                .where()
+                .andIfNotNull(typeName, queryStatement::typeName)
+                .andIfNotNull(vnfType, queryStatement::vnfType)
+                .andIfNotNull(serviceId, queryStatement::serviceId)
+                .andIfNotNull(serviceLocation, queryStatement::serviceLocation)
+                .andIfNotNull(asdcServiceId, queryStatement::asdcServiceId)
+                .andIfNotNull(asdcResourceId, queryStatement::asdcResourceId)
+                .andIfNotNull(owner, queryStatement::owner)
+                .andIfNotNull(application, queryStatement::application)
+                .andIfNotNull(component, queryStatement::component)
+                .and().createdStatement()
+                .andIfTrue(onlyActive, queryStatement::activeOnly);
+
+        return queryStatement.build();
+    }
+
+    private DcaeServiceTypesQueryStatement where() {
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement and() {
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement andIfNotNull(String value, Function<String, DcaeServiceTypesQueryStatement> callback) {
+        if (value != null) {
+            return callback.apply(value);
+        }
+
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement andIfTrue(boolean value, Producer<DcaeServiceTypesQueryStatement> callback) {
+        if (value) {
+            return callback.call();
+        }
+
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement from(String from) {
+        sb.append(from);
+        return this;
+    }
+
+
+    private DcaeServiceTypesQueryStatement typeName(String typeName) {
+        if (!typeName.contains("*")) {
+            whereClauses.add(":typeName = type_name");
+        } else {
+            whereClauses.add("type_name LIKE :typeName");
+        }
+
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement vnfType(String vnfType) {
+        whereClauses.add("lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[])");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement serviceId(String serviceId) {
+        whereClauses.add("(:serviceId = any(service_ids) or service_ids = \'{}\' or service_ids is null)");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement serviceLocation(String serviceLocation) {
+        whereClauses.add("(:serviceLocation = any(service_locations) or service_locations = \'{}\' or service_locations is null)");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement asdcServiceId(String asdcServiceId) {
+        if (asdcServiceId.equalsIgnoreCase("NONE")) {
+            whereClauses.add("asdc_service_id is null");
+        } else {
+            whereClauses.add(":asdcServiceId = asdc_service_id");
+        }
+
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement asdcResourceId(String asdcResourceId) {
+        if (asdcResourceId.equalsIgnoreCase("NONE")) {
+            whereClauses.add("asdc_resource_id is null");
+        } else {
+            whereClauses.add(":asdcResourceId = asdc_resource_id");
+        }
+
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement owner(String owner) {
+        whereClauses.add(":owner = owner");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement application(String application) {
+        whereClauses.add(":application = application");
+        return this;
+    }
+
+
+    private DcaeServiceTypesQueryStatement component(String component) {
+        whereClauses.add(":component = component");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement createdStatement() {
+        whereClauses.add("created < :createdCutoff");
+        return this;
+    }
+
+    private DcaeServiceTypesQueryStatement activeOnly() {
+        whereClauses.add("deactivated is null");
+        return this;
+    }
+
+    private String build() {
+        if (!whereClauses.isEmpty()) {
+            sb.append(" where ");
+            sb.append(String.join(" and ", whereClauses));
+        }
+
+        // Sort by created timestamp - always descending.
+        sb.append(" order by created desc");
+
+        return sb.toString();
+    }
+
+    private static String resolveFrom(Boolean onlyLatest) {
+        String from;
+        if (onlyLatest) {
+            // Use the view which filters types that are of only the latest versions
+            from = "dcae_service_types_latest";
+        } else {
+            from = "dcae_service_types";
+        }
+        return from;
+    }
+}
diff --git a/src/test/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImplTests.java b/src/test/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImplTests.java
index c1d200f..13dd820 100644
--- a/src/test/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImplTests.java
+++ b/src/test/java/io/swagger/api/impl/DcaeServiceTypesApiServiceImplTests.java
@@ -3,6 +3,7 @@ package io.swagger.api.impl;/*-
  * dcae-inventory
  * ================================================================================
  * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2020 Nokia Intellectual Property. All rights reserved.
  * ================================================================================
  * Licensed under the Apache License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
@@ -121,9 +122,9 @@ public class DcaeServiceTypesApiServiceImplTests {
         fullFixture.setCreated(DateTime.parse("2016-10-28T00:00"));
         fullFixture.setAsdcServiceId("4bb4e740-3920-442d-9ed3-89f15bdbff8a");
         fullFixture.setAsdcResourceId("3ea9dfae-a00d-4da8-8c87-02a34de8fc02");
-        fullFixture.setVnfTypes(Arrays.asList(new String[] { "vnf-marble", "vnf-granite" }));
-        fullFixture.setServiceIds(Arrays.asList(new String[] { "service-alpha", "service-bravo" }));
-        fullFixture.setServiceLocations(Arrays.asList(new String[] { "New York", "Washington" }));
+        fullFixture.setVnfTypes(Arrays.asList("vnf-marble", "vnf-granite"));
+        fullFixture.setServiceIds(Arrays.asList("service-alpha", "service-bravo"));
+        fullFixture.setServiceLocations(Arrays.asList("New York", "Washington"));
 
         for (DCAEServiceTypeObject fixture : new DCAEServiceTypeObject[] {minimalFixture, fullFixture}) {
             String someTypeId = fixture.getTypeId();
@@ -184,9 +185,9 @@ public class DcaeServiceTypesApiServiceImplTests {
         fullFixture.setBlueprintTemplate("{ blueprint template goes here }");
         fullFixture.setAsdcServiceId("4bb4e740-3920-442d-9ed3-89f15bdbff8a");
         fullFixture.setAsdcResourceId("3ea9dfae-a00d-4da8-8c87-02a34de8fc02");
-        fullFixture.setVnfTypes(Arrays.asList(new String[] { "vnf-marble", "vnf-granite" }));
-        fullFixture.setServiceIds(Arrays.asList(new String[] { "service-alpha", "service-bravo" }));
-        fullFixture.setServiceLocations(Arrays.asList(new String[] { "New York", "Washington" }));
+        fullFixture.setVnfTypes(Arrays.asList("vnf-marble", "vnf-granite"));
+        fullFixture.setServiceIds(Arrays.asList("service-alpha", "service-bravo"));
+        fullFixture.setServiceLocations(Arrays.asList("New York", "Washington"));
 
         for (DCAEServiceTypeRequest fixture : new DCAEServiceTypeRequest[] {minimalFixture, fullFixture}) {
             try {
@@ -222,4 +223,28 @@ public class DcaeServiceTypesApiServiceImplTests {
         }
     }
 
+    @Test
+    public void shouldReturnOriginalTypeName(){
+        // given
+        String typeName = "abc";
+
+        // when
+        final String actual = DcaeServiceTypesApiServiceImpl.resolveTypeName(typeName);
+
+        // then
+        assertEquals("abc", actual);
+    }
+
+    @Test
+    public void shouldTransformAsteriskToPercentCharacterInTypeName(){
+        // given
+        String typeName = "abc*d";
+
+        // when
+        final String actual = DcaeServiceTypesApiServiceImpl.resolveTypeName(typeName);
+
+        // then
+        assertEquals("abc%d", actual);
+    }
+
 }
diff --git a/src/test/java/io/swagger/api/impl/DcaeServiceTypesQueryStatementTest.java b/src/test/java/io/swagger/api/impl/DcaeServiceTypesQueryStatementTest.java
new file mode 100644
index 0000000..ff6aabf
--- /dev/null
+++ b/src/test/java/io/swagger/api/impl/DcaeServiceTypesQueryStatementTest.java
@@ -0,0 +1,234 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * dcae-inventory
+ * ================================================================================
+ * Copyright (C) 2020 Nokia Intellectual Property. All rights reserved.
+ * ================================================================================
+ * 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.
+ * ============LICENSE_END=========================================================
+ */
+package io.swagger.api.impl;
+
+import org.junit.Test;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+
+public class DcaeServiceTypesQueryStatementTest {
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypes() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create(null, true, true,
+                null, null, null, null, null, null,
+                null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastDeactivatedDcaeServiceTypes() {
+        //when
+        final String sql = DcaeServiceTypesQueryStatement.create(null, true, false,
+                null, null, null, null, null,
+                null, null, null);
+
+        //then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where created < :createdCutoff order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForActiveDcaeServiceTypes() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create(null, false, true,
+                null, null, null, null, null,
+                null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types where created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForDeactivatedDcaeServiceTypes() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create(null, false, false,
+                null, null, null, null, null,
+                null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types where created < :createdCutoff order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithAsteriskTypeName() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("*", true, true,
+                null, null, null, null, null, null,
+                null, null);
+
+        //  then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where type_name LIKE :typeName and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeName() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                null, null, null, null, null, null,
+                null, null);
+
+        // then
+        assertThat(sql).isEqualTo("select * from dcae_service_types_latest where :typeName = type_name and created < :createdCutoff and deactivated is null order by created desc");
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfType() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", null, null, null, null, null,
+                null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceId() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", null, null, null,
+                null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocation() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", null,
+                null, null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdSetToNone() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "none",
+                null, null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and asdc_service_id is null and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceId() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "asdcServiceId",
+                null, null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and :asdcServiceId = asdc_service_id and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdAndAsdcResourceIdSetToNone() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "none",
+                "none", null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and asdc_service_id is null and asdc_resource_id is null and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdAndAsdcResourceId() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "asdcServiceId",
+                "asdcResourceId", null, null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and :asdcServiceId = asdc_service_id and :asdcResourceId = asdc_resource_id and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdAndAsdcResourceIdAndOwner() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "asdcServiceId",
+                "asdcResourceId", "owner", null, null);
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and :asdcServiceId = asdc_service_id and :asdcResourceId = asdc_resource_id and :owner = owner and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdAndAsdcResourceIdAndOwnerAndApplication() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "asdcServiceId",
+                "asdcResourceId", "owner", "application", null);
+
+        // then
+        assertThat(sql).isEqualTo("select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and :asdcServiceId = asdc_service_id and :asdcResourceId = asdc_resource_id and :owner = owner and :application = application and created < :createdCutoff and deactivated is null order by created desc");
+    }
+
+    @Test
+    public void shouldReturnSqlTemplateForLastActiveDcaeServiceTypesWithTypeNameAndVnfTypeAndServiceIdAndServiceLocationAndAsdcServiceIdAndAsdcResourceIdAndOwnerAndApplicationAndComponent() {
+        // when
+        final String sql = DcaeServiceTypesQueryStatement.create("typeName", true, true,
+                "vnfType", "serviceId", "serviceLocation", "asdcServiceId",
+                "asdcResourceId", "owner", "application", "component");
+
+        // then
+        assertThat(sql).isEqualTo(
+                "select * from dcae_service_types_latest where :typeName = type_name and lower(:vnfType) = any(lower(vnf_types\\:\\:text)\\:\\:text[]) and (:serviceId = any(service_ids) or service_ids = '{}' or service_ids is null) and (:serviceLocation = any(service_locations) or service_locations = '{}' or service_locations is null) and :asdcServiceId = asdc_service_id and :asdcResourceId = asdc_resource_id and :owner = owner and :application = application and :component = component and created < :createdCutoff and deactivated is null order by created desc"
+        );
+    }
+}