Index: /trunk/ippToPsps/jython/stackbatch.py =================================================================== --- /trunk/ippToPsps/jython/stackbatch.py (revision 36727) +++ /trunk/ippToPsps/jython/stackbatch.py (revision 36728) @@ -280,5 +280,10 @@ def updateStackMetaID(self, table): - sql = "UPDATE " + table + " SET stackMetaID=" + str(self.id) + sql = "UPDATE " + table + " SET gstackMetaID=" + str(self.gstackID) + " \ + , rstackMetaID=" + str(self.rstackID) + " \ + , istackMetaID=" + str(self.istackID) + " \ + , zstackMetaID=" + str(self.zstackID) + " \ + , ystackMetaID=" + str(self.ystackID) + self.scratchDb.execute(sql) @@ -683,65 +688,42 @@ # insert all the detections - sql = "INSERT INTO StackObject(\ - "+f+"ippDetectID \ - ,"+f+"xPos \ - ,"+f+"yPos \ - ,"+f+"xPosErr \ - ,"+f+"yPosErr \ - ,"+f+"PSFFlux \ - ,"+f+"PSFFluxErr \ - ,"+f+"sky \ - ,"+f+"skyErr \ - ,"+f+"psfMajorFWHM \ - ,"+f+"psfMinorFWHM \ - ,"+f+"psfTheta \ - ,"+f+"psfLikelihood \ - ,"+f+"infoFlag \ - ,"+f+"psfQf \ - ,"+f+"psfQfPerfect \ - ,"+f+"psfChiSq \ - ,"+f+"momentXX \ - ,"+f+"momentXY \ - ,"+f+"momentYY \ - ,"+f+"momentR1 \ - ,"+f+"momentRH \ - ,"+f+"ApFlux \ - ,"+f+"ApFluxErr \ - ,"+f+"KronFlux \ - ,"+f+"KronFluxErr \ - ,"+f+"KronRad \ - ,"+f+"nFrames \ - ) \ - SELECT \ - IPP_IDET \ - ,X_PSF \ - ,Y_PSF \ - ,X_PSF_SIG \ - ,Y_PSF_SIG \ - ,PSF_INST_FLUX / " + str(exptime) + " \ - ,PSF_INST_FLUX_SIG / " + str(exptime) + " \ - ,SKY / " + str(exptime) + " \ - ,SKY_SIGMA / " + str(exptime) + " \ - ,PSF_MAJOR \ - ,PSF_MINOR \ - ,PSF_THETA \ - ,psfLikelihood(EXT_NSIGMA) \ - , FLAGS2 << 32 | FLAGS \ - ,PSF_QF \ - ,PSF_QF_PERFECT \ - ,PSF_CHISQ / PSF_NDOF \ - ,MOMENTS_XX \ - ,MOMENTS_XY \ - ,MOMENTS_YY \ - ,MOMENTS_R1 \ - ,MOMENTS_RH \ - ,POW(10.0, -0.4*AP_MAG) / " + str(exptime) + " \ - ,NULL \ - ,KRON_FLUX / " + str(exptime) + " \ - ,KRON_FLUX_ERR / " + str(exptime) + " \ - ,MOMENTS_R1 * 2.5 \ - ,N_FRAMES \ - FROM " + f + "SkyChip_psf" - + sql = "UPDATE StackObject as a , " + f + "SkyChip_psf as b set \ + a."+f+"xPos = b.X_PSF \ + ,a."+f+"yPos = b.Y_PSF \ + ,a."+f+"xPosErr = b.X_PSF_SIG \ + ,a."+f+"yPosErr = b.Y_PSF_SIG \ + ,a."+f+"PSFFlux = b.PSF_INST_FLUX / " + str(exptime) + " \ + ,a."+f+"PSFFluxErr = b.PSF_INST_FLUX_SIG / " + str(exptime) + " \ + ,a."+f+"sky = b.SKY / " + str(exptime) + " \ + ,a."+f+"skyErr = b.SKY_SIGMA / " + str(exptime) + " \ + ,a."+f+"psfMajorFWHM = b.PSF_MAJOR \ + ,a."+f+"psfMinorFWHM = b.PSF_MINOR \ + ,a."+f+"psfTheta = b.PSF_THETA \ + ,a."+f+"psfCore = NULL \ + ,a."+f+"psfLikelihood = psfLikelihood(b.EXT_NSIGMA) \ + ,a."+f+"infoFlag = b.FLAGS2 << 32 | FLAGS \ + ,a."+f+"psfQf = b.PSF_QF \ + ,a."+f+"psfQfPerfect = b.PSF_QF_PERFECT \ + ,a."+f+"psfChiSq = b.PSF_CHISQ / b.PSF_NDOF \ + ,a."+f+"momentXX = b.MOMENTS_XX \ + ,a."+f+"momentXY = b.MOMENTS_XY \ + ,a."+f+"momentYY = b.MOMENTS_YY \ + ,a."+f+"momentR1 = b.MOMENTS_R1 \ + ,a."+f+"momentRH = b.MOMENTS_RH \ + ,a."+f+"ApFlux = POW(10.0, -0.4*b.AP_MAG) / " + str(exptime) + " \ + ,a."+f+"ApFluxErr = NULL \ + ,a."+f+"ApFillFac = NULL \ + ,a."+f+"KronFlux = b.KRON_FLUX / " + str(exptime) + " \ + ,a."+f+"KronFluxErr = b.KRON_FLUX_ERR / " + str(exptime) + " \ + ,a."+f+"KronRad = b.MOMENTS_R1 * 2.5 \ + ,a."+f+"PSFMag = b.PSF_INST_MAG \ + ,a."+f+"PSFMagErr = b.PSF_INST_MAG_SIG \ + ,a."+f+"ApMag = b.AP_MAG \ + ,a."+f+"ApMagErr = NULL \ + ,a."+f+"KronMag = NULL \ + ,a."+f+"KronMagErr = NULL \ + ,a."+f+"nFrames = b. N_FRAMES \ + WHERE a." + f + "ippDetectID=b.IPP_IDET " + self.logger.infoPair('sql', sql) try: self.scratchDb.execute(sql) @@ -764,10 +746,13 @@ except: self.logger.errorPair('failed update surveyID on','StackObject') - + raise # self.scratchDb.updateFilterID("StackObject", f+".00000") self.scratchDb.updateAllRows("StackObject", "dataRelease", str(self.skychunk.dataRelease)) - ##self.updateStackMetaID("StackObject") - + try: + self.updateStackMetaID("StackObject") + except: + self.logger.errorPair("failed update stack meta id on ", "StackObject") + raise ##self.updateStackTypeID("StackObject") @@ -1014,43 +999,31 @@ self.logger.infoPair("Procesing table", "StackToImage") - - imageIDs = self.gpc1Db.getImageIDsForThisStackID(self.id) - - for imageID in imageIDs: - sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ + filters = ['g','r','i','z','y'] + for f in filters: + + if f == 'g': + stackmetaid = self.gstackID + if f == 'r': + stackmetaid = self.rstackID + if f == 'i': + stackmetaid = self.istackID + if f == 'z': + stackmetaid = self.zstackID + if f == 'y': + stackmetaid = self.ystackID + if stackmetaid > 0: + + imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaid) + + for imageID in imageIDs: + sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ VALUES (\ - " + str(self.id) + ", " + imageID + ")" - self.scratchDb.execute(sql) + " + str(stackmetaid) + ", " + imageID + ")" + self.scratchDb.execute(sql) # now update StackMeta with correct number of inputs - sql = "UPDATE StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage)" - self.scratchDb.execute(sql) - - - ''' - 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 populateStackObjectCalibInsertUpdate(self): - self.logger.infoPair("Processing table", "StackObject") - tableName = "StackObject" - - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - - # insert calibration information from dvoDetections into the Table - 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)) + sql = "UPDATE "+f+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaid)+")" + self.scratchDb.execute(sql) + def populateStackObjectFromDvo(self): @@ -1094,4 +1067,5 @@ a." + f + "zp = b.zp, \ a." + f + "expTime = b.expTime, \ + a." + f + "ippDetectID = b.ippDetectID, \ a." + f + "stackDetectID = b.detectID \ WHERE a.objID = b.objID \ @@ -1104,7 +1078,4 @@ self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - ## XXX write this with the select/insert - # self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - ''' Applies indexes to the PSPS tables @@ -1119,9 +1090,4 @@ self.scratchDb.createIndex("StackApFlx", "ippDetectID") self.scratchDb.createIndex("StackModelFit", "ippDetectID") - - # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of - # the column in PSPS - ## we don't have that in stacks anymore (it is now gdec/rdec/etc) - ##self.scratchDb.execute("ALTER TABLE StackObject CHANGE dec_ `dec` double") return True @@ -1201,5 +1167,4 @@ print "update dvo sql: ", sql - # response = raw_input("update dvo...") self.scratchDb.execute(sql) @@ -1211,6 +1176,4 @@ self.logger.infoPair("starting","populatePspsTables"); self.skipBatch = False - - # HAF everything is commented out for now with ## if not self.useFullTables: if not self.getIDsFromDVO(): @@ -1218,5 +1181,5 @@ #do some basics first # populate stack meta per filter - # insert objid per filter to each table + # insert objid/stackdetectid per filter to each table self.tablesToExport=[] @@ -1257,27 +1220,53 @@ self.tablesToExport.append("yStackMeta") - self.logger.infoPair("populating","StackObject") - # need to index on objid or it is stupidly slow + self.logger.infoPair("populating ","StackObject") + # need to index on objid and ippdetectid or it is stupidly slow self.scratchDb.createIndex("StackObject", "objID") - self.scratchDb.createIndex("StackModelFit", "objID") - self.scratchDb.createIndex("StackApFlx", "objID") - - - - #ok, now we have the shell stackObjects - these need to be filled. + #populate from dvo the (f)ippDetectID into StackObject self.populateStackObjectFromDvo() + #index the ippDetectids for the next step + self.scratchDb.createIndex("StackObject", "gippDetectID") + self.scratchDb.createIndex("StackObject", "rippDetectID") + self.scratchDb.createIndex("StackObject", "iippDetectID") + self.scratchDb.createIndex("StackObject", "zippDetectID") + self.scratchDb.createIndex("StackObject", "yippDetectID") self.populateStackObject() - - ##if self.stackType != "NIGHTLY_STACK": - ##self.logger.infoPair("populating","StackModelFit") - ##self.populateStackModelFit() - ##self.logger.infoPair("populating","StackApFlx") - ##self.populateStackApFlx() + # stackObject should be done, pending a few NULL columns (need help) + + if self.stackType != "NIGHTLY_STACK": + + self.logger.infoPair("populating ","StackModelFit") + + self.scratchDb.createIndex("StackModelFit", "objID") + #populate from dvo the (f)ippdetectid into stackModelFit + + #index the ippDetectIds for the next step + self.scratchDb.createIndex("StackModelFit", "gippDetectID") + self.scratchDb.createIndex("StackModelFit", "rippDetectID") + self.scratchDb.createIndex("StackModelFit", "iippDetectID") + self.scratchDb.createIndex("StackModelFit", "zippDetectID") + self.scratchDb.createIndex("StackModelFit", "yippDetectID") + #populate stackModelFit + #cull the entries that should not be (how?) + + self.logger.infoPair("populating ","StackApFlx") + + self.scratchDb.createIndex("StackApFlx", "objID") + #populate from dvo the (f)ippDetectID into StackApFlx + + #index the ippDetectIDs for the next step + self.scratchDb.createIndex("StackApFlx", "gippDetectID") + self.scratchDb.createIndex("StackApFlx", "rippDetectID") + self.scratchDb.createIndex("StackApFlx", "iippDetectID") + self.scratchDb.createIndex("StackApFlx", "zippDetectID") + self.scratchDb.createIndex("StackApFlx", "yippDetectID") + #populate StackApFlx + #cull the entries that should not be (how?) + + self.logger.infoPair("populating","StackToImage") self.populateStackToImage() - ##self.logger.infoPair("populating","StackObjectCalib") - ##self.populateStackObjectCalib() - - ##self.setMinMaxObjID(["StackObject"]) + + self.setMinMaxObjID(["StackObject"]) ##if self.totalDetections < 1: @@ -1290,8 +1279,5 @@ self.tablesToExport.append("StackApFlx") - - self.logger.infoPair("finishing","populatePspsTables"); -# raise return True '''