Batch.pm untested

filter: Statementstmt 0.0% 0 / 26 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 0.0% 0 / 7 Podpod 0.0% 0 / 1 totaltotal 0.0% 0 / 34 CC 1 SCAR 6.9
CC
1
Cov
0%
new
2.0
BEGIN
2.0
BEGIN
2.0
CRAP 2.0
1 package DB::Batch;
2 # Encapsulate DB::Batch components
3 use strict;
4 use warnings;
5  
6 use DB::Batch::Main;
7 use DB::Batch::Fetch;
8 use DB::Batch::Do;
9  
10 use base qw(DB::Batch::Main);
11  
12 our $VERSION = '0.11';
13  
14 # put together the pieces that make a DB::Batch object
15 sub new {
16 my $class = shift;
17 my $self = bless DB::Batch::Main->new(@_), $class;
18  
19 $self->{fetcher} = DB::Batch::Fetch->new();
20 $self->{doer} = DB::Batch::Do->new();
21 $self->{autothrottler} = undef;
22 $self->{loadbalancer} = undef;
23  
24 return $self;
25 }
26  
27 1;
28  
29 =head1 NAME
30  
31 DB::Batch - Run database queries in batches through DBI
32  
33 =head1 SYNOPSIS
34  
35 DB::Batch is a system for breaking up queries into batches behind the scenes,
36 while maintaining the appearance of running a single query.
37 Queries are run against an instance of DBI.
38  
39 For example, a query like:
40  
41 SELECT * FROM TABLE
42  
43 is expressed as:
44  
45 SELECT * FROM TABLE WHERE id BETWEEN # AND #
46  
47 and would be executed as:
48  
49 SELECT * FROM TABLE WHERE id BETWEEN 1 AND 100
50 SELECT * FROM TABLE WHERE id BETWEEN 101 AND 200
51 SELECT * FROM TABLE WHERE id BETWEEN 201 AND 300
52 ...
53  
54 Until the MAX(id) is reached.
55  
56 The script running this query, however, acts as if you are just
57 running 'SELECT * FROM TABLE'.
58  
59 The purpose of this is to diminish load on a sql database when large amounts of
60 data need to be retrieved by managing batch size and throttling.
61
62  
63 =head1 USAGE
64  
65 =over
66  
67 =item Divide a query over a primary key.
68  
69 In this example, we use a BETWEEN clause with two '#' placeholders.
70 During execution the values in these placeholders are automatically incremented in chunks of 1000 (based on the batch size sent in)
71 The execution starts at the given start and end values, which can be defined as integers or sql statements
72  
73 calling $batch->fetchrow_array, fetchrow_hashref, or fetchrow_array will return all rows from the table,
74 however in the background it will be running separate queries for each batch
75  
76 use DB::Batch;
77 use DBI;
78
79 my $dbh = DBI->connect(...);
80 my $batch = DB::Batch->new(dbh => $dbh);
81
82 my $sql = 'SELECT id,col FROM table WHERE id BETWEEN # AND #';
83 my %db_args = (
84 start => 1,
85 end => 'SELECT MAX(id) FROM table',
86 batch => 1000,
87 sleep => 1,
88 );
89
90 # this will retrieve all rows from table
91 while (my ($id,$col) = $batch->fetchrow_array($sql,\%db_args) {
92 print "$id,$col \n";
93 }
94
95  
96 =item Divide a query using limits and offsets
97  
98 In this example, DB::Batch will automatically increment our values for LIMIT and OFFSET
99 behind the scenes until 1000 rows have been returned.
100 (Note: this method is not optimal for very large data sets)
101  
102  
103 use DB::Batch;
104 use DBI;
105
106 my $dbh = DBI->connect(...);
107 my $batch = DB::Batch->new(dbh => $dbh);
108  
109 my $sql = 'SELECT id,col FROM table WHERE id LIMIT # OFFSET #';
110  
111 # you can also use the form:
112 # my $sql = 'SELECT id,col FROM table WHERE id LIMIT #,#';
113  
114 my %db_args = (
115 start => 1,
116 limit => 1000,
117 batch => 100,
118 sleep => 1,
119 );
120  
121 # this will retrieve all rows from table
122 while (my ($id,$col) = $batch->fetchrow_array($sql,\%db_args) {
123 print "$id,$col \n";
124 }
125
126  
127  
128  
129 =item Selecting with a list of arguments.
130  
131 In this example we provide a list of arguments and tell DB::Batch to bind 10 values at a time.
132 The '#' will be expanded to the number of placeholders specified by makebinds, or up to as many placeholders
133 that are needed to bind all the values of the given list.
134  
135 use DB::Batch;
136 use DBI;
137
138 my $dbh = DBI->connect(...);
139 my $batch = DB::Batch->new(dbh => $dbh);
140  
141 my $sql = 'SELECT id,col FROM table WHERE id IN (#)';
142  
143  
144 my %db_args = (
145 list => [qw(1..105)],
146 makebinds => 10,
147 );
148  
149 # this will retrieve all rows from table
150 while (my ($id,$col) = $batch->fetchrow_array($sql,\%db_args) {
151 print "$id,$col \n";
152 }
153  
154  
155 =item Inserting.
156  
157 Inserting can be done in batches using do_batch.
158 In this example, 10 values from the given list will be binded and executed at a time behind the scenes.
159  
160 use DB::Batch;
161 use DBI;
162
163 my $dbh = DBI->connect(...);
164 my $batch = DB::Batch->new(dbh => $dbh);
165  
166 my $sql = "UPDATE foo SET col='abc' WHERE id IN (#)";
167  
168 my %db_args = (
169 list => [qw(1..105)],
170 makebinds => 10,
171 );
172  
173 # this will retrieve all rows from table
174 $batch->do_batch($sql,\%db_args);
175  
176  
177  
178 =item Inserting II
179  
180 inserting multiple rows at once can be done by specifying the number of groups and number of placeholders
181 with makebinds
182  
183 use DB::Batch;
184 use DBI;
185
186 my $dbh = DBI->connect(...);
187 my $batch = DB::Batch->new(dbh => $dbh);
188  
189 my $sql = 'INSERT INTO foo (col1,col2) VALUES #';
190  
191 my %db_args = (
192 list => [qw(1 col2value2 2 col2value2 3 col2value3)],
193 makebinds => 2, #
194 groups => 3, # make 3 groups of 2 placeholders
195 );
196  
197 # this will retrieve all rows from table
198 $batch->do_batch($sql,\%db_args);
199  
200  
201 =item Inserting III
202  
203 If the flow of control of your script requires you to iterate over a large amount of data,
204 use buffer_batch to buffer a number of rows of data and then execute it all at once in the end
205  
206  
207 use DB::Batch;
208 use DBI;
209
210 my $dbh = DBI->connect(...);
211 my $batch = DB::Batch->new(dbh => $dbh);
212  
213 my $sql = 'INSERT INTO foo (col1,col2) VALUES #';
214  
215 my @data = (1..10000);
216  
217 my %db_args = (
218 makebinds => 2, #
219 groups => 3, # make 3 groups of 2 placeholders
220 );
221  
222 for my $col1 (@data) {
223 my $col2 = $col1;
224 $batch->buffer_batch($sql,$col1,$col2);
225 }
226  
227 $batch->exec_buffer($sql,\%db_args);
228  
229  
230 Hooks
231  
232  
233 use DB::Batch;
234 use DBI;
235
236 my $dbh = DBI->connect(...);
237 my $batch = DB::Batch->new(dbh => $dbh);
238  
239 my $sql = 'SELECT id,col FROM table WHERE id BETWEEN # AND #';
240 my $insert = 'INSERT INTO foo (col1,col2) VALUES #';
241  
242  
243 my %db_args = (
244 start => 1,
245 end => 'SELECT MAX(id) FROM table',
246 batch => 1000,
247 sleep => 1,
248 pre_hook => sub {
249 # this is executed before the query is run through $dbh->prepare
250 print Dumper [ $batch->get_last_batch() ]; # returns values about to be bound
251 },
252 post_hook => sub {
253 # This will run the INSERT after each batch of the SELECT query is finished,
254 # this way the entire table worth of data doesn't need to be kept in memory
255 # and written all at once
256 $batch->exec_buffer($insert,{makebinds => 2, groups => 10});
257 }
258 );
259  
260  
261 # this will retrieve all rows from table
262 while (my ($id,$col) = $batch->fetchrow_array($sql,\%db_args) {
263  
264 # returns true if a new batch was started behind the scenes
265 if ($batch->is_new_batch()) {
266 print Dumper [ $batch->get_last_batch() ]; # returns current values being bound
267 }
268  
269 # you can buffer results from one query to insert in batches to another table
270 $batch->buffer_batch($insert,$id,$col);
271 }
272  
273  
274  
275  
276  
277 =back
278  
279 =head1 PUBLIC METHODS
280  
281 =over
282  
283 =item new (dbh => DBI::db)
284  
285 =item fetchrow_array ($SQL_STRING, {ARGS})
286  
287 =item fetchrow_arrayref ($SQL_STRING, {ARGS})
288  
289 =item fetchrow_hashref ($SQL_STRING, {ARGS})
290  
291 -- start at id 0, and run the query in increments of 100 up to 1000.
292 SQL_STRING: 'SELECT foo FROM table WHERE id BETWEEN # AND #'
293 ARGS: {
294 start => 0
295 end => 1000,
296 batch => 100
297 sleep => 100000 # number of microseconds to wait between queries
298 pre_hook => sub {
299 # do something prior to current sth being prepared
300 },
301 post_hook => sub {
302 # do something after current sth is finished
303 }
304 }
305
306 -- determine start and end values at runtime based on the results of the given queries
307 ARGS: {
308 start => 'SELECT MIN(id) FROM table'
309 end => 'SELECT MAX(id) FROM table',
310 batch => 100
311 }
312
313
314 -- expand the placeholder into 10 placeholders and bind the values of 'list' 10 at a time
315 SQL_STRING: 'SELECT foo FROM table WHERE id IN (#)'
316 ARGS: {
317 makebinds => 10,
318 list => [qw(2 4 6 8 10 12 14 16 18 20)],
319 }
320  
321  
322 =item buffer_batch ($SQL_STRING,@LIST)
323
324 buffer the values in @LIST to be bound and run when exec_buffer is called on the same SQL_STRING
325  
326 =item exec_buffer($SQL_STRING,\%ARGS)
327  
328 run do_batch after having buffered a list of bind values using buffer_batch();
329  
330 -- args are identical to do_batch, except 'list' was build internally when buffer_batch was called
331 SQL_STRING: 'INSERT INTO table VALUES #'
332 ARGS: {
333 makebinds => 10,
334 }
335  
336 =item buffer_batch_triggered ($SQL_STRING,\%ARGS,@LIST)
337
338 buffer the values in @LIST to be bound and run when exec_buffer is called on the same SQL_STRING
339 when the number of rows of data matches $ARGS->{trigger}, the insert is executed automatically
340  
341 =item do_batch
342  
343 execute write queries in batches
344  
345 SQL_STRING: 'INSERT INTO table VALUES #'
346 ARGS: {
347 makebinds => 3,
348 groups => 2
349 }
350  
351 =item get_dbh
352  
353 return the primary db handle being used
354  
355 =item get_last_batch
356  
357 return a list of the last batch values bound to the current query
358  
359 =item get_last_query
360  
361 return a string with the full query that was last executed.
362  
363 =item get_sth
364  
365 call this from inside a while($batch->fetch...) { } loop. Return the statement handle currently being processed
366  
367 =item is_new_batch
368  
369 call this from inside a while($batch->fetch...) { } loop. Returns 1 if a new query batch was executed.
370  
371  
372 =back
373  
374 =head1 COPYRIGHT & LICENSE
375  
376 Copyright 2010, Chris Becker C<< <clbecker@gmail.com> >>
377  
378 Original work sponsered by Shutterstock, LLC.
379 L<http://shutterstock.com>
380  
381 This program is free software; you can redistribute it and/or modify
382 it under the same terms as Perl itself.
383  
384 =cut