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::Abstact'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.

  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
        All caches for this table are marked stale and will be re-cached on
        next retrieval.

    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');

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

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.