NAME
   Rose::DBx::CannedQuery - Conveniently manage a specific SQL query

SYNOPSIS
     use Rose::DBx::CannedQuery;
     my $qry = Rose::DBx::CannedQuery->new(rdb_class => 'My::DB',
                 rdb_params => { type => 'real', domain => 'some' },
                 sql => 'SELECT * FROM table WHERE attr = ?');
     foreach my $row ( $qry->results($bind_val) ) {
       do_something($row);
     }

     sub do_something_repeatedly {
       ...
       my $qry = Rose::DBx::CannedQuery->new_or_cached(rdb_class => 'My::DB',
                 rdb_params => { type => 'real', domain => 'some' },
                 sql => 'SELECT my, items FROM table WHERE attr = ?');
       # Exdcute query and manage results
       ...
     }

DESCRIPTION
   This class provides a convenient means to execute specific queries
   against a database fronted by Rose::DB subclasses, in a manner similar
   to (and I hope a bit more flexible than) the DBI's "selectall_arrayref"
   in DBI method. You can set up the query once, then execute it whenever
   you need to, without worrying about the mechanics of the database
   connection.

   The database connection is not actually made and the query is not
   actually executed until you retrieve results or the active statement
   handle.

 ATTRIBUTES
   The specifics of the query are passed as attributes at object
   construction. You may specify the database connection in either of two
   ways:

   rdb_class
   rdb_params
       These describe, respectively, the Rose::DB-derived class and the
       parameters to be passed to that class' "new" in Rose::DB method to
       create the Rose::DB object. The "rdb_params" attribute must be a
       hash reference, the single-argument shortcut allowed by Rose::DB to
       specify just a data source "type" is not supported.

       When the Rose::DB>-derived object is created, the information in
       rdb_params will be merged with the class' default attributes, with
       attributes in rdb_params taking precedence. You may omit
       "rdb_params" if "rdb_class" has default domain and type values that
       point to a specific datasource; if this isn't the case, Rose::DB
       will die noisily.

       Rose::DBx::CannedQuery provides a small set of defaults:

         { connect_options =>
            { RaiseError => 1,
              PrintError => 0,
              AutoCommit => 1
            }
         }

       Subclasses may change or extend these defaults (see below). The
       merged parameters are then passed to the "rdb_class"' new_or_cached
       constructor.

       If a Rose::DBx::CannedQuery object was created by passing in a
       Rose::DB database handle directly, the rdb_params attribute will
       return "type" and "domain" information only; if you want more
       information about the handle, you can call Rose::DB accessor methods
       on it directly.

   rdb This is the Rose::DB-derived object ("handle") managing the database
       connection. It may be supplied at connection time instead of the
       rdb_class and rdb_params parameters, if you want to make use of an
       already-constructed database handle.

   One or the other of these attribute sets must be provided when creating
   a Rose::DBx::CannedQuery object.

   Other attributes are:

   sql The SQL query that this object mediates is supplied as a string.
       This attribute is required.

   sth The DBI statement handle mediating the canned query. This is a
       read-only accessor; a statement handle cannot be specified at object
       construction.

 CLASS METHODS
   new(*%args*)
       Create a new Rose::DBx::CannedQuery, taking values for its
       attributes from *%args*. In the style of Moose, *%args* may be
       either a list of key-value pairs or a single hash reference.

       The "sql" attribute is required, as is either "rdb" or enough of
       "rdb_class" and "rdb_params" to construct a database handle. If
       "rdb" is provided, it will be used regardless of the values in
       "rdb_class" and "rdb_params". Otherwise, "rdb_class"'
       "new_or_cached" in Rose::DB will be called with the contents of
       "rdb_params" as parameters to obtain a new Rose::DB-derived database
       object.

   new_or_cached(*%args*)
       Attempt to retrieve a cached Rose::DBx::CannedQuery matching
       *%args*. If successful, return the existing query object. If not,
       create a query via "new" and add it to the cache. See "CACHING
       QUERIES" for a description of the query cache

 OBJECT METHODS
   dbh Convenience method that returns the DBI database handle associated
       with this object. It is equivalent to "$obj->rdb->dbh".

   setup_dbh_for_query
       Establishes the DBI database connection. It also sets the
       "FetchHashKeyName" in DBI attribute on the handle to "NAME_lc", so
       the methods below will by default return hash references with
       lowercase keys.

       Returns the DBI database handle.

   execute([*@bind_args*])
       Executes the query, binding the elements of *@bind_args*, if any, to
       placeholders in the SQL. Returns a DBI statement handle on success,
       and raises an exception on failure.

       You should use this method when you want to access the statement
       handle directly for detailed control over how the results are
       retrieved.

   results([*@bind_args*])
       Calls "execute", passing *@bind_args*, if any, and then fetches the
       results. In scalar context, returns the number of rows fetched. In
       array context, returns a list of hash references corresponding to
       rows fetched. The keys in each hash are the lower-case column names
       (cf. "setup_dbh_for_query"), and the values are the results for that
       row, as described for "fetchrow_hashref" in DBI.

   resultref([*$bind_args*, *$query_opts*])
       This method provides more flexibility than "results", at the cost of
       a slightly more complex calling sequence.

       Calls "execute", passing the contents of the array referenced by
       *$bind_args*, if any, and then fetches the results. If present,
       *$query_opts* must be an array reference, whose contents are passed
       to "fetchall_arrayref" in DBI. If *$query_opts* is omitted, an empty
       hash reference is passed, causing each row of the resultset to be
       returned as a hash reference.

       Returns the array reference resulting from the call to
       "fetchall_arrayref" in DBI.

 INTERNAL METHODS
   These methods are exposed to facilitate subclassing, and should not
   otherwise be used to interact with a Rose::DBx::CannedQuery object.

   _default_rdb_params
       This method returns a hash reference that supplies default
       parameters to be passed to the Rose::DB-derived constructor.
       Specific parameters will be overridden by equivalent keys in the
       "rdb_params" attribute.

   _retcon_rdb_class
       This method is used to generate the value of the "rdb_class"
       attribute iff the object was constructed using an existing
       Rose::DB-derived object rather than connection parameters.

   _retcon_rdb_params
       This method is used to generate the value of the "rdb_params"
       attribute iff the object was constructed using an existing
       Rose::DB-derived object rather than connection parameters. As noted
       above, it is perhaps useful for reference, but is under no
       obligation to accurately reproduce all of the parameters necessary
       to construct a Rose::DB handle just like the one owned by this
       object.

   _init_rdb
       Given the connection information in "rdb_class" and "rdb_params",
       construct a Rose::DB-derived handle for the "rdb" attribute. If
       necessary, you should arrange for "rdb_class" to be loaded. An
       exception should be raised on failure; the Rose::DB constructor
       usually does this for you.

   _init_sth
       Given a validly constructed and connected Rose::DBx::CannedQuery,
       create a prepared DBI statement handle for the query in "sql". On
       success, you should return the statement handle. On failure, you
       should raise an exception.

   BUILDARGS
       The Rose::DBx::CannedQuery BUILDARGS simply checks that either a
       Rose::DB-derived handle or the necessary connection class and
       parameters are provided.

   _query_cache([*$query_cache_object*)
       If called without any parameters, returns the query cache currently
       in use. In this form, may be called as a class or object method, but
       remember that the cache is class-wide, no matter how you retrieve
       it.

       If called with *$query_cache_object*, the current query cache (if
       any) is cleared, and the query cache is set to
       *$query_cache_object*, which must conform to the API implemented by
       Rose::DBx::CannedQuery::SimpleQueryCache. For simple variations on
       the default behavior, you may be better served by supplying an
       appropriately reconfigured Rose::DBx::CannedQuery::SimpleQueryCache
       instance than by writing a new cache class.

       If you have not set the query cache explicitly (or if you set it to
       "undef"), an instance of Rose::DBx::CannedQuery::SimpleQueryCache
       will be lazily constructed using its default behaviors when a cache
       is needed.

CACHING QUERIES
   Since one of the common uses for canned queries is execution of a
   prepared SQL statement whenever a function is called,
   Rose::DBx::CannedQuery provides a (very!) simplistic cache to keep
   queries around without requiring each place that might need the query to
   maintain state. You can use "new_or_cached" as an alternative to the
   regular "new" constructor, and it will return to you the cached version
   of a query, if any, in preference to creating a new one.

   There are a few important limitations of this caching mechanism to keep
   in mind:

   *   there is a single class-level query cache, so there will be at most
       one query object compatible with any given set of parameters passed
       to "new_or_cached" at a time. This cached object is returned in
       whatever state the last user left it, so it may have already
       "execute"d using a particular set of bind values, or be in the midst
       of fetching a resultset.

       This may be construed as a feature, if you want to be able to pick
       up where you left off in collecting results, but be careful if you
       plan to retrieve the same query from multiple places.

   *   the key used to determine whether a query is in the cache is up to
       the cache class, which is passed the arguments that were given to
       "new_or_cached". The default key generating function for
       Rose::DBx::CannedQuery::SimpleQueryCache simply serializes the
       arguments as a string. This means that two calls that refer to the
       same conceptual database operation in different ways (e.g. one which
       says "SELECT a FROM mytable ..." and another which says "SELECT a
       FROM mytable tab ...") will result in creation and caching of two
       queries. Other cache classes may be smarter.

       It also means the cache is not aware of any bind parameter values,
       so it's not possible to simultaneously cache the same query being
       executed with different bind parameters.

   *   Rose::DBx::CannedQuery::SimpleQueryCache (q.v.) makes an attempt to
       insure that a cached query hasn't been disconnected since it was
       last used. However, the checks err on the side of low overhead
       rather than comprehensiveness, and aren't foolproof. If you plan to
       leave queries untouched in the cache for a long time, you need to
       account for the possibility that you'll get a stale query back (or
       you might want to avoid the cache altogether, since the benefit is
       likely smaller).

       If your application typically handles bursts of work with intervals
       of rest in between (e.g. in responding to incoming requests), you
       may benefit from caching queries while working, then explicitly
       clearing the cache (e.g. by calling
       "Rose::DBx::Cannedquery->_query_cache->clear") at the end of each
       cycle.

EXPORT
   None.

DIAGNOSTICS
   Any message produced by an included package, as well as

   Need either Rose::DB object or information to constuct one (F)
       The constructor was called without either a "rdb" attribute or
       necessary "rdb_class" and "rdb_params" attributes.

   Failed to load class (F)
       The Rose::DB-derived class specified by "rdb_class" either couldn't
       be found or didn't load successfully.

   Error preparing query (F)
       Something went wrong when trying to "prepare" in DBI the DBI
       statement handle using "sql".

   Error executing query (F)
       A problem was encountered trying to "execute" in DBI the prepared
       query. This could be a sign of a database problem, or it may reflect
       pilot error, such as passing the wrong number of bind parameters.

   Can't recover Rose::DB class information (F)
   Can't recover Rose::DB datasource information (F)
       Somehow we managed to get an object with neither "rdb_class" or a
       "rdb" handle. This shouldn't happen; it probably means a subclass
       overrode BUILDARGS and forgot to call the superclass method.

BUGS AND CAVEATS
   All query results are prefetched by the "results" and "resultref"
   methods; if you want to iterate over a potentially large resultset,
   you'll need to call appropriate DBI methods on the statement handle
   returned by "execute".

   The default connection parameters include "RaiseError", and the
   exceptions thrown when "_init_sth" or "execute" fails also include the
   error information, so you may see it twice. Better that than not at all,
   if you happen to have changed the connection options in "rdb_params".

BUT *WHY?*
   You might think, "What's the point? How hard can it be to write a little
   wrapper around straight DBI calls? Anybody who uses a database has done
   that already. Why should I bloat my dependency chain with Rose::DB and
   some object system?" or "Why is this different from any of the other ORM
   or SQL-simplifier packages out there?" And you may well be right, if
   you're dealing with a single database connection, or are already up to
   your elbows in DBI calls.

   However, I find that this lands in a "sweet spot" for my coding style. I
   find myself dealing with several databases on a recurring basis, and
   Rose::DB is a handy way to wrap up connection information and
   credentials so they're easy to use elsewhere. But when I just want to
   pull some data, I don't necessarily need the weight of an ORM.
   Rose::DBx::CannedQuery cuts down on the boilerplate I need to make these
   queries, and lets me keep the credentials separate from the code
   (particularly when using Rose::DBx::MoreConfig), without adding the
   overhead of converting the results into objects.

   Then there's the question, "What's with the Moo stuff?" Sure,
   Rose::DBx::CannedQuery could be written using only "core" Perl
   constructs. But again, I find the Mooy sugar makes the code cleaner for
   me, and easier for someone else to subclass if they want to.

   In the end, I hope Rose::DBx::CannedQuery makes your life sufficiently
   easier that you find it worth using. If it's close, but you think it's
   not quite there, suggestions (better still, patches!) are happily
   received.

SEE ALSO
   Rose::DB and DBI for more detailed information on options for managing a
   canned query object.

   Rose::DBx::MoreConfig for an alternative to vanilla Rose::DB that lets
   you manage configuration data (such as server names and credentials) in
   a manner that plays nicely with many CI and packaging sytems.

   If you're using Rose::DB::Object as an ORM, see
   "make_manager_method_from_sql" in Rose::DB::Object::Manager for a
   similar apprach that produces objects rather than raw results.

   Moo (or Moose), if you're interested in subclassing

   Rose::DBx::CannedQuery::SimpleQueryCache for the default query cache

   Rose::DBx::CannedQuery::Glycosylated for slightly more sugary variant

VERSION
   version 1.00

AUTHOR
   Charles Bailey <[email protected]>

COPYRIGHT AND LICENSE
   Copyright (C) 2015 by Charles Bailey

   This software may be used under the terms of the Artistic License or the
   GNU General Public License, as the user prefers.

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.