[DB-SIG] Getting schemas and other niceties

[Fabien COUTANT]

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

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

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.

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

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

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

[I stop here for now, want to see other's reactions]

-- 
Hope this helps,
Fabien.