Index: trunk/ippToPsps/jython/ipptopspsdb.py
===================================================================
--- trunk/ippToPsps/jython/ipptopspsdb.py	(revision 33683)
+++ trunk/ippToPsps/jython/ipptopspsdb.py	(revision 33684)
@@ -25,11 +25,11 @@
     Returns a list of merged batch IDs that are merged but not yet deleted
     '''
-    def getMergedButNotDeletedBatchIDs(self, batchType, epoch, dvoGpc1Label, column):
+    def getMergedButNotDeletedBatchIDs(self, batchType, column):
 
         sql = "SELECT DISTINCT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
                AND batch_type = '" + batchType + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND merged = 1 \
                AND " + column + " = 0"
@@ -50,11 +50,11 @@
     Returns a list of batch IDs marked as 'purged' but not yet deleted
     '''
-    def getPurgedButNotDeletedBatchIDs(self, batchType, epoch, dvoGpc1Label, column):
+    def getPurgedButNotDeletedBatchIDs(self, batchType, column):
 
         sql = "SELECT DISTINCT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
                AND batch_type = '" + batchType + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND purged = 1 \
                AND " + column + " = 0"
@@ -76,11 +76,11 @@
     Returns a list of processed batch IDs that have been loaded to the ODM but not yet deleted
     '''
-    def getLoadedToODMButNotDeletedBatchIDs(self, batchType, epoch, dvoGpc1Label, column):
+    def getLoadedToODMButNotDeletedBatchIDs(self, batchType, column):
 
         sql = "SELECT DISTINCT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
                AND batch_type = '" + batchType + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND (loaded_to_ODM = -1 OR merge_worthy = 1) \
                AND " + column + " = 0"
@@ -101,36 +101,36 @@
     Returns a list of merged batch IDs that are not deleted from local disk
     '''
-    def getMergedButNotDeletedFromLocalDisk(self, batchType, epoch, dvoGpc1Label):
-        return self.getMergedButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_local")
+    def getMergedButNotDeletedFromLocalDisk(self, batchType):
+        return self.getMergedButNotDeletedBatchIDs(batchType, "deleted_local")
 
     '''
     Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from local disk
     '''
-    def getLoadedToODMButNotDeletedFromLocalDisk(self, batchType, epoch, dvoGpc1Label):
-        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_local")
+    def getLoadedToODMButNotDeletedFromLocalDisk(self, batchType):
+        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_local")
 
     '''
     Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from datastore
     '''
-    def getLoadedToODMButNotDeletedFromDatastore(self, batchType, epoch, dvoGpc1Label):
-        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_datastore")
+    def getLoadedToODMButNotDeletedFromDatastore(self, batchType):
+        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_datastore")
 
     '''
     Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from DXLayer
     '''
-    def getLoadedToODMButNotDeletedFromDXLayer(self, batchType, epoch, dvoGpc1Label):
-        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_dxlayer")
+    def getLoadedToODMButNotDeletedFromDXLayer(self, batchType):
+        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_dxlayer")
 
     '''
     Returns a list of purged  batch IDs that not deleted from local disk
     '''
-    def getPurgedButNotDeletedFromLocalDisk(self, batchType, epoch, dvoGpc1Label):
-        return self.getPurgedButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_local")
+    def getPurgedButNotDeletedFromLocalDisk(self, batchType):
+        return self.getPurgedButNotDeletedBatchIDs(batchType, "deleted_local")
 
     '''
     Returns a list of purged batch IDs that are not deleted from datastore
     '''
-    def getPurgedButNotDeletedFromDatastore(self, batchType, epoch, dvoGpc1Label):
-        return self.getPurgedButNotDeletedBatchIDs(batchType, epoch, dvoGpc1Label, "deleted_datastore")
+    def getPurgedButNotDeletedFromDatastore(self, batchType):
+        return self.getPurgedButNotDeletedBatchIDs(batchType, "deleted_datastore")
 
 
@@ -144,11 +144,11 @@
     publish something simulataneously with the client that it producing it
     '''
-    def getProcessedButFailedDatastoreBatchIDs(self, epoch, dvoGpc1Label, batchType):
+    def getProcessedButFailedDatastoreBatchIDs(self, batchType):
 
         sql = "SELECT DISTINCT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
                AND batch_type = '" + batchType + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND processed = 1\
                AND loaded_to_datastore != 1 \
@@ -173,10 +173,10 @@
     NOTE we exclude batches already deleted from the datastore, as sometimes we delete stuff prior to loading to ODM if there is a known problem with the batch
     '''
-    def getUnloadedBatchIDs(self, epoch, dvoGpc1Label, batchType):
+    def getUnloadedBatchIDs(self, batchType):
 
         sql = "SELECT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND loaded_to_datastore = 1 \
                AND batch_type = '" + batchType + "' \
@@ -191,6 +191,6 @@
         except:
             self.logger.exception("Can't query for unloaded batch ids in ipptopsps Db")
-
-        rs.close()
+        finally:
+            rs.close()
 
         self.logger.debug("Found %d unloaded items" % len(ids))
@@ -202,10 +202,10 @@
     NOTE we exclude batches already deleted from the datastore, as sometimes we delete stuff prior to loading to ODM if there is a known problem with the batch
     '''
-    def getUnmergeWorthyBatchIDs(self, epoch, dvoGpc1Label, batchType):
+    def getUnmergeWorthyBatchIDs(self, batchType):
 
         sql = "SELECT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND loaded_to_datastore = 1 \
                AND batch_type = '" + batchType + "' \
@@ -221,6 +221,6 @@
         except:
             self.logger.exception("Can't query for un-mergeworthy batch ids in ipptopsps Db")
-
-        rs.close()
+        finally:
+            rs.close()
 
         self.logger.debug("Found %d unloaded items" % len(ids))
@@ -231,10 +231,10 @@
     Returns a list of processed batch IDs that are merge_worthy, but not yet merged, for this epoch and dvo label
     '''
-    def getUnmergedBatchIDs(self, epoch, dvoGpc1Label, batchType):
+    def getUnmergedBatchIDs(self, batchType):
 
         sql = "SELECT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND merge_worthy = 1 \
                AND batch_type = '" + batchType + "' \
@@ -248,6 +248,6 @@
         except:
             self.logger.exception("Can't query for unmerged batch ids in ipptopsps Db")
-
-        rs.close()
+        finally:
+            rs.close()
 
         self.logger.debug("Found %d un-merged items" % len(ids))
@@ -258,10 +258,10 @@
     Returns a list of batch IDs for this epoch and dvo label for which the specified column is set to value
     '''
-    def getBatchIDs(self, epoch, dvoGpc1Label, column, value):
+    def getBatchIDs(self, column, value):
 
         sql = "SELECT DISTINCT batch_id \
                FROM batch \
-               WHERE timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               WHERE timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND " + column + " = " + str(value)
 
@@ -283,11 +283,11 @@
     Returns the total detections published to the datastore for this epoch, dvo label and batch type
     '''
-    def getTotalDetectionsPublished(self, batchType, epoch, dvoGpc1Label, interval="10 YEAR"):
+    def getTotalDetectionsPublished(self, batchType, interval="10 YEAR"):
 
         sql = "SELECT SUM(total_detections) \
                FROM batch \
                WHERE batch_type = '" + batchType + "' \
-               AND timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND processed = 1 \
                AND loaded_to_datastore = 1 \
@@ -300,9 +300,7 @@
             total =  rs.getLong(1)
         except:
-            self.logger.exception("Can't query for total detections published")
+            self.logger.errorPair("Can't get total detections published", sql)
         finally:
             rs.close()
-
-        self.logger.debug("Found %d detections" % total)
 
         return total
@@ -312,11 +310,11 @@
             cam_ids or stack_ids) for which the specified column is set to value
     '''
-    def getStageIDs(self, batchType, epoch, dvoGpc1Label, column, value):
+    def getStageIDs(self, batchType, column, value):
 
         sql = "SELECT DISTINCT stage_id \
                FROM batch \
                WHERE batch_type = '" + batchType + "' \
-               AND timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND " + column + " = " + str(value)
 
@@ -328,8 +326,6 @@
         except:
             self.logger.exception("Can't query for ids with " + column + " = " + str(value) + " in ipptopsps Db")
-
-        rs.close()
-
-        self.logger.debug("Found %d batches with %s = 1 for batch type='%s'" % (len(ids), column, batchType))
+        finally:
+            rs.close()
 
         return ids
@@ -338,8 +334,8 @@
     Series of getter methods that utilise getBatchIDs() method above
     '''
-    def getloadedToDatastoreBatchIDs(self, epoch, dvoGpc1Label):
-        return self.getBatchIDs(epoch, dvoGpc1Label, "loaded_to_datastore", 1)
-    def getFailedLoadedToODMBatchIDs(self, epoch, dvoGpc1Label):
-        return self.getBatchIDs(epoch, dvoGpc1Label, "loaded_to_ODM", -1)
+    def getloadedToDatastoreBatchIDs(self):
+        return self.getBatchIDs("loaded_to_datastore", 1)
+    def getFailedLoadedToODMBatchIDs(self):
+        return self.getBatchIDs("loaded_to_ODM", -1)
 
     '''
@@ -347,24 +343,24 @@
     Series of getter methods that utilise getStageIDs() method above
     '''
-    def getProcessedIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "processed", 1)
-    def getFailedProcessedIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "processed", -1)
-    def getLoadedToDatastoreIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_datastore", 1)
-    def getFailedLoadedToDatastoreIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_datastore", -1)
-    def getLoadedToODMIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_ODM", 1)
-    def getFailedLoadedToODMIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_ODM", -1)
-    def getMergeWorthyIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merge_worthy", 1)
-    def getFailedMergeWorthyIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merge_worthy", -1)
-    def getMergedIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merged", 1)
-    def getFailedMergedIDs(self, batchType, epoch, dvoGpc1Label):
-        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merged", -1)
+    def getProcessedIDs(self, batchType):
+        return self.getStageIDs(batchType, "processed", 1)
+    def getFailedProcessedIDs(self, batchType):
+        return self.getStageIDs(batchType, "processed", -1)
+    def getLoadedToDatastoreIDs(self, batchType):
+        return self.getStageIDs(batchType, "loaded_to_datastore", 1)
+    def getFailedLoadedToDatastoreIDs(self, batchType):
+        return self.getStageIDs(batchType, "loaded_to_datastore", -1)
+    def getLoadedToODMIDs(self, batchType):
+        return self.getStageIDs(batchType, "loaded_to_ODM", 1)
+    def getFailedLoadedToODMIDs(self, batchType):
+        return self.getStageIDs(batchType, "loaded_to_ODM", -1)
+    def getMergeWorthyIDs(self, batchType):
+        return self.getStageIDs(batchType, "merge_worthy", 1)
+    def getFailedMergeWorthyIDs(self, batchType):
+        return self.getStageIDs(batchType, "merge_worthy", -1)
+    def getMergedIDs(self, batchType):
+        return self.getStageIDs(batchType, "merged", 1)
+    def getFailedMergedIDs(self, batchType):
+        return self.getStageIDs(batchType, "merged", -1)
 
     '''
@@ -385,5 +381,5 @@
     Returns a count of batches processed in the specified time period.
     '''
-    def countBatches(self, batchType, epoch, dvoGpc1Label, interval="10 YEAR"):
+    def countBatches(self, batchType, interval="10 YEAR"):
 
         sql = "SELECT COUNT(batch_id) \
@@ -391,6 +387,6 @@
                WHERE batch_type = '" + batchType + "' \
                AND processed = 1 \
-               AND timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND timestamp BETWEEN now() - INTERVAL " + interval + " AND now()"
 
@@ -408,5 +404,5 @@
     this epoch, dvo label and batch type
     '''
-    def getTimeOfLastBatchPublished(self, batchType, epoch, dvoGpc1Label):
+    def getTimeOfLastBatchPublished(self, batchType):
 
         seconds = None
@@ -416,6 +412,6 @@
                WHERE batch_type = '" + batchType + "' \
                AND loaded_to_datastore = 1 \
-               AND timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                ORDER BY timestamp DESC LIMIT 1"
 
@@ -601,11 +597,11 @@
     Returns a list of stage_ids that have failed the max number of times
     '''
-    def getConsistentlyFailedIDs(self, batchType, epoch, dvoGpc1Label):
+    def getConsistentlyFailedIDs(self, batchType):
 
         sql = "SELECT DISTINCT stage_id, COUNT(stage_id) AS numoccur \
                FROM batch \
                WHERE batch_type = '" + batchType + "' \
-               AND timestamp > '" + epoch + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND timestamp > '" + self.config.epoch + "' \
+               AND dvo_db = '" + self.config.dvoLabel + "' \
                AND processed = -1 \
                GROUP BY stage_id HAVING numoccur >= " + str(self.MAX_FAILS)
@@ -706,4 +702,18 @@
         self.execute(sql)
 
+    '''
+    Inserts some object metadata for this batch ID
+    '''
+    def insertObjectMeta(self, batchID, region):
+
+        sql = "INSERT INTO object ( \
+               batch_id \
+               ,region \
+               ) VALUES ( \
+               " + str(batchID) + " \
+               ,'" + region + "' \
+               )"
+
+        self.execute(sql)
     '''
     Resets a batch ready for re-loading to the datastore
@@ -864,4 +874,5 @@
                ,queue_P2 = " + self.config.queuingThisBatchType("P2") + " \
                ,queue_ST = " + self.config.queuingThisBatchType("ST") + " \
+               ,queue_OB = " + self.config.queuingThisBatchType("OB") + " \
                WHERE name = '" + self.config.name + "'"
 
@@ -910,4 +921,5 @@
         ,queue_P2 \
         ,queue_ST \
+        ,queue_OB \
         FROM config \
         WHERE name = '" + self.config.name + "'"
@@ -942,4 +954,5 @@
             if rs.getInt(19) == 1: self.config.batchTypes.append("P2")
             if rs.getInt(20) == 1: self.config.batchTypes.append("ST")
+            if rs.getInt(21) == 1: self.config.batchTypes.append("OB")
             self.config.force = False # TODO
             self.config.test = False # TODO
@@ -1009,5 +1022,5 @@
 
         for id in ids:
-            sql = "INSERT INTO pending \
+            sql = "INSERT IGNORE INTO pending \
                (box_id, batch_type, stage_id) \
                VALUES \
@@ -1132,7 +1145,11 @@
     Returns ids for pending items for this config
     '''
-    def getPendingIds(self):
-
-        sql = "SELECT DISTINCT stage_id FROM pending JOIN box ON (box_id = id) WHERE config = '" + self.config.name + "'"
+    def getPendingIds(self, batchType):
+
+        sql = "SELECT DISTINCT stage_id \
+               FROM pending \
+               JOIN box ON (box_id = id) \
+               WHERE config = '" + self.config.name + "' \
+               AND batch_type = '" + batchType + "'"
 
         ids = []
@@ -1282,8 +1299,8 @@
 
     '''
-    Purges old clients that may have die, i.e. those with a timestamp older than n hours
+    Purges old clients that may have died, i.e. those with a timestamp older than n hours
     '''
     def purgeDeadClients(self):
-        self.execute("DELETE FROM clients WHERE timestamp <  now() -  INTERVAL 6 HOUR")
+        self.execute("DELETE FROM clients WHERE timestamp < now() - INTERVAL 14 HOUR")
 
     '''