From 34f89faa64f815d5cf33d0905046eaf392017b39 Mon Sep 17 00:00:00 2001 From: Michael Dürre Date: Wed, 31 Aug 2022 08:46:55 +0200 Subject: add fixes for wt sulfur MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit fix devmgrs and db access Issue-ID: CCSDK-3749 Signed-off-by: Michael Dürre Change-Id: I41018d2daa55b200a9ba89e784f8adf4200d32c3 Signed-off-by: Michael Dürre --- sdnr/wt/data-provider/dblib/pom.xml | 4 +++ .../dataprovider/database/sqldb/SqlDBClient.java | 11 ++++++ .../database/sqldb/database/SqlDBReaderWriter.java | 30 ++++++++++++++-- .../database/sqldb/database/SqlDBStatusReader.java | 6 ++++ .../sqldb/query/filters/RegexSqlDBFilter.java | 2 +- .../dblib/test/TestMariaDataProvider.java | 41 +++++++++++++++++++--- .../dataprovider/dblib/test/TestQuerySyntax.java | 14 ++++++++ .../dblib/test/util/MariaDBTestBase.java | 13 +++++++ 8 files changed, 113 insertions(+), 8 deletions(-) (limited to 'sdnr/wt/data-provider/dblib') 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 @@ provided + + com.zaxxer + HikariCP + org.mariadb.jdbc mariadb-java-client 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 { } 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 { 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 { 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 { 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 { } 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 { 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 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 @@ -224,6 +224,20 @@ public class TestQuerySyntax { assertTrue(query.toSql().contains(".*")); } + @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)); 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 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; -- cgit 1.2.3-korg