Skip Menu |
 

This queue is for tickets about the Class-DBI-Pg CPAN distribution.

Report information
The Basics
Id: 3123
Status: new
Priority: 0/
Queue: Class-DBI-Pg

People
Owner: Nobody in particular
Requestors: ivey [...] gweezlebur.com
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)

Attachments


Subject: sequences and Postgres appear to not get along
Download (untitled) / with headers
text/plain 512b
Howdy. I'm trying to use Postgres as the backend, and use sequences for object IDs. It appears that this won't work: # the DBI will provide a standard attribute soon, meanwhile... my $id = $dbh->{mysql_insertid} # mysql || eval { $dbh->func('last_insert_rowid') }; # SQLite Postgres has neither mysql_insertid nor last_insert_rowid. I guess this is a good reason to play with SQLite, but you may want to at least add a doc note, until the DBI offers a solution. Thanks.
Download (untitled) / with headers
text/plain 215b
Sequences are added as the primary key before the row is inserted, rather than after. This only works if you've explicitly set up a sequence. See the "sequence / auto_increment" section of the docs. Thanks, Tony
From: andrew [...] etc.gen.nz
Download (untitled) / with headers
text/plain 899b
[TMTM - Fri Sep 12 05:07:37 2003]: Show quoted text
> Sequences are added as the primary key before the row is inserted, > rather than after. > > This only works if you've explicitly set up a sequence. See the > "sequence / auto_increment" section of the docs.
Attached is a patch which makes _auto_increment_value fetch the latest sequence ID from a PostgreSQL table and doesn't include the primary keys in the _create statement if they are null (assumes that the serial works). This allows the same code to work against an SQLite DB and a PostgreSQL DB without any modifications to the application code. This patch causes one of the unit tests to fail - 09/has-many test 23. I've not sure of how to debug unit tests, so haven't done so. Would this patch be considered for Class::DBI? (If not, I won't bother debugging the test case, if it is then I'll investigate further why it isn't working.) Cheers!
--- lib/Class/DBI.pm.orig 2004-04-30 19:22:12.000000000 +1200 +++ lib/Class/DBI.pm 2005-04-10 14:21:44.000000000 +1200 @@ -613,12 +613,31 @@ } sub _auto_increment_value { - my $self = shift; - my $dbh = $self->db_Main; + my $self = shift; + my $dbh = $self->db_Main; + my $driver = lc($self->__driver); + my $id = undef; # the DBI will provide a standard attribute soon, meanwhile... - my $id = $dbh->{mysql_insertid} # mysql - || eval { $dbh->func('last_insert_rowid') }; # SQLite + if ($driver eq 'mysql') { + $id = $dbh->{mysql_insertid} + } elsif ($driver eq 'sqlite') { + $id = $dbh->func('last_insert_rowid'); + } elsif ($driver eq 'pg') { + my $table = $self->table; + my $column = ($self->primary_column)[0]; + + my ($sth) = $dbh->prepare("SELECT last_value FROM ${table}_${column}_seq") + || $self->_croak("Failed to prepare select: $DBI::errstr"); + + my ($rc) = $sth->execute + || $self->_croak("Failed to execute select: $DBI::errstr"); + + $sth->bind_columns(\$id); + $sth->fetch; + $sth->finish; + } + $self->_croak("Can't get last insert id") unless defined $id; return $id; } @@ -626,14 +645,23 @@ sub _insert_row { my $self = shift; my $data = shift; + + my %primary_columns = map { $_ => 1 } $self->primary_columns; + my (@columns) = (); + my (@values) = (); + for my $col (keys %$data) { + if (! (defined $primary_columns{$col} && ! defined $data->{$col})) { + push @columns, $col; + push @values, $data->{$col}; + } + } eval { - my @columns = keys %$data; my $sth = $self->sql_MakeNewObj( join(', ', @columns), join(', ', map $self->_column_placeholder($_), @columns), ); $self->_bind_param($sth, \@columns); - $sth->execute(values %$data); + $sth->execute(@values); my @primary_columns = $self->primary_columns; $data->{ $primary_columns[0] } = $self->_auto_increment_value if @primary_columns == 1
Date: Sun, 10 Apr 2005 09:07:05 +0100
From: Tony Bowden <tony [...] kasei.com>
To: Guest via RT <bug-Class-DBI [...] rt.cpan.org>
Subject: Re: [cpan #3123] sequences and Postgres appear to not get along
RT-Send-Cc:
Download (untitled) / with headers
text/plain 429b
On Sat, Apr 09, 2005 at 10:39:49PM -0400, Guest via RT wrote: Show quoted text
> Attached is a patch which makes _auto_increment_value fetch the latest > sequence ID from a PostgreSQL table and doesn't include the primary keys > in the _create statement if they are null (assumes that the serial works).
I'm more inclined to say that this should go in Class::DBI::Pg, but I'm open to persuasion about it needing to go higher... Thanks, Tony


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.