datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/utils/DB.java

   1 /*******************************************************************************\r
   2  * ============LICENSE_START==================================================\r
   3  * * org.onap.dmaap\r
   4  * * ===========================================================================\r
   5  * * Copyright © 2017 AT&T Intellectual Property. All rights reserved.\r
   6  * * ===========================================================================\r
   7  * * Licensed under the Apache License, Version 2.0 (the "License");\r
   8  * * you may not use this file except in compliance with the License.\r
   9  * * You may obtain a copy of the License at\r
  10  * * \r
  11  *  *      http://www.apache.org/licenses/LICENSE-2.0\r
  12  * * \r
  13  *  * Unless required by applicable law or agreed to in writing, software\r
  14  * * distributed under the License is distributed on an "AS IS" BASIS,\r
  15  * * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\r
  16  * * See the License for the specific language governing permissions and\r
  17  * * limitations under the License.\r
  18  * * ============LICENSE_END====================================================\r
  19  * *\r
  20  * * ECOMP is a trademark and service mark of AT&T Intellectual Property.\r
  21  * *\r
  22  ******************************************************************************/\r
  23 \r
  24 \r
  25 package org.onap.dmaap.datarouter.provisioning.utils;\r
  26 \r
  27 import java.io.File;\r
  28 import java.io.FileReader;\r
  29 import java.io.IOException;\r
  30 import java.io.InputStream;\r
  31 import java.io.LineNumberReader;\r
  32 import java.lang.reflect.Constructor;\r
  33 import java.lang.reflect.InvocationTargetException;\r
  34 import java.sql.Connection;\r
  35 import java.sql.DatabaseMetaData;\r
  36 import java.sql.DriverManager;\r
  37 import java.sql.PreparedStatement;\r
  38 import java.sql.ResultSet;\r
  39 import java.sql.SQLException;\r
  40 import java.sql.Statement;\r
  41 import java.util.HashSet;\r
  42 import java.util.LinkedList;\r
  43 import java.util.NoSuchElementException;\r
  44 import java.util.Properties;\r
  45 import java.util.Queue;\r
  46 import java.util.Set;\r
  47 \r
  48 import org.apache.log4j.Logger;\r
  49 import org.onap.dmaap.datarouter.provisioning.beans.DeliveryRecord;\r
  50 import org.onap.dmaap.datarouter.provisioning.beans.ExpiryRecord;\r
  51 import org.onap.dmaap.datarouter.provisioning.beans.Loadable;\r
  52 import org.onap.dmaap.datarouter.provisioning.beans.PublishRecord;\r
  53 \r
  54 /**\r
  55  * Load the DB JDBC driver, and manage a simple pool of connections to the DB.\r
  56  *\r
  57  * @author Robert Eby\r
  58  * @version $Id$\r
  59  */\r
  60 public class DB {\r
  61         /** The name of the properties file (in CLASSPATH) */\r
  62         public static final String CONFIG_FILE = "provserver.properties";\r
  63 \r
  64         private static String DB_DRIVER   = "com.mysql.jdbc.Driver";\r
  65         private static String DB_URL      = "jdbc:mysql://127.0.0.1:3306/datarouter";\r
  66         private static String DB_LOGIN    = "datarouter";\r
  67         private static String DB_PASSWORD = "datarouter";\r
  68         private static Properties props;\r
  69         private static Logger intlogger = Logger.getLogger("org.onap.dmaap.datarouter.provisioning.internal");\r
  70         private static Queue<Connection> queue = new LinkedList<Connection>();\r
  71 \r
  72         public static String HTTPS_PORT;\r
  73         public static String HTTP_PORT;\r
  74 \r
  75         /**\r
  76          * Construct a DB object.  If this is the very first creation of this object, it will load a copy\r
  77          * of the properties for the server, and attempt to load the JDBC driver for the database.  If a fatal\r
  78          * error occurs (e.g. either the properties file or the DB driver is missing), the JVM will exit.\r
  79          */\r
  80         public DB() {\r
  81                 if (props == null) {\r
  82                         props = new Properties();\r
  83                         InputStream inStream = getClass().getClassLoader().getResourceAsStream(CONFIG_FILE);\r
  84                         try {\r
  85                                 props.load(inStream);\r
  86                                 DB_DRIVER   = (String) props.get("org.onap.dmaap.datarouter.db.driver");\r
  87                                 DB_URL      = (String) props.get("org.onap.dmaap.datarouter.db.url");\r
  88                                 DB_LOGIN    = (String) props.get("org.onap.dmaap.datarouter.db.login");\r
  89                                 DB_PASSWORD = (String) props.get("org.onap.dmaap.datarouter.db.password");\r
  90                                 HTTPS_PORT = (String) props.get("org.onap.dmaap.datarouter.provserver.https.port");\r
  91                                 HTTP_PORT = (String) props.get("org.onap.dmaap.datarouter.provserver.http.port");\r
  92                                 Class.forName(DB_DRIVER);\r
  93                         } catch (IOException e) {\r
  94                                 intlogger.fatal("PROV9003 Opening properties: "+e.getMessage());\r
  95                                 e.printStackTrace();\r
  96                                 System.exit(1);\r
  97                         } catch (ClassNotFoundException e) {\r
  98                                 intlogger.fatal("PROV9004 cannot find the DB driver: "+e);\r
  99                                 e.printStackTrace();\r
 100                                 System.exit(1);\r
 101                         } finally {\r
 102                                 try {\r
 103                                         inStream.close();\r
 104                                 } catch (IOException e) {\r
 105                                 }\r
 106                         }\r
 107                 }\r
 108         }\r
 109         /**\r
 110          * Get the provisioning server properties (loaded from provserver.properties).\r
 111          * @return the Properties object\r
 112          */\r
 113         public Properties getProperties() {\r
 114                 return props;\r
 115         }\r
 116         /**\r
 117          * Get a JDBC connection to the DB from the pool.  Creates a new one if none are available.\r
 118          * @return the Connection\r
 119          * @throws SQLException\r
 120          */\r
 121         @SuppressWarnings("resource")\r
 122         public Connection getConnection() throws SQLException {\r
 123                 Connection c = null;\r
 124                 while (c == null) {\r
 125                         synchronized (queue) {\r
 126                                 try {\r
 127                                         c = queue.remove();\r
 128                                 } catch (NoSuchElementException e) {\r
 129                                         int n = 0;\r
 130                                         do {\r
 131                                                 // Try up to 3 times to get a connection\r
 132                                                 try {\r
 133                                                         c = DriverManager.getConnection(DB_URL, DB_LOGIN, DB_PASSWORD);\r
 134                                                 } catch (SQLException e1) {\r
 135                                                         if (++n >= 3)\r
 136                                                                 throw e1;\r
 137                                                 }\r
 138                                         } while (c == null);\r
 139                                 }\r
 140                         }\r
 141                         if (c != null && !c.isValid(1)) {\r
 142                                 c.close();\r
 143                                 c = null;\r
 144                         }\r
 145                 }\r
 146                 return c;\r
 147         }\r
 148         /**\r
 149          * Returns a JDBC connection to the pool.\r
 150          * @param c the Connection to return\r
 151          * @throws SQLException\r
 152          */\r
 153         public void release(Connection c) {\r
 154                 if (c != null) {\r
 155                         synchronized (queue) {\r
 156                                 if (!queue.contains(c))\r
 157                                         queue.add(c);\r
 158                         }\r
 159                 }\r
 160         }\r
 161 \r
 162         /**\r
 163          * Run all necessary retrofits required to bring the database up to the level required for this version\r
 164          * of the provisioning server.  This should be run before the server itself is started.\r
 165          * @return true if all retrofits worked, false otherwise\r
 166          */\r
 167         public boolean runRetroFits() {\r
 168                 return retroFit1()\r
 169                         && retroFit2()\r
 170                         && retroFit3()\r
 171                         && retroFit4()\r
 172                         && retroFit5()\r
 173                         && retroFit6()\r
 174                         && retroFit7()\r
 175                         && retroFit8()\r
 176                         && retroFit9()  //New retroFit call to add CREATED_DATE column Rally:US674199 - 1610\r
 177                         && retroFit10() //New retroFit call to add BUSINESS_DESCRIPTION column Rally:US708102 - 1610\r
 178                         && retroFit11() //New retroFit call for groups feature Rally:US708115 - 1610    \r
 179                         ;\r
 180         }\r
 181         /**\r
 182          * Retrofit 1 - Make sure the expected tables are in MySQL and are initialized.\r
 183          * Uses mysql_init_0000 and mysql_init_0001 to setup the DB.\r
 184          * @return true if the retrofit worked, false otherwise\r
 185          */\r
 186         private boolean retroFit1() {\r
 187                 final String[] expected_tables = {\r
 188                         "FEEDS", "FEED_ENDPOINT_ADDRS", "FEED_ENDPOINT_IDS", "PARAMETERS", "SUBSCRIPTIONS"\r
 189                 };\r
 190                 Connection c = null;\r
 191                 try {\r
 192                         c = getConnection();\r
 193                         Set<String> tables = getTableSet(c);\r
 194                         boolean initialize = false;\r
 195                         for (String s : expected_tables) {\r
 196                                 initialize |= !tables.contains(s);\r
 197                         }\r
 198                         if (initialize) {\r
 199                                 intlogger.info("PROV9001: First time startup; The database is being initialized.");\r
 200                                 runInitScript(c, 0);            // script 0 creates the provisioning tables\r
 201                                 runInitScript(c, 1);            // script 1 initializes PARAMETERS\r
 202                         }\r
 203                 } catch (SQLException e) {\r
 204                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 205                         return false;\r
 206                 } finally {\r
 207                         if (c != null)\r
 208                                 release(c);\r
 209                 }\r
 210                 return true;\r
 211         }\r
 212         /**\r
 213          * Retrofit 2 - if the LOG_RECORDS table is missing, add it.\r
 214          * Uses mysql_init_0002 to create this table.\r
 215          * @return true if the retrofit worked, false otherwise\r
 216          */\r
 217         private boolean retroFit2() {\r
 218                 Connection c = null;\r
 219                 try {\r
 220                         // If LOG_RECORDS table is missing, add it\r
 221                         c = getConnection();\r
 222                         Set<String> tables = getTableSet(c);\r
 223                         if (!tables.contains("LOG_RECORDS")) {\r
 224                                 intlogger.info("PROV9002: Creating LOG_RECORDS table.");\r
 225                                 runInitScript(c, 2);            // script 2 creates the LOG_RECORDS table\r
 226                         }\r
 227                 } catch (SQLException e) {\r
 228                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 229                         return false;\r
 230                 } finally {\r
 231                         if (c != null)\r
 232                                 release(c);\r
 233                 }\r
 234                 return true;\r
 235         }\r
 236         /**\r
 237          * Retrofit 3 - if the FEEDS_UNIQUEID table (from release 1.0.*) exists, drop it.\r
 238          * If SUBSCRIPTIONS.SUBID still has the auto_increment attribute, remove it.\r
 239          * @return true if the retrofit worked, false otherwise\r
 240          */\r
 241         @SuppressWarnings("resource")\r
 242         private boolean retroFit3() {\r
 243                 Connection c = null;\r
 244                 try {\r
 245                         // if SUBSCRIPTIONS.SUBID still has auto_increment, remove it\r
 246                         boolean doremove = false;\r
 247                         c = getConnection();\r
 248                         DatabaseMetaData md = c.getMetaData();\r
 249                         ResultSet rs = md.getColumns("datarouter", "", "SUBSCRIPTIONS", "SUBID");\r
 250                         if (rs != null) {\r
 251                                 while (rs.next()) {\r
 252                                         doremove = rs.getString("IS_AUTOINCREMENT").equals("YES");\r
 253                                 }\r
 254                                 rs.close();\r
 255                                 rs = null;\r
 256                         }\r
 257                         if (doremove) {\r
 258                                 intlogger.info("PROV9002: Modifying SUBSCRIPTIONS SUBID column to remove auto increment.");\r
 259                                 Statement s = c.createStatement();\r
 260                                 s.execute("ALTER TABLE SUBSCRIPTIONS MODIFY COLUMN SUBID INT UNSIGNED NOT NULL");\r
 261                                 s.close();\r
 262                         }\r
 263 \r
 264                         // Remove the FEEDS_UNIQUEID table, if it exists\r
 265                         Set<String> tables = getTableSet(c);\r
 266                         if (tables.contains("FEEDS_UNIQUEID")) {\r
 267                                 intlogger.info("PROV9002: Dropping FEEDS_UNIQUEID table.");\r
 268                                 Statement s = c.createStatement();\r
 269                                 s.execute("DROP TABLE FEEDS_UNIQUEID");\r
 270                                 s.close();\r
 271                         }\r
 272                 } catch (SQLException e) {\r
 273                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 274                         return false;\r
 275                 } finally {\r
 276                         if (c != null)\r
 277                                 release(c);\r
 278                 }\r
 279                 return true;\r
 280         }\r
 281         private long nextid = 0;        // used for initial creation of LOG_RECORDS table.\r
 282         /**\r
 283          * Retrofit 4 - if old log tables exist (from release 1.0.*), copy them to LOG_RECORDS, then drop them.\r
 284          * @return true if the retrofit worked, false otherwise\r
 285          */\r
 286         @SuppressWarnings("resource")\r
 287         private boolean retroFit4() {\r
 288                 Connection c = null;\r
 289                 try {\r
 290                         c = getConnection();\r
 291                         Set<String> tables = getTableSet(c);\r
 292                         if (tables.contains("PUBLISH_RECORDS")) {\r
 293                                 intlogger.info("PROV9002: Copying PUBLISH_RECORDS to LOG_RECORDS table.");\r
 294                                 copyLogTable("PUBLISH_RECORDS", PublishRecord.class);\r
 295                                 intlogger.info("PROV9002: Dropping PUBLISH_RECORDS table.");\r
 296                                 Statement s = c.createStatement();\r
 297                                 s.execute("DROP TABLE PUBLISH_RECORDS");\r
 298                                 s.close();\r
 299                         }\r
 300                         if (tables.contains("DELIVERY_RECORDS")) {\r
 301                                 intlogger.info("PROV9002: Copying DELIVERY_RECORDS to LOG_RECORDS table.");\r
 302                                 copyLogTable("DELIVERY_RECORDS", DeliveryRecord.class);\r
 303                                 intlogger.info("PROV9002: Dropping DELIVERY_RECORDS table.");\r
 304                                 Statement s = c.createStatement();\r
 305                                 s.execute("DROP TABLE DELIVERY_RECORDS");\r
 306                                 s.close();\r
 307                         }\r
 308                         if (tables.contains("EXPIRY_RECORDS")) {\r
 309                                 intlogger.info("PROV9002: Copying EXPIRY_RECORDS to LOG_RECORDS table.");\r
 310                                 copyLogTable("EXPIRY_RECORDS", ExpiryRecord.class);\r
 311                                 intlogger.info("PROV9002: Dropping EXPIRY_RECORDS table.");\r
 312                                 Statement s = c.createStatement();\r
 313                                 s.execute("DROP TABLE EXPIRY_RECORDS");\r
 314                                 s.close();\r
 315                         }\r
 316                 } catch (SQLException e) {\r
 317                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 318                         return false;\r
 319                 } finally {\r
 320                         if (c != null)\r
 321                                 release(c);\r
 322                 }\r
 323                 return true;\r
 324         }\r
 325         /**\r
 326          * Retrofit 5 - Create the new routing tables required for Release 2.\r
 327          * Adds a new "SUSPENDED" column to FEEDS and SUBSCRIPTIONS.\r
 328          * Modifies the LOG_RECORDS table to handle new R2 records.\r
 329          * @return true if the retrofit worked, false otherwise\r
 330          */\r
 331         @SuppressWarnings("resource")\r
 332         private boolean retroFit5() {\r
 333                 final String[] expected_tables = {\r
 334                         "INGRESS_ROUTES", "EGRESS_ROUTES", "NETWORK_ROUTES", "NODESETS", "NODES"\r
 335                 };\r
 336                 Connection c = null;\r
 337                 try {\r
 338                         // If expected tables are not present, then add new routing tables\r
 339                         c = getConnection();\r
 340                         Set<String> tables = getTableSet(c);\r
 341                         boolean initialize = false;\r
 342                         for (String s : expected_tables) {\r
 343                                 initialize |= !tables.contains(s);\r
 344                         }\r
 345                         if (initialize) {\r
 346                                 intlogger.info("PROV9002: Adding routing tables for Release 2.0.");\r
 347                                 runInitScript(c, 3);            // script 3 creates the routing tables\r
 348                         }\r
 349 \r
 350                         // Add SUSPENDED column to FEEDS/SUBSCRIPTIONS\r
 351                         DatabaseMetaData md = c.getMetaData();\r
 352                         for (String tbl : new String[] {"FEEDS", "SUBSCRIPTIONS" }) {\r
 353                                 boolean add_col = true;\r
 354                                 ResultSet rs = md.getColumns("datarouter", "", tbl, "SUSPENDED");\r
 355                                 if (rs != null) {\r
 356                                         add_col = !rs.next();\r
 357                                         rs.close();\r
 358                                         rs = null;\r
 359                                 }\r
 360                                 if (add_col) {\r
 361                                         intlogger.info("PROV9002: Adding SUSPENDED column to "+tbl+" table.");\r
 362                                         Statement s = c.createStatement();\r
 363                                         s.execute("ALTER TABLE "+tbl+" ADD COLUMN SUSPENDED BOOLEAN DEFAULT FALSE");\r
 364                                         s.close();\r
 365                                 }\r
 366                         }\r
 367 \r
 368                         // Modify LOG_RECORDS for R2\r
 369                         intlogger.info("PROV9002: Modifying LOG_RECORDS table.");\r
 370                         Statement s = c.createStatement();\r
 371                         s.execute("ALTER TABLE LOG_RECORDS MODIFY COLUMN TYPE ENUM('pub', 'del', 'exp', 'pbf', 'dlx') NOT NULL");\r
 372                         s.close();\r
 373                         s = c.createStatement();\r
 374                         s.execute("ALTER TABLE LOG_RECORDS MODIFY COLUMN REASON ENUM('notRetryable', 'retriesExhausted', 'diskFull', 'other')");\r
 375                         s.close();\r
 376                         boolean add_col = true;\r
 377                         ResultSet rs = md.getColumns("datarouter", "", "LOG_RECORDS", "CONTENT_LENGTH_2");\r
 378                         if (rs != null) {\r
 379                                 add_col = !rs.next();\r
 380                                 rs.close();\r
 381                                 rs = null;\r
 382                         }\r
 383                         if (add_col) {\r
 384                                 intlogger.info("PROV9002: Fixing two columns in LOG_RECORDS table (this may take some time).");\r
 385                                 s = c.createStatement();\r
 386                                 s.execute("ALTER TABLE LOG_RECORDS MODIFY COLUMN CONTENT_LENGTH BIGINT NOT NULL, ADD COLUMN CONTENT_LENGTH_2 BIGINT AFTER RECORD_ID");\r
 387                                 s.close();\r
 388                         }\r
 389                 } catch (SQLException e) {\r
 390                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 391                         return false;\r
 392                 } finally {\r
 393                         if (c != null)\r
 394                                 release(c);\r
 395                 }\r
 396                 return true;\r
 397         }\r
 398         /**\r
 399          * Retrofit 6 - Adjust LOG_RECORDS.USER to be 50 chars (MR #74).\r
 400          * @return true if the retrofit worked, false otherwise\r
 401          */\r
 402         @SuppressWarnings("resource")\r
 403         private boolean retroFit6() {\r
 404                 Connection c = null;\r
 405                 try {\r
 406                         c = getConnection();\r
 407                         // Modify LOG_RECORDS for R2\r
 408                         intlogger.info("PROV9002: Modifying LOG_RECORDS.USER length.");\r
 409                         Statement s = c.createStatement();\r
 410                         s.execute("ALTER TABLE LOG_RECORDS MODIFY COLUMN USER VARCHAR(50)");\r
 411                         s.close();\r
 412                 } catch (SQLException e) {\r
 413                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 414                         return false;\r
 415                 } finally {\r
 416                         if (c != null)\r
 417                                 release(c);\r
 418                 }\r
 419                 return true;\r
 420         }\r
 421         /**\r
 422          * Retrofit 7 - Adjust LOG_RECORDS.FEED_FILEID and LOG_RECORDS.DELIVERY_FILEID to be 256 chars.\r
 423          * @return true if the retrofit worked, false otherwise\r
 424          */\r
 425         @SuppressWarnings("resource")\r
 426         private boolean retroFit7() {\r
 427                 Connection c = null;\r
 428                 try {\r
 429                         c = getConnection();\r
 430                         // Modify LOG_RECORDS for long (>128) FILEIDs\r
 431                         intlogger.info("PROV9002: Modifying LOG_RECORDS.USER length.");\r
 432                         Statement s = c.createStatement();\r
 433                         s.execute("ALTER TABLE LOG_RECORDS MODIFY COLUMN FEED_FILEID VARCHAR(256), MODIFY COLUMN DELIVERY_FILEID VARCHAR(256)");\r
 434                         s.close();\r
 435                 } catch (SQLException e) {\r
 436                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 437                         return false;\r
 438                 } finally {\r
 439                         if (c != null)\r
 440                                 release(c);\r
 441                 }\r
 442                 return true;\r
 443         }\r
 444         /**\r
 445          * Retrofit 8 - Adjust FEEDS.NAME to be 255 chars (MR #74).\r
 446          * @return true if the retrofit worked, false otherwise\r
 447          */\r
 448         @SuppressWarnings("resource")\r
 449         private boolean retroFit8() {\r
 450                 Connection c = null;\r
 451                 try {\r
 452                         c = getConnection();\r
 453                         intlogger.info("PROV9002: Modifying FEEDS.NAME length.");\r
 454                         Statement s = c.createStatement();\r
 455                         s.execute("ALTER TABLE FEEDS MODIFY COLUMN NAME VARCHAR(255)");\r
 456                         s.close();\r
 457                 } catch (SQLException e) {\r
 458                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());\r
 459                         return false;\r
 460                 } finally {\r
 461                         if (c != null)\r
 462                                 release(c);\r
 463                 }\r
 464                 return true;\r
 465         }\r
 466         \r
 467         /**\r
 468          * Retrofit 9 - Add column FEEDS.CREATED_DATE and SUBSCRIPTIONS.CREATED_DATE, 1610 release user story US674199.\r
 469          * @return true if the retrofit worked, false otherwise\r
 470          */\r
 471 \r
 472         @SuppressWarnings("resource")           \r
 473         private boolean retroFit9() {           \r
 474                 Connection c = null;            \r
 475                 try {           \r
 476                         c = getConnection();            \r
 477                         // Add CREATED_DATE column to FEEDS/SUBSCRIPTIONS tables\r
 478                         DatabaseMetaData md = c.getMetaData();          \r
 479                         for (String tbl : new String[] {"FEEDS", "SUBSCRIPTIONS" }) {           \r
 480                                 boolean add_col = true;         \r
 481                                 ResultSet rs = md.getColumns("datarouter", "", tbl, "CREATED_DATE");            \r
 482                                 if (rs != null) {               \r
 483                                         add_col = !rs.next();           \r
 484                                         rs.close();             \r
 485                                         rs = null;              \r
 486                                 }               \r
 487                                 if (add_col) {          \r
 488                                         intlogger.info("PROV9002: Adding CREATED_DATE column to "+tbl+" table.");               \r
 489                                         Statement s = c.createStatement();\r
 490                                         s.execute("ALTER TABLE "+tbl+" ADD COLUMN CREATED_DATE timestamp DEFAULT CURRENT_TIMESTAMP");           \r
 491                                         s.close();              \r
 492                                 }               \r
 493                         }                                               \r
 494                 } catch (SQLException e) {              \r
 495                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());         \r
 496                         return false;           \r
 497                 } finally {             \r
 498                         if (c != null)          \r
 499                                 release(c);             \r
 500                 }               \r
 501                 return true;            \r
 502         }\r
 503 \r
 504         /**\r
 505          * Retrofit 10 -Adding business BUSINESS_DESCRIPTION to FEEDS table (Rally\r
 506          * US708102).\r
 507          * \r
 508          * @return true if the retrofit worked, false otherwise\r
 509          */\r
 510 \r
 511         @SuppressWarnings("resource")\r
 512         private boolean retroFit10() {\r
 513                 Connection c = null;\r
 514                 boolean addColumn = true;\r
 515                 \r
 516                 try {\r
 517 \r
 518                         c = getConnection();            \r
 519                         // Add BUSINESS_DESCRIPTION column to FEEDS table\r
 520                         DatabaseMetaData md = c.getMetaData();          \r
 521                                 boolean add_col = true;         \r
 522                                 ResultSet rs = md.getColumns("datarouter", "", "FEEDS", "BUSINESS_DESCRIPTION");                \r
 523                                 if (rs != null) {               \r
 524                                         add_col = !rs.next();           \r
 525                                         rs.close();             \r
 526                                         rs = null;              \r
 527                                 }       \r
 528                 if(add_col) {\r
 529                         intlogger\r
 530                                         .info("PROV9002: Adding BUSINESS_DESCRIPTION column to FEEDS table.");\r
 531                         Statement s = c.createStatement();\r
 532                         s.execute("ALTER TABLE FEEDS ADD COLUMN BUSINESS_DESCRIPTION varchar(1000) DEFAULT NULL AFTER DESCRIPTION, MODIFY COLUMN DESCRIPTION VARCHAR(1000)");\r
 533                         s.close();\r
 534                         }\r
 535                 }\r
 536                 catch (SQLException e) {\r
 537                         intlogger\r
 538                                         .fatal("PROV9000: The database credentials are not working: "\r
 539                                                         + e.getMessage());\r
 540                         return false;\r
 541                 } finally {\r
 542                         if (c != null)\r
 543                                 release(c);\r
 544                 }\r
 545                 return true;\r
 546         }\r
 547 \r
 548 \r
 549         /*New retroFit method is added for groups feature Rally:US708115 - 1610 \r
 550         * @retroFit11()\r
 551         * @parmas: none\r
 552         * @return - boolean if table and fields are created (Group table, group id in FEEDS, SUBSCRIPTION TABLES)\r
 553         */\r
 554         @SuppressWarnings("resource")   \r
 555         private boolean retroFit11() {          \r
 556                 final String[] expected_tables = {              \r
 557                         "GROUPS"                \r
 558                 };              \r
 559                 Connection c = null;            \r
 560                         \r
 561                 try {           \r
 562                         // If expected tables are not present, then add new routing tables              \r
 563                         c = getConnection();            \r
 564                         Set<String> tables = getTableSet(c);            \r
 565                         boolean initialize = false;             \r
 566                         for (String s : expected_tables) {              \r
 567                                 initialize |= !tables.contains(s);              \r
 568                         }               \r
 569                         if (initialize) {               \r
 570                                 intlogger.info("PROV9002: Adding GROUPS table for Release 1610.");              \r
 571                                 runInitScript(c, 4);            // script 4 creates the routing tables          \r
 572                         }               \r
 573                                         \r
 574                         // Add GROUPID column to FEEDS/SUBSCRIPTIONS            \r
 575                         DatabaseMetaData md = c.getMetaData();          \r
 576                         for (String tbl : new String[] {"FEEDS", "SUBSCRIPTIONS" }) {           \r
 577                                 boolean add_col = true;         \r
 578                                 ResultSet rs = md.getColumns("datarouter", "", tbl, "GROUPID");         \r
 579                                 if (rs != null) {               \r
 580                                         add_col = !rs.next();           \r
 581                                         rs.close();             \r
 582                                         rs = null;              \r
 583                                 }               \r
 584                                 if (add_col) {          \r
 585                                         intlogger.info("PROV9002: Adding GROUPID column to "+tbl+" table.");            \r
 586                                         Statement s = c.createStatement();              \r
 587                                         s.execute("ALTER TABLE "+tbl+" ADD COLUMN GROUPID INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER FEEDID");           \r
 588                                         s.close();              \r
 589                                 }               \r
 590                         }                                               \r
 591                 } catch (SQLException e) {              \r
 592                         intlogger.fatal("PROV9000: The database credentials are not working: "+e.getMessage());         \r
 593                         return false;           \r
 594                 } finally {             \r
 595                         if (c != null)          \r
 596                                 release(c);             \r
 597                 }               \r
 598                 return true;            \r
 599         }\r
 600 \r
 601 \r
 602         /**\r
 603          * Copy the log table <i>table_name</i> to LOG_RECORDS;\r
 604          * @param table_name the name of the old (1.0.*) table to copy\r
 605          * @param table_class the class used to instantiate a record from the table\r
 606          * @throws SQLException if there is a problem getting a MySQL connection\r
 607          */\r
 608         @SuppressWarnings("resource")\r
 609         private void copyLogTable(String table_name, Class<? extends Loadable> table_class) throws SQLException {\r
 610                 long start = System.currentTimeMillis();\r
 611                 int n = 0;\r
 612                 Connection c1 = getConnection();\r
 613                 Connection c2 = getConnection();\r
 614 \r
 615                 try {\r
 616                         Constructor<? extends Loadable> cnst = table_class.getConstructor(ResultSet.class);\r
 617                         PreparedStatement ps = c2.prepareStatement(LogfileLoader.INSERT_SQL);\r
 618                         Statement stmt = c1.createStatement();\r
 619                         ResultSet rs = stmt.executeQuery("select * from "+table_name);\r
 620                         while (rs.next()) {\r
 621                                 Loadable rec = cnst.newInstance(rs);\r
 622                                 rec.load(ps);\r
 623                                 ps.setLong(18, ++nextid);\r
 624                                 ps.executeUpdate();\r
 625                                 if ((++n % 10000) == 0)\r
 626                                         intlogger.debug("  "+n+" records done.");\r
 627                         }\r
 628                         stmt.close();\r
 629                         ps.close();\r
 630                 } catch (SQLException e) {\r
 631                         e.printStackTrace();\r
 632                 } catch (NoSuchMethodException e) {\r
 633                         e.printStackTrace();\r
 634                 } catch (SecurityException e) {\r
 635                         e.printStackTrace();\r
 636                 } catch (InstantiationException e) {\r
 637                         e.printStackTrace();\r
 638                 } catch (IllegalAccessException e) {\r
 639                         e.printStackTrace();\r
 640                 } catch (IllegalArgumentException e) {\r
 641                         e.printStackTrace();\r
 642                 } catch (InvocationTargetException e) {\r
 643                         e.printStackTrace();\r
 644                 }\r
 645 \r
 646                 release(c1);\r
 647                 release(c2);\r
 648                 long x = (System.currentTimeMillis() - start);\r
 649                 intlogger.debug("  "+n+" records done in "+x+" ms.");\r
 650         }\r
 651 \r
 652         /**\r
 653          * Get a set of all table names in the DB.\r
 654          * @param c a DB connection\r
 655          * @return the set of table names\r
 656          */\r
 657         private Set<String> getTableSet(Connection c) {\r
 658                 Set<String> tables = new HashSet<String>();\r
 659                 try {\r
 660                         DatabaseMetaData md = c.getMetaData();\r
 661                         ResultSet rs = md.getTables("datarouter", "", "", null);\r
 662                         if (rs != null) {\r
 663                                 while (rs.next()) {\r
 664                                         tables.add(rs.getString("TABLE_NAME"));\r
 665                                 }\r
 666                                 rs.close();\r
 667                         }\r
 668                 } catch (SQLException e) {\r
 669                 }\r
 670                 return tables;\r
 671         }\r
 672         /**\r
 673          * Initialize the tables by running the initialization scripts located in the directory specified\r
 674          * by the property <i>org.onap.dmaap.datarouter.provserver.dbscripts</i>.  Scripts have names of\r
 675          * the form mysql_init_NNNN.\r
 676          * @param c a DB connection\r
 677          * @param n the number of the mysql_init_NNNN script to run\r
 678          */\r
 679         private void runInitScript(Connection c, int n) {\r
 680                 String scriptdir = (String) props.get("org.onap.dmaap.datarouter.provserver.dbscripts");\r
 681                 StringBuilder sb = new StringBuilder();\r
 682                 try {\r
 683                         String scriptfile = String.format("%s/mysql_init_%04d", scriptdir, n);\r
 684                         if (!(new File(scriptfile)).exists())\r
 685                                 return;\r
 686 \r
 687                         LineNumberReader in = new LineNumberReader(new FileReader(scriptfile));\r
 688                         String line;\r
 689                         while ((line = in.readLine()) != null) {\r
 690                                 if (!line.startsWith("--")) {\r
 691                                         line = line.trim();\r
 692                                         sb.append(line);\r
 693                                         if (line.endsWith(";")) {\r
 694                                                 // Execute one DDL statement\r
 695                                                 String sql = sb.toString();\r
 696                                                 sb.setLength(0);\r
 697                                                 Statement s = c.createStatement();\r
 698                                                 s.execute(sql);\r
 699                                                 s.close();\r
 700                                         }\r
 701                                 }\r
 702                         }\r
 703                         in.close();\r
 704                         sb.setLength(0);\r
 705                 } catch (Exception e) {\r
 706                         intlogger.fatal("PROV9002 Error when initializing table: "+e.getMessage());\r
 707                         System.exit(1);\r
 708                 }\r
 709         }\r
 710 }\r