[DB-SIG] table metadata available?

[Andy Todd]

Patrick K. O'Brien wrote:
> [Danny Yoo]
> 
>>I hope I'm not bothering too much!  I'm starting to work on some tools
>>that try to probe databases a bit dynamically.  Is there an API to pull
>>the table definitions?  In particular, it'd be nice to be able to discover
>>the primary keys of each table.  I can't see a way of doing this reliably
>>with just the DB 2.0 API, but perhaps I'm just missing something obvious.
> 
> 
> Hey, Danny. You should ask Andy Todd. I know he's done some things along
> these lines. I've copied this message to him. Andy? Got any advice?
> 
> ---
> Patrick K. O'Brien
> Orbtech
> 
> 
> 

Thanks Pat, the simple answer is that there isn't a common definition 
for relational databases and the DB-API doesn't try to provide 
generalised metadata.

Anthony Baxter in his post is absolutely correct, every database stores 
their metadata slightly differently and writing cross product code is a 
little bit tricky.

As a first step I would suggest that you look at the dbdoc code 
(http://dbdoc.sourceforge.net).

The approach that Steve Purcell took for dbdoc is to write a database 
specific wrapper module that provides data to the calling module in a 
standard format. dbdoc currently supports Oracle and PostgreSQL, I have 
a half finished MySQL module but I would need to dust it off before it 
would be any good for you.

There is a similar (but not nearly as elegant) approach in the dbBrowser 
sample for PythonCard (http://pythoncard.sourceforge.net). Hardly 
suprising since I wrote it. This supports Oracle and MySQL.

Have a look at the code for these modules and if you have any specific 
questions, or you are not using Oracle, PostgreSQL or MySQL then post a 
specific question here and we will see if we can help you out.

Regards,
Andy
-- 
----------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com