Re: autocommit property broken with MySQL

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

On Aug 17, 2005, at 08:30, Jacob Smullyan wrote:

> I checked something in just now to support this kind of thing, but I
> didn't use your patch as is, because I don't think it works correctly
> with pools....
>
> Also, rather than using initSQL, I accept a parameter, "init", to
> initAlias, which can be a string or a callable.  If it is a callable,
> it should accept one argument, the new connection.  If it is a string,
> it is turned into a callable that executes the string as sql on the
> connection.  (I forgot for a moment that you accepted a list of
> strings, which is a good idea, and I'll go back and add that.)

This seems better than my approach. One could do something like this:

	# Generic DB init callable base class
	class DbInit(object):
		def __init__(self, *a_args, **a_kwargs):
			self.__args = a_args
			self.__kwargs = a_kwargs
		def __call__(self, a_conn):
			self.init(a_conn, *self.__args, **self.__kwargs)
		def init(self, a_conn, *a_args, **a_kwargs):
			pass # Overload this method in derived classes

If you want autocommit initialization behavior which is specific to 
each DB type, you could define an init class abstracting it:

	class MySQLInit(DbInit):
		def init(self, a_conn, a_autocommit = False):
			a_conn.execute('SET AUTOCOMMIT = %s', \
				int(bool(a_autocommit)))

	class PsychoPGInit(DbInit):
		def init(self, a_conn, a_autocommit = False):
			a_conn.conn.isolation_level = not a_autocommit

	pydo.initAlias('mysql_db', 'mysql', ..., \
		init = MySQLInit(a_autocommit = False))

	pydo.initAlias('pgsql_db', 'psycopg', ..., \
		init = PsychoPGInit(a_autocommit = False))

If this approach is too complicated for most (one callable could accept 
and execute many statements), one could also accept either a string, a 
callable, a list of strings or callables:

	if init:
		if isinstance(init, basestring) \
	        or callable(init):
			init = [init]
		for i in xrange(len(init)):
			if isinstance(init[i], basestring):
				sql = init[i]
				def _init(conn):
					c=conn.cursor()
					c.execute(sql)
					c.close()
				init[i] = _init
			...

Alternatively, one could require that init be a sequence of the format 
(init0, init1, ..., initN) where initI could be either a SQL string, a 
sequence (sql_string, (value0, value1, ..., valueN)), a sequence 
(sql_string, {key0: value0, key1: value1, ..., keyN: valueN}), or a 
callable. One could even use a callable to implement such a behavior by 
deriving from DbInit (above) as such:

	class SQLInit(DbInit):
		def init(self, a_conn, a_exec, *a_vals, **a_kwvals):
			execs = a_exec
			if isinstance(execs, basestring):
				execs = [[execs]]
				if a_vals:
					execs[0].append(a_vals)
				elif a_kwvals:
					execs[0].append(a_kwvals)
			for i in xrange(len(execs)):
				exec_val = execs[i]
				if isinstance(exec_val, basestring) \
					or operator.isSequenceType(exec_val):
					if isinstance(exec_val, basestring):
						exec_val = [exec_val, None]
					sql = exec_val[0]
					sql_vals = exec_val[1:]
					def _init(conn):
						c=conn.cursor()
						if sql_vals:
							c.execute(sql, sql_vals[0])
						else:
							c.execute(sql)
						c.close()
					execs[i] = _init
					del _init, exec_val, sql, sql_vals
				elif not callable(exec_val):
					raise ValueError, ...
			for callable in execs:
				callable(a_conn)
	init_seq = \
	[
		'SET AUTOCOMMIT = 0',
		(SET AUTOCOMMIT = %s', (0,)),
		(SET AUTOCOMMIT = %(val)s', {'val': 0}),
		MySQLInit(a_autocommit = False),
	]
	initAlias('mysql_db', 'mysql', ..., \
		init = SQLInit(init_seq))
	initAlias('pgsql_db', 'psychopg', ..., \
		init = SQLInit('SELECT * FROM %s', 'my_table');

This is probably a little too complicated that what you had in mind, 
but at least your current implementation allows for it (even if it is a 
fair amount of work for the user to do things like the above).

> I've only verified that the regression tests still work; I don't know
> if the initialization works yet.  Let me know if you try it.  Also, I
> still have been putting off fixing autocommit, for some peculiar and
> no doubt pathological psycological non-reason.

I don't think it hurts anything leaving autocommit in there (even if it 
is broken), especially since removing it would probably upset the fact 
that this is a "beta" and (in theory) should have its API frozen by 
now.

	-- Matt

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

iD8DBQFDA7rlnLpDzL5I7l8RAquxAJ9ql06M3ltsxd1qt4n9l+b1ctCwGwCfbR8n
dPkP43GHbGubDKC9Bjl65gk=
=FCXL
-----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