Changeset 35352 for branches/eam_branches/ipp-20130307/ippToPsps/jython/scratchdb.py

Timestamp:

Apr 3, 2013, 12:52:33 PM (13 years ago)

Author:

eugene

Message:

changed dvoDetectionFull mysql engine to 'MEMORY' to improve ingest speed

handle errrors in calls to dvopsps (only in detectionbatch.py)

removed a lot of excess verbosity used for debugging / testing

determine the mysql major version number via mysql query

added test option -realgpc to use test ipptopsps databases with a real gpc1 dataset

add version-dependent call to 'set session old_alter_table' to allow
the ALTER IGNORE statements to work on mysql v5.5 machines (like our
desktops).

raise an exception in case the load infile operations fail

remove unneeded 'killLastConnectionID'

extend logging time to milliseconds

allow an exposure to be skipped without aborting loader

check that a P2 image exists in the dvoImage table (otherwise skip the chip)

change the mysql floating point types for the dvoDetection table to use FLOAT/DOUBLE instead of REAL/FLOAT

remove the old test for valid astrometry of a chip (NASTRO >= 50)

this test was crude and not really the right test. it was also used
to verify that an image should / should not be included. now we check
against dvoImages to see if the chip should be loaded or not (DVO
rejects input images for bad astrometry)

skip an exposure if too many detections have been rejected. this is a sign of a problem and should require more attention

File:

• branches/eam_branches/ipp-20130307/ippToPsps/jython/scratchdb.py (modified) (5 diffs)

branches/eam_branches/ipp-20130307/ippToPsps/jython/scratchdb.py

@@ -29 +29 @@
             dbType = "localdatabase"
 
-        print "trying to connect: ", dbType
+        # print "trying to connect: ", dbType
 
         dbName = config.getDbName(dbType)
@@ -368 +368 @@
 
     '''
+    Checks whether the given chipID is in the DVO Image table
+    '''
+    def haveThisChip(self, imageID):
+
+        sql = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE IMAGE_ID = " + str(imageID)
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            if rs.getInt(1) == int(imageID):
+                # print "DVO has imageID ", imageID
+                return True
+            else:
+                print "DVO lacks imageID ", imageID
+                print "result was ", rs.getInt(1)
+                print "sql: ", sql
+                return False
+        except:
+            print "DVO imageID query failed ", imageID
+            print "sql: ", sql
+            return False
+
+        return True
+
+    '''
     Creates a table for for ID matching
     '''
@@ -409 +434 @@
                objID BIGINT, \
                flags INT, \
-               zp REAL, \
-               zpErr REAL, \
-               airMass REAL, \
-               expTime REAL, \
-               ra FLOAT, \
-               dec_ FLOAT, \
-               raErr REAL, \
-               decErr REAL, \
+               zp FLOAT, \
+               zpErr FLOAT, \
+               airMass FLOAT, \
+               expTime FLOAT, \
+               ra DOUBLE, \
+               dec_ DOUBLE, \
+               raErr FLOAT, \
+               decErr FLOAT, \
                PRIMARY KEY (imageID, ippDetectID), \
                KEY (objID, detectID) \
@@ -463 +488 @@
     Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
     '''
+    def resetDvoDetectionTable(self):
+
+       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
+       self.dropTable(self.dvoDetectionTable)
+
+       # create detections table
+       self.logger.infoPair("Creating table", self.dvoDetectionTable)
+       sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
+       try: self.execute(sql)
+       except:
+           self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
+           return False
+
+       # XXX changing the dvoDetectionFull table to MEMORY
+       # self.changeEngineToInnoDB(self.dvoDetectionTable)
+
+       # set engine to memory
+       sql = "ALTER TABLE " + self.dvoDetectionTable + " ENGINE = MEMORY"
+       try: self.execute(sql)
+       except: 
+           self.logger.errorPair("Unable to set engine to MEMORY for ", self.dvoDetectionTable)
+           return False
+
+       return True
+
+    '''
+    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
+    '''
     def resetAllDvoTables(self):
 
@@ -485 +538 @@
        self.changeEngineToInnoDB(self.dvoDoneTable)
 
-       # create detections table
-       self.logger.infoPair("Creating table", self.dvoDetectionTable)
-       sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
-       try: self.execute(sql)
-       except:
-           self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
+       # blow away existing dvoDetection table & re-crate
+       if not self.resetDvoDetectionTable():
+           self.logger.errorPair("Unable to reset scratch table", "DvoDetectionFull")
            return False
-       self.changeEngineToInnoDB(self.dvoDetectionTable)
-
-       # add fileID column
-       sql = "ALTER TABLE " + self.dvoDetectionTable + " ADD fileID INT NOT NULL"
-       try: self.execute(sql)
-       except: 
-           self.logger.errorPair("Unable to add fileID column to ", self.dvoDetectionTable)
-           return False
-
-       # add an index to the fileID column
-       self.createIndex(self.dvoDetectionTable, "fileID")
-
-       # XXX : this should be based on choice of useStilts (which is set in dvo.py and must be moved)
-       if (False):
-           # now add a delete cascading foreign key constraint on fileID
-           sql = "ALTER TABLE dvoDetectionFull \
-              ADD CONSTRAINT fk_fileID \
-              FOREIGN KEY (fileID) \
-              REFERENCES dvoDone(id) \
-              ON DELETE CASCADE"
-           try: self.execute(sql)
-           except: 
-               self.logger.errorPair("Unable to create foreign key on", self.dvoDoneTable)
-               return False
 
        return True