From: Date: March 1 2007 10:13pm Subject: [svn:DBD-mysql] r9188 - in DBD-mysql/trunk: . lib/DBD t List-Archive: http://lists.mysql.com/commits/20948 Message-Id: <20070301211353.8774ACBA2A@x12.develooper.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 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 Jim Winstead (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");