[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')"
etc.
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.
con.autocommit(1)
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')
sth.execute()
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(...)
cur.execute(q,('T',))
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.
DBI.available_drivers()
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.
DBI.data_sources(driver)
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...)
Driver.threadsafety
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...):
Autocommit
[...]
* 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
document.
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