Index: trunk/ippToPsps/jython/stackbatch.py =================================================================== --- trunk/ippToPsps/jython/stackbatch.py (revision 36447) +++ trunk/ippToPsps/jython/stackbatch.py (revision 36706) @@ -98,13 +98,9 @@ # delete PSPS tables self.scratchDb.dropTable("StackMeta") - self.scratchDb.dropTable("StackDetection") + self.scratchDb.dropTable("StackObject") self.scratchDb.dropTable("StackModelFit") self.scratchDb.dropTable("StackApFlx") self.scratchDb.dropTable("StackToImage") - self.scratchDb.dropTable("SkinnyObject") - #self.scratchDb.dropTable("ObjectCalColor") - self.scratchDb.dropTable("StackDetectionCalib") - - # self.testprint() + # self.testprint() # delete IPP tables @@ -447,12 +443,12 @@ ''' - Populates the StackDetection table - ''' - def populateStackDetection(self): - - self.logger.infoPair("Procesing table", "StackDetection") + Populates the StackObject table + ''' + def populateStackObject(self): + + self.logger.infoPair("Procesing table", "StackObject") # insert all the detections - sql = "INSERT INTO StackDetection (\ + sql = "INSERT INTO StackObject(\ ippDetectID \ ,skyCellID \ @@ -550,7 +546,7 @@ #it is possible to drop some detections from dvo (that are present in the cmf). when that happens we get a 0 for objid #we drop those... - sql="DELETE FROM StackDetection where objID = 0" - self.scratchDb.execute(sql) - self.logger.infoPair("Deleting", "entries with StackDetection.objID = 0") + sql="DELETE FROM StackObject where objID = 0" + self.scratchDb.execute(sql) + self.logger.infoPair("Deleting", "entries with StackObject.objID = 0") # XXX EAM : this seems quite inefficient : these commands use updates to set @@ -558,19 +554,19 @@ # the original insert above? - self.scratchDb.updateAllRows("StackDetection", "surveyID", str(self.surveyID)) - - self.scratchDb.updateFilterID("StackDetection", self.filter) - - self.scratchDb.updateAllRows("StackDetection", "dataRelease", str(self.skychunk.dataRelease)) + self.scratchDb.updateAllRows("StackObject", "surveyID", str(self.surveyID)) + + self.scratchDb.updateFilterID("StackObject", self.filter) + + self.scratchDb.updateAllRows("StackObject", "dataRelease", str(self.skychunk.dataRelease)) #set primary F to 0 - self.scratchDb.updateAllRows("StackDetection", "primaryF", "0") - - self.scratchDb.updateAllRows("StackDetection", "activeFlag", "0") - - self.updateStackMetaID("StackDetection") - - self.updateStackTypeID("StackDetection") - - self.updateDvoIDsAndFlags("StackDetection") + self.scratchDb.updateAllRows("StackObject", "primaryF", "0") + + self.scratchDb.updateAllRows("StackObject", "activeFlag", "0") + + self.updateStackMetaID("StackObject") + + self.updateStackTypeID("StackObject") + + self.updateDvoIDsAndFlags("StackObject") #this now updates primary F as well # response = raw_input("updated dvo ") @@ -585,5 +581,5 @@ self.scratchDb.execute("set session old_alter_table=1") - sql = "ALTER IGNORE TABLE StackDetection ADD PRIMARY KEY (objID)" + sql = "ALTER IGNORE TABLE StackObject ADD PRIMARY KEY (objID)" if self.scratchDb.version > 5.1: @@ -597,5 +593,5 @@ #if deep stack and instFlux = null and err not null - sql = "UPDATE StackDetection AS a, SkyChip_psf AS b \ + sql = "UPDATE StackObject AS a, SkyChip_psf AS b \ SET psfFlux = 2*b.PSF_INST_FLUX_SIG / " + str(self.expTime) + " \ WHERE psfFlux IS NULL \ @@ -610,23 +606,23 @@ # something like (f < 0.0) ? -999 : -2.5*log10(f) # as a result, the negative fluxes here result in floating point errors - sql = "UPDATE StackDetection SET psfFlux = 1e20 WHERE psfFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackDetection SET apFlux = 1e20 WHERE apFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackDetection SET kronFlux = 1e20 WHERE kronFlux <= 0.0" + sql = "UPDATE StackObject SET psfFlux = 1e20 WHERE psfFlux <= 0.0" + self.scratchDb.execute(sql) + + sql = "UPDATE StackObject SET apFlux = 1e20 WHERE apFlux <= 0.0" + self.scratchDb.execute(sql) + + sql = "UPDATE StackObject SET kronFlux = 1e20 WHERE kronFlux <= 0.0" self.scratchDb.execute(sql) #leave null instflux in - #self.scratchDb.reportAndDeleteRowsWithNULLS("StackDetection", "instFlux") - - self.scratchDb.reportAndDeleteRowsWithNULLS("StackDetection", "objID") + #self.scratchDb.reportAndDeleteRowsWithNULLS("StackObject", "instFlux") + + self.scratchDb.reportAndDeleteRowsWithNULLS("StackObject", "objID") # response = raw_input("delete nulls ") - sql="DELETE FROM StackDetection where objID = 0" - - self.scratchDb.execute(sql) - self.logger.infoPair("Deleting", "entries with StackDetection.objID = 0") + sql="DELETE FROM StackObject where objID = 0" + + self.scratchDb.execute(sql) + self.logger.infoPair("Deleting", "entries with StackObject.objID = 0") # response = raw_input("deleted objID is 0 ") @@ -709,5 +705,5 @@ self.updateDvoIDs("StackApFlx") self.scratchDb.reportAndDeleteRowsWithNULLS("StackApFlx", "objID") - self.deleteDetectionsNotInStackDetection("StackApFlx") + self.deleteDetectionsNotInStackObject("StackApFlx") @@ -759,12 +755,12 @@ self.updateDvoIDs("StackModelFit") self.scratchDb.reportAndDeleteRowsWithNULLS("StackModelFit", "objID") - self.deleteDetectionsNotInStackDetection("StackModelFit") - - ''' - Reports and deletes detections in this table that are not in StackDetection - ''' - def deleteDetectionsNotInStackDetection(self, table): - - sql = "SELECT COUNT(*) FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackDetection)" + self.deleteDetectionsNotInStackObject("StackModelFit") + + ''' + Reports and deletes detections in this table that are not in StackObject + ''' + def deleteDetectionsNotInStackObject(self, table): + + sql = "SELECT COUNT(*) FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackObject)" rs = self.scratchDb.executeQuery(sql) rs.first() @@ -773,7 +769,7 @@ if nMissing < 1: return - sql = "DELETE FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackDetection)" - self.scratchDb.execute(sql) - self.logger.infoPair("Detections not in StackDetection", "%d deleted from %s" % (nMissing, table)) + sql = "DELETE FROM " + table + " WHERE ippDetectID NOT IN (SELECT ippDetectID FROM StackObject)" + self.scratchDb.execute(sql) + self.logger.infoPair("Detections not in StackObject", "%d deleted from %s" % (nMissing, table)) @@ -797,52 +793,13 @@ self.scratchDb.execute(sql) - ''' - Populates the SkinnyObject table - ''' - def populateSkinnyObject(self): - - self.logger.infoPair("Procesing table", "SkinnyObject") - - sql = "INSERT INTO SkinnyObject (\ - objID \ - ,ippObjID \ - ,projectionCellID \ - ) \ - SELECT \ - DISTINCT objID \ - ,ippObjID \ - ,projectionCellID \ - FROM StackDetection" - self.scratchDb.execute(sql) - - self.scratchDb.updateAllRows("SkinnyObject", "surveyID", str(self.surveyID)) - self.scratchDb.updateAllRows("SkinnyObject", "dataRelease", str(self.skychunk.dataRelease)) ''' - Populates the StackDetectionCalib table + Populates the StackObjectCalib table XXX this can probably get a big speed increase by using 'SELECT () INTO OUTFILE '/tmp/name' and then calling the load data infile '/tmp/name' into table; ''' - def populateStackDetectionCalibInsertUpdate(self): - self.logger.infoPair("Processing table", "StackDetectionCalib") - tableName = "StackDetectionCalib" - # insert all detections into table - sql = "INSERT INTO " + tableName + " ( \ - objID \ - ,stackDetectID \ - ,ippObjID \ - ,ippDetectID \ - ,filterID \ - ,surveyID \ - ) \ - SELECT \ - objID \ - ,stackDetectID \ - ,ippObjID \ - ,ippDetectID \ - ,filterID \ - ,surveyID \ - FROM StackDetection" - self.scratchDb.execute(sql) + def populateStackObjectCalibInsertUpdate(self): + self.logger.infoPair("Processing table", "StackObject") + tableName = "StackObject" imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) @@ -856,5 +813,4 @@ a.decErr = b.decErr, \ a.zp = b.zp, \ - a.zpErr = b.zpErr, \ a.expTime = b.expTime, \ a.airMass = b.airMass \ @@ -864,63 +820,23 @@ self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - def populateStackDetectionCalib(self): - - tableName = "StackDetectionCalib" + def populateStackObjectCalib(self): + + tableName = "StackObject" self.logger.infoPair("Processing table", tableName) imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) self.logger.infoPair("obtained","imageID") - - # check for & create output directory first - datadumpDir = "/tmp/datadump" - try: - statinfo = os.stat(datadumpDir) - # check on the stat results? - except: - print "making the data dump directory ", datadumpDir - os.mkdir(datadumpDir) - os.chmod(datadumpDir, 0777) - statinfo = os.stat(datadumpDir) - - dumpFile = datadumpDir + "/genetest.xx.dat" - files = glob.glob(dumpFile) - if len(files) > 0: - os.unlink(dumpFile) - - # insert all detections into table - sql = "SELECT \ - a.objID, \ - a.stackDetectID, \ - a.ippObjID, \ - a.ippDetectID, \ - a.filterID, \ - a.surveyID, \ - b.ra, \ - b.dec_, \ - b.raErr, \ - b.decErr, \ - b.zp, \ - b.zpErr, \ - b.expTime, \ - b.airMass, \ - " + str(self.skychunk.dataRelease) + " \ - FROM \ - StackDetection as a \ - JOIN " + self.scratchDb.dvoDetectionTable + " as b \ - ON (a.stackDetectID = b.detectID) where b.imageID = " + str(imageID) + \ - " INTO OUTFILE '" + dumpFile + "'" - - try: self.scratchDb.execute(sql) - except: - self.logger.info("failed to select data for stackDetectionCalib") - self.logger.infoPair("sql: ", sql) - raise - - sql = "LOAD DATA INFILE '" + dumpFile + "' INTO TABLE " + tableName - try: self.scratchDb.execute(sql) - except: - self.logger.info("failed to load data from infile for stackDetectionCalib") - self.logger.infoPair("sql: ", sql) - raise + sql = "UPDATE " + tableName + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b \ + SET a.ra = b.ra, \ + a.dec = b.dec_, \ + a.raErr = b.raErr, \ + a.decErr = b.decErr, \ + a.zp = b.zp, \ + a.expTime = b.expTime, \ + a.airMass = b.airMass \ + WHERE a.stackDetectID = b.detectID \ + AND b.imageID = " + str(imageID) + self.scratchDb.execute(sql) + self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) ## XXX write this with the select/insert @@ -928,25 +844,4 @@ ''' - Populates the ObjectCalColor table - ''' - def populateObjectCalColor(self): - - self.logger.infoPair("Procesing table", "ObjectCalColor table") - - sql = "INSERT INTO ObjectCalColor (\ - objID \ - ,ippObjID \ - ) \ - SELECT \ - DISTINCT objID \ - ,ippObjID \ - FROM StackDetection" - self.scratchDb.execute(sql) - - self.scratchDb.updateFilterID("ObjectCalColor", self.filter) - self.scratchDb.updateAllRows("ObjectCalColor", "dataRelease", str(self.skychunk.dataRelease)) - - - ''' Applies indexes to the PSPS tables ''' @@ -955,7 +850,7 @@ self.logger.debug("Altering PSPS tables") # heather uncommented the unique line -- well that no worky - #self.scratchDb.makeColumnUnique("StackDetection", "objID") - self.scratchDb.createIndex("StackDetection", "ippDetectID") - self.scratchDb.createIndex("StackDetection", "stackDetectID") + #self.scratchDb.makeColumnUnique("StackObject", "objID") + self.scratchDb.createIndex("StackObject", "ippDetectID") + self.scratchDb.createIndex("StackObject", "stackDetectID") self.scratchDb.createIndex("StackApFlx", "ippDetectID") self.scratchDb.createIndex("StackModelFit", "ippDetectID") @@ -963,5 +858,5 @@ # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of # the column in PSPS - self.scratchDb.execute("ALTER TABLE StackDetectionCalib CHANGE dec_ `dec` double") + self.scratchDb.execute("ALTER TABLE StackObject CHANGE dec_ `dec` double") return True @@ -1030,6 +925,6 @@ #self.logger.infoPair("populating","StackMeta") self.populateStackMeta() - #self.logger.infoPair("populating","StackDetection") - self.populateStackDetection() + #self.logger.infoPair("populating","StackObject") + self.populateStackObject() if self.stackType != "NIGHTLY_STACK": @@ -1040,12 +935,8 @@ #self.logger.infoPair("populating","StackToImage") self.populateStackToImage() - #self.logger.infoPair("populating","SkinnyObject") - self.populateSkinnyObject() - #self.logger.infoPair("populating","ObjectCalColor") - #self.populateObjectCalColor() - #self.logger.infoPair("populating","StackDetectionCalib") - self.populateStackDetectionCalib() - - self.setMinMaxObjID(["StackDetection"]) + #self.logger.infoPair("populating","StackObjectCalib") + self.populateStackObjectCalib() + + self.setMinMaxObjID(["StackObject"]) if self.totalDetections < 1: