Changeset 32337 for branches/eam_branches/ipp-20110710/ippToPsps/jython/scratchdb.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)
• scratchdb.py (modified) (12 diffs)

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

@@ -23 +23 @@
 
         if useFull:
-            self.dvoMeta = "dvoMetaNew"
-            self.dvoDetection = "dvoDetectionFull"
+            self.dvoMetaTable = "dvoMetaFull"
+            self.dvoDetectionTable = "dvoDetectionFull"
+            self.dvoDoneTable = "dvoDone"
         else:
-            self.dvoMeta = "dvoMeta"
-            self.dvoDetection = "dvoDetection"
-
-        self.logger.debug("ScratchDb constructor, using DVO tables: " + self.dvoMeta + " and " + self.dvoDetection)
+            self.dvoMetaTable = "dvoMeta"
+            self.dvoDetectionTable = "dvoDetection"
+
+        self.logger.debugPair("Using DVO meta table", self.dvoMetaTable)
+        self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)
 
     '''
@@ -37 +39 @@
 
         self.logger.debug("ScratchDb destructor")
+
+
+    '''
+    Creates a stored procedure for psfLikelihood. This uses an implementation of the complimentary error function taken from Numerical Recipes in C.
+    '''
+    def createPsfLikelihoodFunction(self):
+        sql = "DELIMITER $$ \
+               DROP FUNCTION IF EXISTS `psfLikelihood` $$ \
+               CREATE FUNCTION `psfLikelihood`(x REAL) RETURNS REAL \
+               DETERMINISTIC \
+               COMMENT 'Returns the psfLikelihood using the complimentary error function with fractional error everywhere less than 1.2 x 10-7. From Numerical Recipes in C' \
+               BEGIN \
+               DECLARE ans REAL; \
+               DECLARE t REAL; \
+               DECLARE z REAL; \
+               SET x = ABS(x) / 1.4142135623731; \
+               SET z=ABS(x); \
+               SET t=1.0 / (1.0 + 0.5*z); \
+               SET ans = t * EXP(-z*z-1.26551223+t*(1.00002368+t*(.37409196+t*(.09678418+t*(-.18628806+t*(.27886807+t*(-1.13520398+t*(1.48851587+t*(-.82215223+t*.17087277))))))))); \
+               IF x < 0.0 THEN \
+               SET ans = 2.0 - ans; \
+               END IF; \
+               RETURN ans; \
+               END $$ \
+               DELIMITER ;"
+
+        try:
+            self.execute(sql)
+            self.logger.debugPair("Installed stored procedure", "psfLikelihood")
+            return True
+        except:
+            self.logger.errorPair("Could not install stored procedure", "psfLikelihood")
+            return False
 
     '''
@@ -47 +82 @@
             rs = self.executeQuery(sql)
             rs.first()
-            return rs.getInt(1)
-        except:
-            self.logger.exception("No survey ID found for this survey: '" + self.survey + "'")
-            return -1
+            surveyID = rs.getInt(1)
+        except:
+            self.logger.errorPair("No survey ID found for survey", name)
+            surveyID = -1
+
+        return surveyID
+
+    '''
+    Gets filter ID for this filter name
+    '''
+    def getFilterID(self, name):
+     
+        sql = "SELECT filterID FROM Filter WHERE filterType = '" + name + "'"
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            filterID = rs.getInt(1)
+        except:
+            self.logger.errorPair("No filter ID found for filter", name)
+            filterID = -1
+
+        return filterID
+
+    ''' 
+    Gets imageID from extern ID 
+    ''' 
+    def getImageIDFromExternID(self, sourceID, externID): 
+               
+        imageID = -1 
+               
+        sql = "SELECT imageID FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID) 
+        try: 
+            rs = self.executeQuery(sql)   
+            rs.first() 
+            imageID = rs.getInt(1) 
+        except: 
+            self.logger.errorPair("Unable to get imageID from dvo using", sql) 
+              
+        return imageID 
 
     '''
@@ -59 +129 @@
         flags = 0
 
-        sql = "SELECT flags FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
+        sql = "SELECT flags FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
         try:
             rs = self.executeQuery(sql)  
@@ -65 +135 @@
             flags = rs.getInt(1)
         except:
-            self.logger.exception("Unable to get flags from dvo table using " + sql)
+            self.logger.errorPair("Unable to get flags from dvo using", sql)
         return flags
 
     '''
-    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)
+    Gets photcode (aka photoCalID from dvo table)
+    '''
+    def getPhotoCalID(self, sourceID, externID):
+
+        photcode = -1
+
+        sql = "SELECT photcode FROM " + self.dvoMetaTable + " 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 photcode (aka photoCalID from dvo table)
-    '''
-    def getPhotoCalID(self, sourceID, externID):
-
-        photcode = -1
-
-        sql = "SELECT photcode FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
-        try:
-            rs = self.executeQuery(sql)  
-            rs.first()
             photcode = rs.getInt(1)
         except:
-            self.logger.exception("Unable to get photcode from dvo table with: " + sql)
+            self.logger.errorPair("Unable to get photcode from dvo with", sql)
 
         return photcode
@@ -113 +166 @@
     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(imageID) + "    \
+               " + str(externID) + "   \
                )"
         self.execute(sql)
@@ -135 +186 @@
         
     '''
+    Checks whether the astrometric solution is ok for this chip
+    TODO the value of 50 for numAstroRef is hardcoded here, but this should be temporary anyway
+    '''
+    def astrometricSolutionOK(self, ota):
+
+        sql = "SELECT numAstroRef FROM ImageMeta_" + ota
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            if rs.getInt(1) < 50:
+                self.logger.debug("Bad astrometric solution for",  ota)
+                return False
+            else:
+                return True
+        except:
+            self.logger.debug("Unable to check astrometric solution")
+
+        return True
+
+    '''
     Have we already imported this DVO table?
     '''
@@ -145 +217 @@
             rs.first()
             if rs.getInt(1) > 0:
-                self.logger.error("DVO tables " + name + " have already been imported")
+                self.logger.errorPair("Already imported DVO tables for",  name)
                 return True
             else:
@@ -158 +230 @@
     def createDvoTables(self):
 
-        self.logger.infoPair("Creating DVO table", "dvoMeta")
+        self.logger.debugPair("Creating DVO table", "dvoMeta")
 
         sql = "DROP TABLE dvoMeta"
@@ -174 +246 @@
                flags INT, \
                photcode INT, \
-               PRIMARY KEY (sourceID, imageID) \
+               PRIMARY KEY (sourceID, imageID, externID) \
                )"
 
@@ -181 +253 @@
             self.logger.error("Unable to create DVO meta-data database table")
 
-        self.logger.infoPair("Creating DVO table", "dvoDetection")
+        self.logger.debugPair("Creating DVO table", "dvoDetection")
         sql = "CREATE TABLE dvoDetection ( \
                sourceID INT, \
@@ -192 +264 @@
                PRIMARY KEY (sourceID, imageID, ippDetectID) \
                )"
-               #INDEX (sourceID), \
-               #INDEX (imageID), \
-               #INDEX (ippDetectID) \
 
         try: self.execute(sql)
         except: 
             self.logger.error("Unable to create DVO detection database table")
+
+
+    '''
+    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
+    '''
+    def resetDvoToMysqlTables(self):
+
+       self.logger.infoPair("Dropping table", self.dvoMetaTable)
+       self.dropTable(self.dvoMetaTable)
+       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
+       self.dropTable(self.dvoDetectionTable)
+       self.logger.infoPair("Dropping table", self.dvoDoneTable)
+       self.dropTable(self.dvoDoneTable) 
+
+       self.logger.infoPair("Creating table", self.dvoMetaTable)
+       sql = "CREATE TABLE " + self.dvoMetaTable + " LIKE dvoMeta"
+       try: self.execute(sql)
+       except: self.logger.errorPair("Unable to create table", self.dvoMetaTable)
+
+       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)
+
+       self.logger.infoPair("Creating table", self.dvoDoneTable )
+       sql = "CREATE TABLE " + self.dvoDoneTable + " (name VARCHAR(100))"
+       try: self.execute(sql)
+       except: self.logger.errorPair("Unable to create table", self.dvoDoneTable)
+
+
+