[DB-SIG] Getting schemas and other niceties

[M.-A. Lemburg]

Mike C. Fletcher wrote:
> M.-A. Lemburg wrote:
>> 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?

Yes. I'm proposing to go the ODBC way here, because folks have
already put a lot of work into this, so reinventing the wheel
can effectively be prevented :-)

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

Oh well... so they changed the URLs again :-/ MS has the
tendency to change the web-site structure every few months.
It's hard to keep the links up to date.

> Questions:
> 
>    What is specialcolumns supposed to do?  It appears to get primary 
> keys which may be "special" in some way.

These special columns can be used to uniquely identify
a row within the table, i.e. the row id column name or a
primary key column if nothing else is available.

It may even return multiple columns, which you'd then
have to query together in order to identify a row in the
table if the underlying data source does not have the concept
of a row id.

>    Why is the index-retrieval method called statistics?  Yes, it appears 
> to include statistics, but that seems secondary to me.

Ask MS :-)

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

Most of the time, the schema inspection is a one time operation
which then sets up some internal data structure for use in the
abstraction layers.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Jul 19 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2003-07-01: Released mxODBC.Zope.DA for FreeBSD             1.0.6 beta 1