Changeset 35417 for trunk/ippToPsps/jython/scratchdb.py

Timestamp:

Apr 19, 2013, 4:35:54 PM (13 years ago)

Author:

eugene

Message:

use MEMORY engine for dvoDetectionFull; add error handline for dvopsps; check mysql version; remove some test verbosity; check for image existence in dvoImages; skip bad batches but do not exit; fix precision issues; add -realgpc option (part of -test mode); log times in milliseconds

Location:

trunk/ippToPsps

Files:

• . (modified) (1 prop)
• jython/scratchdb.py (modified) (6 diffs)

trunk/ippToPsps/jython/scratchdb.py

@@ -29 +29 @@
             dbType = "localdatabase"
 
-        print "trying to connect: ", dbType
+        # print "trying to connect: ", dbType
 
         dbName = config.getDbName(dbType)
@@ -38 +38 @@
         self.dvoSkyTable = "dvoSkyTable"
         self.dvoPhotcodesTable = "dvoPhotcodes"
+        self.memoryTableSize = config.getDbMemory(dbType)
 
     '''
@@ -368 +369 @@
 
     '''
+    Checks whether the given chipID is in the DVO Image table
+    '''
+    def haveThisChip(self, imageID, sourceID):
+
+        sql = "SELECT EXTERN_ID FROM " \
+            + self.dvoImagesTable + \
+            " WHERE EXTERN_ID = " + str(imageID) + \
+            " AND SOURCE_ID = " + str(sourceID)
+
+        try:
+            rs = self.executeQuery(sql)
+            
+            if not rs:
+                print "missing result set for imageID query"
+                raise
+
+            # if no returned rows, return an error
+            if not rs.next():
+                print "DVO lacks imageID ", imageID
+                print "sql: ", sql
+                return False
+
+            if rs.getInt(1) == int(imageID):
+                # print "DVO has imageID ", imageID
+                return True
+
+            else:
+                print "DVO has wrong imageID? ", imageID
+                print "result was ", rs.getInt(1)
+                print "sql: ", sql
+                raise
+
+        except:
+            print "DVO imageID query failed ", imageID
+            print "sql: ", sql
+            raise
+
+        # how can I reach this?
+        return True
+
+    '''
     Creates a table for for ID matching
     '''
@@ -409 +451 @@
                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 +505 @@
     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)
+
+       # we need a big enough memory table for the incoming detections
+       print "requested memory in gigs: ", self.memoryTableSize
+       tableSize = int(self.memoryTableSize) * 1024 * 1024 * 1024
+       sql = "set @@max_heap_table_size = " + str(tableSize)
+       print "setting max heap size: ", sql
+       try: self.execute(sql)
+       except: 
+           self.logger.errorPair("Unable to set max MEMORY table size for ", self.dvoDetectionTable)
+           return False
+
+       # 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 +565 @@
        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