[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
>> 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
> > ...
>>> 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?
Mike C. Fletcher
Designer, VR Plumber, Coder
More information about the DB-SIG