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;
@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 regexp_sql not_regexp_sql );
+@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 @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;
$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');
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,
params => { map { $_ => $param->{$_} } @pass_params },
#?
default_csv => $opt->{default_csv},
+ postinsert_callback => $opt->{postinsert_callback},
);
if ( $opt->{'batch_namecol'} ) {
=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_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
=item type
-csv, xls or fixedlength
-
-=item format
+csv, xls, fixedlength, xml
=item empty_ok
my $param = shift;
warn "$me batch_import call with params: \n". Dumper($param)
- ;# if $DEBUG;
+ 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 ) {
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 $row_callback =
- $param->{'format_row_callbacks'}
- ? $param->{'format_row_callbacks'}{ $param->{'format'} }
- : '';
-
- my @fields = @{ $formats->{ $format } };
my $row = 0;
my $count;
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";
}
$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";
}
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'};
$params->{ $batch_col } = $batch_value;
}
-
+
+ #my $job = $param->{job};
my $line;
my $imported = 0;
my( $last, $min_sec ) = ( time, 5 ); #progressbar foo
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' ) {
#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} );
+ 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 ) {
$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
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_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.
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);
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)
$h ? $h->history_date : '';
}
-=item scalar_sql SQL
+=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.
-A class method with a propensity for becoming an instance method. This
-method executes the sql statement represented by SQL and returns a scalar
-representing the result. Don't ask for rows -- you get the first column
-of the first row. Don't give me bogus SQL or I'll die on you.
+Dies on bogus SQL. Returns an empty string if no row is returned.
-Returns an empty string in the event of no rows.
+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($self, $sql) = (shift, shift);
my $sth = dbh->prepare($sql) or die dbh->errstr;
- $sth->execute
+ $sth->execute(@_)
or die "Unexpected error executing statement $sql: ". $sth->errstr;
- $sth->fetchrow_arrayref->[0] || '';
+ my $row = $sth->fetchrow_arrayref or return '';
+ my $scalar = $row->[0];
+ defined($scalar) ? $scalar : '';
}
=back
}
+=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
projects / freeside.git / blobdiff