Batch/Query.pm untested

filter: Statementstmt 0.0% 0 / 81 Branchbran 0.0% 0 / 16 Conditioncond 0.0% 0 / 23 MC/DCmcdc 0.0% 0 / 14 Subroutinesub 0.0% 0 / 19 Podpod 0.0% 0 / 13 totaltotal 0.0% 0 / 166 CC 9 SCAR 23.2
CC
9
Cov
0%
get_param_type
30.0
expand_placeholders
20.0
convert_placeholders
6.0
CRAP 10.1
1 package DB::Batch::Query;
2 # expand queries into batch form
3 # example query: SELECT id FROM accounts WHERE id BETWEEN # AND #
4 #
5 # Copyright 2010, Chris Becker <clbecker@gmail.com>
6 #
7 # Original work sponsered by Shutterstock, LLC. http://shutterstock.com
8 #
9 # This program is free software; you can redistribute it and/or modify
10 # it under the same terms as Perl itself.
11  
12 use strict;
13 use warnings;
14  
15 use Data::Dumper;
16 use POSIX qw(ceil);
17 use List::Util qw(min max);
18 use Digest::MD5 qw(md5_hex);
19  
20 # args:
21 # query => 'sql query'
22 # makebinds => convert # into this number of placeholders
23 # groups => used for batch inserts, makes this number of groups containing $makebinds placeholders each
24 # args => list of values to be binded
25 sub new {
26 my $class = shift;
27 my %args = @_;
28 my $self = bless \%args, $class;
29  
30 $self->{__placeholder_query__} = $self->convert_placeholders();
31  
32 return $self;
33 }
34  
35 # If bind parameters determine some range, figure out what type of clause we're dealing with
36 sub get_param_type {
37 my $self = shift;
38  
39 return $self->{__type__} if $self->{__type__};
40  
41 if ($self->{__placeholder_query__} =~ m/LIMIT\s+\%s\s*,\s*\%s/i) {
42 $self->{__type__} = 'limit';
43 } elsif ($self->{__placeholder_query__} =~ m/LIMIT\s+\%s\s+OFFSET\s+\%s/i) {
44 $self->{__type__} = 'limit-offset';
45 } elsif ($self->{__placeholder_query__} =~ m/BETWEEN\s+%s\s+AND\s+%s/i) {
46 $self->{__type__} = 'between';
47 }
48  
49 return $self->{__type__};
50 }
51  
52 # bind the current batch of values to the query
53 sub bind_batch {
54 my $self = shift;
55 my $values = shift || [];
56  
57 # expand any placeholders based on the values of makebinds and groups
58 my $_query = $self->expand_placeholders(scalar @$values);
59 return sprintf($_query,@$values);
60 }
61  
62 # expand # into #,#,#...
63 # optional args:
64 # $batch_size: number of arguments being bound.
65 # if this number is less than makebinds, then fewer placeholders will be generated to avoid null entries in the query
66 #
67 # example:
68 # with the parameters groups => 2, makebinds => 3
69 # the query:
70 # INSERT INTO foo VALUES #
71 # will expand to:
72 # INSERT INTO foo VALUES (#,#,#),(#,#,#)
73 sub expand_placeholders {
74 my $self = shift;
75 my $batch_size = shift || $self->{makebinds};
76 my $query = $self->{__placeholder_query__};
77  
78 if ($self->{makebinds} && $self->{groups}) {
79  
80 if ($self->{groups} * $self->{makebinds} > $batch_size) {
81 $self->{groups} = ceil($batch_size / $self->{makebinds});
82 }
83 return sprintf($query,group_placeholders($self->{groups},$self->{makebinds},'%s',$self->{group}));
84 } elsif ($self->{makebinds}) {
85 return sprintf($query,placeholders($batch_size,'%s'));
86 }
87 return $query;
88 }
89  
90  
91 # return unique identifier of the current query and cache for later use
92 sub get_key {
93 my $self = shift;
94 return $self->{__md5__} || ($self->{__md5__} = md5_hex($self->{query}));
95 }
96  
97 # return the original sql string given
98 sub get_original_query {
99 my $self = shift;
100 return $self->{query};
101 }
102  
103 # return the current number of placeholders
104 sub get_placeholder_count {
105 my $self = shift;
106 my $placeholder_count = () = $self->{__placeholder_query__} =~ m/(%s)/g;
107 return $placeholder_count;
108 }
109  
110 # return query string with bind variables (e.g. '?') bound
111 sub bindstr {
112 my $self = shift;
113 my $query = shift || $self->{query};
114 $query =~ s/%(?!s)/%%/g; # escape standalone %'s
115 $query =~ s/(?<!\\)\?/%s/g; # replace all #'s with %s unless escaped
116 return sprintf ($query,@_);
117 }
118  
119 # convert '#' to '%s'
120 sub convert_placeholders {
121 my $self = shift;
122 my $query = $self->{query};
123 if ($query =~ m/(?<!\\)#/) {
124 $query =~ s/%(?!s)/%%/g; # escape standalone %'s
125 $query =~ s/(?<!\\)#/%s/g; # replace all #'s with %s unless escaped
126 }
127 return $query;
128 }
129  
130 # given column names, create the body of an insert statement with placeholders
131 sub make_insert_string {
132 my $fields = shift;
133 my $placeholder = shift;
134 my $field_string = join(',',map {"`$_`"} @$fields);
135 my $binds = placeholders(scalar @$fields,$placeholder);
136 return " ($field_string) VALUES ($binds)";
137 }
138  
139 # given column names, create the body of an insert statement with multiple sets of placeholders
140 # INSERT INTO table (a,b,c) VALUES (#,#,#),(#,#,#)...
141 #
142 # $fields: list of columns
143 # $count: number of batches of values (optional, default 1)
144 # $placeholder: optional, default '?'
145 sub make_multi_insert_string {
146 my $fields = shift;
147 my $count = shift || 1;
148 my $placeholder = shift;
149 my $field_string = join(',',map {"`$_`"} @$fields);
150 return " ($field_string) VALUES ".group_placeholders($count,scalar @$fields,$placeholder);
151 }
152  
153 # create a string containing N comma-separated placeholders
154 # args:
155 # $_[0]: number of placeholders
156 # $_[1]: (optional) placeholder character to use. Default is '?'
157 sub placeholders {
158 my $num = shift;
159 my $p = shift || '?';
160 return join(',', ($p) x $num);
161 }
162  
163 # created $g groups containing $n placeholders
164 # $_[0]: number of groups
165 # $_[1]: number of placeholders per group
166 # $_[2]: (optional) placeholder string, default '?'
167 # $_[3]: (optional) predefined placeholder group
168 sub group_placeholders {
169 my $g = shift;
170 my $n = shift;
171 my $p = shift || '?';
172 my $binds = shift || placeholders($n,$p);
173 return join(',',("($binds)") x $g);
174 }
175  
176 1;