The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-QueryByName-0.18
River stage zero No dependents
++
/ DBIx::QueryByName

NAME

DBIx::QueryByName - Execute SQL queries by name

DESCRIPTION

DBIx::QueryByName allows you to decouple SQL code from Perl code by replacing inline SQL queries with method calls.

The idea is to write the code of your SQL queries somewhere else than in your perl code (in a XML file for example) and let DBIx::QueryByName load those SQL declarations and generate methods to execute each query as a usual object method call.

As sugar on your code, DBIx::QueryByName let you choose to get the queries's results as plain DBI statement handles, as single row results or as various iterator-like objects.

This module also implements automatic database session recovery and query retry, when it is deemed safe to do so. It was specifically designed to be used as a high availability interface against a cluster of replicated postgres databases running behind one service IP.

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

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

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 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 $$) 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 :)

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 query, begin_work, commit, 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 retry attribute in the query's xml definition (See 'XML SYNTAX').

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.

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.

QUERY RESULTS

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

  • a DBI statement handle

  • a scalar containing the fetched value

  • a reference to a hash containing the fetched row

  • an iterator object that returns scalars

  • an iterator object that returns hashrefs

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

Result as a sth

By default, if the 'result' attribute is not set or set to sth, a DBI statement handle is returned, on which you may call 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'});
    while (my $v = $sth->fetchrow_hashref) {
        # do stuff with $v
    }

Result as a hashref

If the 'result' attribute is set to hashref, the query gets executed and fetchrow_hashref is called on it. IMPORTANT: the query is expected to return only one row. If multiple rows may be returned, an error occurs. The matching row is returned as a hash reference. The previous example then becomes:

Example:

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

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

    # NOTE: an error occurs if get_bcd would return more than 1 row

Result as a hashref iterator

If the 'result' attribute is set to hashrefiterator, the query gets executed and an iterator object is returned that supports the method next. next returns the next fetched row as a hashref or undef if no more rows are available. The previous example then becomes:

Example:

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

    my $it = $dbh->get_bcd({ a => 'this'});
    while (my $v = $it->next) {
        # do stuff with $v
    }

    # alternatively, tell next() to return hash values as a list:
    while ( my ($id,$name) = $it->next('id','name') ) {
        last if (!defined $id);
        ...
    }

For more details on how to use hashref iterators, read the pod of DBIx::QueryByName::Result::HashIterator.

Result as a scalar

If the 'result' attribute is set to scalar, the query gets executed and fetchrow_array is called on it. IMPORTANT: the query is expected to return only one row and this row should have only one column. Anything else triggers an error. Otherwise, it returns the columns value as a scalar. Usefull for queries like 'SELECT COUNT(*) FROM...'.

Example:

    # assuming the xml definition:
    # <query name="get_max_id" params="user" result="scalar">SELECT MAX(id) FROM jobs WHERE user=?</query>

    my $max = $dbh->get_max_id({ user => 'me'});

Result as a scalar iterator

Finally, if the 'result' attribute is set to scalariterator, an iterator object is returned. It has the same api than for hashrefiterator, meaning it implements the next method. An error occurs if the returned rows have more than one column.

Example:

    # assuming the xml definition:
    # <query name="get_job_id" params="a" result="scalariterator">SELECT id FROM jobs WHERE user=?</query>

    # list all the jobs of user 'me'
    my $it = $dbh->get_job_id({ user => 'me'});
    while (my $id = $it->next) {
        # do stuff with the scalar $id
    }

For more details on how to use scalar iterators, read the pod of DBIx::QueryByName::Result::ScalarIterator.

INTERFACE

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

Return an instance of DBIx::QueryByName.

$dbh->connect($session_name, @dbi_connection_params);

Declare how to open (later on) a database connection called $session_name with the provided standard DBI connection parameters. Actually opening the connection is defered until needed, that is until one of query(), quote(), begin_work(), rollback() or commit() is called or any of the named queries loaded for this session.

Example: $dbh->connect('db',"dbi:Pg:dbname=$db;host=$host;port=$port", $username, $password, {pg_enable_utf8 => 1});

$dbh->load(session => $session_name, from_xml_file => $file)

or

$dbh->load(session => $session_name, from_xml => $string)

Load SQL queries from the xml query file $queryfile or the string $string. Afterward, to execute those queries just call the method of the same name on $dbh. This method will automatically execute the corresponding query over the database connection $session_name. Return $dbh.

$dbh->unload(session => $session_name)

Unload all the queries previously loaded under the session $session_name. This let's you implement a reload mechanism in which you would call unload followed by a number of calls to load. Return $dbh.

my $res = $dbh->$your_query_name( )

or

my $res = $dbh->$your_query_name( {param1 => value1, param2 => value2...} )

or

my $res = $dbh->$your_query_name( \%values1, \%values2, \%values3... )

Once you have specified how to connect to the database with connect() and loaded some named queries with load(), you can execute any of the sql queries by its name as a method of $dbh.

Both single execution and bulk execution are supported.

If the query has no sql parameters, just call the query's method without parameters. Example:

    $dbh->increase_counter( );

If the query accept a values to bind to sql parameters, pass those values as an anonymous hash in which keys are the names of sql parameters and values are their values. Example:

    $dbh->add_book( { author => 'me',
                      title => 'my life',
                      isbn => $blabla,
                    } );

If the query allows it, you may perform bulk execution and execute multiple parameter hashes at once. This is done by calling DBI's execute_array method. Example:

    # insert 2 books at once (or more)
    $dbh->add_book( { author => 'me',
                      title => 'my life',
                      isbn => $blabla,
                    },
                    { author => 'you',
                      title => 'your life',
                      isbn => $moreblabla,
                    },
                  );

For a detailed description of what the named query returns, refer to the section 'QUERY RESULTS'.

my $bool = $dbh->can($query_name)

Return 1 if the query $query_name is implemented, 0 if not.

my @params = $dbh->params($query_name)

Return the named parameters expected by the query $query_name.

The following methods are just aliases for the corresponding DBI methods. Do not use them if you don't really have to as some might be removed in a later version of this module.

$dbh->rollback($session_name);

Perform a rollback on the session named $session_name and return its result.

$dbh->commit();

Perform a commit on the session named $session_name and return its result.

$dbh->begin_work();

Call the DBI begin_work() method on the session named $session_name and return its result.

$dbh->quote($session_name, $string);

Call DBI's quote() method on $string for the database handler associated with $session_name and return its result. WARNING: this method might be removed from later versions as it is outside the core scope of this module. Use at your own risk.

my $sth = $dbh->query($session_name,$sql);

Call prepare and execute for this SQL. Return the executed statement handler. WARNING: this method might be removed from later versions as it only provides a backdoor to the querying-by-name mechanism. Use at your own risk.

XML SYNTAX

When calling load() with from_xml or from_xml_file, the XML string expected must have the following format:

    <queries>
        <query name="{query's name}"
               params="{names of the sql's placeholders, as a comma-separated and in order of appearance}"
               result="one of (sth|scalar|scalariterator|hashref|hashrefiterator)"
               retry="one of (safe|always|never)">
        {some sql code with placeholders}</query>
        <query ...>...</query>
        <query ...>...</query>
        <query ...>...</query>
        ...
    </queries>

Always use placeholders ('?' signs) in your SQL!

The result tag specifies what kind of object the named query should return.

The retry attribute is optional and tells DBIx::QueryByName how to behave upon getting a timeout error from the server. If retry is set to 'never', the query will never be retried if it fails. If retry is set to 'safe', the query will be retried only if it failed with an error message that guarrantees that the query was not executed by the server. If retry is set to 'always' and the error message is a known one (see SthPool.pm), the query gets retried. Note that this feature recognizes only a restricted subset of server error messages, and does not support servers other than postgres. If retry is omitted, it defaults to 'safe'.

DEBUGGING

To see all the gutwork happening on stderr, set the environment variable DBIXQUERYBYNAMEDEBUG to 1.

KNOWN ISSUES

Forked processes not calling queries

If a process opens one or more database connections and forks, but it's child opens no database connection of its own, the connections of the parent will be closed without respect to InactiveDestroy when the child exits. To avoid troubles, always commit data explicitely.

Execute does not timeout

In some cases, a call to DBI's execute method (or ping) may hang forever. This may happen if you loose contact with the server during an operation. DBIx::QueryByName does no attempt at making execute to timeout. This is a design decision.

The only alternative would be to implement a eval/die/alarm block around the execute call but that would require to run perl with unsafe signal handling, which the authors declined to do.

For an example of how to implement such an eval/die/alarm block, see the source for SthPool.pm.

SEE ALSO

DBIx::NamedQuery: almost the same but doesn't support named parameters, forks and multiple simultaneous database connections.

AUTHORS

Created by Joel Jacobson <joel AT gluefinance.com>.

Maintained by Erwan Lemonnier <erwan AT gluefinance.com> with the support of Claes Jakobsson <claes AT gluefinance.com>.

COPYRIGHT AND DISCLAIMER

This module was developed by Glue Finance AB as part of the corporation's software development activities. This module is distributed under the same terms as Perl itself. We encourage you to help us improving this module by sending feedback and bug reports to the maintainer(s).

This module is provided 'as is' and comes with no warranty. Glue Finance AB as well as the author(s) decline any responsibility for the consequences of using all or part of this module.

Glue Finance is a payment solution provider based in Stockholm, Sweden. Our clients include online and offline companies requiring low cost instant payment transfers domestically and internationally. For more information, please see our website.

SVN INFO

$Id$