X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FRecord.pm;h=9a724feac62f806592309c4bbf5b26529320ace1;hp=4286606f048b93be82436a550e64a804b384bf0c;hb=c97973a5145438eedd9fe16841897f5c4febb995;hpb=6991d4986df7fb3a6c7c49b5ae1b3713e87a16c4

diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index 4286606f0..9a724feac 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -2,16 +2,17 @@ package FS::Record;
 
 use strict;
 use vars qw( $dbdef_file $dbdef $setup_hack $AUTOLOAD @ISA @EXPORT_OK $DEBUG
-             $me );
+             $me %dbdef_cache );
 use subs qw(reload_dbdef);
 use Exporter;
 use Carp qw(carp cluck croak confess);
 use File::CounterFile;
 use Locale::Country;
 use DBI qw(:sql_types);
-use DBIx::DBSchema 0.19;
-use FS::UID qw(dbh checkruid getotaker datasrc driver_name);
+use DBIx::DBSchema 0.21;
+use FS::UID qw(dbh getotaker datasrc driver_name);
 use FS::SearchCache;
+use FS::Msgcat qw(gettext);
 
 @ISA = qw(Exporter);
 @EXPORT_OK = qw(dbh fields hfields qsearch qsearchs dbdef jsearch);
@@ -59,14 +60,12 @@ FS::Record - Database record objects
     $hashref = $record->hashref;
 
     $error = $record->insert;
-    #$error = $record->add; #depriciated
 
     $error = $record->delete;
-    #$error = $record->del; #depriciated
 
     $error = $new_record->replace($old_record);
-    #$error = $new_record->rep($old_record); #depriciated
 
+    # external use deprecated - handled by the database (at least for Pg, mysql)
     $value = $record->unique('column');
 
     $error = $record->ut_float('column');
@@ -87,7 +86,7 @@ FS::Record - Database record objects
 
     $quoted_value = _quote($value,'table','field');
 
-    #depriciated
+    #deprecated
     $fields = hfields('table');
     if ( $fields->{Field} ) { # etc.
 
@@ -131,15 +130,8 @@ sub new {
 
   my $hashref = $self->{'Hash'} = shift;
 
-  foreach my $field ( $self->fields ) { 
-    $hashref->{$field}='' unless defined $hashref->{$field};
-    #trim the '$' and ',' from money fields for Pg (belong HERE?)
-    #(what about Pg i18n?)
-    if ( driver_name =~ /^Pg$/i
-         && $self->dbdef_table->column($field)->type eq 'money' ) {
-      ${$hashref}{$field} =~ s/^\$//;
-      ${$hashref}{$field} =~ s/\,//;
-    }
+  foreach my $field ( grep !defined($hashref->{$_}), $self->fields ) { 
+    $hashref->{$field}='';
   }
 
   $self->_cache($hashref, shift) if $self->can('_cache') && @_;
@@ -173,14 +165,14 @@ sub create {
   my $self = {};
   bless ($self, $class);
   if ( defined $self->table ) {
-    cluck "create constructor is depriciated, use new!";
+    cluck "create constructor is deprecated, use new!";
     $self->new(@_);
   } else {
     croak "FS::Record::create called (not from a subclass)!";
   }
 }
 
-=item qsearch TABLE, HASHREF, SELECT, EXTRA_SQL
+=item qsearch TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ
 
 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
@@ -214,20 +206,56 @@ sub qsearch {
   my $statement = "SELECT $select FROM $stable";
   if ( @fields ) {
     $statement .= ' WHERE '. join(' AND ', map {
+
+      my $op = '=';
+      my $column = $_;
+      if ( ref($record->{$_}) ) {
+        $op = $record->{$_}{'op'} if $record->{$_}{'op'};
+        #$op = 'LIKE' if $op =~ /^ILIKE$/i && driver_name ne 'Pg';
+        if ( uc($op) eq 'ILIKE' ) {
+          $op = 'LIKE';
+          $record->{$_}{'value'} = lc($record->{$_}{'value'});
+          $column = "LOWER($_)";
+        }
+        $record->{$_} = $record->{$_}{'value'}
+      }
+
       if ( ! defined( $record->{$_} ) || $record->{$_} eq '' ) {
-        if ( driver_name =~ /^Pg$/i ) {
-          qq-( $_ IS NULL OR $_ = '' )-;
+        if ( $op eq '=' ) {
+          if ( driver_name eq 'Pg' ) {
+            if ( $dbdef->table($table)->column($column)->type =~ /(int)/i ) {
+              qq-( $column IS NULL )-;
+            } else {
+              qq-( $column IS NULL OR $column = '' )-;
+            }
+          } else {
+            qq-( $column IS NULL OR $column = "" )-;
+          }
+        } elsif ( $op eq '!=' ) {
+          if ( driver_name eq 'Pg' ) {
+            if ( $dbdef->table($table)->column($column)->type =~ /(int)/i ) {
+              qq-( $column IS NOT NULL )-;
+            } else {
+              qq-( $column IS NOT NULL AND $column != '' )-;
+            }
+          } else {
+            qq-( $column IS NOT NULL AND $column != "" )-;
+          }
         } else {
-          qq-( $_ IS NULL OR $_ = "" )-;
+          if ( driver_name eq 'Pg' ) {
+            qq-( $column $op '' )-;
+          } else {
+            qq-( $column $op "" )-;
+          }
         }
       } else {
-        "$_ = ?";
+        "$column $op ?";
       }
     } @fields );
   }
   $statement .= " $extra_sql" if defined($extra_sql);
 
-  warn "[debug]$me $statement\n" if $DEBUG;
+  warn "[debug]$me $statement\n" if $DEBUG > 1;
   my $sth = $dbh->prepare($statement)
     or croak "$dbh->errstr doing $statement";
 
@@ -310,9 +338,11 @@ for a single item, or your data is corrupted.
 =cut
 
 sub qsearchs { # $result_record = &FS::Record:qsearchs('table',\%hash);
+  my $table = $_[0];
   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?
+  carp "warning: Multiple records in scalar search ($table)"
+    if scalar(@result) > 1;
+  #should warn more vehemently if the search was on a primary key?
   scalar(@result) ? ($result[0]) : ();
 }
 
@@ -329,14 +359,14 @@ Returns the table name.
 =cut
 
 sub table {
-#  cluck "warning: FS::Record::table depriciated; supply one in subclass!";
+#  cluck "warning: FS::Record::table deprecated; supply one in subclass!";
   my $self = shift;
   $self -> {'Table'};
 }
 
 =item dbdef_table
 
-Returns the FS::dbdef_table object for the table.
+Returns the DBIx::DBSchema::Table object for the table.
 
 =cut
 
@@ -390,32 +420,32 @@ $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 $field = $AUTOLOAD;
+  my($self,$value)=@_;
+  my($field)=$AUTOLOAD;
   $field =~ s/.*://;
-  if ( defined($_[1]) ) {
-    $_[0]->setfield($field, $_[1]);
+  if ( defined($value) ) {
+    confess "errant AUTOLOAD $field for $self (arg $value)"
+      unless $self->can('setfield');
+    $self->setfield($field,$value);
   } else {
-    $_[0]->getfield($field);
+    confess "errant AUTOLOAD $field for $self (no args)"
+      unless $self->can('getfield');
+    $self->getfield($field);
   }    
 }
 
+# efficient
+#sub AUTOLOAD {
+#  my $field = $AUTOLOAD;
+#  $field =~ s/.*://;
+#  if ( defined($_[1]) ) {
+#    $_[0]->setfield($field, $_[1]);
+#  } else {
+#    $_[0]->getfield($field);
+#  }    
+#}
+
 =item hash
 
 Returns a list of the column/value pairs, usually for assigning to a new hash.
@@ -456,27 +486,46 @@ sub insert {
   return $error if $error;
 
   #single-field unique keys are given a value if false
-  #(like MySQL's AUTO_INCREMENT)
+  #(like MySQL's AUTO_INCREMENT or Pg SERIAL)
   foreach ( $self->dbdef_table->unique->singles ) {
     $self->unique($_) unless $self->getfield($_);
   }
-  #and also the primary key
+
+  #and also the primary key, if the database isn't going to
   my $primary_key = $self->dbdef_table->primary_key;
-  $self->unique($primary_key) 
-    if $primary_key && ! $self->getfield($primary_key);
+  my $db_seq = 0;
+  if ( $primary_key ) {
+    my $col = $self->dbdef_table->column($primary_key);
+    
+    $db_seq =
+      uc($col->type) eq 'SERIAL'
+      || ( driver_name eq 'Pg'
+             && defined($col->default)
+             && $col->default =~ /^nextval\(/i
+         )
+      || ( driver_name eq 'mysql'
+             && defined($col->local)
+             && $col->local =~ /AUTO_INCREMENT/i
+         );
+    $self->unique($primary_key) unless $self->getfield($primary_key) || $db_seq;
+  }
 
+  my $table = $self->table;
+  #false laziness w/delete
   my @fields =
     grep defined($self->getfield($_)) && $self->getfield($_) ne "",
     $self->fields
   ;
+  my @values = map { _quote( $self->getfield($_), $table, $_) } @fields;
+  #eslaf
 
-  my $statement = "INSERT INTO ". $self->table. " ( ".
-      join(', ',@fields ).
+  my $statement = "INSERT INTO $table ( ".
+      join( ', ', @fields ).
     ") VALUES (".
-      join(', ',map(_quote($self->getfield($_),$self->table,$_), @fields)).
+      join( ', ', @values ).
     ")"
   ;
-  warn "[debug]$me $statement\n" if $DEBUG;
+  warn "[debug]$me $statement\n" if $DEBUG > 1;
   my $sth = dbh->prepare($statement) or return dbh->errstr;
 
   local $SIG{HUP} = 'IGNORE';
@@ -487,6 +536,64 @@ sub insert {
   local $SIG{PIPE} = 'IGNORE';
 
   $sth->execute or return $sth->errstr;
+
+  if ( $db_seq ) { # get inserted id from the database, if applicable
+    warn "[debug]$me retreiving sequence from database\n" if $DEBUG;
+    my $insertid = '';
+    if ( driver_name eq 'Pg' ) {
+
+      my $oid = $sth->{'pg_oid_status'};
+      my $i_sql = "SELECT $primary_key FROM $table WHERE oid = ?";
+      my $i_sth = dbh->prepare($i_sql) or do {
+        dbh->rollback if $FS::UID::AutoCommit;
+        return dbh->errstr;
+      };
+      $i_sth->execute($oid) or do {
+        dbh->rollback if $FS::UID::AutoCommit;
+        return $i_sth->errstr;
+      };
+      $insertid = $i_sth->fetchrow_arrayref->[0];
+
+    } elsif ( driver_name eq 'mysql' ) {
+
+      $insertid = dbh->{'mysql_insertid'};
+      # work around mysql_insertid being null some of the time, ala RT :/
+      unless ( $insertid ) {
+        warn "WARNING: DBD::mysql didn't return mysql_insertid; ".
+             "using SELECT LAST_INSERT_ID();";
+        my $i_sql = "SELECT LAST_INSERT_ID()";
+        my $i_sth = dbh->prepare($i_sql) or do {
+          dbh->rollback if $FS::UID::AutoCommit;
+          return dbh->errstr;
+        };
+        $i_sth->execute or do {
+          dbh->rollback if $FS::UID::AutoCommit;
+          return $i_sth->errstr;
+        };
+        $insertid = $i_sth->fetchrow_arrayref->[0];
+      }
+
+    } else {
+      dbh->rollback if $FS::UID::AutoCommit;
+      return "don't know how to retreive inserted ids from ". driver_name. 
+             ", try using counterfiles (maybe run dbdef-create?)";
+    }
+    $self->setfield($primary_key, $insertid);
+  }
+
+  my $h_sth;
+  if ( defined $dbdef->table('h_'. $table) ) {
+    my $h_statement = $self->_h_statement('insert');
+    warn "[debug]$me $h_statement\n" if $DEBUG > 2;
+    $h_sth = dbh->prepare($h_statement) or do {
+      dbh->rollback if $FS::UID::AutoCommit;
+      return dbh->errstr;
+    };
+  } else {
+    $h_sth = '';
+  }
+  $h_sth->execute or return $h_sth->errstr if $h_sth;
+
   dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
 
   '';
@@ -499,7 +606,7 @@ Depriciated (use insert instead).
 =cut
 
 sub add {
-  cluck "warning: FS::Record::add depriciated!";
+  cluck "warning: FS::Record::add deprecated!";
   insert @_; #call method in this scope
 }
 
@@ -513,11 +620,11 @@ otherwise returns false.
 sub delete {
   my $self = shift;
 
-  my($statement)="DELETE FROM ". $self->table. " WHERE ". join(' AND ',
+  my $statement = "DELETE FROM ". $self->table. " WHERE ". join(' AND ',
     map {
       $self->getfield($_) eq ''
         #? "( $_ IS NULL OR $_ = \"\" )"
-        ? ( driver_name =~ /^Pg$/i
+        ? ( driver_name eq 'Pg'
               ? "$_ IS NULL"
               : "( $_ IS NULL OR $_ = \"\" )"
           )
@@ -526,9 +633,18 @@ sub delete {
           ? ( $self->dbdef_table->primary_key)
           : $self->fields
   );
-  warn "[debug]$me $statement\n" if $DEBUG;
+  warn "[debug]$me $statement\n" if $DEBUG > 1;
   my $sth = dbh->prepare($statement) or return dbh->errstr;
 
+  my $h_sth;
+  if ( defined $dbdef->table('h_'. $self->table) ) {
+    my $h_statement = $self->_h_statement('delete');
+    warn "[debug]$me $h_statement\n" if $DEBUG > 2;
+    $h_sth = dbh->prepare($h_statement) or return dbh->errstr;
+  } else {
+    $h_sth = '';
+  }
+
   local $SIG{HUP} = 'IGNORE';
   local $SIG{INT} = 'IGNORE';
   local $SIG{QUIT} = 'IGNORE'; 
@@ -538,9 +654,11 @@ sub delete {
 
   my $rc = $sth->execute or return $sth->errstr;
   #not portable #return "Record not found, statement:\n$statement" if $rc eq "0E0";
+  $h_sth->execute or return $h_sth->errstr if $h_sth;
   dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
 
-  undef $self; #no need to keep object!
+  #no need to needlessly destoy the data either (causes problems actually)
+  #undef $self; #no need to keep object!
 
   '';
 }
@@ -552,7 +670,7 @@ Depriciated (use delete instead).
 =cut
 
 sub del {
-  cluck "warning: FS::Record::del depriciated!";
+  cluck "warning: FS::Record::del deprecated!";
   &delete(@_); #call method in this scope
 }
 
@@ -567,12 +685,6 @@ 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]$me $new -> replace $old: records identical";
-    return '';
-  }
-
   return "Records not in same table!" unless $new->table eq $old->table;
 
   my $primary_key = $old->dbdef_table->primary_key;
@@ -583,6 +695,12 @@ sub replace {
   my $error = $new->check;
   return $error if $error;
 
+  my @diff = grep $new->getfield($_) ne $old->getfield($_), $old->fields;
+  unless ( @diff ) {
+    carp "[warning]$me $new -> replace $old: records identical";
+    return '';
+  }
+
   my $statement = "UPDATE ". $old->table. " SET ". join(', ',
     map {
       "$_ = ". _quote($new->getfield($_),$old->table,$_) 
@@ -592,7 +710,7 @@ sub replace {
       map {
         $old->getfield($_) eq ''
           #? "( $_ IS NULL OR $_ = \"\" )"
-          ? ( driver_name =~ /^Pg$/i
+          ? ( driver_name eq 'Pg'
                 ? "$_ IS NULL"
                 : "( $_ IS NULL OR $_ = \"\" )"
             )
@@ -600,9 +718,27 @@ sub replace {
       } ( $primary_key ? ( $primary_key ) : $old->fields )
     )
   ;
-  warn "[debug]$me $statement\n" if $DEBUG;
+  warn "[debug]$me $statement\n" if $DEBUG > 1;
   my $sth = dbh->prepare($statement) or return dbh->errstr;
 
+  my $h_old_sth;
+  if ( defined $dbdef->table('h_'. $old->table) ) {
+    my $h_old_statement = $old->_h_statement('replace_old');
+    warn "[debug]$me $h_old_statement\n" if $DEBUG > 2;
+    $h_old_sth = dbh->prepare($h_old_statement) or return dbh->errstr;
+  } else {
+    $h_old_sth = '';
+  }
+
+  my $h_new_sth;
+  if ( defined $dbdef->table('h_'. $new->table) ) {
+    my $h_new_statement = $new->_h_statement('replace_new');
+    warn "[debug]$me $h_new_statement\n" if $DEBUG > 2;
+    $h_new_sth = dbh->prepare($h_new_statement) or return dbh->errstr;
+  } else {
+    $h_new_sth = '';
+  }
+
   local $SIG{HUP} = 'IGNORE';
   local $SIG{INT} = 'IGNORE';
   local $SIG{QUIT} = 'IGNORE'; 
@@ -612,6 +748,8 @@ sub replace {
 
   my $rc = $sth->execute or return $sth->errstr;
   #not portable #return "Record not found (or records identical)." if $rc eq "0E0";
+  $h_old_sth->execute or return $h_old_sth->errstr if $h_old_sth;
+  $h_new_sth->execute or return $h_new_sth->errstr if $h_new_sth;
   dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit;
 
   '';
@@ -625,7 +763,7 @@ Depriciated (use replace instead).
 =cut
 
 sub rep {
-  cluck "warning: FS::Record::rep depriciated!";
+  cluck "warning: FS::Record::rep deprecated!";
   replace @_; #call method in this scope
 }
 
@@ -639,10 +777,32 @@ sub check {
   confess "FS::Record::check not implemented; supply one in subclass!";
 }
 
+sub _h_statement {
+  my( $self, $action ) = @_;
+
+  my @fields =
+    grep defined($self->getfield($_)) && $self->getfield($_) ne "",
+    $self->fields
+  ;
+  my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields;
+
+  "INSERT INTO h_". $self->table. " ( ".
+      join(', ', qw(history_date history_user history_action), @fields ).
+    ") VALUES (".
+      join(', ', time, dbh->quote(getotaker()), dbh->quote($action), @values).
+    ")"
+  ;
+}
+
 =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<DBIx::DBSchema::Table>).
+B<Warning>: External use is B<deprecated>.  
+
+Replaces COLUMN in record with a unique number, using counters in the
+filesystem.  Used by the B<insert> method on single-field unique columns
+(see L<DBIx::DBSchema::Table>) and also as a fallback for primary keys
+that aren't SERIAL (Pg) or AUTO_INCREMENT (mysql).
+
 Returns the new value.
 
 =cut
@@ -651,8 +811,6 @@ sub unique {
   my($self,$field) = @_;
   my($table)=$self->table;
 
-  croak("&FS::UID::checkruid failed") unless &checkruid;
-
   croak "Unique called on field $field, but it is ",
         $self->getfield($field),
         ", not null!"
@@ -668,9 +826,8 @@ sub unique {
 #  my($counter) = new File::CounterFile "$user/$table.$field",0;
 # endhack
 
-  my($index)=$counter->inc;
-  $index=$counter->inc
-    while qsearchs($table,{$field=>$index}); #just in case
+  my $index = $counter->inc;
+  $index = $counter->inc while qsearchs($table, { $field=>$index } );
 
   $index =~ /^(\d*)$/;
   $index=$1;
@@ -747,7 +904,7 @@ sub ut_money {
 =item ut_text COLUMN
 
 Check/untaint text.  Alphanumerics, spaces, and the following punctuation
-symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? /
+symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / =
 May not be null.  If there is an error, returns the error, otherwise returns
 false.
 
@@ -755,8 +912,12 @@ false.
 
 sub ut_text {
   my($self,$field)=@_;
-  $self->getfield($field) =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/]+)$/
-    or return "Illegal or empty (text) $field: ". $self->getfield($field);
+  #warn "msgcat ". \&msgcat. "\n";
+  #warn "notexist ". \&notexist. "\n";
+  #warn "AUTOLOAD ". \&AUTOLOAD. "\n";
+  $self->getfield($field) =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]+)$/
+    or return gettext('illegal_or_empty_text'). " $field: ".
+               $self->getfield($field);
   $self->setfield($field,$1);
   '';
 }
@@ -771,8 +932,8 @@ May be null.  If there is an error, returns the error, otherwise returns false.
 
 sub ut_textn {
   my($self,$field)=@_;
-  $self->getfield($field) =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/]*)$/
-    or return "Illegal (text) $field: ". $self->getfield($field);
+  $self->getfield($field) =~ /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/
+    or return gettext('illegal_text'). " $field: ". $self->getfield($field);
   $self->setfield($field,$1);
   '';
 }
@@ -827,7 +988,7 @@ sub ut_phonen {
   } 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);
+      or return gettext('illegal_phone'). " $field: ". $self->getfield($field);
     $phonen = "$1-$2-$3";
     $phonen .= " x$4" if $4;
     $self->setfield($field,$phonen);
@@ -849,7 +1010,7 @@ sub ut_ip {
   $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");
+  $self->setfield($field, "$1.$2.$3.$4");
   '';
 }
 
@@ -878,7 +1039,7 @@ Check/untaint host and domain names.
 sub ut_domain {
   my( $self, $field ) = @_;
   #$self->getfield($field) =~/^(\w+\.)*\w+$/
-  $self->getfield($field) =~/^(\w+\.)*\w+$/
+  $self->getfield($field) =~/^(([\w\-]+\.)*\w+)$/
     or return "Illegal (domain) $field: ". $self->getfield($field);
   $self->setfield($field,$1);
   '';
@@ -896,7 +1057,7 @@ May not be null.
 sub ut_name {
   my( $self, $field ) = @_;
   $self->getfield($field) =~ /^([\w \,\.\-\']+)$/
-    or return "Illegal (name) $field: ". $self->getfield($field);
+    or return gettext('illegal_name'). " $field: ". $self->getfield($field);
   $self->setfield($field,$1);
   '';
 }
@@ -911,12 +1072,12 @@ 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: ".
+      or return gettext('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);
+      or return gettext('illegal_zip'). " $field: ". $self->getfield($field);
     $self->setfield($field,$1);
   }
   '';
@@ -974,6 +1135,34 @@ sub ut_enum {
   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
@@ -1014,12 +1203,20 @@ 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;
+
+  unless ( exists $dbdef_cache{$file} ) {
+    warn "[debug]$me loading dbdef for $file\n" if $DEBUG;
+    $dbdef_cache{$file} = DBIx::DBSchema->load( $file )
+                            or die "can't load database schema from $file";
+  } else {
+    warn "[debug]$me re-using cached dbdef for $file\n" if $DEBUG;
+  }
+  $dbdef = $dbdef_cache{$file};
 }
 
 =item dbdef
 
-Returns the current database definition.  See L<FS::dbdef>.
+Returns the current database definition.  See L<DBIx::DBSchema>.
 
 =cut
 
@@ -1029,33 +1226,41 @@ sub dbdef { $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<FS::dbdef_column>) does not end in `char' or `binary'.
+type (see L<DBIx::DBSchema::Column>) does not end in `char' or `binary'.
 
 =cut
 
 sub _quote {
-  my($value,$table,$field)=@_;
-  my($dbh)=dbh;
-  if ( $value =~ /^\d+(\.\d+)?$/ && 
-#       ! ( datatype($table,$field) =~ /^char/ ) 
-       ! ( $dbdef->table($table)->column($field)->type =~ /(char|binary)$/i ) 
-  ) {
+  my($value, $table, $column) = @_;
+  my $column_obj = $dbdef->table($table)->column($column);
+  my $column_type = $column_obj->type;
+
+  if ( $value eq '' && $column_type =~ /^int/ ) {
+    if ( $column_obj->null ) {
+      'NULL';
+    } else {
+      cluck "WARNING: Attempting to set non-null integer $table.$column null; ".
+            "using 0 instead";
+      0;
+    }
+  } elsif ( $value =~ /^\d+(\.\d+)?$/ && 
+            ! $column_type =~ /(char|binary|text)$/i ) {
     $value;
   } else {
-    $dbh->quote($value);
+    dbh->quote($value);
   }
 }
 
 =item hfields TABLE
 
-This is depriciated.  Don't use it.
+This is deprecated.  Don't use it.
 
 It returns a hash-type list with the fields of this record's table set true.
 
 =cut
 
 sub hfields {
-  carp "warning: hfields is depriciated";
+  carp "warning: hfields is deprecated";
   my($table)=@_;
   my(%hash);
   foreach (fields($table)) {
@@ -1091,7 +1296,7 @@ sub DESTROY { return; }
 This module should probably be renamed, since much of the functionality is
 of general use.  It is not completely unlike Adapter::DBI (see below).
 
-Exported qsearch and qsearchs should be depriciated in favor of method calls
+Exported qsearch and qsearchs should be deprecated in favor of method calls
 (against an FS::Record object like the old search and searchs that qsearch
 and qsearchs were on top of.)
 
@@ -1099,7 +1304,7 @@ The whole fields / hfields mess should be removed.
 
 The various WHERE clauses should be subroutined.
 
-table string should be depriciated in favor of FS::dbdef_table.
+table string should be deprecated in favor of DBIx::DBSchema::Table.
 
 No doubt we could benefit from a Tied hash.  Documenting how exists / defined
 true maps to the database (and WHERE clauses) would also help.