########################################################################
#
# File Name: 	        Postgres.py
#
# Documentation:	http://docs.ftsuite.com/4ODS/Drivers/Postgres.py.html
#
"""
The Postgres back-end.
WWW: http://4suite.org/4ODS         e-mail: support@4suite.org

Copyright (c) 1999 Fourthought, Inc, USA.   All Rights Reserved.
See  http://4suite.org/COPYRIGHT  for license and copyright information
"""
import sys,string,types,base64

import string, cPickle
from Ft.Ods.Exception import FtodsObjectNotFound, FtodsUnsupportedError

from Ft.Ods.StorageManager.Adapters import Constants
from Ft.Ods.StorageManager.Adapters import SQL
from Ft.Ods.StorageManager.Adapters.SQL import CompiliedStatement
import PostgresStatements
import PManager
import _pg

BLOB_CHUNK_LENGTH = 7900

def _4ods_anyToSql(value):
    return "'"+escapeQuotes(cPickle.dumps(value))+"'"

def escapeQuotes(qstr):
    """----------------------------------------------------------
    Postgres uses single quotes for string marker, so put a
    backslash before single quotes for insertion into a database.
    pre: qstr = string to be escaped
    post: return the string with all single quotes escaped
    ----------------------------------------------------------"""
    if qstr is None:
        return ''
    tmp = string.replace(qstr,"\\","\\\\")
    tmp = string.replace(tmp, "'", "\\'")
    return tmp






class DbAdapter(SQL.DbAdapter):
    statements = {}
    uptoDateStatements = {}
    nameMap = {}
    manager = PManager.DbManager()
    
    ################
    #The Transactional Interface
    ################
    def begin(self,db):
        db.query('BEGIN')


    def commit(self,db):
        db.query('COMMIT')
        db.close()

    def abort(self,db):
        db.close()

    def checkpoint(self,db):
        db.query('COMMIT')
        db.query('BEGIN')
        

    def lock(self,db,repoId,mode):
        raise FtodsUnsupportedError(feature = "Postgres Locking")
        table_name = self._4ods_getTableNameFromRepoId(txId,repoId)
        if mode == Constants.READ_LOCK:
            #Do a read lock
            qstr = 'LOCK %s IN ACCESS SHARE MODE'
        else:
            #Do a write lock
            qstr = 'LOCK %s in ACCESS EXCLUSIVE MODE'

        self._db.execute(qstr)
        return

    def try_lock(self,repoId,mode):
        raise FtodsUnsupportedError(feature = "Postgres Locking")
        #FIXME how do we do this in Postgres???
        self.lock(repoId,mode)



    #####################################
    #MISC
    #####################################


    def _execute(self,db,st):
        return db.query(st)

    statements[CompiliedStatement.NEW_PYTHON_CLASS_ID] = PostgresStatements.NewPythonClassIdStatement()
    statements[CompiliedStatement.NEW_PYTHON_CLASS] = PostgresStatements.NewPythonClassStatement()
    statements[CompiliedStatement.GET_PYTHON_CLASS] = PostgresStatements.GetPythonClassStatement()
    statements[CompiliedStatement.DELETE_PYTHON_CLASS] = PostgresStatements.DeletePythonClassStatement()
    statements[CompiliedStatement.GET_PYTHON_CLASS_ID] = PostgresStatements.GetPythonClassIdStatement()

    statements[CompiliedStatement.NEW_LITERAL_CLASS] = PostgresStatements.NewLiteralClassStatement()
    statements[CompiliedStatement.GET_LITERAL_CLASS] = PostgresStatements.GetLiteralClassStatement()
    statements[CompiliedStatement.DELETE_LITERAL_CLASS] = PostgresStatements.DeleteLiteralClassStatement()

    statements[CompiliedStatement.INSERT_OPERATION] = PostgresStatements.InsertOperationStatement()
    statements[CompiliedStatement.UPDATE_OPERATION] = PostgresStatements.UpdateOperationStatement()
    statements[CompiliedStatement.GET_OPERATION] = PostgresStatements.GetOperationStatement()


    

    #####################
    #Repository Management
    #####################
    statements[CompiliedStatement.NEW_REPO_ID] = PostgresStatements.NewRepoIdStatement()
    statements[CompiliedStatement.NEW_REPO] = PostgresStatements.NewRepoStatement()
    statements[CompiliedStatement.NEW_REPO_MAP] = PostgresStatements.NewRepoMapStatement()
    statements[CompiliedStatement.GET_REPO] = PostgresStatements.GetRepoStatement()
    statements[CompiliedStatement.GET_REPO_TUPLE] = PostgresStatements.GetRepoTupleStatement()
    statements[CompiliedStatement.DELETE_REPO] = PostgresStatements.DeleteRepoStatement()
    statements[CompiliedStatement.DELETE_REPO_MAP] = PostgresStatements.DeleteRepoMapStatement()
    statements[CompiliedStatement.UPDATE_REPO_TIME] = PostgresStatements.UpdateRepoTimeStatement()
    uptoDateStatements[Constants.Types.ROBJECT] = PostgresStatements.CheckRepoTimeStatement()
    nameMap['references'] = '_references'


    #################################
    #Objects
    #################################
    statements[CompiliedStatement.NEW_OBJECT_ID] = PostgresStatements.NewObjectIdStatement()
    statements[CompiliedStatement.NEW_OBJECT_MAP] = PostgresStatements.NewObjectMapStatement()
    statements[CompiliedStatement.NEW_OBJECT] = PostgresStatements.NewObjectStatement()
    statements[CompiliedStatement.UPDATE_OBJECT_TIME] = PostgresStatements.UpdateObjectTimeStatement()
    statements[CompiliedStatement.GET_OBJECT] = PostgresStatements.GetObjectStatement()
    statements[CompiliedStatement.GET_OBJECT_TUPLE] = PostgresStatements.GetObjectTupleStatement()
    statements[CompiliedStatement.DELETE_OBJECT] = PostgresStatements.DeleteObjectStatement()
    statements[CompiliedStatement.DELETE_OBJECT_MAP] = PostgresStatements.DeleteObjectMapStatement()
    statements[CompiliedStatement.GET_OBJECT_IDS] = PostgresStatements.GetObjectIdsStatement()
    uptoDateStatements[Constants.Types.POBJECT] = PostgresStatements.CheckObjectTimeStatement()


    #############################
    #Extents
    #############################
    statements[CompiliedStatement.NEW_EXTENT_ID] = PostgresStatements.NewExtentIdStatement()
    statements[CompiliedStatement.NEW_EXTENT] = PostgresStatements.NewExtentStatement()
    statements[CompiliedStatement.GET_EXTENT_ID] = PostgresStatements.GetExtentIdStatement()
    statements[CompiliedStatement.ADD_EXTENT_MAPPING] = PostgresStatements.AddExtentMappingStatement()
    statements[CompiliedStatement.DROP_EXTENT_MAPPING] = PostgresStatements.DropExtentMappingStatement()
    statements[CompiliedStatement.DROP_ALL_EXTENT_MAPPING] = PostgresStatements.DropAllExtentMappingStatement()
    statements[CompiliedStatement.DROP_EXTENT] = PostgresStatements.DropExtentStatement()
    statements[CompiliedStatement.GET_EXTENT_NAMES] = PostgresStatements.GetExtentNamesStatement()
    statements[CompiliedStatement.GET_EXTENT_ID_AND_TYPE] = PostgresStatements.GetExtentIdAndTypeStatement()
    statements[CompiliedStatement.GET_EXTENT] = PostgresStatements.GetExtentStatement()


    ##############################
    #Bindings
    #############################
    statements[CompiliedStatement.GET_BOUND_OBJECT] = PostgresStatements.GetBoundObjectStatement()
    statements[CompiliedStatement.ADD_BOUND_NAME] = PostgresStatements.AddBoundNameStatement()
    statements[CompiliedStatement.DROP_BOUND_NAME] = PostgresStatements.DropBoundNameStatement()
    statements[CompiliedStatement.GET_BOUND_NAMES] = PostgresStatements.GetBoundNamesStatement()
    statements[CompiliedStatement.GET_OBJECT_BOUND_NAMES] = PostgresStatements.GetObjectBoundNamesStatement()


    ##############################
    #Collections
    ##############################
    statements[CompiliedStatement.NEW_COLLECTION_ID] = PostgresStatements.NewCollectionIdStatement()
    statements[CompiliedStatement.NEW_COLLECTION] = PostgresStatements.NewCollectionStatement()
    statements[CompiliedStatement.UPDATE_COLLECTION_INDEXES_ADD] = PostgresStatements.UpdateCollectionIndexesAddStatement()
    statements[CompiliedStatement.NEW_COLLECTION_ENTRY] = PostgresStatements.NewCollectionEntryStatement()
    statements[CompiliedStatement.DELETE_COLLECTION_ENTRY] = PostgresStatements.DeleteCollectionEntryStatement()
    statements[CompiliedStatement.UPDATE_COLLECTION_INDEXES_REMOVE] = PostgresStatements.UpdateCollectionIndexesRemoveStatement()
    statements[CompiliedStatement.DELETE_COLLECTION] = PostgresStatements.DeleteCollectionStatement()
    statements[CompiliedStatement.DELETE_COLLECTION_TUPLE] = PostgresStatements.DeleteCollectionTupleStatement()
    statements[CompiliedStatement.GET_COLLECTION] = PostgresStatements.GetCollectionStatement()
    statements[CompiliedStatement.GET_COLLECTION_TUPLE] = PostgresStatements.GetCollectionTupleStatement()
    statements[CompiliedStatement.UPDATE_COLLECTION_TIME] = PostgresStatements.UpdateCollectionTimeStatement()

    uptoDateStatements[Constants.Types.LIST_COLLECTION] = PostgresStatements.CheckCollectionTimeStatement()
    uptoDateStatements[Constants.Types.SET_COLLECTION] = PostgresStatements.CheckCollectionTimeStatement()
    uptoDateStatements[Constants.Types.BAG_COLLECTION] = PostgresStatements.CheckCollectionTimeStatement()



    ##############################
    #Dictionariess
    ##############################
    statements[CompiliedStatement.NEW_DICTIONARY_ID] = PostgresStatements.NewDictionaryIdStatement()
    statements[CompiliedStatement.NEW_DICTIONARY] = PostgresStatements.NewDictionaryStatement()
    statements[CompiliedStatement.NEW_DICTIONARY_ENTRY] = PostgresStatements.NewDictionaryEntryStatement()
    statements[CompiliedStatement.UPDATE_DICTIONARY_ENTRY] = PostgresStatements.UpdateDictionaryEntryStatement()
    statements[CompiliedStatement.DELETE_DICTIONARY_ENTRY] = PostgresStatements.DeleteDictionaryEntryStatement()
    statements[CompiliedStatement.DELETE_DICTIONARY] = PostgresStatements.DeleteDictionaryStatement()
    statements[CompiliedStatement.DELETE_DICTIONARY_TUPLE] = PostgresStatements.DeleteDictionaryTupleStatement()
    statements[CompiliedStatement.GET_DICTIONARY] = PostgresStatements.GetDictionaryStatement()
    statements[CompiliedStatement.GET_DICTIONARY_TUPLE] = PostgresStatements.GetDictionaryTupleStatement()
    statements[CompiliedStatement.UPDATE_DICTIONARY_TIME] = PostgresStatements.UpdateDictionaryTimeStatement()

    uptoDateStatements[Constants.Types.DICTIONARY_COLLECTION] = PostgresStatements.CheckDictionaryTimeStatement()



    #######################
    #Blobs
    #######################
    #FIXME extra space is not released

    def newBlob(self,db):
        res = db.query("SELECT NEXTVAL('ftods_blobid')")
        if res:
            try:
                return res.getresult()[0][0]
            except AttributeError:
                return None
        return None

    def readBlob(self,db,bid):
        res = db.query("SELECT bdata FROM ftods_blobs WHERE bid = %d ORDER BY bindex" % bid)
        if res:
            try:
                data = res.getresult()
            except AttributeError:
                return None
        if not data:
            return None
        return base64.decodestring(string.join(map(lambda x:x[0],data),''))



    def writeBlob(self,db,bid,data):

        #Convert data for writing
        data = base64.encodestring(data)

        chunks = []
        index = 0
        while (len(data) - index) > BLOB_CHUNK_LENGTH:
            chunks.append(data[index:index+BLOB_CHUNK_LENGTH])
            index = index + BLOB_CHUNK_LENGTH
                  
        chunks.append(data[index:])
        l = 0
        for c in chunks:
            l = l + len(c)

        #Delete the old blob
        db.query("DELETE FROM ftods_blobs where bid = %d" % bid)


        #Add the new data
        for ctr in range(len(chunks)):
            db.query("""INSERT INTO ftods_blobs (bid,bindex,bdata) VALUES (%d, %d, '%s')""" % (bid,ctr,chunks[ctr]))

    def deleteBlob(self,db,bid):
        db.query("DELETE FROM ftods_blobs where bid = %d" % bid)
        


#######################
#
# Developers Note:
#
# Exlpoding the tables in a SQL database is __alot__ of work but will need to be done before
# All of OQL works.  To get around this vast amount of work now, we will use cPickle on the listeral values
###
#######################



    toSqlValues = {Constants.Types.STRING:lambda x:"'%s'"%escapeQuotes(x),
                   Constants.Types.BLOB:str,
                   Constants.Types.UNSIGNED_SHORT:str,
                   Constants.Types.ROBJECT:str,
                   Constants.Types.POBJECT:str,
                   Constants.Types.SIGNED_SHORT:str,
                   Constants.Types.DOUBLE:str,
                   Constants.Types.FLOAT:str,
                   Constants.Types.UNSIGNED_LONG:lambda x:"%g"%x,
                   Constants.Types.SIGNED_LONG_LONG:lambda x:repr(long(x))[:-1],
                   Constants.Types.SIGNED_LONG:lambda x:"%g"%x,
                   Constants.Types.BOOLEAN:lambda x:x and "'t'" or "'f'",
                   Constants.Types.LIST_COLLECTION:str,
                   Constants.Types.SET_COLLECTION:str,
                   Constants.Types.BAG_COLLECTION:str,
                   Constants.Types.DICTIONARY_COLLECTION:str,
                   Constants.Types.FIXEDSTRING:lambda x:"'%s'"%escapeQuotes(type(x) == type(()) and x[0] or x),

                   Constants.Types.DATE:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),
                   Constants.Types.INTERVAL:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),
                   Constants.Types.TIME:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),
                   Constants.Types.TIMESTAMP:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),

                   Constants.Types.CHAR:lambda x:"'%s'"%escapeQuotes(x),
                   Constants.Types.OCTET:str,

                   Constants.Types.STRUCTURE:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),
                   Constants.Types.UNION:lambda x:"'%s'"%escapeQuotes(cPickle.dumps(x)),
                   Constants.Types.ENUMERATION:lambda x:str((type(x) == type(()) and x[0]+1 or x+1)-1),
                   }
                   

    toOdmgValues = {Constants.Types.UNSIGNED_SHORT:int,
                    Constants.Types.ROBJECT:int,
                    Constants.Types.POBJECT:int,
                    Constants.Types.SIGNED_SHORT:int,
                    Constants.Types.UNSIGNED_LONG:int,
                    Constants.Types.SIGNED_LONG_LONG:long,
                    Constants.Types.SIGNED_LONG:long,
                    Constants.Types.DOUBLE:float,
                    Constants.Types.FLOAT:float,
                    Constants.Types.STRING:str,
                    Constants.Types.BLOB:int,
                    Constants.Types.BOOLEAN:lambda x: x == 't',
                    Constants.Types.LIST_COLLECTION:int,
                    Constants.Types.SET_COLLECTION:int,
                    Constants.Types.BAG_COLLECTION:int,
                    Constants.Types.DICTIONARY_COLLECTION:int,
                    Constants.Types.FIXEDSTRING:lambda x:x and str(x) or "",

                    Constants.Types.DATE:  lambda x:x and cPickle.loads(x) or None,
                    Constants.Types.INTERVAL:  lambda x:x and cPickle.loads(x) or None,
                    Constants.Types.TIME:  lambda x:x and cPickle.loads(x) or None,
                    Constants.Types.TIMESTAMP:  lambda x:x and cPickle.loads(x) or None,

                    Constants.Types.CHAR:str,
                    Constants.Types.OCTET:int,

                    Constants.Types.STRUCTURE: lambda x:x and cPickle.loads(x) or None,
                    Constants.Types.UNION: lambda x:x and cPickle.loads(x) or None,
                    Constants.Types.ENUMERATION: lambda x:int(x),

                    }




