Add MS SQL driver in CDS blueprints and

DB resolution fails on an empty dataset with required template mapping.

Issue-ID: CCSDK-3293
Issue-ID: CCSDK-3294
Signed-off-by: Prudence Au <prudence.au@amdocs.com>
Change-Id: I9f302d12e7359b62180fcdfddbd8ae2c993a8324

5 files changed, 77 insertions, 1 deletions

diff --git a/ms/blueprintsprocessor/modules/commons/db-lib/pom.xml b/ms/blueprintsprocessor/modules/commons/db-lib/pom.xml
index d9c61933a..de3960c02 100644
--- a/ms/blueprintsprocessor/modules/commons/db-lib/pom.xml
+++ b/ms/blueprintsprocessor/modules/commons/db-lib/pom.xml
@@ -71,5 +71,10 @@
             <artifactId>hibernate-testing</artifactId>
             <scope>test</scope>
         </dependency>
+        <dependency>
+            <groupId>com.microsoft.sqlserver</groupId>
+            <artifactId>mssql-jdbc</artifactId>
+            <version>9.2.1.jre11</version>
+        </dependency>
     </dependencies>
 </project>
diff --git a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibConfiguration.kt b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibConfiguration.kt
index f11832beb..ff1072c94 100644
--- a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibConfiguration.kt
+++ b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibConfiguration.kt
@@ -71,11 +71,13 @@ class DBLibConstants {
         const val MYSQL_DB: String = "mysql-db"
         const val ORACLE_DB: String = "oracle-db"
         const val POSTGRES_DB: String = "postgres-db"
+        const val MSSQL_DB: String = "mssql"
 
         // List of database drivers
         const val DRIVER_MARIA_DB = "org.mariadb.jdbc.Driver"
         const val DRIVER_MYSQL_DB = "com.mysql.jdbc.Driver"
         const val DRIVER_ORACLE_DB = "oracle.jdbc.driver.OracleDriver"
         const val DRIVER_POSTGRES_DB = "org.postgresql.Driver"
+        const val DRIVER_MSSQL_DB = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
     }
 }
diff --git a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibData.kt b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibData.kt
index 7023106eb..2da83823c 100644
--- a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibData.kt
+++ b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/BlueprintDBLibData.kt
@@ -50,3 +50,11 @@ open class MySqlDataSourceProperties : DBDataSourceProperties() {
     lateinit var hibernateDialect: String
     override var driverClassName = DBLibConstants.DRIVER_MYSQL_DB
 }
+
+open class MSSqlDataSourceProperties : DBDataSourceProperties() {
+    lateinit var hibernateHbm2ddlAuto: String
+    lateinit var hibernateDDLAuto: String
+    lateinit var hibernateNamingStrategy: String
+    lateinit var hibernateDialect: String
+    override var driverClassName = DBLibConstants.DRIVER_MSSQL_DB
+}
diff --git a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/BlueprintDBLibPropertyService.kt b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/BlueprintDBLibPropertyService.kt
index b272a9708..3dbd76c13 100644
--- a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/BlueprintDBLibPropertyService.kt
+++ b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/BlueprintDBLibPropertyService.kt
@@ -22,9 +22,11 @@ import org.onap.ccsdk.cds.blueprintsprocessor.db.BlueprintDBLibGenericService
 import org.onap.ccsdk.cds.blueprintsprocessor.db.DBDataSourceProperties
 import org.onap.ccsdk.cds.blueprintsprocessor.db.DBLibConstants.Companion.MARIA_DB
 import org.onap.ccsdk.cds.blueprintsprocessor.db.DBLibConstants.Companion.MYSQL_DB
+import org.onap.ccsdk.cds.blueprintsprocessor.db.DBLibConstants.Companion.MSSQL_DB
 import org.onap.ccsdk.cds.blueprintsprocessor.db.DBLibConstants.Companion.PROCESSOR_DB
 import org.onap.ccsdk.cds.blueprintsprocessor.db.MariaDataSourceProperties
 import org.onap.ccsdk.cds.blueprintsprocessor.db.MySqlDataSourceProperties
+import org.onap.ccsdk.cds.blueprintsprocessor.db.MSSqlDataSourceProperties
 import org.onap.ccsdk.cds.controllerblueprints.core.BlueprintProcessorException
 import org.onap.ccsdk.cds.controllerblueprints.core.utils.JacksonUtils
 import org.springframework.stereotype.Service
@@ -42,9 +44,10 @@ class BlueprintDBLibPropertyService(private var bluePrintPropertiesService: Blue
         when (val type = jsonNode.get("type").textValue()) {
             MYSQL_DB -> JacksonUtils.readValue(jsonNode, MySqlDataSourceProperties::class.java)
             MARIA_DB -> JacksonUtils.readValue(jsonNode, MariaDataSourceProperties::class.java)
+            MSSQL_DB -> JacksonUtils.readValue(jsonNode, MSSqlDataSourceProperties::class.java)
             else -> {
                 throw BlueprintProcessorException(
-                    "DB type ($type) is not supported. Valid types: $MARIA_DB, $MYSQL_DB"
+                    "DB type ($type) is not supported. Valid types: $MARIA_DB, $MYSQL_DB, $MSSQL_DB"
                 )
             }
         }!!
@@ -54,6 +57,7 @@ class BlueprintDBLibPropertyService(private var bluePrintPropertiesService: Blue
             return when (it) {
                 MARIA_DB, PROCESSOR_DB -> mariaDBConnectionProperties(prefix)
                 MYSQL_DB -> mySqlDBConnectionProperties(prefix)
+                MSSQL_DB -> mssqlDBConnectionProperties(prefix)
                 else -> {
                     throw BlueprintProcessorException(
                         "DB type ($it) is not supported. Valid types: $MARIA_DB, $MYSQL_DB, $PROCESSOR_DB"
@@ -66,6 +70,7 @@ class BlueprintDBLibPropertyService(private var bluePrintPropertiesService: Blue
         when (dBConnetionProperties) {
             is MariaDataSourceProperties -> MariaDatabaseConfiguration(dBConnetionProperties)
             is MySqlDataSourceProperties -> MySqlDatabaseConfiguration(dBConnetionProperties)
+            is MSSqlDataSourceProperties -> MSSqlDatabaseConfiguration(dBConnetionProperties)
             else -> throw BlueprintProcessorException(
                 "Failed to create db configuration for ${dBConnetionProperties.url}"
             )
@@ -76,4 +81,7 @@ class BlueprintDBLibPropertyService(private var bluePrintPropertiesService: Blue
 
     private fun mariaDBConnectionProperties(prefix: String): MariaDataSourceProperties =
         bluePrintPropertiesService.propertyBeanType(prefix, MariaDataSourceProperties::class.java)
+
+    private fun mssqlDBConnectionProperties(prefix: String): MSSqlDataSourceProperties =
+        bluePrintPropertiesService.propertyBeanType(prefix, MSSqlDataSourceProperties::class.java)
 }
diff --git a/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/MSSqlDatabaseConfiguration.kt b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/MSSqlDatabaseConfiguration.kt
new file mode 100644
index 000000000..d0ff5b6f3
--- /dev/null
+++ b/ms/blueprintsprocessor/modules/commons/db-lib/src/main/kotlin/org/onap/ccsdk/cds/blueprintsprocessor/db/primary/MSSqlDatabaseConfiguration.kt
@@ -0,0 +1,53 @@
+/*
+ * Copyright © 2021 Bell Canada Intellectual Property.
+ *
+ * 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.
+ */
+
+package org.onap.ccsdk.cds.blueprintsprocessor.db.primary
+
+import org.onap.ccsdk.cds.blueprintsprocessor.db.BlueprintDBLibGenericService
+import org.onap.ccsdk.cds.blueprintsprocessor.db.MSSqlDataSourceProperties
+import org.slf4j.LoggerFactory
+import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate
+import org.springframework.jdbc.datasource.DriverManagerDataSource
+import javax.sql.DataSource
+
+class MSSqlDatabaseConfiguration(private val msSqlDataSourceProperties: MSSqlDataSourceProperties) : BlueprintDBLibGenericService {
+    override fun namedParameterJdbcTemplate(): NamedParameterJdbcTemplate {
+        return msSqlNamedParameterJdbcTemplate(msSqlDataSource())
+    }
+
+    override fun query(sql: String, params: Map<String, Any>): List<Map<String, Any>> {
+        return msSqlNamedParameterJdbcTemplate(msSqlDataSource()).queryForList(sql, params)
+    }
+
+    override fun update(sql: String, params: Map<String, Any>): Int {
+        return msSqlNamedParameterJdbcTemplate(msSqlDataSource()).update(sql, params)
+    }
+
+    val log = LoggerFactory.getLogger(PrimaryDatabaseConfiguration::class.java)!!
+
+    fun msSqlDataSource(): DataSource {
+        val dataSource = DriverManagerDataSource()
+        dataSource.setDriverClassName(msSqlDataSourceProperties.driverClassName)
+        dataSource.url = msSqlDataSourceProperties.url
+        dataSource.username = msSqlDataSourceProperties.username
+        dataSource.password = msSqlDataSourceProperties.password
+        return dataSource
+    }
+
+    fun msSqlNamedParameterJdbcTemplate(msSqlDataSource: DataSource): NamedParameterJdbcTemplate {
+        return NamedParameterJdbcTemplate(msSqlDataSource)
+    }
+}