autocommit property broken with MySQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I can't log this as a bug on BerliOS (since I can't seem to create an 
account), so I thought I'd bring this up here instead.

I have been using PyDO-2.0b1 with MySQL and have been playing around 
with InnoDB tables and ACID transactions (i.e., with commit, rollback, 
etc.). I am using PyDO with MySQLdb 1.0.1.

I am trying to use the autocommit property, but I've noticed that it 
doesn't work with this setup:

> >>> import MySQLdb.constants.CLIENT
> >>> import pydo
> >>> pydo.dbi.initAlias('my_test', 'mysql', { 'db': 'my_test', 'user': 
> 'my_test', 'passwd': 'my_test', 'client_flag': 
> MySQLdb.constants.CLIENT.FOUND_ROWS })
> >>> db = pydo.dbi.getConnection('my_test')
> >>> db.autocommit
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File 
> "/Users/matt/Documents/dev/solus/dev/trunk/src/lib/py/pydo/dbi.py", 
> line 40, in fget
>     return self.conn.autocommit
> AttributeError: 'Connection' object has no attribute 'autocommit'

Setting the property before attempting to get its value avoids the 
above exception, but has no affect on the underlying connection.

I'm proposing that the pydo.drivers.mysqlconn.MysqlDBI class be amended 
to include the following (overriding) definition (I've tried to stay 
true to form of the original code):

> class MysqlDBI(DBIBase):
>
>     ...
>
>     def autocommit():
>         def fget(self):
>             ac_row = self.execute('SELECT @@SESSION.AUTOCOMMIT')
>             ac_val = ac_row[0]['AUTOCOMMIT']
>             return bool(ac_val)
>         def fset(self, val):
>             ac_val = int(val == True)
>             self.execute('SET AUTOCOMMIT = %s', (ac_val,))
>         fdel = DBIBase.autocommit.fdel
>         doc = DBIBase.autocommit.__doc__
>         return fget, fset, fdel, doc
>     autocommit=property(*autocommit())
>
>     ...

This, while (I believe) correct, is not scalable, since the autocommit 
property is accessed very frequently (potentially with every call to 
execute()). After thinking about it some more, the following might be a 
better solution:

> class MysqlDBI(DBIBase):
>
>     ...
>
>     def __init__(self, ...):
>         ...
>         ac_row = self.execute('SELECT @@SESSION.AUTOCOMMIT')
>         ac_val = bool(ac_row[0]['AUTOCOMMIT'])
>         DBIBase.autocommit.fset(self, ac_val)
>
>     ...
>
>     def autocommit():
>         fget = DBIBase.autocommit.fget
>         def fset(self, val):
>             ac_val = bool(val)
>             self.execute('SET AUTOCOMMIT = %s', (int(ac_val),))
>             DBIBase.autocommit.fset(self, ac_val)
>         fdel = DBIBase.autocommit.fdel
>         doc = DBIBase.autocommit.__doc__
>         return fget, fset, fdel, doc
>     autocommit=property(*autocommit())
>
>     ...

This approach may break if the underlying implementation of the 
autocommit property of the base class changes, but at least this will 
stay (mostly) true to form and effectively cache the value of the 
AUTOCOMMIT session variable for MySQL connections without unnecessary 
queries.

I believe this will also solve the MySQL breakage problem seen above.

Just my $0.02 from looking at it for a brief moment.

	-- Matt

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)

iD8DBQFC+ZB8nLpDzL5I7l8RAi5NAKCKQabp+eY7DmNlBQdcaN1D0CiyVACdHO6C
iaAaLX+lxCykhs8vmFTF/IA=
=2Oij
-----END PGP SIGNATURE-----



-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf