| 1 | package POE::Component::Pool::DBI; | ||
| 2 | # ----------------------------------------------------------------------------- | ||
| 3 | # "THE BEER-WARE LICENSE" (Revision 43) borrowed from FreeBSD's jail.c: | ||
| 4 | # <tag@cpan.org> wrote this file. As long as you retain this notice you | ||
| 5 | # can do whatever you want with this stuff. If we meet some day, and you think | ||
| 6 | # this stuff is worth it, you can buy me a beer in return. Scott S. McCoy | ||
| 7 | # ----------------------------------------------------------------------------- | ||
| 8 | # A rather simple pooled database resource. | ||
| 9 | |||
| 10 | 1 | ▶ | use strict; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 11 | 1 | ▶ | use warnings FATAL => "all"; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 12 | |||
| 13 | 1 | ▶ | use POE qw( Component::Pool::Thread ); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 14 | use DBI; | ||
| 15 | use threads::shared; | ||
| 16 | use constant { | ||
| 17 | DEBUG => 0, | ||
| 18 | REFCOUNT_IDENTIFIER => "queries", | ||
| 19 | }; | ||
| 20 | |||
| 21 | our $VERSION = 0.014; | ||
| 22 | |||
| 23 | sub new { | ||
| 24 | my ($class, %args) = @_; | ||
| 25 | |||
| 26 | my $self = bless \%args, $class; | ||
| 27 | |||
| 28 | # For tracking the job id. | ||
| 29 | $self->{current_id} = 0; | ||
| 30 | |||
| 31 | print "Connections: $args{connections}"; | ||
| 32 | |||
| 33 | # This creates the threadpool which does the actual work. The entry point | ||
| 34 | # for the actual threads themselves is the query_database function. | ||
| 35 | $self->{session} = POE::Component::Pool::Thread->new | ||
| 36 | ( MaxFree => $args{connections}, | ||
| 37 | StartThreads => $args{connections}, | ||
| 38 | MaxThreads => $args{connections}, | ||
| 39 | EntryPoint => \&query_database, | ||
| 40 | CallBack => \&send_response, | ||
| 41 | inline_states => { | ||
| 42 | query => sub { | ||
| 43 | my ($kernel, $heap, $sender, $callback, $ud, $query, $args) = | ||
| 44 | @_[ KERNEL, HEAP, SENDER, ARG0 .. $#_ ]; | ||
| 45 | |||
| 46 | my $id = $self->{current_id}++; | ||
| 47 | |||
| 48 | $args ||= []; | ||
| 49 | $heap->{data}{$id} = $ud; | ||
| 50 | |||
| 51 | DEBUG && print "got query, calling run state"; | ||
| 52 | |||
| 53 | $kernel->yield(run => "query", @$self{qw( dsn username | ||
| 54 | password)}, $id, $sender->ID, $callback, $query, @$args); | ||
| 55 | }, | ||
| 56 | |||
| 57 | do => sub { | ||
| 58 | my ($kernel, $heap, $sender, $callback, $ud, $query, $args) = | ||
| 59 | @_[ KERNEL, HEAP, SENDER, ARG0 .. $#_ ]; | ||
| 60 | |||
| 61 | my $id = $self->{current_id}++; | ||
| 62 | |||
| 63 | $args ||= []; | ||
| 64 | $heap->{data}{$id} = $ud; | ||
| 65 | |||
| 66 | $kernel->yield(run => "do", @$self{qw( dsn username password)}, | ||
| 67 | $id, $sender->ID, $callback, $query, @$args); | ||
| 68 | }, | ||
| 69 | } | ||
| 70 | ); | ||
| 71 | |||
| 72 | return $self; | ||
| 73 | } | ||
| 74 | |||
| 75 | # You can call this method...It's just for convenience. | ||
| 76 | sub query { | ||
| 77 | my ($self, %args) = @_; | ||
| 78 | |||
| 79 | DEBUG && print "->query(): dispatching event: \"query\""; | ||
| 80 | |||
| 81 | # We post from here so we can enter the session context of the component. | ||
| 82 | $poe_kernel->post($self->{session}->ID, query => @args{qw( callback | ||
| 83 | userdata query params)}); | ||
| 84 | |||
| 85 | # Keep the calling session alive until this finished. | ||
| 86 | # XXX Possible room for optimization. | ||
| 87 | $poe_kernel->refcount_increment( | ||
| 88 | $poe_kernel->get_active_session->ID, REFCOUNT_IDENTIFIER | ||
| 89 | ); | ||
| 90 | } | ||
| 91 | |||
| 92 | # Do will run a query without fetching records, use this for inserts. | ||
| 93 | sub do { | ||
| 94 | my ($self, %args) = @_; | ||
| 95 | |||
| 96 | DEBUG && print "->do(): dispatching event: \"do\""; | ||
| 97 | |||
| 98 | $poe_kernel->post($self->{session}->ID, do => @args{qw( callback userdata | ||
| 99 | query params)}); | ||
| 100 | |||
| 101 | # XXX Possible room for optimization. | ||
| 102 | $poe_kernel->refcount_increment( | ||
| 103 | $poe_kernel->get_active_session->ID, REFCOUNT_IDENTIFIER | ||
| 104 | ); | ||
| 105 | } | ||
| 106 | |||
| 107 | sub shutdown { | ||
| 108 | my ($self) = @_; | ||
| 109 | |||
| 110 | # Simply piggyback off of PoCo:P:T | ||
| 111 | $poe_kernel->post($self->{session}->ID, "shutdown"); | ||
| 112 | } | ||
| 113 | |||
| 114 | # This thread entry point (the job of our thread pool) simply creates a | ||
| 115 | # connection if it doesn't already have one that's good, and then it runs the | ||
| 116 | # query supplied. It will | ||
| 117 | sub query_database { | ||
| 118 | DEBUG && warn "thread entry point"; | ||
| 119 | |||
| 120 | # I pass the DSN info in every time, because otherwise it won't be | ||
| 121 | # available in the entry point. This method has not access to the parent | ||
| 122 | # object. (perl threads are strange that way). | ||
| 123 | my ($action, $dsn, $user, $pass, $id, $caller, $cb, $query, @args) = @_; | ||
| 124 | |||
| 125 | my @response = eval { | ||
| 126 | DEBUG && warn "testing & creating database connection"; | ||
| 127 | |||
| 128 | # "our" is a shared variable, but it's only shared within each thread | ||
| 129 | # (assuming that crunch_data is never called outside a thread). This | ||
| 130 | # statement will call DBI->connect if, and only if, the $dbh is not defined | ||
| 131 | # and you cannot call $dbh->ping if it is defined. | ||
| 132 | # | ||
| 133 | # I do this rather than connect_cached, just incase any initialized | ||
| 134 | # connections were made before the ithread was created. This mechanism | ||
| 135 | # provides a bit more safety in terms of ensuring that we get a unique | ||
| 136 | # connection per thread. | ||
| 137 | our $dbh = DBI->connect($dsn, $user, $pass) | ||
| 138 | unless defined $dbh && $dbh->ping; | ||
| 139 | |||
| 140 | DEBUG && warn "preparing query"; | ||
| 141 | # prepare_cached should be safe, since we should have a unique connection | ||
| 142 | # per our previous statement, and my tests showed this optimizes querying | ||
| 143 | # by up to 8x over creating a new prepared statement each time. | ||
| 144 | my $sth = $dbh->prepare_cached($query); | ||
| 145 | |||
| 146 | DEBUG && warn "executing query"; | ||
| 147 | $sth->execute(@args); | ||
| 148 | |||
| 149 | if ($action eq "do") { | ||
| 150 | $sth->finish; | ||
| 151 | return $id, $caller, $cb; | ||
| 152 | } | ||
| 153 | elsif ($action eq "query") { | ||
| 154 | DEBUG && warn "fetching results"; | ||
| 155 | my $results = $sth->fetchall_arrayref({}); | ||
| 156 | my @response; | ||
| 157 | |||
| 158 | for my $record (@$results) { | ||
| 159 | # Create a shared hashref. | ||
| 160 | my $r = &share({}); | ||
| 161 | |||
| 162 | # Copy all values into the hashref. | ||
| 163 | @$r{keys %$record} = values %$record; | ||
| 164 | |||
| 165 | # Put the shared hashref into the response | ||
| 166 | push @response, $r; | ||
| 167 | } | ||
| 168 | |||
| 169 | DEBUG && warn "returnning to caller"; | ||
| 170 | |||
| 171 | $sth->finish; | ||
| 172 | |||
| 173 | # This is automatically placed in a shared array, as per | ||
| 174 | # PoCo::Thread::Pool | ||
| 175 | return @response; | ||
| 176 | } | ||
| 177 | }; | ||
| 178 | if ($@) { | ||
| 179 | warn "An unexpected error was trapped while trying to run a query: $@"; | ||
| 180 | warn "The query was: $query"; | ||
| 181 | } | ||
| 182 | |||
| 183 | return $id, $caller, $cb, @response; | ||
| 184 | } | ||
| 185 | |||
| 186 | # This component will send a message to the caller session, with the data | ||
| 187 | # returned from the query as it's argument. | ||
| 188 | sub send_response { | ||
| 189 | my ($kernel, $heap, $id, $caller, $callback, @results) = | ||
| 190 | @_[ KERNEL, HEAP, ARG0 .. $#_ ]; | ||
| 191 | |||
| 192 | # Pass the results along as ARG0, and any user data as ARG0. delete | ||
| 193 | # removes it from the heap at the same time so we don't leap memory. | ||
| 194 | my $userdata = delete $heap->{data}{$id}; | ||
| 195 | |||
| 196 | if (defined $callback) { | ||
| 197 | if (DEBUG) { | ||
| 198 | use Data::Dumper; | ||
| 199 | |||
| 200 | print "dispatching response ($caller, $callback)", | ||
| 201 | Dumper(\@results); | ||
| 202 | } | ||
| 203 | |||
| 204 | $kernel->post($caller, $callback, \@results, $userdata); | ||
| 205 | } | ||
| 206 | |||
| 207 | # The pending state->post (if applicable) will keep the calling session | ||
| 208 | # alive, so we can release our reference here. | ||
| 209 | $kernel->refcount_decrement($caller, REFCOUNT_IDENTIFIER); | ||
| 210 | } | ||
| 211 | |||
| 212 | # All perl modules must return a true value. | ||
| 213 | 1; | ||
| 214 | |||
| 215 | =head1 NAME | ||
| 216 | |||
| 217 | POE::Component::Pool::DBI - Simplified DBI access through a pooled resource. | ||
| 218 | |||
| 219 | =head1 SYNOPSIS | ||
| 220 | |||
| 221 | use POE qw( Component::Pool::DBI ); | ||
| 222 | |||
| 223 | POE::Session->create( | ||
| 224 | inline_states => { | ||
| 225 | _start => sub { | ||
| 226 | my ($kernel, $heap) = @_[ KERNEL, HEAP ]; | ||
| 227 | |||
| 228 | my $dbpool = POE::Component::Pool::DBI->new( | ||
| 229 | connections => 10, | ||
| 230 | dsn => "DBI:mysql:database=test", | ||
| 231 | username => "username", | ||
| 232 | password => "password" | ||
| 233 | ); | ||
| 234 | |||
| 235 | # Outstanding queries keep the calling session alive. | ||
| 236 | $dbpool->query( | ||
| 237 | callback => "handle_result", | ||
| 238 | query => "select foo from bar where foo = ?", | ||
| 239 | params => [ "foo" ], | ||
| 240 | userdata => "example" | ||
| 241 | ); | ||
| 242 | |||
| 243 | $heap->{dbpool} = $dbpool; | ||
| 244 | }, | ||
| 245 | |||
| 246 | handle_result => sub { | ||
| 247 | my ($kernel, $heap, $results, $userdata) = | ||
| 248 | @_[ KERNEL, HEAP, ARG0, ARG1 ]; | ||
| 249 | |||
| 250 | # Will be an arrayref of hashrefs. | ||
| 251 | for my $record (@$results) { | ||
| 252 | print $record->{foo}; | ||
| 253 | } | ||
| 254 | |||
| 255 | my $dbpool = $heap->{dbpool}; | ||
| 256 | |||
| 257 | # Queries which do not return data should use the do method. | ||
| 258 | # If no callback is supplied, no callback happens. This is | ||
| 259 | # suitable for queries where the result is not necessarily | ||
| 260 | # important. | ||
| 261 | $dbpool->do( | ||
| 262 | query => "INSERT INTO results (query, count) VALUES (?,?)", | ||
| 263 | args => [ $udata, @$results ], | ||
| 264 | ); | ||
| 265 | |||
| 266 | # Ask for a clean shutdown. | ||
| 267 | $dbpool->shutdown; | ||
| 268 | }, | ||
| 269 | }, | ||
| 270 | ); | ||
| 271 | |||
| 272 | POE::Kernel->run(); | ||
| 273 | |||
| 274 | =head1 DESCRIPTION | ||
| 275 | |||
| 276 | This component provides a threadpool-backed DBI connection pool. It's fairly | ||
| 277 | well optimized for high throughput (particularly insert) servers which rely on | ||
| 278 | a relational database. It enables pooled connectivity by being implemented | ||
| 279 | upon a limited-availability thread pool, with each thread maintaining a | ||
| 280 | connection. It uses an asyncronous queue for allowing excess queries to 'stack | ||
| 281 | up', in order to enable availability. | ||
| 282 | |||
| 283 | |||
| 284 | =head1 RATIONALE | ||
| 285 | |||
| 286 | Why yet *another* DBI interface for POE? There are already about 6. But in | ||
| 287 | looking for a solution for a high availability UDP server I was unable to find | ||
| 288 | one which managed pooling effectively, and I always thought this would be a | ||
| 289 | reasonable application of the partner component, POE::Component::Pool::Thread. | ||
| 290 | |||
| 291 | =head1 CONSTRUCTORS | ||
| 292 | |||
| 293 | =over 4 | ||
| 294 | |||
| 295 | =item new LOTS_OF_THINGS | ||
| 296 | |||
| 297 | =over 4 | ||
| 298 | |||
| 299 | =item connections | ||
| 300 | |||
| 301 | The size, in number of threads and connections, of the resource pool. | ||
| 302 | |||
| 303 | =item dsn | ||
| 304 | |||
| 305 | The database service name, as per the DBI->connect method. | ||
| 306 | |||
| 307 | =item username | ||
| 308 | |||
| 309 | The username on the database (L<DBI>->connect). | ||
| 310 | |||
| 311 | =item password | ||
| 312 | |||
| 313 | The password on the database (L<DBI>->connect). | ||
| 314 | |||
| 315 | =back | ||
| 316 | |||
| 317 | =back | ||
| 318 | |||
| 319 | =head1 METHODS | ||
| 320 | |||
| 321 | =over 4 | ||
| 322 | |||
| 323 | =item query ARGUMENTS | ||
| 324 | |||
| 325 | The query method enqueues a query to be run by the job pool. The preparation | ||
| 326 | of the query will be cached, so it's suggested you use placeholders to ensure | ||
| 327 | the fastest response times and to avoid leaking statements. | ||
| 328 | |||
| 329 | =over 4 | ||
| 330 | |||
| 331 | =item query | ||
| 332 | |||
| 333 | The query argument holds the SQL or PL/SQL statement to execute. The statement | ||
| 334 | will be invoked immediately, but it will be cached within the connection | ||
| 335 | (see L<DBI> prepare_cached). | ||
| 336 | |||
| 337 | =item params | ||
| 338 | |||
| 339 | The arguments to provide to DBI's execute method (see L<DBI>). | ||
| 340 | |||
| 341 | =item callback | ||
| 342 | |||
| 343 | The state to invoke wihen the operation is complete. The state will be provided | ||
| 344 | with the results of the query (if applicable), as an array of hashref | ||
| 345 | (faciliated by DBI's fetchall_arrayref({})) assuming your driver supports this | ||
| 346 | functionality (most do). | ||
| 347 | |||
| 348 | =item userdata | ||
| 349 | |||
| 350 | Assuming a callback has been provided, this data will be provided to the | ||
| 351 | callback. This data is cached within the controlling session, and is not | ||
| 352 | "available" from inside the job threads. It needs not be shared in any way. | ||
| 353 | |||
| 354 | =back | ||
| 355 | |||
| 356 | =item do ARGUMENTS | ||
| 357 | |||
| 358 | The do operation will invoke a query, but forgo attempting to fetch any results | ||
| 359 | from the query. This method should be used for statements which do not return | ||
| 360 | result sets, such as INSERT or UPDATE statements on most databases. This | ||
| 361 | operation accepts the same arguments as the query method, which are documented | ||
| 362 | above. | ||
| 363 | |||
| 364 | =item shutdown | ||
| 365 | |||
| 366 | The shutdown operation piggybacks off of POE::Component::Pool::Thread (the | ||
| 367 | PoCo:P:T session is used as the management session for this component). It | ||
| 368 | simply asks all the threads to shut down. | ||
| 369 | |||
| 370 | =back | ||
| 371 | |||
| 372 | =head1 BUGS | ||
| 373 | |||
| 374 | =over | ||
| 375 | |||
| 376 | =item * | ||
| 377 | |||
| 378 | This component intentionally doesn't allow fine graned control over prepared | ||
| 379 | statement objects. | ||
| 380 | |||
| 381 | =item * | ||
| 382 | |||
| 383 | Some types of data sharing in ithreads have been known to leak, | ||
| 384 | |||
| 385 | =item * | ||
| 386 | |||
| 387 | My tests have shown this component, as well as POE::Component::Pool::Thread, | ||
| 388 | does not work with the forks pragma. | ||
| 389 | |||
| 390 | =item * | ||
| 391 | |||
| 392 | This module doesn't particularly support transactional operations. | ||
| 393 | |||
| 394 | =back | ||
| 395 | |||
| 396 | =head1 AUTHOR | ||
| 397 | |||
| 398 | Scott S. McCoy (tag@cpan.org) | ||