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    }