Provided by: libclass-dbi-sweet-perl_0.11-1_all 
NAME
Class::DBI::Sweet - Making sweet things sweeter
SYNOPSIS
package MyApp::DBI;
use base 'Class::DBI::Sweet';
MyApp::DBI->connection('dbi:driver:dbname', 'username', 'password');
package MyApp::Article;
use base 'MyApp::DBI';
use DateTime;
__PACKAGE__->table('article');
__PACKAGE__->columns( Primary => qw[ id ] );
__PACKAGE__->columns( Essential => qw[ title created_on created_by ] );
__PACKAGE__->has_a(
created_on => 'DateTime',
inflate => sub { DateTime->from_epoch( epoch => shift ) },
deflate => sub { shift->epoch }
);
# Simple search
MyApp::Article->search( created_by => 'sri', { order_by => 'title' } );
MyApp::Article->count( created_by => 'sri' );
MyApp::Article->page( created_by => 'sri', { page => 5 } );
MyApp::Article->retrieve_all( order_by => 'created_on' );
# More powerful search with deflating
$criteria = {
created_on => {
-between => [
DateTime->new( year => 2004 ),
DateTime->new( year => 2005 ),
]
},
created_by => [ qw(chansen draven gabb jester sri) ],
title => {
-like => [ qw( perl% catalyst% ) ]
}
};
MyApp::Article->search( $criteria, { rows => 30 } );
MyApp::Article->count($criteria);
MyApp::Article->page( $criteria, { rows => 10, page => 2 } );
MyApp::Article->retrieve_next( $criteria,
{ order_by => 'created_on' } );
MyApp::Article->retrieve_previous( $criteria,
{ order_by => 'created_on' } );
MyApp::Article->default_search_attributes(
{ order_by => 'created_on' } );
# Automatic joins for search and count
MyApp::CD->has_many(tracks => 'MyApp::Track');
MyApp::CD->has_many(tags => 'MyApp::Tag');
MyApp::CD->has_a(artist => 'MyApp::Artist');
MyApp::CD->might_have(liner_notes
=> 'MyApp::LinerNotes' => qw/notes/);
MyApp::Artist->search({ 'cds.year' => $cd }, # $cd->year subtituted
{ order_by => 'artistid DESC' });
my ($tag) = $cd->tags; # Grab first tag off CD
my ($next) = $cd->retrieve_next( { 'tags.tag' => $tag },
{ order_by => 'title' } );
MyApp::CD->search( { 'liner_notes.notes' => { "!=" => undef } } );
MyApp::CD->count(
{ 'year' => { '>', 1998 }, 'tags.tag' => 'Cheesy',
'liner_notes.notes' => { 'like' => 'Buy%' } } );
# Multi-step joins
MyApp::Artist->search({ 'cds.tags.tag' => 'Shiny' });
# Retrieval with pre-loading
my ($cd) = MyApp::CD->search( { ... },
{ prefetch => [ qw/artist liner_notes/ ] } );
$cd->artist # Pre-loaded
# Caching of resultsets (*experimental*)
__PACKAGE__->default_search_attributes( { use_resultset_cache => 1 } );
DESCRIPTION
Class::DBI::Sweet provides convenient count, search, page, and cache functions in a sweet
package. It integrates these functions with "Class::DBI" in a convenient and efficient
way.
RETRIEVING OBJECTS
All retrieving methods can take the same criteria and attributes. Criteria is the only
required parameter.
criteria
Can be a hash, hashref, or an arrayref. Takes the same options as the SQL::Abstract
"where" method. If values contain any objects, they will be deflated before querying the
database.
attributes
case, cmp, convert, and logic
These attributes are passed to SQL::Abstract's constuctor and alter the behavior of
the criteria.
{ cmp => 'like' }
order_by
Specifies the sort order of the results.
{ order_by => 'created_on DESC' }
rows
Specifies the maximum number of rows to return. Currently supported RDBMs are
Interbase, MaxDB, MySQL, PostgreSQL and SQLite. For other RDBMs, it will be emulated.
{ rows => 10 }
offset
Specifies the offset of the first row to return. Defaults to 0 if unspecified.
{ offset => 0 }
page
Specifies the current page in "page". Defaults to 1 if unspecified.
{ page => 1 }
prefetch
Specifies a listref of relationships to prefetch. These must be has_a or might_haves
or Sweet will throw an error. This will cause Sweet to do a join across to the related
tables in order to return the related object without a second trip to the database.
All 'Essential' columns of the foreign table are retrieved.
{ prefetch => [ qw/some_rel some_other_rel/ ] }
Sweet constructs the joined SQL statement by aliasing the columns in each table and
prefixing the column name with 'sweet__N_' where N is a counter starting at 1. Note
that if your database has a column length limit (for example, Oracle's limit is 30)
and you use long column names in your application, Sweet's addition of at least 9
extra characters to your column name may cause database errors.
use_resultset_cache
Enables the resultset cache. This is a little experimental and massive gotchas may
rear their ugly head at some stage, but it does seem to work pretty well.
For best results, the resultset cache should only be used selectively on queries where
you experience performance problems. Enabling it for every single query in your
application will most likely cause a drop in performance as the cache overhead is
greater than simply fetching the data from the database.
profile_cache
Records cache hits/misses and what keys they were for in ->profiling_data. Note that
this is class metadata so if you don't want it to be global for Sweet you need to do
__PACKAGE__->profiling_data({ });
in either your base class or your table classes to taste.
disable_sql_paging
Disables the use of paging in SQL statements if set, forcing Sweet to emulate paging
by slicing the iterator at the end of ->search (which it normally only uses as a
fallback mechanism). Useful for testing or for causing the entire query to be
retrieved initially when the resultset cache is used.
This is also useful when using custom SQL via "set_sql" and setting "sql_method" (see
below) where a COUNT(*) may not make sense (i.e. when the COUNT(*) might be as
expensive as just running the full query and just slicing the iterator).
sql_method
This sets the name of the sql fragment to use as previously set by a "set_sql" call.
The default name is "Join_Retrieve" and the associated default sql fragment set in
this class is:
__PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
SELECT __ESSENTIAL(me)__%s
FROM %s
WHERE %s
SQL
You may override this in your table or base class using the same name and CDBI::Sweet
will use your custom fragment, instead.
If you need to use more than one sql fragment in a given class you may create a new
sql fragment and then specify its name using the "sql_method" attribute.
The %s strings are replaced by sql parts as described in Ima::DBI. See
"statement_order" for the sql part that replaces each instance of %s.
In addition, the associated statment for COUNT(*) statement has "_Count" appended to
the sql_method name. Only "from" and "where" are passed to the sprintf function.
The default sql fragment used for "Join_Retrieve" is:
__PACKAGE__->set_sql( Join_Retrieve_Count => <<'SQL' );
SELECT COUNT(*)
FROM %s
WHERE %s
SQL
If you create a custom sql method (and set the "sql_method" attribute) then you will
likely need to also create an associated _Count fragment. If you do not have an
associated _Count, and wish to call the "page" method, then set "disable_sql_paging"
to true and your result set from the select will be spliced to return the page you
request.
Here's an example.
Assume a CD has_a Artist (and thus Artists have_many CDs), and you wish to return a
list of artists and how many CDs each have:
In package MyDB::Artist
__PACKAGE__->columns( TEMP => 'cd_count');
__PACKAGE__->set_sql( 'count_by_cd', <<'');
SELECT __ESSENTIAL(me)__, COUNT(cds.cdid) as cd_count
FROM %s -- ("from")
WHERE %s -- ("where")
GROUP BY __ESSENTIAL(me)__
%s %s -- ("limit" and "order_by")
Then in your application code:
my ($pager, $iterator) = MyDB::Artist->page(
{
'cds.title' => { '!=', undef },
},
{
sql_method => 'count_by_cd',
statement_order => [qw/ from where limit order_by / ],
disable_sql_paging => 1,
order_by => 'cd_count desc',
rows => 10,
page => 1,
} );
The above generates the following SQL:
SELECT me.artistid, me.name, COUNT(cds.cdid) as cd_count
FROM artist me, cd cds
WHERE ( cds.title IS NOT NULL ) AND me.artistid = cds.artist
GROUP BY me.artistid, me.name
ORDER BY cd_count desc
The one caveat is that Sweet cannot figure out the has_many joins unless you specify
them in the $criteria. In the previous example that's done by asking for all cd
titles that are not null (which should be all).
To fetch a list like above but limited to cds that were created before the year 2000,
you might do:
my ($pager, $iterator) = MyDB::Artist->page(
{
'cds.year' => { '<', 2000 },
},
{
sql_method => 'count_by_cd',
statement_order => [qw/ from where limit order_by / ],
disable_sql_paging => 1,
order_by => 'cd_count desc',
rows => 10,
page => 1,
} );
statement_order
Specifies a list reference of SQL parts that are replaced in the SQL fragment (which
is defined with "sql_method" above). The available SQL parts are:
prefetch_cols from where order_by limit sql prefetch_names
The "sql" part is shortcut notation for these three combined:
where order_by limit
Prefecch_cols are the columns selected when a prefetch is speccified -- use in the
SELECT. Prefetch_names are just the column names for use in GROUP BY.
This is useful when statement order needs to be changed, such as when using a GROUP
BY:
count
Returns a count of the number of rows matching the criteria. "count" will discard
"offset", "order_by", and "rows".
$count = MyApp::Article->count(%criteria);
search
Returns an iterator in scalar context, or an array of objects in list context.
@objects = MyApp::Article->search(%criteria);
$iterator = MyApp::Article->search(%criteria);
search_like
As search but adds the attribute { cmp => 'like' }.
page
Retuns a page object and an iterator. The page object is an instance of Data::Page.
( $page, $iterator )
= MyApp::Article->page( $criteria, { rows => 10, page => 2 );
printf( "Results %d - %d of %d Found\n",
$page->first, $page->last, $page->total_entries );
pager
An alias to page.
retrieve_all
Same as "Class::DBI" with addition that it takes "attributes" as arguments, "attributes"
can be a hash or a hashref.
$iterator = MyApp::Article->retrieve_all( order_by => 'created_on' );
retrieve_next
Returns the next record after the current one according to the order_by attribute (or
primary key if no order_by specified) matching the criteria. Must be called as an object
method.
retrieve_previous
As retrieve_next but retrieves the previous record.
CACHING OBJECTS
Objects will be stored deflated in cache. Only "Primary" and "Essential" columns will be
cached.
cache
Class method: if this is set caching is enabled. Any cache object that has a "get", "set",
and "remove" method is supported.
__PACKAGE__->cache(
Cache::FastMmap->new(
share_file => '/tmp/cdbi',
expire_time => 3600
)
);
cache_key
Returns a cache key for an object consisting of class and primary keys.
Overloaded methods
_init
Overrides "Class::DBI"'s internal cache. On a cache hit, it will return a cached
object; on a cache miss it will create an new object and store it in the cache.
create
insert
All caches for this table are marked stale and will be re-cached on next retrieval.
create is an alias kept for backwards compatibility.
retrieve
On a cache hit the object will be inflated by the "select" trigger and then served.
update
Object is removed from the cache and will be cached on next retrieval.
delete
Object is removed from the cache.
UNIVERSALLY UNIQUE IDENTIFIERS
If enabled a UUID string will be generated for primary column. A CHAR(36) column is
suitable for storage.
__PACKAGE__->sequence('uuid');
MAINTAINERS
Fred Moyer <fred@redhotpenguin.com>
AUTHORS
Christian Hansen <ch@ngmedia.com>
Matt S Trout <mstrout@cpan.org>
Andy Grundman <andy@hybridized.org>
THANKS TO
Danijel Milicevic, Jesse Sheidlower, Marcus Ramberg, Sebastian Riedel, Viljo Marrandi,
Bill Moseley
SUPPORT
#catalyst on <irc://irc.perl.org>
<http://lists.rawmode.org/mailman/listinfo/catalyst>
<http://lists.rawmode.org/mailman/listinfo/catalyst-dev>
LICENSE
This library is free software; you can redistribute it and/or modify it under the same
terms as Perl itself.
SEE ALSO
Class::DBI
Data::Page
Data::UUID
SQL::Abstract
Catalyst
<http://cpan.robm.fastmail.fm/cache_perf.html> A comparison of different caching modules
for perl.