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