Class::DBI::Sweet - Making sweet things sweeter
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 } );
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.
Class::DBI
All retrieving methods can take the same criteria and attributes. Criteria is the only required parameter.
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.
where
These attributes are passed to SQL::Abstact's constuctor and alter the behavior of the criteria.
{ cmp => 'like' }
Specifies the sort order of the results.
{ order_by => 'created_on DESC' }
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 }
Specifies the offset of the first row to return. Defaults to 0 if unspecified.
{ offset => 0 }
Specifies the current page in page. Defaults to 1 if unspecified.
page
{ page => 1 }
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.
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.
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.
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.
Returns a count of the number of rows matching the criteria. count will discard offset, order_by, and rows.
count
offset
order_by
rows
$count = MyApp::Article->count(%criteria);
Returns an iterator in scalar context, or an array of objects in list context.
@objects = MyApp::Article->search(%criteria); $iterator = MyApp::Article->search(%criteria);
As search but adds the attribute { cmp => 'like' }.
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 );
An alias to page.
Same as Class::DBI with addition that it takes attributes as arguments, attributes can be a hash or a hashref.
attributes
$iterator = MyApp::Article->retrieve_all( order_by => 'created_on' );
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.
As retrieve_next but retrieves the previous record.
Objects will be stored deflated in cache. Only Primary and Essential columns will be cached.
Primary
Essential
Class method: if this is set caching is enabled. Any cache object that has a get, set, and remove method is supported.
get
set
remove
__PACKAGE__->cache( Cache::FastMmap->new( share_file => '/tmp/cdbi', expire_time => 3600 ) );
Returns a cache key for an object consisting of class and primary keys.
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.
All caches for this table are marked stale and will be re-cached on next retrieval.
On a cache hit the object will be inflated by the select trigger and then served.
select
Object is removed from the cache and will be cached on next retrieval.
Object is removed from the cache.
If enabled a UUID string will be generated for primary column. A CHAR(36) column is suitable for storage.
__PACKAGE__->sequence('uuid');
Christian Hansen <ch@ngmedia.com>
Matt S Trout <mstrout@cpan.org>
Andy Grundman <andy@hybridized.org>
Danijel Milicevic, Jesse Sheidlower, Marcus Ramberg, Sebastian Riedel, Viljo Marrandi
#catalyst on irc://irc.perl.org
http://lists.rawmode.org/mailman/listinfo/catalyst
http://lists.rawmode.org/mailman/listinfo/catalyst-dev
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Data::Page
Data::UUID
SQL::Abstract
Catalyst
http://cpan.robm.fastmail.fm/cache_perf.html A comparison of different caching modules for perl.
To install Class::DBI::Sweet, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Class::DBI::Sweet
CPAN shell
perl -MCPAN -e shell install Class::DBI::Sweet
For more information on module installation, please visit the detailed CPAN module installation guide.