#!/usr/bin/env jython

import re
import sys
import os
import math
import logging

from mysql import MySql
from java.sql import *

'''
Class for ippToPsps database connectivity
'''
class IppToPspsDb(MySql):

    '''
    Constructor
    '''
    def __init__(self, logger, config, skychunk):
        if (config.test): 
            dbType = "ipptopspsdatabase_test"
        else:
            dbType = "ipptopspsdatabase"
        super(IppToPspsDb, self).__init__(logger, config, dbType)

        self.skychunk = skychunk

        self.MAX_FAILS = 15

    '''
    Returns a list of merged batch IDs that are merged but not yet deleted (but allowed to be deleted)
    '''
    def getMergedReadyToDeleteFromLocalDisk(self, batchType):
    
        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND comment = '" + self.skychunk.name + "' \
               AND datastore_product = '" + self.skychunk.datastoreProduct + "' \
               AND merged = 1 \
               AND deleted_local = 0 \
               AND block_delete_local = 0 "
    
        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for merged batch ids in ipptopsps Db")
    
        self.logger.debug("Found %d merged but un-deleted items" % len(ids))
    
        return ids

    '''
    Returns a list of batch IDs marked as 'purged' but not yet deleted (but allowed to be deleted)
    '''
    def getPurgedReadyToDeleteFromLocalDisk(self, batchType):
    
        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND comment = '" + self.skychunk.name + "' \
               AND datastore_product = '" + self.skychunk.datastoreProduct + "' \
               AND purged = 1 \
               AND deleted_local = 0 \
               AND block_delete_local = 0 "
    
        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for merged batch ids in ipptopsps Db")
    
    
        self.logger.debug("Found %d merged but un-deleted items" % len(ids))
    
        return ids

    '''
    Returns a list of batch IDs marked as 'purged' but not yet deleted (but allowed to be deleted)
    '''
    def getPurgedReadyToDeleteFromDatastore(self, batchType):
    
        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND comment = '" + self.skychunk.name + "' \
               AND datastore_product = '" + self.skychunk.datastoreProduct + "' \
               AND purged = 1 \
               AND deleted_datastore = 0 \
               AND block_delete_datastore = 0 "
    
        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for merged batch ids in ipptopsps Db")
    
    
        self.logger.debug("Found %d merged but un-deleted items" % len(ids))
    
        return ids

    '''
    Returns a list of processed batch IDs that have been loaded to the ODM but not yet deleted from the datastore
    '''
    def getLoadedToODMReadyToDeleteFromDatastore(self, batchType):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND comment = '" + self.skychunk.name + "' \
               AND datastore_product = '" + self.skychunk.datastoreProduct + "' \
               AND (merge_worthy = 1) \
               AND deleted_datastore = 0 AND block_delete_datastore = 0"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for merged batch ids in ipptopsps Db")

        self.logger.debug("Found %d merged but un-deleted items" % len(ids))

        return ids

    '''
    Returns a list of processed batch IDs that have been loaded to the ODM but not yet deleted from the dxlayer
    '''
    def getLoadedToODMReadyToDeleteFromDxLayer(self, batchType):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND (merge_worthy = 1) \
               AND deleted_dxlayer = 0 and block_delete_dxlayer = 0"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for merged batch ids in ipptopsps Db")

        self.logger.debug("Found %d merged but un-deleted items" % len(ids))

        return ids

    '''
    Returns a list of processed batch IDs that are not loaded to the datastore
    NB we have a date constraint here because, before this time, it is possible that
    a test batch would be labelled as processed but not loaded to datastore, and we would NOT
    want to load a test batch to PSPS

    We also check that these batches are more than 12 HOURs old, so that we don't attempt to 
    publish something simulataneously with the client that it producing it
    '''
    def getProcessedButFailedDatastoreBatchIDs(self, batchType):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND processed = 1\
               AND loaded_to_datastore != 1 \
               AND timestamp > '2011-10-27' \
               AND timestamp < now() -  INTERVAL 12 HOUR"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.exception("Can't query for processed batch ids not loaded to datastore ipptopsps Db")

        self.logger.debug("Found %d processed but-not-on-datastore items" % len(ids))

        return ids

    '''
    Returns a list of processed batch IDs that are not yet loaded to the ODM
    NOTE we exclude batches already deleted from the datastore, as sometimes we delete stuff prior to loading to ODM if there is a known problem with the batch
    '''
    def getUnloadedBatchIDs(self, batchType):

        sql = "SELECT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND loaded_to_datastore = 1 \
               AND batch_type = '" + batchType + "' \
               AND loaded_to_ODM = 0 \
               AND deleted_datastore = 0"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for unloaded batch ids in ipptopsps Db")
        finally:
            rs.close()

        self.logger.debug("Found %d unloaded items" % len(ids))

        return ids

    '''
    Returns a list of processed batch IDs that are not yet merge_worthy and that have not failed to load
    NOTE we exclude batches already deleted from the datastore, as sometimes we delete stuff prior to loading to ODM if there is a known problem with the batch
    '''
    def getUnmergeWorthyBatchIDs(self, batchType):

        sql = "SELECT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND loaded_to_datastore = 1 \
               AND batch_type = '" + batchType + "' \
               AND merge_worthy = 0 \
               AND loaded_to_ODM != -1 \
               AND deleted_datastore = 0"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for un-mergeworthy batch ids in ipptopsps Db")
        finally:
            rs.close()

        self.logger.debug("Found %d unloaded items" % len(ids))

        return ids

    '''
    Returns a list of processed batch IDs that are merge_worthy, but not yet merged, for this epoch and dvo label
    '''
    def getUnmergedBatchIDs(self, batchType):

        sql = "SELECT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND merge_worthy = 1 \
               AND batch_type = '" + batchType + "' \
               AND merged != 1"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for unmerged batch ids in ipptopsps Db")
        finally:
            rs.close()

        self.logger.debug("Found %d un-merged items" % len(ids))

        return ids

    '''
    Returns a list of batch IDs for this epoch and dvo label for which the specified column is set to value
    '''
    def getBatchIDs(self, column, value):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND " + column + " = " + str(value)

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for batch ids with " + column + " = " + str(value) + " in ipptopsps Db")
        finally:
            rs.close()

        self.logger.debug("Found %d batches with %s = %d" % (len(ids), column, value))

        return ids

    '''
    Returns the total detections published to the datastore for this epoch, dvo label and batch type
    '''
    def getTotalDetectionsPublished(self, batchType, interval="10 YEAR"):

        sql = "SELECT SUM(total_detections) \
               FROM batch \
               WHERE batch_type = '" + batchType + "' \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND processed = 1 \
               AND loaded_to_datastore = 1 \
               AND timestamp >= now() - INTERVAL " + interval + " AND timestamp < now()"

        total = -1
        try:
            rs = self.executeQuery(sql)
            rs.first()
            total =  rs.getLong(1)
        except:
            self.logger.errorPair("Can't get total detections published", sql)
        finally:
            rs.close()

        return total

    '''
    Returns a list of IDs for this batch type, epoch and dvo label (i.e. either 
            cam_ids or stack_ids) for which the specified column is set to value
    '''
    def getStageIDs(self, batchType, column, value):

        sql = "SELECT DISTINCT stage_id \
               FROM batch \
               WHERE batch_type = '" + batchType + "' \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND " + column + " = " + str(value)

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for ids with " + column + " = " + str(value) + " in ipptopsps Db")
        finally:
            rs.close()

        return ids

    '''
    Series of getter methods that utilise getBatchIDs() method above
    '''
    def getloadedToDatastoreBatchIDs(self):
        return self.getBatchIDs("loaded_to_datastore", 1)
    def getFailedLoadedToODMBatchIDs(self):
        return self.getBatchIDs("loaded_to_ODM", -1)

    '''
    TODO remove these
    Series of getter methods that utilise getStageIDs() method above
    '''
    def getProcessedIDs(self, batchType):
        return self.getStageIDs(batchType, "processed", 1)
    def getFailedProcessedIDs(self, batchType):
        return self.getStageIDs(batchType, "processed", -1)
    def getLoadedToDatastoreIDs(self, batchType):
        return self.getStageIDs(batchType, "loaded_to_datastore", 1)
    def getFailedLoadedToDatastoreIDs(self, batchType):
        return self.getStageIDs(batchType, "loaded_to_datastore", -1)
    def getLoadedToODMIDs(self, batchType):
        return self.getStageIDs(batchType, "loaded_to_ODM", 1)
    def getFailedLoadedToODMIDs(self, batchType):
        return self.getStageIDs(batchType, "loaded_to_ODM", -1)
    def getMergeWorthyIDs(self, batchType):
        return self.getStageIDs(batchType, "merge_worthy", 1)
    def getFailedMergeWorthyIDs(self, batchType):
        return self.getStageIDs(batchType, "merge_worthy", -1)
    def getMergedIDs(self, batchType):
        return self.getStageIDs(batchType, "merged", 1)
    def getFailedMergedIDs(self, batchType):
        return self.getStageIDs(batchType, "merged", -1)

    '''
    Returns a list of stages
    '''
    def getStages(self):
        return [
        'processed', 
        'loaded_to_datastore', 
        'loaded_to_ODM', 
        'merge_worthy', 
        'deleted_datastore', 
        'deleted_dxlayer', 
        'merged', 
        'deleted_local' ]

    '''
    Returns a count of batches processed in the specified time period.
    '''
    def countBatches(self, batchType, interval="10 YEAR"):

        sql = "SELECT COUNT(batch_id) \
               FROM batch \
               WHERE batch_type = '" + batchType + "' \
               AND processed = 1 \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND timestamp >= now() - INTERVAL " + interval + " AND timestamp < now()"

        try:
            rs = self.executeQuery(sql)
            if rs.first(): return rs.getInt(1)
        except:
            self.logger.exception("Unable to count batches in interval")
            return 0
        finally:
            rs.close()

    '''
    Returns the time (as a string) that the last batch was published to the datastore for
    this epoch, dvo label and batch type
    '''
    def getTimeOfLastBatchPublished(self, batchType):

        seconds = None

        sql = "SELECT TIMESTAMPDIFF(SECOND, timestamp, now()) \
               FROM batch \
               WHERE batch_type = '" + batchType + "' \
               AND loaded_to_datastore = 1 \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               ORDER BY timestamp DESC LIMIT 1"

        try:
            rs = self.executeQuery(sql)
            if rs.first(): seconds = rs.getFloat(1)
        except:
            self.logger.exception("Unable to get last batch published")

        if not seconds: return "Never"

        minutes = seconds/60.0
        hours = minutes/60.0
        days = hours/24.0
        weeks = days/7.0

        if seconds < 60: return "%.1f secs ago" % seconds
        elif minutes < 60: return "%.1f mins ago" % minutes
        elif hours < 48: return "%.1f hours ago" % hours
        elif days < 7: return "%.1f days ago" % days
        return "%.1f weeks ago" % weeks

    '''
    Updates min/max object ID on this table and batch 
    '''
    def updateDetectionStats(self, batchID, minObjID, maxObjID, totalDetections):

        sql = "UPDATE batch SET \
               min_obj_id = " + str(minObjID) + ", \
               max_obj_id = " + str(maxObjID) + ", \
               total_detections = " + str(totalDetections) + " \
               WHERE batch_id = " + str(batchID)

        self.execute(sql)

    '''
    Updates a batch-table column
    '''
    def updateColumn(self, batchID, column, value):

        sql = "UPDATE batch \
               SET " + column + " = " + str(value) + " \
               WHERE batch_id = " + str(batchID)

        self.execute(sql)

    '''
    Updates batch processed field 
    '''
    def updateProcessed(self, batchID, processed):
        self.updateColumn(batchID, "processed", processed)

    '''
    Updates batch deleted local field 
    '''
    def updateDeletedLocal(self, batchID, value):
        self.updateColumn(batchID, "deleted_local", value)

    '''
    Updates batch deleted datastore field 
    '''
    def updateDeletedDatastore(self, batchID, value):
        self.updateColumn(batchID, "deleted_datastore", value)

    '''
    Updates batch deleted dxlayer field 
    '''
    def updateDeletedDxlayer(self, batchID, value):
        self.updateColumn(batchID, "deleted_dxlayer", value)

    '''
    Updates batch loaded_to_datastore field 
    '''
    def updateLoadedToDatastore(self, batchID, loadedToDatastore):
        self.updateColumn(batchID, "loaded_to_datastore", loadedToDatastore)

    '''
    Updates ODM status for this batch 
    '''
    def updateOdmStatus(self, batchID, odmStatus):

        sql = "UPDATE batch \
               SET loaded_to_ODM = " + str(odmStatus['LOADEDTOODM']) + ", \
               merge_worthy = " + str(odmStatus['MERGEWORTHY']) + ", \
               merged = " + str(odmStatus['MERGED']) + " \
               WHERE batch_id = " + str(batchID)

        self.execute(sql)

    '''
    Is someone processing this item right now? Checks whether this was attempted within the last 12 HOUR
    '''
    def processingNow(self, batchType, stage_id):

        sql = "SELECT COUNT(*) \
               FROM batch \
               WHERE stage_id = " + str(stage_id) + " \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND batch_type != 'IN' \
               AND timestamp >= now() - INTERVAL 12 HOUR AND timestamp < now()"

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) > 0: 
                self.logger.debugPair(str(stage_id) + " is already being processed", "skipping")
                return True
            else: 
                return False
        except:
            self.logger.exception("Unable to check whether this batch is being processed")

    '''
    Gets the batch type of this batch
    '''
    def getBatchType(self, batch_id):

        sql = "SELECT batch_type \
               FROM batch \
               WHERE batch_id = " + str(batch_id)

        batch_type = "NA"
        try:
            rs = self.executeQuery(sql)
            rs.first()
            batch_type = rs.getString(1) 
        except:
            self.logger.exception("Unable to check whether this batch has already been processed")
 
        return batch_type

    '''
    Have we already processed this stage_id?
    '''
    def alreadyProcessed(self, batchType, stage_id):

        sql = "SELECT COUNT(*) \
               FROM batch \
               WHERE stage_id = " + str(stage_id) + " \
               AND batch_type = '" + batchType + "' \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND batch_type != 'IN' \
               AND processed = 1"

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) > 0: 
                self.logger.debugPair(str(stage_id) + " has already been published", "skipping")
                return True
            else: 
                return False
        except:
            self.logger.exception("Unable to check whether this batch has already been processed")

    '''
    Has this stage_id consistently failed to process?
    '''
    def consistentlyFailed(self, batchType, stage_id):

        sql = "SELECT COUNT(*) \
               FROM batch \
               WHERE stage_id = " + str(stage_id) + " \
               AND batch_type = '" + batchType + "' \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND processed = -1"

        try:
            rs = self.executeQuery(sql)
            rs.first()
            if rs.getInt(1) >= self.MAX_FAILS: 
                self.logger.errorPair(str(stage_id) + " has failed %d times" % self.MAX_FAILS, "skipping")
                return True
            else: 
                return False
        except:
            self.logger.exception("Unable to check whether this batch has consistently failed")

    '''
    Returns a list of stage_ids that have failed the max number of times
    '''
    def getConsistentlyFailedIDs(self, batchType):

        sql = "SELECT DISTINCT stage_id, COUNT(stage_id) AS numoccur \
               FROM batch \
               WHERE batch_type = '" + batchType + "' \
               AND timestamp > '" + self.skychunk.epoch + "' \
               AND dvo_db = '" + self.skychunk.dvoLabel + "' \
               AND processed = -1 \
               GROUP BY stage_id HAVING numoccur >= " + str(self.MAX_FAILS)

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                ids.append(rs.getInt(1))
        except:
            self.logger.exception("Can't query for consistantly failed ids in ipptopsps Db")

        rs.close()

        self.logger.debug("Found %d consistantly failed stage_ids for batch type='%s'" % (len(ids), batchType))

        return ids

    '''
    Locks the batch table. This will wait if the lock is held by someone else
    '''
    def lockBatchTable(self):
        self.lockTable("batch")

    '''
    Creates a new batch. This is done with a locked table in order that no two clients can start work on the same item, i.e. this is a crital section
    '''
    def createNewBatch(self, batchType, stageID):

        batchID = -1;

        if self.skychunk.force or \
            (not self.alreadyProcessed(batchType, stageID) \
            and not self.processingNow(batchType, stageID) \
            and not self.consistentlyFailed(batchType, stageID)): 
            
            # self.logger.infoPair("heather:","passed logic")

            sql = "INSERT INTO batch ( \
                   batch_type, \
                   stage_id, \
                   survey, \
                   dvo_db, \
                   datastore_product, \
                   comment \
                   ) VALUES ( \
                       '" + batchType + "', \
                       " + str(stageID) + ", \
                       '" + self.skychunk.survey + "', \
                       '" + self.skychunk.dvoLabel + "', \
                       '" + self.skychunk.datastoreProduct + "', \
                       '" + self.skychunk.name + "' \
                       )"
 
            # self.logger.infoPair("heather:",sql)

            self.execute(sql)

            sql = "SELECT MAX(batch_id) FROM batch"

            try:
                rs = self.executeQuery(sql)
                rs.first()
                batchID = rs.getInt(1)
                self.logger.debug("Created new batch in ippToPsps database with batchID = %d" % batchID)
            except:
                self.logger.exception("Unable to get batch ID")
                self.logger.infoPair("sql:",sql)

                batchID = -1

        return batchID;
     

    '''
    Inserts some detection metadata for this batch ID
    '''
    def insertDetectionMeta(self, batchID, expID, filter):

        sql = "INSERT INTO detection ( \
               batch_id \
               ,exp_id \
               ,filter \
               ) VALUES ( \
               " + str(batchID) + " \
               ," + str(expID) + " \
               ,'" + filter + "' \
               )"

        self.execute(sql)

    '''
    Inserts some stack metadata for this batch ID
    '''
    def insertStackMeta(self, batchID, filter, stackType):

        sql = "INSERT INTO stack ( \
               batch_id \
               ,filter \
               ,stack_type \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + filter + "' \
               ,'" + stackType + "')"

        self.execute(sql)

    '''
    Inserts some object metadata for this batch ID
    '''
    def insertObjectMeta(self, batchID, region):

        sql = "INSERT INTO object ( \
               batch_id \
               ,region \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + region + "' \
               )"

        self.execute(sql)

    '''
    Inserts some forced detection metadata for this batch ID
    '''
    def insertForcedWarpMeta(self, batchID, expID, filter):

        sql = "INSERT INTO forcedwarp ( \
               batch_id \
               ,exp_id \
               ,filter \
               ) VALUES ( \
               " + str(batchID) + " \
               ," + str(expID) + " \
               ,'" + filter + "' \
               )"

        self.execute(sql)



    '''
    Inserts some forcedobject metadata for this batch ID
    '''
    def insertForcedObjectMeta(self, batchID, region):

        sql = "INSERT INTO forcedobject ( \
               batch_id \
               ,region \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + region + "' \
               )"

        self.execute(sql)




    ''' 
    Inserts some diff metadata for this batch ID
    '''
    def insertDiffMeta(self, batchID, filter, diffType):

        sql = "INSERT INTO diff ( \
               batch_id \
               ,filter \
               ,diff_type \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + filter + "' \
               ,'" + diffType + "')"

        self.execute(sql)




    '''
    Inserts some diffobject metadata for this batch ID
    '''
    def insertDiffObjectMeta(self, batchID, region):

        sql = "INSERT INTO diffobject ( \
               batch_id \
               ,region \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + region + "' \
               )"

        self.execute(sql)

    '''
    Inserts some gaiaobject metadata for this batch ID
    '''
    def insertGaiaObjectMeta(self, batchID, region):

        sql = "INSERT INTO gaiaobject ( \
               batch_id \
               ,region \
               ) VALUES ( \
               " + str(batchID) + " \
               ,'" + region + "' \
               )"

        self.execute(sql)






    '''
    Resets a batch ready for re-loading to the datastore
    '''
    def resetBatch(self, batchID):

        sql = "UPDATE batch SET \
               loaded_to_datastore = 0, \
               loaded_to_ODM = 0, \
               merge_worthy = 0, \
               merged = 0, \
               deleted_datastore = 0, \
               deleted_dxlayer = 0, \
               comment = 'This batch was reset, ready for re-load to datastore' \
               WHERE batch_id = " + str(batchID)

        self.execute(sql)

    '''
    Pause loading clients
    '''
    def pauseLoaders(self, bool, ids):
        self.setColForLoaders("paused", bool, ids)

    '''
    Kill loading clients
    '''
    def killLoaders(self, bool, ids):
        self.setColForLoaders("killed", bool, ids)

    '''
    Sets the skychunk field for this client
    '''
    def setSkychunkForThisClient(self, skychunk, host, pid):

        self.execute("UPDATE clients SET skychunk = '" + skychunk + "' \
                WHERE host = '" + host + "' \
                AND pid = " + str(pid))

    '''
    Sets the skychunk field for a set of loader clients
    '''
    def setSkychunkForLoaders(self, skychunk, ids):

        for id in ids:
            self.execute("UPDATE clients SET skychunk = '" + skychunk + "' \
                    WHERE type = 'loader.py' \
                    AND id = " + str(id))

    '''
    Sets the value for a given column for a set of loader clients
    '''
    def setColForLoaders(self, col, bool, ids):

        if bool: value = 1
        else: value = 0

        for id in ids:
            self.execute("UPDATE clients SET " + col + " = " + str(value) + " \
                    WHERE type = 'loader.py' \
                    AND id = " + str(id))

    '''
    Remove client by id
    '''
    def removeClientsByIds(self, ids):
        for id in ids:
            self.execute("DELETE FROM clients WHERE id = " + str(id))

    '''
    Remove client
    '''
    def removeClient(self, host, pid):
        self.execute("DELETE FROM clients WHERE host = '" + host + "' AND pid = " + str(pid))

    '''
    Insert client
    '''
    def insertClient(self, type, host, pid):
        self.execute("INSERT INTO clients (timestamp, type, host, pid, skychunk) VALUES (now(), '" + type + "', '" + host + "', " + str(pid) + ", '" + self.skychunk.name + "')")

    '''
    Returns boolean value for this column in clients table
    '''
    def existsClient(self, host, pid):

        sql = "SELECT id FROM clients WHERE host = '" + host + "' AND pid = " + str(pid) + " LIMIT 1"

        try:
            rs = self.executeQuery(sql)
            if rs.first() is False: return False
        except:
            self.logger.exception("Unable to check if " + host + "-" + str(pid) + " is in clients table")

        return True

    '''
    Update clients, or inserts it for the first time
    '''
    def updateClient(self, type, host, pid):

        if self.existsClient(host, pid):
            self.execute("UPDATE clients SET timestamp = now() WHERE host = '" + host + "' AND pid = " + str(pid))
        else:
            self.insertClient(type, host, pid)

    '''
    Is this client paused?
    '''
    def isPaused(self, host, pid):
        return self.getClientsColumnBoolean("paused", host, pid)

    '''
    Is this client killed?
    '''
    def isKilled(self, host, pid):
        return self.getClientsColumnBoolean("killed", host, pid)

    '''
    Returns boolean value for this column in clients table
    '''
    def getClientsColumnBoolean(self, col, host, pid):

        sql = "SELECT " + col + " \
               FROM clients \
               WHERE host = '" + host + "' AND pid = " + str(pid)

        try:
            rs = self.executeQuery(sql)
            if rs.first() is False:  return False
            if rs.getInt(1) == 1: return True
            else: return False
        except:
            self.logger.exception("Unable to check whether " + col + " is true or false in clients table")

        return False

    '''
    Returns a list of available skychunks
    '''
    def getActiveSkychunkList(self):
        
        sql = "SELECT DISTINCT name FROM skychunk WHERE active = 1 ORDER BY name"

        skychunks = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): skychunks.append(rs.getString(1))
        except:
            self.logger.errorPair("Can't get skychunk names", sql)
        finally:
            rs.close()

        return skychunks

    '''
    Prompts user for info about a new or existing skychunk and lets them neter the various details
    '''
    def editSkychunk(self):

       print " ********************************************************************"
       print " *              Skychunk editor"
       print " *"
       response = raw_input(" * Name for new skychunk, or existing skychunk to edit? ")
       if response == "": return
       response = response.strip()
       self.skychunk.name = response

       # attempt to insert new skychunk, if it already exists then carry on with update
       sql = "INSERT INTO skychunk (name) VALUES ('" + self.skychunk.name + "')"
       try:        
           self.execute(sql) 
       except: pass

       # for each column in the skychunk table
       sql = "SHOW COLUMNS FROM skychunk"
       rs = self.executeQuery(sql)
       while (rs.next()):

           field = rs.getString(1)
           type = rs.getString(2)
           null = rs.getString(3)

           # things we don;t want the user to edit
           if field == 'timestamp': continue
           if field == 'name': continue
           if field == 'ingested_det': continue
           if field == 'ingested_obj': continue

           # get current value for this field, if any
           try:
               rs2 = self.executeQuery("SELECT " + field + " FROM skychunk WHERE name = '" + self.skychunk.name + "'")
               rs2.first()
               default = rs2.getString(1)
           except: pass

           question = " * " + field + " (" + type + ")"

           # there may not be a default value
           try: question = question +  " hit return to accept default of: '" + default + "'"
           except: pass

           # prompt the user for new value or to accept existing value
           question = question +  "? "
           response = raw_input(question)
           response = response.strip()
           print "response: '" + response + "'"
           if response != "":
               if type.find("varchar") != -1 or type.find("timestamp") != -1: quotes = "'"
               else: quotes = ""
               # print "UPDATE skychunk SET " + field + " = " + quotes + response + quotes + " WHERE name = '" + self.skychunk.name + "'"
               self.execute("UPDATE skychunk SET " + field + " = " + quotes + response + quotes + " WHERE name = '" + self.skychunk.name + "'")
       
       print " *"
       print " ********************************************************************"

    '''
    Returns skychunk for this client
    '''
    def getSkychunkForThisClient(self, host, pid):

        sql = "SELECT skychunk \
               FROM clients \
               WHERE host = '" + host + "' \
               AND pid = " + str(pid)

        try:
            rs = self.executeQuery(sql)
            rs.first()
            self.skychunk.name = rs.getString(1)
        except:
            self.logger.errorPair("No skychunk set for", "%s (pid=%d)" % (host, pid))
            self.skychunk.isLoaded = False
            return False

        return True

    '''
    Reads skychunk from the database and populates Skychunk object
    '''
    def readSkychunk(self, host, pid):

        # first get skychunk defined for this client
        if not self.getSkychunkForThisClient(host, pid): return False

        # now load that skychunk
        sql = "SELECT \
        datastore_product \
        ,datastore_type \
        ,datastore_publish \
        ,dvo_label \
        ,dvo_location \
        ,min_ra \
        ,max_ra \
        ,min_dec \
        ,max_dec \
        ,box_size \
        ,base_path \
        ,data_release \
        ,delete_local \
        ,delete_datastore \
        ,delete_dxlayer \
        ,epoch \
        ,survey \
        ,psps_survey \
        ,queue_P2 \
        ,queue_ST \
        ,queue_OB \
        ,queue_DF \
        ,queue_DO \
        ,queue_FW \
        ,queue_FO \
        ,queue_FG \
        ,queue_GO \
        ,parallel \
        ,P2_smf_version \
        ,ST_cmf_version \
        ,trange_start \
        ,trange_end \
        FROM skychunk \
        WHERE name = '" + self.skychunk.name + "'"
#        ,queue_FG \
        self.skychunk.batchTypes = []

        try:
            rs = self.executeQuery("show tables")
        except:
            self.logger.errorPair("cannot even show tables?", "Boo hoo")
            sys.exit(1)

        # while rs.next():
        #     print "table row"
        #     print rs.getString(1)
        # 
        # rs = self.executeQuery("select * from skychunk")
        # while rs.next():
        #     print "skychunk row"
        #     print rs.getString(1)
        # 
        # print "SQL (2): " + sql

        try:
            rs = self.executeQuery(sql)

        except:
            self.logger.errorPair("problem with sql query for skychunk", sql)
            sys.exit(1)

        if not rs.next():
            self.logger.errorPair("Could not read skychunk with name", self.skychunk.name)
            self.skychunk.isLoaded = False
            return False

        self.skychunk.datastoreProduct = rs.getString(1)
        self.skychunk.datastoreType = rs.getString(2)
        if rs.getInt(3) == 1: self.skychunk.datastorePublishing = True
        else: self.skychunk.datastorePublishing = False
        self.skychunk.dvoLabel = rs.getString(4)

        # if dvoLabel is null is can break queries, so set to something
        if not self.skychunk.dvoLabel: self.skychunk.dvoLabel = "none"
        self.skychunk.dvoLocation = rs.getString(5)
        self.skychunk.minRa = rs.getDouble(6)
        self.skychunk.maxRa = rs.getDouble(7)
        self.skychunk.minDec = rs.getDouble(8)
        self.skychunk.maxDec = rs.getDouble(9)

        self.skychunk.boxSize = rs.getDouble(10)

        self.skychunk.basePath = rs.getString(11)
        self.skychunk.processingVersion = rs.getInt(12)

        self.skychunk.deleteLocal = rs.getInt(13)
        self.skychunk.deleteDatastore = rs.getInt(14)
        self.skychunk.deleteDxLayer = rs.getInt(15)

        self.skychunk.epoch = rs.getString(16)

        self.skychunk.survey = rs.getString(17)
        self.skychunk.pspsSurvey = rs.getString(18)

        if rs.getInt(19) == 1: self.skychunk.batchTypes.append("P2")
        if rs.getInt(20) == 1: self.skychunk.batchTypes.append("ST")
        if rs.getInt(21) == 1: self.skychunk.batchTypes.append("OB")
        if rs.getInt(22) == 1: self.skychunk.batchTypes.append("DF")
        if rs.getInt(23) == 1: self.skychunk.batchTypes.append("DO")
        if rs.getInt(24) == 1: self.skychunk.batchTypes.append("FW")
###        if rs.getInt(25) == 1: self.skychunk.batchTypes.append("FG") fix this when P2 is done
        if rs.getInt(25) == 1: self.skychunk.batchTypes.append("FO")
        if rs.getInt(26) == 1: self.skychunk.batchTypes.append("FG")
        if rs.getInt(27) == 1: self.skychunk.batchTypes.append("GO")
        

        self.skychunk.force = True # TODO
        self.skychunk.parallel = False # TODO

        if rs.getInt(28) == 1: self.skychunk.parallel = True
        self.skychunk.P2_smf_version = rs.getString(29)
        # options: "use_new", "not_reproc", "use_original"

        self.skychunk.ST_cmf_version = rs.getString(30)
        # options: not used?

        # NOTE : the rs (ResultSet) class is somewhat annoying and finicky esp about timestamp values
        # for example, rs.getTimestamp(N) fails silently if the value is not a valid time.  this is
        # also true to getString() on the same column.  Docs for the ResultSet class can be found here:
        # http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSet.html?is-external=true

        # rsmd = rs.getMetaData()
        # print "got metadata"
        # print "number of columns: " + str(rsmd.getColumnCount())
        # print "label: " + rsmd.getColumnLabel(25)
        # print "label: " + rsmd.getColumnLabel(29)
        # print "name: " + rsmd.getColumnName(25)
        # print "name: " + rsmd.getColumnName(29)
        # print "typename: " + rsmd.getColumnTypeName(25)
        # print "typename: " + rsmd.getColumnTypeName(29)

        # epochStamp = rs.getTimestamp(16)
        # print "epoch: " + epochStamp.toString()

        # print "label: " + rsmd.getColumnLabel(16)
        # print "name: " + rsmd.getColumnName(16)
        # print "typename: " + rsmd.getColumnTypeName(16)

        self.skychunk.trange_start = rs.getString(31)
        # options: "YYYY-MM-DD,HH:MM:SS" or "0000-00-00 00:00:00"

        self.skychunk.trange_end = rs.getString(32) 
        # options: "YYYY-MM-DD,HH:MM:SS" or "0000-00-00 00:00:00"

        if self.skychunk.parallel: print "USING parallel"
        self.skychunk.isLoaded = True

        return self.skychunk.isLoaded

    '''
    Gets id of box with these coords
    XXX EAM : this code implies we can have multiple boxes at the same location for the same
    XXX EAM : skychunk distinguished by having different sizes.  that seems quite bad
    '''
    def getBoxId(self, ra, dec):

        # for the float comparison, need to use a small window
        minRA = ra - 0.01
        maxRA = ra + 0.01
        minDEC = dec - 0.01
        maxDEC = dec + 0.01

        sql = "SELECT id FROM box \
               WHERE skychunk  = '" + self.skychunk.name + "' \
               AND ra_center >= " + str(minRA) + " AND ra_center < " + str(maxRA) + " \
               AND dec_center >= " + str(minDEC) + " AND dec_center < " + str(maxDEC)

               # AND box_side = " + str(self.skychunk.boxSize)

        # print "sql for boxid: ", sql
       
        id = -1
        try:
            rs = self.executeQuery(sql)
            rs.first()
            id = rs.getInt(1)
        except:
            pass

        return id

    '''
    Remove all boxes and associated ids in pending table
    '''
    def removeAllBoxes(self):
        self.execute("DELETE FROM box WHERE skychunk = '" + self.skychunk.name + "'")

    '''
    Inserts new box into box table. If it already exisits, it returns existing box id 
    '''
    def insertBox(self, ra, dec):

        id = self.getBoxId(ra, dec)

        # does not already exists so insert
        if id < 0:

            sql = "INSERT INTO box \
                   (skychunk, ra_center, dec_center, box_side) \
                   VALUES \
                   ('" + self.skychunk.name + "', " + str(ra) + ", " + str(dec) + ", " + str(self.skychunk.boxSize) + ")"

            # print "insertBox: ", sql

            self.execute(sql)
            id = self.getBoxId(ra, dec)

        return id

    '''
    Inserts new box into box table. If it already exisits, it returns existing box id 
    '''
    def isBoxIngested(self, id, host):

        # set the field 'ingested' to 1 for this box
        sql = "select ingested from box where id = " + str(id)
        try:
            rs = self.executeQuery(sql)
            if not rs.next():
                print "no matching boxes for ", str(id)
                raise
            value = rs.getString(1)
        except:
            print "failed to find box for ", str(id)
            raise

        if value == host:
            return 1

        if value == "none":
            return 0

        if value is None:
            return 0

        print "box ingested host ", value, " does not match db host ", host
        raise

    '''
    Inserts new box into box table. If it already exisits, it returns existing box id 
    '''
    def setIngestedBox(self, id, host):

        # set the field 'ingested' to 1 for this box
        sql = "update box set ingested = '" + host + "' where id = " + str(id)
        try:
            self.execute(sql)
        except:
            print "failed to set box ", str(id), "as ingested"
            raise

    '''
    Clear ingested flag for boxes owned by this host
    '''
    def clearIngestedBoxes(self, host):

        # set the field 'ingested' to 1 for this box
        sql = "update box set ingested = 'none' where ingested = '" + host + "'"
        try:
            self.execute(sql)
        except:
            print "failed to clear ingested state for boxes owned by host ", host
            raise

    '''
    Inserts new box ids into the pending table 
    '''
    def insertPending(self, box_id, batchType, ids):

        print "starting insert pending"

        # first delete old pending items
        self.execute("DELETE FROM pending \
                WHERE box_id = " + str(box_id) + " \
                AND batch_type = '" + batchType + "'")

        for id in ids:
            # why is there an IGNORE here? we have deleted old versions already above
            sql = "INSERT INTO pending \
               (box_id, batch_type, stage_id) \
               VALUES \
               (" + str(box_id) + ", '" + batchType + "', " + str(id) + ")"

            self.execute(sql)

    '''
    Returns ids for all boxes with pending items in a stripe of RA not already being processed
    by another client. If none are available, then it uses the stripe already used by another 
    client that has the most pending items
    '''
    def getStripeBoxIds(self, host, pid, batchType):

        self.execute("LOCK TABLES box READ, pending READ, stripe WRITE, clients WRITE")

        ids = []
        raCenter = -1.0
        sql = "SELECT ra_center \
               FROM box \
               JOIN pending ON (id = box_id) \
               WHERE skychunk = '" + self.skychunk.name + "' \
               AND batch_type = '" + batchType + "' \
               AND ra_center NOT IN \
               (SELECT ra_center \
                FROM stripe \
                WHERE skychunk = '" + self.skychunk.name + "') \
               GROUP BY ra_center LIMIT 1"

        try: rs = self.executeQuery(sql)
        except:
            self.logger.errorPair("Problem querying for outstanding boxes", "error in sql?")
            print "SQL: " + sql

        if not rs.next():
            self.logger.errorPair("No outstanding boxes", "nothing to do here")
            rs.close()
            self.unlockTables()
            return ids

        raCenter = rs.getFloat(1)
        rs.close()

        #except:
        #    self.logger.errorPair("No free stripes", "trying to steal boxes from other client...")
        #    sql = "SELECT ra_center, COUNT(*) AS numPending\
        #           FROM box \
        #           JOIN pending ON (id = box_id) \
        #           WHERE skychunk = '" + self.skychunk.name + "' \
        #           AND batch_type = '" + batchType + "' \
        #           GROUP BY ra_center \
        #           ORDER BY numPending \
        #           DESC LIMIT 1"
        #
        #    try:
        #        rs = self.executeQuery(sql)
        #        rs.first()
        #        raCenter = rs.getFloat(1)
        #        rs.close()
        #    except:
        #        self.logger.errorPair("No outstanding boxes to steal", "no stripe obtained")

        # tests against ra_center need to use a finite box
        minRA = raCenter - 0.01
        maxRA = raCenter + 0.01

        self.logger.infoPair("Using RA stripe", "%f" % raCenter)
        sql = "SELECT DISTINCT id \
               FROM box \
               JOIN pending ON (id = box_id) \
               WHERE ra_center >= " + str(minRA) + " AND ra_center < " + str(maxRA) + " \
               AND batch_type = '" + batchType + "' \
               AND skychunk = '" + self.skychunk.name + "' \
               ORDER BY dec_center DESC"

        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.errorPair("Can't get stripe boxes", sql)

        self.registerStripe(host, pid, raCenter)

        self.unlockTables()

        return ids

    '''
    Returns ids for all boxes with pending items in a stripe of RA not already being processed
    by another client. If none are available, then it uses the stripe already used by another 
    client that has the most pending items
    '''
    def getStripeBoxIdsAnyBatch(self, host, pid):

        self.execute("LOCK TABLES box READ, pending READ, stripe WRITE, clients WRITE")

        ids = []
        raCenter = -1.0
        sql = "SELECT ra_center \
               FROM box \
               JOIN pending ON (id = box_id) \
               WHERE skychunk = '" + self.skychunk.name + "' \
               AND ra_center NOT IN \
               (SELECT ra_center \
                FROM stripe \
                WHERE skychunk = '" + self.skychunk.name + "') \
               GROUP BY ra_center LIMIT 1"

        try: rs = self.executeQuery(sql)
        except:
            self.logger.errorPair("Problem querying for outstanding boxes", "error in sql?")
            print "SQL: " + sql

        if not rs.next():
            self.logger.infoPair("No outstanding boxes", "nothing to do here")
            rs.close()
            self.unlockTables()
            return ids

        raCenter = rs.getFloat(1)
        rs.close()

        # except:
        #     self.logger.errorPair("No free stripes", "trying to steal boxes from other client...")
        #     sql = "SELECT ra_center, COUNT(*) AS numPending\
        #            FROM box \
        #            JOIN pending ON (id = box_id) \
        #            WHERE skychunk = '" + self.skychunk.name + "' \
        #            GROUP BY ra_center \
        #            ORDER BY numPending \
        #            DESC LIMIT 1"
        # 
        #     try:
        #         rs = self.executeQuery(sql)
        #         rs.first()
        #         raCenter = rs.getFloat(1)
        #         rs.close()
        #     except:
        #         self.logger.errorPair("No outstanding boxes to steal", "no stripe obtained")

        # tests against ra_center need to use a finite box
        minRA = raCenter - 0.01
        maxRA = raCenter + 0.01

        self.logger.infoPair("Using RA stripe", "%f" % raCenter)
        sql = "SELECT DISTINCT id \
               FROM box \
               JOIN pending ON (id = box_id) \
               WHERE ra_center >= " + str(minRA) + " AND ra_center < " + str(maxRA) + " \
               AND skychunk = '" + self.skychunk.name + "' \
               ORDER BY dec_center DESC"

        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.errorPair("Can't get stripe boxes", sql)

        self.unlockTables()

        return ids

    '''
    Inserts row into strip table
    '''
    def registerStripe(self, host, pid, raCenter):
       
        # first, delete any existing record
        try:
            self.execute("DELETE FROM stripe \
                    WHERE client_id = \
                    (SELECT id FROM clients \
                     WHERE host = '" + host + "' AND pid = " + str(pid) + ")")
        except:
            self.logger.errorPair("Can't delete from stripe table", sql)


        # now insert new stripe entry
        sql = "INSERT INTO stripe \
               (client_id, skychunk, ra_center) \
               SELECT clients.id, clients.skychunk, " + str(raCenter) + " \
               FROM clients \
               WHERE host = '" + host + "' \
               AND pid = " + str(pid)
        try:
            self.execute(sql)
        except:
            self.logger.errorPair("Can't insert into stripe", sql)


    '''
    Returns dimensions of this box
    '''
    def getBoxDimensions(self, boxId):

        sql = "SELECT ra_center, dec_center, box_side FROM box WHERE id = " + str(boxId)

        boxDim = {}
        rs = self.executeQuery(sql)
        rs.first()

        boxDim['RA'] = rs.getFloat(1)
        boxDim['DEC'] = rs.getFloat(2)
        boxDim['SIDE'] = rs.getFloat(3)

        # SIDE is the linear full-width of the box

        # XXX EAM : need to do something special at the poles...
        #if (boxDim['DEC'] > 85): raise
        #heather commented out for oB   
   
        # a given box centered at RA,DEC with (linear) size of SIDE has the following bounds
        dR = 0.5*boxDim['SIDE'] / math.cos(math.radians(boxDim['DEC']))
        dD = 0.5*boxDim['SIDE']

        boxDim['minRA']  = boxDim['RA']  - dR
        boxDim['maxRA']  = boxDim['RA']  + dR
        boxDim['minDEC'] = boxDim['DEC'] - dD
        boxDim['maxDEC'] = boxDim['DEC'] + dD

        return boxDim

    '''
    Returns ids for pending items for this skychunk
    '''
    def getPendingIds(self, batchType):

        sql = "SELECT DISTINCT stage_id \
               FROM pending \
               JOIN box ON (box_id = id) \
               WHERE skychunk = '" + self.skychunk.name + "' \
               AND batch_type = '" + batchType + "'"

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.errorPair("Can't get pending ids for this skychunk", sql)

        return ids

    '''
    Returns ids for pending items in this box for this batch type
    '''
    def getPendingIdsForThisBox(self, boxId, batchType):

        sql = "SELECT stage_id FROM pending WHERE batch_type = '" + batchType + "' AND box_id = " + str(boxId)

        ids = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()): ids.append(rs.getInt(1))
            rs.close()
        except:
            self.logger.errorPair("Can't get ids from this box", sql)

        return ids

    '''
    Deletes an item from the pending table
    '''
    def deletePendingItem(self, batchType, stageID):

        sql = "DELETE pending \
               FROM pending \
               JOIN box ON (id = box_id) \
               WHERE stage_id = " + str(stageID) + " \
               AND batch_type = '" + batchType + "' \
               AND skychunk = '" + self.skychunk.name + "'"

        print "--- sql: " + sql
        self.execute(sql)

    '''
    Writes density-plot data to file for pending stuff over sky for given skychunk
    '''
    def createPendingDensityPlotData(self, batchType, DATFILE):

        sql = "SELECT id, ra_center, dec_center \
               FROM box join skychunk on box.skychunk = skychunk.name \
               WHERE dvo_label = '" + self.skychunk.dvoLabel + "' \
               ORDER BY ra_center, dec_center"

        maxPending = 0
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                
                boxId = rs.getInt(1)

                sql = "SELECT COUNT(stage_id) \
                       FROM pending \
                       WHERE box_id = " + str(boxId) + " \
                       AND batch_type = '" + batchType + "'"
                rs2 = self.executeQuery(sql)
                rs2.first()
                max = rs2.getInt(1)
                rs2.close() 
                print >> DATFILE, rs.getFloat(2), rs.getFloat(3), max
                if max > maxPending: maxPending = max
            rs.close()
        except:
            self.logger.errorPair("Can't get density plot data", sql)

        return maxPending

    '''
    Creates a temporary table and shoves it full of stage_ids and ra/dec coords
    '''
    def storeAllItems(self, rows):

        self.dropTable("all_pending")

        self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)")
        for row in rows:
            try:
                sql = "INSERT INTO all_pending (stage_id, ra_bore, dec_bore) \
                       VALUES (%d, %f, %f)" % (row[0], row[1], row[2]) 
                # print "sql 1: ", sql
                self.execute(sql)
            except: continue

        count = self.getRowCount("all_pending")
        self.logger.infoPair("Items written to Db", "%d" % count)

    '''
    Creates a temporary table and shoves it full of stage_ids and ra/dec coords
    '''
    def storeAllItemsInDvodb(self, rows, dvo_db, batchType):

        #self.execute("CREATE TEMPORARY TABLE all_pending (stage_id bigint(20), ra_bore float, dec_bore float)")
        for row in rows:

            try:
                uniq = dvo_db + "."+ batchType + "." + str(row[0])
                sql = "INSERT INTO dvodb (dvo_db, batch_type, stage_id, ra_center, dec_center, uniq_key) \
                       VALUES ( '" + dvo_db + "', '" + batchType + "'," + str(row[0]) + "," + str(row[1]) + " \
                       , " + str(row[2]) + ", '"+uniq+"' ) " 
                # print "sql 1: ", sql
                self.execute(sql)
            except: continue

        #count = self.getRowCount("all_pending")
        #self.logger.infoPair("Items written to Db", "%d" % count)

    '''
    Gets all items in the all_pending temporary table within the bounds of this box
    '''
    def getItemsInThisBox(self, minRA, maxRA, minDEC, maxDEC):

        ids = []

        sql = "SELECT DISTINCT stage_id \
               FROM all_pending \
               WHERE ra_bore >= " + str(minRA) + " AND ra_bore < " + str(maxRA) + " \
               AND dec_bore >= " + str(minDEC) + " AND dec_bore < " + str(maxDEC)

        try:
            rs = self.executeQuery(sql)
            while (rs.next()): 
                ids.append(rs.getInt(1))
        except:
            self.logger.errorPair("Can't get items in this box", sql)

        return ids

    '''
    Gets client info
    '''
    def getClientInfo(self):

        sql = "SELECT id, host, started, timestamp, ra_center, paused, killed \
               FROM stripe \
               JOIN clients ON (client_id = id) \
               WHERE clients.skychunk = '" + self.skychunk.name + "' \
               ORDER BY host, started"

        rows = []
        try:
            rs = self.executeQuery(sql)
            while (rs.next()):
                rows.append([rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getFloat(5), rs.getInt(6), rs.getInt(7)])
            rs.close()
        except:
            self.logger.errorPair("Can't query for client data", sql)

        return rows

    '''
    Purges old clients that may have died, i.e. those with a timestamp older than n hours
    '''
    def purgeDeadClients(self):
        self.execute("DELETE FROM clients WHERE timestamp < now() - INTERVAL 14 HOUR")

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

        self.logger.debug("IppToPspsDb Destructor")
        self.stmt.close()
        self.con.close()



