Index: trunk/ippToPsps/config/README =================================================================== --- trunk/ippToPsps/config/README (revision 36699) +++ trunk/ippToPsps/config/README (revision 36706) @@ -11,2 +11,17 @@ tables.FW.vot : forced warp measurement table tables.FO.vot : forced mean object table + + +Heather's Vague Instructions: + +to create a new batch db: + +create database ipptopsps2 +cut and paste the sql file - should be no problems, I tried to reorganize them so no foriegn key constraint fails. + +to make a scratch db: + check settings.xml if necessary and recompile + mysql -h localhost -u dvo -pdvo -e "create database ipptopsps2_scratch" + ippjython setupScratchDb.py + +should be good to go! Index: trunk/ippToPsps/config/ippToPspsDbSchema.sql =================================================================== --- trunk/ippToPsps/config/ippToPspsDbSchema.sql (revision 36699) +++ trunk/ippToPsps/config/ippToPspsDbSchema.sql (revision 36706) @@ -53,24 +53,4 @@ SET character_set_client = @saved_cs_client; --- --- Table structure for table `box` --- - -DROP TABLE IF EXISTS `box`; -SET @saved_cs_client = @@character_set_client; -SET character_set_client = utf8; -CREATE TABLE `box` ( - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, - `id` bigint(20) unsigned NOT NULL auto_increment, - `skychunk` varchar(30) default NULL, - `ra_center` float default NULL, - `dec_center` float default NULL, - `box_side` float default NULL, - `ingested` varchar(64) default 'none', - PRIMARY KEY (`id`), - KEY `fk_skychunk` (`skychunk`), - CONSTRAINT `fk_skychunk` FOREIGN KEY (`skychunk`) REFERENCES `skychunk` (`name`) ON DELETE CASCADE -) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; -SET character_set_client = @saved_cs_client; -- @@ -142,4 +122,25 @@ -- +-- Table structure for table `box` needs to be after skychunk or get err:150 +-- + +DROP TABLE IF EXISTS `box`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `box` ( + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, + `id` bigint(20) unsigned NOT NULL auto_increment, + `skychunk` varchar(30) default NULL, + `ra_center` float default NULL, + `dec_center` float default NULL, + `box_side` float default NULL, + `ingested` varchar(64) default 'none', + PRIMARY KEY (`id`), + KEY `fk_skychunk` (`skychunk`), + CONSTRAINT `fk_skychunk` FOREIGN KEY (`skychunk`) REFERENCES `skychunk` (`name`) ON DELETE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; +SET character_set_client = @saved_cs_client; + +-- -- Table structure for table `detection` -- @@ -169,5 +170,5 @@ `filter` varchar(1) default NULL, KEY `batch_id` (`batch_id`), - CONSTRAINT `detection_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) + CONSTRAINT `forcedwarpd_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; @@ -198,5 +199,5 @@ `region` varchar(50) default NULL, KEY `batch_id` (`batch_id`), - CONSTRAINT `object_fk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) + CONSTRAINT `diffobject_fk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; @@ -247,5 +248,5 @@ `diff_type` varchar(20) default NULL, KEY `batch_id` (`batch_id`), - CONSTRAINT `stack_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) + CONSTRAINT `diff_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `batch` (`batch_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client; Index: trunk/ippToPsps/config/parse_vot.pl =================================================================== --- trunk/ippToPsps/config/parse_vot.pl (revision 36699) +++ trunk/ippToPsps/config/parse_vot.pl (revision 36706) @@ -3,6 +3,6 @@ # this thing will just parse the votables and print out in 'ken schema style' -$wiki=1; - +#$wiki=1; +$csv = 1; $infield = 0; $intable = 0; @@ -33,5 +33,8 @@ if($datatype eq "long") {$datatype1 = "BIGINT 8";} - + if ($csv) { + $datatype1 =~ s/ .//; + + } if ($_ =~ /.*default=\"/) { @@ -49,8 +52,8 @@ if ($wiki) { printf("||%-17s|| %-15s|| %-15s || %-5s||",$name, $unit, $datatype1, $default); - } else { - printf("%-17s %-15s %-15s %-5s",$name, $unit, $datatype1, $default); + } elsif ($csv) { + printf("%s,%s,%s,%s,\"",$name, $unit, $datatype1, $default); } - + $infield = 1; @@ -68,8 +71,9 @@ if ($_ =~ /\<\/FIELD/) { if ($wiki) { print "||";} + if ($csv) {print "\"";} print "\n"; $infield = 0; } - + } else { #not in field something new @@ -80,24 +84,34 @@ $name =~ s/\".*//g; $intable = 1; - print "---------------------------\n"; - printf ("%-16s ", $name); + if ($csv) { + print "$name\n"; + } else { + print "---------------------------\n"; + printf ("%-16s ", $name); + } } else { if ($intable) { - if ($_ =~ /\<\/DESCRIPTION/) { - $t = $_; - chomp $t; - $t =~ s/\s+\//; - $t =~ s/\<\/DESCRIPTION\>//; - print "$t\n"; - print "---------------------------\n"; - $intable = 1; + if ($_ =~ /\<\/DESCRIPTION/) { + $t = $_; + chomp $t; + $t =~ s/\s+\//; + $t =~ s/\<\/DESCRIPTION\>//; + if ($csv) { + $dostuff = 1; + } else { + print "$t\n"; + print "---------------------------\n"; + } + $intable = 1; + + } + + } + } + if ($_ =~ /\<\/TABLE\>/) { + $intable = 0; } - } - if ($_ =~ /\<\/TABLE\>/) { - $intable = 0; - } - } - + } } Index: trunk/ippToPsps/jython/detectionbatch.py =================================================================== --- trunk/ippToPsps/jython/detectionbatch.py (revision 36699) +++ trunk/ippToPsps/jython/detectionbatch.py (revision 36706) @@ -257,7 +257,13 @@ ,psfWidMinor \ ,psfTheta \ - ,momentFwhm \ - ,momentWidMajor \ - ,momentWidMinor \ + ,momentXX \ + ,momentXY \ + ,momentYY \ + ,momentM3C \ + ,momentM3S \ + ,momentM4C \ + ,momentM4S \ + ,momentR1 \ + ,momentRH \ ,apResid \ ,dapResid \ @@ -319,7 +325,13 @@ ," + self.safeDictionaryAccess(header, 'FWHM_MIN') + " \ ," + self.safeDictionaryAccess(header, 'ANGLE') + " \ - , ( " + self.safeDictionaryAccess(header, 'IQ_FW1') + " + " + self.safeDictionaryAccess(header, 'IQ_FW2') + " ) / 2.0 \ - ," + self.safeDictionaryAccess(header, 'IQ_FW1') + " \ - ," + self.safeDictionaryAccess(header, 'IQ_FW2') + " \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ ," + self.safeDictionaryAccess(header, 'APMIFIT') + " \ ," + self.safeDictionaryAccess(header, 'DAPMIFIT') + " \ @@ -407,8 +419,8 @@ ,psfFlux \ ,psfFluxErr \ - ,peakADU \ - ,psfWidMajor \ - ,psfWidMinor \ + ,psfMajorFWHM \ + ,psfMinorFWHM \ ,psfTheta \ + ,psfCore \ ,psfLikelihood \ ,psfQf \ @@ -416,4 +428,10 @@ ,momentXY \ ,momentYY \ + ,momentR1 \ + ,momentRH \ + ,momentM3C \ + ,momentM3S \ + ,momentM4C \ + ,momentM4S \ ,apFlux \ ,kronFlux \ @@ -424,8 +442,4 @@ ,sky \ ,skyErr \ - ,sgSep \ - ,activeFlag \ - ,assocDate \ - ,historyModNum \ ,dataRelease \ ) \ @@ -441,8 +455,8 @@ ,PSF_INST_FLUX / " + self.header['EXPTIME'] + " \ ,PSF_INST_FLUX_SIG / " + self.header['EXPTIME'] + " \ - ,POW(10.0, (-0.4*PEAK_FLUX_AS_MAG)) / " + self.header['EXPTIME'] + " \ ,PSF_MAJOR \ ,PSF_MINOR \ ,PSF_THETA \ + ,-999 \ ,psfLikelihood(EXT_NSIGMA) \ ,PSF_QF \ @@ -450,4 +464,10 @@ ,MOMENTS_XY \ ,MOMENTS_YY \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ + ,-999 \ ,POW(10.0, -0.4 *AP_MAG) / " + self.header['EXPTIME'] + " \ ,KRON_FLUX / " + self.header['EXPTIME'] + " \ @@ -458,8 +478,4 @@ ,SKY / " + self.header['EXPTIME'] + " \ ,SKY_SIGMA / " + self.header['EXPTIME'] + " \ - ,EXT_NSIGMA \ - , 0 \ - , '" + self.dateStr + "' \ - , 0 \ , " + str(self.skychunk.dataRelease) + "\ FROM " + ippTableName @@ -496,5 +512,5 @@ # we don't delete these anymore results['NULLINSTFLUX'] = 0; - results['NULLPEAKADU'] = 0; + ''' @@ -531,10 +547,10 @@ def populateDetectionCalibTableUpdateInsert(self, ota): - tableName = "DetectionCalib_" + ota + tableName = "Detection_" + ota # drop then re-create table - self.scratchDb.dropTable(tableName) - sql = "CREATE TABLE " + tableName + " LIKE DetectionCalib" - try: self.scratchDb.execute(sql) - except: pass + #self.scratchDb.dropTable(tableName) + #sql = "CREATE TABLE " + tableName + " LIKE DetectionCalib" + #try: self.scratchDb.execute(sql) + #except: pass # insert all detections into table @@ -555,5 +571,5 @@ ,surveyID \ FROM Detection_" + ota - self.scratchDb.execute(sql) +# self.scratchDb.execute(sql) # insert calibration information from dvoDetections into the Table @@ -564,5 +580,4 @@ a.decErr = b.decErr, \ a.zp = b.zp, \ - a.zpErr = b.zpErr, \ a.expTime = b.expTime, \ a.airMass = b.airMass \ @@ -577,66 +592,33 @@ # target table name: - tableName = "DetectionCalib_" + ota + tableName = "Detection_" + ota # drop then re-create table - self.scratchDb.dropTable(tableName) - sql = "CREATE TABLE " + tableName + " LIKE DetectionCalib" - try: self.scratchDb.execute(sql) - except: pass + #self.scratchDb.dropTable(tableName) + #sql = "CREATE TABLE " + tableName + " LIKE Detection" + #try: self.scratchDb.execute(sql) + #except: pass externID = self.imageIDs[ota] imageID = self.scratchDb.getImageIDFromExternID(externID) - 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) + self.logger.infoPair("obtained imageID",imageID) + self.logger.infoPair("obtained externID",externID) # insert all detections into table - sql = "SELECT \ - a.objID, \ - a.detectID, \ - 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 \ - Detection_" + ota + " as a \ - JOIN " + self.scratchDb.dvoDetectionTable + " as b \ - ON (a.objID = b.objID AND a.detectID = b.detectID) \ - WHERE b.imageID = " + str(imageID) + \ - " INTO OUTFILE '" + dumpFile + "'" + sql = "Update Detection_" + ota + " 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.objID = b.objID AND a.detectID = b.detectID) \ + AND b.imageID = " + str(imageID) \ + try: self.scratchDb.execute(sql) except: - self.logger.info("failed to select data for detectionCalib") - 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 detectionCalib") + self.logger.info("failed to update data for detectionCalib") self.logger.infoPair("sql: ", sql) raise @@ -751,5 +733,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 DetectionCalib CHANGE dec_ `dec` double") + self.scratchDb.execute("ALTER TABLE Detection CHANGE dec_ `dec` double") return True @@ -818,23 +800,22 @@ return False - self.logger.info("populate stuff "); + #self.logger.info("populate stuff "); # populate remainder of tables self.populateDetectionTable(chipname, results) - self.logger.info("successful populate "); + #self.logger.info("successful populate "); # now add DVO IDs self.updateDvoIDs("Detection_" + chipname, self.imageIDs[chipname]) - self.logger.info("updated dvoids") + #self.logger.info("updated dvoids") results['NULLOBJID'] = self.scratchDb.reportAndDeleteRowsWithNULLS("Detection_" + chipname, "objID") #self.logger.info("deleted nulls") self.updateImageID("Detection_" + chipname, x, y) - self.logger.info("updateImageId") + #self.logger.info("updateImageId") rowCount = self.scratchDb.getRowCount("Detection_" + chipname) - self.logger.info("got row count") - self.logger.info("| %5s | %13d | %13d | %13d | %13d | %13d | %13d |", + #self.logger.info("got row count") + self.logger.info("| %5s | %13d | %13d | %13d | %13d | %13d |", chipname, results['ORIGINALTOTAL'], results['SATDET'], results['NULLINSTFLUX'], - results['NULLPEAKADU'], results['NULLOBJID'], rowCount) @@ -842,5 +823,4 @@ self.totalSatDet = self.totalSatDet + results['SATDET'] self.totalNulIInstFlux = self.totalNulIInstFlux + results['NULLINSTFLUX'] - self.totalNullPeakFlux = self.totalNullPeakFlux + results['NULLPEAKADU'] self.totalNullObjID = self.totalNullObjID + results['NULLOBJID'] self.totalDetections = self.totalDetections + rowCount @@ -867,11 +847,8 @@ self.scratchDb.execute(sql) - self.logger.info("updated imagedata") - self.populateSkinnyObjectTable(chipname) - self.logger.info("updated skinnyobject") - #self.populateObjectCalColorTable(chipname) - #self.logger.info("updated objectcalcolor") + #self.logger.info("updated imagedata") + self.populateDetectionCalibTable(chipname) - self.logger.info("updated detectioncalibtable") + #self.logger.info("updated detectioncalibtable") # add these to list of tables to export later @@ -882,11 +859,5 @@ self.logger.info("export Detection") - self.tablesToExport.append("SkinnyObject_" + chipname) - self.logger.info("export Skinny") - - #self.tablesToExport.append("ObjectCalColor_" + chipname) - self.tablesToExport.append("DetectionCalib_" + chipname) - self.logger.info("export DetectionCalib") - + tables.append("Detection_" + chipname) self.logger.info("updated detectioncalibtable") @@ -910,7 +881,7 @@ results = {} self.totalOriginal = self.totalSatDet = self.totalNulIInstFlux = self.totalNullPeakFlux = self.totalNullObjID = self.totalDetections = 0 - self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+---------------+") - self.logger.info("| OTA | Initial total | Sat Det | NULL instFlux | NULL peak ADU | NULL obj ID | Remainder |") - self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+---------------+") + self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+") + self.logger.info("| OTA | Initial total | Sat Det | NULL instFlux | NULL obj ID | Remainder |") + self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+") for x in range(self.startX, self.endX): for y in range(self.startY, self.endY): @@ -937,5 +908,5 @@ # print totals - self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+---------------+") + self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+") self.logger.info("| Total | %13d | %13d | %13d | %13d | %13d | %13d |", self.totalOriginal, @@ -945,5 +916,5 @@ self.totalNullObjID, self.totalDetections) - self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+---------------+") + self.logger.info("+-------+---------------+---------------+---------------+---------------+---------------+") # if we only have one table export, i.e. FrameMeta, then get out of here (skip batch, but do not abort) Index: trunk/ippToPsps/jython/stackbatch.py =================================================================== --- trunk/ippToPsps/jython/stackbatch.py (revision 36699) +++ 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: