Batch/BindValues.pm untested

filter: Statementstmt 0.0% 0 / 119 Branchbran 0.0% 0 / 58 Conditioncond 0.0% 0 / 23 MC/DCmcdc 0.0% 0 / 14 Subroutinesub 0.0% 0 / 11 Podpod 0.0% 0 / 6 totaltotal 0.0% 0 / 231 CC 30 SCAR 46.3
CC
30
Cov
0%
identify_range_args
210.0
set_list_args
90.0
increment
42.0
CRAP 102.4
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;