"""
sqlite is a dbx module for the SQLite embedded database server.

sqlite requires the DB-API pysqlite2 module.
"""


from pysqlite2 import dbapi2 as _sqlite


def escape(value):
    if isinstance(value, basestring):
        return value.replace("'", "''")
    elif isinstance(value, (int, long)):
        return value
    else:
        raise TypeError


def escapeAll(values):
    return [escape(i) for i in values]


class Error(Exception):
    pass


class SQLError(Error):
    pass


class SQLite:
    C_NAME = 0
    C_TYPE = 1
    C_NULL = 2
    C_DEFAULT = 3

    def __init__(self, dbname=None):
        if dbname is not None:
            self.connect(dbname)

    def __del__(self):
        try:
            self.close()
        except:
            pass

    def connect(self, dbname):
        try:
            self.conn = _sqlite.connect(dbname, isolation_level=None)
        except _sqlite.Error, e:
            raise Error, e.args[0]
        self.cursor = self.conn.cursor()

    def selectDB(self, dbname):
        try:
            self.connect(dbname)
        except _sqlite.Error, e:
            raise Error, e.args[0]

    def close(self):
        self.cursor.close()
        self.conn.close()

    def query(self, sql):
        try:
            self.cursor.execute(sql)
        except _sqlite.Error, e:
            raise SQLError, (e.args[0], sql)
        if self.cursor.rowcount == -1:
            return None
        return self.cursor.rowcount

    def rowQuery(self, sql):
        self.query(sql)
        x = self.cursor.fetchone()
        return self.__dict_row(x)

    def listQuery(self, sql):
        self.query(sql)
        x = self.cursor.fetchall()
        return [self.__dict_row(i) for i in x]

    def queryID(self):
        return self.cursor.lastrowid

    def version(self):
        return _sqlite.sqlite_version

    def tables(self):
        tables = self.listQuery("""
            SELECT name FROM sqlite_master
            WHERE type = 'table' ORDER BY name
            """)
        return [i.values()[0] for i in tables]

    def columns(self, table):
        sql = "PRAGMA TABLE_INFO('%s')" % escape(table)
        columns = self.listQuery(sql)
        ret = []
        for i in columns:
            null = not int(i["notnull"])
            ret.append((i["name"], i["type"], null, i["dflt_value"]))
        return ret

    def __dict_row(self, row):
        x = {}
        for i in range(0, len(row)):
            x[self.cursor.description[i][0]] = row[i]
        return x