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;
33 import org.onap.ccsdk.features.sdnr.wt.common.database.Portstatus;
34 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntry;
35 import org.onap.ccsdk.features.sdnr.wt.common.database.data.AliasesEntryList;
36 import org.onap.ccsdk.features.sdnr.wt.common.database.data.DatabaseVersion;
37 import org.onap.ccsdk.features.sdnr.wt.common.database.data.IndicesEntryList;
38 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.data.SqlDBIndicesEntry;
39 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.database.SqlDBMapper;
40 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.database.SqlDBMapper.UnableToMapClassException;
41 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.Entity;
42 import org.slf4j.Logger;
43 import org.slf4j.LoggerFactory;
45 public class SqlDBClient {
47 private static final Logger LOG = LoggerFactory.getLogger(SqlDBClient.class);
50 // 1=>type, e.g. mariadb, mysql, ...
54 private static final String DBURL_REGEX = "^jdbc:([^:]+):\\/\\/([^:]+):([0-9]+)\\/(.+)$";
55 private static final Pattern DBURL_PATTERN = Pattern.compile(DBURL_REGEX);
56 private static final String DBVERSION_REGEX = "^([\\d]+\\.[\\d]+\\.[\\d]+)";
57 private static final Pattern DBVERSION_PATTERN = Pattern.compile(DBVERSION_REGEX);
58 private static final String SELECT_VERSION_QUERY = "SELECT @@version as version";
60 private static final String DBNAME_DEFAULT = "sdnrdb";
61 private final String dbConnectionString;
62 private final String dbName;
63 private final String dbHost;
64 private final int dbPort;
68 * @param dbUrl e.g. jdbc:mysql://sdnrdb:3306/sdnrdb
72 public SqlDBClient(String dbUrl, String username, String password) throws IllegalArgumentException {
73 this.dbConnectionString = String.format("%s?user=%s&password=%s", dbUrl, username, password);
74 final Matcher matcher = DBURL_PATTERN.matcher(dbUrl);
75 if (!matcher.find()) {
76 throw new IllegalArgumentException("unable to parse databaseUrl " + dbUrl);
78 this.dbHost = matcher.group(2);
79 this.dbPort = Integer.parseInt(matcher.group(3));
80 this.dbName = matcher.group(4);
83 public AliasesEntryList readViews() {
84 return this.readViews(DBNAME_DEFAULT);
87 public AliasesEntryList readViews(String dbName) {
88 AliasesEntryList list = new AliasesEntryList();
89 final String query = "SELECT v.`TABLE_NAME` AS vn, t.`TABLE_NAME` AS tn\n"
90 + "FROM `information_schema`.`TABLES` AS v\n"
91 + "LEFT JOIN `information_schema`.`TABLES` AS t ON t.`TABLE_NAME` LIKE CONCAT(v.`TABLE_NAME`,'%')"
92 + " AND t.`TABLE_TYPE`='BASE TABLE'\n" + "WHERE v.`TABLE_SCHEMA`='" + dbName
93 + "' AND v.`TABLE_TYPE`='VIEW'";
94 ResultSet data = this.read(query);
97 list.add(new AliasesEntry(data.getString(2), data.getString(1)));
99 } catch (SQLException e) {
100 LOG.warn("problem reading views: ", e);
102 try { data.close(); } catch (SQLException ignore) { }
106 public IndicesEntryList readTables() {
107 final String query = "SHOW FULL TABLES WHERE `Table_type` = 'BASE TABLE'";
108 IndicesEntryList list = new IndicesEntryList();
109 ResultSet data = this.read(query);
111 while (data.next()) {
112 list.add(new SqlDBIndicesEntry(data.getString(1)));
114 } catch (SQLException e) {
115 LOG.warn("problem reading tables: ", e);
117 try { data.close(); } catch (SQLException ignore) { }
121 public void waitForYellowStatus(long timeoutms) {
122 Portstatus.waitSecondsTillAvailable(timeoutms / 1000, this.dbHost, this.dbPort);
125 public DatabaseVersion readActualVersion() throws SQLException, ParseException {
128 data = this.read(SELECT_VERSION_QUERY);
130 final String s = data.getString(1);
131 final Matcher matcher = DBVERSION_PATTERN.matcher(s);
134 if (matcher.find()) {
135 return new DatabaseVersion(matcher.group(1));
137 throw new ParseException(String.format("unable to extract version out of string '%s'", s), 0);
140 } catch (SQLException e) {
141 LOG.warn("problem reading actual version: ", e);
143 throw new SQLException("unable to read version from database");
146 public boolean createTable(Entity entity, Class<?> clazz, String suffix) throws UnableToMapClassException {
147 String createStatement = SqlDBMapper.createTable(clazz, entity, suffix);
148 return this.createTable(createStatement);
151 public boolean createTable(String tableName, String tableMappings) {
152 final String createStatement = String.format("CREATE TABLE IF NOT EXISTS `%s` (%s)", tableName, tableMappings);
153 return this.createTable(createStatement);
156 public boolean createTable(String query) {
157 boolean result = false;
158 PreparedStatement stmt = null;
159 Connection connection = null;
161 connection = this.getConnection();
162 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
166 } catch (SQLException e) {
167 LOG.warn("problem creating table:", e);
169 if (stmt != null) try { stmt.close(); } catch (SQLException logOrIgnore) {}
170 if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
175 public boolean createView(String tableName, String viewName) throws SQLException {
177 this.write(String.format("CREATE VIEW IF NOT EXISTS `%s` AS SELECT * FROM `%s`", viewName, tableName));
179 } catch (SQLException e) {
180 LOG.warn("problem deleting table:", e);
185 public boolean deleteView(String viewName) throws SQLException {
187 this.write(String.format("DROP VIEW IF EXISTS `%s`", viewName));
189 } catch (SQLException e) {
190 LOG.warn("problem deleting view:", e);
195 public boolean update(String query) throws SQLException {
196 boolean result = false;
197 SQLException innerE = null;
198 Statement stmt = null;
199 Connection connection = null;
201 connection= this.getConnection();
202 stmt = connection.createStatement();
203 result = stmt.execute(query);
204 result = stmt.getUpdateCount() > 0 ? stmt.getUpdateCount() > 0 : result;
205 } catch (SQLException e) {
208 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
209 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
211 if (innerE != null) {
217 public boolean write(String query) throws SQLException {
218 boolean result = false;
219 SQLException innerE = null;
220 PreparedStatement stmt = null;
221 Connection connection = null;
223 connection = this.getConnection();
224 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
225 result = stmt.execute();
226 result = stmt.getUpdateCount() > 0 ? stmt.getUpdateCount() > 0 : result;
227 } catch (SQLException e) {
230 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
231 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
233 if (innerE != null) {
239 public String writeAndReturnId(String query) throws SQLException {
240 String result = null;
241 SQLException innerE = null;
242 PreparedStatement stmt = null;
243 ResultSet generatedKeys = null;
244 Connection connection = null;
246 connection = this.getConnection();
247 stmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
249 generatedKeys = stmt.getGeneratedKeys();
250 if (generatedKeys.next()) {
251 result = String.valueOf(generatedKeys.getLong(1));
253 } catch (SQLException e) {
256 if (generatedKeys != null) try { generatedKeys.close(); } catch (SQLException ignore) {}
257 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
258 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
260 if (innerE != null) {
266 public boolean deleteTable(String tableName) throws SQLException {
268 this.write(String.format("DROP TABLE IF EXISTS `%s`", tableName));
270 } catch (SQLException e) {
271 LOG.warn("problem deleting table:", e);
276 public String getDatabaseName() {
280 public ResultSet read(String query) {
281 ResultSet data = null;
282 Statement stmt = null;
283 Connection connection = null;
285 connection = this.getConnection();
286 stmt = connection.createStatement();
287 data = stmt.executeQuery(query);
288 } catch (SQLException e) {
289 LOG.warn("problem reading db for query '{}': ", query, e);
291 if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
292 if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
297 public Connection getConnection() throws SQLException {
298 return DriverManager.getConnection(this.dbConnectionString);
301 public boolean delete(String query) throws SQLException {