[DB-SIG] Getting schemas and other niceties

[M.-A. Lemburg]

Fabien COUTANT wrote:
> On Saturday, 19 July 2003, you (M.-A. Lemburg) wrote:
> [...]
> 
>>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.
> 
> I have just finished reading it.  Interesting reading.  I don't agree on
> everything written in it, but that's another story.
> 
> A few remarks:
> - I read that mxODBC is not 100% DB-API compliant, as some meta-information
>     (column sizes) are not available; I consider this is a failure of ODBC
>     (not your package) since the info is present and fetchable from
>     databases.  In JDBC column sizes *are* available.

mxODBC 2.0 is 100% DB-API compliant. If you read the spec carefully,
you'll find that things like column sizes (which were really only
useful in the days of mainfraime text based UIs) are optional.

Rather than spending time to query this information from the
database for each and every query, I chose to leave it to the
user to call the appropriate catalog methods instead. In practice
you'll also find that the information from the catalog methods
is more realiable than the data available at query time.

> - your doc is not complete by itself, because it refers to MS's ODBC
>     docs where constants and meta-info are used or returned ("getinfo"
>     connection's method).

True, people using these methods are usually experts and don't
have trouble looking up the data in the MS docs. After all, we
didn't want to reauthor the entire ODBC spec :-)

>>If we add optional DB-API extensions, then I'd suggest to
>>simply go with the set defined in the mxODBC docs.
> 
> As I understand you *don't* suggest that db-api is useless and we should
> all use mxODBC, but rather that we should mimic mxODBC's introspecting API
> into DB-API.

Both actually :-)

No, seriously, I'm only suggesting that if we consider adding
methods for introspection, then we should follow the existing
sets in mxODBC and zxJDBC.

> Why not...  I like the idea of your cursor's "catalog" methods.  I
> synthetize for others:
> - connection objects have some meta-data as r/o attributes:  DBMS name and
>     version, driver name and version (plus others more specific to ODBC)
> - cursor objects have a set of methods that return result sets (i.e.
>     sequences of sequences in DB-API interpretation) for meta-information
>     about DB structure and access rights (I intentionally omit arguments
>     for clarity):
>     - tables()
>     - tableprivileges()
>     - columns()
>     - columnprivileges()
>     - primarykeys()
>     - foreignkeys()
>     - procedures()
>     - procedurecolumns()
> 
> I'm ok for the concept, but I see a few things that should be taken into
> account to accomplish integration into DB-API:
> 
> - ODBC, its API and your document are copyrighted material (respectively by
>     MS and egenix), so we must invent DB-API's own naming and
>     representation of meta-data.

I'm the editor of the DB API spec and don't have a problem with
putting some of our docs in the public domain.

> - SQL-level representation should be returned (such as the already declared
>     type codes used in cursors description attribute) instead of byte
>     streams or DB/ODBC specific codes.
>     This does not prevent specific types to be added, I just mean standard
>     "codes" must be used for standard types.

No problem with that as long as we define name-based codes rather
than hard-code the values into the spec.

> - columns in result sets corresponding to features of standard SQL (column
>     name, type, size, unique, nullable, ...) should be made first and
>     mandatory (but would allow for None values in some specified columns,
>     as a provision for DBMS that don't support the feature).  We have to
>     carefully select the columns that fall into this category.

I'd rather not change the layout of the result sets. Adding new
columns is OK though (ODBC allows this too).

> - columns returned in such result sets would not be bound by the
>     specification, but could be extended to include other driver/DB
>     specific infos, as long as mandatory infos are here in the first
>     columns.

Right.

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