X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=cb091806825ea242d55d076ea24fc2766005ab09;hb=e4c7cd8602ebb3e70895aee1d7d8a49371ccb70c;hp=713b967676ccbe29de83998d410b59fef129b248;hpb=f4eb2dd15795c2f9da636ee084516cbafef7fbd5;p=freeside.git diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 713b96767..cb0918068 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -2,19 +2,25 @@ package FS::Record; use strict; use vars qw( $AUTOLOAD @ISA @EXPORT_OK $DEBUG - $me %virtual_fields_cache $nowarn_identical ); + $conf $conf_encryption $me + %virtual_fields_cache + $nowarn_identical $no_update_diff $no_check_foreign + ); use Exporter; use Carp qw(carp cluck croak confess); +use Scalar::Util qw( blessed ); use File::CounterFile; use Locale::Country; +use Text::CSV_XS; +use File::Slurp qw( slurp ); use DBI qw(:sql_types); -use DBIx::DBSchema 0.25; +use DBIx::DBSchema 0.33; 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 FS::Conf; +#use FS::Conf; #dependency loop bs, in install_callback below instead use FS::part_virtual_field; @@ -23,22 +29,32 @@ use Tie::IxHash; @ISA = qw(Exporter); #export dbdef for now... everything else expects to find it here -@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch); +@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch + str2time_sql str2time_sql_closing ); $DEBUG = 0; $me = '[FS::Record]'; $nowarn_identical = 0; +$no_update_diff = 0; +$no_check_foreign = 0; my $rsa_module; my $rsa_loaded; my $rsa_encrypt; my $rsa_decrypt; +$conf = ''; +$conf_encryption = ''; FS::UID->install_callback( sub { - $File::CounterFile::DEFAULT_DIR = "/usr/local/etc/freeside/counters.". datasrc; + eval "use FS::Conf;"; + die $@ if $@; + $conf = FS::Conf->new; + $conf_encryption = $conf->exists('encryption'); + $File::CounterFile::DEFAULT_DIR = $conf->base_dir . "/counters.". datasrc; } ); + =head1 NAME FS::Record - Database record objects @@ -81,8 +97,11 @@ FS::Record - Database record objects $value = $record->unique('column'); $error = $record->ut_float('column'); + $error = $record->ut_floatn('column'); $error = $record->ut_number('column'); $error = $record->ut_numbern('column'); + $error = $record->ut_snumber('column'); + $error = $record->ut_snumbern('column'); $error = $record->ut_money('column'); $error = $record->ut_text('column'); $error = $record->ut_textn('column'); @@ -204,8 +223,10 @@ The preferred usage is to pass a hash reference of named parameters: #these are optional... 'select' => '*', 'extra_sql' => 'AND field ', + 'order_by' => 'ORDER BY something', #'cache_obj' => '', #optional 'addl_from' => 'LEFT JOIN othtable USING ( field )', + 'debug' => 1, } ); @@ -225,16 +246,31 @@ fine in the common case where there are only two parameters: =cut +my %TYPE = (); #for debugging + +sub _is_fs_float { + my ($type, $value) = @_; + if ( ( $type =~ /(numeric)/i && $value =~ /^[+-]?\d+(\.\d+)?$/ ) || + ( $type =~ /(real|float4)/i && $value =~ /[-+]?\d*\.?\d+([eE][-+]?\d+)?/) + ) { + return 1; + } + ''; +} + sub qsearch { - my($stable, $record, $select, $extra_sql, $cache, $addl_from ); + my($stable, $record, $select, $extra_sql, $order_by, $cache, $addl_from ); + my $debug = ''; if ( ref($_[0]) ) { #hashref for now, eventually maybe accept a list too my $opt = shift; $stable = $opt->{'table'} or die "table name is required"; $record = $opt->{'hashref'} || {}; $select = $opt->{'select'} || '*'; $extra_sql = $opt->{'extra_sql'} || ''; + $order_by = $opt->{'order_by'} || ''; $cache = $opt->{'cache_obj'} || ''; $addl_from = $opt->{'addl_from'} || ''; + $debug = $opt->{'debug'} || ''; } else { ($stable, $record, $select, $extra_sql, $cache, $addl_from ) = @_; $select ||= '*'; @@ -265,96 +301,15 @@ sub qsearch { $statement .= " $addl_from" if $addl_from; if ( @real_fields or @virtual_fields ) { $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'; - 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' ) { - my $type = dbdef->table($table)->column($column)->type; - if ( $type =~ /(int|(big)?serial)/i ) { - qq-( $column IS NULL )-; - } else { - qq-( $column IS NULL OR $column = '' )-; - } - } else { - qq-( $column IS NULL OR $column = "" )-; - } - } elsif ( $op eq '!=' ) { - if ( driver_name eq 'Pg' ) { - my $type = dbdef->table($table)->column($column)->type; - if ( $type =~ /(int|(big)?serial)/i ) { - qq-( $column IS NOT NULL )-; - } else { - qq-( $column IS NOT NULL AND $column != '' )-; - } - } else { - qq-( $column IS NOT NULL AND $column != "" )-; - } - } else { - if ( driver_name eq 'Pg' ) { - qq-( $column $op '' )-; - } else { - qq-( $column $op "" )-; - } - } - } else { - "$column $op ?"; - } - } @real_fields ), - ( map { - my $op = '='; - my $column = $_; - if ( ref($record->{$_}) ) { - $op = $record->{$_}{'op'} if $record->{$_}{'op'}; - if ( uc($op) eq 'ILIKE' ) { - $op = 'LIKE'; - $record->{$_}{'value'} = lc($record->{$_}{'value'}); - $column = "LOWER($_)"; - } - $record->{$_} = $record->{$_}{'value'}; - } - - # ... EXISTS ( SELECT name, value FROM part_virtual_field - # JOIN virtual_field - # ON part_virtual_field.vfieldpart = virtual_field.vfieldpart - # WHERE recnum = svc_acct.svcnum - # AND (name, value) = ('egad', 'brain') ) - - my $value = $record->{$_}; - - my $subq; - - $subq = ($value ? 'EXISTS ' : 'NOT EXISTS ') . - "( SELECT part_virtual_field.name, virtual_field.value ". - "FROM part_virtual_field JOIN virtual_field ". - "ON part_virtual_field.vfieldpart = virtual_field.vfieldpart ". - "WHERE virtual_field.recnum = ${table}.${pkey} ". - "AND part_virtual_field.name = '${column}'". - ($value ? - " AND virtual_field.value ${op} '${value}'" - : "") . ")"; - $subq; - - } @virtual_fields ) ); - + get_real_fields($table, $record, \@real_fields) , + get_virtual_fields($table, $pkey, $record, \@virtual_fields), + ); } $statement .= " $extra_sql" if defined($extra_sql); + $statement .= " $order_by" if defined($order_by); - warn "[debug]$me $statement\n" if $DEBUG > 1; + warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug; my $sth = $dbh->prepare($statement) or croak "$dbh->errstr doing $statement"; @@ -363,13 +318,41 @@ sub qsearch { foreach my $field ( grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields ) { - if ( $record->{$field} =~ /^\d+(\.\d+)?$/ - && dbdef->table($table)->column($field)->type =~ /(int|(big)?serial)/i - ) { - $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_INTEGER } ); - } else { - $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_VARCHAR } ); + + my $value = $record->{$field}; + my $op = (ref($value) && $value->{op}) ? $value->{op} : '='; + $value = $value->{'value'} if ref($value); + my $type = dbdef->table($table)->column($field)->type; + + my $TYPE = SQL_VARCHAR; + if ( $type =~ /(int|(big)?serial)/i && $value =~ /^\d+(\.\d+)?$/ ) { + $TYPE = SQL_INTEGER; + + #DBD::Pg 1.49: Cannot bind ... unknown sql_type 6 with SQL_FLOAT + #fixed by DBD::Pg 2.11.8 + #can change back to SQL_FLOAT in early-mid 2010, once everyone's upgraded + } elsif ( _is_fs_float( $type, $value ) ) { + $TYPE = SQL_DECIMAL; } + + if ( $DEBUG > 2 ) { + no strict 'refs'; + %TYPE = map { &{"DBI::$_"}() => $_ } @{ $DBI::EXPORT_TAGS{sql_types} } + unless keys %TYPE; + warn " bind_param $bind (for field $field), $value, TYPE $TYPE{$TYPE}\n"; + } + + #if this needs to be re-enabled, it needs to use a custom op like + #"APPROX=" or something (better name?, not '=', to avoid affecting other + # searches + #if ($TYPE eq SQL_DECIMAL && $op eq 'APPROX=' ) { + # # these values are arbitrary; better (faster?) ones welcome + # $sth->bind_param($bind++, $value*1.00001, { TYPE => $TYPE } ); + # $sth->bind_param($bind++, $value*.99999, { TYPE => $TYPE } ); + #} else { + $sth->bind_param($bind++, $value, { TYPE => $TYPE } ); + #} + } # $sth->execute( map $record->{$_}, @@ -388,8 +371,6 @@ sub qsearch { my %result; tie %result, "Tie::IxHash"; my @stuff = @{ $sth->fetchall_arrayref( {} ) }; - #if ( $pkey ) { - #if ( $pkey && $stuff[0]->{$pkey} ) { if ( $pkey && scalar(@stuff) && $stuff[0]->{$pkey} ) { %result = map { $_->{$pkey}, $_ } @stuff; } else { @@ -442,8 +423,9 @@ sub qsearch { } # Check for encrypted fields and decrypt them. - my $conf = new FS::Conf; - if ($conf->exists('encryption') && eval 'defined(@FS::'. $table . '::encrypted_fields)') { + ## only in the local copy, not the cached object + if ( $conf_encryption + && eval 'defined(@FS::'. $table . '::encrypted_fields)' ) { foreach my $record (@return) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { # Set it directly... This may cause a problem in the future... @@ -460,6 +442,116 @@ sub qsearch { return @return; } +## makes this easier to read + +sub get_virtual_fields { + my $table = shift; + my $pkey = shift; + my $record = shift; + my $virtual_fields = shift; + + return + ( map { + my $op = '='; + my $column = $_; + if ( ref($record->{$_}) ) { + $op = $record->{$_}{'op'} if $record->{$_}{'op'}; + if ( uc($op) eq 'ILIKE' ) { + $op = 'LIKE'; + $record->{$_}{'value'} = lc($record->{$_}{'value'}); + $column = "LOWER($_)"; + } + $record->{$_} = $record->{$_}{'value'}; + } + + # ... EXISTS ( SELECT name, value FROM part_virtual_field + # JOIN virtual_field + # ON part_virtual_field.vfieldpart = virtual_field.vfieldpart + # WHERE recnum = svc_acct.svcnum + # AND (name, value) = ('egad', 'brain') ) + + my $value = $record->{$_}; + + my $subq; + + $subq = ($value ? 'EXISTS ' : 'NOT EXISTS ') . + "( SELECT part_virtual_field.name, virtual_field.value ". + "FROM part_virtual_field JOIN virtual_field ". + "ON part_virtual_field.vfieldpart = virtual_field.vfieldpart ". + "WHERE virtual_field.recnum = ${table}.${pkey} ". + "AND part_virtual_field.name = '${column}'". + ($value ? + " AND virtual_field.value ${op} '${value}'" + : "") . ")"; + $subq; + + } @{ $virtual_fields } ) ; +} + +sub get_real_fields { + my $table = shift; + my $record = shift; + my $real_fields = shift; + + ## this huge map was previously inline, just broke it out to help read the qsearch method, should be optimized for readability + return ( + map { + + my $op = '='; + my $column = $_; + my $type = dbdef->table($table)->column($column)->type; + my $value = $record->{$column}; + $value = $value->{'value'} if ref($value); + if ( ref($record->{$_}) ) { + $op = $record->{$_}{'op'} if $record->{$_}{'op'}; + #$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' ) { + if ( $type =~ /(int|numeric|real|float4|(big)?serial)/i ) { + qq-( $column IS NULL )-; + } else { + qq-( $column IS NULL OR $column = '' )-; + } + } else { + qq-( $column IS NULL OR $column = "" )-; + } + } elsif ( $op eq '!=' ) { + if ( driver_name eq 'Pg' ) { + if ( $type =~ /(int|numeric|real|float4|(big)?serial)/i ) { + qq-( $column IS NOT NULL )-; + } else { + qq-( $column IS NOT NULL AND $column != '' )-; + } + } else { + qq-( $column IS NOT NULL AND $column != "" )-; + } + } else { + if ( driver_name eq 'Pg' ) { + qq-( $column $op '' )-; + } else { + qq-( $column $op "" )-; + } + } + #if this needs to be re-enabled, it needs to use a custom op like + #"APPROX=" or something (better name?, not '=', to avoid affecting other + # searches + #} elsif ( $op eq 'APPROX=' && _is_fs_float( $type, $value ) ) { + # ( "$column <= ?", "$column >= ?" ); + } else { + "$column $op ?"; + } + } @{ $real_fields } ); +} + =item by_key PRIMARY_KEY_VALUE This is a class method that returns the record with the given primary key @@ -556,6 +648,17 @@ sub dbdef_table { dbdef->table($table); } +=item primary_key + +Returns the primary key for the table. + +=cut + +sub primary_key { + my $self = shift; + my $pkey = $self->dbdef_table->primary_key; +} + =item get, getfield COLUMN Returns the value of the column/field/key COLUMN. @@ -607,11 +710,11 @@ sub AUTOLOAD { $field =~ s/.*://; if ( defined($value) ) { confess "errant AUTOLOAD $field for $self (arg $value)" - unless ref($self) && $self->can('setfield'); + unless blessed($self) && $self->can('setfield'); $self->setfield($field,$value); } else { confess "errant AUTOLOAD $field for $self (no args)" - unless ref($self) && $self->can('getfield'); + unless blessed($self) && $self->can('getfield'); $self->getfield($field); } } @@ -670,6 +773,68 @@ sub modified { $self->{'modified'}; } +=item select_for_update + +Selects this record with the SQL "FOR UPDATE" command. This can be useful as +a mutex. + +=cut + +sub select_for_update { + my $self = shift; + my $primary_key = $self->primary_key; + qsearchs( { + 'select' => '*', + 'table' => $self->table, + 'hashref' => { $primary_key => $self->$primary_key() }, + 'extra_sql' => 'FOR UPDATE', + } ); +} + +=item lock_table + +Locks this table with a database-driver specific lock method. This is used +as a mutex in order to do a duplicate search. + +For PostgreSQL, does "LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE". + +For MySQL, does a SELECT FOR UPDATE on the duplicate_lock table. + +Errors are fatal; no useful return value. + +Note: To use this method for new tables other than svc_acct and svc_phone, +edit freeside-upgrade and add those tables to the duplicate_lock list. + +=cut + +sub lock_table { + my $self = shift; + my $table = $self->table; + + warn "$me locking $table table\n" if $DEBUG; + + if ( driver_name =~ /^Pg/i ) { + + dbh->do("LOCK TABLE $table IN SHARE ROW EXCLUSIVE MODE") + or die dbh->errstr; + + } elsif ( driver_name =~ /^mysql/i ) { + + dbh->do("SELECT * FROM duplicate_lock + WHERE lockname = '$table' + FOR UPDATE" + ) or die dbh->errstr; + + } else { + + die "unknown database ". driver_name. "; don't know how to lock table"; + + } + + warn "$me acquired $table table lock\n" if $DEBUG; + +} + =item insert Inserts this record to the database. If there is an error, returns the error, @@ -681,12 +846,14 @@ sub insert { my $self = shift; my $saved = {}; + warn "$self -> insert" if $DEBUG; + my $error = $self->check; return $error if $error; #single-field unique keys are given a value if false #(like MySQL's AUTO_INCREMENT or Pg SERIAL) - foreach ( $self->dbdef_table->unique->singles ) { + foreach ( $self->dbdef_table->unique_singles) { $self->unique($_) unless $self->getfield($_); } @@ -710,21 +877,21 @@ sub insert { } my $table = $self->table; - # Encrypt before the database - my $conf = new FS::Conf; - if ($conf->exists('encryption') && defined(eval '@FS::'. $table . 'encrypted_fields')) { + if ( defined(eval '@FS::'. $table . '::encrypted_fields') + && scalar( eval '@FS::'. $table . '::encrypted_fields') + && $conf->exists('encryption') + ) { foreach my $field (eval '@FS::'. $table . '::encrypted_fields') { $self->{'saved'} = $self->getfield($field); - $self->setfield($field, $self->enrypt($self->getfield($field))); + $self->setfield($field, $self->encrypt($self->getfield($field))); } } - #false laziness w/delete my @real_fields = - grep defined($self->getfield($_)) && $self->getfield($_) ne "", + grep { defined($self->getfield($_)) && $self->getfield($_) ne "" } real_fields($table) ; my @values = map { _quote( $self->getfield($_), $table, $_) } @real_fields; @@ -778,8 +945,7 @@ sub insert { dbh->rollback if $FS::UID::AutoCommit; return dbh->errstr; }; - #$i_sth->execute($oid) or do { - $i_sth->execute() or do { + $i_sth->execute() or do { #$i_sth->execute($oid) dbh->rollback if $FS::UID::AutoCommit; return $i_sth->errstr; }; @@ -968,21 +1134,9 @@ returns the error, otherwise returns false. =cut sub replace { - my $new = shift; - my $old = shift; - - if (!defined($old)) { - warn "[debug]$me replace called with no arguments; autoloading old record\n" - if $DEBUG; - my $primary_key = $new->dbdef_table->primary_key; - if ( $primary_key ) { - $old = qsearchs($new->table, { $primary_key => $new->$primary_key() } ) - or croak "can't find ". $new->table. ".$primary_key ". - $new->$primary_key(); - } else { - croak $new->table. " has no primary key; pass old record as argument"; - } - } + my ($new, $old) = (shift, shift); + + $old = $new->replace_old unless defined($old); warn "[debug]$me $new ->replace $old\n" if $DEBUG; @@ -1004,9 +1158,8 @@ sub replace { return $error if $error; # Encrypt for replace - my $conf = new FS::Conf; my $saved = {}; - if ($conf->exists('encryption') && defined(eval '@FS::'. $new->table . 'encrypted_fields')) { + if ($conf->exists('encryption') && defined(eval '@FS::'. $new->table . '::encrypted_fields')) { foreach my $field (eval '@FS::'. $new->table . '::encrypted_fields') { $saved->{$field} = $new->getfield($field); $new->setfield($field, $new->encrypt($new->getfield($field))); @@ -1017,7 +1170,7 @@ sub replace { my %diff = map { ($new->getfield($_) ne $old->getfield($_)) ? ($_, $new->getfield($_)) : () } $old->fields; - unless ( keys(%diff) ) { + unless (keys(%diff) || $no_update_diff ) { carp "[warning]$me $new -> replace $old: records identical" unless $nowarn_identical; return ''; @@ -1152,6 +1305,22 @@ sub replace { } +sub replace_old { + my( $self ) = shift; + warn "[$me] replace called with no arguments; autoloading old record\n" + if $DEBUG; + + my $primary_key = $self->dbdef_table->primary_key; + if ( $primary_key ) { + $self->by_key( $self->$primary_key() ) #this is what's returned + or croak "can't find ". $self->table. ".$primary_key ". + $self->$primary_key(); + } else { + croak $self->table. " has no primary key; pass old record as argument"; + } + +} + =item rep Depriciated (use replace instead). @@ -1196,32 +1365,390 @@ sub check { ''; } -sub _h_statement { - my( $self, $action, $time ) = @_; +=item process_batch_import JOB OPTIONS_HASHREF PARAMS - $time ||= time; +Processes a batch import as a queued JSRPC job - my @fields = - grep defined($self->getfield($_)) && $self->getfield($_) ne "", - real_fields($self->table); - ; - my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields; +JOB is an FS::queue entry. - "INSERT INTO h_". $self->table. " ( ". - join(', ', qw(history_date history_user history_action), @fields ). - ") VALUES (". - join(', ', $time, dbh->quote(getotaker()), dbh->quote($action), @values). - ")" - ; -} +OPTIONS_HASHREF can have the following keys: -=item unique COLUMN +=over 4 -B: External use is B. +=item table -Replaces COLUMN in record with a unique number, using counters in the -filesystem. Used by the B method on single-field unique columns -(see L) and also as a fallback for primary keys +Table name (required). + +=item params + +Listref of field names for static fields. They will be given values from the +PARAMS hashref and passed as a "params" hashref to batch_import. + +=item formats + +Formats hashref. Keys are field names, values are listrefs that define the +format. + +Each listref value can be a column name or a code reference. Coderefs are run +with the row object, data and a FS::Conf object as the three parameters. +For example, this coderef does the same thing as using the "columnname" string: + + sub { + my( $record, $data, $conf ) = @_; + $record->columnname( $data ); + }, + +Coderefs are run after all "column name" fields are assigned. + +=item format_types + +Optional format hashref of types. Keys are field names, values are "csv", +"xls" or "fixedlength". Overrides automatic determination of file type +from extension. + +=item format_headers + +Optional format hashref of header lines. Keys are field names, values are 0 +for no header, 1 to ignore the first line, or to higher numbers to ignore that +number of lines. + +=item format_sep_chars + +Optional format hashref of CSV sep_chars. Keys are field names, values are the +CSV separation character. + +=item format_fixedlenth_formats + +Optional format hashref of fixed length format defintiions. Keys are field +names, values Parse::FixedLength listrefs of field definitions. + +=item default_csv + +Set true to default to CSV file type if the filename does not contain a +recognizable ".csv" or ".xls" extension (and type is not pre-specified by +format_types). + +=back + +PARAMS is a base64-encoded Storable string containing the POSTed data as +a hash ref. It normally contains at least one field, "uploaded files", +generated by /elements/file-upload.html and containing the list of uploaded +files. Currently only supports a single file named "file". + +=cut + +use Storable qw(thaw); +use Data::Dumper; +use MIME::Base64; +sub process_batch_import { + my($job, $opt) = ( shift, shift ); + + my $table = $opt->{table}; + my @pass_params = @{ $opt->{params} }; + my %formats = %{ $opt->{formats} }; + + my $param = thaw(decode_base64(shift)); + warn Dumper($param) if $DEBUG; + + my $files = $param->{'uploaded_files'} + or die "No files provided.\n"; + + my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files; + + 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}, + } ); + + unlink $file; + + die "$error\n" if $error; +} + +=item batch_import PARAM_HASHREF + +Class method for batch imports. Available params: + +=over 4 + +=item table + +=item formats + +=item format_types + +=item format_headers + +=item format_sep_chars + +=item format_fixedlength_formats + +=item params + +=item job + +FS::queue object, will be updated with progress + +=item file + +=item type + +csv, xls or fixedlength + +=item format + +=item empty_ok + +=back + +=cut + +sub batch_import { + my $param = shift; + + warn "$me batch_import call with params: \n". Dumper($param) + 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 = $param->{'format_types'} + ? $param->{'format_types'}{ $format } + : $param->{type} || 'csv'; + + unless ( $type ) { + if ( $file =~ /\.(\w+)$/i ) { + $type = lc($1); + } else { + #or error out??? + warn "can't parse file type from filename $file; defaulting to CSV"; + $type = 'csv'; + } + $type = 'csv' + 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; + my $parser; + my @buffer = (); + if ( $type eq 'csv' || $type eq 'fixedlength' ) { + + if ( $type eq 'csv' ) { + + my %attr = (); + $attr{sep_char} = $sep_char if $sep_char; + $parser = new Text::CSV_XS \%attr; + + } elsif ( $type eq 'fixedlength' ) { + + eval "use Parse::FixedLength;"; + die $@ if $@; + $parser = new Parse::FixedLength $fixedlength_format; + + } else { + die "Unknown file type $type\n"; + } + + @buffer = split(/\r?\n/, slurp($file) ); + splice(@buffer, 0, ($header || 0) ); + $count = scalar(@buffer); + + } elsif ( $type eq 'xls' ) { + + eval "use Spreadsheet::ParseExcel;"; + die $@ if $@; + + eval "use DateTime::Format::Excel;"; + #for now, just let the error be thrown if it is used, since only CDR + # formats bill_west and troop use it, not other excel-parsing things + #die $@ if $@; + + my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file); + + $parser = $excel->{Worksheet}[0]; #first sheet + + $count = $parser->{MaxRow} || $parser->{MinRow}; + $count++; + + $row = $header || 0; + + } else { + die "Unknown file type $type\n"; + } + + #my $columns; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + my $line; + my $imported = 0; + my( $last, $min_sec ) = ( time, 5 ); #progressbar foo + while (1) { + + my @columns = (); + if ( $type eq 'csv' ) { + + last unless scalar(@buffer); + $line = shift(@buffer); + + $parser->parse($line) or do { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $parser->error_input(); + }; + @columns = $parser->fields(); + + } elsif ( $type eq 'fixedlength' ) { + + @columns = $parser->parse($line); + + } elsif ( $type eq 'xls' ) { + + last if $row > ($parser->{MaxRow} || $parser->{MinRow}) + || ! $parser->{Cells}[$row]; + + my @row = @{ $parser->{Cells}[$row] }; + @columns = map $_->{Val}, @row; + + #my $z = 'A'; + #warn $z++. ": $_\n" for @columns; + + } else { + die "Unknown file type $type\n"; + } + + my @later = (); + my %hash = %$params; + + foreach my $field ( @fields ) { + + my $value = shift @columns; + + if ( ref($field) eq 'CODE' ) { + #&{$field}(\%hash, $value); + push @later, $field, $value; + } else { + #??? $hash{$field} = $value if length($value); + $hash{$field} = $value if defined($value) && length($value); + } + + } + + my $class = "FS::$table"; + + my $record = $class->new( \%hash ); + + while ( scalar(@later) ) { + my $sub = shift @later; + my $data = shift @later; + &{$sub}($record, $data, $conf); # $record->&{$sub}($data, $conf); + } + + my $error = $record->insert; + + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert record". ( $line ? " for $line" : '' ). ": $error"; + } + + $row++; + $imported++; + + if ( $job && time - $min_sec > $last ) { #progress bar + $job->update_statustext( int(100 * $imported / $count) ); + $last = time; + } + + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit;; + + return "Empty file!" unless $imported || $param->{empty_ok}; + + ''; #no error + +} + +sub _h_statement { + my( $self, $action, $time ) = @_; + + $time ||= time; + + my @fields = + grep { defined($self->getfield($_)) && $self->getfield($_) ne "" } + real_fields($self->table); + ; + + # If we're encrypting then don't ever store the payinfo or CVV2 in the history.... + # You can see if it changed by the paymask... + if ($conf && $conf->exists('encryption') ) { + @fields = grep $_ ne 'payinfo' && $_ ne 'cvv2', @fields; + } + my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields; + + "INSERT INTO h_". $self->table. " ( ". + join(', ', qw(history_date history_user history_action), @fields ). + ") VALUES (". + join(', ', $time, dbh->quote(getotaker()), dbh->quote($action), @values). + ")" + ; +} + +=item unique COLUMN + +B: External use is B. + +Replaces COLUMN in record with a unique number, using counters in the +filesystem. Used by the B method on single-field unique columns +(see L) and also as a fallback for primary keys that aren't SERIAL (Pg) or AUTO_INCREMENT (mysql). Returns the new value. @@ -1266,30 +1793,101 @@ null. If there is an error, returns the error, otherwise returns false. sub ut_float { my($self,$field)=@_ ; - ($self->getfield($field) =~ /^(\d+\.\d+)$/ || - $self->getfield($field) =~ /^(\d+)$/ || - $self->getfield($field) =~ /^(\d+\.\d+e\d+)$/ || - $self->getfield($field) =~ /^(\d+e\d+)$/) + ($self->getfield($field) =~ /^\s*(\d+\.\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(\d+\.\d+e\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(\d+e\d+)\s*$/) or return "Illegal or empty (float) $field: ". $self->getfield($field); $self->setfield($field,$1); ''; } +=item ut_floatn COLUMN + +Check/untaint floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be +null. If there is an error, returns the error, otherwise returns false. + +=cut + +#false laziness w/ut_ipn +sub ut_floatn { + my( $self, $field ) = @_; + if ( $self->getfield($field) =~ /^()$/ ) { + $self->setfield($field,''); + ''; + } else { + $self->ut_float($field); + } +} + +=item ut_sfloat COLUMN + +Check/untaint signed floating point numeric data: 1.1, 1, 1.1e10, 1e10. +May not be null. If there is an error, returns the error, otherwise returns +false. + +=cut + +sub ut_sfloat { + my($self,$field)=@_ ; + ($self->getfield($field) =~ /^\s*(-?\d+\.\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(-?\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(-?\d+\.\d+[eE]-?\d+)\s*$/ || + $self->getfield($field) =~ /^\s*(-?\d+[eE]-?\d+)\s*$/) + or return "Illegal or empty (float) $field: ". $self->getfield($field); + $self->setfield($field,$1); + ''; +} +=item ut_sfloatn COLUMN + +Check/untaint signed floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be +null. If there is an error, returns the error, otherwise returns false. + +=cut + +sub ut_sfloatn { + my( $self, $field ) = @_; + if ( $self->getfield($field) =~ /^()$/ ) { + $self->setfield($field,''); + ''; + } else { + $self->ut_sfloat($field); + } +} =item ut_snumber COLUMN -Check/untaint signed numeric data (whole numbers). May not be null. If there -is an error, returns the error, otherwise returns false. +Check/untaint signed numeric data (whole numbers). If there is an error, +returns the error, otherwise returns false. =cut sub ut_snumber { my($self, $field) = @_; - $self->getfield($field) =~ /^(-?)\s*(\d+)$/ + $self->getfield($field) =~ /^\s*(-?)\s*(\d+)\s*$/ or return "Illegal or empty (numeric) $field: ". $self->getfield($field); $self->setfield($field, "$1$2"); ''; } +=item ut_snumbern COLUMN + +Check/untaint signed numeric data (whole numbers). If there is an error, +returns the error, otherwise returns false. + +=cut + +sub ut_snumbern { + my($self, $field) = @_; + $self->getfield($field) =~ /^\s*(-?)\s*(\d*)\s*$/ + or return "Illegal (numeric) $field: ". $self->getfield($field); + if ($1) { + return "Illegal (numeric) $field: ". $self->getfield($field) + unless $2; + } + $self->setfield($field, "$1$2"); + ''; +} + =item ut_number COLUMN Check/untaint simple numeric data (whole numbers). May not be null. If there @@ -1299,7 +1897,7 @@ is an error, returns the error, otherwise returns false. sub ut_number { my($self,$field)=@_; - $self->getfield($field) =~ /^(\d+)$/ + $self->getfield($field) =~ /^\s*(\d+)\s*$/ or return "Illegal or empty (numeric) $field: ". $self->getfield($field); $self->setfield($field,$1); ''; @@ -1314,7 +1912,7 @@ an error, returns the error, otherwise returns false. sub ut_numbern { my($self,$field)=@_; - $self->getfield($field) =~ /^(\d*)$/ + $self->getfield($field) =~ /^\s*(\d*)\s*$/ or return "Illegal (numeric) $field: ". $self->getfield($field); $self->setfield($field,$1); ''; @@ -1330,7 +1928,7 @@ is an error, returns the error, otherwise returns false. sub ut_money { my($self,$field)=@_; $self->setfield($field, 0) if $self->getfield($field) eq ''; - $self->getfield($field) =~ /^(\-)? ?(\d*)(\.\d{2})?$/ + $self->getfield($field) =~ /^\s*(\-)?\s*(\d*)(\.\d{2})?\s*$/ or return "Illegal (money) $field: ". $self->getfield($field); #$self->setfield($field, "$1$2$3" || 0); $self->setfield($field, ( ($1||''). ($2||''). ($3||'') ) || 0); @@ -1407,6 +2005,20 @@ sub ut_alphan { ''; } +=item ut_alpha_lower COLUMN + +Check/untaint lowercase alphanumeric strings (no spaces). May not be null. If +there is an error, returns the error, otherwise returns false. + +=cut + +sub ut_alpha_lower { + my($self,$field)=@_; + $self->getfield($field) =~ /[[:upper:]]/ + and return "Uppercase characters are not permitted in $field"; + $self->ut_alpha($field); +} + =item ut_phonen COLUMN [ COUNTRY ] Check/untaint phone numbers. May be null. If there is an error, returns @@ -1425,6 +2037,8 @@ sub ut_phonen { $self->setfield($field,''); } elsif ( $country eq 'US' || $country eq 'CA' ) { $phonen =~ s/\D//g; + $phonen = $conf->config('cust_main-default_areacode').$phonen + if length($phonen)==7 && $conf->config('cust_main-default_areacode'); $phonen =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ or return gettext('illegal_phone'). " $field: ". $self->getfield($field); $phonen = "$1-$2-$3"; @@ -1437,6 +2051,33 @@ sub ut_phonen { ''; } +=item ut_hex COLUMN + +Check/untaint hexadecimal values. + +=cut + +sub ut_hex { + my($self, $field) = @_; + $self->getfield($field) =~ /^([\da-fA-F]+)$/ + or return "Illegal (hex) $field: ". $self->getfield($field); + $self->setfield($field, uc($1)); + ''; +} + +=item ut_hexn COLUMN + +Check/untaint hexadecimal values. May be null. + +=cut + +sub ut_hexn { + my($self, $field) = @_; + $self->getfield($field) =~ /^([\da-fA-F]*)$/ + or return "Illegal (hex) $field: ". $self->getfield($field); + $self->setfield($field, uc($1)); + ''; +} =item ut_ip COLUMN Check/untaint ip addresses. IPv4 only for now. @@ -1468,6 +2109,92 @@ sub ut_ipn { } } +=item ut_coord COLUMN [ LOWER [ UPPER ] ] + +Check/untaint coordinates. +Accepts the following forms: +DDD.DDDDD +-DDD.DDDDD +DDD MM.MMM +-DDD MM.MMM +DDD MM SS +-DDD MM SS +DDD MM MMM +-DDD MM MMM + +The "DDD MM SS" and "DDD MM MMM" are potentially ambiguous. +The latter form (that is, the MMM are thousands of minutes) is +assumed if the "MMM" is exactly three digits or two digits > 59. + +To be safe, just use the DDD.DDDDD form. + +If LOWER or UPPER are specified, then the coordinate is checked +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 $coord = $self->getfield($field); + my $neg = $coord =~ s/^(-)//; + + my ($d, $m, $s) = (0, 0, 0); + + if ( + (($d) = ($coord =~ /^(\s*\d{1,3}(?:\.\d+)?)\s*$/)) || + (($d, $m) = ($coord =~ /^(\s*\d{1,3})\s+(\d{1,2}(?:\.\d+))\s*$/)) || + (($d, $m, $s) = ($coord =~ /^(\s*\d{1,3})\s+(\d{1,2})\s+(\d{1,3})\s*$/)) + ) { + $s = (((($s =~ /^\d{3}$/) or $s > 59) ? ($s / 1000) : ($s / 60)) / 60); + $m = $m / 60; + if ($m > 59) { + return "Invalid (coordinate with minutes > 59) $field: " + . $self->getfield($field); + } + + $coord = ($neg ? -1 : 1) * sprintf('%.8f', $d + $m + $s); + + if (defined($lower) and ($coord < $lower)) { + return "Invalid (coordinate < $lower) $field: " + . $self->getfield($field);; + } + + if (defined($upper) and ($coord > $upper)) { + return "Invalid (coordinate > $upper) $field: " + . $self->getfield($field);; + } + + $self->setfield($field, $coord); + return ''; + } + + return "Invalid (coordinate) $field: " . $self->getfield($field); + +} + +=item ut_coordn COLUMN [ LOWER [ UPPER ] ] + +Same as ut_coord, except optionally null. + +=cut + +sub ut_coordn { + + my ($self, $field) = (shift, shift); + + if ($self->getfield($field) =~ /^$/) { + return ''; + } else { + return $self->ut_coord($field, @_); + } + +} + + =item ut_domain COLUMN Check/untaint host and domain names. @@ -1506,7 +2233,7 @@ Check/untaint zip codes. =cut -my @zip_reqd_countries = qw( CA ); #US implicit... +my @zip_reqd_countries = qw( AU CA US ); #CA, US implicit... sub ut_zip { my( $self, $field, $country ) = @_; @@ -1604,6 +2331,7 @@ on the column first. sub ut_foreign_key { my( $self, $field, $table, $foreign ) = @_; + return '' if $no_check_foreign; qsearchs($table, { $foreign => $self->getfield($field) }) or return "Can't find ". $self->table. ".$field ". $self->getfield($field). " in $table.$foreign"; @@ -1623,15 +2351,18 @@ sub ut_foreign_keyn { : ''; } -=item ut_agentnum_acl +=item ut_agentnum_acl COLUMN [ NULL_RIGHT | NULL_RIGHT_LISTREF ] Checks this column as an agentnum, taking into account the current users's -ACLs. +ACLs. NULL_RIGHT or NULL_RIGHT_LISTREF, if specified, indicates the access +right or rights allowing no agentnum. =cut sub ut_agentnum_acl { - my( $self, $field, $null_acl ) = @_; + my( $self, $field ) = (shift, shift); + my $null_acl = scalar(@_) ? shift : []; + $null_acl = [ $null_acl ] unless ref($null_acl); my $error = $self->ut_foreign_keyn($field, 'agent', 'agentnum'); return "Illegal agentnum: $error" if $error; @@ -1640,13 +2371,13 @@ sub ut_agentnum_acl { if ( $self->$field() ) { - return "Access deined" + return "Access denied" unless $curuser->agentnum($self->$field()); } else { return "Access denied" - unless $curuser->access_right($null_acl); + unless grep $curuser->access_right($_), @$null_acl; } @@ -1704,8 +2435,6 @@ sub fields { return (real_fields($table), $something->virtual_fields()); } -=back - =item pvf FIELD_NAME Returns the FS::part_virtual_field object corresponding to a field in the @@ -1723,59 +2452,6 @@ sub pvf { '' } -=head1 SUBROUTINES - -=over 4 - -=item real_fields [ TABLE ] - -Returns a list of the real columns in the specified table. Called only by -fields() and other subroutines elsewhere in FS::Record. - -=cut - -sub real_fields { - my $table = shift; - - my($table_obj) = dbdef->table($table); - confess "Unknown table $table" unless $table_obj; - $table_obj->columns; -} - -=item _quote VALUE, TABLE, COLUMN - -This is an internal function used to construct SQL statements. It returns -VALUE DBI-quoted (see L) unless VALUE is a number and the column -type (see L) does not end in `char' or `binary'. - -=cut - -sub _quote { - my($value, $table, $column) = @_; - my $column_obj = dbdef->table($table)->column($column); - my $column_type = $column_obj->type; - my $nullable = $column_obj->null; - - warn " $table.$column: $value ($column_type". - ( $nullable ? ' NULL' : ' NOT NULL' ). - ")\n" if $DEBUG > 2; - - if ( $value eq '' && $column_type =~ /^(int|numeric)/ ) { - if ( $nullable ) { - '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); - } -} - =item vfieldpart_hashref TABLE Returns a hashref of virtual field names and vfieldparts applicable to the given @@ -1799,37 +2475,20 @@ sub vfieldpart_hashref { } +=item encrypt($value) -=item hfields TABLE +Encrypts the credit card using a combination of PK to encrypt and uuencode to armour. -This is deprecated. Don't use it. +Returns the encrypted string. -It returns a hash-type list with the fields of this record's table set true. +You should generally not have to worry about calling this, as the system handles this for you. =cut -sub hfields { - carp "warning: hfields is deprecated"; - my($table)=@_; - my(%hash); - foreach (fields($table)) { - $hash{$_}=1; - } - \%hash; -} - -sub _dump { - my($self)=@_; - join("\n", map { - "$_: ". $self->getfield($_). "|" - } (fields($self->table)) ); -} - sub encrypt { my ($self, $value) = @_; my $encrypted; - my $conf = new FS::Conf; if ($conf->exists('encryption')) { if ($self->is_encrypted($value)) { # Return the original value if it isn't plaintext. @@ -1849,26 +2508,41 @@ sub encrypt { return $encrypted; } +=item is_encrypted($value) + +Checks to see if the string is encrypted and returns true or false (1/0) to indicate it's status. + +=cut + + sub is_encrypted { my ($self, $value) = @_; # Possible Bug - Some work may be required here.... - if (length($value) > 80) { + if ($value =~ /^M/ && length($value) > 80) { return 1; } else { return 0; } } +=item decrypt($value) + +Uses the private key to decrypt the string. Returns the decryoted string or undef on failure. + +You should generally not have to worry about calling this, as the system handles this for you. + +=cut + sub decrypt { my ($self,$value) = @_; my $decrypted = $value; # Will return the original value if it isn't encrypted or can't be decrypted. - my $conf = new FS::Conf; if ($conf->exists('encryption') && $self->is_encrypted($value)) { $self->loadRSA; if (ref($rsa_decrypt) =~ /::RSA/) { my $encrypted = unpack ("u*", $value); - $decrypted = unpack("Z*", $rsa_decrypt->decrypt($encrypted)); + $decrypted = unpack("Z*", eval{$rsa_decrypt->decrypt($encrypted)}); + if ($@) {warn "Decryption Failed"}; } } return $decrypted; @@ -1879,8 +2553,7 @@ sub loadRSA { #Initialize the Module $rsa_module = 'Crypt::OpenSSL::RSA'; # The Default - my $conf = new FS::Conf; - if ($conf->exists('encryptionmodule') && $conf->config('encryptionmodule') ne '') { + if ($conf->exists('encryptionmodule') && $conf->config_binary('encryptionmodule') ne '') { $rsa_module = $conf->config('encryptionmodule'); } @@ -1889,18 +2562,133 @@ sub loadRSA { $rsa_loaded++; } # Initialize Encryption - if ($conf->exists('encryptionpublickey') && $conf->config('encryptionpublickey') ne '') { + if ($conf->exists('encryptionpublickey') && $conf->config_binary('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('encryptionprivatekey') ne '') { + if ($conf->exists('encryptionprivatekey') && $conf->config_binary('encryptionprivatekey') ne '') { my $private_key = join("\n",$conf->config('encryptionprivatekey')); $rsa_decrypt = $rsa_module->new_private_key($private_key); } } +=item h_search ACTION + +Given an ACTION, either "insert", or "delete", returns the appropriate history +record corresponding to this record, if any. + +=cut + +sub h_search { + my( $self, $action ) = @_; + + my $table = $self->table; + $table =~ s/^h_//; + + my $primary_key = dbdef->table($table)->primary_key; + + qsearchs({ + 'table' => "h_$table", + 'hashref' => { $primary_key => $self->$primary_key(), + 'history_action' => $action, + }, + }); + +} + +=item h_date ACTION + +Given an ACTION, either "insert", or "delete", returns the timestamp of the +appropriate history record corresponding to this record, if any. + +=cut + +sub h_date { + my($self, $action) = @_; + my $h = $self->h_search($action); + $h ? $h->history_date : ''; +} + +=back + +=head1 SUBROUTINES + +=over 4 + +=item real_fields [ TABLE ] + +Returns a list of the real columns in the specified table. Called only by +fields() and other subroutines elsewhere in FS::Record. + +=cut + +sub real_fields { + my $table = shift; + + my($table_obj) = dbdef->table($table); + confess "Unknown table $table" unless $table_obj; + $table_obj->columns; +} + +=item _quote VALUE, TABLE, COLUMN + +This is an internal function used to construct SQL statements. It returns +VALUE DBI-quoted (see L) unless VALUE is a number and the column +type (see L) does not end in `char' or `binary'. + +=cut + +sub _quote { + my($value, $table, $column) = @_; + my $column_obj = dbdef->table($table)->column($column); + my $column_type = $column_obj->type; + my $nullable = $column_obj->null; + + warn " $table.$column: $value ($column_type". + ( $nullable ? ' NULL' : ' NOT NULL' ). + ")\n" if $DEBUG > 2; + + if ( $value eq '' && $nullable ) { + 'NULL' + } elsif ( $value eq '' && $column_type =~ /^(int|numeric)/ ) { + 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); + } +} + +=item hfields TABLE + +This is deprecated. Don't use it. + +It returns a hash-type list with the fields of this record's table set true. + +=cut + +sub hfields { + carp "warning: hfields is deprecated"; + my($table)=@_; + my(%hash); + foreach (fields($table)) { + $hash{$_}=1; + } + \%hash; +} + +sub _dump { + my($self)=@_; + join("\n", map { + "$_: ". $self->getfield($_). "|" + } (fields($self->table)) ); +} + sub DESTROY { return; } #sub DESTROY { @@ -1914,6 +2702,49 @@ sub DESTROY { return; } # return ! eval { join('',@_), kill 0; 1; }; # } +=item str2time_sql [ DRIVER_NAME ] + +Returns a function to convert to unix time based on database type, such as +"EXTRACT( EPOCH FROM" for Pg or "UNIX_TIMESTAMP(" for mysql. See +the str2time_sql_closing method to return a closing string rather than just +using a closing parenthesis as previously suggested. + +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 str2time_sql { + my $driver = shift || driver_name; + + return 'UNIX_TIMESTAMP(' if $driver =~ /^mysql/i; + return 'EXTRACT( EPOCH FROM ' if $driver =~ /^Pg/i; + + warn "warning: unknown database type $driver; guessing how to convert ". + "dates to UNIX timestamps"; + return 'EXTRACT(EPOCH FROM '; + +} + +=item str2time_sql_closing [ DRIVER_NAME ] + +Returns the closing suffix of a function to convert to unix time based on +database type, such as ")::integer" for Pg or ")" 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 str2time_sql_closing { + my $driver = shift || driver_name; + + return ' )::INTEGER ' if $driver =~ /^Pg/i; + return ' ) '; +} + =back =head1 BUGS