Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/Makefile.am =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/Makefile.am (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/Makefile.am (revision 37129) @@ -22,5 +22,5 @@ gpc1db.py \ initbatch.py \ - insert.dvodb.info.py \ + insert.dvodb.info.py \ ipptopspsdb.py \ ipptopsps.py \ @@ -35,8 +35,9 @@ pslogger.py \ queue.py \ - removepending.py \ + removepending.py \ scratchdb.py \ setupScratchDb.py \ skychunk.py \ + sqlUtility.py \ stackbatch.py Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/batch.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/batch.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/batch.py (revision 37129) @@ -37,6 +37,5 @@ batchID, batchType, - fits, - useFullTables): + fits): # print "starting the batch" @@ -49,5 +48,4 @@ self.skychunk = skychunk self.fits = fits - self.useFullTables = useFullTables # set up logging @@ -79,5 +77,6 @@ raise - self.scratchDb.setUseFullTables(self.useFullTables) + # define the dvo table names + self.scratchDb.setDvoTableNames() # TODO @@ -113,6 +112,6 @@ self.dateStr = now.strftime("%Y-%m-%d") - # create DVO tables if accessing DVO directly - if not self.useFullTables: self.scratchDb.createDvoTables() + # create DVO tables (scratch database) + self.scratchDb.createDvoTables() # dump stuff to the log @@ -120,5 +119,4 @@ self.logger.infoPair("Batch name", self.batchName) self.logger.infoPair("Survey ID", "%d" % self.surveyID) - self.logger.infoBool("Use full DVO tables?", self.useFullTables) if self.fits: Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/detectionbatch.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/detectionbatch.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/detectionbatch.py (revision 37129) @@ -19,4 +19,5 @@ from ipptopspsdb import IppToPspsDb from scratchdb import ScratchDb +from sqlUtility import sqlUtility import logging.config @@ -41,6 +42,5 @@ scratchDb, camID, - batchID, - useFullTables): + batchID): super(DetectionBatch, self).__init__( @@ -54,6 +54,5 @@ batchID, "P2", - gpc1Db.getCameraStageSmf(camID,skychunk.P2_smf_version), - useFullTables) + gpc1Db.getCameraStageSmf(camID,skychunk.P2_smf_version)) # get camera meta data @@ -124,93 +123,51 @@ self.logger.infoPair("Proccesing table", "FrameMeta") - sql = "INSERT INTO FrameMeta (\ - frameID \ - ,frameName \ - ,cameraID \ - ,cameraConfigID \ - ,telescopeID \ - ,analysisVer \ - ,p1Recip \ - ,p2Recip \ - ,p3Recip \ - ,photoScat \ - ,expStart \ - ,expTime \ - ,airmass \ - ,raBore \ - ,decBore \ - ,ctype1 \ - ,ctype2 \ - ,crval1 \ - ,crval2 \ - ,crpix1 \ - ,crpix2 \ - ,cdelt1 \ - ,cdelt2 \ - ,pc001001 \ - ,pc001002 \ - ,pc002001 \ - ,pc002002 \ - ,polyOrder \ - ,pca1x3y0 \ - ,pca1x2y1 \ - ,pca1x1y2 \ - ,pca1x0y3 \ - ,pca1x2y0 \ - ,pca1x1y1 \ - ,pca1x0y2 \ - ,pca2x3y0 \ - ,pca2x2y1 \ - ,pca2x1y2 \ - ,pca2x0y3 \ - ,pca2x2y0 \ - ,pca2x1y1 \ - ,pca2x0y2 \ - ) VALUES ( \ - " + str(self.expID) + " \ - ,'" + self.expName + "' \ - ,1 \ - ,1 \ - ,1 \ - ,'" + str(self.analysisVer) + "' \ - ,' ' \ - ,' ' \ - ,' ' \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_ERR') + " \ - ," + self.safeDictionaryAccess(self.header, 'MJD-OBS') + " \ - ," + self.safeDictionaryAccess(self.header, 'EXPREQ') + " \ - ," + self.safeDictionaryAccess(self.header, 'AIRMASS') + " \ - ," + self.safeDictionaryAccess(self.header, 'RA') + " \ - ," + self.safeDictionaryAccess(self.header, 'DEC') + " \ - ,'" + self.safeDictionaryAccess(self.header, 'CTYPE1') + "' \ - ,'" + self.safeDictionaryAccess(self.header, 'CTYPE2') + "' \ - ," + self.safeDictionaryAccess(self.header, 'CRVAL1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRVAL2') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRPIX1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRPIX2') + " \ - ," + self.safeDictionaryAccess(self.header, 'CDELT1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CDELT2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC001001') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC001002') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC002001') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC002002') + " \ - ," + self.safeDictionaryAccess(self.header, 'NPLYTERM') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X3Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y3') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X3Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y3') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y2') + " \ - )" - - # print "frame meta sql: ", sql + sqlLine = sqlUtility("INSERT INTO FrameMeta (") + + sqlLine.group("frameID", str(self.expID)); + sqlLine.group("frameName", self.expName); + sqlLine.group("cameraID", "1"); + sqlLine.group("cameraConfigID", "1"); + sqlLine.group("telescopeID", "1"); + sqlLine.group("analysisVer", str(self.analysisVer)); + sqlLine.group("p1Recip", " "); + sqlLine.group("p2Recip", " "); + sqlLine.group("p3Recip", " "); + sqlLine.group("photoScat", "self.safeDictionaryAccess(self.header, 'ZPT_ERR')"); + sqlLine.group("expStart", "self.safeDictionaryAccess(self.header, 'MJD-OBS')"); + sqlLine.group("expTime", "self.safeDictionaryAccess(self.header, 'EXPREQ')"); + sqlLine.group("airmass", "self.safeDictionaryAccess(self.header, 'AIRMASS')"); + sqlLine.group("raBore", "self.safeDictionaryAccess(self.header, 'RA')"); + sqlLine.group("decBore", "self.safeDictionaryAccess(self.header, 'DEC')"); + sqlLine.group("ctype1", self.safeDictionaryAccess(self.header, 'CTYPE1')); + sqlLine.group("ctype2", self.safeDictionaryAccess(self.header, 'CTYPE2')); + sqlLine.group("crval1", "self.safeDictionaryAccess(self.header, 'CRVAL1')"); + sqlLine.group("crval2", "self.safeDictionaryAccess(self.header, 'CRVAL2')"); + sqlLine.group("crpix1", "self.safeDictionaryAccess(self.header, 'CRPIX1')"); + sqlLine.group("crpix2", "self.safeDictionaryAccess(self.header, 'CRPIX2')"); + sqlLine.group("cdelt1", "self.safeDictionaryAccess(self.header, 'CDELT1')"); + sqlLine.group("cdelt2", "self.safeDictionaryAccess(self.header, 'CDELT2')"); + sqlLine.group("pc001001", "self.safeDictionaryAccess(self.header, 'PC001001')"); + sqlLine.group("pc001002", "self.safeDictionaryAccess(self.header, 'PC001002')"); + sqlLine.group("pc002001", "self.safeDictionaryAccess(self.header, 'PC002001')"); + sqlLine.group("pc002002", "self.safeDictionaryAccess(self.header, 'PC002002')"); + sqlLine.group("polyOrder", "self.safeDictionaryAccess(self.header, 'NPLYTERM')"); + sqlLine.group("pca1x3y0", "self.safeDictionaryAccess(self.header, 'PCA1X3Y0')"); + sqlLine.group("pca1x2y1", "self.safeDictionaryAccess(self.header, 'PCA1X2Y1')"); + sqlLine.group("pca1x1y2", "self.safeDictionaryAccess(self.header, 'PCA1X1Y2')"); + sqlLine.group("pca1x0y3", "self.safeDictionaryAccess(self.header, 'PCA1X0Y3')"); + sqlLine.group("pca1x2y0", "self.safeDictionaryAccess(self.header, 'PCA1X2Y0')"); + sqlLine.group("pca1x1y1", "self.safeDictionaryAccess(self.header, 'PCA1X1Y1')"); + sqlLine.group("pca1x0y2", "self.safeDictionaryAccess(self.header, 'PCA1X0Y2')"); + sqlLine.group("pca2x3y0", "self.safeDictionaryAccess(self.header, 'PCA2X3Y0')"); + sqlLine.group("pca2x2y1", "self.safeDictionaryAccess(self.header, 'PCA2X2Y1')"); + sqlLine.group("pca2x1y2", "self.safeDictionaryAccess(self.header, 'PCA2X1Y2')"); + sqlLine.group("pca2x0y3", "self.safeDictionaryAccess(self.header, 'PCA2X0Y3')"); + sqlLine.group("pca2x2y0", "self.safeDictionaryAccess(self.header, 'PCA2X2Y0')"); + sqlLine.group("pca2x1y1", "self.safeDictionaryAccess(self.header, 'PCA2X1Y1')"); + sqlLine.group("pca2x0y2", "self.safeDictionaryAccess(self.header, 'PCA2X0Y2')"); + + sql = sqlLine.make(") VALUES ( ", ")") + print "frame meta sql: ", sql self.scratchDb.execute(sql) @@ -228,151 +185,86 @@ tableName = "ImageMeta_" + ota - # XXX we drop the table above so it is not left behind on failure - # drop then re-create table - # self.scratchDb.dropTable(tableName) + # we drop the table before calling this functoin so it is not left behind on failure sql = "CREATE TABLE " + tableName + " LIKE ImageMeta" try: self.scratchDb.execute(sql) except: pass + if (ota[0:2] == "XY"): ccdID = ota[2:4] else: ccdID = 0 + psfFwhm = 0.5*(self.safeDictionaryAccess(header, 'FWHM_MAJ') + self.safeDictionaryAccess(header, 'FWHM_MIN')) + # insert image metadata into table - sql = "INSERT INTO " + tableName + " ( \ - frameID \ - ,ccdID \ - ,bias \ - ,biasScat \ - ,sky \ - ,skyScat \ - ,magSat \ - ,completMag \ - ,astroScat \ - ,photoScat \ - ,numAstroRef \ - ,numPhotoRef \ - ,nx \ - ,ny \ - ,psfFwhm \ - ,psfWidMajor \ - ,psfWidMinor \ - ,psfTheta \ - ,momentXX \ - ,momentXY \ - ,momentYY \ - ,momentM3C \ - ,momentM3S \ - ,momentM4C \ - ,momentM4S \ - ,momentR1 \ - ,momentRH \ - ,apResid \ - ,dapResid \ - ,detectorID \ - ,qaFlags \ - ,detrend1 \ - ,detrend2 \ - ,detrend3 \ - ,detrend4 \ - ,detrend5 \ - ,detrend6 \ - ,detrend7 \ - ,detrend8 \ - ,photoZero \ - ,ctype1 \ - ,ctype2 \ - ,crval1 \ - ,crval2 \ - ,crpix1 \ - ,crpix2 \ - ,cdelt1 \ - ,cdelt2 \ - ,pc001001 \ - ,pc001002 \ - ,pc002001 \ - ,pc002002 \ - ,polyOrder \ - ,pca1x3y0 \ - ,pca1x2y1 \ - ,pca1x1y2 \ - ,pca1x0y3 \ - ,pca1x2y0 \ - ,pca1x1y1 \ - ,pca1x0y2 \ - ,pca2x3y0 \ - ,pca2x2y1 \ - ,pca2x1y2 \ - ,pca2x0y3 \ - ,pca2x2y0 \ - ,pca2x1y1 \ - ,pca2x0y2 \ - ) VALUES ( \ - " + str(self.expID) + " \ - ," + str(ccdID) + " \ - ," + str(self.bias) + " \ - ," + str(self.biasScat) + " \ - ," + self.safeDictionaryAccess(header, 'MSKY_MN') + " \ - ," + self.safeDictionaryAccess(header, 'MSKY_SIG') + " \ - ," + self.safeDictionaryAccess(header, 'FSATUR') + " \ - ," + self.safeDictionaryAccess(header, 'FLIMIT') + " \ - ," + self.safeDictionaryAccess(header, 'CERROR') + " \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \ - ," + self.safeDictionaryAccess(header, 'NASTRO') + " \ - ," + self.safeDictionaryAccess(header, 'NASTRO') + " \ - ," + self.safeDictionaryAccess(header, 'CNAXIS1') + " \ - ," + self.safeDictionaryAccess(header, 'CNAXIS2') + " \ - , ( " + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " + " + self.safeDictionaryAccess(header, 'FWHM_MIN') + " ) / 2.0 \ - ," + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " \ - ," + self.safeDictionaryAccess(header, 'FWHM_MIN') + " \ - ," + self.safeDictionaryAccess(header, 'ANGLE') + " \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ," + self.safeDictionaryAccess(header, 'APMIFIT') + " \ - ," + self.safeDictionaryAccess(header, 'DAPMIFIT') + " \ - ,'" + self.safeDictionaryAccess(header, 'DETECTOR') + "' \ - ," + str(self.scratchDb.getDvoImageFlags(header['IMAGEID'])) + " \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.MASK') + "' \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.DARK') + "' \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.FLAT') + "' \ - ,' ' \ - ,' ' \ - ,' ' \ - ,' ' \ - ,' ' \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \ - ,'" + self.safeDictionaryAccess(header, 'CTYPE1') + "' \ - ,'" + self.safeDictionaryAccess(header, 'CTYPE2') + "' \ - ," + self.safeDictionaryAccess(header, 'CRVAL1') + " \ - ," + self.safeDictionaryAccess(header, 'CRVAL2') + " \ - ," + self.safeDictionaryAccess(header, 'CRPIX1') + " \ - ," + self.safeDictionaryAccess(header, 'CRPIX2') + " \ - ," + self.safeDictionaryAccess(header, 'CDELT1') + " \ - ," + self.safeDictionaryAccess(header, 'CDELT2') + " \ - ," + self.safeDictionaryAccess(header, 'PC001001') + " \ - ," + self.safeDictionaryAccess(header, 'PC001002') + " \ - ," + self.safeDictionaryAccess(header, 'PC002001') + " \ - ," + self.safeDictionaryAccess(header, 'PC002002') + " \ - ," + self.safeDictionaryAccess(header, 'NPLYTERM') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X3Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X2Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X1Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X0Y3') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X2Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X1Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X0Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X3Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X2Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X1Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X0Y3') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X2Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X1Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X0Y2') + " \ - )" + sqlLine = sqlUtility("INSERT INTO " + tableName + "(") + + sqlLine.group("frameID", str(self.expID)) + sqlLine.group("ccdID", str(ccdID)) + sqlLine.group("bias", str(self.bias)) + sqlLine.group("biasScat", str(self.biasScat)) + sqlLine.group("sky", "self.safeDictionaryAccess(header, 'MSKY_MN')") + sqlLine.group("skyScat", "self.safeDictionaryAccess(header, 'MSKY_SIG')") + sqlLine.group("magSat", "self.safeDictionaryAccess(header, 'FSATUR')") + sqlLine.group("completMag", "self.safeDictionaryAccess(header, 'FLIMIT')") + sqlLine.group("astroScat", "self.safeDictionaryAccess(header, 'CERROR')") + sqlLine.group("photoScat", "self.safeDictionaryAccess(self.header, 'ZPT_OBS')") + sqlLine.group("numAstroRef", "self.safeDictionaryAccess(header, 'NASTRO')") + sqlLine.group("numPhotoRef", "self.safeDictionaryAccess(header, 'NASTRO')") + sqlLine.group("nx", "self.safeDictionaryAccess(header, 'CNAXIS1')") + sqlLine.group("ny", "self.safeDictionaryAccess(header, 'CNAXIS2')") + sqlLine.group("psfFwhm", str(psfFwhm)) + sqlLine.group("psfWidMajor", "self.safeDictionaryAccess(header, 'FWHM_MAJ')") + sqlLine.group("psfWidMinor", "self.safeDictionaryAccess(header, 'FWHM_MIN')") + sqlLine.group("psfTheta", "self.safeDictionaryAccess(header, 'ANGLE')") + sqlLine.group("momentXX", "-999") + sqlLine.group("momentXY", "-999") + sqlLine.group("momentYY", "-999") + sqlLine.group("momentM3C", "-999") + sqlLine.group("momentM3S", "-999") + sqlLine.group("momentM4C", "-999") + sqlLine.group("momentM4S", "-999") + sqlLine.group("momentR1", "-999") + sqlLine.group("momentRH", "-999") + sqlLine.group("apResid", "self.safeDictionaryAccess(header, 'APMIFIT')") + sqlLine.group("dapResid", "self.safeDictionaryAccess(header, 'DAPMIFIT')") + sqlLine.group("detectorID", self.safeDictionaryAccess(header, 'DETECTOR')) + sqlLine.group("qaFlags", str(self.scratchDb.getDvoImageFlags(header['IMAGEID']))) + sqlLine.group("detrend1", self.safeDictionaryAccess(header, 'DETREND.MASK')) + sqlLine.group("detrend2", self.safeDictionaryAccess(header, 'DETREND.DARK')) + sqlLine.group("detrend3", self.safeDictionaryAccess(header, 'DETREND.FLAT')) + sqlLine.group("detrend4", " ") + sqlLine.group("detrend5", " ") + sqlLine.group("detrend6", " ") + sqlLine.group("detrend7", " ") + sqlLine.group("detrend8", " ") + sqlLine.group("photoZero", "self.safeDictionaryAccess(self.header, 'ZPT_OBS')") + sqlLine.group("ctype1", "self.safeDictionaryAccess(header, 'CTYPE1')") + sqlLine.group("ctype2", "self.safeDictionaryAccess(header, 'CTYPE2')") + sqlLine.group("crval1", "self.safeDictionaryAccess(header, 'CRVAL1')") + sqlLine.group("crval2", "self.safeDictionaryAccess(header, 'CRVAL2')") + sqlLine.group("crpix1", "self.safeDictionaryAccess(header, 'CRPIX1')") + sqlLine.group("crpix2", "self.safeDictionaryAccess(header, 'CRPIX2')") + sqlLine.group("cdelt1", "self.safeDictionaryAccess(header, 'CDELT1')") + sqlLine.group("cdelt2", "self.safeDictionaryAccess(header, 'CDELT2')") + sqlLine.group("pc001001", "self.safeDictionaryAccess(header, 'PC001001')") + sqlLine.group("pc001002", "self.safeDictionaryAccess(header, 'PC001002')") + sqlLine.group("pc002001", "self.safeDictionaryAccess(header, 'PC002001')") + sqlLine.group("pc002002", "self.safeDictionaryAccess(header, 'PC002002')") + sqlLine.group("polyOrder", "self.safeDictionaryAccess(header, 'NPLYTERM')") + sqlLine.group("pca1x3y0", "self.safeDictionaryAccess(header, 'PCA1X3Y0')") + sqlLine.group("pca1x2y1", "self.safeDictionaryAccess(header, 'PCA1X2Y1')") + sqlLine.group("pca1x1y2", "self.safeDictionaryAccess(header, 'PCA1X1Y2')") + sqlLine.group("pca1x0y3", "self.safeDictionaryAccess(header, 'PCA1X0Y3')") + sqlLine.group("pca1x2y0", "self.safeDictionaryAccess(header, 'PCA1X2Y0')") + sqlLine.group("pca1x1y1", "self.safeDictionaryAccess(header, 'PCA1X1Y1')") + sqlLine.group("pca1x0y2", "self.safeDictionaryAccess(header, 'PCA1X0Y2')") + sqlLine.group("pca2x3y0", "self.safeDictionaryAccess(header, 'PCA2X3Y0')") + sqlLine.group("pca2x2y1", "self.safeDictionaryAccess(header, 'PCA2X2Y1')") + sqlLine.group("pca2x1y2", "self.safeDictionaryAccess(header, 'PCA2X1Y2')") + sqlLine.group("pca2x0y3", "self.safeDictionaryAccess(header, 'PCA2X0Y3')") + sqlLine.group("pca2x2y0", "self.safeDictionaryAccess(header, 'PCA2X2Y0')") + sqlLine.group("pca2x1y1", "self.safeDictionaryAccess(header, 'PCA2X1Y1')") + sqlLine.group("pca2x0y2", "self.safeDictionaryAccess(header, 'PCA2X0Y2')") + + sql = sqlLine.make(") VALUES ( ", ")") try: self.scratchDb.execute(sql) @@ -407,104 +299,66 @@ results['SATDET'] = 0 - # insert all detections into table - sql = "INSERT IGNORE INTO " + pspsTableName + " ( \ - ippDetectID \ - ,randomDetID \ - ,filterID \ - ,surveyID \ - ,obsTime \ - ,xPos \ - ,yPos \ - ,xPosErr \ - ,yPosErr \ - ,psfFlux \ - ,psfFluxErr \ - ,psfMajorFWHM \ - ,psfMinorFWHM \ - ,psfTheta \ - ,psfCore \ - ,psfLikelihood \ - ,psfQf \ - ,momentXX \ - ,momentXY \ - ,momentYY \ - ,momentR1 \ - ,momentRH \ - ,momentM3C \ - ,momentM3S \ - ,momentM4C \ - ,momentM4S \ - ,apFlux \ - ,kronFlux \ - ,kronFluxErr \ - ,psfQfPerfect \ - ,psfChiSq \ - ,infoFlag \ - ,sky \ - ,skyErr \ - ,pltScale \ - ,posAngle \ - ,dataRelease \ - ) \ - SELECT \ - IPP_IDET \ - , FLOOR(RAND()*9223372036854775807) \ - , " + str(self.filterID) + "\ - , " + str(self.surveyID) + " \ - ," + str(self.obsTime) + " \ - ,X_PSF \ - ,Y_PSF \ - ,X_PSF_SIG \ - ,Y_PSF_SIG \ - ,PSF_INST_FLUX / " + self.header['EXPTIME'] + " \ - ,PSF_INST_FLUX_SIG / " + self.header['EXPTIME'] + " \ - ,PSF_MAJOR \ - ,PSF_MINOR \ - ,PSF_THETA \ - ,-999 \ - ,psfLikelihood(EXT_NSIGMA) \ - ,PSF_QF \ - ,MOMENTS_XX \ - ,MOMENTS_XY \ - ,MOMENTS_YY \ - ,MOMENTS_R1 \ - ,MOMENTS_RH \ - ,MOMENTS_M3C \ - ,MOMENTS_M3S \ - ,MOMENTS_M4C \ - ,MOMENTS_M4S \ - ,POW(10.0, -0.4 *AP_MAG) / " + self.header['EXPTIME'] + " \ - ,KRON_FLUX / " + self.header['EXPTIME'] + " \ - ,KRON_FLUX_ERR / " + self.header['EXPTIME'] + " \ - ,PSF_QF_PERFECT \ - ,PSF_CHISQ \ - ,FLAGS2 << 32 | FLAGS \ - ,SKY / " + self.header['EXPTIME'] + " \ - ,SKY_SIGMA / " + self.header['EXPTIME'] + " \ - ,PLTSCALE \ - ,POSANGLE \ - , " + str(self.skychunk.dataRelease) + "\ - FROM " + ippTableName - # self.logger.info(sql) - -# these were used above (left over from before PSF_INST_FLUX, PSF_INST_FLUX_SIG were available? -# ,POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + " -# ,ABS((PSF_INST_MAG_SIG*(POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + ")) / 1.085736) - -# I need a way to choose the sql above based on the cmf version: V3 (LAP.PV1) does not have AP_FLUX -# AP_FLUX is no ,AP_FLUX / " + self.header['EXPTIME'] + " - - self.scratchDb.execute(sql) - - # XXX EAM : I removed this old fix for invalid fluxes - # add a instFlux = 0.0 -> 0.000001 - # XXX EAM : why is this done? - # sql="UPDATE " + pspsTableName + " SET psfFlux = 0.0000001 WHERE psfFlux = 0" - # self.scratchDb.execute(sql) - + extTimeString = str(self.header['EXPTIME']) + + # insert all detections into table : do NOT include the IGNORE unless we have to + # sqlLine = sqlUtility("INSERT IGNORE INTO " + pspsTableName + " (") + sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (") + + # XXX WARNING: need to apply platescale to convert sizes to arcsec + # XXX apFluxF or apFluxNpix + apFluxRadius? + + sqlLine.group("ippDetectID", "IPP_IDET") + sqlLine.group("randomDetID", "FLOOR(RAND()*9223372036854775807)") + sqlLine.group("filterID", str(self.filterID)) + sqlLine.group("surveyID", str(self.surveyID)) + sqlLine.group("obsTime", str(self.obsTime)) + sqlLine.group("xPos", "X_PSF") + sqlLine.group("yPos", "Y_PSF") + sqlLine.group("xPosErr", "X_PSF_SIG") + sqlLine.group("yPosErr", "Y_PSF_SIG") + sqlLine.group("pltScale", "PLTSCALE") + sqlLine.group("posAngle", "POSANGLE") + sqlLine.group("psfFlux", "PSF_INST_FLUX / " + extTimeString) + sqlLine.group("psfFluxErr", "PSF_INST_FLUX_SIG / " + extTimeString) + sqlLine.group("psfMajorFWHM", "PSF_FWHM_MAJ") + sqlLine.group("psfMinorFWHM", "PSF_FWHM_MIN") + sqlLine.group("psfTheta", "PSF_THETA") + sqlLine.group("psfCore", "PSF_CORE") + sqlLine.group("psfQf", "PSF_QF") + sqlLine.group("psfQfPerfect", "PSF_QF_PERFECT") + sqlLine.group("psfChiSq", "PSF_CHISQ") + sqlLine.group("psfLikelihood", "psfLikelihood(EXT_NSIGMA)") + sqlLine.group("momentXX", "MOMENTS_XX") + sqlLine.group("momentXY", "MOMENTS_XY") + sqlLine.group("momentYY", "MOMENTS_YY") + sqlLine.group("momentR1", "MOMENTS_R1") + sqlLine.group("momentRH", "MOMENTS_RH") + sqlLine.group("momentM3C", "MOMENTS_M3C") + sqlLine.group("momentM3S", "MOMENTS_M3S") + sqlLine.group("momentM4C", "MOMENTS_M4C") + sqlLine.group("momentM4S", "MOMENTS_M4S") + sqlLine.group("apFlux", "AP_FLUX / " + extTimeString) + sqlLine.group("apFluxErr", "AP_FLUX_SIG / " + extTimeString) + sqlLine.group("apFluxNpix", "AP_NPIX") + sqlLine.group("kronFlux", "KRON_FLUX / " + extTimeString) + sqlLine.group("kronFluxErr", "KRON_FLUX_ERR / " + extTimeString) + sqlLine.group("kronRad", "MOMENTS_R1 * 2.5") + sqlLine.group("sky", "SKY / " + extTimeString) + sqlLine.group("skyErr", "SKY_SIGMA / " + extTimeString) + sqlLine.group("infoFlag", "FLAGS") + sqlLine.group("infoFlag2", "FLAGS2") + sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) + + sqlLine.make(") SELECT ", " FROM " + ippTableName) + + print "sql: " + sql + + self.scratchDb.execute(sql) + # NOTE : Flux limits : in the current PSPS schema, negative fluxes # cause problems for sql queries which work in mags as SQL cannot do # something like (f < 0.0) ? -999 : -2.5*log10(f) # as a result, the negative fluxes here result in floating point errors + # XXX EAM 2014072 : Is this still a problem? sql = "UPDATE " + pspsTableName + " SET psfFlux = 1e20 WHERE psfFlux <= 0.0" self.scratchDb.execute(sql) @@ -518,5 +372,4 @@ # we don't delete these anymore results['NULLINSTFLUX'] = 0; - ''' @@ -547,114 +400,30 @@ self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - - ''' - Populates the DetectionCalib table for this OTA - ''' - def populateDetectionCalibTableUpdateInsert(self, ota): - - tableName = "Detection_" + ota + ''' + Populates the SkinnyObject table for this OTA + ''' + def populateSkinnyObjectTable(self, ota): + + tableName = "SkinnyObject_" + 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 SkinnyObject" + try: self.scratchDb.execute(sql) + except: pass # insert all detections into table sql = "INSERT INTO " + tableName + " ( \ objID \ - ,detectID \ ,ippObjID \ - ,ippDetectID \ - ,filterID \ ,surveyID \ ) \ SELECT \ objID \ - ,detectID \ ,ippObjID \ - ,ippDetectID \ - ,filterID \ ,surveyID \ FROM Detection_" + ota -# self.scratchDb.execute(sql) - - # 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.objID = b.objID AND a.detectID = b.detectID" - self.scratchDb.execute(sql) - self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - - ''' - Populates the DetectionCalib table for this OTA - ''' - def populateDetectionCalibTable(self, ota): - - # target table name: - tableName = "Detection_" + ota - # drop then re-create table - #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",imageID) - self.logger.infoPair("obtained externID",externID) - - # insert all detections into table - 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 update data for detectionCalib") - self.logger.infoPair("sql: ", sql) - raise - - ''' - Populates the Detection table for this OTA - ''' - def populateObjectCalColorTable(self, ota): - - tableName = "ObjectCalColor_" + ota - - # drop then re-create table - self.scratchDb.dropTable(tableName) - sql = "CREATE TABLE " + tableName + " LIKE ObjectCalColor" - try: self.scratchDb.execute(sql) - except: pass - - # insert all detections into table - sql = "INSERT INTO " + tableName + " ( \ - objID \ - ,ippObjID \ - ,filterID \ - ) \ - SELECT \ - objID \ - ,ippObjID \ - ,filterID \ - FROM Detection_" + ota - self.scratchDb.execute(sql) - - self.scratchDb.updateAllRows(tableName, "calibModNum", str(self.calibModNum)) + self.scratchDb.execute(sql) + self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) @@ -685,6 +454,5 @@ # store sourceID/imageID combo in Db so DVO can look up later - if not self.useFullTables: - self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID']) + self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID']) # store these for later @@ -734,6 +502,5 @@ # now run DVO code to get all IDs - if not self.useFullTables: - if not self.getIDsFromDVO(): return False + if not self.getIDsFromDVO(): return False # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of @@ -774,12 +541,23 @@ imageID = self.scratchDb.getImageIDFromExternID(externID) self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID) + sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.detectID = b.detectID, \ - a.objID = b.objID, \ - a.infoFlag = b.flags << 45 | a.infoFlag \ + a.objID = b.objID, \ + a.detectID = b.detectID, \ + a.ippObjID = b.ippObjID, \ + a.ra = b.ra, \ + a.dec_ = b.dec_, \ + a.raErr = b.raErr, \ + a.decErr = b.decErr, \ + a.zp = b.zp, \ + a.telluricExt = b.telluricExt, \ + a.airmass = b.airmass, \ + a.expTime = b.expTime, \ + a.infoFlag2 = (b.flags << 13) | a.infoFlag2 \ WHERE a.ippDetectID = b.ippDetectID \ AND b.imageID = " + str(imageID) + # XXX need to add dvoRegion + self.scratchDb.execute(sql) @@ -788,9 +566,9 @@ ''' def populatePspsTablesChip(self, chipname, x, y, results, tables): - # XXX EAM NOTE: drop tables (Detection_, SkinnyObject_, DetectionCalib_, Detection_) here so + # XXX EAM NOTE: drop tables Detection_* here so # they do not polute the db? # XXX or put an explicit drop at the end of the loop? - #self.logger.infoTitle("Processing " + chipname) + # self.logger.infoTitle("Processing " + chipname) # this is a bit crude: if the chip is not present, this test will fail and the chip # will be (correctly) skipped. would be better to carry that information explicitly ("chip is missing") @@ -853,9 +631,4 @@ self.scratchDb.execute(sql) - #self.logger.info("updated imagedata") - - self.populateDetectionCalibTable(chipname) - #self.logger.info("updated detectioncalibtable") - # add these to list of tables to export later self.tablesToExport.append("ImageMeta_" + chipname) @@ -865,7 +638,6 @@ self.logger.info("export Detection") - tables.append("Detection_" + chipname) - self.logger.info("updated detectioncalibtable") + self.logger.info("updated detection") self.validChips.append(chipname) @@ -1024,5 +796,4 @@ return super(DetectionBatch, self).importIppTables(columns, regex) - ''' Overriding this method. Use regex to trim off, eg _XY33 extension @@ -1030,3 +801,2 @@ def exportPspsTablesToFits(self, regex="(.*)"): return super(DetectionBatch, self).exportPspsTablesToFits("([a-zA-Z]+)") - Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py (revision 37129) @@ -50,12 +50,11 @@ self.regionsIngestedButOutOfDate = [] - # we have (still) 3 modes of ingest from dvo cpt/cps/cpm files - # 1) dvograbber (useFullTables == FALSE), + # we USED TO have 3 modes of ingest from dvo cpt/cps/cpm files + # 1) dvograbber (useStilts == TRUE, useFullTables == FALSE), # 2) jython/stilts (useStilts == TRUE, useFullTables == TRUE) - # 3) dvopsps (useStilts == FALSE, useFullTables == TRUE) + # 3) dvopsps (useStilts == FALSE, useFullTables == FALSE) # my goal (EAM, 2013/01/31) is to migrate to only dvopsps - self.useStilts = 0 - self.scratchDb.setUseFullTables(True) + self.scratchDb.setDvoTableNames() # decide if we are using the right DVO for this scratchDb @@ -105,4 +104,8 @@ if not self.correctDvo: return + if (self.skychunk.dvoLocation == 'none'): + print "do not load Photcodes for place-holder DVO " + self.skychunk.dvoLocation + return False + # check if we have up-to-date version path = self.skychunk.dvoLocation + "/Photcodes.dat" @@ -156,4 +159,8 @@ if not self.correctDvo: return False + if (self.skychunk.dvoLocation == 'none'): + print "do not load SkyTable for place-holder DVO " + self.skychunk.dvoLocation + return False + path = self.skychunk.dvoLocation + "/SkyTable.fits" if self.scratchDb.alreadyImportedThisDvoTable(path): @@ -164,35 +171,27 @@ self.logger.infoPair("DVO SkyTable.fits file", "NOT up-to-date") - if self.useStilts: - self.importFits( - path, - "R_MIN R_MAX D_MIN D_MAX INDEX NAME", - self.scratchDb.dvoSkyTable) - else: - - # create dvoSkyTable - sql = "drop TABLE dvoSkyTable" - try: - self.scratchDb.execute(sql) - except: pass - - # create dvoSkyTable - sql = "CREATE TABLE dvoSkyTable (R_MIN REAL, R_MAX REAL, D_MIN REAL, D_MAX REAL, INDEX_ INT, NAME CHAR(18))" - self.scratchDb.execute(sql) - - # TODO path to DVO prog hardcoded temporarily - cmd = "dvopsps skytable" - cmd += " -dbhost " + self.scratchDb.dbHost - cmd += " -dbname " + self.scratchDb.dbName - cmd += " -dbuser " + self.scratchDb.dbUser - cmd += " -dbpass " + self.scratchDb.dbPass - cmd += " -D CATDIR " + self.skychunk.dvoLocation - - self.logger.infoPair("Running dvopsps", cmd) - p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE) - p.wait() - if p.returncode != 0: - self.logger.errorPair("dvopsps failed on SkyTable.fits for ", self.skychunk.dvoLocation) - raise + # drop dvoSkyTable (if it exists) + sql = "drop TABLE dvoSkyTable" + try: self.scratchDb.execute(sql) + except: pass + + # create dvoSkyTable + sql = "CREATE TABLE dvoSkyTable (R_MIN REAL, R_MAX REAL, D_MIN REAL, D_MAX REAL, INDEX_ INT, NAME CHAR(18))" + self.scratchDb.execute(sql) + + # TODO path to DVO prog hardcoded temporarily + cmd = "dvopsps skytable" + cmd += " -dbhost " + self.scratchDb.dbHost + cmd += " -dbname " + self.scratchDb.dbName + cmd += " -dbuser " + self.scratchDb.dbUser + cmd += " -dbpass " + self.scratchDb.dbPass + cmd += " -D CATDIR " + self.skychunk.dvoLocation + + self.logger.infoPair("Running dvopsps", cmd) + p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE) + p.wait() + if p.returncode != 0: + self.logger.errorPair("dvopsps failed on SkyTable.fits for ", self.skychunk.dvoLocation) + raise self.logger.infoPair("Adding index to", self.scratchDb.dvoSkyTable) Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/dvoobjects.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/dvoobjects.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/dvoobjects.py (revision 37129) @@ -27,23 +27,4 @@ self.ingestFileTypes = ['cpt', 'cps'] self.loadPhotcodes() - - ''' - Ingests object files for this region - ''' - def ingestRegion(self, region): - - self.logger.infoPair("Ingesting objects for", region) - - for fileType in self.ingestFileTypes: - - path = self.skychunk.dvoLocation + "/" + region + "." + fileType - tableName = self.scratchDb.getDbFriendlyTableName(region + "." + fileType) - - self.importFits(path, "*", tableName) - - self.scratchDb.setImportedThisDvoTable(path, region) - - return True - ''' Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython (revision 37129) @@ -13,5 +13,9 @@ set JARDIR = $datadir/jar + +# XXX if you want to run a local test, use . for JYDIR +# set JYDIR = . set JYDIR = $datadir/jython + setenv IPPTOPSPS_DATA $datadir/ipptopsps @@ -45,6 +49,3 @@ shift -# XXX if you want to run a test on a local file, use this line: -# $JAVA -Xbootclasspath/a:$CLASSPATH -jar $JYTHON $classfile $* - $JAVA -Xbootclasspath/a:$CLASSPATH -jar $JYTHON $JYDIR/$classfile $* Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopsps.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopsps.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopsps.py (revision 37129) @@ -143,8 +143,8 @@ def checkClientStatus(self): - print "update client" + print "updateClient (add this process to client table)" self.ippToPspsDb.updateClient(self.config.programName, self.HOST, self.PID) - print "check killed" + print "check if isKilled (has someone killed me?)" if self.ippToPspsDb.isKilled(self.HOST, self.PID): self.exitProgram("killed via Db") @@ -152,5 +152,5 @@ # this loop pauses the process if we have no skychunk or we have set it to pause firstTimeIn = True - print "refresh skychunk" + print "refreshSkychunk" while not self.refreshSkychunk() or self.ippToPspsDb.isPaused(self.HOST, self.PID): Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopspsdb.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopspsdb.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/ipptopspsdb.py (revision 37129) @@ -786,5 +786,5 @@ and not self.consistentlyFailed(batchType, stageID)): - self.logger.infoPair("heather:","passed logic") + # self.logger.infoPair("heather:","passed logic") sql = "INSERT INTO batch ( \ @@ -803,5 +803,6 @@ '" + self.skychunk.name + "' \ )" - self.logger.infoPair("heather:","sql") + + # self.logger.infoPair("heather:",sql) self.execute(sql) @@ -816,4 +817,6 @@ except: self.logger.exception("Unable to get batch ID") + self.logger.infoPair("sql:",sql) + batchID = -1 @@ -1017,4 +1020,5 @@ response = raw_input(" * Name for new skychunk, or existing skychunk to edit? ") if response == "": return + response = response.strip() self.skychunk.name = response @@ -1056,7 +1060,10 @@ question = question + "? " response = raw_input(question) + response = response.strip() + print "response: '" + response + "'" if response != "": if type.find("varchar") != -1 or type.find("timestamp") != -1: quotes = "'" else: quotes = "" + # print "UPDATE skychunk SET " + field + " = " + quotes + response + quotes + " WHERE name = '" + self.skychunk.name + "'" self.execute("UPDATE skychunk SET " + field + " = " + quotes + response + quotes + " WHERE name = '" + self.skychunk.name + "'") @@ -1130,4 +1137,5 @@ self.skychunk.batchTypes = [] try: + # print "sql: " + sql rs = self.executeQuery(sql) @@ -1176,7 +1184,36 @@ if rs.getInt(26) == 1: self.skychunk.parallel = True self.skychunk.P2_smf_version = rs.getString(27) + # options: "use_new", "not_reproc", "use_original" + self.skychunk.ST_cmf_version = rs.getString(28) + # options: not used? + + # NOTE : the rs (ResultSet) class is somewhat annoying and finicky esp about timestamp values + # for example, rs.getTimestamp(N) fails silently if the value is not a valid time. this is + # also true to getString() on the same column. Docs for the ResultSet class can be found here: + # http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSet.html?is-external=true + + # rsmd = rs.getMetaData() + # print "got metadata" + # print "number of columns: " + str(rsmd.getColumnCount()) + # print "label: " + rsmd.getColumnLabel(25) + # print "label: " + rsmd.getColumnLabel(29) + # print "name: " + rsmd.getColumnName(25) + # print "name: " + rsmd.getColumnName(29) + # print "typename: " + rsmd.getColumnTypeName(25) + # print "typename: " + rsmd.getColumnTypeName(29) + + # epochStamp = rs.getTimestamp(16) + # print "epoch: " + epochStamp.toString() + + # print "label: " + rsmd.getColumnLabel(16) + # print "name: " + rsmd.getColumnName(16) + # print "typename: " + rsmd.getColumnTypeName(16) + self.skychunk.trange_start = rs.getString(29) + # options: "YYYY-MM-DD,HH:MM:SS" or "0000-00-00 00:00:00" + self.skychunk.trange_end = rs.getString(30) + # options: "YYYY-MM-DD,HH:MM:SS" or "0000-00-00 00:00:00" if self.skychunk.parallel: print "USING parallel" @@ -1621,5 +1658,7 @@ pass - self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)") + # XXX EAM : 20140727: for now this is not temporary (for debugging) + # self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)") + self.execute("CREATE TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)") for row in rows: @@ -1702,5 +1741,7 @@ try: rs = self.executeQuery(sql) - while (rs.next()): ids.append(rs.getInt(1)) + while (rs.next()): + ids.append(rs.getInt(1)) + print "stage_id: " + str(rs.getInt(1)) except: self.logger.errorPair("Can't get items in this box", sql) Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py (revision 37129) @@ -160,60 +160,14 @@ self.logger.infoPair(batchType + " items found in this box", "%d" % len(ids)) - useFullTables = 0 if batchType != "OB": - - if (self.dvoDetections.useStilts): - # look in DVO for this box (with extra border) - self.logger.infoPair("Querying DVO for this sky area", "") - - # XXXX EAM : This code should not be used anymore - boxSize = 0.0 - self.dvoDetections.setSkyAreaAsBox(boxDim['RA'], boxDim['DEC'], boxSize) - #self.dvoDetections.setSkyArea() - sizeToBeIngested = self.dvoDetections.getDiskSizeOfRegionsToBeIngested() - if sizeToBeIngested == 0.0: smfsPerGB = 999999999 - else: smfsPerGB = len(ids)/sizeToBeIngested - self.logger.infoPair("DVO to be ingested", "%7.1e GB" % sizeToBeIngested) - self.logger.infoPair("smfs-per-GB", "%.1f" % smfsPerGB) - - # should do we pre-ingest stuff from DVO? - # NOTE EAM : this skychunk loads the dvo detections into the mysql db - # XXXX EAM : this should happen for both P2 and Stack detection - # XXXX EAM : in parallel model, this should happen for all P2 & Stack batches - # - if (batchType == 'P2' or batchType == 'ST' or batchType == 'DF' or batchType == 'FW') and smfsPerGB > 30: - if not self.dvoDetections.sync(): - self.logger.errorPair("Could not sync DVO with MySQL", "skipping") - continue - - useFullTables = 1 - else: - # XXX EAM : this is not currently optional - # (it is the only path). either remove the - # non-native ingest code or make it an - # option if we are using the native - # loader, always use it need to work out a - # good boundary / region strategy in - # coordination with impact of mysql - # insertion - if not self.ippToPspsDb.isBoxIngested(boxId, self.scratchDb.dbHost): - if not self.dvoDetections.nativeIngestDetections(boxId, boxDim): - self.logger.error("Unable to ingest detections with nativeIngest") - return False - - useFullTables = 1 - - ## else: - ## # if we are using the native loader, always use it - ## # need to work out a good boundary / region strategy in coordination with - ## # impact of mysql insertion - ## self.dvoObjects.nativeIngestObjects(boxDim['RA'], boxDim['DEC'], boxSizeSansBorder) - ## useFullTables = 1 + if not self.ippToPspsDb.isBoxIngested(boxId, self.scratchDb.dbHost): + if not self.dvoDetections.nativeIngestDetections(boxId, boxDim): + self.logger.error("Unable to ingest detections with nativeIngest") + return False ''' NOTE EAM : "TheseItems" refers to each of the stack CMFs, camera SMFs, or object batches ''' - self.logger.infoBool("Using pre-ingested DVO data?", useFullTables) - if not self.processTheseItems(batchType, ids, useFullTables): + if not self.processTheseItems(batchType, ids): abort = True break @@ -228,5 +182,5 @@ Actually loops through items and processes them ''' - def processTheseItems(self, batchType, ids, useFullTables): + def processTheseItems(self, batchType, ids): self.logger.infoPair("%s items queued" % batchType, "%d" % len(ids)) @@ -262,6 +216,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "ST": self.logger.infoPair("defining ST batch for sky_id:",id) @@ -273,6 +226,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "OB": @@ -284,6 +236,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "DF": @@ -295,6 +246,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "DO": @@ -306,6 +256,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "FW": @@ -317,6 +266,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) elif batchType == "FO": @@ -328,6 +276,5 @@ self.scratchDb, id, - batchID, - useFullTables) + batchID) batch.run() Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37129) @@ -108,4 +108,13 @@ except: print "failed to kill connection: ", connectionID + + def listTables(self): + + sql = "show tables" + rs = self.executeQuery(sql) + while (rs.next()): + tableName = rs.getString(1) + print "table: " + tableName + rs.close() ''' Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/objectbatch.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/objectbatch.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/objectbatch.py (revision 37129) @@ -15,4 +15,5 @@ from scratchdb import ScratchDb from dvoobjects import DvoObjects +from sqlUtility import sqlUtility import logging.config @@ -37,6 +38,5 @@ scratchDb, dvoID, - batchID, - useFullTables): + batchID): super(ObjectBatch, self).__init__( @@ -50,9 +50,7 @@ batchID, "OB", - None, - 1) + None) try: - ## XX pass in the existing connection to scratchDb self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb) except: @@ -74,8 +72,5 @@ self.region = self.scratchDb.getRegionNameFromThisDvoIndex(self.id) self.ippToPspsDb.insertObjectMeta(self.batchID, self.region) - if True: - self.dvoObjects.nativeIngestRegion(self.region) - else: - self.dvoObjects.ingestRegion(self.region) + self.dvoObjects.nativeIngestRegion(self.region) cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") @@ -90,7 +85,8 @@ def alterPspsTables(self): + # XXX EAM 2014.07.24 : why is this no longer needed? # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using `` - # self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double") - # self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double") + # self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double") + # self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double") return True @@ -101,5 +97,6 @@ def indexIppTables(self): - self.logger.infoPair("Creating indexes on", "IPP tables") + # since dvopsps is now used, no action is needed here + # self.logger.infoPair("Creating indexes on", "IPP tables") return True @@ -109,4 +106,76 @@ ''' def updateMeanObjectFromCps(self, cpsTable): + + # list of all filters PSPS is interested in + # XXX EAM : 2014.07.24 : this list should probably be in a config file somewhere + interestedFilters = ['g', 'r', 'i', 'z', 'y'] + + filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters) + + # get a count of the available filters + filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable() + + self.logger.infoPair("Available filters in Photcodes", filters) + + # the 'code' now defines the order in the cps file that the mags are listed for a given filter + self.logger.infoPair("Adding magnitudes from", "cps table") + for filter in filters: + + filterID = self.scratchDb.getFilterID(filter[1]) + + # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) + # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags + # XXX EAM : 20140724 this manipulation is no longer needed : [grizy]Flags is now 4 byte (was 8 byte!) + + # set the MeanObject fields based largely on dvopsps cps fields: + + # XXX EAM 20140724 : filterCount is meant to match + # Nsecfilt, but is potentially not determined correctly. + # use a call to a dvo-native command which knows how to + # find Nsecfilt (or save in the db with dvopsps) + + # the math below depends on filterCount = Nsecfilt and MeanObject.row being 1 counting but cps being 0 counting? + # cps.row has a count of MeanObject.row * Nsecfilt + Nfilter + # " + 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]) + ")) \ + SET \ + MeanObject." + filter[1] + "QfPerfect = -999 \ + ,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) " + + + self.logger.info(sql) + self.scratchDb.execute(sql) + + # 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) + + ''' + Inserts stuff for all mags + ''' + def updateObjectThinFromCps(self, cpsTable): # list of all filters PSPS is interested in @@ -127,66 +196,5 @@ filterID = self.scratchDb.getFilterID(filter[1]) - # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) - # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags - - sql = "UPDATE MeanObject JOIN \ - " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ - SET \ - MeanObject." + filter[1] + "QfPerfect = -999 \ - ,MeanObject." + filter[1] + "MeanPSFMag = MAG \ - ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \ - ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \ - ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ - ,MeanObject." + filter[1] + "nIncPSFMag = -999 \ - ,MeanObject." + filter[1] + "MeanPSFMagStd = -999 \ - ,MeanObject." + filter[1] + "MinPSFMag = -999 \ - ,MeanObject." + filter[1] + "MaxPSFMag = -999 \ - ,MeanObject." + filter[1] + "nIncKronMag = -999 \ - ,MeanObject." + filter[1] + "MeanKronMag = -999 \ - ,MeanObject." + filter[1] + "MeanKronMagStd = -999 \ - ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \ - ,MeanObject." + filter[1] + "MeanApMagErr = MAG_ERR \ - ,MeanObject." + filter[1] + "MeanApMagStd = MAG_STDEV \ - ,MeanObject." + filter[1] + "nIncApMag = -999 \ - ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) " - - - self.logger.info(sql) - self.scratchDb.execute(sql) - - # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12) - 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) - ''' - Inserts stuff for all mags - ''' - def updateObjectThinFromCps(self, cpsTable): - - # list of all filters PSPS is interested in - interestedFilters = ['g', 'r', 'i', 'z', 'y'] - - filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters) - - # get a count of the available filters - filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable() - # filterCount = len(filters) - - self.logger.infoPair("Available filters in Photcodes", filters) - - # the 'code' now defines the order in the cps file that the mags are listed for a given filter - self.logger.infoPair("Adding magnitudes from", "cps table") - for filter in filters: - - filterID = self.scratchDb.getFilterID(filter[1]) - - # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) - # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags - + # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table? sql = "UPDATE ObjectThin JOIN \ " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ @@ -196,4 +204,5 @@ self.scratchDb.execute(sql) + # XXX this does not seem like a good thing to leave in SQL self.logger.infoPair("Calculating nDetections from", "n[filters]") for filter in filters: @@ -203,9 +212,8 @@ WHERE n" + filter[1] + " != -999" self.scratchDb.execute(sql) - - ''' give objectName to objectThin + XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps? ''' def updateObjName(self): @@ -281,10 +289,4 @@ return False - - - - - - ''' Populates the Object table @@ -296,6 +298,8 @@ if False: + # XXX EAM 20140724 : this is probably wrong : flux measurements can be 0.0 or negative: please review self.logger.infoPair("setting to null > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR") sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 " + self.exitProgram("review this code" + sql) try: @@ -315,62 +319,39 @@ - self.logger.infoPair("Populating", "ThinObject") - self.logger.infoPair("Inserting objects from", "cpt file") - - # note `` around dec here, as this is a reserved word in MySQL - sql = "INSERT IGNORE INTO ObjectThin (\ - objID \ - ,gcobjID \ - ,ippObjID \ - ,surveyID \ - ,skyCellID \ - ,randomID \ - ,batchID \ - ,dvoRegionID \ - ,dataRelease \ - ,objInfoFlag \ - ,qualityFlag \ - ,consistencyFlag \ - ,raStack \ - ,decStack \ - ,raStackErr \ - ,decStackErr \ - ,raMean \ - ,decMean \ - ,raMeanErr \ - ,decMeanErr \ - ,raMeanStd \ - ,decMeanStd \ - ,nStackObjectRows \ - ,nStackDetections \ - ,nDetections \ - ) \ - SELECT \ - EXT_ID \ - , -999 \ - ,CAT_ID*1000000000 + OBJ_ID \ - ," + str(self.surveyID) + " \ - , -999 \ - , FLOOR(RAND()*9223372036854775807) \ - , " + str(self.batchID) + "\ - , -999 \ - , " + str(self.skychunk.dataRelease) + "\ - ,FLAGS \ - ,FLAGS >> 24 & 0xFF \ - , 0 \ - , -999 \ - , -999 \ - , -999 \ - , -999 \ - ,RA \ - ,DEC_ \ - ,RA_ERR \ - ,DEC_ERR \ - , -999 \ - , -999 \ - ,0 \ - ,0 \ - ,0 \ - FROM " + cptTableName + self.logger.info("Populating ThinObject") + self.logger.info("Inserting objects from cpt file") + + # note "dec" is a reserved word in MySQL + # XXX EAM 20140724 : do not use INGORE unless we discover unavoidable problems... + # INSERT IGNORE INTO ObjectThin + + sqlLine = sqlUtility("INSERT INTO ObjectThin (") + + sqlLine.group("objID", "EXT_ID") + # sqlLine.group("gcobjID", "EXT_ID_GC") # drop this one? + sqlLine.group("ippObjID", "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly + sqlLine.group("surveyID", str(self.surveyID)) + sqlLine.group("skyCellID", "-999") # XXX This does not make sense + sqlLine.group("randomID", "FLOOR(RAND()*9223372036854775807)") # XXX where does this number come from?? + sqlLine.group("batchID", str(self.batchID)) + sqlLine.group("dvoRegionID", "DVO_REGION") # XXX add to dvopsps output for cpt + sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) + sqlLine.group("objInfoFlag", "FLAGS") + 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("raMeanStd", "-999") # XXX I do not calculate this, use chisq? + sqlLine.group("decMeanStd", "-999") + sqlLine.group("nStackObjectRows", "0") # XXX I need to add / define this in dvopsps + sqlLine.group("nStackDetections", "0") + sqlLine.group("nDetections", "0") + sql = sqlLine.make(") SELECT ", " FROM " + cptTableName) try: @@ -390,9 +371,9 @@ self.updateObjName() - self.logger.infoPair("update ObjectThin from ","cps table") self.updateObjectThinFromCps(cpsTableName) + # XXX EAM 20140724 : is this necessary?? #objects can have out of range ra dec in dvo - need to find and kill them at the end Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/queue.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/queue.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/queue.py (revision 37129) @@ -45,5 +45,5 @@ try: - print "why doesn't this work" + print "does this work?" self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb) except: Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37129) @@ -51,12 +51,8 @@ Sets whether we are using normal or pre-ingested DVO tables ''' - def setUseFullTables(self, useFull): - - if useFull: - self.dvoImagesTable = "dvoImagesFull" - self.dvoDetectionTable = "dvoDetectionFull" - else: - self.dvoImagesTable = "dvoImages" - self.dvoDetectionTable = "dvoDetection" + def setDvoTableNames(self): + + self.dvoImagesTable = "dvoImagesFull" + self.dvoDetectionTable = "dvoDetectionFull" self.logger.debugPair("Using DVO images table", self.dvoImagesTable) @@ -233,18 +229,29 @@ def alreadyImportedThisDvoTable(self, path): + print "***** alreadyImportedThisDvoTable *****" + print "path: " + path + fileStat = os.stat(path) + print "path: " + path sql = "SELECT COUNT(*) FROM " + self.dvoDoneTable + " WHERE path = '" + path + "' AND modifiedDate = " + str(fileStat.st_mtime) - - try: - rs = self.executeQuery(sql) + print "sql: " + sql + + try: + rs = self.executeQuery(sql) + print "ran sql: " + sql rs.first() if rs.getInt(1) > 0: + print "***** DID import previously *****" self.logger.debugPair("Already imported up-to-date version of", path) return True else: + print "***** did NOT import previously *****" return False except: + print "***** exception in alreadyImportedThisDvoTable *****" self.logger.exception("Unable to check whether this DVO table has been imported") + + print "***** done with alreadyImportedThisDvoTable *****" @@ -322,4 +329,21 @@ ''' def isCorrectDvo(self, path): + + sql = "show tables" + try: + rs = self.executeQuery(sql) + haveDone = False + while (rs.next()): + tableName = rs.getString(1) + # print "table: " + tableName + if tableName == self.dvoDoneTable: + haveDone = True + + rs.close() + if not haveDone: + print "dvoDone not yet build" + return False + except: + print "failed to run query: " + sql sql = "SELECT path FROM " + self.dvoDoneTable + " WHERE path LIKE '%SkyTable.fits'" @@ -415,8 +439,8 @@ dvoImagesTable = "dvoImages" - dvoDetectionTable = "dvoDetections" + dvoDetectionTable = "dvoDetection" # drop and create Images table - self.logger.debugPair("Creating DVO table", dvoImagesTable) + self.logger.infoPair("Creating DVO table", dvoImagesTable) sql = "DROP TABLE " + dvoImagesTable @@ -438,25 +462,27 @@ # now detection table - self.logger.debugPair("Creating DVO table", dvoDetectionTable) + self.logger.infoPair("Dropping DVO table", dvoDetectionTable) sql = "DROP TABLE " + dvoDetectionTable try: self.execute(sql) - except: pass - - self.logger.debugPair("Creating DVO table", "dvoDetection") + except: + print "problem dropping DVO " + dvoDetectionTable + pass + + self.logger.infoPair("Creating DVO table", "dvoDetection") sql = "CREATE TABLE dvoDetection ( \ - imageID INT, \ - ippDetectID BIGINT, \ + objID BIGINT, \ detectID BIGINT, \ ippObjID BIGINT, \ - objID BIGINT, \ - flags INT, \ - zp FLOAT, \ - zpErr FLOAT, \ - airMass FLOAT, \ - expTime FLOAT, \ + ippDetectID BIGINT, \ + imageID INT, \ ra DOUBLE, \ dec_ DOUBLE, \ raErr FLOAT, \ decErr FLOAT, \ + zp FLOAT, \ + telluricExt FLOAT, \ + airmass FLOAT, \ + expTime FLOAT, \ + flags INT, \ PRIMARY KEY (imageID, ippDetectID), \ KEY (objID, detectID) \ @@ -466,4 +492,5 @@ except: self.logger.error("Unable to create DVO detection database table") + self.sys.exit(1) # XXX EAM : for the parallel mode, dvoDetection requires duplicate objID values! @@ -550,8 +577,11 @@ self.logger.infoPair("Dropping table", self.dvoSkyTable) self.dropTable(self.dvoSkyTable) - self.logger.infoPair("Dropping table", self.dvoDetectionTable) - self.dropTable(self.dvoDetectionTable) self.logger.infoPair("Dropping table", self.dvoDoneTable) self.dropTable(self.dvoDoneTable) + + # blow away existing dvoDetection table & re-crate + if not self.resetDvoDetectionTable(): + self.logger.errorPair("Unable to reset scratch table", "DvoDetectionFull") + return False # create dvoDone table @@ -564,9 +594,4 @@ self.changeEngineToInnoDB(self.dvoDoneTable) - - # blow away existing dvoDetection table & re-crate - if not self.resetDvoDetectionTable(): - self.logger.errorPair("Unable to reset scratch table", "DvoDetectionFull") - return False return True Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py (revision 37129) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py (revision 37129) @@ -0,0 +1,46 @@ +#!/usr/bin/env jython + +import os +import re +import shutil + +# SQL Utility class, for manipulating SQL string commands +class sqlUtility(object): + + # Constructor + def __init__(self,startString): + self.fields = [] + self.values = [] + self.startString = startString + return + + def group(self,field,value): + self.fields.append(field) + self.values.append(value) + return + + def make(self,middleString,endString): + + output = self.startString + + for i in range(len(self.fields)): + output += self.fields[i] + if (i < len(self.fields) - 1): + output += ", " + + output += middleString + + for i in range(len(self.values)): + output += self.values[i] + if (i < len(self.values) - 1): + output += ", " + + output += endString + + return output + + # when we do a sql insert, we have a command that looks like this: + # INSERT INTO Table (field1, field2,...) OPERATION (value1, value2,...) + # I want to make it easy to group field1 and value1, etc + + Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37129) @@ -39,6 +39,5 @@ scratchDb, skyID, - batchID, - useFullTables): + batchID): super(StackBatch, self).__init__( @@ -52,11 +51,6 @@ batchID, "ST", - "", - #gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID), - useFullTables) - - # self.printline = 0 - # # self.testprint() -# self.logger.infoPair("did I get", "here"); + None) + self.stackType = "DEEP_STACK" # TODO ## = stuff I commented out for pv2 stacks @@ -215,5 +209,5 @@ self.scratchDb.dropTable("StackObject") self.logger.infoPair("dropping table:","StackModelFit") - + self.scratchDb.dropTable("SkinnyObject") self.scratchDb.dropTable("StackModelFit") self.logger.infoPair("dropping table:","StackApFlx") @@ -262,10 +256,4 @@ # insert what we know about this stack batch into the stack table self.ippToPspsDb.insertStackMeta(self.batchID, "x" , self.stackType) - - # insert sourceID/imageID combo so DVO can look it up - ##if not self.useFullTables: - ## self.scratchDb.insertNewDvoExternID(self.header['SOURCEID'], self.header['IMAGEID']) - - # self.testprint() # dump stuff to log @@ -785,5 +773,26 @@ self.logger.infoPair("Deleting", "entries with StackObject.objID = 0") # response = raw_input("deleted objID is 0 ") - + ''' + 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)) + ''' @@ -1189,8 +1198,10 @@ self.logger.infoPair("starting","populatePspsTables"); self.skipBatch = False - if not self.useFullTables: - if not self.getIDsFromDVO(): - return False - #do some basics first + + # get the IDs from the DVO tables + if not self.getIDsFromDVO(): + return False + + # do some basics first: # populate stack meta per filter # insert objid/stackdetectid per filter to each table @@ -1307,4 +1318,6 @@ ##if self.totalDetections < 1: + #self.logger.infoPair("populating","SkinnyObject") + self.populateSkinnyObject() ##self.logger.error("No detections to publish") @@ -1314,5 +1327,5 @@ self.tablesToExport.append("StackModelFit") self.tablesToExport.append("StackApFlx") - + self.tablesToExport.append("SkinnyObject") self.logger.infoPair("finishing","populatePspsTables"); return True Index: branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py (revision 37129) +++ branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py (revision 37129) @@ -0,0 +1,43 @@ +#!/usr/bin/env jython + +# A test script for testing new jython code snippets + +# import time +import sys +# import os +# import math +# import logging.config + +from sqlUtility import sqlUtility + +# testCode class +class testCode(object): + + # Constructor + def __init__(self, argv): + super(testCode, self).__init__(argv) + + ''' + Main processing loop. + Tiled area is defined in 'skychunk' and looks for available stage_ids for each tile and writes them to ippToPsps database ready for processing + by one or more loading clients that may be running on any host + ''' + def run(self): + sqlLine = sqlUtility("INSERT INTO Table (") + + sqlLine.group("field1", "value1") + sqlLine.group("field2", "value2") + sqlLine.group("field3", "value3") + + sql = sqlLine.make(") VALUES (", ")") + + print "sql: " + sql + +''' +Start of program. +''' +try: + testcode = testCode(sys.argv) + testcode.run() + testcode.exitProgram("completed") +except: pass Index: branches/eam_branches/ipp-20140717/ippToPsps/test/fulltest.sh =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/test/fulltest.sh (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/test/fulltest.sh (revision 37129) @@ -171,29 +171,4 @@ endif -# `name` -# `datastore_product` -# `datastore_type` -# `datastore_publish` -# `dvo_label` -# `dvo_location` -# `min_ra` -# `max_ra` -# `min_dec` -# `max_dec` -# `box_size` -# `base_path` -# `data_release` -# `delete_local` -# `delete_datastore` -# `delete_dxlayer` -# `epoch` -# `survey` -# `psps_survey` -# `queue_P2` -# `queue_ST` -# `queue_OB` -# `active` -# `parallel` - # this is poor : we need to queue something in order to generate an init batch # this is because a skychunk carries the info about the datastore used to publish @@ -203,32 +178,37 @@ echo " ---- make skychunk for IN batches ----" - ippjython queue.py -test edit < initbatch.dat # skychunk name + echo "test_ds " >> initbatch.dat # datastore_product + echo "IPP_PSPS " >> initbatch.dat # datastore_type + echo "0 " >> initbatch.dat # datastore_publish + echo "none " >> initbatch.dat # dvo_label + echo "none " >> initbatch.dat # dvo_location + echo "0 " >> initbatch.dat # min_ra + echo "0 " >> initbatch.dat # max_ra + echo "0 " >> initbatch.dat # min_dec + echo "0 " >> initbatch.dat # max_dec + echo "2 " >> initbatch.dat # box_size + echo "none " >> initbatch.dat # base_path + echo "0 " >> initbatch.dat # data_release + echo "0 " >> initbatch.dat # delete_local + echo "1 " >> initbatch.dat # delete_datastore + echo "1 " >> initbatch.dat # delete_dxlayer + echo "2012-12-10 00:00:00.0 " >> initbatch.dat # epoch + echo "3PI " >> initbatch.dat # survey + echo "3PI " >> initbatch.dat # psps_survey + echo "0 " >> initbatch.dat # queue_P2 + echo "0 " >> initbatch.dat # queue_ST + echo "0 " >> initbatch.dat # queue_OB + echo "0 " >> initbatch.dat # queue_DF + echo "0 " >> initbatch.dat # queue_DO + echo "0 " >> initbatch.dat # queue_FW + echo "0 " >> initbatch.dat # queue_FO + echo "1 " >> initbatch.dat # active + echo "0 " >> initbatch.dat # parallel + echo "use_new " >> initbatch.dat # P2_smf_version + echo "any " >> initbatch.dat # ST_cmf_version + echo "2000-01-01 00:00:00.0 " >> initbatch.dat # trange_start + echo "2020-01-01 00:00:00.0 " >> initbatch.dat # trange_end + cat initbatch.dat | ippjython queue.py -test edit echo "" @@ -244,32 +224,37 @@ echo " ---- queue P2 batches ----" - ippjython queue.py -test edit < cambatch.dat # skychunk name + echo "test_ds " >> cambatch.dat # datastore_product + echo "IPP_PSPS " >> cambatch.dat # datastore_type + echo "0 " >> cambatch.dat # datastore_publish + echo "catdir.cam " >> cambatch.dat # dvo_label + echo "$OUTDIR/catdir.cam " >> cambatch.dat # dvo_location + echo "9 " >> cambatch.dat # min_ra + echo "11 " >> cambatch.dat # max_ra + echo "19 " >> cambatch.dat # min_dec + echo "21 " >> cambatch.dat # max_dec + echo "2 " >> cambatch.dat # box_size + echo "$OUTDIR " >> cambatch.dat # base_path + echo "0 " >> cambatch.dat # data_release + echo "0 " >> cambatch.dat # delete_local + echo "1 " >> cambatch.dat # delete_datastore + echo "1 " >> cambatch.dat # delete_dxlayer + echo "2012-12-10 00:00:00.0 " >> cambatch.dat # epoch + echo "3PI " >> cambatch.dat # survey + echo "3PI " >> cambatch.dat # psps_survey + echo "1 " >> cambatch.dat # queue_P2 + echo "0 " >> cambatch.dat # queue_ST + echo "0 " >> cambatch.dat # queue_OB + echo "0 " >> cambatch.dat # queue_DF + echo "0 " >> cambatch.dat # queue_DO + echo "0 " >> cambatch.dat # queue_FW + echo "0 " >> cambatch.dat # queue_FO + echo "1 " >> cambatch.dat # active + echo "0 " >> cambatch.dat # parallel + echo "use_new " >> cambatch.dat # P2_smf_version + echo "any " >> cambatch.dat # ST_cmf_version + echo "2000-01-01 00:00:00.0 " >> cambatch.dat # trange_start + echo "2020-01-01 00:00:00.0 " >> cambatch.dat # trange_end + cat cambatch.dat | ippjython queue.py -test edit endif @@ -290,32 +275,37 @@ set stackqueuename = $queuename\_stk endif - ippjython queue.py -test edit < stackbatch.dat # skychunk name + echo "test_ds " >> stackbatch.dat # datastore_product + echo "IPP_PSPS " >> stackbatch.dat # datastore_type + echo "0 " >> stackbatch.dat # datastore_publish + echo "catdir.stk " >> stackbatch.dat # dvo_label + echo "$OUTDIR/catdir.stk " >> stackbatch.dat # dvo_location + echo "9 " >> stackbatch.dat # min_ra + echo "11 " >> stackbatch.dat # max_ra + echo "19 " >> stackbatch.dat # min_dec + echo "21 " >> stackbatch.dat # max_dec + echo "2 " >> stackbatch.dat # box_size + echo "$OUTDIR " >> stackbatch.dat # base_path + echo "0 " >> stackbatch.dat # data_release + echo "0 " >> stackbatch.dat # delete_local + echo "1 " >> stackbatch.dat # delete_datastore + echo "1 " >> stackbatch.dat # delete_dxlayer + echo "2012-12-10 00:00:00.0 " >> stackbatch.dat # epoch + echo "3PI " >> stackbatch.dat # survey + echo "3PI " >> stackbatch.dat # psps_survey + echo "0 " >> stackbatch.dat # queue_P2 + echo "1 " >> stackbatch.dat # queue_ST + echo "0 " >> stackbatch.dat # queue_OB + echo "0 " >> stackbatch.dat # queue_DF + echo "0 " >> stackbatch.dat # queue_DO + echo "0 " >> stackbatch.dat # queue_FW + echo "0 " >> stackbatch.dat # queue_FO + echo "1 " >> stackbatch.dat # active + echo "0 " >> stackbatch.dat # parallel + echo "use_new " >> stackbatch.dat # P2_smf_version + echo "any " >> stackbatch.dat # ST_cmf_version + echo "2000-01-01 00:00:00.0 " >> stackbatch.dat # trange_start + echo "2020-01-01 00:00:00.0 " >> stackbatch.dat # trange_end + cat stackbatch.dat | ippjython queue.py -test edit endif @@ -339,32 +329,37 @@ set objectqueuename = $queuename\_obj endif - ippjython queue.py -test edit < objectbatch.dat # skychunk name + echo "test_ds " >> objectbatch.dat # datastore_product + echo "IPP_PSPS " >> objectbatch.dat # datastore_type + echo "0 " >> objectbatch.dat # datastore_publish + echo "catdir.cam " >> objectbatch.dat # dvo_label + echo "$OUTDIR/catdir.cam " >> objectbatch.dat # dvo_location + echo "9 " >> objectbatch.dat # min_ra + echo "11 " >> objectbatch.dat # max_ra + echo "19 " >> objectbatch.dat # min_dec + echo "21 " >> objectbatch.dat # max_dec + echo "2 " >> objectbatch.dat # box_size + echo "$OUTDIR " >> objectbatch.dat # base_path + echo "0 " >> objectbatch.dat # data_release + echo "0 " >> objectbatch.dat # delete_local + echo "1 " >> objectbatch.dat # delete_datastore + echo "1 " >> objectbatch.dat # delete_dxlayer + echo "2012-12-10 00:00:00.0 " >> objectbatch.dat # epoch + echo "3PI " >> objectbatch.dat # survey + echo "3PI " >> objectbatch.dat # psps_survey + echo "0 " >> objectbatch.dat # queue_P2 + echo "0 " >> objectbatch.dat # queue_ST + echo "1 " >> objectbatch.dat # queue_OB + echo "0 " >> objectbatch.dat # queue_DF + echo "0 " >> objectbatch.dat # queue_DO + echo "0 " >> objectbatch.dat # queue_FW + echo "0 " >> objectbatch.dat # queue_FO + echo "1 " >> objectbatch.dat # active + echo "0 " >> objectbatch.dat # parallel + echo "use_new " >> objectbatch.dat # P2_smf_version + echo "any " >> objectbatch.dat # ST_cmf_version + echo "2000-01-01 00:00:00.0 " >> objectbatch.dat # trange_start + echo "2020-01-01 00:00:00.0 " >> objectbatch.dat # trange_end + cat objectbatch.dat | ippjython queue.py -test edit once endif Index: branches/eam_branches/ipp-20140717/ippToPsps/test/mkgpc1data.dvo =================================================================== --- branches/eam_branches/ipp-20140717/ippToPsps/test/mkgpc1data.dvo (revision 37128) +++ branches/eam_branches/ipp-20140717/ippToPsps/test/mkgpc1data.dvo (revision 37129) @@ -39,6 +39,8 @@ # mkcatdir.cam PS1_V3 PS1_V4 # mkcatdir.stk PS1_V3 PS1_V4 - mkcatdir.cam PS1_V4 PS1_V4 - mkcatdir.stk PS1_V4 PS1_V4 + # mkcatdir.cam PS1_V4 PS1_V4 + # mkcatdir.stk PS1_V4 PS1_V4 + mkcatdir.cam PS1_V5 PS1_V5 + mkcatdir.stk PS1_V5 PS1_V5 insert.stack.set end @@ -203,20 +205,23 @@ end - if ($i == 5) + if (0 && ($i == 5)) echo "NOTE: adding image $i 2x to DVO, but without detections : this simulates a failure in LAP dvo" - echo addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -image -dup-images - exec addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -image -dup-images + echo addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -image -dup-images + exec addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -image -dup-images end - if ($i == 7) + if (0 && ($i == 7)) echo "NOTE: adding detections from image $i 2x to DVO, but with wrong photcode and duplicate image ID : this simulates a failure in LAP dvo" - echo addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -dup-images -image-id-override 7 -photcode 2MASS_J - exec addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -dup-images -image-id-override 7 -photcode 2MASS_J + echo addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -dup-images -image-id-override 7 -photcode 2MASS_J + exec addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass -dup-images -image-id-override 7 -photcode 2MASS_J end - echo addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass - exec addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass - end + echo addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass + exec addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass + end + + echo relphot -D CATDIR $catdir -region 8 12 18 22 -images g,r,i,z,y -update + exec relphot -D CATDIR $catdir -region 8 12 18 22 -images g,r,i,z,y -update if ($PARALLEL) @@ -264,4 +269,6 @@ $offset = $word:2 + ## this is not generating photcodes recognized as stack by relphot + mkinput $offset $rawfile exec mkcmf $rawfile $cmffile -extname SkyChip -date 2008/1/1 -time $i\:00:00 -radec $RA $DEC -type $1 -imageID $ID -sourceID 1 @@ -271,6 +278,10 @@ exec fits_insert $cmffile tmp.hdr - exec addstar -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass - end + echo addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass + exec addstar -D SKY_DEPTH 4 -D CATDIR $catdir -D CAMERA simtest $cmffile -D CATFORMAT $2 -quick-airmass + end + + echo relphot -D CATDIR $catdir.stk -region 8 12 18 22 -images g,r,i,z,y -update + exec relphot -D CATDIR $catdir.stk -region 8 12 18 22 -images g,r,i,z,y -update if ($PARALLEL)