001 package org.hackystat.sensorbase.db.postgres; 002 003 import java.io.BufferedReader; 004 import java.io.ByteArrayInputStream; 005 import java.io.File; 006 import java.io.FileNotFoundException; 007 import java.io.FileReader; 008 import java.io.IOException; 009 import java.math.BigInteger; 010 import java.net.MalformedURLException; 011 import java.net.URL; 012 import java.sql.Connection; 013 import java.sql.DriverManager; 014 import java.sql.PreparedStatement; 015 import java.sql.ResultSet; 016 import java.sql.SQLException; 017 import java.sql.Timestamp; 018 import java.sql.Types; 019 import java.util.ArrayList; 020 import java.util.Date; 021 import java.util.HashMap; 022 import java.util.HashSet; 023 import java.util.Iterator; 024 import java.util.List; 025 import java.util.Locale; 026 import java.util.Map; 027 import java.util.Set; 028 import java.util.UUID; 029 030 import javax.xml.datatype.XMLGregorianCalendar; 031 import javax.xml.parsers.SAXParser; 032 import javax.xml.parsers.SAXParserFactory; 033 034 import org.hackystat.sensorbase.db.DbImplementation; 035 import org.hackystat.sensorbase.resource.projects.jaxb.Project; 036 import org.hackystat.sensorbase.resource.projects.jaxb.ProjectSummary; 037 import org.hackystat.sensorbase.resource.projects.jaxb.SensorDataSummaries; 038 import org.hackystat.sensorbase.resource.projects.jaxb.SensorDataSummary; 039 import org.hackystat.sensorbase.resource.sensordata.jaxb.SensorData; 040 import org.hackystat.sensorbase.resource.sensordatatypes.jaxb.SensorDataType; 041 import org.hackystat.sensorbase.resource.users.jaxb.User; 042 import org.hackystat.sensorbase.server.Server; 043 import org.hackystat.sensorbase.server.ServerProperties; 044 import org.hackystat.utilities.stacktrace.StackTrace; 045 import org.hackystat.utilities.tstamp.Tstamp; 046 047 /** 048 * Provides a implementation of DbImplementation using Postgres. 049 * 050 * @author Philip Johnson 051 * @author Austen Ito 052 */ 053 public class PostgresImplementation extends DbImplementation { 054 /** The database connection url. */ 055 private final String connectionURL; 056 /** The database connection. */ 057 private Connection connection = null; 058 /** Indicates whether this database was initialized or was pre-existing. */ 059 private boolean isFreshlyCreated; 060 061 /** The logger message for connection closing errors. */ 062 private static final String errorClosingMsg = "Postgres: Error while closing. \n"; 063 064 /** The logger message when executing a query. */ 065 private static final String executeQueryMsg = "Postgres: Executing query "; 066 067 /** Required by PMD since this string occurs multiple times in this file. */ 068 private static final String ownerIdEquals = " Owner_Id='"; 069 private static final String sdtIdEquals = " Sdt_Id= '"; 070 private static final String toolEquals = " tool = '"; 071 072 /** Required by PMD as above. */ 073 private static final String quoteAndClause = "' AND "; 074 private static final String andClause = " AND "; 075 private static final String tstampBetweenTstamp = " Tstamp BETWEEN TIMESTAMP '"; 076 private static final String timeStampClause = " TIMESTAMP '"; 077 private static final String selectPrefix = "SELECT XmlSensorDataRef FROM SensorData WHERE "; 078 private static final String selectSnapshot = "SELECT XmlSensorDataRef, Runtime, Tool FROM " 079 + "SensorData WHERE "; 080 private static final String orderByTstamp = " ORDER BY tstamp"; 081 private static final String orderByRuntime = " ORDER BY runtime DESC"; 082 private static final String postgresError = "Postgres: Error "; 083 private static final String indexSuffix = "Index>"; 084 private static final String xml = "Xml"; 085 /** The postgres database name. */ 086 public static final String POSTGRES_DB = "sensorbase.db.postgres.db"; 087 /** The postgres server username. */ 088 public static final String POSTGRES_USER = "sensorbase.db.postgres.user"; 089 /** The postgres server password. */ 090 public static final String POSTGRES_PASSWORD = "sensorbase.db.postgres.password"; 091 092 /** 093 * The SQL state indicating that INSERT tried to add data to a table with a 094 * preexisting key. 095 */ 096 private static final String DUPLICATE_KEY = "23505"; 097 098 /** 099 * Instantiates the Postgres implementation. Throws a Runtime exception if the 100 * Postgres jar file cannot be found on the classpath. 101 * @param server The SensorBase server instance. 102 */ 103 public PostgresImplementation(Server server) { 104 super(server); 105 ServerProperties props = new ServerProperties(); 106 this.connectionURL = "jdbc:postgresql:" + props.get(POSTGRES_DB) + "?user=" 107 + props.get(POSTGRES_USER) + "&password=" + props.get(POSTGRES_PASSWORD); 108 // Try to load the derby driver. 109 try { 110 Class.forName("org.postgresql.Driver"); 111 } 112 catch (java.lang.ClassNotFoundException e) { 113 String msg = "Postgres: Exception during DbManager initialization: " 114 + "Postgres not on CLASSPATH."; 115 this.logger.warning(msg + "\n" + StackTrace.toString(e)); 116 throw new RuntimeException(msg, e); 117 } 118 119 try { 120 this.connection = DriverManager.getConnection(this.connectionURL); 121 } 122 catch (SQLException e) { 123 this.logger.warning("Postgres: failed to open connection." + StackTrace.toString(e)); 124 } 125 } 126 127 /** {@inheritDoc} */ 128 @Override 129 public void initialize() { 130 String errorPrefix = "Error loading database schema: "; 131 if (this.shouldLoadSchema()) { 132 try { 133 this.logger.warning("Postgres schema doesn't exist. Creating..."); 134 File topLevelDir = new File(""); 135 File resource = new File(topLevelDir.getAbsolutePath() + "/" 136 + "postgres_hackystat_schema.sql"); 137 URL url = resource.toURI().toURL(); 138 139 BufferedReader reader = new BufferedReader(new FileReader(url.getFile())); 140 StringBuffer query = new StringBuffer(); 141 String line = reader.readLine(); 142 while (line != null) { 143 query.append(line); 144 line = reader.readLine(); 145 } 146 147 PreparedStatement statement = this.connection.prepareStatement(query.toString()); 148 statement.execute(); 149 statement.close(); 150 reader.close(); 151 } 152 catch (SQLException e) { 153 this.logger.warning(errorPrefix + StackTrace.toString(e)); 154 } 155 catch (MalformedURLException e) { 156 this.logger.warning(errorPrefix + StackTrace.toString(e)); 157 } 158 catch (FileNotFoundException e) { 159 this.logger.warning(errorPrefix + StackTrace.toString(e)); 160 } 161 catch (IOException e) { 162 this.logger.warning(errorPrefix + StackTrace.toString(e)); 163 } 164 } 165 } 166 167 /** 168 * Returns true if the postgres schema file should be loaded. 169 * @return true if the schema should be loaded, false if not. 170 */ 171 private boolean shouldLoadSchema() { 172 try { 173 PreparedStatement statement = this.connection 174 .prepareStatement("SELECT * FROM HackyUser"); 175 statement.execute(); 176 statement.close(); 177 } 178 catch (SQLException e) { 179 return true; 180 } 181 return false; 182 } 183 184 /** {@inheritDoc} */ 185 @Override 186 public boolean storeSensorData(SensorData data, String xmlSensorData, String xmlSensorDataRef) { 187 PreparedStatement preparedStatement = null; 188 try { 189 preparedStatement = this.connection 190 .prepareStatement("INSERT INTO SensorData VALUES (?, " 191 + "(select id from hackyuser where email = ?), ?, " 192 + "(select id from sensordatatype where name = ?), ?, ?, ?, ?, ?, ?)"); 193 // Order: Id Owner_Id Tstamp Sdt_id Runtime Tool Resource LastMod 194 // XmlSensorData XmlSensorDataRef 195 Object uuid = UUID.randomUUID(); 196 preparedStatement.setObject(1, uuid, Types.OTHER); 197 preparedStatement.setString(2, data.getOwner()); 198 preparedStatement.setTimestamp(3, Tstamp.makeTimestamp(data.getTimestamp())); 199 preparedStatement.setString(4, data.getSensorDataType()); 200 preparedStatement.setTimestamp(5, Tstamp.makeTimestamp(data.getRuntime())); 201 preparedStatement.setString(6, data.getTool()); 202 preparedStatement.setString(7, data.getResource()); 203 preparedStatement.setTimestamp(8, new Timestamp(new Date().getTime())); 204 preparedStatement.setString(9, xmlSensorData); 205 preparedStatement.setString(10, xmlSensorDataRef); 206 preparedStatement.executeUpdate(); 207 this.storeSensorDataProperties(uuid, xmlSensorData, false); 208 this.logger.fine("Postgres: Inserted " + data.getOwner() + " " + data.getTimestamp()); 209 } 210 catch (SQLException e) { 211 if (DUPLICATE_KEY.equals(e.getSQLState())) { 212 PreparedStatement sensordataIdStatement = null; 213 ResultSet sensordataIdResultSet = null; 214 try { 215 preparedStatement = this.connection.prepareStatement("UPDATE SensorData SET " 216 + " sdt_id=(select id from sensordatatype where name = ?), runtime=?, tool=?, " 217 + " resource=?, xmlsensordata=?, xmlsensordataRef=?, lastmod=?" 218 + " WHERE owner_id=(select id from hackyuser where email = ?) AND tstamp=?"); 219 // Order: Id Owner_Id Tstamp Sdt_id Runtime Tool Resource LastMod 220 // XmlSensorData XmlSensorDataRef 221 preparedStatement.setString(1, data.getSensorDataType()); 222 preparedStatement.setTimestamp(2, Tstamp.makeTimestamp(data.getRuntime())); 223 preparedStatement.setString(3, data.getTool()); 224 preparedStatement.setString(4, data.getResource()); 225 preparedStatement.setString(5, xmlSensorData); 226 preparedStatement.setString(6, xmlSensorDataRef); 227 preparedStatement.setTimestamp(7, new Timestamp(new Date().getTime())); 228 preparedStatement.setString(8, data.getOwner()); 229 preparedStatement.setTimestamp(9, Tstamp.makeTimestamp(data.getTimestamp())); 230 preparedStatement.executeUpdate(); 231 232 String query = "SELECT sensordata.id FROM SensorData, hackyuser where email = '" 233 + data.getOwner() + "' and sensordata.owner_id = hackyuser.id AND " 234 + " SensorData.Tstamp = '" + Tstamp.makeTimestamp(data.getTimestamp()) + "'"; 235 sensordataIdStatement = this.connection.prepareStatement(query); 236 sensordataIdResultSet = sensordataIdStatement.executeQuery(); 237 Object uuid = null; 238 if (sensordataIdResultSet.next()) { 239 uuid = sensordataIdResultSet.getObject(1); 240 } 241 242 this.storeSensorDataProperties(uuid, xmlSensorData, true); 243 } 244 catch (SQLException f) { 245 this.logger.info(postgresError + StackTrace.toString(f)); 246 } 247 finally { 248 try { 249 sensordataIdStatement.close(); 250 sensordataIdResultSet.close(); 251 } 252 catch (SQLException e2) { 253 this.logger.warning(errorClosingMsg + StackTrace.toString(e2)); 254 } 255 } 256 this.logger.fine("Postgres: Updated " + data.getOwner() + " " + data.getTimestamp()); 257 } 258 } 259 finally { 260 try { 261 preparedStatement.close(); 262 } 263 catch (SQLException e) { 264 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 265 } 266 } 267 return true; 268 } 269 270 /** 271 * Stores the optional properties found in the specified sensor data string. 272 * The properties are related to a sensor data record via the specified id. 273 * @param sensorDataId the specified sensor data record id. 274 * @param xmlSensorData the string containing the sensor data properties. 275 * @param isUpdating true if the properties are to be updated, false to insert 276 * a new record. 277 */ 278 private void storeSensorDataProperties(Object sensorDataId, String xmlSensorData, 279 boolean isUpdating) { 280 PreparedStatement s = null; 281 282 try { 283 // conn = DriverManager.getConnection(connectionURL); 284 Map<String, String> keyValMap = this.getPropertiesMap(xmlSensorData); 285 // The sensordata properties exists, let's update it. 286 if (isUpdating) { 287 for (Map.Entry<String, String> entry : keyValMap.entrySet()) { 288 s = this.connection.prepareStatement("UPDATE SensorData_Properties SET " 289 + " Key=?, Value=?" + " WHERE SensorData_Id=?"); 290 s.setString(1, entry.getKey()); 291 s.setString(2, entry.getValue()); 292 s.setObject(3, sensorDataId, Types.OTHER); 293 s.executeUpdate(); 294 this.logger.fine("Postgres: Update Key=" + entry.getKey() + ", Value=" 295 + entry.getValue()); 296 } 297 } 298 else { // No properties, let's create a new record. 299 for (Map.Entry<String, String> entry : keyValMap.entrySet()) { 300 s = this.connection 301 .prepareStatement("INSERT INTO SensorData_Properties VALUES (?, ?, ?, ?)"); 302 // Order: Id SensorData_Id Key Value 303 // XmlSensorData XmlSensorDataRef 304 s.setObject(1, UUID.randomUUID(), Types.OTHER); 305 s.setObject(2, sensorDataId, Types.OTHER); 306 s.setString(3, entry.getKey()); 307 s.setString(4, entry.getValue()); 308 s.executeUpdate(); 309 this.logger.fine("Postgres: Inserted Key=" + entry.getKey() + ", Value=" 310 + entry.getValue()); 311 } 312 } 313 } 314 catch (SQLException e) { 315 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 316 } 317 finally { 318 try { 319 if (s != null) { 320 s.close(); 321 } 322 } 323 catch (SQLException e) { 324 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 325 } 326 } 327 } 328 329 /** 330 * The helper method returning a a mapping of Property Key-> Property Value. 331 * @param xmlSensorData the xml string with the property keys and values. 332 * @return the properties map. 333 */ 334 private Map<String, String> getPropertiesMap(String xmlSensorData) { 335 try { 336 SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); 337 SensorPropertiesHandler handler = new SensorPropertiesHandler(); 338 parser.parse(new ByteArrayInputStream(xmlSensorData.getBytes()), handler); 339 return handler.getKeyValMap(); 340 } 341 catch (Exception e) { 342 this.logger 343 .warning("Error reading the sensor data properties:" + StackTrace.toString(e)); 344 } 345 return new HashMap<String, String>(); 346 } 347 348 /** 349 * The helper method used to return a HackyUser ResultSet which has the 350 * specified email. 351 * @param conn the connection used to obtain the record. 352 * @param email the email associated with the record. 353 * @return the result set containing the record with the specified email. 354 * @throws SQLException thrown if the record could not be returned. 355 */ 356 private ResultSet getUserRecord(Connection conn, String email) throws SQLException { 357 String query = "SELECT * FROM HackyUser where Email='" + email + "'"; 358 PreparedStatement statement = conn.prepareStatement(query, 359 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 360 return statement.executeQuery(); 361 } 362 363 /** 364 * The helper method used to return a SensorDataType ResultSet which has the 365 * specified sensor data type name.. 366 * @param conn the connection used to obtain the record. 367 * @param sdtName the name of sensor data type to find. 368 * @return the result set containing the record with the specified sdt. 369 * @throws SQLException thrown if the record could not be returned. 370 */ 371 private ResultSet getSdtRecord(Connection conn, String sdtName) throws SQLException { 372 String query = "SELECT * FROM SensorDataType where Name='" + sdtName + "'"; 373 PreparedStatement statement = conn.prepareStatement(query, 374 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 375 return statement.executeQuery(); 376 } 377 378 /** 379 * The helper method used to return a Project ResultSet with the specified 380 * project name. 381 * @param conn the connection used to obtain the record. 382 * @param projectName the name of project. 383 * @return the result set containing the record with the specified project 384 * name. 385 * @throws SQLException thrown if the record could not be returned. 386 */ 387 private ResultSet getProjectRecord(Connection conn, String projectName) throws SQLException { 388 String query = "SELECT * FROM Project where ProjectName='" + projectName + "'"; 389 PreparedStatement statement = conn.prepareStatement(query, 390 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 391 return statement.executeQuery(); 392 } 393 394 /** 395 * The helper method used to return a ProjectUri ResultSet with the related to 396 * the project with the specified project name. 397 * @param conn the connection used to obtain the record. 398 * @param projectName the name of project. 399 * @return the result set containing the record with the specified project 400 * uri. 401 * @throws SQLException thrown if the record could not be returned. 402 */ 403 private ResultSet getProjectUriRecords(Connection conn, String projectName) 404 throws SQLException { 405 String query = "SELECT * FROM ProjectUri where Project_Id IN " 406 + "(SELECT Id FROM Project WHERE ProjectName='" + projectName + "')"; 407 PreparedStatement statement = conn.prepareStatement(query, 408 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); 409 return statement.executeQuery(); 410 } 411 412 /** {@inheritDoc} */ 413 @Override 414 public boolean isFreshlyCreated() { 415 return this.isFreshlyCreated; 416 } 417 418 /** {@inheritDoc} */ 419 @Override 420 public String getSensorDataIndex() { 421 String st = "SELECT XmlSensorDataRef FROM SensorData"; 422 return getIndex("SensorData", st); // NOPMD (See below) 423 } 424 425 /* 426 * Interestingly, I could not refactor out the string "SensorData" to avoid 427 * the PMD error resulting from multiple occurrences of the same string. This 428 * is because if I made it a private String, then Findbugs would throw a 429 * warning asking for it to be static: 430 * 431 * private static final String sensorData = "SensorData"; 432 * 433 * However, the above declaration causes the system to deadlock! So, I'm just 434 * ignoring the PMD error. 435 */ 436 437 /** {@inheritDoc} */ 438 @Override 439 public String getSensorDataIndex(User user) { 440 ResultSet ownerResultSet = null; 441 try { 442 ownerResultSet = this.getUserRecord(this.connection, user.getEmail()); 443 if (ownerResultSet.next()) { 444 String st = "SELECT XmlSensorDataRef FROM SensorData WHERE" + ownerIdEquals 445 + ownerResultSet.getObject("Id") + "'"; 446 return getIndex("SensorData", st); 447 } 448 } 449 catch (SQLException e) { 450 this.logger.info(postgresError + StackTrace.toString(e)); 451 } 452 finally { 453 try { 454 ownerResultSet.close(); 455 } 456 catch (SQLException e) { 457 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 458 } 459 } 460 return ""; 461 } 462 463 /** {@inheritDoc} */ 464 @Override 465 public String getSensorDataIndex(User user, String sdtName) { 466 ResultSet ownerResults = null; 467 ResultSet sdtResults = null; 468 469 try { 470 ownerResults = this.getUserRecord(this.connection, user.getEmail()); 471 sdtResults = this.getSdtRecord(this.connection, sdtName); 472 if (ownerResults.next() && sdtResults.next()) { 473 String st = selectPrefix + ownerIdEquals + ownerResults.getObject("Id") 474 + quoteAndClause + sdtIdEquals + sdtResults.getObject("Id") + "'" + orderByTstamp; 475 return getIndex("SensorData", st); 476 } 477 } 478 catch (SQLException e) { 479 this.logger.info(postgresError + StackTrace.toString(e)); 480 } 481 finally { 482 try { 483 ownerResults.close(); 484 sdtResults.close(); 485 } 486 catch (SQLException e) { 487 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 488 } 489 } 490 return ""; 491 } 492 493 /** {@inheritDoc} */ 494 @Override 495 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 496 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt) { 497 String statement = null; 498 ResultSet results = null; 499 500 try { 501 if (sdt == null) { // Retrieve sensor data of all SDTs 502 statement = selectPrefix + constructOwnerClause(users) + andClause + " (" 503 + tstampBetweenTstamp + Tstamp.makeTimestamp(startTime) + quoteAndClause 504 + timeStampClause + Tstamp.makeTimestamp(endTime) + "')" 505 + constructLikeClauses(uriPatterns) + orderByTstamp; 506 } 507 else { // Retrieve sensor data of the specified SDT. 508 results = this.getSdtRecord(this.connection, sdt); 509 if (results.next()) { 510 statement = selectPrefix + constructOwnerClause(users) + andClause + sdtIdEquals 511 + results.getObject("Id") + quoteAndClause + " (" + tstampBetweenTstamp 512 + Tstamp.makeTimestamp(startTime) + quoteAndClause + timeStampClause 513 + Tstamp.makeTimestamp(endTime) + "')" + constructLikeClauses(uriPatterns) 514 + orderByTstamp; 515 } 516 else { 517 return ""; 518 } 519 } 520 } 521 catch (SQLException e) { 522 this.logger.info(postgresError + StackTrace.toString(e)); 523 return ""; 524 } 525 finally { 526 try { 527 if (results != null) { 528 results.close(); 529 } 530 } 531 catch (SQLException e) { 532 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 533 } 534 } 535 return getIndex("SensorData", statement); 536 } 537 538 /** {@inheritDoc} */ 539 @Override 540 public String getProjectSensorDataSnapshot(List<User> users, XMLGregorianCalendar startTime, 541 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt, String tool) { 542 String statement = null; 543 ResultSet results = null; 544 try { 545 results = this.getSdtRecord(this.connection, sdt); 546 if (results.next()) { 547 Object sdtId = results.getObject("Id"); 548 if (tool == null) { // Retrieve sensor data with latest runtime 549 // regardless 550 // of tool. 551 statement = selectSnapshot + constructOwnerClause(users) + andClause + sdtIdEquals 552 + sdtId + quoteAndClause + " (" + tstampBetweenTstamp 553 + Tstamp.makeTimestamp(startTime) + quoteAndClause + timeStampClause 554 + Tstamp.makeTimestamp(endTime) + "')" // NOPMD 555 + constructLikeClauses(uriPatterns) + orderByRuntime; 556 } 557 else { // Retrieve sensor data with the latest runtime for the 558 // specified 559 // tool. 560 statement = selectSnapshot + constructOwnerClause(users) + andClause + sdtIdEquals 561 + sdtId + quoteAndClause + toolEquals + tool + quoteAndClause + " (" 562 + tstampBetweenTstamp + Tstamp.makeTimestamp(startTime) + quoteAndClause 563 + timeStampClause + Tstamp.makeTimestamp(endTime) + "')" 564 + constructLikeClauses(uriPatterns) + orderByRuntime; 565 } 566 } 567 else { 568 return ""; 569 } 570 } 571 catch (SQLException e) { 572 this.logger.info(postgresError + StackTrace.toString(e)); 573 return ""; 574 } 575 finally { 576 try { 577 results.close(); 578 } 579 catch (SQLException e) { 580 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 581 } 582 } 583 // Generate a SensorDataIndex string that contains only entries with the 584 // latest runtime. 585 return getSnapshotIndex(statement); 586 } 587 588 /** {@inheritDoc} */ 589 @Override 590 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 591 XMLGregorianCalendar endTime, List<String> uriPatterns, int startIndex, int maxInstances) { 592 String statement = selectPrefix + constructOwnerClause(users) + andClause + " (" 593 + tstampBetweenTstamp + Tstamp.makeTimestamp(startTime) + quoteAndClause 594 + timeStampClause + Tstamp.makeTimestamp(endTime) + "')" 595 + constructLikeClauses(uriPatterns) + orderByTstamp; 596 return getIndex("SensorData", statement, startIndex, maxInstances); 597 } 598 599 /** 600 * Constructs a set of LIKE clauses corresponding to the passed set of 601 * UriPatterns. 602 * <p> 603 * Each UriPattern is translated in the following way: 604 * <ul> 605 * <li>If there is an occurrence of a "\" or a "/" in the UriPattern, then two 606 * translated UriPatterns are generated, one with all "\" replaced with "/", 607 * and one with all "/" replaced with "\". 608 * <li>The escape character is "\", unless we are generating a LIKE clause 609 * containing a "\", in which case the escape character will be "/". 610 * <li>All occurrences of "%" in the UriPattern are escaped. 611 * <li>All occurrences of "_" in the UriPattern are escaped. 612 * <li>All occurrences of "*" are changed to "%". 613 * </ul> 614 * The new set of 'translated' UriPatterns are now used to generate a set of 615 * LIKE clauses with the following form: 616 * 617 * <pre> 618 * (RESOURCE like 'translatedUriPattern1' escape 'escapeChar1') OR 619 * (RESOURCE like 'translatedUriPattern2' escape 'escapeChar2') .. 620 * </pre> 621 * 622 * <p> 623 * There is one special case. If the List(UriPattern) is null, empty, or 624 * consists of exactly one UriPattern which is "**" or "*", then the empty 625 * string is returned. This is an optimization for the common case where all 626 * resources should be matched and so we don't need any LIKE clauses. 627 * <p> 628 * We return either the empty string (""), or else a string of the form: " AND 629 * ([like clause] AND [like clause] ... )" This enables the return value to be 630 * appended to the SELECT statement. 631 * <p> 632 * This method is static and package private to support testing. See the class 633 * TestConstructUriPattern for example invocations and expected return values. 634 * 635 * @param uriPatterns The list of uriPatterns. 636 * @return The String to be used in the where clause to check for resource 637 * correctness. 638 */ 639 static String constructLikeClauses(List<String> uriPatterns) { 640 // Deal with special case. UriPatterns is null, or empty, or "**", or "*" 641 if (((uriPatterns == null) || uriPatterns.isEmpty()) 642 || ((uriPatterns.size() == 1) && uriPatterns.get(0).equals("**")) 643 || ((uriPatterns.size() == 1) && uriPatterns.get(0).equals("*"))) { 644 return ""; 645 } 646 // Deal with the potential presence of path separator character in 647 // UriPattern. 648 List<String> translatedPatterns = new ArrayList<String>(); 649 for (String pattern : uriPatterns) { 650 if (pattern.contains("\\") || pattern.contains("/")) { 651 translatedPatterns.add(pattern.replace('\\', '/')); 652 653 // Postgres allows POSIX pattern matching so '\' must be escaped. 654 translatedPatterns.add(pattern.replace("\\", "\\" + "\\")); 655 translatedPatterns.add(pattern.replace("/", "\\" + "\\")); 656 } 657 else { 658 translatedPatterns.add(pattern); 659 } 660 } 661 // Now escape the SQL wildcards, and make our UriPattern wildcard into the 662 // SQL wildcard. 663 for (int i = 0; i < translatedPatterns.size(); i++) { 664 String pattern = translatedPatterns.get(i); 665 pattern = pattern.replace("%", "`%"); // used to be / 666 pattern = pattern.replace("_", "`_"); // used to be / 667 pattern = pattern.replace('*', '%'); 668 translatedPatterns.set(i, pattern); 669 } 670 671 // Now generate the return string: " AND (<like clause> OR <like clause> ... 672 // )". 673 StringBuffer buff = new StringBuffer(); 674 buff.append(" AND ("); 675 if (!translatedPatterns.isEmpty()) { 676 buff.append(makeLikeClause(translatedPatterns, "`")); // used to be / 677 } 678 679 buff.append(')'); 680 681 return buff.toString(); 682 } 683 684 /** 685 * Creates a set of LIKE clauses with the specified escape character. 686 * @param patterns The patterns. 687 * @param escape The escape character. 688 * @return The StringBuffer with the LIKE clauses. 689 */ 690 private static StringBuffer makeLikeClause(List<String> patterns, String escape) { 691 StringBuffer buff = new StringBuffer(); // NOPMD generates false warning 692 // about buff size. 693 if (patterns.isEmpty()) { 694 return buff; 695 } 696 for (Iterator<String> i = patterns.iterator(); i.hasNext();) { 697 String pattern = i.next(); 698 buff.append("(RESOURCE LIKE '"); 699 buff.append(pattern); 700 buff.append("' ESCAPE '"); 701 buff.append(escape); 702 buff.append("')"); 703 if (i.hasNext()) { 704 buff.append(" OR "); 705 } 706 } 707 buff.append(' '); 708 return buff; 709 } 710 711 /** 712 * Constructs a clause of form ( OWNER = 'user1' [ OR OWNER = 'user2']* ). 713 * @param users The list of users whose ownership is being searched for. 714 * @return The String to be used in the where clause to check for ownership. 715 */ 716 private String constructOwnerClause(List<User> users) { 717 StringBuffer buff = new StringBuffer(); 718 buff.append('('); 719 // Use old school iterator so we can do a hasNext() inside the loop. 720 ResultSet results = null; 721 try { 722 for (Iterator<User> i = users.iterator(); i.hasNext();) { 723 User user = i.next(); 724 results = this.getUserRecord(this.connection, user.getEmail()); 725 if (results.next()) { 726 buff.append(ownerIdEquals); 727 buff.append(results.getObject("Id")); 728 buff.append('\''); 729 if (i.hasNext()) { 730 buff.append(" OR"); 731 } 732 } 733 } 734 buff.append(") "); 735 return buff.toString(); 736 } 737 catch (SQLException e) { 738 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 739 } 740 finally { 741 try { 742 results.close(); 743 } 744 catch (SQLException e) { 745 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 746 } 747 } 748 return ""; 749 } 750 751 /** {@inheritDoc} */ 752 @Override 753 public String getSensorDataIndexLastMod(User user, XMLGregorianCalendar lastModStartTime, 754 XMLGregorianCalendar lastModEndTime) { 755 Connection conn = null; 756 ResultSet ownerResults = null; 757 try { 758 conn = DriverManager.getConnection(connectionURL); 759 ownerResults = this.getUserRecord(conn, user.getEmail()); 760 if (ownerResults.next()) { 761 String statement = selectPrefix + ownerIdEquals + ownerResults.getObject("Id") 762 + quoteAndClause + " LastMod BETWEEN TIMESTAMP '" 763 + Tstamp.makeTimestamp(lastModStartTime) + "' AND " + timeStampClause 764 + Tstamp.makeTimestamp(lastModEndTime) + "'"; 765 return getIndex("SensorData", statement); 766 } 767 } 768 catch (SQLException e) { 769 this.logger.info(postgresError + StackTrace.toString(e)); 770 } 771 finally { 772 try { 773 conn.close(); 774 ownerResults.close(); 775 } 776 catch (SQLException e) { 777 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 778 } 779 } 780 return ""; 781 } 782 783 /** {@inheritDoc} */ 784 @Override 785 public boolean hasSensorData(User user, XMLGregorianCalendar timestamp) { 786 PreparedStatement s = null; 787 ResultSet rs = null; 788 ResultSet ownerResults = null; 789 boolean isFound = false; 790 try { 791 ownerResults = this.getUserRecord(this.connection, user.getEmail()); 792 if (ownerResults.next()) { 793 String statement = selectPrefix + ownerIdEquals + ownerResults.getObject("Id") 794 + quoteAndClause + " Tstamp='" + Tstamp.makeTimestamp(timestamp) + "'"; 795 server.getLogger().fine(executeQueryMsg + statement); 796 s = this.connection.prepareStatement(statement); 797 rs = s.executeQuery(); 798 // If a record was retrieved, we'll enter the loop, otherwise we won't. 799 while (rs.next()) { 800 isFound = true; 801 } 802 } 803 } 804 catch (SQLException e) { 805 this.logger.info("Postgres: Error in hasSensorData()" + StackTrace.toString(e)); 806 } 807 finally { 808 try { 809 if (s != null) { 810 s.close(); 811 } 812 ownerResults.close(); 813 if (rs != null) { 814 rs.close(); 815 } 816 } 817 catch (SQLException e) { 818 this.logger.warning("Postgres: Error closing the connection" + StackTrace.toString(e)); 819 } 820 } 821 return isFound; 822 } 823 824 /** {@inheritDoc} */ 825 @Override 826 public void deleteSensorData(User user, XMLGregorianCalendar timestamp) { 827 ResultSet ownerResults = null; 828 829 try { 830 ownerResults = this.getUserRecord(this.connection, user.getEmail()); 831 if (ownerResults.next()) { 832 String statement = "DELETE FROM SensorData WHERE " + ownerIdEquals 833 + ownerResults.getObject("Id") + quoteAndClause + " Tstamp='" 834 + Tstamp.makeTimestamp(timestamp) + "'"; 835 deleteResource(statement); 836 } 837 } 838 catch (SQLException e) { 839 this.logger.info(postgresError + StackTrace.toString(e)); 840 } 841 finally { 842 try { 843 ownerResults.close(); 844 } 845 catch (SQLException e) { 846 this.logger.warning("Postgres: Error closing the connection" + StackTrace.toString(e)); 847 } 848 } 849 } 850 851 /** {@inheritDoc} */ 852 @Override 853 public void deleteSensorData(User user) { 854 // no op for now. 855 // if (true) { 856 // this.logger.fine("Postgres: Not Deleted" + user.getEmail()); 857 // return; 858 // } 859 860 ResultSet ownerResults = null; 861 try { 862 ownerResults = this.getUserRecord(this.connection, user.getEmail()); 863 if (ownerResults.next()) { 864 String statement = "DELETE FROM SensorData WHERE" + ownerIdEquals 865 + ownerResults.getObject("Id") + "'"; 866 deleteResource(statement); 867 } 868 } 869 catch (SQLException e) { 870 this.logger.info(postgresError + StackTrace.toString(e)); 871 } 872 finally { 873 try { 874 ownerResults.close(); 875 } 876 catch (SQLException e) { 877 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 878 } 879 } 880 } 881 882 /** {@inheritDoc} */ 883 @Override 884 public String getSensorData(User user, XMLGregorianCalendar timestamp) { 885 ResultSet ownerResults = null; 886 try { 887 ownerResults = this.getUserRecord(this.connection, user.getEmail()); 888 if (ownerResults.next()) { 889 String statement = "SELECT XmlSensorData FROM SensorData WHERE" + ownerIdEquals 890 + ownerResults.getObject("Id") + quoteAndClause + " Tstamp='" 891 + Tstamp.makeTimestamp(timestamp) + "'"; 892 return getResource("SensorData", statement); 893 } 894 } 895 catch (SQLException e) { 896 this.logger.info(postgresError + StackTrace.toString(e)); 897 } 898 finally { 899 try { 900 ownerResults.close(); 901 } 902 catch (SQLException e) { 903 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 904 } 905 } 906 return ""; 907 } 908 909 /** {@inheritDoc} */ 910 @Override 911 public boolean storeSensorDataType(SensorDataType sdt, String xmlSensorDataType, 912 String xmlSensorDataTypeRef) { 913 PreparedStatement countStatement = null; 914 PreparedStatement dataStatement = null; 915 ResultSet countResultSet = null; 916 try { 917 String countQuery = "SELECT * FROM SensorDataType where name='" + sdt.getName() + "'"; 918 countStatement = this.connection.prepareStatement(countQuery, 919 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 920 countResultSet = countStatement.executeQuery(); 921 // If an SDT with the same name exists, let's update it. 922 if (countResultSet.next()) { 923 dataStatement = this.connection.prepareStatement("UPDATE SensorDataType SET " 924 + " LastMod=?," + "XmlSensorDataType=?, " + " XmlSensorDataTypeRef=?" 925 + " WHERE Name=?"); 926 dataStatement.setTimestamp(1, new Timestamp(new Date().getTime())); 927 dataStatement.setString(2, xmlSensorDataType); 928 dataStatement.setString(3, xmlSensorDataTypeRef); 929 dataStatement.setString(4, sdt.getName()); 930 dataStatement.executeUpdate(); 931 this.logger.fine("Postgres: Updated SDT " + sdt.getName()); 932 } 933 // Insert the new SDT. 934 else { 935 dataStatement = this.connection 936 .prepareStatement("INSERT INTO SensorDataType VALUES (?, ?, ?, ?, ?)"); 937 // Order: id name lastmod xmlsensordatatype xmlsensordatatyperef 938 dataStatement.setObject(1, UUID.randomUUID(), Types.OTHER); 939 dataStatement.setString(2, sdt.getName()); 940 dataStatement.setTimestamp(3, new Timestamp(new Date().getTime())); 941 dataStatement.setString(4, xmlSensorDataType); 942 dataStatement.setString(5, xmlSensorDataTypeRef); 943 dataStatement.executeUpdate(); 944 this.logger.fine("Postgres: Inserted SDT" + sdt.getName()); 945 } 946 } 947 catch (SQLException e) { 948 this.logger.info(postgresError + StackTrace.toString(e)); 949 } 950 finally { 951 try { 952 countStatement.close(); 953 dataStatement.close(); 954 countResultSet.close(); 955 } 956 catch (SQLException e) { 957 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 958 } 959 } 960 return true; 961 } 962 963 /** {@inheritDoc} */ 964 @Override 965 public void deleteSensorDataType(String sdtName) { 966 String statement = "DELETE FROM SensorDataType WHERE Name='" + sdtName + "'"; 967 deleteResource(statement); 968 } 969 970 /** {@inheritDoc} */ 971 @Override 972 public String getSensorDataTypeIndex() { 973 return getIndex("SensorDataType", "SELECT XmlSensorDataTypeRef FROM SensorDataType"); 974 } 975 976 /** {@inheritDoc} */ 977 @Override 978 public String getSensorDataType(String sdtName) { 979 String statement = "SELECT XmlSensorDataType FROM SensorDataType WHERE Name = '" + sdtName 980 + "'"; 981 return getResource("SensorDataType", statement); 982 } 983 984 /** {@inheritDoc} */ 985 @Override 986 public void deleteUser(String email) { 987 String statement = "DELETE FROM HackyUser WHERE Email='" + email + "'"; 988 deleteResource(statement); 989 } 990 991 /** {@inheritDoc} */ 992 @Override 993 public String getUser(String email) { 994 String statement = "SELECT XmlUser FROM HackyUser WHERE Email = '" + email + "'"; 995 return getResource("User", statement); 996 } 997 998 /** {@inheritDoc} */ 999 @Override 1000 public String getUserIndex() { 1001 return getIndex("User", "SELECT XmlUserRef FROM HackyUser"); 1002 } 1003 1004 /** {@inheritDoc} */ 1005 @Override 1006 public boolean storeUser(User user, String xmlUser, String xmlUserRef) { 1007 PreparedStatement countStatement = null; 1008 PreparedStatement userStatement = null; 1009 ResultSet countResultSet = null; 1010 try { 1011 String countQuery = "SELECT * FROM HackyUser where email='" + user.getEmail() + "'"; 1012 countStatement = this.connection.prepareStatement(countQuery, 1013 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 1014 countResultSet = countStatement.executeQuery(); 1015 1016 // If the user with the same email exists, perform an update. 1017 if (countResultSet.next()) { 1018 userStatement = this.connection.prepareStatement("UPDATE HackyUser SET " 1019 + " Password=?, " + " Role=?, " + " LastMod=?," + " XmlUser=?, " 1020 + " XmlUserRef=? " + " WHERE Email=?"); 1021 userStatement.setString(1, user.getPassword()); 1022 userStatement.setString(2, user.getRole()); 1023 userStatement.setTimestamp(3, new Timestamp(new Date().getTime())); 1024 userStatement.setString(4, xmlUser); 1025 userStatement.setString(5, xmlUserRef); 1026 userStatement.setString(6, user.getEmail()); 1027 userStatement.executeUpdate(); 1028 this.logger.fine("Postgres: Updated User " + user.getEmail()); 1029 } 1030 // Insert the new user into the database. 1031 else { 1032 userStatement = this.connection 1033 .prepareStatement("INSERT INTO HackyUser VALUES (?, ?, ?, ?, ?, ?, ?)"); 1034 // Order: id email password role lastmod xmluser xmluserref 1035 userStatement.setObject(1, UUID.randomUUID(), Types.OTHER); 1036 userStatement.setString(2, user.getEmail()); 1037 userStatement.setString(3, user.getPassword()); 1038 userStatement.setString(4, user.getRole()); 1039 userStatement.setTimestamp(5, new Timestamp(new Date().getTime())); 1040 userStatement.setString(6, xmlUser); 1041 userStatement.setString(7, xmlUserRef); 1042 userStatement.executeUpdate(); 1043 this.logger.fine("Postgres: Inserted User" + user.getEmail()); 1044 } 1045 } 1046 catch (SQLException e) { 1047 this.logger.info(postgresError + StackTrace.toString(e)); 1048 } 1049 finally { 1050 try { 1051 userStatement.close(); 1052 countStatement.close(); 1053 countResultSet.close(); 1054 } 1055 catch (SQLException e) { 1056 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1057 } 1058 } 1059 return true; 1060 } 1061 1062 /** {@inheritDoc} */ 1063 @Override 1064 public void deleteProject(User owner, String projectName) { 1065 ResultSet projectUriResults = null; 1066 ResultSet userResults = null; 1067 try { 1068 // Removes the ProjectUri records associated with the project. 1069 projectUriResults = this.getProjectUriRecords(this.connection, projectName); 1070 if (projectUriResults.next()) { 1071 String statement = "DELETE FROM ProjectUri WHERE Id='" 1072 + projectUriResults.getObject("Id") + "'"; 1073 deleteResource(statement); 1074 } 1075 1076 // Removes the Project. 1077 userResults = this.getUserRecord(this.connection, owner.getEmail()); 1078 if (userResults.next()) { 1079 String statement = "DELETE FROM Project WHERE " + ownerIdEquals 1080 + userResults.getObject("Id") + quoteAndClause + " ProjectName = '" + projectName 1081 + "'"; 1082 deleteResource(statement); 1083 } 1084 } 1085 catch (SQLException e) { 1086 this.logger.info(postgresError + StackTrace.toString(e)); 1087 } 1088 finally { 1089 try { 1090 projectUriResults.close(); 1091 userResults.close(); 1092 } 1093 catch (SQLException e) { 1094 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1095 } 1096 } 1097 } 1098 1099 /** {@inheritDoc} */ 1100 @Override 1101 public String getProject(User owner, String projectName) { 1102 ResultSet userResults = null; 1103 try { 1104 userResults = this.getUserRecord(this.connection, owner.getEmail()); 1105 if (userResults.next()) { 1106 Object userId = userResults.getObject("Id"); 1107 String statement = "SELECT XmlProject FROM Project WHERE" + ownerIdEquals + userId 1108 + quoteAndClause + " ProjectName ='" + projectName + "'"; 1109 return getResource("Project", statement); 1110 } 1111 } 1112 catch (SQLException e) { 1113 this.logger.info(postgresError + StackTrace.toString(e)); 1114 } 1115 finally { 1116 try { 1117 userResults.close(); 1118 } 1119 catch (SQLException e) { 1120 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1121 } 1122 } 1123 return ""; 1124 } 1125 1126 /** {@inheritDoc} */ 1127 @Override 1128 public String getProjectIndex() { 1129 return getIndex("Project", "SELECT XmlProjectRef FROM Project"); 1130 } 1131 1132 /** {@inheritDoc} */ 1133 @Override 1134 public ProjectSummary getProjectSummary(List<User> users, XMLGregorianCalendar startTime, 1135 XMLGregorianCalendar endTime, List<String> uriPatterns, String href) { 1136 PreparedStatement dataStatement = null; 1137 ResultSet dataResultSet = null; 1138 ResultSet sdtResultSet = null; 1139 PreparedStatement sdtStatement = null; 1140 1141 // Create the [SDT, Tool] -> NumInstances data structure. 1142 Map<String, Map<String, Integer>> sdtInstances = new HashMap<String, Map<String, Integer>>(); 1143 try { 1144 // Make a statement to return all SensorData for this project in the time 1145 // period. 1146 String statement = "SELECT Sdt_Id, Tool FROM SensorData WHERE " 1147 + constructOwnerClause(users) + andClause + " (" + tstampBetweenTstamp 1148 + Tstamp.makeTimestamp(startTime) + quoteAndClause + timeStampClause 1149 + Tstamp.makeTimestamp(endTime) + "')" + constructLikeClauses(uriPatterns); 1150 1151 // Retrieve the sensordata for this project and time period. 1152 dataStatement = this.connection.prepareStatement(statement); 1153 dataResultSet = dataStatement.executeQuery(); 1154 // Loop through all retrieved SensorData records. 1155 while (dataResultSet.next()) { 1156 String sdt = ""; 1157 String sdtId = dataResultSet.getString("Sdt_Id"); 1158 String tool = dataResultSet.getString("Tool"); 1159 1160 String sdtQuery = "SELECT * FROM SensorDataType WHERE ID='" + sdtId + "';"; 1161 sdtStatement = this.connection.prepareStatement(sdtQuery, 1162 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 1163 sdtResultSet = sdtStatement.executeQuery(); 1164 if (sdtResultSet.next()) { 1165 // Don't want null SDTs or Tools, call them the empty string instead. 1166 sdt = sdtResultSet.getString("name"); 1167 } 1168 if (tool == null) { 1169 tool = ""; 1170 } 1171 1172 // Now update our numInstance data structure. 1173 // First, initialize the data structure if this is a new SDT. 1174 if (!sdtInstances.containsKey(sdt)) { 1175 Map<String, Integer> tool2NumInstances = new HashMap<String, Integer>(); 1176 tool2NumInstances.put(tool, 0); 1177 sdtInstances.put(sdt, tool2NumInstances); 1178 } 1179 Map<String, Integer> tool2NumInstances = sdtInstances.get(sdt); 1180 // Second, initialize the data structure if this is a new tool for a 1181 // preexisting SDT. 1182 if (tool2NumInstances.get(tool) == null) { 1183 tool2NumInstances.put(tool, 0); 1184 } 1185 // Finally, increment this entry. 1186 tool2NumInstances.put(tool, tool2NumInstances.get(tool) + 1); 1187 } 1188 } 1189 catch (SQLException e) { 1190 this.logger.info("Postgres: Error in getProjectSummary()" + StackTrace.toString(e)); 1191 } 1192 finally { 1193 try { 1194 dataResultSet.close(); 1195 dataStatement.close(); 1196 if (sdtStatement != null) { 1197 sdtStatement.close(); 1198 } 1199 if (sdtResultSet != null) { 1200 sdtResultSet.close(); 1201 } 1202 } 1203 catch (SQLException e) { 1204 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1205 } 1206 } 1207 1208 // Now create the project summary object from our data structures. 1209 return makeProjectSummary(href, startTime, endTime, sdtInstances); 1210 } 1211 1212 /** 1213 * Creates a ProjectSummary instances from the passed data. 1214 * @param href The Href representing this resource. 1215 * @param startTime The startTime for this data. 1216 * @param endTime The endTime for this data. 1217 * @param sdtInstances The data structure containing the instances. 1218 * @return The ProjectSummary instance. 1219 */ 1220 private ProjectSummary makeProjectSummary(String href, XMLGregorianCalendar startTime, 1221 XMLGregorianCalendar endTime, Map<String, Map<String, Integer>> sdtInstances) { 1222 ProjectSummary projectSummary = new ProjectSummary(); 1223 projectSummary.setHref(href); 1224 projectSummary.setStartTime(startTime); 1225 projectSummary.setEndTime(endTime); 1226 projectSummary.setLastMod(Tstamp.makeTimestamp()); 1227 SensorDataSummaries summaries = new SensorDataSummaries(); 1228 projectSummary.setSensorDataSummaries(summaries); 1229 int totalInstances = 0; 1230 for (Map.Entry<String, Map<String, Integer>> entry : sdtInstances.entrySet()) { 1231 String sdt = entry.getKey(); 1232 Map<String, Integer> tool2NumInstances = entry.getValue(); 1233 for (Map.Entry<String, Integer> entry2 : tool2NumInstances.entrySet()) { 1234 SensorDataSummary summary = new SensorDataSummary(); 1235 summary.setSensorDataType(sdt); 1236 summary.setTool(entry2.getKey()); 1237 int numInstances = entry2.getValue(); 1238 totalInstances += numInstances; 1239 summary.setNumInstances(BigInteger.valueOf(numInstances)); 1240 summaries.getSensorDataSummary().add(summary); 1241 } 1242 } 1243 summaries.setNumInstances(BigInteger.valueOf(totalInstances)); 1244 return projectSummary; 1245 } 1246 1247 /** {@inheritDoc} */ 1248 @Override 1249 public boolean storeProject(Project project, String xmlProject, String xmlProjectRef) { 1250 PreparedStatement countStatement = null; 1251 PreparedStatement projectStatement = null; 1252 PreparedStatement projectUriStatement = null; 1253 ResultSet countResultSet = null; 1254 ResultSet userResultSet = null; 1255 ResultSet projectUriResultSet = null; 1256 ResultSet projectResultSet = null; 1257 try { 1258 // Get the amount of projects with the specified name. 1259 String countQuery = "SELECT * FROM Project where ProjectName='" + project.getName() 1260 + "'"; 1261 countStatement = this.connection.prepareStatement(countQuery, 1262 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 1263 countResultSet = countStatement.executeQuery(); 1264 1265 userResultSet = this.getUserRecord(this.connection, project.getOwner()); 1266 if (userResultSet.next()) { 1267 // Get the user associated with the inserted/updated project. 1268 Object ownerId = userResultSet.getObject("Id"); 1269 1270 // If a project with the same name exists, let's update the record. 1271 if (countResultSet.next()) { 1272 // First, delete the ProjectUri Records linked to the updated Project. 1273 projectUriResultSet = this.getProjectUriRecords(this.connection, project.getName()); 1274 while (projectUriResultSet.next()) { 1275 String statement = "DELETE FROM ProjectUri WHERE Id='" 1276 + projectUriResultSet.getObject("Id") + "'"; 1277 deleteResource(statement); 1278 } 1279 1280 // Then add the new uri record. A remove and add action is done 1281 // because there is no way to figure out which project uri to update. 1282 projectResultSet = this.getProjectRecord(this.connection, project.getName()); 1283 projectResultSet.next(); 1284 for (String pattern : project.getUriPatterns().getUriPattern()) { 1285 projectUriStatement = this.connection 1286 .prepareStatement("INSERT INTO ProjectUri VALUES (?, ?, ?)"); 1287 // Order: Id Project_Id Uri 1288 projectUriStatement.setObject(1, UUID.randomUUID(), Types.OTHER); 1289 projectUriStatement.setObject(2, projectResultSet.getObject("Id"), Types.OTHER); 1290 projectUriStatement.setString(3, pattern); 1291 projectUriStatement.executeUpdate(); 1292 } 1293 1294 projectStatement = this.connection.prepareStatement("UPDATE Project SET " 1295 + " StartTime=?, EndTime=?, LastMod=?, XmlProject=?, XmlProjectRef=?" 1296 + " WHERE Owner_Id=?" + andClause + "ProjectName=?"); 1297 projectStatement.setTimestamp(1, Tstamp.makeTimestamp(project.getStartTime())); 1298 projectStatement.setTimestamp(2, Tstamp.makeTimestamp(project.getEndTime())); 1299 projectStatement.setTimestamp(3, Tstamp.makeTimestamp(project.getEndTime())); 1300 projectStatement.setString(4, xmlProject); 1301 projectStatement.setString(5, xmlProjectRef); 1302 projectStatement.setObject(6, ownerId, Types.OTHER); 1303 projectStatement.setString(7, project.getName()); 1304 projectStatement.executeUpdate(); 1305 this.logger.fine("Postres: Updated " + project.getOwner() + " " + project.getName()); 1306 } 1307 // Let's create a new project record. 1308 else { 1309 projectStatement = this.connection 1310 .prepareStatement("INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?, ?, ?)"); 1311 // Order: Id ProjectName Owner_Id StartTime EndTime LastMod XmlProject 1312 // XmlProjectRef 1313 UUID projectId = UUID.randomUUID(); 1314 projectStatement.setObject(1, projectId, Types.OTHER); 1315 projectStatement.setString(2, project.getName()); 1316 projectStatement.setObject(3, ownerId, Types.OTHER); 1317 projectStatement.setTimestamp(4, Tstamp.makeTimestamp(project.getStartTime())); 1318 projectStatement.setTimestamp(5, Tstamp.makeTimestamp(project.getEndTime())); 1319 projectStatement.setTimestamp(6, Tstamp.makeTimestamp(project.getLastMod())); 1320 projectStatement.setString(7, xmlProject); 1321 projectStatement.setString(8, xmlProjectRef); 1322 projectStatement.executeUpdate(); 1323 1324 for (String pattern : project.getUriPatterns().getUriPattern()) { 1325 projectUriStatement = this.connection 1326 .prepareStatement("INSERT INTO ProjectUri VALUES (?, ?, ?)"); 1327 // Order: Id Project_Id Uri 1328 projectUriStatement.setObject(1, UUID.randomUUID(), Types.OTHER); 1329 projectUriStatement.setObject(2, projectId, Types.OTHER); 1330 projectUriStatement.setString(3, pattern); 1331 projectUriStatement.executeUpdate(); 1332 } 1333 this.logger.fine("Postgres: Inserted " + project.getOwner() + " " 1334 + project.getName()); 1335 } 1336 } 1337 } 1338 catch (SQLException e) { 1339 this.logger.info(postgresError + StackTrace.toString(e)); 1340 } 1341 finally { 1342 try { 1343 if (projectStatement != null) { 1344 projectStatement.close(); 1345 } 1346 if (projectUriStatement != null) { 1347 projectUriStatement.close(); 1348 } 1349 if (projectUriResultSet != null) { 1350 projectUriResultSet.close(); 1351 } 1352 if (projectResultSet != null) { 1353 projectResultSet.close(); 1354 } 1355 countStatement.close(); 1356 countResultSet.close(); 1357 userResultSet.close(); 1358 } 1359 catch (SQLException e) { 1360 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1361 } 1362 } 1363 return true; 1364 } 1365 1366 /** 1367 * Returns a string containing the Index for the given resource indicated by 1368 * resourceName. 1369 * @param resourceName The resource name, such as "Project". 1370 * @param statement The SQL Statement to be used to retrieve the resource 1371 * references. 1372 * @return The aggregate Index XML string. 1373 */ 1374 private String getIndex(String resourceName, String statement) { 1375 StringBuilder builder = new StringBuilder(512); 1376 builder.append("<").append(resourceName).append(indexSuffix); 1377 // Retrieve all the SensorData 1378 PreparedStatement s = null; 1379 ResultSet rs = null; 1380 try { 1381 s = this.connection.prepareStatement(statement); 1382 rs = s.executeQuery(); 1383 String resourceRefColumnName = xml + resourceName + "Ref"; 1384 while (rs.next()) { 1385 builder.append(rs.getString(resourceRefColumnName)); 1386 } 1387 } 1388 catch (SQLException e) { 1389 this.logger.info("Postgres: Error in getIndex()" + StackTrace.toString(e)); 1390 } 1391 finally { 1392 try { 1393 rs.close(); 1394 s.close(); 1395 } 1396 catch (SQLException e) { 1397 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1398 } 1399 } 1400 builder.append("</").append(resourceName).append(indexSuffix); 1401 return builder.toString(); 1402 } 1403 1404 /** 1405 * Returns a string containing the Index of all of the SensorData whose 1406 * runtime field matches the first runtime in the result set. Since the passed 1407 * statement will retrieve sensor data in the given time period ordered in 1408 * descending order by runtime, this should result in an index containing only 1409 * @param statement The SQL Statement to be used to retrieve the resource 1410 * references. 1411 * @return The aggregate Index XML string. 1412 */ 1413 private String getSnapshotIndex(String statement) { 1414 String resourceName = "SensorData"; 1415 StringBuilder builder = new StringBuilder(512); 1416 builder.append("<").append(resourceName).append(indexSuffix); 1417 // Retrieve all the SensorData 1418 PreparedStatement s = null; 1419 ResultSet rs = null; 1420 String firstRunTime = null; 1421 try { 1422 this.connection = DriverManager.getConnection(connectionURL); 1423 s = this.connection.prepareStatement(statement); 1424 rs = s.executeQuery(); 1425 String resourceRefColumnName = xml + resourceName + "Ref"; 1426 boolean finished = false; 1427 // Add all entries with the first retrieved nruntime value to the index. 1428 while (rs.next() && !finished) { 1429 String runtime = rs.getString("Runtime"); 1430 // Should never be null, but just in case. 1431 if (runtime != null) { 1432 // Initial firstRunTime to the first retrieved non-null runtime value. 1433 if (firstRunTime == null) { 1434 firstRunTime = runtime; 1435 } 1436 // Now add every entry whose runtime equals the first retrieved run 1437 // time. 1438 if (runtime.equals(firstRunTime)) { 1439 builder.append(rs.getString(resourceRefColumnName)); 1440 } 1441 else { 1442 // As soon as we find a runtime not equal to firstRunTime, we can 1443 // stop. 1444 finished = true; 1445 } 1446 } 1447 } 1448 } 1449 catch (SQLException e) { 1450 this.logger.info("Postgres: Error in getIndex()" + StackTrace.toString(e)); 1451 } 1452 finally { 1453 try { 1454 rs.close(); 1455 s.close(); 1456 } 1457 catch (SQLException e) { 1458 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1459 } 1460 } 1461 builder.append("</").append(resourceName).append(indexSuffix); 1462 return builder.toString(); 1463 } 1464 1465 /** 1466 * Returns a string containing the Index for the given resource indicated by 1467 * resourceName, returning only the instances starting at startIndex, and with 1468 * the maximum number of returned instances indicated by maxInstances. 1469 * @param resourceName The resource name, such as "Project". 1470 * @param startIndex The (zero-based) starting index for instances to be 1471 * returned. 1472 * @param maxInstances The maximum number of instances to return. 1473 * @param statement The SQL Statement to be used to retrieve the resource 1474 * references. 1475 * @return The aggregate Index XML string. 1476 */ 1477 private String getIndex(String resourceName, String statement, int startIndex, 1478 int maxInstances) { 1479 StringBuilder builder = new StringBuilder(512); 1480 builder.append("<").append(resourceName).append(indexSuffix); 1481 // Retrieve all the SensorData to start. 1482 PreparedStatement s = null; 1483 ResultSet rs = null; 1484 try { 1485 s = this.connection.prepareStatement(statement); 1486 rs = s.executeQuery(); 1487 int currIndex = 0; 1488 int totalInstances = 0; 1489 String resourceRefColumnName = xml + resourceName + "Ref"; 1490 while (rs.next()) { 1491 if ((currIndex >= startIndex) && (totalInstances < maxInstances)) { 1492 builder.append(rs.getString(resourceRefColumnName)); 1493 totalInstances++; 1494 } 1495 currIndex++; 1496 } 1497 } 1498 catch (SQLException e) { 1499 this.logger.info("Postgres: Error in getIndex()" + StackTrace.toString(e)); 1500 } 1501 finally { 1502 try { 1503 rs.close(); 1504 s.close(); 1505 } 1506 catch (SQLException e) { 1507 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1508 } 1509 } 1510 builder.append("</").append(resourceName).append(indexSuffix); 1511 return builder.toString(); 1512 } 1513 1514 /** 1515 * Returns a string containing the Resource as XML. 1516 * @param resourceName The name of the resource, such as "User". 1517 * @param statement The select statement used to retrieve the resultset 1518 * containing a single row with that resource. 1519 * @return The string containing the resource as an XML string. 1520 */ 1521 private String getResource(String resourceName, String statement) { 1522 StringBuilder builder = new StringBuilder(512); 1523 PreparedStatement s = null; 1524 ResultSet rs = null; 1525 try { 1526 server.getLogger().fine(executeQueryMsg + statement); 1527 s = this.connection.prepareStatement(statement); 1528 rs = s.executeQuery(); 1529 String resourceXmlColumnName = xml + resourceName; 1530 while (rs.next()) { // the select statement must guarantee only one row is 1531 // returned. 1532 builder.append(rs.getString(resourceXmlColumnName)); 1533 } 1534 } 1535 catch (SQLException e) { 1536 this.logger.info("DB: Error in getResource()" + StackTrace.toString(e)); 1537 } 1538 finally { 1539 try { 1540 rs.close(); 1541 s.close(); 1542 } 1543 catch (SQLException e) { 1544 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1545 } 1546 } 1547 return builder.toString(); 1548 } 1549 1550 /** 1551 * Deletes the resource, given the SQL statement to perform the delete. 1552 * @param statement The SQL delete statement. 1553 */ 1554 private void deleteResource(String statement) { 1555 PreparedStatement s = null; 1556 try { 1557 server.getLogger().fine("Postgres: " + statement); 1558 s = this.connection.prepareStatement(statement); 1559 s.executeUpdate(); 1560 } 1561 catch (SQLException e) { 1562 this.logger.info("Postgres: Error in deleteResource()" + StackTrace.toString(e)); 1563 } 1564 finally { 1565 try { 1566 s.close(); 1567 } 1568 catch (SQLException e) { 1569 e.printStackTrace(); 1570 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1571 } 1572 } 1573 } 1574 1575 /** 1576 * Always returns true because compression is not supported in this Postgres 1577 * implementation. 1578 * @return returns true. 1579 */ 1580 @Override 1581 public boolean compressTables() { 1582 return true; 1583 } 1584 1585 /** 1586 * {@inheritDoc}. This is an estimate, it turns out that postgreSQL has some 1587 * problems counting its row counts. 1588 */ 1589 @Override 1590 public int getRowCount(String table) { 1591 int numRows = -1; 1592 PreparedStatement s = null; 1593 ResultSet rs = null; 1594 String statement = "select n_live_tup, relname, last_analyze from pg_stat_user_tables " 1595 + " where relname = '" + table.toLowerCase(Locale.ENGLISH) + "'"; 1596 try { 1597 s = this.connection.prepareStatement(statement); 1598 rs = s.executeQuery(); 1599 rs.next(); 1600 numRows = rs.getInt(1); 1601 } 1602 catch (SQLException e) { 1603 this.logger.info("Postgres: Error in getRowCount: " + StackTrace.toString(e)); 1604 } 1605 finally { 1606 try { 1607 rs.close(); 1608 s.close(); 1609 } 1610 catch (SQLException e) { 1611 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1612 } 1613 } 1614 return numRows; 1615 } 1616 1617 /** {@inheritDoc} */ 1618 @Override 1619 public Set<String> getTableNames() { 1620 Set<String> tableNames = new HashSet<String>(); 1621 tableNames.add("SensorData"); 1622 tableNames.add("SensorDataType"); 1623 tableNames.add("HackyUser"); 1624 tableNames.add("Project"); 1625 return tableNames; 1626 } 1627 1628 /** {@inheritDoc} */ 1629 @Override 1630 public String getSensorDataIndex(List<User> users, XMLGregorianCalendar startTime, 1631 XMLGregorianCalendar endTime, List<String> uriPatterns, String sdt, String tool) { 1632 String statement = null; 1633 ResultSet results = null; 1634 1635 try { 1636 results = this.getSdtRecord(this.connection, sdt); 1637 if (results.next()) { 1638 statement = selectPrefix + constructOwnerClause(users) + andClause + sdtIdEquals 1639 + results.getObject("Id") + quoteAndClause + toolEquals + tool + quoteAndClause 1640 + " (Tstamp BETWEEN TIMESTAMP '" + Tstamp.makeTimestamp(startTime) + "' AND " // NOPMD 1641 + " TIMESTAMP '" + Tstamp.makeTimestamp(endTime) + "')" // NOPMD 1642 + constructLikeClauses(uriPatterns) + orderByTstamp; 1643 return getIndex("SensorData", statement); 1644 } 1645 } 1646 catch (SQLException e) { 1647 this.logger.info(postgresError + StackTrace.toString(e)); 1648 return ""; 1649 } 1650 finally { 1651 try { 1652 results.close(); 1653 } 1654 catch (SQLException e) { 1655 this.logger.warning(errorClosingMsg + StackTrace.toString(e)); 1656 } 1657 } 1658 return ""; 1659 } 1660 1661 /** 1662 * Always returns true because indexing is not supported in this Postgres 1663 * implementation. 1664 * @return returns true. 1665 */ 1666 @Override 1667 public boolean indexTables() { 1668 return true; 1669 } 1670 }