blib/lib/POE/Component/Pool/DBI.pm

filter: Statementstmt 100% 9 / 9 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 3 / 3 Podpod n/a totaltotal 100% 12 / 12 CC 1 SCAR 0.0
CC
1
Cov
100%
BEGIN
1.0
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
↑ Summary
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)
↑ Summary