#
# author : jiankaiwang (http://jiankaiwang.no-ip.biz/)
# source code in github : seed (https://github.com/jiankaiwang/seed)
# document in gitbook : seed (https://www.gitbook.com/book/jiankaiwang/seed/details)
#

#
# example :
#
# link to postgresql database
# p2l = py2psql("127.0.0.1","5432","ckan_default","public.user","ckan_default","ckan")
#
# returned status : call status()
# { "state" : [success|failure|warning], "info" : "message", "data" : []}
#
# example.1 get table schema
# 1.1
# p2l = py2psql("127.0.0.1","5432","ckan_default","public.user","ckan_default","ckan")
# p2l.getTableSchema()  # default table, only fetches column name(desc[0])
# 1.2
# p2l2 = py2psql("127.0.0.1","5432","ckan_default","","ckan_default","ckan")
# p2l2.getTableSchema("public.user",0)  # desired table
# p2l2.getTableSchema("public.user",-1)  # fetch all description
# p2l.status()
#
# example.2 query data
# 2.1 select column name, email, 123 (not existing)
# data = p2l.select({where},[columns])
# data = p2l.select({"name":"test114"},["name","email","123"])
# data = p2l.select({"name":"test114"},[])
# 2.2 select column name, email, 123 (not existing) and also returned as dictionary
# data = p2l.select({where},[columns],asdict=True)
# data = p2l.select({"name":"test114"},["name","email","123"],asdict=True)
# data = p2l.select({"name":"test114"},[],asdict=True)
#
# example.3 update data
# 3.1
# p2l.update({set},{where})
# p2l.update({"email":"test@tw"},{"name":"test114"})
#
# example.4 insert data
# 4.1
# p2l.insert({ data })
# p2l.insert({ "id" : "acbdhcbdh-abchdbch", "name":"123","email":"123@tw" })
#
# example.5 delete data
# 5.1
# p2l.delete({where})
# p2l.delete({"name":"test1", "email":"test1@tw"})
#
# example.6 execsql data
# 6.1 only one function can create object without assigning table
# p2l.execsql("sql command", is there returned value, {parameter : value})
# p2l.execsql("select * from public.user where name = %(name)s;", True, {'name' : "test114"}, True)
# p2l.status()
# 6.2 get table list
# p2l.execsql("select * from information_schema.tables where table_name = %(name)s;", True, {'name' : "user"})
# p2l.status()
#
# example.7 create data table
# 7.1 drop first
# p2l.createTable("test", {"id" : "serial primary key", "context" : "text not null"}, dropFirst=True)
# p2l.status()
#
# example.8 alter data table
# 8.1 
# p2l.alterTable("test", {"context" : "text"}, createTableFirstIfNotExisted=False, addColIfNotExisted=False, theSameWithThisSchema=True)
# p2l.status()
#
# example.9 drop data table
# 9.1
# p2l.dropTable("test")
# p2l.status()
#

import psycopg2

class py2psql:
    
    # private member
    # host : URL or IP
    # port : postgresql server port
    # db : as a string
    # tb : as a string
    # user : as a string
    # pwd : as a string
    # data : as a dictionary, { colName : colValue }
    # columns : save table schema
    # datatype : save column data tpye { "col" : { "type" : "code", "null" : "True/False" } }
    # retStatus : returned data as a dictionary
    __host = ""
    __port = ""
    __db = ""
    __tb = ""
    __user = ""
    __pwd = ""
    __columns = []
    __datatype = {}
    __retStatus = { }

    #
    # desc : constructor
    # param@getTB : can be null when only using execsql()
    #
    def __init__(self, getHost, getPort, getDB, getTB, getUser, getPwd):
        self.__host = getHost
        self.__port = getPort
        self.__db = getDB
        self.__tb = getTB
        self.__user = getUser
        self.__pwd = getPwd
        self.__columns = []
        self.__datatype = {}
        self.__retStatus = { "state" : 0, "data" : {}, "info" : "" }

        # fetch column information
        if len(getTB) > 0:
            self.__tableSchema()

    # ------------------------
    # private member
    # ------------------------            
    #
    # desc : define server DSN
    # retn : string
    #
    def __serverDSN(self):
        conStr = ["host=" + self.__host, "port=" + self.__port, "dbname=" + self.__db, "user=" + self.__user , "password=" + self.__pwd]
        return ' '.join(conStr)

    #
    # desc : get table schema
    # retn : None
    #
    def __tableSchema(self):
        # Connect to an existing database
        conn = psycopg2.connect(self.__serverDSN())
        
        # Open a cursor to perform database operations
        cur = conn.cursor()

        # select sql
        selectStr = "select * from " + self.__tb

        cur.execute(selectStr)
        
        # get columns
        self.__columns = [desc[0] for desc in cur.description]

        # close communication
        cur.close()
        conn.close()

    #
    # desc : get table colunm data type
    # retn : column data type in the table
    #
    def __tableColDatatype(self):
        # Connect to an existing database
        conn = psycopg2.connect(self.__serverDSN())
        
        # Open a cursor to perform database operations
        cur = conn.cursor()

        # select sql
        selectStr = "select * from " + self.__tb

        cur.execute(selectStr)
        
        # get column data type
        for item in cur.description:
            self.__datatype.setdefault(item[0], { "type" : item[1] , "null" : item[6] })
        
        # close communication
        cur.close()
        conn.close()

        return self.__datatype
        
        
    #
    # desc : get col index in the column order
    # retn : -1 (None) or Number
    #
    def __getColIndex(self, getColName):
        if getColName in self.__columns:
            return self.__columns.index(getColName)
        else:
            return -1

    #
    # desc : set returned status
    # retn : None
    #
    def __setStatus(self, getStatus, getInfo, getData):
        self.__retStatus["state"] = getStatus
        self.__retStatus["data"] = getData
        self.__retStatus["info"] = getInfo

    #
    # desc : get column description on the execution pointer
    # param@getCur : a psycopg2 connect cursor
    # param@curIndex : index on the cursor description
    # retn : [] data type
    #
    def __getCurDesc(self, getCur, curIndex):
        curInfo = [desc[curIndex] for desc in getCur.description]
        return curInfo

    # ------------------------
    # public member
    # ------------------------  
                
    #
    # desc : returned status
    # retn : return executing status
    #
    def status(self):
        return self.__retStatus
    
    #
    # desc : get table schema
    # param@getTable : get desired table schema
    # param@descIndex : description index of table schema, -1 : means all
    # retn : status object
    #
    def getTableSchema(self, getTable=None, descIndex=0):    
        if self.__tb == "" and getTable == None:
            self.__setStatus("failure","There is no table assigned.",{})
        elif self.__tb != "" and getTable == None:
            try:
                self.__tableSchema()
                self.__setStatus("success","Get the table schema.", self.__columns)
            except:
                self.__setStatus("failure","Can not get the table schema.", self.__columns)
        elif getTable != None:
            # Connect to an existing database
            conn = psycopg2.connect(self.__serverDSN())
        
            # Open a cursor to perform database operations
            cur = conn.cursor()

            # select sql
            selectStr = "select * from " + getTable

            try:
                cur.execute(selectStr)
        
                # get columns desc
                if descIndex < 0:
                    getColDesc = [desc for desc in cur.description]
                else:
                    getColDesc = [desc[descIndex] for desc in cur.description]
                self.__setStatus("success","Get the table schema.", getColDesc)
            except:
                self.__setStatus("failure","Can not get the table schema.", {})

            # close communication
            cur.close()
            conn.close()             
            
        return self.__retStatus
                
    #
    # desc : select operation
    # param@getConds : {}, defined where SQL conditions
    # param@getParams : [], selected column names, empty : means all
    # param@asdict : boolean, returned row as dictionary data type
    # retn : data as [] type
    # note : also support status object, use status()
    #
    def select(self, getConds, getParams, asdict=False):
        # filter the column value
        colSelected = "*"
        colList = []
        retdata = []
        dataTuple = ()
        
        # check column existing
        if len(getParams) > 0:
            for item in getParams:        
                if self.__getColIndex(item) > -1:
                    colList.append(item)
        
        # set selected columns
        if len(colList) > 0:
            colSelected = ','.join(colList)

        try:
                                
            # Connect to an existing database
            conn = psycopg2.connect(self.__serverDSN())
            
            # Open a cursor to perform database operations
            cur = conn.cursor()
    
            # select sql
            selectStr = "select " + colSelected + " from " + self.__tb
    
            if len(getConds.keys()) > 0:
                selectStr += " where "
                item = 0
                for key, value in getConds.iteritems():
                    if item != 0:
                        selectStr += " and "
                    selectStr += str(key) + "= %s "
                    item += 1