A bit [OT]: table definition with rubyDBI

ruby-talk
1

Hi,

I'm very happy with rubyDBI (thanks michael). Especially DBI::Utils::XMLFormatter.table is great.
What i'm looking for is a XMLFormatter for the table definition.
As a first approach :
  DBI::Utils::XMLFormatter.table(dbh.select_all("show fields from counter"))
seems ok to me.
But where are the FOREIGN KEY definitions?
How can i get them out of the database without doing "show create table <tablename>"?
Is there a special DBI command or a special MySQL command?

Many thanks
Ralf

2

Ralf Müller wrote:

Hi,

I'm very happy with rubyDBI (thanks michael). Especially DBI::Utils::XMLFormatter.table is great.
What i'm looking for is a XMLFormatter for the table definition.
As a first approach : DBI::Utils::XMLFormatter.table(dbh.select_all("show fields from counter"))
seems ok to me. But where are the FOREIGN KEY definitions?
How can i get them out of the database without doing "show create table <tablename>"?
Is there a special DBI command or a special MySQL command?

We have the method column_info, but this does not include whether a column is a foreign-key or not (only primary, indexed, nullable etc.).

It's probably very database-dependent to get these informations. If you find out how to do this in Mysql (or other DBs), please let me know. I guess in Postgres it would be a simple select to some internal tables.

Regards,

   Michael

3

Hi Michael,

i posted my question to mysql@lists.mysql.com and the only answers are:
SHOW KEYS FROM <dbname>.<tablename>
and:
show table status from <tablename>

cheers
ralf

···

Am Freitag, 6. August 2004 12:19 schrieb Michael Neumann:

Ralf Müller wrote:
> Hi,
>
> I'm very happy with rubyDBI (thanks michael). Especially
> DBI::Utils::XMLFormatter.table is great. What i'm looking for is a
> XMLFormatter for the table definition. As a first approach :
> DBI::Utils::XMLFormatter.table(dbh.select_all("show fields from
> counter")) seems ok to me.
> But where are the FOREIGN KEY definitions?
> How can i get them out of the database without doing "show create table
> <tablename>"? Is there a special DBI command or a special MySQL command?

We have the method column_info, but this does not include whether a
column is a foreign-key or not (only primary, indexed, nullable etc.).

It's probably very database-dependent to get these informations. If you
find out how to do this in Mysql (or other DBs), please let me know. I
guess in Postgres it would be a simple select to some internal tables.

Regards,

   Michael

4

>But where are the FOREIGN KEY definitions?
>How can i get them out of the database without doing "show create table
><tablename>"?
>Is there a special DBI command or a special MySQL command?

We have the method column_info, but this does not include whether a
column is a foreign-key or not (only primary, indexed, nullable etc.).

Besides, remember that a foreign key constraint is not necessarily bound to
a single column. For example:

create table foo (a varchar(200), b varchar(200), index(a,b)) type=innodb;

Query OK, 0 rows affected (0.01 sec)

create table bar(c varchar(200), d varchar(200), index(c,d), constraint testc foreign key(c,d) references foo(a,b)) type=innodb;

Query OK, 0 rows affected (0.01 sec)

···

On Fri, Aug 06, 2004 at 12:19:03PM +0200, Michael Neumann wrote:

show create table bar;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

bar | CREATE TABLE `bar` (

  `c` varchar(200) default NULL,
  `d` varchar(200) default NULL,
  KEY `c` (`c`,`d`),
  CONSTRAINT `testc` FOREIGN KEY (`c`, `d`) REFERENCES `foo` (`a`, `b`)
) TYPE=InnoDB |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Regards,

Brian.