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