[DB-SIG] Proposal: model database metadata from JDBC

[brian zimmer]

While mimicing the Java DatabaseMetaData would make things trivial for
me I don't think it makes a very pythonic interface.  I have chosen to
copy mxODBC's extensions (such as .tables, .columns, .procedures, etc)
when implementing my own extensions for zxJDBC.  I have exposed a lot of
the DatabaseMetaData and think a feature such as it is invaluable, but I
wouldn't want to see it copied straight away.  Instead, I'd rather see
the following (as copied from mxODBC and extended by me):

The name in parens is the DMD method name.  It's much easier just to
cross reference with that but I've included a short description
nonetheless.

tables (getTables)
 - a list of all tables

columns (getColumns)
 - a list of all columns for a table

primarykeys (getPrimaryKeys)
 - the primary keys for a table

foreignkeys (getCrossReference)
 - the foreign keys for a table as well as keys imported by other tables

procedures (getProcedures)
 - a list of all procedures

procedurecolumns (getProcedureColumns)
 - description of a procedure's columns

statistics (getIndexInfo)
 - description of a table's indices and statistics

bestrow (getBestRowIdentifier)
 - optimal set of columns that uniquely identifies a row

versioncolumns (getVersionColumns)
 - columns that are automatically updated when any value in a row is
updated

typeinfo (getTypeInfo)
 - a list of all available datatypes

One thing I've found while getting zxJDBC to work across all databases
is the case of the values, in particular table and procedure names, can
be tricky.  It would also be useful therefore to include information
about how a particular database stores table and other such names.

I've also found that not all drivers are created equal and given the
number of methods needed to implement the entire DatabaseMetaData
interface some corners are cut and not all the methods are tested.  I
think it would be best to start with the most useful and add as needed.

Just my $0.02

thanks,

brian

> -----Original Message-----
> From: db-sig-admin@python.org 
> [mailto:db-sig-admin@python.org] On Behalf Of Federico Di Gregorio
> Sent: Sunday, January 13, 2002 6:16 PM
> To: 'db-sig@python.org'
> Subject: Re: [DB-SIG] Proposal: model database metadata from JDBC
> 
> 
> Il lun, 2002-01-14 alle 01:06, Dittmar, Daniel ha scritto:
> > I would like to propose that any metadata facilities for the Python
> > DB API follow the interface specified by JDBC:DatabaseMetadata:
> > http://java.sun.com/j2se/1.4/docs/api/java/sql/DatabaseMetaData.html
> 
> i don't want to start a python vs java flame here, but you 
> seem to suppose that a java api is good by itself (i myself 
> consider java a api-bloated language...) 
> 
> can you please elaborate on "metadata facilities" and why 
> such an interface would be good?
> 
> federico
> 
> -- 
> Federico Di Gregorio
> Debian GNU/Linux Developer & Italian Press Contact        
> fog@debian.org
> INIT.D Developer                                           
> fog@initd.org
>    God is real. Unless declared integer. -- Anonymous FORTRAN 
> programmer
>