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