[DB-SIG] Metadata interface - was Re: Cursor.description when Cursor.rowcount == 0

[Chris Clark]

Art Protin wrote:
> Dear folks,
>     Carsten Haese wrote:
>> On Fri, 2006-11-10 at 19:03 +0100, M.-A. Lemburg wrote:
>>   
>>> It is common practice to do
>>>
>>> cursor.execute('select * from mytable where 1=0')
>>> print cursor.description
>>>     
> The DBMS that I am writing the interface for has other (more
> efficient) ways of producing this meta data.  Don't the other DBMSs ? 
> Should the spec include a method (or more) to report on tables in the
> DB and on columns in a table?  Is there a common extension that should
> be codified?

I would vote +1 on method(s) for meta data access being added to the
(next) DBI spec rather than a new extension. Practically every DBMS
offer meta data query access but they all differ in approaches; some
have api calls most have meta data tables/views that can be queried
using SQL. Adding methods to the spec would allow mapping to host api
methods OR meta data tables depending on what the DBMS offers.

As for what the new api could be, it depends on what we expect the
information to be used for. Do we want to know the Python type or the
SQL type? If we want to know the SQL type; should the type information
be native or portable - e.g. assume we are looking at a column in Oracle
of type NUMBER(10,3), should that be reported as NUMBER(10,3) or should
it be reported as DECIMAL(10,3)? If you just want to deal with Python
stuff then Python type information should be fine, if you want to
perform or create DDL statements from that information you really want
the host type.

What do people want/need or do people want both types of information?
Some DBI drivers already implement meta data query functionality (some
ORMs do too), do we want to base the spec on existing implementations?

We probably want avoid ripping-off ^h^h^h^h^h^h^h borrowing the jdbc or
odbc metadata interface as they are rather verbose and un-pythonic but
they would be a good place to start for requirements (for example the
meta data information in jdbc is not restricted to just table
information; e.g. supportsSubqueriesInIns()).

Chris