Changeset 32337 for branches/eam_branches/ipp-20110710/ippToPsps/jython/dvoToMySQL.py

Timestamp:

Sep 6, 2011, 11:00:22 AM (15 years ago)

Author:

eugene

Message:

merge changes from trunk

Location:

branches/eam_branches/ipp-20110710/ippToPsps/jython

Files:

• . (modified) (1 prop)
• dvoToMySQL.py (modified) (9 diffs)

branches/eam_branches/ipp-20110710/ippToPsps/jython/dvoToMySQL.py

@@ -10 +10 @@
 from subprocess import call, PIPE, Popen
 
-from gpc1db import Gpc1Db
+from pslogger import PSLogger
 from scratchdb import ScratchDb
 
@@ -27 +27 @@
 
     '''
-    def __init__(self, logger, pathToDvo):
+    def __init__(self, logger, doc, RESETTABLES):
 
         # set up logging
         self.logger = logger
-        self.pathToDvo = pathToDvo
-        self.logger.debug("DvoToMySql class constructor")
-
-        self.logger.debug("Important DVO database at " + self.pathToDvo)
-
-        # open config
-        doc = ElementTree(file="config.xml")
-
-        # create database objects
-        self.scratchDb = ScratchDb(logger)
-        self.gpc1Db = Gpc1Db(self.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
-        #self.scratchDb.createDvoTables()
+        if RESETTABLES: self.scratchDb.resetDvoToMysqlTables()
 
         # import Images.dat table
-        sql = "DELETE FROM dvoMetaFull"
-        self.scratchDb.stmt.execute(sql)
-
-        imagesTableName = self.importFits(self.pathToDvo, 
+        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", 
-                "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
-        self.logger.info("Inserting all image meta data into database")
-        sql = "INSERT INTO dvoMetaNew ( \
+        # 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, \
@@ -71 +73 @@
                PHOTCODE \
                FROM " + imagesTableName
-        self.scratchDb.stmt.execute(sql)
+        self.scratchDb.execute(sql)
 
         subdirs = ['n0000']
@@ -77 +79 @@
         for subdir in subdirs:
 
-            files = glob.glob(pathToDvo + "/" + subdir + "/*.cpm")
+            files = glob.glob(self.dvoLocation + "/" + subdir + "/*.cpm")
 
             #files = ['0247.06', '0244.06', '0244.10']
@@ -85 +87 @@
                 # get just filename, without extension
                 file = os.path.basename(os.path.splitext(file)[0])
-                self.logger.info("---------------------------------------------: " + file)
+                self.logger.infoSeparator()
 
                 if self.scratchDb.alreadyImportedThisDvoTable(file): continue
 
                 # import cpm table and index
-                cpmTableName = self.importFits(self.pathToDvo, 
+                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.pathToDvo, 
+                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.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 \
+                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.stmt.execute(sql)
+                self.scratchDb.execute(sql)
 
                 # shove PSPS objID in measurement table
-                self.logger.info("Adding PSPS objID into measurements table")
+                self.logger.infoPair("Adding","PSPS objIDs")
                 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")
-                sql = "INSERT IGNORE INTO dvoDetectionFull (\
+                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 \
@@ -142 +150 @@
                        ,DB_FLAGS \
                        FROM " + cpmTableName
-                self.scratchDb.stmt.execute(sql)
+                try:
+                    self.scratchDb.execute(sql)
+                except:
+                    self.logger.error("FAILED: " + sql)
+                    return 
 
                 # now drop what we don't need
-                self.logger.info("Dropping tables")
+                self.logger.infoPair("Dropping table", cpmTableName)
                 self.scratchDb.dropTable(cpmTableName)
+                self.logger.infoPair("Dropping table", cptTableName)
                 self.scratchDb.dropTable(cptTableName)
        
@@ -173 +186 @@
       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)
@@ -180 +194 @@
       for table in tables:
 
-          self.logger.info("Reading IPP table " + table.name + " from FITS file")
+          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.info("Removing Infinity values from all columns")
+          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.info("Writing FITS table to database")
+          self.logger.infoPair("Writing FITS table to", "database")
           table.cmd_keepcols(columns).write(self.scratchDb.url + '#' + tableName)
           #except:
@@ -195 +209 @@
           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")
-
+
+'''
+Start of program.
+'''
+
+if len(sys.argv) > 1: CONFIG  = sys.argv[1]
+else:
+    print "** Usage: " + sys.argv[0] + " <configPath> [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")
+