########################################################################
#
# File Name:            PgDatabase.py
#
# Documentation:        http://docs.ftsuite.com/Lib/PgDatabase.py.html
#
"""
A utility class for Postgres connections.
WWW: http://4suite.org/4ODS         e-mail: support@4suite.org

Copyright (c) 2000-2001 Fourthought, Inc. USA.   All Rights Reserved.
See  http://4suite.org/COPYRIGHT  for license and copyright information
"""

import string, os
from Ft.Lib import PgException

try:
    from pg import DB
except ImportError:
    raise PgException(PgException.NO_PYGRES)

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 Database:
    def __init__(self, name, hostName=None, port=-1, user=None, passwd=None):
        """----------------------------------------------------------
        initialize the DB object.
        pre: name = name of the postgres database
        post: self.db is a valid postgres database object.
        ----------------------------------------------------------"""
        self.db = DB(name, host=hostName, port=port, user=user, passwd=passwd)
        #as a kludge until large objects are fixed,
        #store all los in the file system
        self.lo_path="/var/data/"+name
        return

    def queryAllTuple(self, qstr):
        """----------------------------------------------------------
        send a generic query to the database.  Handles large objects
        pre: qstr = query string
        post: if the query is a SELECT, return a dictionary of results
              else return none
        ----------------------------------------------------------"""
        res = self.db.db.query(qstr)
        if res:
            try:
                res = res.getresult()
            except AttributeError:
                res = None
        return res

    def query(self, qstr):
        """----------------------------------------------------------
        send a generic query to the database.  Handles large objects
        pre: qstr = query string
        post: if the query is a SELECT, return a dictionary of results
              else return none
        ----------------------------------------------------------"""
        try:
            return self.db.db.query(qstr).dictresult()
        except AttributeError:
            return None

    def insert(self,qstr):
        return self.db.db.query(qstr)

    queryAllDict = query
    execute = query

    def query_field(self, qstr, field):
        """----------------------------------------------------------
        send a generic query to the database
        pre: qstr = query string, field = databse column name
        post: return just a list with the column of the named field.
              Especially useful when the table has only one column
        ----------------------------------------------------------"""
        q = self.db.db.query(qstr)
        q = q.dictresult()
        return map(lambda x, n = field: x[n], q)

    def create_lo(self, text):
        #we cannot use the Postgres large-object interface (The code
        #between "BEGIN" and "END") until version 6.4

        #self.query("BEGIN")
        lobj = self.db.locreate(pgext.INV_WRITE | pgext.INV_READ)
        #lobj.open(pgext.INV_WRITE)
        #lobj.write(text)
        #lobj.close()
        #self.query("END")

        #we only used locreate above to gat a unique object id (oid)
        f = open(self.lo_path+`lobj.oid`, "w")
        f.write(text)
        f.close()

        return lobj.oid

    def retrieve_lo(self, oid):
        #we cannot use the Postgres large-object interface (The code
        #between "BEGIN" and "END") until version 6.4

        #self.query("BEGIN")
        #lobj = self.db.getlo(oid)
        #lobj.open(pgext.INV_READ)
        #text = lobj.read(10000)
        #lobj.close()
        #self.query("END")

        f = open(self.lo_path+`oid`, "r")
        text = f.read()
        f.close()

        return text

    def delete_lo(self, oid):
        #we cannot use the Postgres large-object interface (The code
        #between "BEGIN" and "END") until version 6.4

        #self.query("BEGIN")
        #lobj = self.db.getlo(oid)
        #lobj.unlink()

        os.unlink(self.lo_path+`oid`)
        self.query("END")
        return

    def __del__(self):
        try:
            self.db.close()
        except:
            pass

