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