1DBIAgent(3) User Contributed Perl Documentation DBIAgent(3)
2
6 POE::Component::DBIAgent - POE Component for running asynchronous DBI
7 calls.
8
10 sub _start {
11 my ($self, $kernel, $heap) = @_[OBJECT, KERNEL, HEAP];
12 $heap->{helper} = POE::Component::DBIAgent->new( DSN => [$dsn,
14 $username,
15 $password
16 ],
17 Queries => $self->make_queries,
18 Count => 3,
19 Debug => 1,
20 );
21 # Queries takes a hashref of the form:
23 # { query_name => 'select blah from table where x = ?',
24 # other_query => 'select blah_blah from big_view',
25 # etc.
26 # }
27 $heap->{helper}->query(query_name =>
29 { cookie => 'starting_query' },
30 session => 'get_row_from_dbiagent');
31 }
33 sub get_row_from_dbiagent {
35 my ($kernel, $self, $heap, $row, $cookie) = @_[KERNEL, OBJECT, HEAP, ARG0, ARG1];
36 if ($row ne 'EOF') {
37 # {{{ PROCESS A ROW
39 #row is a listref of columns
41 # }}} PROCESS A ROW
43 } else {
45 # {{{ NO MORE ROWS
47 #cleanup code here
49 # }}} NO MORE ROWS
51 }
53 }
55
57 DBIAgent is your answer to non-blocking DBI in POE.
58 It fires off a configurable number child processes (defaults to 3) and
60 feeds database queries to it via two-way pipe (or sockets ... however
61 POE::Component::Wheel::Run is able to manage it). The primary method
62 is "query".
63 Usage
65 After initializing a DBIAgent and storing it in a session's heap, one
67 executes a "query" (or "query_slow") with the query name, destination
68 session (name or id) and destination state (as well as any query param‐
69 eters, optionally) as arguments. As each row of data comes back from
70 the query, the destination state (in the destination session) is
71 invoked with that row of data in its $_[ARG0] slot. When there are no
72 more rows to return, the data in $_[ARG0] is the string 'EOF'.
73 Not EVERY query should run through the DBIAgent. If you need to run a
75 short lookup from within a state, sometimes it can be a hassle to have
76 to define a whole seperate state to receive its value, and resume pro‐
77 cessing from there.. The determining factor, of course, is how long
78 your query will take to execute. If you are trying to retrieve one row
79 from a properly indexed table, use "$dbh->selectrow_array()". If
80 there's a join involved, or multiple rows, or a view, you probably want
81 to use DBIAgent. If it's a longish query and startup costs (time)
82 don't matter to you, go ahead and do it inline.. but remember the whole
83 of your program suspends waiting for the result. If startup costs DO
84 matter, use DBIAgent.
85 Return Values
87 The destination state in the destination session (specified in the call
89 to "query()") will receive the return values from the query in its
90 $_[ARG0] parameter. DBIAgent invokes DBI's "fetch" method internally,
91 so the value will be a reference to an array. If your query returns
92 multiple rows, then your state will be invoked multiple times, once per
93 row. ADDITIONALLY, your state will be called one time with $_[ARG0]
94 containing the string 'EOF'. 'EOF' is returned even if the query
95 doesn't return any other rows. This is also what to expect for DML
96 (INSERT, UPDATE, DELETE) queries. A way to utilise this might be as
97 follows:
98 sub some_state {
100 #...
101 if ($enough_values_to_begin_updating) {
102 $heap->{dbiagent}->query(update_values_query =>
104 this_session =>
105 update_next_value =>
106 shift @{$heap->{values_to_be_updated}}
107 );
108 }
109 }
110 sub update_next_value {
112 my ($self, $heap) = @_[OBJECT, HEAP];
113 # we got 'EOF' in ARG0 here but we don't care... we know that an
114 # update has been executed.
115 for (1..3) { # Do three at a time!
117 my $value;
118 last unless defined ($value = shift @{$heap->{values_to_be_updated}});
119 $heap->{dbiagent}->query(update_values =>
120 this_session =>
121 update_next_value =>
122 $value
123 );
124 }
125 }
127 new()
129 Creating an instance creates a POE::Session to manage communication
131 with the Helper processes. Queue management is transparent and auto‐
132 matic. The constructor is named "new()" (surprised, eh? Yeah, me
133 too). The parameters are as follows:
134 DSN An arrayref of parameters to pass to DBI->connect (usually a dsn,
136 username, and password).
137 Queries
139 A hashref of the form Query_Name => "$SQL". For example:
140 {
142 sysdate => "select sysdate from dual",
143 employee_record => "select * from emp where id = ?",
144 increase_inventory => "update inventory
145 set count = count + ?
146 where item_id = ?",
147 }
148 As the example indicates, DBI placeholders are supported, as are
150 DML statements.
151 Count
153 The number of helper processes to spawn. Defaults to 3. The opti‐
154 mal value for this parameter will depend on several factors, such
155 as: how many different queries your program will be running, how
156 much RAM you have, how often you run queries, and most importantly,
157 how many queries you intend to run simultaneously.
158 ErrorState
160 An listref containing a session and event name to receive error
161 messages from the DBI. The message arrives in ARG0.
162 query($query_name, [ \%args, ] $session, $state, [ @parameters ])
164 The "query()" method takes at least three parameters, plus any bind
166 values for the specific query you are executing.
167 $query_name
169 This parameter must be one of the keys to the Queries hashref you
170 passed to the constructor. It is used to indicate which query you
171 wish to execute.
172 \%args
174 This is an OPTIONAL hashref of arguments to pass to the query.
175 Currently supported arguments:
177 hash
179 Return rows hash references instead of array references.
180 cookie
182 A cookie to pass to this query. This is passed back unchanged
183 to the destination state in $_[ARG1]. Can be any scalar
184 (including references, and even POE postbacks, so be careful!).
185 You can use this as an identifier if you have one destination
186 state handling multiple different queries or sessions.
187 delay
189 Insert a 1ms delay between each row of output.
190 I know what you're thinking: "WHY would you want to slow down
192 query responses?!?!?" It has to do with CONCURRENCY. When a
193 response (finally) comes in from the agent after running the
194 query, it floods the input channel with response data. This
195 has the effect of monopolizing POE's attention, so that any
196 other handles (network sockets, pipes, file descriptors) keep
197 getting pushed further back on the queue, and to all other pro‐
198 cesses EXCEPT the agent, your POE program looks hung for the
199 amount of time it takes to process all of the incoming query
200 data.
201 So, we insert 1ms of time via Time::HiRes's "usleep" function.
203 In human terms, this is essentially negligible. But it is just
204 enough time to allow competing handles (sockets, files) to
205 trigger "select()", and get handled by the POE::Kernel, in sit‐
206 uations where concurrency has priority over transfer rate.
207 Naturally, the Time::HiRes module is required for this func‐
209 tionality. If Time::HiRes is not installed, the delay is
210 ignored.
211 group
213 Sends the return event back when "group" rows are retrieved
214 from the database, to avoid event spam when selecting lots of
215 rows. NB: using group means that $row will be an arrayref of
216 rows, not just a single row.
217 $session, $state
219 These parameters indicate the POE state that is to receive the data
220 returned from the database. The state indicated will receive the
221 data in its $_[ARG0] parameter. PLEASE make sure this is a valid
222 state, otherwise you will spend a LOT of time banging your head
223 against the wall wondering where your query data is.
224 @parameters
226 These are any parameters your query requires. WARNING: You must
227 supply exactly as many parameters as your query has placeholders!
228 This means that if your query has NO placeholders, then you should
229 pass NO extra parameters to "query".
230 Suggestions to improve this syntax are welcome.
232 finish()
234 The "finish()" method tells DBIAgent that the program is finished send‐
236 ing queries. DBIAgent will shut its helpers down gracefully after they
237 complete any pending queries. If there are no pending queries, the
238 DBIAgent will shut down immediately.
239
241 · Error handling is practically non-existent.
242 · The calling syntax is still pretty weak... but improving. We may
244 eventually add an optional attributes hash so that each query can
245 be called with its own individual characteristics.
246 · I might eventually want to support returning hashrefs, if there is
248 any demand.
249 · Every query is prepared at Helper startup. This could potentially
251 be pretty expensive. Perhaps a cached or deferred loading might be
252 better? This is considering that not every helper is going to run
253 every query, especially if you have a lot of miscellaneous queries.
254 Suggestions welcome! Diffs more welcome! :-)
256
258 This module has been fine-tuned and packaged by Rob Bloodgood
259 <robb@empire2.com>. However, most of the queuing code originated with
260 Fletch <fletch@phydeaux.org>, either directly or via his ideas. Thank
261 you for making this module a reality, Fletch!
262 However, I own all of the bugs.
264 This module is free software; you may redistribute it and/or modify it
266 under the same terms as Perl itself.
267perl v5.8.8 2004-09-17 DBIAgent(3)