[DB-SIG] Getting schemas and other niceties
mal at lemburg.com
Sun Jul 20 00:09:59 EDT 2003
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.
> 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
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
More information about the DB-SIG