Index: trunk/ippToPsps/jython/detectionbatch.py =================================================================== --- trunk/ippToPsps/jython/detectionbatch.py (revision 31951) +++ trunk/ippToPsps/jython/detectionbatch.py (revision 31965) @@ -533,7 +533,8 @@ Updates provided table with DVO IDs from DVO table ''' - def updateDvoIDs(self, table, sourceID, imageID): - - self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = " + imageID) + def updateDvoIDs(self, table, sourceID, externID): + + imageID = self.scratchDb.getImageIDFromExternID(sourceID, externID) + self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID) sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetection + " AS b SET \ a.ippObjID = b.ippObjID, \ @@ -617,5 +618,5 @@ # store sourceID/imageID combo in Db so DVO can look up later if not self.useFullTables: - self.scratchDb.insertNewDvoImage(header['SOURCEID'], header['IMAGEID']) + self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID']) # store these for later Index: trunk/ippToPsps/jython/dvoToMySQL.py =================================================================== --- trunk/ippToPsps/jython/dvoToMySQL.py (revision 31951) +++ trunk/ippToPsps/jython/dvoToMySQL.py (revision 31965) @@ -10,5 +10,5 @@ from subprocess import call, PIPE, Popen -from gpc1db import Gpc1Db +from pslogger import PSLogger from scratchdb import ScratchDb @@ -27,19 +27,24 @@ ''' - def __init__(self, logger, pathToDvo): + def __init__(self, logger): # set up logging self.logger = logger - self.pathToDvo = pathToDvo - self.logger.debug("DvoToMySql class constructor") - - self.logger.debug("Important DVO database at " + self.pathToDvo) + self.logger.infoSeparator() + self.logger.info("Started dvoToMySql") + self.dvoMetaTable = "dvoMetaFull" + self.dvoDetectionTable = "dvoDetectionFull" # open config - doc = ElementTree(file="config.xml") + self.doc = ElementTree(file="config.xml") + self.dvoLocation = self.doc.find("dvo/location").text + + # some log stuff + self.logger.infoPair("Using DVO meta table", self.dvoMetaTable) + self.logger.infoPair("Using DVO detection table", self.dvoDetectionTable) + self.logger.infoPair("Importing DVO database at", self.dvoLocation) # create database objects - self.scratchDb = ScratchDb(logger) - self.gpc1Db = Gpc1Db(self.logger) + self.scratchDb = ScratchDb(logger, self.doc, 1) # create DVO tables @@ -47,16 +52,19 @@ # import Images.dat table + self.logger.infoPair("Deleting from table", self.dvoMetaTable) sql = "DELETE FROM dvoMetaFull" - self.scratchDb.stmt.execute(sql) - - imagesTableName = self.importFits(self.pathToDvo, + self.scratchDb.execute(sql) + + imagesTableName = self.importFits(self.dvoLocation, "", "Images.dat", - "IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR") - self.scratchDb.createIndex(imagesTableName, "IMAGE_ID") + "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR") + self.scratchDb.createIndex(imagesTableName, "EXTERN_ID") # insert into dvoMetaFull + # NB what we and smf files call IMAGE_ID, DVO calls EXTERN_ID. We are sticking + # with the smf name self.logger.info("Inserting all image meta data into database") - sql = "INSERT INTO dvoMetaNew ( \ + sql = "INSERT INTO dvoMetaFull ( \ sourceID, \ imageID, \ @@ -71,5 +79,5 @@ PHOTCODE \ FROM " + imagesTableName - self.scratchDb.stmt.execute(sql) + self.scratchDb.execute(sql) subdirs = ['n0000'] @@ -77,5 +85,5 @@ for subdir in subdirs: - files = glob.glob(pathToDvo + "/" + subdir + "/*.cpm") + files = glob.glob(self.dvoLocation + "/" + subdir + "/*.cpm") #files = ['0247.06', '0244.06', '0244.10'] @@ -90,5 +98,5 @@ # import cpm table and index - cpmTableName = self.importFits(self.pathToDvo, + cpmTableName = self.importFits(self.dvoLocation, subdir, file + ".cpm", @@ -98,5 +106,5 @@ # import cpt table and index - cptTableName = self.importFits(self.pathToDvo, + cptTableName = self.importFits(self.dvoLocation, subdir, file + ".cpt", @@ -107,22 +115,22 @@ # shove SOURCE_IDs into measurement table self.logger.info("Adding SOURCE_IDs into measurements table") - sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (SOURCE_ID SMALLINT)" - self.scratchDb.stmt.execute(sql) - sql = "UPDATE "+cpmTableName+" AS a, "+imagesTableName+" AS b \ + sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (SOURCE_ID SMALLINT)" + self.scratchDb.execute(sql) + sql = "UPDATE " + cpmTableName + " AS a, " + imagesTableName + " AS b \ SET a.SOURCE_ID = b.SOURCE_ID \ WHERE a.IMAGE_ID = b.IMAGE_ID" - self.scratchDb.stmt.execute(sql) + self.scratchDb.execute(sql) # shove PSPS objID in measurement table self.logger.info("Adding PSPS objID into measurements table") sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)" - self.scratchDb.stmt.execute(sql) + self.scratchDb.execute(sql) sql = "UPDATE "+cpmTableName+" AS a, "+cptTableName+" AS b \ SET a.PSPS_OBJ_ID = b.EXT_ID \ WHERE a.CAT_ID = b.CAT_ID \ AND a.OBJ_ID = b.OBJ_ID" - self.scratchDb.stmt.execute(sql) - - self.logger.info("Putting everything into dvoDetectionFull table") + self.scratchDb.execute(sql) + + self.logger.infoPair("Populating", self.dvoDetectionTable) sql = "INSERT IGNORE INTO dvoDetectionFull (\ sourceID \ @@ -142,5 +150,5 @@ ,DB_FLAGS \ FROM " + cpmTableName - self.scratchDb.stmt.execute(sql) + self.scratchDb.execute(sql) # now drop what we don't need @@ -173,5 +181,6 @@ tableName = tableName.replace('.', '_') - self.logger.info("Attempting to import tables from '" + fullPath + "' to '" + tableName + "'") + self.logger.infoPair("Importing tables from file", fullPath) + self.logger.infoPair("Writing to database table", tableName) tables = stilts.treads(fullPath) @@ -195,15 +204,16 @@ count = count + 1 - self.logger.info("Done. Imported %d tables" % count) + self.logger.infoPair("Finished importing", "%d tables" % count) return tableName logging.config.fileConfig("logging.conf") -logger = logging.getLogger("dvotomysql") -logger.info("Starting") - -dvoToMySql = DvoToMySql(logger, "/data/ipp005.0/gpc1/catdirs/MD04.merges/MD04.merge") -#dvoToMySql = DvoToMySql(logger, "/export/ippc00.1/rhenders/MD04.merge") - -logger.info("Program complete") - +logging.setLoggerClass(PSLogger) +logger = logging.getLogger("dvoToMySQLLog") +logger.setLevel(logging.INFO) + + +dvoToMySql = DvoToMySql(logger) + +logger.infoPair("Program...", "complete") + Index: trunk/ippToPsps/jython/scratchdb.py =================================================================== --- trunk/ippToPsps/jython/scratchdb.py (revision 31951) +++ trunk/ippToPsps/jython/scratchdb.py (revision 31965) @@ -52,12 +52,29 @@ return -1 + ''' + Gets imageID from extern ID + ''' + def getImageIDFromExternID(self, sourceID, externID): + + imageID = -1 + + sql = "SELECT imageID FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID) + try: + rs = self.executeQuery(sql) + rs.first() + imageID = rs.getInt(1) + except: + self.logger.exception("Unable to get imageID from dvo meta table using " + sql) + + return imageID + ''' Gets DVO image flags ''' - def getDvoImageFlags(self, sourceID, imageID): + def getDvoImageFlags(self, sourceID, externID): flags = 0 - sql = "SELECT flags FROM " + self.dvoMeta + " WHERE sourceID = %s AND imageID = %s" % (sourceID, imageID) + sql = "SELECT flags FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID) try: rs = self.executeQuery(sql) @@ -71,9 +88,9 @@ Gets photcode (aka photoCalID from dvo table) ''' - def getPhotoCalID(self, sourceID, imageID): + def getPhotoCalID(self, sourceID, externID): photcode = -1 - sql = "SELECT photcode FROM " + self.dvoMeta + " WHERE sourceID = %s AND imageID = %s" % (sourceID, imageID) + sql = "SELECT photcode FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID) try: rs = self.executeQuery(sql) @@ -96,12 +113,12 @@ Inserts a new sourceID/imageID combo into dvoMeta ''' - def insertNewDvoImage(self, sourceID, imageID): + def insertNewDvoExternID(self, sourceID, externID): sql = "INSERT INTO dvoMeta ( \ sourceID, \ - imageID \ + externID \ ) VALUES (\ " + str(sourceID) + ", \ - " + str(imageID) + " \ + " + str(externID) + " \ )" self.execute(sql) @@ -152,7 +169,8 @@ sourceID INT, \ imageID INT, \ + externID INT, \ flags INT, \ photcode INT, \ - PRIMARY KEY (sourceID, imageID) \ + PRIMARY KEY (sourceID, imageID, externID) \ )" @@ -172,7 +190,4 @@ PRIMARY KEY (sourceID, imageID, ippDetectID) \ )" - #INDEX (sourceID), \ - #INDEX (imageID), \ - #INDEX (ippDetectID) \ try: self.execute(sql) Index: trunk/ippToPsps/jython/stackbatch.py =================================================================== --- trunk/ippToPsps/jython/stackbatch.py (revision 31951) +++ trunk/ippToPsps/jython/stackbatch.py (revision 31965) @@ -88,5 +88,5 @@ # insert sourceID/imageID combo so DVO can look it up if not self.useFullTables: - self.scratchDb.insertNewDvoImage(self.header['SOURCEID'], self.header['IMAGEID']) + self.scratchDb.insertNewDvoExternID(self.header['SOURCEID'], self.header['IMAGEID']) ''' @@ -588,4 +588,5 @@ def updateDvoIDs(self, table): + imageID = self.scratchDb.getImageIDFromExternID(self.header['SOURCEID'], self.header['IMAGEID']) self.logger.debug("Updating table '" + table + "' with DVO IDs...") sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetection + " AS b SET \ @@ -595,5 +596,5 @@ WHERE a.ippDetectID = b.ippDetectID \ AND b.sourceID = " + self.header['SOURCEID'] + "\ - AND b.imageID = " + self.header['IMAGEID'] + AND b.imageID = " + str(imageID) self.scratchDb.execute(sql) Index: trunk/ippToPsps/src/Dvo.c =================================================================== --- trunk/ippToPsps/src/Dvo.c (revision 31951) +++ trunk/ippToPsps/src/Dvo.c (revision 31965) @@ -35,5 +35,5 @@ mysql_query(this->mysql, "DELETE FROM dvoDetection"); - mysql_query(this->mysql, "SELECT sourceID, imageID FROM dvoMeta"); + mysql_query(this->mysql, "SELECT sourceID, externID FROM dvoMeta"); MYSQL_RES* result = mysql_store_result(this->mysql); @@ -41,19 +41,19 @@ MYSQL_ROW row; char sql[500]; - int sourceID, imageID; + int sourceID, externID; while ((row = mysql_fetch_row(result))) { sourceID = atoi(row[0]); - imageID = atoi(row[1]); + externID = atoi(row[1]); this->logger->print(this->logger, MSG_INFO, "Dvo", "---------------------------------------------------------------------\n"); - this->logger->print(this->logger, MSG_INFO, "Dvo", "Getting skylist from DVO database for source ID = %d and image ID = %d\n", - sourceID, imageID); - skyList = dvoSkyListByExternID(this->dvoConfig, sourceID, imageID, &image); + this->logger->print(this->logger, MSG_INFO, "Dvo", "Getting skylist from DVO database for source ID = %d and extern ID = %d\n", + sourceID, externID); + skyList = dvoSkyListByExternID(this->dvoConfig, sourceID, externID, &image); if (!skyList) { this->logger->print(this->logger, MSG_ERROR, - "Dvo", "Could not find skylist for sourceId=%d and image ID = %d\n", - sourceID, imageID); + "Dvo", "Could not find skylist for sourceId=%d and extern ID = %d\n", + sourceID, externID); continue; @@ -61,9 +61,10 @@ sprintf(sql, - "UPDATE dvoMeta SET flags = %d, photcode = %d WHERE sourceID = %d AND imageID = %d", + "UPDATE dvoMeta SET imageID = %d,flags = %d, photcode = %d WHERE sourceID = %d AND externID = %d", + image->imageID, image->flags, image->photcode, sourceID, - imageID); + externID); mysql_query(this->mysql, sql); @@ -92,5 +93,5 @@ "INSERT INTO dvoDetection (sourceID, imageID, ippDetectID, detectID, ippObjID, objID, flags) VALUES (%d, %d, %u, %lu, %lu, %lu, %u)", sourceID, // sourceID - imageID, // imageID + image->imageID, // imageID dvoDetections[i].meas.detID, // ippDetectID dvoDetections[i].meas.extID, // detectID