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.openecomp.sdnc.sli;
24 import java.io.ByteArrayInputStream;
25 import java.io.ByteArrayOutputStream;
26 import java.io.IOException;
27 import java.io.ObjectInputStream;
28 import java.io.ObjectOutputStream;
30 import java.sql.Connection;
31 import java.sql.DatabaseMetaData;
32 import java.sql.Driver;
33 import java.sql.DriverManager;
34 import java.sql.PreparedStatement;
35 import java.sql.ResultSet;
36 import java.sql.SQLException;
37 import java.sql.Statement;
38 import java.util.Properties;
40 import org.slf4j.Logger;
41 import org.slf4j.LoggerFactory;
44 public class SvcLogicJdbcStore implements SvcLogicStore {
45 private static final Logger LOG = LoggerFactory
46 .getLogger(SvcLogicJdbcStore.class);
48 private String dbUrl = null;
49 private String dbName = null;
50 private String dbUser = null;
51 private String dbPasswd = null;
52 private String dbDriver = null;
54 private Connection dbConn;
55 private PreparedStatement hasActiveGraphStmt = null;
56 private PreparedStatement hasVersionGraphStmt = null;
57 private PreparedStatement fetchActiveGraphStmt = null;
58 private PreparedStatement fetchVersionGraphStmt = null;
59 private PreparedStatement storeGraphStmt = null;
60 private PreparedStatement deleteGraphStmt = null;
62 private PreparedStatement deactivateStmt = null;
63 private PreparedStatement activateStmt = null;
65 private PreparedStatement registerNodeStmt = null;
66 private PreparedStatement unregisterNodeStmt = null;
67 private PreparedStatement validateNodeStmt = null;
69 private void getConnection() throws ConfigurationException
72 Properties jdbcProps = new Properties();
74 jdbcProps.setProperty("user", dbUser);
75 jdbcProps.setProperty("password", dbPasswd);
78 Driver dvr = new com.mysql.jdbc.Driver();
79 if (dvr.acceptsURL(dbUrl))
81 LOG.debug("Driver com.mysql.jdbc.Driver accepts "+dbUrl);
85 LOG.warn("Driver com.mysql.jdbc.Driver does not accept "+dbUrl);
87 } catch (SQLException e1) {
88 LOG.error("Caught exception trying to load com.mysql.jdbc.Driver", e1);
96 this.dbConn = DriverManager.getConnection(dbUrl, jdbcProps);
100 throw new ConfigurationException("failed to get database connection ["+dbUrl+"]", e);
105 private void createTable() throws ConfigurationException
109 DatabaseMetaData dbm = null;
113 dbm = dbConn.getMetaData();
114 } catch (SQLException e) {
116 throw new ConfigurationException("could not get databse metadata", e);
119 // See if table SVC_LOGIC exists. If not, create it.
124 ResultSet tables = dbm.getTables(null, null, "SVC_LOGIC", null);
130 String crTableCmd = "CREATE TABLE "+dbName+".SVC_LOGIC ("
131 + "module varchar(80) NOT NULL,"
132 + "rpc varchar(80) NOT NULL,"
133 + "version varchar(40) NOT NULL,"
134 + "mode varchar(5) NOT NULL,"
135 + "active varchar(1) NOT NULL,"
137 + "CONSTRAINT P_SVC_LOGIC PRIMARY KEY(module, rpc, version, mode))";
139 Statement stmt = null;
140 ConfigurationException myExc = null;
143 stmt = dbConn.createStatement();
144 stmt.executeUpdate(crTableCmd);
146 catch (SQLException e1)
148 myExc = new ConfigurationException("cannot create SVC_LOGIC table", e1);
166 throw new ConfigurationException("could not create SVC_LOGIC table", e);
169 // See if NODE_TYPES table exists and, if not, create it
175 ResultSet tables = dbm.getTables(null, null, "NODE_TYPES", null);
181 String crTableCmd = "CREATE TABLE "+dbName+".NODE_TYPES ("
182 + "nodetype varchar(80) NOT NULL,"
183 + "CONSTRAINT P_NODE_TYPES PRIMARY KEY(nodetype))";
185 Statement stmt = null;
186 ConfigurationException myExc = null;
189 stmt = dbConn.createStatement();
190 stmt.executeUpdate(crTableCmd);
192 catch (SQLException e1)
194 myExc = new ConfigurationException("cannot create SVC_LOGIC table", e1);
212 throw new ConfigurationException("could not create SVC_LOGIC table", e);
216 private void prepStatements() throws ConfigurationException
219 // Prepare statements
220 String hasVersionGraphSql = "SELECT count(*) FROM "+dbName+".SVC_LOGIC"
221 + " WHERE module = ? AND rpc = ? AND mode = ? AND version = ?";
225 hasVersionGraphStmt = dbConn.prepareStatement(hasVersionGraphSql);
229 throw new ConfigurationException("could not prepare statement "+hasVersionGraphSql, e);
233 String hasActiveGraphSql = "SELECT count(*) FROM "+dbName+".SVC_LOGIC"
234 + " WHERE module = ? AND rpc = ? AND mode = ? AND active = 'Y'";
238 hasActiveGraphStmt = dbConn.prepareStatement(hasActiveGraphSql);
242 throw new ConfigurationException("could not prepare statement "+hasVersionGraphSql, e);
246 String fetchVersionGraphSql = "SELECT graph FROM "+dbName+".SVC_LOGIC"
247 + " WHERE module = ? AND rpc = ? AND mode = ? AND version = ?";
251 fetchVersionGraphStmt = dbConn.prepareStatement(fetchVersionGraphSql);
255 throw new ConfigurationException("could not prepare statement "+fetchVersionGraphSql, e);
259 String fetchActiveGraphSql = "SELECT graph FROM "+dbName+".SVC_LOGIC"
260 + " WHERE module = ? AND rpc = ? AND mode = ? AND active = 'Y'";
264 fetchActiveGraphStmt = dbConn.prepareStatement(fetchActiveGraphSql);
268 throw new ConfigurationException("could not prepare statement "+fetchVersionGraphSql, e);
272 String storeGraphSql = "INSERT INTO "+dbName+".SVC_LOGIC (module, rpc, version, mode, active, graph)"
273 + " VALUES(?, ?, ?, ?, ?, ?)";
277 storeGraphStmt = dbConn.prepareStatement(storeGraphSql);
281 throw new ConfigurationException("could not prepare statement "+storeGraphSql, e);
284 String deleteGraphSql = "DELETE FROM "+dbName+".SVC_LOGIC WHERE module = ? AND rpc = ? AND version = ? AND mode = ?";
288 deleteGraphStmt = dbConn.prepareStatement(deleteGraphSql);
292 throw new ConfigurationException("could not prepare statement "+deleteGraphSql, e);
295 String deactivateSql = "UPDATE "+dbName+".SVC_LOGIC SET active = 'N' WHERE module = ? AND rpc = ? AND mode = ?";
299 deactivateStmt = dbConn.prepareStatement(deactivateSql);
303 throw new ConfigurationException("could not prepare statement "+deactivateSql, e);
306 String activateSql = "UPDATE "+dbName+".SVC_LOGIC SET active = 'Y' WHERE module = ? AND rpc = ? AND version = ? AND mode = ?";
310 activateStmt = dbConn.prepareStatement(activateSql);
314 throw new ConfigurationException("could not prepare statement "+activateSql, e);
317 String registerNodeSql = "INSERT INTO "+dbName+".NODE_TYPES (nodetype) VALUES(?)";
320 registerNodeStmt = dbConn.prepareStatement(registerNodeSql);
324 throw new ConfigurationException("could not prepare statement "+registerNodeSql, e);
327 String unregisterNodeSql = "DELETE FROM "+dbName+".NODE_TYPES WHERE nodetype = ?";
330 unregisterNodeStmt = dbConn.prepareStatement(unregisterNodeSql);
334 throw new ConfigurationException("could not prepare statement "+unregisterNodeSql, e);
337 String validateNodeSql = "SELECT count(*) FROM "+dbName+".NODE_TYPES WHERE nodetype = ?";
340 validateNodeStmt = dbConn.prepareStatement(validateNodeSql);
344 throw new ConfigurationException("could not prepare statement "+validateNodeSql, e);
348 private void initDbResources() throws ConfigurationException
350 if ((dbDriver != null) && (dbDriver.length() > 0))
355 Class.forName(dbDriver);
359 throw new ConfigurationException("could not load driver class "+dbDriver, e);
368 public void init(Properties props) throws ConfigurationException {
371 dbUrl = props.getProperty("org.openecomp.sdnc.sli.jdbc.url");
372 if ((dbUrl == null) || (dbUrl.length() == 0))
374 throw new ConfigurationException("property org.openecomp.sdnc.sli.jdbc.url unset");
377 dbName = props.getProperty("org.openecomp.sdnc.sli.jdbc.database");
378 if ((dbName == null) || (dbName.length() == 0))
380 throw new ConfigurationException("property org.openecomp.sdnc.sli.jdbc.database unset");
383 dbUser = props.getProperty("org.openecomp.sdnc.sli.jdbc.user");
384 if ((dbUser == null) || (dbUser.length() == 0))
386 throw new ConfigurationException("property org.openecomp.sdnc.sli.jdbc.user unset");
390 dbPasswd = props.getProperty("org.openecomp.sdnc.sli.jdbc.password");
391 if ((dbPasswd == null) || (dbPasswd.length() == 0))
393 throw new ConfigurationException("property org.openecomp.sdnc.sli.jdbc.password unset");
396 dbDriver = props.getProperty("org.openecomp.sdnc.sli.jdbc.driver");
403 private boolean isDbConnValid()
406 boolean isValid = false;
412 isValid = dbConn.isValid(1);
415 catch (SQLException e)
420 public boolean hasGraph(String module, String rpc, String version, String mode) throws SvcLogicException {
425 if (!isDbConnValid())
428 // Try reinitializing
431 if (!isDbConnValid())
433 throw new ConfigurationException("no jdbc connection");
439 boolean retval = false;
440 ResultSet results = null;
442 PreparedStatement hasGraphStmt = null;
445 hasGraphStmt = hasActiveGraphStmt;
449 hasGraphStmt = hasVersionGraphStmt;
456 hasGraphStmt.setString(1, module);
457 hasGraphStmt.setString(2, rpc);
458 hasGraphStmt.setString(3, mode);
463 hasGraphStmt.setString(4, version);
465 boolean oldAutoCommit = dbConn.getAutoCommit();
466 dbConn.setAutoCommit(false);
467 results = hasGraphStmt.executeQuery();
469 dbConn.setAutoCommit(oldAutoCommit);
473 int cnt = results.getInt(1);
484 throw new ConfigurationException("SQL query failed", e);
495 catch (SQLException x)
507 public SvcLogicGraph fetch(String module, String rpc, String version, String mode) throws SvcLogicException {
512 if (!isDbConnValid())
515 // Try reinitializing
518 if (!isDbConnValid())
520 throw new ConfigurationException("no jdbc connection");
526 SvcLogicGraph retval = null;
527 ResultSet results = null;
529 PreparedStatement fetchGraphStmt = null;
532 fetchGraphStmt = fetchActiveGraphStmt;
536 fetchGraphStmt = fetchVersionGraphStmt;
540 fetchGraphStmt.setString(1, module);
541 fetchGraphStmt.setString(2, rpc);
542 fetchGraphStmt.setString(3, mode);
547 fetchGraphStmt.setString(4, version);
549 boolean oldAutoCommit = dbConn.getAutoCommit();
550 dbConn.setAutoCommit(false);
551 results = fetchGraphStmt.executeQuery();
553 dbConn.setAutoCommit(oldAutoCommit);
557 Blob graphBlob = results.getBlob("graph");
559 ObjectInputStream gStream = new ObjectInputStream(graphBlob.getBinaryStream());
561 Object graphObj = gStream.readObject();
564 if (graphObj instanceof SvcLogicGraph)
566 retval = (SvcLogicGraph) graphObj;
570 throw new ConfigurationException("invalid type for graph ("+graphObj.getClass().getName());
582 throw new ConfigurationException("SQL query failed", e);
592 catch (SQLException x)
604 public void store(SvcLogicGraph graph) throws SvcLogicException {
607 if (!isDbConnValid())
610 // Try reinitializing
613 if (!isDbConnValid())
615 throw new ConfigurationException("no jdbc connection");
621 throw new SvcLogicException("graph cannot be null");
624 byte[] graphBytes = null;
626 ByteArrayOutputStream byteStr = null;
627 ObjectOutputStream goutStr = null;
631 byteStr = new ByteArrayOutputStream();
632 goutStr = new ObjectOutputStream(byteStr);
633 goutStr.writeObject(graph);
635 graphBytes = byteStr.toByteArray();
640 throw new SvcLogicException("could not serialize graph", e);
649 } catch (IOException e) {
658 } catch (IOException e) {
665 // If object already stored in database, delete it
666 if (hasGraph(graph.getModule(), graph.getRpc(), graph.getVersion(), graph.getMode()))
668 delete(graph.getModule(), graph.getRpc(), graph.getVersion(), graph.getMode());
673 boolean oldAutoCommit = dbConn.getAutoCommit();
674 dbConn.setAutoCommit(false);
675 storeGraphStmt.setString(1, graph.getModule());
676 storeGraphStmt.setString(2, graph.getRpc());
677 storeGraphStmt.setString(3, graph.getVersion());
678 storeGraphStmt.setString(4, graph.getMode());
679 storeGraphStmt.setString(5, "N");
680 storeGraphStmt.setBlob(6, new ByteArrayInputStream(graphBytes));
682 storeGraphStmt.executeUpdate();
685 dbConn.setAutoCommit(oldAutoCommit);
689 throw new SvcLogicException("Could not write object to database", e);
693 public void delete(String module, String rpc, String version, String mode) throws SvcLogicException
695 if (!isDbConnValid())
698 // Try reinitializing
701 if (!isDbConnValid())
703 throw new ConfigurationException("no jdbc connection");
709 boolean oldAutoCommit = dbConn.getAutoCommit();
710 dbConn.setAutoCommit(false);
711 deleteGraphStmt.setString(1, module);
712 deleteGraphStmt.setString(2, rpc);
713 deleteGraphStmt.setString(3, version);
714 deleteGraphStmt.setString(4, mode);
717 deleteGraphStmt.executeUpdate();
719 dbConn.setAutoCommit(oldAutoCommit);
723 throw new SvcLogicException("Could not delete object from database", e);
727 public void activate(SvcLogicGraph graph) throws SvcLogicException
731 boolean oldAutoCommit = dbConn.getAutoCommit();
733 dbConn.setAutoCommit(false);
735 // Deactivate any current active version
736 deactivateStmt.setString(1, graph.getModule());
737 deactivateStmt.setString(2, graph.getRpc());
738 deactivateStmt.setString(3, graph.getMode());
739 deactivateStmt.executeUpdate();
741 // Activate this version
742 activateStmt.setString(1, graph.getModule());
743 activateStmt.setString(2, graph.getRpc());
744 activateStmt.setString(3, graph.getVersion());
745 activateStmt.setString(4, graph.getMode());
746 activateStmt.executeUpdate();
750 dbConn.setAutoCommit(oldAutoCommit);
755 throw new SvcLogicException("Could not activate graph", e);
760 public void registerNodeType(String nodeType) throws SvcLogicException {
762 if (isValidNodeType(nodeType))
767 if (!isDbConnValid())
770 // Try reinitializing
773 if (!isDbConnValid())
775 throw new ConfigurationException("no jdbc connection");
781 boolean oldAutoCommit = dbConn.getAutoCommit();
782 dbConn.setAutoCommit(false);
783 registerNodeStmt.setString(1, nodeType);
784 registerNodeStmt.executeUpdate();
786 dbConn.setAutoCommit(oldAutoCommit);
790 throw new SvcLogicException("Could not add node type to database", e);
796 public void unregisterNodeType(String nodeType) throws SvcLogicException {
798 if (!isValidNodeType(nodeType))
803 if (!isDbConnValid())
806 // Try reinitializing
809 if (!isDbConnValid())
811 throw new ConfigurationException("no jdbc connection");
817 boolean oldAutoCommit = dbConn.getAutoCommit();
818 dbConn.setAutoCommit(false);
819 unregisterNodeStmt.setString(1, nodeType);
820 unregisterNodeStmt.executeUpdate();
822 dbConn.setAutoCommit(oldAutoCommit);
826 throw new SvcLogicException("Could not delete node type from database", e);
832 public boolean isValidNodeType(String nodeType) throws SvcLogicException {
834 boolean isValid = false;
836 if (!isDbConnValid())
839 // Try reinitializing
842 if (!isDbConnValid())
844 throw new ConfigurationException("no jdbc connection");
848 ResultSet results = null;
851 validateNodeStmt.setString(1, nodeType);
853 boolean oldAutoCommit = dbConn.getAutoCommit();
854 dbConn.setAutoCommit(false);
855 results = validateNodeStmt.executeQuery();
857 dbConn.setAutoCommit(oldAutoCommit);
863 int cnt = results.getInt(1);
875 throw new SvcLogicException("Cannot select node type from database", e);
885 catch (SQLException x)