Changeset 32089 for trunk/ippToPsps

Timestamp:

Aug 12, 2011, 4:04:46 PM (15 years ago)

Author:

rhenders

Message:

new getter methods for the various columns in the batch table; new method to update the ODM columns (used by pollODM program); removed getFailedBatches() method

File:

• trunk/ippToPsps/jython/ipptopspsdb.py (modified) (5 diffs)

trunk/ippToPsps/jython/ipptopspsdb.py

@@ -57 +57 @@
 
     '''
-    TODO
-    '''
-    def getProcessedIDsForThisStage(self, batchType, epoch, dvoGpc1Label):
+    Returns a list of processed batch IDs that are not yet merged for this epoch and dvo label
+    '''
+    def getProcessedButUnmergedBatchIDs(self, epoch, dvoGpc1Label):
+
+        sql = "SELECT DISTINCT batch_id \
+               FROM batch \
+               WHERE timestamp > '" + epoch + "' \
+               AND dvo_db = '" + dvoGpc1Label + "' \
+               AND loaded_to_datastore \
+               AND !merged"
+
+        ids = []
+        try:
+            rs = self.executeQuery(sql)
+            while (rs.next()):
+                ids.append(rs.getInt(1))
+        except:
+            self.logger.exception("Can't query for processed batch ids in ipptopsps Db")
+
+        rs.close()
+
+        self.logger.debug("Found %d processed and un-merged items" % len(ids))
+
+        return ids
+
+    '''
+    Returns a list of IDs for this batch type, epoch and dvo label (i.e. either 
+            cam_ids or stack_ids) for which the spefified column is true (=1)
+    '''
+    def getStageIDs(self, batchType, epoch, dvoGpc1Label, column):
 
         sql = "SELECT DISTINCT stage_id \
@@ -66 +93 @@
                AND timestamp > '" + epoch + "' \
                AND dvo_db = '" + dvoGpc1Label + "' \
-               AND loaded_to_datastore"
+               AND " + column + " = 1"
 
         ids = []
@@ -74 +101 @@
                 ids.append(rs.getInt(1))
         except:
-            self.logger.exception("Can't query for processed ids in ipptopsps Db")
+            self.logger.exception("Can't query for ids with " + column + " = 1 in ipptopsps Db")
 
         rs.close()
 
-        self.logger.debug("Found %d processed and published items for batchType='%s'" % (len(ids), batchType))
+        self.logger.debug("Found %d batches with %s = 1 for batch type='%s'" % (len(ids), column, batchType))
 
         return ids
 
     '''
-    TODO
-    '''
-    def getLastBatchPublished(self, batchType):
-
-        sql = "SELECT TIMESTAMPDIFF(MINUTE, timestamp, now())/60.0 \
+    Series of getter methods that utilise getStageIDs() method above
+    '''
+    def getProcessedIDs(self, batchType, epoch, dvoGpc1Label):
+        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "processed")
+    def getloadedToDatastoreIDs(self, batchType, epoch, dvoGpc1Label):
+        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_datastore")
+    def getLoadedToODMIDs(self, batchType, epoch, dvoGpc1Label):
+        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "loaded_to_ODM")
+    def getMergeWothyIDs(self, batchType, epoch, dvoGpc1Label):
+        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merge_worthy")
+    def getMergedIDs(self, batchType, epoch, dvoGpc1Label):
+        return self.getStageIDs(batchType, epoch, dvoGpc1Label, "merged")
+
+    '''
+    Returns the time (as a string) that the last batch was published to the datastore for
+    this epoch, dvo label and batch type
+    '''
+    def getTimeOfLastBatchPublished(self, batchType, epoch, dvoGpc1Label):
+
+        minutes = None
+
+        sql = "SELECT TIMESTAMPDIFF(MINUTE, timestamp, now()) \
                FROM batch \
                WHERE batch_type = '" + batchType + "' \
                AND loaded_to_datastore \
+               AND timestamp > '" + epoch + "' \
+               AND dvo_db = '" + dvoGpc1Label + "' \
                ORDER BY timestamp DESC LIMIT 1"
 
         try:
             rs = self.executeQuery(sql)
-            rs.first()
-            hours = rs.getFloat(1)
+            if rs.first(): minutes = rs.getFloat(1)
         except:
             self.logger.exception("Unable to get last batch published")
 
-        return hours
+        if not minutes: return "Never"
+
+        hours = minutes/60.0
+        days = float(hours)/24.0
+        weeks = float(days)/7.0
+
+        if minutes < 60: return "%.1f minutes ago" % minutes
+        elif hours < 48: return "%.1f hours ago" % hours
+        elif days < 7: return "%.1f days ago" % days
+        return "%.1f weeks ago" % weeks
 
     '''
@@ -153 +207 @@
 
         return bpm
-
-
-    '''
-    TODO
-    '''
-    def getFailedBatches(self, batchType, epoch, dvoGpc1Label):
-
-        sql = "SELECT DISTINCT stage_id \
-               FROM batch \
-               WHERE timestamp > '" + epoch + "' \
-               AND batch_type = '" + batchType + "' \
-               AND dvo_db = '" + dvoGpc1Label + "' \
-               AND !processed"
-
-        ids = []
-        try:
-            rs = self.executeQuery(sql)
-            while (rs.next()):
-                ids.append(rs.getInt(1))
-        except:
-            self.logger.exception("Can't query for failed ids in ipptopsps Db")
-
-        rs.close()
-
-        self.logger.debug("Found %d failed items for batchType='%s'" % (len(ids), batchType))
-
-        return ids
 
     '''
@@ -200 +227 @@
         sql = "UPDATE batch \
                SET processed = " + str(processed) + " \
+               WHERE batch_id = " + str(batchID)
+
+        self.execute(sql)
+
+    '''
+    Updates ODM status for this batch 
+    '''
+    def updateOdmStatus(self, batchID, odmStatus):
+
+        sql = "UPDATE batch \
+               SET loaded_to_ODM = " + str(odmStatus['LOADEDTOODM']) + ", \
+               merge_worthy = " + str(odmStatus['MERGEWORTHY']) + ", \
+               merged = " + str(odmStatus['MERGED']) + " \
                WHERE batch_id = " + str(batchID)