X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=020d14d8f298d8fe210e31db4198fed363047b66;hp=2c745b4137a599ac31749ed340a4a2a5b40ee081;hb=8f42b751aebda2e7dce2c363bed6f1e15b411b1d;hpb=6ef34dda51afba96d8dc6c4dd72427c3d4003945

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 2c745b413..020d14d8f 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -1,19 +1,23 @@
 package FS::Record;
 
 use strict;
-use vars qw($dbdef_file $dbdef $setup_hack $AUTOLOAD @ISA @EXPORT_OK $DEBUG);
+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 Locale::Country;
-use DBIx::DBSchema;
+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 { 
@@ -55,27 +59,27 @@ FS::Record - Database record objects
     $hashref = $record->hashref;
 
     $error = $record->insert;
-    #$error = $record->add; #depriciated
+    #$error = $record->add; #deprecated
 
     $error = $record->delete;
-    #$error = $record->del; #depriciated
+    #$error = $record->del; #deprecated
 
     $error = $new_record->replace($old_record);
-    #$error = $new_record->rep($old_record); #depriciated
+    #$error = $new_record->rep($old_record); #deprecated
 
     $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_anything('column');
-    $value = $record->ut_name('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";
@@ -120,7 +124,10 @@ sub new {
   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;
 
@@ -135,9 +142,31 @@ sub new {
     }
   }
 
+  $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;
@@ -170,20 +199,24 @@ objects.
 =cut
 
 sub qsearch {
-  my($table, $record, $select, $extra_sql ) = @_;
-  $table =~ /^([\w\_]+)$/ or die "Illegal table: $table";
-  $table = $1;
+  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 $table";
+  my $statement = "SELECT $select FROM $stable";
   if ( @fields ) {
     $statement .= ' WHERE '. join(' AND ', map {
       if ( ! defined( $record->{$_} ) || $record->{$_} eq '' ) {
         if ( driver_name =~ /^Pg$/i ) {
-          "$_ IS NULL";
+          qq-( $_ IS NULL OR $_ = '' )-;
         } else {
           qq-( $_ IS NULL OR $_ = "" )-;
         }
@@ -194,21 +227,44 @@ sub qsearch {
   }
   $statement .= " $extra_sql" if defined($extra_sql);
 
-  warn $statement if $DEBUG;
+  warn "[debug]$me $statement\n" if $DEBUG;
   my $sth = $dbh->prepare($statement)
     or croak "$dbh->errstr doing $statement";
 
-  $sth->execute( map $record->{$_},
+  my $bind = 1;
+
+  foreach my $field (
     grep defined( $record->{$_} ) && $record->{$_} ne '', @fields
-  ) or croak "Error executing \"$statement\": ". $dbh->errstr;
+  ) {
+    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
-      map {
-        new( "FS::$table", { %{$_} } )
-      } @{$sth->fetchall_arrayref( {} )};
+      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 {
@@ -224,6 +280,27 @@ sub qsearch {
 
 }
 
+=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
@@ -312,16 +389,30 @@ $record->column('value') is a synonym for $record->set('column','value');
 
 =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) ) {
-    confess "errant AUTOLOAD $field for $self (arg $value)"
-      unless $self->can('setfield');
-    $self->setfield($field,$value);
+  if ( defined($_[1]) ) {
+    $_[0]->setfield($field, $_[1]);
   } else {
-    $self->getfield($field);
+    $_[0]->getfield($field);
   }    
 }
 
@@ -385,6 +476,7 @@ sub insert {
       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';
@@ -434,6 +526,7 @@ sub delete {
           ? ( $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';
@@ -472,10 +565,11 @@ returns the error, otherwise returns false.
 
 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 '';
   }
 
@@ -506,6 +600,7 @@ sub replace {
       } ( $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';
@@ -737,7 +832,7 @@ sub ut_phonen {
     $phonen .= " x$4" if $4;
     $self->setfield($field,$phonen);
   } else {
-    warn "don't know how to check phone numbers for country $country";
+    warn "warning: don't know how to check phone numbers for country $country";
     return $self->ut_textn($field);
   }
   '';
@@ -813,10 +908,17 @@ Check/untaint zip codes.
 =cut
 
 sub ut_zip {
-  my( $self, $field ) = @_;
-  $self->getfield($field) =~ /^\s*(\w[\w\-\s]{2,8}\w)\s*$/
-    or return "Illegal (zip) $field: ". $self->getfield($field);
-  $self->setfield($field,$1);
+  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);
+  }
   '';
 }
 
@@ -848,13 +950,58 @@ Untaints arbitrary data.  Be careful.
 =cut
 
 sub ut_anything {
-  my($self,$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 ut_foreign_key COLUMN FOREIGN_TABLE FOREIGN_COLUMN
+
+Check/untaint a foreign column key.  Call a regular ut_ method (like ut_number)
+on the column first.
+
+=cut
+
+sub ut_foreign_key {
+  my( $self, $field, $table, $foreign ) = @_;
+  qsearchs($table, { $foreign => $self->getfield($field) })
+    or return "Can't find $field ". $self->getfield($field).
+              " in $table.$foreign";
+  '';
+}
+
+=item ut_foreign_keyn COLUMN FOREIGN_TABLE FOREIGN_COLUMN
+
+Like ut_foreign_key, except the null value is also allowed.
+
+=cut
+
+sub ut_foreign_keyn {
+  my( $self, $field, $table, $foreign ) = @_;
+  $self->getfield($field)
+    ? $self->ut_foreign_key($field, $table, $foreign)
+    : '';
+}
+
 =item fields [ TABLE ]
 
 This can be used as both a subroutine and a method call.  It returns a list
@@ -875,7 +1022,7 @@ sub fields {
   }
   #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;
 }
 
@@ -895,7 +1042,8 @@ I<$FS::Record::setup_hack> is true.  Returns a DBIx::DBSchema object.
 
 sub reload_dbdef {
   my $file = shift || $dbdef_file;
-  $dbdef = load DBIx::DBSchema $file;
+  $dbdef = load DBIx::DBSchema $file
+    or die "can't load database schema from $file";
 }
 
 =item dbdef
@@ -945,12 +1093,12 @@ sub hfields {
   \%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; }
 
@@ -967,10 +1115,6 @@ sub DESTROY { return; }
 
 =back
 
-=head1 VERSION
-
-$Id: Record.pm,v 1.25 2001-08-21 09:34:13 ivan Exp $
-
 =head1 BUGS
 
 This module should probably be renamed, since much of the functionality is