[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
