MySQL Bugs: #26603: PATCH: support views in table_info(), add primary_key_info(), add mysql_is_auto

Bug #26603	PATCH: support views in table_info(), add primary_key_info(), add mysql_is_auto_
Submitted:	23 Feb 2007 18:44	Modified:	28 Feb 2007 23:19
Reporter:	Dave Rolsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
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.

How to repeat:
Uh, this is silly.

Suggested fix:
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;
+}
+

Thank you for the bug report and contribution.

primary_key_info() was already added in 4.001.

I've incorporated your patches for having mysql_is_auto_increment in the column_info results, and returning the correct table type for views from table_info.

Thanks for the patches.