PATCH - support views in table_info(), add primary_key_info(), add mysql_is_auto_increment to column_info()
Dave Rolsky <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Message-ID | <[email protected]> |
Here's a patch which fixes some bugs and enhances some of the various *_info() methods. It does the following: Improves the TABLE_TYPE value for table_info(). Previously, this was hard-coded to 'TABLE', but since MySQL now supports views, this is no longer correct. I'm not too thrilled with the _has_views() thing I added, and I suspect there's a better way to get this version info. Adds primary_key_info(). Once this exists then DBI appears to provide a primary_key() method as well. I added a new mysql-specific item to the column_info return value, mysql_is_auto_increment. It should be obvious what this does ;) There's no tests for any of these, but I tested the code with a project I'm working on which makes use of these methods, and it did work. -dave /*=================================================== VegGuide.Org www.BookIRead.com Your guide to all that's veg. My book blog ===================================================*/ -- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/[email protected]
dbd-mysql.patch
(text/plain, 4.6 KB)
Only in .: dbd-mysql.patch
Only in .: foo
Only in .: foo~
diff -ru ../DBD-mysql-4.00.old/lib/DBD/mysql.pm ./lib/DBD/mysql.pm
--- ../DBD-mysql-4.00.old/lib/DBD/mysql.pm 2006-12-28 14:22:31.000000000 -0600
+++ ./lib/DBD/mysql.pm 2006-12-28 14:59:32.000000000 -0600
@@ -183,6 +183,7 @@
package DBD::mysql::db; # ====== DATABASE ======
use strict;
use DBI qw(:sql_types);
+use DBI::Const::GetInfoType;
%DBD::mysql::db::db2ANSI = ("INT" => "INTEGER",
"CHAR" => "CHAR",
@@ -257,14 +258,17 @@
sub table_info ($) {
my $dbh = shift;
- my $sth = $dbh->prepare("SHOW TABLES");
+
+ my $sql = _has_views($dbh) ? 'SHOW FULL TABLES' : 'SHOW TABLES';
+ my $sth = $dbh->prepare($sql);
return undef unless $sth;
if (!$sth->execute()) {
return DBI::set_err($dbh, $sth->err(), $sth->errstr());
}
my @tables;
while (my $ref = $sth->fetchrow_arrayref()) {
- push(@tables, [ undef, undef, $ref->[0], 'TABLE', undef ]);
+ my $type = $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'})) {
@@ -283,6 +287,15 @@
}
$sth2;
}
+
+ sub _has_views {
+ my $dbh = shift;
+
+ my ($maj, $min, $point) =
+ $dbh->get_info($GetInfoType{SQL_DBMS_VER}) =~ /(\d+)\.(\d+)\.(\d+)/;
+
+ return 1 if $maj >= 5 && $point >= 1;
+ }
}
sub _ListTables {
@@ -313,7 +326,7 @@
UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME
SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY
DTD_IDENTIFIER IS_SELF_REF
- mysql_is_pri_key mysql_type_name mysql_values
+ mysql_is_pri_key mysql_type_name mysql_is_auto_increment mysql_values
);
my %col_info;
@@ -325,6 +338,8 @@
my $type = $row->{type};
$type =~ m/^(\w+)(?:\((.*?)\))?\s*(.*)/;
my $basetype = lc($1);
+ my $typemod = $2;
+ my $attr = $3;
my $info = $col_info{ $row->{field} } = {
TABLE_CAT => $catalog,
@@ -338,15 +353,16 @@
ORDINAL_POSITION => ++$ordinal_pos,
mysql_is_pri_key => ($row->{key} eq 'PRI'),
mysql_type_name => $row->{type},
+ mysql_is_auto_increment => ($row->{extra} =~ /auto_increment/i ? 1 : 0),
};
# This code won't deal with a pathalogical case where a value
# contains a single quote followed by a comma, and doesn't unescape
# any escaped values. But who would use those in an enum or set?
- my @type_params = ($2 && index($2,"'")>=0)
- ? ("$2," =~ /'(.*?)',/g) # assume all are quoted
- : split /,/, $2||''; # no quotes, plain list
- s/''/'/g for @type_params; # undo doubling of quotes
- my @type_attr = split / /, $3||'';
+ my @type_params = ($typemod && index($typemod,"'")>=0)
+ ? ("$typemod," =~ /'(.*?)',/g) # assume all are quoted
+ : split /,/, $typemod||''; # no quotes, plain list
+ s/''/'/g for @type_params; # undo doubling of quotes
+ my @type_attr = split / /, $attr||'';
#warn "$type: $basetype [@type_params] [@type_attr]\n";
$info->{DATA_TYPE} = SQL_VARCHAR();
@@ -433,6 +449,45 @@
return $sth;
}
+sub primary_key_info {
+ my ($dbh, $catalog, $schema, $table) = @_;
+ return $dbh->set_err(1, "primary_key doesn't support table wildcard")
+ if $table !~ /^\w+$/;
+
+ my $table_id = $dbh->quote_identifier($catalog, $schema, $table);
+
+ local $dbh->{FetchHashKeyName} = 'NAME_lc';
+ my $index_sth = $dbh->prepare("SHOW INDEX FROM $table_id");
+ my $index = $dbh->selectall_arrayref($index_sth, { Columns=>{} });
+
+ my @names = qw(
+ TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
+ KEY_SEQ PK_NAME
+ );
+
+ my @pk_info;
+ for my $row (grep {$_->{key_name} eq 'PRIMARY'} @$index) {
+ push @pk_info, {
+ TABLE_CAT => $catalog,
+ TABLE_SCHEM => $schema,
+ TABLE_NAME => $table,
+ COLUMN_NAME => $row->{column_name},
+ KEY_SEQ => $row->{seq_in_index},
+ PK_NAME => $row->{key_name},
+ };
+ }
+
+ my $sponge = DBI->connect("DBI:Sponge:", '','')
+ or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
+ my $sth = $sponge->prepare("column_info $table", {
+ rows => [ map { [ @{$_}{@names} ] } @pk_info ],
+ NUM_OF_FIELDS => scalar @names,
+ NAME => \@names,
+ }) or return $dbh->DBI::set_err($sponge->err(), $sponge->errstr());
+
+ return $sth;
+}
+
####################
Only in .: mysql.patch
Only in ./t: mysql.mtest