Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37178) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37179) @@ -207,4 +207,13 @@ sql = "ALTER TABLE " + table + " DROP " + column + try: self.execute(sql) + except: return + + ''' + Add a column to the table + ''' + def addColumn(self, table, column, type): + + sql = "ALTER TABLE " + table + " ADD COLUMN " + column + " " + type try: self.execute(sql) except: return Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37178) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37179) @@ -131,4 +131,20 @@ return filterID + ''' + Gets tess ID for this tess name + ''' + def getTessID(self, name): + + sql = "SELECT tessID FROM TessellationType WHERE name = '" + name + "'" + try: + rs = self.executeQuery(sql) + rs.first() + tessID = rs.getInt(1) + except: + self.logger.errorPair("No tess ID found for tess", name) + tessID = -1 + + return tessID + ''' Gets imageID from extern ID @@ -476,4 +492,5 @@ ippDetectID BIGINT, \ imageID INT, \ + catID INT, \ ra DOUBLE, \ dec_ DOUBLE, \ Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37178) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37179) @@ -71,6 +71,6 @@ # IMAGE_ID in dvoImagesTable - # we just need one stack ID to get the generic metadata - metastackID = -999 + # we just need one valid filter to get the generic metadata + validFilter = 'none' # get a list of stackIDs, identify the CMFs, read the headers: @@ -85,5 +85,5 @@ continue - metastackID = stackID + validFilter = filter # from each stack, get fits name from getStackStageCmf @@ -112,39 +112,42 @@ # did we find any valid stacks? - if metastackID < 0: - self.logger.errorPair("can't find any stack ids...?? ","??") + if validFilter == 'none': + self.logger.errorPair("can't find any valid stacks...?? ","??") raise + validStackID = self.stackIDs[validFilter] + # using the given stack ID, get the generic information: filter, skycell, etc. - meta = self.gpc1Db.getStackStageMeta(metastackID) - - if not meta: - self.logger.errorPair("Could not get stack metadata for", metastackID) + metadata = self.gpc1Db.getStackStageMeta(validStackID) + + if not metadata: + self.logger.errorPair("Could not get stack metadata for", validStackID) raise - fullSkycell = meta[1]; - - # XXX EAM 20140729 : review and fix skycell vs projection cell ID - # skycell is, eg "skycell.1133.081" - # we need 1133.081 for skycell, but store as an int, so instead store 1133081 - # projection cell is just the 1133 bit - - #self.projectioncell = self.skycell[8:12] - #self.skycell = self.skycell[8:] - #self.skycell = self.skycell.replace(".", "") - #self.projectioncell = self.skycell[8:12] - - # TODO this is the wrong way, but is consistent with SkyCell table in IN data - self.skycell = fullSkycell[8:] - self.projectioncell = fullSkycell - - # XXX need to set this somewhere else, but where?? - self.dvoRegionID = 1 - - # proposed new values. Need to coordinate with the SkyCell table - # this fits in 32 bits for 0 < tessID < 214 - # self.skycell = str( (tessID * (10000 * 1000)) + (int(self.projectioncell) * 1000) + cellID ) - - self.analysisVer = meta[2]; + validHeader = self.headerSet[validFilter] + + tessName = self.getKeyValue(header, 'TESS_ID') + self.tessID = self.scratchDb.getTessID(tessName) + + skycellName = self.getKeyValue(header, 'SKYCELL') + + # XXX EAM : I am hardwiring the parsing logic for RINGS vs LOCAL + if tessName == 'RINGS.V3': + # skycell is, eg "skycell.1133.081" + # 0123456789012345 + self.projectionID = skycellName[8:12] + self.skycellID = skycellName[13:] + else: + # skycell is, eg "skycell.081" + # 01234567890 + self.projectionID = 0 + self.skycellID = skycellName[8:11] + + self.analysisVer = metadata[2]; + if self.analysisVer[-1] == 'M': + self.analysisVer = self.analysisVer[:-1] + + ### XXX TEST + self.analysisVer = 30000 # delete PSPS tables @@ -198,33 +201,8 @@ self.logger.infoPair("Stack ID", "%d" % self.id) self.logger.infoPair("Stack type", "%s" % self.stackType) - self.logger.infoPair("Skycell", "%s" % self.skycell) - self.logger.infoPair("Projectioncell", "%s" % self.projectioncell) - - ### *********************** Utility Functions ************************************ - - ''' - Drop a table and report the drop - ''' - def dropTableVerbose(self, table): - self.logger.infoPair("dropping table:",table) - self.scratchDb.dropTable(table) - - ''' - get stackTypeID corresponding to our stackType - ''' - def getStackTypeID(self): - - sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" - rs = self.scratchDb.executeQuery(sql) - if not rs.next(): - self.logger.errorPair("cannot get stack type ID from database for", self.stackType) - raise - - ID = rs.getInt(1) - return ID - - def testprint(self): - print "here ", self.printline - self.printline += 1 + self.logger.infoPair("SkycellID", "%s" % self.skycellID) + self.logger.infoPair("ProjectionID", "%s" % self.projectionID) + + ### *********************** main populate functions ************************************ ''' @@ -268,7 +246,9 @@ sqlLine.group("filterID", str(filterID)) sqlLine.group("stackTypeID", str(self.stackTypeID)) - sqlLine.group("skyCellID", self.skycell) + sqlLine.group("tessID", str(self.tessID)) + sqlLine.group("projectionID", str(self.projectionID)) + sqlLine.group("skyCellID", str(self.skycellID)) sqlLine.group("photoCalID", photoCalID) - sqlLine.group("analysisVer", self.analysisVer)) + sqlLine.group("analysisVer", str(self.analysisVer)) sqlLine.group("expTime", self.getKeyFloat(header, "%.5f", 'EXPTIME')) sqlLine.group("psfModelID", psfmodel) @@ -303,7 +283,9 @@ self.logger.infoPair("Procesing table", "StackObjectThin") + + # insert the per-object data (IDs, random stack ID, dataRelease, etc) self.selectDvoObjIDs() - # XXX EAM 20140731 : if we are going to add indexes (objID?) to StackObjectThin, do it HERE + # add indexes StackObjectThin self.scratchDb.createIndex("StackObjectThin", "objID") @@ -331,4 +313,9 @@ sqlLine.group("a."+filter+"stackDetectID", "b.detectID") # eg, gstackDetectID (from dvo.measure.extID) sqlLine.group("a."+filter+"stackMetaID", str(stackID)) # eg, gstackMetaID (from cmf.IMAGEID) + + sqlLine.group("a.dvoRegionID", "b.catID") # this is not filter dependent, but is re-written for each filter (if data exists) + sqlLine.group("a.primaryDetection", "((b.flags & 0x10000) >> 16)") # this is not filter dependent, but is re-written for each filter (if data exists) + + sqlLine.group("a."+filter+"haveData", "'1'") sqlLine.group("a."+filter+"ra", "b.ra") @@ -387,11 +374,13 @@ def populateStackObjectExtra(self): - self.logger.infoPair("Procesing table", "StackObjectExtra") - self.insertDvoIDs("StackObjectExtra", "StackObjectThin") + tablename = "StackObjectExtra" + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") # if we are going to add a key, do it here so it is useful # self.scratchDb.createIndex("StackObjectExtra", "objID") - self.logger.infoPair("inserting filter dependent cmf items into", "StackObjectExtra") + self.logger.infoPair("inserting filter dependent cmf items into", tablename) for filter in self.filters: self.logger.infoPair("filter", filter) @@ -408,5 +397,7 @@ # insert all the detections - sqlLine = sqlUtility("UPDATE StackObjectExtra AS a , " + filter + "SkyChip_psf AS b SET") + sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + filter + "SkyChip_psf AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") sqlLine.group("a."+filter+"xPos", "b.X_PSF") @@ -450,5 +441,5 @@ # insert detection information for each filter based on the DVO contents - sqlLine = sqlUtility("UPDATE StackObjectExtra as a, " + self.scratchDb.dvoDetectionTable + " as b SET") + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") sqlLine.group("a."+filter+"expTime", "b.expTime") sqlLine.group("a."+filter+"zp", "b.zp") @@ -466,5 +457,7 @@ return - self.tablesToExport.append("StackObjectExtra") + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) ''' @@ -486,8 +479,8 @@ def populateStackModelFit(self, model, ippModelType): - table = "StackModelFit" + model - - self.logger.infoPair("Procesing table", table) - self.insertDvoIDs(table, "StackObjectThin") + tablename = "StackModelFit" + model + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") # if we are going to add a key, do it here so it is useful @@ -497,5 +490,6 @@ self.populateStackModelFitFilter(model, ippModelType, filter) - self.tablesToExport.append(table) + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) ''' @@ -503,4 +497,6 @@ ''' def populateStackModelFitFilter(self, model, ippModelType, filter): + + tablename = "StackModelFit" + model if self.stackIDs[filter] <= 0: @@ -508,6 +504,4 @@ return True - table = "StackModelFit" + model - header = self.headerSet[filter] print "exptime in header: " + header['EXPTIME'] @@ -518,5 +512,7 @@ print "magtime: " + magtime + ", exptime: " + exptime - sqlLine = sqlUtility("UPDATE " + table + " AS a, " + filter + "SkyChip_xfit AS b SET") + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xfit AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime) @@ -552,5 +548,5 @@ # insert detection information for each filter based on the DVO contents - sqlLine = sqlUtility("UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") sqlLine.group("a." + filter + model + "Mag", "a." + filter + model + "Mag + b.zp") sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") @@ -568,16 +564,17 @@ def populateStackPetrosian(self): - table = "StackModelFitPet" - - self.logger.infoPair("Procesing table", table) - self.insertDvoIDs(table, "StackObjectThin") + tablename = "StackModelFitPet" + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") # if we are going to add a key, do it here so it is useful - # self.scratchDb.createIndex(table, "objID") + # self.scratchDb.createIndex(tablename, "objID") for filter in self.filters: self.populateStackPetrosianFilter(filter) - self.tablesToExport.append(table) + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) ''' @@ -592,4 +589,6 @@ self.logger.infoPair("Adding", "petrosians for extended sources") sqlLine = sqlUtility("UPDATE StackModelFitPet AS a, " + filter + "SkyChip_xsrc AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") sqlLine.group("a." + filter + "petRadius", "b.PETRO_RADIUS") @@ -626,4 +625,5 @@ self.populateStackApFlxFilter(tablename, "C2", filter) + self.deleteRowsWithNoStackData(tablename) self.tablesToExport.append(tablename) @@ -644,4 +644,5 @@ self.populateStackApFlxFilter(tablename, version, filter) + self.deleteRowsWithNoStackData(tablename) self.tablesToExport.append(tablename) @@ -678,6 +679,6 @@ maxRadius = 5 - # properly -999 these to start with: - # XXX the default should take care of this, but does not + # properly -999 these to start with. the default should take + # care of this, but does not sqlLine = sqlUtility("UPDATE " + tablename + " SET") @@ -718,5 +719,5 @@ self.logger.infoPair("Aperture Numbers",number) - # XXX : I need to worry about which header this is + # get the exposure time for aperture fluxes header = self.headerSet[filter] exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") @@ -724,4 +725,6 @@ # generate the sql to do the necessary ops on the columns sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ") + + sqlLine.group("a."+filter+"haveData", "'1'") for number in numbers: @@ -762,31 +765,39 @@ self.tablesToExport.append("StackToImage") - ''' - Reports and deletes detections in this table that are not in StackObject - ''' - def deleteDetectionsNotInStackObject(self, table, filter): - f= filter - - sql = "SELECT COUNT(*) FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" + ### *********************** Utility Functions ************************************ + + ''' + Drop a table and report the drop + ''' + def dropTableVerbose(self, table): + self.logger.infoPair("dropping table:",table) + self.scratchDb.dropTable(table) + + ''' + get stackTypeID corresponding to our stackType + ''' + def getStackTypeID(self): + + sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" rs = self.scratchDb.executeQuery(sql) - rs.first() - nMissing = rs.getInt(1) - - if nMissing < 1: return + if not rs.next(): + self.logger.errorPair("cannot get stack type ID from database for", self.stackType) + raise - sql = "DELETE FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" - self.scratchDb.execute(sql) - self.logger.infoPair("Detections not in StackObject", "%d deleted from %s" % (nMissing, table)) - - - def deleteNULLippDetectIDs(self, tableName): - - self.logger.infoPair("Deleting NULL ippdetectIDs from table", tableName) - sql = "DELETE FROM " + tableName + " WHERE \ - gippDetectID is NULL AND \ - rippDetectID is NULL AND \ - iippDetectID is NULL AND \ - zippDetectID is NULL AND \ - yippDetectID is NULL " + ID = rs.getInt(1) + return ID + + def deleteRowsWithNoStackData(self, tableName): + + self.logger.infoPair("Deleting rows with no stack data from", tableName) + sql = "DELETE FROM " + tableName + " WHERE " + + for i in range(len(self.filters)): + filter = self.filters[i] + sql += filter + "haveData is NULL" + + if (i < len(self.filters) - 1): + sql += " AND " + self.logger.infoPair('sql',sql) self.scratchDb.execute(sql) @@ -798,10 +809,4 @@ self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)") - # heather uncommented the unique line -- well that no worky - #self.scratchDb.makeColumnUnique("StackObject", "objID") - #self.scratchDb.createIndex("StackObject", "ippDetectID") - #self.scratchDb.createIndex("StackObject", "stackDetectID") - #self.scratchDb.createIndex("StackApFlx", "ippDetectID") - #self.scratchDb.createIndex("StackModelFit", "ippDetectID") return True @@ -827,7 +832,11 @@ ''' select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert - ippObjID and several constants (for a skycell): surveyID, skyCellId, dvoRegionID, dataRelease + ippObjID and several constants (for a skycell): surveyID, skyCellId, dataRelease ''' def selectDvoObjIDs(self): + + # we need a temporary column for each filter to identify empty rows + for filter in self.filters: + self.scratchDb.addColumn("StackObjectThin", filter + "haveData", "tinyint") sqlLine = sqlUtility("INSERT INTO StackObjectThin (") @@ -836,6 +845,7 @@ sqlLine.group("ippObjID", "ippObjID") sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") - sqlLine.group("skyCellID", "'" + str(self.skycell) + "'") - sqlLine.group("dvoRegionID", "'" + str(self.dvoRegionID) + "'") + sqlLine.group("tessID", "'" + str(self.tessID) + "'") + sqlLine.group("projectionID", "'" + str(self.projectionID) + "'") + sqlLine.group("skyCellID", "'" + str(self.skycellID) + "'") sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)") @@ -864,5 +874,9 @@ def insertDvoIDs(self, table, mainTable): - fields = "objID, ippObjID, surveyID, skyCellID, randomStackObjID, primaryDetection, dvoRegionID, dataRelease, " + # we need a temporary column for each filter to identify empty rows + for filter in self.filters: + self.scratchDb.addColumn(table, filter + "haveData", "tinyint") + + fields = "objID, ippObjID, randomStackObjID, primaryDetection, bestDetection, " for i in range(len(self.filters)): @@ -882,36 +896,36 @@ Updates provided table with DVO IDs from DVO table ''' - def updateDvoIDs(self, table): - self.logger.infoPair("getting imageID for ","filter") - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.debug("Updating table '" + table + "' with DVO IDs...") - sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.stackDetectID = b.detectID, \ - a.objID = b.objID \ - WHERE a.ippDetectID = b.ippDetectID \ - AND b.imageID = " + str(imageID) - self.scratchDb.execute(sql) + ## XX def updateDvoIDs(self, table): + ## XX self.logger.infoPair("getting imageID for ","filter") + ## XX imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) + ## XX self.logger.infoPair("obtained","imageID") + ## XX self.logger.debug("Updating table '" + table + "' with DVO IDs...") + ## XX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ + ## XX a.ippObjID = b.ippObjID, \ + ## XX a.stackDetectID = b.detectID, \ + ## XX a.objID = b.objID \ + ## XX WHERE a.ippDetectID = b.ippDetectID \ + ## XX AND b.imageID = " + str(imageID) + ## XX self.scratchDb.execute(sql) ''' Updates Flags ''' - def updateDvoIDsAndFlags(self, table): - self.logger.infoPair("getting","imageID") - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.debug("Updating table '" + table + "' with DVO IDs...") - sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.stackDetectID = b.detectID, \ - a.objID = b.objID, \ - a.infoFlag = b.flags << 45 | a.infoFlag, \ - a.primaryF = ((b.flags & 0x10000) >> 16 ) \ - WHERE a.ippDetectID = b.ippDetectID \ - AND b.imageID = " + str(imageID) - - print "update dvo sql: ", sql - self.scratchDb.execute(sql) + ## XX def updateDvoIDsAndFlags(self, table): + ## XX self.logger.infoPair("getting","imageID") + ## XX imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) + ## XX self.logger.infoPair("obtained","imageID") + ## XX self.logger.debug("Updating table '" + table + "' with DVO IDs...") + ## XX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ + ## XX a.ippObjID = b.ippObjID, \ + ## XX a.stackDetectID = b.detectID, \ + ## XX a.objID = b.objID, \ + ## XX a.infoFlag = b.flags << 45 | a.infoFlag, \ + ## XX a.primaryF = ((b.flags & 0x10000) >> 16 ) \ + ## XX WHERE a.ippDetectID = b.ippDetectID \ + ## XX AND b.imageID = " + str(imageID) + ## XX + ## XX print "update dvo sql: ", sql + ## XX self.scratchDb.execute(sql) ''' @@ -925,15 +939,11 @@ self.tablesToExport=[] - # insertDvoIDs called below populates the given StackDetection - # tables with the DVO object IDs by querying the dvoDetection - # table for detections associated with the corresponding image - # IDs. XXX EAM 20140729 : Is this an efficient method? - - # EAM 20140730 : I have revised the StackObjectXX construction process. - # The challenge here is that we have dvo-generated objIDs and ippObjIDs - # for each stack detection in seperate rows, but we want to merge the - # 5 filter sets into a single set of rows. The old code was - # relying on INSERT IGNORE, making it hard to catch errors, - # and probably not doing the insert very efficiently + # EAM 20140730 : I have revised the StackObjectXX construction + # process. The challenge here is that we have dvo-generated + # objIDs and ippObjIDs for each stack detection in seperate + # rows, but we want to merge the 5 filter sets into a single + # set of rows. The old code was relying on INSERT IGNORE, + # making it hard to catch errors, and probably not doing the + # insert very efficiently # Now we do a first pass with SELECT DISTINCT to get the @@ -957,6 +967,4 @@ self.populateStackObjectExtra() - # self.populateRandomsAndSkyCellID() - if self.stackType != "NIGHTLY_STACK": @@ -971,6 +979,4 @@ self.populateStackPetrosian() - self.populateStackModelFit("Ser", "PS_MODEL_SERSIC") - self.logger.infoPair("populating ","StackApFlx Tables") self.populateStackApFlxThin() @@ -980,8 +986,13 @@ self.populateStackApFlx("C2") + # delete rows with no content. for the other tables, this is done in + # the 'populate' functions, but StackObjectThin needs to retain all rows + # until those tables are done + self.deleteRowsWithNoStackData("StackObjectThin") + self.logger.infoPair("populating","StackToImage") self.populateStackToImage() - # self.setMinMaxObjID(["StackObject"]) + self.setMinMaxObjID(["StackObjectThin"]) self.logger.infoPair("finishing","populatePspsTables");