Index: trunk/ippToPsps/jython/diffobjectbatch.py =================================================================== --- trunk/ippToPsps/jython/diffobjectbatch.py (revision 37755) +++ trunk/ippToPsps/jython/diffobjectbatch.py (revision 37763) @@ -134,40 +134,13 @@ # " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ - sql = "UPDATE DiffObject JOIN \ - " + cpsTable + " AS cps ON (cps.row = (DiffObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ + sql = "UPDATE DiffDetObject JOIN \ + " + cpsTable + " AS cps ON (cps.row = (DiffDetObject.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) " - + DiffDetObject." + filter[1] + "QfPerfect = PSF_QF_PERF_MAX " try: self.scratchDb.execute(sql) except: - self.logger.errorPair("failed update MeanObject", sql) + self.logger.errorPair("failed update DiffDetObject", 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) ''' @@ -196,7 +169,7 @@ # sqlLine = sqlUtility() - sql = "UPDATE DiffObject JOIN " + sql = "UPDATE DiffDetObject JOIN " sql += cpsTable + " AS cps " - sql += "ON (cps.row = (DiffObject.row * " + sql += "ON (cps.row = (DiffDetObject.row * " sql += str(filterCount) + ")-(" sql += str(filterCount) + " - " @@ -204,6 +177,6 @@ sql += ")) " - sql += "SET DiffObject.n" + filter[1] + " = NCODE, " - sql += "DiffObject.nDetections = DiffObject.nDetections + cps.NSTACK_DET" + sql += "SET DiffDetObject.n" + filter[1] + " = NCODE, " + sql += "DiffDetObject.nDetections = DiffDetObject.nDetections + cps.NSTACK_DET" self.logger.info(sql) self.scratchDb.execute(sql) @@ -213,5 +186,5 @@ for filter in filters: # now do a sum of n[filters], but do not include the ones with -999 - sql = "UPDATE DiffObject " + sql = "UPDATE DiffDetObject " sql += "SET nDetections = nDetections + n" + filter[1] sql += " WHERE n" + filter[1] + " != -999" @@ -227,13 +200,13 @@ self.logger.infoPair("updating diffObjName", "using means") ## use mean ra and dec (dec >= 0) - sql = "update DiffObject set diffObjName = concat('PS1.2 J', \ - lpad(floor(raMean/15.),2,'0'), \ - lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ - lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \ + sql = "update DiffDetObject set diffObjName = concat('PS1.2 J', \ + lpad(floor(ra/15.),2,'0'), \ + lpad((ra/15.-(floor(ra/15.)))*60. ,2,'0'), \ + lpad(format(((ra/15.-(floor(ra/15.)))*60.-floor((ra/15.-(floor(ra/15.)))*60.))*60.,2),4,'0'), \ '+', \ - lpad(floor(decMean),2,'0'), \ - lpad((decMean-(floor(decMean)))*60. ,2,'0'), \ - lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \ - ) where raMean > -999 and decMean > -999 and decMean >= 0" + lpad(floor(dec_),2,'0'), \ + lpad((dec_-(floor(dec_)))*60. ,2,'0'), \ + lpad(format(((dec_-(floor(dec_)))*60.-floor((dec_-(floor(dec_)))*60.))*60.,2),4,'0') \ + ) where ra > -999 and dec_ > -999 and dec_ >= 0" try: self.scratchDb.execute(sql) @@ -244,13 +217,13 @@ ## use mean ra and dec (dec < 0) - sql = "update DiffObject set diffObjName = concat('PS1.2 J', \ - lpad(floor(raMean/15.),2,'0'), \ - lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ - lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \ + sql = "update DiffDetObject set diffObjName = concat('PS1.2 J', \ + lpad(floor(ra/15.),2,'0'), \ + lpad((ra/15.-(floor(ra/15.)))*60. ,2,'0'), \ + lpad(format(((ra/15.-(floor(ra/15.)))*60.-floor((ra/15.-(floor(ra/15.)))*60.))*60.,2),4,'0'), \ '-', \ - lpad(floor(decMean),2,'0'), \ - lpad((decMean-(floor(decMean)))*60. ,2,'0'), \ - lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \ - ) where raMean > -999 and decMean > -999 and decMean < 0" + lpad(floor(dec_),2,'0'), \ + lpad((dec_-(floor(dec_)))*60. ,2,'0'), \ + lpad(format(((dec_-(floor(dec_)))*60.-floor((dec_-(floor(dec_)))*60.))*60.,2),4,'0') \ + ) where ra > -999 and dec_ > -999 and dec_ < 0" try: @@ -290,5 +263,5 @@ - self.logger.info("Populating DiffObject") + self.logger.info("Populating DiffDetObject") self.logger.info("Inserting objects from cpt file") @@ -297,5 +270,5 @@ # INSERT IGNORE INTO ObjectThin - sqlLine = sqlUtility("INSERT INTO DiffObject (") + sqlLine = sqlUtility("INSERT INTO DiffDetObject (") sqlLine.group("diffObjID", "EXT_ID") @@ -311,5 +284,5 @@ sqlLine.group("consistencyFlag", "'0'") sqlLine.group("ra", "RA_MEAN") - sqlLine.group("dec", "DEC_MEAN") + sqlLine.group("dec_", "DEC_MEAN") sqlLine.group("raErr", "RA_ERR") sqlLine.group("decErr", "DEC_ERR") @@ -320,18 +293,18 @@ self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't populate DiffObject table", sql) + self.logger.errorPair("Couldn't populate DiffDetObject table", sql) return False # add row count columns so we can perform joins to get colors - self.logger.infoPair("Adding 'row' columns to", "DiffObject and cps tables") - self.scratchDb.addRowCountColumn("DiffObject", "row") + self.logger.infoPair("Adding 'row' columns to", "DiffDetObject and cps tables") + self.scratchDb.addRowCountColumn("DiffDetObject", "row") self.scratchDb.addRowCountColumn(cpsTableName, "row") ## self.insertMeanMags(cpsTableName) ## this is the new version - self.logger.infoPair("update diffObjName for ","DiffObject") + self.logger.infoPair("update diffObjName for ","DiffDetObject") self.updateDiffObjName() - self.logger.infoPair("update DiffObject from ","cps table") + self.logger.infoPair("update DiffDetObject from ","cps table") self.updateDiffObjectFromCps(cpsTableName) @@ -353,9 +326,9 @@ #count out of range - sql = "SELECT count(*) FROM DiffObject where \ - DiffObject.dec > (" + str(decMax) + " + .0033) \ - or DiffObject.dec < (" + str(decMin) + " - .0033) \ - or DiffObject.ra > (" + str(raMax) + " + .0033) \ - or DiffObject.ra < (" + str(raMin) + " - .0033)" + sql = "SELECT count(*) FROM DiffDetObject where \ + DiffDetObject.dec_ > (" + str(decMax) + " + .0033) \ + or DiffDetObject.dec_ < (" + str(decMin) + " - .0033) \ + or DiffDetObject.ra > (" + str(raMax) + " + .0033) \ + or DiffDetObject.ra < (" + str(raMin) + " - .0033)" rs = self.scratchDb.executeQuery(sql) @@ -366,9 +339,9 @@ - sql = "DELETE FROM DiffObject where \ - DiffObject.decMean > (" + str(decMax) + " + .0033) or \ - DiffObject.decMean < (" + str(decMin) + " - .0033) or \ - DiffObject.raMean > (" + str(raMax) + " + .0033) or \ - DiffObject.raMean < (" + str(raMin) + " - .0033)" + sql = "DELETE FROM DiffDetObject where \ + DiffDetObject.dec_ > (" + str(decMax) + " + .0033) or \ + DiffDetObject.dec_ < (" + str(decMin) + " - .0033) or \ + DiffDetObject.ra > (" + str(raMax) + " + .0033) or \ + DiffDetObject.ra < (" + str(raMin) + " - .0033)" self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range") @@ -376,9 +349,9 @@ self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't cull outsiders from DiffObject table", sql) + self.logger.errorPair("Couldn't cull outsiders from DiffDetObject table", sql) return False - self.logger.infoPair("Dropping row column from", "DiffObject table") - self.scratchDb.dropColumn("DiffObject", "row") + self.logger.infoPair("Dropping row column from", "DiffDetObject table") + self.scratchDb.dropColumn("DiffDetObject", "row") ##self.logger.infoPair("Purging from scratch Db", self.region + " region") @@ -386,7 +359,22 @@ ##self.dvoObjects.purgeRegion(self.region) - self.setMinMaxDiffObjID(["DiffObject"]) + self.setMinMaxDiffObjID(["DiffDetObject"]) return True + + ''' + Updates table and generates pspsuniqueids + ''' + + def updatePspsUniqueIDs(self,table): + sql = "UPDATE "+table+" join (select @r:=@r+1 rownum, diffobjID from \ + (select @r:=0) r, "+table+" t) as foo using (diffobjID) set \ + uniquePspsDOid = ((" +str(self.batchID)+ "*1000000000 ) + rownum)" + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + ''' @@ -399,5 +387,5 @@ # 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 DIffObject table") - rowCountBefore = self.scratchDb.getRowCount("DiffObject") + rowCountBefore = self.scratchDb.getRowCount("DiffDetObject") # XXX EAM : note that in mysql versions later than 5.1, this fails @@ -410,16 +398,21 @@ self.scratchDb.execute("set session old_alter_table=1") - self.scratchDb.execute("ALTER IGNORE TABLE DiffObject ADD UNIQUE INDEX(diffObjID)") + self.scratchDb.execute("ALTER IGNORE TABLE DiffDetObject ADD UNIQUE INDEX(diffObjID)") if self.scratchDb.version > 5.1: self.scratchDb.execute("set session old_alter_table=0") - rowCountAfter = self.scratchDb.getRowCount("DiffObject") + rowCountAfter = self.scratchDb.getRowCount("DiffDetObject") self.logger.infoPair("Number of duplicated diffobjIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) + self.scratchDb.execute("ALTER TABLE DiffDetObject CHANGE dec_ `dec` double") + + self.updatePspsUniqueIDs("DiffDetObject") + self.dvoObjects.purgeRegion(self.region) #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") + +# rowCountAfter = self.scratchDb.getRowCount("Object") return True # return False