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

The design principles for statements are described in the DESIGN ("STATEMENT OBJECTS" in DBIx::DataModel::Doc::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.

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.

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

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

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.

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.

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

The main use of refine is to set up some additional -where conditions, like in

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

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.

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

Arguments are optional, and are just a shortcut instead of writing

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

Arguments are optional, and are just a shortcut instead of writing

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

Returns the index number of the next row to be fetched (starting at $self->offset, or 0 by default).

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

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

Returns the page size (requested number of rows), as it was set through the -pageSize argument to refine() or 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.

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

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

The following methods or functions are used internally by this module and should be considered as reserved names, not to be redefined in subclasses :

_blessFromDB

_compute_fromDB_handlers

_reorganize_columns

_reorganize_pagination

_resolve_source

_limit_offset

_add_conditions

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.