Author: jimw
Date: Thu Mar 1 13:13:52 2007
New Revision: 9188
Modified:
DBD-mysql/trunk/ChangeLog
DBD-mysql/trunk/lib/DBD/mysql.pm
DBD-mysql/trunk/t/40catalog.t
Log:
Rewrite table_info method to support all arguments (previously it would only
ever return all of the tables in the current database, no matter what was
specified).
Includes a number of tests which should be fairly comprehensive.
Tested with both 5.1 and 4.1 servers. (There's nothing here that should behave
any different between 5.0 and 5.1.)
Modified: DBD-mysql/trunk/ChangeLog
==============================================================================
--- DBD-mysql/trunk/ChangeLog (original)
+++ DBD-mysql/trunk/ChangeLog Thu Mar 1 13:13:52 2007
@@ -1,15 +1,21 @@
2007-02-24 Patrick Galbraith <patg@stripped> Jim Winstead <jimw@stripped>
(4.002)
-* Fixed version to be a string, which was previously a float, which caused problems
- for certain locales
-* Fixed bug #23974. $dbh->column_info now returns empty arrayref upon table not
existing.
- Much thanks to Tim Bunce for help fixing the probelem in mysql.pm vs. dbdimp.c
-* Removed #ifdefs for do error (sqlstate being passed as last arg depending on version)
+* Rewrote table_info method to support all arguments (previously it would
+ only ever return all of the tables in the current database, no matter what
+ was specified)
+* Fixed version to be a string, which was previously a float, which caused
+ problems for certain locales
+* Fixed bug #23974. $dbh->column_info now returns empty arrayref upon table not
+ existing. Much thanks to Tim Bunce for help fixing the problem in mysql.pm
+ vs. dbdimp.c
+* Removed #ifdefs for do error (sqlstate being passed as last arg depending on
+ version)
* Fixed insertid test to work with auto_increment_increment replication setup.
* Patch from Tim Bunce fixing do() not set $dbh->{Statement} attribute,
- which prevented DBD::Profile from giving correct results for calls to do() and
- causing ShowErrorStatement to possibly report the wrong statement in the error message
-* Patch from Tim Bunce clearing out the sth attribute cache when switching between
result,
- sets which prevented the adjustedment of NUM_OF_FIELDS
+ which prevented DBD::Profile from giving correct results for calls to do()
+ and causing ShowErrorStatement to possibly report the wrong statement in the
+ error message
+* Patch from Tim Bunce clearing out the sth attribute cache when switching
+ between result, sets which prevented the adjustedment of NUM_OF_FIELDS
* Cleanup of several unused variables
* Added support for wildcards in last argument of column_info().
* Add mysql_is_auto_increment to results of column_info(). (Bug #26603,
Modified: DBD-mysql/trunk/lib/DBD/mysql.pm
==============================================================================
--- DBD-mysql/trunk/lib/DBD/mysql.pm (original)
+++ DBD-mysql/trunk/lib/DBD/mysql.pm Thu Mar 1 13:13:52 2007
@@ -253,40 +253,113 @@
die "_SelectDB is removed from this module; use DBI->connect instead.";
}
-{
- my $names = ['TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME',
- 'TABLE_TYPE', 'REMARKS'];
-
- sub table_info ($) {
- my $dbh = shift;
- my $sth = $dbh->prepare("SHOW /*!50002 FULL*/ TABLES");
- return undef unless $sth;
- if (!$sth->execute()) {
- return DBI::set_err($dbh, $sth->err(), $sth->errstr());
- }
- my @tables;
+sub table_info ($) {
+ my ($dbh, $catalog, $schema, $table, $type, $attr) = @_;
+ my @names = qw(TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS);
+ my @rows;
+
+ my $sponge = DBI->connect("DBI:Sponge:", '','')
+ or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
+
+ # Return the list of catalogs
+ if (defined $catalog && $catalog eq "%" &&
+ (!defined($schema) || $schema eq "") &&
+ (!defined($table) || $table eq ""))
+ {
+ @rows = (); # Empty, because MySQL doesn't support catalogs (yet)
+ }
+
+ # Return the list of schemas
+ elsif (defined $schema && $schema eq "%" &&
+ (!defined($catalog) || $catalog eq "") &&
+ (!defined($table) || $table eq ""))
+ {
+ my $sth = $dbh->prepare("SHOW DATABASES")
+ or return undef;
+
+ $sth->execute()
+ or return DBI::set_err($dbh, $sth->err(), $sth->errstr());
+
while (my $ref = $sth->fetchrow_arrayref()) {
- my $type = (defined $ref->[1] &&
- $ref->[1] =~ /view/i) ? 'VIEW' : 'TABLE';
- push(@tables, [ undef, undef, $ref->[0], $type, undef ]);
- }
- my $dbh2;
- if (!($dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'})) {
- $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} =
- DBI->connect("DBI:Sponge:");
- if (!$dbh2) {
- DBI::set_err($dbh, 1, $DBI::errstr);
- return undef;
+ push(@rows, [ undef, $ref->[0], undef, undef, undef ]);
+ }
+ }
+
+ # Return the list of table types
+ elsif (defined $type && $type eq "%" &&
+ (!defined($catalog) || $catalog eq "") &&
+ (!defined($schema) || $schema eq "") &&
+ (!defined($table) || $table eq ""))
+ {
+ @rows = (
+ [ undef, undef, undef, "TABLE", undef ],
+ [ undef, undef, undef, "VIEW", undef ],
+ );
+ }
+
+ # Special case: a catalog other than undef, "", or "%"
+ elsif (defined $catalog && $catalog ne "" && $catalog ne "%") {
+ @rows = (); # Nothing, because MySQL doesn't support catalogs yet.
+ }
+
+ # Uh oh, we actually have a meaty table_info call. Work is required!
+ else {
+ my @schemas;
+
+ # If no table was specified, we want them all
+ $table ||= "%";
+
+ # If something was given for the schema, we need to expand it to
+ # a list of schemas, since it may be a wildcard.
+ if (defined $schema && $schema ne "") {
+ my $sth = $dbh->prepare("SHOW DATABASES LIKE " .
+ $dbh->quote($schema))
+ or return undef;
+ $sth->execute()
+ or return DBI::set_err($dbh, $sth->err(), $sth->errstr());
+
+ while (my $ref = $sth->fetchrow_arrayref()) {
+ push @schemas, $ref->[0];
}
+ # Otherwise we want the current database
+ } else {
+ push @schemas, $dbh->selectrow_array("SELECT DATABASE()");
}
- my $sth2 = $dbh2->prepare("SHOW TABLES", { 'rows' => \@tables,
- 'NAME' => $names,
- 'NUM_OF_FIELDS' => 5 });
- if (!$sth2) {
- DBI::set_err($sth2, $dbh2->err(), $dbh2->errstr());
+
+ # Figure out which table types are desired
+ my ($want_tables, $want_views);
+ if (defined $type && $type ne "") {
+ $want_tables = ($type =~ m/table/i);
+ $want_views = ($type =~ m/view/i);
+ } else {
+ $want_tables = $want_views = 1;
+ }
+
+ foreach my $database (@schemas) {
+ my $sth = $dbh->prepare("SHOW /*!50002 FULL*/ TABLES FROM " .
+ $dbh->quote_identifier($database) .
+ " LIKE " . $dbh->quote($table))
+ or return undef;
+ $sth->execute() or
+ return DBI::set_err($dbh, $sth->err(), $sth->errstr());
+
+ while (my $ref = $sth->fetchrow_arrayref()) {
+ my $type = (defined $ref->[1] &&
+ $ref->[1] =~ /view/i) ? 'VIEW' : 'TABLE';
+ next if $type eq 'TABLE' && not $want_tables;
+ next if $type eq 'VIEW' && not $want_views;
+ push @rows, [ undef, $database, $ref->[0], $type, undef ];
+ }
}
- $sth2;
}
+
+ my $sth = $sponge->prepare("table_info", {
+ rows => \@rows,
+ NUM_OF_FIELDS => scalar @names,
+ NAME => \@names,
+ }) or return $dbh->DBI::set_err($sponge->err(), $sponge->errstr());
+
+ return $sth;
}
sub _ListTables {
Modified: DBD-mysql/trunk/t/40catalog.t
==============================================================================
--- DBD-mysql/trunk/t/40catalog.t (original)
+++ DBD-mysql/trunk/t/40catalog.t Thu Mar 1 13:13:52 2007
@@ -21,7 +21,7 @@
my $dbh= DBI->connect($test_dsn, $test_user, $test_password,
{ RaiseError => 1, PrintError => 1, AutoCommit => 0 });
-plan tests => 32;
+plan tests => 77;
ok(defined $dbh, "connecting");
@@ -82,12 +82,118 @@
};
#
-# Bug #26603: (one part) support views in table_info()
+# table_info() tests
+#
+# These tests assume that no other tables name like 't_dbd_mysql_%' exist on
+# the server we are using for testing.
+#
+SKIP: {
+ my $sth = $dbh->table_info("%", undef, undef, undef);
+ is(scalar @{$sth->fetchall_arrayref()}, 0, "No catalogs expected");
+
+ $sth = $dbh->table_info(undef, "%", undef, undef);
+ ok(scalar @{$sth->fetchall_arrayref()} > 0, "Some schemas expected");
+
+ $sth = $dbh->table_info(undef, undef, undef, "%");
+ ok(scalar @{$sth->fetchall_arrayref()} > 0, "Some table types expected");
+
+ ok($dbh->do(qq{DROP TABLE IF EXISTS t_dbd_mysql_t1, t_dbd_mysql_t11,
+ t_dbd_mysql_t2, t_dbd_mysqlat2,
+ `t_dbd_mysql_a'b`,
+ `t_dbd_mysql_a``b`}),
+ "cleaning up");
+ ok($dbh->do(qq{CREATE TABLE t_dbd_mysql_t1 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysql_t11 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysql_t2 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysqlat2 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a'b` (a INT)}) and
+ $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a``b` (a INT)}),
+ "creating test tables");
+
+ # $base is our base table name, with the _ escaped to avoid extra matches
+ my $esc = $dbh->get_info(14); # SQL_SEARCH_PATTERN_ESCAPE
+ (my $base = "t_dbd_mysql_") =~ s/([_%])/$esc$1/g;
+
+ # Test fetching info on a single table
+ $sth = $dbh->table_info(undef, undef, $base . "t1", undef);
+ my $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_CAT}, undef);
+ is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t1");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 1, "one row expected");
+
+ # Test fetching info on a wildcard
+ $sth = $dbh->table_info(undef, undef, $base . "t1%", undef);
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_CAT}, undef);
+ is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t1");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is($info->[1]->{TABLE_CAT}, undef);
+ is($info->[1]->{TABLE_NAME}, "t_dbd_mysql_t11");
+ is($info->[1]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 2, "two rows expected");
+
+ # Test fetching info on a single table with escaped wildcards
+ $sth = $dbh->table_info(undef, undef, $base . "t2", undef);
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_CAT}, undef);
+ is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_t2");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 1, "only one table expected");
+
+ # Test fetching info on a single table with ` in name
+ $sth = $dbh->table_info(undef, undef, $base . "a`b", undef);
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_CAT}, undef);
+ is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_a`b");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 1, "only one table expected");
+
+ # Test fetching info on a single table with ' in name
+ $sth = $dbh->table_info(undef, undef, $base . "a'b", undef);
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_CAT}, undef);
+ is($info->[0]->{TABLE_NAME}, "t_dbd_mysql_a'b");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 1, "only one table expected");
+
+ # Test fetching our tables with a wildcard schema
+ # NOTE: the performance of this could be bad if the mysql user we
+ # are connecting as can see lots of databases.
+ $sth = $dbh->table_info(undef, "%", $base . "%", undef);
+ $info = $sth->fetchall_arrayref({});
+
+ is(scalar @$info, 5, "five tables expected");
+
+ # Check that tables() finds and escapes the tables named with quotes
+ $info = [ $dbh->tables(undef, undef, $base . 'a%') ];
+ like($info->[0], qr/\.`t_dbd_mysql_a'b`$/, "table with single quote");
+ like($info->[1], qr/\.`t_dbd_mysql_a``b`$/, "table with back quote");
+ is(scalar @$info, 2, "two tables expected");
+
+ # Clean up
+ ok($dbh->do(qq{DROP TABLE IF EXISTS t_dbd_mysql_t1, t_dbd_mysql_t11,
+ t_dbd_mysql_t2, t_dbd_mysqlat2,
+ `t_dbd_mysql_a'b`,
+ `t_dbd_mysql_a``b`}),
+ "cleaning up");
+};
+
+#
+# view-related table_info tests
#
SKIP: {
- skip "Server is too old to support views", 16
+ skip "Server is too old to support views", 19
if substr($version, 0, 1) < 5;
+ #
+ # Bug #26603: (one part) support views in table_info()
+ #
ok($dbh->do(qq{DROP VIEW IF EXISTS bug26603_v1}) and
$dbh->do(qq{DROP TABLE IF EXISTS bug26603_t1}), "cleaning up");
@@ -95,13 +201,41 @@
$dbh->do(qq{CREATE VIEW bug26603_v1 AS SELECT * FROM bug26603_t1}),
"creating resources");
- $sth= $dbh->table_info(undef, undef, "bug26603%");
- my ($info)= $sth->fetchall_arrayref({});
+ # Try without any table type specified
+ $sth = $dbh->table_info(undef, undef, "bug26603%");
+ my $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_NAME}, "bug26603_t1");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is($info->[1]->{TABLE_NAME}, "bug26603_v1");
+ is($info->[1]->{TABLE_TYPE}, "VIEW");
+ is(scalar @$info, 2, "two rows expected");
+
+ # Just get the view
+ $sth = $dbh->table_info(undef, undef, "bug26603%", "VIEW");
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_NAME}, "bug26603_v1");
+ is($info->[0]->{TABLE_TYPE}, "VIEW");
+ is(scalar @$info, 1, "one row expected");
+
+ # Just get the table
+ $sth = $dbh->table_info(undef, undef, "bug26603%", "TABLE");
+ $info = $sth->fetchall_arrayref({});
+
+ is($info->[0]->{TABLE_NAME}, "bug26603_t1");
+ is($info->[0]->{TABLE_TYPE}, "TABLE");
+ is(scalar @$info, 1, "one row expected");
+
+ # Get both tables and views
+ $sth = $dbh->table_info(undef, undef, "bug26603%", "'TABLE','VIEW'");
+ $info = $sth->fetchall_arrayref({});
is($info->[0]->{TABLE_NAME}, "bug26603_t1");
is($info->[0]->{TABLE_TYPE}, "TABLE");
is($info->[1]->{TABLE_NAME}, "bug26603_v1");
is($info->[1]->{TABLE_TYPE}, "VIEW");
+ is(scalar @$info, 2, "two rows expected");
ok($dbh->do(qq{DROP VIEW IF EXISTS bug26603_v1}) and
$dbh->do(qq{DROP TABLE IF EXISTS bug26603_t1}), "cleaning up");
| Thread |
|---|
| • [svn:DBD-mysql] r9188 - in DBD-mysql/trunk: . lib/DBD t | jimw | 1 Mar |
