NAME
Jifty::DBI::Collection - Encapsulate SQL queries and rows in simple perl objects
SYNOPSIS
use Jifty::DBI::Collection;
package My::Things;
use base qw/Jifty::DBI::Collection/;
package main;
use Jifty::DBI::Handle;
my $handle = Jifty::DBI::Handle->new();
$handle->connect( Driver => 'SQLite', Database => "my_test_db" );
my $sb = My::Things->new( handle => $handle );
$sb->limit( column => "column_1", value => "matchstring" );
while ( my $record = $sb->next ) {
print $record->my_column_name();
}DESCRIPTION
This module provides an object-oriented mechanism for retrieving and updating data in a DBI-accesible 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 $sb = 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.
_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.
_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_cols", 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_limited
If we've limited down this search, return true. Otherwise, return false.
build_select_query
Builds a query string for a "SELECT rows from Tables" statement for this collection
build_select_count_query
Builds a SELECT statement to find the number of rows this collection would find.
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.
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
items_array_ref
Return a refernece to an array containing all objects found by this search.
record_class
Returns the record class which this is a collection of; override this to subclass. Or, pass it the name of a class an 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" 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's asked for a record, it should requery the database
unlimit
unlimit clears all restrictions and causes this object to return all rows in the primary table.
limit
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 criterias will be taken from EXT_LINKcolumn and INT_LINKcolumn and added to the criterias. If alias is set, new criterias about the foreign table will be added.
- column
-
Column to be checked against.
- value
-
Should always be set and will always be quoted.
- operator
-
operator is the SQL operator to use for this phrase. Possible choices include:
- 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
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.
Use array of hashes to order by many columns/functions.
The results would be unordered if method called without arguments.
_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 param hash 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 preform 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 specity other operators to join with.
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.
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 argrument. 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.
column { column => undef }
Specify that we want to load the column column.
Other parameters are table alias AND function.
Autrijus and Ruslan owe docs.
columns LIST
Specify that we want to load only the columns in LIST
columns_in_db table
Return a list of columns in table, lowercased.
TODO: Why are they lowercased?
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.
refers_to
Private convenience method needed for the declarative schema generation.
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 clonning then you probably want override this method and add own values to the list.
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 testAUTHOR
Copyright (c) 2001-2005 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::Handle, Jifty::DBI::Record.