# -*- coding: cp1252 -*-
import database

class SQL:
    '''Abstract class
    columnnames is a list of column names
    values is a list of lists representing rows in the database'''
    def __init__(self, columnNames, values):
        self.columnNames = columnNames
        self.values = values
    def introspect(self):
        text = 'Dictionary: '
        text += str(self.__dict__)
        text += '\n'+'Functions: '
        text += '\n'+ str(dir(self))
        return text
    def printContents(self):
        '''prints the data of the object'''
        print 'Column names: '+str(self.columnNames)
        print 'Values: '+str(self.values)
    def makeHTMLTable(self):
        '''makes a html-table that displays the column names and the values'''
        tableWithHeading = []
        tableWithHeading.append(self.columnNames)
        tableWithHeading.extend(self.values)
        return makeTable(tableWithHeading)
        
        

class Persistent(SQL):
    '''Abstract class'''
    def __init__(self, tableName, values):
        self.primaryKey = database.getPrimaryKey(tableName)
        self.tableName = tableName
        columnNames = database.getColumnNames(tableName)
        SQL.__init__(self, columnNames, values)
    def printContents(self):
        '''prints the data of the object'''
        print 'Table name: '+self.tableName
        print 'Primary key: ' + self.primaryKey
        SQL.printContents(self)
    def getPrimaryKey(self):
        '''returns the primary key'''
        return self.primaryKey


class Table(Persistent):
    ''' This class represents a table
    Values contains a list of lists representing the rows in the table.
    Components contains corresponding instances of the Row class
    Instances are generated by Table(tablename)'''
    def __init__(self, tableName):
        values = database.findRecords(tableName,[])
        valuelist = []           
        Persistent.__init__(self,tableName, values)
        #insert a list of Row-instances corresponding to the values
        rows =[]
        primaryKey = self.primaryKey
        i = self.columnNames.index(primaryKey)
        for v in self.values:
            theKey = v[i]
            rows.append(Row(tableName,theKey))
        self.rows = rows
    def getRow(self,theKey):
        '''returns a row-instance whose primary key = theKey
        if none exists, returns None'''
        primaryKey = self.getPrimaryKey()
        for r in self.rows:
            if r.get(primaryKey) == theKey:
                return r
        return None
    def printContents(self):
        '''prints the data of the object'''
        print 'Table name: '+self.tableName
        print 'Primary key: '+ self.primaryKey
        print 'Values : \n' + str(self.values)
        print 'Components: '
        for c in self.rows:
            c.printContents()
        

class Row(Persistent):
    ''' This class represents a row in a table.
    The data of the row is represented by a list containing one list:
    [[data, data, data, data]]
    If a key is provided, the row in the database with the key
    is read into the instance.
    If no key is provided, an instance with empty values is generated
    Instances are generated by Row(tablename) or Row(tablename,key)'''
    def __init__(self, tableName, key=''):
        Persistent.__init__(self,tableName, None)
        if key != '':
            #insert the table row
            self.values = database.findRecords(tableName,[],{self.primaryKey:key})
        else:
            #insert an empty table row
            theLength = len(self.columnNames)
            self.values = ['']*theLength       
    def put(self,attribute,value):
        ''' inserts value as the value of the attribute'''
        if attribute in self.columnNames:
            i = self.columnNames.index(attribute)
            self.values[0][i] = value
        else:
            print attribute + ' does not exist'
    def putAllValues(self,valueList):
        '''inserts valueList as the value of the instance'''
        if len(valueList) == len(self.columnNames):
            self.values = valueList
        else:
            print 'length of valueList is different from length of columnNames'
    def get(self,attribute):
        '''returns the value of attribute'''
        if attribute in self.columnNames:
            i = self.columnNames.index(attribute)
            return self.values[0][i]
        else:
            return None
    def update(self):
        '''updates the database'''
        theKey = self.get(self.primaryKey)
        if theKey != '':
            updateList = database.rowToDictionary(self.columnNames,self.values[0])
            database.updateRecords(self.tableName, updateList, {self.primaryKey:theKey})
        else:
            print 'cannot update because the key is missing'
    def insert(self):
        '''inserts the row as a new row in the database'''
        theKey = self.get(self.primaryKey)
        if theKey != '':
            database.insertRecord(self.tableName, self.values)
        else:
            print 'cannot insert because the key is missing'
    def delete(self):
        '''deletes the row from the database'''
        theKey = self.get(self.primaryKey)
        if theKey != '':
            database.deleteRecords(self.tableName, {self.primaryKey:theKey})
        else:
            print 'cannot delete because the key is missing'
    
class Temporary(SQL):
    '''Class representing an arbitrary selection from the database
    Instances are generated by Temporary(columnNames, sqlCommand)
    where columnNames is a list of column names'''
    def __init__(self, columnNames, sqlCommand):
        SQL.__init__(self, columnNames, database.queryDB(sqlCommand))

def makeTable(tablecontents):
    ''' Produces a html table.
    Input is a list of list [a1,a2,a3...] where ai is a list.
    Each ai is visualised as a row in the html-table.
    '''
    htmltabel = str(tablecontents)
    return htmltabel


if __name__ == "__main__":

    print
    aTable = Table('Child')
    aTable.printContents()
    print
    print 'fetch a row from Child with primary key 1111111111'
    aRow = Row('Child','1111111111')
    print 'change the first name to Jeppe and store it'
    aRow.put('firstname','Jeppe')
    aRow.update()
    print 'fetch the row again to see if the change worked'
    aRow = Row('Child','1111111111')
    aRow.printContents()
    print
    print 'restore the first name'
    aRow.put('firstname','Jeppe Boegh')
    aRow.update()
    print
    print 'print a html version of the row'
    print aRow.makeHTMLTable()
    print
    print 'make a new empty row-object'
    aRow = Row('Child')
    aRow.printContents()
    print
    print 'insert values into the object'
    valueList = ['1111111111', 'Jeppe Boegh', 'Andersen', 'Thorsgade 20', '8410', 'dreng', '', '86379790', 'Baltica', '1111111112', '1111111113', '1111111114']
    aRow.putAllValues(valueList)
    aRow.printContents()
    print
    print 'create a selection of all children that live at the same address as their mother'
    SQLordre = '''SELECT Child.cpr, Child.firstname, Child.lastname 
FROM Child, Person
WHERE Child.hasMother = Person.cpr AND Child.address = Person.address'''
    aSelection = Temporary(['cpr','firstname','lastname'],SQLordre)
    aSelection.printContents()


    print aSelection.makeHTMLTable()

    print
    print 'Introspect a Table'
    print aTable.introspect()
    print
    print 'Introspect a Row'
    print aRow.introspect()
    print
    print 'Introspect a Selection'
    print aSelection.introspect()