[DB-SIG] API Enhancements

M.-A. Lemburg mal@lemburg.com
Mon, 01 Mar 1999 11:23:53 +0100

Greg Stein wrote:
> M.-A. Lemburg wrote:
> >
> > Hi everybody,
> >
> > It's been a long time since we've last had some discussion
> > about a novelation of the API. As you probably remember I have
> > a proposed spec available at:
> >
> >         http://starship.skyport.net/~lemburg/DatabaseAPI-1.1.html
> Is it possible to get this document (or a version of it) red-lined so
> that we can see the differences from the 1.0 document? I can generally
> see the differences, but it would be better to clarify it.
> [ I'd be willing to construct the red-lined version if you'd like
> assistance ]

[Meta: is there a tool available to aid in doing this, e.g. a
HTML aware diff ?]

I would appreciate you helping with it, since I'm pretty much
burried in work.

> > 1. Threading
> >
> > The API spec should make some notes about the scope of thread
> > safety imposed on the interfaces. Thread safety could be given
> > at module level (threads all use the same module, but maintain
> > their own connections), connection level (threads share modules
> > and connections, cursors are not shared between threads) and
> > cursor level (threads share module, connections and cursors).
> >
> > The last level is probably not feasable, but I think connection
> > level could be reached.
> A discussion in the API would be good. I might even say that a module
> could export an attribute that states which level is supported. For
> example, I know that many Win32 ODBC drivers are NOT thread-safe at all.
> You must open a connection per thread.

How about a constant defined as module global:


0 - module level
1 - connection level
2 - cursor level

If it's not available, then module level should be assumed.

> >
> > 2. Stored procedures
> >
> > Basically I want to revisit the discussion. The 1.1 proposal
> > defines this interface:
> >
> > callproc(procname,list_of_parameters)
> Should be a sequence of params, and probably should be optional.
> >               This method is optional since not all databases
> >               provide stored procedures.
> >
> >               Call a stored database procedure with the given
> >               name. The list of parameters must contain one
> >               entry for each argument that the procedure
> >               expects. The result of the call is returned by
> >               modifying the list contents in place. Input
> >               parameters are left untouched, output and
> >               input/output parameters replaced with the new
> >               values.
> This should be clarified that a copy of the params is made, then
> modified. The wording sounds like the method mucks with the argument
> value (which it may not be able to do if the argument is a tuple or
> other sequence).

Right. I'm currently tending towards this interface description:

The sequence of parameters is passed to the stored procedure
as copy. The possibly modified result is returned to the caller.
Ordering of the sequence entries remains the same.
Entries that bind to output variables will be overwritten with
new values.

Well, something along those lines... the basic idea is that
the sequence will look the same on input and output, making it
very easy to grab the output variables after the call is made.

> >               The procedure may also provide a result set as
> >               output. This must then be made available through
> >               the standard fetch-methods.
> >
> > Is this general enough to fit everybody's needs ? I know that
> Nobody has said "no" yet, so why don't we just say it is and be done
> with it. It is certainly a step better than the 1.0 spec.
> > Jim Fulton would rather like an interface which returns a callable
> > type... but it seems overkill to ask module writers to implement
> > this just to be DB API conform.
> The hope is that the modules are easy to code, and then Python stuff is
> layered on top if more functionality is needed. It is "The Python Way"
> :-)
> > 3. A standard catalog interface
> >
> > There is often a need to connect to a database without knowing
> > in advance what tables it contains and how the table columns
> > are named.
> >
> > The API should define a catalog method for this purpose,
> I'd say "no" and leave that to a Python-level module. In many databases,
> you can get this information from special tables. Custom APIs are not
> required. For those databases that require a custom API, then I'd say
> the module should expose those functions as a module-specific extension.
> Creating a "unified" API can then be done at the Python level. Few
> programs, however, require that unified API, so it seems burdensome to
> impose a unified API upon the module writers.

Hmm. This takes us to another project: that of turning database
support for Python into packages... I'm currently building all my
extensions this way: there is usually one C extension and a whole
bunch of Python scripts which build upon its functionality. The user
always accesses the package through the main door :-) and thus does
not see what actually happens at the back end.

Database interfaces could use a similar approach, e.g. code
the catalog interface in Python using the C extension to access
those special tables.

> >...
> > 4. Optional named cursors
> >
> > The cursor constructor should be allowed to have an optional
> > argument for the cursor name. This is useful for UPDATEs.
> I do not understand the use or operation of this, and how it would
> benefit an UPDATE. Could you elaborate?

In ODBC, for example, the ODBC driver uses generated names for
all cursors. Now, it is sometimes useful to be able to refer to
the cursors under known names, e.g. to do UPDATE ... WHERE CURRENT
OF <cursorname>. The optional argument allows this by providing
an explicit name.

Database interfaces that do not support naming cursors should
probably raise an exception in case they receive an argument
to the cursor constructor. Actually, they will if the follow the
1.0 spec closely ;-)

> What should the process be for moving to a new rev of the API? Marc has
> been championing his changes for well over a year now, but closure
> hasn't been reached.
> I would recommend a "Last Call" type of approach. Set the date as (say)
> March 12, receive comments until then, fold in the comments, then issue
> a "Final Call" for the updated version for March 26.
> The DBAPI 1.1 would be posted by Monday the 29th.

Sounds like a reasonable approach. I'll post a note about those
dates later on.

> META-META :-) ... is this approach acceptable? If not, then speak up. If
> nobody has an issue with the approach, then let's assume that we've
> begun the Last Call phase.


Marc-Andre Lemburg                               Y2000: 305 days left
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :