[DB-SIG] Getting schemas and other niceties

Mike C. Fletcher mcfletch at rogers.com
Sat Jul 19 13:37:58 EDT 2003

M.-A. Lemburg wrote:

> Fabien COUTANT wrote:


>> 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.

> Uhm, we already have such an interface: mxODBC provides these
> interfaces for CPython and zxJDBC for Jython. 

Well, technically, *you*, and your clients, of course, have it just now 
;) .  I'm gathering you're offering that definition/doc as a reference 
"spec" for a standardisation effort?

>> 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
>> tools.
> > ...
>>> 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. 
Very similar indeed, basically the same tables getting returned AFAICS.

> Have a look at http://www.egenix.com/files/python/mxODBC.pdf
> for a list of cursor level APIs for DB introspection. ODBC has
> had these for a long time and they have proven to provide
> everything you need for the class of software you describe
> above. zxJDBC provides the same set of APIs on top of JDBC. 

The APIs seem adequate, though it's not clear from that document, for 
instance, how multi-field foreign-key references work (I assume they're 
supposed to create two rows in the foreign-key table).  BTW, all of the 
links to the ODBC docs on MSDN that I tried failed.  Questions:

    What is specialcolumns supposed to do?  It appears to get primary 
keys which may be "special" in some way.
    Why is the index-retrieval method called statistics?  Yes, it 
appears to include statistics, but that seems secondary to me.

I don't currently have any code for describing procedures/functions from 
the database, that would need to be created.  Similarly I don't 
currently pull the type descriptions out of the databases.

> If we add optional DB-API extensions, then I'd suggest to
> simply go with the set defined in the mxODBC docs.

Well, most of the system-catalog query stuff seems doable from 
PostgreSQL and MySQL (it's basically the same information as I'm 
reverse-engineering into an object-based schema description, though the 
mxODBC stuff has a considerable number of fields I don't yet extract).  
Out of curiousity, do people actually use the table-based formats for 
real work?  Or do they just parse the tables to create objects 
describing the schemas?

DB-Catalog-API anyone?


  Mike C. Fletcher
  Designer, VR Plumber, Coder

More information about the DB-SIG mailing list