The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
POE-Component-SimpleDBI-1.06
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.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 croak.

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