Changeset 31434 for branches/czw_branch/20110406/ippToPsps/jython/gpc1db.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/gpc1db.py (modified) (5 diffs)

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

@@ -7 +7 @@
 import logging
 
+from mysql import MySql
 from java.sql import *
-from xml.etree.ElementTree import ElementTree
 
 
@@ -14 +14 @@
 Class for GPC1 database connectivity
 '''
-class Gpc1Db(object):
-
-    driverName="com.mysql.jdbc.Driver"
+class Gpc1Db(MySql):
 
     '''
     Constructor
-
     '''
     def __init__(self, logger):
-
-        # setup logging
-        self.logger = logger
-        self.logger.debug("Gpc1Db constructor")
-
-        # open config
-        doc = ElementTree(file="config.xml")
-
-        # set up JDBC connection
-        dbName = doc.find("gpc1database/name").text
-        dbHost = doc.find("gpc1database/host").text
-        dbUser = doc.find("gpc1database/user").text
-        dbPass = doc.find("gpc1database/password").text
-        self.url = "jdbc:mysql://"+dbHost+"/"+dbName+"?user="+dbUser+"&password="+dbPass
-        self.con = DriverManager.getConnection(self.url)
-        self.stmt = self.con.createStatement()
+        super(Gpc1Db, self).__init__(logger,"gpc1database")
 
     '''
@@ -46 +28 @@
 
         self.logger.debug("Gpc1Db destructor")
-        self.stmt.close()
-        self.con.close()
-
-
-    '''
-    Gets all cmf files for this sky_id. handles both absolute paths and neb paths
-    '''
-    def getStackStageCmfs(self, skyID):
-
-        self.logger.debug("Querying for stack cmf files")
-
-        sql = "SELECT path_base, num_inputs \
-               FROM staticskyResult \
-               WHERE sky_id = %d" % skyID
-        try:
-            rs = self.stmt.executeQuery(sql)
-            rs.first()
-        except:
-            self.logger.exception("Can't query for stack cmfs")
+
+    '''
+    Gets a list of ids in this DVO database for this stage, could be cam or staticsky (so far)
+    '''
+    def getIDsInThisDVODbForThisStage(self, dvoDb, stage):
+
+        sql = "SELECT DISTINCT stage_id \
+               FROM addRun \
+               WHERE stage = '" + stage + "' \
+               AND dvodb = '" + dvoDb + "'"
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+        except:
+            self.logger.exception("Can't query for ids in DVO")
+
+        ids = []
+        while (rs.next()):
+            ids.append(rs.getInt(1))
+
+        rs.close()
+
+        self.logger.debug("Found %d items in DVO database '%s' for stage='%s'" % (len(ids), dvoDb, stage))
+
+        return ids
+
+    '''
+    Gets a list of PSPS image IDs for this stack ID
+    '''
+    def getImageIDsForThisStackID(self, stackID):
+
+        self.logger.debug("Querying GPC1 for image IDs for stack ID: " + str(stackID))
+
+        sql = "SELECT DISTINCT CONCAT(exp_id, SUBSTR(class_id, 3, 4)) FROM ( \
+               SELECT DISTINCT exp_id,class_id \
+               FROM warpSkyCellMap \
+               JOIN warpRun USING(warp_id) \
+               JOIN stackInputSkyfile USING(warp_id) \
+               JOIN stackRun USING(stack_id,skycell_id) \
+               JOIN fakeRun USING(fake_id) \
+               JOIN camRun USING(cam_id)  \
+               JOIN chipRun USING(chip_id) \
+               WHERE stackRun.stack_id = " + str(stackID) + ") AS a"
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+        except:
+            self.logger.exception("Can't query for imageIDs")
+
+        imageIDs = []
+        while (rs.next()):
+            imageIDs.append(rs.getString(1))
+        rs.close()
+
+        return imageIDs
+
+    '''
+    Gets some stack-stage meta data for this sky_id # TODO this SQL could surely be improved
+    '''
+    def getStackStageMeta(self, skyID, filter):
+
+        self.logger.debug("Querying GPC1 for stack meta data")
+
+        meta = []
+        sql = "SELECT \
+               stackRun.stack_id,\
+               stackRun.skycell_id \
+               FROM \
+               staticskyInput, staticskyRun, stackRun, staticskyResult \
+               WHERE staticskyRun.sky_id = staticskyInput.sky_id \
+               AND staticskyInput.stack_id = stackRun.stack_id \
+               AND staticskyInput.sky_id = staticskyResult.sky_id \
+               and staticskyInput.sky_id = %d \
+               and filter = '%s'" % (skyID, filter)
+
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+            rs.first()
+            meta.append(rs.getInt(1))
+            meta.append(rs.getString(2))
+        except:
+            self.logger.exception("Can't query for stack meta")
+
+        return meta
+    '''
+    Gets some camera-stage meta data for this cam_id
+    '''
+    def getCameraStageMeta(self, camID):
+
+        self.logger.debug("Querying GPC1 for camera meta data")
+
+        meta = []
+        sql = "SELECT exp_id, exp_name, camRun.dist_group \
+               FROM camRun \
+               JOIN chipRun USING(chip_id) \
+               JOIN rawExp USING(exp_id) WHERE camRun.cam_id = %d" % camID
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+            rs.first()
+            meta.append(rs.getInt(1))
+            meta.append(rs.getString(2))
+            meta.append(rs.getString(3))
+        except:
+            self.logger.exception("Can't query for camera meta")
+
+        return meta
+
+    '''
+    Gets a camera-stage smf for this cam_id
+    '''
+    def getCameraStageSmf(self, camID):
+
+        self.logger.debug("Querying GPC1 for camera smf files with cam_id = " + str(camID))
+
+        sql = "SELECT path_base \
+               FROM camProcessedExp \
+               JOIN camRun USING(cam_id) \
+               WHERE camRun.cam_id = %d" % camID
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+            rs.first()
+        except:
+            self.logger.exception("Can't query for camera smfs")
 
         # get path to base dir of cmf files
@@ -74 +163 @@
         if path.startswith("neb"):
 
-            f=os.popen("neb-ls -p "+path+"/%cmf")
+            f=os.popen("neb-ls -p "+path+"/%smf")
             for i in f.readlines():
                 files.append(i.rstrip())
@@ -82 +171 @@
             files = glob.glob(path + "/*.cmf")
 
+        return files[0] # TODO just returning first file - check
+
+
+    '''
+    Gets all cmf files for this sky_id. handles both absolute paths and neb paths
+    '''
+    def getStackStageCmfs(self, skyID):
+
+        self.logger.debug("Querying GPC1 for stack cmf files")
+
+        sql = "SELECT path_base, num_inputs \
+               FROM staticskyResult \
+               WHERE sky_id = %d" % skyID
+
+        try:
+            rs = self.stmt.executeQuery(sql)
+            rs.first()
+        except:
+            self.logger.exception("Can't query for stack cmfs")
+
+        # get path to base dir of cmf files
+        path = rs.getString(1)
+        #path = path[0:path.rfind("/")]
+       
+        # list all cmf files if a neb path
+        files = []
+        if path.startswith("neb"):
+
+            f=os.popen("neb-ls -p "+path+"%cmf")
+            print "neb-ls -p "+path+"%cmf"
+            for i in f.readlines():
+                files.append(i.rstrip())
+                print i.rstrip()
+
+        # or not a neb path
+        else:
+            files = glob.glob(path + "*.cmf")
+
         return files