Changeset 33175

Timestamp:

Jan 27, 2012, 3:46:15 PM (14 years ago)

Author:

rhenders

Message:

added new columns to dvoDone table and dvoDetectionFull so that each detection is linked to a particular DVO fits file; method to purge detections for a set of DVO regions; method to get all ingested regions; re-named some methods for clarity

File:

• trunk/ippToPsps/jython/scratchdb.py (modified) (7 diffs)

trunk/ippToPsps/jython/scratchdb.py

@@ -207 +207 @@
     Updates dvoDone table with this DVO table
     '''
-    def setImportedThisDvoTable(self, path):
+    def setImportedThisDvoTable(self, path, region="null"):
 
         fileStat = os.stat(path)
@@ -216 +216 @@
 
         # now insert new version with up-to-date size and date
-        sql = "INSERT INTO " + self.dvoDoneTable + " (path, modifiedDate, size) VALUES ('" + path + "', " + str(str(fileStat.st_mtime)) + ", " + str(str(fileStat.st_size)) + ")"
+        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
@@ -225 +278 @@
     def getTotalSizeOfIngestedDvoFiles(self):
 
+        size = 0
         sql = "SELECT SUM(size) FROM " + self.dvoDoneTable + " WHERE path LIKE '%cpm' OR path LIKE '%cpt'"
         try:
@@ -248 +302 @@
 
             if head == path: 
-                self.logger.infoPair("Already ingested stuff from DVO at", path)
+                self.logger.debugPair("Already ingested stuff from DVO at", path)
                 return True
             else: 
@@ -347 +401 @@
 
     '''
+    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)
+
+        
+        return detectionCount - self.getRowCount(self.dvoDetectionTable)
+           
+
+    '''
     Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
     '''
@@ -368 +452 @@
        except: self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
 
+       # 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)
+
        self.logger.infoPair("Creating table", self.dvoDoneTable )
-       sql = "CREATE TABLE " + self.dvoDoneTable + " (path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)"
+       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)
@@ -377 +466 @@
     Gets a list of PSPS image IDs for this stack ID
     '''
-    def getDvoFilesCoveringThisRegion(self, minRa, maxRa, minDec, maxDec):
+    def getDvoRegionsForThisBox(self, minRa, maxRa, minDec, maxDec):
 
         self.logger.debug("Querying DVO SkyTable for FITS files in this region")