##############################################################################
# 
# Zope Public License (ZPL) Version 0.9.7
# ---------------------------------------
# 
# Copyright (c) Digital Creations.  All rights reserved.
# 
# This license has been certified as Open Source(tm).
# 
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
# 
# 1. Redistributions in source code must retain the above copyright
#    notice, this list of conditions, and the following disclaimer.
# 
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions, and the following disclaimer in
#    the documentation and/or other materials provided with the
#    distribution.
# 
# 3. Digital Creations requests that attribution be given to Zope
#    in any manner possible. Zope includes a "Powered by Zope"
#    button that is installed by default. While it is not a license
#    violation to remove this button, it is requested that the
#    attribution remain. A significant investment has been put
#    into Zope, and this effort will continue if the Zope community
#    continues to grow. This is one way to assure that growth.
# 
# 4. All advertising materials and documentation mentioning
#    features derived from or use of this software must display
#    the following acknowledgement:
# 
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
# 
#    In the event that the product being advertised includes an
#    intact Zope distribution (with copyright and license included)
#    then this clause is waived.
# 
# 5. Names associated with Zope or Digital Creations must not be used to
#    endorse or promote products derived from this software without
#    prior written permission from Digital Creations.
# 
# 6. Modified redistributions of any form whatsoever must retain
#    the following acknowledgment:
# 
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
# 
#    Intact (re-)distributions of any official Zope release do not
#    require an external acknowledgement.
# 
# 7. Modifications are encouraged but must be packaged separately as
#    patches to official Zope releases.  Distributions that do not
#    clearly separate the patches from the original work must be clearly
#    labeled as unofficial distributions.  Modifications which do not
#    carry the name Zope may be packaged in any form, as long as they
#    conform to all of the clauses above.
# 
# 
# Disclaimer
# 
#   THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
#   EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
#   IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
#   PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
#   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
#   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
#   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
#   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
#   SUCH DAMAGE.
# 
# 
# This software consists of contributions made by Digital Creations and
# many individuals on behalf of Digital Creations.  Specific
# attributions are listed in the accompanying credits file.
# 
##############################################################################

"""Db connection implementation"""
__version__='$Revision: 1.9 $'[11:-2]

import _pg, regex, sys, types
import Shared.DC.ZRDB.THUNK
from string import strip, split, find, join
from time import gmtime, strftime

failures=calls = 0

nonselect_desc=[
    ('Query',  'meta', "string", 62, ''),
    ('Status', 'meta', "string", 12, ''),
    ('Calls',  'meta', "string", 12, ''),
    ]

class DB(Shared.DC.ZRDB.THUNK.THUNKED_TM):

    defs={
        "long": "i", "float": "n", "string": "t", "int": "i",
        }

    Database_Error=_pg.error

    def _begin(self):
        self.db.query('begin transaction')

    def _finish(self):
        self.db.query('commit transaction')

    def _abort(self):
        self.db.query('rollback transaction')

    def __init__(self,connection):
        self.connection=connection
        info=split(connection)
        if info and len(info) < 6:
            while len(info) < 5: info.append('')
            [user, pw, opt, tty]=info[1:]
            dbhost=split(info[0],'@')
            if dbhost and len(dbhost) < 3:
                if len(dbhost) == 1: db, host, port = dbhost[0], None, -1
                else:
		    db = dbhost[0]
		    dbhost = split(dbhost[1], ':')
		    if dbhost and len(dbhost) < 3:
		        if len(dbhost) == 1: host, port = dbhost[0], 5432
			else:
			    [host, port] = dbhost
			    port = int(port)

                c=_pg.connect(db, host, port, opt, tty, user, pw)
                self.db=c
                return

        raise self.Database_Error, (
            'Invalid connection string, <code>%s</code>.\n'
            'The connection should consist of a database name,\n'
            'user name and optional password, options and ttyname\n'
	    'separated by spaces.\n'
            'The database name may include a host specification\n'
            'by following the database name with an @ symbol and a\n'
            'host name with optional : symbol and port number.\n'
	    'A sample connection string is:\n'
            '<pre>\n'
            'sample@cooldata.com:5432 public\n'
            '</pre>\n'
            'If the password, options and ttyname are ommitted,\n'
	    'empty strings will be used.'
            )

    def close(self):
        self.db.close()
        self.db=None

    def query(self,query_string,
              max_rows=9999999,
              select_m=regex.compile('[\0- ]*select[\0- ]+',
                                     regex.casefold).match,
              ):
        db=self.db
        try:
            queries=filter(None, map(strip,split(query_string, '\0')))
            if not queries: raise 'Query Error', 'empty query'
            result=()
            desc=None
            for qs in queries:
		self._register()
                c=db.query(qs)
                try:
                    desc=c.listfields()
                    r=c.getresult()
                except: r=None
                if not r: continue
                if result:
                    raise 'Query Error', (
                        'select in multiple sql-statement query'
                        )
                result=r
        except self.Database_Error, mess:
            db.query('abort transaction')
            raise sys.exc_type, sys.exc_value, sys.exc_traceback

        if desc is None: return (), ()

        items=[]
        func=items.append

	if not result:
	   for d in desc:
            item={'name': d,
                  'type': 'string',
                  'width': len(d),
                 }
            func(item)
	    return items, result

        defs=self.defs
	i=0
        for d in desc:
            item={'name': d,
                  'type': defs[self.findType(result[0][i])],
                  'width': len(d),
                 }
            func(item)
	    i=i+1
        return items, result

    def findType(self, field):
	"""Find the type of a field"""
	self.field=field
	if type(field)==types.StringType:
	    return 'string'
	elif type(field)==types.IntType:
	    return 'int'
	elif type(field)==types.LongType:
	    return 'long'
	elif type(field)==types.FloatType:
	    return 'float'
	else:
	    return 'error'