[Zope-DB] Accessing the MySQL data dictionary

[Charlie Clark]

On 2005-06-02 at 18:49:59 [+0200], Ken Winter <ken at sunward.org> wrote:
> 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:

Hehe, MySQL actually implementing an SQL standard instead of trying to take 
credit for not doing it.

> 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?

To be honest I don't think table descriptions are standard enough for it to 
be possible to create a standard parser for them. However, you might want to 
look at the MySQLdb driver and how this might be used or extended to provide 
you with the information using the appropriate functions. Hint: look at the 
Browse function of your ZopeDA. In any case you're probably better taking 
the discussion to MySQLdb list.

Charlie