2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017 AT&T Intellectual Property. All rights
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.sli.core.sli;
24 import java.io.ByteArrayInputStream;
25 import java.io.ByteArrayOutputStream;
26 import java.io.ObjectInputStream;
27 import java.io.ObjectOutputStream;
29 import java.sql.Connection;
30 import java.sql.DatabaseMetaData;
31 import java.sql.Driver;
32 import java.sql.DriverManager;
33 import java.sql.PreparedStatement;
34 import java.sql.ResultSet;
35 import java.sql.SQLException;
36 import java.sql.Statement;
37 import java.util.Properties;
38 import org.slf4j.Logger;
39 import org.slf4j.LoggerFactory;
41 public class SvcLogicJdbcStore implements SvcLogicStore {
42 private static final Logger LOG = LoggerFactory.getLogger(SvcLogicJdbcStore.class);
44 private String dbUrl = null;
45 private String dbName = null;
46 private String dbUser = null;
47 private String dbPasswd = null;
48 private String dbDriver = null;
50 private Connection dbConn;
51 private PreparedStatement hasActiveGraphStmt = null;
52 private PreparedStatement hasVersionGraphStmt = null;
53 private PreparedStatement fetchActiveGraphStmt = null;
54 private PreparedStatement fetchVersionGraphStmt = null;
55 private PreparedStatement storeGraphStmt = null;
56 private PreparedStatement deleteGraphStmt = null;
58 private PreparedStatement deactivateStmt = null;
59 private PreparedStatement activateStmt = null;
61 private void getConnection() throws ConfigurationException {
63 Properties jdbcProps = new Properties();
65 jdbcProps.setProperty("user", dbUser);
66 jdbcProps.setProperty("password", dbPasswd);
69 Driver dvr = new org.mariadb.jdbc.Driver();
70 if (dvr.acceptsURL(dbUrl)) {
71 LOG.debug("Driver com.mysql.jdbc.Driver accepts {}", dbUrl);
73 LOG.warn("Driver com.mysql.jdbc.Driver does not accept {}", dbUrl);
75 } catch (SQLException e1) {
76 LOG.error("Caught exception trying to load com.mysql.jdbc.Driver", e1);
80 this.dbConn = DriverManager.getConnection(dbUrl, jdbcProps);
81 } catch (Exception e) {
82 throw new ConfigurationException("failed to get database connection [" + dbUrl + "]", e);
87 private void createTable() throws ConfigurationException {
92 dbm = dbConn.getMetaData();
93 } catch (SQLException e) {
95 throw new ConfigurationException("could not get databse metadata", e);
98 // See if table SVC_LOGIC exists. If not, create it.
99 Statement stmt = null;
102 ResultSet tables = dbm.getTables(null, null, "SVC_LOGIC", null);
104 LOG.debug("SVC_LOGIC table already exists");
106 String crTableCmd = "CREATE TABLE " + dbName + ".SVC_LOGIC (" + "module varchar(80) NOT NULL,"
107 + "rpc varchar(80) NOT NULL," + "version varchar(40) NOT NULL," + "mode varchar(5) NOT NULL,"
108 + "active varchar(1) NOT NULL,graph BLOB,"
109 + "modified_timestamp timestamp ,"
110 + "md5sum varchar(128) DEFAULT NULL,"
111 + "CONSTRAINT P_SVC_LOGIC PRIMARY KEY(module, rpc, version, mode))";
113 stmt = dbConn.createStatement();
114 stmt.executeUpdate(crTableCmd);
116 } catch (Exception e) {
117 throw new ConfigurationException("could not create SVC_LOGIC table", e);
122 } catch (SQLException e) {
123 LOG.error("Statement close error ", e);
128 // See if NODE_TYPES table exists and, if not, create it
132 ResultSet tables = dbm.getTables(null, null, "NODE_TYPES", null);
134 LOG.debug("NODE_TYPES table already exists");
136 String crTableCmd = "CREATE TABLE " + dbName + ".NODE_TYPES (" + "nodetype varchar(80) NOT NULL,"
137 + "CONSTRAINT P_NODE_TYPES PRIMARY KEY(nodetype))";
139 stmt = dbConn.createStatement();
141 stmt.executeUpdate(crTableCmd);
143 } catch (Exception e) {
144 throw new ConfigurationException("could not create SVC_LOGIC table", e);
149 } catch (SQLException e) {
150 LOG.error("Statement close error ", e);
156 private void prepStatements() throws ConfigurationException {
158 // Prepare statements
159 String hasVersionGraphSql = CommonConstants.JDBC_SELECT_COUNT + dbName + CommonConstants.SVCLOGIC_TABLE
160 + CommonConstants.JDBC_GRAPH_QUERY;
163 hasVersionGraphStmt = dbConn.prepareStatement(hasVersionGraphSql);
164 } catch (Exception e) {
165 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + hasVersionGraphSql, e);
169 String hasActiveGraphSql = CommonConstants.JDBC_SELECT_COUNT + dbName + CommonConstants.SVCLOGIC_TABLE
170 + CommonConstants.JDBC_ACTIVE_GRAPH_QUERY;
173 hasActiveGraphStmt = dbConn.prepareStatement(hasActiveGraphSql);
174 } catch (Exception e) {
175 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + hasVersionGraphSql, e);
179 String fetchVersionGraphSql = CommonConstants.JDBC_SELECT_GRAPGH + dbName + CommonConstants.SVCLOGIC_TABLE
180 + CommonConstants.JDBC_GRAPH_QUERY;
183 fetchVersionGraphStmt = dbConn.prepareStatement(fetchVersionGraphSql);
184 } catch (Exception e) {
185 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + fetchVersionGraphSql, e);
189 String fetchActiveGraphSql = CommonConstants.JDBC_SELECT_GRAPGH + dbName + CommonConstants.SVCLOGIC_TABLE
190 + CommonConstants.JDBC_ACTIVE_GRAPH_QUERY;
193 fetchActiveGraphStmt = dbConn.prepareStatement(fetchActiveGraphSql);
194 } catch (Exception e) {
195 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + fetchVersionGraphSql, e);
199 String storeGraphSql = CommonConstants.JDBC_INSERT + dbName
200 + ".SVC_LOGIC (module, rpc, version, mode, active, graph, md5sum) VALUES(?, ?, ?, ?, ?, ?, ?)";
203 storeGraphStmt = dbConn.prepareStatement(storeGraphSql);
204 } catch (Exception e) {
205 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + storeGraphSql, e);
208 String deleteGraphSql = CommonConstants.JDBC_DELETE + dbName
209 + ".SVC_LOGIC WHERE module = ? AND rpc = ? AND version = ? AND mode = ?";
212 deleteGraphStmt = dbConn.prepareStatement(deleteGraphSql);
213 } catch (Exception e) {
214 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + deleteGraphSql, e);
217 String deactivateSql = CommonConstants.JDBC_UPDATE + dbName
218 + ".SVC_LOGIC SET active = 'N' WHERE module = ? AND rpc = ? AND mode = ?";
221 deactivateStmt = dbConn.prepareStatement(deactivateSql);
222 } catch (Exception e) {
223 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + deactivateSql, e);
226 String activateSql = CommonConstants.JDBC_UPDATE + dbName
227 + ".SVC_LOGIC SET active = 'Y' WHERE module = ? AND rpc = ? AND version = ? AND mode = ?";
230 activateStmt = dbConn.prepareStatement(activateSql);
231 } catch (Exception e) {
232 throw new ConfigurationException(CommonConstants.JDBC_STATEMENT_ERR + activateSql, e);
236 private void initDbResources() throws ConfigurationException {
237 if ((dbDriver != null) && (dbDriver.length() > 0)) {
240 Class.forName(dbDriver);
241 } catch (Exception e) {
242 throw new ConfigurationException("could not load driver class " + dbDriver, e);
252 public void init(Properties props) throws ConfigurationException {
255 dbUrl = props.getProperty("org.onap.ccsdk.sli.jdbc.url");
256 if ((dbUrl == null) || (dbUrl.length() == 0)) {
257 throw new ConfigurationException("property org.onap.ccsdk.sli.jdbc.url unset");
260 dbName = props.getProperty("org.onap.ccsdk.sli.jdbc.database");
261 if ((dbName == null) || (dbName.length() == 0)) {
262 throw new ConfigurationException("property org.onap.ccsdk.sli.jdbc.database unset");
265 dbUser = props.getProperty("org.onap.ccsdk.sli.jdbc.user");
266 if ((dbUser == null) || (dbUser.length() == 0)) {
267 throw new ConfigurationException("property org.onap.ccsdk.sli.jdbc.user unset");
271 dbPasswd = props.getProperty("org.onap.ccsdk.sli.jdbc.password");
272 if ((dbPasswd == null) || (dbPasswd.length() == 0)) {
273 throw new ConfigurationException("property org.onap.ccsdk.sli.jdbc.password unset");
276 dbDriver = props.getProperty("org.onap.ccsdk.sli.jdbc.driver");
283 private boolean isDbConnValid() {
285 boolean isValid = false;
288 if (dbConn != null) {
289 isValid = dbConn.isValid(1);
291 } catch (SQLException e) {
292 LOG.error("Not a valid db connection: ", e);
299 public boolean hasGraph(String module, String rpc, String version, String mode) throws SvcLogicException {
301 if (!isDbConnValid()) {
303 // Try reinitializing
306 if (!isDbConnValid()) {
307 throw new ConfigurationException(CommonConstants.JDBC_CONN_ERR);
311 boolean retval = false;
312 ResultSet results = null;
314 PreparedStatement hasGraphStmt;
315 if (version == null) {
316 hasGraphStmt = hasActiveGraphStmt;
318 hasGraphStmt = hasVersionGraphStmt;
322 hasGraphStmt.setString(1, module);
323 hasGraphStmt.setString(2, rpc);
324 hasGraphStmt.setString(3, mode);
327 if (version != null) {
328 hasGraphStmt.setString(4, version);
330 boolean oldAutoCommit = dbConn.getAutoCommit();
331 dbConn.setAutoCommit(false);
332 results = hasGraphStmt.executeQuery();
334 dbConn.setAutoCommit(oldAutoCommit);
336 if (results.next()) {
337 int cnt = results.getInt(1);
344 } catch (Exception e) {
345 throw new ConfigurationException("SQL query failed", e);
347 if (results != null) {
350 } catch (SQLException x) {
351 LOG.error(CommonConstants.RESULTSET_CLOSE_ERR, x);
362 public SvcLogicGraph fetch(String module, String rpc, String version, String mode) throws SvcLogicException {
365 if (!isDbConnValid()) {
367 // Try reinitializing
370 if (!isDbConnValid()) {
371 throw new ConfigurationException(CommonConstants.JDBC_CONN_ERR);
375 SvcLogicGraph retval = null;
376 ResultSet results = null;
378 PreparedStatement fetchGraphStmt;
379 if (version == null) {
380 fetchGraphStmt = fetchActiveGraphStmt;
382 fetchGraphStmt = fetchVersionGraphStmt;
385 fetchGraphStmt.setString(1, module);
386 fetchGraphStmt.setString(2, rpc);
387 fetchGraphStmt.setString(3, mode);
390 if (version != null) {
391 fetchGraphStmt.setString(4, version);
393 boolean oldAutoCommit = dbConn.getAutoCommit();
394 dbConn.setAutoCommit(false);
395 results = fetchGraphStmt.executeQuery();
397 dbConn.setAutoCommit(oldAutoCommit);
399 if (results.next()) {
400 Blob graphBlob = results.getBlob("graph");
402 ObjectInputStream gStream = new ObjectInputStream(graphBlob.getBinaryStream());
404 Object graphObj = gStream.readObject();
407 if (graphObj instanceof SvcLogicGraph) {
408 retval = (SvcLogicGraph) graphObj;
410 throw new ConfigurationException("invalid type for graph (" + graphObj.getClass().getName());
415 } catch (Exception e) {
416 throw new ConfigurationException("SQL query failed", e);
418 if (results != null) {
421 } catch (SQLException x) {
422 LOG.error(CommonConstants.RESULTSET_CLOSE_ERR, x);
431 public void store(SvcLogicGraph graph) throws SvcLogicException {
434 if (!isDbConnValid()) {
436 // Try reinitializing
439 if (!isDbConnValid()) {
440 throw new ConfigurationException(CommonConstants.JDBC_CONN_ERR);
445 throw new SvcLogicException("graph cannot be null");
450 try (ByteArrayOutputStream byteStr = new ByteArrayOutputStream();
451 ObjectOutputStream goutStr = new ObjectOutputStream(byteStr)) {
453 goutStr.writeObject(graph);
455 graphBytes = byteStr.toByteArray();
457 } catch (Exception e) {
458 throw new SvcLogicException("could not serialize graph", e);
461 // If object already stored in database, delete it
462 if (hasGraph(graph.getModule(), graph.getRpc(), graph.getVersion(), graph.getMode())) {
463 delete(graph.getModule(), graph.getRpc(), graph.getVersion(), graph.getMode());
467 boolean oldAutoCommit = dbConn.getAutoCommit();
468 dbConn.setAutoCommit(false);
469 storeGraphStmt.setString(1, graph.getModule());
470 storeGraphStmt.setString(2, graph.getRpc());
471 storeGraphStmt.setString(3, graph.getVersion());
472 storeGraphStmt.setString(4, graph.getMode());
473 storeGraphStmt.setString(5, "N");
474 storeGraphStmt.setBlob(6, new ByteArrayInputStream(graphBytes));
475 storeGraphStmt.setString(7, graph.getMd5sum());
477 storeGraphStmt.executeUpdate();
480 dbConn.setAutoCommit(oldAutoCommit);
481 } catch (Exception e) {
482 throw new SvcLogicException("Could not write object to database", e);
487 public void delete(String module, String rpc, String version, String mode) throws SvcLogicException {
488 if (!isDbConnValid()) {
490 // Try reinitializing
493 if (!isDbConnValid()) {
494 throw new ConfigurationException(CommonConstants.JDBC_CONN_ERR);
499 boolean oldAutoCommit = dbConn.getAutoCommit();
500 dbConn.setAutoCommit(false);
501 deleteGraphStmt.setString(1, module);
502 deleteGraphStmt.setString(2, rpc);
503 deleteGraphStmt.setString(3, version);
504 deleteGraphStmt.setString(4, mode);
507 deleteGraphStmt.executeUpdate();
509 dbConn.setAutoCommit(oldAutoCommit);
510 } catch (Exception e) {
511 throw new SvcLogicException("Could not delete object from database", e);
516 public void activate(SvcLogicGraph graph) throws SvcLogicException {
518 boolean oldAutoCommit = dbConn.getAutoCommit();
520 dbConn.setAutoCommit(false);
522 // Deactivate any current active version
523 deactivateStmt.setString(1, graph.getModule());
524 deactivateStmt.setString(2, graph.getRpc());
525 deactivateStmt.setString(3, graph.getMode());
526 deactivateStmt.executeUpdate();
528 // Activate this version
529 activateStmt.setString(1, graph.getModule());
530 activateStmt.setString(2, graph.getRpc());
531 activateStmt.setString(3, graph.getVersion());
532 activateStmt.setString(4, graph.getMode());
533 activateStmt.executeUpdate();
537 dbConn.setAutoCommit(oldAutoCommit);
539 } catch (Exception e) {
540 throw new SvcLogicException("Could not activate graph", e);
545 public void activate(String module, String rpc, String version, String mode) throws SvcLogicException {
547 boolean oldAutoCommit = dbConn.getAutoCommit();
549 dbConn.setAutoCommit(false);
551 // Deactivate any current active version
552 deactivateStmt.setString(1, module);
553 deactivateStmt.setString(2, rpc);
554 deactivateStmt.setString(3, mode);
555 deactivateStmt.executeUpdate();
557 // Activate this version
558 activateStmt.setString(1, module);
559 activateStmt.setString(2, rpc);
560 activateStmt.setString(3, version);
561 activateStmt.setString(4, mode);
562 activateStmt.executeUpdate();
566 dbConn.setAutoCommit(oldAutoCommit);
568 } catch (Exception e) {
569 throw new SvcLogicException("Could not activate graph", e);