001 package org.hackystat.sensorbase.db.derby; 002 003 import static org.hackystat.sensorbase.server.ServerProperties.DB_DIR_KEY; 004 005 import java.math.BigInteger; 006 import java.sql.CallableStatement; 007 import java.sql.Connection; 008 import java.sql.DriverManager; 009 import java.sql.PreparedStatement; 010 import java.sql.ResultSet; 011 import java.sql.SQLException; 012 import java.sql.Statement; 013 import java.sql.Timestamp; 014 import java.util.ArrayList; 015 import java.util.Date; 016 import java.util.HashMap; 017 import java.util.HashSet; 018 import java.util.Iterator; 019 import java.util.List; 020 import java.util.Map; 021 import java.util.Set; 022 023 import javax.xml.datatype.XMLGregorianCalendar; 024 025 import org.hackystat.sensorbase.db.DbImplementation; 026 import org.hackystat.utilities.stacktrace.StackTrace; 027 import org.hackystat.utilities.tstamp.Tstamp; 028 import org.hackystat.sensorbase.resource.projects.jaxb.Project; 029 import org.hackystat.sensorbase.resource.projects.jaxb.ProjectSummary; 030 import org.hackystat.sensorbase.resource.projects.jaxb.SensorDataSummaries; 031 import org.hackystat.sensorbase.resource.projects.jaxb.SensorDataSummary; 032 import org.hackystat.sensorbase.resource.sensordata.jaxb.SensorData; 033 import org.hackystat.sensorbase.resource.sensordatatypes.jaxb.SensorDataType; 034 import org.hackystat.sensorbase.resource.users.jaxb.User; 035 import org.hackystat.sensorbase.server.Server; 036 037 038 /** 039 * Provides a implementation of DbImplementation using Derby in embedded mode. 040 * 041 * Note: If you are using this implementation as a guide for implementing an alternative database, 042 * you should be aware that this implementation does not do connection pooling. It turns out 043 * that embedded Derby does not require connection pooling, so it is not present in this code. 044 * You will probably want it for your version, of course. 045 * 046 * @author Philip Johnson 047 */ 048 public class DerbyImplementation extends DbImplementation { 049 050 /** The JDBC driver. */ 051 private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver"; 052 053 /** The Database name. */ 054 private static final String dbName = "sensorbase"; 055 056 /** The Derby connection URL. */ 057 private static final String connectionURL = "jdbc:derby:" + dbName + ";create=true"; 058 059 /** Indicates whether this database was initialized or was pre-existing. */ 060 private boolean isFreshlyCreated; 061 062 /** The SQL state indicating that INSERT tried to add data to a table with a preexisting key. */ 063 private static final String DUPLICATE_KEY = "23505"; 064 065 /** The key for putting/retrieving the directory where Derby will create its databases. */ 066 private static final String derbySystemKey = "derby.system.home"; 067 068 /** The logger message for connection closing errors. */ 069 private static final String errorClosingMsg = "Derby: Error while closing. \n"; 070 071 /** The logger message when executing a query. */ 072 private static final String executeQueryMsg = "Derby: Executing query "; 073 074 /** Required by PMD since this string occurs multiple times in this file. */ 075 private static final String ownerEquals = " owner = '"; 076 077 /** Required by PMD since this string occurs multiple times in this file. */ 078 private static final String sdtEquals = " sdt = '"; 079 private static final String toolEquals = " tool = '"; 080 081 /** Required by PMD as above. */ 082 private static final String quoteAndClause = "' AND "; 083 private static final String andClause = " AND "; 084 private static final String selectPrefix = "SELECT XmlSensorDataRef FROM SensorData WHERE "; 085 private static final String selectSnapshot = 086 "SELECT XmlSensorDataRef, Runtime, Tool FROM SensorData WHERE "; 087 private static final String orderByTstamp = " ORDER BY tstamp"; 088 private static final String orderByRuntime = " ORDER BY runtime DESC"; 089 private static final String derbyError = "Derby: Error "; 090 private static final String indexSuffix = "Index>"; 091 private static final String xml = "Xml"; 092 093 /** 094 * Instantiates the Derby implementation. Throws a Runtime exception if the Derby 095 * jar file cannot be found on the classpath. 096 * @param server The SensorBase server instance. 097 */ 098 public DerbyImplementation(Server server) { 099 super(server); 100 // Set the directory where the DB will be created and/or accessed. 101 // This must happen before loading the driver. 102 String dbDir = server.getServerProperties().get(DB_DIR_KEY); 103 System.getProperties().put(derbySystemKey, dbDir); 104 // Try to load the derby driver. 105 try { 106 Class.forName(driver); 107 } 108 catch (java.lang.ClassNotFoundException e) { 109 String msg = "Derby: Exception during DbManager initialization: Derby not on CLASSPATH."; 110 this.logger.warning(msg + "\n" + StackTrace.toString(e)); 111 throw new RuntimeException(msg, e); 112 } 113 } 114 115 116 /** {@inheritDoc} */ 117 @Override 118 public void initialize() { 119 try { 120 // Create a shutdown hook that shuts down derby. 121 Runtime.getRuntime().addShutdownHook(new Thread() { 122 /** Run the shutdown hook for shutting down Derby. */ 123 @Override 124 public void run() { 125 Connection conn = null; 126 try { 127 conn = DriverManager.getConnection("jdbc:derby:;shutdown=true"); 128 } 129 catch (Exception e) { 130 System.out.println("Derby shutdown hook results: " + e.getMessage()); 131 } 132 finally { 133 try { 134 conn.close(); 135 } 136 catch (Exception e) { //NOPMD 137 // we tried. 138 } 139 } 140 } 141 }); 142 // Initialize the database table structure if necessary. 143 this.isFreshlyCreated = !isPreExisting(); 144 String dbStatusMsg = (this.isFreshlyCreated) ? 145 "Derby: uninitialized." : "Derby: previously initialized."; 146 this.logger.info(dbStatusMsg); 147 if (this.isFreshlyCreated) { 148 this.logger.info("Derby: creating DB in: " + System.getProperty(derbySystemKey)); 149 createTables(); 150 } 151 152 if (server.getServerProperties().compressOnStartup()) { 153 this.logger.info("Derby: compressing database..."); 154 compressTables(); 155 } 156 if (server.getServerProperties().reindexOnStartup()) { 157 this.logger.info("Derby: reindexing database..."); 158 this.logger.info("Derby: reindexing database " + ((indexTables()) ? "OK" : "not OK")); 159 } 160 } 161 catch (Exception e) { 162 String msg = "Derby: Exception during DerbyImplementation initialization:"; 163 this.logger.warning(msg + "\n" + StackTrace.toString(e)); 164 throw new RuntimeException(msg, e); 165 } 166 167 } 168 169 /** 170 * Determine if the database has already been initialized with correct table definitions. 171 * Table schemas are checked by seeing if a dummy insert on the table will work OK. 172 * @return True if the database exists and tables are set up correctly. 173 * @throws SQLException If problems occur accessing the database or the tables are set right. 174 */ 175 private boolean isPreExisting() throws SQLException { 176 Connection conn = null; 177 Statement s = null; 178 try { 179 conn = DriverManager.getConnection(connectionURL); 180 s = conn.createStatement(); 181 s.execute(testSensorDataTableStatement); 182 s.execute(testSensorDataTypeTableStatement); 183 s.execute(testUserTableStatement); 184 s.execute(testProjectTableStatement); 185 } 186 catch (SQLException e) { 187 String theError = (e).getSQLState(); 188 if ("42X05".equals(theError)) { 189 // Database doesn't exist. 190 return false; 191 } 192 else if ("42X14".equals(theError) || "42821".equals(theError)) { 193 // Incorrect table definition. 194 throw e; 195 } 196 else { 197 // Unknown SQLException 198 throw e; 199 } 200 } 201 finally { 202 if (s != null) { 203 s.close(); 204 } 205 if (conn != null) { 206 conn.close(); 207 } 208 } 209 // If table exists will get - WARNING 02000: No row was found 210 return true; 211 } 212 213 /** 214 * Initialize the database by creating tables for each resource type. 215 * @throws SQLException If table creation fails. 216 */ 217 private void createTables() throws SQLException { 218 Connection conn = null; 219 Statement s = null; 220 try { 221 conn = DriverManager.getConnection(connectionURL); 222 s = conn.createStatement(); 223 s.execute(createSensorDataTableStatement); 224 s.execute(indexSensorDataTstampStatement); 225 s.execute(indexSensorDataRuntimeStatement); 226 s.execute(indexSensorDataToolStatement); 227 s.execute(createSensorDataTypeTableStatement); 228 s.execute(createUserTableStatement); 229 s.execute(createProjectTableStatement); 230 s.close(); 231 } 232 finally { 233 s.close(); 234 conn.close(); 235 } 236 } 237 238 // ******************** Start Sensor Data specific stuff here ***************** // 239 240 /** The SQL string for creating the SensorData table. */ 241 private static final String createSensorDataTableStatement = 242 "create table SensorData " 243 + "(" 244 + " Owner VARCHAR(64) NOT NULL, " 245 + " Tstamp TIMESTAMP NOT NULL, " 246 + " Sdt VARCHAR(64) NOT NULL, " 247 + " Runtime TIMESTAMP NOT NULL, " 248 + " Tool VARCHAR(64) NOT NULL, " 249 + " Resource VARCHAR(512) NOT NULL, " 250 + " XmlSensorData VARCHAR(32000) NOT NULL, " 251 + " XmlSensorDataRef VARCHAR(1000) NOT NULL, " 252 + " LastMod TIMESTAMP NOT NULL, " //NOPMD (Don't worry about repeat occurrences of this string) 253 + " PRIMARY KEY (Owner, Tstamp) " 254 + ")" ; 255 256 /** An SQL string to test whether the SensorData table exists and has the correct schema. */ 257 private static final String testSensorDataTableStatement = 258 " UPDATE SensorData SET " 259 + " Owner = 'TestUser', " 260 + " Tstamp = '" + new Timestamp(new Date().getTime()).toString() + "', " //NOPMD (dup string) 261 + " Sdt = 'testSdt'," 262 + " Runtime = '" + new Timestamp(new Date().getTime()).toString() + "', " 263 + " Tool = 'testTool', " 264 + " Resource = 'testResource', " 265 + " XmlSensorData = 'testXmlResource', " 266 + " XmlSensorDataRef = 'testXmlRef', " 267 + " LastMod = '" + new Timestamp(new Date().getTime()).toString() + "' " //NOPMD (dup string) 268 + " WHERE 1=3"; //NOPMD (duplicate string) 269 270 private static final String indexSensorDataTstampStatement = 271 "CREATE INDEX TstampIndex ON SensorData(Tstamp asc)"; 272 private static final String dropIndexSensorDataTstampStatement = 273 "DROP INDEX TstampIndex"; 274 275 private static final String indexSensorDataRuntimeStatement = 276 "CREATE INDEX RuntimeIndex ON SensorData(Runtime desc)"; 277 private static final String dropIndexSensorDataRuntimeStatement = 278 "DROP INDEX RuntimeIndex"; 279 280 private static final String indexSensorDataToolStatement = 281 "CREATE INDEX ToolIndex ON SensorData(Tool asc)"; 282 private static final String dropIndexSensorDataToolStatement = 283 "DROP INDEX ToolIndex"; 284 285 286 /** {@inheritDoc} */ 287 @Override 288 public boolean storeSensorData(SensorData data, String xmlSensorData, String xmlSensorDataRef) { 289 Connection conn = null; 290 PreparedStatement s = null; 291 try { 292 conn = DriverManager.getConnection(connectionURL); 293 s = conn.prepareStatement("INSERT INTO SensorData VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); 294 // Order: Owner Tstamp Sdt Runtime Tool Resource XmlSensorData XmlSensorDataRef LastMod 295 s.setString(1, data.getOwner()); 296 s.setTimestamp(2, Tstamp.makeTimestamp(data.getTimestamp())); 297 s.setString(3, data.getSensorDataType()); 298 s.setTimestamp(4, Tstamp.makeTimestamp(data.getRuntime())); 299 s.setString(5, data.getTool()); 300 s.setString(6, data.getResource()); 301 s.setString(7, xmlSensorData); 302 s.setString(8, xmlSensorDataRef); 303 s.setTimestamp(9, new Timestamp(new Date().getTime())); 304 s.executeUpdate(); 305 this.logger.fine("Derby: Inserted " + data.getOwner() + " " + data.getTimestamp()); 306 } 307 catch (SQLException e) { 308 if (DUPLICATE_KEY.equals(e.getSQLState())) { 309 try { 310 // Do an update, not an insert. 311 s = conn.prepareStatement( 312 "UPDATE SensorData SET " 313 + " Sdt=?, Runtime=?, Tool=?, Resource=?, XmlSensorData=?, " 314 + " XmlSensorDataRef=?, LastMod=?" 315 + " WHERE Owner=? AND Tstamp=?"); 316 s.setString(1, data.getSensorDataType()); 317 s.setTimestamp(2, Tstamp.makeTimestamp(data.getRuntime())); 318 s.setString(3, data.getTool()); 319 s.setString(4, data.getResource()); 320 s.setString(5, xmlSensorData); 321 s.setString(6, xmlSensorDataRef); 322 s.setTimestamp(7, new Timestamp(new Date().getTime())); 323 s.setString(8, data.getOwner()); 324 s.setTimestamp(9, Tstamp.makeTimestamp(data.getTimestamp())); 325 s.executeUpdate(); 326 this.logger.fine("Derby: Updated " + data.getOwner() + " " + data.getTimestamp()); 327 } 328 catch (SQLException f) { 329 this.logger.info(derbyError + StackTrace.toString(f)); 330 } 331 } 332 } 333 finally { 334 try { 335 s.close(); 336 conn.close(); 337 } 338 catch (SQLException e) { 339 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 340 } 341 } 342 return true; 343 } 344 345 346 /** {@inheritDoc} */ 347 @Override 348 public boolean isFreshlyCreated() { 349 return this.isFreshlyCreated; 350 } 351 352 353 /** {@inheritDoc} */ 354 @Override 355 public String getSensorDataIndex() { 356 String st = "SELECT XmlSensorDataRef FROM SensorData"; 357 return getIndex("SensorData", st); //NOPMD (See below) 358 } 359 360 /* 361 * Interestingly, I could not refactor out the string "SensorData" to avoid the PMD error 362 * resulting from multiple occurrences of the same string. 363 * This is because if I made it a private String, then Findbugs would throw a warning asking 364 * for it to be static: 365 * 366 * private static final String sensorData = "SensorData"; 367 * 368 * However, the above declaration causes the system to deadlock! 369 * So, I'm just ignoring the PMD error. 370 */ 371 372 /** {@inheritDoc} */ 373 @Override 374 public String getSensorDataIndex(User user) { 375 String st = "SELECT XmlSensorDataRef FROM SensorData WHERE owner='" + user.getEmail() + "'"; 376 return getIndex("SensorData", st); 377 } 378 379 /** {@inheritDoc} */ 380 @Override 381 public String getSensorDataIndex(User user, String sdtName) { 382 String st = 383 selectPrefix 384 + ownerEquals + user.getEmail() + quoteAndClause 385 + " Sdt='" + sdtName + "'" 386 + orderByTstamp; 387 return getIndex("SensorData", st); 388 } 389 390 /** {@inheritDoc} */ 391 @Override 392 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 393 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt) { 394 String statement; 395 if (sdt == null) { // Retrieve sensor data of all SDTs 396 statement = 397 selectPrefix 398 + constructOwnerClause(users) 399 + andClause 400 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 401 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 402 + constructLikeClauses(uriPatterns) 403 + orderByTstamp; 404 } 405 else { // Retrieve sensor data of the specified SDT. 406 statement = 407 selectPrefix 408 + constructOwnerClause(users) 409 + andClause 410 + sdtEquals + sdt + quoteAndClause 411 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 412 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 413 + constructLikeClauses(uriPatterns) 414 + orderByTstamp; 415 } 416 //System.out.println(statement); 417 return getIndex("SensorData", statement); 418 } 419 420 /** {@inheritDoc} */ 421 @Override 422 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 423 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt, String tool) { 424 String statement = 425 selectPrefix 426 + constructOwnerClause(users) 427 + andClause 428 + sdtEquals + sdt + quoteAndClause 429 + toolEquals + tool + quoteAndClause 430 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 431 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 432 + constructLikeClauses(uriPatterns) 433 + orderByTstamp; 434 //System.out.println(statement); 435 return getIndex("SensorData", statement); 436 } 437 438 /** {@inheritDoc} */ 439 @Override 440 public String getProjectSensorDataSnapshot(List<User> users, XMLGregorianCalendar startTime, 441 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt, String tool) { 442 String statement; 443 if (tool == null) { // Retrieve sensor data with latest runtime regardless of tool. 444 statement = 445 selectSnapshot 446 + constructOwnerClause(users) 447 + andClause 448 + sdtEquals + sdt + quoteAndClause 449 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 450 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 451 + constructLikeClauses(uriPatterns) 452 + orderByRuntime; 453 } 454 else { // Retrieve sensor data with the latest runtime for the specified tool. 455 statement = 456 selectSnapshot 457 + constructOwnerClause(users) 458 + andClause 459 + sdtEquals + sdt + quoteAndClause 460 + toolEquals + tool + quoteAndClause 461 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 462 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 463 + constructLikeClauses(uriPatterns) 464 + orderByRuntime; 465 } 466 //Generate a SensorDataIndex string that contains only entries with the latest runtime. 467 //System.out.println(statement); 468 return getSnapshotIndex(statement); 469 } 470 471 /** {@inheritDoc} */ 472 @Override 473 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 474 XMLGregorianCalendar endTime, List<String> uriPatterns, int startIndex, 475 int maxInstances) { 476 String statement; 477 478 statement = 479 selectPrefix 480 + constructOwnerClause(users) 481 + andClause 482 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " //NOPMD 483 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" //NOPMD 484 + constructLikeClauses(uriPatterns) 485 + orderByTstamp; 486 //System.out.println(statement); 487 return getIndex("SensorData", statement, startIndex, maxInstances); 488 } 489 490 /** 491 * Constructs a set of LIKE clauses corresponding to the passed set of UriPatterns. 492 * <p> 493 * Each UriPattern is translated in the following way: 494 * <ul> 495 * <li> If there is an occurrence of a "\" or a "/" in the UriPattern, then 496 * two translated UriPatterns are generated, one with all "\" replaced with "/", and one with 497 * all "/" replaced with "\". 498 * <li> The escape character is "\", unless we are generating a LIKE clause containing a 499 * "\", in which case the escape character will be "/". 500 * <li> All occurrences of "%" in the UriPattern are escaped. 501 * <li> All occurrences of "_" in the UriPattern are escaped. 502 * <li> All occurrences of "*" are changed to "%". 503 * </ul> 504 * The new set of 'translated' UriPatterns are now used to generate a set of LIKE clauses 505 * with the following form: 506 * <pre> 507 * (RESOURCE like 'translatedUriPattern1' escape 'escapeChar1') OR 508 * (RESOURCE like 'translatedUriPattern2' escape 'escapeChar2') .. 509 * </pre> 510 * 511 * <p> 512 * There is one special case. If the List(UriPattern) is null, empty, or consists of exactly one 513 * UriPattern which is "**" or "*", then the empty string is returned. This is an optimization for 514 * the common case where all resources should be matched and so we don't need any LIKE clauses. 515 * <p> 516 * We return either the empty string (""), or else a string of the form: 517 * " AND ([like clause] AND [like clause] ... )" 518 * This enables the return value to be appended to the SELECT statement. 519 * <p> 520 * This method is static and package private to support testing. See the class 521 * TestConstructUriPattern for example invocations and expected return values. 522 * 523 * @param uriPatterns The list of uriPatterns. 524 * @return The String to be used in the where clause to check for resource correctness. 525 */ 526 static String constructLikeClauses(List<String> uriPatterns) { 527 // Deal with special case. UriPatterns is null, or empty, or "**", or "*" 528 if (((uriPatterns == null) || uriPatterns.isEmpty()) || 529 ((uriPatterns.size() == 1) && uriPatterns.get(0).equals("**")) || 530 ((uriPatterns.size() == 1) && uriPatterns.get(0).equals("*"))) { 531 return ""; 532 } 533 // Deal with the potential presence of path separator character in UriPattern. 534 List<String> translatedPatterns = new ArrayList<String>(); 535 for (String pattern : uriPatterns) { 536 if (pattern.contains("\\") || pattern.contains("/")) { 537 translatedPatterns.add(pattern.replace('\\', '/')); 538 translatedPatterns.add(pattern.replace('/', '\\')); 539 } 540 else { 541 translatedPatterns.add(pattern); 542 } 543 } 544 // Now escape the SQL wildcards, and make our UriPattern wildcard into the SQL wildcard. 545 for (int i = 0; i < translatedPatterns.size(); i++) { 546 String pattern = translatedPatterns.get(i); 547 pattern = pattern.replace("%", "`%"); // used to be / 548 pattern = pattern.replace("_", "`_"); // used to be / 549 pattern = pattern.replace('*', '%'); 550 translatedPatterns.set(i, pattern); 551 } 552 553 // Now generate the return string: " AND (<like clause> OR <like clause> ... )". 554 StringBuffer buff = new StringBuffer(); 555 buff.append(" AND ("); 556 if (!translatedPatterns.isEmpty()) { 557 buff.append(makeLikeClause(translatedPatterns, "`")); // used to be / 558 } 559 560 buff.append(')'); 561 562 return buff.toString(); 563 } 564 565 /** 566 * Creates a set of LIKE clauses with the specified escape character. 567 * @param patterns The patterns. 568 * @param escape The escape character. 569 * @return The StringBuffer with the LIKE clauses. 570 */ 571 private static StringBuffer makeLikeClause(List<String> patterns, String escape) { 572 StringBuffer buff = new StringBuffer(); //NOPMD generates false warning about buff size. 573 if (patterns.isEmpty()) { 574 return buff; 575 } 576 for (Iterator<String> i = patterns.iterator(); i.hasNext(); ) { 577 String pattern = i.next(); 578 buff.append("(RESOURCE LIKE '"); 579 buff.append(pattern); 580 buff.append("' ESCAPE '"); 581 buff.append(escape); 582 buff.append("')"); 583 if (i.hasNext()) { 584 buff.append(" OR "); 585 } 586 } 587 buff.append(' '); 588 return buff; 589 } 590 591 /** 592 * Constructs a clause of form ( OWNER = 'user1' [ OR OWNER = 'user2']* ). 593 * @param users The list of users whose ownership is being searched for. 594 * @return The String to be used in the where clause to check for ownership. 595 */ 596 private String constructOwnerClause(List<User> users) { 597 StringBuffer buff = new StringBuffer(); 598 buff.append('('); 599 // Use old school iterator so we can do a hasNext() inside the loop. 600 for (Iterator<User> i = users.iterator(); i.hasNext(); ) { 601 User user = i.next(); 602 buff.append(ownerEquals); 603 buff.append(user.getEmail()); 604 buff.append('\''); 605 if (i.hasNext()) { 606 buff.append(" OR"); 607 } 608 } 609 buff.append(") "); 610 return buff.toString(); 611 } 612 613 /** {@inheritDoc} */ 614 @Override 615 public String getSensorDataIndexLastMod(User user, XMLGregorianCalendar lastModStartTime, 616 XMLGregorianCalendar lastModEndTime) { 617 String statement = 618 selectPrefix 619 + ownerEquals + user.getEmail() + quoteAndClause 620 + " LastMod BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(lastModStartTime) + "') AND " 621 + " TIMESTAMP('" + Tstamp.makeTimestamp(lastModEndTime) + "')"; 622 return getIndex("SensorData", statement); 623 } 624 625 626 /** {@inheritDoc} */ 627 @Override 628 public boolean hasSensorData(User user, XMLGregorianCalendar timestamp) { 629 Connection conn = null; 630 PreparedStatement s = null; 631 ResultSet rs = null; 632 boolean isFound = false; 633 try { 634 conn = DriverManager.getConnection(connectionURL); 635 // 636 String statement = 637 selectPrefix 638 + ownerEquals + user.getEmail() + quoteAndClause 639 + " Tstamp='" + Tstamp.makeTimestamp(timestamp) + "'"; 640 server.getLogger().fine(executeQueryMsg + statement); 641 s = conn.prepareStatement(statement); 642 rs = s.executeQuery(); 643 // If a record was retrieved, we'll enter the loop, otherwise we won't. 644 while (rs.next()) { 645 isFound = true; 646 } 647 } 648 catch (SQLException e) { 649 this.logger.info("Derby: Error in hasSensorData()" + StackTrace.toString(e)); 650 } 651 finally { 652 try { 653 rs.close(); 654 s.close(); 655 conn.close(); 656 } 657 catch (SQLException e) { 658 this.logger.warning("Derby: Error closing the connection" + StackTrace.toString(e)); 659 } 660 } 661 return isFound; 662 } 663 664 /** {@inheritDoc} */ 665 @Override 666 public void deleteSensorData(User user, XMLGregorianCalendar timestamp) { 667 String statement = 668 "DELETE FROM SensorData WHERE " 669 + ownerEquals + user.getEmail() + quoteAndClause 670 + " Tstamp='" + Tstamp.makeTimestamp(timestamp) + "'"; 671 deleteResource(statement); 672 } 673 674 /** {@inheritDoc} */ 675 @Override 676 public void deleteSensorData(User user) { 677 String statement = 678 "DELETE FROM SensorData WHERE " + ownerEquals + user.getEmail() + "'"; 679 deleteResource(statement); 680 //compressTables(); // this should be done separately as part of some maintenance. 681 } 682 683 /** {@inheritDoc} */ 684 @Override 685 public String getSensorData(User user, XMLGregorianCalendar timestamp) { 686 String statement = 687 "SELECT XmlSensorData FROM SensorData WHERE " 688 + ownerEquals + user.getEmail() + quoteAndClause 689 + " Tstamp='" + Tstamp.makeTimestamp(timestamp) + "'"; 690 return getResource("SensorData", statement); 691 } 692 693 // ******************** Start SensorDataType specific stuff here ***************** // 694 695 /** The SQL string for creating the SensorDataType table. */ 696 private static final String createSensorDataTypeTableStatement = 697 "create table SensorDataType " 698 + "(" 699 + " Name VARCHAR(64) NOT NULL, " 700 + " XmlSensorDataType VARCHAR(32000) NOT NULL, " 701 + " XmlSensorDataTypeRef VARCHAR(1000) NOT NULL, " 702 + " LastMod TIMESTAMP NOT NULL, " 703 + " PRIMARY KEY (Name) " 704 + ")" ; 705 706 /** An SQL string to test whether the SensorDataType table exists and has the correct schema. */ 707 private static final String testSensorDataTypeTableStatement = 708 " UPDATE SensorDataType SET " 709 + " Name = 'TestSdt', " 710 + " XmlSensorDataType = 'testXmlResource', " 711 + " XmlSensorDataTypeRef = 'testXmlRef', " 712 + " LastMod = '" + new Timestamp(new Date().getTime()).toString() + "' " 713 + " WHERE 1=3"; 714 715 /** {@inheritDoc} */ 716 @Override 717 public boolean storeSensorDataType(SensorDataType sdt, String xmlSensorDataType, 718 String xmlSensorDataTypeRef) { 719 Connection conn = null; 720 PreparedStatement s = null; 721 try { 722 conn = DriverManager.getConnection(connectionURL); 723 s = conn.prepareStatement("INSERT INTO SensorDataType VALUES (?, ?, ?, ?)"); 724 // Order: Name XmlSensorData XmlSensorDataRef LastMod 725 s.setString(1, sdt.getName()); 726 s.setString(2, xmlSensorDataType); 727 s.setString(3, xmlSensorDataTypeRef); 728 s.setTimestamp(4, new Timestamp(new Date().getTime())); 729 s.executeUpdate(); 730 this.logger.fine("Derby: Inserted SDT" + sdt.getName()); 731 } 732 catch (SQLException e) { 733 if (DUPLICATE_KEY.equals(e.getSQLState())) { 734 try { 735 // Do an update, not an insert. 736 s = conn.prepareStatement( 737 "UPDATE SensorDataType SET " 738 + " XmlSensorDataType=?, " 739 + " XmlSensorDataTypeRef=?, " 740 + " LastMod=?" 741 + " WHERE Name=?"); 742 s.setString(1, xmlSensorDataType); 743 s.setString(2, xmlSensorDataTypeRef); 744 s.setTimestamp(3, new Timestamp(new Date().getTime())); 745 s.setString(4, sdt.getName()); 746 s.executeUpdate(); 747 this.logger.fine("Derby: Updated SDT " + sdt.getName()); 748 } 749 catch (SQLException f) { 750 this.logger.info(derbyError + StackTrace.toString(f)); 751 } 752 } 753 } 754 finally { 755 try { 756 s.close(); 757 conn.close(); 758 } 759 catch (SQLException e) { 760 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 761 } 762 } 763 return true; 764 } 765 766 /** {@inheritDoc} */ 767 @Override 768 public void deleteSensorDataType(String sdtName) { 769 String statement = "DELETE FROM SensorDataType WHERE Name='" + sdtName + "'"; 770 deleteResource(statement); 771 } 772 773 /** {@inheritDoc} */ 774 @Override 775 public String getSensorDataTypeIndex() { 776 return getIndex("SensorDataType", "SELECT XmlSensorDataTypeRef FROM SensorDataType"); 777 } 778 779 /** {@inheritDoc} */ 780 @Override 781 public String getSensorDataType(String sdtName) { 782 String statement = 783 "SELECT XmlSensorDataType FROM SensorDataType WHERE Name = '" + sdtName + "'"; 784 return getResource("SensorDataType", statement); 785 } 786 787 // ******************** Start User specific stuff here ***************** // 788 /** The SQL string for creating the HackyUser table. So named because 'User' is reserved. */ 789 private static final String createUserTableStatement = 790 "create table HackyUser " 791 + "(" 792 + " Email VARCHAR(128) NOT NULL, " 793 + " Password VARCHAR(128) NOT NULL, " 794 + " Role CHAR(16), " 795 + " XmlUser VARCHAR(32000) NOT NULL, " 796 + " XmlUserRef VARCHAR(1000) NOT NULL, " 797 + " LastMod TIMESTAMP NOT NULL, " 798 + " PRIMARY KEY (Email) " 799 + ")" ; 800 801 /** An SQL string to test whether the User table exists and has the correct schema. */ 802 private static final String testUserTableStatement = 803 " UPDATE HackyUser SET " 804 + " Email = 'TestEmail@foo.com', " 805 + " Password = 'changeme', " 806 + " Role = 'basic', " 807 + " XmlUser = 'testXmlResource', " 808 + " XmlUserRef = 'testXmlRef', " 809 + " LastMod = '" + new Timestamp(new Date().getTime()).toString() + "' " 810 + " WHERE 1=3"; 811 812 /** {@inheritDoc} */ 813 @Override 814 public void deleteUser(String email) { 815 String statement = "DELETE FROM HackyUser WHERE Email='" + email + "'"; 816 deleteResource(statement); 817 } 818 819 /** {@inheritDoc} */ 820 @Override 821 public String getUser(String email) { 822 String statement = "SELECT XmlUser FROM HackyUser WHERE Email = '" + email + "'"; 823 return getResource("User", statement); 824 } 825 826 827 /** {@inheritDoc} */ 828 @Override 829 public String getUserIndex() { 830 return getIndex("User", "SELECT XmlUserRef FROM HackyUser"); 831 } 832 833 /** {@inheritDoc} */ 834 @Override 835 public boolean storeUser(User user, String xmlUser, String xmlUserRef) { 836 Connection conn = null; 837 PreparedStatement s = null; 838 try { 839 conn = DriverManager.getConnection(connectionURL); 840 s = conn.prepareStatement("INSERT INTO HackyUser VALUES (?, ?, ?, ?, ?, ?)"); 841 // Order: Email Password Role XmlUser XmlUserRef LastMod 842 s.setString(1, user.getEmail()); 843 s.setString(2, user.getPassword()); 844 s.setString(3, user.getRole()); 845 s.setString(4, xmlUser); 846 s.setString(5, xmlUserRef); 847 s.setTimestamp(6, new Timestamp(new Date().getTime())); 848 s.executeUpdate(); 849 this.logger.fine("Derby: Inserted User" + user.getEmail()); 850 } 851 catch (SQLException e) { 852 if (DUPLICATE_KEY.equals(e.getSQLState())) { 853 try { 854 // Do an update, not an insert. 855 s = conn.prepareStatement( 856 "UPDATE HackyUser SET " 857 + " Password=?, " 858 + " Role=?, " 859 + " XmlUser=?, " 860 + " XmlUserRef=?, " 861 + " LastMod=?" 862 + " WHERE Email=?"); 863 s.setString(1, user.getPassword()); 864 s.setString(2, user.getRole()); 865 s.setString(3, xmlUser); 866 s.setString(4, xmlUserRef); 867 s.setTimestamp(5, new Timestamp(new Date().getTime())); 868 s.setString(6, user.getEmail()); 869 s.executeUpdate(); 870 this.logger.fine("Derby: Updated User " + user.getEmail()); 871 } 872 catch (SQLException f) { 873 this.logger.info(derbyError + StackTrace.toString(f)); 874 } 875 } 876 else { 877 this.logger.info(derbyError + StackTrace.toString(e)); 878 } 879 } 880 finally { 881 try { 882 s.close(); 883 conn.close(); 884 } 885 catch (SQLException e) { 886 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 887 } 888 } 889 return true; 890 } 891 892 // ******************** Start Project specific stuff here ***************** // 893 894 /** The SQL string for creating the Project table. */ 895 private static final String createProjectTableStatement = 896 "create table Project " 897 + "(" 898 + " Owner VARCHAR(128) NOT NULL, " 899 + " ProjectName VARCHAR(128) NOT NULL, " 900 + " StartTime TIMESTAMP NOT NULL, " 901 + " EndTime TIMESTAMP NOT NULL, " 902 + " XmlProject VARCHAR(32000) NOT NULL, " 903 + " XmlProjectRef VARCHAR(1000) NOT NULL, " 904 + " LastMod TIMESTAMP NOT NULL, " 905 + " PRIMARY KEY (Owner, ProjectName) " 906 + ")" ; 907 908 /** An SQL string to test whether the Project table exists and has the correct schema. */ 909 private static final String testProjectTableStatement = 910 " UPDATE Project SET " 911 + " Owner = 'TestEmail@foo.com', " 912 + " ProjectName = 'TestProject', " 913 + " StartTime = '" + new Timestamp(new Date().getTime()).toString() + "', " 914 + " EndTime = '" + new Timestamp(new Date().getTime()).toString() + "', " 915 + " XmlProject = 'testXmlResource', " 916 + " XmlProjectRef = 'testXmlRef', " 917 + " LastMod = '" + new Timestamp(new Date().getTime()).toString() + "' " 918 + " WHERE 1=3"; 919 920 private static final String indexProjectNameStatement = 921 "CREATE INDEX ProjectNameIndex ON Project(ProjectName asc)"; 922 private static final String dropIndexProjectNameStatement = 923 "DROP INDEX ProjectNameIndex"; 924 925 /** {@inheritDoc} */ 926 @Override 927 public void deleteProject(User owner, String projectName) { 928 String statement = 929 "DELETE FROM Project WHERE " 930 + ownerEquals + owner.getEmail() + quoteAndClause 931 + " ProjectName = '" + projectName + "'"; 932 deleteResource(statement); 933 } 934 935 /** {@inheritDoc} */ 936 @Override 937 public String getProject(User owner, String projectName) { 938 String statement = 939 "SELECT XmlProject FROM Project WHERE " 940 + ownerEquals + owner.getEmail() + quoteAndClause 941 + " ProjectName ='" + projectName + "'"; 942 return getResource("Project", statement); 943 } 944 945 /** {@inheritDoc} */ 946 @Override 947 public String getProjectIndex() { 948 return getIndex("Project", "SELECT XmlProjectRef FROM Project ORDER BY ProjectName"); 949 } 950 951 /** {@inheritDoc} */ 952 @Override 953 public ProjectSummary getProjectSummary(List<User> users, XMLGregorianCalendar startTime, 954 XMLGregorianCalendar endTime, List<String> uriPatterns, String href) { 955 // Make a statement to return all SensorData for this project in the time period. 956 String statement = 957 "SELECT Sdt, Tool FROM SensorData WHERE " 958 + constructOwnerClause(users) 959 + andClause 960 + " (Tstamp BETWEEN TIMESTAMP('" + Tstamp.makeTimestamp(startTime) + "') AND " 961 + " TIMESTAMP('" + Tstamp.makeTimestamp(endTime) + "'))" 962 + constructLikeClauses(uriPatterns); 963 964 // Create the [SDT, Tool] -> NumInstances data structure. 965 Map<String, Map<String, Integer>> sdtInstances = new HashMap<String, Map<String, Integer>>(); 966 967 // Retrieve the sensordata for this project and time period. 968 Connection conn = null; 969 PreparedStatement s = null; 970 ResultSet rs = null; 971 try { 972 conn = DriverManager.getConnection(connectionURL); 973 s = conn.prepareStatement(statement); 974 rs = s.executeQuery(); 975 // Loop through all retrieved SensorData records. 976 while (rs.next()) { 977 String sdt = rs.getString("Sdt"); 978 String tool = rs.getString("Tool"); 979 // Don't want null SDTs or Tools, call them the empty string instead. 980 if (sdt == null) { 981 sdt = ""; 982 } 983 if (tool == null) { 984 tool = ""; 985 } 986 // Now update our numInstance data structure. 987 // First, initialize the data structure if this is a new SDT. 988 if (!sdtInstances.containsKey(sdt)) { 989 Map<String, Integer> tool2NumInstances = new HashMap<String, Integer>(); 990 tool2NumInstances.put(tool, 0); 991 sdtInstances.put(sdt, tool2NumInstances); 992 } 993 Map<String, Integer> tool2NumInstances = sdtInstances.get(sdt); 994 // Second, initialize the data structure if this is a new tool for a preexisting SDT. 995 if (tool2NumInstances.get(tool) == null) { 996 tool2NumInstances.put(tool, 0); 997 } 998 // Finally, increment this entry. 999 tool2NumInstances.put(tool, tool2NumInstances.get(tool) + 1); 1000 } 1001 } 1002 catch (SQLException e) { 1003 this.logger.info("Derby: Error in getProjectSummary()" + StackTrace.toString(e)); 1004 } 1005 finally { 1006 try { 1007 rs.close(); 1008 s.close(); 1009 conn.close(); 1010 } 1011 catch (SQLException e) { 1012 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1013 } 1014 } 1015 1016 //Now create the project summary object from our data structures. 1017 return makeProjectSummary(href, startTime, endTime, sdtInstances); 1018 } 1019 1020 /** 1021 * Creates a ProjectSummary instances from the passed data. 1022 * @param href The Href representing this resource. 1023 * @param startTime The startTime for this data. 1024 * @param endTime The endTime for this data. 1025 * @param sdtInstances The data structure containing the instances. 1026 * @return The ProjectSummary instance. 1027 */ 1028 private ProjectSummary makeProjectSummary(String href, XMLGregorianCalendar startTime, 1029 XMLGregorianCalendar endTime, Map<String, Map<String, Integer>> sdtInstances) { 1030 ProjectSummary projectSummary = new ProjectSummary(); 1031 projectSummary.setHref(href); 1032 projectSummary.setStartTime(startTime); 1033 projectSummary.setEndTime(endTime); 1034 projectSummary.setLastMod(Tstamp.makeTimestamp()); 1035 SensorDataSummaries summaries = new SensorDataSummaries(); 1036 projectSummary.setSensorDataSummaries(summaries); 1037 int totalInstances = 0; 1038 for (Map.Entry<String, Map<String, Integer>> entry : sdtInstances.entrySet()) { 1039 String sdt = entry.getKey(); 1040 Map<String, Integer> tool2NumInstances = entry.getValue(); 1041 for (Map.Entry<String, Integer> entry2 : tool2NumInstances.entrySet()) { 1042 SensorDataSummary summary = new SensorDataSummary(); 1043 summary.setSensorDataType(sdt); 1044 summary.setTool(entry2.getKey()); 1045 int numInstances = entry2.getValue(); 1046 totalInstances += numInstances; 1047 summary.setNumInstances(BigInteger.valueOf(numInstances)); 1048 summaries.getSensorDataSummary().add(summary); 1049 } 1050 } 1051 summaries.setNumInstances(BigInteger.valueOf(totalInstances)); 1052 return projectSummary; 1053 } 1054 1055 1056 /** {@inheritDoc} */ 1057 @Override 1058 public boolean storeProject(Project project, String xmlProject, String xmlProjectRef) { 1059 Connection conn = null; 1060 PreparedStatement s = null; 1061 try { 1062 conn = DriverManager.getConnection(connectionURL); 1063 s = conn.prepareStatement("INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?, ?)"); 1064 // Order: Owner ProjectName StartTime EndTime XmlProject XmlProjectRef LastMod 1065 s.setString(1, project.getOwner()); 1066 s.setString(2, project.getName()); 1067 s.setTimestamp(3, Tstamp.makeTimestamp(project.getStartTime())); 1068 s.setTimestamp(4, Tstamp.makeTimestamp(project.getEndTime())); 1069 s.setString(5, xmlProject); 1070 s.setString(6, xmlProjectRef); 1071 s.setTimestamp(7, Tstamp.makeTimestamp(project.getLastMod())); 1072 s.executeUpdate(); 1073 this.logger.fine("Derby: Inserted " + project.getOwner() + " " + project.getName()); 1074 } 1075 catch (SQLException e) { 1076 if (DUPLICATE_KEY.equals(e.getSQLState())) { 1077 try { 1078 // Do an update, not an insert. 1079 s = conn.prepareStatement( 1080 "UPDATE Project SET " 1081 + " StartTime=?, EndTime=?, XmlProject=?, " 1082 + " XmlProjectRef=?, LastMod=?" 1083 + " WHERE Owner=? AND ProjectName=?"); 1084 s.setTimestamp(1, Tstamp.makeTimestamp(project.getStartTime())); 1085 s.setTimestamp(2, Tstamp.makeTimestamp(project.getEndTime())); 1086 s.setString(3, xmlProject); 1087 s.setString(4, xmlProjectRef); 1088 s.setTimestamp(5, Tstamp.makeTimestamp(project.getEndTime())); 1089 s.setString(6, project.getOwner()); 1090 s.setString(7, project.getName()); 1091 s.executeUpdate(); 1092 this.logger.fine("Derby: Updated " + project.getOwner() + " " + project.getName()); 1093 } 1094 catch (SQLException f) { 1095 this.logger.info(derbyError + StackTrace.toString(f)); 1096 } 1097 } 1098 } 1099 finally { 1100 try { 1101 s.close(); 1102 conn.close(); 1103 } 1104 catch (SQLException e) { 1105 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1106 } 1107 } 1108 return true; 1109 } 1110 1111 // **************************** Internal helper functions ***************************** 1112 1113 /** 1114 * Returns a string containing the Index for the given resource indicated by resourceName. 1115 * @param resourceName The resource name, such as "Project". 1116 * @param statement The SQL Statement to be used to retrieve the resource references. 1117 * @return The aggregate Index XML string. 1118 */ 1119 private String getIndex(String resourceName, String statement) { 1120 StringBuilder builder = new StringBuilder(512); 1121 builder.append("<").append(resourceName).append(indexSuffix); 1122 // Retrieve all the SensorData 1123 Connection conn = null; 1124 PreparedStatement s = null; 1125 ResultSet rs = null; 1126 try { 1127 conn = DriverManager.getConnection(connectionURL); 1128 s = conn.prepareStatement(statement); 1129 rs = s.executeQuery(); 1130 String resourceRefColumnName = xml + resourceName + "Ref"; 1131 while (rs.next()) { 1132 builder.append(rs.getString(resourceRefColumnName)); 1133 } 1134 } 1135 catch (SQLException e) { 1136 this.logger.info("Derby: Error in getIndex()" + StackTrace.toString(e)); 1137 } 1138 finally { 1139 try { 1140 rs.close(); 1141 s.close(); 1142 conn.close(); 1143 } 1144 catch (SQLException e) { 1145 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1146 } 1147 } 1148 builder.append("</").append(resourceName).append(indexSuffix); 1149 //System.out.println(builder.toString()); 1150 return builder.toString(); 1151 } 1152 1153 /** 1154 * Returns a string containing the Index of all of the SensorData whose runtime field matches 1155 * the first runtime in the result set. Since the passed statement will retrieve sensor 1156 * data in the given time period ordered in descending order by runtime, this should result 1157 * in an index containing only 1158 * @param statement The SQL Statement to be used to retrieve the resource references. 1159 * @return The aggregate Index XML string. 1160 */ 1161 private String getSnapshotIndex(String statement) { 1162 String resourceName = "SensorData"; 1163 StringBuilder builder = new StringBuilder(512); 1164 builder.append("<").append(resourceName).append(indexSuffix); 1165 // Retrieve all the SensorData 1166 Connection conn = null; 1167 PreparedStatement s = null; 1168 ResultSet rs = null; 1169 String firstRunTime = null; 1170 try { 1171 conn = DriverManager.getConnection(connectionURL); 1172 s = conn.prepareStatement(statement); 1173 rs = s.executeQuery(); 1174 String resourceRefColumnName = xml + resourceName + "Ref"; 1175 boolean finished = false; 1176 // Add all entries with the first retrieved nruntime value to the index. 1177 while (rs.next() && !finished) { 1178 String runtime = rs.getString("Runtime"); 1179 // Should never be null, but just in case. 1180 if (runtime != null) { 1181 // Initial firstRunTime to the first retrieved non-null runtime value. 1182 if (firstRunTime == null) { 1183 firstRunTime = runtime; 1184 } 1185 // Now add every entry whose runtime equals the first retrieved run time. 1186 if (runtime.equals(firstRunTime)) { 1187 builder.append(rs.getString(resourceRefColumnName)); 1188 } 1189 else { 1190 // As soon as we find a runtime not equal to firstRunTime, we can stop. 1191 finished = true; 1192 } 1193 } 1194 } 1195 } 1196 catch (SQLException e) { 1197 this.logger.info("Derby: Error in getIndex()" + StackTrace.toString(e)); 1198 } 1199 finally { 1200 try { 1201 rs.close(); 1202 s.close(); 1203 conn.close(); 1204 } 1205 catch (SQLException e) { 1206 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1207 } 1208 } 1209 builder.append("</").append(resourceName).append(indexSuffix); 1210 //System.out.println(builder.toString()); 1211 return builder.toString(); 1212 } 1213 1214 /** 1215 * Returns a string containing the Index for the given resource indicated by resourceName, 1216 * returning only the instances starting at startIndex, and with the maximum number of 1217 * returned instances indicated by maxInstances. 1218 * @param resourceName The resource name, such as "Project". 1219 * @param startIndex The (zero-based) starting index for instances to be returned. 1220 * @param maxInstances The maximum number of instances to return. 1221 * @param statement The SQL Statement to be used to retrieve the resource references. 1222 * @return The aggregate Index XML string. 1223 */ 1224 private String getIndex(String resourceName, String statement, int startIndex, int maxInstances) { 1225 StringBuilder builder = new StringBuilder(512); 1226 builder.append("<").append(resourceName).append(indexSuffix); 1227 // Retrieve all the SensorData to start. 1228 Connection conn = null; 1229 PreparedStatement s = null; 1230 ResultSet rs = null; 1231 try { 1232 conn = DriverManager.getConnection(connectionURL); 1233 s = conn.prepareStatement(statement); 1234 rs = s.executeQuery(); 1235 int currIndex = 0; 1236 int totalInstances = 0; 1237 String resourceRefColumnName = xml + resourceName + "Ref"; 1238 while (rs.next()) { 1239 if ((currIndex >= startIndex) && (totalInstances < maxInstances)) { 1240 builder.append(rs.getString(resourceRefColumnName)); 1241 totalInstances++; 1242 } 1243 currIndex++; 1244 } 1245 } 1246 catch (SQLException e) { 1247 this.logger.info("Derby: Error in getIndex()" + StackTrace.toString(e)); 1248 } 1249 finally { 1250 try { 1251 rs.close(); 1252 s.close(); 1253 conn.close(); 1254 } 1255 catch (SQLException e) { 1256 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1257 } 1258 } 1259 builder.append("</").append(resourceName).append(indexSuffix); 1260 //System.out.println(builder.toString()); 1261 return builder.toString(); 1262 } 1263 1264 /** 1265 * Returns a string containing the Resource as XML, or null if not found. 1266 * @param resourceName The name of the resource, such as "User". 1267 * @param statement The select statement used to retrieve the resultset containing a single 1268 * row with that resource. 1269 * @return The string containing the resource as an XML string. 1270 */ 1271 private String getResource(String resourceName, String statement) { 1272 StringBuilder builder = new StringBuilder(512); 1273 Connection conn = null; 1274 PreparedStatement s = null; 1275 ResultSet rs = null; 1276 boolean hasData = false; 1277 try { 1278 conn = DriverManager.getConnection(connectionURL); 1279 server.getLogger().fine(executeQueryMsg + statement); 1280 s = conn.prepareStatement(statement); 1281 rs = s.executeQuery(); 1282 String resourceXmlColumnName = xml + resourceName; 1283 while (rs.next()) { // the select statement must guarantee only one row is returned. 1284 hasData = true; 1285 builder.append(rs.getString(resourceXmlColumnName)); 1286 } 1287 } 1288 catch (SQLException e) { 1289 this.logger.info("DB: Error in getResource()" + StackTrace.toString(e)); 1290 } 1291 finally { 1292 try { 1293 rs.close(); 1294 s.close(); 1295 conn.close(); 1296 } 1297 catch (SQLException e) { 1298 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1299 } 1300 } 1301 return (hasData) ? builder.toString() : null; 1302 } 1303 1304 /** 1305 * Deletes the resource, given the SQL statement to perform the delete. 1306 * @param statement The SQL delete statement. 1307 */ 1308 private void deleteResource(String statement) { 1309 Connection conn = null; 1310 PreparedStatement s = null; 1311 try { 1312 conn = DriverManager.getConnection(connectionURL); 1313 server.getLogger().fine("Derby: " + statement); 1314 s = conn.prepareStatement(statement); 1315 s.executeUpdate(); 1316 } 1317 catch (SQLException e) { 1318 this.logger.info("Derby: Error in deleteResource()" + StackTrace.toString(e)); 1319 } 1320 finally { 1321 try { 1322 s.close(); 1323 conn.close(); 1324 } 1325 catch (SQLException e) { 1326 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1327 } 1328 } 1329 } 1330 1331 1332 /** {@inheritDoc} */ 1333 @Override 1334 public boolean compressTables() { 1335 boolean success = false; 1336 this.logger.fine("Starting to compress tables."); 1337 Connection conn = null; 1338 CallableStatement cs = null; 1339 try { 1340 conn = DriverManager.getConnection(connectionURL); 1341 cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)"); 1342 cs.setString(1, "APP"); 1343 cs.setString(2, "SENSORDATA"); 1344 cs.setShort(3, (short) 1); 1345 cs.execute(); 1346 cs.setString(2, "SENSORDATATYPE"); 1347 cs.execute(); 1348 cs.setString(2, "HACKYUSER"); 1349 cs.execute(); 1350 cs.setString(2, "PROJECT"); 1351 cs.execute(); 1352 success = true; 1353 } 1354 catch (SQLException e) { 1355 this.logger.info("Derby: Error in compressTables()" + StackTrace.toString(e)); 1356 success = false; 1357 } 1358 finally { 1359 try { 1360 cs.close(); 1361 conn.close(); 1362 } 1363 catch (SQLException e) { 1364 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1365 success = false; 1366 } 1367 } 1368 this.logger.fine("Finished compressing tables."); 1369 return success; 1370 } 1371 1372 /** {@inheritDoc} */ 1373 @Override 1374 public boolean indexTables() { 1375 this.logger.fine("Starting to index tables."); 1376 boolean success = false; 1377 Connection conn = null; 1378 Statement s = null; 1379 try { 1380 conn = DriverManager.getConnection(connectionURL); 1381 s = conn.createStatement(); 1382 1383 // Note: If the db is being set up for the first time, it is not an error for the drop index 1384 // statement to fail. Thus, we simply log the occurrence. 1385 1386 try { 1387 s.execute(dropIndexSensorDataTstampStatement); 1388 } 1389 catch (Exception e) { 1390 this.logger.info("Failed to drop SensorData(Tstamp) index."); 1391 } 1392 s.execute(indexSensorDataTstampStatement); 1393 1394 try { 1395 s.execute(dropIndexSensorDataRuntimeStatement); 1396 } 1397 catch (Exception e) { 1398 this.logger.info("Failed to drop SensorData(Runtime) index."); 1399 } 1400 s.execute(indexSensorDataRuntimeStatement); 1401 1402 try { 1403 s.execute(dropIndexSensorDataToolStatement); 1404 } 1405 catch (Exception e) { 1406 this.logger.info("Failed to drop SensorData(Tool) index."); 1407 } 1408 s.execute(indexSensorDataToolStatement); 1409 1410 try { 1411 s.execute(dropIndexProjectNameStatement); 1412 } 1413 catch (Exception e) { 1414 this.logger.info("Failed to drop Project(ProjectName) index."); 1415 } 1416 s.execute(indexProjectNameStatement); 1417 1418 s.close(); 1419 success = true; 1420 } 1421 catch (SQLException e) { 1422 this.logger.info("Derby: Error in indexTables()" + StackTrace.toString(e)); 1423 success = false; 1424 } 1425 finally { 1426 try { 1427 s.close(); 1428 conn.close(); 1429 } 1430 catch (SQLException e) { 1431 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1432 success = false; 1433 } 1434 } 1435 this.logger.fine("Finished indexing tables."); 1436 return success; 1437 } 1438 1439 /** {@inheritDoc} */ 1440 @Override 1441 public int getRowCount(String table) { 1442 int numRows = -1; 1443 Connection conn = null; 1444 PreparedStatement s = null; 1445 ResultSet rs = null; 1446 String statement = "Select COUNT(1) from " + table; 1447 try { 1448 conn = DriverManager.getConnection(connectionURL); 1449 s = conn.prepareStatement(statement); 1450 rs = s.executeQuery(); 1451 rs.next(); 1452 numRows = rs.getInt(1); 1453 } 1454 catch (SQLException e) { 1455 this.logger.info("Derby: Error in getRowCount: " + StackTrace.toString(e)); 1456 } 1457 finally { 1458 try { 1459 rs.close(); 1460 s.close(); 1461 conn.close(); 1462 } 1463 catch (SQLException e) { 1464 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1465 } 1466 } 1467 return numRows; 1468 } 1469 1470 /** {@inheritDoc} */ 1471 @Override 1472 public Set<String> getTableNames() { 1473 Set<String> tableNames = new HashSet<String>(); 1474 tableNames.add("SensorData"); 1475 tableNames.add("SensorDataType"); 1476 tableNames.add("HackyUser"); 1477 tableNames.add("Project"); 1478 return tableNames; 1479 } 1480 }