Index: trunk/ippToPsps/jython/objectbatch.py =================================================================== --- trunk/ippToPsps/jython/objectbatch.py (revision 35496) +++ trunk/ippToPsps/jython/objectbatch.py (revision 36736) @@ -91,5 +91,6 @@ # 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 Object 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 @@ -107,5 +108,5 @@ Inserts stuff for all mags ''' - def insertMags(self, cpsTable): + def insertMeanMags(self, cpsTable): # list of all filters PSPS is interested in @@ -129,28 +130,16 @@ # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags - sql = "UPDATE Object JOIN \ - " + cpsTable + " AS cps ON (cps.row = (Object.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ + sql = "UPDATE MeanObject JOIN ObjectThin using (objID) JOIN \ + " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ SET \ - n" + filter[1] + " = NCODE \ - ," + filter[1] + "MeanPSFMag = MAG \ - ," + filter[1] + "MeanPSFMagErr = MAG_ERR \ - ," + filter[1] + "MeanKronMag = MAG_KRON \ - ," + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ - ," + filter[1] + "StackPSFMag = STACK_PSF_MAG \ - ," + filter[1] + "StackPSFMagErr = STACK_PSF_MAG_ERR \ - ," + filter[1] + "StackKronMag = STACK_KRON_MAG \ - ," + filter[1] + "StackKronMagErr = STACK_KRON_MAG_ERR \ - ," + filter[1] + "20pct = MAG_20/1000 \ - ," + filter[1] + "80pct = MAG_80/1000 \ - ," + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) \ - ," + filter[1] + "StackDetectID = STACK_DETECT_ID" - -# old method for setting the Stack (PSF,Kron) Mags: -# ," + filter[1] + "StackPSFMag = -2.5 * log10(FLUX_PSF) + 8.9 \ -# ," + filter[1] + "StackPSFMagErr = FLUX_PSF_ERR / FLUX_PSF \ -# ," + filter[1] + "StackKronMag = -2.5 * log10(FLUX_KRON) + 8.9 \ -# ," + filter[1] + "StackKronMagErr = FLUX_KRON_ERR / FLUX_KRON \ - - #self.logger.info(sql) + ObjectThin.n" + filter[1] + " = NCODE \ + ,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] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) " + + + self.logger.info(sql) self.scratchDb.execute(sql) @@ -158,5 +147,5 @@ for filter in filters: # now do a sum of n[filters], but do not include the ones with -999 - sql = "UPDATE Object \ + sql = "UPDATE ObjectThin join MeanObject using (objID) \ SET nDetections = nDetections + n" + filter[1] + " \ WHERE n" + filter[1] + " != -999" @@ -171,51 +160,14 @@ for filter in filters: - sql = "UPDATE Object \ + sql = "UPDATE MeanObject \ SET " + filter[1] + "MeanPSFMagErr = null \ WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut) self.scratchDb.execute(sql) - self.logger.infoPair("Setting to NULL all x20pct =", "32.767" ) - for filter in filters: - sql = "UPDATE Object \ - SET " + filter[1] + "20pct = null \ - WHERE " + filter[1] + "20pct > 32.766" - self.scratchDb.execute(sql) - self.logger.infoPair("Setting to NULL all x80pct =", "32.767" ) - for filter in filters: - sql = "UPDATE Object \ - SET " + filter[1] + "80pct = null \ - WHERE " + filter[1] + "80pct > 32.766" - self.scratchDb.execute(sql) - - - - - ''' - Inserts colors - ''' - def updateColors(self): - - self.logger.infoPair("Calculating", "colors") - sql = "UPDATE Object \ - SET \ - grMeanColor = (gMeanMag - rMeanMag) \ - ,riMeanColor = (rMeanMag - iMeanMag) \ - ,izMeanColor = (iMeanMag - zMeanMag) \ - ,zyMeanColor = (zMeanMag - yMeanMag) \ - ,grMeanColorErr = SQRT(POW(gMeanMagErr,2) + POW(rMeanMagErr,2)) \ - ,riMeanColorErr = SQRT(POW(rMeanMagErr,2) + POW(iMeanMagErr,2)) \ - ,izMeanColorErr = SQRT(POW(iMeanMagErr,2) + POW(zMeanMagErr,2)) \ - ,zyMeanColorErr = SQRT(POW(zMeanMagErr,2) + POW(yMeanMagErr,2)) \ - " - try: - self.scratchDb.execute(sql) - except: - self.logger.errorPair("Couldn't calculate colors", sql) ''' Populates the Object table ''' - def populateObjectTable(self): + def populateObjectThinTable(self): cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") @@ -242,31 +194,36 @@ - self.logger.infoPair("Populating", "Object s") + 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 Object (\ + sql = "INSERT IGNORE INTO ObjectThin (\ objID \ + ,gcobjID \ ,ippObjID \ + ,surveyID \ + ,skyCellID \ + ,randomID \ + ,batchID \ + ,dataRelease \ ,objInfoFlag \ ,qualityFlag \ - ,surveyID \ - ,ra \ - ,`dec` \ - ,raErr \ - ,decErr \ + ,raMean \ + ,decMean \ + ,raMeanErr \ + ,decMeanErr \ ,nDetections \ - ,qfPerfect \ - ,sgSep \ - ,dataRelease \ - ,Random \ - ,batchID \ - ) \ + ) \ SELECT \ EXT_ID \ + , -999 \ ,CAT_ID*1000000000 + OBJ_ID \ + ," + str(self.surveyID) + " \ + ,-999 \ + , RAND() \ + , " + str(self.batchID) + "\ + , " + str(self.skychunk.dataRelease) + "\ ,FLAGS \ ,FLAGS >> 24 & 0xFF \ - ," + str(self.surveyID) + " \ ,RA \ ,DEC_ \ @@ -274,10 +231,5 @@ ,DEC_ERR \ ,0 \ - ,PSF_QF_PERF \ - ,STARGAL_SEP \ - , " + str(self.skychunk.dataRelease) + "\ - , RAND() \ - , " + str(self.batchID) + "\ - FROM " + cptTableName + FROM " + cptTableName try: @@ -289,8 +241,8 @@ # add row count columns so we can perform joins to get colors self.logger.infoPair("Adding 'row' columns to", "Object and cps tables") - self.scratchDb.addRowCountColumn("Object", "row") + self.scratchDb.addRowCountColumn("ObjectThin", "row") self.scratchDb.addRowCountColumn(cpsTableName, "row") - self.insertMags(cpsTableName) + # self.insertMeanMags(cpsTableName) #don't do this as we removed those columns.. @@ -312,5 +264,9 @@ #count out of range - sql = "SELECT count(*) FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) + sql = "SELECT count(*) FROM ObjectThin where \ + ObjectThin.decMean > " + str(decMax) + " \ + or ObjectThin.decMean < " + str(decMin) + " \ + or ObjectThin.raMean > " + str(raMax) + " \ + or ObjectThin.raMean < " + str(raMin) rs = self.scratchDb.executeQuery(sql) @@ -321,5 +277,9 @@ - sql = "DELETE FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) + sql = "DELETE FROM ObjectThin where \ + ObjectThin.decMean > " + str(decMax) + " or \ + ObjectThin.decMean < " + str(decMin) + " or \ + ObjectThin.raMean > " + str(raMax) + " or \ + ObjectThin.raMean < " + str(raMin) self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range") @@ -327,14 +287,14 @@ self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't cull outsiders from Object table", sql) + self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql) return False - self.logger.infoPair("Dropping row column from", "Object table") - self.scratchDb.dropColumn("Object", "row") + self.logger.infoPair("Dropping row column from", "ObjectThin table") + self.scratchDb.dropColumn("ObjectThin", "row") self.logger.infoPair("Purging from scratch Db", self.region + " region") self.dvoObjects.purgeRegion(self.region) - self.setMinMaxObjID(["Object"]) + self.setMinMaxObjID(["ObjectThin"]) return True @@ -343,24 +303,23 @@ Populates the ObjectCalColor table ''' - def populateObjectCalColorTable(self): - - self.logger.infoPair("Populating", "ObjectCalColor") - - sql = "INSERT INTO ObjectCalColor ( \ + def populateMeanObjectTable(self): + + self.logger.infoPair("Populating", "MeanObject") + + sql = "INSERT INTO MeanObject ( \ objID \ - ,ippObjID \ - ,dataRelease \ + ,gcobjID \ ) \ SELECT \ objID \ - ,ippObjID \ - ,dataRelease \ - FROM Object" + ,gcobjID \ + FROM ObjectThin" try: self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't populate ObjectCalColor table", sql) + self.logger.errorPair("Couldn't populate MeanObject table", sql) return False + self.insertMeanMags("MeanObject") return True @@ -370,10 +329,11 @@ def populatePspsTables(self): - if not self.populateObjectTable(): return False + if not self.populateObjectThinTable(): return False #if not self.populateObjectCalColorTable(): return False + if not self.populateMeanObjectTable(): return False # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number - self.logger.infoPair("Forcing uniqueness on", "objID in Object table") - rowCountBefore = self.scratchDb.getRowCount("Object") + self.logger.infoPair("Forcing uniqueness on", "objID in ObjectThin table") + rowCountBefore = self.scratchDb.getRowCount("ObjectThin") # XXX EAM : note that in mysql versions later than 5.1, this fails @@ -386,11 +346,34 @@ self.scratchDb.execute("set session old_alter_table=1") - self.scratchDb.execute("ALTER IGNORE TABLE Object ADD UNIQUE INDEX(objID)") + self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)") if self.scratchDb.version > 5.1: self.scratchDb.execute("set session old_alter_table=0") + rowCountAfter = self.scratchDb.getRowCount("ObjectThin") + self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) + + self.logger.infoPair("Forcing uniqueness on", "objID in MeanObject table") + rowCountBefore = self.scratchDb.getRowCount("MeanObject") + + # XXX EAM : note that in mysql versions later than 5.1, this fails + # unless the following is called first: + # set session old_alter_table=1 + # follow the command with + # set session old_alter_table=0 + # OF COURSE, this fails for mysql version < 5.5... + if self.scratchDb.version > 5.1: + self.scratchDb.execute("set session old_alter_table=1") + + self.scratchDb.execute("ALTER IGNORE TABLE MeanObject ADD UNIQUE INDEX(objID)") + if self.scratchDb.version > 5.1: + self.scratchDb.execute("set session old_alter_table=0") + + rowCountAfter = self.scratchDb.getRowCount("MeanObject") + self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) + + + #this is abuse of something but this is how I get the object batches to crash to further investigate them + rowCountAfter = self.scratchDb.getRowCount("Object") - self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) - - return True - + #return True + return False