Changeset 33415 for branches/meh_branches/ppstack_test/ippToPsps/jython/scratchdb.py

Timestamp:

Mar 5, 2012, 5:19:48 PM (14 years ago)

Author:

mhuber

Message:

merging latest r33407 trunk changes to meh_branches/ppstack_test

Location:

branches/meh_branches/ppstack_test

Files:

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

branches/meh_branches/ppstack_test/ippToPsps/jython/scratchdb.py

@@ -19 +19 @@
     Constructor
     '''
-    def __init__(self, logger, doc, useFull=0):
-        super(ScratchDb, self).__init__(logger, doc, "localdatabase")
+    def __init__(self, logger, config, dbName=None):
+        super(ScratchDb, self).__init__(logger, config, "localdatabase", dbName)
+
+        self.dvoDoneTable = "dvoDone"
+        self.dvoSkyTable = "dvoSkyTable"
+
+
+    '''
+    Destructor
+    '''
+    def __del__(self):
+
+        self.logger.debug("ScratchDb destructor")
+
+
+    '''
+    Sets whether we are using normal or pre-ingested DVO tables
+    '''
+    def setUseFullTables(self, useFull):
 
         if useFull:
-            self.dvoMetaTable = "dvoMetaFull"
+            self.dvoImagesTable = "dvoImagesFull"
             self.dvoDetectionTable = "dvoDetectionFull"
-            self.dvoDoneTable = "dvoDone"
         else:
-            self.dvoMetaTable = "dvoMeta"
+            self.dvoImagesTable = "dvoImages"
             self.dvoDetectionTable = "dvoDetection"
 
-        self.logger.debugPair("Using DVO meta table", self.dvoMetaTable)
+        self.logger.debugPair("Using DVO images table", self.dvoImagesTable)
         self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)
 
     '''
-    Destructor
-    '''
-    def __del__(self):
-
-        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 = "DROP FUNCTION IF EXISTS `psfLikelihood`"
+        try:
+            self.execute(sql)
+        except:
+            self.logger.errorPair("Could not drop stored procedure", "psfLikelihood")
+            return False
+
+        sql = "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"
+
+        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
 
     '''
@@ -56 +102 @@
         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): 
+    def getImageIDFromExternID(self, externID): 
                
         imageID = -1 
                
-        sql = "SELECT imageID FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID) 
+        sql = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID) 
         try: 
             rs = self.executeQuery(sql)   
@@ -76 +138 @@
     Gets DVO image flags
     '''
-    def getDvoImageFlags(self, sourceID, externID):
+    def getDvoImageFlags(self, externID):
 
         flags = 0
 
-        sql = "SELECT flags FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
+        sql = "SELECT FLAGS FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
         try:
             rs = self.executeQuery(sql)  
@@ -92 +154 @@
     Gets photcode (aka photoCalID from dvo table)
     '''
-    def getPhotoCalID(self, sourceID, externID):
+    def getPhotoCalID(self, externID):
 
         photcode = -1
 
-        sql = "SELECT photcode FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
+        sql = "SELECT PHOTCODE FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
         try:
             rs = self.executeQuery(sql)  
@@ -115 +177 @@
 
     '''
-    Inserts a new sourceID/imageID combo into dvoMeta
+    Inserts a new sourceID/imageID combo into dvoImages
     '''
     def insertNewDvoExternID(self, sourceID, externID):
 
-        sql = "INSERT INTO dvoMeta ( \
-               sourceID, \
-               externID \
+        sql = "INSERT INTO " + self.dvoImagesTable + " ( \
+               SOURCE_ID, \
+               EXTERN_ID \
                ) VALUES (\
                " + str(sourceID) + ", \
@@ -129 +191 @@
 
     '''
-    Updates dvoDone table with this DVO table
-    '''
-    def setImportedThisDvoTable(self, name):
-
-        sql = "INSERT INTO dvoDone (name) VALUES ('" + name + "')"
-        self.execute(sql)
-        
-    '''
-    Have we already imported this DVO table?
-    '''
-    def alreadyImportedThisDvoTable(self, name):
-
-        sql = "SELECT COUNT(*) FROM dvoDone WHERE name = '" + name + "'"
+    Do we have an out-of-date version of this file?
+    '''
+    def haveOutOfDateVersionOfThisDvoTable(self, path):
+
+        fileStat = os.stat(path)
+
+        sql = "SELECT COUNT(*) FROM " + self.dvoDoneTable + " WHERE path = '" + path + "' AND modifiedDate != " + str(fileStat.st_mtime) 
 
         try:
@@ -147 +203 @@
             rs.first()
             if rs.getInt(1) > 0:
-                self.logger.errorPair("Already imported DVO tables for",  name)
+                self.logger.debugPair("Have out-to-date version of",  path)
                 return True
             else:
                 return False
         except:
+            self.logger.exception("Unable to check whether this DVO table is out-of-date")
+
+    '''
+    Have we already imported this DVO table?
+    Checks path and modified date to be sure
+    '''
+    def alreadyImportedThisDvoTable(self, path):
+
+        fileStat = os.stat(path)
+
+        sql = "SELECT COUNT(*) FROM " + self.dvoDoneTable + " WHERE path = '" + path + "' AND modifiedDate = " + str(fileStat.st_mtime) 
+
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            if rs.getInt(1) > 0:
+                self.logger.debugPair("Already imported up-to-date version of",  path)
+                return True
+            else:
+                return False
+        except:
             self.logger.exception("Unable to check whether this DVO table has been imported")
 
 
     '''
+    Updates dvoDone table with this DVO table
+    '''
+    def setImportedThisDvoTable(self, path, region="null"):
+
+        fileStat = os.stat(path)
+
+        # first delete any old version we have ingested
+        sql = "DELETE FROM " + self.dvoDoneTable + " WHERE path = '" + path + "'"
+        self.execute(sql)
+
+        # now insert new version with up-to-date size and date
+        sql = "INSERT INTO " + self.dvoDoneTable + " (region, path, modifiedDate, size) VALUES ('" + region + "', '" + path + "', " + str(str(fileStat.st_mtime)) + ", " + str(str(fileStat.st_size)) + ")"
+        self.execute(sql)
+
+
+    '''
+    Gets the id for the cpm file for the provided region
+    '''
+    def getDvoCpmFileIdForRegion(self, region):
+
+        id = -1
+        sql = "SELECT id FROM " + self.dvoDoneTable + " WHERE region = '" + region + "' AND path LIKE '%.cpm'";
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            id = rs.getInt(1)
+        except:
+            self.logger.errorPair("Could not get file id from " + self.dvoDoneTable, "for region: " + region)
+
+        return id
+
+
+    '''
+    Gets the id for the provided file
+    '''
+    def getDvoFileId(self, path):
+
+        id = -1
+        sql = "SELECT id FROM " + self.dvoDoneTable + " WHERE path = '" + path + "'"
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            id = rs.getInt(1)
+        except:
+            self.logger.errprPair("Could not get file id from " + self.dvoDoneTable + "for", path)
+
+        return id
+                                                                                                
+    '''
+    Returns a list of DVO regions currently ingested
+    '''
+    def getIngestedDvoRegions(self):
+
+        regions = []
+
+        sql = "SELECT DISTINCT region FROM dvoDone WHERE region != 'null'"
+        try:
+            rs = self.executeQuery(sql)
+            while (rs.next()):
+                regions.append(rs.getString(1))
+            rs.close()
+        except:
+            self.logger.errorPai("Can't query for ingested regions", sql)
+
+        return regions
+
+    
+    '''
+    Gets total size of DVO files imported to this database
+    Counts only cpm and cpt files
+    '''
+    def getTotalSizeOfIngestedDvoFiles(self):
+
+        size = 0
+        sql = "SELECT SUM(size) FROM " + self.dvoDoneTable + " WHERE path LIKE '%cpm' OR path LIKE '%cpt'"
+        try:
+            rs = self.executeQuery(sql)  
+            rs.first()
+            size = rs.getLong(1)
+        except:
+            self.logger.errorPair("Unable to get total ingested DVO size", sql)
+
+        return size
+
+    '''
+    Checks whether DVO files we have ingested are form the DVO path provided
+    '''
+    def isCorrectDvo(self, path):
+
+        sql = "SELECT path FROM " + self.dvoDoneTable + " where path LIKE '%Images.dat'"
+        try:
+            rs = self.executeQuery(sql)  
+            rs.first()
+
+            head,tail = os.path.split(rs.getString(1))
+
+            if head == path: 
+                self.logger.debugPair("Already ingested stuff from DVO at", path)
+                return True
+            else: 
+                self.logger.errorPair("DVO does not match. We have", head)
+                return False
+
+        except:
+            self.logger.errorPair("No DVO files ingested for this path", path)
+            return False
+
+    '''
+    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
+
+
+    '''
     Creates a table for for ID matching
     '''
     def createDvoTables(self):
-
-        self.logger.debugPair("Creating DVO table", "dvoMeta")
-
-        sql = "DROP TABLE dvoMeta"
+    
+        # drop and create Images table
+        self.logger.debugPair("Creating DVO table", self.dvoImagesTable)
+        sql = "DROP TABLE " + self.dvoImagesTable
         try: self.execute(sql)
         except: pass
         
-        sql = "DROP TABLE dvoDetection"
-        try: self.execute(sql)
-        except: pass
-
-        sql = "CREATE TABLE dvoMeta ( \
-               sourceID INT, \
-               imageID INT, \
-               externID INT, \
-               flags INT, \
-               photcode INT, \
-               PRIMARY KEY (sourceID, imageID, externID) \
+        sql = "CREATE TABLE " + self.dvoImagesTable + " ( \
+               SOURCE_ID SMALLINT, \
+               IMAGE_ID INT, \
+               EXTERN_ID INT, \
+               FLAGS INT, \
+               PHOTCODE SMALLINT, \
+               PRIMARY KEY (IMAGE_ID, EXTERN_ID) \
                )"
 
@@ -183 +386 @@
             self.logger.error("Unable to create DVO meta-data database table")
 
+        # now detection table
+        self.logger.debugPair("Creating DVO table", self.dvoDetectionTable)
+        sql = "DROP TABLE " + self.dvoDetectionTable
+        try: self.execute(sql)
+        except: pass
+
         self.logger.debugPair("Creating DVO table", "dvoDetection")
         sql = "CREATE TABLE dvoDetection ( \
-               sourceID INT, \
                imageID INT, \
                ippDetectID BIGINT, \
@@ -192 +400 @@
                objID BIGINT, \
                flags INT, \
-               PRIMARY KEY (sourceID, imageID, ippDetectID) \
+               PRIMARY KEY (imageID, ippDetectID) \
                )"
 
@@ -201 +409 @@
 
     '''
-    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)
+    Removes the provided DVO FITS files from from dvoDone and dvoDetection tables
+    Returns a count of the number of detections deleted
+    '''
+    def purgeTheseDvoRegions(self, regions):
+
+        detectionCount = self.getRowCount(self.dvoDetectionTable)
+        for region in regions:
+           
+           fileId = self.getDvoCpmFileIdForRegion(region)
+
+           if fileId < 0:
+               self.logger.errorPair("Unable to get file ID to delete " + region + " from", self.dvoDoneTable)
+               continue
+
+           sql = "DELETE FROM " + self.dvoDetectionTable + " WHERE fileID = " + str(fileId)
+           try: self.execute(sql)
+           except: 
+               self.logger.errorPair("Unable to delete from " + self.dvoDetectionTable, "for file ID: %d" % fileId)
+               return False
+
+           sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "'"
+           try: self.execute(sql)
+           except:
+               self.logger.errorPair("Unable to delete " + region + " from", self.dvoDoneTable)
+
+        finalCount = detectionCount - self.getRowCount(self.dvoDetectionTable)
+
+        # after a big delete, we should OPTIMIZE the table
+        self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
+        if finalCount > 0: self.optimizeTable(self.dvoDetectionTable)
+
+        return finalCount
+           
+    '''
+    Drops DVO stuff pertaining to detections
+    '''
+    def resetDvoDetectionTables(self):
+
+       self.logger.infoPair("Deleting detections from", self.dvoDoneTable)
+       sql = "DELETE FROM " + self.dvoDoneTable + " WHERE path LIKE '%cpm' OR path LIKE '%cpt'"
+       try: self.execute(sql)
+       except: 
+           self.logger.errorPair("Unable to delete from", self.dvoDoneTable)
+           return False
+
        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)
-
+       except:
+           self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
+           return False
+
+       # TODO
+       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
+
+    '''
+    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
+    '''
+    def resetAllDvoTables(self):
+
+       self.logger.infoPair("Dropping table", self.dvoImagesTable)
+       self.dropTable(self.dvoImagesTable) 
+       self.logger.infoPair("Dropping table", self.dvoSkyTable)
+       self.dropTable(self.dvoSkyTable) 
+       self.logger.infoPair("Dropping table", self.dvoDoneTable)
+       self.dropTable(self.dvoDoneTable) 
+
+       # create dvoDone table
        self.logger.infoPair("Creating table", self.dvoDoneTable )
-       sql = "CREATE TABLE " + self.dvoDoneTable + " (name VARCHAR(100))"
+       sql = "CREATE TABLE " + self.dvoDoneTable + " (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region VARCHAR(100), path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)"
        try: self.execute(sql)
        except: self.logger.errorPair("Unable to create table", self.dvoDoneTable)
 
-
-
+       self.resetDvoDetectionTables()
+
+    '''
+    Gets a list of DVO regions that overlap with the defined box
+    '''
+    def getDvoRegions(self, minRa, maxRa, minDec, maxDec):
+
+        self.logger.debug("Querying DVO SkyTable for FITS files in this region")
+
+        regions = []
+        self.getDvoRegionsForThisBox(regions, minRa, maxRa, minDec, maxDec)
+
+        # deal with 0/360 boundary
+        if minRa < 0: self.getDvoRegionsForThisBox(regions, minRa + 360, 361 ,minDec, maxDec)
+        if maxRa > 360: self.getDvoRegionsForThisBox(regions, 0, maxRa - 360 ,minDec, maxDec)
+
+        return regions
+
+    '''
+    Gets a list of DVO regions that overlap with the defined box
+    '''
+    def getDvoRegionsForThisBox(self, regions, minRa, maxRa, minDec, maxDec):
+
+        sql = "SELECT name FROM " + self.dvoSkyTable + " \
+               WHERE NOT ((" + str(maxRa) + " <= R_MIN) OR \
+                       (" + str(minRa) + " >= R_MAX) OR \
+                       (" + str(maxDec) + " <= D_MIN) OR \
+                       (" + str(minDec) + " >= D_MAX)) "
+
+        try:
+            rs = self.executeQuery(sql)
+            while (rs.next()): regions.append(rs.getString(1))
+            rs.close()
+        except:
+            self.logger.exception("Can't query for DVO regions")
+
+