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