Index: /trunk/ippToPsps/jython/diffobjectbatch.py =================================================================== --- /trunk/ippToPsps/jython/diffobjectbatch.py (revision 37754) +++ /trunk/ippToPsps/jython/diffobjectbatch.py (revision 37755) @@ -49,5 +49,5 @@ dvoID, batchID, - "OB", + "DO", None) @@ -134,6 +134,6 @@ # " + 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]) + ")) \ + sql = "UPDATE DiffObject JOIN \ + " + cpsTable + " AS cps ON (cps.row = (DiffObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ SET \ MeanObject." + filter[1] + "QfPerfect = PSF_QF_PERF_MAX \ @@ -174,5 +174,5 @@ Inserts stuff for all mags ''' - def updateObjectThinFromCps(self, cpsTable): + def updateDiffObjectFromCps(self, cpsTable): # list of all filters PSPS is interested in @@ -196,7 +196,7 @@ # sqlLine = sqlUtility() - sql = "UPDATE ObjectThin JOIN " + sql = "UPDATE DiffObject JOIN " sql += cpsTable + " AS cps " - sql += "ON (cps.row = (ObjectThin.row * " + sql += "ON (cps.row = (DiffObject.row * " sql += str(filterCount) + ")-(" sql += str(filterCount) + " - " @@ -204,6 +204,6 @@ sql += ")) " - sql += "SET ObjectThin.n" + filter[1] + " = NCODE, " - sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET" + sql += "SET DiffObject.n" + filter[1] + " = NCODE, " + sql += "DiffObject.nDetections = DiffObject.nDetections + cps.NSTACK_DET" self.logger.info(sql) self.scratchDb.execute(sql) @@ -213,5 +213,5 @@ for filter in filters: # now do a sum of n[filters], but do not include the ones with -999 - sql = "UPDATE ObjectThin " + sql = "UPDATE DiffObject " sql += "SET nDetections = nDetections + n" + filter[1] sql += " WHERE n" + filter[1] + " != -999" @@ -222,44 +222,10 @@ XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps? ''' - def updateObjName(self): - ##this is a 4 part update: - self.logger.infoPair("updating objName", "using stacks") - ## if we have a stackra and dec, use stackra and dec , dec >=0 - - sql = "update ObjectThin set objName = concat('PS1.2 J', \ - lpad(floor(raStack/15.),2,'0'), \ - lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \ - lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \ - '+', \ - lpad(floor(decStack),2,'0'), \ - lpad((decStack-(floor(decStack)))*60. ,2,'0'), \ - lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \ - ) where raStack > -999 and decStack > -999 and decStack >= 0" - try: - self.scratchDb.execute(sql) - except: - self.logger.errorPair("failed sql", sql) - return False - - - ## if we have a stackra and dec, use stackra and dec, dec < 0 - - sql = "update ObjectThin set objName = concat('PS1.2 J', \ - lpad(floor(raStack/15.),2,'0'), \ - lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \ - lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \ - '-', \ - lpad(floor(decStack),2,'0'), \ - lpad((decStack-(floor(decStack)))*60. ,2,'0'), \ - lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \ - ) where raStack > -999 and decStack > -999 and decStack <= 0" - try: - self.scratchDb.execute(sql) - except: - self.logger.errorPair("failed sql", sql) - return False - self.logger.infoPair("updating objName", "using means") + def updateDiffObjName(self): + ##this is a 2 part update: + + self.logger.infoPair("updating diffObjName", "using means") ## use mean ra and dec (dec >= 0) - sql = "update ObjectThin set objName = concat('PS1.2 J', \ + sql = "update DiffObject set diffObjName = concat('PS1.2 J', \ lpad(floor(raMean/15.),2,'0'), \ lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ @@ -278,5 +244,5 @@ ## use mean ra and dec (dec < 0) - sql = "update ObjectThin set objName = concat('PS1.2 J', \ + sql = "update DiffObject set diffObjName = concat('PS1.2 J', \ lpad(floor(raMean/15.),2,'0'), \ lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ @@ -295,7 +261,7 @@ ''' - Populates the Object table - ''' - def populateObjectThinTable(self): + Populates the DiffObject table + ''' + def populateDiffObjectTable(self): cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") @@ -324,5 +290,5 @@ - self.logger.info("Populating ThinObject") + self.logger.info("Populating DiffObject") self.logger.info("Inserting objects from cpt file") @@ -331,7 +297,7 @@ # INSERT IGNORE INTO ObjectThin - sqlLine = sqlUtility("INSERT INTO ObjectThin (") - - sqlLine.group("objID", "EXT_ID") + sqlLine = sqlUtility("INSERT INTO DiffObject (") + + sqlLine.group("diffObjID", "EXT_ID") sqlLine.group("ippObjID", "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") @@ -339,6 +305,4 @@ 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) + "'") @@ -346,15 +310,8 @@ 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("ra", "RA_MEAN") + sqlLine.group("dec", "DEC_MEAN") + sqlLine.group("raErr", "RA_ERR") + sqlLine.group("decErr", "DEC_ERR") sqlLine.group("nDetections", "'0'") sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName) @@ -363,20 +320,20 @@ self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't populate Object table", sql) + self.logger.errorPair("Couldn't populate DiffObject table", sql) return False # 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("ObjectThin", "row") + self.logger.infoPair("Adding 'row' columns to", "DiffObject and cps tables") + self.scratchDb.addRowCountColumn("DiffObject", "row") self.scratchDb.addRowCountColumn(cpsTableName, "row") ## self.insertMeanMags(cpsTableName) ## this is the new version - self.logger.infoPair("update objName for ","objectThin") - self.updateObjName() - - self.logger.infoPair("update ObjectThin from ","cps table") - - self.updateObjectThinFromCps(cpsTableName) + self.logger.infoPair("update diffObjName for ","DiffObject") + self.updateDiffObjName() + + self.logger.infoPair("update DiffObject from ","cps table") + + self.updateDiffObjectFromCps(cpsTableName) # XXX EAM 20140724 : is this necessary?? @@ -396,9 +353,9 @@ #count out of range - 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) + 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)" rs = self.scratchDb.executeQuery(sql) @@ -409,9 +366,9 @@ - sql = "DELETE FROM ObjectThin where \ - ObjectThin.decMean > (" + str(decMax) + " + .0033) or \ - ObjectThin.decMean < (" + str(decMin) + " - .0033) or \ - ObjectThin.raMean > (" + str(raMax) + " + .0033) or \ - ObjectThin.raMean < (" + str(raMin) + " - .0033)" + 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)" self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range") @@ -419,9 +376,9 @@ self.scratchDb.execute(sql) except: - self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql) + self.logger.errorPair("Couldn't cull outsiders from DiffObject table", sql) return False - self.logger.infoPair("Dropping row column from", "ObjectThin table") - self.scratchDb.dropColumn("ObjectThin", "row") + self.logger.infoPair("Dropping row column from", "DiffObject table") + self.scratchDb.dropColumn("DiffObject", "row") ##self.logger.infoPair("Purging from scratch Db", self.region + " region") @@ -429,48 +386,18 @@ ##self.dvoObjects.purgeRegion(self.region) - self.setMinMaxObjID(["ObjectThin"]) + self.setMinMaxDiffObjID(["DiffObject"]) return True ''' - Populates the ObjectCalColor table - ''' - def populateMeanObjectTable(self): - cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") - cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps") - self.logger.infoPair("Populating MeanObject from ", "ObjectThin") - - sql = "INSERT INTO MeanObject ( objID ) SELECT objID FROM ObjectThin" - try: - self.scratchDb.execute(sql) - except: - self.logger.errorPair("Couldn't populate MeanObject table", sql) - return False - - self.logger.infoPair("Adding 'row' columns to", "MeanObject table") - self.scratchDb.addRowCountColumn("MeanObject", "row") - - - ##self.scratchDb.addRowCountColumn(cpsTableName, "row") - self.logger.infoPair("update MeanObjects from ","cps table") - self.updateMeanObjectFromCps(cpsTableName) - - self.logger.infoPair("Dropping row column from", "MeanObject table") - self.scratchDb.dropColumn("MeanObject", "row") - - return True - - ''' Does the processing, i.e. pulling stuff from IPP tables into PSPS tables ''' def populatePspsTables(self): - if not self.populateObjectThinTable(): return False - #if not self.populateObjectCalColorTable(): return False - if not self.populateMeanObjectTable(): return False + if not self.populateDiffObjectTable(): 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 ObjectThin table") - rowCountBefore = self.scratchDb.getRowCount("ObjectThin") + self.logger.infoPair("Forcing uniqueness on", "objID in DIffObject table") + rowCountBefore = self.scratchDb.getRowCount("DiffObject") # XXX EAM : note that in mysql versions later than 5.1, this fails @@ -483,29 +410,10 @@ self.scratchDb.execute("set session old_alter_table=1") - self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)") + self.scratchDb.execute("ALTER IGNORE TABLE DiffObject ADD UNIQUE INDEX(diffObjID)") 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)) + rowCountAfter = self.scratchDb.getRowCount("DiffObject") + self.logger.infoPair("Number of duplicated diffobjIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) self.dvoObjects.purgeRegion(self.region) @@ -513,5 +421,5 @@ #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