| 1 | package DB::Batch::BindValues; | ||
| 2 | # handle the batching of batched bind values | ||
| 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 Data::Dumper; | ||
| 15 | use POSIX qw(ceil); | ||
| 16 | use List::Util qw(min max); | ||
| 17 | |||
| 18 | # | ||
| 19 | # args: | ||
| 20 | # query => Batch::Query object | ||
| 21 | # bindvalues => [] | ||
| 22 | # dbh => database handle | ||
| 23 | # start => start incrementing ranged arguments at this value (i.e. for BETWEEN start AND end) | ||
| 24 | # end => stop incrementing ranged arguments at this value | ||
| 25 | # batch => number of items to batch at once | ||
| 26 | # list => | ||
| 27 | sub new { | ||
| 28 | my $class = shift; | ||
| 29 | my %args = @_; | ||
| 30 | my $self = bless \%args, $class; | ||
| 31 | |||
| 32 | # used to cache the current start value for ranged batches over multiple queries (e.g. between # and #) | ||
| 33 | $self->{increment} ||= {}; | ||
| 34 | $self->identify_range_args(); | ||
| 35 | |||
| 36 | return $self; | ||
| 37 | } | ||
| 38 | |||
| 39 | |||
| 40 | ## | ||
| 41 | # figure out what the next batch of bind values should be, and return them as a list | ||
| 42 | sub increment { | ||
| 43 | my $self = shift; | ||
| 44 | my $key = $self->{query}->get_key(); | ||
| 45 | |||
| 46 | # if we're following a previous batch, set the next start value to start + batch size | ||
| 47 | # if we're just starting, set the start of this increment to $self->{start} | ||
| 48 | if (exists $self->{increment}{$key} && defined $self->{increment}{$key}{start}) { | ||
| 49 | $self->{increment}{$key}{start} += $self->{batch}; | ||
| 50 | } else { | ||
| 51 | $self->{increment}{$key}{start} = $self->{start}; | ||
| 52 | } | ||
| 53 | |||
| 54 | # don't go past the specified end value | ||
| 55 | unless ($self->{list}) { | ||
| 56 | if ($self->{increment}{$key}{start} + $self->{batch} > $self->{end}) { | ||
| 57 | $self->{batch} = $self->{end} - $self->{increment}{$key}{start} + 1; | ||
| 58 | } | ||
| 59 | } | ||
| 60 | |||
| 61 | # if start reaches end, we're done | ||
| 62 | if ($self->{increment}{$key}{start} > $self->{end}) { | ||
| 63 | $self->{__current_batch_values__} = []; | ||
| 64 | return (); | ||
| 65 | } | ||
| 66 | |||
| 67 | # split list into batches to inject directly into query | ||
| 68 | if ($self->{list}) { | ||
| 69 | $self->{__current_batch_values__} = [ $self->set_list_args() ]; | ||
| 70 | } else { | ||
| 71 | # create start and end values to inject into between or limit/offset clause | ||
| 72 | $self->{__current_batch_values__} = [ $self->set_range_args() ]; | ||
| 73 | } | ||
| 74 | |||
| 75 | return @{$self->{__current_batch_values__}} | ||
| 76 | } | ||
| 77 | |||
| 78 | |||
| 79 | # identify which arguments in the query are start (offset), end (start + limit), or limit | ||
| 80 | # based on simple patterns | ||
| 81 | sub identify_range_args { | ||
| 82 | my $self = shift; | ||
| 83 | my $query = $self->{query}; | ||
| 84 | |||
| 85 | # depending on the query, placeholders may have different meanings | ||
| 86 | # i.e. ...where id between 100 and 200 | ||
| 87 | # ...limit 100,200 | ||
| 88 | # ...limit 200 offset 100 | ||
| 89 | if ($self->{list} && @{$self->{list}}) { | ||
| 90 | |||
| 91 | if ($self->{makebinds} && $self->{groups}) { | ||
| 92 | # e.g. (#,#),(#,#),(#,#); makebinds = 2, groups = 3, batch = 6 | ||
| 93 | $self->{batch} = $self->{makebinds} * $self->{groups}; | ||
| 94 | } elsif ($self->{makebinds}) { | ||
| 95 | # e.g. (#,#,#) makebinds = 3, batch = 3 | ||
| 96 | $self->{batch} = $self->{makebinds}; | ||
| 97 | } else { | ||
| 98 | # set batch count to total number of placeholders that the user manually put in the query string | ||
| 99 | $self->{batch} = $query->get_placeholder_count(); | ||
| 100 | my $size = scalar @{$self->{list}}; | ||
| 101 | if ($size % $self->{batch} != 0) { | ||
| 102 | die "number of arguments is not a multiple of the number of placeholders in this query"; | ||
| 103 | } | ||
| 104 | } | ||
| 105 | |||
| 106 | if (ref $self->{list}[0] eq 'ARRAY') { | ||
| 107 | # if given a list of lists of bind values, set the end value to the total number of items (i.e. x * y) | ||
| 108 | $self->{end} = scalar @{$self->{list}[0]} * scalar @{$self->{list}}; | ||
| 109 | } else { | ||
| 110 | # if given a list of bind values, set the end to the number of values in the list | ||
| 111 | $self->{end} = scalar @{$self->{list}}; | ||
| 112 | } | ||
| 113 | |||
| 114 | # these are only used if defining ranges, e.g. for 'between # and #' clauses | ||
| 115 | $self->{arg1} = undef; | ||
| 116 | $self->{arg2} = undef; | ||
| 117 | return; | ||
| 118 | } | ||
| 119 | |||
| 120 | if ($query->get_param_type() eq 'limit') { | ||
| 121 | # e.g. ...LIMIT offset,rowcount | ||
| 122 | $self->{arg1} = 'offset'; | ||
| 123 | $self->{arg2} = 'row_count'; | ||
| 124 | |||
| 125 | } elsif ($query->get_param_type() eq 'limit-offset') { | ||
| 126 | # e.g. ...LIMIT row_count OFFSET offset | ||
| 127 | $self->{arg1} = 'row_count'; | ||
| 128 | $self->{arg2} = 'offset'; | ||
| 129 | |||
| 130 | } elsif ($query->get_param_type() eq 'between') { | ||
| 131 | # e.g. ...BETWEEN offset AND end | ||
| 132 | $self->{arg1} = 'offset'; | ||
| 133 | $self->{arg2} = 'end'; | ||
| 134 | |||
| 135 | } elsif (!defined $self->{list}) { | ||
| 136 | # base start, end, limit on number of bind values entered | ||
| 137 | $self->{arg1} = 'offset' if (defined $self->{start}); | ||
| 138 | $self->{arg2} = 'row_count' if (defined $self->{limit}); | ||
| 139 | $self->{arg2} = 'end' if (defined $self->{end}); | ||
| 140 | die 'you need to define start, limit, and/or end;'."\n-- $query" unless (defined $self->{arg1} && defined $self->{arg2}); | ||
| 141 | |||
| 142 | } else { | ||
| 143 | die 'you need to define start, limit or end, or list'."\n-- $query"; | ||
| 144 | } | ||
| 145 | } | ||
| 146 | |||
| 147 | |||
| 148 | # return a batch of $opts->{list} | ||
| 149 | sub set_list_args { | ||
| 150 | my $self = shift; | ||
| 151 | my $key = $self->{query}->get_key(); | ||
| 152 | |||
| 153 | return undef unless @{$self->{list}}; | ||
| 154 | |||
| 155 | my @args; | ||
| 156 | # if list contains lists of bind values. shift one value off each list until all placeholders are filled | ||
| 157 | if (ref $self->{list}[0] eq 'ARRAY') { | ||
| 158 | my $index = 0; | ||
| 159 | |||
| 160 | # e.g. (#,#,#) | ||
| 161 | # list => [ [1,4], [2,5], [3,6] ] | ||
| 162 | # results in: | ||
| 163 | # (1,2,3) | ||
| 164 | # (4,5,6) | ||
| 165 | |||
| 166 | for my $i (1..$self->{batch}) { | ||
| 167 | my $_list = $self->{list}[$index++]; | ||
| 168 | last unless @$_list; | ||
| 169 | push @args, shift @$_list; | ||
| 170 | $index = 0 if ($index > $#{$self->{list}}); | ||
| 171 | } | ||
| 172 | } else { | ||
| 173 | my $end; | ||
| 174 | |||
| 175 | # if dynamically generating placeholders, avoid excess undef entries in list | ||
| 176 | if ($self->{makebinds}) { | ||
| 177 | # calculated end point, or actual end point | ||
| 178 | $end = min($self->{increment}{$key}{start} + $self->{batch} - 1, $#{ $self->{list} }); | ||
| 179 | } else { | ||
| 180 | # calculate end point using the specified batch size | ||
| 181 | $end = $self->{increment}{$key}{start} + $self->{batch}; | ||
| 182 | } | ||
| 183 | |||
| 184 | $end = $end > $#{$self->{list}} ? $#{$self->{list}} : $end; | ||
| 185 | |||
| 186 | if ($self->{makebinds} && | ||
| 187 | $self->{groups} && | ||
| 188 | @{$self->{list}} % $self->{makebinds}) { | ||
| 189 | |||
| 190 | # if generating groups of placeholders, avoid extra groups of placeholders | ||
| 191 | # but fill the last group with null's if there aren't enough values | ||
| 192 | # e.g. (#,#,#),(#,#,#) | ||
| 193 | # list => [1,2,3,4,5] | ||
| 194 | # results in: | ||
| 195 | # (1,2,3),(4,5,NULL) | ||
| 196 | my $g = ceil((@{$self->{list}} - $self->{increment}{$key}{start}) / $self->{makebinds}); | ||
| 197 | $end = $self->{increment}{$key}{start} + $self->{makebinds} * $g - 1; | ||
| 198 | } | ||
| 199 | |||
| 200 | @args = @{ $self->{list} }[ $self->{increment}{$key}{start}..$end]; | ||
| 201 | } | ||
| 202 | |||
| 203 | # run each argument through $dbh->quote, or a custom quote function if provided | ||
| 204 | return map { | ||
| 205 | if (defined ($self->{quote}) && ref($self->{quote}) eq 'CODE') { | ||
| 206 | $self->{quote}->($_); | ||
| 207 | } else { | ||
| 208 | $self->{dbh}->quote($_); | ||
| 209 | } | ||
| 210 | } @args; | ||
| 211 | } | ||
| 212 | |||
| 213 | |||
| 214 | # return a start and end/limit for current query | ||
| 215 | sub set_range_args { | ||
| 216 | my $self = shift; | ||
| 217 | my $key = $self->{query}->get_key(); | ||
| 218 | |||
| 219 | # map field to position in array | ||
| 220 | # position in array then corresponds to the order in which each element is bound in the query via sprintf | ||
| 221 | my %m = ( | ||
| 222 | arg1 => 0, | ||
| 223 | arg2 => 1, | ||
| 224 | ); | ||
| 225 | |||
| 226 | # self->{arg1} and $self->{arg2} are defined in identify_range_args() | ||
| 227 | # assign argN of @return as follows: | ||
| 228 | # if argN is 'offset', assign to start of the current increment | ||
| 229 | # if argN is 'end', assign to start + batch size for the current increment | ||
| 230 | # if argN is 'row_count', assign to batch size | ||
| 231 | |||
| 232 | # between identify_range_args() and this function, a sample result would be: | ||
| 233 | # given query: ...LIMIT #,# | ||
| 234 | # then identify_range_args() says: | ||
| 235 | # arg1 = 'offset', | ||
| 236 | # arg2 = 'row_count' | ||
| 237 | # then this functions sets: | ||
| 238 | # $return[0] = $self->{increment}{$key}{start}; | ||
| 239 | # $return[1] = $self->{batch} | ||
| 240 | # | ||
| 241 | my @return; | ||
| 242 | # place arguments in correct positions based on resut of identify_range_args() | ||
| 243 | for my $k (keys %m) { | ||
| 244 | |||
| 245 | if ($self->{$k} eq 'offset') { | ||
| 246 | |||
| 247 | $return[$m{$k}] = $self->{increment}{$key}{start}; | ||
| 248 | } elsif ($self->{$k} eq 'end') { | ||
| 249 | |||
| 250 | $return[$m{$k}] = $self->{increment}{$key}{start} + $self->{batch} - 1; # make between's exclusive | ||
| 251 | } elsif ($self->{$k} eq 'row_count') { | ||
| 252 | |||
| 253 | $return[$m{$k}] = $self->{batch}; | ||
| 254 | } | ||
| 255 | } | ||
| 256 | |||
| 257 | return @return; | ||
| 258 | } | ||
| 259 | |||
| 260 | # get the last batch of bind values that was executed | ||
| 261 | sub get_last_batch { | ||
| 262 | my $self = shift; | ||
| 263 | return @{$self->{__current_batch_values__}}; | ||
| 264 | } | ||
| 265 | |||
| 266 | |||
| 267 | 1; |