from basic import Basic
import string, types


class Data(Basic):

    def getdata(self, table, id):
        cursor = self.connection.cursor()
        cursor.execute('''SELECT * FROM %s_%s
                          WHERE id=%d
                       ''' % (self.name, table, id))
        data = cursor.dictfetchall()
        cursor.close()
        if data:
            return data[0]
        else:
            return None


    def newdata(self, table, id=None):
        cursor = self.connection.cursor()
        if id == None:
            cursor.execute('''SELECT id FROM %s_%s
                              ORDER BY id DESC
                              LIMIT 1
                           ''' % (self.name, table))
            data = cursor.fetchall()
            if data:
                id = data[0][0] + 1
            else:
                id = 1
        cursor.execute('''INSERT INTO %s_%s(id, time)
                          VALUES(%d, 'now')
                       ''' % (self.name, table, id))
        cursor.close()
        return id


    def setdata(self, table, id, data):
        cursor = self.connection.cursor()
        for field in data.keys():
            if field == 'id': continue
            value = data[field]
            cursor.execute('''UPDATE %s_%s
                              SET %s='%s'
                              WHERE id=%d
                           ''' % (self.name, table, field, self.quote(value), id))
        cursor.execute('''UPDATE %s_%s
                          SET time='now'
                          WHERE id=%d
                       ''' % (self.name, table, id))
        cursor.close()
        return


    def deldata(self, table, id):
        cursor = self.connection.cursor()
        cursor.execute('''DELETE FROM %s_%s
                          WHERE id=%d
                       ''' % (self.name, table, id))
        cursor.close()
        return


    def listdata(self, table, where='', order='id', constraint=''):
        if type(where) in (types.ListType,):
            if not where:
                return []
            where = 'id in (%s)' % (string.join(map(str, where), ','))
        if where:
            where = 'WHERE %s' % (where)
        if order:
            order = 'ORDER BY %s' % (order)
        cursor = self.connection.cursor()
        cursor.execute('''SELECT * FROM %s_%s
                          %s
                          %s
                          %s
                       ''' % (self.name, table, where, order, constraint))
        list = cursor.dictfetchall()
        cursor.close()
        return list


    def finddata(self, table, where='', order='id', constraint=''):
        if where:
            where = 'WHERE %s' % (where)
        if order:
            order = 'ORDER BY %s' % (order)
        cursor = self.connection.cursor()
        cursor.execute('''SELECT * FROM %s_%s
                          %s
                          %s
                          %s
                       ''' % (self.name, table, where, order, constraint))
        list = cursor.dictfetchall()
        cursor.close()
        return list


    def addtable(self, table):
        cursor = self.connection.cursor()
        cursor.execute('''CREATE TABLE %s_%s (
                              id   INTEGER,
                              time TIMESTAMP,
                              PRIMARY KEY (id)
                          )
                       ''' % (self.name, table))
        cursor.close()
        return


    def deltable(self, table):
        cursor = self.connection.cursor()
        try:
            cursor.execute('''DROP TABLE %s_%s
                           ''' % (self.name, table))
        except Exception:
            pass
        cursor.close()
        return


    def addfield(self, table, field, type, default, index):
        cursor = self.connection.cursor()
        cursor.execute('''ALTER TABLE %s_%s
                          ADD COLUMN %s %s
                       ''' % (self.name, table, field, type))
        if type in ('INTEGER','FLOAT',):
            default = '%s' % (default)
        else:
            default = '\'%s\'' % (default)
        cursor.execute('''ALTER TABLE %s_%s
                          ALTER COLUMN %s
                          SET DEFAULT %s
                       ''' % (self.name, table, field, default))
        if index:
            cursor.execute('''CREATE INDEX %s_%s_%s_a
                              ON %s_%s USING %s (%s)
                           ''' % (self.name, table, field, self.name, table, index, field))
        cursor.close()
        return


    def createindex(self, table, field, index):
        cursor = self.connection.cursor()
        if index:
            cursor.execute('''CREATE INDEX %s_%s_%s_a
                              ON %s_%s USING %s (%s)
                           ''' % (self.name, table, field, self.name, table, index, field))
        cursor.close()
        return


    def dropindex(self, table, field, index):
        cursor = self.connection.cursor()
        if index:
            cursor.execute('''DROP INDEX %s_%s_%s_a
                           ''' % (self.name, table, field))
        cursor.close()
        return