[DB-SIG] Getting schemas and other niceties (was: Result Set Inconsistencies)

Fabien COUTANT fcoutant at freesurf.fr
Fri Jul 18 22:26:42 EDT 2003

Hi Mike, Hi everyone,

On Friday, 18 July 2003, you (Mike C. Fletcher) wrote:
> library is open-sourced, so have to work on it off-hours).  Most of what 
> the system provides at the moment is reverse engineering of database 
> schemas from live databases on MySQL or PostgreSQL (i.e. get databases, 
> tables, fields, constraints and indices), a "thick" API level that 
> provides missing features for given database adapters (e.g. 
> cursor.connection), and a fairly extensive set of objects for modelling 
> schemas (either reverse engineered or generated directly, with 
> SQL-generation support).  It's also got a few convenience elements here 
> and there.
> How many middleware systems do we have?  Is it possible that we need a 
> Database-API on top of/beside DB-API to start reducing all the 
> duplication, or is there really no common set of functions?

I tried about a year ago to make things move on this subject: I suggested
an API extension to obtain and represent schema information.
With no luck.

At that time I explained that more and more tools want to work on the
meta-level, but by lack of standard API each one implements its own schema
getting methods, incompatible between each others and each supporting a
different, limited set of DBMS.
You show this is still true today.

My general idea is that standard things should be usable in standard ways. 
This is what an API specification is for:  it tells a common way to do
standard things, and can let open some doors to implement specific things.
This idea is implemented in Java and makes a big part of its success (this
is also why Sun outputs so many API specifications, as new domains emerge).

This idea can be declined on several subjects in our DBAPI context:
- all DBMS communicate on behalf of a connection, so we have connection
- there is a SQL standard, so there is a method to execute SQL commands and
    retrieve results.
- all DBMS allow schema introspection, so we should set an API to do it.
    (that's what I had tried)
- SQL enforces quoting rules, so we should have a standard-quoting method.
    Unfortunately, there are some type-specific rules and types here and
    there, so the quoting method should attach to a particular DB driver /
    connection.  This is actually implemented by the execute* cursor
    methods; Some problems still reside in unclear parameter passing/typing
    aspects (more in list archives for the past few months, I don't want to
    re-open the debate here)
- all DMBS have some form of URL string as single connection argument
    (think of JDBC's jdbc:<driver>:...  URL scheme); Additionally the same
    parts are more or less always encountered in these URLs:  host, port,
    base, schema, user and password.  Maybe we should set a common syntax
    for the connect method argument(s) across all drivers...
- SQL describes a minimal common set of types, so we should set a common
    naming for type names/objects in column types of result sets and
Etc... (anyone wants to add ?)

All this would probably imply a new (major?) revision of the DBAPI spec,
and would in turn imply updating DB drivers.  But not necessarily, if the
biggest changes are made optional.  As always, some people (like me) are
expecting changes, and some don't.  As you saw, nothing moved up to now ;-)

I also post this to test people's feeling after a year and several debates.
Is is time for DBAPI3 (or should it be 2.5) ?
Personnaly I think DBAPI2 is not enough as it is (as you guessed :), as it
lacks standardization in several common domains.

NB: for your result-set row type problem, I think specifying a "sequence"
in DBAPI is enough.  For your comparison you should try to convert the searched
element to the row type like this:
    search = ["foo", "bar"]
    rs = cursor.execute('...some SQL...')
    # convert here, if there is at least one row to test its type
    if len(rs) > 0 :
	rowSeqType = type(rs[0])
	search = rowSeqType(search)
	# might be tricky (who says doesn't work ?!) on classes that don't
	# have the right constructor
    if search in rs :
or convert the result set rows:
    rs = map(type(search), rs)

Hope this helps,

More information about the DB-SIG mailing list