DBIx::DataModel::Statement - DBIx::DataModel statement objects
# statement creation my $stmt = DBIx::DataModel::Statement->new($source, @args); # or my $stmt = My::Table->select(-resultAs => 'statement'); #or my $stmt = My::Table->join(qw/role1 role2 .../); # statement refinement (adding clauses) $stmt->refine(-where => {col1 => {">" => 123}, col2 => "?foo"}) # ?foo is a named placeholder $stmt->refine(-where => {col3 => 456, col4 => "?bar", col5 => {"<>" => "?foo"}}, -orderBy => ...); # early binding for named placeholders $stmt->bind(bar => 987); # database prepare (with optional further refinements to the statement) $stmt->prepare(-columns => qw/.../); # late binding for named placeholders $stmt->bind(foo => 654); # database execute (with optional further bindings) $stmt->execute(foo => 321); # get the results my $list = $stmt->all; #or while (my $row = $stmt->next) { ... }
The purpose of a statement object is to retrieve rows from the database and bless them as objects of appropriate table or view classes.
Internally the statement builds and then encapsulates a DBI statement handle (sth).
DBI
The design principles for statements are described in the DESIGN section of the manual (purpose, lifecycle, etc.).
my $statement = DBIx::DataModel::Statement->new($source, @args);
Creates a new statement. The first parameter $source is a subclass of DBIx::DataModel::Table or DBIx::DataModel::View. Other parameters are optional and directly transmitted to "refine".
$source
Returns a copy of the statement. This is only possible when in states new or sqlized, i.e. before a DBI sth has been created.
new
sqlized
Returns the current status or the statement. This is a dualvar with a string component (new, sqlized, prepared, executed) and an integer component (1, 2, 3, 4).
prepared
executed
$sql = $statement->sql; (sql, @bind) = $statement->sql;
In scalar context, returns the SQL code for this statement (or undef if the statement is not yet sqlized).
undef
In list context, returns the SQL code followed by the bind values, suitable for a call to "execute" in DBI.
Obviously, this method is only available after the statement has been sqlized (through direct call to the "sqlize" method, or indirect call via "prepare", "execute" or "select").
$statement->bind(foo => 123, bar => 456); $statement->bind({foo => 123, bar => 456}); # equivalent to above $statement->bind(0 => 123, 1 => 456); $statement->bind([123, 456]); # equivalent to above
Takes a list of bindings (name-value pairs), and associates them to placeholders within the statement. If successive bindings occur on the same named placeholder, the last value silently overrides previous values. If a binding has no corresponding named placeholder, it is ignored. Names can be any string (including numbers), except reserved words limit and offset, which have a special use for pagination.
limit
offset
The list may alternatively be given as a hashref. This is convenient for example in situations like
my $statement = $source->some_method; foreach my $row (@{$source->select}) { my $subrows = $statement->bind($row)->select; }
The list may also be given as an arrayref; this is equivalent to a hashref in which keys are positions within the array.
Finally, there is a ternary form of bind for passing DBI-specific arguments.
bind
use DBI qw/:sql_types/; $statement->bind(foo => $val, {TYPE => SQL_INTEGER});
See "bind_param" in DBI for explanations.
$statement->refine(%args);
Set up some named parameters on the statement, that will be used later by the select method (see that method for a complete list of available parameters).
select
The main use of refine is to set up some additional -where conditions, like in
refine
-where
$statement->refine(-where => {col1 => $value1, col2 => {">" => $value2}});
These conditions are accumulated into the statement, implicitly combined as an AND, until generation of SQL through the sqlize method. After this step, no further refinement is allowed.
sqlize
The -where parameter is the only one with a special combinatory logic. Other named parameters to refine, like -columns, -orderBy, etc., are simply stored into the statement, for later use by the select method; the latest specified value overrides any previous value.
-columns
-orderBy
$statement->sqlize(@args);
Generates SQL from all parameters accumulated so far in the statement. The statement switches from state new to state sqlized, which forbids any further refinement of the statement (but does not forbid further bindings).
Arguments are optional, and are just a shortcut instead of writing
$statement->refine(@args)->sqlize;
$statement->prepare(@args);
Method sqlized is called automatically if necessary. Then the SQL is sent to the database, and the returned DBI sth is stored internally within the statement. The state switches to "prepared".
sth
$statement->sqlize(@args)->prepare;
$statement->execute(@bindings);
Translates the internal named bindings into positional bindings, calls "execute" in DBI on the internal sth, and applies the -preExec and -postExec callbacks if necessary. The state switches to "executed".
-preExec
-postExec
$statement->bind(@bindings)->execute;
An executed statement can be executed again, possibly with some different bindings. When this happens, the internal result set is reset, and fresh data rows can be retrieved through the "next" or "all" methods.
This is the frontend method to most methods above: it will automatically take the statement through the necessary state transitions, passing appropriate arguments at each step. The select API is complex and is fully described in "select" in DBIx::DataModel::Doc::Reference.
Returns the number of rows corresponding to the current executed statement. Raises an exception if the statement is not in state "executed".
Note : usually this involves an additional call to the database (SELECT COUNT(*) FROM ...), unless the database driver implements a specific method for counting rows (see for example DBIx::DataModel::Statement::JDBC).
SELECT COUNT(*) FROM ...
Returns the index number of the next row to be fetched (starting at $self->offset, or 0 by default).
$self->offset
while (my $row = $statement->next) {...} my $slice_arrayref = $statement->next(10);
If called without argument, returns the next data row, or undef if there are no more data rows. If called with a numeric argument, attempts to retrieve that number of rows, and returns an arrayref; the size of the array may be smaller than required, if there were no more data rows. The numeric argument is forbidden on fast statements (i.e. when "reuseRow" has been called).
Each row is blessed into an object of the proper class, and is passed to the -postBless callback (if applicable).
-postBless
my $rows = $statement->all;
Similar to the next method, but returns an arrayref containing all remaining rows. This method is forbidden on fast statements (i.e. when "reuseRow" has been called).
next
Returns the page size (requested number of rows), as it was set through the -pageSize argument to refine() or select().
-pageSize
refine()
select()
Returns the current page index (starting at 1). Always returns 1 if no pagination is activated (no -pageSize argument was provided).
Returns the current requested row offset (starting at 0). This offset changes when a request is made to go to another page; but it does not change when retrieving successive rows through the "next" method.
Calls "rowCount" to get the total number of rows for the current statement, and then computes the total number of pages.
$statement->gotoPage($pageIndex);
Goes to the beginning of the specified page; usually this involves a new call to "execute", unless the current statement has methods to scroll through the result set (see for example DBIx::DataModel::Statement::JDBC).
Like for Perl arrays, a negative index is interpreted as going backwards from the last page.
$statement->shiftPages($delta);
Goes to the beginning of the page corresponding to the current page index + $delta.
$delta
my ($first, $last) = $statement->pageBoundaries;
Returns the indices of first and last rows on the current page. These numbers are given in "user coordinates", i.e. starting at 1, not 0 : so if -pageSize is 10 and -pageIndex is 3, the boundaries are 21 / 30, while technically the current offset is 20. On the last page, the $last index corresponds to rowCount (so $last - $first is not always equal to pageSize + 1).
-pageIndex
$last
rowCount
$last - $first
pageSize + 1
Returns an arrayref of rows corresponding to the current page (maximum -pageSize rows).
Creates an internal memory location that will be reused for each row retrieved from the database; this is the implementation for select(-resultAs => "fast_statement").
select(-resultAs => "fast_statement")
The following methods or functions are used internally by this module and should be considered as reserved names, not to be redefined in subclasses :
Laurent Dami, <laurent.dami AT etat ge ch>
Copyright 2008 by Laurent Dami.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::DataModel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::DataModel
CPAN shell
perl -MCPAN -e shell install DBIx::DataModel
For more information on module installation, please visit the detailed CPAN module installation guide.