Author: jimw
Date: Tue Feb 27 17:24:15 2007
New Revision: 9176
Added:
DBD-mysql/trunk/t/40catalog.t
Modified:
DBD-mysql/trunk/ChangeLog
DBD-mysql/trunk/lib/DBD/mysql.pm
Log:
Add implementation of foreign_key_info(), based on an initial implementation
by Dave Rolsky and the code to implement similar functionalit in Connector/J.
Requires versions of MySQL after 5.0.6, since the implementation makes use of
INFORMATION_SCHEMA.
Modified: DBD-mysql/trunk/ChangeLog
==============================================================================
--- DBD-mysql/trunk/ChangeLog (original)
+++ DBD-mysql/trunk/ChangeLog Tue Feb 27 17:24:15 2007
@@ -1,4 +1,8 @@
-<unreleased> Jim Winstead <jimw@stripped> Patrick Galbraith
<patg@stripped> (4.001)
+<unreleased> Jim Winstead <jimw@stripped> (4.002)
+* Add implementation of foreign_key_info() (Bug #26604, original patch from
+ Dave Rolsky, and final implementation based on Connector/J code)
+
+2007-1-8 Jim Winstead <jimw@stripped> Patrick Galbraith <patg@stripped>
(4.001)
* Fix handling of unsigned integer values in result sets when using
server-side prepared statements (they were not retrieved at all).
* Fix handling of signed integer values when using server-side prepared
Modified: DBD-mysql/trunk/lib/DBD/mysql.pm
==============================================================================
--- DBD-mysql/trunk/lib/DBD/mysql.pm (original)
+++ DBD-mysql/trunk/lib/DBD/mysql.pm Tue Feb 27 17:24:15 2007
@@ -98,6 +98,7 @@
package DBD::mysql::dr; # ====== DRIVER ======
use strict;
use DBI qw(:sql_types);
+use DBI::Const::GetInfoType;
sub connect {
my($drh, $dsn, $username, $password, $attrhash) = @_;
@@ -473,6 +474,103 @@
}
+sub foreign_key_info {
+ my ($dbh,
+ $pk_catalog, $pk_schema, $pk_table,
+ $fk_catalog, $fk_schema, $fk_table,
+ ) = @_;
+
+ # INFORMATION_SCHEMA.KEY_COLUMN_USAGE was added in 5.0.6
+ my ($maj, $min, $point) = _version($dbh);
+ return if $maj < 5 || ($maj == 5 && $point < 6);
+
+ my @names = qw(
+ UK_TABLE_CAT UK_TABLE_SCHEM UK_TABLE_NAME UK_COLUMN_NAME
+ FK_TABLE_CAT FK_TABLE_SCHEM FK_TABLE_NAME FK_COLUMN_NAME
+ ORDINAL_POSITION DELETE_RULE FK_NAME UK_NAME DEFERABILITY
+ UNIQUE_OR_PRIMARY
+ );
+
+ my $sql = <<'EOF';
+SELECT NULL AS PKTABLE_CAT,
+ A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
+ A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
+ A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
+ A.TABLE_CATALOG AS FKTABLE_CAT,
+ A.TABLE_SCHEMA AS FKTABLE_SCHEM,
+ A.TABLE_NAME AS FKTABLE_NAME,
+ A.COLUMN_NAME AS FKCOLUMN_NAME,
+ A.ORDINAL_POSITION AS KEY_SEQ,
+ NULL AS UPDATE_RULE,
+ NULL AS DELETE_RULE,
+ A.CONSTRAINT_NAME AS FK_NAME,
+ NULL AS PK_NAME,
+ NULL AS DEFERABILITY,
+ NULL AS UNIQUE_OR_PRIMARY
+ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,
+ INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
+ WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
+ AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
+EOF
+
+ my @where;
+ my @bind;
+
+ # catalogs are not yet supported by MySQL
+
+# if (defined $pk_catalog) {
+# push @where, 'A.REFERENCED_TABLE_CATALOG = ?';
+# push @bind, $pk_catalog;
+# }
+
+ if (defined $pk_schema) {
+ push @where, 'A.REFERENCED_TABLE_SCHEMA = ?';
+ push @bind, $pk_schema;
+ }
+
+ if (defined $pk_table) {
+ push @where, 'A.REFERENCED_TABLE_NAME = ?';
+ push @bind, $pk_table;
+ }
+
+# if (defined $fk_catalog) {
+# push @where, 'A.TABLE_CATALOG = ?';
+# push @bind, $fk_schema;
+# }
+
+ if (defined $fk_schema) {
+ push @where, 'A.TABLE_SCHEMA = ?';
+ push @bind, $fk_schema;
+ }
+
+ if (defined $fk_table) {
+ push @where, 'A.TABLE_NAME = ?';
+ push @bind, $fk_table;
+ }
+
+ if (@where) {
+ $sql .= ' AND ';
+ $sql .= join ' AND ', @where;
+ }
+ $sql .= " ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
+
+ local $dbh->{FetchHashKeyName} = 'NAME_uc';
+ my $sth = $dbh->prepare($sql);
+ $sth->execute(@bind);
+
+ return $sth;
+}
+
+
+sub _version {
+ my $dbh = shift;
+
+ return
+ $dbh->get_info($DBI::Const::GetInfoType::GetInfoType{SQL_DBMS_VER})
+ =~ /(\d+)\.(\d+)\.(\d+)/;
+}
+
+
####################
# get_info()
# Generated by DBI::DBD::Metadata
Added: DBD-mysql/trunk/t/40catalog.t
==============================================================================
--- (empty file)
+++ DBD-mysql/trunk/t/40catalog.t Tue Feb 27 17:24:15 2007
@@ -0,0 +1,83 @@
+#!perl -w
+# vim: ft=perl
+
+use Test::More;
+use DBI;
+use strict;
+$|= 1;
+
+my $mdriver= "";
+
+our ($test_dsn, $test_user, $test_password);
+foreach my $file ("lib.pl", "t/lib.pl") {
+ do $file;
+ if ($@) {
+ print STDERR "Error while executing $file: $@\n";
+ exit 10;
+ }
+ last if $mdriver ne '';
+}
+
+my $dbh= DBI->connect($test_dsn, $test_user, $test_password,
+ { RaiseError => 1, PrintError => 1, AutoCommit => 0 });
+
+plan tests => 17;
+
+ok(defined $dbh, "connecting");
+
+my $sth;
+
+my ($version)= $dbh->selectrow_array("SELECT version()")
+ or DbiError($dbh->err, $dbh->errstr);
+
+#
+# Bug #26604: foreign_key_info() implementation
+#
+# The tests for this are adapted from the Connector/J test suite.
+#
+SKIP: {
+ skip "Server is too old to support INFORMATION_SCHEMA for foreign keys", 16
+ if substr($version, 0, 1) < 5;
+
+ my ($have_innodb)= $dbh->selectrow_array("SELECT \@\@have_innodb = 'YES'")
+ or DbiError($dbh->err, $dbh->errstr);
+ skip "Server doesn't support InnoDB, needed for testing foreign keys", 16
+ if not $have_innodb;
+
+ ok($dbh->do(qq{DROP TABLE IF EXISTS child, parent}), "cleaning up");
+
+ ok($dbh->do(qq{CREATE TABLE parent(id INT NOT NULL,
+ PRIMARY KEY (id)) ENGINE=INNODB}));
+ ok($dbh->do(qq{CREATE TABLE child(id INT, parent_id INT,
+ FOREIGN KEY (parent_id)
+ REFERENCES parent(id) ON DELETE SET NULL)
+ ENGINE=INNODB}));
+
+ $sth= $dbh->foreign_key_info(undef, undef, "parent", undef, undef, "child");
+ my ($info)= $sth->fetchall_arrayref({});
+
+ is($info->[0]->{PKTABLE_NAME}, "parent");
+ is($info->[0]->{PKCOLUMN_NAME}, "id");
+ is($info->[0]->{FKTABLE_NAME}, "child");
+ is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
+
+ $sth= $dbh->foreign_key_info(undef, undef, "parent", undef, undef, undef);
+ ($info)= $sth->fetchall_arrayref({});
+
+ is($info->[0]->{PKTABLE_NAME}, "parent");
+ is($info->[0]->{PKCOLUMN_NAME}, "id");
+ is($info->[0]->{FKTABLE_NAME}, "child");
+ is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
+
+ $sth= $dbh->foreign_key_info(undef, undef, undef, undef, undef, "child");
+ ($info)= $sth->fetchall_arrayref({});
+
+ is($info->[0]->{PKTABLE_NAME}, "parent");
+ is($info->[0]->{PKCOLUMN_NAME}, "id");
+ is($info->[0]->{FKTABLE_NAME}, "child");
+ is($info->[0]->{FKCOLUMN_NAME}, "parent_id");
+
+ ok($dbh->do(qq{DROP TABLE IF EXISTS child, parent}), "cleaning up");
+};
+
+$dbh->disconnect();
| Thread |
|---|
| • [svn:DBD-mysql] r9176 - in DBD-mysql/trunk: . lib/DBD t | jimw | 28 Feb |
