[DB-SIG] Next Version [was Re: Preparing statement API]

Stuart 'Zen' Bishop zen@cs.rmit.edu.au
Sun, 30 Jan 2000 12:15:48 +1100 (EST)

On Sat, 29 Jan 2000, M.-A. Lemburg wrote:

> While this would help, it is not needed. All you have to do is
> check whether the connection object has the .rollback() method
> and if it does, whether it works:
> def haveTransactions(conn):
>     if not hasattr(conn,'rollback'):
> 	return 0
>     try:
> 	conn.rollback()
>     except:
> 	return 0
>     else:
> 	return 1
> The function has to be called on newly created connection objects
> to not cause unwanted .rollbacks().

I've realized the obvious that faking autocommit mode in a higher level 
abstraction layer will double the number of calls to the RDBMS. I think
there is definitly a need to a conn.autocommit() method for drivers that
support it.

> Such a higher level layer written in Python would indeed be nice.
> It should ideally focus on an OO-style representation of DB
> access similar to the RogueWave OO-layer on top of JDBC... well,IMHO
> anyway ;-)

I was thinking of just building on the existing Driver design.
I feel the design of the driver API would provide an excellent basis
for an abstraction layer. I've experimented with OO access to RDBMS tables,
and found in many cases (the ones I was working on at the time), it creates
more problems than it solves. 

> Perhaps someone could summarize these features in Perl's DBI or
> provide pointers ?! I, for one, don't have any experience with Perl's
> DBI.

I've attached the DBI docs to this email for people who want a read.
Its probably a few months out of date. 
Below is a summary/comparison/general mess.

What PerlDBI has that Python DB doesn't (translated to pseudo-python).
I've ignored the sections that are irrelevant (we thankfully don't have
to worry about Perl's error handling and references):

    con.quote('It's an ex-parrot') == "'It''s an ex-parrot'"
    con.quote(None) == "NULL"
    con.quote(dbi.Date()) == "TO_DATE('1999-04-01 12:53','YYYY-MM-DD HH:MM')"

	This method allows us to dynamically build SQL, without worrying
	about the specifics of our RDBMS quoting. Actually, I don't think
	PerlDBI does the last, but the Python version could as the PyDB
	handles dates. Really useful for when you are building table joins 
	and selection criteria from command line arguments, HTML form input 
	etc. They also allow con.quote(arga,type), but I don't know how 
	useful this would be in python - perhaps some obscure case where 
	python cannot easily cast a datatype but the RDBMS can.

    mode = con.autocommit()

	As discussed above. An interesting point from the docs - 'Drivers 
	should always default to AutoCommit mode. (An unfortunate choice 
	forced on the DBI by ODBC and JDBC conventions.)'. I see no
	reason why we can't do it properly :-)

    cur.prepare('select * from parrots')

	PerlDBI allows you to explicitly prepare your statement. PyDB
	neatly sidesteps this issue, but it is non obvious to people
	who come from a DBI/ODBC/JDBC background. A small snippet
	of code should be added to the PyDB API to demonstrate the reuse:

	q = 'select * from parrots where dead = ?'
	cur = con.Connect(...)
	print cur.fetchall()
	cur.execute(q,('F',))  # Reuses same RDBMS cursor if possible.
	                       # Major performance gain on some RDBMS
	print cur.getchall()
	q2 = 'select * from parrots where %s = ?' % dead
	cur.execute(q2,('T',)) # Note that a new RDBMS cursor will be created
	                       # because a different string instance is passed.
			       # String equality is not enough.
	print cur.fetchall()

    cur.fetch_mapping() = {'COLA': 'A Value','COLB','B Value'}

	Also a fetchall variant. This would live in an abstraction layer,
	not in the driver layer.


	List of available drivers. Leave this for version 2 of the abstraction
	layer, as it would mean it couldn't support PyDB 1 or PyDB 2 drivers.


	List of available data sources. 'Note that many drivers have no way 
	of knowing what data sources might be available for it and thus, 
	typically, return an empty or incomplete list.'. Might be worth
	defining the call for those few drivers that can support it.

    cur.longlength = 2 * 1024 * 1024
    cur.truncok = 1

	Specify the maximum size of a blob returned from the database,
	and if an exception should be raised if the data would need to
	be truncated. Does any interface handle BLOBS nicely? I've never
	used them myself, as they just seem to painful.

Things Python DB has that PerlDBI doesnt:

    Better date handling 

	PerlDBI claims Y2K compliance because it knows nothing about dates
	(although some drivers contain some date handling ability).

    Generic functions for input/output optimization.

	PyDB provides the arraysize() and setXXXsize() methods to allow
	the programmer to specify hints to the RDBMS. These hints are 
	driver specific in PerlDBI.

    Generic function for calling stored procedures.

	PerlDBI requires you to do this though the standard cursor
	interface, which makes it a pain to obtain stored function output.

    cur.nextset() method

	PerlDBI doesn't know about multiple result sets (and neither do
	I for that matter...)


	There is no generic way of interigating a PerlDBI driver for
	thread safety - if you want to write generic DB code, it needs
	to be single threaded.

An interesting bit that I think relates to Digital Creations issues
(I wasn't paying too much attention at the time...):



    * Database in which a transaction must be explicitly started

         For these database the intention is to have them act
         like databases in which a transaction is always active
         (as described above).

         To do this the DBI driver will automatically begin a
         transaction when AutoCommit is turned off (from the
         default on state) and will automatically begin another
         transaction after a the commit entry elsewhere in this
         documentor the rollback entry elsewhere in this

         In this way, the application does not have to treat
         these databases as a special case.

   //     Zen (alias Stuart Bishop)     Work: zen@cs.rmit.edu.au
  // E N  Senior Systems Alchemist      Play: zen@shangri-la.dropbear.id.au
 //__     Computer Science, RMIT 	 WWW: http://www.cs.rmit.edu.au/~zen