###
### SQLDict - Container access to an SQL table
###

from ...builtins import *
from ...parsing.autotype import *

class SQLDict(object):

    def __init__(self, conn=None, table=None, key=None, value=None):

        object.__setattr__(self, 'conn', conn)
        object.__setattr__(self, 'table', table)
        object.__setattr__(self, 'key', key)
        object.__setattr__(self, 'value', value)

    def __getitem__(self, key=None):

        curs = self.conn.cursor()
        sql = "SELECT %s FROM %s WHERE %s = '%s'" % (self.value, self.table, self.key, key)
        curs.execute(sql)
        results = curs.fetchone()
        if results:
            value = autotype(results[0])
            return value
        else:
            return None

    def __getattr__(self, name):

        return self.__getitem__(name)

    def __setitem__(self, key, value):

        curs = self.conn.cursor()

        if key in self:
            sql = "UPDATE %s SET %s = '%s' WHERE %s = '%s'" % (self.table, self.value, value, self.key, key)
        else:
            sql = "INSERT %s (%s, %s) VALUES ('%s', '%s')" % (self.table, self.key, self.value, key, value)

        curs.execute(sql)
        self.conn.commit()

    def __setattr__(self, name, value):

        if self.__dict__.has_key(name):
            return object.__setattr__(self, name, value)
        else:
            return self.__setitem__(name, value)

    def __delitem__(self, key):

        curs = self.conn.cursor()

        sql = "DELETE FROM %s WHERE %s = '%s'" % (self.table, self.key, key)

        curs.execute(sql)
        self.conn.commit()

    def __delattr__(self, name):

        return self.__delitem__(name)

    def __contains__(self, item):

        curs = self.conn.cursor()
        sql = "SELECT COUNT(*) FROM %s WHERE %s = '%s'" % (self.table, self.key, item)
        curs.execute(sql)
        results = curs.fetchone()
        count = int(results[0])

        if count > 0:
            return True
        else:
            return False

    def __iter__(self):

        return self.iteritems()

    def clear(self):

        curs = self.conn.cursor()
        sql = "DELETE FROM %s" % (self.table)
        curs.execute(sql)
        self.conn.commit()

        return

    def copy(self):

        curs = self.conn.cursor()
        sql = "SELECT %s, %s FROM %s" % (self.key, self.value, self.table)
        curs.execute(sql)

        copy = {}

        for row in curs.fetchall():
            key = row[0]
            value = row[1]
            copy[key] = value

        return copy

    def has_key(self, key):

        return True if key in self else False

    def items(self):

        curs = self.conn.cursor()
        sql = "SELECT %s, %s FROM %s" % (self.key, self.value, self.table)
        curs.execute(sql)

        return [(row[0], row[1]) for row in curs.fetchall()]

    def keys(self):

        curs = self.conn.cursor()
        sql = "SELECT %s FROM %s" % (self.key, self.table)
        curs.execute(sql)

        return [row[0] for row in curs.fetchall()]

    def update(self, b):

        for key in b.keys():
            value = b[key]
            self[key] = value

        return None

    def fromkeys(self, seq, value=None):

        fk = {}

        for key in self.iterkeys():
            if key in seq:
                fk[key] = value

        return fk

    def values(self):

        curs = self.conn.cursor()
        sql = "SELECT %s FROM %s" % (self.value, self.table)
        curs.execute(sql)

        return [row[0] for row in curs.fetchall()]

    def get(self, k, x):

        # this could be more efficient..
        return self[k] if k in self else x

    def setdefault(self, k, x):

        if k in self:
            return self[k]
        else:
            self[k] = x
            return x

    # def pop(self, k, x)

    # def popitem(self)

    def iteritems(self):

        return iter(self.items())

    def iterkeys(self):

        return iter(self.keys())

    def itervalues(self):

        return iter(self.values())