add fixes for wt sulfur

fix devmgrs and db access

Issue-ID: CCSDK-3749
Signed-off-by: Michael Dürre <michael.duerre@highstreet-technologies.com>
Change-Id: I41018d2daa55b200a9ba89e784f8adf4200d32c3
Signed-off-by: Michael Dürre <michael.duerre@highstreet-technologies.com>

8 files changed, 113 insertions, 8 deletions

diff --git a/sdnr/wt/data-provider/dblib/pom.xml b/sdnr/wt/data-provider/dblib/pom.xml
index 5c7294c50..9ea692c27 100644
--- a/sdnr/wt/data-provider/dblib/pom.xml
+++ b/sdnr/wt/data-provider/dblib/pom.xml
@@ -102,6 +102,10 @@
             <scope>provided</scope>
         </dependency>
         <!--  MariaDB start -->
+        <dependency>
+            <groupId>com.zaxxer</groupId>
+            <artifactId>HikariCP</artifactId>
+        </dependency>
         <dependency> <!--  Not part of ODL karaf standard delivery -->
             <groupId>org.mariadb.jdbc</groupId>
             <artifactId>mariadb-java-client</artifactId>
diff --git a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/SqlDBClient.java b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/SqlDBClient.java
index 1ed0b5f17..b93145cbb 100644
--- a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/SqlDBClient.java
+++ b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/SqlDBClient.java
@@ -30,6 +30,9 @@ import java.sql.Statement;
 import java.text.ParseException;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
+
+import com.zaxxer.hikari.HikariDataSource;
+import org.mariadb.jdbc.MariaDbPoolDataSource;
 import org.onap.ccsdk.features.sdnr.wt.common.database.Portstatus;
 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntry;
 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntryList;
@@ -42,6 +45,8 @@ import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.pro
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
+import javax.sql.ConnectionPoolDataSource;
+
 public class SqlDBClient {
 
     private static final Logger LOG = LoggerFactory.getLogger(SqlDBClient.class);
@@ -58,11 +63,13 @@ public class SqlDBClient {
     private static final String SELECT_VERSION_QUERY = "SELECT @@version as version";
 
     private static final String DBNAME_DEFAULT = "sdnrdb";
+    private static final int DEFAULT_POOLSIZE = 50;
     private final String dbConnectionString;
     private final String dbName;
     private final String dbHost;
     private final int dbPort;
 
+    private final HikariDataSource connectionPool;
     /**
      *
      * @param dbUrl e.g. jdbc:mysql://sdnrdb:3306/sdnrdb
@@ -78,6 +85,10 @@ public class SqlDBClient {
         this.dbHost = matcher.group(2);
         this.dbPort = Integer.parseInt(matcher.group(3));
         this.dbName = matcher.group(4);
+        this.connectionPool = new HikariDataSource();
+        this.connectionPool.setJdbcUrl(this.dbConnectionString);
+        this.connectionPool.setUsername(username);
+        this.connectionPool.setPassword(password);
     }
 
     public AliasesEntryList readViews() {
diff --git a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBReaderWriter.java b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBReaderWriter.java
index 56e965c9f..56a986e55 100644
--- a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBReaderWriter.java
+++ b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBReaderWriter.java
@@ -185,8 +185,9 @@ public class SqlDBReaderWriter<T extends DataObject> {
         }
         String insertedId = null;
         PreparedStatement stmt = null;
+        Connection connection = null;
         try {
-            Connection connection = this.dbService.getConnection();
+            connection = this.dbService.getConnection();
             stmt = connection.prepareStatement(query.toSql());
             stmt.execute();
 
@@ -208,6 +209,13 @@ public class SqlDBReaderWriter<T extends DataObject> {
                     LOG.warn("problem closing sql statement: ", e);
                 }
             }
+            if (connection != null) {
+                try {
+                    connection.close();
+                } catch (SQLException e) {
+                    LOG.warn("problem closing sql connection: ", e);
+                }
+            }
         }
 
         return insertedId;
@@ -221,8 +229,9 @@ public class SqlDBReaderWriter<T extends DataObject> {
             LOG.trace("query={}", query.toSql());
         }
         PreparedStatement stmt = null;
+        Connection connection = null;
         try {
-            Connection connection = this.dbService.getConnection();
+            connection = this.dbService.getConnection();
             stmt = connection.prepareStatement(query.toSql());
             stmt.execute();
 
@@ -241,6 +250,13 @@ public class SqlDBReaderWriter<T extends DataObject> {
                     LOG.warn("problem closing sql statement: ", e);
                 }
             }
+            if (connection != null) {
+                try {
+                    connection.close();
+                } catch (SQLException e) {
+                    LOG.warn("problem closing sql connection: ", e);
+                }
+            }
         }
         return insertedId;
     }
@@ -262,8 +278,9 @@ public class SqlDBReaderWriter<T extends DataObject> {
         }
         int affectedRows = 0;
         PreparedStatement stmt = null;
+        Connection connection = null;
         try {
-            Connection connection = this.dbService.getConnection();
+            connection = this.dbService.getConnection();
             stmt = connection.prepareStatement(query.toSql());
             stmt.execute();
             affectedRows = stmt.getUpdateCount();
@@ -278,6 +295,13 @@ public class SqlDBReaderWriter<T extends DataObject> {
                     LOG.warn("problem closing sql statement: ", e);
                 }
             }
+            if (connection != null) {
+                try {
+                    connection.close();
+                } catch (SQLException e) {
+                    LOG.warn("problem closing sql connection: ", e);
+                }
+            }
         }
         return affectedRows;
     }
diff --git a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBStatusReader.java b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBStatusReader.java
index 75d8c9ad2..d98d36d7c 100644
--- a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBStatusReader.java
+++ b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/database/SqlDBStatusReader.java
@@ -61,6 +61,9 @@ public class SqlDBStatusReader {
         long minorCount = 0;
         long warningCount = 0;
         ResultSet data;
+        if(LOG.isTraceEnabled()){
+            LOG.trace("query={}", selectQuery);
+        }
         try {
             data = this.dbClient.read(selectQuery);
             String severity;
@@ -92,6 +95,9 @@ public class SqlDBStatusReader {
         long connectedCount = 0, connectingCount = 0, disconnectedCount = 0, mountedCount = 0, unableToConnectCount = 0,
                 undefinedCount = 0, unmountedCount = 0;
         long cnt;
+        if(LOG.isTraceEnabled()){
+            LOG.trace("query={}", selectQuery);
+        }
         try {
             data = this.dbClient.read(selectQuery);
             while (data.next()) {
diff --git a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/query/filters/RegexSqlDBFilter.java b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/query/filters/RegexSqlDBFilter.java
index 65e0c54e4..bc888df50 100644
--- a/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/query/filters/RegexSqlDBFilter.java
+++ b/sdnr/wt/data-provider/dblib/src/main/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/database/sqldb/query/filters/RegexSqlDBFilter.java
@@ -30,7 +30,7 @@ public class RegexSqlDBFilter extends DBKeyValuePair<String> implements SqlDBFil
 
     @Override
     public String getFilterExpression() {
-        return String.format("`%s` RLIKE '%s'", this.getKey(), this.getValue());
+        return String.format("`%s` RLIKE '^%s$'", this.getKey(), this.getValue());
     }
 
     @Override
diff --git a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestMariaDataProvider.java b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestMariaDataProvider.java
index 34c4d6fe8..33703042e 100644
--- a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestMariaDataProvider.java
+++ b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestMariaDataProvider.java
@@ -125,6 +125,7 @@ public class TestMariaDataProvider {
 
     private static final String NODEID1 = "node1";
     private static final String NODEID2 = "node2";
+    private static final String NODEID22 = "node22";
     private static final String NODEID3 = "node3";
     private static final String NODEID4 = "node4";
     private static final String NODEID5 = "node5";
@@ -140,6 +141,7 @@ public class TestMariaDataProvider {
     private static final String USERNAME = "admin";
     private static MariaDBTestBase testBase;
     private static SqlDBDataProvider dbProvider;
+    private static SqlDBDataProvider dbProviderOverall;
     private static SqlDBClient dbClient;
     private static String CONTROLLERID;
 
@@ -153,6 +155,7 @@ public class TestMariaDataProvider {
         MariaDBTestBase.testCreateTableStructure(dbClient);
         dbProvider.setControllerId();
         CONTROLLERID = dbProvider.getControllerId();
+        dbProviderOverall = testBase.getOverallDbProvider();
 
     }
 
@@ -533,19 +536,23 @@ public class TestMariaDataProvider {
         NetworkElementConnectionEntity ne2 = new NetworkElementConnectionBuilder().setNodeId(NODEID2)
                 .setHost("10.20.30.55").setPort(Uint32.valueOf(8300)).setIsRequired(false).setUsername("user")
                 .setPassword("passwd").setStatus(ConnectionLogStatus.Connecting).build();
+        NetworkElementConnectionEntity ne22 = new NetworkElementConnectionBuilder().setNodeId(NODEID22)
+                .setHost("10.20.30.55").setPort(Uint32.valueOf(8300)).setIsRequired(false).setUsername("user")
+                .setPassword("passwd").setStatus(ConnectionLogStatus.Connected).build();
         NetworkElementConnectionEntity ne3 = new NetworkElementConnectionBuilder().setNodeId(NODEID3)
                 .setHost("10.20.30.55").setPort(Uint32.valueOf(8300)).setIsRequired(false).setUsername("user")
                 .setPassword("passwd").setStatus(ConnectionLogStatus.Connecting).build();
         try {
             dbProvider.createNetworkElementConnection(ne1);
             dbProvider.createNetworkElementConnection(ne2);
+            dbProvider.createNetworkElementConnection(ne22);
             dbProvider.updateNetworkConnection22(ne3, NODEID3);
         } catch (IOException e) {
             e.printStackTrace();
             fail("problem creating neconnection");
         }
         data = dbProvider.readNetworkElementConnectionList(createInput(1, 20));
-        assertEquals(3, data.getData().size());
+        assertEquals(4, data.getData().size());
         NetworkElementConnectionEntity update1 = new NetworkElementConnectionBuilder()
                 .setStatus(ConnectionLogStatus.Connected).setDeviceType(NetworkElementDeviceType.ORAN).build();
         dbProvider.updateNetworkConnectionDeviceType(update1, NODEID1);
@@ -574,15 +581,41 @@ public class TestMariaDataProvider {
             e.printStackTrace();
             fail("failed to read status");
         }
-        assertEquals(1, status.getData().get(0).getNetworkElementConnections().getConnected().intValue());
+        assertEquals(2, status.getData().get(0).getNetworkElementConnections().getConnected().intValue());
         assertEquals(2, status.getData().get(0).getNetworkElementConnections().getConnecting().intValue());
         assertEquals(0, status.getData().get(0).getNetworkElementConnections().getDisconnected().intValue());
         assertEquals(0, status.getData().get(0).getNetworkElementConnections().getMounted().intValue());
-        assertEquals(3, status.getData().get(0).getNetworkElementConnections().getTotal().intValue());
+        assertEquals(4, status.getData().get(0).getNetworkElementConnections().getTotal().intValue());
         assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUnableToConnect().intValue());
         assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUndefined().intValue());
         assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUnmounted().intValue());
 
+        ReadStatusOutputBuilder status2=null;
+        try {
+            EntityInput input=createInput("node-id","node2*", 1, 20);
+            status = dbProvider.readStatus(input);
+            status2 = dbProviderOverall.readStatus(input);
+        } catch (IOException e) {
+            e.printStackTrace();
+            fail("failed to read status");
+        }
+        assertEquals(1, status.getData().get(0).getNetworkElementConnections().getConnected().intValue());
+        assertEquals(1, status.getData().get(0).getNetworkElementConnections().getConnecting().intValue());
+        assertEquals(0, status.getData().get(0).getNetworkElementConnections().getDisconnected().intValue());
+        assertEquals(0, status.getData().get(0).getNetworkElementConnections().getMounted().intValue());
+        assertEquals(2, status.getData().get(0).getNetworkElementConnections().getTotal().intValue());
+        assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUnableToConnect().intValue());
+        assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUndefined().intValue());
+        assertEquals(0, status.getData().get(0).getNetworkElementConnections().getUnmounted().intValue());
+        
+        assertEquals(1, status2.getData().get(0).getNetworkElementConnections().getConnected().intValue());
+        assertEquals(1, status2.getData().get(0).getNetworkElementConnections().getConnecting().intValue());
+        assertEquals(0, status2.getData().get(0).getNetworkElementConnections().getDisconnected().intValue());
+        assertEquals(0, status2.getData().get(0).getNetworkElementConnections().getMounted().intValue());
+        assertEquals(2, status2.getData().get(0).getNetworkElementConnections().getTotal().intValue());
+        assertEquals(0, status2.getData().get(0).getNetworkElementConnections().getUnableToConnect().intValue());
+        assertEquals(0, status2.getData().get(0).getNetworkElementConnections().getUndefined().intValue());
+        assertEquals(0, status2.getData().get(0).getNetworkElementConnections().getUnmounted().intValue());
 
         DeleteNetworkElementConnectionInput delete1 =
                 new DeleteNetworkElementConnectionInputBuilder().setId(NODEID1).build();
@@ -595,7 +628,7 @@ public class TestMariaDataProvider {
         data = dbProvider.readNetworkElementConnectionList(createInput("node-id", NODEID1, 1, 20));
         assertEquals(0, data.getData().size());
         data = dbProvider.readNetworkElementConnectionList(createInput(1, 20));
-        assertEquals(2, data.getData().size());
+        assertEquals(3, data.getData().size());
 
     }
 
diff --git a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestQuerySyntax.java b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestQuerySyntax.java
index 7dd2464f9..30c68a01d 100644
--- a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestQuerySyntax.java
+++ b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/TestQuerySyntax.java
@@ -225,6 +225,20 @@ public class TestQuerySyntax {
 
     }
     @Test
+    public void testSelectForFilterValues6() {
+        EntityInput input = new ReadGuiCutThroughEntryInputBuilder()
+                .setFilter(YangToolsMapperHelper.toMap(Arrays.asList(
+                        new FilterBuilder().setProperty("id").setFiltervalue("*ran*").build())))
+                .setPagination(new PaginationBuilder().setSize(Uint32.valueOf(20)).setPage(Uint64.valueOf(1)).build())
+                .build();
+        SelectQuery query = new SelectQuery(TABLENAME1, input, CONTROLLERID);
+        System.out.println(query.toSql());
+        assertTrue(query.toSql().contains("RLIKE"));
+        assertTrue(query.toSql().contains(".*"));
+
+    }
+
+    @Test
     public void testCount() {
         CountQuery query = new CountQuery(Entity.Eventlog, TestMariaDataProvider.createInput(1, 20));
         String sQuery = query.toSql();
diff --git a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/util/MariaDBTestBase.java b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/util/MariaDBTestBase.java
index aebd3328e..3123d25a6 100644
--- a/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/util/MariaDBTestBase.java
+++ b/sdnr/wt/data-provider/dblib/src/test/java/org/onap/ccsdk/features/sdnr/wt/dataprovider/dblib/test/util/MariaDBTestBase.java
@@ -57,6 +57,7 @@ import ch.vorburger.mariadb4j.DBConfigurationBuilder;
 public class MariaDBTestBase {
 
     private final SqlDBDataProvider dbProvider;
+    private final SqlDBDataProvider dbProviderOverall;
     private final DB db;
     private SqlDBConfig config;
     private static final Map<String, String> envDefaultValues = initEnvDefaultValues();
@@ -96,6 +97,11 @@ public class MariaDBTestBase {
         this.config.setControllerId("test123");
         this.db = null;
         this.dbProvider = new SqlDBDataProvider(config, false);
+
+        SqlDBConfig config2 = new SqlDBConfig(new ConfigurationFileRepresentation("test2.properties"));
+        config2.setDbSuffix("");
+        config2.setControllerId(null);
+        this.dbProviderOverall = new SqlDBDataProvider(config2, false);
     }
 
     public MariaDBTestBase(int port) throws ManagedProcessException {
@@ -111,6 +117,10 @@ public class MariaDBTestBase {
         this.db = startDatabase(port);
         //create db with name sdnrdb
         this.dbProvider = new SqlDBDataProvider(config, false);
+        SqlDBConfig config2 = new SqlDBConfig(new ConfigurationFileRepresentation("test2.properties"));
+        config2.setDbSuffix("");
+        config2.setControllerId(null);
+        this.dbProviderOverall = new SqlDBDataProvider(config2, false);
     }
 
     public void close() throws ManagedProcessException {
@@ -126,6 +136,9 @@ public class MariaDBTestBase {
     public SqlDBDataProvider getDbProvider() {
         return dbProvider;
     }
+    public SqlDBDataProvider getOverallDbProvider() {
+        return dbProviderOverall;
+    }
 
     public DB getDb() {
         return db;