#!bin/python

###############################
# Author: Ahmed Youssef
# Site  : Programming-Fr34ks.NET
# Date  : 1-8-07
###############################


#-Imports-#
import sys, os, os.path
import cPickle
#-Imports-#

#
#creating a table, columns
#CREATE tblName of (int, string, int)
#supported types are int, string
#CREATE student of (id as int, name as string, level as int)  
#

class TableNotFoundException(Exception):
    pass


class ColumnNotFoundException(Exception):
    pass


class NonValidDataException(Exception):
    pass


class Column(object):

    def __init__(self, columnName, _type):
        self.__columnName=columnName
        self.__columnType=_type #(int or string)
        self.__values=[]

    def __getType(self):
        return self.__columnType

    def __setType(self, newType):
        self.__columnType=newType

    ColumnType=property(fget=__getType, fset=__setType)

    def getValues(self):
        return self.__values
    def addValue(self, val):
        try:
            #assert isinstance(val, self.__getType)
            self.__values +=[val] #in order..
        except AssertionError, e:
            pass

    def changeVal(self, index, to):
        self.__values[index]=to

    def changeType(self, newType):
        if not isinstance(newType, self.__columnType):
            self.__columnType=newType
            self.__values=[]
        else:
            self.__columnType=newType

    def clearVals(self):
        self.__values=[]
    


class Table(object):

    def __init__(self, tblName, columns=[]):
        self.__tblName=tblName

        self.__colsTbl={} 


    def __getColumnByName(self, colName):
        if not colName in self.__columns:
            raise ColumnNotFoundException
        
        return self.__columns[self.__columns.index(colName)]
        
          
    def createColumns(self, cols=[], types=[]):
        try:
            assert len(cols)==len(types) #don't insert missin' data
            index=0
            while index<len(cols):
                 self.__colsTbl[cols[index]]= Column(cols[index], types[index])
                 index += 1
        except AssertionError, e:
            print "Error: ", e

    def insertIntoColumns(self, cols=[], vals=[]):
        assert len(cols)==len(vals)
        index=0
        while index<len(cols):
            self.__colsTbl[cols[index]].addValue(vals[index])
            index += 1



    def showMe(self):
        s=""
        index=0
        cols=self.__colsTbl.keys()
        objs=self.__colsTbl.values()

        s += "-"*30
        s +="\n"
        for col in cols:
            s += col + "\t"

        s +="\n"
        s += "-"*30
        s += "\n"
        length=len(objs[0].getValues())
        while index<length:
            s +="\n"
            for obj in objs:
                s += str(obj.getValues()[index]) +"\t"
            index += 1
        s += "\n"+"-"*30
        return s

    def getRowAtIndex(self, index):
        cols=self.__colsTbl.keys()
        objs=self.__colsTbl.values()

        s=""
        for obj in objs:
            s += str(obj.getValues()[index]) + "\t"
        return s
    
    def selectWith(self, colName, value):
        cols=self.__colsTbl.keys()
        objs=self.__colsTbl.values()

        s = ""
        s += "\nQuerying DB...\n"
        
        index=0
        cols=self.__colsTbl.keys()
        objs=self.__colsTbl.values()

        s += "-"*30
        s +="\n"
        for col in cols:
            s += col + "\t"

        s +="\n"
        s += "-"*30
        s += "\n"

        who_has_it=[]
        for obj in objs:
            if value in obj.getValues():
                numOfOcc=obj.getValues().count(value)
                who_has_it +=[obj]

        obj=who_has_it[0]
        vals=obj.getValues()
        indxS=[]
        index=0
        while index<len(vals):
            if vals[index]==value:
                indxS +=[index]
            index += 1
            
 
        for index in indxS:
            s += self.getRowAtIndex(index) + "\n"

        return s



         
        
class Database(object):

    def __init__(self, dbName, ext="pySQL"):
        self.dbName=dbName
        #mkdir for the db..
        if not os.path.exists(dbName):
           os.mkdir(dbName)
        self.tbl=None
        self.__ext=ext

    def openTable(self, tblName): #create or open
        os.chdir( self.dbName)
        self.tblName=tblName+"."+self.__ext
        if os.path.exists(self.tblName):
            self.tbl=cPickle.load(file(self.tblName))
        else:
            self.tbl=Table(self.tblName)

                           
    def saveTable(self):
        if not os.path.exists(self.tblName):
            f=file(self.tblName, "wb")
            cPickle.dump(self.tbl, f)
            f.close()
        else:
            os.remove(self.tblName)
            f=file(self.tblName, "wb")
            cPickle.dump(self.tbl, f)
            f.close()
    
            
    def close(self):
        self.saveTable()
            
                           
                           
    def deleteTable(self, tblName):
        if not tblName in os.listdir(self.dbName):
            raise TableNotFoundException("No such a table")
        os.remove(tblName)
        
    def close(self):
        pass
    
if __name__=="__main__":
    
    db=Database("students")
    db.openTable("st2")
    print db.tbl.showMe()
    print db.tbl.selectWith("name", "tina")
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[9, "hanna"])
##    db.saveTable()
    print db.tbl.selectWith("name", "hanna")
    db.close()
##    db.tbl.createColumns(cols=["id","name"], types=[int, str])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[0, "ahmed"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[1, "tina"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[2, "gina"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[3, "youssef"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[4, "guru"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[5, "ahmed"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[6, "tina"])
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[7, "tina"]) 
##    db.tbl.insertIntoColumns(cols=["id", "name"], vals=[8, "gina"]) 
##    db.saveTable()
##    db.close()