| 1 | package DB::Batch::Main; | ||
| 2 | # Manage overall flow of control of DB::Batch | ||
| 3 | # | ||
| 4 | # Copyright 2010, Chris Becker <clbecker@gmail.com> | ||
| 5 | # | ||
| 6 | # Original work sponsered by Shutterstock, LLC. http://shutterstock.com | ||
| 7 | # | ||
| 8 | # This program is free software; you can redistribute it and/or modify | ||
| 9 | # it under the same terms as Perl itself. | ||
| 10 | |||
| 11 | use strict; | ||
| 12 | use warnings; | ||
| 13 | |||
| 14 | use Digest::MD5 qw(md5_hex); | ||
| 15 | use DB::Batch::Query; | ||
| 16 | use DB::Batch::BindValues; | ||
| 17 | use DB::Batch::Fetch; | ||
| 18 | use DB::Batch::Do; | ||
| 19 | |||
| 20 | # args: | ||
| 21 | # dbh => provide your own DB instance | ||
| 22 | sub new { | ||
| 23 | my $class = shift; | ||
| 24 | my %args = @_; | ||
| 25 | |||
| 26 | die 'You need to provide a database handle' unless $args{dbh}; | ||
| 27 | |||
| 28 | my $self = bless \%args, $class; | ||
| 29 | |||
| 30 | # set modules to process fetching, executing, and writing data | ||
| 31 | # other variants of these will allow separate scripts to split batches and run in parallel | ||
| 32 | $self->{fetcher} ||= DB::Batch::Fetch->new(); | ||
| 33 | $self->{doer} ||= DB::Batch::Do->new(); | ||
| 34 | |||
| 35 | # TODO: encapsulate autothrottling to DB::Batch::Autothrottler | ||
| 36 | # if set this will allow throttling to be determined automatically based on replication times and server load and such | ||
| 37 | $self->{autothrottler} ||= undef; | ||
| 38 | |||
| 39 | # TODO: write DB::Batch::LoadBalancer plugin | ||
| 40 | # - provide a list of db handles to it | ||
| 41 | # - it'll return the right one to use | ||
| 42 | $self->{loadbalancer} ||= undef; # DB::Batch::LoadBalancer->new($self->{dbh}); | ||
| 43 | return $self; | ||
| 44 | } | ||
| 45 | |||
| 46 | |||
| 47 | # send a custom subroutine in to process the sth manually | ||
| 48 | sub fetchbatch_custom { | ||
| 49 | my $self = shift; | ||
| 50 | die 'batch subroutine not defined' unless (defined $_[1]{sub}); | ||
| 51 | return $self->_fetch(@_); | ||
| 52 | } | ||
| 53 | |||
| 54 | # acts like DBI::sth counterparts | ||
| 55 | sub fetchrow_hashref { | ||
| 56 | my $self = shift; | ||
| 57 | return $self->_fetch(@_); | ||
| 58 | } | ||
| 59 | |||
| 60 | sub fetchrow_arrayref { | ||
| 61 | my $self = shift; | ||
| 62 | return $self->_fetch(@_); | ||
| 63 | } | ||
| 64 | |||
| 65 | sub fetchrow_array { | ||
| 66 | my $self = shift; | ||
| 67 | return $self->_fetch(@_); | ||
| 68 | } | ||
| 69 | |||
| 70 | |||
| 71 | ## | ||
| 72 | # $query: sql select statement | ||
| 73 | # $opts: hash of options (see options section in pod below) | ||
| 74 | # @_: arguments passed to execute() | ||
| 75 | sub _fetch { | ||
| 76 | my $self = shift; | ||
| 77 | my $query = shift; | ||
| 78 | my $opts = $self->vivifyopts(shift); | ||
| 79 | |||
| 80 | die 'query not defined' unless ($query); | ||
| 81 | |||
| 82 | my $caller = (caller(1))[3]; # get the calling subroutine (e.g. .._hashref, .._arrayref, .._array) | ||
| 83 | my $dbh = $self->{dbh}; | ||
| 84 | my $query_obj = $self->get_query_object($query,$opts); | ||
| 85 | my $bind_obj = $self->get_bindvalue_object($query_obj,$opts); | ||
| 86 | my $key = $query_obj->get_key(); # get hash key of query w/ placeholders | ||
| 87 | |||
| 88 | $self->{sth}{$key} = undef if ($caller =~ m/fetchbatch_custom$/); # sth is dealt with in custom sub for fetchbatch_custom() | ||
| 89 | |||
| 90 | # run query with incremented values. If nothing is returned, then return undef or () to finish | ||
| 91 | unless ($self->_execute_batch_read( | ||
| 92 | query => $query_obj, | ||
| 93 | opts => $opts, | ||
| 94 | bindvalues => $bind_obj, | ||
| 95 | dbi_bind => [@_])) { | ||
| 96 | return wantarray ? () : undef | ||
| 97 | } | ||
| 98 | |||
| 99 | # process the sth behind the scenes | ||
| 100 | return $self->_process_sth( | ||
| 101 | query => $query_obj, | ||
| 102 | opts => $opts, | ||
| 103 | dbi_bind => [@_], | ||
| 104 | caller => $caller, | ||
| 105 | ); | ||
| 106 | } | ||
| 107 | |||
| 108 | |||
| 109 | # execute query with current range of data | ||
| 110 | sub _execute_batch_read { | ||
| 111 | my $self = shift; | ||
| 112 | $self->{fetcher}->execute_batch_read(parent => $self, @_); | ||
| 113 | } | ||
| 114 | |||
| 115 | |||
| 116 | # process the executed sth | ||
| 117 | sub _process_sth { | ||
| 118 | my $self = shift; | ||
| 119 | $self->{fetcher}->process_sth(parent => $self, @_); | ||
| 120 | } | ||
| 121 | |||
| 122 | |||
| 123 | ## | ||
| 124 | # $query: sql statement (e.g. insert/update) | ||
| 125 | # $opts: hash of options (see options section in pod below) | ||
| 126 | # @_: arguments passed to execute() | ||
| 127 | sub do_batch { | ||
| 128 | my $self = shift; | ||
| 129 | my $query_str = shift; | ||
| 130 | my $opts = $self->vivifyopts(shift); | ||
| 131 | |||
| 132 | $self->{doer}->do_batch(parent => $self, query => $query_str, opts => $opts, dbi_binds => [@_]); | ||
| 133 | } | ||
| 134 | |||
| 135 | # | ||
| 136 | # add list of bind variables to a buffer for later execution in batches | ||
| 137 | sub buffer_batch { | ||
| 138 | my $self = shift; | ||
| 139 | my $query = shift; | ||
| 140 | die 'query not defined' unless ($query); | ||
| 141 | my $key = md5_hex($query); | ||
| 142 | push @{$self->{buffer}{$key}}, @_; | ||
| 143 | } | ||
| 144 | |||
| 145 | # execute query with buffered list of bind variables | ||
| 146 | sub exec_buffer { | ||
| 147 | my $self = shift; | ||
| 148 | my $query = shift; | ||
| 149 | my $opts = shift; | ||
| 150 | |||
| 151 | die 'query not defined' unless ($query); | ||
| 152 | my $key = md5_hex($query); | ||
| 153 | return unless ($self->{buffer}{$key} && @{$self->{buffer}{$key}}); | ||
| 154 | |||
| 155 | $opts->{list} = $self->{buffer}{$key}; | ||
| 156 | $self->do_batch($query,$opts,@_); | ||
| 157 | undef @{$self->{buffer}{$key}}; | ||
| 158 | } | ||
| 159 | |||
| 160 | # determine throttling dynamically based on server stats | ||
| 161 | sub auto_throttle { | ||
| 162 | my $self = shift; | ||
| 163 | return unless $self->{autothrottler}; | ||
| 164 | $self->{autothrottler}->throttle(); | ||
| 165 | } | ||
| 166 | |||
| 167 | # sleep for some amount of microseconds | ||
| 168 | sub throttle { | ||
| 169 | my $self = shift; | ||
| 170 | my $opts = shift; | ||
| 171 | sleep($self->{autothrottle_sleep} || $opts->{sleep}); | ||
| 172 | } | ||
| 173 | |||
| 174 | # initialize all possible options. | ||
| 175 | sub vivifyopts { | ||
| 176 | my $self = shift; | ||
| 177 | my $opts = shift; | ||
| 178 | |||
| 179 | # if a string is given for any field expecting an integer, | ||
| 180 | # presume its a query and store its result; | ||
| 181 | for my $field qw(limit start end batch sleep) { | ||
| 182 | |||
| 183 | if (defined $opts->{$field} && $opts->{$field} =~ m/\D+/) { | ||
| 184 | |||
| 185 | my ($result) = $self->{dbh}->selectrow_array($opts->{$field}); | ||
| 186 | $opts->{$field} = $result || 0; | ||
| 187 | $opts->{$field} += 1 if $field eq 'end'; # adjust for exclusiveness for end id | ||
| 188 | } | ||
| 189 | } | ||
| 190 | |||
| 191 | $opts->{log} ||= undef; # Log::Log4Perl object | ||
| 192 | $opts->{list} ||= undef; # array of values to bind in batches | ||
| 193 | $opts->{quote} ||= undef; # custom quote function (else use dbh->quote) | ||
| 194 | $opts->{verbose} ||= 0; | ||
| 195 | $opts->{batch} ||= 0; # range of values to retrieve in one query | ||
| 196 | $opts->{makebinds} ||= 0; | ||
| 197 | $opts->{groups} ||= 0; | ||
| 198 | $opts->{limit} ||= 0; # number of values to return before finishing | ||
| 199 | $opts->{start} ||= 0; # min value to start at | ||
| 200 | $opts->{sleep} ||= 0; # time (microseconds) to wait between executing | ||
| 201 | $opts->{end} ||= $opts->{start} + $opts->{limit}; # final value to return before exiting | ||
| 202 | $opts->{output} ||= undef; # file/output handle to redirect updates | ||
| 203 | $opts->{sub_args} ||= undef; # args sent to the sub called by fetchbatch_custom | ||
| 204 | $opts->{sub} ||= undef; # sub called by fetchbatch_custom | ||
| 205 | return $opts; | ||
| 206 | } | ||
| 207 | |||
| 208 | |||
| 209 | # call this in a while ...$this->fetchrow_* loop to get whether or not we just started the next query in the batch | ||
| 210 | sub is_new_batch { | ||
| 211 | my $self = shift; | ||
| 212 | return $self->{__new_batch__} || 0; | ||
| 213 | } | ||
| 214 | |||
| 215 | # return a list of the current batch of bound values | ||
| 216 | sub get_last_batch { | ||
| 217 | my $self = shift; | ||
| 218 | return ($self->{__bindvalues__}{ $self->{__last_query_key__} }->get_last_batch()); | ||
| 219 | } | ||
| 220 | |||
| 221 | # return a list of the current batch of bound values | ||
| 222 | sub get_sth { | ||
| 223 | my $self = shift; | ||
| 224 | return ($self->{sth}{ $self->{__last_query_key__} }->get_last_batch()); | ||
| 225 | } | ||
| 226 | |||
| 227 | |||
| 228 | # return the last executed query with batched bind variables bound | ||
| 229 | sub get_last_query { | ||
| 230 | my $self = shift; | ||
| 231 | return $self->{__last_query__}; | ||
| 232 | } | ||
| 233 | |||
| 234 | # create a Batch::Query object of the given query if one has not been created yet, otherwise return one stored in cache | ||
| 235 | sub get_query_object { | ||
| 236 | my $self = shift; | ||
| 237 | my $query = shift; | ||
| 238 | my $opts = shift; | ||
| 239 | my $key = md5_hex($query); | ||
| 240 | |||
| 241 | return $self->{__queries__}{$key} || ($self->{__queries__}{$key} = DB::Batch::Query->new(query => $query, %$opts)); | ||
| 242 | } | ||
| 243 | |||
| 244 | # create a Batch::BindValue object to handle argument increments and whatnot if one hasn't been created yet | ||
| 245 | sub get_bindvalue_object { | ||
| 246 | my $self = shift; | ||
| 247 | my $query = shift; | ||
| 248 | my $opts = shift; | ||
| 249 | my $key = $query->get_key(); | ||
| 250 | |||
| 251 | return $self->{__bindvalues__}{$key} || ($self->{__bindvalues__}{$key} = DB::Batch::BindValues->new(query => $query, dbh => $self->{dbh}, %$opts)); | ||
| 252 | } | ||
| 253 | |||
| 254 | # get the current db handle to use | ||
| 255 | sub get_dbh { | ||
| 256 | my $self = shift; | ||
| 257 | |||
| 258 | if ($self->{loadbalancer}) { | ||
| 259 | # TODO: write loadbalancer | ||
| 260 | } | ||
| 261 | |||
| 262 | return $self->{dbh}; | ||
| 263 | } | ||
| 264 | |||
| 265 | # remove cached data for a query | ||
| 266 | sub clear { | ||
| 267 | my $self = shift; | ||
| 268 | my $key = shift; | ||
| 269 | undef $self->{__queries__}{$key}; | ||
| 270 | undef $self->{__bindvalues__}{$key}; | ||
| 271 | undef $self->{sth}{$key}; | ||
| 272 | } | ||
| 273 | |||
| 274 | 1; |