| Subject: | Foreign keys and MySQL 5 |
CDBI::Loader::mysql's _relationships uses the 'Comment' column to
determine foreign keys.
In MySQL 5 this column is trimmed (to 100 chars at my installation), so
sometimes information gets lost. But you can use the new
information_schema database to get foreign key information.
Patch is attached.
| Subject: | mysql.pm.patch |
--- /usr/local/share/perl/5.8.7/Class/DBI/Loader/mysql.pm 2006-02-04 15:07:30.000000000 +0100
+++ mysql.pm 2006-02-08 22:37:42.000000000 +0100
@@ -50,8 +50,28 @@
my $dbname = $conn{database} || $conn{dbname} || $conn{db};
die("Can't figure out the table name automatically.") if !$dbname;
my $quoter = $dbh->get_info(29);
+ my $is_mysql5 = $dbh->get_info(18) =~ /^5./;
foreach my $table (@tables) {
+ if ( $is_mysql5 ) {
+ my $query = qq(
+ SELECT column_name,
+ referenced_table_name
+ FROM information_schema.key_column_usage
+ WHERE referenced_table_name IS NOT NULL
+ AND table_schema = ?
+ AND table_name = ?
+ );
+ my $sth = $dbh->prepare($query)
+ or die("Cannot get table information: $table");
+ $sth->execute($dbname, $table);
+ while ( my $data = $sth->fetchrow_hashref ) {
+ eval { $self->_has_a_many( $table, $data->{column_name}, $data->{referenced_table_name} ) };
+ warn qq/\# has_a_many failed "$@"\n\n/ if $@ && $self->debug;
+ }
+ $sth->finish;
+ next;
+ }
my $query = "SHOW TABLE STATUS FROM $dbname LIKE '$table'";
my $sth = $dbh->prepare($query)
or die("Cannot get table status: $table");