IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 33236 for trunk


Ignore:
Timestamp:
Feb 10, 2012, 1:42:23 PM (14 years ago)
Author:
rhenders
Message:

got rid of dvoMeta table - now only use dvoImages table; changes to reset methods - can reset only detections

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippToPsps/jython/scratchdb.py

    r33185 r33236  
    2323
    2424        if useFull:
    25             self.dvoMetaTable = "dvoMetaFull"
    2625            self.dvoDetectionTable = "dvoDetectionFull"
    2726            self.dvoDoneTable = "dvoDone"
    2827            self.dvoSkyTable = "dvoSkyTable"
    29             self.dvoImageTable = "dvoImages"
     28            self.dvoImagesTable = "dvoImagesFull"
    3029        else:
    31             self.dvoMetaTable = "dvoMeta"
     30            self.dvoImagesTable = "dvoImages"
    3231            self.dvoDetectionTable = "dvoDetection"
    3332
    34         self.logger.debugPair("Using DVO meta table", self.dvoMetaTable)
     33        self.logger.debugPair("Using DVO images table", self.dvoImagesTable)
    3534        self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)
    3635
    … …  
    114113    Gets imageID from extern ID
    115114    '''
    116     def getImageIDFromExternID(self, sourceID, externID):
     115    def getImageIDFromExternID(self, externID):
    117116               
    118117        imageID = -1
    119118               
    120         sql = "SELECT imageID FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
     119        sql = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
    121120        try:
    122121            rs = self.executeQuery(sql)   
    … …  
    131130    Gets DVO image flags
    132131    '''
    133     def getDvoImageFlags(self, sourceID, externID):
     132    def getDvoImageFlags(self, externID):
    134133
    135134        flags = 0
    136135
    137         sql = "SELECT flags FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
     136        sql = "SELECT FLAGS FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
    138137        try:
    139138            rs = self.executeQuery(sql) 
    … …  
    147146    Gets photcode (aka photoCalID from dvo table)
    148147    '''
    149     def getPhotoCalID(self, sourceID, externID):
     148    def getPhotoCalID(self, externID):
    150149
    151150        photcode = -1
    152151
    153         sql = "SELECT photcode FROM " + self.dvoMetaTable + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
     152        sql = "SELECT PHOTCODE FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
    154153        try:
    155154            rs = self.executeQuery(sql) 
    … …  
    170169
    171170    '''
    172     Inserts a new sourceID/imageID combo into dvoMeta
     171    Inserts a new sourceID/imageID combo into dvoImages
    173172    '''
    174173    def insertNewDvoExternID(self, sourceID, externID):
    175174
    176         sql = "INSERT INTO dvoMeta ( \
    177                sourceID, \
    178                externID \
     175        sql = "INSERT INTO " + self.dvoImagesTable + " ( \
     176               SOURCE_ID, \
     177               EXTERN_ID \
    179178               ) VALUES (\
    180179               " + str(sourceID) + ", \
    … …  
    359358    '''
    360359    def createDvoTables(self):
    361 
    362         self.logger.debugPair("Creating DVO table", "dvoMeta")
    363 
    364         sql = "DROP TABLE dvoMeta"
     360   
     361        # drop and create Images table
     362        self.logger.debugPair("Creating DVO table", self.dvoImagesTable)
     363        sql = "DROP TABLE " + self.dvoImagesTable
    365364        try: self.execute(sql)
    366365        except: pass
    367366       
    368         sql = "DROP TABLE dvoDetection"
    369         try: self.execute(sql)
    370         except: pass
    371 
    372         sql = "CREATE TABLE dvoMeta ( \
    373                sourceID INT, \
    374                imageID INT, \
    375                externID INT, \
    376                flags INT, \
    377                photcode INT, \
    378                PRIMARY KEY (sourceID, imageID, externID) \
     367        sql = "CREATE TABLE " + self.dvoImagesTable + " ( \
     368               SOURCE_ID SMALLINT, \
     369               IMAGE_ID INT, \
     370               EXTERN_ID INT, \
     371               FLAGS INT, \
     372               PHOTCODE SMALLINT, \
     373               PRIMARY KEY (IMAGE_ID, EXTERN_ID) \
    379374               )"
    380375
    … …  
    383378            self.logger.error("Unable to create DVO meta-data database table")
    384379
     380        # now detection table
     381        self.logger.debugPair("Creating DVO table", self.dvoDetectionTable)
     382        sql = "DROP TABLE " + self.dvoDetectionTable
     383        try: self.execute(sql)
     384        except: pass
     385
    385386        self.logger.debugPair("Creating DVO table", "dvoDetection")
    386387        sql = "CREATE TABLE dvoDetection ( \
    387                sourceID INT, \
    388388               imageID INT, \
    389389               ippDetectID BIGINT, \
    … …  
    392392               objID BIGINT, \
    393393               flags INT, \
    394                PRIMARY KEY (sourceID, imageID, ippDetectID) \
     394               PRIMARY KEY (imageID, ippDetectID) \
    395395               )"
    396396
    … …  
    401401
    402402    '''
    403     Resets only the detection table info from DVO ingest
    404     '''
    405     def resetOnlyDetectionDvoTables(self):
     403    Removes the provided DVO FITS files from from dvoDone and dvoDetection tables
     404    Returns a count of the number of detections deleted
     405    '''
     406    def purgeTheseDvoRegions(self, regions):
     407
     408        detectionCount = self.getRowCount(self.dvoDetectionTable)
     409        for region in regions:
     410           
     411           fileId = self.getDvoCpmFileIdForRegion(region)
     412
     413           if fileId < 0:
     414               self.logger.errorPair("Unable to get file ID to delete " + region + " from", self.dvoDoneTable)
     415               continue
     416
     417           sql = "DELETE FROM " + self.dvoDetectionTable + " WHERE fileID = " + str(fileId)
     418           try: self.execute(sql)
     419           except:
     420               self.logger.errorPair("Unable to delete from " + self.dvoDetectionTable, "for file ID: %d" % fileId)
     421               return False
     422
     423           sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "'"
     424           try: self.execute(sql)
     425           except:
     426               self.logger.errorPair("Unable to delete " + region + " from", self.dvoDoneTable)
     427
     428        finalCount = detectionCount - self.getRowCount(self.dvoDetectionTable)
     429
     430        # after a big delete, we should OPTIMIZE the table
     431        self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
     432        if finalCount > 0: self.optimizeTable(self.dvoDetectionTable)
     433
     434        return finalCount
     435           
     436    '''
     437    Drops DVO stuff pertaining to detections
     438    '''
     439    def resetDvoDetectionTables(self):
    406440
    407441       self.logger.infoPair("Deleting detections from", self.dvoDoneTable)
    … …  
    412446           return False
    413447
    414        self.logger.infoPair("Deleting detections from", self.dvoDetectionTable)
    415        sql = "DELETE FROM " + self.dvoDetectionTable
     448       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
     449       self.dropTable(self.dvoDetectionTable)
     450
     451       self.logger.infoPair("Creating table", self.dvoDetectionTable)
     452       sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
     453       try: self.execute(sql)
     454       except:
     455           self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
     456           return False
     457
     458       # TODO
     459       sql = "ALTER TABLE " + self.dvoDetectionTable + " ADD fileID INT NOT NULL"
    416460       try: self.execute(sql)
    417461       except:
    418            self.logger.errorPair("Unable to delete from", self.dvoDetectionTable)
     462           self.logger.errorPair("Unable to add fileID column to ", self.dvoDetectionTable)
    419463           return False
    420464
    421        return True
    422 
    423     '''
    424     Removes the provided DVO FITS files from from dvoDone and dvoDetection tables
    425     Returns a count of the number of detections deleted
    426     '''
    427     def purgeTheseDvoRegions(self, regions):
    428 
    429         detectionCount = self.getRowCount(self.dvoDetectionTable)
    430         for region in regions:
    431            
    432            fileId = self.getDvoCpmFileIdForRegion(region)
    433 
    434            if fileId < 0:
    435                self.logger.errorPair("Unable to get file ID to delete " + region + " from", self.dvoDoneTable)
    436                continue
    437 
    438            sql = "DELETE FROM " + self.dvoDetectionTable + " WHERE fileID = " + str(fileId)
    439            try: self.execute(sql)
    440            except:
    441                self.logger.errorPair("Unable to delete from " + self.dvoDetectionTable, "for file ID: %d" % fileId)
    442                return False
    443 
    444            sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "'"
    445            try: self.execute(sql)
    446            except:
    447                self.logger.errorPair("Unable to delete " + region + " from", self.dvoDoneTable)
    448 
    449         finalCount = detectionCount - self.getRowCount(self.dvoDetectionTable)
    450 
    451         # after a big delete, we should OPTIMIZE the table
    452         self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
    453         if finalCount > 0: self.optimizeTable(self.dvoDetectionTable)
    454 
    455         return finalCount
    456            
    457 
    458465    '''
    459466    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
    460467    '''
    461     def resetDvoToMysqlTables(self):
    462 
    463        self.logger.infoPair("Dropping table", self.dvoMetaTable)
    464        self.dropTable(self.dvoMetaTable)
    465        self.logger.infoPair("Dropping table", self.dvoDetectionTable)
    466        self.dropTable(self.dvoDetectionTable)
     468    def resetAllDvoTables(self):
     469
     470       self.logger.infoPair("Dropping table", self.dvoImagesTable)
     471       self.dropTable(self.dvoImagesTable)
    467472       self.logger.infoPair("Dropping table", self.dvoSkyTable)
    468473       self.dropTable(self.dvoSkyTable)
    469474       self.logger.infoPair("Dropping table", self.dvoDoneTable)
    470475       self.dropTable(self.dvoDoneTable)
    471        self.logger.infoPair("Dropping table", self.dvoImageTable)
    472        self.dropTable(self.dvoImageTable)
    473 
    474        self.logger.infoPair("Creating table", self.dvoMetaTable)
    475        sql = "CREATE TABLE " + self.dvoMetaTable + " LIKE dvoMeta"
    476        try: self.execute(sql)
    477        except: self.logger.errorPair("Unable to create table", self.dvoMetaTable)
    478 
    479        self.logger.infoPair("Creating table", self.dvoDetectionTable)
    480        sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
    481        try: self.execute(sql)
    482        except: self.logger.errorPair("Unable to create table", self.dvoDetectionTable)
    483 
    484        # TODO
    485        sql = "ALTER TABLE " + self.dvoDetectionTable + " ADD fileID INT NOT NULL"
    486        try: self.execute(sql)
    487        except: self.logger.errorPair("Unable to add fileID column to ", self.dvoDetectionTable)
    488 
     476
     477       # create dvoDone table
    489478       self.logger.infoPair("Creating table", self.dvoDoneTable )
    490479       sql = "CREATE TABLE " + self.dvoDoneTable + " (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region VARCHAR(100), path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)"
    … …  
    492481       except: self.logger.errorPair("Unable to create table", self.dvoDoneTable)
    493482
     483       self.resetDvoDetectionTables()
    494484
    495485    '''
Note: See TracChangeset for help on using the changeset viewer.