X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=126b0cb31dee2740ec507531c8d4b81cfb2c39a4;hp=f17b240362d6b7ee9949218a787b9ef1cc152c32;hb=aed8ec35ccb9cdeb7ea0cb6ff2946f9d83d582f6;hpb=2b1f6ef77f0ef347897a060cfe94bbff2e3f6c76 diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index f17b24036..126b0cb31 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -2,10 +2,12 @@ package FS::Record; use strict; use vars qw( $AUTOLOAD @ISA @EXPORT_OK $DEBUG - $conf $conf_encryption $me %virtual_fields_cache + $conf $conf_encryption $money_char $lat_lower $lon_upper + $me $nowarn_identical $nowarn_classload $no_update_diff $no_check_foreign + @encrypt_payby ); use Exporter; use Carp qw(carp cluck croak confess); @@ -15,12 +17,13 @@ use Locale::Country; use Text::CSV_XS; use File::Slurp qw( slurp ); use DBI qw(:sql_types); -use DBIx::DBSchema 0.33; +use DBIx::DBSchema 0.38; use FS::UID qw(dbh getotaker datasrc driver_name); use FS::CurrentUser; use FS::Schema qw(dbdef); use FS::SearchCache; use FS::Msgcat qw(gettext); +use NetAddr::IP; # for validation #use FS::Conf; #dependency loop bs, in install_callback below instead use FS::part_virtual_field; @@ -29,9 +32,13 @@ use Tie::IxHash; @ISA = qw(Exporter); +@encrypt_payby = qw( CARD DCRD CHEK DCHK ); + #export dbdef for now... everything else expects to find it here -@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch - str2time_sql str2time_sql_closing ); +@EXPORT_OK = qw( + dbh fields hfields qsearch qsearchs dbdef jsearch + str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql +); $DEBUG = 0; $me = '[FS::Record]'; @@ -49,17 +56,25 @@ my $rsa_decrypt; $conf = ''; $conf_encryption = ''; FS::UID->install_callback( sub { + eval "use FS::Conf;"; die $@ if $@; $conf = FS::Conf->new; $conf_encryption = $conf->exists('encryption'); + $money_char = $conf->config('money_char') || '$'; + my $nw_coords = $conf->exists('geocode-require_nw_coordinates'); + $lat_lower = $nw_coords ? 1 : -90; + $lon_upper = $nw_coords ? -1 : 180; + $File::CounterFile::DEFAULT_DIR = $conf->base_dir . "/counters.". datasrc; + if ( driver_name eq 'Pg' ) { eval "use DBD::Pg ':pg_types'"; die $@ if $@; } else { eval "sub PG_BYTEA { die 'guru meditation #9: calling PG_BYTEA when not running Pg?'; }"; } + } ); =head1 NAME @@ -265,7 +280,7 @@ sub _bind_type { my $bind_type = { TYPE => SQL_VARCHAR }; - if ( $type =~ /(big)?(int|serial)/i && $value =~ /^\d+(\.\d+)?$/ ) { + if ( $type =~ /(big)?(int|serial)/i && $value =~ /^-?\d+(\.\d+)?$/ ) { $bind_type = { TYPE => SQL_INTEGER }; @@ -350,7 +365,8 @@ sub qsearch { my @bind_type = (); my $dbh = dbh; foreach my $stable ( @stable ) { - my $record = shift @record; + #stop altering the caller's hashref + my $record = { %{ shift(@record) || {} } };#and be liberal in receipt my $select = shift @select; my $extra_sql = shift @extra_sql; my $extra_param = shift @extra_param; @@ -527,6 +543,11 @@ sub qsearch { && eval 'defined(@FS::'. $table . '::encrypted_fields)' ) { foreach my $record (@return) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { + next if $field eq 'payinfo' + && ($record->isa('FS::payinfo_transaction_Mixin') + || $record->isa('FS::payinfo_Mixin') ) + && $record->payby + && !grep { $record->payby eq $_ } @encrypt_payby; # Set it directly... This may cause a problem in the future... $record->setfield($field, $record->decrypt($record->getfield($field))); } @@ -795,6 +816,17 @@ sub setfield { $self->set(@_); } +=item exists COLUMN + +Returns true if the column/field/key COLUMN exists. + +=cut + +sub exists { + my($self,$field) = @_; + exists($self->{Hash}->{$field}); +} + =item AUTLOADED METHODS $record->column is a synonym for $record->get('column'); @@ -962,12 +994,12 @@ sub insert { my $db_seq = 0; if ( $primary_key ) { my $col = $self->dbdef_table->column($primary_key); - + $db_seq = uc($col->type) =~ /^(BIG)?SERIAL\d?/ || ( driver_name eq 'Pg' && defined($col->default) - && $col->default =~ /^nextval\(/i + && $col->quoted_default =~ /^nextval\(/i ) || ( driver_name eq 'mysql' && defined($col->local) @@ -984,7 +1016,12 @@ sub insert { && $conf->exists('encryption') ) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { - $self->{'saved'} = $self->getfield($field); + next if $field eq 'payinfo' + && ($self->isa('FS::payinfo_transaction_Mixin') + || $self->isa('FS::payinfo_Mixin') ) + && $self->payby + && !grep { $self->payby eq $_ } @encrypt_payby; + $saved->{$field} = $self->getfield($field); $self->setfield($field, $self->encrypt($self->getfield($field))); } } @@ -1032,7 +1069,7 @@ sub insert { #my $oid = $sth->{'pg_oid_status'}; #my $i_sql = "SELECT $primary_key FROM $table WHERE oid = ?"; - my $default = $self->dbdef_table->column($primary_key)->default; + my $default = $self->dbdef_table->column($primary_key)->quoted_default; unless ( $default =~ /^nextval\(\(?'"?([\w\.]+)"?'/i ) { dbh->rollback if $FS::UID::AutoCommit; return "can't parse $table.$primary_key default value". @@ -1264,6 +1301,11 @@ sub replace { && scalar( eval '@FS::'. $new->table . '::encrypted_fields') ) { foreach my $field (eval '@FS::'. $new->table . '::encrypted_fields') { + next if $field eq 'payinfo' + && ($new->isa('FS::payinfo_transaction_Mixin') + || $new->isa('FS::payinfo_Mixin') ) + && $new->payby + && !grep { $new->payby eq $_ } @encrypt_payby; $saved->{$field} = $new->getfield($field); $new->setfield($field, $new->encrypt($new->getfield($field))); } @@ -1274,7 +1316,9 @@ sub replace { ? ($_, $new->getfield($_)) : () } $old->fields; unless (keys(%diff) || $no_update_diff ) { - carp "[warning]$me $new -> replace $old: records identical" + carp "[warning]$me ". ref($new)."->replace ". + ( $primary_key ? "$primary_key ".$new->get($primary_key) : '' ). + ": records identical" unless $nowarn_identical; return ''; } @@ -1547,7 +1591,7 @@ sub process_batch_import { my($job, $opt) = ( shift, shift ); my $table = $opt->{table}; - my @pass_params = @{ $opt->{params} }; + my @pass_params = $opt->{params} ? @{ $opt->{params} } : (); my %formats = %{ $opt->{formats} }; my $param = thaw(decode_base64(shift)); @@ -1561,24 +1605,33 @@ sub process_batch_import { my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; my $file = $dir. $files{'file'}; - my $error = - FS::Record::batch_import( { - #class-static - table => $table, - formats => \%formats, - format_types => $opt->{format_types}, - format_headers => $opt->{format_headers}, - format_sep_chars => $opt->{format_sep_chars}, - format_fixedlength_formats => $opt->{format_fixedlength_formats}, - #per-import - job => $job, - file => $file, - #type => $type, - format => $param->{format}, - params => { map { $_ => $param->{$_} } @pass_params }, - #? - default_csv => $opt->{default_csv}, - } ); + my %iopt = ( + #class-static + table => $table, + formats => \%formats, + format_types => $opt->{format_types}, + format_headers => $opt->{format_headers}, + format_sep_chars => $opt->{format_sep_chars}, + format_fixedlength_formats => $opt->{format_fixedlength_formats}, + format_xml_formats => $opt->{format_xml_formats}, + format_row_callbacks => $opt->{format_row_callbacks}, + #per-import + job => $job, + file => $file, + #type => $type, + format => $param->{format}, + params => { map { $_ => $param->{$_} } @pass_params }, + #? + default_csv => $opt->{default_csv}, + postinsert_callback => $opt->{postinsert_callback}, + ); + + if ( $opt->{'batch_namecol'} ) { + $iopt{'batch_namevalue'} = $param->{ $opt->{'batch_namecol'} }; + $iopt{$_} = $opt->{$_} foreach qw( batch_keycol batch_table batch_namecol ); + } + + my $error = FS::Record::batch_import( \%iopt ); unlink $file; @@ -1593,6 +1646,8 @@ Class method for batch imports. Available params: =item table +=item format - usual way to specify import, with this format string selecting data from the formats and format_* info hashes + =item formats =item format_types @@ -1603,6 +1658,14 @@ Class method for batch imports. Available params: =item format_fixedlength_formats +=item format_row_callbacks + +=item fields - Alternate way to specify import, specifying import fields directly as a listref + +=item preinsert_callback + +=item postinsert_callback + =item params =item job @@ -1613,9 +1676,7 @@ FS::queue object, will be updated with progress =item type -csv, xls or fixedlength - -=item format +csv, xls, fixedlength, xml =item empty_ok @@ -1630,18 +1691,71 @@ sub batch_import { if $DEBUG; my $table = $param->{table}; - my $formats = $param->{formats}; my $job = $param->{job}; my $file = $param->{file}; - my $format = $param->{'format'}; my $params = $param->{params} || {}; - die "unknown format $format" unless exists $formats->{ $format }; + my( $type, $header, $sep_char, $fixedlength_format, + $xml_format, $row_callback, @fields ); + + my $postinsert_callback = ''; + $postinsert_callback = $param->{'postinsert_callback'} + if $param->{'postinsert_callback'}; + my $preinsert_callback = ''; + $preinsert_callback = $param->{'preinsert_callback'} + if $param->{'preinsert_callback'}; - my $type = $param->{'format_types'} - ? $param->{'format_types'}{ $format } - : $param->{type} || 'csv'; + if ( $param->{'format'} ) { + + my $format = $param->{'format'}; + my $formats = $param->{formats}; + die "unknown format $format" unless exists $formats->{ $format }; + + $type = $param->{'format_types'} + ? $param->{'format_types'}{ $format } + : $param->{type} || 'csv'; + + + $header = $param->{'format_headers'} + ? $param->{'format_headers'}{ $param->{'format'} } + : 0; + + $sep_char = $param->{'format_sep_chars'} + ? $param->{'format_sep_chars'}{ $param->{'format'} } + : ','; + + $fixedlength_format = + $param->{'format_fixedlength_formats'} + ? $param->{'format_fixedlength_formats'}{ $param->{'format'} } + : ''; + + $xml_format = + $param->{'format_xml_formats'} + ? $param->{'format_xml_formats'}{ $param->{'format'} } + : ''; + + $row_callback = + $param->{'format_row_callbacks'} + ? $param->{'format_row_callbacks'}{ $param->{'format'} } + : ''; + + @fields = @{ $formats->{ $format } }; + + } elsif ( $param->{'fields'} ) { + + $type = ''; #infer from filename + $header = 0; + $sep_char = ','; + $fixedlength_format = ''; + $row_callback = ''; + @fields = @{ $param->{'fields'} }; + + } else { + die "neither format nor fields specified"; + } + + #my $file = $param->{file}; unless ( $type ) { if ( $file =~ /\.(\w+)$/i ) { @@ -1655,20 +1769,6 @@ sub batch_import { if $param->{'default_csv'} && $type ne 'xls'; } - my $header = $param->{'format_headers'} - ? $param->{'format_headers'}{ $param->{'format'} } - : 0; - - my $sep_char = $param->{'format_sep_chars'} - ? $param->{'format_sep_chars'}{ $param->{'format'} } - : ','; - - my $fixedlength_format = - $param->{'format_fixedlength_formats'} - ? $param->{'format_fixedlength_formats'}{ $param->{'format'} } - : ''; - - my @fields = @{ $formats->{ $format } }; my $row = 0; my $count; @@ -1686,9 +1786,10 @@ sub batch_import { eval "use Parse::FixedLength;"; die $@ if $@; - $parser = new Parse::FixedLength $fixedlength_format; - - } else { + $parser = Parse::FixedLength->new($fixedlength_format); + + } + else { die "Unknown file type $type\n"; } @@ -1714,7 +1815,22 @@ sub batch_import { $count++; $row = $header || 0; - + } elsif ( $type eq 'xml' ) { + # FS::pay_batch + eval "use XML::Simple;"; + die $@ if $@; + my $xmlrow = $xml_format->{'xmlrow'}; + $parser = $xml_format->{'xmlkeys'}; + die 'no xmlkeys specified' unless ref $parser eq 'ARRAY'; + my $data = XML::Simple::XMLin( + $file, + 'SuppressEmpty' => '', #sets empty values to '' + 'KeepRoot' => 1, + ); + my $rows = $data; + $rows = $rows->{$_} foreach @$xmlrow; + $rows = [ $rows ] if ref($rows) ne 'ARRAY'; + $count = @buffer = @$rows; } else { die "Unknown file type $type\n"; } @@ -1731,7 +1847,27 @@ sub batch_import { my $oldAutoCommit = $FS::UID::AutoCommit; local $FS::UID::AutoCommit = 0; my $dbh = dbh; - + + #my $params = $param->{params} || {}; + if ( $param->{'batch_namecol'} && $param->{'batch_namevalue'} ) { + my $batch_col = $param->{'batch_keycol'}; + + my $batch_class = 'FS::'. $param->{'batch_table'}; + my $batch = $batch_class->new({ + $param->{'batch_namecol'} => $param->{'batch_namevalue'} + }); + my $error = $batch->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert batch record: $error"; + } + #primary key via dbdef? (so the column names don't have to match) + my $batch_value = $batch->get( $param->{'batch_keycol'} ); + + $params->{ $batch_col } = $batch_value; + } + + #my $job = $param->{job}; my $line; my $imported = 0; my( $last, $min_sec ) = ( time, 5 ); #progressbar foo @@ -1743,14 +1879,23 @@ sub batch_import { last unless scalar(@buffer); $line = shift(@buffer); + next if $line =~ /^\s*$/; #skip empty lines + + $line = &{$row_callback}($line) if $row_callback; + + next if $line =~ /^\s*$/; #skip empty lines + $parser->parse($line) or do { $dbh->rollback if $oldAutoCommit; - return "can't parse: ". $parser->error_input(); + return "can't parse: ". $parser->error_input() . " " . $parser->error_diag; }; @columns = $parser->fields(); } elsif ( $type eq 'fixedlength' ) { + last unless scalar(@buffer); + $line = shift(@buffer); + @columns = $parser->parse($line); } elsif ( $type eq 'xls' ) { @@ -1764,6 +1909,11 @@ sub batch_import { #my $z = 'A'; #warn $z++. ": $_\n" for @columns; + } elsif ( $type eq 'xml' ) { + # $parser = [ 'Column0Key', 'Column1Key' ... ] + last unless scalar(@buffer); + my $row = shift @buffer; + @columns = @{ $row }{ @$parser }; } else { die "Unknown file type $type\n"; } @@ -1785,6 +1935,7 @@ sub batch_import { } + #my $table = $param->{table}; my $class = "FS::$table"; my $record = $class->new( \%hash ); @@ -1793,11 +1944,27 @@ sub batch_import { while ( scalar(@later) ) { my $sub = shift @later; my $data = shift @later; - &{$sub}($record, $data, $conf, $param); # $record->&{$sub}($data, $conf); + eval { + &{$sub}($record, $data, $conf, $param); # $record->&{$sub}($data, $conf) + }; + if ( $@ ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert record". ( $line ? " for $line" : '' ). ": $@"; + } last if exists( $param->{skiprow} ); } next if exists( $param->{skiprow} ); + if ( $preinsert_callback ) { + my $error = &{$preinsert_callback}($record, $param); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "preinsert_callback error". ( $line ? " for $line" : '' ). + ": $error"; + } + next if exists $param->{skiprow} && $param->{skiprow}; + } + my $error = $record->insert; if ( $error ) { @@ -1808,6 +1975,15 @@ sub batch_import { $row++; $imported++; + if ( $postinsert_callback ) { + my $error = &{$postinsert_callback}($record, $param); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "postinsert_callback error". ( $line ? " for $line" : '' ). + ": $error"; + } + } + if ( $job && time - $min_sec > $last ) { #progress bar $job->update_statustext( int(100 * $imported / $count) ); $last = time; @@ -1815,9 +1991,12 @@ sub batch_import { } - $dbh->commit or die $dbh->errstr if $oldAutoCommit;; + unless ( $imported || $param->{empty_ok} ) { + $dbh->rollback if $oldAutoCommit; + return "Empty file!"; + } - return "Empty file!" unless $imported || $param->{empty_ok}; + $dbh->commit or die $dbh->errstr if $oldAutoCommit;; ''; #no error @@ -1836,7 +2015,7 @@ sub _h_statement { ; # If we're encrypting then don't store the payinfo in the history - if ( $conf && $conf->exists('encryption') ) { + if ( $conf && $conf->exists('encryption') && $self->table ne 'banned_pay' ) { @fields = grep { $_ ne 'payinfo' } @fields; } @@ -2074,7 +2253,7 @@ sub ut_text { #warn "notexist ". \¬exist. "\n"; #warn "AUTOLOAD ". \&AUTOLOAD. "\n"; $self->getfield($field) - =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>]+)$/ + =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/ or return gettext('illegal_or_empty_text'). " $field: ". $self->getfield($field); $self->setfield($field,$1); @@ -2084,18 +2263,15 @@ sub ut_text { =item ut_textn COLUMN Check/untaint text. Alphanumerics, spaces, and the following punctuation -symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / +symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / = [ ] < > May be null. If there is an error, returns the error, otherwise returns false. =cut sub ut_textn { my($self,$field)=@_; - $self->getfield($field) - =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]]*)$/ - or return gettext('illegal_text'). " $field: ". $self->getfield($field); - $self->setfield($field,$1); - ''; + return $self->setfield($field, '') if $self->getfield($field) =~ /^$/; + $self->ut_text($field); } =item ut_alpha COLUMN @@ -2114,7 +2290,7 @@ sub ut_alpha { ''; } -=item ut_alpha COLUMN +=item ut_alphan COLUMN Check/untaint alphanumeric strings (no spaces). May be null. If there is an error, returns the error, otherwise returns false. @@ -2129,6 +2305,22 @@ sub ut_alphan { ''; } +=item ut_alphasn COLUMN + +Check/untaint alphanumeric strings, spaces allowed. May be null. If there is +an error, returns the error, otherwise returns false. + +=cut + +sub ut_alphasn { + my($self,$field)=@_; + $self->getfield($field) =~ /^([\w ]*)$/ + or return "Illegal (alphanumeric) $field: ". $self->getfield($field); + $self->setfield($field,$1); + ''; +} + + =item ut_alpha_lower COLUMN Check/untaint lowercase alphanumeric strings (no spaces). May not be null. If @@ -2202,14 +2394,52 @@ sub ut_hexn { $self->setfield($field, uc($1)); ''; } + +=item ut_mac_addr COLUMN + +Check/untaint mac addresses. May be null. + +=cut + +sub ut_mac_addr { + my($self, $field) = @_; + + my $mac = $self->get($field); + $mac =~ s/\s+//g; + $mac =~ s/://g; + $self->set($field, $mac); + + my $e = $self->ut_hex($field); + return $e if $e; + + return "Illegal (mac address) $field: ". $self->getfield($field) + unless length($self->getfield($field)) == 12; + + ''; + +} + +=item ut_mac_addrn COLUMN + +Check/untaint mac addresses. May be null. + +=cut + +sub ut_mac_addrn { + my($self, $field) = @_; + ($self->getfield($field) eq '') ? '' : $self->ut_mac_addr($field); +} + =item ut_ip COLUMN -Check/untaint ip addresses. IPv4 only for now. +Check/untaint ip addresses. IPv4 only for now, though ::1 is auto-translated +to 127.0.0.1. =cut sub ut_ip { my( $self, $field ) = @_; + $self->setfield($field, '127.0.0.1') if $self->getfield($field) eq '::1'; $self->getfield($field) =~ /^(\d+)\.(\d+)\.(\d+)\.(\d+)$/ or return "Illegal (IP address) $field: ". $self->getfield($field); for ( $1, $2, $3, $4 ) { return "Illegal (IP address) $field" if $_ > 255; } @@ -2219,7 +2449,8 @@ sub ut_ip { =item ut_ipn COLUMN -Check/untaint ip addresses. IPv4 only for now. May be null. +Check/untaint ip addresses. IPv4 only for now, though ::1 is auto-translated +to 127.0.0.1. May be null. =cut @@ -2233,6 +2464,35 @@ sub ut_ipn { } } +=item ut_ip46 COLUMN + +Check/untaint IPv4 or IPv6 address. + +=cut + +sub ut_ip46 { + my( $self, $field ) = @_; + my $ip = NetAddr::IP->new($self->getfield($field)) + or return "Illegal (IP address) $field: ".$self->getfield($field); + $self->setfield($field, lc($ip->addr)); + return ''; +} + +=item ut_ip46n + +Check/untaint IPv6 or IPv6 address. May be null. + +=cut + +sub ut_ip46n { + my( $self, $field ) = @_; + if ( $self->getfield($field) =~ /^$/ ) { + $self->setfield($field, ''); + return ''; + } + $self->ut_ip46($field); +} + =item ut_coord COLUMN [ LOWER [ UPPER ] ] Check/untaint coordinates. @@ -2258,11 +2518,17 @@ for lower and upper bounds, respectively. =cut sub ut_coord { - my ($self, $field) = (shift, shift); - my $lower = shift if scalar(@_); - my $upper = shift if scalar(@_); + my($lower, $upper); + if ( $field =~ /latitude/ ) { + $lower = $lat_lower; + $upper = 90; + } elsif ( $field =~ /longitude/ ) { + $lower = -180; + $upper = $lon_upper; + } + my $coord = $self->getfield($field); my $neg = $coord =~ s/^(-)//; @@ -2310,7 +2576,7 @@ sub ut_coordn { my ($self, $field) = (shift, shift); - if ($self->getfield($field) =~ /^$/) { + if ($self->getfield($field) =~ /^\s*$/) { return ''; } else { return $self->ut_coord($field, @_); @@ -2345,6 +2611,7 @@ May not be null. sub ut_name { my( $self, $field ) = @_; +# warn "ut_name allowed alphanumerics: +(sort grep /\w/, map { chr() } 0..255), "\n"; $self->getfield($field) =~ /^([\w \,\.\-\']+)$/ or return gettext('illegal_name'). " $field: ". $self->getfield($field); $self->setfield($field,$1); @@ -2384,7 +2651,7 @@ sub ut_zip { { $self->setfield($field,''); } else { - $self->getfield($field) =~ /^\s*(\w[\w\-\s]{2,8}\w)\s*$/ + $self->getfield($field) =~ /^\s*(\w[\w\-\s]{0,8}\w)\s*$/ or return gettext('illegal_zip'). " $field: ". $self->getfield($field); $self->setfield($field,$1); } @@ -2446,6 +2713,20 @@ sub ut_enum { return "Illegal (enum) field $field: ". $self->getfield($field); } +=item ut_enumn COLUMN CHOICES_ARRAYREF + +Like ut_enum, except the null value is also allowed. + +=cut + +sub ut_enumn { + my( $self, $field, $choices ) = @_; + $self->getfield($field) + ? $self->ut_enum($field, $choices) + : ''; +} + + =item ut_foreign_key COLUMN FOREIGN_TABLE FOREIGN_COLUMN Check/untaint a foreign column key. Call a regular ut_ method (like ut_number) @@ -2677,7 +2958,7 @@ sub loadRSA { #Initialize the Module $rsa_module = 'Crypt::OpenSSL::RSA'; # The Default - if ($conf->exists('encryptionmodule') && $conf->config_binary('encryptionmodule') ne '') { + if ($conf->exists('encryptionmodule') && $conf->config('encryptionmodule') ne '') { $rsa_module = $conf->config('encryptionmodule'); } @@ -2686,13 +2967,13 @@ sub loadRSA { $rsa_loaded++; } # Initialize Encryption - if ($conf->exists('encryptionpublickey') && $conf->config_binary('encryptionpublickey') ne '') { + if ($conf->exists('encryptionpublickey') && $conf->config('encryptionpublickey') ne '') { my $public_key = join("\n",$conf->config('encryptionpublickey')); $rsa_encrypt = $rsa_module->new_public_key($public_key); } # Intitalize Decryption - if ($conf->exists('encryptionprivatekey') && $conf->config_binary('encryptionprivatekey') ne '') { + if ($conf->exists('encryptionprivatekey') && $conf->config('encryptionprivatekey') ne '') { my $private_key = join("\n",$conf->config('encryptionprivatekey')); $rsa_decrypt = $rsa_module->new_private_key($private_key); } @@ -2735,6 +3016,28 @@ sub h_date { $h ? $h->history_date : ''; } +=item scalar_sql SQL [ PLACEHOLDER, ... ] + +A class or object method. Executes the sql statement represented by SQL and +returns a scalar representing the result: the first column of the first row. + +Dies on bogus SQL. Returns an empty string if no row is returned. + +Typically used for statments which return a single value such as "SELECT +COUNT(*) FROM table WHERE something" OR "SELECT column FROM table WHERE key = ?" + +=cut + +sub scalar_sql { + my($self, $sql) = (shift, shift); + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute(@_) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + my $row = $sth->fetchrow_arrayref or return ''; + my $scalar = $row->[0]; + defined($scalar) ? $scalar : ''; +} + =back =head1 SUBROUTINES @@ -2878,6 +3181,71 @@ sub str2time_sql_closing { return ' ) '; } +=item regexp_sql [ DRIVER_NAME ] + +Returns the operator to do a regular expression comparison based on database +type, such as '~' for Pg or 'REGEXP' for mysql. + +You can pass an optional driver name such as "Pg", "mysql" or +$dbh->{Driver}->{Name} to return a function for that database instead of +the current database. + +=cut + +sub regexp_sql { + my $driver = shift || driver_name; + + return '~' if $driver =~ /^Pg/i; + return 'REGEXP' if $driver =~ /^mysql/i; + + die "don't know how to use regular expressions in ". driver_name." databases"; + +} + +=item not_regexp_sql [ DRIVER_NAME ] + +Returns the operator to do a regular expression negation based on database +type, such as '!~' for Pg or 'NOT REGEXP' for mysql. + +You can pass an optional driver name such as "Pg", "mysql" or +$dbh->{Driver}->{Name} to return a function for that database instead of +the current database. + +=cut + +sub not_regexp_sql { + my $driver = shift || driver_name; + + return '!~' if $driver =~ /^Pg/i; + return 'NOT REGEXP' if $driver =~ /^mysql/i; + + die "don't know how to use regular expressions in ". driver_name." databases"; + +} + +=item concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF + +Returns the items concatendated based on database type, using "CONCAT()" for +mysql and " || " for Pg and other databases. + +You can pass an optional driver name such as "Pg", "mysql" or +$dbh->{Driver}->{Name} to return a function for that database instead of +the current database. + +=cut + +sub concat_sql { + my $driver = ref($_[0]) ? driver_name : shift; + my $items = shift; + + if ( $driver =~ /^mysql/i ) { + 'CONCAT('. join(',', @$items). ')'; + } else { + join('||', @$items); + } + +} + =back =head1 BUGS