2 * ============LICENSE_START=======================================================
3 * ONAP : ccsdk features
4 * ================================================================================
5 * Copyright (C) 2020 highstreet technologies GmbH Intellectual Property.
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END=========================================================
22 package org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb;
24 import java.sql.Connection;
25 import java.sql.DriverManager;
26 import java.sql.PreparedStatement;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.sql.Statement;
30 import java.text.ParseException;
31 import java.util.regex.Matcher;
32 import java.util.regex.Pattern;
34 import com.zaxxer.hikari.HikariDataSource;
35 import org.mariadb.jdbc.MariaDbPoolDataSource;
36 import org.onap.ccsdk.features.sdnr.wt.common.database.Portstatus;
37 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntry;
38 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntryList;
39 import org.onap.ccsdk.features.sdnr.wt.common.database.data.DatabaseVersion;
40 import org.onap.ccsdk.features.sdnr.wt.common.database.data.IndicesEntryList;
41 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.data.SqlDBIndicesEntry;
42 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.database.SqlDBMapper;
43 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.database.SqlDBMapper.UnableToMapClassException;
44 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.Entity;
45 import org.slf4j.Logger;
46 import org.slf4j.LoggerFactory;
48 import javax.sql.ConnectionPoolDataSource;
50 public class SqlDBClient {
52 private static final Logger LOG = LoggerFactory.getLogger(SqlDBClient.class);
55 // 1=>type, e.g. mariadb, mysql, ...
59 private static final String DBURL_REGEX = "^jdbc:([^:]+):\\/\\/([^:]+):([0-9]+)\\/(.+)$";
60 private static final Pattern DBURL_PATTERN = Pattern.compile(DBURL_REGEX);
61 private static final String DBVERSION_REGEX = "^([\\d]+\\.[\\d]+\\.[\\d]+)";
62 private static final Pattern DBVERSION_PATTERN = Pattern.compile(DBVERSION_REGEX);
63 private static final String SELECT_VERSION_QUERY = "SELECT @@version as version";
65 private static final String DBNAME_DEFAULT = "sdnrdb";
66 private static final int DEFAULT_POOLSIZE = 50;
67 private final String dbConnectionString;
68 private final String dbName;
69 private final String dbHost;
70 private final int dbPort;
72 private final HikariDataSource connectionPool;
75 * @param dbUrl e.g. jdbc:mysql://sdnrdb:3306/sdnrdb
79 public SqlDBClient(String dbUrl, String username, String password) throws IllegalArgumentException {
80 this.dbConnectionString = String.format("%s?user=%s&password=%s", dbUrl, username, password);
81 final Matcher matcher = DBURL_PATTERN.matcher(dbUrl);
82 if (!matcher.find()) {
83 throw new IllegalArgumentException("unable to parse databaseUrl " + dbUrl);
85 this.dbHost = matcher.group(2);
86 this.dbPort = Integer.parseInt(matcher.group(3));
87 this.dbName = matcher.group(4);
88 this.connectionPool = new HikariDataSource();
89 this.connectionPool.setJdbcUrl(this.dbConnectionString);
90 this.connectionPool.setUsername(username);
91 this.connectionPool.setPassword(password);
94 public AliasesEntryList readViews() {
95 return this.readViews(DBNAME_DEFAULT);
98 public AliasesEntryList readViews(String dbName) {
99 AliasesEntryList list = new AliasesEntryList();
100 final String query = "SELECT v.`TABLE_NAME` AS vn, t.`TABLE_NAME` AS tn\n"
101 + "FROM `information_schema`.`TABLES` AS v\n"
102 + "LEFT JOIN `information_schema`.`TABLES` AS t ON t.`TABLE_NAME` LIKE CONCAT(v.`TABLE_NAME`,'%')"
103 + " AND t.`TABLE_TYPE`='BASE TABLE'\n" + "WHERE v.`TABLE_SCHEMA`='" + dbName
104 + "' AND v.`TABLE_TYPE`='VIEW'";
105 ResultSet data = this.read(query);
107 while (data.next()) {
108 list.add(new AliasesEntry(data.getString(2), data.getString(1)));
110 } catch (SQLException e) {
111 LOG.warn("problem reading views: ", e);
113 try { data.close(); } catch (SQLException ignore) { }
117 public IndicesEntryList readTables() {
118 final String query = "SHOW FULL TABLES WHERE `Table_type` = 'BASE TABLE'";
119 IndicesEntryList list = new IndicesEntryList();
120 ResultSet data = this.read(query);
122 while (data.next()) {
123 list.add(new SqlDBIndicesEntry(data.getString(1)));
125 } catch (SQLException e) {
126 LOG.warn("problem reading tables: ", e);
128 try { data.close(); } catch (SQLException ignore) { }
132 public void waitForYellowStatus(long timeoutms) {
133 Portstatus.waitSecondsTillAvailable(timeoutms / 1000, this.dbHost, this.dbPort);
136 public DatabaseVersion readActualVersion() throws SQLException, ParseException {
139 data = this.read(SELECT_VERSION_QUERY);
141 final String s = data.getString(1);
142 final Matcher matcher = DBVERSION_PATTERN.matcher(s);
145 if (matcher.find()) {
146 return new DatabaseVersion(matcher.group(1));
148 throw new ParseException(String.format("unable to extract version out of string '%s'", s), 0);
151 } catch (SQLException e) {
152 LOG.warn("problem reading actual version: ", e);
154 throw new SQLException("unable to read version from database");
157 public boolean createTable(Entity entity, Class<?> clazz, String suffix) throws UnableToMapClassException {
158 String createStatement = SqlDBMapper.createTable(clazz, entity, suffix);
159 return this.createTable(createStatement);
162 public boolean createTable(String tableName, String tableMappings) {
163 final String createStatement = String.format("CREATE TABLE IF NOT EXISTS `%s` (%s)", tableName, tableMappings);
164 return this.createTable(createStatement);
167 public boolean createTable(String query) {
168 boolean result = false;
169 PreparedStatement stmt = null;
170 Connection connection = null;
172 connection = this.getConnection();
173 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
177 } catch (SQLException e) {
178 LOG.warn("problem creating table:", e);
180 if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
181 if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
186 public boolean createView(String tableName, String viewName) throws SQLException {
188 this.write(String.format("CREATE VIEW IF NOT EXISTS `%s` AS SELECT * FROM `%s`", viewName, tableName));
190 } catch (SQLException e) {
191 LOG.warn("problem deleting table:", e);
196 public boolean deleteView(String viewName) throws SQLException {
198 this.write(String.format("DROP VIEW IF EXISTS `%s`", viewName));
200 } catch (SQLException e) {
201 LOG.warn("problem deleting view:", e);
206 public boolean update(String query) throws SQLException {
207 boolean result = false;
208 SQLException innerE = null;
209 Statement stmt = null;
210 Connection connection = null;
212 connection= this.getConnection();
213 stmt = connection.createStatement();
214 result = stmt.execute(query);
215 result = stmt.getUpdateCount() > 0 ? stmt.getUpdateCount() > 0 : result;
216 } catch (SQLException e) {
219 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
220 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
222 if (innerE != null) {
228 public boolean write(String query) throws SQLException {
229 boolean result = false;
230 SQLException innerE = null;
231 PreparedStatement stmt = null;
232 Connection connection = null;
234 connection = this.getConnection();
235 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
236 result = stmt.execute();
237 result = stmt.getUpdateCount() > 0 ? stmt.getUpdateCount() > 0 : result;
238 } catch (SQLException e) {
241 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
242 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
244 if (innerE != null) {
250 public String writeAndReturnId(String query) throws SQLException {
251 String result = null;
252 SQLException innerE = null;
253 PreparedStatement stmt = null;
254 ResultSet generatedKeys = null;
255 Connection connection = null;
257 connection = this.getConnection();
258 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
260 generatedKeys = stmt.getGeneratedKeys();
261 if (generatedKeys.next()) {
262 result = String.valueOf(generatedKeys.getLong(1));
264 } catch (SQLException e) {
267 if (generatedKeys != null) try { generatedKeys.close(); } catch (SQLException ignore) {}
268 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
269 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
271 if (innerE != null) {
277 public boolean deleteTable(String tableName) throws SQLException {
279 this.write(String.format("DROP TABLE IF EXISTS `%s`", tableName));
281 } catch (SQLException e) {
282 LOG.warn("problem deleting table:", e);
287 public String getDatabaseName() {
291 public ResultSet read(String query) {
292 ResultSet data = null;
293 Statement stmt = null;
294 Connection connection = null;
296 connection = this.getConnection();
297 stmt = connection.createStatement();
298 data = stmt.executeQuery(query);
299 } catch (SQLException e) {
300 LOG.warn("problem reading db for query '{}': ", query, e);
302 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
303 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
308 public Connection getConnection() throws SQLException {
309 return DriverManager.getConnection(this.dbConnectionString);
312 public boolean delete(String query) throws SQLException {