NAME

POE::Component::DBIAgent - POE Component for running asynchronous DBI calls.

SYNOPSIS

 sub _start {
    my ($self, $kernel, $heap) = @_[OBJECT, KERNEL, HEAP];

    $heap->{helper} = POE::Component::DBIAgent->new( DSN => [$dsn,
					       $username,
					       $password
					      ],
				       Queries => $self->make_queries,
				       Debug => 1,
				     );

	# Queries takes a hashref of the form:
	# { query_name => 'select blah from table where x = ?',
	#   other_query => 'select blah_blah from big_view',
	#   etc.
	# }

    $heap->{helper}->query(query_name => session => 'get_row_from_dbiagent');

 }

 sub get_row_from_qa {
    my ($kernel, $self, $heap, $row) = @_[KERNEL, OBJECT, HEAP, ARG0];
    if ($row ne 'EOF') {

 # {{{ PROCESS A ROW

	#row is a listref of columns

 # }}} PROCESS A ROW

    } else {

 # {{{ NO MORE ROWS

	#cleanup code here

 # }}} NO MORE ROWS

    }

 }

DESCRIPTION

The DBIAgent is your answer to non-blocking DBI in POE.

It fires off child processes (configurable, defaults to 3) and feeds database queries to it via two-way pipe (or however Wheel::Run is able to manage it). The only method is query().

Usage

Not EVERY query should run through the DBI agent. If you need to run a short query within a state, sometimes it can be a hassle to have to define a whole seperate state to receive its value. The determining factor, of course, is how long your query will take. If you are trying to retrieve one row from a properly indexed table, use $dbh->selectrow_array(). If there's a join involved, or multiple rows, or a view, use DBIAgent. If it's a longish query and startup costs don't matter to you, do it inline. If startup costs DO matter, use the Agent.

Return Values

The state in the session specified in the call to query() will receive in its ARG0 parameter the return value from the query. If your query returns multiple rows, then your state will be called multiple times, once per row. ADDITIONALLY, your state will be called one time with ARG0 containing the string 'EOF'. For DML (INSERT, UPDATE, DELETE), this is the only time. A way to utilise this might be as follows:

 sub some_state {
     #...
     if ($enough_values_to_begin_updating) {

	 $heap->{dbiagent}->query(update_values_query =>
				  this_session =>
				  update_next_value =>
				  shift @{$heap->{values_to_be_updated}}
				 );
     }
 }

 sub update_next_value {
     my ($self, $heap) = @_[OBJECT, HEAP];
     # we got 'EOF' in ARG0 here but we don't care

     for (1..3) {		# Do three at a time!
	 my $value;
	 last unless defined ($value = shift @{$heap->{values_to_be_updated}});
	 $heap->{dbiagent}->query(update_values =>
				  this_session =>
				  update_next_value =>
				  $value
				 );
     }

 }

new()

Creating an instance creates a POE::Session to manage communication with the Helper processes. Queue management is transparent and automatic. The constructor is named new() (surprised, eh? Yeah, me too). The parameters are as follows:

DSN

An arrayref of parameters to pass to DBI->connect (usually a dsn, username, and password).

Queries

A hashref of the form Query_Name => "$SQL". For example:

{
  sysdate => "select sysdate from dual",
  employee_record => "select * from emp where id = ?",
  increase_inventory => "update inventory
                         set count = count + ?
                         where item_id = ?",
}

As the example indicates, DBI placeholders are supported, as are DML statements.

Count

The number of helper processes to spawn. Defaults to 3. The optimal value for this parameter will depend on several factors, such as: how many different queries you will be running, how much RAM you have, how often you run queries, and how many queries you intend to run simultaneously.

query(query_name, session, state, [ parameter, parameter, ... ])

The query() method takes at least three parameters, plus any bind values for the specific query you are executing.

Query Name

This parameter must be one of the keys to the Queries hashref you passed to the constructor. It is used to indicate which query you wish to execute.

Session, State

These parameters indicate the POE state that is to receive the data returned from the database. The state indicated will receive the data in its ARG0 parameter. PLEASE make sure this is a valid state, otherwise you will spend a LOT of time banging your head against the wall wondering where your query data is.

Query Parameters

These are any parameters your query requires. WARNING: You must supply exactly as many parameters as your query has placeholders! This means that if your query has NO placeholders, then you should pass NO extra parameters to query().

Since this is the first release of this module, suggestions to improve this syntax are welcome. Consider this subject to change.

finish()

The finish() method tells DBIAgent that the program is finished sending queries. DBIAgent will shut its helpers down gracefully after they complete any pending queries. If there are no pending queries, the DBIAgent will shut down immediately.

NOTES

• Error handling is practically non-existent.

• The calling syntax is still pretty weak.

• I might eventually want to support returning hashrefs, if there is any demand.

• Every query is prepared at Helper startup. This could potentially be pretty expensive. Perhaps a cached or deferred loading might be better? This is considering that not every helper is going to run every query, especially if you have a lot of miscellaneous queries.

Suggestions welcome! Diffs more welcome! :-)

AUTHOR

This module has been fine-tuned and packaged by Rob Bloodgood <robb@empire2.com>. However, alot of the code came directly from Fletch <fletch@phydeaux.org>, either directly (Po:Co:DBIAgent:Queue) or via his ideas. Thank you, Fletch!

However, I own all of the bugs.

This module is free software; you may redistribute it and/or modify it under the same terms as Perl itself.