Changeset 37820 for branches/eam_branches/ipp-pv3-20140717-merge/ippToPsps/jython/gpc1db.py

Timestamp:

Jan 12, 2015, 12:24:24 PM (12 years ago)

Author:

eugene

Message:

various updates merged from trunk

File:

• branches/eam_branches/ipp-pv3-20140717-merge/ippToPsps/jython/gpc1db.py (modified) (6 diffs)

branches/eam_branches/ipp-pv3-20140717-merge/ippToPsps/jython/gpc1db.py

@@ -108 +108 @@
                    AND mjd_obs <= (to_days('" + tend + "') - 678941) "
            # self.logger.infoPair("sql",sql)      
-        elif batchType == "DF":
+        elif batchType == "DF":            # this is specifically only for WARPSTACK types of diffs -
+            # it only makes sense for the date to come from the warp1
+            # may need to be more clever if we do other types of diffs
 
             stage = "diff"
-            sql = "SELECT stuff from stufftable"
+            sql = "SELECT DISTINCT stage_extra1, radeg,decdeg \
+                   FROM addRun \
+                   JOIN addProcessedExp using (add_id) \
+                   JOIN minidvodbRun using (minidvodb_name) \
+                   JOIN minidvodbProcessed using (minidvodb_id)  \
+                   JOIN mergedvodbRun using (minidvodb_id) \
+                   JOIN mergedvodbProcessed using (merge_id) \
+                   JOIN diffInputSkyfile \
+                   ON (diff_id = stage_id AND diff_skyfile_id = stage_extra1 \
+                   AND stage = 'diff') \
+                   JOIN skycell using (skycell_id, tess_id) \
+                   JOIN warpRun on (warp1 = warp_id \
+                   AND diffInputSkyfile.skycell_id = skycell_id) \
+                   JOIN fakeRun using (fake_id) \
+                   JOIN camRun using (cam_id) \
+                   JOIN chipRun using (chip_id)  \
+                   JOIN rawExp using (exp_id) \
+                   WHERE  mergedvodbRun.mergedvodb = '" + dvoDb + "' \
+                   AND minidvodbRun.state = 'merged' \
+                   AND minidvodbProcessed.fault = 0 \
+                   AND mergedvodbRun.state = 'full' \
+                   AND mergedvodbProcessed.fault = 0 \
+                   AND addRun.stage = '" + stage + "' \
+                   AND addRun.state = 'full' \
+                   AND decdeg BETWEEN " + str(minDec) + " AND " + str(maxDec) + " \
+                   AND radeg BETWEEN " + str(minRA) + " AND " + str(maxRA) + " \
+                   AND dateobs >= '" + tstart + "'\
+                   AND dateobs <= '" + tend  + "'"
+
 
         elif batchType == "FW":
             
             stage = "forcedwarp"
-            sql = "SELECT differentStuff from Stufftable"
-
-        try:
-            # XXX EAM : test output
-            rs = self.executeQuery(sql)
-            while (rs.next()): 
-               rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)])
-            rs.close()
+            sql = "SELECT DISTINCT sky_id, radeg, decdeg FROM fullForceInput \
+                   JOIN addRun ON(fullForceInput.ff_id = addRun.stage_id, fullForceInput.warp_id = addRun.stage_extra1) \
+                   JOIN minidvodbRun USING(minidvodb_name) \
+                   JOIN minidvodbProcessed USING(minidvodb_id) \
+                   JOIN mergedvodbRun USING(minidvodb_id) \
+                   JOIN mergedvodbProcessed USING (merge_id) \
+                   JOIN fullForceRun USING(ff_id) \
+                   JOIN skycalRun using (skycal_id) \
+                   JOIN stackRun USING(stack_id) \
+                   JOIN stackSumSkyfile using (stack_id) \
+                   JOIN skycell USING(skycell_id) \
+                   WHERE mergedvodbRun.mergedvodb = '" + dvoDb + "' \
+                   AND minidvodbRun.state = 'merged' \
+                   AND minidvodbProcessed.fault = 0 \
+                   AND mergedvodbRun.state = 'full' \
+                   AND mergedvodbProcessed.fault = 0 \
+                   AND addRun.stage = '" + stage + "' \
+                   AND addRun.state = 'full' \
+                   AND decdeg BETWEEN " + str(minDec) + " AND " + str(maxDec) + " \
+                   AND radeg BETWEEN " + str(minRA) + " AND " + str(maxRA) + " \
+                   AND mjd_obs >= (to_days('" + tstart + "')-678941) \
+                   AND mjd_obs <= (to_days('" + tend + "') - 678941) "
+
+        try:
+            rs = self.executeQuery(sql)
+
         except:
             self.logger.exception("Can't query for ids in DVO")
             self.logger.infoPair("failed sql:", sql)
-            return rows
+            sys.exit(1)
+
+        while (rs.next()): 
+            rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)])
+
+        rs.close()
 
         self.logger.debug("Found %d items in DVO database '%s' for stage='%s'" % (len(rows), dvoDb, stage))
@@ -164 +217 @@
         return imageIDs
 
+    
+
+
+
+
     '''
     Gets some stack-stage meta data for this sky_id # TODO this SQL could surely be improved
@@ -194 +252 @@
         return meta
 
+
+    '''
+    Gets some stack-stage meta data for this sky_id # TODO this SQL could surely be improved
+    '''
+    def getDiffStageMeta(self, diffSkyfileID):
+
+        self.logger.debug("Querying GPC1 for stack meta data")
+
+        meta = []
+        sql = "SELECT \
+               filter,\
+               diff_mode, \
+               warp1, \
+               stack2, \
+               skycell_id, \
+               diffRun.software_ver, \
+               exp_time, \
+               (to_days(dateobs)-678941 + time_to_sec(dateobs)/86400.) \
+               FROM diffRun join diffInputSkyfile using (diff_id) \
+               JOIN warpRun on (warp1=warp_id) \
+               JOIN fakeRun using (fake_id) \
+               JOIN camRun using (cam_id) \
+               JOIN chipRun using (chip_id) \
+               JOIN rawExp using (exp_id) \
+               WHERE diff_skyfile_id = %d" % diffSkyfileID
+        #this is a crappy way to get the mjd from the diff but we don't save it anywhere       
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+        except:
+            self.logger.infoPair ("this sql is broken:",sql)
+            self.logger.errorPair("Can't query for", "stack meta data")
+           
+        try:
+            meta.append(rs.getString(1))
+            meta.append(rs.getString(2))
+            meta.append(rs.getString(3))
+            meta.append(rs.getString(4))
+            meta.append(rs.getString(5))
+            meta.append(rs.getString(6))
+            meta.append(rs.getString(7))
+            meta.append(rs.getString(8))
+        except:
+            self.logger.errorPair("getDiffStageMeta()", "empty meta data")
+     
+        return meta
+
+    '''
+    get diff stage cmf name
+    '''
+    def getDiffStageCmf(self, dvoDb, diffSkyfileID):
+        print "got here, diffstagecmf"
+        sql = "SELECT DISTINCT diffSkyfile.path_base \
+               FROM mergedvodbRun join minidvodbRun using (minidvodb_id) \
+               JOIN addRun using (minidvodb_name) \
+               JOIN diffInputSkyfile on (diff_skyfile_id = stage_extra1 \
+               AND diff_id = stage_id AND stage = 'diff') \
+               JOIN diffSkyfile using (diff_id, skycell_id) \
+               WHERE diff_skyfile_id = " + str(diffSkyfileID) + " \
+               AND minidvodbRun.state = 'merged' \
+               AND mergedvodbRun.state = 'full' \
+               AND mergedvodbRun.mergedvodb = '" + dvoDb + "' \
+               AND stage = 'diff'"
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+        except:
+            self.logger.infoPair("failed sql", sql)
+            self.logger.errorPair("can't query for diff cmf", "dfif_skyfile_id = %d" %diff_skyfile_id)
+            return None
+        print "query worked"
+        try: 
+            pathBase = rs.getString(1)
+        except:    
+            self.logger.errorPair("no diff cms found for diff_skyfile_id = %d" %diff_skyfile_id)
+            return None
+        print pathBase
+        files = []
+        if pathBase.startswith("neb"):
+            print "open"
+            f=os.popen("neb-ls -p " + pathBase + ".cmf")
+            for i in f.readlines():
+                print "i",i
+                files.append(i.rstrip())
+                print "append"
+        print i
+        if len(files) < 1: return None
+        fits = Fits(self.logger, self.config, files[0])
+        return fits
+
+    '''
+    Gets a list of PSPS image IDs for this diff ID
+    '''
+    def getImageIDsForThisDiffID(self, diffID):
+
+        self.logger.debug("Querying GPC1 for image IDs for diff ID: " + str(diffID))
+
+        # JOIN stackInputSkyfile shoule use stack_id as well as warp_id, right? (maybe not -- the join to warp is first)
+        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 diffInputSkyfile on warp1 = warp_id  \
+               JOIN fakeRun using (fake_id) \
+               JOIN camRun using (cam_id) \
+               JOIN chipRun using (chip_id) \
+               WHERE diffInputSkyfile.diff_skyfile_id = " + str(diffID) + ") AS a"
+
+        try:
+            rs = self.executeQuery(sql)
+        except:
+            self.logger.infoPair("failed sql:",sql)
+            self.logger.exception("Can't query for imageIDs")
+            
+
+        imageIDs = []
+        while (rs.next()):
+            imageIDs.append(rs.getString(1))
+        rs.close()
+
+        return imageIDs
+
+    
+
+
+    
     '''
     Gets some camera-stage meta data for this cam_id
@@ -339 +524 @@
 
 
-        # XXX EAM : test output
         self.logger.infoPair("smf files:", files)
         if len(files) < 1: return None
@@ -367 +551 @@
     def getStackStageCmf(self, dvoDb, stackID):
      
-        '''
-        sql = "SELECT DISTINCT staticskyResult.path_base \
-               FROM staticskyRun  \
-               JOIN staticskyInput USING(sky_id)  \
-               JOIN staticskyResult USING(sky_id) \
-               JOIN stackRun USING(stack_id)  \
-               JOIN stackSumSkyfile USING(stack_id) \
-               JOIN addRun ON sky_id = stage_id \
-               JOIN minidvodbRun USING (minidvodb_name) \
-               WHERE stack_id = " + str(stackID) + " \
-               AND minidvodbRun.minidvodb_group = '" + dvoDb + "' \
-               AND minidvodbRun.state = 'merged' \
-               AND stage = 'staticsky'"
-        '''
-
         sql = "SELECT DISTINCT skycalResult.path_base \
                FROM skycalRun  \
@@ -431 +600 @@
         fits = Fits(self.logger, self.config, files[0])
         return fits
-
-        # XXX validate the file?
-        # if fits.getPrimaryHeaderValue("STK_ID") == str(stackID):
-
-            # now find and loop through all cmf files at this path_base
-
-            ## XXX the code below was needed when we ingested from staticsky (N files per run)
-            ## XXX ingesting from skycal solves this problem (1 file per run)
-            ## files=os.popen("neb-ls " + pathBase + "%cmf")
-            ## for i in files.readlines():
-            ##     nebPath = i.rstrip()
-            ## 
-            ##     # now attempt to run 'neb-ls -p' to actually read this cmf file
-            ##     try:
-            ##         self.logger.debug("Trying to neb-ls -p on '" + nebPath + "'")
-            ##         paths=os.popen("neb-ls -p " + nebPath)
-            ##         path = paths.readline().rstrip()
-            ##         if len(path) < 1:
-            ##            self.logger.debug("zero length path - skipping")
-            ##            raise
-            ## 
-            ##         print "starint stack stage cmf 3"
-            ## 
-            ##         # if we get here, then the cmf is readable, now check the stack_id
-            ##         fits = Fits(self.logger, self.config, path)
-            ##         if fits.getPrimaryHeaderValue("STK_ID") == str(stackID):
-            ##             # we have the right file!
-            ##             self.logger.debug("SUCCESS!: %s == %s"% (fits.getPrimaryHeaderValue("STK_ID"), str(stackID)))
-            ##             return fits
-            ##         else:
-            ##             # this is not the correct file
-            ##             self.logger.debug("STACK ID: %s != %s"% (fits.getPrimaryHeaderValue("STK_ID"), str(stackID)))
-            ## 
-            ##     # an exception here means that nebulous could not read this file, so we just skip it
-            ##     except:
-            ##         self.logger.debug("NEB FAILED SKIPPING")
-            ##         pass
 
     '''