[DB-SIG] Getting schemas and other niceties

Fabien COUTANT fcoutant at freesurf.fr
Sat Jul 19 14:52:04 EDT 2003

> >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.
> Perhaps that's because people usually write application specific
> database abstractions ?!

...and they surely do like this partly because they have specific needs,
and partly because they lack support for some standard thing in the API.

> In real life, you only support n different database backends
> (with n <= 3 in most cases). Writing an application abstraction
> then boils down to writing a class with methods using DB-API calls
> and one defining the SQL to be used for each backend.

I only agree about the end-user class of applications, though I've seen
in most of my work projects an additional layer is not necessary most of
the time (only connect/execute/fetch methods are).

> That's not much work and easier to customize/understand/debug/etc
> than trying to wrap your head around complex overgeneralized
> object-relational database mapping interfaces.

I only said *standard* *SQL* *related* things should be translated to an

OR mappers are a specific thing that depend on target language, object
design and underlying data model, and I fully agree there should be nothing
about them in a DB access API:  they deserve an layer/software on their

Howver the data model is where a DB access API is concerned.  There is a
whole class of software that deserves a schema access API:  OR mappers, DB
designers / reverse-engineering tools, automatic code and web/GUI forms
generator, and there are surely others.

This class of software may be bigger than you think, and we, as application
developers, would have greater choice and benefit if those tools were not
limited each to a few DMBS, but instead worked using a common API.  Why are
there so many Java tools about DB design, OR mapping, code or form
generation ?  Because JDBC has standardized a schema introspection API !
So why not doing the same with Python ?

Such an extension would be optional, written only once in a given driver,
and would benefit a whole set of tools, so this would actually be easier
to do than duplicating/understanding/debugging/etc code in each of those

> "Practicality beats purity."

As a project leader at work, I totally agree with this.  But this does not
apply here: I find absolutely impractical that each meta-level DB tool that
want to supports a DB has to write specific code for it, when the feature
it needs is standard/ubiquitous in the first place.

> W/r to the subject line, I think the best workable approach that
> the industry has come up with is the ODBC approach to schema

Don't know ODBC, only got my hands on JDBC.  I suppose they are similar wrt
schema inspection.

> inspection. But that's really a DB-API extensions (which is only
> needed by a few application types), so does not have a place
> in the specification itself.

I am indeed speaking of extensions, which are needed by a few software
*types*.  But it's worth standardizing the APIs for those extensions, and
the best place is DB-API.  Make them optional, so that current drivers
still conform to the new spec, but at least drivers that want to implement
the extensions will have an API to conform to.

Hope this helps,

More information about the DB-SIG mailing list