Re: Patch for $dbh->primary_key support
am 01.03.2004 22:28:16 von Rudy Lippan
On Tue, 2 Mar 2004, David Dick wrote:
> Date: Tue, 02 Mar 2004 08:05:41 +1100
> From: David Dick
> To: Stephen Adkins
> Cc: perl@lists.mysql.com
> Subject: Re: Patch for $dbh->primary_key support
>
> Stephen Adkins wrote:
> > Hi,
> >
> > Instead of using the "describe " command,
> > why not use the "show index from " command?
> > I imagine this is guaranteed to return the columns
> > in index (primary key) order.
> >
>
> Good call. :)
>
Here is a ROUGH draft of primary_key_info() -- once you have
primary_key_info() you get pirmary_key() for free :)
As of right now the code needs to insert the $catalog name even when the
catalog is not specified, needs to be tested some more & it should add the
rest of the information that mysql provides but the dbi does not require.
And, of course, The code needs to be cleaned up a bit ;)
sub primary_key_info {
my ($dbh, $catalog, $schema, $table) = @_;
return undef if $schema;
local $dbh->{FetchHashKeyName} = 'NAME_lc';
my $query = "SHOW indexes FROM ".$dbh->quote_identifier($table);
$query .= " FROM ".$dbh->quote_identifier( $catalog) if defined($catalog);
my $sth = $dbh->prepare_cached($query) || return undef;;
$sth->execute() || return undef;
my @pk_info;
while (my $pk_row = $sth->fetchrow_hashref()) {
next if $pk_row->{"key_name"} ne 'PRIMARY';
push @pk_info, [$catalog, undef, @{$pk_row}{ "table", "column_name",
"seq_in_index", "key_name"
}];
}
my $poriferan = DBI->connect("dbi:Sponge:","","",{ AutoCommit=>1 });
$poriferan or return undef;
my $fk_data = $poriferan->prepare('primarky_key_info', {
rows => \@pk_info, NAME => [qw(
TABLE_CAT TABLE_SCHEME TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
)]
});
return $fk_data;
}
I have checked the above into CVS (cvs.perl.org) on the Dev-3_0 branch.
If you have any comments/suggestions let me know.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Patch for $dbh->primary_key support
am 01.03.2004 22:28:16 von Rudy Lippan
On Tue, 2 Mar 2004, David Dick wrote:
> Date: Tue, 02 Mar 2004 08:05:41 +1100
> From: David Dick
> To: Stephen Adkins
> Cc: perl@lists.mysql.com
> Subject: Re: Patch for $dbh->primary_key support
>
> Stephen Adkins wrote:
> > Hi,
> >
> > Instead of using the "describe " command,
> > why not use the "show index from " command?
> > I imagine this is guaranteed to return the columns
> > in index (primary key) order.
> >
>
> Good call. :)
>
Here is a ROUGH draft of primary_key_info() -- once you have
primary_key_info() you get pirmary_key() for free :)
As of right now the code needs to insert the $catalog name even when the
catalog is not specified, needs to be tested some more & it should add the
rest of the information that mysql provides but the dbi does not require.
And, of course, The code needs to be cleaned up a bit ;)
sub primary_key_info {
my ($dbh, $catalog, $schema, $table) = @_;
return undef if $schema;
local $dbh->{FetchHashKeyName} = 'NAME_lc';
my $query = "SHOW indexes FROM ".$dbh->quote_identifier($table);
$query .= " FROM ".$dbh->quote_identifier( $catalog) if defined($catalog);
my $sth = $dbh->prepare_cached($query) || return undef;;
$sth->execute() || return undef;
my @pk_info;
while (my $pk_row = $sth->fetchrow_hashref()) {
next if $pk_row->{"key_name"} ne 'PRIMARY';
push @pk_info, [$catalog, undef, @{$pk_row}{ "table", "column_name",
"seq_in_index", "key_name"
}];
}
my $poriferan = DBI->connect("dbi:Sponge:","","",{ AutoCommit=>1 });
$poriferan or return undef;
my $fk_data = $poriferan->prepare('primarky_key_info', {
rows => \@pk_info, NAME => [qw(
TABLE_CAT TABLE_SCHEME TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
)]
});
return $fk_data;
}
I have checked the above into CVS (cvs.perl.org) on the Dev-3_0 branch.
If you have any comments/suggestions let me know.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Patch for $dbh->primary_key support
am 01.03.2004 22:47:53 von Stephen Adkins
Hi,
You probably intend to clean up the misspelling "primarky_key_info",
but I thought I'd point it out and save you one round trip of debugging.
Stephen
P.S. Thanks for all your work over the years on DBD::mysql.
On Mon, 2004-03-01 at 16:28, Rudy Lippan wrote:
> Here is a ROUGH draft of primary_key_info() -- once you have
> primary_key_info() you get pirmary_key() for free :)
....
> my $fk_data = $poriferan->prepare('primarky_key_info', {
> rows => \@pk_info, NAME => [qw(
> TABLE_CAT TABLE_SCHEME TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
> )]
> });
....
> If you have any comments/suggestions let me know.
>
> Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Patch for $dbh->primary_key support
am 01.03.2004 22:47:53 von Stephen Adkins
Hi,
You probably intend to clean up the misspelling "primarky_key_info",
but I thought I'd point it out and save you one round trip of debugging.
Stephen
P.S. Thanks for all your work over the years on DBD::mysql.
On Mon, 2004-03-01 at 16:28, Rudy Lippan wrote:
> Here is a ROUGH draft of primary_key_info() -- once you have
> primary_key_info() you get pirmary_key() for free :)
....
> my $fk_data = $poriferan->prepare('primarky_key_info', {
> rows => \@pk_info, NAME => [qw(
> TABLE_CAT TABLE_SCHEME TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
> )]
> });
....
> If you have any comments/suggestions let me know.
>
> Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Patch for $dbh->primary_key support
am 03.03.2004 03:53:38 von Rudy Lippan
On 1 Mar 2004, Stephen Adkins wrote:
> You probably intend to clean up the misspelling "primarky_key_info",
Thank you for pointing this out. I have updated my copy of the code and
it will get commited next time I do a ci.
> but I thought I'd point it out and save you one round trip of debugging.
>
> Stephen
>
> P.S. Thanks for all your work over the years on DBD::mysql.
>
I don't think I have been working on DBD::mysql year yet ;)
Once again, thank you for the bug fix.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Re: Patch for $dbh->primary_key support
am 03.03.2004 03:53:38 von Rudy Lippan
On 1 Mar 2004, Stephen Adkins wrote:
> You probably intend to clean up the misspelling "primarky_key_info",
Thank you for pointing this out. I have updated my copy of the code and
it will get commited next time I do a ci.
> but I thought I'd point it out and save you one round trip of debugging.
>
> Stephen
>
> P.S. Thanks for all your work over the years on DBD::mysql.
>
I don't think I have been working on DBD::mysql year yet ;)
Once again, thank you for the bug fix.
Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org