Changeset 31434 for branches/czw_branch/20110406/ippToPsps/jython/ipptopspsdb.py

Timestamp:

May 4, 2011, 3:20:38 PM (15 years ago)

Author:

watersc1

Message:

Merging trunk into this branch

Location:

branches/czw_branch/20110406

Files:

• . (modified) (1 prop)
• ippToPsps/jython/ipptopspsdb.py (modified) (2 diffs)

branches/czw_branch/20110406/ippToPsps/jython/ipptopspsdb.py

@@ -6 +6 @@
 import logging
 
+from mysql import MySql
 from java.sql import *
-from xml.etree.ElementTree import ElementTree
 
 '''
 Class for ippToPsps database connectivity
 '''
-class IppToPspsDb(object):
-
-    driverName="com.mysql.jdbc.Driver"
+class IppToPspsDb(MySql):
 
     '''
     Constructor
-
     '''
     def __init__(self, logger):
-
-        # setup logging
-        self.logger = logger
-        self.logger.debug("IppToPspsDb Constructor")
-        # open config
-        doc = ElementTree(file="config.xml")
-
-        # set up JDBC connection
-        dbName = doc.find("ipptopspsdatabase/name").text
-        dbHost = doc.find("ipptopspsdatabase/host").text
-        dbUser = doc.find("ipptopspsdatabase/user").text
-        dbPass = doc.find("ipptopspsdatabase/password").text
-        self.url = "jdbc:mysql://"+dbHost+"/"+dbName+"?user="+dbUser+"&password="+dbPass
-        self.con = DriverManager.getConnection(self.url)
-        self.stmt = self.con.createStatement()
+        super(IppToPspsDb, self).__init__(logger,"ipptopspsdatabase")
 
     '''
     Creates a new batch
     '''
-    def createNewBatch(self, expID, surveyType, batchType, dvoDb, datastoreProduct):
+    def createNewBatch(self, batchType, survey, dvoDb, datastoreProduct):
 
-        sql = "SELECT batch_id FROM batches ORDER BY batch_id DESC LIMIT 1"
+        sql = "INSERT INTO batch ( \
+               batch_type, \
+               survey, \
+               dvo_db, \
+               datastore_product \
+               ) VALUES ( \
+               '" + batchType + "', \
+               '" + survey + "', \
+               '" + dvoDb + "', \
+               '" + datastoreProduct + "' \
+               )"
+
+        self.stmt.execute(sql)
+
+        sql = "SELECT MAX(batch_id) FROM batch"
 
         batchID = -1;
@@ -53 +50 @@
             self.logger.exception("Unable to get batch ID")
 
-        if batchID > 0:
-            batchID = batchID + 1
-
-            sql = "INSERT INTO batches \
-                   (batch_id, \
-                    exp_id, \
-                    survey_id, \
-                    batch_type, \
-                    dvo_db, \
-                    datastore_product) \
-                   VALUES \
-                   ("+str(batchID)+", \
-                    " + str(expID) + ", \
-                    '"+surveyType+"', \
-                    '"+batchType+"', \
-                    '"+dvoDb+"', \
-                    '"+datastoreProduct+"')"
-
-            self.stmt.execute(sql)
-
-        self.logger.info("Creating new batch in ippToPsps database with batchID = %d" % batchID)
+        self.logger.info("Created new batch in ippToPsps database with batchID = %d" % batchID)
 
         return batchID;
+
+    '''
+    Updates min/max object ID on this table and batch 
+    '''
+    def updateMinMaxObjID(self, batchID, minObjID, maxObjID):
+
+        sql = "UPDATE batch SET \
+               min_obj_id = " + str(minObjID) + ", \
+               max_obj_id = " + str(maxObjID) + " \
+               WHERE batch_id = " + str(batchID)
+
+        self.stmt.execute(sql)
+
+    '''
+    Updates batch processed field 
+    '''
+    def updateProcessed(self, batchID, processed):
+
+        sql = "UPDATE batch \
+               SET processed = " + str(processed) + " \
+               WHERE batch_id = " + str(batchID)
+
+        self.stmt.execute(sql)
+
+    '''
+    Updates batch loaded_to_datastore field 
+    '''
+    def updateLoadedToDatastore(self, batchID, loadedToDatastore):
+
+        sql = "UPDATE batch \
+               SET loaded_to_datastore = " + str(loadedToDatastore) + " \
+               WHERE batch_id = " + str(batchID)
+
+        self.stmt.execute(sql)
+
+    '''
+    Have we already processed and published this batch?
+    '''
+    def alreadyProcessed(self, table, col, value):
+
+        sql = "SELECT COUNT(*) FROM \
+               " + table + " \
+               JOIN batch USING(batch_id) \
+               WHERE " + col + " = " + str(value) + " \
+               AND processed \
+               AND loaded_to_datastore"
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+            rs.first()
+            if rs.getInt(1) > 0: 
+                self.logger.error("Batch with "+col+" = "+str(value)+" has already been processed and published to datastore")
+                return True
+            else: 
+                return False
+        except:
+            self.logger.exception("Unable to check whether this batch has already been processed")
+
+     
+
+    '''
+    Inserts some detection metadata for this batch ID
+    '''
+    def insertDetectionMeta(self, batchID, expID, filter):
+
+        sql = "INSERT INTO detection ( \
+               batch_id \
+               ,exp_id \
+               ,filter \
+               ) VALUES ( \
+               " + str(batchID) + " \
+               ," + str(expID) + " \
+               ,'" + filter + "' \
+               )"
+
+        self.stmt.execute(sql)
+
+    '''
+    Inserts some stack metadata for this batch ID
+    '''
+    def insertStackMeta(self, batchID, skyID, stackID, filter, stackType):
+
+        sql = "INSERT INTO stack ( \
+               batch_id \
+               ,sky_id \
+               ,stack_id \
+               ,filter \
+               ,stack_type \
+               ) VALUES ( \
+               " + str(batchID) + " \
+               ," + str(skyID) + " \
+               ," + str(stackID) + " \
+               ,'" + filter + "' \
+               ,'" + stackType + "' \
+               )"
+
+        self.stmt.execute(sql)
+
 
     '''