#!/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, skychunk):
        if (config.test): 
            dbType = "ipptopspsdatabase_test"
        else:
            dbType = "ipptopspsdatabase"
        super(IppToPspsDb, self).__init__(logger, config, dbType)

        self.skychunk = skychunk

        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.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.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.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.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.skychunk.epoch + "' \
               AND batch_type = '" + batchType + "' \
               AND dvo_db = '" + self.skychunk.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.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 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.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 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.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 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.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 \
                   ) VALUES ( \
                       '" + batchType + "', \
                       " + str(stageID) + ", \
                       '" + self.skychunk.survey + "', \
                       '" + self.skychunk.dvoLabel + "', \
                       '" + self.skychunk.datastoreProduct + "' \
                       )"
            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")
                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 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 + "')")

    '''
    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)
            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
       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)
           if response != "":
               if type.find("varchar") != -1 or type.find("timestamp") != -1: quotes = "'"
               else: quotes = ""
               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 \
        ,parallel \
        FROM skychunk \
        WHERE name = '" + self.skychunk.name + "'"

        self.skychunk.batchTypes = []
        try:
            rs = self.executeQuery(sql)

            rs.first()
            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.halfBox = self.skychunk.boxSize/2.0
            self.skychunk.boxSizeWithBorder = self.skychunk.boxSize + (self.skychunk.BORDER * 2)

            self.skychunk.basePath = rs.getString(11)
            self.skychunk.dataRelease = 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")

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

            if rs.getInt(22) == 1: self.skychunk.parallel = True

            if self.skychunk.parallel: print "USING parallel"
            self.skychunk.isLoaded = True
        except:
            self.logger.errorPair("Could not read skychunk with name", self.skychunk.name)
            self.skychunk.isLoaded = False

        return self.skychunk.isLoaded

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

        sql = "SELECT id FROM box \
               WHERE skychunk  = '" + self.skychunk.name + "' \
               AND ra_center = " + str(ra) + " \
               AND dec_center = " + str(dec) + " \
               AND box_side = " + str(self.skychunk.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 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) + ")"

            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):

        # 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
            status = rs.getInt(1)
        except:
            print "failed to set box ", str(id), "as ingested"
            raise

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

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

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

        # set the field 'ingested' to 1 for this box
        sql = "update box set ingested = 0"
        try:
            self.execute(sql)
        except:
            print "failed to clear boxes as ingested"
            raise

    '''
    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 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"

        # XXX print "sql: ", sql               

        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 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 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 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

    '''
    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)

        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 + "'"

        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 \
               WHERE skychunk = '" + self.skychunk.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.skychunk.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.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()



