$statement .= ' WHERE '. join(' AND ', map {
my $op = '=';
+ my $column = $_;
if ( ref($record->{$_}) ) {
$op = $record->{$_}{'op'} if $record->{$_}{'op'};
- $op = 'LIKE' if $op =~ /^ILIKE$/i && driver_name ne 'Pg';
+ #$op = 'LIKE' if $op =~ /^ILIKE$/i && driver_name ne 'Pg';
+ if ( uc($op) eq 'ILIKE' ) {
+ $op = 'LIKE';
+ $record->{$_}{'value'} = lc($record->{$_}{'value'});
+ $column = "LOWER($_)";
+ }
$record->{$_} = $record->{$_}{'value'}
}
if ( ! defined( $record->{$_} ) || $record->{$_} eq '' ) {
if ( $op eq '=' ) {
if ( driver_name eq 'Pg' ) {
- qq-( $_ IS NULL OR $_ = '' )-;
+ if ( $dbdef->table($table)->column($column)->type =~ /(int)/i ) {
+ qq-( $column IS NULL )-;
+ } else {
+ qq-( $column IS NULL OR $column = '' )-;
+ }
} else {
- qq-( $_ IS NULL OR $_ = "" )-;
+ qq-( $column IS NULL OR $column = "" )-;
}
} elsif ( $op eq '!=' ) {
if ( driver_name eq 'Pg' ) {
- qq-( $_ IS NOT NULL AND $_ != '' )-;
+ if ( $dbdef->table($table)->column($column)->type =~ /(int)/i ) {
+ qq-( $column IS NOT NULL )-;
+ } else {
+ qq-( $column IS NOT NULL AND $column != '' )-;
+ }
} else {
- qq-( $_ IS NOT NULL AND $_ != "" )-;
+ qq-( $column IS NOT NULL AND $column != "" )-;
}
} else {
if ( driver_name eq 'Pg' ) {
- qq-( $_ $op '' )-;
+ qq-( $column $op '' )-;
} else {
- qq-( $_ $op "" )-;
+ qq-( $column $op "" )-;
}
}
} else {
- "$_ $op ?";
+ "$column $op ?";
}
} @fields );
}
=cut
sub qsearchs { # $result_record = &FS::Record:qsearchs('table',\%hash);
+ my $table = $_[0];
my(@result) = qsearch(@_);
- carp "warning: Multiple records in scalar search!" if scalar(@result) > 1;
- #should warn more vehemently if the search was on a primary key?
+ carp "warning: Multiple records in scalar search ($table)"
+ if scalar(@result) > 1;
+ #should warn more vehemently if the search was on a primary key?
scalar(@result) ? ($result[0]) : ();
}
my $db_seq = 0;
if ( $primary_key ) {
my $col = $self->dbdef_table->column($primary_key);
- my $db_seq =
+
+ $db_seq =
uc($col->type) eq 'SERIAL'
- || ( driver_name eq 'Pg' && $col->default =~ /^nextval\(/i )
- || ( driver_name eq 'mysql' && $col->local =~ /AUTO_INCREMENT/i );
+ || ( driver_name eq 'Pg'
+ && defined($col->default)
+ && $col->default =~ /^nextval\(/i
+ )
+ || ( driver_name eq 'mysql'
+ && defined($col->local)
+ && $col->local =~ /AUTO_INCREMENT/i
+ );
$self->unique($primary_key) unless $self->getfield($primary_key) || $db_seq;
}
$sth->execute or return $sth->errstr;
if ( $db_seq ) { # get inserted id from the database, if applicable
+ warn "[debug]$me retreiving sequence from database\n" if $DEBUG;
my $insertid = '';
if ( driver_name eq 'Pg' ) {
my $oid = $sth->{'pg_oid_status'};
- my $i_sql = "SELECT id FROM $table WHERE oid = ?";
+ my $i_sql = "SELECT $primary_key FROM $table WHERE oid = ?";
my $i_sth = dbh->prepare($i_sql) or do {
dbh->rollback if $FS::UID::AutoCommit;
return dbh->errstr;
=cut
sub _quote {
- my($value,$table,$field)=@_;
- my($dbh)=dbh;
- if ( $value =~ /^\d+(\.\d+)?$/ &&
-# ! ( datatype($table,$field) =~ /^char/ )
- ! $dbdef->table($table)->column($field)->type =~ /(char|binary|text)$/i
- ) {
+ my($value, $table, $column) = @_;
+ my $column_obj = $dbdef->table($table)->column($column);
+ my $column_type = $column_obj->type;
+
+ if ( $value eq '' && $column_type =~ /^int/ ) {
+ if ( $column_obj->null ) {
+ 'NULL';
+ } else {
+ cluck "WARNING: Attempting to set non-null integer $table.$column null; ".
+ "using 0 instead";
+ 0;
+ }
+ } elsif ( $value =~ /^\d+(\.\d+)?$/ &&
+ ! $column_type =~ /(char|binary|text)$/i ) {
$value;
} else {
- $dbh->quote($value);
+ dbh->quote($value);
}
}