NAME FS::Record - Database record objects SYNOPSIS use FS::Record; use FS::Record qw(dbh fields hfields qsearch qsearchs dbdef); $record = new FS::Record 'table', \%hash; $record = new FS::Record 'table', { 'column' => 'value', ... }; $record = qsearchs FS::Record 'table', \%hash; $record = qsearchs FS::Record 'table', { 'column' => 'value', ... }; @records = qsearch FS::Record 'table', \%hash; @records = qsearch FS::Record 'table', { 'column' => 'value', ... }; $table = $record->table; $dbdef_table = $record->dbdef_table; $value = $record->get('column'); $value = $record->getfield('column'); $value = $record->column; $record->set( 'column' => 'value' ); $record->setfield( 'column' => 'value' ); $record->column('value'); %hash = $record->hash; $hashref = $record->hashref; $error = $record->add; $error = $record->del; $error = $new_record->rep($old_record); $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'); $dbdef = reload_dbdef; $dbdef = reload_dbdef "/non/standard/filename"; $dbdef = dbdef; $quoted_value = _quote($value,'table','field'); #depriciated $fields = hfields('table'); if ( $fields->{Field} ) { # etc. @fields = fields 'table'; DESCRIPTION (Mostly) object-oriented interface to database records. Records are currently implemented on top of DBI. FS::Record is intended as a base class for table-specific classes to inherit from, i.e. FS::cust_main. METHODS new TABLE, HASHREF Creates a new record. It doesn't store it in the database, though. See the section on "add" for that. Note that the object stores this hash reference, not a distinct copy of the hash it points to. You can ask the object for a copy with the *hash* method. qsearch TABLE, HASHREF 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. qsearchs TABLE, HASHREF Same as qsearch, except that if more than one record matches, it carps but returns the first. If this happens, you either made a logic error in asking for a single item, or your data is corrupted. table Returns the table name. dbdef_table Returns the FS::dbdef_table object for the table. get, getfield COLUMN Returns the value of the column/field/key COLUMN. set, setfield COLUMN, VALUE Sets the value of the column/field/key COLUMN to VALUE. Returns VALUE. AUTLOADED METHODS $record->column is a synonym for $record->get('column'); $record->column('value') is a synonym for $record- >set('column','value'); hash Returns a list of the column/value pairs, usually for assigning to a new hash. To make a distinct duplicate of an FS::Record object, you can do: $new = new FS::Record ( $old->table, { $old->hash } ); hashref Returns a reference to the column/value hash. add Adds this record to the database. If there is an error, returns the error, otherwise returns false. del Delete this record from the database. If there is an error, returns the error, otherwise returns false. rep OLD_RECORD Replace the OLD_RECORD with this one in the database. If there is an error, returns the error, otherwise returns false. unique COLUMN Replaces COLUMN in record with a unique number. Called by the add method on primary keys and single-field unique columns (see the FS::dbdef_table manpage). Returns the new value. ut_float COLUMN Check/untaint floating point numeric data: 1.1, 1, 1.1e10, 1e10. May not be null. If there is an error, returns the error, otherwise returns false. ut_number COLUMN Check/untaint simple numeric data (whole numbers). May not be null. If there is an error, returns the error, otherwise returns false. ut_numbern COLUMN Check/untaint simple numeric data (whole numbers). May be null. If there is an error, returns the error, otherwise returns false. ut_money COLUMN Check/untaint monetary numbers. May be negative. Set to 0 if null. If there is an error, returns the error, otherwise returns false. ut_text COLUMN Check/untaint text. Alphanumerics, spaces, and the following punctuation symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / May not be null. If there is an error, returns the error, otherwise returns false. ut_textn COLUMN Check/untaint text. Alphanumerics, spaces, and the following punctuation symbols are currently permitted: ! @ # $ % & ( ) - + ; : ' " , . ? / May be null. If there is an error, returns the error, otherwise returns false. ut_alpha COLUMN Check/untaint alphanumeric strings (no spaces). May not be null. If there is an error, returns the error, otherwise returns false. ut_alpha COLUMN Check/untaint alphanumeric strings (no spaces). May be null. If there is an error, returns the error, otherwise returns false. ut_phonen COLUMN Check/untaint phone numbers. May be null. If there is an error, returns the error, otherwise returns false. ut_anything COLUMN Untaints arbitrary data. Be careful. SUBROUTINES reload_dbdef([FILENAME]) Load a database definition (see the FS::dbdef manpage), optionally from a non-default filename. This command is executed at startup unless *$FS::Record::setup_hack* is true. Returns a FS::dbdef object. dbdef Returns the current database definition. See the FS::dbdef manpage. _quote VALUE, TABLE, COLUMN This is an internal function used to construct SQL statements. It returns VALUE DBI-quoted (see the section on "quote" in the DBI manpage) unless VALUE is a number and the column type (see the dbdef_column manpage) does not end in `char' or `binary'. hfields TABLE This is depriciated. Don't use it. It returns a hash-type list with the fields of this record's table set true. fields TABLE This returns a list of the columns in this record's table (See the dbdef_table manpage). BUGS 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 (against an FS::Record object like the old search and searchs that qsearch and qsearchs were on top of.) 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. No doubt we could benefit from a Tied hash. Documenting how exists / defined true maps to the database (and WHERE clauses) would also help. The ut_ methods should ask the dbdef for a default length. ut_sqltype (like ut_varchar) should all be defined A fallback check method should be provided whith 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 _quote function should probably use ut_float instead of a regex. All the subroutines probably should be methods, here or elsewhere. SEE ALSO the FS::dbdef manpage, the FS::UID manpage, the DBI manpage Adapter::DBI from Ch. 11 of Advanced Perl Programming by Sriram Srinivasan. HISTORY ivan@voicenet.com 97-jun-2 - 9, 19, 25, 27, 30 DBI version ivan@sisd.com 97-nov-8 - 12 cleaned up, added autoloaded $self->any_field calls, moved DBI login stuff to FS::UID ivan@sisd.com 97-nov-21-23 since AUTO_INCREMENT is MySQL specific, use my own unique number generator (again) ivan@sisd.com 97-dec-4 untaint $user in unique (web demo hack...bah) make unique skip multiple-field unique's from dbdef ivan@sisd.com 97- dec-11 merge with FS::Search, which after all was just alternate constructors for FS::Record objects. Makes lots of things cleaner. :) ivan@sisd.com 97-dec-13 use FS::dbdef::primary key in replace searches, hopefully for all practical purposes the string/number problem in SQL statements should be gone? (SQL bites) ivan@sisd.com 98-jan- 20 Put all SQL statments in $statment before we $sth=$dbh- >prepare( them, for debugging reasons (warn $statement) ivan@sisd.com 98-feb-19 (sigh)... use dbdef type (char, etc.) instead of a regex to decide what to quote in _quote (more sillines...) SQL bites. ivan@sisd.com 98-feb-20 more friendly error messages ivan@sisd.com 98-mar-13 Added import of datasrc from FS::UID to allow Pg6.3 to work Added code to right-trim strings read from Pg6.3 databases Modified 'add' to only insert fields that actually have data Added ut_float to handle floating point numbers (for sales tax). Pg6.3 does not have a "SHOW FIELDS" statement, so I faked it 8). bmccane@maxbaud.net 98-apr-3 commented out Pg wrapper around `` Modified 'add' to only insert fields that actually have data '' ivan@sisd.com 98- apr-16 dbdef usage changes ivan@sisd.com 98-jun-1 sub fields now asks dbdef, not database ivan@sisd.com 98- jun-2 added debugging method ->_dump ivan@sisd.com 98-jun-16 use FS::dbdef::primary key in delete searches as well as replace searches (SQL still bites) ivan@sisd.com 98-jun-22 sub dbdef_table ivan@sisd.com 98-jun-28 removed Pg wrapper around `` Modified 'add' to only insert fields that actually have data '' ivan@sisd.com 98-jul-14 sub fields croaks on errors ivan@sisd.com 98-jul-17 $rc eq '0E0' doesn't mean we couldn't delete for all rdbmss ivan@sisd.com 98-jul-18 commented out code to right-trim strings read from Pg6.3 databases; ChopBlanks is in UID.pm ivan@sisd.com 98-aug-16 added code (with Pg wrapper) to deal with Pg money fields ivan@sisd.com 98-aug-18 added pod documentation ivan@sisd.com 98-sep-6 ut_phonen got ''; at the end ivan@sisd.com 98-sep-27 $Log: Record.txt,v $ Revision 1.3 1998-11-13 09:56:36 ivan change configuration file layout to support multiple distinct databases (with own set of config files, export, etc.) Revision 1.4 1998/11/10 07:45:25 ivan doc clarification Revision 1.2 1998/11/07 05:17:18 ivan In sub new, Pg wrapper for money fields from dbdef (FS::Record::fields $table), not keys of supplied hashref.