Changeset 31842

Timestamp:

Jul 8, 2011, 2:50:57 PM (15 years ago)

Author:

rhenders

Message:

added numerous methods to provide numbers for new metrics code; method tp join across ipptopsps and gpc1 Dbs in order to ascertian all unprocessed IDs

File:

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

trunk/ippToPsps/jython/ipptopspsdb.py

@@ -17 +17 @@
     Constructor
     '''
-    def __init__(self, logger):
-        super(IppToPspsDb, self).__init__(logger,"ipptopspsdatabase")
+    def __init__(self, logger, doc):
+        super(IppToPspsDb, self).__init__(logger, doc, "ipptopspsdatabase")
 
     '''
@@ -52 +52 @@
             self.logger.exception("Unable to get batch ID")
 
-        self.logger.info("Created new batch in ippToPsps database with batchID = %d" % batchID)
+        self.logger.debug("Created new batch in ippToPsps database with batchID = %d" % batchID)
 
         return batchID;
+
+    '''
+    TODO
+    '''
+    def getUnprocessedIDsForThisStage(self, dvoDb, batchType, startDate):
+
+        if batchType == "P2": # TODO define these someplace 
+
+            stage = "cam"
+            sql = "SELECT DISTINCT stage_id \
+                   FROM gpc1.addRun \
+                   WHERE stage = '" + stage + "' \
+                   AND dvodb = '" + dvoDb + "' \
+                   AND stage_id NOT IN "
+
+        elif batchType == "ST": 
+
+            stage = "staticsky"
+            sql = "SELECT DISTINCT stack_id \
+                   FROM gpc1.staticskyInput \
+                   JOIN gpc1.addRun ON(gpc1.staticskyInput.sky_id = gpc1.addRun.stage_id) \
+                   WHERE stage = '" + stage + "' \
+                   AND dvodb = '" + dvoDb + "' \
+                   AND stack_id NOT IN "
+
+        sql = sql + "(SELECT stage_id \
+              FROM batch \
+              WHERE batch_type = '" + batchType + "' \
+              AND timestamp > '" + startDate + "' \
+              AND loaded_to_datastore)"
+
+        ids = []
+        try:
+            rs = self.executeQuery(sql)
+            while (rs.next()):
+                ids.append(rs.getInt(1))
+        except:
+            self.logger.exception("Can't query for ids in DVO")
+
+        rs.close()
+
+        self.logger.debug("Found %d unprocessed items in DVO database '%s' for stage='%s'" % (len(ids), dvoDb, stage))
+
+        return ids
+
+    '''
+    TODO
+    '''
+    def getLastBatchPublished(self, batchType):
+
+        sql = "SELECT TIMESTAMPDIFF(MINUTE, timestamp, now())/60.0 \
+               FROM batch \
+               WHERE batch_type = '" + batchType + "' \
+               AND loaded_to_datastore \
+               ORDER BY timestamp DESC LIMIT 1"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            hours = rs.getFloat(1)
+        except:
+            self.logger.exception("Unable to get last batch published")
+
+        return hours
+
+    '''
+    TODO
+    '''
+    def getBatchesPerDay(self, batchType, startTime, endTime=""):
+
+        bph = self.getBatchesPerHour(batchType, startTime, endTime)
+        return bph * 24
+
+    '''
+    TODO
+    '''
+    def getBatchesPerHour(self, batchType, startTime, endTime=""):
+
+        bpm = self.getBatchesPerMinute(batchType, startTime, endTime)
+        return bpm * 60
+
+    '''
+    TODO
+    '''
+    def getAverageTimePerBatch(self, batchType, startTime, endTime=""):
+
+        sql = "SELECT (TIMESTAMPDIFF(MINUTE, min(timestamp), max(timestamp)) / COUNT(*)) \
+               FROM batch \
+               WHERE timestamp > '" + startTime + "' and batch_type = '" + batchType + "'"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            time = rs.getDouble(1)
+        except:
+            self.logger.exception("Unable to get batches per minute")
+
+        return time
+
+    '''
+    TODO
+    '''
+    def getBatchesPerMinute(self, batchType, startTime, endTime=""):
+
+        sql = "SELECT (count(*) / TIMESTAMPDIFF(MINUTE, min(timestamp), max(timestamp)) ) \
+               FROM batch \
+               WHERE timestamp > '" + startTime + "' and batch_type = '" + batchType + "'"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            bpm = rs.getDouble(1)
+        except:
+            self.logger.exception("Unable to get batches per minute")
+
+        return bpm
+
+
+    '''
+    TODO
+    '''
+    def getTotalFailedBatches(self, batchType, startTime, endTime=""):
+
+        sql = "SELECT COUNT(DISTINCT stage_id) \
+               FROM batch \
+               WHERE timestamp > '" + startTime + "' \
+               AND batch_type = '" + batchType + "' \
+               AND !processed \
+               AND stage_id NOT IN \
+               (SELECT DISTINCT stage_id \
+                FROM batch \
+                WHERE timestamp > '" + startTime + "'  \
+                AND batch_type = '" + batchType + "' \
+                AND loaded_to_datastore)"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            total = rs.getInt(1)
+        except:
+            self.logger.exception("Unable to count failed batches")
+
+        return total
+    '''
+    TODO
+    '''
+    def getTotalBatchesPublished(self, batchType, startTime, endTime=""):
+
+        sql = "SELECT COUNT(*) \
+               FROM batch \
+               WHERE timestamp > '" + startTime + "' \
+               AND batch_type = '" + batchType + "' \
+               AND loaded_to_datastore"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            total = rs.getInt(1)
+        except:
+            self.logger.exception("Unable to count batches")
+
+        return total
 
     '''
@@ -134 +296 @@
     Inserts some stack metadata for this batch ID
     '''
-    def insertStackMeta(self, batchID, stackID, filter, stackType):
+    def insertStackMeta(self, batchID, filter, stackType):
 
         sql = "INSERT INTO stack ( \
                batch_id \
-               ,stack_id \
                ,filter \
                ,stack_type \
                ) VALUES ( \
                " + str(batchID) + " \
-               ," + str(stackID) + " \
                ,'" + filter + "' \
-               ,'" + stackType + "' \
-               )"
+               ,'" + stackType + "')"
 
         self.execute(sql)