dblib/provider/src/main/java/org/openecomp/sdnc/sli/resource/dblib/CachedDataSource.java

   1 /*-
   2  * ============LICENSE_START=======================================================
   3  * openecomp
   4  * ================================================================================
   5  * Copyright (C) 2016 - 2017 AT&T
   6  * ================================================================================
   7  * Licensed under the Apache License, Version 2.0 (the "License");
   8  * you may not use this file except in compliance with the License.
   9  * You may obtain a copy of the License at
  10  *
  11  *      http://www.apache.org/licenses/LICENSE-2.0
  12  *
  13  * Unless required by applicable law or agreed to in writing, software
  14  * distributed under the License is distributed on an "AS IS" BASIS,
  15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  16  * See the License for the specific language governing permissions and
  17  * limitations under the License.
  18  * ============LICENSE_END=========================================================
  19  */
  20
  21 package org.openecomp.sdnc.sli.resource.dblib;
  22
  23 import java.io.PrintWriter;
  24 import java.sql.Connection;
  25 import java.sql.PreparedStatement;
  26 import java.sql.ResultSet;
  27 import java.sql.SQLException;
  28 import java.sql.Statement;
  29 import java.util.ArrayList;
  30 import java.util.Arrays;
  31 import java.util.Observer;
  32
  33 import javax.sql.DataSource;
  34 import javax.sql.rowset.CachedRowSet;
  35 import javax.sql.rowset.RowSetProvider;
  36
  37 import org.openecomp.sdnc.sli.resource.dblib.config.BaseDBConfiguration;
  38 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitor;
  39 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitorObserver;
  40 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitor.TestObject;
  41 import org.slf4j.Logger;
  42 import org.slf4j.LoggerFactory;
  43
  44
  45 /**
  46  * @version $Revision: 1.13 $
  47  * Change Log
  48  * Author         Date     Comments
  49  * ============== ======== ====================================================
  50  * Rich Tabedzki
  51  */
  52
  53 public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver
  54 {
  55         private static Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
  56
  57         protected static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
  58
  59         protected long CONN_REQ_TIMEOUT = 30L;
  60         protected long DATA_REQ_TIMEOUT = 100L;
  61
  62         private final SQLExecutionMonitor monitor;
  63         protected DataSource ds = null;
  64         protected String connectionName = null;
  65         protected boolean initialized = false;
  66
  67         private long interval = 1000;
  68         private long initisalDelay = 5000;
  69         private long expectedCompletionTime = 50L;
  70         private boolean canTakeOffLine = true;
  71         private long unprocessedFailoverThreshold = 3L;
  72
  73
  74         public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException
  75         {
  76                 configure(jdbcElem);
  77                 monitor = new SQLExecutionMonitor(this);
  78         }
  79
  80         protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
  81         /* (non-Javadoc)
  82          * @see javax.sql.DataSource#getConnection()
  83          */
  84         public Connection getConnection() throws SQLException
  85         {
  86                 return ds.getConnection();
  87         }
  88
  89         public CachedRowSet getData(String statement, ArrayList<String> arguments) throws SQLException, Throwable
  90         {
  91                 TestObject testObject = null;
  92                 testObject = monitor.registerRequest();
  93
  94                 Connection connection = null;
  95                 try {
  96                         connection = this.getConnection();
  97                         if(connection ==  null ) {
  98                                 throw new SQLException("Connection invalid");
  99                         }
 100                         if(LOGGER.isDebugEnabled())
 101                                 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
 102                         return executePreparedStatement(connection, statement, arguments);
 103                 } finally {
 104                         try {
 105                                 if(connection != null && !connection.isClosed()) {
 106                                         connection.close();
 107                                 }
 108                         } catch(Throwable exc) {
 109                                 // the exception not monitored
 110                         } finally {
 111                                 connection = null;
 112                         }
 113
 114                         monitor.deregisterReguest(testObject);
 115                 }
 116         }
 117
 118         public boolean writeData(String statement, ArrayList<String> arguments) throws SQLException, Throwable
 119         {
 120                 TestObject testObject = null;
 121                 testObject = monitor.registerRequest();
 122
 123                 Connection connection = null;
 124                 try {
 125                         connection = this.getConnection();
 126                         if(connection ==  null ) {
 127                                 throw new SQLException("Connection invalid");
 128                         }
 129                         if(LOGGER.isDebugEnabled())
 130                                 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
 131                         return executeUpdatePreparedStatement(connection, statement, arguments);
 132                 } finally {
 133                         try {
 134                                 if(connection != null && !connection.isClosed()) {
 135                                         connection.close();
 136                                 }
 137                         } catch(Throwable exc) {
 138                                 // the exception not monitored
 139                         } finally {
 140                                 connection = null;
 141                         }
 142
 143                         monitor.deregisterReguest(testObject);
 144                 }
 145         }
 146
 147         private CachedRowSet executePreparedStatement(Connection conn, String statement, ArrayList<String> arguments) throws SQLException, Throwable {
 148                 long time = System.currentTimeMillis();
 149
 150                 CachedRowSet data = null;
 151                 if(LOGGER.isDebugEnabled()){
 152                         LOGGER.debug("SQL Statement: "+ statement);
 153                         if(arguments != null && !arguments.isEmpty()) {
 154                                 LOGGER.debug("Argunments: "+ Arrays.toString(arguments.toArray()));
 155                         }
 156                 }
 157
 158                 ResultSet rs = null;
 159                 try {
 160                         data = RowSetProvider.newFactory().createCachedRowSet();
 161                         PreparedStatement ps = conn.prepareStatement(statement);
 162                         if(arguments != null)
 163                         {
 164                                 for(int i = 0, max = arguments.size(); i < max; i++){
 165                                         ps.setObject(i+1, arguments.get(i));
 166                                 }
 167                         }
 168                         rs = ps.executeQuery();
 169                         data.populate(rs);
 170                     // Point the rowset Cursor to the start
 171                         if(LOGGER.isDebugEnabled()){
 172                                 LOGGER.debug("SQL SUCCESS. count=" + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));                 }
 173                 } catch(SQLException exc){
 174                         if(LOGGER.isDebugEnabled()){
 175                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 176                         }
 177                         try {   conn.rollback(); } catch(Throwable thr){}
 178                         if(arguments != null && !arguments.isEmpty()) {
 179                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 180                         } else {
 181                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 182                         }
 183                         throw exc;
 184                 } catch(Throwable exc){
 185                         if(LOGGER.isDebugEnabled()){
 186                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 187                         }
 188                         if(arguments != null && !arguments.isEmpty()) {
 189                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 190                         } else {
 191                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 192                         }
 193                         throw exc; // new SQLException(exc);
 194                 } finally {
 195
 196                         try {
 197                                 if(rs != null){
 198                                         rs.close();
 199                                         rs = null;
 200                                 }
 201                         } catch(Exception exc){
 202
 203                         }
 204                         try {
 205                                 if(conn != null){
 206                                         conn.close();
 207                                         conn = null;
 208                                 }
 209                         } catch(Exception exc){
 210
 211                         }
 212                 }
 213
 214                 return data;
 215         }
 216
 217         private boolean executeUpdatePreparedStatement(Connection conn, String statement, ArrayList<String> arguments) throws SQLException, Throwable {
 218                 long time = System.currentTimeMillis();
 219
 220                 CachedRowSet data = null;
 221
 222                 int rs = -1;
 223                 try {
 224                         data = RowSetProvider.newFactory().createCachedRowSet();
 225                         PreparedStatement ps = conn.prepareStatement(statement);
 226                         if(arguments != null)
 227                         {
 228                                 for(int i = 0, max = arguments.size(); i < max; i++){
 229                                         ps.setObject(i+1, arguments.get(i));
 230                                 }
 231                         }
 232                         rs = ps.executeUpdate();
 233                     // Point the rowset Cursor to the start
 234                         if(LOGGER.isDebugEnabled()){
 235                                 LOGGER.debug("SQL SUCCESS. count=" + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));
 236                         }
 237                 } catch(SQLException exc){
 238                         if(LOGGER.isDebugEnabled()){
 239                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 240                         }
 241                         try {   conn.rollback(); } catch(Throwable thr){}
 242                         if(arguments != null && !arguments.isEmpty()) {
 243                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 244                         } else {
 245                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 246                         }
 247                         throw exc;
 248                 } catch(Throwable exc){
 249                         if(LOGGER.isDebugEnabled()){
 250                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 251                         }
 252                         if(arguments != null && !arguments.isEmpty()) {
 253                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 254                         } else {
 255                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 256                         }
 257                         throw exc; // new SQLException(exc);
 258                 } finally {
 259
 260                         try {
 261                                 if(conn != null){
 262                                         conn.close();
 263                                         conn = null;
 264                                 }
 265                         } catch(Exception exc){
 266
 267                         }
 268                 }
 269
 270                 return true;
 271         }
 272
 273         /* (non-Javadoc)
 274          * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
 275          */
 276         public Connection getConnection(String username, String password)
 277                         throws SQLException
 278         {
 279                 return ds.getConnection(username, password);
 280         }
 281
 282         /* (non-Javadoc)
 283          * @see javax.sql.DataSource#getLogWriter()
 284          */
 285         public PrintWriter getLogWriter() throws SQLException
 286         {
 287                 return ds.getLogWriter();
 288         }
 289
 290         /* (non-Javadoc)
 291          * @see javax.sql.DataSource#getLoginTimeout()
 292          */
 293         public int getLoginTimeout() throws SQLException
 294         {
 295                 return ds.getLoginTimeout();
 296         }
 297
 298         /* (non-Javadoc)
 299          * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
 300          */
 301         public void setLogWriter(PrintWriter out) throws SQLException
 302         {
 303                 ds.setLogWriter(out);
 304         }
 305
 306         /* (non-Javadoc)
 307          * @see javax.sql.DataSource#setLoginTimeout(int)
 308          */
 309         public void setLoginTimeout(int seconds) throws SQLException
 310         {
 311                 ds.setLoginTimeout(seconds);
 312         }
 313
 314
 315         public final String getDbConnectionName(){
 316                 return connectionName;
 317         }
 318
 319         protected final void setDbConnectionName(String name) {
 320                 this.connectionName = name;
 321         }
 322
 323         public void cleanUp(){
 324                 ds = null;
 325                 monitor.deleteObservers();
 326                 monitor.cleanup();
 327         }
 328
 329         public boolean isInitialized() {
 330                 return initialized;
 331         }
 332
 333         protected boolean testConnection(){
 334                 return testConnection(false);
 335         }
 336
 337         protected boolean testConnection(boolean error_level){
 338                 Connection conn = null;
 339                 ResultSet rs = null;
 340                 Statement stmt = null;
 341                 try
 342                 {
 343                         Boolean readOnly = null;
 344                         String hostname = null;
 345                         conn = this.getConnection();
 346                         stmt = conn.createStatement();
 347                         rs = stmt.executeQuery("SELECT @@global.read_only, @@global.hostname");   //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
 348                         while(rs.next())
 349                         {
 350                                 readOnly = rs.getBoolean(1);
 351                                 hostname = rs.getString(2);
 352 //                              if(rs.getInt(1)==1){
 353                                         if(LOGGER.isDebugEnabled()){
 354                                                 LOGGER.debug("SQL DataSource <"+getDbConnectionName() + "> connected to " + hostname + ", read-only is " + readOnly + ", tested successfully ");
 355                                         }
 356 //                              }
 357                         }
 358
 359                 } catch (Throwable exc) {
 360                         if(error_level) {
 361                                 LOGGER.error("SQL DataSource <" + this.getDbConnectionName() +  "> test failed. Cause : " + exc.getMessage());
 362                         } else {
 363                                 LOGGER.info("SQL DataSource <" + this.getDbConnectionName() +   "> test failed. Cause : " + exc.getMessage());
 364                         }
 365                         return false;
 366                 } finally {
 367                         if(rs != null) {
 368                                 try {
 369                                         rs.close();
 370                                         rs = null;
 371                                 } catch (SQLException e) {
 372                                 }
 373                         }
 374                         if(stmt != null) {
 375                                 try {
 376                                         stmt.close();
 377                                         stmt = null;
 378                                 } catch (SQLException e) {
 379                                 }
 380                         }
 381                         if(conn !=null){
 382                                 try {
 383                                         conn.close();
 384                                         conn = null;
 385                                 } catch (SQLException e) {
 386                                 }
 387                         }
 388                 }
 389                 return true;
 390         }
 391
 392         public boolean isWrapperFor(Class<?> iface) throws SQLException {
 393                 return false;
 394         }
 395
 396         public <T> T unwrap(Class<T> iface) throws SQLException {
 397                 return null;
 398         }
 399
 400         @SuppressWarnings("deprecation")
 401         public void setConnectionCachingEnabled(boolean state)
 402         {
 403 //              if(ds != null && ds instanceof OracleDataSource)
 404 //                      try {
 405 //                              ((OracleDataSource)ds).setConnectionCachingEnabled(true);
 406 //                      } catch (SQLException exc) {
 407 //                              LOGGER.warn("", exc);
 408 //                      }
 409         }
 410
 411         public void addObserver(Observer observer) {
 412                 monitor.addObserver(observer);
 413         }
 414
 415         public void deleteObserver(Observer observer) {
 416                 monitor.deleteObserver(observer);
 417         }
 418
 419         public long getInterval() {
 420                 return interval;
 421         }
 422
 423         public long getInitialDelay() {
 424                 return initisalDelay;
 425         }
 426
 427         public void setInterval(long value) {
 428                 interval = value;
 429         }
 430
 431         public void setInitialDelay(long value) {
 432                 initisalDelay = value;
 433         }
 434
 435         public long getExpectedCompletionTime() {
 436                 return expectedCompletionTime;
 437         }
 438
 439         public void setExpectedCompletionTime(long value) {
 440                 expectedCompletionTime = value;
 441         }
 442
 443         public long getUnprocessedFailoverThreshold() {
 444                 return unprocessedFailoverThreshold;
 445         }
 446
 447         public void setUnprocessedFailoverThreshold(long value) {
 448                 this.unprocessedFailoverThreshold = value;
 449         }
 450
 451         public boolean canTakeOffLine() {
 452                 return canTakeOffLine;
 453         }
 454
 455         public void blockImmediateOffLine() {
 456                 canTakeOffLine = false;
 457                 final Thread offLineTimer = new Thread()
 458                 {
 459                         public void run(){
 460                                 try {
 461                                         Thread.sleep(30000L);
 462                                 }catch(Throwable exc){
 463
 464                                 }finally{
 465                                         canTakeOffLine = true;
 466                                 }
 467                         }
 468                 };
 469                 offLineTimer.setDaemon(true);
 470                 offLineTimer.start();
 471         }
 472
 473         /**
 474          * @return the monitor
 475          */
 476         final SQLExecutionMonitor getMonitor() {
 477                 return monitor;
 478         }
 479
 480         protected boolean isSlave() {
 481                 CachedRowSet rs = null;
 482                 boolean isSlave = true;
 483                 String hostname = "UNDETERMINED";
 484                 try {
 485 //                      rs = this.getData("show slave status", new ArrayList<String>());
 486 //                      while(rs.next()) {
 487 //                              String master = rs.getString(2);
 488 //                              LOGGER.debug("database <"+connectionName+"> defines master as " + master);
 489 //                              if(master == null || master.isEmpty() || master.equals(this.getDbConnectionName())) {
 490 //                                      isSlave = false;
 491 //                              } else {
 492 //                                      isSlave = true;
 493 //                              }
 494 //                      }
 495
 496                         boolean localSlave = true;
 497                         rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<String>());
 498                         while(rs.next()) {
 499                                 localSlave = rs.getBoolean(1);
 500                                 hostname = rs.getString(2);
 501                         }
 502                         isSlave = localSlave;
 503                 } catch (SQLException e) {
 504                         LOGGER.error("", e);
 505                         isSlave = true;
 506                 } catch (Throwable e) {
 507                         LOGGER.error("", e);
 508                         isSlave = true;
 509                 }
 510                 if(isSlave){
 511                         LOGGER.debug("SQL SLAVE : "+connectionName + " on server " + hostname);
 512                 } else {
 513                         LOGGER.debug("SQL MASTER : "+connectionName + " on server " + hostname);
 514                 }
 515                 return isSlave;
 516         }
 517
 518         public boolean isFabric() {
 519                 return false;
 520         }
 521 }