/ 
POE-Component-SimpleDBI-1.07
River stage one • 1 direct dependent • 1 total dependent
/ POE::Component::SimpleDBI

NAME

POE::Component::SimpleDBI - Perl extension for asynchronous non-blocking DBI calls in POE

SYNOPSIS

use POE;
use POE::Component::SimpleDBI;

# Set up the DBI
POE::Component::SimpleDBI->new(
	ALIAS	=> 'SimpleDBI',
	DSN	=> 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306',
	USERNAME => 'FooBar',
	PASSWORD => 'SecretPassword',
) or die 'Unable to create the DBI session';

# Create our own session to communicate with SimpleDBI
POE::Session->create(
	inline_states => {
		_start => sub {
			$_[KERNEL]->post( 'SimpleDBI', 'DO',
				SQL => 'DELETE FROM FooTable WHERE ID = ?',
				PLACEHOLDERS => [ qw( 38 ) ],
				EVENT => 'deleted_handler',
			);

			$_[KERNEL]->post( 'SimpleDBI', 'SINGLE',
				SQL => 'Select * from FooTable',
				EVENT => 'success_handler',
				BAGGAGE => 'Some Stuff I want to keep!',
			);

			my $id = $_[KERNEL]->call( 'SimpleDBI', 'MULTIPLE',
				SQL => 'SELECT foo, baz FROM FooTable2 WHERE id = ?',
				EVENT => 'multiple_handler',
				PLACEHOLDERS => [ qw( 53 ) ],
			);

			$_[KERNEL]->post( 'SimpleDBI', 'QUOTE',
				SQL => 'foo$*@%%sdkf"""',
				EVENT => 'quote_handler',
			);

			# Changed our mind!
			$_[KERNEL]->post( 'SimpleDBI', 'Delete_Query', $id );

			# 3 ways to shutdown

			# This will let the existing queries finish, then shutdown
			$_[KERNEL]->post( 'SimpleDBI', 'shutdown' );

			# This will terminate when the event traverses
			# POE's queue and arrives at SimpleDBI
			$_[KERNEL]->post( 'SimpleDBI', 'shutdown', 'NOW' );

			# Even QUICKER shutdown :)
			$_[KERNEL]->call( 'SimpleDBI', 'shutdown', 'NOW' );
		},

		success_handler => \&success_handler,
		deleted_handler => \&deleted_handler,
		quote_handler	=> \&quote_handler,
		multiple_handler => \&multiple_handler,
	},
);

sub quote_handler {
	# For QUOTE calls, we receive the scalar string of SQL quoted
	# $_[ARG0] = {
	#	SQL => The SQL You put in
	#	RESULT	=> scalar quoted SQL
	#	PLACEHOLDERS => The placeholders
	#	ACTION => QUOTE
	#	BAGGAGE => whatever you set it to
	# }

	if ( exists $_[ARG0]->{'ERROR'} ) {
		# Handle error here
	}
}

sub deleted_handler {
	# For DO calls, we receive the scalar value of rows affected
	# $_[ARG0] = {
	#	SQL => The SQL You put in
	#	RESULT	=> scalar value of rows affected
	#	PLACEHOLDERS => The placeholders
	#	ACTION => DO
	#	BAGGAGE => whatever you set it to
	# }

	if ( exists $_[ARG0]->{'ERROR'} ) {
		# Handle error here
	}
}

sub success_handler {
	# For SINGLE calls, we receive a hash ( similar to fetchrow_hash )
	# $_[ARG0] = {
	#	SQL => The SQL You put in
	#	RESULT	=> hash
	#	PLACEHOLDERS => The placeholders
	#	ACTION => SINGLE
	#	BAGGAGE => whatever you set it to
	# }

	if ( exists $_[ARG0]->{'ERROR'} ) {
		# Handle error here
	}
}

sub multiple_handler {
	# For MULTIPLE calls, we receive an array of hashes
	# $_[ARG0] = {
	#	SQL => The SQL You put in
	#	RESULT	=> array of hashes
	#	PLACEHOLDERS => The placeholders
	#	ACTION => MULTIPLE
	#	BAGGAGE => whatever you set it to
	# }

	if ( exists $_[ARG0]->{'ERROR'} ) {
		# Handle error here
	}
}

ABSTRACT

This module simplifies DBI usage in POE's multitasking world.

This module is a breeze to use, you'll have DBI calls in your POE program
up and running in only a few seconds of setup.

This module does what XML::Simple does for the XML world.

If you want more advanced usage, check out:
	POE::Component::LaDBI

If you want even simpler usage, check out:
	POE::Component::DBIAgent

CHANGES

1.06 -> 1.07

In the SubProcess, fixed a silly mistake in DO's execution of placeholders

Cleaned up a few error messages in the SubProcess

Peppered the code with *more* DEBUG statements :)

Replaced a croak() with a die() when it couldn't connect to the database

Documented the _child events

1.05 -> 1.06

Fixed some typos in the POD

Added the BAGGAGE option

1.04 -> 1.05

Fixed some typos in the POD

Fixed the DEBUG + MAX_RETRIES "Subroutine redefined" foolishness

1.03 -> 1.04

Got rid of the EVENT_S and EVENT_E handlers, replaced with a single EVENT handler

Internal changes to get rid of some stuff -> Send_Query / Send_Wheel

Added the Delete_Query event -> Deletes an query via ID

Changed the DO/MULTIPLE/SINGLE/QUOTE events to return an ID ( Only usable if call'ed )

Made sure that the ACTION key is sent back to the EVENT handler every time

Added some DEBUG stuff :)

Added the CHANGES section

Fixed some typos in the POD

1.02 -> 1.03

Increments refcount for querying sessions so they don't go away

POD formatting

Consolidated shutdown and shutdown_NOW into one single event

General formatting in program

DB connection error handling

Renamed the result hash: RESULTS to RESULT for better readability

SubProcess -> added DBI connect failure handling

DESCRIPTION

This module works its magic by creating a new session with POE, then spawning off a child process to do the "heavy" lifting. That way, your main POE process can continue servicing other clients.

The standard way to use this module is to do this:

use POE;
use POE::Component::SimpleDBI;

POE::Component::SimpleDBI->new( ... );

POE::Session->create( ... );

POE::Kernel->run();

Starting SimpleDBI

To start SimpleDBI, just call it's new method:

POE::Component::SimpleDBI->new(
	'ALIAS'		=>	'DataBase',
	'DSN'		=>	'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306',
	'USERNAME'	=>	'DBLogin',
	'PASSWORD'	=>	'DBPass',
);

This method will die on error or return success.

NOTE: If the SubProcess could not connect to the DB, it will return an error, causing SimpleDBI to die.

NOTE: The act of starting/stopping SimpleDBI fires off _child events, read the POE documentation on what to do with them :)

This constructor accepts only 4 different options.

ALIAS

This will set the alias SimpleDBI uses in the POE Kernel. This will default TO "SimpleDBI"

DSN

This is the DSN -> Database connection string

SimpleDBI expects this to contain everything you need to connect to a database via DBI, sans the username and password.

For valid DSN strings, consult your DBI manual.

USERNAME

Simply put, this is the DB username SimpleDBI will use.

PASSWORD

Simply put, this is the DB password SimpleDBI will use.

Events

There is a few events you can trigger in SimpleDBI. They all share a common argument format, except for the shutdown and Delete_Query event.

QUOTE
This simply sends off a string to be quoted, and gets it back.

Internally, it does this:

return $dbh->quote( $SQL );

Here's an example on how to trigger this event:

$_[KERNEL]->post( 'SimpleDBI', 'QUOTE',
	SQL => 'foo$*@%%sdkf"""',
	EVENT => 'quote_handler',
);

The Event handler will get a hash in ARG0:
{
	'SQL'		=>	Original SQL inputted
	'RESULT'	=>	The quoted SQL
	'PLACEHOLDERS'	=>	Original placeholders
	'ACTION'	=>	'QUOTE'
	'ERROR'		=>	exists only if an error occured
	'BAGGAGE'	=>	whatever you set it to
}

Note: It will return the internal query ID if you store the return value via call:
my $queryid = $_[KERNEL]->call( ... );

Look at Delete_Query for what you can do with the ID.
DO
This query is specialized for those queries where you UPDATE/DELETE/INSERT/etc.

THIS IS NOT FOR SELECT QUERIES!

Internally, it does this:

$sth = $dbh->prepare_cached( $SQL );
$rows_affected = $sth->execute( $PLACEHOLDERS );
return $rows_affected;

Here's an example on how to trigger this event:

$_[KERNEL]->post( 'SimpleDBI', 'DO',
	SQL => 'DELETE FROM FooTable WHERE ID = ?',
	PLACEHOLDERS => [ qw( 38 ) ],
	EVENT => 'deleted_handler',
);

The Event handler will get a hash in ARG0:
{
	'SQL'		=>	Original SQL inputted
	'RESULT'	=>	Scalar value of rows affected
	'PLACEHOLDERS'	=>	Original placeholders
	'ACTION'	=>	'DO'
	'ERROR'		=>	exists only if an error occured
	'BAGGAGE'	=>	whatever you set it to
}

Note: It will return the internal query ID if you store the return value via call:
my $queryid = $_[KERNEL]->call( ... );

Look at Delete_Query for what you can do with the ID.
SINGLE
This query is specialized for those queries where you will get exactly 1 result back.

Keep in mind: the column names are all lowercased automatically!

NOTE: This subroutine will automatically append ' LIMIT 1' to all queries passed in.

Internally, it does this:

$sth = $dbh->prepare_cached( $SQL );
$sth->execute( $PLACEHOLDERS );
$sth->bind_columns( %result );
$sth->fetch();
return %result;

Here's an example on how to trigger this event:

$_[KERNEL]->post( 'SimpleDBI', 'SINGLE',
	SQL => 'Select * from FooTable',
	EVENT => 'success_handler',
);

The Event handler will get a hash in ARG0:
{
	'SQL'		=>	Original SQL inputted
	'RESULT'	=>	Hash of rows - similar to fetchrow_hashref
	'PLACEHOLDERS'	=>	Original placeholders
	'ACTION'	=>	'SINGLE'
	'ERROR'		=>	exists only if an error occured
	'BAGGAGE'	=>	whatever you set it to
}

Note: It will return the internal query ID if you store the return value via call:
my $queryid = $_[KERNEL]->call( ... );

Look at Delete_Query for what you can do with the ID.
MULTIPLE
This query is specialized for those queries where you will get more than 1 result back.

Keep in mind: the column names are all lowercased automatically!

Internally, it does this:

$sth = $dbh->prepare_cached( $SQL );
$sth->execute( $PLACEHOLDERS );
$sth->bind_columns( %row );
while ( $sth->fetch() ) {
	push( @results, %row );
}
return @results;

Here's an example on how to trigger this event:

$_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE',
	SQL => 'SELECT foo, baz FROM FooTable2 WHERE id = ?',
	EVENT => 'multiple_handler',
	PLACEHOLDERS => [ qw( 53 ) ],
);

The Event handler will get a hash in ARG0:
{
	'SQL'		=>	Original SQL inputted
	'RESULT'	=>	Array of hash of rows ( array of fetchrow_hashref's )
	'PLACEHOLDERS'	=>	Original placeholders
	'ACTION'	=>	'MULTIPLE'
	'ERROR'		=>	exists only if an error occured
	'BAGGAGE'	=>	whatever you set it to
}

Note: It will return the internal query ID if you store the return value via call:
my $queryid = $_[KERNEL]->call( ... );

Look at Delete_Query for what you can do with the ID.
Delete_Query
Call this event if you want to delete a query via the ID.

Returns:
	undef if it wasn't able to find the ID
	undef if the query is currently being processed
	true if the query was successfully deleted

Here's an example on how to trigger this event:

$_[KERNEL]->post( 'SimpleDBI', 'Delete_Query', $queryID );

IF you really want to know the status, execute a call on the event and check the returned value.
Shutdown
$_[KERNEL]->post( 'SimpleDBI', 'shutdown' );

This will signal SimpleDBI to start the shutdown procedure.

NOTE: This will let all outstanding queries run!
SimpleDBI will kill it's session when all the queries have been processed.

you can also specify an argument:

$_[KERNEL]->post( 'SimpleDBI', 'shutdown', 'NOW' );

This will signal SimpleDBI to shutdown.

NOTE: This will NOT let the outstanding queries finish!
Any queries running will be lost!

Due to the way POE's queue works, this shutdown event will take some time to propagate POE's queue.
If you REALLY want to shut down immediately, do this:

$_[KERNEL]->call( 'SimpleDBI', 'shutdown', 'NOW' );

Arguments

They are passed in via the $_[KERNEL]->post( ... );

NOTE: Capitalization is very important!

SQL

This is the actual SQL line you want SimpleDBI to execute. You can put in placeholders, this module supports them.

PLACEHOLDERS

This is an array of placeholders.

You can skip this if your query does not utilize it.

EVENT

This is the event, triggered whenever a query finished.

It will get a hash in ARG0, consult the specific queries on what you will get.

NOTE: If the key 'ERROR' exists in the hash, then it will contain the error string.

BAGGAGE

This is a special argument, you can "attach" any kind of baggage to a query. The baggage will be kept by SimpleDBI and returned to the Event handler intact.

This is good for storing data associated with a query like a client object, etc.

SimpleDBI Notes

This module is very picky about capitalization!

All of the options are uppercase, to avoid confusion.

You can enable debugging mode by doing this:

sub POE::Component::SimpleDBI::DEBUG () { 1 }
use POE::Component::SimpleDBI;

Also, this module will try to keep the SubProcess alive. if it dies, it will open it again for a max of 5 retries.

You can override this behavior by doing this:

sub POE::Component::SimpleDBI::MAX_RETRIES () { 10 }
use POE::Component::SimpleDBI;

EXPORT

Nothing.

SEE ALSO

DBI

POE

POE::Wheel::Run

POE::Component::DBIAgent

POE::Component::LaDBI

AUTHOR

Apocalypse <apocal@cpan.org>

COPYRIGHT AND LICENSE

Copyright 2003 by Apocalypse

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