view release on metacpan or  search on metacpan

lib/DBIx/QueryByName.pm  view on Meta::CPAN

DBIx::QueryByName can manage multiple database connections and is fork
safe.

=head1 SYNOPSIS

    use DBIx::QueryByName;

    my $dbh = DBIx::QueryByName->new();

    # define 2 database connections
    $dbh->connect("db1", "dbi:Pg:dbname=mydb;host=127.0.0.1;port=6666", $username, $password);
    $dbh->connect("db2", "dbi:SQLite:/path/to/db/file");

    # load some default queries to run against db1
    my $queries = <<__ENDQ__;
    <queries>
        <query name="add_job" params="id,username,description">INSERT INTO jobs (id, username, description, status) VALUES (?,?,?,0)</query>
        <query name="get_job_count" params="" result="scalar">SELECT COUNT(*) FROM jobs</query>
    </queries>
    __ENDQ__

    $dbh->load(session => 'db1', from_xml => $queries);

    # load some default queries to run against db2, from an xml file
    $dbh->load(session => 'db1', from_xml_file => $filepath);

    # now run some queries:

    # insert a few rows in db1.jobs
    $dbh->add_job( { id => 12,
                     username => "tom",
                     description => "catch mouse" } );
    $dbh->add_job( { id => 13,
                     username => "jerry",
                     description => "run away from cat" } );

    # count the number of rows:
    my $count = $dbh->get_job_count();

    # then do what you usually do with a statement handler...

=head1 SESSION MANAGEMENT AND FORK SAFETY

DBIx::QueryByName opens one database connection for every process that
needs to execute a query over a given session (as declared in
C<load()>) and for every child process of that process.

A rollback or commit (or any other database method) therefore only
affects the connection associated with the running process (defined by
its pid C<$$>) and not the connections to the same database openened
for the process's children or parents.

Notice that fork safety has been tested against Postgres databases
only. We cannot guarantee that it works with other databases :)

=head1 AUTOMATED RECOVERY

If a database connection gets interupted or closed, and the reason for
the interuption is that the database server is closing down or is not
reachable, DBIx::QueryByName will transparently try to reconnect to
the database until it succeeds and re-execute the query. Note that
this only works when you call a query by its name. Calls to C<query>,
C<begin_work>, C<commit>, C<rollback> are only aliases to the
corresponding DBI calls and will fail in the same way.

Any other connection or execution failure will still result in a
die/croak that you will have to catch and handle from within your
application.

You may change this default behavior per query by setting the C<retry>
attribute in the query's xml definition (See 'XML SYNTAX').

=head1 SUPPORTED DATABASES

DBIx::QueryByName has been tested thoroughly against postgres. We
cannot guarrantee that it will work with other databases (but it
should :). A database is supported if it provides standard error
messages (see QueryByName.pm::AUTOLOAD) and support the DBI parameter
InactiveDestroy.

=head1 LOGGING

DBIx::QueryByName logs via Log::Log4perl if it is available. If
Log::Log4perl is available but not configured, you may see warnings
poping up. Just configure a default logger in Log::Log4perl to get rid
of them.

=head1 QUERY RESULTS

The result of a named query will be one of the following:

=over 4

=item * a DBI statement handle

=item * a scalar containing the fetched value

=item * a reference to a hash containing the fetched row

=item * an iterator object that returns scalars

=item * an iterator object that returns hashrefs

=back

The type of result a query should return is defined by the xml attribute 'result'
in the named query's xml definition.

=head2 Result as a sth

By default, if the 'result' attribute is not set or set to C<sth>, a
DBI statement handle is returned, on which you may call C<fetchrow*>
or any other standard DBI method.

Example:

    # assuming the xml definition:
    # <query name="get_bcd" params="a" result="sth">SELECT b,c,d FROM jobs WHERE a=?</query>

    my $sth = $dbh->get_bcd({ a => 'this'});