Changeset 33492 for trunk

Timestamp:

Mar 13, 2012, 10:06:25 AM (14 years ago)

Author:

rhenders

Message:

dvoDetectionFull table now has a foreign key constraint on dvoDone, so deletions from the latter will cascade to the former; now just one method to completely reset all DVO tables

File:

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

trunk/ippToPsps/jython/scratchdb.py

@@ -424 +424 @@
                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)
@@ -444 +438 @@
            
     '''
-    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"
@@ -464 +469 @@
            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)
@@ -472 +478 @@
            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
 
     '''