[Zope-DB] Accessing the MySQL data dictionary

[Ken Winter]

> -----Original Message-----
> From: Chris Withers [mailto:chris at simplistix.co.uk]
> Sent: Wednesday, June 01, 2005 3:54 PM
> To: ken at sunward.org
> Cc: zope-db at zope.org
> Subject: Re: [Zope-DB] Accessing the MySQL data dictionary
> 
> Ken Winter wrote:
> > - Thanks for your help or your reference
> 
> That sounded like a question probably better directed to a MySQL list...
> 
Right you are.  

Here's what I've learned: MySQL 5.0.2 supports something called
INFORMATION_SCHEMA (a SQL '99 standard that few DBMS makers have
implemented) - see
http://dev.mysql.com/doc/mysql/en/information-schema.html.  It lets you do
queries such as:

select * from information_schema.table_constraints where
constraint_type='foreign key'

which is exactly what I need.  Alas, my Zope host (zettai.net) only has
MySQL 4.x at present, so the best available is to do a "show create table"
query, and then parse the DDL that it returns into my own "catalog" tables.

Next question:  Does anybody know of a callable SQL parser (that can handle
'create table' DDL statements) available out there in Zope-land or
Python-land?

- Thanks, Ken