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

[Andy Todd]

Whilst it is not the purpose of the application, perhaps you should look 
at dbdoc (http://dbdoc.sourceforge.net/).

This implements a standard API which is then used to query the metadata 
in a database and produce html documentation from it. I mention it 
because the API has some similarities with the DMD methods mentioned below.

Experience also tells me that getting a common metadata set is very 
difficult. dbdoc works with Oracle and PostgreSQL and just implementing 
those two engines has involved a lot of compromise. I've started porting 
the API to MySQL and that is just a nightmare.

I'm not trying to be negative though, just realistic. I'm interested to 
see which RDBMS the java interface supports. That would provide a 
guideline of the possible, rather than the ideal.

brian zimmer wrote:

> 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
>>
>>
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
> 
> 
> 

Regards,
Andy
-- 
-----------------------------------------------------------------------
 From the desk of Andrew J Todd esq.
"Another year older, still no wiser." - Me, on my birthday