Index: /trunk/ippToPsps/jython/dvo.py =================================================================== --- /trunk/ippToPsps/jython/dvo.py (revision 33154) +++ /trunk/ippToPsps/jython/dvo.py (revision 33155) @@ -47,7 +47,5 @@ ''' def __del__(self): - self.logger.debug("Dvo destructor") - ''' @@ -62,13 +60,18 @@ def loadImages(self): - # import Images.dat table + # check if we have up-to-date version + path = self.dvoLocation + "/Images.dat" + if self.scratchDb.alreadyImportedThisDvoTable(path): return + + # first, delete old dvoMeta table self.logger.infoPair("Deleting from table", self.scratchDb.dvoMetaTable) sql = "DELETE FROM " + self.scratchDb.dvoMetaTable self.scratchDb.execute(sql) - self.imagesTableName = self.importFits( - "Images.dat", - "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR") - self.scratchDb.createIndex(self.imagesTableName, "EXTERN_ID") + self.importFits( + path, + "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR", + self.scratchDb.dvoImageTable) + self.scratchDb.createIndex(self.scratchDb.dvoImageTable, "EXTERN_ID") # insert into dvoMetaFull @@ -88,7 +91,13 @@ FLAGS, \ PHOTCODE \ - FROM " + self.imagesTableName - self.scratchDb.execute(sql) - + FROM " + self.scratchDb.dvoImageTable + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair("Could not insert into", self.scratchDb.dvoMetaTable) + return + + self.scratchDb.setImportedThisDvoTable(path) ''' @@ -96,41 +105,53 @@ ''' def loadSkyTable(self): + + path = self.dvoLocation + "/SkyTable.fits" + if self.scratchDb.alreadyImportedThisDvoTable(path): return + self.importFits( + path, + "R_MIN R_MAX D_MIN D_MAX INDEX NAME", + self.scratchDb.dvoSkyTable) + + self.logger.infoPair("Adding index to", self.scratchDb.dvoSkyTable) + + self.scratchDb.createIndex(self.scratchDb.dvoSkyTable, "INDEX") - self.skyTableName = self.importFits( - "SkyTable.fits", - "R_MIN R_MAX D_MIN D_MAX INDEX NAME") - - self.logger.infoPair("Adding index to", self.skyTableName) - - self.scratchDb.createIndex(self.skyTableName, "INDEX") - + self.scratchDb.setImportedThisDvoTable(path) self.logger.infoPair("Finished importing SkyTable at", self.dvoLocation) ''' - Populates dvoDetections with FITS tables from DVO for the whole sky - ''' - def loadRegion(self, minRa, maxRa, minDec, maxDec): - self.loadRegion(-1, 361, -91, 91) - - ''' Populates dvoDetections and dvoMeta with FITS tables from DVO for the defined region - ''' - def loadRegion(self, minRa, maxRa, minDec, maxDec): - + Defaults to whole sky if limits are missing + ''' + def loadRegion(self, minRa=-1., maxRa=361., minDec=-91., maxDec=91): + + if (maxRa - minRa <= 0) or (maxDec - minDec) <= 0: + self.logger.errorPair("Zero range in either RA or Dec", + "RA: %.2f -> %.2f, Dec: %.2f -> %.2f" % (minRa, maxRa, minDec, maxDec)) + return False + + self.logger.infoPair("Finding DVO files for range", + "RA: %.2f -> %.2f, Dec: %.2f -> %.2f" % (minRa, maxRa, minDec, maxDec)) files = self.scratchDb.getDvoFilesCoveringThisRegion(minRa, maxRa, minDec, maxDec) count = 0 + + self.logger.infoSeparator() for file in files: - path = self.dvoLocation + "/" + file + ".cpm" - if not os.path.isfile(path): continue + + cpmPath = self.dvoLocation + "/" + file + ".cpm" + cptPath = self.dvoLocation + "/" + file + ".cpt" + + if not os.path.isfile(cpmPath): continue count = count + 1 - print path - - if self.scratchDb.alreadyImportedThisDvoTable(file): continue + + # only if we have already imported up-to-date versions of both the cpm and ctp + # will we skip this one + if self.scratchDb.alreadyImportedThisDvoTable(cpmPath) and self.scratchDb.alreadyImportedThisDvoTable(cptPath): continue # import cpm table and index cpmTableName = self.importFits( - file + ".cpm", + cpmPath, "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS") self.scratchDb.createIndex(cpmTableName, "IMAGE_ID") @@ -140,5 +161,5 @@ # import cpt table and index cptTableName = self.importFits( - file + ".cpt", + cptPath, "OBJ_ID CAT_ID EXT_ID") self.scratchDb.createIndex(cptTableName, "IMAGE_ID") @@ -150,5 +171,5 @@ sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (SOURCE_ID SMALLINT)" self.scratchDb.execute(sql) - sql = "UPDATE " + cpmTableName + " AS a, " + self.imagesTableName + " AS b \ + sql = "UPDATE " + cpmTableName + " AS a, " + self.scratchDb.dvoImageTable + " AS b \ SET a.SOURCE_ID = b.SOURCE_ID \ WHERE a.IMAGE_ID = b.IMAGE_ID" @@ -196,28 +217,33 @@ # now drop what we don't need self.logger.infoPair("Dropping table", cpmTableName) - #self.scratchDb.dropTable(cpmTableName) + self.scratchDb.dropTable(cpmTableName) self.logger.infoPair("Dropping table", cptTableName) - #self.scratchDb.dropTable(cptTableName) - - self.scratchDb.setImportedThisDvoTable(file) + self.scratchDb.dropTable(cptTableName) + + self.scratchDb.setImportedThisDvoTable(cpmPath) + self.scratchDb.setImportedThisDvoTable(cptPath) self.logger.infoPair("Found", "%d region files" % count) + return True + ''' Imports a FITS file. A regex filter lets you choose which tables to pull from the file - ''' - def importFits(self, file, columns): - - fullPath = self.dvoLocation + "/" + file - self.logger.infoPair("Importing file", fullPath) - - tableName = file - tableName = tableName.replace('.', '_') - tableName = tableName.replace('/', '_') + An optional final argument lets you name the resulatant database table + ''' + def importFits(self, path, columns, tableName=None): + + self.logger.infoPair("Importing file", path) + + if not tableName: + head,tail = os.path.split(path) + tableName = tail + tableName = tableName.replace('.', '_') + tableName = tableName.replace('/', '_') self.logger.infoPair("Writing to database table", tableName) - tables = stilts.treads(fullPath) + tables = stilts.treads(path) count = 0 @@ -263,7 +289,7 @@ dvo = Dvo(logger, configDoc) #dvo.resetAllTables() -#dvo.loadImages() -#dvo.loadSkyTable() -dvo.loadRegion(330, 330.1, 0.3, 0.4) +dvo.loadImages() +dvo.loadSkyTable() +dvo.loadRegion(330, 330.1, 0.2, 0.7) logger.infoPair("Program...", "complete") Index: /trunk/ippToPsps/jython/scratchdb.py =================================================================== --- /trunk/ippToPsps/jython/scratchdb.py (revision 33154) +++ /trunk/ippToPsps/jython/scratchdb.py (revision 33155) @@ -26,5 +26,6 @@ self.dvoDetectionTable = "dvoDetectionFull" self.dvoDoneTable = "dvoDone" - self.dvoSkyTable = "SkyTable_fits" + self.dvoSkyTable = "dvoSkyTable" + self.dvoImageTable = "dvoImages" else: self.dvoMetaTable = "dvoMeta" @@ -183,9 +184,37 @@ ''' + Have we already imported this DVO table? + ''' + 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.errorPair("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, name): - - sql = "INSERT INTO dvoDone (name) VALUES ('" + name + "')" + def setImportedThisDvoTable(self, path): + + 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 + " (path, modifiedDate, size) VALUES ('" + path + "', " + str(str(fileStat.st_mtime)) + ", " + str(str(fileStat.st_size)) + ")" self.execute(sql) @@ -210,22 +239,4 @@ return True - - ''' - Have we already imported this DVO table? - ''' - def alreadyImportedThisDvoTable(self, name): - - sql = "SELECT COUNT(*) FROM dvoDone WHERE name = '" + name + "'" - - try: - rs = self.executeQuery(sql) - rs.first() - if rs.getInt(1) > 0: - self.logger.errorPair("Already imported DVO tables for", name) - return True - else: - return False - except: - self.logger.exception("Unable to check whether this DVO table has been imported") @@ -298,5 +309,5 @@ self.logger.infoPair("Creating table", self.dvoDoneTable ) - sql = "CREATE TABLE " + self.dvoDoneTable + " (name VARCHAR(100))" + sql = "CREATE TABLE " + self.dvoDoneTable + " (path VARCHAR(1000), modifiedDate BIGINT, size BIGINT)" try: self.execute(sql) except: self.logger.errorPair("Unable to create table", self.dvoDoneTable)