line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package SQL::Interp; |
2
|
|
|
|
|
|
|
|
3
|
|
|
|
|
|
|
our $VERSION = '1.26'; |
4
|
|
|
|
|
|
|
|
5
|
4
|
|
|
4
|
|
138095
|
use strict; |
|
4
|
|
|
|
|
23
|
|
|
4
|
|
|
|
|
117
|
|
6
|
4
|
|
|
4
|
|
21
|
use warnings; |
|
4
|
|
|
|
|
7
|
|
|
4
|
|
|
|
|
97
|
|
7
|
4
|
|
|
4
|
|
19
|
use Carp; |
|
4
|
|
|
|
|
6
|
|
|
4
|
|
|
|
|
685
|
|
8
|
|
|
|
|
|
|
|
9
|
|
|
|
|
|
|
|
10
|
|
|
|
|
|
|
# Custom import magic to support Sub::Exporter '-as' style function renaming. |
11
|
|
|
|
|
|
|
# (for backwards compatibility with older versions without adding an additional dependency) |
12
|
|
|
|
|
|
|
sub import { |
13
|
6
|
|
|
6
|
|
8493
|
my $pkg = shift; |
14
|
6
|
|
|
|
|
33
|
my $caller = caller; |
15
|
6
|
|
|
|
|
24
|
my %export = qw/sql_interp 1 sql_interp_strict 1 sql_type 1 sql 1/; |
16
|
6
|
|
|
|
|
24
|
while(my $fn = shift) { |
17
|
27
|
100
|
|
|
|
56
|
if($fn eq ':all') { |
18
|
5
|
|
|
|
|
22
|
push @_, keys %export; |
19
|
5
|
|
|
|
|
18
|
next; |
20
|
|
|
|
|
|
|
} |
21
|
22
|
50
|
|
|
|
43
|
croak "Symbol '$fn' is not exported by $pkg" if !$export{$fn}; |
22
|
22
|
|
|
|
|
32
|
my $as = $fn; |
23
|
22
|
100
|
|
|
|
63
|
if(ref $_[0] eq 'HASH') { |
24
|
2
|
|
|
|
|
3
|
my $arg = shift; |
25
|
2
|
50
|
|
|
|
6
|
$as = $arg->{'-as'} if defined $arg->{'-as'}; |
26
|
|
|
|
|
|
|
} |
27
|
4
|
|
|
4
|
|
30
|
no strict 'refs'; |
|
4
|
|
|
|
|
8
|
|
|
4
|
|
|
|
|
10232
|
|
28
|
22
|
|
|
|
|
27
|
*{$caller.'::'.$as} = *{$pkg.'::'.$fn}; |
|
22
|
|
|
|
|
1535
|
|
|
22
|
|
|
|
|
51
|
|
29
|
|
|
|
|
|
|
} |
30
|
|
|
|
|
|
|
} |
31
|
|
|
|
|
|
|
|
32
|
|
|
|
|
|
|
|
33
|
|
|
|
|
|
|
# whether TRACE_SQL is enabled |
34
|
|
|
|
|
|
|
my $trace_sql_enabled = $ENV{TRACE_SQL} || 0; |
35
|
|
|
|
|
|
|
|
36
|
|
|
|
|
|
|
# regexes |
37
|
|
|
|
|
|
|
my $id_match = qr/(?:[a-zA-Z_][a-zA-Z0-9_\$\.]*|"[^"]+"|`[^`]+`)/; |
38
|
|
|
|
|
|
|
|
39
|
|
|
|
|
|
|
|
40
|
|
|
|
|
|
|
# next ID to use for table alias |
41
|
|
|
|
|
|
|
# [local to sql_interp functions] |
42
|
|
|
|
|
|
|
my $alias_id = 0; |
43
|
|
|
|
|
|
|
|
44
|
|
|
|
|
|
|
# current index in interpolation list |
45
|
|
|
|
|
|
|
# [local to sql_interp functions] |
46
|
|
|
|
|
|
|
my $idx = 0; |
47
|
|
|
|
|
|
|
|
48
|
|
|
|
|
|
|
# current interpolation list |
49
|
|
|
|
|
|
|
# [local to sql_interp functions] |
50
|
|
|
|
|
|
|
my $items_ref = undef; |
51
|
|
|
|
|
|
|
|
52
|
|
|
|
|
|
|
# whether typed sql_type() ever used (if so, |
53
|
|
|
|
|
|
|
# format of @bind result is more complicated) |
54
|
|
|
|
|
|
|
# [local to sql_interp functions] |
55
|
|
|
|
|
|
|
my $is_var_used = 0; |
56
|
|
|
|
|
|
|
|
57
|
|
|
|
|
|
|
# state item (SQL::Iterpolate or DBI handle) used in interpolation. |
58
|
|
|
|
|
|
|
# [local to sql_interp functions] |
59
|
|
|
|
|
|
|
my $state = undef; |
60
|
|
|
|
|
|
|
|
61
|
|
|
|
|
|
|
# bind elements in interpolation |
62
|
|
|
|
|
|
|
# [local to sql_interp functions] |
63
|
|
|
|
|
|
|
my @bind; |
64
|
|
|
|
|
|
|
|
65
|
|
|
|
|
|
|
# only used by DBIx::Interp, so not further documented here |
66
|
|
|
|
|
|
|
sub new { |
67
|
2
|
|
|
2
|
0
|
96
|
my $class = shift; |
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
# process special params. |
70
|
2
|
|
|
|
|
3
|
my $dbh; |
71
|
2
|
|
|
|
|
11
|
while (ref $_[0] ne '') { |
72
|
1
|
50
|
|
|
|
4
|
if (UNIVERSAL::isa($_[0], 'DBI::db')) { |
73
|
1
|
|
|
|
|
4
|
$dbh = shift; |
74
|
|
|
|
|
|
|
} |
75
|
|
|
|
|
|
|
} |
76
|
2
|
|
|
|
|
6
|
my %params = @_; |
77
|
|
|
|
|
|
|
|
78
|
|
|
|
|
|
|
# build object |
79
|
2
|
|
|
|
|
8
|
my $self = bless { |
80
|
|
|
|
|
|
|
dbh => $dbh, |
81
|
|
|
|
|
|
|
}, $class; |
82
|
2
|
|
|
|
|
5
|
return $self; |
83
|
|
|
|
|
|
|
} |
84
|
|
|
|
|
|
|
|
85
|
|
|
|
|
|
|
# note: sql_interp is not reentrant. |
86
|
|
|
|
|
|
|
sub sql_interp { |
87
|
164
|
|
|
164
|
1
|
51685
|
my @items = @_; |
88
|
|
|
|
|
|
|
|
89
|
|
|
|
|
|
|
# clear call state |
90
|
164
|
|
|
|
|
254
|
$alias_id = 0; |
91
|
164
|
|
|
|
|
209
|
$idx = 0; |
92
|
164
|
|
|
|
|
394
|
$items_ref = undef; |
93
|
164
|
|
|
|
|
215
|
$is_var_used = 0; |
94
|
164
|
|
|
|
|
228
|
$state = undef; |
95
|
164
|
|
|
|
|
262
|
@bind = (); |
96
|
|
|
|
|
|
|
|
97
|
|
|
|
|
|
|
# extract state item (if any) |
98
|
164
|
|
|
|
|
215
|
my $interp; |
99
|
164
|
100
|
|
|
|
777
|
if (UNIVERSAL::isa($items[0], 'SQL::Interp')) { |
|
|
50
|
|
|
|
|
|
100
|
83
|
|
|
|
|
139
|
$state = $interp = $items[0]; |
101
|
|
|
|
|
|
|
} |
102
|
|
|
|
|
|
|
elsif (UNIVERSAL::isa($items[0], 'DBI::db')) { |
103
|
0
|
|
|
|
|
0
|
$state = $items[0]; |
104
|
|
|
|
|
|
|
} |
105
|
|
|
|
|
|
|
|
106
|
164
|
100
|
|
|
|
345
|
shift @items if $state; |
107
|
|
|
|
|
|
|
|
108
|
164
|
|
|
|
|
254
|
$items_ref = \@items; |
109
|
|
|
|
|
|
|
|
110
|
|
|
|
|
|
|
# interpolate! |
111
|
164
|
|
|
|
|
332
|
my $sql = _sql_interp(@items); |
112
|
|
|
|
|
|
|
|
113
|
|
|
|
|
|
|
# convert bind values to complex format (if needed) |
114
|
159
|
100
|
|
|
|
303
|
if ($is_var_used) { |
115
|
12
|
|
|
|
|
18
|
for my $val (@bind) { |
116
|
36
|
|
|
|
|
53
|
my $valcopy = $val; |
117
|
36
|
100
|
|
|
|
82
|
! ref $val and $val = [$val, sql_type(\$valcopy)]; |
118
|
|
|
|
|
|
|
} |
119
|
|
|
|
|
|
|
} |
120
|
|
|
|
|
|
|
|
121
|
|
|
|
|
|
|
$trace_sql_enabled |
122
|
159
|
50
|
|
|
|
293
|
and print STDERR "DEBUG:interp[sql=$sql,bind=" |
123
|
|
|
|
|
|
|
. join(':', @bind) . "]\n"; |
124
|
|
|
|
|
|
|
|
125
|
159
|
|
|
|
|
490
|
return ($sql, @bind); |
126
|
|
|
|
|
|
|
} |
127
|
|
|
|
|
|
|
|
128
|
|
|
|
|
|
|
# Prevent accidental SQL injection holes |
129
|
|
|
|
|
|
|
# By enforcing the rule that two non-references cannot be used |
130
|
|
|
|
|
|
|
# in a row. If you really mean that, concatanate the strings instead. |
131
|
|
|
|
|
|
|
sub sql_interp_strict { |
132
|
1
|
|
|
1
|
0
|
82
|
my @items = @_; |
133
|
|
|
|
|
|
|
|
134
|
1
|
|
|
|
|
3
|
my $adjacent_string_cnt = 0; |
135
|
1
|
|
|
|
|
3
|
for my $item (@items) { |
136
|
|
|
|
|
|
|
# If we have a reference, reset the counter and move to the next element. |
137
|
2
|
50
|
|
|
|
6
|
if (ref $item) { |
138
|
0
|
|
|
|
|
0
|
$adjacent_string_cnt = 0; |
139
|
|
|
|
|
|
|
} |
140
|
|
|
|
|
|
|
else { |
141
|
2
|
|
|
|
|
4
|
$adjacent_string_cnt++; |
142
|
2
|
100
|
|
|
|
9
|
if ($adjacent_string_cnt == 2) { |
143
|
1
|
|
|
|
|
181
|
croak "failed sql_interp_strict check. Refactor to concatenate adjacent strings in sql_interp array"; |
144
|
|
|
|
|
|
|
} |
145
|
|
|
|
|
|
|
} |
146
|
|
|
|
|
|
|
|
147
|
|
|
|
|
|
|
} |
148
|
|
|
|
|
|
|
|
149
|
0
|
|
|
|
|
0
|
return sql_interp(@_); |
150
|
|
|
|
|
|
|
} |
151
|
|
|
|
|
|
|
|
152
|
|
|
|
|
|
|
# helper called by sql_interp() |
153
|
|
|
|
|
|
|
# @items - interpolation list |
154
|
|
|
|
|
|
|
sub _sql_interp { |
155
|
252
|
|
|
252
|
|
446
|
my (@items) = @_; |
156
|
|
|
|
|
|
|
|
157
|
252
|
|
|
|
|
356
|
my $sql = ''; |
158
|
|
|
|
|
|
|
|
159
|
252
|
|
|
|
|
390
|
foreach my $item (@items) { |
160
|
446
|
|
|
|
|
558
|
my $varobj; |
161
|
446
|
|
|
|
|
580
|
my $bind_size = @bind; |
162
|
446
|
100
|
|
|
|
891
|
if (ref $item eq 'SQL::Interp::Variable') { |
163
|
18
|
100
|
66
|
|
|
63
|
unless (keys %$item == 1 && defined($item->{value})) { |
164
|
16
|
|
|
|
|
22
|
$varobj = $item; |
165
|
16
|
|
|
|
|
23
|
$is_var_used = 1; |
166
|
|
|
|
|
|
|
} |
167
|
18
|
|
|
|
|
29
|
$item = $item->{value}; |
168
|
|
|
|
|
|
|
} |
169
|
|
|
|
|
|
|
|
170
|
446
|
100
|
|
|
|
906
|
if (ref $item eq 'SQL::Interp::SQL') { |
|
|
100
|
|
|
|
|
|
171
|
50
|
|
|
|
|
140
|
my ($sql2, @bind2) = _sql_interp(@$item); |
172
|
50
|
100
|
|
|
|
105
|
$sql .= ' ' if $sql ne ''; |
173
|
50
|
|
|
|
|
73
|
$sql .= $sql2; |
174
|
50
|
|
|
|
|
76
|
push @bind, @bind2; |
175
|
|
|
|
|
|
|
} |
176
|
|
|
|
|
|
|
elsif (ref $item) { |
177
|
191
|
100
|
66
|
|
|
2471
|
if ($sql =~ /\b(NOT\s+)?IN\s*$/si) { |
|
|
100
|
100
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
178
|
30
|
|
100
|
|
|
146
|
my $not = quotemeta($1 || ''); |
179
|
|
|
|
|
|
|
|
180
|
30
|
100
|
|
|
|
66
|
$item = [ $$item ] if ref $item eq 'SCALAR'; |
181
|
|
|
|
|
|
|
|
182
|
|
|
|
|
|
|
# allow double references |
183
|
30
|
100
|
|
|
|
96
|
$item = $$item if ref $item eq 'REF' ; |
184
|
|
|
|
|
|
|
|
185
|
30
|
50
|
|
|
|
59
|
if (ref $item eq 'ARRAY') { |
186
|
30
|
100
|
|
|
|
50
|
if (@$item == 0) { |
187
|
12
|
100
|
|
|
|
22
|
my $dummy_expr = $not ? '1=1' : '1=0'; |
188
|
12
|
50
|
|
|
|
310
|
$sql =~ s/$id_match\s+${not}IN\s*$/$dummy_expr/si or croak 'ASSERT'; |
189
|
|
|
|
|
|
|
} |
190
|
|
|
|
|
|
|
else { |
191
|
|
|
|
|
|
|
$sql .= " (" . join(', ', map { |
192
|
18
|
|
|
|
|
38
|
_sql_interp_data($_); |
|
37
|
|
|
|
|
58
|
|
193
|
|
|
|
|
|
|
} @$item) . ")"; |
194
|
|
|
|
|
|
|
} |
195
|
|
|
|
|
|
|
} |
196
|
|
|
|
|
|
|
else { |
197
|
0
|
|
|
|
|
0
|
_error_item($idx, \@items); |
198
|
|
|
|
|
|
|
} |
199
|
|
|
|
|
|
|
} |
200
|
|
|
|
|
|
|
elsif ($sql =~ /\bARRAY\s*$/si) { |
201
|
8
|
100
|
|
|
|
21
|
$item = [ $$item ] if ref $item eq 'SCALAR'; |
202
|
|
|
|
|
|
|
|
203
|
|
|
|
|
|
|
# allow double references |
204
|
8
|
100
|
|
|
|
18
|
$item = $$item if ref $item eq 'REF' ; |
205
|
|
|
|
|
|
|
|
206
|
8
|
50
|
|
|
|
16
|
if (ref $item eq 'ARRAY') { |
207
|
|
|
|
|
|
|
$sql .= '[' . join(', ', map { |
208
|
8
|
|
|
|
|
19
|
_sql_interp_data($_); |
|
10
|
|
|
|
|
18
|
|
209
|
|
|
|
|
|
|
} @$item) . ']'; |
210
|
|
|
|
|
|
|
} |
211
|
|
|
|
|
|
|
else { |
212
|
0
|
|
|
|
|
0
|
_error_item($idx, \@items); |
213
|
|
|
|
|
|
|
} |
214
|
|
|
|
|
|
|
} |
215
|
|
|
|
|
|
|
elsif ($sql =~ /\b(?:ON\s+DUPLICATE\s+KEY\s+UPDATE|SET)\s*$/si && ref $item eq 'HASH') { |
216
|
4
|
50
|
|
|
|
15
|
_error('Hash has zero elements.') if keys %$item == 0; |
217
|
|
|
|
|
|
|
$sql .= " " . join(', ', map { |
218
|
4
|
|
|
|
|
28
|
my $key = $_; |
|
10
|
|
|
|
|
17
|
|
219
|
10
|
|
|
|
|
13
|
my $val = $item->{$key}; |
220
|
10
|
|
|
|
|
24
|
"$key=" . |
221
|
|
|
|
|
|
|
_sql_interp_data($val); |
222
|
|
|
|
|
|
|
} (sort keys %$item)); |
223
|
|
|
|
|
|
|
} |
224
|
|
|
|
|
|
|
elsif ($sql =~ /\b(REPLACE|INSERT)[\w\s]*\sINTO\s*$id_match\s*$/si) { |
225
|
26
|
100
|
|
|
|
78
|
$item = [ $$item ] if ref $item eq 'SCALAR'; |
226
|
26
|
100
|
|
|
|
59
|
if (ref $item eq 'ARRAY') { |
|
|
50
|
|
|
|
|
|
227
|
|
|
|
|
|
|
$sql .= " VALUES(" . join(', ', map { |
228
|
18
|
|
|
|
|
41
|
_sql_interp_data($_); |
|
24
|
|
|
|
|
45
|
|
229
|
|
|
|
|
|
|
} @$item) . ")"; |
230
|
|
|
|
|
|
|
} |
231
|
|
|
|
|
|
|
elsif (ref $item eq 'HASH') { |
232
|
8
|
|
|
|
|
36
|
my @keyseq = sort keys %$item; |
233
|
|
|
|
|
|
|
$sql .= |
234
|
|
|
|
|
|
|
" (" . join(', ', @keyseq) . ")" . |
235
|
|
|
|
|
|
|
" VALUES(" . join(', ', map { |
236
|
8
|
|
|
|
|
33
|
_sql_interp_data($item->{$_}); |
|
14
|
|
|
|
|
29
|
|
237
|
|
|
|
|
|
|
} @keyseq) . ")"; |
238
|
|
|
|
|
|
|
} |
239
|
0
|
|
|
|
|
0
|
else { _error_item($idx, \@items); } |
240
|
|
|
|
|
|
|
} |
241
|
|
|
|
|
|
|
elsif ($sql =~ /(?:\bFROM|JOIN)\s*$/si && $sql !~ /DISTINCT\s+FROM\s*$/) { |
242
|
|
|
|
|
|
|
# table reference |
243
|
|
|
|
|
|
|
|
244
|
|
|
|
|
|
|
# get alias for table |
245
|
33
|
|
|
|
|
67
|
my $table_alias = undef; # alias given to table |
246
|
33
|
|
|
|
|
55
|
my $next_item = $items[$idx + 1]; |
247
|
33
|
100
|
66
|
|
|
102
|
if(defined $next_item && ref $next_item eq '' && |
|
|
|
100
|
|
|
|
|
248
|
|
|
|
|
|
|
$next_item =~ /\s*AS\b/is) |
249
|
|
|
|
|
|
|
{ |
250
|
2
|
|
|
|
|
4
|
$table_alias = undef; # provided by client |
251
|
|
|
|
|
|
|
} |
252
|
|
|
|
|
|
|
else { |
253
|
31
|
|
|
|
|
61
|
$table_alias = 'tbl' . $alias_id++; |
254
|
|
|
|
|
|
|
} |
255
|
|
|
|
|
|
|
|
256
|
33
|
50
|
|
|
|
80
|
$sql .= ' ' unless $sql eq ''; |
257
|
33
|
|
|
|
|
91
|
$sql .= _sql_interp_resultset($item); |
258
|
30
|
100
|
|
|
|
82
|
$sql .= " AS $table_alias" if defined $table_alias; |
259
|
|
|
|
|
|
|
} |
260
|
|
|
|
|
|
|
elsif (ref $item eq 'SCALAR') { |
261
|
54
|
|
|
|
|
122
|
push @bind, $$item; |
262
|
54
|
|
|
|
|
94
|
$sql .= ' ?'; |
263
|
|
|
|
|
|
|
} |
264
|
|
|
|
|
|
|
elsif (ref $item eq 'HASH') { # e.g. WHERE {x = 3, y = 4} |
265
|
22
|
100
|
|
|
|
65
|
if (keys %$item == 0) { |
266
|
2
|
|
|
|
|
5
|
$sql .= ' 1=1'; |
267
|
|
|
|
|
|
|
} |
268
|
|
|
|
|
|
|
else { |
269
|
|
|
|
|
|
|
my $s = join ' AND ', map { |
270
|
20
|
|
|
|
|
71
|
my $key = $_; |
|
34
|
|
|
|
|
49
|
|
271
|
34
|
|
|
|
|
58
|
my $val = $item->{$key}; |
272
|
34
|
100
|
|
|
|
79
|
if (! defined $val) { |
|
|
100
|
|
|
|
|
|
273
|
2
|
|
|
|
|
7
|
"$key IS NULL"; |
274
|
|
|
|
|
|
|
} |
275
|
|
|
|
|
|
|
elsif (ref $val eq 'ARRAY') { |
276
|
8
|
|
|
|
|
19
|
_sql_interp_list($key, $val); |
277
|
|
|
|
|
|
|
} |
278
|
|
|
|
|
|
|
else { |
279
|
24
|
|
|
|
|
46
|
"$key=" . |
280
|
|
|
|
|
|
|
_sql_interp_data($val); |
281
|
|
|
|
|
|
|
} |
282
|
|
|
|
|
|
|
} (sort keys %$item); |
283
|
20
|
100
|
|
|
|
69
|
$s = "($s)" if keys %$item > 1; |
284
|
20
|
|
|
|
|
40
|
$s = " $s"; |
285
|
20
|
|
|
|
|
42
|
$sql .= $s; |
286
|
|
|
|
|
|
|
} |
287
|
|
|
|
|
|
|
} |
288
|
|
|
|
|
|
|
elsif (ref $item eq 'ARRAY') { # result set |
289
|
14
|
50
|
|
|
|
31
|
$sql .= ' ' unless $sql eq ''; |
290
|
14
|
|
|
|
|
29
|
$sql .= _sql_interp_resultset($item); |
291
|
|
|
|
|
|
|
} |
292
|
0
|
|
|
|
|
0
|
else { _error_item($idx, \@items); } |
293
|
|
|
|
|
|
|
} |
294
|
|
|
|
|
|
|
else { |
295
|
205
|
100
|
100
|
|
|
1044
|
$sql .= ' ' unless $sql =~ /(^|\s)$/ || $item =~ /^\s/; # style |
296
|
205
|
|
|
|
|
426
|
$sql .= $item; |
297
|
|
|
|
|
|
|
} |
298
|
|
|
|
|
|
|
|
299
|
|
|
|
|
|
|
# attach $varobj to any bind values it generates |
300
|
441
|
100
|
|
|
|
816
|
if ($varobj) { |
301
|
16
|
|
|
|
|
27
|
my $num_pushed = @bind - $bind_size; |
302
|
16
|
|
|
|
|
46
|
for my $val (@bind[-$num_pushed..-1]) { |
303
|
20
|
|
|
|
|
49
|
$val = [$val, $varobj]; |
304
|
|
|
|
|
|
|
} |
305
|
|
|
|
|
|
|
} |
306
|
441
|
|
|
|
|
692
|
$idx++; |
307
|
|
|
|
|
|
|
} |
308
|
|
|
|
|
|
|
|
309
|
247
|
|
|
|
|
553
|
return $sql; |
310
|
|
|
|
|
|
|
} |
311
|
|
|
|
|
|
|
|
312
|
|
|
|
|
|
|
# sql_interp helper function. |
313
|
|
|
|
|
|
|
# Interpolate data element in aggregate variable (hashref or arrayref). |
314
|
|
|
|
|
|
|
# $ele - raw input element from aggregate. |
315
|
|
|
|
|
|
|
# returns $sql |
316
|
|
|
|
|
|
|
sub _sql_interp_data { |
317
|
203
|
|
|
203
|
|
341
|
my ($ele) = @_; |
318
|
203
|
100
|
|
|
|
362
|
if (ref $ele) { # e.g. sql() |
319
|
38
|
|
|
|
|
79
|
my ($sql2, @bind2) = _sql_interp($ele); |
320
|
38
|
|
|
|
|
53
|
push @bind, @bind2; |
321
|
38
|
50
|
|
|
|
77
|
$is_var_used = 1 if ref $bind2[0]; |
322
|
38
|
|
|
|
|
147
|
return $sql2; |
323
|
|
|
|
|
|
|
} |
324
|
|
|
|
|
|
|
else { |
325
|
165
|
|
|
|
|
263
|
push @bind, $ele; |
326
|
165
|
|
|
|
|
505
|
return '?'; |
327
|
|
|
|
|
|
|
} |
328
|
|
|
|
|
|
|
} |
329
|
|
|
|
|
|
|
|
330
|
|
|
|
|
|
|
# sql_interp helper function to interpolate "key IN list", |
331
|
|
|
|
|
|
|
# assuming context ("WHERE", {key => $list, ...}). |
332
|
|
|
|
|
|
|
sub _sql_interp_list { |
333
|
8
|
|
|
8
|
|
17
|
my ($key, $list) = @_; |
334
|
8
|
100
|
|
|
|
14
|
if (@$list == 0) { |
335
|
2
|
|
|
|
|
8
|
return "1=0"; |
336
|
|
|
|
|
|
|
} |
337
|
|
|
|
|
|
|
else { |
338
|
6
|
|
|
|
|
9
|
my @sqle; |
339
|
6
|
|
|
|
|
11
|
for my $ele (@$list) { |
340
|
12
|
|
|
|
|
21
|
my $sqle |
341
|
|
|
|
|
|
|
= _sql_interp_data($ele); |
342
|
12
|
|
|
|
|
24
|
push @sqle, $sqle; |
343
|
|
|
|
|
|
|
} |
344
|
6
|
|
|
|
|
20
|
my $sql2 = $key . " IN (" . join(', ', @sqle) . ")"; |
345
|
6
|
|
|
|
|
23
|
return $sql2; |
346
|
|
|
|
|
|
|
} |
347
|
|
|
|
|
|
|
} |
348
|
|
|
|
|
|
|
# sql_interp helper function to interpolate result set, |
349
|
|
|
|
|
|
|
# e.g. [[1,2],[3,4]] or [{a=>1,b=>2},{a=>3,b=>4}]. |
350
|
|
|
|
|
|
|
sub _sql_interp_resultset { |
351
|
47
|
|
|
47
|
|
114
|
my($item) = @_; |
352
|
47
|
|
|
|
|
68
|
my $sql = ''; |
353
|
47
|
50
|
|
|
|
87
|
if (ref $item eq 'ARRAY') { |
354
|
47
|
100
|
|
|
|
93
|
_error("table reference has zero rows") # improve? |
355
|
|
|
|
|
|
|
if @$item == 0; |
356
|
46
|
|
|
|
|
66
|
my $sql2 = ''; |
357
|
46
|
100
|
|
|
|
113
|
if(ref $item->[0] eq 'ARRAY') { |
|
|
50
|
|
|
|
|
|
358
|
|
|
|
|
|
|
_error("table reference has zero columns") # improve? |
359
|
28
|
100
|
|
|
|
37
|
if @{ $item->[0] } == 0; |
|
28
|
|
|
|
|
58
|
|
360
|
26
|
|
|
|
|
58
|
for my $row ( @$item ) { |
361
|
30
|
|
|
|
|
48
|
my $is_first_row = ($sql2 eq ''); |
362
|
30
|
100
|
|
|
|
52
|
$sql2 .= ' UNION ALL ' unless $is_first_row; |
363
|
|
|
|
|
|
|
$sql2 .= |
364
|
|
|
|
|
|
|
"SELECT " . |
365
|
|
|
|
|
|
|
join(', ', map { |
366
|
30
|
|
|
|
|
59
|
_sql_interp_data($_) |
|
40
|
|
|
|
|
67
|
|
367
|
|
|
|
|
|
|
} @$row); |
368
|
|
|
|
|
|
|
} |
369
|
|
|
|
|
|
|
} |
370
|
|
|
|
|
|
|
elsif(ref $item->[0] eq 'HASH') { |
371
|
|
|
|
|
|
|
_error("table reference has zero columns") # improve? |
372
|
18
|
100
|
|
|
|
21
|
if keys %{ $item->[0] } == 0; |
|
18
|
|
|
|
|
55
|
|
373
|
16
|
|
|
|
|
26
|
my $first_row = $item->[0]; |
374
|
16
|
|
|
|
|
26
|
for my $row ( @$item ) { |
375
|
20
|
|
|
|
|
32
|
my $is_first_row = ($sql2 eq ''); |
376
|
20
|
100
|
|
|
|
59
|
$sql2 .= ' UNION ALL ' unless $is_first_row; |
377
|
|
|
|
|
|
|
$sql2 .= |
378
|
|
|
|
|
|
|
"SELECT " . |
379
|
|
|
|
|
|
|
join(', ', map { |
380
|
20
|
|
|
|
|
62
|
my($key, $val) = ($_, $row->{$_}); |
|
32
|
|
|
|
|
64
|
|
381
|
32
|
|
|
|
|
47
|
my $sql3 = _sql_interp_data($val); |
382
|
32
|
100
|
|
|
|
66
|
$sql3 .= " AS $key" if $is_first_row; |
383
|
32
|
|
|
|
|
102
|
$sql3; |
384
|
|
|
|
|
|
|
} (sort keys %$first_row)); |
385
|
|
|
|
|
|
|
} |
386
|
|
|
|
|
|
|
} |
387
|
|
|
|
|
|
|
else { |
388
|
0
|
|
|
|
|
0
|
_error_item($idx, $items_ref); |
389
|
|
|
|
|
|
|
} |
390
|
42
|
50
|
|
|
|
87
|
$sql .= ' ' unless $sql eq ''; |
391
|
42
|
|
|
|
|
87
|
$sql .= "($sql2)"; |
392
|
|
|
|
|
|
|
} |
393
|
0
|
|
|
|
|
0
|
else { _error_item($idx, $items_ref); } |
394
|
42
|
|
|
|
|
78
|
return $sql; |
395
|
|
|
|
|
|
|
} |
396
|
|
|
|
|
|
|
|
397
|
|
|
|
|
|
|
sub sql { |
398
|
23
|
|
|
23
|
1
|
2990
|
return SQL::Interp::SQL->new(@_); |
399
|
|
|
|
|
|
|
} |
400
|
|
|
|
|
|
|
|
401
|
|
|
|
|
|
|
sub sql_type { |
402
|
29
|
|
|
29
|
1
|
2008
|
return SQL::Interp::Variable->new(@_); |
403
|
|
|
|
|
|
|
} |
404
|
|
|
|
|
|
|
|
405
|
|
|
|
|
|
|
# helper function to throw error |
406
|
|
|
|
|
|
|
sub _error_item { |
407
|
0
|
|
|
0
|
|
0
|
my ($idx, $items_ref) = @_; |
408
|
0
|
0
|
|
|
|
0
|
my $prev = $idx > 0 ? $items_ref->[$idx-1] : undef; |
409
|
0
|
0
|
|
|
|
0
|
my $prev_text = defined($prev) ? " following '$prev'" : ""; |
410
|
0
|
|
|
|
|
0
|
my $cur = $items_ref->[$idx]; |
411
|
0
|
|
|
|
|
0
|
_error("SQL::Interp error: Unrecognized " |
412
|
|
|
|
|
|
|
. "'$cur'$prev_text in interpolation list."); |
413
|
0
|
|
|
|
|
0
|
return; |
414
|
|
|
|
|
|
|
} |
415
|
|
|
|
|
|
|
|
416
|
|
|
|
|
|
|
sub _error { |
417
|
5
|
|
|
5
|
|
650
|
croak "SQL::Interp error: $_[0]"; |
418
|
|
|
|
|
|
|
} |
419
|
|
|
|
|
|
|
|
420
|
|
|
|
|
|
|
1; |
421
|
|
|
|
|
|
|
|
422
|
|
|
|
|
|
|
package SQL::Interp::Variable; |
423
|
4
|
|
|
4
|
|
32
|
use strict; |
|
4
|
|
|
|
|
9
|
|
|
4
|
|
|
|
|
113
|
|
424
|
4
|
|
|
4
|
|
23
|
use Carp; |
|
4
|
|
|
|
|
7
|
|
|
4
|
|
|
|
|
570
|
|
425
|
|
|
|
|
|
|
|
426
|
|
|
|
|
|
|
sub new { |
427
|
29
|
|
|
29
|
|
55
|
my ($class, $value, %params) = @_; |
428
|
29
|
50
|
|
|
|
71
|
SQL::Interp::_error( |
429
|
|
|
|
|
|
|
"Value '$value' in sql_type constructor is not a reference") |
430
|
|
|
|
|
|
|
if ! ref $value; |
431
|
29
|
|
|
|
|
79
|
my $self = bless {value => $value, %params}, $class; |
432
|
29
|
|
|
|
|
99
|
return $self; |
433
|
|
|
|
|
|
|
} |
434
|
|
|
|
|
|
|
|
435
|
|
|
|
|
|
|
1; |
436
|
|
|
|
|
|
|
|
437
|
|
|
|
|
|
|
|
438
|
|
|
|
|
|
|
package SQL::Interp::SQL; |
439
|
4
|
|
|
4
|
|
27
|
use strict; |
|
4
|
|
|
|
|
8
|
|
|
4
|
|
|
|
|
107
|
|
440
|
4
|
|
|
4
|
|
21
|
use Carp; |
|
4
|
|
|
|
|
7
|
|
|
4
|
|
|
|
|
316
|
|
441
|
4
|
|
|
4
|
|
3878
|
use overload '.' => \&concat, '""' => \&stringify; |
|
4
|
|
|
|
|
3000
|
|
|
4
|
|
|
|
|
32
|
|
442
|
|
|
|
|
|
|
|
443
|
|
|
|
|
|
|
sub new { |
444
|
24
|
|
|
24
|
|
58
|
my ($class, @list) = @_; |
445
|
|
|
|
|
|
|
|
446
|
24
|
|
|
|
|
41
|
my $self = \@list; |
447
|
24
|
|
|
|
|
41
|
bless $self, $class; |
448
|
24
|
|
|
|
|
138
|
return $self; |
449
|
|
|
|
|
|
|
} |
450
|
|
|
|
|
|
|
|
451
|
|
|
|
|
|
|
# Concatenate SQL object with another expression. |
452
|
|
|
|
|
|
|
# An SQL object can be concatenated with another SQL object, |
453
|
|
|
|
|
|
|
# variable reference, or an SQL string. |
454
|
|
|
|
|
|
|
sub concat { |
455
|
0
|
|
|
0
|
|
|
my ($a, $b, $inverted) = @_; |
456
|
|
|
|
|
|
|
|
457
|
0
|
0
|
|
|
|
|
my @params = ( @$a, ref $b eq __PACKAGE__ ? @$b : $b ); |
458
|
0
|
0
|
|
|
|
|
@params = reverse @params if $inverted; |
459
|
0
|
|
|
|
|
|
my $o = SQL::Interp::SQL->new(@params); |
460
|
0
|
|
|
|
|
|
return $o; |
461
|
|
|
|
|
|
|
} |
462
|
|
|
|
|
|
|
|
463
|
|
|
|
|
|
|
sub stringify { |
464
|
0
|
|
|
0
|
|
|
my ($a) = @_; |
465
|
0
|
|
|
|
|
|
return $a; |
466
|
|
|
|
|
|
|
} |
467
|
|
|
|
|
|
|
|
468
|
|
|
|
|
|
|
1; |
469
|
|
|
|
|
|
|
|
470
|
|
|
|
|
|
|
__END__ |