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

[Andy Dustman]

On Fri, 12 May 2000, M.-A. Lemburg wrote:

> One addition which I tried in the mxODBC was the addition
> of a .prepare(command) method and a .command read-only 
> attribute. 
> 
> The .prepare() method allows preparing a command for execution
> on the cursor *without* actually executing it. The .command
> attribute is always set to the currently cached command
> on the cursor. Now, to execute a prepared command, all you
> have to do is pass .command to the .execute() method as
> command. Then the re-use optimization will notice that you
> are executing a prepared command and skip the normally
> needed prepare step -- very useful for pooling cursors
> with often used commands such as id-name mappings etc.
> 
> Modules not capabale of providing such a method should
> not implement it and ones which only know at run-time
> should raise a NotSupported error (like for all other
> optional features).

Well, actually, prepare() and command should be mandatory, in fact. For
any database that they don't make sense on (MySQL), prepare() should be
implemented as:

    def prepare(self, command):
        self.command = command

Also trivial to implement in a C interface (this is left as an exorcism
for the reader).

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.

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. The other is MySQL, which is very simple. In fact,
it's too damn simple: All queries are literal. It does no type conversion
at all. (Maybe this is a good thing.) Result sets are either entirely
stored in the client or fetched row-by-row from the server (unless you
want to do multiple queries with LIMIT). Almost nothing does BLOBs very
well (try doing a 1MB BLOB in MySQL; try bigger).

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.

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"