[DB-SIG] Proposal: Standardized way to get database schema

Fabien COUTANT fcoutant@freesurf.fr
Mon, 16 Sep 2002 18:59:15 +0200

On Monday, 16 September 2002, you (M.-A. Lemburg) wrote:
> The DB API is available as PEP 249. If sending in patches,
> please use the text form of that PEP.

Thanks.  Please forgive me for my ignorance of the PEP procedures !
I will prepare a patch and submit it to you...  if you are still
maintaining it ?

> There's a problem with your approach: many databases
> store the schema information in (system) database tables
> and to get at these you'd have to run actual queries against
> the database connection. This usually requires having
> a cursor setup to perform the tasks, so it would be
> more natural to expose the various methods on cursor
> objects rather than to create a new kind of hybrid
> object for schemas.

Exposing catalog methods on cursor objects doesn't seem natural to me.
IMO only a database connection should be required:

- It's the driver's job to get the information from that.  If this
  internally implies using SQL requests, then this detail should be hidden
  to the API user.

- You're right on the point that many databases can use SQL queries to get
  the schema, however I'm sure there are others that don't (Gadfly? 

I don't see why we should enforce something that is not necessary, and
which could prevent writing such API in some drivers that don't work as you
expect.  Again the database connection should be enough to retrieve all
dependent information (but I may be wrong).

> Another aspect to consider is that databases tend to
> provide much more information than exposed in your
> proposal.

Yes.  That was just a proposal :)
In fact I intended to provide access methods to all Standard SQL attributes
of tables, columns and indices.

If access to DB specific information is to be provided (which I'm not sure
we should do), I would like it to be done through specific methods, so that 
the API remains the simplest possible, and does not try to support most
exotic features from all possible databases !

> I'd suggest to use the ODBC catalog methods as basis
> since these are in wide use and have proven their
> ability to capture enough information to be of good
> use to the programmer.

I don't know about ODBC, except that it comes from Bill's world and has a
reputation of complexity and slowness.

Also I wouldn't like the API to be burdened with lots of MS-specific
things.  Let's start from Standard SQL; there's enough for it to be useful.

> BTW, instead of making the API more complicated, we
> could also have a support module which then supports
> various backends, much like the dbinfo.py module I
> started out with some years ago and which has made its
> way into mxODBC as mx.ODBC.Misc.dbinfo (this module aims
> at translating abstract schemas into specific database ones
> to support cross-database schema creation).

Interesting idea.  What I'm afraid of, is that it won't encourage writing
the necessary support in the driver.  But again, this supposes that catalog
information is available through SQL queries, hence some databases may not
be handled this way.

I would use such a solution as a proof of concept to wrap actual drivers,
and provide the wanted new methods, until they are integrated in each
driver.  This is also a safe alternative for databases which use SQL-based
catalog access.

There have not been much discussion on this subject past weeks.  I'm sad to
see nearly nobody seems interested.

Hope this helps,