Index: trunk/ippToPsps/jython/objectbatch.py =================================================================== --- trunk/ippToPsps/jython/objectbatch.py (revision 36744) +++ trunk/ippToPsps/jython/objectbatch.py (revision 37246) @@ -15,4 +15,5 @@ from scratchdb import ScratchDb from dvoobjects import DvoObjects +from sqlUtility import sqlUtility import logging.config @@ -37,6 +38,5 @@ scratchDb, dvoID, - batchID, - useFullTables): + batchID): super(ObjectBatch, self).__init__( @@ -50,9 +50,7 @@ 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,8 +72,5 @@ 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,8 +85,4 @@ def alterPspsTables(self): - # 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") - return True @@ -101,5 +92,6 @@ 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,4 +101,78 @@ ''' 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 = PSF_QF_PERF_MAX \ + ,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) " + + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair("failed update MeanObject", sql) + raise + + # 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,85 +193,32 @@ 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) " - - + # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table? + # sqlLine = sqlUtility() + + sql = "UPDATE ObjectThin JOIN " + sql += cpsTable + " AS cps " + sql += "ON (cps.row = (ObjectThin.row * " + sql += str(filterCount) + ")-(" + sql += str(filterCount) + " - " + sql += str(filter[0]) + sql += ")) " + + sql += "SET ObjectThin.n" + filter[1] + " = NCODE, " + sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET" 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 - - sql = "UPDATE ObjectThin JOIN \ - " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ - SET \ - ObjectThin.n" + filter[1] + " = NCODE " - self.logger.info(sql) - 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: # now do a sum of n[filters], but do not include the ones with -999 - sql = "UPDATE ObjectThin \ - SET nDetections = nDetections + n" + filter[1] + " \ - WHERE n" + filter[1] + " != -999" - self.scratchDb.execute(sql) - - + sql = "UPDATE ObjectThin " + sql += "SET nDetections = nDetections + n" + filter[1] + sql += " 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,10 +294,4 @@ return False - - - - - - ''' Populates the Object table @@ -296,6 +303,8 @@ 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,62 +324,39 @@ - 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("ippObjID", "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly + sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") + sqlLine.group("randomID", "FLOOR(RAND()*9223372036854775807)") # XXX where does this number come from?? + sqlLine.group("batchID", "'" + str(self.batchID) + "'") + sqlLine.group("dvoRegionID", "CAT_ID") + sqlLine.group("tessID", "TESS_ID") + sqlLine.group("projectionID", "PROJECTION_ID") + sqlLine.group("skycellID", "SKYCELL_ID") + 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("posMeanChisq", "CHISQ_POS") + sqlLine.group("nStackObjectRows", "'0'") # XXX I need to add / define this in dvopsps + sqlLine.group("nStackDetections", "'0'") + sqlLine.group("nDetections", "'0'") + sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName) try: @@ -390,9 +376,9 @@ 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 @@ -455,12 +441,5 @@ self.logger.infoPair("Populating MeanObject from ", "ObjectThin") - sql = "INSERT INTO MeanObject ( \ - objID \ - ,gcobjID \ - ) \ - SELECT \ - objID \ - ,gcobjID \ - FROM ObjectThin" + sql = "INSERT INTO MeanObject ( objID ) SELECT objID FROM ObjectThin" try: self.scratchDb.execute(sql)