Difference between revisions of "Freeside:3:Documentation:Developer/FS/Record"

Revision as of 06:35, 24 March 2015

NAME

FS::Record - Database record objects

SYNOPSIS

   use FS::Record;
   use FS::Record qw(dbh fields qsearch qsearchs);

   $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->insert;

   $error = $record->delete;

   $error = $new_record->replace($old_record);

   # external use deprecated - handled by the database (at least for Pg, mysql)
   $value = $record->unique('column');

   $error = $record->ut_float('column');
   $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');
   $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');

   $quoted_value = _quote($value,'table','field');

   #deprecated
   $fields = hfields('table');
   if ( $fields->{Field} ) { # etc.

   @fields = fields 'table'; #as a subroutine
   @fields = $record->fields; #as a method call

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.

CONSTRUCTORS

new [ TABLE, ] HASHREF

Creates a new record. It doesn't store it in the database, though. See "insert" 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.

TABLE can only be omitted when a dervived class overrides the table method.

qsearch PARAMS_HASHREF | TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ, ADDL_FROM

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.

The preferred usage is to pass a hash reference of named parameters:

 @records = qsearch( {
                       'table'       => 'table_name',
                       'hashref'     => { 'field' => 'value'
                                          'field' => { 'op'    => '<',
                                                       'value' => '420',
                                                     },
                                        },

                       #these are optional...
                       'select'      => '*',
                       'extra_sql'   => 'AND field = ? AND intfield = ?',
                       'extra_param' => [ 'value', [ 5, 'int' ] ],
                       'order_by'    => 'ORDER BY something',
                       #'cache_obj'   => '', #optional
                       'addl_from'   => 'LEFT JOIN othtable USING ( field )',
                       'debug'       => 1,
                     }
                   );

Much code still uses old-style positional parameters, this is also probably fine in the common case where there are only two parameters:

 my @records = qsearch( 'table', { 'field' => 'value' } );

Also possible is an experimental LISTREF of PARAMS_HASHREFs for a UNION of the individual PARAMS_HASHREF queries

1. 1. 1. oops, argh, FS::Record::new only lets us create database fields. #Normal behaviour if SELECT is not specified is `*', as in #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.

_query

Construct the SQL statement and parameter-binding list for qsearch. Takes the qsearch parameters.

Returns a hash containing: 'table': The primary table name (if there is one). 'statement': The SQL statement itself. 'bind_type': An arrayref of bind types. 'value': An arrayref of parameter values. 'cache': The cache object, if one was passed.

by_key PRIMARY_KEY_VALUE

This is a class method that returns the record with the given primary key value. This method is only useful in FS::Record subclasses. For example:

 my $cust_main = FS::cust_main->by_key(1); # retrieve customer with custnum 1

is equivalent to:

 my $cust_main = qsearchs('cust_main', { 'custnum' => 1 } );

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:

qsearchs PARAMS_HASHREF | TABLE, HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ, ADDL_FROM

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.

METHODS

table

Returns the table name.

dbdef_table

Returns the DBIx::DBSchema::Table object for the table.

primary_key

Returns the primary key 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.

exists COLUMN

Returns true if the column/field/key COLUMN exists.

AUTLOADED METHODS

$record->column is a synonym for $record->get('column');

$record->column('value') is a synonym for $record->set('column','value');

$record->foreign_table_name calls qsearchs and returns a single FS::foreign_table record (for tables referenced by a column of this table) or qsearch and returns an array of FS::foreign_table records (for tables referenced by a column in the foreign table).

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. This may be deprecated in the future; if there's a reason you can't just use the autoloaded or get/set methods, speak up.

modified

Returns true if any of this object's values have been modified with set (or via an autoloaded method). Doesn't yet recognize when you retreive a hashref and modify that.

select_for_update

Selects this record with the SQL "FOR UPDATE" command. This can be useful as a mutex.

lock_table

Locks this table with a database-driver specific lock method. This is used as a mutex in order to do a duplicate search.

For PostgreSQL, does "LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE".

For MySQL, does a SELECT FOR UPDATE on the duplicate_lock table.

Errors are fatal; no useful return value.

Note: To use this method for new tables other than svc_acct and svc_phone, edit freeside-upgrade and add those tables to the duplicate_lock list.

insert

Inserts this record to the database. If there is an error, returns the error, otherwise returns false.

add

Depriciated (use insert instead).

delete

Delete this record from the database. If there is an error, returns the error, otherwise returns false.

del

Depriciated (use delete instead).

replace OLD_RECORD

Replace the OLD_RECORD with this one in the database. If there is an error, returns the error, otherwise returns false.

rep

Depriciated (use replace instead).

check

Checks custom fields. Subclasses should still provide a check method to validate non-custom fields, etc., and call this method via $self->SUPER::check.

virtual_fields [ TABLE ]

Returns a list of virtual fields defined for the table. This should not be exported, and should only be called as an instance or class method.

process_batch_import JOB OPTIONS_HASHREF PARAMS

Processes a batch import as a queued JSRPC job

JOB is an FS::queue entry.

OPTIONS_HASHREF can have the following keys:

table

Table name (required).

params

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.

formats

Formats hashref. Keys are field names, values are listrefs that define the format.

Each listref value can be a column name or a code reference. Coderefs are run with the row object, data and a FS::Conf object as the three parameters. For example, this coderef does the same thing as using the "columnname" string:

 sub {
   my( $record, $data, $conf ) = @_;
   $record->columnname( $data );
 },

Coderefs are run after all "column name" fields are assigned.

format_types

Optional format hashref of types. Keys are field names, values are "csv", "xls" or "fixedlength". Overrides automatic determination of file type from extension.

format_headers

Optional format hashref of header lines. Keys are field names, values are 0 for no header, 1 to ignore the first line, or to higher numbers to ignore that number of lines.

format_sep_chars

Optional format hashref of CSV sep_chars. Keys are field names, values are the CSV separation character.

format_fixedlenth_formats

Optional format hashref of fixed length format defintiions. Keys are field names, values Parse::FixedLength listrefs of field definitions.

default_csv

Set true to default to CSV file type if the filename does not contain a recognizable ".csv" or ".xls" extension (and type is not pre-specified by format_types).

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".

batch_import PARAM_HASHREF

Class method for batch imports. Available params:

table

; format - usual way to specify import, with this format string selecting data from the formats and format_* info hashes:; formats:; format_types:; format_headers:; format_sep_chars:; format_fixedlength_formats:; format_row_callbacks:; format_hash_callbacks - After parsing, before object creation:; fields - Alternate way to specify import, specifying import fields directly as a listref:; preinsert_callback:; postinsert_callback:; params:; job

FS::queue object, will be updated with progress

file

; type

csv, xls, fixedlength, xml

empty_ok; unique COLUMN

Warning: External use is deprecated.

Replaces COLUMN in record with a unique number, using counters in the filesystem. Used by the insert method on single-field unique columns (see DBIx::DBSchema::Table) and also as a fallback for primary keys that aren't SERIAL (Pg) or AUTO_INCREMENT (mysql).

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_floatn COLUMN

Check/untaint floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be null. If there is an error, returns the error, otherwise returns false.

ut_sfloat COLUMN

Check/untaint signed 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_sfloatn COLUMN

Check/untaint signed floating point numeric data: 1.1, 1, 1.1e10, 1e10. May be null. If there is an error, returns the error, otherwise returns false.

ut_snumber COLUMN

Check/untaint signed numeric data (whole numbers). If there is an error, returns the error, otherwise returns false.

ut_snumbern COLUMN

Check/untaint signed numeric data (whole numbers). 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_decimal COLUMN[, DIGITS]

Check/untaint decimal numbers (up to DIGITS decimal places. If there is an error, returns the error, otherwise returns false.

ut_decimaln COLUMN[, DIGITS]

Check/untaint decimal 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_moneyn COLUMN

Check/untaint monetary numbers. May be negative. If there is an error, returns the error, otherwise returns false.

ut_currencyn COLUMN

Check/untaint currency indicators, such as USD or EUR. May be null. If there is an error, returns the error, otherwise returns false.

ut_currency COLUMN

Check/untaint currency indicators, such as USD or EUR. May not be 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_alphan COLUMN

Check/untaint alphanumeric strings (no spaces). May be null. If there is an error, returns the error, otherwise returns false.

ut_alphasn COLUMN

Check/untaint alphanumeric strings, spaces allowed. May be null. If there is an error, returns the error, otherwise returns false.

ut_alpha_lower COLUMN

Check/untaint lowercase alphanumeric strings (no spaces). May not be null. If there is an error, returns the error, otherwise returns false.

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 3166-1 alpha-2 country code; without it or with unsupported countries, ut_phonen simply calls ut_alphan.

ut_hex COLUMN

Check/untaint hexadecimal values.

ut_hexn COLUMN

Check/untaint hexadecimal values. May be null.

ut_mac_addr COLUMN

Check/untaint mac addresses. May be null.

ut_mac_addrn COLUMN

Check/untaint mac addresses. May be null.

ut_ip COLUMN

Check/untaint ip addresses. IPv4 only for now, though ::1 is auto-translated to 127.0.0.1.

ut_ipn COLUMN

Check/untaint ip addresses. IPv4 only for now, though ::1 is auto-translated to 127.0.0.1. May be null.

ut_ip46 COLUMN

Check/untaint IPv4 or IPv6 address.

ut_ip46n

Check/untaint IPv6 or IPv6 address. May be null.

ut_coord COLUMN [ LOWER [ UPPER ] ]

Check/untaint coordinates. Accepts the following forms: DDD.DDDDD -DDD.DDDDD DDD MM.MMM -DDD MM.MMM DDD MM SS -DDD MM SS DDD MM MMM -DDD MM MMM

The "DDD MM SS" and "DDD MM MMM" are potentially ambiguous. The latter form (that is, the MMM are thousands of minutes) is assumed if the "MMM" is exactly three digits or two digits > 59.

To be safe, just use the DDD.DDDDD form.

If LOWER or UPPER are specified, then the coordinate is checked for lower and upper bounds, respectively.

ut_coordn COLUMN [ LOWER [ UPPER ] ]

Same as ut_coord, except optionally null.

ut_domain COLUMN

Check/untaint host and domain names. May not be null.

ut_domainn COLUMN

Check/untaint host and domain names. May be null.

ut_name COLUMN

Check/untaint proper names; allows alphanumerics, spaces and the following punctuation: , . - '

May not be null.

ut_namen COLUMN

Check/untaint proper names; allows alphanumerics, spaces and the following punctuation: , . - '

May not be null.

ut_zip COLUMN

Check/untaint zip codes.

ut_country COLUMN

Check/untaint country codes. Country names are changed to codes, if possible - see Locale::Country.

ut_anything COLUMN

Untaints arbitrary data. Be careful.

ut_enum COLUMN CHOICES_ARRAYREF

Check/untaint a column, supplying all possible choices, like the "enum" type.

ut_enumn COLUMN CHOICES_ARRAYREF

Like ut_enum, except the null value is also allowed.

ut_flag COLUMN

Check/untaint a column if it contains either an empty string or 'Y'. This is the standard form for boolean flags in Freeside.

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.

ut_foreign_keyn COLUMN FOREIGN_TABLE FOREIGN_COLUMN

Like ut_foreign_key, except the null value is also allowed.

ut_agentnum_acl COLUMN [ NULL_RIGHT | NULL_RIGHT_LISTREF ]

Checks this column as an agentnum, taking into account the current users's ACLs. NULL_RIGHT or NULL_RIGHT_LISTREF, if specified, indicates the access right or rights allowing no agentnum.

fields [ TABLE ]

This is a wrapper for real_fields. Code that called fields before should probably continue to call fields.

encrypt($value)

Encrypts the credit card using a combination of PK to encrypt and uuencode to armour.

Returns the encrypted string.

You should generally not have to worry about calling this, as the system handles this for you.

is_encrypted($value)

Checks to see if the string is encrypted and returns true or false (1/0) to indicate it's status.

decrypt($value)

Uses the private key to decrypt the string. Returns the decryoted string or undef on failure.

You should generally not have to worry about calling this, as the system handles this for you.

h_search ACTION

Given an ACTION, either "insert", or "delete", returns the appropriate history record corresponding to this record, if any.

h_date ACTION

Given an ACTION, either "insert", or "delete", returns the timestamp of the appropriate history record corresponding to this record, if any.

scalar_sql SQL [ PLACEHOLDER, ... ]

A class or object method. Executes the sql statement represented by SQL and returns a scalar representing the result: the first column of the first row.

Dies on bogus SQL. Returns an empty string if no row is returned.

Typically used for statments which return a single value such as "SELECT COUNT(*) FROM table WHERE something" OR "SELECT column FROM table WHERE key = ?"

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 associated table.

row_exists [ WHERE [, PLACEHOLDER ...] ]

Convenience method for the common case of "SELECT 1 FROM table ... LIMIT 1" with optional (but almost always needed) WHERE.

SUBROUTINES

real_fields [ TABLE ]

Returns a list of the real columns in the specified table. Called only by fields() and other subroutines elsewhere in FS::Record.

pvf FIELD_NAME

Returns the FS::part_virtual_field object corresponding to a field in the record (specified by FIELD_NAME).

_quote VALUE, TABLE, COLUMN

This is an internal function used to construct SQL statements. It returns VALUE DBI-quoted (see "quote" in DBI|DBI#quote|"quote" in DBI) unless VALUE is a number and the column type (see DBIx::DBSchema::Column) does not end in `char' or `binary'.

hfields TABLE

This is deprecated. Don't use it.

It returns a hash-type list with the fields of this record's table set true.

str2time_sql [ DRIVER_NAME ]

Returns a function to convert to unix time based on database type, such as "EXTRACT( EPOCH FROM" for Pg or "UNIX_TIMESTAMP(" for mysql. See the str2time_sql_closing method to return a closing string rather than just using a closing parenthesis as previously suggested.

You can pass an optional driver name such as "Pg", "mysql" or $dbh->{Driver}->{Name} to return a function for that database instead of the current database.

str2time_sql_closing [ DRIVER_NAME ]

Returns the closing suffix of a function to convert to unix time based on database type, such as ")::integer" for Pg or ")" for mysql.

You can pass an optional driver name such as "Pg", "mysql" or $dbh->{Driver}->{Name} to return a function for that database instead of the current database.

regexp_sql [ DRIVER_NAME ]

Returns the operator to do a regular expression comparison based on database type, such as '~' for Pg or 'REGEXP' for mysql.

You can pass an optional driver name such as "Pg", "mysql" or $dbh->{Driver}->{Name} to return a function for that database instead of the current database.

not_regexp_sql [ DRIVER_NAME ]

Returns the operator to do a regular expression negation based on database type, such as '!~' for Pg or 'NOT REGEXP' for mysql.

You can pass an optional driver name such as "Pg", "mysql" or $dbh->{Driver}->{Name} to return a function for that database instead of the current database.

concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF

Returns the items concatenated based on database type, using "CONCAT()" for mysql and " || " for Pg and other databases.

You can pass an optional driver name such as "Pg", "mysql" or $dbh->{Driver}->{Name} to return a function for that database instead of the current database.

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.

midnight_sql DATE

Returns an SQL expression to convert DATE (a unix timestamp) to midnight on that day in the system timezone, using the default driver name.

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 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.)

The whole fields / hfields mess should be removed.

The various WHERE clauses should be subroutined.

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.

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 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 only checks 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.

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.

SEE ALSO

DBIx::DBSchema, FS::UID, DBI

Adapter::DBI from Ch. 11 of Advanced Perl Programming by Sriram Srinivasan.

http://poop.sf.net/