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

Timestamp:

Jun 19, 2012, 5:24:19 PM (14 years ago)

Author:

mhuber

Message:

merging latest r34040 trunk changes to branch

Location:

branches/meh_branches/ppstack_test

Files:

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

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

@@ -24 +24 @@
         self.dvoDoneTable = "dvoDone"
         self.dvoSkyTable = "dvoSkyTable"
-
+        self.dvoPhotcodesTable = "dvoPhotcodes"
 
     '''
@@ -249 +249 @@
 
     '''
-    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
     '''
@@ -277 +260 @@
             id = rs.getInt(1)
         except:
-            self.logger.errprPair("Could not get file id from " + self.dvoDoneTable + "for", path)
+            self.logger.errorPair("Could not get file id from " + self.dvoDoneTable + "for", path)
 
         return id
@@ -295 +278 @@
             rs.close()
         except:
-            self.logger.errorPai("Can't query for ingested regions", sql)
+            self.logger.errorPair("Can't query for ingested regions", sql)
 
         return regions
@@ -318 +301 @@
 
     '''
-    Checks whether DVO files we have ingested are form the DVO path provided
+    Checks whether DVO files we have ingested are from the DVO path provided
+    Does this by checking the original path of the SkyTable.fits file
     '''
     def isCorrectDvo(self, path):
 
-        sql = "SELECT path FROM " + self.dvoDoneTable + " where path LIKE '%Images.dat'"
+        sql = "SELECT path FROM " + self.dvoDoneTable + " WHERE path LIKE '%SkyTable.fits'"
         try:
             rs = self.executeQuery(sql)  
@@ -336 +320 @@
                 return False
 
-        except:
+        except Exception, e:
+            print str(e)
             self.logger.errorPair("No DVO files ingested for this path", path)
             return False
@@ -342 +327 @@
     '''
     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
+    TODO the value of 50 for numAstroRef (provided by Gene) is hardcoded here, but this should be temporary anyway
     '''
     def astrometricSolutionOK(self, ota):
@@ -410 +395 @@
     '''
     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):
+    Returns a count of the number of detections deleted, if any
+    '''
+    def purgeTheseDvoRegions(self, regions, fileTypes):
 
         detectionCount = self.getRowCount(self.dvoDetectionTable)
         for region in regions:
+          
+           for fileType in fileTypes:
+               sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "' AND path LIKE '%" + fileType + "'"
+               try: self.execute(sql)
+               except:
+                    self.logger.errorPair("Unable to delete " + fileType + " file from", self.dvoDoneTable)
+
+               tableName = self.getDbFriendlyTableName(region + "." + fileType) 
+               sql = "DROP TABLE " + tableName
+               try: self.execute(sql)
+               except:
+                    self.logger.errorPair("Unable to drop table", tableName)
+                    pass
+
+        deletedDetectionCount = detectionCount - self.getRowCount(self.dvoDetectionTable)
+
+        # after a big detection delete, we should OPTIMIZE the table
+        if deletedDetectionCount > 0:
+            self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
+            self.optimizeTable(self.dvoDetectionTable)
+
+        return deletedDetectionCount
            
-           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'"
+    '''
+    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
+    '''
+    def resetAllDvoTables(self):
+
+       # drop all dvo tables - NB detections have to be deleted prior to dvoDone due to foreign key constraint
+       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.dvoDetectionTable)
+       self.dropTable(self.dvoDetectionTable)
+       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 + " (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 delete from", self.dvoDoneTable)
+           self.logger.errorPair("Unable to create table", self.dvoDoneTable)
            return False
 
-       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
-       self.dropTable(self.dvoDetectionTable)
-
+       self.changeEngineToInnoDB(self.dvoDoneTable)
+
+       # create detections table
        self.logger.infoPair("Creating table", self.dvoDetectionTable)
        sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
@@ -463 +456 @@
            self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
            return False
-
-       # TODO
+       self.changeEngineToInnoDB(self.dvoDetectionTable)
+
+       # add fileID column
        sql = "ALTER TABLE " + self.dvoDetectionTable + " ADD fileID INT NOT NULL"
        try: self.execute(sql)
@@ -471 +465 @@
            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 + " (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region VARCHAR(100), path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)"
+       # add an index to the fileID column
+       self.createIndex(self.dvoDetectionTable, "fileID")
+
+       # 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 table", self.dvoDoneTable)
-
-       self.resetDvoDetectionTables()
+       except: 
+           self.logger.errorPair("Unable to create foreign key on", self.dvoDoneTable)
+           return False
+
+       return True
 
     '''
@@ -512 +502 @@
     def getDvoRegionsForThisBox(self, regions, minRa, maxRa, minDec, maxDec):
 
-        sql = "SELECT name FROM " + self.dvoSkyTable + " \
+        sql = "SELECT \
+               INDEX_, \
+               R_MIN+ (R_MAX - R_MIN)/2.0, \
+               D_MIN + (D_MAX - D_MIN)/2.0, \
+               NAME \
+               FROM " + self.dvoSkyTable + " \
                WHERE NOT ((" + str(maxRa) + " <= R_MIN) OR \
                        (" + str(minRa) + " >= R_MAX) OR \
@@ -520 +515 @@
         try:
             rs = self.executeQuery(sql)
-            while (rs.next()): regions.append(rs.getString(1))
+            while (rs.next()): regions.append([
+                    rs.getInt(1), 
+                    rs.getFloat(2), 
+                    rs.getFloat(3), 
+                    rs.getString(4)])
+
             rs.close()
         except:
@@ -526 +526 @@
 
 
+    '''
+    Gets the id for the provided file
+    '''
+    def getRegionNameFromThisDvoIndex(self, index):
+
+        region = ""
+        sql = "SELECT NAME FROM " + self.dvoSkyTable + " WHERE INDEX_ = " + str(index)
+        try:
+            rs = self.executeQuery(sql)
+            rs.first()
+            region = rs.getString(1)
+            rs.close()
+        except:
+            self.logger.errorPair("Could not get region using index", sql )
+
+        return region
+
+    '''
+    Gets a list of filters ordered as thy are in the DVO Photcodes table, as this reflects the order they apper in the cps file
+    '''
+    def getOrderedListOfFiltersFromPhotcodesTable(self, interestedFilters):
+
+        sql = "SELECT NAME FROM " + self.dvoPhotcodesTable + " WHERE "
+
+        count = 0
+        for filter in interestedFilters:
+           if count != 0: sql = sql + " OR "
+           sql = sql + " name = '" + filter + "'"
+           count += 1
+
+        sql = sql + " ORDER BY CODE"
+
+        orderedFilters = []
+        try:
+            rs = self.executeQuery(sql)
+            row = 1
+            while (rs.next()):
+                orderedFilters.append([row,rs.getString(1)])
+                row += 1
+            rs.close()
+        except:
+            self.logger.errorPair("Could not get ordered filter list", sql )
+
+        return orderedFilters
+