Index: trunk/ippToPsps/jython/dvoToMySQL.py =================================================================== --- trunk/ippToPsps/jython/dvoToMySQL.py (revision 33731) +++ (revision ) @@ -1,242 +1,0 @@ -#!/usr/bin/env jython - -import stilts -import datetime -import re -import sys -import os -import logging -import glob -from subprocess import call, PIPE, Popen - -from pslogger import PSLogger -from scratchdb import ScratchDb - -from java.lang import * -from java.sql import * -from xml.etree.ElementTree import ElementTree, Element, tostring - - -''' -Class for pulling IDs from a DVO database and shoving in a MySQL database -''' -class DvoToMySql(object): - - ''' - Constructor - - ''' - def __init__(self, logger, doc, RESETTABLES): - - # set up logging - self.logger = logger - self.doc = doc - self.logger.infoSeparator() - self.dvoLocation = self.doc.find("dvo/location").text - - # create database object - self.scratchDb = ScratchDb(logger, self.doc, 1) - - # some log stuff - self.logger.infoPair("Started", "dvoToMySql") - self.logger.infoPair("Importing DVO database at", self.dvoLocation) - - # create DVO tables - if RESETTABLES: self.scratchDb.resetDvoToMysqlTables() - - # import Images.dat table - self.logger.infoPair("Deleting from table", self.scratchDb.dvoMetaTable) - sql = "DELETE FROM " + self.scratchDb.dvoMetaTable - self.scratchDb.execute(sql) - - imagesTableName = self.importFits(self.dvoLocation, - "", - "Images.dat", - "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.infoPair("Populating", "all image meta data") - sql = "INSERT INTO " + self.scratchDb.dvoMetaTable + " ( \ - sourceID, \ - imageID, \ - externID, \ - flags, \ - photcode \ - ) SELECT \ - SOURCE_ID, \ - IMAGE_ID, \ - EXTERN_ID, \ - FLAGS, \ - PHOTCODE \ - FROM " + imagesTableName - self.scratchDb.execute(sql) - - subdirs = ['n0000'] - - for subdir in subdirs: - - files = glob.glob(self.dvoLocation + "/" + subdir + "/*.cpm") - - #files = ['0247.06', '0244.06', '0244.10'] - - for file in files: - - # get just filename, without extension - file = os.path.basename(os.path.splitext(file)[0]) - self.logger.infoSeparator() - - if self.scratchDb.alreadyImportedThisDvoTable(file): continue - - # import cpm table and index - cpmTableName = self.importFits(self.dvoLocation, - subdir, - file + ".cpm", - "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS") - self.scratchDb.createIndex(cpmTableName, "IMAGE_ID") - self.scratchDb.createIndex(cpmTableName, "CAT_ID") - self.scratchDb.createIndex(cpmTableName, "OBJ_ID") - - # import cpt table and index - cptTableName = self.importFits(self.dvoLocation, - subdir, - file + ".cpt", - "OBJ_ID CAT_ID EXT_ID") - self.scratchDb.createIndex(cptTableName, "IMAGE_ID") - self.scratchDb.createIndex(cptTableName, "CAT_ID") - self.scratchDb.createIndex(cptTableName, "OBJ_ID") - - # shove SOURCE_IDs into measurement table - self.logger.infoPair("Adding", "SOURCE_IDs") - 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.execute(sql) - - # shove PSPS objID in measurement table - self.logger.infoPair("Adding","PSPS objIDs") - sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)" - 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.execute(sql) - - # NB we use an INSERT IGNORE here. This is because of a known issue where multiple DVO cpm - # files can include the same detection, with the same IMAGE_ID/DET_ID pairing, but different - # PSPS object IDs assigned in the corresponding cpt file. This is believed to be a chip-boundary - # issue within DVO. So, for now, we take the first IMAGE_ID/DET_ID detection we find, and ignore the rest - self.logger.infoPair("Populating", self.scratchDb.dvoDetectionTable) - sql = "INSERT IGNORE INTO " + self.scratchDb.dvoDetectionTable + " (\ - sourceID \ - ,imageID \ - ,ippDetectID \ - ,detectID \ - ,ippObjID \ - ,objID \ - ,flags \ - ) SELECT \ - SOURCE_ID \ - ,IMAGE_ID \ - ,DET_ID \ - ,EXT_ID \ - ,CAT_ID * 1000000000 + OBJ_ID \ - ,PSPS_OBJ_ID \ - ,DB_FLAGS \ - FROM " + cpmTableName - try: - self.scratchDb.execute(sql) - except: - self.logger.error("FAILED: " + sql) - return - - # now drop what we don't need - self.logger.infoPair("Dropping table", cpmTableName) - self.scratchDb.dropTable(cpmTableName) - self.logger.infoPair("Dropping table", cptTableName) - self.scratchDb.dropTable(cptTableName) - - self.scratchDb.setImportedThisDvoTable(file) - - self.scratchDb.dropTable(imagesTableName) - - - ''' - Destructor - ''' - def __del__(self): - - self.logger.debug("DvoToMySql destructor") - - ''' - Imports a FITS file. A regex filter lets you choose which tables to pull from the file - ''' - def importFits(self, path, subdir, file, columns): - - fullPath = path + "/" + subdir + "/" + file - - if len(subdir) < 1: tableName = file - else: tableName = subdir + "_" + file - - tableName = tableName.replace('.', '_') - - self.logger.infoPair("Importing tables from file", fullPath) - self.logger.infoPair("Writing to database table", tableName) - - tables = stilts.treads(fullPath) - - count = 0 - for table in tables: - - self.logger.infoPair("Reading IPP table", table.name) - table = stilts.tpipe(table, cmd='explodeall') - - # IPP FITS files are littered with infinities, so remove these - self.logger.infoPair("Removing", "infinity values") - table = stilts.tpipe(table, cmd='replaceval -Infinity null *') - table = stilts.tpipe(table, cmd='replaceval Infinity null *') - - #try: - self.logger.infoPair("Writing FITS table to", "database") - table.cmd_keepcols(columns).write(self.scratchDb.url + '#' + tableName) - #except: - # self.logger.exception(" Problem writing table '" + table.name + "' to the database") - count = count + 1 - - self.logger.infoPair("Finished importing", "%d tables" % count) - - return tableName - - -''' -Start of program. -''' - -if len(sys.argv) > 1: CONFIG = sys.argv[1] -else: - print "** Usage: " + sys.argv[0] + " [reset]" - sys.exit(1) - -# open config file -configDoc = ElementTree(file=CONFIG) - -logging.setLoggerClass(PSLogger) -logger = logging.getLogger("dvoToMySQL") -logger.setup(configDoc, "dvoToMySQL", 0, 1) - -RESETTABLES = 0 - -if len(sys.argv) > 2 and sys.argv[2] == "reset": - response = raw_input("* Are you ABSOLUTELY sure you want to recreate the DVO tables (y/n)? ") - if response == "y": RESETTABLES = 1 - else: sys.exit(1) - -dvoToMySql = DvoToMySql(logger, configDoc, RESETTABLES) - -logger.infoPair("Program...", "complete") -