#!/usr/bin/env jython

import re
import sys
import os
import logging

from mysql import MySql
from java.sql import *

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

    '''
    Constructor
    '''
    def __init__(self, logger, config):
        super(IppToPspsDb, self).__init__(logger, config, "ipptopspsdatabase")

        self.MAX_FAILS = 5

    '''
    Returns a list of merged batch IDs that are merged but not yet deleted
    '''
    def getMergedButNotDeletedBatchIDs(self, batchType, column):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.config.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.config.dvoLabel + "' \
               AND merged = 1 \
               AND " + column + " = 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
    '''
    def getPurgedButNotDeletedBatchIDs(self, batchType, column):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.config.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.config.dvoLabel + "' \
               AND purged = 1 \
               AND " + column + " = 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
    '''
    def getLoadedToODMButNotDeletedBatchIDs(self, batchType, column):

        sql = "SELECT DISTINCT batch_id \
               FROM batch \
               WHERE timestamp > '" + self.config.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.config.dvoLabel + "' \
               AND (loaded_to_ODM = -1 OR merge_worthy = 1) \
               AND " + column + " = 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 merged batch IDs that are not deleted from local disk
    '''
    def getMergedButNotDeletedFromLocalDisk(self, batchType):
        return self.getMergedButNotDeletedBatchIDs(batchType, "deleted_local")

    '''
    Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from local disk
    '''
    def getLoadedToODMButNotDeletedFromLocalDisk(self, batchType):
        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_local")

    '''
    Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from datastore
    '''
    def getLoadedToODMButNotDeletedFromDatastore(self, batchType):
        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_datastore")

    '''
    Returns a list of processed batch IDs that are loaded to the ODM, but not deleted from DXLayer
    '''
    def getLoadedToODMButNotDeletedFromDXLayer(self, batchType):
        return self.getLoadedToODMButNotDeletedBatchIDs(batchType, "deleted_dxlayer")

    '''
    Returns a list of purged  batch IDs that not deleted from local disk
    '''
    def getPurgedButNotDeletedFromLocalDisk(self, batchType):
        return self.getPurgedButNotDeletedBatchIDs(batchType, "deleted_local")

    '''
    Returns a list of purged batch IDs that are not deleted from datastore
    '''
    def getPurgedButNotDeletedFromDatastore(self, batchType):
        return self.getPurgedButNotDeletedBatchIDs(batchType, "deleted_datastore")


    '''
    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.config.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.dvoLabel + "' \
               AND processed = 1 \
               AND loaded_to_datastore = 1 \
               AND timestamp BETWEEN now() - INTERVAL " + interval + " AND 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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.dvoLabel + "' \
               AND timestamp BETWEEN now() - INTERVAL " + interval + " AND 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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.dvoLabel + "' \
               AND batch_type != 'IN' \
               AND timestamp BETWEEN now() - INTERVAL 12 HOUR AND 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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.epoch + "' \
               AND dvo_db = '" + self.config.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.config.force or \
            (not self.alreadyProcessed(batchType, stageID) \
            and not self.processingNow(batchType, stageID) \
            and not self.consistentlyFailed(batchType, stageID)): 

            sql = "INSERT INTO batch ( \
                   batch_type, \
                   stage_id, \
                   survey, \
                   dvo_db, \
                   datastore_product \
                   ) VALUES ( \
                       '" + batchType + "', \
                       " + str(stageID) + ", \
                       '" + self.config.survey + "', \
                       '" + self.config.dvoLabel + "', \
                       '" + self.config.datastoreProduct + "' \
                       )"

            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")
                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)
    '''
    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 config field for this client
    '''
    def setConfigForThisClient(self, config, host, pid):

        self.execute("UPDATE clients SET config = '" + config + "' \
                WHERE host = '" + host + "' \
                AND pid = " + str(pid))
    '''
    Sets the config field for a set of loader clients
    '''
    def setConfigForLoaders(self, config, ids):

        for id in ids:
            self.execute("UPDATE clients SET config = '" + config + "' \
                    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, config) VALUES (now(), '" + type + "', '" + host + "', " + str(pid) + ", '" + self.config.name + "')")

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

        try:
            self.insertClient(type, host, pid)
        except:
            self.execute("UPDATE clients SET timestamp = now() WHERE host = '" + host + "' AND pid = " + str(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)
            rs.first()
            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 configs
    '''
    def getActiveConfigList(self):
        
        sql = "SELECT DISTINCT name FROM config WHERE active = 1 ORDER BY name"

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

        return configs

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

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

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

       # for each column in the config table
       sql = "SHOW COLUMNS FROM config"
       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

           # get current value for this field, if any
           try:
               rs2 = self.executeQuery("SELECT " + field + " FROM config WHERE name = '" + self.config.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)
           if response != "":
               if type.find("varchar") != -1 or type.find("timestamp") != -1: quotes = "'"
               else: quotes = ""
               self.execute("UPDATE config SET " + field + " = " + quotes + response + quotes + " WHERE name = '" + self.config.name + "'")
       
       print " *"
       print " ********************************************************************"

    '''
    Returns config for this client
    '''
    def getConfigForThisClient(self, host, pid):

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

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

        return True

    '''
    Reads config from the database and populates Config object
    '''
    def readConfig(self, host, pid):

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

        # now load that config
        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 \
        FROM config \
        WHERE name = '" + self.config.name + "'"

        self.config.batchTypes = []
        try:
            rs = self.executeQuery(sql)
            rs.first()
            self.config.datastoreProduct = rs.getString(1)
            self.config.datastoreType = rs.getString(2)
            if rs.getInt(3) == 1: self.config.datastorePublishing = True
            else: self.config.datastorePublishing = False
            self.config.dvoLabel = rs.getString(4)

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

            self.config.boxSize = rs.getDouble(10)
            self.config.halfBox = self.config.boxSize/2.0
            self.config.boxSizeWithBorder = self.config.boxSize + (self.config.BORDER * 2)

            self.config.basePath = rs.getString(11)
            self.config.dataRelease = rs.getInt(12)
            self.config.deleteLocal = rs.getInt(13)
            self.config.deleteDatastore = rs.getInt(14)
            self.config.deleteDxLayer = rs.getInt(15)
            self.config.epoch = rs.getString(16)
            self.config.survey = rs.getString(17)
            self.config.pspsSurvey = rs.getString(18)
            if rs.getInt(19) == 1: self.config.batchTypes.append("P2")
            if rs.getInt(20) == 1: self.config.batchTypes.append("ST")
            if rs.getInt(21) == 1: self.config.batchTypes.append("OB")
            self.config.force = False # TODO
            self.config.test = False # TODO
            self.config.isLoaded = True
        except:
            self.logger.errorPair("Could not read config with name", self.config.name)
            self.config.isLoaded = False

        return self.config.isLoaded

    '''
    Gets id of box with these coords
    '''
    def getBoxId(self, ra, dec):

        sql = "SELECT id FROM box \
               WHERE config  = '" + self.config.name + "' \
               AND ra_center = " + str(ra) + " \
               AND dec_center = " + str(dec) + " \
               AND box_side = " + str(self.config.boxSize)

        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 config = '" + self.config.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 \
                   (config, ra_center, dec_center, box_side) \
                   VALUES \
                   ('" + self.config.name + "', " + str(ra) + ", " + str(dec) + ", " + str(self.config.boxSize) + ")"

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

        return id

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

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

        for id in ids:
            sql = "INSERT IGNORE 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 config = '" + self.config.name + "' \
               AND batch_type = '" + batchType + "' \
               AND ra_center NOT IN \
               (SELECT ra_center \
                FROM stripe \
                WHERE config = '" + self.config.name + "') \
               GROUP BY ra_center LIMIT 1"

        try:
            rs = self.executeQuery(sql)
            rs.first()
            raCenter = rs.getFloat(1)
            rs.close()
        except:
            self.logger.errorPair("No free stripes", "trying to share with other client...")
            sql = "SELECT ra_center, COUNT(*) AS numPending\
                   FROM box \
                   JOIN pending ON (id = box_id) \
                   WHERE config = '" + self.config.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 available stripes to share", "no stripe obtained")

        self.logger.infoPair("Using RA stripe", "%f" % raCenter)
        sql = "SELECT DISTINCT id \
               FROM box \
               JOIN pending ON (id = box_id) \
               WHERE ra_center = " + str(raCenter) + " \
               AND batch_type = '" + batchType + "' \
               AND config = '" + self.config.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

    '''
    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, config, ra_center) \
               SELECT clients.id, clients.config, " + 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)

        return boxDim

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

        sql = "SELECT DISTINCT stage_id \
               FROM pending \
               JOIN box ON (box_id = id) \
               WHERE config = '" + self.config.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 config", 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 config = '" + self.config.name + "'"

        self.execute(sql)

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

        sql = "SELECT id, ra_center, dec_center \
               FROM box \
               WHERE config = '" + self.config.name + "' \
               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):

        try:
            self.execute("DROP TABLE all_pending")
        except:
            pass

        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]) 
                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 getItemsInThisThisBox(self, ra, dec):

        halfSide = self.config.boxSize/2.0
        minRa =  ra-halfSide
        maxRa = ra+halfSide
        minDec = dec-halfSide
        maxDec = dec+halfSide

        ids = []

        sql = "SELECT DISTINCT stage_id \
               FROM all_pending \
               WHERE ra_bore BETWEEN " + str(minRa) + " AND " + str(maxRa) + " \
               AND dec_bore BETWEEN " + str(minDec) + " AND " + 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.config = '" + self.config.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()



