view release on metacpan or  search on metacpan

README  view on Meta::CPAN

    list of objects as with any built in query.

    For example:

      Music::CD->add_constructor(new_music => 'year > 2000');
      my @recent = Music::CD->new_music;

    You can also supply placeholders in your SQL, which must then be
    specified at query time:

      Music::CD->add_constructor(new_music => 'year > ?');
      my @recent = Music::CD->new_music(2000);

  retrieve_from_sql
    On occasions where you want to execute arbitrary SQL, but don't want to
    go to the trouble of setting up a constructor method, you can inline the
    entire WHERE clause, and just get the objects back directly:

      my @cds = Music::CD->retrieve_from_sql(qq{
        artist = 'Ozzy Osbourne' AND
        title like "%Crazy"      AND
        year <= 1986
        ORDER BY year
        LIMIT 2,3
      });

  Ima::DBI queries
    When you can't use 'add_constructor', e.g. when using aggregate
    functions, you can fall back on the fact that Class::DBI inherits from
    Ima::DBI and prefers to use its style of dealing with statements, via
    set_sql().

    The Class::DBI set_sql() method defaults to using prepare_cached()
    unless the $cache parameter is defined and false (see Ima::DBI docs for
    more information).

    To assist with writing SQL that is inheritable into subclasses, several
    additional substitutions are available here: __TABLE__, __ESSENTIAL__
    and __IDENTIFIER__. These represent the table name associated with the
    class, its essential columns, and the primary key of the current object,
    in the case of an instance method on it.

    For example, the SQL for the internal 'update' method is implemented as:

      __PACKAGE__->set_sql('update', <<"");
        UPDATE __TABLE__
        SET    %s
        WHERE  __IDENTIFIER__

    The 'longhand' version of the new_music constructor shown above would
    similarly be:

      Music::CD->set_sql(new_music => qq{
        SELECT __ESSENTIAL__
          FROM __TABLE__
         WHERE year > ?
      });

    For such 'SELECT' queries Ima::DBI's set_sql() method is extended to
    create a helper shortcut method, named by prefixing the name of the SQL
    fragment with 'search_'. Thus, the above call to set_sql() will
    automatically set up the method Music::CD->search_new_music(), which
    will execute this search and return the relevant objects or Iterator.
    (If there are placeholders in the query, you must pass the relevant
    arguments when calling your search method.)

    This does the equivalent of:

      sub search_new_music {
        my ($class, @args) = @_;
        my $sth = $class->sql_new_music;
        $sth->execute(@args);
        return $class->sth_to_objects($sth);
      }

    The $sth which is used to return the objects here is a normal DBI-style
    statement handle, so if the results can't be turned into objects easily,
    it is still possible to call $sth->fetchrow_array etc and return
    whatever data you choose.

    Of course, any query can be added via set_sql, including joins. So, to
    add a query that returns the 10 Artists with the most CDs, you could
    write (with MySQL):

      Music::Artist->set_sql(most_cds => qq{
        SELECT artist.id, COUNT(cd.id) AS cds
          FROM artist, cd
         WHERE artist.id = cd.artist
         GROUP BY artist.id
         ORDER BY cds DESC
         LIMIT 10
      });

      my @artists = Music::Artist->search_most_cds();

    If you also need to access the 'cds' value returned from this query, the
    best approach is to declare 'cds' to be a TEMP column. (See
    "Non-Persistent Fields" below).

  Class::DBI::AbstractSearch
      my @music = Music::CD->search_where(
        artist => [ 'Ozzy', 'Kelly' ],
        status => { '!=', 'outdated' },
      );

    The Class::DBI::AbstractSearch module, available from CPAN, is a plugin
    for Class::DBI that allows you to write arbitrarily complex searches
    using perl data structures, rather than SQL.

  Single Value SELECTs
   select_val
    Selects which only return a single value can couple Class::DBI's
    sql_single() SQL, with the $sth->select_val() call which we get from
    DBIx::ContextualFetch.

      __PACKAGE__->set_sql(count_all => "SELECT COUNT(*) FROM __TABLE__");
      # .. then ..
      my $count = $class->sql_count_all->select_val;

    This can also take placeholders and/or do column interpolation if
    required:

      __PACKAGE__->set_sql(count_above => q{
        SELECT COUNT(*) FROM __TABLE__ WHERE %s > ?
      });
      # .. then ..
      my $count = $class->sql_count_above('year')->select_val(2001);

   sql_single
    Internally Class::DBI defines a very simple SQL fragment called
    'single':

      "SELECT %s FROM __TABLE__".  

    This is used to implement the above Class->count_all():

      $class->sql_single("COUNT(*)")->select_val;

    This interpolates the COUNT(*) into the %s of the SQL, and then executes
    the query, returning a single value.

    Any SQL set up via set_sql() can of course be supplied here, and
    select_val can take arguments for any placeholders there.

    Internally several helper methods are defined using this approach:

    - count_all
    - maximum_value_of($column)
    - minimum_value_of($column)

LAZY POPULATION
    In the tradition of Perl, Class::DBI is lazy about how it loads your
    objects. Often, you find yourself using only a small number of the
    available columns and it would be a waste of memory to load all of them
    just to get at two, especially if you're dealing with large numbers of
    objects simultaneously.

    You should therefore group together your columns by typical usage, as
    fetching one value from a group can also pre-fetch all the others in
    that group for you, for more efficient access.

    So for example, if we usually fetch the artist and title, but don't use
    the 'year' so much, then we could say the following:

      Music::CD->columns(Primary   => qw/cdid/);
      Music::CD->columns(Essential => qw/artist title/);
      Music::CD->columns(Others    => qw/year runlength/);

    Now when you fetch back a CD it will come pre-loaded with the 'cdid',
    'artist' and 'title' fields. Fetching the 'year' will mean another visit
    to the database, but will bring back the 'runlength' whilst it's there.

    This can potentially increase performance.

    If you don't like this behavior, then just add all your columns to the
    Essential group, and Class::DBI will load everything at once. If you
    have a single column primary key you can do this all in one shot with
    one single column declaration:

      Music::CD->columns(Essential => qw/cdid artist title year runlength/);

  columns
      my @all_columns  = $class->columns;
      my @columns      = $class->columns($group);

      my @primary      = $class->primary_columns;
      my $primary      = $class->primary_column;
      my @essential    = $class->_essential;

    There are four 'reserved' groups: 'All', 'Essential', 'Primary' and