#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 concat_sql
+ str2time_sql str2time_sql_closing regexp_sql not_regexp_sql
+ concat_sql group_concat_sql
midnight_sql
);
$error = $record->ut_floatn('column');
$error = $record->ut_number('column');
$error = $record->ut_numbern('column');
+ $error = $record->ut_decimal('column');
+ $error = $record->ut_decimaln('column');
$error = $record->ut_snumber('column');
$error = $record->ut_snumbern('column');
$error = $record->ut_money('column');
=item params
-Listref of field names for static fields. They will be given values from the
+Arrayref 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
=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".
+PARAMS is a hashref (or base64-encoded Storable hashref) containing the
+POSTed data. It must contain the 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
+# uploaded_files is kind of bizarre; fix that some time
+
use Storable qw(thaw);
use Data::Dumper;
use MIME::Base64;
my @pass_params = $opt->{params} ? @{ $opt->{params} } : ();
my %formats = %{ $opt->{formats} };
- my $param = thaw(decode_base64(shift));
+ my $param = shift;
+ # because some job-spawning code (JSRPC) pre-freezes the arguments,
+ # and then the 'frozen' attribute doesn't get set, and thus $job->args
+ # doesn't know to thaw them, we have to do this everywhere.
+ if (!ref $param) {
+ $param = thaw(decode_base64($param));
+ }
warn Dumper($param) if $DEBUG;
my $files = $param->{'uploaded_files'}
my( $type, $header, $sep_char,
$fixedlength_format, $xml_format, $asn_format,
- $row_callback, @fields );
+ $parser_opt, $row_callback, @fields );
my $postinsert_callback = '';
$postinsert_callback = $param->{'postinsert_callback'}
? $param->{'format_fixedlength_formats'}{ $param->{'format'} }
: '';
+ $parser_opt =
+ $param->{'format_parser_opts'}
+ ? $param->{'format_parser_opts'}{ $param->{'format'} }
+ : {};
+
$xml_format =
$param->{'format_xml_formats'}
? $param->{'format_xml_formats'}{ $param->{'format'} }
if ( $type eq 'csv' ) {
- my %attr = ( 'binary' => 1, );
- $attr{sep_char} = $sep_char if $sep_char;
- $parser = new Text::CSV_XS \%attr;
+ $parser_opt->{'binary'} = 1;
+ $parser_opt->{'sep_char'} = $sep_char if $sep_char;
+ $parser = Text::CSV_XS->new($parser_opt);
} elsif ( $type eq 'fixedlength' ) {
eval "use Parse::FixedLength;";
die $@ if $@;
- $parser = Parse::FixedLength->new($fixedlength_format);
+ $parser = Parse::FixedLength->new($fixedlength_format, $parser_opt);
- }
- else {
+ } else {
die "Unknown file type $type\n";
}
'';
}
+=item ut_decimal COLUMN[, DIGITS]
+
+Check/untaint decimal numbers (up to DIGITS decimal places. If there is an
+error, returns the error, otherwise returns false.
+
+=item ut_decimaln COLUMN[, DIGITS]
+
+Check/untaint decimal numbers. May be null. If there is an error, returns
+the error, otherwise returns false.
+
+=cut
+
+sub ut_decimal {
+ my($self, $field, $digits) = @_;
+ $digits ||= '';
+ $self->getfield($field) =~ /^\s*(\d+(\.\d{0,$digits})?)\s*$/
+ or return "Illegal or empty (decimal) $field: ".$self->getfield($field);
+ $self->setfield($field, $1);
+ '';
+}
+
+sub ut_decimaln {
+ my($self, $field, $digits) = @_;
+ $self->getfield($field) =~ /^\s*(\d*(\.\d{0,$digits})?)\s*$/
+ or return "Illegal (decimal) $field: ".$self->getfield($field);
+ $self->setfield($field, $1);
+ '';
+}
+
=item ut_money COLUMN
Check/untaint monetary numbers. May be negative. Set to 0 if null. If there
#warn "msgcat ". \&msgcat. "\n";
#warn "notexist ". \¬exist. "\n";
#warn "AUTOLOAD ". \&AUTOLOAD. "\n";
+ # \p{Word} = alphanumerics, marks (diacritics), and connectors
+ # see perldoc perluniprops
$self->getfield($field)
- =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/
+ =~ /^([\p{Word} \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>$money_char]+)$/
or return gettext('illegal_or_empty_text'). " $field: ".
$self->getfield($field);
$self->setfield($field,$1);
Check/untaint phone numbers. May be null. If there is an error, returns
the error, otherwise returns false.
-Takes an optional two-letter ISO country code; without it or with unsupported
-countries, ut_phonen simply calls ut_alphan.
+Takes an optional two-letter ISO 3166-1 alpha-2 country code; without
+it or with unsupported countries, ut_phonen simply calls ut_alphan.
=cut
sub ut_name {
my( $self, $field ) = @_;
# warn "ut_name allowed alphanumerics: +(sort grep /\w/, map { chr() } 0..255), "\n";
- $self->getfield($field) =~ /^([\w \,\.\-\']+)$/
+ $self->getfield($field) =~ /^([\p{Word} \,\.\-\']+)$/
or return gettext('illegal_name'). " $field: ". $self->getfield($field);
my $name = $1;
$name =~ s/^\s+//;
defined($scalar) ? $scalar : '';
}
-=item count [ WHERE ]
+=item count [ WHERE [, PLACEHOLDER ...] ]
Convenience method for the common case of "SELECT COUNT(*) FROM table",
with optional WHERE. Must be called as method on a class with an
my $table = $self->table or die 'count called on object of class '.ref($self);
my $sql = "SELECT COUNT(*) FROM $table";
$sql .= " WHERE $where" if $where;
- $self->scalar_sql($sql);
+ $self->scalar_sql($sql, @_);
+}
+
+=item row_exists [ WHERE [, PLACEHOLDER ...] ]
+
+Convenience method for the common case of "SELECT 1 FROM table ... LIMIT 1"
+with optional (but almost always needed) WHERE.
+
+=cut
+
+sub row_exists {
+ my($self, $where) = (shift, shift);
+ my $table = $self->table or die 'row_exists called on object of class '.ref($self);
+ my $sql = "SELECT 1 FROM $table";
+ $sql .= " WHERE $where" if $where;
+ $sql .= " LIMIT 1";
+ $self->scalar_sql($sql, @_);
}
=back
my $column_type = $column_obj->type;
my $nullable = $column_obj->null;
+ utf8::upgrade($value);
+
warn " $table.$column: $value ($column_type".
( $nullable ? ' NULL' : ' NOT NULL' ).
")\n" if $DEBUG > 2;
}
+=item group_concat_sql COLUMN, DELIMITER
+
+Returns an SQL expression to concatenate an aggregate column, using
+GROUP_CONCAT() for mysql and array_to_string() and array_agg() for Pg.
+
+=cut
+
+sub group_concat_sql {
+ my ($col, $delim) = @_;
+ $delim = dbh->quote($delim);
+ if ( driver_name() =~ /^mysql/i ) {
+ # DISTINCT(foo) is valid as $col
+ return "GROUP_CONCAT($col SEPARATOR $delim)";
+ } else {
+ return "array_to_string(array_agg($col), $delim)";
+ }
+}
+
=item midnight_sql DATE
Returns an SQL expression to convert DATE (a unix timestamp) to midnight
projects / freeside.git / blobdiff