[Zope-DB] ZFirebirdDA

Godefroid Chapelle gotcha@swing.be
Fri, 10 May 2002 11:06:59 +0200


--=====================_3446646==_
Content-Type: text/plain; charset="us-ascii"; format=flowed

Hi Phil,

After having began to use ZFirebirdDA, I found out some bugs :

First, there was never any data after an INSERT statement.

This was fixed by W. Kellogs which sent a modified db.py on Zope-DB list.

Second, after un unsucessful INSERT statement try (with Kellogs version) 
for example because of attempt to save a duplicate primary key, the 
connection would stay in a bad state causing an error with any next 
statement, even simple SELECT.

I think I patched this error.

You can find enclosed the version of db.py with both Kellogs and my patches.

Could you publish a new version of ZFirebirdDA so that it is more usable 
and that we get more chances that many people use it and help us test it or 
(better) fix it ?

Thanks. 
--=====================_3446646==_
Content-Type: text/plain; charset="us-ascii"
Content-Disposition: attachment; filename="db.py"

#####
# ZFireBirdDA
#
# (C) Copyright 2000,2001 Phil Harris
# ACN: 082 081 472  ABN: 83 082 081 472
# All Rights Reserved
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS 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 THE AUTHOR OR 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
#
# Author Phil Harris
#####
'''ZFireBird'''
__version__='0.0.2'

import kinterbasdb, DateTime
# W Robert Kellock 11/04/02 this is a single threaded adaptor so
# to be safe serialize threads
#
# from Shared.DC.ZRDB.TM import TM
import Shared.DC.ZRDB.THUNK

import string, sys
strsplit=string.split
from string import strip, find
from time import time

failures=0
calls=0
last_call_time=time()

# W Robert Kellock 11/04/02 this is a single threaded adaptor so
# to be safe serialize threads
#
# class DB(TM):
class DB(Shared.DC.ZRDB.THUNK.THUNKED_TM):

    _p_oid=_p_changed=_registered=None

    Database_Connection=kinterbasdb.connect
    Database_Error=kinterbasdb.Error

    def __init__(self,connection):
        self.connection=connection
# Godefroid Chapelle 09/05/02 separate connection and cursor reset
        self.connect()
        self.resetCursor()
# GC

# Godefroid Chapelle 09/05/02 separate connection and cursor reset
    def connect(self):        
        try:
            self.db.close()
        except:
            pass
        #---
        # parse the connection string
        #---
        connbits=strsplit(self.connection, ' ')
        db=kinterbasdb.connect(dsn=connbits[0],user=connbits[1],password=connbits[2])
        self.db=db
        self.db.begin()

    def resetCursor(self):
        try:
            self.cursor.close()
        except:
            pass
        self.cursor=self.db.cursor()
# GC
   
    def str(self,v, StringType=type('')):
        if v is None: return ''
        r=str(v)
        if r[-1:]=='L' and type(v) is not StringType: r=r[:-1]
        return r

    def _finish(self, *ignored):
        # W Robert Kellock 12/04/02 changed to explicitly commit transaction
        # pass
        self.db.commit()
# Godefroid Chapelle 09/05/02 reinitialize connection
        self.db.begin()
# GC

    def _abort(self, *ignored):
        # W Robert Kellock 12/04/02 changed to explicitly abort transaction
        # pass
        self.db.rollback()
# Godefroid Chapelle 09/05/02 reinitialize connection
        self.db.begin()
        self.resetCursor()
# GC

    def tables(self, rdb=0,
               _care=('TABLE', 'VIEW')):
        r=[]
        a=r.append
        c=self.db.cursor()
        c.execute('''SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE ( NOT (RDB$RELATION_NAME STARTING WITH 'RDB$') ) ORDER BY 1''')

        tabs=c.fetchall()
        for tab in tabs:
            a({'TABLE_NAME': strip(str(tab[0])), 'TABLE_TYPE': 'TABLE'})
        return r

    def columns(self, table_name):
        c=self.cursor
        try: r=c.execute('''select  r.rdb$field_name field_name, t.rdb$type_name type_name,
        f.rdb$field_length field_length, f.rdb$field_sub_type field_sub_type,
        f.rdb$null_flag null_flag, rdb$field_precision field_precision,
        f.rdb$field_scale field_scale, f.rdb$default_source default_value
 from    rdb$relation_fields r, rdb$types t, rdb$fields f
 where   r.rdb$relation_name='%s' and
        f.rdb$field_name=r.rdb$field_source and
        t.rdb$field_name='RDB$FIELD_TYPE' and
        f.rdb$field_type=t.rdb$type;''' % table_name)
        except:
# Godefroid Chapelle 09/05/02 separate connection and cursor reset
            self.resetCursor()
# GC
            return ()
        
        rows=c.fetchallmap()
        r=[]
        a=r.append
        for row in rows:
            name=row['field_name']
            typ=strip(row['type_name'])
            p=row['field_length']
            scale=row['field_scale']
            null_ok=row['null_flag'] is not None and 'Nullable' or ''
            default=row['default_value']
            print "'%s'" % typ
            if strip(typ)=='VARYING':
                typ='VARCHAR'
            subtyp=''
            if typ=='BLOB':
                subtyp=row['field_sub_type']
                c.execute('select rdb$type_name from rdb$types where rdb$type=%s' % subtyp)
                subtyp=c.fetchall()
                print subtyp
                subtyp=strip(subtyp[0][0])
            a({ 'Name': strip(name),
                'Type': strip(typ),
                'Precision': strip(str(p)),
                'Scale': strip(str(scale)),
                'Nullable': strip(str(null_ok)),
                'Default': strip(str(default)),
                'SubType': strip(str(subtyp)),
                })

        return r

    def query(self,query_string, max_rows=9999999):
        global failures, calls, last_call_time
        calls=calls+1
        desc=None
        result=()
        self._register()
        c=self.cursor
        try:
            for qs in filter(None, map(strip,strsplit(query_string, '\0'))):
                r=c.execute(qs)
                if r is None:
                    if desc is not None:
                        if c.description != desc:
                            raise 'Query Error', ( 'Multiple select schema are not allowed')
                        if type(result) is not ListType: result=list(result)
                        if max_rows:
                            for row in c.fetchmany(max_rows-len(result)):
                                result.append(row)
                    else:
                        desc=c.description
                        # W Robert Kellock 12/04/02 changed max_rows to desc
                        if desc:
                            if max_rows==1: result=(c.fetchone(),)
                            else: result=c.fetchmany(max_rows)
                        
            failures=0
            last_call_time=time()
        except kinterbasdb.ProgrammingError, mess:
            if mess[0]!=0:
                    raise self.Database_Error(mess)
# Godefroid Chapelle 09/05/02 separate connection and cursor reset
            self.resetCursor()
# GC
        except self.Database_Error,mess:
            raise sys.exc_type, sys.exc_value, sys.exc_traceback

        if desc is None: return (),()

        items=[]
        for name, type, width, ds, p, scale, null_ok in desc:
            if type=='NUMBER':
                if scale==0: type='i'
                else: type='n'
            elif type=='DATE':
                type='d'
            else: type='s'
            items.append({
                'name': name,
                'type': type,
                'width': width,
                'null': null_ok,
                })
        return items, result



--=====================_3446646==_
Content-Type: text/plain; charset="us-ascii"; format=flowed

--

Godefroid Chapelle

BubbleNet sprl
rue Victor Horta, 18 / 202
1348 Louvain-la-Neuve
Belgium

Tel + 32 (10) 459901
Mob + 32 (477) 363942

TVA 467 093 008
RC Niv 49849

--=====================_3446646==_--