Take me over?
NAME
Alzabo::Runtime::Table - Table objects
SYNOPSIS
my $table = $schema->table('foo');
my $row = $table->row_by_pk( pk => 1 );
my $row_cursor =
$table->rows_where
( where =>
[ Alzabo::Column object, '=', 5 } );DESCRIPTION
This object is able to create rows, either by making objects based on existing data or inserting new data to make new rows.
This object also implements a method of lazy column evaluation that can be used to save memory and database wear and tear, though it needs to be used carefully. Please see methods as well as "LAZY COLUMN LOADING" for details.
INHERITS FROM
Alzabo::Table
METHODS
Methods that return an Alzabo::Runtime::Row object
insert
Inserts the given values into the table. If no values are given for a primary key column and the column is sequenced then the values will be generated from the sequence.
Parameters
values => $hashref
The hashref contains column names and values for the new row. This parameter is optional.
All other parameters given will be passed directly to the Alzabo::Runtime::Row->new method.
Returns
A new Alzabo::Runtime::Row object.
Throws
row_by_pk
The primary key can be either a simple scalar, as when the table has a single primary key, or a hash reference of column names to primary key values when the primary key is more than one column.
Parameters
pk => $pk_val or \%pk_val
All other parameters given will be passed directly to the Alzabo::Runtime::Row->new method.
Returns
A new Alzabo::Runtime::Row object. If no rows in the database match the value(s) given then an empty list or undef will be returned (for list or scalar context).
Throws
row_by_id
This method is useful for regenerating a row that has been saved by reference to its id (returned by the Alzabo::Runtime::Row->id method). This may be more convenient than saving a multi-column primary key when trying to maintain state in a web app, for example.
Parameters
row_id => $row_id
A string representation of a row's id (as returned by the Alzabo::Runtime::Row->id method).
All other parameters given will be passed directly to the Alzabo::Runtime::Row->new method.
Returns
A new Alzabo::Runtime::Row object. If no rows in the database match the value(s) given then an empty list or undef will be returned (for list or scalar context).
Common Parameters
A number of methods in this clas take the same parameters. These are documented below.
where => [
Alzabo::Columnobject or SQL function, $comparison, $value orAlzabo::Columnobject ]This parameter can take a variety of values. It can take a single array reference as shown above. The
$comparisonshould be a string containing a SQL operator such as'>'or'='.The parameter can also be an array of references to such arrays:
[ [ Alzabo::Column object or SQL function, $comparison, $value or Alzabo::Column object ], [ Alzabo::Column object or SQL function, $comparison, $value or Alzabo::Column object ], ... ]For more details on exactly what the possibilities are here, please see the documentation for Alzabo::SQLMaker.
By default, each clause represented by an array reference is joined together with an 'AND'. However, you can put the string 'or' between two array references to cause them to be joined with an 'OR', such as:
[ [ $foo_col, '=', 5 ], 'or', [ $foo_col, '>', 10 ] ]which would generate SQL something like:
WHERE foo = 5 OR foo > 10If you want to be explicit, you can also use the string 'and'.
If you need to group conditionals you can use '(' and ')' characters in between array references representing a conditional. For example:
[ [ $foo_col, '=', 5 ], '(', [ $foo_col, '>', 10 ] 'or', [ $bar_col, '<', 50, ')' ], ')' ]which would generate SQL something like:
WHERE foo = 5 AND ( foo > 10 OR bar < 50 )Make sure that your parentheses balance out or an exception will be thrown.
You can also use the SQL functions (Using SQL functions) exported from the SQLMaker subclass you are using. For example:
[ LENGTH($foo_col), '<', 10 ]would generate something like:
WHERE LENGTH(foo) < 10order_by => see below
This parameter can take one of two different values. The simplest form is to just give it a single column object or SQL function. Alternatively, you can give it an array reference to a list of column objects, SQL functions and strings like this:
order_by => [ $col1, COUNT('*'), $col2, 'DESC', $col3, 'ASC' ]It is important to note that you cannot simply use any old SQL function as part of your order by clause. You need to use a function that is exactly the same as one that was given as part of the select parameter.
group_by => see below
This parameter can take either a single column object or an array of column objects.
having => same as "where"
This parameter is specified in the same way as the "where" parameter.
limit => $limit or [ $limit, $offset ]
For databases that support LIMIT clauses, this incorporates such a clause into the SQL. For databases that don't, the limit will be implemented programatically as rows are being requested. If an offset is given, this will be the number of rows skipped in the result set before the first one is returned.
Methods that return an Alzabo::Runtime::RowCursor object
The rows_where and all_rows methods both return an Alzabo::Runtime::RowCursor object representing the results of the query. This is the case even for queries that end up returning one or zero rows, because Alzabo cannot know in advance how many rows these queries will return.
rows_where
A simple way to retrieve a row cursor based on one or more colum values. This does not handle any conditionals besides equality.
Parameters
where => see Common Parameters
order_by => see Common Parameters
limit => see Common Parameters
All other parameters given will be passed directly to the Alzabo::Runtime::Row->new method.
Given these items this method generates SQL that will retrieve a set of primary keys for the table.
Returns
An Alzabo::Runtime::RowCursor object representing the query.
all_rows
Simply returns all the rows in the table.
Parameters
order_by => see Common Parameters
limit => see Common Parameters
All other parameters given will be passed directly to the Alzabo::Runtime::Row->new method.
Returns
An Alzabo::Runtime::RowCursor object representing the query.
one_row
This method takes the exact same parameters as the rows_where method but instead of returning a cursor, it returns a single row. This object will be the row for the first id returned by the database.
potential_row
This method is used to create a new Alzabo::Runtime::PotentialRow object.
Parameters
values => \%values
This should be a hash reference containing column names, just as is given to insert.
It is ok to omit columns that are normally not nullable, but they cannot be explicitly set to null.
Any values given will be set in the new potential row object.
Returns
A new Alzabo::Runtime::PotentialRow object.
Other Methods
row_count
Parameters
where => see Common Parameters
Returns
A scalar indicating how many rows the table has.
function and select
These two methods differ only in their return values.
Parameters
select => $function or [ scalars, SQL functions and/or
Alzabo::Columnobjects ]If you pass an array reference for this parameter, it may contain scalars, SQL functions, or column objects. For example:
$table->function( select => [ 1, $table->column('name'), LENGTH( $table->column('name') ) ] );where => see Common Parameters
order_by => see Common Parameters
group_by => see Common Parameters
limit => see Common Parameters
This method is used to call arbitrary SQL functions such as 'AVG' or 'MAX'. The function (or functions) should be the return values from the functions exported by the SQLMaker subclass that you are using. Please see Using SQL functions for more details.
Returns
function
The return value of this method is highly context sensitive.
If you only requested a single function ( DISTINCT(foo) ), then it returns the first value in scalar context and all the values in list context.
If you requested multiple functions ( AVG(foo), MAX(foo) ) then it returns a single array reference (the first row of values) in scalar context and a list of array references in list context.
select
This method always returns a new Alzabo::DriverStatement object containing the results of the query.
alias
Returns
An object which can be used in joins to allow a particular table to be involved in the join under multiple aliases. This allows for self-joins as well as more complex joins involving multiple aliases of a given table.
The object returned by this method is more or less identical to a table object in terms of the methods it supports. This includes methods that were generated by Alzabo::MethodMaker.
However, this object should not be used outside the context of a join query because the results will be unpredictable. In addition, the column objects that the aliased table object returns should also not be used outside of the context of a join.
LAZY COLUMN LOADING
This concept was taken directly from Michael Schwern's Class::DBI module (credit where it is due).
This lazy loading is only done when caching is turned on. Otherwise, Alzabo always fetches data from the database when it is requested and does not store it locally in memory at all. In fact, trying to use lazy column loading without caching will simply slow things down.
By default, Alzabo::Runtime::Row objects only load data from the database as it is requested via the select method. This is stored internally in the object after being fetched.
This is good because it saves on memory and makes object creation quicker, but it is bad because you could potentially end up with one SQL call per column (excluding primary key columns, which are usually not fetched from the database).
This class provides two method to help you handle this potential problem. Basically these methods allow you to declare usage patterns for the table.
The first method, set_prefetch, allows you to specify a list of columns to be fetched immediately after object creation. These should be columns that you expect to use extremely frequently.
The second method, add_group, allows you to group columns together. If you attempt to fetch one of these columns, then all the columns in the group will be fetched. This is useful in cases where you don't often want certain data, but when you do you need several related pieces.
Lazy column loading related methods
set_prefetch (Alzabo::Column objects)
Given a list of column objects, this makes sure that all Alzabo::Runtime::Row objects fetch this data as soon as they are created.
This method is only effective when caching is turned on, as otherwise prefetched data may be stale and we would never know this.
NOTE: It is pointless (though not an error) to give primary key column here as these are always prefetched (in a sense).
Throws
add_group (Alzabo::Column objects)
Given a list of Alzabo::Column objects, this method creates a group containing these columns. This means that if any column in the group is fetched from the database, then they will all be fetched. Otherwise column are always fetched singly. Currently, a column cannot be part of more than one group.
This method is only effective when caching is turned on, as otherwise group-fetched data may be stale and we would never know this.
NOTE: It is pointless to include a column that was given to the set_prefetch method in a group here, as it always fetched as soon as possible.
Throws
prefetch
This method primarily exists for use by the Alzabo::Runtime::Row class.
Returns
A list of column names (not objects) that should be prefetched.
group_by_column ($column_name)
This method primarily exists for use by the Alzabo::Runtime::Row class.
Returns
A list of column names representing the group that this column is part of. If the column named is not part of a group, only the name passed in is returned.
AUTHOR
Dave Rolsky, <autarch@urth.org>