#!/usr/bin/env jython

import stilts
import datetime
import re
import sys
import os
import math
import logging
import glob
import tempfile
from subprocess import call, PIPE, Popen

from pslogger import PSLogger
from scratchdb import ScratchDb

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

'''
Abstract base-class for DVO classes

Provides generic functionality to pull relevant stuff from a DVO database into a scratch MySQL database while also providing access queries
'''
class Dvo(object):

    '''
    Constructor

    - sets up logging
    - checks we are using the correct DVO
    - checks we have up-to-date Images.dat
    - checks we have up-to-date SkyTable.fits

    '''
    def __init__(self, logger, config, skychunk, ippToPspsDb, scratchDb, gpc1Db):
#   def __init__(self, logger, config, skychunk, ippToPspsDb, scratchDb):

        # set up logging
        self.logger = logger
        self.config = config
        self.skychunk = skychunk
        self.ippToPspsDb = ippToPspsDb
        self.scratchDb = scratchDb
        self.gpc1Db = gpc1Db

        # set up empty lists
        self.regionsToIngest = []
        self.regionsToPurge = []
        self.allPopulatedRegions = []
        self.regionsAlreadyIngested = []
        self.regionsIngestedButOutOfDate = []
       
        # we USED TO have 3 modes of ingest from dvo cpt/cps/cpm files 
        # 1) dvograbber (useStilts == TRUE, useFullTables == FALSE), 
        # 2) jython/stilts (useStilts == TRUE, useFullTables == TRUE)
        # 3) dvopsps (useStilts == FALSE, useFullTables == FALSE)
        # my goal (EAM, 2013/01/31) is to migrate to only dvopsps

        self.scratchDb.setDvoTableNames()

        # decide if we are using the right DVO for this scratchDb
        self.correctDvo = self.scratchDb.isCorrectDvo(self.skychunk.dvoLocation)

        # this scratch Db either has no DVO info ingested, or a different DVO ingested. Prompt user
        if not self.correctDvo:
            self.logger.warning("switching to a new dvo: '" + self.skychunk.dvoLocation + "'")
            # response = raw_input("(y/n) ")
            # if response == "y":
            #     self.logger.warning("Are you ABSOLUTELY sure you want to do this?")
            #     response = raw_input("(y/n) ")
            #     if response == "y":
            self.resetAllTables() ## called if we need to change DVOs

    '''
    Destructor
    '''
    def __del__(self):
        self.logger.debug("Dvo destructor")

    '''
    Check we are importing the DVO currenly ingested
    '''
    def isThisDvoCurrentlyIngested(self):
        return self.correctDvo

    '''
    Resets all dvo tables. Be very careful....
    '''
    def resetAllTables(self):

        self.logger.infoPair("Resetting", "all tables")
        if not self.scratchDb.resetAllDvoTables(): return
        self.correctDvo = True
        if not self.loadSkyTable():
            os._exit(1)

        if not self.loadPhotcodes():
            os._exit(1)

        if not self.loadImages():
            self.logger.debugPair("FAILED to load images", "exiting")
            os._exit(1)

        print "DONE load images (resetAllTables)"

    '''
    Loads the DVO Photcodes.dat FITS file
    '''
    def loadPhotcodes(self):

        if (self.skychunk.dvoLocation == 'none'):
            # print "do not load Photcodes for place-holder DVO " + self.skychunk.dvoLocation
            return True

        # check if we have up-to-date version
        path = self.skychunk.dvoLocation + "/Photcodes.dat"
        if self.scratchDb.alreadyImportedThisDvoTable(path): 
            self.logger.debugPair("DVO Photcodes.dat file", "up-to-date")
            return True
        
        self.logger.infoSeparator()
        self.logger.infoPair("DVO Photcodes.dat file", "NOT up-to-date")

        self.importPhotcodes(
                path,
                self.scratchDb.dvoPhotcodesTable)
        self.logger.infoPair("Adding primary key to", self.scratchDb.dvoPhotcodesTable)
        self.scratchDb.makeColumnPrimaryKey(self.scratchDb.dvoPhotcodesTable, "CODE")

        self.scratchDb.setImportedThisDvoTable(path)
        return True

    '''
    Loads the DVO Images.dat FITS file
    '''
    def loadImages(self):

        # check if we have up-to-date version
        path = self.skychunk.dvoLocation + "/Images.dat"
        if self.scratchDb.alreadyImportedThisDvoTable(path): 
            self.logger.debugPair("DVO Images.dat file", "up-to-date")
            return True
        
        self.logger.infoSeparator()
        self.logger.infoPair("DVO Images.dat file", "NOT up-to-date")

        # check on the format:
        try: 
            imagesHeaderPHU = self.readFitsHeader("PHU", path)
        except Exception,e:
            # print the error, wait a few secs then try again
            print str(e)
            self.logger.infoPair("Failed to find PHU for Images.dat", path)
            raise

        if "FORMAT" in imagesHeaderPHU:
            fileFormat = imagesHeaderPHU["FORMAT"]
        else:
            self.logger.infoPair("Missing FORMAT for Images.dat", path)
            raise

        self.logger.infoPair("got FORMAT for Images.dat", fileFormat)

        if not (fileFormat == "PS1_V6"):
            self.logger.infoPair("Invalid FORMAT for Images.dat (PS1_V6)", fileFormat)
            return False

        self.importFits(
                path,
                "SOURCE_ID IMAGE_ID EXTERN_ID FLAGS MCAL_PSF MCAL_APER SECZ PHOTCODE XPIX_SYS_ERR YPIX_SYS_ERR N_FIT_ASTROM MAG_SYS_ERR N_FIT_PHOTOM",
                self.scratchDb.dvoImagesTable)
        self.logger.infoPair("Adding primary key to", self.scratchDb.dvoImagesTable)
        self.scratchDb.execute("ALTER TABLE " + self.scratchDb.dvoImagesTable + " ADD PRIMARY KEY (IMAGE_ID, EXTERN_ID)")

        self.scratchDb.execute("CREATE INDEX externID_index ON dvoImagesFull (EXTERN_ID)")
        self.scratchDb.setImportedThisDvoTable(path)
        return True

    '''
    executes the dvopsps call with polling to check the status of the table
    '''
    def dvopspsRunWithPoll(self,cmd,myTable,waittime):

        # sql = "set global wait_timeout = 2"
        # print "---- sql: " + sql
        # self.scratchDb.execute(sql)

        (dvoStdoutFile, dvoStdoutName) = tempfile.mkstemp(prefix="dvopsps.stdout.", dir="/tmp")
        (dvoStderrFile, dvoStderrName) = tempfile.mkstemp(prefix="dvopsps.stderr.", dir="/tmp")

        self.logger.infoPair("Running dvopsps", cmd)
        self.logger.infoPair("Stdout to ", dvoStdoutName)
        self.logger.infoPair("Stderr to ", dvoStderrName)
        p = Popen(cmd, shell=True, stdout=dvoStdoutFile, stderr=dvoStderrFile)

        # If I set waitTime to the full value at the beginning, we will always wait a long time for short jobs.

        totalWait = 0
        dvopspsRunning = True
        while dvopspsRunning:
            p.poll()
            if p.returncode == None:
                time.sleep(2.0)

                totalWait += 2.0
                if (totalWait < waittime): continue
                totalWait = 0.0

                sql = "show table status where name = '" + myTable + "'"
                rs = self.scratchDb.executeQuery(sql)
                rs.first()
                tableName = rs.getString(1)
                tableRows = rs.getInt(5)
                print "--- " + tableName + " : " + str(tableRows)
                
                sql = "show tables"
                rs = self.gpc1Db.executeQuery(sql)
                rs.first()
                tableName = rs.getString(1)
                print "--- " + tableName
                
                sql = "show tables"
                rs = self.ippToPspsDb.executeQuery(sql)
                rs.first()
                tableName = rs.getString(1)
                print "--- " + tableName

                continue

            dvopspsRunning = False
            if p.returncode < 0:
                self.logger.errorPair("dvopsps terminated on signal for: ", myTable)
                os._exit(1)

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

        os.close(dvoStdoutFile)
        os.close(dvoStderrFile)

    '''
    Loads the DVO SkyTable into MySQL and then applies an index
    '''
    def loadSkyTable(self):
       
        if (self.skychunk.dvoLocation == 'none'):
            # print "do not load SkyTable for place-holder DVO " + self.skychunk.dvoLocation
            return True

        path =  self.skychunk.dvoLocation + "/SkyTable.fits"
        if self.scratchDb.alreadyImportedThisDvoTable(path): 
            self.logger.debugPair("DVO SkyTable.fits file", "up-to-date")        
            return True

        # self.logger.infoSeparator()
        # self.logger.infoPair("DVO SkyTable.fits file", "NOT up-to-date")        
        # print "--- go"

        self.scratchDb.dropTable("dvoSkyTable")

        # create dvoSkyTable 
        sql = "CREATE TABLE dvoSkyTable (R_MIN REAL, R_MAX REAL, D_MIN REAL, D_MAX REAL, REGION_ID INT, NAME CHAR(18))"
        self.scratchDb.execute(sql)

        # TODO path to DVO prog hardcoded temporarily
        cmd = "dvopsps skytable"
        cmd += " -dbhost " + self.scratchDb.dbHost
        cmd += " -dbname " + self.scratchDb.dbName
        cmd += " -dbuser " + self.scratchDb.dbUser
        cmd += " -dbpass " + self.scratchDb.dbPass
        cmd += " -D CATDIR " + self.skychunk.dvoLocation

        self.dvopspsRunWithPoll(cmd, "dvoSkyTable", 30)

        self.logger.infoPair("Adding index to", self.scratchDb.dvoSkyTable)

        self.scratchDb.createIndex(self.scratchDb.dvoSkyTable, "REGION_ID")
        
        self.scratchDb.setImportedThisDvoTable(path)
        self.logger.infoPair("Finished importing SkyTable at", self.skychunk.dvoLocation)

        return True

    '''
    Sets a bunch of region files from DVO for a stripe starting at this RA and with this width
    '''
    def setSkyAreaAsStripe(self, ra=0., width=4.):

        print "this function should not reached now, error"
        raise

        self.setSkyArea(ra, ra+width, -91., 91.)

    '''
    Sets a bunch of region files from DVO for a RA/Dec box centered with these coords and with this side length
    '''
    def setSkyAreaAsBox(self, ra=180., dec=0., side=181.0):

        print "this function should not reached now, error"
        raise

        halfSide = side/2.0
        self.setSkyArea(ra-halfSide, ra+halfSide, dec-halfSide, dec+halfSide)

    '''
    Determines what DVO files need to be ingested to the defines region
    XXX EAM : this function has been substantially reduced / stripped down to work with the parallel dvo
    XXX EAM : some additional review of the goals would be useful
    '''
    def setSkyArea(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        

        # update the SkyTable
        self.loadSkyTable() ## in setSkyArea

        # set bounding-box coords
        self.minRa = minRa
        self.maxRa = maxRa
        self.minDec = minDec
        self.maxDec = maxDec

        # reset all lists
        allRegions = self.scratchDb.getDvoRegions(minRa, maxRa, minDec, maxDec)
        allIngestedRegions = self.scratchDb.getIngestedDvoRegions()
        self.regionsToIngest = []
        self.regionsToPurge = []
        self.allPopulatedRegions = []
        self.allPopulatedRegionInfo = []
        self.regionsAlreadyIngested = []
        self.regionsIngestedButOutOfDate = []
        tablesToCleanup = []

        # loop through all regions in DVO inside this bounding-box
        for region in allRegions:

           regionId = region[0]
           regionRa = region[1]
           regionDec = region[2]
           regionPath = region[3]

           # ok, have real, populated files so add this region to the list
           self.allPopulatedRegions.append(regionPath)
           self.allPopulatedRegionInfo.append([regionId, regionRa, regionDec])

           # if we have already imported up-to-date versions of all interested files, then we skip this region
           ## alreadyIngested = True
           ## for path in paths:
           ##     if not self.scratchDb.alreadyImportedThisDvoTable(path): 
           ##         alreadyIngested = False
           ##         break

           alreadyIngested = False
           if alreadyIngested:
               self.regionsAlreadyIngested.append(regionPath)
               continue

           # we have no record of ingesting this region, but does the table exist? 
           # May hev been left behind after a crash, and therefore needs to be cleaned-up
           for fileType in self.ingestFileTypes:
               tableName = self.scratchDb.getDbFriendlyTableName(regionPath + "." + fileType)
               if self.scratchDb.tableExists(tableName):
                   tablesToCleanup.append(tableName)

           # check if we have out-of-date versions of any interested files, if so, add region to the purge list
           ## outOfDate = False
           ## for path in paths:
           ##     if self.scratchDb.haveOutOfDateVersionOfThisDvoTable(path):
           ##         outOfDate = True
           ##         break
           ## 
           ## if outOfDate: self.regionsIngestedButOutOfDate.append(regionPath)

           print "using region: ", regionPath

           self.regionsToIngest.append(regionPath)

        # add out-of-range regions for purge list
        items = list(set(allIngestedRegions) - set(self.allPopulatedRegions))
        for item in items: self.regionsToPurge.append(item)

        # now add out-of-date items to purge list
        for item in self.regionsIngestedButOutOfDate: self.regionsToPurge.append(item)

        self.sizeOfAllPopulatedRegions = self.getDiskSizeOfRegions(self.allPopulatedRegions)
        self.sizeOfRegionsAlreadyIngested = self.getDiskSizeOfRegions(self.regionsAlreadyIngested)
        self.sizeOfRegionsIngestedButOutOfDate = self.getDiskSizeOfRegions(self.regionsIngestedButOutOfDate)
        self.sizeOfRegionsToPurge = self.getDiskSizeOfRegions(self.regionsToPurge)
        self.sizeOfRegionsToIngest = self.getDiskSizeOfRegions(self.regionsToIngest)

        # any tables need cleaning up?
        if len(tablesToCleanup) > 0:
            self.logger.infoPair("Found tables to be cleaned up", "%d" % len(tablesToCleanup))
            self.scratchDb.dropTables(tablesToCleanup)
            

    '''
    Prints out a summary of the currently set regions
    '''
    def printSummary(self):

        self.logger.infoTitle("Summary for currently set DVO region") 
        self.logger.infoPair("DVO ingest type", self.__class__.__name__) 

        # go no further if we've already partly ingested a different DVO
        if not self.correctDvo:
            self.logger.infoPair("Wrong DVO in use", self.skychunk.dvoLocation)
            return

        for fileType in self.ingestFileTypes:
            self.logger.infoPair("Ingesting files of type", fileType)

        self.logger.infoPair("RA range", "%.2f -> %.2f" % (self.minRa, self.maxRa))
        self.logger.infoPair("Dec range", "%.2f -> %.2f" % (self.minDec, self.maxDec))

        self.logger.info("+----------------+----------------+----------------+----------------+----------------+")
        self.logger.info("|   populated    |   ingested     |  out-of-date   |    to-purge    |   to ingest    |")
        self.logger.info("+----------------+----------------+----------------+----------------+----------------+")
        self.logger.info("| %5d (%6s) | %5d (%6s) | %5d (%6s) | %5d (%6s) | %5d (%6s) |" % (
                    len(self.allPopulatedRegions), self.getFileSizeStr(self.sizeOfAllPopulatedRegions), 
                    len(self.regionsAlreadyIngested), self.getFileSizeStr(self.sizeOfRegionsAlreadyIngested), 
                    len(self.regionsIngestedButOutOfDate), self.getFileSizeStr(self.sizeOfRegionsIngestedButOutOfDate),
                    len(self.regionsToPurge), self.getFileSizeStr(self.sizeOfRegionsToPurge),
                    len(self.regionsToIngest), self.getFileSizeStr(self.sizeOfRegionsToIngest))) 
        self.logger.info("+----------------+----------------+----------------+----------------+----------------+")

    '''
    Returns the size on disk of the regions that need to be ingested in GB
    '''
    def getDiskSizeOfRegionsToBeIngested(self):

        try:
            return self.sizeOfRegionsToIngest/1073741824.0
        except:
            return 0.0

    '''
    Purges a single region from the database
    '''
    def purgeRegion(self, region):

        regions = []
        regions.append(region)
        self.purgeRegions(regions)

    '''
    Purges the provided regions from the database
    '''
    def purgeRegions(self, regions):

        # go no further if we've already partly ingested a different DVO
        if not self.correctDvo: return False

        if len(regions) < 1: return False

        self.logger.infoPair("Purging", "%d regions" % len(regions)) 

        self.scratchDb.purgeTheseDvoRegions(regions, self.ingestFileTypes)

        ## detectionDeleteCount = self.scratchDb.purgeTheseDvoRegions(regions, self.ingestFileTypes)
        ## if detectionDeleteCount > 0: self.logger.infoPair("Deleted", "%d detections" % detectionDeleteCount)

        return True

    '''
    Formats a nice file size string
    Accepts a size in bytes
    '''
    def getFileSizeStr(self, byteSize):

        # format a string for the user -smaller than one MB
        if byteSize < 1048576: return "%.1f K" % (byteSize/1024.0)
        # smaller than one GB
        elif byteSize < 1073741824: return "%.1fM" % (byteSize/1048576.0)
        # more than a GB
        else: return "%.1fG" % (byteSize/1073741824.0)

    '''
    Returns the disk size of this file
    '''
    def getDiskSize(self, path):

        return os.stat(path).st_size

    '''
    Returns the disk size of these DVO regions, i.e. the size on disk of all relevant DVO FITS files
    '''
    def getDiskSizeOfRegions(self, regions):

        size = 0.0
        return size

        for region in regions:
             self.logger.infoPair("sizes for region", region)
             # get combined size of all interested files
             for fileType in self.ingestFileTypes:
                 print "trying ", fileType
                 size = size + self.getDiskSize(self.skychunk.dvoLocation + "/" + region + "." + fileType)
                 # EAM TEST I/O
                 self.logger.infoPair(fileType, size)

        return size


    '''
    Ingests object files for this region, given an INDEX from the SkyTable
    '''
    def ingestRegionUsingIndex(self, index):
        return self.ingestRegion(self.scratchDb.getRegionNameFromThisDvoIndex(index))

    '''
    Abstract method for ingesting a particular reegion. Must be implemented in all subclasses
    '''
    def ingestRegion(self, region):
        return self.logger.errorPair("ingestRegion()", "not implemented for this class") 

    '''
    Synchronises MySQL database with the sky area currently set from DVO
    Populates dvoDetections and dvoDone with FITS tables from DVO for the defined sky area, this
    includes purging detections outside sky area
    '''
    def sync(self):

        print "---- sync is disabled ----"
        os._exit(3)

        ### XXX # go no further if we've already partly ingested a different DVO
        ### XXX if not self.correctDvo: return False
        ### XXX 
        ### XXX # check we have something to do
        ### XXX if len(self.regionsToPurge) < 1 and len(self.regionsToIngest) < 1:
        ### XXX     self.logger.infoPair("Nothing to ingest or purge", "finished ingest")
        ### XXX     return True
        ### XXX 
        ### XXX # make sure we have an up-to-date Images table
        ### XXX self.loadImages()
        ### XXX 
        ### XXX # purge stuff
        ### XXX self.purgeRegions(self.regionsToPurge)
        ### XXX 
        ### XXX totalRegions = len(self.regionsToIngest)
        ### XXX if totalRegions > 0: self.logger.infoSeparator()
        ### XXX 
        ### XXX # now loop through all regions and ingest to MySQL
        ### XXX ingestedRegionsCount = 0
        ### XXX regionCount = 1
        ### XXX for region in self.regionsToIngest:
        ### XXX 
        ### XXX    self.logger.infoTitle("Region %d of %d: %s" % (regionCount, totalRegions, region))
        ### XXX    if self.ingestRegion(region): ingestedRegionsCount += 1
        ### XXX    regionCount += 1
        ### XXX 
        ### XXX self.logger.infoSeparator()
        ### XXX self.logger.infoPair("Ingested", "%d DVO regions (out of %d)" % (ingestedRegionsCount, len(self.regionsToIngest)))
        ### XXX 
        ### XXX # update lists after attempted sync
        ### XXX self.setSkyArea(self.minRa, self.maxRa, self.minDec, self.maxDec)
        ### XXX self.printSummary()

        return True

    '''
    Imports a FITS file. A regex filter lets you choose which tables to pull from the file
    An optional final argument lets you name the resultant database table
    '''
    def importFits(self, path, columns, tableName):

      self.logger.infoPair("Importing file", "%s (%s)" % (path, self.getFileSizeStr(self.getDiskSize(path))))
      self.logger.debugPair("Writing to database table", tableName)

      tables = stilts.treads(path)

      count = 0
      for table in tables:

          self.logger.debugPair("Reading IPP table", table.name)
          table = stilts.tpipe(table, cmd='explodeall')

          # adds an index to all the tables 
          # table = stilts.tpipe(table, cmd='addcol table_index $0')

          # IPP FITS files are littered with infinity values. Remove them
          self.logger.debugPair("Removing", "infinity values")
          table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
          table = stilts.tpipe(table, cmd='replaceval Infinity null *')

          # verify that all columns in 'columns' exist in the tabl
          ## myColumns = table.columns()
          ## for wcol in columns:
          ##     foundCol = False
          ##     for tcol in myColumns:
          ##         if (wcol == tcol.name):
          ##             print "found wcol " + wcol
          ##             foundCol = True;
          ##             break
          ##     if not foundCol:

          attempts = 0
          self.logger.debugPair("Writing table to database",  table.name)
          while True:
    
              try:
                  if attempts > 0: self.logger.infoPair("Attempt %d to write" % attempts, table.name)
                  table.cmd_keepcols(columns).write(self.scratchDb.url + '#' + tableName)
                  break
              except Exception,e:
                  # print the error, wait a few secs then try again
                  print str(e)
                  time.sleep(2)
                  attempts = attempts + 1

          count = count + 1

      self.logger.debugPair("Finished importing", tableName)

      return tableName

    '''
    Imports the Photcode file
    '''
    def importPhotcodes(self, path, tableName):
        if self.config.useOhanaMYSQLtoFITS:
            self.importPhotcodesOhana(path,tableName)
        else:
            self.importPhotcodesStilts(path,tableName)

    '''
    Imports the Photcode file
    '''
    def importPhotcodesOhana(self, path, tableName):

      self.logger.infoPair("Importing file with ohana", "%s (%s)" % (path, self.getFileSizeStr(self.getDiskSize(path))))
      self.logger.debugPair("Writing to database table", tableName)

      ## XXX may need to do this in mysql after table is read
      # convert the 1-char field TYPE to an int for querying ease (probably more sophisticated ways to do this...)
      # table = stilts.tpipe(table, cmd='addcol -after TYPE TYPE_AS_INT "TYPE + 0"')

      self.scratchDb.loadFITStoMYSQL (path, "-tablename " + tableName)

      sql = "ALTER TABLE " + tableName + " ADD COLUMN TYPE_AS_INT INT AFTER TYPE"
      self.scratchDb.execute(sql)

      sql = "UPDATE " + tableName + " SET TYPE_AS_INT = ascii(TYPE)"
      self.scratchDb.execute(sql)

      self.logger.debugPair("Finished importing", tableName)

      return tableName

    '''
    Imports the Photcode file
    '''
    def importPhotcodesStilts(self, path, tableName):

      self.logger.infoPair("Importing file", "%s (%s)" % (path, self.getFileSizeStr(self.getDiskSize(path))))
      self.logger.debugPair("Writing to database table", tableName)

      tables = stilts.treads(path)

      # complain if len(tables) > 1

      self.logger.debugPair("Reading IPP table", tables[0].name)
      table = stilts.tpipe(tables[0], cmd='explodeall')
     
      # IPP FITS files are littered with infinity values. Remove them (probably not needed for photcodes)
      self.logger.debugPair("Removing", "infinity values")
      table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
      table = stilts.tpipe(table, cmd='replaceval Infinity null *')

      # convert the 1-char field TYPE to an int for querying ease (probably more sophisticated was to do this...)
      table = stilts.tpipe(table, cmd='addcol -after TYPE TYPE_AS_INT "TYPE + 0"')

      attempts = 0
      self.logger.debugPair("Writing table to database",  table.name)
      while True:
    
          try:
              if attempts > 0: self.logger.infoPair("Attempt %d to write" % attempts, table.name)
              table.cmd_keepcols("CODE NAME TYPE_AS_INT C_LAM K").write(self.scratchDb.url + '#' + tableName)
              break
          except:
              # wait a few secs then try again
              time.sleep(2)
              attempts = attempts + 1
              
              count = count + 1
              
      self.logger.debugPair("Finished importing", tableName)

      return tableName


    '''
    Find and read a header extension.
    '''
    def readFitsHeader(self, extname, filename):

        cmd = "fhead -n %s %s" % (extname, filename)
        p = Popen(cmd, shell=True, stdout=PIPE)
        output = p.communicate()[0]        

        header = {}

        # split the output header bytes into key/value pairs for each 80-byte line
        for i in range(0, len(output), 80):
            # print "line %d : %s" % (i, output[i:i+80])
            record = output[i:i+80]

            match = re.match('^(HIERARCH )*([a-zA-Z0-9-_\.]+)\s*=\s+\'*([a-zA-Z0-9-+_\.:\s@#\(\)\,]+)\'*\\/*', record)

            if match:
                param = match.group(2)
                value = match.group(3).strip()
                if value == "NaN": value = "NULL"
                header[param] = value

        return header

    '''
    ingest skyregion into MySQL database using the native DVO program dvopsps
    Populates dvoDetections and dvoDone with FITS tables from DVO for the defined sky area, this
    includes purging detections outside sky area
    '''
    def nativeIngestDetections(self, boxId, boxDim, myBatchType):

        # clear the 'ingested' field for all boxes owned by this host
        self.ippToPspsDb.clearIngestedBoxes(self.scratchDb.dbHost)
        
        if False:
            self.logger.warning("about to delete detection table and re-ingest; is this correct?")
            response = raw_input("(y/n) ")
            if response != "y":
                raise
            self.logger.warning("Are you ABSOLUTELY sure you want to do this?")
            response = raw_input("(y/n) ")
            if response != "y":
                raise

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

        # make sure we have an up-to-date Images table
        if not self.loadImages(): # this is in nativeIngestDetections (remove?)
            self.logger.debugPair("FAILED to load images", "exiting")
            os._exit(1)

        print "DONE load images (nativeIngestDetections)"

        # the box dimensions are the area used to select the items of
        # interest from the gpc1 database (camera smfs / skycal cmfs).
        # for dvopsps, we need to include a border region large enough
        # to grab all detections / objects which are contributed by an
        # item with the center in the given box

        # P2 and DF need to use a full GPC1 exposure radius:
        myBorder = self.skychunk.BORDER_GPC1
        self.logger.infoPair("batch type", myBatchType)
        # ST and FW need to use just a skycell
        if myBatchType == "ST" :
            myBorder = self.skychunk.BORDER_SKYCELL
        if myBatchType == "FW" :
            myBorder = self.skychunk.BORDER_SKYCELL


        dR_border = myBorder / math.cos(math.radians(boxDim['DEC']))
        dD_border = myBorder

        minRA  = boxDim['minRA']  - dR_border
        maxRA  = boxDim['maxRA']  + dR_border
        minDEC = boxDim['minDEC'] - dD_border
        maxDEC = boxDim['maxDEC'] + dD_border

        #find out if we use a time_range or not
        valid_trange_start = 0
        valid_trange_end = 0
        use_trange = 0 
        if (self.skychunk.trange_start != '0000-00-00 00:00:00'):
            valid_trange_start = 1
        if (self.skychunk.trange_end != '0000-00-00 00:00:00'):
            valid_trange_end = 1             

        if (valid_trange_start and valid_trange_end):
            use_trange = 1

        cmd = "dvopsps detections"
        cmd += " -dbhost " + self.scratchDb.dbHost
        cmd += " -dbname " + self.scratchDb.dbName
        cmd += " -dbuser " + self.scratchDb.dbUser
        cmd += " -dbpass " + self.scratchDb.dbPass
        cmd += " -D CATDIR " + self.skychunk.dvoLocation
        cmd += " -region "
        cmd += " " + str(minRA)
        cmd += " " + str(maxRA)
        cmd += " " + str(minDEC)
        cmd += " " + str(maxDEC)

        if self.skychunk.parallel:
            cmd += " -parallel"

        if use_trange:
            # make tranges valid for dvopsps
            time_start1 = self.skychunk.trange_start
            time_end1 = self.skychunk.trange_end
            time_start=re.sub('\s+',',',time_start1)
            time_end=re.sub('\s+',',',time_end1)
           
            # make command
            cmd += " -time-start " + time_start
            cmd += " -time-end " + time_end

        useP2 = 0    
        useST = 0
        useFW = 0
        useDF = 0
        for batchType in self.skychunk.batchTypes:
            self.logger.infoPair("batchType", batchType)
            if batchType == "ST": useST = 1
            if batchType == "P2": useP2 = 1
            if batchType == "DF": useDF = 1
            if batchType == "FW": useFW = 1
                
        if (useP2 == 0 and useST == 1):
            # grab only stacks
            if (self.config.camera == "simtest"):
                # SIMTEST.x.SkyChip
                cmd += " -photcode-start 14100 -photcode-end 14500"
            else:
                # not gpc1test -> GPC1.x.SkyShip
                cmd += " -photcode-start 11000 -photcode-end 11500"

        if (useP2 == 1 and useST == 0):
            # grab only P2s (4000-4500 are test cameras, SIMTEST and SIMMOSAIC)
            if (self.config.camera == "simtest"):
                # SIMTEST.x.Chip
                cmd += " -photcode-start  4100 -photcode-end 4500"
            else:
                # not gpc1test -> GPC1.x.XYnn
                cmd += " -photcode-start 10000 -photcode-end 10577"

        if (useFW == 1):
            if (self.config.camera == "simtest"):
                # SIMTEST.x.ForcedWarp
                cmd += " -photcode-start 15100 -photcode-end 15500"
                # print "need photcodes for non gpc1 camera"
            else:
                cmd += " -photcode-start 12000 -photcode-end 12500"

        if (useST + useP2 + useDF + useFW > 1):
            self.logger.errorPair("multiple batch types in a run", "NOW ILLEGAL")
            os._exit(5)
                
        ## dvopsps can take a very long time. try 2 or 3 times to re-establish communication with the db before proceeding?
        ## NOTE: fixed with a poll?
        self.dvopspsRunWithPoll(cmd, self.scratchDb.dvoDetectionTable, 300)

        self.ippToPspsDb.setIngestedBox(boxId, self.scratchDb.dbHost)

        # run 'ANALYZE' to get indexes up-to-snuff
        sql = "ANALYZE TABLE " + self.scratchDb.dvoDetectionTable
        try: self.scratchDb.execute(sql)
        except: 
            self.logger.errorPair("Unable to analyze mysql table", self.scratchDb.dvoDetectionTable)
            return False

        # update lists after attempted sync
        # self.printSummary()

        return True
