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 compability.

   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 <[email protected]>

AUTHORS
   Christian Hansen <[email protected]>

   Matt S Trout <[email protected]>

   Andy Grundman <[email protected]>

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.

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.