| Subject: | Incorrect behaviour when sequences are primary keys in pgsql |
Hello,
DBIx::SQLengine does not appear to auto-detect sequential column types correctly under PGsql. In addition, it attempts to insert these columns as NULL when they are both unspecified and the primary key, which causes an error. (The correct action would be to insert them as DEFAULT).
The patch I have attached does the following:
- passes ColumnSet objects to sql_insert, since they are supported by the code
- If a field is either required or of type "sequential", the INSERT query will substitute DEFAULT for NULL
- When using DBIx::DBO2, take it's idea of what the column types are, instead of the underlying database layer's
With these changes, my DBIx::DBO2 database schemas are behaving as expected. Thanks for the great work on both these modules!
Cheers,
Tyler
p4 edit //depot/main/Appliance/src/pmx/cpan-packages/DBIx-SQLEngine/SQLEngine/Driver.pm
Index: SQLEngine/Driver.pm
--- SQLEngine/Driver.pm.~1~ Thu Jun 9 12:58:52 2005
+++ SQLEngine/Driver.pm Thu Jun 9 12:58:52 2005
@@ -1564,25 +1564,28 @@
}
$sql = "insert into $table";
- my $columns = delete $clauses{'columns'};
- if ( ! $columns and UNIVERSAL::isa( $clauses{'values'}, 'HASH' ) ) {
- $columns = $clauses{'values'}
+ my $columns_in = delete $clauses{'columns'};
+
+ my $columns;
+ if ( ! $columns_in and UNIVERSAL::isa( $clauses{'values'}, 'HASH' ) ) {
+ $columns_in = $clauses{'values'}
}
- if ( ! $columns or $columns eq '*' ) {
- $columns = '';
- } elsif ( ! ref( $columns ) and length( $columns ) ) {
+ if ( ! $columns_in or $columns_in eq '*' ) {
+ $columns_in = '';
+ } elsif ( ! ref( $columns_in ) and length( $columns_in ) ) {
# should be one or more comma-separated column names
- } elsif ( UNIVERSAL::can($columns, 'column_names') ) {
- $columns = join ', ', $columns->column_names;
- } elsif ( ref($columns) eq 'HASH' ) {
- $columns = join ', ', sort keys %$columns;
- } elsif ( ref($columns) eq 'ARRAY' ) {
- $columns = join ', ', @$columns;
+ $columns = $columns_in;
+ } elsif ( UNIVERSAL::can($columns_in, 'column_names') ) {
+ $columns = join ', ', $columns_in->column_names;
+ } elsif ( ref($columns_in) eq 'HASH' ) {
+ $columns = join ', ', sort keys %$columns_in;
+ } elsif ( ref($columns_in) eq 'ARRAY' ) {
+ $columns = join ', ', @$columns_in;
} else {
- confess("Unsupported column spec '$columns'");
+ confess("Unsupported column spec '$columns_in'");
}
if ( $columns ) {
- $sql .= " ($columns)";
+ $sql .= " ($columns)";
}
my $values = delete $clauses{'values'};
@@ -1602,9 +1605,21 @@
( scalar @value_args ) or croak("Values are missing or empty");
my @v_literals;
my @v_params;
+ my @columns;
+
+ if(UNIVERSAL::can($columns_in, 'column_names')) {
+ @columns = $columns_in->columns;
+ }
+
foreach my $v ( @value_args ) {
+ my $c;
+ $c = shift(@columns);
if ( ! defined($v) ) {
- push @v_literals, 'NULL';
+ if($c && ($c->required || $c->type eq 'sequential')) {
+ push @v_literals, 'DEFAULT';
+ } else {
+ push @v_literals, 'NULL';
+ }
} elsif ( ! ref($v) ) {
push @v_literals, '?';
push @v_params, $v;
p4 edit //depot/main/Appliance/src/pmx/cpan-packages/DBIx-SQLEngine/SQLEngine/Schema/Table.pm
Index: SQLEngine/Schema/Table.pm
--- SQLEngine/Schema/Table.pm.~1~ Thu Jun 9 12:58:52 2005
+++ SQLEngine/Schema/Table.pm Thu Jun 9 12:58:52 2005
@@ -404,13 +404,23 @@
sub insert_row {
my ($self, $row) = @_;
+ my @columns_in;
+ if(UNIVERSAL::can($row, 'field_columns')) {
+ @columns_in =
+ map { DBIx::SQLEngine::Schema::Column->new(name => $_->{name}, type => $_->{type}) } ($row->field_columns);
+ } else {
+ @columns_in = $self->columns;
+ };
+
my $primary = $self->column_primary_name;
- my @colnames = grep { $_ eq $primary or defined $row->{$_} }
- $self->column_names;
+ my @columns = grep { $_->name eq $primary or defined $row->{$_->name} }
+ @columns_in;
+
+ my $columns = DBIx::SQLEngine::Schema::ColumnSet->new(@columns);
$self->sqlengine_do('do_insert',
( $self->column_primary_is_sequence ? ( sequence => $primary ) : () ),
- columns => \@colnames,
+ columns => $columns,
values => $row,
);
}
End of Patch.