/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 2381

Added by Duane Costa almost 20 years ago

Re-implement logic to prune old log entries from the HARVEST_LOG and HARVEST_DETAIL_LOG tables. The old logic caused integrity constraint violations in the database because it tried to delete parent records from HARVEST_LOG prior to deleting child records from HARVEST_DETAIL_LOG.

Also formatting improvements.

             Thread.sleep(delay * oneHour);
+          }
           catch (InterruptedException e) {
               System.err.println("InterruptedException: " + e.getMessage());
               System.exit(1);
             System.err.println("InterruptedException: " + e.getMessage());
             System.exit(1);
+          }
+        }
-...
        */
       public Connection getConnection() {
         String dbDriver = "";
     		String defaultDB;
         String defaultDB;
         String password;
         String user;
         SQLWarning warn;
-...
       private int getMaxValue(String tableName, String fieldName) {
         int maxValue = 0;
         int fieldValue;
     		String query = "SELECT " + fieldName + " FROM " + tableName;
     		Statement stmt;
         String query = "SELECT " + fieldName + " FROM " + tableName;
         Statement stmt;
     		try {
     			stmt = conn.createStatement();
     			ResultSet rs = stmt.executeQuery(query);
     	try {
           stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery(query);
     			while (rs.next()) {
     				fieldValue = rs.getInt(fieldName);
           while (rs.next()) {
             fieldValue = rs.getInt(fieldName);
             maxValue = Math.max(maxValue, fieldValue);
+    			}
     			stmt.close();
+    		}
+          }
           stmt.close();
+        }
         catch(SQLException ex) {
     			System.out.println("SQLException: " + ex.getMessage());
+    		}
           System.out.println("SQLException: " + ex.getMessage());
+        }
         return maxValue;
+      }
-...
       private int getMinValue(String tableName, String fieldName) {
         int minValue = 0;
         int fieldValue;
     		String query = "SELECT " + fieldName + " FROM " + tableName;
     		Statement stmt;
         String query = "SELECT " + fieldName + " FROM " + tableName;
         Statement stmt;
     		try {
     			stmt = conn.createStatement();
     			ResultSet rs = stmt.executeQuery(query);
         try {
           stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery(query);
     			while (rs.next()) {
     				fieldValue = rs.getInt(fieldName);
           while (rs.next()) {
             fieldValue = rs.getInt(fieldName);
             if (minValue == 0) {
               minValue = fieldValue;
-...
             else {
               minValue = Math.min(minValue, fieldValue);
+            }
+    			}
     			stmt.close();
+    		}
+          }
           stmt.close();
+        }
         catch(SQLException ex) {
     			System.out.println("SQLException: " + ex.getMessage());
+    		}
           System.out.println("SQLException: " + ex.getMessage());
+        }
         return minValue;
+      }
-...
        * @param siteScheduleID   the primary key in the HARVEST_SITE_SCHEDULE table
        */
       void printHarvestSiteSchedule(PrintStream out, int siteScheduleID) {
          HarvestSiteSchedule harvestSiteSchedule;
         HarvestSiteSchedule harvestSiteSchedule;
         for (int i = 0; i < harvestSiteScheduleList.size(); i++) {
           harvestSiteSchedule = (HarvestSiteSchedule)harvestSiteScheduleList.get(i);
-...
       /**
        * Prunes old records from the HARVEST_DETAIL_LOG table. Records are
        * removed if the HARVEST_LOG_ID foreign key is less than the lowest
        * HARVEST_LOG_ID primary key in the HARVEST_LOG table.
        */
       private void pruneHarvestDetailLog() {
     		String deleteString;
         int minHarvestLogID;
         int recordsDeleted;
     		Statement stmt;
         minHarvestLogID = getMinValue("HARVEST_LOG", "HARVEST_LOG_ID");
         deleteString = "DELETE FROM HARVEST_DETAIL_LOG WHERE HARVEST_LOG_ID < " +
                        minHarvestLogID;
     		try {
     			System.out.print("Pruning log entries from HARVEST_DETAIL_LOG: ");
           System.out.println(deleteString);
     			stmt = conn.createStatement();
     			recordsDeleted = stmt.executeUpdate(deleteString);
     			System.out.println(recordsDeleted + " records deleted");
     			stmt.close();
+    		}
         catch(SQLException e) {
     			System.out.println("SQLException: " + e.getMessage());
+    		}
+      }
       /**
        * Prunes old records from the HARVEST_LOG table. Records are removed if
        * their HARVEST_DATE is older than a given number of days, as stored in the
        * logPeriod object field.
        * logPeriod object field. First deletes records from the HARVEST_DETAIL_LOG
        * table that reference the to-be-pruned entries in the HARVEST_LOG table.
        */
       private void pruneHarvestLog() {
         long currentTime = harvestStartTime.getTime(); // time in milliseconds
         Date dateLastLog;                    // Prune everything prior to this date
     		String deleteString;
         String deleteString;
         String deleteStringDetailLog;
         long delta;
         final long millisecondsPerDay = (1000 * 60 * 60 * 24);
         int recordsDeleted;
         int recordsDeletedDetail = 0;
         SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
         String dateString;
     		Statement stmt;
         ResultSet rs;
         String selectString;
         Statement stmt;
         long timeLastLog = 0;
         SQLWarning warn;
         delta = logPeriod * millisecondsPerDay;
         deleteString = "DELETE FROM HARVEST_LOG WHERE HARVEST_DATE < ";
         selectString="SELECT HARVEST_LOG_ID FROM HARVEST_LOG WHERE HARVEST_DATE < ";
         deleteStringDetailLog =
                            "DELETE FROM HARVEST_DETAIL_LOG WHERE HARVEST_LOG_ID = ";
         timeLastLog = currentTime - delta;
         dateLastLog = new Date(timeLastLog);
         dateString = "'" + simpleDateFormat.format(dateLastLog) + "'";
         deleteString += dateString;
         selectString += dateString;
     		try {
     			System.out.print("Pruning log entries from HARVEST_LOG: ");
           System.out.println(deleteString);
     			stmt = conn.createStatement();
     			recordsDeleted = stmt.executeUpdate(deleteString);
     			System.out.println(recordsDeleted + " records deleted");
     			stmt.close();
+    		}
         try {
           System.out.println(
                     "Pruning log entries from HARVEST_DETAIL_LOG and HARVEST_LOG:");
           /* Get the list of entries that need to be pruned from the HARVEST_LOG
            * table.
            */
           stmt = conn.createStatement();
           rs = stmt.executeQuery(selectString);
           warn = rs.getWarnings();
           if (warn != null) {
             System.out.println("\n---Warning---\n");
             while (warn != null) {
               System.out.println("Message: " + warn.getMessage());
               System.out.println("SQLState: " + warn.getSQLState());
               System.out.print("Vendor error code: ");
               System.out.println(warn.getErrorCode());
               System.out.println("");
               warn = warn.getNextWarning();
+            }
+          }
           /* Delete any entries from the HARVEST_DETAIL_LOG which reference
            * HARVEST_LOG_IDs that are about to be pruned. HARVEST_DETAIL_LOG must
            * be pruned first because its records have a child relationship to those
            * in HARVEST_LOG.
            */
           while (rs.next()) {
             harvestLogID = rs.getInt("HARVEST_LOG_ID");
             stmt = conn.createStatement();
             recordsDeleted = stmt.executeUpdate(deleteStringDetailLog +
                                                 harvestLogID);
             recordsDeletedDetail += recordsDeleted;
             stmt.close();
+          }
           /* Now prune entries from the HARVEST_LOG table using a single update.
            */
           stmt = conn.createStatement();
           recordsDeleted = stmt.executeUpdate(deleteString);
           stmt.close();
           System.out.println("  " + recordsDeletedDetail +
                              " records deleted from HARVEST_DETAIL_LOG");
           System.out.println("  " + recordsDeleted +
                              " records deleted from HARVEST_LOG");
+        }
         catch (SQLException e) {
     			System.out.println("SQLException: " + e.getMessage());
+    		}
           System.out.println("SQLException: " + e.getMessage());
+        }
+      }
-...
         System.out.println("Shutting Down Harvester");
         addLogEntry(0, "Shutting Down Harvester", "HarvesterShutdown", 0, null, "");
         pruneHarvestLog();
         pruneHarvestDetailLog();
         closeConnection();
         // Print log to standard output and then email the Harvester administrator
         printHarvestLog(System.out, maxCodeLevel, siteScheduleID);

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 2381

Added by Duane Costa almost 20 years ago