#!/usr/bin/env jython

import re
import sys
import os
import logging
import tempfile
from subprocess import call, PIPE, Popen

from java.lang import *
from java.sql import *

from mysql import MySql

'''
Class for local scratch database connectivity
'''
class ScratchDb(MySql):

    '''
    Constructor
    '''
    def __init__(self, logger, config):

        # create gpc1 database objects
        # XXX the test option is probably not needed since
        # the scratchDb config infor is set up specially
        if (config.test): 
            dbType = "localdatabase_test"
        else:
            dbType = "localdatabase"

        # print "trying to connect: ", dbType

        dbName = config.getDbName(dbType)

        super(ScratchDb, self).__init__(logger, config, dbType, dbName)

        self.dvoDoneTable = "dvoDone"
        self.dvoSkyTable = "dvoSkyTable"
        self.dvoPhotcodesTable = "dvoPhotcodes"
        self.memoryTableSize = config.getDbMemory(dbType)

    '''
    Destructor
    '''
    def __del__(self):

        self.logger.debug("ScratchDb destructor")


    '''
    Sets whether we are using normal or pre-ingested DVO tables
    '''
    def setDvoTableNames(self):

        self.dvoImagesTable = "dvoImagesFull"
        self.dvoDetectionTable = "dvoDetectionFull"

        self.logger.debugPair("Using DVO images table", self.dvoImagesTable)
        self.logger.debugPair("Using DVO detection table", self.dvoDetectionTable)

    '''
    Creates a stored procedure for psfLikelihood. This uses an implementation of the complimentary error function taken from Numerical Recipes in C.
    '''
    def createPsfLikelihoodFunction(self):

        sql = "DROP FUNCTION IF EXISTS `psfLikelihood`"
        try:
            self.execute(sql)
        except:
            self.logger.errorPair("Could not drop stored procedure", "psfLikelihood")
            return False

        sql = "CREATE FUNCTION `psfLikelihood`(x REAL) RETURNS REAL \
               DETERMINISTIC \
               COMMENT 'Returns the psfLikelihood using the complimentary error function with fractional error everywhere less than 1.2 x 10-7. From Numerical Recipes in C' \
               BEGIN \
               DECLARE ans REAL; \
               DECLARE t REAL; \
               DECLARE z REAL; \
               DECLARE s INT; \
               SET s= SIGN(x); \
               SET x = ABS(x) / 1.4142135623731; \
               SET z=ABS(x); \
               SET t=1.0 / (1.0 + 0.5*z); \
               SET ans = t * EXP(-z*z-1.26551223+t*(1.00002368+t*(.37409196+t*(.09678418+t*(-.18628806+t*(.27886807+t*(-1.13520398+t*(1.48851587+t*(-.82215223+t*.17087277))))))))); \
               IF x < 0.0 THEN \
               SET ans = 2.0 - ans; \
               END IF; \
               SET ans = ans * s; \
               RETURN ans; \
               END"

        try:
            self.execute(sql)
            self.logger.debugPair("Installed stored procedure", "psfLikelihood")
            return True
        except:
            self.logger.errorPair("Could not install stored procedure", "psfLikelihood")
            return False

    '''
    Gets survey ID for this survey name
    '''
    def getSurveyID(self, name):
     
        sql = "SELECT surveyID FROM Survey WHERE name = '" + name + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            surveyID = rs.getInt(1)
        except:
            self.logger.errorPair("No survey ID found for survey", name)
            surveyID = 254

        return surveyID

    '''
    Gets filter ID for this filter name
    '''
    def getFilterID(self, name):
     
        sql = "SELECT filterID FROM Filter WHERE filterType = '" + name + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            filterID = rs.getInt(1)
        except:
            self.logger.errorPair("No filter ID found for filter", name)
            filterID = -1

        return filterID

    '''
    Get the fitModelID from FitModel table based on model name
    '''
    def getFitModelID(self, name):
        
        if name == 'NONE':
            self.logger.infoPair("No valid PSF model ID: ", name)
            return 0

        sql = "SELECT fitModelID FROM FitModel WHERE name = '" + name + "'"
        rs = self.executeQuery(sql)

        if not rs.first():
            self.logger.errorPair("No fitModelID found for model name", name)
            print "--- sql: " + sql
            os._exit(2)

        fitModelID = rs.getInt(1)
        return fitModelID

    '''
    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 
    ''' 
    def getImageIDFromExternID(self, externID): 
               
        imageID = -1 
               
        sql = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID) 
        self.logger.info(sql)
        try: 
            rs = self.executeQuery(sql)   
            rs.first() 
            imageID = rs.getInt(1) 
        except: 
            self.logger.errorPair("Unable to get imageID from dvo using", sql) 
              
        return imageID 

    ''' 
    Gets imageID from extern ID and photcoderange (needed for FW, possibly for others)
    ''' 
    def getImageIDFromExternIDandPhotcodeRange(self, externID, r1, r2): 
               
        imageID = -1 
               
        sql1 = "SELECT IMAGE_ID FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID) 
        range = " AND PHOTCODE >= " + str(r1) +" AND PHOTCODE <=" + str(r2)
        sql = sql1 + range

        self.logger.info(sql)
        try: 
            rs = self.executeQuery(sql)   
            rs.first() 
            imageID = rs.getInt(1) 
        except: 
            self.logger.errorPair("Unable to get imageID from dvo using", sql) 
              
        return imageID 





    '''
    Gets DVO image flags
    '''
    def getDvoImageFlags(self, externID):

        flags = 0

        sql = "SELECT FLAGS FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
        rs = self.executeQuery(sql)  

        if not rs.first():
            self.logger.errorPair("Unable to get flags from dvo using", sql)
        else:
            flags = rs.getInt(1)

        return flags

    '''
    Gets photcode (aka photoCalID from dvo table)
    '''
    def getPhotoCalID(self, externID):

        photcode = -1

        sql = "SELECT PHOTCODE FROM " + self.dvoImagesTable + " WHERE EXTERN_ID = %s" % (externID)
        rs = self.executeQuery(sql)  

        if not rs.first():
            self.logger.errorPair("Unable to get photcode from dvo with", sql)
        else:
            photcode = rs.getInt(1)

        return photcode

    '''
    Gets photcode (aka photoCalID from dvo table)
    '''
    def getPhotcodeByName(self, photcodeName):

        photcode = -1

        sql = "SELECT CODE FROM %s WHERE NAME = '%s'" % (self.dvoPhotcodesTable, photcodeName)
        rs = self.executeQuery(sql)  

        if not rs.first():
            self.logger.errorPair("No photcode found for ", photcodeName)
            print "--- sql: " + sql
            os._exit(2)

        photcode = rs.getInt(1)
        return photcode

    '''
    Gets photcode (aka photoCalID from dvo table)
    '''
    def getImageZeroPoint(self, externID):

        zeroPoint = -999

        # MCAL_PSF, MCAL_APER, SECZ are from dvoImagesTabl
        # C_LAM, K are from dvoPhotcodeTable
        sql = "SELECT MCAL_PSF, SECZ, C_LAM, K FROM %s AS a JOIN %s as b WHERE EXTERN_ID = %s AND a.PHOTCODE = b.CODE" % (self.dvoImagesTable, self.dvoPhotcodesTable, (externID))
        rs = self.executeQuery(sql)  
        if not rs:
            print "missing result set for imageID query"
            os._exit(2)

        if not rs.first():
            self.logger.infoPair("no zero point; image is missing, externID: ", externID)
            return zeroPoint
            
        Mcal     = rs.getFloat(1)
        airmass  = rs.getFloat(2)
        Clam     = rs.getFloat(3)
        Klam     = rs.getFloat(4)

        zeroPoint = 0.001*Clam + Klam*(airmass - 1.0) - Mcal
        return zeroPoint

    '''
    Gets zero point for image (PSF and APER)
    '''
    def getStackZeroPoint(self, externID):

        zeroPointPSF  = -999
        zeroPointAPER = -999

        # MCAL_PSF, MCAL_APER, SECZ are from dvoImagesTabl
        # C_LAM, K are from dvoPhotcodeTable
        sql = "SELECT MCAL_PSF, MCAL_APER, SECZ, C_LAM, K FROM %s AS a JOIN %s as b WHERE EXTERN_ID = %s AND a.PHOTCODE = b.CODE" % (self.dvoImagesTable, self.dvoPhotcodesTable, (externID))
        rs = self.executeQuery(sql)  
        if not rs:
            print "missing result set for imageID query"
            os._exit(2)

        if not rs.first():
            self.logger.infoPair("no zero point; image is missing, externID: ", externID)
            return zeroPoint
            
        McalPSF  = rs.getFloat(1)
        McalAPER = rs.getFloat(2)
        airmass  = rs.getFloat(3)
        Clam     = rs.getFloat(4)
        Klam     = rs.getFloat(5)

        zeroPointPSF  = 0.001*Clam + Klam*(airmass - 1.0) - McalPSF
        zeroPointAPER = 0.001*Clam + Klam*(airmass - 1.0) - McalAPER
        return (zeroPointPSF, zeroPointAPER)

    '''
    Updates a table with filterID grabbed from Filter init table
    '''
    def updateFilterID(self, table, filter):

        sql = "UPDATE "+table+" AS a, Filter AS b SET a.filterID=b.filterID WHERE b.filterType = '" + filter + "'"
        self.execute(sql)

    '''
    Updates a table with the recalibration stats grabbed from dvoImages table
    '''
    def updateRecalStats(self, table, externID, pltScale):

        sql = "UPDATE "+table+" AS a,"+self.dvoImagesTable+" AS b \
               SET \
               a.recalAstroScatX = b.XPIX_SYS_ERR *"+pltScale+", \
               a.recalAstroScatY = b.YPIX_SYS_ERR *"+pltScale+", \
               a.recalNAstroStars = b.N_FIT_ASTROM, \
               a.recalPhotoScat  = b.MAG_SYS_ERR, \
               a.recalNPhotoStars = b.N_FIT_PHOTOM \
               WHERE b.EXTERN_ID = "+externID
        self.execute(sql)

    '''
    Inserts a new sourceID/imageID combo into dvoImages
    '''
    def insertNewDvoExternID(self, sourceID, externID):

        sql = "INSERT INTO " + self.dvoImagesTable + " ( \
               SOURCE_ID, \
               EXTERN_ID \
               ) VALUES (\
               " + str(sourceID) + ", \
               " + str(externID) + "   \
               )"
        self.execute(sql)

    '''
    Do we have an out-of-date version of this file?
    '''
    def haveOutOfDateVersionOfThisDvoTable(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.debugPair("Have out-to-date version of",  path)
                return True
            else:
                return False
        except:
            self.logger.exception("Unable to check whether this DVO table is out-of-date")

    '''
    Have we already imported this DVO table?
    Checks path and modified date to be sure
    '''
    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.infoPair("Already imported up-to-date version of",  path)
                return True
            else:
                self.logger.infoPair("Did NOT already import from",  path)
                return False
        except:
            print "***** exception in alreadyImportedThisDvoTable *****"
            self.logger.exception("Unable to check whether this DVO table has been imported")

    '''
    Updates dvoDone table with this DVO table
    '''
    def setImportedThisDvoTable(self, path, region="null"):

        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 + " (region, path, modifiedDate, size) VALUES ('" + region + "', '" + path + "', " + str(str(fileStat.st_mtime)) + ", " + str(str(fileStat.st_size)) + ")"
        self.execute(sql)


    '''
    Gets the id for the provided file
    '''
    def getDvoFileId(self, path):

        id = -1
        sql = "SELECT id FROM " + self.dvoDoneTable + " WHERE path = '" + path + "'"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            id = rs.getInt(1)
        except:
            self.logger.errorPair("Could not get file id from " + self.dvoDoneTable + "for", path)

        return id
                                                                                                
    '''
    Returns a list of DVO regions currently ingested
    '''
    def getIngestedDvoRegions(self):

        regions = []

        sql = "SELECT DISTINCT region FROM dvoDone WHERE region != 'null'"
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                regions.append(rs.getString(1))
            rs.close()
        except:
            self.logger.errorPair("Can't query for ingested regions", sql)

        return regions

    
    '''
    Gets total size of DVO files imported to this database
    Counts only cpm and cpt files
    '''
    def getTotalSizeOfIngestedDvoFiles(self):

        size = 0
        sql = "SELECT SUM(size) FROM " + self.dvoDoneTable + " WHERE path LIKE '%cpm' OR path LIKE '%cpt'"
        try:
            rs = self.executeQuery(sql)  
            rs.first()
            size = rs.getLong(1)
        except:
            self.logger.errorPair("Unable to get total ingested DVO size", sql)

        return size

    '''
    Returns a row count for this table
    '''
    def getMaxIPP_IDET(self, table):

        sql = "SELECT MAX(IPP_IDET) FROM " + table
        rs = self.executeQuery(sql)  

        if not rs.first():
            self.logger.exception("Could not count rows for table: '" + table + "'")
            return 0

        return rs.getInt(1)

    '''
    Checks whether DVO files we have ingested are from the DVO path provided
    Does this by checking the original path of the SkyTable.fits file
    '''
    def isCorrectDvo(self, path):

        sql = "show tables"
        try:
            rs = self.executeQuery(sql)  
            haveDone = False
            while (rs.next()):
                tableName = rs.getString(1)
                # print "table: " + tableName
                if tableName == self.dvoDoneTable:
                    haveDone = True
                    
            rs.close()
            if not haveDone:
                self.logger.info("dvoDone not yet built")
                return False

        except Exception, e:
            print str(e)
            self.logger.errorPair("problem examining table", sql)
            sys.exit(1)

        sql = "SELECT path FROM " + self.dvoDoneTable + " WHERE path LIKE '%SkyTable.fits'"
        try: rs = self.executeQuery(sql)  
        except Exception, e:
            print str(e)
            self.logger.errorPair("problem querying for dvo path", path)
            sys.exit(1)

        if not rs.next():
            self.logger.info("dvoDone exists, but is empty")
            return False

        try:
            head,tail = os.path.split(rs.getString(1))

            if head == path: 
                self.logger.debugPair("Already ingested stuff from DVO at", path)
                return True
            else: 
                self.logger.infoPair("DVO does not match. We have", head)
                return False

        except Exception, e:
            print str(e)
            self.logger.errorPair("problem searching for DVO at", path)
            sys.exit(1)

    '''
    Checks whether the astrometric solution is ok for this chip
    TODO the value of 50 for numAstroRef (provided by Gene) is hardcoded here, but this should be temporary anyway
    '''
    def astrometricSolutionOK(self, ota):

        sql = "SELECT numAstroRef FROM ImageMeta_" + ota

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) < 50:
                # print "bad astromety"
                self.logger.debug("Bad astrometric solution for",  ota)
                return False
            else:
                # print "good astromety"
                return True
        except:
            # print "query failed"
            self.logger.debug("Unable to check astrometric solution")
            return False

        return True


    '''
    Checks whether the given chipID is in the DVO Image table
    '''
    def haveThisChip(self, imageID, sourceID):

        sql = "SELECT EXTERN_ID FROM " \
            + self.dvoImagesTable + \
            " WHERE EXTERN_ID = " + str(imageID) + \
            " AND SOURCE_ID = " + str(sourceID)

        rs = self.executeQuery(sql)
        if not rs:
            print "missing result set for imageID query"
            raise

        # if no returned rows, return an error
        if not rs.next():
            self.logger.infoPair("DVO lacks imageID ", imageID)
            return False

        if rs.getInt(1) != int(imageID):
            self.logger.error("DVO has wrong imageID? ")
            self.logger.error("imageID: ", imageID)
            self.logger.error("EXTERN_ID: ", rs.getInt(1))
            self.logger.infoPair("sql: ", sql)
            os._exit(2)

        # print "DVO has imageID ", imageID
        return True

    '''
    Creates a table for for ID matching
    '''
    def createDvoTables(self):
    
        dvoImagesTable = "dvoImages"
        dvoDetectionTable = "dvoDetection"

        # drop and create Images table
        self.logger.infoPair("Creating DVO table", dvoImagesTable)
        self.dropTable(dvoImagesTable)

        sql = "CREATE TABLE " + dvoImagesTable + " ( \
               SOURCE_ID SMALLINT, \
               IMAGE_ID INT, \
               EXTERN_ID INT, \
               FLAGS INT, \
               MCAL_PSF FLOAT, \
               MCAL_APER FLOAT, \
               SECZ FLOAT, \
               PHOTCODE SMALLINT, \
               XPIX_SYS_ERR FLOAT, \
               YPIX_SYS_ERR FLOAT, \
               N_FIT_ASTROM INT, \
               MAG_SYS_ERR FLOAT, \
               N_FIT_PHOTOM INT, \
               PRIMARY KEY (IMAGE_ID, EXTERN_ID) \
               )"

        try: self.execute(sql)
        except: 
            self.logger.error("Unable to create DVO meta-data database table")

        # now detection table
        self.logger.infoPair("Dropping DVO table", dvoDetectionTable)
        self.dropTable(dvoDetectionTable)

        self.logger.infoPair("Creating DVO table", "dvoDetection")
        sql = "CREATE TABLE dvoDetection ( \
               objID BIGINT, \
               detectID BIGINT, \
               ippObjID BIGINT, \
               ippDetectID BIGINT, \
               imageID INT, \
               catID INT, \
               ra DOUBLE, \
               dec_ DOUBLE, \
               raErr FLOAT, \
               decErr FLOAT, \
               zpPSF FLOAT, \
               zpFactorPSF FLOAT, \
               zpAPER FLOAT, \
               zpFactorAPER FLOAT, \
               telluricExt FLOAT, \
               airmass FLOAT, \
               expTime FLOAT, \
               Mpsf FLOAT, \
               dMpsf FLOAT, \
               Mkron FLOAT, \
               dMkron FLOAT, \
               Map FLOAT, \
               dMap FLOAT, \
               flags INT, \
               objflags INT, \
               filtflags INT, \
               PRIMARY KEY (imageID, ippDetectID), \
               KEY (objID, detectID), \
               KEY (objID, ippDetectID) \
             )"

        try: self.execute(sql)
        except: 
            self.logger.error("Unable to create DVO detection database table")
            self.sys.exit(1)

        # XXX EAM : for the parallel mode, dvoDetection requires duplicate objID values!
        # self.makeColumnUnique("dvoDetection", "objID") 

    '''
    Removes the provided DVO FITS files from from dvoDone and dvoDetection tables
    Returns a count of the number of detections deleted, if any
    '''
    def purgeTheseDvoRegions(self, regions, fileTypes):

        # XXX this must have been a remnant from something else: we are deleting a region, not entries from the dvoDetectionTable
        # detectionCount = self.getRowCount(self.dvoDetectionTable)
        for region in regions:
          
           for fileType in fileTypes:
               sql = "DELETE FROM " + self.dvoDoneTable + " WHERE region = '" + region + "' AND path LIKE '%" + fileType + "'"
               try: self.execute(sql)
               except:
                    self.logger.errorPair("Unable to delete " + fileType + " file from", self.dvoDoneTable)

               tableName = self.getDbFriendlyTableName(region + "." + fileType) 
               self.dropTable(tableName)

        # deletedDetectionCount = detectionCount - self.getRowCount(self.dvoDetectionTable)

        # after a big detection delete, we should OPTIMIZE the table
        # if deletedDetectionCount > 0:
        #     self.logger.infoPair("Running OPTIMIZE on ", self.dvoDetectionTable)
        #     self.optimizeTable(self.dvoDetectionTable)

        return 1
           
    '''
    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
    '''
    def resetDvoDetectionTable(self):

       self.logger.infoPair("Dropping table", self.dvoDetectionTable)
       self.dropTable(self.dvoDetectionTable)

       # we need a big enough memory table for the incoming detections
       # print "requested memory in gigs: ", self.memoryTableSize
       tableSize = int(self.memoryTableSize) * 1024 * 1024 * 1024
       self.logger.infoPair("setting max heap size: ", tableSize)

       sql = "set @@max_heap_table_size = " + str(tableSize)
       self.execute(sql)

       # create detections table
       self.logger.infoPair("Creating table", self.dvoDetectionTable)
       sql = "CREATE TABLE " + self.dvoDetectionTable + " LIKE dvoDetection"
       self.execute(sql)

       # XXX changing the dvoDetectionFull table to MEMORY
       # self.changeEngineToInnoDB(self.dvoDetectionTable)

       # set engine to memory
       sql = "ALTER TABLE " + self.dvoDetectionTable + " ENGINE = MEMORY"
       try: self.execute(sql)
       except: 
           self.logger.errorPair("Unable to set engine to MEMORY for ", self.dvoDetectionTable)
           return False

       return True

    '''
    Drops and recreates tables necessary for dvoToMySQL program. Be very careful before using this...
    '''
    def resetAllDvoTables(self):

        # re-create the definition tables
        self.createDvoTables()

        # 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.dvoDoneTable)
        self.dropTable(self.dvoDoneTable) 

        # blow away existing dvoDetection table & re-crate
        if not self.resetDvoDetectionTable():
            self.logger.errorPair("Unable to reset scratch table", "DvoDetectionFull")
            return False

        # 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 create table", self.dvoDoneTable)
            return False

        self.changeEngineToInnoDB(self.dvoDoneTable)
            
        return True

    '''
    Gets a list of DVO regions that overlap with the defined box
    '''
    def getDvoRegions(self, minRa, maxRa, minDec, maxDec):

        self.logger.debug("Querying DVO SkyTable for FITS files in this region")

        regions = []
        self.getDvoRegionsForThisBox(regions, minRa, maxRa, minDec, maxDec)

        # deal with 0/360 boundary
        if minRa < 0: self.getDvoRegionsForThisBox(regions, minRa + 360, 361 ,minDec, maxDec)
        if maxRa > 360: self.getDvoRegionsForThisBox(regions, 0, maxRa - 360 ,minDec, maxDec)

        return regions

    '''
    Gets a list of DVO regions that overlap with the defined box
    '''
    def getDvoRegionsForThisBox(self, regions, minRa, maxRa, minDec, maxDec):

        sql = "SELECT \
               REGION_ID, \
               R_MIN+ (R_MAX - R_MIN)/2.0, \
               D_MIN + (D_MAX - D_MIN)/2.0, \
               NAME \
               FROM " + self.dvoSkyTable + " \
               WHERE NOT ((" + str(maxRa) + " <= R_MIN) OR \
                       (" + str(minRa) + " >= R_MAX) OR \
                       (" + str(maxDec) + " <= D_MIN) OR \
                       (" + str(minDec) + " >= D_MAX)) "

        try:
            rs = self.executeQuery(sql)
            while (rs.next()): regions.append([
                    rs.getInt(1), 
                    rs.getFloat(2), 
                    rs.getFloat(3), 
                    rs.getString(4)])

            rs.close()
        except:
            self.logger.exception("Can't query for DVO regions")


    '''
    Gets the id for the provided file
    '''
    def getRegionNameFromThisDvoIndex(self, index):

        region = ""
        sql = "SELECT NAME FROM " + self.dvoSkyTable + " WHERE REGION_ID = " + str(index)
        try:
            rs = self.executeQuery(sql)
            rs.first()
            region = rs.getString(1)
            rs.close()
        except:
            self.logger.errorPair("Could not get region using index", sql )

        return region

    '''
    Gets a count of average filters (secfilt) in the DVO Photcodes table
    '''
    def getCountOfFiltersFromPhotcodesTable(self):

        # 2 is hardwired -- convert to an enum (should probably read Ohana/src/libdvo/include/dvo.h defines
        sql = "SELECT count(TYPE_AS_INT) FROM " + self.dvoPhotcodesTable + " WHERE TYPE_AS_INT = 2"

        filterCount = 0
        try:
            rs = self.executeQuery(sql)
            row = 1
            while (rs.next()):
                filterCount = rs.getString(1)
            rs.close()
        except:
            self.logger.errorPair("Could not get filter count", sql )

        return filterCount

    '''
    Gets a list of filters ordered as thy are in the DVO Photcodes table, as this reflects the order they apper in the cps file
    '''
    def getOrderedListOfFiltersFromPhotcodesTable(self, interestedFilters):

        sql = "SELECT NAME FROM " + self.dvoPhotcodesTable + " WHERE "

        count = 0
        for filter in interestedFilters:
           if count != 0: sql = sql + " OR "
           sql = sql + " name = '" + filter + "'"
           count += 1

        sql = sql + " ORDER BY CODE"

        orderedFilters = []
        try:
            rs = self.executeQuery(sql)
            row = 1
            while (rs.next()):
                orderedFilters.append([row,rs.getString(1)])
                row += 1
            rs.close()
        except:
            self.logger.errorPair("Could not get ordered filter list", sql )

        return orderedFilters


    def getRaDecRangesFromdvoSkyTable(self, name):
     
        sql = "SELECT R_MIN, R_MAX, D_MIN, D_MAX FROM dvoSkyTable WHERE NAME = '" + name + "' limit 1"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            raMin = rs.getFloat(1)
            raMax = rs.getFloat(2)
            decMin = rs.getFloat(3)
            decMax = rs.getFloat(4)
           
        except:
            self.logger.errorPair("no ra/decs found for", name)
            raMin = -999
            raMax = -999
            decMin = -999
            decMax = -999

        return raMin, raMax, decMin, decMax
 
    def getFromdvoSkyTable(self, column, name):
     
        sql = "SELECT "+column + " FROM dvoSkyTable WHERE NAME = '" + name + "' limit 1"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            res = rs.getDouble(1)
                       
        except:
            self.logger.errorPair("no ra/decs found for", name)
            res = -999
            

        return res

    '''
    Use Ohana fits_to_mysql to load FITS tables into scratch db
    '''
    def loadFITStoMYSQL(self, filename, tablename=""):

        self.logger.infoPair("starting loadFITStoMYSQL: ", filename)

        # TODO path to DVO prog hardcoded temporarily
        cmd = "fits_to_mysql " + filename
        cmd += " -dbhost " + self.dbHost
        cmd += " -dbname " + self.dbName
        cmd += " -dbuser " + self.dbUser
        cmd += " -dbpass " + self.dbPass
        cmd += " " + tablename

        self.logger.debugPair("preparing command: ", cmd)

        (stdoutFile, stdoutName) = tempfile.mkstemp(prefix="fits_to_mysql.stdout.", dir="/tmp")
        (stderrFile, stderrName) = tempfile.mkstemp(prefix="fits_to_mysql.stderr.", dir="/tmp")

        self.logger.debugPair("generated tmpfiles: ", cmd)

        self.logger.infoPair("Running fits_to_mysql", cmd)
        self.logger.infoPair("Stdout to ", stdoutName)
        self.logger.infoPair("Stderr to ", stderrName)
        p = Popen(cmd, shell=True, stdout=stdoutFile, stderr=stderrFile)

        p.wait()

        if p.returncode < 0:
            self.logger.errorPair("fits_to_mysql terminated on signal for: ", cmd)
            os._exit(1)

        if p.returncode > 0:
            self.logger.errorPair("fits_to_mysql failed for: ", cmd)
            os._exit(1)

        os.close(stdoutFile)
        os.close(stderrFile)
