DB::Table::Row - provide an interface to a row within a DB::Table object.

SYNOPSIS

use DB::Table::Row;
my $row = DB::Table::Row->construct($dbh, $table, $hashRef);
$row->insert() || check_for_errors();

my $userId = $row->userId();
my $userId = $row->getValue('userId');
$row->username('newUserName');
$row->setValue('username', 'newUserName');
$row->update() || check_for_errors();

my $row = DB::Table::Row->getByPKey($dbh, $table, $userId);
$row->delete();

DESCRIPTION

DB::Table::Row provides an interface to a row in a given database table. It does its best to prevent the need for writing any custom SQL and also understands foreign-key relationships and implements constraints on databases which understand the CASE ... END clause.

This module gets its understanding of the table structure that the row is from by being passed an instance of a DB::Table object (or a sub-class there-of).

METHODS

Class Methods

my $row = DB::Table::Row->construct($dbh, $table, $hashRef);

This method is used to construct a row object with the structure defined by $table, with values defined in $hashRef. This method does not actually insert the row into the given table (See the insert() method), but rather just constructs in in-memory representation of the row.

my @rows = DB::Table::Row->getByPKey($dbh, $table, @keys);

This method is used to fetch one or more rows using primary keys.

If used in scalar context (my $row = DB::Table::Row->getByPKey()) then only the first row is actually returned, while in array context all rows are returned.

If no primary keys are specified, then all rows in the table are returned (use with caution on very large tables).

You may, however, select a subset of the expected rows by supplying a hash-ref as the first key, with 'pageLength' being the number of rows you'd like, and 'pageOffset' being the row to start counting from. For example:

my @rows = DB::Table::Row->getByPKey($dbh, $table, {pageOffset => 100,
                                                    pageLength => 10}
                                     [, @id_list]);
Will only select rows 100 - 110 from the database. You may still supply
an ID list if you choose, in which case you will get back a subset of
rows with the IDs you specify.

Rows are always returned in ncrementing primary key order, ie row[n]'s primary key will always be smaller than row[n + 1]'s primary key.

my @rows = DB::Table::Row->getRowsWhere($dbh, $table, $whereClause, $bindParams, $options);

This method allows for you to retrieve rows from the database table using a custom where clause, defined by the string $whereClause. Any place-holders to be interpolated should contain question marks, as with the DBI, and the actual values should be passed in an array-reference specifined by $bindParams. $options is an optional hash-reference which can define other selection options, such as how to order the results and limit the results returned. It has 3 elements: pageLength and pageOffset, and orderBy. pageLength specifies how many rows to return, while pageOffset specifies where to start counting from. orderBy can be a string such as 'username, password' which will then order the results first by the username column, then by the password column. By default, rows are ordered by primary key.

Example:

  my $username = 'bradley';
  my $password = 'as if';
  my @rows = DB::Table::Row->getRowsWhere($dbh, $table,
                "username = ? AND password = ?",
                [$username, $password],
                {pageLength => 1, pageOffset 0});

The above will select the first row from the table specified by the DB::Table object $table, with the given username and password.

my @rows = DB::Table::Row->getByFKey($dbh, $table, $fKeyName, @FKeys);

This method is used to get all rows from the specified $table object, where $fKeyName is in the list of FKeys. This method can be used to select rows where a non-primary-key field is equal to a value supplied in the list of @FKeys. For example, if you want to get all rows who's 'owner_id' field is equal to 5, you might say:

my @row = DB::Table::Row->getByFKey($dbh, $table, 'owner_id', 5);

my @rows = DB::Table::Row->searchByString($dbh, $table, $searchString, $options);

This method allows a free-text search to be performed on the database. All non-primary-key fields are cast to a string and then search using the LIKE clause. Searching is not case-sensitive either.

$options may specify aditionaly options for limiting the result or ordering. See the getRowsWhere() method for more details.

TODO: There is not yet a way to limit which fields are searched, and foreign key's are not "de-referenced".

Object Methods

$row->insert();

Once constructed, the row can be inserted into the database. If the row cannot be inserted into the database, then undef is returned (You should then check validationError(), and then use getValidationError() on each field to see the reason why the row could not be inserted.

$row->update();

If any changes are made to the values of a row, then when the row gets DESTORY'd, the changes will automatically be saved back to the database. However, you may wish explicitly call update() for two reasons: You may want the changes saved to the datbase before the row object goes out of scope, and (more importantly) you should check the return value of the call to update() to catch any invalid values.

If the row could not be updated in the database, then undef is returned (You should then check validationError(), and then use getValidationError() on each field to see the reason why the row could not be inserted.

$row->delete();

This method deletes the row from the database, and marks it as deleted so that futher access to the object can be made. You can still use a deleted object to construct() a new one though.

my $foreignRow = $row->getFKey($foreignKeyField);

Where $foreignKeyField in $row references a row in another table, this method can be used to fetch the referenced row.

my $foreignTable = $row->getFTable($foreignKeyField);

Where $foreignKeyField in $row references a row in another table, this method can be used to return the referenced table object.

my $value = $row->getValue($fieldName);

This method is used to get the value of the field/column specified by $fieldName.

fieldName's are also autoloaded, so you can also say:

my $value = $row->$fieldName();

my $oldValue = $row->setValue($fieldName, $newValue);

This method is used to set a new value for the field specified by $fieldName.

The old value is returned.

fieldName's are also autoloaded, so you can also say:

my $oldValue = $row->$fieldName($newValue);

my $didError = $row->validationError();

This method returns true if an insert() or update() failed because of a validation error (false otherwise).

If this method returns true, you should iterate through each field and call getValidationError() to see which field failed validation, and why.

my $errorMsg = $row->getValidationError($fieldName);

If an insert() or update() failed because of a validation error (check with validationError then you should iterate through each field and call getValidationError to get the error message associated with each field. Fields without an error message passed validation, hence undef is returned.

my $didValidate = $row->validate();

Validation is automatically performed when ever needed (before the row is inserted or updated in the database) and as such you probably wont need to call this explicitly, however, it is here in case you do.

If all values are considered legal, then true is returned, undef otherwise.

The exception is that validation is performed in such a way that the database never has to read/write any rows in the database, and as such duplicate key checking is not performed here. Instead, if validation passes, the record will be then be inserted/updated and if this fails because of a duplicate key then this is trapped and undef is then returned by the insert/update method (After setting the various validation-related error flags).

AUTHOR

Bradley Kite <bradley-cpan@kitefamily.co.uk>

If you wish to email me, then please remove the '-cpan' part of my email address as anything addressed to 'bradley-cpan' is assumed to be spam and is not read.

SEE ALSO

DB::Table, DBI, perl