IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 31965


Ignore:
Timestamp:
Aug 1, 2011, 4:05:53 PM (15 years ago)
Author:
rhenders
Message:

finally cleared up image_id/extern_id confusion

Location:
trunk/ippToPsps
Files:
5 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippToPsps/jython/detectionbatch.py

    r31951 r31965  
    533533    Updates provided table with DVO IDs from DVO table
    534534    '''
    535     def updateDvoIDs(self, table, sourceID, imageID):
    536 
    537         self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = " + imageID)
     535    def updateDvoIDs(self, table, sourceID, externID):
     536
     537        imageID = self.scratchDb.getImageIDFromExternID(sourceID, externID)
     538        self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID)
    538539        sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetection + " AS b SET \
    539540               a.ippObjID = b.ippObjID, \
    … …  
    617618                # store sourceID/imageID combo in Db so DVO can look up later
    618619                if not self.useFullTables:
    619                     self.scratchDb.insertNewDvoImage(header['SOURCEID'], header['IMAGEID'])
     620                    self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID'])
    620621
    621622                # store these for later

  • trunk/ippToPsps/jython/dvoToMySQL.py

    r31398 r31965  
    1010from subprocess import call, PIPE, Popen
    1111
    12 from gpc1db import Gpc1Db
     12from pslogger import PSLogger
    1313from scratchdb import ScratchDb
    1414
    … …  
    2727
    2828    '''
    29     def __init__(self, logger, pathToDvo):
     29    def __init__(self, logger):
    3030
    3131        # set up logging
    3232        self.logger = logger
    33         self.pathToDvo = pathToDvo
    34         self.logger.debug("DvoToMySql class constructor")
    35 
    36         self.logger.debug("Important DVO database at " + self.pathToDvo)
     33        self.logger.infoSeparator()
     34        self.logger.info("Started dvoToMySql")
     35        self.dvoMetaTable = "dvoMetaFull"
     36        self.dvoDetectionTable = "dvoDetectionFull"
    3737
    3838        # open config
    39         doc = ElementTree(file="config.xml")
     39        self.doc = ElementTree(file="config.xml")
     40        self.dvoLocation = self.doc.find("dvo/location").text
     41
     42        # some log stuff
     43        self.logger.infoPair("Using DVO meta table", self.dvoMetaTable)
     44        self.logger.infoPair("Using DVO detection table", self.dvoDetectionTable)
     45        self.logger.infoPair("Importing DVO database at", self.dvoLocation)
    4046
    4147        # create database objects
    42         self.scratchDb = ScratchDb(logger)
    43         self.gpc1Db = Gpc1Db(self.logger)
     48        self.scratchDb = ScratchDb(logger, self.doc, 1)
    4449
    4550        # create DVO tables
    … …  
    4752
    4853        # import Images.dat table
     54        self.logger.infoPair("Deleting from table", self.dvoMetaTable)
    4955        sql = "DELETE FROM dvoMetaFull"
    50         self.scratchDb.stmt.execute(sql)
    51 
    52         imagesTableName = self.importFits(self.pathToDvo,
     56        self.scratchDb.execute(sql)
     57
     58        imagesTableName = self.importFits(self.dvoLocation,
    5359                "",
    5460                "Images.dat",
    55                 "IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR")
    56         self.scratchDb.createIndex(imagesTableName, "IMAGE_ID")
     61                "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR")
     62        self.scratchDb.createIndex(imagesTableName, "EXTERN_ID")
    5763
    5864        # insert into dvoMetaFull
     65        # NB what we and smf files call IMAGE_ID, DVO calls EXTERN_ID. We are sticking
     66        # with the smf name
    5967        self.logger.info("Inserting all image meta data into database")
    60         sql = "INSERT INTO dvoMetaNew ( \
     68        sql = "INSERT INTO dvoMetaFull ( \
    6169               sourceID, \
    6270               imageID, \
    … …  
    7179               PHOTCODE \
    7280               FROM " + imagesTableName
    73         self.scratchDb.stmt.execute(sql)
     81        self.scratchDb.execute(sql)
    7482
    7583        subdirs = ['n0000']
    … …  
    7785        for subdir in subdirs:
    7886
    79             files = glob.glob(pathToDvo + "/" + subdir + "/*.cpm")
     87            files = glob.glob(self.dvoLocation + "/" + subdir + "/*.cpm")
    8088
    8189            #files = ['0247.06', '0244.06', '0244.10']
    … …  
    9098
    9199                # import cpm table and index
    92                 cpmTableName = self.importFits(self.pathToDvo,
     100                cpmTableName = self.importFits(self.dvoLocation,
    93101                        subdir,
    94102                        file + ".cpm",
    … …  
    98106
    99107                # import cpt table and index
    100                 cptTableName = self.importFits(self.pathToDvo,
     108                cptTableName = self.importFits(self.dvoLocation,
    101109                        subdir,
    102110                        file + ".cpt",
    … …  
    107115                # shove SOURCE_IDs into measurement table
    108116                self.logger.info("Adding SOURCE_IDs into measurements table")
    109                 sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (SOURCE_ID SMALLINT)"
    110                 self.scratchDb.stmt.execute(sql)
    111                 sql = "UPDATE "+cpmTableName+" AS a, "+imagesTableName+" AS b \
     117                sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (SOURCE_ID SMALLINT)"
     118                self.scratchDb.execute(sql)
     119                sql = "UPDATE " + cpmTableName + " AS a, " + imagesTableName + " AS b \
    112120                       SET a.SOURCE_ID = b.SOURCE_ID \
    113121                       WHERE a.IMAGE_ID = b.IMAGE_ID"
    114                 self.scratchDb.stmt.execute(sql)
     122                self.scratchDb.execute(sql)
    115123
    116124                # shove PSPS objID in measurement table
    117125                self.logger.info("Adding PSPS objID into measurements table")
    118126                sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)"
    119                 self.scratchDb.stmt.execute(sql)
     127                self.scratchDb.execute(sql)
    120128                sql = "UPDATE "+cpmTableName+" AS a, "+cptTableName+" AS b \
    121129                       SET a.PSPS_OBJ_ID = b.EXT_ID \
    122130                       WHERE a.CAT_ID = b.CAT_ID \
    123131                       AND a.OBJ_ID = b.OBJ_ID"
    124                 self.scratchDb.stmt.execute(sql)
    125 
    126                 self.logger.info("Putting everything into dvoDetectionFull table")
     132                self.scratchDb.execute(sql)
     133
     134                self.logger.infoPair("Populating", self.dvoDetectionTable)
    127135                sql = "INSERT IGNORE INTO dvoDetectionFull (\
    128136                       sourceID \
    … …  
    142150                       ,DB_FLAGS \
    143151                       FROM " + cpmTableName
    144                 self.scratchDb.stmt.execute(sql)
     152                self.scratchDb.execute(sql)
    145153
    146154                # now drop what we don't need
    … …  
    173181      tableName = tableName.replace('.', '_')
    174182
    175       self.logger.info("Attempting to import tables from '" + fullPath + "' to '" + tableName + "'")
     183      self.logger.infoPair("Importing tables from file", fullPath)
     184      self.logger.infoPair("Writing to database table", tableName)
    176185
    177186      tables = stilts.treads(fullPath)
    … …  
    195204          count = count + 1
    196205
    197       self.logger.info("Done. Imported %d tables" % count)
     206      self.logger.infoPair("Finished importing", "%d tables" % count)
    198207
    199208      return tableName
    200209
    201210logging.config.fileConfig("logging.conf")
    202 logger = logging.getLogger("dvotomysql")
    203 logger.info("Starting")
    204 
    205 dvoToMySql = DvoToMySql(logger, "/data/ipp005.0/gpc1/catdirs/MD04.merges/MD04.merge")
    206 #dvoToMySql = DvoToMySql(logger, "/export/ippc00.1/rhenders/MD04.merge")
    207 
    208 logger.info("Program complete")
    209 
     211logging.setLoggerClass(PSLogger)
     212logger = logging.getLogger("dvoToMySQLLog")
     213logger.setLevel(logging.INFO)
     214
     215
     216dvoToMySql = DvoToMySql(logger)
     217
     218logger.infoPair("Program...", "complete")
     219

  • trunk/ippToPsps/jython/scratchdb.py

    r31950 r31965  
    5252            return -1
    5353
     54    '''
     55    Gets imageID from extern ID
     56    '''
     57    def getImageIDFromExternID(self, sourceID, externID):
     58               
     59        imageID = -1
     60               
     61        sql = "SELECT imageID FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
     62        try:
     63            rs = self.executeQuery(sql)   
     64            rs.first()
     65            imageID = rs.getInt(1)
     66        except:
     67            self.logger.exception("Unable to get imageID from dvo meta table using " + sql)
     68             
     69        return imageID
     70
    5471    '''
    5572    Gets DVO image flags
    5673    '''
    57     def getDvoImageFlags(self, sourceID, imageID):
     74    def getDvoImageFlags(self, sourceID, externID):
    5875
    5976        flags = 0
    6077
    61         sql = "SELECT flags FROM " + self.dvoMeta + " WHERE sourceID = %s AND imageID = %s" % (sourceID, imageID)
     78        sql = "SELECT flags FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
    6279        try:
    6380            rs = self.executeQuery(sql) 
    … …  
    7188    Gets photcode (aka photoCalID from dvo table)
    7289    '''
    73     def getPhotoCalID(self, sourceID, imageID):
     90    def getPhotoCalID(self, sourceID, externID):
    7491
    7592        photcode = -1
    7693
    77         sql = "SELECT photcode FROM " + self.dvoMeta + " WHERE sourceID = %s AND imageID = %s" % (sourceID, imageID)
     94        sql = "SELECT photcode FROM " + self.dvoMeta + " WHERE sourceID = %s AND externID = %s" % (sourceID, externID)
    7895        try:
    7996            rs = self.executeQuery(sql) 
    … …  
    96113    Inserts a new sourceID/imageID combo into dvoMeta
    97114    '''
    98     def insertNewDvoImage(self, sourceID, imageID):
     115    def insertNewDvoExternID(self, sourceID, externID):
    99116
    100117        sql = "INSERT INTO dvoMeta ( \
    101118               sourceID, \
    102                imageID \
     119               externID \
    103120               ) VALUES (\
    104121               " + str(sourceID) + ", \
    105                " + str(imageID) + "    \
     122               " + str(externID) + "   \
    106123               )"
    107124        self.execute(sql)
    … …  
    152169               sourceID INT, \
    153170               imageID INT, \
     171               externID INT, \
    154172               flags INT, \
    155173               photcode INT, \
    156                PRIMARY KEY (sourceID, imageID) \
     174               PRIMARY KEY (sourceID, imageID, externID) \
    157175               )"
    158176
    … …  
    172190               PRIMARY KEY (sourceID, imageID, ippDetectID) \
    173191               )"
    174                #INDEX (sourceID), \
    175                #INDEX (imageID), \
    176                #INDEX (ippDetectID) \
    177192
    178193        try: self.execute(sql)

  • trunk/ippToPsps/jython/stackbatch.py

    r31951 r31965  
    8888       # insert sourceID/imageID combo so DVO can look it up
    8989       if not self.useFullTables:
    90            self.scratchDb.insertNewDvoImage(self.header['SOURCEID'], self.header['IMAGEID'])
     90           self.scratchDb.insertNewDvoExternID(self.header['SOURCEID'], self.header['IMAGEID'])
    9191
    9292    '''
    … …  
    588588    def updateDvoIDs(self, table):
    589589
     590        imageID = self.scratchDb.getImageIDFromExternID(self.header['SOURCEID'], self.header['IMAGEID'])
    590591        self.logger.debug("Updating table '" + table + "' with DVO IDs...")
    591592        sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetection + " AS b SET \
    … …  
    595596               WHERE a.ippDetectID = b.ippDetectID \
    596597               AND b.sourceID = " + self.header['SOURCEID'] + "\
    597                AND b.imageID = " + self.header['IMAGEID']
     598               AND b.imageID = " + str(imageID)
    598599        self.scratchDb.execute(sql)
    599600

  • trunk/ippToPsps/src/Dvo.c

    r31928 r31965  
    3535    mysql_query(this->mysql, "DELETE FROM dvoDetection");
    3636
    37     mysql_query(this->mysql, "SELECT sourceID, imageID FROM dvoMeta");
     37    mysql_query(this->mysql, "SELECT sourceID, externID FROM dvoMeta");
    3838    MYSQL_RES* result = mysql_store_result(this->mysql);
    3939
    … …  
    4141    MYSQL_ROW row;
    4242    char sql[500];
    43     int sourceID, imageID;
     43    int sourceID, externID;
    4444    while ((row = mysql_fetch_row(result))) {
    4545
    4646        sourceID = atoi(row[0]);
    47         imageID = atoi(row[1]);
     47        externID = atoi(row[1]);
    4848
    4949        this->logger->print(this->logger, MSG_INFO, "Dvo", "---------------------------------------------------------------------\n");
    50         this->logger->print(this->logger, MSG_INFO, "Dvo", "Getting skylist from DVO database for source ID = %d and image ID = %d\n",
    51                 sourceID, imageID);
    52         skyList = dvoSkyListByExternID(this->dvoConfig, sourceID, imageID, &image);
     50        this->logger->print(this->logger, MSG_INFO, "Dvo", "Getting skylist from DVO database for source ID = %d and extern ID = %d\n",
     51                sourceID, externID);
     52        skyList = dvoSkyListByExternID(this->dvoConfig, sourceID, externID, &image);
    5353        if (!skyList) {
    5454
    5555            this->logger->print(this->logger, MSG_ERROR,
    56                     "Dvo", "Could not find skylist for sourceId=%d and image ID = %d\n",
    57                     sourceID, imageID);
     56                    "Dvo", "Could not find skylist for sourceId=%d and extern ID = %d\n",
     57                    sourceID, externID);
    5858
    5959            continue;
    … …  
    6161
    6262        sprintf(sql,
    63                 "UPDATE dvoMeta SET flags = %d, photcode = %d WHERE sourceID = %d AND imageID = %d",
     63                "UPDATE dvoMeta SET imageID = %d,flags = %d, photcode = %d WHERE sourceID = %d AND externID = %d",
     64                image->imageID,
    6465                image->flags,
    6566                image->photcode,
    6667                sourceID,
    67                 imageID);
     68                externID);
    6869        mysql_query(this->mysql, sql);
    6970
    … …  
    9293                        "INSERT INTO dvoDetection (sourceID, imageID, ippDetectID, detectID, ippObjID, objID, flags) VALUES (%d, %d, %u, %lu, %lu, %lu, %u)",
    9394                        sourceID,   // sourceID
    94                         imageID,    // imageID
     95                        image->imageID,    // imageID
    9596                        dvoDetections[i].meas.detID,   // ippDetectID
    9697                        dvoDetections[i].meas.extID,   // detectID
Note: See TracChangeset for help on using the changeset viewer.