POE::Component::SimpleDBI - Asynchronous non-blocking DBI calls in POE made simple
use POE; use POE::Component::SimpleDBI; # Create a new session with the alias we want POE::Component::SimpleDBI->new( 'SimpleDBI' ) or die 'Unable to create the DBI session'; # Create our own session to communicate with SimpleDBI POE::Session->create( inline_states => { _start => sub { # Tell SimpleDBI to connect $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306', 'USERNAME' => 'FooBar', 'PASSWORD' => 'SecretPassword', 'EVENT' => 'conn_handler', ); # Execute a query and return number of rows affected $_[KERNEL]->post( 'SimpleDBI', 'DO', 'SQL' => 'DELETE FROM FooTable WHERE ID = ?', 'PLACEHOLDERS' => [ qw( 38 ) ], 'EVENT' => 'deleted_handler', ); # Retrieve one row of information $_[KERNEL]->post( 'SimpleDBI', 'SINGLE', 'SQL' => 'Select * from FooTable LIMIT 1', 'EVENT' => 'success_handler', 'BAGGAGE' => 'Some Stuff I want to keep!', ); # We want many rows of information + get the query ID so we can delete it later my $id = $_[KERNEL]->call( 'SimpleDBI', 'MULTIPLE', 'SQL' => 'SELECT foo, baz FROM FooTable2 WHERE id = ?', 'PLACEHOLDERS' => [ qw( 53 ) ], 'EVENT' => 'multiple_handler', ); # Quote something and send it to another session $_[KERNEL]->post( 'SimpleDBI', 'QUOTE', 'SQL' => 'foo$*@%%sdkf"""', 'SESSION' => 'OtherSession', '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' ); }, # Define your request handlers here 'quote_handler' => \&FooHandler, # And so on }, );
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
Hannes had a problem: His IRC bot logs events to a database, and sometimes there is no events to log after hours and hours of inactivity ( must be a boring channel haha ), the db server disconnected! The solution was to do a $dbh->ping() before each query, if your DBI driver does it inefficiently, go yell at them! In the event that a reconnect is not possible, an error will be sent to the CONNECT event handler, look at the updated pod.
Fixed a bug in the DO routine, thanks to Hannes!
Removed the abstract LIMIT 1 to the SINGLE query Removed the silly 5.8.x requirement in Makefile.PL Made the SubProcess use less memory by exec()ing itself Added the new CONNECT/DISCONNECT commands Removed the db connection information from new() Minor tweaks here and there to not stupidly call() the queue checker when there is nothing to check :) Added the sysreaderr debugging output More intelligent SQL/PLACEHOLDERS/BAGGAGE handling Made the command arguments more stricter, it will only accept valid arguments, instead of just extracting what it needs Made sure all return data have ID/EVENT/SESSION/ACTION in them for easy debugging Added the SESSION parameter to all commands for easy redirection Updated the POD and generally made it better :) Added a new command -> Clear_Queue ( clears the queue )
In the SubProcess, removed the select statement requirement
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
Fixed some typos in the POD Added the BAGGAGE option
Fixed some typos in the POD Fixed the DEBUG + MAX_RETRIES "Subroutine redefined" foolishness
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
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
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. Queries are put into a queue, and processed one at a time.
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();
To start SimpleDBI, just call it's new method:
POE::Component::SimpleDBI->new( 'ALIAS' );
This method will die on error or return success.
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 1 argument: the alias. The default is "SimpleDBI".
There are a few commands you can trigger in SimpleDBI. They are triggered via $_[KERNEL]->post( ... );
All of the commands except for Delete_Query and shutdown return an id. To get them, do this: my $id = $_[KERNEL]->call( 'SimpleDBI', ... );
Afterwards, the id can be used to delete queries, look at Delete_Query for more information.
All of the commands validate their arguments, and if an error happens ( missing argument, etc ), they will do either: - return undef and forget that your request even existed - post to the SESSION/EVENT with ERROR present in the data NOTE: The data will not have an ID key present
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.
SESSION
This is the session that will get the result
You can skip this, it defaults to the sending session
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.
CONNECT
This tells SimpleDBI to connect to the database NOTE: if we are already connected, it will be a success ( SimpleDBI will not disconnect then connect automatically ) Accepted arguments: DSN -> The DBI DSN string, consult the DBI docs on what this is USERNAME -> The username for the connection PASSWORD -> The password for the connection SESSION -> The session to send the results EVENT -> The event to send the results NOW -> Tells SimpleDBI to bypass the queue and connect NOW! CLEAR -> Tells SimpleDBI to clear the queue and connect NOW! NOTE: if the DSN/USERNAME/PASSWORD/SESSION/EVENT does not exist, SimpleDBI assumes you wanted to use the old connection and will use the cached values ( if you told it to DISCONNECT ). Here's an example on how to trigger this event: $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:mysql:database=foobaz;host=192.168.1.100;port=3306', 'USERNAME' => 'MyUser', 'PASSWORD' => 'MyPassword', 'EVENT' => 'conn_handler', 'NOW' => 1, ); The NOW/CLEAR arguments are special, they will tell SimpleDBI to bypass the request queue and connect NOW... The CLEAR argument will also delete all the requests waiting in the queue, they will get an ERROR result They both default to false, supply a boolean value to turn them on The Event handler will get a hash in ARG0: { 'ERROR' => exists only if an error occured 'GONE' => exists only if the server was disconnected and the reconnect failed 'ACTION' => 'CONNECT' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to } Receiving this event without the ERROR key means SimpleDBI successfully connected and is waiting for queries NOTE: You can do nifty things like: $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:mysql:...', ... ); $_[KERNEL]->post( 'SimpleDBI', 'DO', ... ); $_[KERNEL]->post( 'SimpleDBI', 'SINGLE', ... ); $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT' ); $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', 'DSN' => 'DBI:oracle:...', ... ); $_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE', ... ); $_[KERNEL]->post( 'SimpleDBI', 'shutdown' ); They all will be executed in the right order! As of 1.11 SimpleDBI now detects whether the backend lost the connection to the database server. The backend will automatically reconnect if it happens, but if that fails, an error will be sent to the session/event specified here with an extra key: 'GONE'. In this state SimpleDBI is deadlocked, any new queries will not be processed until a CONNECT NOW event is issued! Keep in mind the SINGLE/etc queries WILL NOT receive an error if this happens, the error goes straight to the CONNECT handler to keep it simple!
DISCONNECT
This tells SimpleDBI to disconnect from the database NOTE: In the case that a DISCONNECT is issued when we are not connected, it will still succeed... Accepted arguments: SESSION -> The session to send the results EVENT -> The event to send the results NOW -> Tells SimpleDBI to bypass the queue and disconnect NOW! CLEAR -> Tells SimpleDBI to clear the queue and disconnect NOW! Here's an example on how to trigger this event: $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT', 'EVENT' => 'disconn_handler', 'NOW' => 1, ); The NOW/CLEAR arguments are special, they will tell SimpleDBI to bypass the request queue and connect NOW... The CLEAR argument will also delete all the requests waiting in the queue, they will get an ERROR result They both default to false, supply a boolean value to turn them on The Event handler will get a hash in ARG0: { 'ERROR' => exists only if an error occured 'ACTION' => 'DISCONNECT' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to } Receiving this event without the ERROR key means SimpleDBI successfully disconnected BEWARE: There is the possibility of a deadlock: $_[KERNEL]->post( 'SimpleDBI', 'CONNECT', ... ); $_[KERNEL]->post( 'SimpleDBI', 'MULTIPLE', ... ); $_[KERNEL]->post( 'SimpleDBI', 'DISCONNECT' ); $_[KERNEL]->post( 'SimpleDBI', 'DO', ... ); $_[KERNEL]->post( 'SimpleDBI', 'SINGLE', ... ); $_[KERNEL]->post( 'SimpleDBI', 'CONNECT' ); In this case, the DO/SINGLE queries will NEVER run until you issue a CONNECT with NOW enabled
QUOTE
This simply sends off a string to be quoted, and gets it back. Accepted arguments: SESSION -> The session to send the results EVENT -> The event to send the results SQL -> The string to be quoted BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it ) 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: { 'ERROR' => exists only if an error occured 'ACTION' => 'QUOTE' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to 'SQL' => Original SQL inputted 'RESULT' => The quoted SQL 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided ) 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided ) }
DO
This query is specialized for those queries where you UPDATE/DELETE/INSERT/etc. THIS IS NOT FOR SELECT QUERIES! Accepted arguments: SESSION -> The session to send the results EVENT -> The event to send the results SQL -> The string to be quoted PLACEHOLDERS -> Any placeholders ( if needed ) BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it ) 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: { 'ERROR' => exists only if an error occured 'ACTION' => 'DO' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to 'SQL' => Original SQL inputted 'RESULT' => Scalar value of rows affected 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided ) 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided ) }
SINGLE
This query is specialized for those queries where you will get exactly 1 result back. Accepted arguments: SESSION -> The session to send the results EVENT -> The event to send the results SQL -> The string to be quoted PLACEHOLDERS -> Any placeholders ( if needed ) BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it ) 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( %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', SESSION => 'MySession', ); The Event handler will get a hash in ARG0: { 'ERROR' => exists only if an error occured 'ACTION' => 'SINGLE' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to 'SQL' => Original SQL inputted 'RESULT' => Hash of rows - similar to fetchrow_hashref 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided ) 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided ) }
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! Accepted arguments: SESSION -> The session to send the results EVENT -> The event to send the results SQL -> The string to be quoted PLACEHOLDERS -> Any placeholders ( if needed ) BAGGAGE -> Any extra data to keep associated with this query ( SimpleDBI will not touch it ) 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: { 'ERROR' => exists only if an error occured 'ACTION' => 'MULTIPLE' 'ID' => ID of the Query 'EVENT' => The event the query will respond to 'SESSION' => The session the query will respond to 'SQL' => Original SQL inputted 'RESULT' => Array of hash of rows ( array of fetchrow_hashref's ) 'PLACEHOLDERS' => Original placeholders ( may not exist if it was not provided ) 'BAGGAGE' => whatever you set it to ( may not exist if it was not provided ) }
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 0 if the query is currently being processed 1 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.
Clear_Queue
This event will clear the entire queue except the running query, if there is one. You can also pass in one argument -> the error string to be used instead of the default, 'Cleared the queue' All the queries in the queue will return ERROR to their respective sessions/events
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' );
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;
Nothing.
DBI
POE
POE::Wheel::Run
POE::Component::DBIAgent
POE::Component::LaDBI
Apocalypse <apocal@cpan.org>
Copyright 2004 by Apocalypse
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install POE::Component::SimpleDBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm POE::Component::SimpleDBI
CPAN shell
perl -MCPAN -e shell install POE::Component::SimpleDBI
For more information on module installation, please visit the detailed CPAN module installation guide.