NAME

POE::Component::Pool::DBI - Simplified DBI access through a pooled resource.

SYNOPSIS

use POE qw( Component::Pool::DBI );

POE::Session->create(
    inline_states => {
        _start => sub {
            my ($kernel, $heap) = @_[ KERNEL, HEAP ];

            my $dbpool = POE::Component::Pool::DBI->new(
                MaxConnections  => 10,
                DSN             => "DBI:mysql:database=test",
                Username        => "username",
                Password        => "password"
            );

            # Outstanding queries keep the calling session alive.
            $dbpool->query(
                callback => "handle_result",
                query    => "select foo from bar where foo = ?",
                args     => [ "foo" ],
                userdata => "example"
            );

            $heap->{dbpool} = $dbpool;
        },

        handle_result => sub {
            my ($kernel, $heap, $results, $userdata) = 
                @_[ KERNEL, HEAP, ARG0, ARG1 ];

            # Will be an arrayref of hashrefs.
            for my $record (@$results) {
                print $record->{foo};
            }

            my $dbpool = $heap->{dbpool};

            # Queries which do not return data should use the do method.
            # If no callback is supplied, no callback happens.  This is
            # suitable for queries where the result is not necessarily
            # important.
            $dbpool->do(
                query => "INSERT INTO results (query, count) VALUES (?,?)",
                args  => [ $udata, @$results ],
            );

            # Ask for a clean shutdown.
            $dbpool->shutdown;
        },
    },
);

POE::Kernel->run();

DESCRIPTION

This component provides a threadpool-backed DBI connection pool. It's fairly well optimized for high throughput (particularly insert) servers which rely on a relational database. It enables pooled connectivity by being implemented upon a limited-availability thread pool, with each thread maintaining a connection. It uses an asyncronous queue for allowing excess queries to 'stack up', in order to enable availability.

RATIONALE

Why yet *another* DBI interface for POE? There are already about 6. But in looking for a solution for a high availability UDP server I was unable to find one which managed pooling effectively, and I always thought this would be a reasonable application of the partner component, POE::Component::Pool::Thread.

CONSTRUCTORS

new LOTS_OF_THINGS

connections

The size, in number of threads and connections, of the resource pool.

dsn

The database service name, as per the DBI->connect method.

username

The username on the database (DBI->connect).

password

The password on the database (DBI->connect).

METHODS

query ARGUMENTS

The query method enqueues a query to be run by the job pool. The preparation of the query will be cached, so it's suggested you use placeholders to ensure the fastest response times and to avoid leaking statements.

query

The query argument holds the SQL or PL/SQL statement to execute. The statement will be invoked immediately, but it will be cached within the connection (see DBI prepare_cached).

args

The arguments to provide to DBI's execute method (see DBI).

callback

The state to invoke wihen the operation is complete. The state will be provided with the results of the query (if applicable), as an array of hashref (faciliated by DBI's fetchall_arrayref({})) assuming your driver supports this functionality (most do).

userdata

Assuming a callback has been provided, this data will be provided to the callback. This data is cached within the controlling session, and is not "available" from inside the job threads. It needs not be shared in any way.

do ARGUMENTS

The do operation will invoke a query, but forgo attempting to fetch any results from the query. This method should be used for statements which do not return result sets, such as INSERT or UPDATE statements on most databases. This operation accepts the same arguments as the query method, which are documented above.

shutdown

The shutdown operation piggybacks off of POE::Component::Pool::Thread (the PoCo:P:T session is used as the management session for this component). It simply asks all the threads to shut down.

BUGS

• This component intentionally doesn't allow fine graned control over prepared statement objects.

• Some types of data sharing in ithreads have been known to leak,

• My tests have shown this component, as well as POE::Component::Pool::Thread, does not work with the forks pragma.

• This module doesn't particularly support transactional operations.

AUTHOR

Scott S. McCoy (tag@cpan.org)