dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java

   1 /*-
   2  * ============LICENSE_START=======================================================
   3  * onap
   4  * ================================================================================
   5  * Copyright (C) 2016 - 2017 ONAP
   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.onap.ccsdk.sli.core.dblib;
  22
  23 import java.io.Closeable;
  24 import java.io.IOException;
  25 import java.io.PrintWriter;
  26 import java.io.StringWriter;
  27 import java.sql.Blob;
  28 import java.sql.Connection;
  29 import java.sql.Date;
  30 import java.sql.PreparedStatement;
  31 import java.sql.ResultSet;
  32 import java.sql.SQLException;
  33 import java.sql.Statement;
  34 import java.sql.Timestamp;
  35 import java.util.ArrayList;
  36 import java.util.Arrays;
  37 import java.util.Observer;
  38
  39 import javax.sql.DataSource;
  40 import javax.sql.rowset.CachedRowSet;
  41 import javax.sql.rowset.RowSetProvider;
  42
  43 import org.slf4j.Logger;
  44 import org.slf4j.LoggerFactory;
  45 import org.apache.tomcat.jdbc.pool.PoolExhaustedException;
  46 import org.onap.ccsdk.sli.core.dblib.config.BaseDBConfiguration;
  47 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor;
  48 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitorObserver;
  49 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor.TestObject;
  50
  51 import com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException;
  52
  53
  54 /**
  55  * @version $Revision: 1.13 $
  56  * Change Log
  57  * Author         Date     Comments
  58  * ============== ======== ====================================================
  59  * Rich Tabedzki
  60  */
  61
  62 public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver
  63 {
  64         private static Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
  65
  66         protected static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
  67
  68         protected long CONN_REQ_TIMEOUT = 30L;
  69         protected long DATA_REQ_TIMEOUT = 100L;
  70
  71         private final SQLExecutionMonitor monitor;
  72         protected DataSource ds = null;
  73         protected String connectionName = null;
  74         protected boolean initialized = false;
  75
  76         private long interval = 1000;
  77         private long initialDelay = 5000;
  78         private long expectedCompletionTime = 50L;
  79         private boolean canTakeOffLine = true;
  80         private long unprocessedFailoverThreshold = 3L;
  81
  82         private long nextErrorReportTime = 0L;
  83
  84         private String globalHostName = null;
  85
  86
  87         public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException
  88         {
  89                 configure(jdbcElem);
  90                 monitor = new SQLExecutionMonitor(this);
  91         }
  92
  93         protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
  94         /* (non-Javadoc)
  95          * @see javax.sql.DataSource#getConnection()
  96          */
  97         public Connection getConnection() throws SQLException
  98         {
  99                 return ds.getConnection();
 100         }
 101
 102         public CachedRowSet getData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
 103         {
 104                 TestObject testObject = null;
 105                 testObject = monitor.registerRequest();
 106
 107                 Connection connection = null;
 108                 try {
 109                         connection = this.getConnection();
 110                         if(connection ==  null ) {
 111                                 throw new SQLException("Connection invalid");
 112                         }
 113                         if(LOGGER.isDebugEnabled())
 114                                 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
 115                         return executePreparedStatement(connection, statement, arguments, true);
 116                 } finally {
 117                         try {
 118                                 if(connection != null && !connection.isClosed()) {
 119                                         connection.close();
 120                                 }
 121                         } catch(Throwable exc) {
 122                                 // the exception not monitored
 123                         } finally {
 124                                 connection = null;
 125                         }
 126
 127                         monitor.deregisterReguest(testObject);
 128                 }
 129         }
 130
 131         public boolean writeData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
 132         {
 133                 TestObject testObject = null;
 134                 testObject = monitor.registerRequest();
 135
 136                 Connection connection = null;
 137                 try {
 138                         connection = this.getConnection();
 139                         if(connection ==  null ) {
 140                                 throw new SQLException("Connection invalid");
 141                         }
 142                         if(LOGGER.isDebugEnabled())
 143                                 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
 144                         return executeUpdatePreparedStatement(connection, statement, arguments, true);
 145                 } finally {
 146                         try {
 147                                 if(connection != null && !connection.isClosed()) {
 148                                         connection.close();
 149                                 }
 150                         } catch(Throwable exc) {
 151                                 // the exception not monitored
 152                         } finally {
 153                                 connection = null;
 154                         }
 155
 156                         monitor.deregisterReguest(testObject);
 157                 }
 158         }
 159
 160         CachedRowSet executePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable
 161         {
 162                 long time = System.currentTimeMillis();
 163
 164                 CachedRowSet data = null;
 165                 if(LOGGER.isDebugEnabled()){
 166                         LOGGER.debug("SQL Statement: "+ statement);
 167                         if(arguments != null && !arguments.isEmpty()) {
 168                                 LOGGER.debug("Argunments: "+ Arrays.toString(arguments.toArray()));
 169                         }
 170                 }
 171
 172                 ResultSet rs = null;
 173                 try {
 174                         data = RowSetProvider.newFactory().createCachedRowSet();
 175                         PreparedStatement ps = conn.prepareStatement(statement);
 176                         if(arguments != null)
 177                         {
 178                                 for(int i = 0, max = arguments.size(); i < max; i++){
 179                                         ps.setObject(i+1, arguments.get(i));
 180                                 }
 181                         }
 182                         rs = ps.executeQuery();
 183                         data.populate(rs);
 184                     // Point the rowset Cursor to the start
 185                         if(LOGGER.isDebugEnabled()){
 186                                 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));                        }
 187                 } catch(SQLException exc){
 188                         if(LOGGER.isDebugEnabled()){
 189                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 190                         }
 191                         try {   conn.rollback(); } catch(Throwable thr){}
 192                         if(arguments != null && !arguments.isEmpty()) {
 193                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 194                         } else {
 195                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 196                         }
 197                         throw exc;
 198                 } catch(Throwable exc){
 199                         if(LOGGER.isDebugEnabled()){
 200                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 201                         }
 202                         if(arguments != null && !arguments.isEmpty()) {
 203                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 204                         } else {
 205                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 206                         }
 207                         throw exc; // new SQLException(exc);
 208                 } finally {
 209
 210                         try {
 211                                 if(rs != null){
 212                                         rs.close();
 213                                         rs = null;
 214                                 }
 215                         } catch(Exception exc){
 216
 217                         }
 218                         try {
 219                                 if(conn != null && close){
 220                                         conn.close();
 221                                         conn = null;
 222                                 }
 223                         } catch(Exception exc){
 224
 225                         }
 226                 }
 227
 228                 return data;
 229         }
 230
 231         boolean executeUpdatePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable {
 232                 long time = System.currentTimeMillis();
 233
 234                 CachedRowSet data = null;
 235
 236                 int rs = -1;
 237                 try {
 238                         data = RowSetProvider.newFactory().createCachedRowSet();
 239                         PreparedStatement ps = conn.prepareStatement(statement);
 240                         if(arguments != null)
 241                         {
 242                                 for(int i = 0, max = arguments.size(); i < max; i++){
 243                                         if(arguments.get(i) instanceof Blob) {
 244                                                 ps.setBlob(i+1, (Blob)arguments.get(i));
 245                                         } else  if(arguments.get(i) instanceof Timestamp) {
 246                                                 ps.setTimestamp(i+1, (Timestamp)arguments.get(i));
 247                                         } else  if(arguments.get(i) instanceof Integer) {
 248                                                 ps.setInt(i+1, (Integer)arguments.get(i));
 249                                         } else  if(arguments.get(i) instanceof Long) {
 250                                                 ps.setLong(i+1, (Long)arguments.get(i));
 251                                         } else  if(arguments.get(i) instanceof Date) {
 252                                                 ps.setDate(i+1, (Date)arguments.get(i));
 253                                         } else {
 254                                         ps.setObject(i+1, arguments.get(i));
 255                                 }
 256                         }
 257                         }
 258                         rs = ps.executeUpdate();
 259                     // Point the rowset Cursor to the start
 260                         if(LOGGER.isDebugEnabled()){
 261                                 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));
 262                         }
 263                 } catch(SQLException exc){
 264                         if(LOGGER.isDebugEnabled()){
 265                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 266                         }
 267                         try {   conn.rollback(); } catch(Throwable thr){}
 268                         if(arguments != null && !arguments.isEmpty()) {
 269                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 270                         } else {
 271                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 272                         }
 273                         throw exc;
 274                 } catch(Throwable exc){
 275                         if(LOGGER.isDebugEnabled()){
 276                                 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
 277                         }
 278                         if(arguments != null && !arguments.isEmpty()) {
 279                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
 280                         } else {
 281                                 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
 282                         }
 283                         throw exc; // new SQLException(exc);
 284                 } finally {
 285                         try {
 286                                 if(conn != null && close){
 287                                         conn.close();
 288                                         conn = null;
 289                                 }
 290                         } catch(Exception exc){
 291
 292                         }
 293                 }
 294
 295                 return true;
 296         }
 297
 298         /* (non-Javadoc)
 299          * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
 300          */
 301         public Connection getConnection(String username, String password)
 302                         throws SQLException
 303         {
 304                 return ds.getConnection(username, password);
 305         }
 306
 307         /* (non-Javadoc)
 308          * @see javax.sql.DataSource#getLogWriter()
 309          */
 310         public PrintWriter getLogWriter() throws SQLException
 311         {
 312                 return ds.getLogWriter();
 313         }
 314
 315         /* (non-Javadoc)
 316          * @see javax.sql.DataSource#getLoginTimeout()
 317          */
 318         public int getLoginTimeout() throws SQLException
 319         {
 320                 return ds.getLoginTimeout();
 321         }
 322
 323         /* (non-Javadoc)
 324          * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
 325          */
 326         public void setLogWriter(PrintWriter out) throws SQLException
 327         {
 328                 ds.setLogWriter(out);
 329         }
 330
 331         /* (non-Javadoc)
 332          * @see javax.sql.DataSource#setLoginTimeout(int)
 333          */
 334         public void setLoginTimeout(int seconds) throws SQLException
 335         {
 336                 ds.setLoginTimeout(seconds);
 337         }
 338
 339
 340         public final String getDbConnectionName(){
 341                 return connectionName;
 342         }
 343
 344         protected final void setDbConnectionName(String name) {
 345                 this.connectionName = name;
 346         }
 347
 348         public void cleanUp(){
 349                 if(ds != null && ds instanceof Closeable) {
 350                         try {
 351                                 ((Closeable)ds).close();
 352                         } catch (IOException e) {
 353                                 LOGGER.warn(e.getMessage());
 354                         }
 355                 }
 356                 ds = null;
 357                 monitor.deleteObservers();
 358                 monitor.cleanup();
 359         }
 360
 361         public boolean isInitialized() {
 362                 return initialized;
 363         }
 364
 365         protected boolean testConnection(){
 366                 return testConnection(false);
 367         }
 368
 369         protected boolean testConnection(boolean error_level){
 370                 Connection conn = null;
 371                 ResultSet rs = null;
 372                 Statement stmt = null;
 373                 try
 374                 {
 375                         Boolean readOnly = null;
 376                         String hostname = null;
 377                         conn = this.getConnection();
 378                         stmt = conn.createStatement();
 379                         rs = stmt.executeQuery("SELECT @@global.read_only, @@global.hostname");   //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
 380                         while(rs.next())
 381                         {
 382                                 readOnly = rs.getBoolean(1);
 383                                 hostname = rs.getString(2);
 384
 385                                         if(LOGGER.isDebugEnabled()){
 386                                                 LOGGER.debug("SQL DataSource <"+getDbConnectionName() + "> connected to " + hostname + ", read-only is " + readOnly + ", tested successfully ");
 387                                         }
 388                         }
 389
 390                 } catch (Throwable exc) {
 391                         if(error_level) {
 392                                 LOGGER.error("SQL DataSource <" + this.getDbConnectionName() +  "> test failed. Cause : " + exc.getMessage());
 393                         } else {
 394                                 LOGGER.info("SQL DataSource <" + this.getDbConnectionName() +   "> test failed. Cause : " + exc.getMessage());
 395                         }
 396                         return false;
 397                 } finally {
 398                         if(rs != null) {
 399                                 try {
 400                                         rs.close();
 401                                         rs = null;
 402                                 } catch (SQLException e) {
 403                                 }
 404                         }
 405                         if(stmt != null) {
 406                                 try {
 407                                         stmt.close();
 408                                         stmt = null;
 409                                 } catch (SQLException e) {
 410                                 }
 411                         }
 412                         if(conn !=null){
 413                                 try {
 414                                         conn.close();
 415                                         conn = null;
 416                                 } catch (SQLException e) {
 417                                 }
 418                         }
 419                 }
 420                 return true;
 421         }
 422
 423         public boolean isWrapperFor(Class<?> iface) throws SQLException {
 424                 return false;
 425         }
 426
 427         public <T> T unwrap(Class<T> iface) throws SQLException {
 428                 return null;
 429         }
 430
 431         @SuppressWarnings("deprecation")
 432         public void setConnectionCachingEnabled(boolean state)
 433         {
 434 //              if(ds != null && ds instanceof OracleDataSource)
 435 //                      try {
 436 //                              ((OracleDataSource)ds).setConnectionCachingEnabled(true);
 437 //                      } catch (SQLException exc) {
 438 //                              LOGGER.warn("", exc);
 439 //                      }
 440         }
 441
 442         public void addObserver(Observer observer) {
 443                 monitor.addObserver(observer);
 444         }
 445
 446         public void deleteObserver(Observer observer) {
 447                 monitor.deleteObserver(observer);
 448         }
 449
 450         public long getInterval() {
 451                 return interval;
 452         }
 453
 454         public long getInitialDelay() {
 455                 return initialDelay;
 456         }
 457
 458         public void setInterval(long value) {
 459                 interval = value;
 460         }
 461
 462         public void setInitialDelay(long value) {
 463                 initialDelay = value;
 464         }
 465
 466         public long getExpectedCompletionTime() {
 467                 return expectedCompletionTime;
 468         }
 469
 470         public void setExpectedCompletionTime(long value) {
 471                 expectedCompletionTime = value;
 472         }
 473
 474         public long getUnprocessedFailoverThreshold() {
 475                 return unprocessedFailoverThreshold;
 476         }
 477
 478         public void setUnprocessedFailoverThreshold(long value) {
 479                 this.unprocessedFailoverThreshold = value;
 480         }
 481
 482         public boolean canTakeOffLine() {
 483                 return canTakeOffLine;
 484         }
 485
 486         public void blockImmediateOffLine() {
 487                 canTakeOffLine = false;
 488                 final Thread offLineTimer = new Thread()
 489                 {
 490                         public void run(){
 491                                 try {
 492                                         Thread.sleep(30000L);
 493                                 }catch(Throwable exc){
 494
 495                                 }finally{
 496                                         canTakeOffLine = true;
 497                                 }
 498                         }
 499                 };
 500                 offLineTimer.setDaemon(true);
 501                 offLineTimer.start();
 502         }
 503
 504         /**
 505          * @return the monitor
 506          */
 507         final SQLExecutionMonitor getMonitor() {
 508                 return monitor;
 509         }
 510
 511         protected boolean isSlave() throws PoolExhaustedException, MySQLNonTransientConnectionException {
 512                 CachedRowSet rs = null;
 513                 boolean isSlave = true;
 514                 String hostname = "UNDETERMINED";
 515                 try {
 516                         boolean localSlave = true;
 517                         rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<Object>());
 518                         while(rs.next()) {
 519                                 localSlave = rs.getBoolean(1);
 520                                 hostname = rs.getString(2);
 521                         }
 522                         isSlave = localSlave;
 523                 } catch(PoolExhaustedException | MySQLNonTransientConnectionException peexc){
 524                         throw peexc;
 525                 } catch (SQLException e) {
 526                         LOGGER.error("", e);
 527                         isSlave = true;
 528                 } catch (Throwable e) {
 529                         LOGGER.error("", e);
 530                         isSlave = true;
 531                 }
 532                 if(isSlave){
 533                         LOGGER.debug("SQL SLAVE : "+connectionName + " on server " + hostname);
 534                 } else {
 535                         LOGGER.debug("SQL MASTER : "+connectionName + " on server " + hostname);
 536                 }
 537                 return isSlave;
 538         }
 539
 540         public boolean isFabric() {
 541                 return false;
 542         }
 543
 544         protected boolean lockTable(Connection conn, String tableName) {
 545                 boolean retValue = false;
 546                 Statement lock = null;
 547                 try {
 548                         if(tableName != null) {
 549                                 if(LOGGER.isDebugEnabled()) {
 550                                         LOGGER.debug("Executing 'LOCK TABLES " + tableName + " WRITE' on connection " + conn.toString());
 551                                         if("SVC_LOGIC".equals(tableName)) {
 552                                                 Exception e = new Exception();
 553                                                 StringWriter sw = new StringWriter();
 554                                                 PrintWriter pw = new PrintWriter(sw);
 555                                                 e.printStackTrace(pw);
 556                                                 LOGGER.debug(sw.toString());
 557                                         }
 558                                 }
 559                                 lock = conn.createStatement();
 560                                 lock.execute("LOCK TABLES " + tableName + " WRITE");
 561                                 retValue = true;
 562                         }
 563                 } catch(Exception exc){
 564                         LOGGER.error("", exc);
 565                         retValue =  false;
 566                 } finally {
 567                         try {
 568                                  lock.close();
 569                         } catch(Exception exc) {
 570
 571                         }
 572                 }
 573                 return retValue;
 574         }
 575
 576         protected boolean unlockTable(Connection conn) {
 577                 boolean retValue = false;
 578                 Statement lock = null;
 579                 try {
 580                         if(LOGGER.isDebugEnabled()) {
 581                                 LOGGER.debug("Executing 'UNLOCK TABLES' on connection " + conn.toString());
 582                         }
 583                         lock = conn.createStatement();
 584                         retValue = lock.execute("UNLOCK TABLES");
 585                 } catch(Exception exc){
 586                         LOGGER.error("", exc);
 587                         retValue =  false;
 588                 } finally {
 589                         try {
 590                                  lock.close();
 591                         } catch(Exception exc) {
 592
 593                         }
 594                 }
 595                 return retValue;
 596         }
 597
 598         public void getPoolInfo(boolean allocation) {
 599
 600         }
 601
 602         public long getNextErrorReportTime() {
 603                 return nextErrorReportTime;
 604         }
 605
 606         public void setNextErrorReportTime(long nextTime) {
 607                 this.nextErrorReportTime = nextTime;
 608         }
 609
 610         public void setGlobalHostName(String hostname) {
 611                 this.globalHostName  = hostname;
 612         }
 613
 614         public String getGlobalHostName() {
 615                 return globalHostName;
 616         }
 617 }