Jifty::DBI::Collection - Encapsulate SQL queries and rows in simple perl objects
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; }
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.
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.
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.
Jifty::DBI::Handle
handle
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.
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".
Get or set this object's Jifty::DBI::Handle object.
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").
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.
This private internal method returns the number of Jifty::DBI::Record objects saved as a result of the last query.
This internal private method actually executes a counting operation on the database; it is used by "count" and "count_all".
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).
$self->rows_per_page
$self->first_row
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 up all of the joins we need to perform this query.
Returns true if this collection will be joining multiple tables together.
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.
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.
1
-1
0
Builds a query string for a "SELECT rows from Tables" statement for this collection
The columns that the query would load for result items. By default it's everything.
Takes the alias you've assigned to a prefetched related object. Returns the class of the column we've declared that alias prefetches.
Prefetches properties of a related table, in the same query. Possible keys in the paramhash are:
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.
refers_to
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 ).
name
$record-> columnname
$record->prefetched( columnname )
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.
users.phones
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.
prefetch
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.
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.
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.
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"
Builds a SELECT statement to find the number of rows this collection would find.
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.
Jifty::DBI::Collection
SELECT COUNT
SELECT
do_search
count
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>) { ... }
Exactly the same as next, only it doesn't move the iterator.
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.
Takes an integer, n. Sets the record counter to n. the next time you call "next", you'll get the nth item.
Returns the first item
Returns the last item
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.
max
sort
$col->distinct_column_values('column'); $col->distinct_column_values(column => 'column'); $col->distinct_column_values('column', max => 10, sort => 'asc');
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) { ... }
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.
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.
Takes no arguments. Tells Jifty::DBI::Collection that the next time it is asked for a record, it should re-execute the query.
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.
unlimit
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).
Takes a hash of parameters with the following keys:
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).
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 to be checked against.
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 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.
MATCHES is like LIKE, except it surrounds the value with % signs.
starts_with is like LIKE, except it only appends a % at the end of the string
ends_with is like LIKE, except it prepends a % to the beginning of the string
IN matches a column within a set of values. The value specified in the limit should be an array reference of values.
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".
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).
Can be AND or OR (or anything else valid to aggregate two clauses in SQL)
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.
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.
CLAUSE
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:
open_paren
close_paren
$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.
"my_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.
*DEPRECATED*. Use order_by method.
order_by
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.
column
order
function
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.
alias.column
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.
min
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.
add_order_by
The results would be unordered if method called without arguments.
Returns the current list of columns.
Same as order_by, except it will not reset the ordering you have already set.
Clears whatever would normally get set in the ORDER BY clause.
returns the ORDER BY clause for the search.
*DEPRECATED*. Use group_by method.
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.
Private function to return the "GROUP BY" clause for this query.
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 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.
alias1
column1
alias2
column2
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.
type
table2
The parameter operator defaults =, but you can specify other operators to join with.
operator
=
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.
is_distinct
Instead of alias1/column1, it's possible to specify expression, to join alias2/table2 on an arbitrary expression.
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.
first_row
rows_per_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)
current_page
all
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.
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.
Returns the current position in the record set.
Returns the number of records in the set.
Returns the total number of potential records in the set, ignoring any limit_clause.
Returns true if the current row is the last record in the set.
Gets/sets the DEBUG flag.
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:
table
limit
DISTINCT COUNT
Same as COUNT(DISTINCT ?).
COUNT(DISTINCT ?)
?
The ? is substituted with the column name, then passed verbatim to the underlying SELECT statement.
(
The expression is passed verbatim to the underlying SELECT.
The expression is taken to be a function name. For example, SUM means the same thing as SUM(?).
SUM
SUM(?)
Specify that we want to load only the columns in LIST, which should be a list of column names.
Return a list of columns in table, in lowercase.
TODO: Why are they in lowercase?
Returns true if table has column column. Return false otherwise
If called with an argument, sets this collection's table.
Always returns this collection's table.
Returns copy of the current object with all search restrictions.
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.
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.
each
If the callback returns zero, the iteration ends.
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:
make test
JDBI_TEST_FOO
JDBI_TEST_FOO_USER
JDBI_TEST_FOO_PASS
DBD::
SQLite
DBD::Sqlite
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
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.
Jifty::DBI, Jifty::DBI::Handle, Jifty::DBI::Record.
To install Jifty::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Jifty::DBI
CPAN shell
perl -MCPAN -e shell install Jifty::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.