Changeset 37129 for branches/eam_branches/ipp-20140717/ippToPsps/jython/objectbatch.py

Timestamp:

Jul 28, 2014, 6:20:27 AM (12 years ago)

Author:

eugene

Message:

dropping options for stilts dvo import methods; fixing a number of previously ignored errors (eg, dropping non-existent tables); allowing queue questions to strip leading and trailing spaces (so we can read from an input file); echo back the response so input from a file is clearer; re-write big sql blocks with new sql utility code; updating names in MeanObject, ObjectThin to be more consistent; add a new test code program; update detection fields to match new schema; drop the code for the old now-unused tables (detecitonCalib, SkinnyObject, etc)

Location:

branches/eam_branches/ipp-20140717/ippToPsps

Files:

• . (modified) (1 prop)
• jython/objectbatch.py (modified) (14 diffs)

branches/eam_branches/ipp-20140717/ippToPsps/jython/objectbatch.py

@@ -15 +15 @@
 from scratchdb import ScratchDb
 from dvoobjects import DvoObjects
+from sqlUtility import sqlUtility
 
 import logging.config
@@ -37 +38 @@
                  scratchDb,
                  dvoID,
-                 batchID,
-                 useFullTables):
+                 batchID):
 
        super(ObjectBatch, self).__init__(
@@ -50 +50 @@
                batchID,
                "OB", 
-               None,
-               1)
+               None)
 
        try:
-           ## XX pass in the existing connection to scratchDb
            self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb)
        except:
@@ -74 +72 @@
         self.region = self.scratchDb.getRegionNameFromThisDvoIndex(self.id)
         self.ippToPspsDb.insertObjectMeta(self.batchID, self.region)
-        if True:
-            self.dvoObjects.nativeIngestRegion(self.region)
-        else:
-            self.dvoObjects.ingestRegion(self.region)
+        self.dvoObjects.nativeIngestRegion(self.region)
 
         cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
@@ -90 +85 @@
     def alterPspsTables(self):
 
+        # XXX EAM 2014.07.24 : why is this no longer needed?
         # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using ``
-      #  self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double")
-      #  self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double")
+        # self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double")
+        # self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double")
 
         return True
@@ -101 +97 @@
     def indexIppTables(self):
 
-        self.logger.infoPair("Creating indexes on", "IPP tables")
+        # since dvopsps is now used, no action is needed here
+        # self.logger.infoPair("Creating indexes on", "IPP tables")
 
         return True
@@ -109 +106 @@
     '''
     def updateMeanObjectFromCps(self, cpsTable):
+
+        # list of all filters PSPS is interested in
+        # XXX EAM : 2014.07.24 : this list should probably be in a config file somewhere
+        interestedFilters = ['g', 'r', 'i', 'z', 'y']
+        
+        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
+    
+        # get a count of the available filters
+        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()
+
+        self.logger.infoPair("Available filters in Photcodes", filters)
+
+        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
+        self.logger.infoPair("Adding magnitudes from", "cps table")
+        for filter in filters:
+
+            filterID = self.scratchDb.getFilterID(filter[1])
+
+            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
+            # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
+            # XXX EAM : 20140724 this manipulation is no longer needed : [grizy]Flags is now 4 byte (was 8 byte!)
+
+            # set the MeanObject fields based largely on dvopsps cps fields:
+
+            # XXX EAM 20140724 : filterCount is meant to match
+            # Nsecfilt, but is potentially not determined correctly.
+            # use a call to a dvo-native command which knows how to
+            # find Nsecfilt (or save in the db with dvopsps)
+
+            # the math below depends on filterCount = Nsecfilt and MeanObject.row being 1 counting but cps being 0 counting?
+            # cps.row has a count of MeanObject.row * Nsecfilt + Nfilter 
+            #  " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+
+            sql = "UPDATE MeanObject JOIN \
+                   " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+                   SET \
+                    MeanObject." + filter[1] + "QfPerfect = -999 \
+                   ,MeanObject." + filter[1] + "MeanPSFMag     = MAG \
+                   ,MeanObject." + filter[1] + "MeanPSFMagErr  = MAG_ERR \
+                   ,MeanObject." + filter[1] + "MeanPSFMagStd  = MAG_STDEV \
+                   ,MeanObject." + filter[1] + "MeanPSFMagMin  = MAG_MIN \
+                   ,MeanObject." + filter[1] + "MeanPSFMagMax  = MAG_MAX \
+                   ,MeanObject." + filter[1] + "MeanPSFMagNpt  = NUSED \
+                   ,MeanObject." + filter[1] + "MeanKronMag    = MAG_KRON \
+                   ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
+                   ,MeanObject." + filter[1] + "MeanKronMagStd = MAG_KRON_STDEV \
+                   ,MeanObject." + filter[1] + "MeanKronMagNpt = NUSED_KRON \
+                   ,MeanObject." + filter[1] + "MeanApMag      = MAG_AP \
+                   ,MeanObject." + filter[1] + "MeanApMagErr   = MAG_AP_ERR \
+                   ,MeanObject." + filter[1] + "MeanApMagStd   = MAG_AP_STDEV \
+                   ,MeanObject." + filter[1] + "MeanApMagNpt   = NUSED_AP \
+                   ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "
+
+
+            self.logger.info(sql)
+            self.scratchDb.execute(sql)
+
+        # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
+        # XXX EAM 20140724 : keep this cut?
+        cut = 0.5
+        self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut)
+        for filter in filters:
+
+            sql = "UPDATE MeanObject \
+                   SET " + filter[1] + "MeanPSFMagErr = null \
+                   WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
+            self.scratchDb.execute(sql)
+
+    '''
+    Inserts stuff for all mags
+    '''
+    def updateObjectThinFromCps(self, cpsTable):
 
         # list of all filters PSPS is interested in
@@ -127 +196 @@
             filterID = self.scratchDb.getFilterID(filter[1])
 
-            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
-            # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
-
-            sql = "UPDATE MeanObject JOIN \
-                   " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
-                   SET \
-                   MeanObject." + filter[1] + "QfPerfect = -999 \
-                   ,MeanObject." + filter[1] + "MeanPSFMag = MAG \
-                   ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \
-                   ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \
-                   ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
-                   ,MeanObject." + filter[1] + "nIncPSFMag = -999 \
-                   ,MeanObject." + filter[1] + "MeanPSFMagStd = -999 \
-                   ,MeanObject." + filter[1] + "MinPSFMag = -999 \
-                   ,MeanObject." + filter[1] + "MaxPSFMag = -999 \
-                   ,MeanObject." + filter[1] + "nIncKronMag = -999 \
-                   ,MeanObject." + filter[1] + "MeanKronMag = -999 \
-                   ,MeanObject." + filter[1] + "MeanKronMagStd = -999 \
-                   ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \
-                   ,MeanObject." + filter[1] + "MeanApMagErr = MAG_ERR \
-                   ,MeanObject." + filter[1] + "MeanApMagStd = MAG_STDEV \
-                   ,MeanObject." + filter[1] + "nIncApMag = -999 \
-                   ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "
-
-
-            self.logger.info(sql)
-            self.scratchDb.execute(sql)
-
-        # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
-        cut = 0.5
-        self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut)
-        for filter in filters:
-
-            sql = "UPDATE MeanObject \
-                   SET " + filter[1] + "MeanPSFMagErr = null \
-                   WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
-            self.scratchDb.execute(sql)
-    '''
-    Inserts stuff for all mags
-    '''
-    def updateObjectThinFromCps(self, cpsTable):
-
-        # list of all filters PSPS is interested in
-        interestedFilters = ['g', 'r', 'i', 'z', 'y']
-        
-        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
-    
-        # get a count of the available filters
-        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()
-        # filterCount = len(filters)
-
-        self.logger.infoPair("Available filters in Photcodes", filters)
-
-        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
-        self.logger.infoPair("Adding magnitudes from", "cps table")
-        for filter in filters:
-
-            filterID = self.scratchDb.getFilterID(filter[1])
-
-            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
-            # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
-
+            # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table?
             sql = "UPDATE ObjectThin JOIN \
                    " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
@@ -196 +204 @@
             self.scratchDb.execute(sql)
 
+        # XXX this does not seem like a good thing to leave in SQL
         self.logger.infoPair("Calculating nDetections from", "n[filters]")
         for filter in filters:
@@ -203 +212 @@
                    WHERE n" + filter[1] + " != -999"
             self.scratchDb.execute(sql)
-            
-
 
     '''
     give objectName to objectThin
+    XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps?
     '''
     def updateObjName(self):
@@ -281 +289 @@
             return False
 
-
-
-
-
-
-
     '''
     Populates the Object table
@@ -296 +298 @@
 
         if False:
+            # XXX EAM 20140724 : this is probably wrong : flux measurements can be 0.0 or negative: please review
             self.logger.infoPair("setting to null  > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR")
             sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 "
+            self.exitProgram("review this code" + sql)
       
             try:
@@ -315 +319 @@
 
         
-        self.logger.infoPair("Populating", "ThinObject")
-        self.logger.infoPair("Inserting objects from", "cpt file")
-
-        # note `` around dec here, as this is a reserved word in MySQL
-        sql = "INSERT IGNORE INTO ObjectThin (\
-               objID \
-               ,gcobjID \
-               ,ippObjID \
-               ,surveyID \
-               ,skyCellID \
-               ,randomID \
-               ,batchID \
-               ,dvoRegionID \
-               ,dataRelease \
-               ,objInfoFlag \
-               ,qualityFlag \
-               ,consistencyFlag \
-               ,raStack \
-               ,decStack \
-               ,raStackErr \
-               ,decStackErr \
-               ,raMean \
-               ,decMean \
-               ,raMeanErr \
-               ,decMeanErr \
-               ,raMeanStd \
-               ,decMeanStd \
-               ,nStackObjectRows \
-               ,nStackDetections \
-               ,nDetections \
-                ) \
-               SELECT \
-               EXT_ID \
-               , -999 \
-               ,CAT_ID*1000000000 + OBJ_ID \
-               ," + str(self.surveyID) + " \
-               , -999 \
-               , FLOOR(RAND()*9223372036854775807) \
-               , " + str(self.batchID) + "\
-               , -999 \
-               , " + str(self.skychunk.dataRelease) + "\
-               ,FLAGS \
-               ,FLAGS >> 24 & 0xFF \
-               , 0 \
-               , -999 \
-               , -999 \
-               , -999 \
-               , -999 \
-               ,RA \
-               ,DEC_ \
-               ,RA_ERR \
-               ,DEC_ERR \
-               , -999 \
-               , -999 \
-               ,0 \
-               ,0 \
-               ,0 \
-                FROM " + cptTableName
+        self.logger.info("Populating ThinObject")
+        self.logger.info("Inserting objects from cpt file")
+
+        # note "dec" is a reserved word in MySQL
+        # XXX EAM 20140724 : do not use INGORE unless we discover unavoidable problems...
+        # INSERT IGNORE INTO ObjectThin
+
+        sqlLine = sqlUtility("INSERT INTO ObjectThin (")
+
+        sqlLine.group("objID",           "EXT_ID") 
+        # sqlLine.group("gcobjID",       "EXT_ID_GC")  # drop this one?
+        sqlLine.group("ippObjID",        "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly
+        sqlLine.group("surveyID",        str(self.surveyID))
+        sqlLine.group("skyCellID",       "-999") # XXX This does not make sense
+        sqlLine.group("randomID",        "FLOOR(RAND()*9223372036854775807)") # XXX where does this number come from??
+        sqlLine.group("batchID",         str(self.batchID))
+        sqlLine.group("dvoRegionID",     "DVO_REGION") # XXX add to dvopsps output for cpt
+        sqlLine.group("dataRelease",     str(self.skychunk.dataRelease))
+        sqlLine.group("objInfoFlag",     "FLAGS") 
+        sqlLine.group("qualityFlag",     "FLAGS >> 24 & 0xFF")
+        sqlLine.group("consistencyFlag", "0") 
+        sqlLine.group("raStack",         "RA_STK")
+        sqlLine.group("decStack",        "DEC_STK") 
+        sqlLine.group("raStackErr",      "RA_STK_ERR") 
+        sqlLine.group("decStackErr",     "DEC_STK_ERR")
+        sqlLine.group("raMean",          "RA_MEAN")
+        sqlLine.group("decMean",         "DEC_MEAN")
+        sqlLine.group("raMeanErr",       "RA_ERR")
+        sqlLine.group("decMeanErr",      "DEC_ERR")
+        sqlLine.group("raMeanStd",       "-999") # XXX I do not calculate this, use chisq?
+        sqlLine.group("decMeanStd",      "-999")
+        sqlLine.group("nStackObjectRows", "0") # XXX I need to add / define this in dvopsps
+        sqlLine.group("nStackDetections", "0")
+        sqlLine.group("nDetections",      "0")
+        sql = sqlLine.make(") SELECT ", " FROM " + cptTableName)
 
         try:
@@ -390 +371 @@
         self.updateObjName()
 
-
         self.logger.infoPair("update ObjectThin from ","cps table")
 
         self.updateObjectThinFromCps(cpsTableName)
 
+        # XXX EAM 20140724 : is this necessary??
         #objects can have out of range ra dec in dvo - need to find and kill them at the end