Provided by: libjifty-dbi-perl_0.78-3_all bug

NAME

       Jifty::DBI::Collection - Encapsulate SQL queries and rows in simple perl objects

SYNOPSIS

         use Jifty::DBI::Collection;

         package My::ThingCollection;
         use base qw/Jifty::DBI::Collection/;

         package My::Thing;
         use Jifty::DBI::Schema;
         use Jifty::DBI::Record schema {
           column column_1 => type is 'text';
         };

         package main;

         use Jifty::DBI::Handle;
         my $handle = Jifty::DBI::Handle->new();
         $handle->connect( driver => 'SQLite', database => "my_test_db" );

         my $collection = My::ThingCollection->new( handle => $handle );

         $collection->limit( column => "column_1", value => "matchstring" );

         while ( my $record = $collection->next ) {
             print $record->id;
         }

DESCRIPTION

       This module provides an object-oriented mechanism for retrieving and updating data in a
       DBI-accessible database.

       In order to use this module, you should create a subclass of Jifty::DBI::Collection and a
       subclass of Jifty::DBI::Record for each table that you wish to access.  (See the
       documentation of Jifty::DBI::Record for more information on subclassing it.)

       Your Jifty::DBI::Collection subclass must override "new_item", and probably should
       override at least "_init" also; at the very least, "_init" should probably call "_handle"
       and "_table" to set the database handle (a Jifty::DBI::Handle object) and table name for
       the class -- see the "SYNOPSIS" for an example.

METHODS

   new
       Creates a new Jifty::DBI::Collection object and immediately calls "_init" with the same
       parameters that were passed to "new".  If you haven't overridden _init in your subclass,
       this means that you should pass in a Jifty::DBI::Handle (or one of its subclasses) like
       this:

          my $collection = My::Jifty::DBI::Subclass->new( handle => $handle );

       However, if your subclass overrides "_init" you do not need to take a handle argument, as
       long as your subclass takes care of calling the "_handle" method somehow.  This is useful
       if you want all of your Jifty::DBI objects to use a shared global handle and don't want to
       have to explicitly pass it in each time, for example.

   _init
       This method is called by new with whatever arguments were passed to "new".  By default, it
       takes a "Jifty::DBI::Handle" object as a "handle" argument and calls "_handle" with that.

   clean_slate
       This completely erases all the data in the object. It's useful if a subclass is doing
       funky stuff to keep track of a search and wants to reset the object's data without losing
       its own data; it's probably cleaner to accomplish that in a different way, though.

   implicit_clauses
       Called by "clean_slate" to set up any implicit clauses that the collection always has.
       Defaults to doing nothing. Is passed the paramhash passed into "new".

   _handle [DBH]
       Get or set this object's Jifty::DBI::Handle object.

   _do_search
       This internal private method actually executes the search on the database; it is called
       automatically the first time that you actually need results (such as a call to "next").

   add_record RECORD
       Adds a record object to this collection.

       This method automatically sets our "must redo search" flag to 0 and our "we have limits"
       flag to 1.

       Without those two flags, counting the number of items wouldn't work.

   _record_count
       This private internal method returns the number of Jifty::DBI::Record objects saved as a
       result of the last query.

   _do_count
       This internal private method actually executes a counting operation on the database; it is
       used by "count" and "count_all".

   _apply_limits STATEMENTREF
       This routine takes a reference to a scalar containing an SQL statement.  It massages the
       statement to limit the returned rows to only "$self->rows_per_page" rows, skipping
       "$self->first_row" rows.  (That is, if rows are numbered starting from 0, row number
       "$self->first_row" will be the first row returned.)  Note that it probably makes no sense
       to set these variables unless you are also enforcing an ordering on the rows (with
       "order_by", say).

   _distinct_query STATEMENTREF
       This routine takes a reference to a scalar containing an SQL statement.  It massages the
       statement to ensure a distinct result set is returned.

   _build_joins
       Build up all of the joins we need to perform this query.

   _is_joined
       Returns true if this collection will be joining multiple tables together.

   _is_distinctly_joined
       Returns true if this collection is joining multiple table, but is joining other table's
       distinct fields, hence resulting in distinct resultsets.  The behaviour is undefined if
       called on a non-joining collection.

   _is_limited
       If we've limited down this search, return true. Otherwise, return false.

       1 means "we have limits" "-1" means "we should return all rows. We want no where clause" 0
       means "no limits have been applied yet.

   build_select_query
       Builds a query string for a "SELECT rows from Tables" statement for this collection

   query_columns
       The columns that the query would load for result items.  By default it's everything.

   class_and_column_for_alias
       Takes the alias you've assigned to a prefetched related object. Returns the class of the
       column we've declared that alias prefetches.

   prefetch PARAMHASH
       Prefetches properties of a related table, in the same query.  Possible keys in the
       paramhash are:

       name
           This argument is required; it specifies the name of the collection or record that is
           to be prefetched.  If the name matches a column with a "refers_to" relationship, the
           other arguments can be inferred, and this is the only parameter which needs to be
           passed.

           It is possible to pass values for "name" which are not real columns in the model;
           these, while they won't be accessible by calling "$record-> columnname" on records in
           this collection, will still be accessible by calling "$record->prefetched( columnname
           )".

       reference
           Specifies the series of column names to traverse to extract the information.  For
           instance, if groups referred to multiple users, and users referred to multiple phone
           numbers, then providing "users.phones" would do the two necessary joins to produce a
           phone collection for all users in each group.

           This option defaults to the name, and is irrelevant if an "alias" is provided.

       alias
           Specifies an alias which has already been joined to this collection as the source of
           the prefetched data.  "class" will also need to be specified.

       class
           Specifies the class of the data to preload.  This is only necessary if "alias" is
           provided, and "name" is not the name of a column which provides "refers_to"
           information.

       For backwards compatibility, "prefetch" can instead be called with "alias" and "name" as
       its two arguments, instead of a paramhash.

   find_column NAMES
       Tales a chained list of column names, where all but the last element is the name of a
       column on the previous class which refers to the next collection or record.  Returns a
       list of Jifty::DBI::Column objects for the list.

   find_class NAMES
       Tales a chained list of column names, where each element is the name of a column on the
       previous class which refers to the next collection or record.  Returns an instance of the
       ending class, followed by the list of Jifty::DBI::Column objects traversed to get there.

   resolve_join COLUMNS
       Takes a chained list of Jifty::DBI::Column objects, and performs the requisite joins to
       join all of them.  Returns the alias of the last join.

   distinct_required
       Returns true if Jifty::DBI expects that this result set will end up with repeated rows and
       should be "condensed" down to a single row for each unique primary key.

       Out of the box, this method returns true if you've joined to another table.  To add
       additional logic, feel free to override this method in your subclass.

       XXX TODO: it should be possible to create a better heuristic than the simple "is it
       joined?" question we're asking now. Something along the lines of "are we joining this
       table to something that is not the other table's primary key"

   build_select_count_query
       Builds a SELECT statement to find the number of rows this collection
        would find.

   do_search
       "Jifty::DBI::Collection" usually does searches "lazily". That is, it does a "SELECT COUNT"
       or a "SELECT" on the fly the first time you ask for results that would need one or the
       other.  Sometimes, you need to display a count of results found before you iterate over a
       collection, but you know you're about to do that too. To save a bit of wear and tear on
       your database, call "do_search" before that "count".

   next
       Returns the next row from the set as an object of the type defined by sub new_item.  When
       the complete set has been iterated through, returns undef and resets the search such that
       the following call to "next" will start over with the first item retrieved from the
       database.

       You may also call this method via the built-in iterator syntax.  The two lines below are
       equivalent:

           while ($_ = $collection->next) { ... }

           while (<$collection>) { ... }

   peek
       Exactly the same as next, only it doesn't move the iterator.

   goto_first_item
       Starts the recordset counter over from the first item. The next time you call "next",
       you'll get the first item returned by the database, as if you'd just started iterating
       through the result set.

   goto_item
       Takes an integer, n.  Sets the record counter to n. the next time you call "next", you'll
       get the nth item.

   first
       Returns the first item

   last
       Returns the last item

   distinct_column_values
       Takes a column name and returns distinct values of the column.  Only values in the current
       collection are returned.

       Optional arguments are "max" and "sort" to limit number of values returned and it makes
       sense to sort results.

           $col->distinct_column_values('column');

           $col->distinct_column_values(column => 'column');

           $col->distinct_column_values('column', max => 10, sort => 'asc');

   items_array_ref
       Return a reference to an array containing all objects found by this search.

       You may also call this method via the built-in array dereference syntax.  The two lines
       below are equivalent:

           for (@{$collection->items_array_ref}) { ... }

           for (@$collection) { ... }

   new_item
       Should return a new object of the correct type for the current collection.  "record_class"
       method is used to determine class of the object.

       Each record class at least once is loaded using require. This method is called each time a
       record fetched so load attempts are cached to avoid penalties. If you're sure that all
       record classes are loaded before first use then you can override this method.

   record_class
       Returns the record class which this is a collection of; override this to subclass.  Or,
       pass it the name of a class as an argument after creating a "Jifty::DBI::Collection"
       object to create an 'anonymous' collection class.

       If you haven't specified a record class, this returns a best guess at the name of the
       record class for this collection.

       It uses a simple heuristic to determine the record class name -- It chops "Collection" or
       "s" off its own name. If you want to name your records and collections differently, go
       right ahead, but don't say we didn't warn you.

   redo_search
       Takes no arguments.  Tells Jifty::DBI::Collection that the next time it is asked for a
       record, it should re-execute the query.

   unlimit
       Unlimit clears all restrictions on this collection and resets it to a "default" pristine
       state. Note, in particular, that this means "unlimit" will erase ordering and grouping
       metadata.  To find all rows without resetting this metadata, use the "find_all_rows"
       method.

   find_all_rows
       "find_all_rows" instructs this collection class to return all rows in the table. (It
       removes the WHERE clause from your query).

   limit
       Takes a hash of parameters with the following keys:

       table
           Can be set to something different than this table if a join is wanted (that means we
           can't do recursive joins as for now).

       alias
           Unless alias is set, the join criteria will be taken from EXT_LINKcolumn and
           INT_LINKcolumn and added to the criteria.  If alias is set, new criteria about the
           foreign table will be added.

       column
           Column to be checked against.

       value
           Should always be set and will always be quoted.  If the value is a subclass of
           Jifty::DBI::Object, the value will be interpreted to be the object's id.

       operator
           operator is the SQL operator to use for this phrase.  Possible choices include:

           "="
           "!="
               Any other standard SQL comparison operators that your underlying database supports
               are also valid.

           "LIKE"
           "NOT LIKE"
           "MATCHES"
               MATCHES is like LIKE, except it surrounds the value with % signs.

           "starts_with"
               starts_with is like LIKE, except it only appends a % at the end of the string

           "ends_with"
               ends_with is like LIKE, except it prepends a % to the beginning of the string

           "IN"
               IN matches a column within a set of values.  The value specified in the limit
               should be an array reference of values.

           "IS"
           "IS NOT"
               This is useful for when you wish to match columns that contain NULL (or ones that
               don't). Use this operator and a value of "NULL".

       escape
           If you need to escape wildcard characters (usually _ or %) in the value *explicitly*
           with "ESCAPE", set the  escape character here. Note that backslashes may require
           special treatment (e.g. Postgres dislikes \ or \\ in queries unless we use the E''
           syntax).

       entry_aggregator
           Can be AND or OR (or anything else valid to aggregate two clauses in SQL)

       case_sensitive
           on some databases, such as postgres, setting case_sensitive to 1 will make this search
           case sensitive.  Note that this flag is ignored if the column is numeric.

   open_paren CLAUSE
       Places an open parenthesis at the current location in the given "CLAUSE".  Note that this
       can be used for Deep Magic, and has a high likelihood of allowing you to construct
       malformed SQL queries.  Its interface will probably change in the near future, but its
       presence allows for arbitrarily complex queries.

       Here's an example, to construct a SQL WHERE clause roughly equivalent to (depending on
       your SQL dialect):

         parent = 12 AND task_type = 'action'
             AND (status = 'open'
                 OR (status = 'done'
                     AND completed_on >= '2008-06-26 11:39:22'))

       You can use sub-clauses and "open_paren" and "close_paren" as follows:

         $col->limit( column => 'parent', value => 12 );
         $col->limit( column => 'task_type', value => 'action' );

         $col->open_paren("my_clause");

         $col->limit( subclause => "my_clause", column => 'status', value => 'open' );

         $col->open_paren("my_clause");

         $col->limit( subclause => "my_clause", column => 'status',
             value => 'done', entry_aggregator => 'OR' );
         $col->limit( subclause => "my_clause", column => 'completed_on',
             operator => '>=', value => '2008-06-26 11:39:22' );

         $col->close_paren("my_clause");

         $col->close_paren("my_clause");

       Where the "my_clause" can be any name you choose.

   close_paren CLAUSE
       Places a close parenthesis at the current location in the given "CLAUSE".  Note that this
       can be used for Deep Magic, and has a high likelihood of allowing you to construct
       malformed SQL queries.  Its interface will probably change in the near future, but its
       presence allows for arbitrarily complex queries.

   order_by_cols DEPRECATED
       *DEPRECATED*. Use "order_by" method.

   order_by EMPTY|HASH|ARRAY_OF_HASHES
       Orders the returned results by column(s) and/or function(s) on column(s).

       Takes a paramhash of "alias", "column" and "order" or "function" and "order".  "alias"
       defaults to main.  "order" defaults to ASC(ending), DES(cending) is also a valid value.
       "column" and "function" have no default values.

       Use "function" instead of "alias" and "column" to order by the function value. Note that
       if you want use a column as argument of the function then you have to build correct
       reference with alias in the "alias.column" format.

       If you specify "function" and "column", the column (and "alias") will be wrapped in the
       function.  This is useful for simple functions like "min" or "lower".

       Use array of hashes to order by many columns/functions.

       Calling this sets the ordering, it doesn't refine it. If you want to keep previous
       ordering, use "add_order_by".

       The results would be unordered if method called without arguments.

       Returns the current list of columns.

   add_order_by EMPTY|HASH|ARRAY_OF_HASHES
       Same as order_by, except it will not reset the ordering you have already set.

   clear_order_by
       Clears whatever would normally get set in the ORDER BY clause.

   _order_clause
       returns the ORDER BY clause for the search.

   group_by_cols DEPRECATED
       *DEPRECATED*. Use group_by method.

   group_by EMPTY|HASH|ARRAY_OF_HASHES
       Groups the search results by column(s) and/or function(s) on column(s).

       Takes a paramhash of "alias" and "column" or "function".  "alias" defaults to main.
       "column" and "function" have no default values.

       Use "function" instead of "alias" and "column" to group by the function value. Note that
       if you want use a column as argument of the function then you have to build correct
       reference with alias in the "alias.column" format.

       Use array of hashes to group by many columns/functions.

       The method is EXPERIMENTAL and subject to change.

   _group_clause
       Private function to return the "GROUP BY" clause for this query.

   new_alias table_OR_CLASS
       Takes the name of a table or a Jifty::DBI::Record subclass.  Returns the string of a new
       Alias for that table, which can be used to Join tables or to limit what gets found by a
       search.

   join
       Join instructs Jifty::DBI::Collection to join two tables.

       The standard form takes a paramhash with keys "alias1", "column1", "alias2" and "column2".
       "alias1" and "alias2" are column aliases obtained from $self->new_alias or a $self->limit.
       "column1" and "column2" are the columns in "alias1" and "alias2" that should be linked,
       respectively.  For this type of join, this method has no return value.

       Supplying the parameter "type" => 'left' causes Join to perform a left join.  in this
       case, it takes "alias1", "column1", "table2" and "column2". Because of the way that left
       joins work, this method needs a table for the second column rather than merely an alias.
       For this type of join, it will return the alias generated by the join.

       The parameter "operator" defaults "=", but you can specify other operators to join with.

       Passing a true value for the "is_distinct" parameter allows one to specify that, despite
       the join, the original table's rows are will all still be distinct.

       Instead of "alias1"/"column1", it's possible to specify expression, to join
       "alias2"/"table2" on an arbitrary expression.

   set_page_info [per_page => NUMBER,] [current_page => NUMBER]
       Sets the current page (one-based) and number of items per page on the pager object, and
       pulls the number of elements from the collection.  This both sets up the collection's
       Data::Page object so that you can use its calculations, and sets the
       Jifty::DBI::Collection "first_row" and "rows_per_page" so that queries return values from
       the selected page.

       If a "current_page" of "all" is passed, then paging is basically disabled (by setting
       "per_page" to the number of entries, and "current_page" to 1)

   rows_per_page
       limits the number of rows returned by the database.  Optionally, takes an integer which
       restricts the # of rows returned in a result Returns the number of rows the database
       should display.

   first_row
       Get or set the first row of the result set the database should return.  Takes an optional
       single integer argument. Returns the currently set integer first row that the database
       should return.

   _items_counter
       Returns the current position in the record set.

   count
       Returns the number of records in the set.

   count_all
       Returns the total number of potential records in the set, ignoring any limit_clause.

   is_last
       Returns true if the current row is the last record in the set.

   DEBUG
       Gets/sets the DEBUG flag.

   column
       Normally a collection object contains record objects populated with all columns in the
       database, but you can restrict the records to only contain some particular columns, by
       calling the "column" method once for each column you are interested in.

       Takes a hash of parameters; the "column", "table" and "alias" keys means the same as in
       the "limit" method.  A special "function" key may contain one of several possible kinds of
       expressions:

       "DISTINCT COUNT"
           Same as "COUNT(DISTINCT ?)".

       Expression with "?" in it
           The "?" is substituted with the column name, then passed verbatim to the underlying
           "SELECT" statement.

       Expression with "(" in it
           The expression is passed verbatim to the underlying "SELECT".

       Any other expression
           The expression is taken to be a function name.  For example, "SUM" means the same
           thing as SUM(?).

   columns LIST
       Specify that we want to load only the columns in LIST, which should be a list of column
       names.

   columns_in_db table
       Return a list of columns in table, in lowercase.

       TODO: Why are they in lowercase?

   has_column  { table => undef, column => undef }
       Returns true if table has column column.  Return false otherwise

   table [table]
       If called with an argument, sets this collection's table.

       Always returns this collection's table.

   clone
       Returns copy of the current object with all search restrictions.

   _cloned_attributes
       Returns list of the object's fields that should be copied.

       If your subclass store references in the object that should be copied while cloning then
       you probably want override this method and add own values to the list.

   each CALLBACK
       Executes the callback for each item in the collection. The callback receives as arguments
       each record, its zero-based index, and the collection. The return value of "each" is the
       original collection.

       If the callback returns zero, the iteration ends.

TESTING

       In order to test most of the features of "Jifty::DBI::Collection", you need to provide
       "make test" with a test database.  For each DBI driver that you would like to test, set
       the environment variables "JDBI_TEST_FOO", "JDBI_TEST_FOO_USER", and "JDBI_TEST_FOO_PASS"
       to a database name, database username, and database password, where "FOO" is the driver
       name in all uppercase.  You can test as many drivers as you like.  (The appropriate
       "DBD::" module needs to be installed in order for the test to work.)  Note that the
       "SQLite" driver will automatically be tested if "DBD::Sqlite" is installed, using a
       temporary file as the database.  For example:

         JDBI_TEST_MYSQL=test JDBI_TEST_MYSQL_USER=root JDBI_TEST_MYSQL_PASS=foo \
           JDBI_TEST_PG=test JDBI_TEST_PG_USER=postgres  make test

AUTHOR

       Jesse Vincent <jesse@bestpractical.com>, Alex Vandiver <alexmv@bestpractical.com>, Ruslan
       Zakirov <ruslan.zakirov@gmail.com>

       Based on DBIx::SearchBuilder::Collection, whose credits read:

        Jesse Vincent, <jesse@fsck.com>

       All rights reserved.

       This library is free software; you can redistribute it and/or modify it under the same
       terms as Perl itself.

SEE ALSO

       Jifty::DBI, Jifty::DBI::Handle, Jifty::DBI::Record.