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