[DB-SIG] Re: [PyGreSQL] Version 3.0 PyGreSQL

[Bill Tutt]

> From: Andy Dustman [mailto:adustman@comstar.net]
> 
> 
> Some general digression/spewing:
> 
> Why prepare()? Because, queries take time to parse. Complex 
> queries take
> longer. In most databases, queries are parsed in the server. 
> If you have a
> commonly used query, one that properly factored out the actual query
> parameters, having a "pre-compiled" query in the server is a 
> big win. Even
> better is if these queries could have some persistence in the 
> server so
> they could be reused by many clients, which may each supply a 
> different
> parameter set.
> 

Well, duh... However, there's no reason you need to expose this concept to
the user in an explicit API.
example implmentation:

# Dictionary mapping query text -> opqaue prepared query reference (sproc
name, underlying db access
# mechanism or something entirely different)
preparedQuery = {}

def execute(self, cmd):
	if not preparedQuery.has_key(cmd):
		preparedQuery[cmd] = conn.prepare(cmd)
	else:
		conn.execute(preparedQuery[cmd])


> I have direct experience with two different native database APIs. The
> first is ODBC, which I think most people would justifiably consider a
> bloated monstrosity. 

ODBC certainly is annoying, but then most flexible APIs into databases from
the C level are. Databases are  complex enough that life starts to suck
bigtime.

OLE DB is better than ODBC by a long shot, but its also a very complex
beast.

[.....]
> 
> But there can be, I think, something closer to an idealized universal
> database interface API. And it will use CORBA.
> 
> I've been thinking about this for awhile now. CORBA can pass all the
> normal string and numeric types via GIOP/IIOPs. BLOBs you 
> can't directly
> pass, but you can create a BLOB within the ORB and then write 
> data to it
> (maybe seek on it like a big file). The data transfer would 
> be transparent
> to the application.
> 
> What would it look like?
> 
> a) Database object: somewhat like a connection object, only 
> it would be a
> unique object that implements the database.
> 
> b) Query object: pre-parsed/compiled query. Create with
> QueryCreate(query). Persistent.
> 
> c) Transaction object: Provides a context for executing 
> queries, i.e. it's
> creation implicitly begins a transaction, there is an explicit
> commit() method, and implicit rollback() upon destruction 
> (and explicit
> rollback()). Not persistent.
> 
> d) Cursor object: Executes queries in the context of a 
> transaction. Would
> have two sets of parameters: a sequence of query parameters, and a
> sequence of sequences of data parameters (for multi-row 
> insertion). Would
> implement various fetching methods. Not persistent.
> 
> e) In a clean-sheet database implementation, columns could contain any
> sort of conceivable CORBA object, thus becoming an object-oriented
> relational database.
> 
> No database that I've ever heard of does this, yet. (Oracle might have
> some CORBA stuff, somewhere.) CORBA interfaces could be 
> constructed for
> existing databases. But, this may not be necessary or practical. The
> Python CORBA bindings are very Python-like. They follow the 
> usual Python
> conventions of package.module.class.method, attributes. So 
> the DB API NG
> (Next Generation) could be designed to operate as the CORBA interface
> would, if it really existed (psuedo-CORBA). Realistically, we 
> are not too
> terribly far from that point now. From a performance 
> perspective, a native
> CORBA database interface ought to be pretty good, certainly 
> no worse than
> ODBC. I am thinking enterprise-level, industrial-strength 
> database here,
> something that would be on a par with Oracle or Informix.
> 
> Comments? If I get inspired, I may try doing something like this as an
> alternate/experimental interface for MySQLdb, as a proof-of-concept.
> 

You really should read the OLE DB specs. They specify COM interfaces for
everything you've specified above and mroe...

SQL 7 is completly built using a superset of these OLE DB interfaces. OLE DB
lets SQL 7 easily perform and optimize joins between different databases. 

Utterly cheesy example:
Joe wants to join table A in an Oracle database against table B in a SQL 7
database.
Depending on the index statistics information (on table B and on table A)
the SQL 7 query optimizer can decide how much of the query can be pushed
into the Oracle database, and how much of it should be done locally.

The OLE DB interfaces exposes all the necessary information that allows SQL
7 do perform this cool task.
(Yes, people really do use this feature in real life.)

Bill