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);
@ISA = qw(Exporter);
#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]';
my @records = qsearch( 'table', { 'field' => 'value' } );
+Also possible is an experimental LISTREF of PARAMS_HASHREFs for a UNION of
+the individual PARAMS_HASHREF queries
+
###oops, argh, FS::Record::new only lets us create database fields.
#Normal behaviour if SELECT is not specified is `*', as in
#C<SELECT * FROM table WHERE ...>. However, there is an experimental new
}
sub qsearch {
- my($stable, $record, $cache );
- my( $select, $extra_sql, $extra_param, $order_by, $addl_from );
- my $debug = '';
- if ( ref($_[0]) ) { #hashref for now, eventually maybe accept a list too
+ my( @stable, @record, @cache );
+ my( @select, @extra_sql, @extra_param, @order_by, @addl_from );
+ my @debug = ();
+ my %union_options = ();
+ if ( ref($_[0]) eq 'ARRAY' ) {
+ my $optlist = shift;
+ %union_options = @_;
+ foreach my $href ( @$optlist ) {
+ push @stable, ( $href->{'table'} or die "table name is required" );
+ push @record, ( $href->{'hashref'} || {} );
+ push @select, ( $href->{'select'} || '*' );
+ push @extra_sql, ( $href->{'extra_sql'} || '' );
+ push @extra_param, ( $href->{'extra_param'} || [] );
+ push @order_by, ( $href->{'order_by'} || '' );
+ push @cache, ( $href->{'cache_obj'} || '' );
+ push @addl_from, ( $href->{'addl_from'} || '' );
+ push @debug, ( $href->{'debug'} || '' );
+ }
+ die "at least one hashref is required" unless scalar(@stable);
+ } elsif ( ref($_[0]) eq 'HASH' ) {
my $opt = shift;
- $stable = $opt->{'table'} or die "table name is required";
- $record = $opt->{'hashref'} || {};
- $select = $opt->{'select'} || '*';
- $extra_sql = $opt->{'extra_sql'} || '';
- $extra_param = $opt->{'extra_param'} || [];
- $order_by = $opt->{'order_by'} || '';
- $cache = $opt->{'cache_obj'} || '';
- $addl_from = $opt->{'addl_from'} || '';
- $debug = $opt->{'debug'} || '';
+ $stable[0] = $opt->{'table'} or die "table name is required";
+ $record[0] = $opt->{'hashref'} || {};
+ $select[0] = $opt->{'select'} || '*';
+ $extra_sql[0] = $opt->{'extra_sql'} || '';
+ $extra_param[0] = $opt->{'extra_param'} || [];
+ $order_by[0] = $opt->{'order_by'} || '';
+ $cache[0] = $opt->{'cache_obj'} || '';
+ $addl_from[0] = $opt->{'addl_from'} || '';
+ $debug[0] = $opt->{'debug'} || '';
} else {
- ($stable, $record, $select, $extra_sql, $cache, $addl_from ) = @_;
- $select ||= '*';
+ ( $stable[0],
+ $record[0],
+ $select[0],
+ $extra_sql[0],
+ $cache[0],
+ $addl_from[0]
+ ) = @_;
+ $select[0] ||= '*';
}
+ my $cache = $cache[0];
- #$stable =~ /^([\w\_]+)$/ or die "Illegal table: $table";
- #for jsearch
- $stable =~ /^([\w\s\(\)\.\,\=]+)$/ or die "Illegal table: $stable";
- $stable = $1;
+ my @statement = ();
+ my @value = ();
+ my @bind_type = ();
my $dbh = dbh;
+ foreach my $stable ( @stable ) {
+ #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;
+ my $order_by = shift @order_by;
+ my $cache = shift @cache;
+ my $addl_from = shift @addl_from;
+ my $debug = shift @debug;
+
+ #$stable =~ /^([\w\_]+)$/ or die "Illegal table: $table";
+ #for jsearch
+ $stable =~ /^([\w\s\(\)\.\,\=]+)$/ or die "Illegal table: $stable";
+ $stable = $1;
+
+ my $table = $cache ? $cache->table : $stable;
+ my $dbdef_table = dbdef->table($table)
+ or die "No schema for table $table found - ".
+ "do you need to run freeside-upgrade?";
+ my $pkey = $dbdef_table->primary_key;
+
+ my @real_fields = grep exists($record->{$_}), real_fields($table);
+ my @virtual_fields;
+ if ( eval 'scalar(@FS::'. $table. '::ISA);' ) {
+ @virtual_fields = grep exists($record->{$_}), "FS::$table"->virtual_fields;
+ } else {
+ cluck "warning: FS::$table not loaded; virtual fields not searchable"
+ unless $nowarn_classload;
+ @virtual_fields = ();
+ }
- my $table = $cache ? $cache->table : $stable;
- my $dbdef_table = dbdef->table($table)
- or die "No schema for table $table found - ".
- "do you need to run freeside-upgrade?";
- my $pkey = $dbdef_table->primary_key;
+ my $statement .= "SELECT $select FROM $stable";
+ $statement .= " $addl_from" if $addl_from;
+ if ( @real_fields or @virtual_fields ) {
+ $statement .= ' WHERE '. join(' AND ',
+ get_real_fields($table, $record, \@real_fields) ,
+ get_virtual_fields($table, $pkey, $record, \@virtual_fields),
+ );
+ }
- my @real_fields = grep exists($record->{$_}), real_fields($table);
- my @virtual_fields;
- if ( eval 'scalar(@FS::'. $table. '::ISA);' ) {
- @virtual_fields = grep exists($record->{$_}), "FS::$table"->virtual_fields;
- } else {
- cluck "warning: FS::$table not loaded; virtual fields not searchable"
- unless $nowarn_classload;
- @virtual_fields = ();
- }
+ $statement .= " $extra_sql" if defined($extra_sql);
+ $statement .= " $order_by" if defined($order_by);
- my $statement = "SELECT $select FROM $stable";
- $statement .= " $addl_from" if $addl_from;
- if ( @real_fields or @virtual_fields ) {
- $statement .= ' WHERE '. join(' AND ',
- get_real_fields($table, $record, \@real_fields) ,
- get_virtual_fields($table, $pkey, $record, \@virtual_fields),
- );
- }
+ push @statement, $statement;
- $statement .= " $extra_sql" if defined($extra_sql);
- $statement .= " $order_by" if defined($order_by);
+ warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug;
+
- warn "[debug]$me $statement\n" if $DEBUG > 1 || $debug;
- my $sth = $dbh->prepare($statement)
- or croak "$dbh->errstr doing $statement";
+ foreach my $field (
+ grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields
+ ) {
- my $bind = 1;
+ 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;
- foreach my $field (
- grep defined( $record->{$_} ) && $record->{$_} ne '', @real_fields
- ) {
+ my $bind_type = _bind_type($type, $value);
- 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 $bind_type = _bind_type($type, $value);
-
- #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, $bind_type );
- #}
+ #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";
+ #}
- }
+ push @value, $value;
+ push @bind_type, $bind_type;
- foreach my $param ( @$extra_param ) {
- my $bind_type = { TYPE => SQL_VARCHAR };
- my $value = $param;
- if ( ref($param) ) {
- $value = $param->[0];
- my $type = $param->[1];
- $bind_type = _bind_type($type, $value);
}
+
+ foreach my $param ( @$extra_param ) {
+ my $bind_type = { TYPE => SQL_VARCHAR };
+ my $value = $param;
+ if ( ref($param) ) {
+ $value = $param->[0];
+ my $type = $param->[1];
+ $bind_type = _bind_type($type, $value);
+ }
+ push @value, $value;
+ push @bind_type, $bind_type;
+ }
+ }
+
+ my $statement = join( ' ) UNION ( ', @statement );
+ $statement = "( $statement )" if scalar(@statement) > 1;
+ $statement .= " $union_options{order_by}" if $union_options{order_by};
+
+ my $sth = $dbh->prepare($statement)
+ or croak "$dbh->errstr doing $statement";
+
+ my $bind = 1;
+ foreach my $value ( @value ) {
+ my $bind_type = shift @bind_type;
$sth->bind_param($bind++, $value, $bind_type );
}
$sth->execute or croak "Error executing \"$statement\": ". $sth->errstr;
+ # virtual fields and blessings are nonsense in a heterogeneous UNION, right?
+ my $table = $stable[0];
+ my $pkey = '';
+ $table = '' if grep { $_ ne $table } @stable;
+ $pkey = dbdef->table($table)->primary_key if $table;
+
+ my @virtual_fields = ();
if ( eval 'scalar(@FS::'. $table. '::ISA);' ) {
@virtual_fields = "FS::$table"->virtual_fields;
} else {
$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');
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)
#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".
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));
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;
=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
=item format_fixedlength_formats
+=item format_row_callbacks
+
+=item fields - Alternate way to specify import, specifying import fields directly as a listref
+
+=item postinsert_callback
+
=item params
=item job
=item type
-csv, xls or fixedlength
-
-=item format
+csv, xls, fixedlength, xml
=item empty_ok
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'};
+ 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;
- my $type = $param->{'format_types'}
- ? $param->{'format_types'}{ $format }
- : $param->{type} || 'csv';
+ $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 ) {
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;
eval "use Parse::FixedLength;";
die $@ if $@;
$parser = new Parse::FixedLength $fixedlength_format;
-
- } else {
+
+ }
+ else {
die "Unknown file type $type\n";
}
$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";
}
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
last unless scalar(@buffer);
$line = shift(@buffer);
+ next if $line =~ /^\s*$/; #skip empty lines
+
+ $line = &{$row_callback}($line) if $row_callback;
+
$parser->parse($line) or do {
$dbh->rollback if $oldAutoCommit;
return "can't parse: ". $parser->error_input();
#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";
}
}
+ #my $table = $param->{table};
my $class = "FS::$table";
my $record = $class->new( \%hash );
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} );
$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;
}
- $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
#warn "notexist ". \¬exist. "\n";
#warn "AUTOLOAD ". \&AUTOLOAD. "\n";
$self->getfield($field)
- =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>]+)$/
+ =~ /^([µ_0123456789aAáÁàÀâÂåÅäÄãêæÆbBcCçÇdDðÐeEéÉèÈêÊëËfFgGhHiIíÍìÌîÎïÏjJkKlLmMnNñÑoOóÓòÒôÔöÖõÕøغpPqQrRsSßtTuUúÚùÙûÛüÜvVwWxXyYýÝÿzZþÞ \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>]+)$/
or return gettext('illegal_or_empty_text'). " $field: ".
$self->getfield($field);
$self->setfield($field,$1);
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
'';
}
-=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.
'';
}
+=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
}
=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; }
=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
sub ut_name {
my( $self, $field ) = @_;
- $self->getfield($field) =~ /^([\w \,\.\-\']+)$/
+# warn "ut_name allowed alphanumerics: +(sort grep /\w/, map { chr() } 0..255), "\n";
+ #$self->getfield($field) =~ /^([\w \,\.\-\']+)$/
+ $self->getfield($field) =~ /^([µ_0123456789aAáÁàÀâÂåÅäÄãêæÆbBcCçÇdDðÐeEéÉèÈêÊëËfFgGhHiIíÍìÌîÎïÏjJkKlLmMnNñÑoOóÓòÒôÔöÖõÕøغpPqQrRsSßtTuUúÚùÙûÛüÜvVwWxXyYýÝÿzZþÞ \,\.\-\']+)$/
or return gettext('illegal_name'). " $field: ". $self->getfield($field);
$self->setfield($field,$1);
'';
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)
#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');
}
$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);
}
$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
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