package FS::Record;
use strict;
-use vars qw($dbdef_file $dbdef $setup_hack $AUTOLOAD @ISA @EXPORT_OK);
+use vars qw( $dbdef_file $dbdef $setup_hack $AUTOLOAD @ISA @EXPORT_OK $DEBUG
+ $me );
use subs qw(reload_dbdef);
use Exporter;
use Carp qw(carp cluck croak confess);
use File::CounterFile;
-use FS::UID qw(dbh checkruid swapuid getotaker datasrc);
-use FS::dbdef;
+use Locale::Country;
+use DBI qw(:sql_types);
+use DBIx::DBSchema 0.19;
+use FS::UID qw(dbh checkruid getotaker datasrc driver_name);
+use FS::SearchCache;
@ISA = qw(Exporter);
-@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef);
+@EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch);
+
+$DEBUG = 0;
+$me = '[FS::Record]';
#ask FS::UID to run this stuff for us later
$FS::UID::callback{'FS::Record'} = sub {
$value = $record->unique('column');
- $value = $record->ut_float('column');
- $value = $record->ut_number('column');
- $value = $record->ut_numbern('column');
- $value = $record->ut_money('column');
- $value = $record->ut_text('column');
- $value = $record->ut_textn('column');
- $value = $record->ut_alpha('column');
- $value = $record->ut_alphan('column');
- $value = $record->ut_phonen('column');
- $value = $record->ut_anythingn('column');
+ $error = $record->ut_float('column');
+ $error = $record->ut_number('column');
+ $error = $record->ut_numbern('column');
+ $error = $record->ut_money('column');
+ $error = $record->ut_text('column');
+ $error = $record->ut_textn('column');
+ $error = $record->ut_alpha('column');
+ $error = $record->ut_alphan('column');
+ $error = $record->ut_phonen('column');
+ $error = $record->ut_anything('column');
+ $error = $record->ut_name('column');
$dbdef = reload_dbdef;
$dbdef = reload_dbdef "/non/standard/filename";
my $self = {};
bless ($self, $class);
- $self->{'Table'} = shift unless defined ( $self->table );
+ unless ( defined ( $self->table ) ) {
+ $self->{'Table'} = shift;
+ carp "warning: FS::Record::new called with table name ". $self->{'Table'};
+ }
my $hashref = $self->{'Hash'} = shift;
$hashref->{$field}='' unless defined $hashref->{$field};
#trim the '$' and ',' from money fields for Pg (belong HERE?)
#(what about Pg i18n?)
- if ( datasrc =~ m/Pg/
+ if ( driver_name =~ /^Pg$/i
&& $self->dbdef_table->column($field)->type eq 'money' ) {
${$hashref}{$field} =~ s/^\$//;
${$hashref}{$field} =~ s/\,//;
}
}
+ $self->_cache($hashref, shift) if $self->can('_cache') && @_;
+
$self;
}
+sub new_or_cached {
+ my $proto = shift;
+ my $class = ref($proto) || $proto;
+ my $self = {};
+ bless ($self, $class);
+
+ $self->{'Table'} = shift unless defined ( $self->table );
+
+ my $hashref = $self->{'Hash'} = shift;
+ my $cache = shift;
+ if ( defined( $cache->cache->{$hashref->{$cache->key}} ) ) {
+ my $obj = $cache->cache->{$hashref->{$cache->key}};
+ $obj->_cache($hashref, $cache) if $obj->can('_cache');
+ $obj;
+ } else {
+ $cache->cache->{$hashref->{$cache->key}} = $self->new($hashref, $cache);
+ }
+
+}
+
sub create {
my $proto = shift;
my $class = ref($proto) || $proto;
}
}
-=item qsearch TABLE, HASHREF
+=item qsearch TABLE, HASHREF, SELECT, EXTRA_SQL
Searches the database for all records matching (at least) the key/value pairs
in HASHREF. Returns all the records found as `FS::TABLE' objects if that
module is loaded (i.e. via `use FS::cust_main;'), otherwise returns FS::Record
objects.
+###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
+#feature where you can specify SELECT - remember, the objects returned,
+#although blessed into the appropriate `FS::TABLE' package, will only have the
+#fields you specify. This might have unwanted results if you then go calling
+#regular FS::TABLE methods
+#on it.
+
=cut
sub qsearch {
- my($table,$record) = @_;
- my($dbh) = dbh;
+ my($stable, $record, $select, $extra_sql, $cache ) = @_;
+ #$stable =~ /^([\w\_]+)$/ or die "Illegal table: $table";
+ #for jsearch
+ $stable =~ /^([\w\s\(\)\.\,\=]+)$/ or die "Illegal table: $stable";
+ $stable = $1;
+ $select ||= '*';
+ my $dbh = dbh;
+
+ my $table = $cache ? $cache->table : $stable;
+
+ my @fields = grep exists($record->{$_}), fields($table);
+
+ my $statement = "SELECT $select FROM $stable";
+ if ( @fields ) {
+ $statement .= ' WHERE '. join(' AND ', map {
+ if ( ! defined( $record->{$_} ) || $record->{$_} eq '' ) {
+ if ( driver_name =~ /^Pg$/i ) {
+ qq-( $_ IS NULL OR $_ = '' )-;
+ } else {
+ qq-( $_ IS NULL OR $_ = "" )-;
+ }
+ } else {
+ "$_ = ?";
+ }
+ } @fields );
+ }
+ $statement .= " $extra_sql" if defined($extra_sql);
- my(@fields)=grep exists($record->{$_}), fields($table);
+ warn "[debug]$me $statement\n" if $DEBUG;
+ my $sth = $dbh->prepare($statement)
+ or croak "$dbh->errstr doing $statement";
- my($sth);
- my($statement) = "SELECT * FROM $table". ( @fields
- ? " WHERE ". join(' AND ',
- map {
- $record->{$_} eq ''
- ? ( datasrc =~ m/Pg/
- ? "$_ IS NULL"
- : "( $_ IS NULL OR $_ = \"\" )"
- )
- : "$_ = ". _quote($record->{$_},$table,$_)
- } @fields
- ) : ''
- );
- $sth=$dbh->prepare($statement)
- or croak $dbh->errstr; #is that a little too harsh? hmm.
- #warn $statement #if $debug # or some such;
+ my $bind = 1;
- if ( eval ' scalar(@FS::'. $table. '::ISA);' ) {
- map {
- eval 'new FS::'. $table. ' ( $sth->fetchrow_hashref );';
- } ( 1 .. $sth->execute );
+ foreach my $field (
+ grep defined( $record->{$_} ) && $record->{$_} ne '', @fields
+ ) {
+ if ( $record->{$field} =~ /^\d+(\.\d+)?$/
+ && $dbdef->table($table)->column($field)->type =~ /(int)/i
+ ) {
+ $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_INTEGER } );
+ } else {
+ $sth->bind_param($bind++, $record->{$field}, { TYPE => SQL_VARCHAR } );
+ }
+ }
+
+# $sth->execute( map $record->{$_},
+# grep defined( $record->{$_} ) && $record->{$_} ne '', @fields
+# ) or croak "Error executing \"$statement\": ". $sth->errstr;
+
+ $sth->execute or croak "Error executing \"$statement\": ". $sth->errstr;
+
+ $dbh->commit or croak $dbh->errstr if $FS::UID::AutoCommit;
+
+ if ( eval 'scalar(@FS::'. $table. '::ISA);' ) {
+ if ( eval 'FS::'. $table. '->can(\'new\')' eq \&new ) {
+ #derivied class didn't override new method, so this optimization is safe
+ if ( $cache ) {
+ map {
+ new_or_cached( "FS::$table", { %{$_} }, $cache )
+ } @{$sth->fetchall_arrayref( {} )};
+ } else {
+ map {
+ new( "FS::$table", { %{$_} } )
+ } @{$sth->fetchall_arrayref( {} )};
+ }
+ } else {
+ warn "untested code (class FS::$table uses custom new method)";
+ map {
+ eval 'FS::'. $table. '->new( { %{$_} } )';
+ } @{$sth->fetchall_arrayref( {} )};
+ }
} else {
- cluck "qsearch: warning: FS::$table not loaded; returning generic FS::Record objects";
+ cluck "warning: FS::$table not loaded; returning FS::Record objects";
map {
- new FS::Record ($table,$sth->fetchrow_hashref);
- } ( 1 .. $sth->execute );
+ FS::Record->new( $table, { %{$_} } );
+ } @{$sth->fetchall_arrayref( {} )};
}
}
+=item jsearch TABLE, HASHREF, SELECT, EXTRA_SQL, PRIMARY_TABLE, PRIMARY_KEY
+
+Experimental JOINed search method. Using this method, you can execute a
+single SELECT spanning multiple tables, and cache the results for subsequent
+method calls. Interface will almost definately change in an incompatible
+fashion.
+
+Arguments:
+
+=cut
+
+sub jsearch {
+ my($table, $record, $select, $extra_sql, $ptable, $pkey ) = @_;
+ my $cache = FS::SearchCache->new( $ptable, $pkey );
+ my %saw;
+ ( $cache,
+ grep { !$saw{$_->getfield($pkey)}++ }
+ qsearch($table, $record, $select, $extra_sql, $cache )
+ );
+}
+
=item qsearchs TABLE, HASHREF
Same as qsearch, except that if more than one record matches, it B<carp>s but
my(@result) = qsearch(@_);
carp "warning: Multiple records in scalar search!" if scalar(@result) > 1;
#should warn more vehemently if the search was on a primary key?
- $result[0];
+ scalar(@result) ? ($result[0]) : ();
}
=back
=cut
+# readable/safe
+#sub AUTOLOAD {
+# my($self,$value)=@_;
+# my($field)=$AUTOLOAD;
+# $field =~ s/.*://;
+# if ( defined($value) ) {
+# confess "errant AUTOLOAD $field for $self (arg $value)"
+# unless $self->can('setfield');
+# $self->setfield($field,$value);
+# } else {
+# confess "errant AUTOLOAD $field for $self (no args)"
+# unless $self->can('getfield');
+# $self->getfield($field);
+# }
+#}
+
+# efficient
sub AUTOLOAD {
- my($self,$value)=@_;
- my($field)=$AUTOLOAD;
+ my $field = $AUTOLOAD;
$field =~ s/.*://;
- if ( defined($value) ) {
- $self->setfield($field,$value);
+ if ( defined($_[1]) ) {
+ $_[0]->setfield($field, $_[1]);
} else {
- $self->getfield($field);
+ $_[0]->getfield($field);
}
}
join(', ',map(_quote($self->getfield($_),$self->table,$_), @fields)).
")"
;
+ warn "[debug]$me $statement\n" if $DEBUG;
my $sth = dbh->prepare($statement) or return dbh->errstr;
local $SIG{HUP} = 'IGNORE';
local $SIG{PIPE} = 'IGNORE';
$sth->execute or return $sth->errstr;
+ dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
'';
}
map {
$self->getfield($_) eq ''
#? "( $_ IS NULL OR $_ = \"\" )"
- ? ( datasrc =~ m/Pg/
+ ? ( driver_name =~ /^Pg$/i
? "$_ IS NULL"
: "( $_ IS NULL OR $_ = \"\" )"
)
? ( $self->dbdef_table->primary_key)
: $self->fields
);
+ warn "[debug]$me $statement\n" if $DEBUG;
my $sth = dbh->prepare($statement) or return dbh->errstr;
local $SIG{HUP} = 'IGNORE';
my $rc = $sth->execute or return $sth->errstr;
#not portable #return "Record not found, statement:\n$statement" if $rc eq "0E0";
+ dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
undef $self; #no need to keep object!
sub replace {
my ( $new, $old ) = ( shift, shift );
+ warn "[debug]$me $new ->replace $old\n" if $DEBUG;
my @diff = grep $new->getfield($_) ne $old->getfield($_), $old->fields;
unless ( @diff ) {
- carp "warning: records identical";
+ carp "[warning]$me $new -> replace $old: records identical";
return '';
}
map {
$old->getfield($_) eq ''
#? "( $_ IS NULL OR $_ = \"\" )"
- ? ( datasrc =~ m/Pg/
+ ? ( driver_name =~ /^Pg$/i
? "$_ IS NULL"
: "( $_ IS NULL OR $_ = \"\" )"
)
} ( $primary_key ? ( $primary_key ) : $old->fields )
)
;
+ warn "[debug]$me $statement\n" if $DEBUG;
my $sth = dbh->prepare($statement) or return dbh->errstr;
local $SIG{HUP} = 'IGNORE';
my $rc = $sth->execute or return $sth->errstr;
#not portable #return "Record not found (or records identical)." if $rc eq "0E0";
+ dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
'';
=item unique COLUMN
Replaces COLUMN in record with a unique number. Called by the B<add> method
-on primary keys and single-field unique columns (see L<FS::dbdef_table>).
+on primary keys and single-field unique columns (see L<DBIx::DBSchema::Table>).
Returns the new value.
=cut
#warn "table $table is tainted" if is_tainted($table);
#warn "field $field is tainted" if is_tainted($field);
- &swapuid;
my($counter) = new File::CounterFile "$table.$field",0;
# hack for web demo
# getotaker() =~ /^([\w\-]{1,16})$/ or die "Illegal CGI REMOTE_USER!";
my($index)=$counter->inc;
$index=$counter->inc
while qsearchs($table,{$field=>$index}); #just in case
- &swapuid;
$index =~ /^(\d*)$/;
$index=$1;
'';
}
-=item ut_phonen COLUMN
+=item ut_phonen COLUMN [ COUNTRY ]
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.
+
=cut
sub ut_phonen {
- my($self,$field)=@_;
+ my( $self, $field, $country ) = @_;
+ return $self->ut_alphan($field) unless defined $country;
my $phonen = $self->getfield($field);
if ( $phonen eq '' ) {
$self->setfield($field,'');
- } else {
+ } elsif ( $country eq 'US' || $country eq 'CA' ) {
$phonen =~ s/\D//g;
$phonen =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/
or return "Illegal (phone) $field: ". $self->getfield($field);
$phonen = "$1-$2-$3";
$phonen .= " x$4" if $4;
$self->setfield($field,$phonen);
+ } else {
+ warn "warning: don't know how to check phone numbers for country $country";
+ return $self->ut_textn($field);
+ }
+ '';
+}
+
+=item ut_ip COLUMN
+
+Check/untaint ip addresses. IPv4 only for now.
+
+=cut
+
+sub ut_ip {
+ my( $self, $field ) = @_;
+ $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; }
+ $self->setfield($field, "$1.$2.$3.$3");
+ '';
+}
+
+=item ut_ipn COLUMN
+
+Check/untaint ip addresses. IPv4 only for now. May be null.
+
+=cut
+
+sub ut_ipn {
+ my( $self, $field ) = @_;
+ if ( $self->getfield($field) =~ /^()$/ ) {
+ $self->setfield($field,'');
+ '';
+ } else {
+ $self->ut_ip($field);
+ }
+}
+
+=item ut_domain COLUMN
+
+Check/untaint host and domain names.
+
+=cut
+
+sub ut_domain {
+ my( $self, $field ) = @_;
+ #$self->getfield($field) =~/^(\w+\.)*\w+$/
+ $self->getfield($field) =~/^(\w+\.)*\w+$/
+ or return "Illegal (domain) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+
+=item ut_name COLUMN
+
+Check/untaint proper names; allows alphanumerics, spaces and the following
+punctuation: , . - '
+
+May not be null.
+
+=cut
+
+sub ut_name {
+ my( $self, $field ) = @_;
+ $self->getfield($field) =~ /^([\w \,\.\-\']+)$/
+ or return "Illegal (name) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
+ '';
+}
+
+=item ut_zip COLUMN
+
+Check/untaint zip codes.
+
+=cut
+
+sub ut_zip {
+ my( $self, $field, $country ) = @_;
+ if ( $country eq 'US' ) {
+ $self->getfield($field) =~ /\s*(\d{5}(\-\d{4})?)\s*$/
+ or return "Illegal (zip) $field for country $country: ".
+ $self->getfield($field);
+ $self->setfield($field,$1);
+ } else {
+ $self->getfield($field) =~ /^\s*(\w[\w\-\s]{2,8}\w)\s*$/
+ or return "Illegal (zip) $field: ". $self->getfield($field);
+ $self->setfield($field,$1);
}
'';
}
+=item ut_country COLUMN
+
+Check/untaint country codes. Country names are changed to codes, if possible -
+see L<Locale::Country>.
+
+=cut
+
+sub ut_country {
+ my( $self, $field ) = @_;
+ unless ( $self->getfield($field) =~ /^(\w\w)$/ ) {
+ if ( $self->getfield($field) =~ /^([\w \,\.\(\)\']+)$/
+ && country2code($1) ) {
+ $self->setfield($field,uc(country2code($1)));
+ }
+ }
+ $self->getfield($field) =~ /^(\w\w)$/
+ or return "Illegal (country) $field: ". $self->getfield($field);
+ $self->setfield($field,uc($1));
+ '';
+}
+
=item ut_anything COLUMN
Untaints arbitrary data. Be careful.
=cut
sub ut_anything {
- my($self,$field)=@_;
- $self->getfield($field) =~ /^(.*)$/
+ my( $self, $field ) = @_;
+ $self->getfield($field) =~ /^(.*)$/s
or return "Illegal $field: ". $self->getfield($field);
$self->setfield($field,$1);
'';
}
+=item ut_enum COLUMN CHOICES_ARRAYREF
+
+Check/untaint a column, supplying all possible choices, like the "enum" type.
+
+=cut
+
+sub ut_enum {
+ my( $self, $field, $choices ) = @_;
+ foreach my $choice ( @$choices ) {
+ if ( $self->getfield($field) eq $choice ) {
+ $self->setfield($choice);
+ return '';
+ }
+ }
+ return "Illegal (enum) field $field: ". $self->getfield($field);
+}
+
=item fields [ TABLE ]
This can be used as both a subroutine and a method call. It returns a list
of the columns in this record's table, or an explicitly specified table.
-(See L<dbdef_table>).
+(See L<DBIx::DBSchema::Table>).
=cut
}
#croak "Usage: \@fields = fields(\$table)\n or: \@fields = \$record->fields" unless $table;
my($table_obj) = $dbdef->table($table);
- croak "Unknown table $table" unless $table_obj;
+ confess "Unknown table $table" unless $table_obj;
$table_obj->columns;
}
+=back
+
=head1 SUBROUTINES
=over 4
=item reload_dbdef([FILENAME])
-Load a database definition (see L<FS::dbdef>), optionally from a non-default
-filename. This command is executed at startup unless
-I<$FS::Record::setup_hack> is true. Returns a FS::dbdef object.
+Load a database definition (see L<DBIx::DBSchema>), optionally from a
+non-default filename. This command is executed at startup unless
+I<$FS::Record::setup_hack> is true. Returns a DBIx::DBSchema object.
=cut
sub reload_dbdef {
my $file = shift || $dbdef_file;
- $dbdef = load FS::dbdef ($file);
+ $dbdef = load DBIx::DBSchema $file;
}
=item dbdef
This is an internal function used to construct SQL statements. It returns
VALUE DBI-quoted (see L<DBI/"quote">) unless VALUE is a number and the column
-type (see L<dbdef_column>) does not end in `char' or `binary'.
+type (see L<FS::dbdef_column>) does not end in `char' or `binary'.
=cut
\%hash;
}
-#sub _dump {
-# my($self)=@_;
-# join("\n", map {
-# "$_: ". $self->getfield($_). "|"
-# } (fields($self->table)) );
-#}
+sub _dump {
+ my($self)=@_;
+ join("\n", map {
+ "$_: ". $self->getfield($_). "|"
+ } (fields($self->table)) );
+}
+
+sub DESTROY { return; }
#sub DESTROY {
# my $self = shift;
=back
-=head1 VERSION
-
-$Id: Record.pm,v 1.1 1999-08-04 09:03:53 ivan Exp $
-
=head1 BUGS
This module should probably be renamed, since much of the functionality is
ut_sqltype (like ut_varchar) should all be defined
-A fallback check method should be provided whith uses the dbdef.
+A fallback check method should be provided which uses the dbdef.
The ut_money method assumes money has two decimal digits.
The Pg money kludge in the new method only strips `$'.
-The ut_phonen method assumes US-style phone numbers.
+The ut_phonen method only checks US-style phone numbers.
The _quote function should probably use ut_float instead of a regex.
Probably should borrow/use some dbdef methods where appropriate (like sub
fields)
+As of 1.14, DBI fetchall_hashref( {} ) doesn't set fetchrow_hashref NAME_lc,
+or allow it to be set. Working around it is ugly any way around - DBI should
+be fixed. (only affects RDBMS which return uppercase column names)
+
+ut_zip should take an optional country like ut_phone.
+
=head1 SEE ALSO
-L<FS::dbdef>, L<FS::UID>, L<DBI>
+L<DBIx::DBSchema>, L<FS::UID>, L<DBI>
Adapter::DBI from Ch. 11 of Advanced Perl Programming by Sriram Srinivasan.