line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package SQL::Abstract::More; |
2
|
10
|
|
|
10
|
|
520899
|
use strict; |
|
10
|
|
|
|
|
106
|
|
|
10
|
|
|
|
|
307
|
|
3
|
10
|
|
|
10
|
|
54
|
use warnings; |
|
10
|
|
|
|
|
19
|
|
|
10
|
|
|
|
|
267
|
|
4
|
|
|
|
|
|
|
|
5
|
|
|
|
|
|
|
# no "use parent ..." here -- the inheritance is specified dynamically in the |
6
|
|
|
|
|
|
|
# import() method -- inheriting either from SQL::Abstract or SQL::Abstract::Classic |
7
|
|
|
|
|
|
|
|
8
|
10
|
|
|
10
|
|
5252
|
use MRO::Compat; |
|
10
|
|
|
|
|
20989
|
|
|
10
|
|
|
|
|
326
|
|
9
|
10
|
|
|
10
|
|
74
|
use mro 'c3'; # implements next::method |
|
10
|
|
|
|
|
21
|
|
|
10
|
|
|
|
|
52
|
|
10
|
|
|
|
|
|
|
|
11
|
10
|
|
|
|
|
952
|
use Params::Validate qw/validate SCALAR SCALARREF CODEREF ARRAYREF HASHREF |
12
|
10
|
|
|
10
|
|
6219
|
UNDEF BOOLEAN/; |
|
10
|
|
|
|
|
94321
|
|
13
|
10
|
|
|
10
|
|
83
|
use Scalar::Util qw/blessed reftype/; |
|
10
|
|
|
|
|
22
|
|
|
10
|
|
|
|
|
651
|
|
14
|
|
|
|
|
|
|
|
15
|
|
|
|
|
|
|
|
16
|
|
|
|
|
|
|
# remove all previously defined or imported functions |
17
|
10
|
|
|
10
|
|
5429
|
use namespace::clean; |
|
10
|
|
|
|
|
98308
|
|
|
10
|
|
|
|
|
68
|
|
18
|
|
|
|
|
|
|
|
19
|
|
|
|
|
|
|
# declare error-reporting functions from SQL::Abstract |
20
|
|
|
|
|
|
|
sub puke(@); sub belch(@); # these will be defined later in import() |
21
|
|
|
|
|
|
|
|
22
|
|
|
|
|
|
|
|
23
|
|
|
|
|
|
|
our $VERSION = '1.37'; |
24
|
|
|
|
|
|
|
our @ISA; |
25
|
|
|
|
|
|
|
|
26
|
|
|
|
|
|
|
sub import { |
27
|
13
|
|
|
13
|
|
1498
|
my $class = shift; |
28
|
|
|
|
|
|
|
|
29
|
|
|
|
|
|
|
# parent class specified from environment variable, or default value |
30
|
13
|
|
50
|
|
|
112
|
my $parent_sqla = $ENV{SQL_ABSTRACT_MORE_EXTENDS} || 'SQL::Abstract'; |
31
|
|
|
|
|
|
|
|
32
|
|
|
|
|
|
|
# parent class specified through -extends => .. when calling import() |
33
|
13
|
100
|
66
|
|
|
81
|
$parent_sqla = $_[1] if @_ >= 2 && $_[0] eq '-extends'; |
34
|
|
|
|
|
|
|
|
35
|
|
|
|
|
|
|
# syntactic sugar : 'Classic' is expanded into SQLA::Classic |
36
|
13
|
100
|
|
|
|
46
|
$parent_sqla = 'SQL::Abstract::Classic' if $parent_sqla eq 'Classic'; |
37
|
|
|
|
|
|
|
|
38
|
|
|
|
|
|
|
# make sure that import() does never get called with different parents |
39
|
13
|
100
|
|
|
|
43
|
if (my $already_isa = $ISA[0]) { |
40
|
3
|
100
|
|
|
|
27
|
$already_isa eq $parent_sqla |
41
|
|
|
|
|
|
|
or die "cannot use SQL::Abstract::More -extends => '$parent_sqla', " |
42
|
|
|
|
|
|
|
. "this module was already loaded with -extends => '$already_isa'"; |
43
|
|
|
|
|
|
|
|
44
|
|
|
|
|
|
|
# the rest of the import() job was already performed, so just return from here |
45
|
2
|
|
|
|
|
38
|
return; |
46
|
|
|
|
|
|
|
} |
47
|
|
|
|
|
|
|
|
48
|
|
|
|
|
|
|
# load the parent, inherit from it, import puke() and belch() |
49
|
10
|
|
|
10
|
|
5549
|
eval qq{use parent '$parent_sqla'; |
|
10
|
|
|
|
|
3395
|
|
|
10
|
|
|
|
|
98
|
|
|
10
|
|
|
|
|
794
|
|
50
|
|
|
|
|
|
|
*puke = \\&${parent_sqla}::puke; |
51
|
|
|
|
|
|
|
*belch = \\&${parent_sqla}::belch; |
52
|
|
|
|
|
|
|
}; |
53
|
|
|
|
|
|
|
|
54
|
|
|
|
|
|
|
# local override of some methods for insert() and update() |
55
|
10
|
|
|
|
|
76
|
_setup_insert_inheritance($parent_sqla); |
56
|
10
|
|
|
|
|
74
|
_setup_update_inheritance($parent_sqla); |
57
|
|
|
|
|
|
|
} |
58
|
|
|
|
|
|
|
|
59
|
|
|
|
|
|
|
|
60
|
|
|
|
|
|
|
|
61
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
62
|
|
|
|
|
|
|
# Utility functions -- not methods -- declared _after_ |
63
|
|
|
|
|
|
|
# namespace::clean so that they can remain visible by external |
64
|
|
|
|
|
|
|
# modules. In particular, DBIx::DataModel imports these functions. |
65
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
66
|
|
|
|
|
|
|
|
67
|
|
|
|
|
|
|
# shallow_clone(): copies of the top-level keys and values, blessed into the same class |
68
|
|
|
|
|
|
|
sub shallow_clone { |
69
|
5
|
|
|
5
|
1
|
28
|
my ($orig, %override) = @_; |
70
|
|
|
|
|
|
|
|
71
|
5
|
50
|
|
|
|
28
|
my $class = ref $orig |
72
|
|
|
|
|
|
|
or puke "arg must be an object"; |
73
|
5
|
|
|
|
|
74
|
my $clone = {%$orig, %override}; |
74
|
5
|
|
|
|
|
24
|
return bless $clone, $class; |
75
|
|
|
|
|
|
|
} |
76
|
|
|
|
|
|
|
|
77
|
|
|
|
|
|
|
|
78
|
|
|
|
|
|
|
# does(): cheap version of Scalar::Does |
79
|
|
|
|
|
|
|
my %meth_for = ( |
80
|
|
|
|
|
|
|
ARRAY => '@{}', |
81
|
|
|
|
|
|
|
HASH => '%{}', |
82
|
|
|
|
|
|
|
SCALAR => '${}', |
83
|
|
|
|
|
|
|
CODE => '&{}', |
84
|
|
|
|
|
|
|
); |
85
|
|
|
|
|
|
|
sub does ($$) { |
86
|
190
|
|
|
190
|
1
|
434
|
my ($data, $type) = @_; |
87
|
190
|
|
|
|
|
530
|
my $reft = reftype $data; |
88
|
|
|
|
|
|
|
return defined $reft && $reft eq $type |
89
|
190
|
|
66
|
|
|
1478
|
|| blessed $data && overload::Method($data, $meth_for{$type}); |
90
|
|
|
|
|
|
|
} |
91
|
|
|
|
|
|
|
|
92
|
|
|
|
|
|
|
|
93
|
|
|
|
|
|
|
|
94
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
95
|
|
|
|
|
|
|
# global variables |
96
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
97
|
|
|
|
|
|
|
|
98
|
|
|
|
|
|
|
# builtin methods for "Limit-Offset" dialects |
99
|
|
|
|
|
|
|
my %limit_offset_dialects = ( |
100
|
|
|
|
|
|
|
LimitOffset => sub {my ($self, $limit, $offset) = @_; |
101
|
|
|
|
|
|
|
$offset ||= 0; |
102
|
|
|
|
|
|
|
return "LIMIT ? OFFSET ?", $limit, $offset;}, |
103
|
|
|
|
|
|
|
LimitXY => sub {my ($self, $limit, $offset) = @_; |
104
|
|
|
|
|
|
|
$offset ||= 0; |
105
|
|
|
|
|
|
|
return "LIMIT ?, ?", $offset, $limit;}, |
106
|
|
|
|
|
|
|
LimitYX => sub {my ($self, $limit, $offset) = @_; |
107
|
|
|
|
|
|
|
$offset ||= 0; |
108
|
|
|
|
|
|
|
return "LIMIT ?, ?", $limit, $offset;}, |
109
|
|
|
|
|
|
|
RowNum => sub { |
110
|
|
|
|
|
|
|
my ($self, $limit, $offset) = @_; |
111
|
|
|
|
|
|
|
# HACK below borrowed from SQL::Abstract::Limit. Not perfect, though, |
112
|
|
|
|
|
|
|
# because it brings back an additional column. Should borrow from |
113
|
|
|
|
|
|
|
# DBIx::Class::SQLMaker::LimitDialects, which does the proper job ... |
114
|
|
|
|
|
|
|
# but it says : "!!! THIS IS ALSO HORRIFIC !!! /me ashamed"; so |
115
|
|
|
|
|
|
|
# I'll only take it as last resort; still exploring other ways. |
116
|
|
|
|
|
|
|
# See also L : within that ORM an additional layer is |
117
|
|
|
|
|
|
|
# added to take advantage of Oracle scrollable cursors. |
118
|
|
|
|
|
|
|
my $sql = "SELECT * FROM (" |
119
|
|
|
|
|
|
|
. "SELECT subq_A.*, ROWNUM rownum__index FROM (%s) subq_A " |
120
|
|
|
|
|
|
|
. "WHERE ROWNUM <= ?" |
121
|
|
|
|
|
|
|
. ") subq_B WHERE rownum__index >= ?"; |
122
|
|
|
|
|
|
|
|
123
|
10
|
|
|
10
|
|
8470
|
no warnings 'uninitialized'; # in case $limit or $offset is undef |
|
10
|
|
|
|
|
24
|
|
|
10
|
|
|
|
|
61496
|
|
124
|
|
|
|
|
|
|
# row numbers start at 1 |
125
|
|
|
|
|
|
|
return $sql, $offset + $limit, $offset + 1; |
126
|
|
|
|
|
|
|
}, |
127
|
|
|
|
|
|
|
); |
128
|
|
|
|
|
|
|
|
129
|
|
|
|
|
|
|
# builtin join operators with associated sprintf syntax |
130
|
|
|
|
|
|
|
my %common_join_syntax = ( |
131
|
|
|
|
|
|
|
'<=>' => '%s INNER JOIN %s ON %s', |
132
|
|
|
|
|
|
|
'=>' => '%s LEFT OUTER JOIN %s ON %s', |
133
|
|
|
|
|
|
|
'<=' => '%s RIGHT OUTER JOIN %s ON %s', |
134
|
|
|
|
|
|
|
'==' => '%s NATURAL JOIN %s', |
135
|
|
|
|
|
|
|
'>=<' => '%s FULL OUTER JOIN %s ON %s', |
136
|
|
|
|
|
|
|
); |
137
|
|
|
|
|
|
|
my %right_assoc_join_syntax = %common_join_syntax; |
138
|
|
|
|
|
|
|
s/JOIN %s/JOIN (%s)/ foreach values %right_assoc_join_syntax; |
139
|
|
|
|
|
|
|
|
140
|
|
|
|
|
|
|
# specification of parameters accepted by the new() method |
141
|
|
|
|
|
|
|
my %params_for_new = ( |
142
|
|
|
|
|
|
|
table_alias => {type => SCALAR|CODEREF, default => '%s AS %s'}, |
143
|
|
|
|
|
|
|
column_alias => {type => SCALAR|CODEREF, default => '%s AS %s'}, |
144
|
|
|
|
|
|
|
limit_offset => {type => SCALAR|CODEREF, default => 'LimitOffset'}, |
145
|
|
|
|
|
|
|
join_syntax => {type => HASHREF, default => |
146
|
|
|
|
|
|
|
\%common_join_syntax}, |
147
|
|
|
|
|
|
|
join_assoc_right => {type => BOOLEAN, default => 0}, |
148
|
|
|
|
|
|
|
max_members_IN => {type => SCALAR, optional => 1}, |
149
|
|
|
|
|
|
|
multicols_sep => {type => SCALAR|SCALARREF, optional => 1}, |
150
|
|
|
|
|
|
|
has_multicols_in_SQL => {type => BOOLEAN, optional => 1}, |
151
|
|
|
|
|
|
|
sql_dialect => {type => SCALAR, optional => 1}, |
152
|
|
|
|
|
|
|
select_implicitly_for=> {type => SCALAR|UNDEF, optional => 1}, |
153
|
|
|
|
|
|
|
); |
154
|
|
|
|
|
|
|
|
155
|
|
|
|
|
|
|
# builtin collection of parameters, for various databases |
156
|
|
|
|
|
|
|
my %sql_dialects = ( |
157
|
|
|
|
|
|
|
MsAccess => { join_assoc_right => 1, |
158
|
|
|
|
|
|
|
join_syntax => \%right_assoc_join_syntax}, |
159
|
|
|
|
|
|
|
BasisJDBC => { column_alias => "%s %s", |
160
|
|
|
|
|
|
|
max_members_IN => 255 }, |
161
|
|
|
|
|
|
|
MySQL_old => { limit_offset => "LimitXY" }, |
162
|
|
|
|
|
|
|
Oracle => { limit_offset => "RowNum", |
163
|
|
|
|
|
|
|
max_members_IN => 999, |
164
|
|
|
|
|
|
|
table_alias => '%s %s', |
165
|
|
|
|
|
|
|
column_alias => '%s %s', |
166
|
|
|
|
|
|
|
has_multicols_in_SQL => 1, }, |
167
|
|
|
|
|
|
|
); |
168
|
|
|
|
|
|
|
|
169
|
|
|
|
|
|
|
|
170
|
|
|
|
|
|
|
# operators for compound queries |
171
|
|
|
|
|
|
|
my @set_operators = qw/union union_all intersect minus except/; |
172
|
|
|
|
|
|
|
|
173
|
|
|
|
|
|
|
# specification of parameters accepted by select, insert, update, delete |
174
|
|
|
|
|
|
|
my %params_for_select = ( |
175
|
|
|
|
|
|
|
-columns => {type => SCALAR|ARRAYREF, default => '*'}, |
176
|
|
|
|
|
|
|
-from => {type => SCALAR|SCALARREF|ARRAYREF}, |
177
|
|
|
|
|
|
|
-where => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
178
|
|
|
|
|
|
|
(map {-$_ => {type => ARRAYREF, optional => 1}} @set_operators), |
179
|
|
|
|
|
|
|
-group_by => {type => SCALAR|ARRAYREF, optional => 1}, |
180
|
|
|
|
|
|
|
-having => {type => SCALAR|ARRAYREF|HASHREF, optional => 1, |
181
|
|
|
|
|
|
|
depends => '-group_by'}, |
182
|
|
|
|
|
|
|
-order_by => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
183
|
|
|
|
|
|
|
-page_size => {type => SCALAR, optional => 1}, |
184
|
|
|
|
|
|
|
-page_index => {type => SCALAR, optional => 1, |
185
|
|
|
|
|
|
|
depends => '-page_size'}, |
186
|
|
|
|
|
|
|
-limit => {type => SCALAR, optional => 1}, |
187
|
|
|
|
|
|
|
-offset => {type => SCALAR, optional => 1, |
188
|
|
|
|
|
|
|
depends => '-limit'}, |
189
|
|
|
|
|
|
|
-for => {type => SCALAR|UNDEF, optional => 1}, |
190
|
|
|
|
|
|
|
-want_details => {type => BOOLEAN, optional => 1}, |
191
|
|
|
|
|
|
|
); |
192
|
|
|
|
|
|
|
my %params_for_insert = ( |
193
|
|
|
|
|
|
|
-into => {type => SCALAR}, |
194
|
|
|
|
|
|
|
-values => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
195
|
|
|
|
|
|
|
-select => {type => HASHREF, optional => 1}, |
196
|
|
|
|
|
|
|
-columns => {type => ARRAYREF, optional => 1}, |
197
|
|
|
|
|
|
|
-returning => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
198
|
|
|
|
|
|
|
-add_sql => {type => SCALAR, optional => 1}, |
199
|
|
|
|
|
|
|
); |
200
|
|
|
|
|
|
|
my %params_for_update = ( |
201
|
|
|
|
|
|
|
-table => {type => SCALAR|SCALARREF|ARRAYREF}, |
202
|
|
|
|
|
|
|
-set => {type => HASHREF}, |
203
|
|
|
|
|
|
|
-where => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
204
|
|
|
|
|
|
|
-order_by => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
205
|
|
|
|
|
|
|
-limit => {type => SCALAR, optional => 1}, |
206
|
|
|
|
|
|
|
-returning => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
207
|
|
|
|
|
|
|
-add_sql => {type => SCALAR, optional => 1}, |
208
|
|
|
|
|
|
|
); |
209
|
|
|
|
|
|
|
my %params_for_delete = ( |
210
|
|
|
|
|
|
|
-from => {type => SCALAR}, |
211
|
|
|
|
|
|
|
-where => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
212
|
|
|
|
|
|
|
-order_by => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, |
213
|
|
|
|
|
|
|
-limit => {type => SCALAR, optional => 1}, |
214
|
|
|
|
|
|
|
-add_sql => {type => SCALAR, optional => 1}, |
215
|
|
|
|
|
|
|
); |
216
|
|
|
|
|
|
|
my %params_for_WITH = ( |
217
|
|
|
|
|
|
|
-table => {type => SCALAR}, |
218
|
|
|
|
|
|
|
-columns => {type => SCALAR|ARRAYREF, optional => 1}, |
219
|
|
|
|
|
|
|
-as_select => {type => HASHREF}, |
220
|
|
|
|
|
|
|
-final_clause => {type => SCALAR, optional => 1}, |
221
|
|
|
|
|
|
|
); |
222
|
|
|
|
|
|
|
|
223
|
|
|
|
|
|
|
|
224
|
|
|
|
|
|
|
|
225
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
226
|
|
|
|
|
|
|
# object creation |
227
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
228
|
|
|
|
|
|
|
|
229
|
|
|
|
|
|
|
sub new { |
230
|
18
|
|
|
18
|
1
|
40617
|
my $class = shift; |
231
|
18
|
100
|
|
|
|
83
|
my %params = does($_[0], 'HASH') ? %{$_[0]} : @_; |
|
1
|
|
|
|
|
6
|
|
232
|
|
|
|
|
|
|
|
233
|
|
|
|
|
|
|
# extract params for this subclass |
234
|
18
|
|
|
|
|
50
|
my %more_params; |
235
|
18
|
|
|
|
|
103
|
foreach my $key (keys %params_for_new) { |
236
|
180
|
100
|
|
|
|
353
|
$more_params{$key} = delete $params{$key} if exists $params{$key}; |
237
|
|
|
|
|
|
|
} |
238
|
|
|
|
|
|
|
|
239
|
|
|
|
|
|
|
# import params from SQL dialect, if any |
240
|
18
|
|
|
|
|
63
|
my $dialect = delete $more_params{sql_dialect}; |
241
|
18
|
100
|
|
|
|
51
|
if ($dialect) { |
242
|
2
|
50
|
|
|
|
7
|
my $dialect_params = $sql_dialects{$dialect} |
243
|
|
|
|
|
|
|
or puke "no such sql dialect: $dialect"; |
244
|
2
|
|
33
|
|
|
26
|
$more_params{$_} ||= $dialect_params->{$_} foreach keys %$dialect_params; |
245
|
|
|
|
|
|
|
} |
246
|
|
|
|
|
|
|
|
247
|
|
|
|
|
|
|
# check parameters for this class |
248
|
18
|
|
|
|
|
58
|
my @more_params = %more_params; |
249
|
18
|
|
|
|
|
613
|
my $more_self = validate(@more_params, \%params_for_new); |
250
|
|
|
|
|
|
|
|
251
|
|
|
|
|
|
|
# check some of the params for parent -- because SQLA doesn't do it :-( |
252
|
|
|
|
|
|
|
!$params{quote_char} || exists $params{name_sep} |
253
|
18
|
50
|
66
|
|
|
128
|
or belch "when 'quote_char' is present, 'name_sep' should be present too"; |
254
|
|
|
|
|
|
|
|
255
|
|
|
|
|
|
|
# call parent constructor |
256
|
18
|
|
|
|
|
108
|
my $self = $class->next::method(%params); |
257
|
|
|
|
|
|
|
|
258
|
|
|
|
|
|
|
# inject into $self |
259
|
18
|
|
|
|
|
5349
|
$self->{$_} = $more_self->{$_} foreach keys %$more_self; |
260
|
|
|
|
|
|
|
|
261
|
|
|
|
|
|
|
# arguments supplied as scalars are transformed into coderefs |
262
|
18
|
50
|
|
|
|
156
|
ref $self->{column_alias} or $self->_make_sub_column_alias; |
263
|
18
|
50
|
|
|
|
141
|
ref $self->{table_alias} or $self->_make_sub_table_alias; |
264
|
18
|
100
|
|
|
|
90
|
ref $self->{limit_offset} or $self->_choose_LIMIT_OFFSET_dialect; |
265
|
|
|
|
|
|
|
|
266
|
|
|
|
|
|
|
# regex for parsing join specifications |
267
|
126
|
50
|
|
|
|
301
|
my @join_ops = sort {length($b) <=> length($a) || $a cmp $b} |
268
|
18
|
|
|
|
|
34
|
keys %{$self->{join_syntax}}; |
|
18
|
|
|
|
|
115
|
|
269
|
18
|
|
|
|
|
134
|
my $joined_ops = join '|', map quotemeta, @join_ops; |
270
|
18
|
|
|
|
|
646
|
$self->{join_regex} = qr[ |
271
|
|
|
|
|
|
|
^ # initial anchor |
272
|
|
|
|
|
|
|
($joined_ops)? # $1: join operator (i.e. '<=>', '=>', etc.)) |
273
|
|
|
|
|
|
|
([[{])? # $2: opening '[' or '{' |
274
|
|
|
|
|
|
|
(.*?) # $3: content of brackets |
275
|
|
|
|
|
|
|
[]}]? # closing ']' or '}' |
276
|
|
|
|
|
|
|
$ # final anchor |
277
|
|
|
|
|
|
|
]x; |
278
|
|
|
|
|
|
|
|
279
|
18
|
|
|
|
|
161
|
return $self; |
280
|
|
|
|
|
|
|
} |
281
|
|
|
|
|
|
|
|
282
|
|
|
|
|
|
|
|
283
|
|
|
|
|
|
|
|
284
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
285
|
|
|
|
|
|
|
# support for WITH or WITH RECURSIVE |
286
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
287
|
|
|
|
|
|
|
|
288
|
|
|
|
|
|
|
sub with_recursive { |
289
|
5
|
|
|
5
|
1
|
46386
|
my $self = shift; |
290
|
|
|
|
|
|
|
|
291
|
5
|
|
|
|
|
18
|
my $new_instance = $self->with(@_); |
292
|
5
|
|
|
|
|
35
|
$new_instance->{WITH}{sql} =~ s/^WITH\b/WITH RECURSIVE/; |
293
|
|
|
|
|
|
|
|
294
|
5
|
|
|
|
|
28
|
return $new_instance; |
295
|
|
|
|
|
|
|
} |
296
|
|
|
|
|
|
|
|
297
|
|
|
|
|
|
|
sub with { |
298
|
5
|
|
|
5
|
1
|
8
|
my $self = shift; |
299
|
|
|
|
|
|
|
|
300
|
|
|
|
|
|
|
! $self->{WITH} |
301
|
5
|
50
|
|
|
|
17
|
or puke "calls to the with() or with_recursive() method cannot be chained"; |
302
|
|
|
|
|
|
|
|
303
|
|
|
|
|
|
|
@_ |
304
|
5
|
50
|
|
|
|
18
|
or puke "->with() : missing arguments"; |
305
|
|
|
|
|
|
|
|
306
|
|
|
|
|
|
|
# create a copy of the current object with an additional attribute WITH |
307
|
5
|
|
|
|
|
23
|
my $clone = shallow_clone($self, WITH => {sql => "", bind => []}); |
308
|
|
|
|
|
|
|
|
309
|
|
|
|
|
|
|
# assemble SQL and bind values for each table expression |
310
|
5
|
100
|
|
|
|
13
|
my @table_expressions = does($_[0], 'ARRAY') ? @_ : ( [ @_]); |
311
|
5
|
|
|
|
|
13
|
foreach my $table_expression (@table_expressions) { |
312
|
6
|
|
|
|
|
165
|
my %args = validate(@$table_expression, \%params_for_WITH); |
313
|
6
|
|
|
|
|
28
|
my ($sql, @bind) = $self->select(%{$args{-as_select}}); |
|
6
|
|
|
|
|
29
|
|
314
|
6
|
100
|
|
|
|
26
|
$clone->{WITH}{sql} .= ", " if $clone->{WITH}{sql}; |
315
|
6
|
|
|
|
|
12
|
$clone->{WITH}{sql} .= $args{-table}; |
316
|
6
|
50
|
|
|
|
28
|
$clone->{WITH}{sql} .= "(" . join(", ", @{$args{-columns}}) . ")" if $args{-columns}; |
|
6
|
|
|
|
|
21
|
|
317
|
6
|
|
|
|
|
18
|
$clone->{WITH}{sql} .= " AS ($sql) "; |
318
|
6
|
100
|
|
|
|
16
|
$clone->{WITH}{sql} .= $args{-final_clause} . " " if $args{-final_clause}; |
319
|
6
|
|
|
|
|
8
|
push @{$clone->{WITH}{bind}}, @bind; |
|
6
|
|
|
|
|
23
|
|
320
|
|
|
|
|
|
|
} |
321
|
|
|
|
|
|
|
|
322
|
|
|
|
|
|
|
# add the initial keyword WITH |
323
|
5
|
|
|
|
|
14
|
substr($clone->{WITH}{sql}, 0, 0, 'WITH '); |
324
|
|
|
|
|
|
|
|
325
|
5
|
|
|
|
|
13
|
return $clone; |
326
|
|
|
|
|
|
|
} |
327
|
|
|
|
|
|
|
|
328
|
|
|
|
|
|
|
|
329
|
|
|
|
|
|
|
sub _prepend_WITH_clause { |
330
|
85
|
|
|
85
|
|
167
|
my ($self, $ref_sql, $ref_bind) = @_; |
331
|
|
|
|
|
|
|
|
332
|
85
|
100
|
|
|
|
232
|
return if !$self->{WITH}; |
333
|
|
|
|
|
|
|
|
334
|
9
|
|
|
|
|
43
|
substr($$ref_sql, 0, 0, $self->{WITH}{sql}); |
335
|
9
|
|
|
|
|
23
|
unshift @$ref_bind, @{$self->{WITH}{bind}}; |
|
9
|
|
|
|
|
24
|
|
336
|
|
|
|
|
|
|
|
337
|
|
|
|
|
|
|
} |
338
|
|
|
|
|
|
|
|
339
|
|
|
|
|
|
|
|
340
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
341
|
|
|
|
|
|
|
# the select method |
342
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
343
|
|
|
|
|
|
|
|
344
|
|
|
|
|
|
|
sub select { |
345
|
59
|
|
|
59
|
1
|
145972
|
my $self = shift; |
346
|
|
|
|
|
|
|
|
347
|
|
|
|
|
|
|
# if got positional args, this is not our job, just delegate to the parent |
348
|
59
|
100
|
|
|
|
157
|
return $self->next::method(@_) if !&_called_with_named_args; |
349
|
|
|
|
|
|
|
|
350
|
58
|
|
|
|
|
121
|
my %aliased_columns; |
351
|
|
|
|
|
|
|
|
352
|
|
|
|
|
|
|
# parse arguments |
353
|
58
|
|
|
|
|
2257
|
my %args = validate(@_, \%params_for_select); |
354
|
|
|
|
|
|
|
|
355
|
|
|
|
|
|
|
# compute join info if the datasource is a join |
356
|
58
|
|
|
|
|
443
|
my $join_info = $self->_compute_join_info($args{-from}); |
357
|
58
|
100
|
|
|
|
144
|
$args{-from} = \($join_info->{sql}) if $join_info; |
358
|
|
|
|
|
|
|
|
359
|
|
|
|
|
|
|
# reorganize columns; initial members starting with "-" are extracted |
360
|
|
|
|
|
|
|
# into a separate list @post_select, later re-injected into the SQL |
361
|
58
|
100
|
|
|
|
188
|
my @cols = ref $args{-columns} ? @{$args{-columns}} : $args{-columns}; |
|
33
|
|
|
|
|
92
|
|
362
|
58
|
|
|
|
|
105
|
my @post_select; |
363
|
58
|
|
66
|
|
|
330
|
push @post_select, shift @cols while @cols && $cols[0] =~ s/^-//; |
364
|
58
|
|
|
|
|
128
|
foreach my $col (@cols) { |
365
|
|
|
|
|
|
|
# extract alias, if any |
366
|
82
|
100
|
|
|
|
268
|
if ($col =~ /^\s* # ignore insignificant leading spaces |
367
|
|
|
|
|
|
|
(.*[^|\s]) # any non-empty string, not ending with ' ' or '|' |
368
|
|
|
|
|
|
|
\| # followed by a literal '|' |
369
|
|
|
|
|
|
|
(\w+) # followed by a word (the alias)) |
370
|
|
|
|
|
|
|
\s* # ignore insignificant trailing spaces |
371
|
|
|
|
|
|
|
$/x) { |
372
|
14
|
|
|
|
|
57
|
$aliased_columns{$2} = $1; |
373
|
14
|
|
|
|
|
36
|
$col = $self->column_alias($1, $2); |
374
|
|
|
|
|
|
|
} |
375
|
|
|
|
|
|
|
} |
376
|
58
|
|
|
|
|
147
|
$args{-columns} = \@cols; |
377
|
|
|
|
|
|
|
|
378
|
|
|
|
|
|
|
# reorganize pagination |
379
|
58
|
100
|
66
|
|
|
264
|
if ($args{-page_index} || $args{-page_size}) { |
380
|
|
|
|
|
|
|
not exists $args{$_} or puke "-page_size conflicts with $_" |
381
|
1
|
|
33
|
|
|
7
|
for qw/-limit -offset/; |
382
|
1
|
|
|
|
|
5
|
$args{-limit} = $args{-page_size}; |
383
|
1
|
50
|
|
|
|
4
|
if ($args{-page_index}) { |
384
|
1
|
|
|
|
|
5
|
$args{-offset} = ($args{-page_index} - 1) * $args{-page_size}; |
385
|
|
|
|
|
|
|
} |
386
|
|
|
|
|
|
|
} |
387
|
|
|
|
|
|
|
|
388
|
|
|
|
|
|
|
# generate initial ($sql, @bind), without -order_by (will be handled later) |
389
|
58
|
|
|
|
|
188
|
my @old_API_args = @args{qw/-from -columns -where/}; # |
390
|
58
|
|
|
|
|
254
|
my ($sql, @bind) = $self->next::method(@old_API_args); |
391
|
58
|
100
|
|
|
|
57400
|
unshift @bind, @{$join_info->{bind}} if $join_info; |
|
11
|
|
|
|
|
35
|
|
392
|
|
|
|
|
|
|
|
393
|
|
|
|
|
|
|
# add @post_select clauses if needed (for ex. -distinct) |
394
|
58
|
|
|
|
|
131
|
my $post_select = join " ", @post_select; |
395
|
58
|
100
|
|
|
|
179
|
$sql =~ s[^SELECT ][SELECT $post_select ]i if $post_select; |
396
|
|
|
|
|
|
|
|
397
|
|
|
|
|
|
|
# add set operators (UNION, INTERSECT, etc) if needed |
398
|
58
|
|
|
|
|
137
|
foreach my $set_op (@set_operators) { |
399
|
290
|
100
|
|
|
|
669
|
if ($args{-$set_op}) { |
400
|
11
|
|
|
|
|
21
|
my %sub_args = @{$args{-$set_op}}; |
|
11
|
|
|
|
|
47
|
|
401
|
11
|
|
66
|
|
|
62
|
$sub_args{$_} ||= $args{$_} for qw/-columns -from/; |
402
|
11
|
|
|
|
|
72
|
local $self->{WITH}; # temporarily disable the WITH part during the subquery |
403
|
11
|
|
|
|
|
177
|
my ($sql1, @bind1) = $self->select(%sub_args); |
404
|
11
|
|
|
|
|
53
|
(my $sql_op = uc($set_op)) =~ s/_/ /g; |
405
|
11
|
|
|
|
|
46
|
$sql .= " $sql_op $sql1"; |
406
|
11
|
|
|
|
|
42
|
push @bind, @bind1; |
407
|
|
|
|
|
|
|
} |
408
|
|
|
|
|
|
|
} |
409
|
|
|
|
|
|
|
|
410
|
|
|
|
|
|
|
# add GROUP BY/HAVING if needed |
411
|
58
|
100
|
|
|
|
145
|
if ($args{-group_by}) { |
412
|
4
|
|
|
|
|
50
|
my $sql_grp = $self->where(undef, $args{-group_by}); |
413
|
4
|
|
|
|
|
1054
|
$sql_grp =~ s/\bORDER\b/GROUP/; |
414
|
4
|
100
|
|
|
|
22
|
if ($args{-having}) { |
415
|
2
|
|
|
|
|
8
|
my ($sql_having, @bind_having) = $self->where($args{-having}); |
416
|
2
|
|
|
|
|
924
|
$sql_having =~ s/\bWHERE\b/HAVING/; |
417
|
2
|
|
|
|
|
10
|
$sql_grp .= " $sql_having"; |
418
|
2
|
|
|
|
|
7
|
push @bind, @bind_having; |
419
|
|
|
|
|
|
|
} |
420
|
4
|
|
|
|
|
12
|
$sql .= $sql_grp; |
421
|
|
|
|
|
|
|
} |
422
|
|
|
|
|
|
|
|
423
|
|
|
|
|
|
|
# add ORDER BY if needed |
424
|
58
|
100
|
|
|
|
152
|
if (my $order = $args{-order_by}) { |
425
|
|
|
|
|
|
|
|
426
|
7
|
|
|
|
|
42
|
my ($sql_order, @orderby_bind) = $self->_order_by($order); |
427
|
7
|
|
|
|
|
2721
|
$sql .= $sql_order; |
428
|
7
|
|
|
|
|
23
|
push @bind, @orderby_bind; |
429
|
|
|
|
|
|
|
} |
430
|
|
|
|
|
|
|
|
431
|
|
|
|
|
|
|
# add LIMIT/OFFSET if needed |
432
|
58
|
100
|
|
|
|
137
|
if (defined $args{-limit}) { |
433
|
|
|
|
|
|
|
my ($limit_sql, @limit_bind) |
434
|
5
|
|
|
|
|
17
|
= $self->limit_offset(@args{qw/-limit -offset/}); |
435
|
5
|
100
|
|
|
|
26
|
$sql = $limit_sql =~ /%s/ ? sprintf $limit_sql, $sql |
436
|
|
|
|
|
|
|
: "$sql $limit_sql"; |
437
|
5
|
|
|
|
|
14
|
push @bind, @limit_bind; |
438
|
|
|
|
|
|
|
} |
439
|
|
|
|
|
|
|
|
440
|
|
|
|
|
|
|
# add FOR clause if needed |
441
|
58
|
100
|
|
|
|
154
|
my $for = exists $args{-for} ? $args{-for} : $self->{select_implicitly_for}; |
442
|
58
|
100
|
|
|
|
117
|
$sql .= " FOR $for" if $for; |
443
|
|
|
|
|
|
|
|
444
|
|
|
|
|
|
|
# initial WITH clause |
445
|
58
|
|
|
|
|
204
|
$self->_prepend_WITH_clause(\$sql, \@bind); |
446
|
|
|
|
|
|
|
|
447
|
|
|
|
|
|
|
# return results |
448
|
58
|
100
|
|
|
|
136
|
if ($args{-want_details}) { |
449
|
|
|
|
|
|
|
return {sql => $sql, |
450
|
|
|
|
|
|
|
bind => \@bind, |
451
|
1
|
|
33
|
|
|
14
|
aliased_tables => ($join_info && $join_info->{aliased_tables}), |
452
|
|
|
|
|
|
|
aliased_columns => \%aliased_columns }; |
453
|
|
|
|
|
|
|
} |
454
|
|
|
|
|
|
|
else { |
455
|
57
|
|
|
|
|
395
|
return ($sql, @bind); |
456
|
|
|
|
|
|
|
} |
457
|
|
|
|
|
|
|
} |
458
|
|
|
|
|
|
|
|
459
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
460
|
|
|
|
|
|
|
# insert |
461
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
462
|
|
|
|
|
|
|
|
463
|
|
|
|
|
|
|
sub _setup_insert_inheritance { |
464
|
10
|
|
|
10
|
|
38
|
my ($parent_sqla) = @_; |
465
|
|
|
|
|
|
|
|
466
|
|
|
|
|
|
|
# if the parent has method '_expand_insert_value' (SQL::Abstract >= v2.0), |
467
|
|
|
|
|
|
|
# we need to override it in this subclass |
468
|
10
|
50
|
|
|
|
165
|
if ($parent_sqla->can('_expand_insert_value')) { |
|
|
0
|
|
|
|
|
|
469
|
|
|
|
|
|
|
*_expand_insert_value = sub { |
470
|
17
|
|
|
17
|
|
6344
|
my ($self, $v) = @_; |
471
|
|
|
|
|
|
|
|
472
|
17
|
|
|
|
|
30
|
my $k = our $Cur_Col_Meta; |
473
|
|
|
|
|
|
|
|
474
|
17
|
100
|
|
|
|
47
|
if (ref($v) eq 'ARRAY') { |
475
|
1
|
50
|
33
|
|
|
7
|
if ($self->{array_datatypes} || $self->is_bind_value_with_type($v)) { |
476
|
1
|
|
|
|
|
11
|
return +{ -bind => [ $k, $v ] }; |
477
|
|
|
|
|
|
|
} |
478
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = @$v; |
479
|
0
|
|
|
|
|
0
|
$self->_assert_bindval_matches_bindtype(@bind); |
480
|
0
|
|
|
|
|
0
|
return +{ -literal => $v }; |
481
|
|
|
|
|
|
|
} |
482
|
16
|
50
|
|
|
|
33
|
if (ref($v) eq 'HASH') { |
483
|
0
|
0
|
|
|
|
0
|
if (grep !/^-/, keys %$v) { |
484
|
0
|
|
|
|
|
0
|
belch "HASH ref as bind value in insert is not supported"; |
485
|
0
|
|
|
|
|
0
|
return +{ -bind => [ $k, $v ] }; |
486
|
|
|
|
|
|
|
} |
487
|
|
|
|
|
|
|
} |
488
|
16
|
50
|
|
|
|
35
|
if (!defined($v)) { |
489
|
0
|
|
|
|
|
0
|
return +{ -bind => [ $k, undef ] }; |
490
|
|
|
|
|
|
|
} |
491
|
16
|
|
|
|
|
39
|
return $self->expand_expr($v); |
492
|
10
|
|
|
|
|
71
|
}; |
493
|
|
|
|
|
|
|
} |
494
|
|
|
|
|
|
|
|
495
|
|
|
|
|
|
|
# otherwise, if the parent is an old SQL::Abstract or it is SQL::Abstract::Classic |
496
|
|
|
|
|
|
|
elsif ($parent_sqla->can('_insert_values')) { |
497
|
|
|
|
|
|
|
|
498
|
|
|
|
|
|
|
# if the parent has no method '_insert_value', this is the old |
499
|
|
|
|
|
|
|
# monolithic _insert_values() method. We must override it |
500
|
0
|
0
|
|
|
|
0
|
if (!$parent_sqla->can('_insert_value')) { |
501
|
|
|
|
|
|
|
*_insert_values = sub { |
502
|
0
|
|
|
0
|
|
0
|
my ($self, $data) = @_; |
503
|
|
|
|
|
|
|
|
504
|
0
|
|
|
|
|
0
|
my (@values, @all_bind); |
505
|
0
|
|
|
|
|
0
|
foreach my $column (sort keys %$data) { |
506
|
0
|
|
|
|
|
0
|
my ($values, @bind) = $self->_insert_value($column, $data->{$column}); |
507
|
0
|
|
|
|
|
0
|
push @values, $values; |
508
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
509
|
|
|
|
|
|
|
} |
510
|
0
|
|
|
|
|
0
|
my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )"; |
511
|
0
|
|
|
|
|
0
|
return ($sql, @all_bind); |
512
|
0
|
|
|
|
|
0
|
}; |
513
|
|
|
|
|
|
|
} |
514
|
|
|
|
|
|
|
|
515
|
|
|
|
|
|
|
# now override the _insert_value() method |
516
|
|
|
|
|
|
|
*_insert_value = sub { |
517
|
|
|
|
|
|
|
|
518
|
|
|
|
|
|
|
# unfortunately, we can't just override the ARRAYREF part, so the whole |
519
|
|
|
|
|
|
|
# parent method is copied here |
520
|
|
|
|
|
|
|
|
521
|
0
|
|
|
0
|
|
0
|
my ($self, $column, $v) = @_; |
522
|
|
|
|
|
|
|
|
523
|
0
|
|
|
|
|
0
|
my (@values, @all_bind); |
524
|
|
|
|
|
|
|
$self->_SWITCH_refkind($v, { |
525
|
|
|
|
|
|
|
|
526
|
|
|
|
|
|
|
ARRAYREF => sub { |
527
|
0
|
0
|
0
|
|
|
0
|
if ($self->{array_datatypes} # if array datatype are activated |
528
|
|
|
|
|
|
|
|| $self->is_bind_value_with_type($v)) { # or if this is a bind val |
529
|
0
|
|
|
|
|
0
|
push @values, '?'; |
530
|
0
|
|
|
|
|
0
|
push @all_bind, $self->_bindtype($column, $v); |
531
|
|
|
|
|
|
|
} |
532
|
|
|
|
|
|
|
else { # else literal SQL with bind |
533
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = @$v; |
534
|
0
|
|
|
|
|
0
|
$self->_assert_bindval_matches_bindtype(@bind); |
535
|
0
|
|
|
|
|
0
|
push @values, $sql; |
536
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
537
|
|
|
|
|
|
|
} |
538
|
|
|
|
|
|
|
}, |
539
|
|
|
|
|
|
|
|
540
|
|
|
|
|
|
|
ARRAYREFREF => sub { # literal SQL with bind |
541
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = @${$v}; |
|
0
|
|
|
|
|
0
|
|
542
|
0
|
|
|
|
|
0
|
$self->_assert_bindval_matches_bindtype(@bind); |
543
|
0
|
|
|
|
|
0
|
push @values, $sql; |
544
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
545
|
|
|
|
|
|
|
}, |
546
|
|
|
|
|
|
|
|
547
|
|
|
|
|
|
|
# THINK : anything useful to do with a HASHREF ? |
548
|
|
|
|
|
|
|
HASHREF => sub { # (nothing, but old SQLA passed it through) |
549
|
|
|
|
|
|
|
#TODO in SQLA >= 2.0 it will die instead |
550
|
0
|
|
|
|
|
0
|
belch "HASH ref as bind value in insert is not supported"; |
551
|
0
|
|
|
|
|
0
|
push @values, '?'; |
552
|
0
|
|
|
|
|
0
|
push @all_bind, $self->_bindtype($column, $v); |
553
|
|
|
|
|
|
|
}, |
554
|
|
|
|
|
|
|
|
555
|
|
|
|
|
|
|
SCALARREF => sub { # literal SQL without bind |
556
|
0
|
|
|
|
|
0
|
push @values, $$v; |
557
|
|
|
|
|
|
|
}, |
558
|
|
|
|
|
|
|
|
559
|
|
|
|
|
|
|
SCALAR_or_UNDEF => sub { |
560
|
0
|
|
|
|
|
0
|
push @values, '?'; |
561
|
0
|
|
|
|
|
0
|
push @all_bind, $self->_bindtype($column, $v); |
562
|
|
|
|
|
|
|
}, |
563
|
|
|
|
|
|
|
|
564
|
0
|
|
|
|
|
0
|
}); |
565
|
|
|
|
|
|
|
|
566
|
0
|
|
|
|
|
0
|
my $sql = CORE::join(", ", @values); |
567
|
0
|
|
|
|
|
0
|
return ($sql, @all_bind); |
568
|
|
|
|
|
|
|
} |
569
|
0
|
|
|
|
|
0
|
} |
570
|
|
|
|
|
|
|
} |
571
|
|
|
|
|
|
|
|
572
|
|
|
|
|
|
|
|
573
|
|
|
|
|
|
|
|
574
|
|
|
|
|
|
|
sub insert { |
575
|
12
|
|
|
12
|
1
|
35456
|
my $self = shift; |
576
|
|
|
|
|
|
|
|
577
|
12
|
|
|
|
|
50
|
my @old_API_args; |
578
|
|
|
|
|
|
|
my $returning_into; |
579
|
12
|
|
|
|
|
0
|
my $sql_to_add; |
580
|
12
|
|
|
|
|
0
|
my $fix_RT134127; |
581
|
|
|
|
|
|
|
|
582
|
12
|
100
|
|
|
|
25
|
if (&_called_with_named_args) { |
583
|
|
|
|
|
|
|
# extract named args and translate to old SQLA API |
584
|
11
|
|
|
|
|
528
|
my %args = validate(@_, \%params_for_insert); |
585
|
|
|
|
|
|
|
$old_API_args[0] = $args{-into} |
586
|
10
|
50
|
|
|
|
76
|
or puke "insert(..) : need -into arg"; |
587
|
|
|
|
|
|
|
|
588
|
10
|
100
|
|
|
|
30
|
if ($args{-values}) { |
|
|
50
|
|
|
|
|
|
589
|
|
|
|
|
|
|
|
590
|
|
|
|
|
|
|
# check mutually exclusive parameters |
591
|
|
|
|
|
|
|
!$args{$_} |
592
|
|
|
|
|
|
|
or puke "insert(-into => .., -values => ...) : cannot use $_ => " |
593
|
8
|
|
33
|
|
|
37
|
for qw/-select -columns/; |
594
|
|
|
|
|
|
|
|
595
|
8
|
|
|
|
|
18
|
$old_API_args[1] = $args{-values}; |
596
|
|
|
|
|
|
|
} |
597
|
|
|
|
|
|
|
elsif ($args{-select}) { |
598
|
2
|
|
|
|
|
7
|
local $self->{WITH}; # temporarily disable the WITH part during the subquery |
599
|
2
|
|
|
|
|
5
|
my ($sql, @bind) = $self->select(%{$args{-select}}); |
|
2
|
|
|
|
|
10
|
|
600
|
2
|
|
|
|
|
9
|
$old_API_args[1] = \ [$sql, @bind]; |
601
|
2
|
50
|
|
|
|
10
|
if (my $cols = $args{-columns}) { |
602
|
2
|
|
|
|
|
9
|
$old_API_args[0] .= "(" . CORE::join(", ", @$cols) . ")"; |
603
|
|
|
|
|
|
|
} |
604
|
2
|
50
|
50
|
|
|
20
|
$fix_RT134127 = 1 if ($SQL::Abstract::VERSION || 0) >= 2.0; |
605
|
|
|
|
|
|
|
} |
606
|
|
|
|
|
|
|
else { |
607
|
0
|
|
|
|
|
0
|
puke "insert(-into => ..) : need either -values arg or -select arg"; |
608
|
|
|
|
|
|
|
} |
609
|
|
|
|
|
|
|
|
610
|
|
|
|
|
|
|
# deal with -returning arg |
611
|
|
|
|
|
|
|
($returning_into, my $old_API_options) |
612
|
10
|
|
|
|
|
41
|
= $self->_compute_returning($args{-returning}); |
613
|
10
|
100
|
|
|
|
30
|
push @old_API_args, $old_API_options if $old_API_options; |
614
|
|
|
|
|
|
|
|
615
|
|
|
|
|
|
|
# SQL to add after the INSERT keyword |
616
|
10
|
|
|
|
|
22
|
$sql_to_add = $args{-add_sql}; |
617
|
|
|
|
|
|
|
} |
618
|
|
|
|
|
|
|
else { |
619
|
1
|
|
|
|
|
4
|
@old_API_args = @_; |
620
|
|
|
|
|
|
|
} |
621
|
|
|
|
|
|
|
|
622
|
|
|
|
|
|
|
# get results from parent method |
623
|
11
|
|
|
|
|
45
|
my ($sql, @bind) = $self->next::method(@old_API_args); |
624
|
|
|
|
|
|
|
|
625
|
|
|
|
|
|
|
# temporary fix for RT#134127 due to a change of behaviour of insert() in SQLA V2.0 |
626
|
|
|
|
|
|
|
# .. waiting for SQLA to fix RT#134128 |
627
|
11
|
100
|
|
|
|
6585
|
$sql =~ s/VALUES SELECT/SELECT/ if $fix_RT134127; |
628
|
|
|
|
|
|
|
|
629
|
|
|
|
|
|
|
# inject more stuff if using Oracle's "RETURNING ... INTO ..." |
630
|
11
|
100
|
|
|
|
35
|
if ($returning_into) { |
631
|
1
|
|
|
|
|
7
|
$sql .= ' INTO ' . join(", ", ("?") x @$returning_into); |
632
|
1
|
|
|
|
|
2
|
push @bind, @$returning_into; |
633
|
|
|
|
|
|
|
} |
634
|
|
|
|
|
|
|
|
635
|
|
|
|
|
|
|
# SQL to add after the INSERT keyword |
636
|
11
|
100
|
|
|
|
41
|
$sql =~ s/\b(INSERT)\b/$1 $sql_to_add/i if $sql_to_add; |
637
|
|
|
|
|
|
|
|
638
|
|
|
|
|
|
|
# initial WITH clause |
639
|
11
|
|
|
|
|
40
|
$self->_prepend_WITH_clause(\$sql, \@bind); |
640
|
|
|
|
|
|
|
|
641
|
11
|
|
|
|
|
65
|
return ($sql, @bind); |
642
|
|
|
|
|
|
|
} |
643
|
|
|
|
|
|
|
|
644
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
645
|
|
|
|
|
|
|
# update |
646
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
647
|
|
|
|
|
|
|
|
648
|
|
|
|
|
|
|
|
649
|
|
|
|
|
|
|
sub _setup_update_inheritance { |
650
|
10
|
|
|
10
|
|
30
|
my ($parent_sqla) = @_; |
651
|
|
|
|
|
|
|
|
652
|
|
|
|
|
|
|
# if the parent has method '_expand_update_set_value' (SQL::Abstract >= v2.0), |
653
|
|
|
|
|
|
|
# we need to override it in this subclass |
654
|
10
|
50
|
|
|
|
60
|
if ($parent_sqla->can('_expand_update_set_values')) { |
655
|
10
|
|
|
|
|
50
|
*_parent_update = $parent_sqla->can('update'); |
656
|
|
|
|
|
|
|
*_expand_update_set_values = sub { |
657
|
11
|
|
|
11
|
|
2413
|
my ($self, undef, $data) = @_; |
658
|
|
|
|
|
|
|
$self->expand_expr({ -list => [ |
659
|
|
|
|
|
|
|
map { |
660
|
16
|
|
|
|
|
972
|
my ($k, $set) = @$_; |
661
|
16
|
50
|
|
|
|
40
|
$set = { -bind => $_ } unless defined $set; |
662
|
16
|
|
|
|
|
83
|
+{ -op => [ '=', { -ident => $k }, $set ] }; |
663
|
|
|
|
|
|
|
} |
664
|
|
|
|
|
|
|
map { |
665
|
11
|
|
|
|
|
43
|
my $k = $_; |
|
16
|
|
|
|
|
517
|
|
666
|
16
|
|
|
|
|
46
|
my $v = $data->{$k}; |
667
|
|
|
|
|
|
|
(ref($v) eq 'ARRAY' |
668
|
|
|
|
|
|
|
? ($self->{array_datatypes} || $self->is_bind_value_with_type($v) |
669
|
|
|
|
|
|
|
? [ $k, +{ -bind => [ $k, $v ] } ] |
670
|
|
|
|
|
|
|
: [ $k, +{ -literal => $v } ]) |
671
|
16
|
50
|
33
|
|
|
45
|
: do { |
|
|
100
|
|
|
|
|
|
672
|
15
|
|
|
|
|
29
|
local our $Cur_Col_Meta = $k; |
673
|
15
|
|
|
|
|
44
|
[ $k, $self->_expand_expr($v) ] |
674
|
|
|
|
|
|
|
} |
675
|
|
|
|
|
|
|
); |
676
|
|
|
|
|
|
|
} sort keys %$data |
677
|
|
|
|
|
|
|
] }); |
678
|
10
|
|
|
|
|
392
|
}; |
679
|
|
|
|
|
|
|
} |
680
|
|
|
|
|
|
|
|
681
|
|
|
|
|
|
|
|
682
|
|
|
|
|
|
|
# otherwise, if the parent is an old SQL::Abstract or it is SQL::Abstract::Classic |
683
|
|
|
|
|
|
|
else { |
684
|
|
|
|
|
|
|
# if the parent has method '_update_set_values()', it is a SQLA version >=1.85. |
685
|
|
|
|
|
|
|
# We can just use its update() method as _parent_update(). |
686
|
0
|
0
|
|
|
|
0
|
if ($parent_sqla->can('_update_set_values')) { |
687
|
0
|
|
|
|
|
0
|
*_parent_update = $parent_sqla->can('update'); |
688
|
|
|
|
|
|
|
} |
689
|
|
|
|
|
|
|
|
690
|
|
|
|
|
|
|
# otherwise, it's the old monolithic update() method. We need to supply our own |
691
|
|
|
|
|
|
|
# version as _parent_update(). |
692
|
|
|
|
|
|
|
else { |
693
|
|
|
|
|
|
|
*_parent_update = sub { |
694
|
0
|
|
|
0
|
|
0
|
my $self = shift; |
695
|
0
|
|
|
|
|
0
|
my $table = $self->_table(shift); |
696
|
0
|
|
0
|
|
|
0
|
my $data = shift || return; |
697
|
0
|
|
|
|
|
0
|
my $where = shift; |
698
|
0
|
|
|
|
|
0
|
my $options = shift; |
699
|
|
|
|
|
|
|
|
700
|
|
|
|
|
|
|
# first build the 'SET' part of the sql statement |
701
|
0
|
0
|
|
|
|
0
|
puke "Unsupported data type specified to \$sql->update" |
702
|
|
|
|
|
|
|
unless ref $data eq 'HASH'; |
703
|
|
|
|
|
|
|
|
704
|
0
|
|
|
|
|
0
|
my ($sql, @all_bind) = $self->_update_set_values($data); |
705
|
0
|
|
|
|
|
0
|
$sql = $self->_sqlcase('update ') . $table . $self->_sqlcase(' set ') |
706
|
|
|
|
|
|
|
. $sql; |
707
|
|
|
|
|
|
|
|
708
|
0
|
0
|
|
|
|
0
|
if ($where) { |
709
|
0
|
|
|
|
|
0
|
my($where_sql, @where_bind) = $self->where($where); |
710
|
0
|
|
|
|
|
0
|
$sql .= $where_sql; |
711
|
0
|
|
|
|
|
0
|
push @all_bind, @where_bind; |
712
|
|
|
|
|
|
|
} |
713
|
|
|
|
|
|
|
|
714
|
0
|
0
|
|
|
|
0
|
if ($options->{returning}) { |
715
|
0
|
|
|
|
|
0
|
my ($returning_sql, @returning_bind) = $self->_update_returning($options); |
716
|
0
|
|
|
|
|
0
|
$sql .= $returning_sql; |
717
|
0
|
|
|
|
|
0
|
push @all_bind, @returning_bind; |
718
|
|
|
|
|
|
|
} |
719
|
|
|
|
|
|
|
|
720
|
0
|
0
|
|
|
|
0
|
return wantarray ? ($sql, @all_bind) : $sql; |
721
|
0
|
|
|
|
|
0
|
}; |
722
|
|
|
|
|
|
|
*_update_returning = sub { |
723
|
0
|
|
|
0
|
|
0
|
my ($self, $options) = @_; |
724
|
|
|
|
|
|
|
|
725
|
0
|
|
|
|
|
0
|
my $f = $options->{returning}; |
726
|
|
|
|
|
|
|
|
727
|
|
|
|
|
|
|
my $fieldlist = $self->_SWITCH_refkind($f, { |
728
|
0
|
|
|
|
|
0
|
ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$f;}, |
|
0
|
|
|
|
|
0
|
|
729
|
0
|
|
|
|
|
0
|
SCALAR => sub {$self->_quote($f)}, |
730
|
0
|
|
|
|
|
0
|
SCALARREF => sub {$$f}, |
731
|
0
|
|
|
|
|
0
|
}); |
732
|
0
|
|
|
|
|
0
|
return $self->_sqlcase(' returning ') . $fieldlist; |
733
|
0
|
|
|
|
|
0
|
}; |
734
|
|
|
|
|
|
|
} |
735
|
|
|
|
|
|
|
|
736
|
|
|
|
|
|
|
# now override or supply the _update_set_value() method |
737
|
|
|
|
|
|
|
*_update_set_values = sub { |
738
|
0
|
|
|
0
|
|
0
|
my ($self, $data) = @_; |
739
|
|
|
|
|
|
|
|
740
|
0
|
|
|
|
|
0
|
my (@set, @all_bind); |
741
|
0
|
|
|
|
|
0
|
for my $k (sort keys %$data) { |
742
|
0
|
|
|
|
|
0
|
my $v = $data->{$k}; |
743
|
0
|
|
|
|
|
0
|
my $r = ref $v; |
744
|
0
|
|
|
|
|
0
|
my $label = $self->_quote($k); |
745
|
|
|
|
|
|
|
|
746
|
|
|
|
|
|
|
$self->_SWITCH_refkind($v, { |
747
|
|
|
|
|
|
|
ARRAYREF => sub { |
748
|
0
|
0
|
0
|
|
|
0
|
if ($self->{array_datatypes} # array datatype |
749
|
|
|
|
|
|
|
|| $self->is_bind_value_with_type($v)) { # or bind value with type |
750
|
0
|
|
|
|
|
0
|
push @set, "$label = ?"; |
751
|
0
|
|
|
|
|
0
|
push @all_bind, $self->_bindtype($k, $v); |
752
|
|
|
|
|
|
|
} |
753
|
|
|
|
|
|
|
else { # literal SQL with bind |
754
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = @$v; |
755
|
0
|
|
|
|
|
0
|
$self->_assert_bindval_matches_bindtype(@bind); |
756
|
0
|
|
|
|
|
0
|
push @set, "$label = $sql"; |
757
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
758
|
|
|
|
|
|
|
} |
759
|
|
|
|
|
|
|
}, |
760
|
|
|
|
|
|
|
ARRAYREFREF => sub { # literal SQL with bind |
761
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = @${$v}; |
|
0
|
|
|
|
|
0
|
|
762
|
0
|
|
|
|
|
0
|
$self->_assert_bindval_matches_bindtype(@bind); |
763
|
0
|
|
|
|
|
0
|
push @set, "$label = $sql"; |
764
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
765
|
|
|
|
|
|
|
}, |
766
|
|
|
|
|
|
|
SCALARREF => sub { # literal SQL without bind |
767
|
0
|
|
|
|
|
0
|
push @set, "$label = $$v"; |
768
|
|
|
|
|
|
|
}, |
769
|
|
|
|
|
|
|
HASHREF => sub { |
770
|
0
|
|
|
|
|
0
|
my ($op, $arg, @rest) = %$v; |
771
|
|
|
|
|
|
|
|
772
|
0
|
0
|
0
|
|
|
0
|
puke 'Operator calls in update must be in the form { -op => $arg }' |
773
|
|
|
|
|
|
|
if (@rest or not $op =~ /^\-(.+)/); |
774
|
|
|
|
|
|
|
|
775
|
0
|
|
|
|
|
0
|
local $self->{_nested_func_lhs} = $k; |
776
|
0
|
|
|
|
|
0
|
my ($sql, @bind) = $self->_where_unary_op($1, $arg); |
777
|
|
|
|
|
|
|
|
778
|
0
|
|
|
|
|
0
|
push @set, "$label = $sql"; |
779
|
0
|
|
|
|
|
0
|
push @all_bind, @bind; |
780
|
|
|
|
|
|
|
}, |
781
|
|
|
|
|
|
|
SCALAR_or_UNDEF => sub { |
782
|
0
|
|
|
|
|
0
|
push @set, "$label = ?"; |
783
|
0
|
|
|
|
|
0
|
push @all_bind, $self->_bindtype($k, $v); |
784
|
|
|
|
|
|
|
}, |
785
|
0
|
|
|
|
|
0
|
}); |
786
|
|
|
|
|
|
|
} |
787
|
|
|
|
|
|
|
# generate sql |
788
|
0
|
|
|
|
|
0
|
my $sql = CORE::join ', ', @set; |
789
|
0
|
|
|
|
|
0
|
return ($sql, @all_bind); |
790
|
0
|
|
|
|
|
0
|
}; |
791
|
|
|
|
|
|
|
} |
792
|
|
|
|
|
|
|
} |
793
|
|
|
|
|
|
|
|
794
|
|
|
|
|
|
|
sub update { |
795
|
11
|
|
|
11
|
1
|
42653
|
my $self = shift; |
796
|
|
|
|
|
|
|
|
797
|
11
|
|
|
|
|
49
|
my $join_info; |
798
|
|
|
|
|
|
|
my @old_API_args; |
799
|
11
|
|
|
|
|
0
|
my $returning_into; |
800
|
11
|
|
|
|
|
0
|
my %args; |
801
|
11
|
100
|
|
|
|
25
|
if (&_called_with_named_args) { |
802
|
10
|
|
|
|
|
294
|
%args = validate(@_, \%params_for_update); |
803
|
|
|
|
|
|
|
|
804
|
|
|
|
|
|
|
# compute join info if the datasource is a join |
805
|
10
|
|
|
|
|
70
|
$join_info = $self->_compute_join_info($args{-table}); |
806
|
10
|
100
|
|
|
|
32
|
$args{-table} = \($join_info->{sql}) if $join_info; |
807
|
|
|
|
|
|
|
|
808
|
10
|
|
|
|
|
42
|
@old_API_args = @args{qw/-table -set -where/}; |
809
|
|
|
|
|
|
|
|
810
|
|
|
|
|
|
|
# deal with -returning arg |
811
|
|
|
|
|
|
|
($returning_into, my $old_API_options) |
812
|
10
|
|
|
|
|
34
|
= $self->_compute_returning($args{-returning}); |
813
|
10
|
100
|
|
|
|
31
|
push @old_API_args, $old_API_options if $old_API_options; |
814
|
|
|
|
|
|
|
} |
815
|
|
|
|
|
|
|
else { |
816
|
1
|
|
|
|
|
3
|
@old_API_args = @_; |
817
|
|
|
|
|
|
|
} |
818
|
|
|
|
|
|
|
|
819
|
|
|
|
|
|
|
# call parent method and merge with bind values from $join_info |
820
|
11
|
|
|
|
|
51
|
my ($sql, @bind) = $self->_parent_update(@old_API_args); |
821
|
|
|
|
|
|
|
|
822
|
11
|
100
|
|
|
|
14186
|
unshift @bind, @{$join_info->{bind}} if $join_info; |
|
1
|
|
|
|
|
4
|
|
823
|
|
|
|
|
|
|
|
824
|
|
|
|
|
|
|
# handle additional args if needed |
825
|
11
|
|
|
|
|
74
|
$self->_handle_additional_args_for_update_delete(\%args, \$sql, \@bind, qr/UPDATE/); |
826
|
|
|
|
|
|
|
|
827
|
|
|
|
|
|
|
# inject more stuff if using Oracle's "RETURNING ... INTO ..." |
828
|
11
|
100
|
|
|
|
36
|
if ($returning_into) { |
829
|
1
|
|
|
|
|
6
|
$sql .= ' INTO ' . join(", ", ("?") x @$returning_into); |
830
|
1
|
|
|
|
|
2
|
push @bind, @$returning_into; |
831
|
|
|
|
|
|
|
} |
832
|
|
|
|
|
|
|
|
833
|
|
|
|
|
|
|
# initial WITH clause |
834
|
11
|
|
|
|
|
53
|
$self->_prepend_WITH_clause(\$sql, \@bind); |
835
|
|
|
|
|
|
|
|
836
|
11
|
|
|
|
|
70
|
return ($sql, @bind); |
837
|
|
|
|
|
|
|
} |
838
|
|
|
|
|
|
|
|
839
|
|
|
|
|
|
|
|
840
|
|
|
|
|
|
|
|
841
|
|
|
|
|
|
|
|
842
|
|
|
|
|
|
|
|
843
|
|
|
|
|
|
|
|
844
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
845
|
|
|
|
|
|
|
# delete |
846
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
847
|
|
|
|
|
|
|
|
848
|
|
|
|
|
|
|
sub delete { |
849
|
5
|
|
|
5
|
1
|
18886
|
my $self = shift; |
850
|
|
|
|
|
|
|
|
851
|
5
|
|
|
|
|
13
|
my @old_API_args; |
852
|
|
|
|
|
|
|
my %args; |
853
|
5
|
100
|
|
|
|
13
|
if (&_called_with_named_args) { |
854
|
4
|
|
|
|
|
116
|
%args = validate(@_, \%params_for_delete); |
855
|
4
|
|
|
|
|
29
|
@old_API_args = @args{qw/-from -where/}; |
856
|
|
|
|
|
|
|
} |
857
|
|
|
|
|
|
|
else { |
858
|
1
|
|
|
|
|
4
|
@old_API_args = @_; |
859
|
|
|
|
|
|
|
} |
860
|
|
|
|
|
|
|
|
861
|
|
|
|
|
|
|
# call parent method |
862
|
5
|
|
|
|
|
21
|
my ($sql, @bind) = $self->next::method(@old_API_args); |
863
|
|
|
|
|
|
|
|
864
|
|
|
|
|
|
|
# maybe need to handle additional args |
865
|
5
|
|
|
|
|
4710
|
$self->_handle_additional_args_for_update_delete(\%args, \$sql, \@bind, qr/DELETE/); |
866
|
|
|
|
|
|
|
|
867
|
|
|
|
|
|
|
# initial WITH clause |
868
|
5
|
|
|
|
|
23
|
$self->_prepend_WITH_clause(\$sql, \@bind); |
869
|
|
|
|
|
|
|
|
870
|
5
|
|
|
|
|
32
|
return ($sql, @bind); |
871
|
|
|
|
|
|
|
} |
872
|
|
|
|
|
|
|
|
873
|
|
|
|
|
|
|
|
874
|
|
|
|
|
|
|
|
875
|
|
|
|
|
|
|
|
876
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
877
|
|
|
|
|
|
|
# auxiliary methods for insert(), update() and delete() |
878
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
879
|
|
|
|
|
|
|
|
880
|
|
|
|
|
|
|
sub _compute_returning { |
881
|
20
|
|
|
20
|
|
55
|
my ($self, $arg_returning) = @_; |
882
|
|
|
|
|
|
|
|
883
|
20
|
|
|
|
|
35
|
my ($returning_into, $old_API_options); |
884
|
|
|
|
|
|
|
|
885
|
20
|
100
|
|
|
|
48
|
if ($arg_returning) { |
886
|
|
|
|
|
|
|
# if present, "-returning" may be a scalar, arrayref or hashref; the latter |
887
|
|
|
|
|
|
|
# is interpreted as .. RETURNING ... INTO ... |
888
|
|
|
|
|
|
|
|
889
|
|
|
|
|
|
|
|
890
|
6
|
100
|
|
|
|
18
|
if (does $arg_returning, 'HASH') { |
891
|
2
|
50
|
|
|
|
15
|
my @keys = sort keys %$arg_returning |
892
|
|
|
|
|
|
|
or puke "-returning => {} : the hash is empty"; |
893
|
|
|
|
|
|
|
|
894
|
2
|
|
|
|
|
8
|
$old_API_options = {returning => \@keys}; |
895
|
2
|
|
|
|
|
4
|
$returning_into = [@{$arg_returning}{@keys}]; |
|
2
|
|
|
|
|
6
|
|
896
|
|
|
|
|
|
|
} |
897
|
|
|
|
|
|
|
else { |
898
|
4
|
|
|
|
|
12
|
$old_API_options = {returning => $arg_returning}; |
899
|
|
|
|
|
|
|
} |
900
|
|
|
|
|
|
|
} |
901
|
|
|
|
|
|
|
|
902
|
20
|
|
|
|
|
50
|
return ($returning_into, $old_API_options); |
903
|
|
|
|
|
|
|
} |
904
|
|
|
|
|
|
|
|
905
|
|
|
|
|
|
|
|
906
|
|
|
|
|
|
|
sub _handle_additional_args_for_update_delete { |
907
|
16
|
|
|
16
|
|
44
|
my ($self, $args, $sql_ref, $bind_ref, $keyword_regex) = @_; |
908
|
|
|
|
|
|
|
|
909
|
16
|
100
|
|
|
|
51
|
if (defined $args->{-order_by}) { |
910
|
2
|
|
|
|
|
8
|
my ($sql_ob, @bind_ob) = $self->_order_by($args->{-order_by}); |
911
|
2
|
|
|
|
|
549
|
$$sql_ref .= $sql_ob; |
912
|
2
|
|
|
|
|
5
|
push @$bind_ref, @bind_ob; |
913
|
|
|
|
|
|
|
} |
914
|
16
|
100
|
|
|
|
42
|
if (defined $args->{-limit}) { |
915
|
|
|
|
|
|
|
# can't call $self->limit_offset(..) because there shouldn't be any offset |
916
|
2
|
|
|
|
|
7
|
$$sql_ref .= $self->_sqlcase(' limit ?'); |
917
|
2
|
|
|
|
|
12
|
push @$bind_ref, $args->{-limit}; |
918
|
|
|
|
|
|
|
} |
919
|
16
|
100
|
|
|
|
44
|
if (defined $args->{-add_sql}) { |
920
|
2
|
|
|
|
|
57
|
$$sql_ref =~ s/\b($keyword_regex)\b/$1 $args->{-add_sql}/i; |
921
|
|
|
|
|
|
|
} |
922
|
|
|
|
|
|
|
} |
923
|
|
|
|
|
|
|
|
924
|
|
|
|
|
|
|
|
925
|
|
|
|
|
|
|
sub _order_by { |
926
|
54
|
|
|
54
|
|
118374
|
my ($self, $order) = @_; |
927
|
|
|
|
|
|
|
|
928
|
|
|
|
|
|
|
# force scalar into an arrayref |
929
|
54
|
100
|
|
|
|
175
|
$order = [$order] if not ref $order; |
930
|
|
|
|
|
|
|
|
931
|
|
|
|
|
|
|
# restructure array data |
932
|
54
|
100
|
|
|
|
150
|
if (does $order, 'ARRAY') { |
933
|
42
|
|
|
|
|
124
|
my @clone = @$order; # because we will modify items |
934
|
|
|
|
|
|
|
|
935
|
|
|
|
|
|
|
# '-' and '+' prefixes are translated into {-desc/asc => } hashrefs |
936
|
42
|
|
|
|
|
89
|
foreach my $item (@clone) { |
937
|
69
|
100
|
100
|
|
|
241
|
next if !$item or ref $item; |
938
|
35
|
100
|
100
|
|
|
148
|
$item =~ s/^-// and $item = {-desc => $item} and next; |
939
|
32
|
100
|
|
|
|
100
|
$item =~ s/^\+// and $item = {-asc => $item}; |
940
|
|
|
|
|
|
|
} |
941
|
42
|
|
|
|
|
96
|
$order = \@clone; |
942
|
|
|
|
|
|
|
} |
943
|
|
|
|
|
|
|
|
944
|
54
|
|
|
|
|
537
|
return $self->next::method($order); |
945
|
|
|
|
|
|
|
} |
946
|
|
|
|
|
|
|
|
947
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
948
|
|
|
|
|
|
|
# other public methods |
949
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
950
|
|
|
|
|
|
|
|
951
|
|
|
|
|
|
|
# same pattern for 3 invocation methods |
952
|
|
|
|
|
|
|
foreach my $attr (qw/table_alias column_alias limit_offset/) { |
953
|
10
|
|
|
10
|
|
126
|
no strict 'refs'; |
|
10
|
|
|
|
|
25
|
|
|
10
|
|
|
|
|
1063
|
|
954
|
|
|
|
|
|
|
*{$attr} = sub { |
955
|
89
|
|
|
89
|
|
12649
|
my $self = shift; |
956
|
89
|
|
|
|
|
186
|
my $method = $self->{$attr}; # grab reference to method body |
957
|
89
|
|
|
|
|
221
|
$self->$method(@_); # invoke |
958
|
|
|
|
|
|
|
}; |
959
|
|
|
|
|
|
|
} |
960
|
|
|
|
|
|
|
|
961
|
|
|
|
|
|
|
# readonly accessor methods |
962
|
|
|
|
|
|
|
foreach my $key (qw/join_syntax join_assoc_right |
963
|
|
|
|
|
|
|
max_members_IN multicols_sep has_multicols_in_SQL/) { |
964
|
10
|
|
|
10
|
|
90
|
no strict 'refs'; |
|
10
|
|
|
|
|
28
|
|
|
10
|
|
|
|
|
16755
|
|
965
|
1
|
|
|
1
|
|
2008
|
*{$key} = sub {shift->{$key}}; |
966
|
|
|
|
|
|
|
} |
967
|
|
|
|
|
|
|
|
968
|
|
|
|
|
|
|
|
969
|
|
|
|
|
|
|
# invocation method for 'join' |
970
|
|
|
|
|
|
|
sub join { |
971
|
30
|
|
|
30
|
1
|
64383
|
my $self = shift; |
972
|
|
|
|
|
|
|
|
973
|
|
|
|
|
|
|
# start from the right if right-associative |
974
|
30
|
100
|
|
|
|
100
|
@_ = reverse @_ if $self->{join_assoc_right}; |
975
|
|
|
|
|
|
|
|
976
|
|
|
|
|
|
|
# shift first single item (a table) before reducing pairs (op, table) |
977
|
30
|
|
|
|
|
56
|
my $combined = shift; |
978
|
30
|
50
|
|
|
|
762
|
$combined = $self->_parse_table($combined) unless ref $combined; |
979
|
|
|
|
|
|
|
|
980
|
|
|
|
|
|
|
# reduce pairs (op, table) |
981
|
30
|
|
|
|
|
129
|
while (@_) { |
982
|
|
|
|
|
|
|
# shift 2 items : next join specification and next table |
983
|
34
|
|
|
|
|
62
|
my $join_spec = shift; |
984
|
34
|
50
|
|
|
|
82
|
my $table_spec = shift or puke "improper number of operands"; |
985
|
|
|
|
|
|
|
|
986
|
34
|
100
|
|
|
|
146
|
$join_spec = $self->_parse_join_spec($join_spec) unless ref $join_spec; |
987
|
34
|
50
|
|
|
|
110
|
$table_spec = $self->_parse_table($table_spec) unless ref $table_spec; |
988
|
34
|
|
|
|
|
102
|
$combined = $self->_single_join($combined, $join_spec, $table_spec); |
989
|
|
|
|
|
|
|
} |
990
|
|
|
|
|
|
|
|
991
|
29
|
|
|
|
|
98
|
return $combined; # {sql=> .., bind => [..], aliased_tables => {..}} |
992
|
|
|
|
|
|
|
} |
993
|
|
|
|
|
|
|
|
994
|
|
|
|
|
|
|
|
995
|
|
|
|
|
|
|
# utility for merging several "where" clauses |
996
|
|
|
|
|
|
|
sub merge_conditions { |
997
|
1
|
|
|
1
|
1
|
3229
|
my $self = shift; |
998
|
1
|
|
|
|
|
3
|
my %merged; |
999
|
|
|
|
|
|
|
|
1000
|
1
|
|
|
|
|
3
|
foreach my $cond (@_) { |
1001
|
2
|
50
|
|
|
|
6
|
if (does $cond, 'HASH') { |
|
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
1002
|
2
|
|
|
|
|
9
|
foreach my $col (sort keys %$cond) { |
1003
|
|
|
|
|
|
|
$merged{$col} = $merged{$col} ? [-and => $merged{$col}, $cond->{$col}] |
1004
|
4
|
100
|
|
|
|
15
|
: $cond->{$col}; |
1005
|
|
|
|
|
|
|
} |
1006
|
|
|
|
|
|
|
} |
1007
|
|
|
|
|
|
|
elsif (does $cond, 'ARRAY') { |
1008
|
0
|
0
|
|
|
|
0
|
$merged{-nest} = $merged{-nest} ? {-and => [$merged{-nest}, $cond]} |
1009
|
|
|
|
|
|
|
: $cond; |
1010
|
|
|
|
|
|
|
} |
1011
|
|
|
|
|
|
|
elsif ($cond) { |
1012
|
0
|
|
|
|
|
0
|
$merged{$cond} = \""; |
1013
|
|
|
|
|
|
|
} |
1014
|
|
|
|
|
|
|
} |
1015
|
1
|
|
|
|
|
4
|
return \%merged; |
1016
|
|
|
|
|
|
|
} |
1017
|
|
|
|
|
|
|
|
1018
|
|
|
|
|
|
|
# utility for calling either bind_param or bind_param_inout |
1019
|
|
|
|
|
|
|
our $INOUT_MAX_LEN = 99; # chosen arbitrarily; see L |
1020
|
|
|
|
|
|
|
sub bind_params { |
1021
|
0
|
|
|
0
|
1
|
0
|
my ($self, $sth, @bind) = @_; |
1022
|
0
|
0
|
|
|
|
0
|
$sth->isa('DBI::st') or puke "sth argument is not a DBI statement handle"; |
1023
|
0
|
|
|
|
|
0
|
foreach my $i (0 .. $#bind) { |
1024
|
0
|
|
|
|
|
0
|
my $val = $bind[$i]; |
1025
|
0
|
0
|
0
|
|
|
0
|
if (does $val, 'SCALAR') { |
|
|
0
|
|
|
|
|
|
1026
|
|
|
|
|
|
|
# a scalarref is interpreted as an INOUT parameter |
1027
|
0
|
|
|
|
|
0
|
$sth->bind_param_inout($i+1, $val, $INOUT_MAX_LEN); |
1028
|
|
|
|
|
|
|
} |
1029
|
|
|
|
|
|
|
elsif (does $val, 'ARRAY' and |
1030
|
|
|
|
|
|
|
my ($bind_meth, @args) = $self->is_bind_value_with_type($val)) { |
1031
|
|
|
|
|
|
|
# either 'bind_param' or 'bind_param_inout', with 2 or 3 args |
1032
|
0
|
|
|
|
|
0
|
$sth->$bind_meth($i+1, @args); |
1033
|
|
|
|
|
|
|
} |
1034
|
|
|
|
|
|
|
else { |
1035
|
|
|
|
|
|
|
# other cases are passed directly to DBI::bind_param |
1036
|
0
|
|
|
|
|
0
|
$sth->bind_param($i+1, $val); |
1037
|
|
|
|
|
|
|
} |
1038
|
|
|
|
|
|
|
} |
1039
|
|
|
|
|
|
|
} |
1040
|
|
|
|
|
|
|
|
1041
|
|
|
|
|
|
|
sub is_bind_value_with_type { |
1042
|
7
|
|
|
7
|
1
|
2243
|
my ($self, $val) = @_; |
1043
|
|
|
|
|
|
|
|
1044
|
|
|
|
|
|
|
# compatibility with DBIx::Class syntax of shape [\%args => $val], |
1045
|
|
|
|
|
|
|
# see L |
1046
|
7
|
100
|
66
|
|
|
47
|
if ( @$val == 2 |
|
|
|
100
|
|
|
|
|
1047
|
|
|
|
|
|
|
&& does($val->[0], 'HASH') |
1048
|
20
|
|
|
|
|
46
|
&& grep {$val->[0]{$_}} qw/dbd_attrs sqlt_size |
1049
|
|
|
|
|
|
|
sqlt_datatype dbic_colname/) { |
1050
|
4
|
|
|
|
|
8
|
my $args = $val->[0]; |
1051
|
4
|
50
|
|
|
|
12
|
if (my $attrs = $args->{dbd_attrs}) { |
|
|
0
|
|
|
|
|
|
1052
|
4
|
|
|
|
|
21
|
return (bind_param => $val->[1], $attrs); |
1053
|
|
|
|
|
|
|
} |
1054
|
|
|
|
|
|
|
elsif (my $size = $args->{sqlt_size}) { |
1055
|
0
|
|
|
|
|
0
|
return (bind_param_inout => $val, $size); |
1056
|
|
|
|
|
|
|
} |
1057
|
|
|
|
|
|
|
# other options like 'sqlt_datatype', 'dbic_colname' are not supported |
1058
|
|
|
|
|
|
|
else { |
1059
|
0
|
|
|
|
|
0
|
puke "unsupported options for bind type : " |
1060
|
|
|
|
|
|
|
. CORE::join(", ", sort keys %$args); |
1061
|
|
|
|
|
|
|
} |
1062
|
|
|
|
|
|
|
|
1063
|
|
|
|
|
|
|
# NOTE : the following DBIx::Class shortcuts are not supported |
1064
|
|
|
|
|
|
|
# [ $name => $val ] === [ { dbic_colname => $name }, $val ] |
1065
|
|
|
|
|
|
|
# [ \$dt => $val ] === [ { sqlt_datatype => $dt }, $val ] |
1066
|
|
|
|
|
|
|
# [ undef, $val ] === [ {}, $val ] |
1067
|
|
|
|
|
|
|
} |
1068
|
|
|
|
|
|
|
|
1069
|
|
|
|
|
|
|
# in all other cases, this is not a bind value with type |
1070
|
3
|
|
|
|
|
24
|
return (); |
1071
|
|
|
|
|
|
|
} |
1072
|
|
|
|
|
|
|
|
1073
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1074
|
|
|
|
|
|
|
# private utility methods for 'join' |
1075
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1076
|
|
|
|
|
|
|
|
1077
|
|
|
|
|
|
|
sub _compute_join_info { |
1078
|
68
|
|
|
68
|
|
167
|
my ($self, $table_arg) = @_; |
1079
|
|
|
|
|
|
|
|
1080
|
68
|
100
|
66
|
|
|
191
|
if (does($table_arg, 'ARRAY') && $table_arg->[0] eq '-join') { |
1081
|
12
|
|
|
|
|
41
|
my @join_args = @$table_arg; |
1082
|
12
|
|
|
|
|
24
|
shift @join_args; # drop initial '-join' |
1083
|
12
|
|
|
|
|
43
|
return $self->join(@join_args); |
1084
|
|
|
|
|
|
|
} |
1085
|
|
|
|
|
|
|
else { |
1086
|
56
|
|
|
|
|
134
|
return; |
1087
|
|
|
|
|
|
|
} |
1088
|
|
|
|
|
|
|
} |
1089
|
|
|
|
|
|
|
|
1090
|
|
|
|
|
|
|
sub _parse_table { |
1091
|
64
|
|
|
64
|
|
150
|
my ($self, $table) = @_; |
1092
|
|
|
|
|
|
|
|
1093
|
|
|
|
|
|
|
# extract alias, if any (recognized as "table|alias") |
1094
|
64
|
|
|
|
|
276
|
($table, my $alias) = split /\|/, $table, 2; |
1095
|
|
|
|
|
|
|
|
1096
|
|
|
|
|
|
|
# build a table spec |
1097
|
|
|
|
|
|
|
return { |
1098
|
64
|
100
|
66
|
|
|
224
|
sql => $self->table_alias($table, $alias), |
1099
|
|
|
|
|
|
|
bind => [], |
1100
|
|
|
|
|
|
|
name => ($alias || $table), |
1101
|
|
|
|
|
|
|
aliased_tables => {$alias ? ($alias => $table) : ()}, |
1102
|
|
|
|
|
|
|
}; |
1103
|
|
|
|
|
|
|
} |
1104
|
|
|
|
|
|
|
|
1105
|
|
|
|
|
|
|
sub _parse_join_spec { |
1106
|
31
|
|
|
31
|
|
65
|
my ($self, $join_spec) = @_; |
1107
|
|
|
|
|
|
|
|
1108
|
|
|
|
|
|
|
# parse the join specification |
1109
|
31
|
50
|
|
|
|
69
|
$join_spec |
1110
|
|
|
|
|
|
|
or puke "empty join specification"; |
1111
|
|
|
|
|
|
|
my ($op, $bracket, $cond_list) = ($join_spec =~ $self->{join_regex}) |
1112
|
31
|
50
|
|
|
|
401
|
or puke "incorrect join specification : $join_spec\n$self->{join_regex}"; |
1113
|
31
|
|
100
|
|
|
156
|
$op ||= '<=>'; |
1114
|
31
|
|
100
|
|
|
94
|
$bracket ||= '{'; |
1115
|
31
|
|
100
|
|
|
83
|
$cond_list ||= ''; |
1116
|
|
|
|
|
|
|
|
1117
|
|
|
|
|
|
|
# extract constants (strings between quotes), replaced by placeholders |
1118
|
31
|
|
|
|
|
122
|
my $regex = qr/' # initial quote |
1119
|
|
|
|
|
|
|
( # begin capturing group |
1120
|
|
|
|
|
|
|
[^']* # any non-quote chars |
1121
|
|
|
|
|
|
|
(?: # begin non-capturing group |
1122
|
|
|
|
|
|
|
'' # pair of quotes |
1123
|
|
|
|
|
|
|
[^']* # any non-quote chars |
1124
|
|
|
|
|
|
|
)* # this non-capturing group 0 or more times |
1125
|
|
|
|
|
|
|
) # end of capturing group |
1126
|
|
|
|
|
|
|
' # ending quote |
1127
|
|
|
|
|
|
|
/x; |
1128
|
31
|
|
|
|
|
57
|
my $placeholder = '_?_'; # unlikely to be counfounded with any value |
1129
|
31
|
|
|
|
|
53
|
my @constants; |
1130
|
31
|
|
|
|
|
194
|
while ($cond_list =~ s/$regex/$placeholder/) { |
1131
|
6
|
|
|
|
|
43
|
push @constants, $1; |
1132
|
|
|
|
|
|
|
}; |
1133
|
31
|
|
|
|
|
83
|
s/''/'/g for @constants; # replace pairs of quotes by single quotes |
1134
|
|
|
|
|
|
|
|
1135
|
|
|
|
|
|
|
# accumulate conditions as pairs ($left => \"$op $right") |
1136
|
31
|
|
|
|
|
69
|
my @conditions; |
1137
|
|
|
|
|
|
|
my @using; |
1138
|
31
|
|
|
|
|
111
|
foreach my $cond (split /,\s*/, $cond_list) { |
1139
|
|
|
|
|
|
|
# parse the condition (left and right operands + comparison operator) |
1140
|
41
|
|
|
|
|
176
|
my ($left, $cmp, $right) = split /([<>=!^]{1,2})/, $cond; |
1141
|
41
|
100
|
66
|
|
|
189
|
if ($cmp && $right) { |
|
|
50
|
|
|
|
|
|
1142
|
|
|
|
|
|
|
# if operands are not qualified by table/alias name, add sprintf hooks |
1143
|
34
|
100
|
|
|
|
110
|
$left = '%1$s.' . $left unless $left =~ /\./; |
1144
|
34
|
100
|
100
|
|
|
153
|
$right = '%2$s.' . $right unless $right =~ /\./ or $right eq $placeholder; |
1145
|
|
|
|
|
|
|
|
1146
|
|
|
|
|
|
|
# add this pair into the list; right operand is either a bind value |
1147
|
|
|
|
|
|
|
# or an identifier within the right table |
1148
|
34
|
100
|
|
|
|
113
|
$right = $right eq $placeholder ? shift @constants : {-ident => $right}; |
1149
|
34
|
|
|
|
|
116
|
push @conditions, $left, {$cmp => $right}; |
1150
|
|
|
|
|
|
|
} |
1151
|
|
|
|
|
|
|
elsif ($cond =~ /^\w+$/) { |
1152
|
7
|
|
|
|
|
19
|
push @using, $cond; |
1153
|
|
|
|
|
|
|
} |
1154
|
0
|
|
|
|
|
0
|
else {puke "can't parse join condition: $cond"} |
1155
|
|
|
|
|
|
|
} |
1156
|
|
|
|
|
|
|
|
1157
|
|
|
|
|
|
|
# build join hashref |
1158
|
31
|
|
|
|
|
94
|
my $join_hash = {operator => $op}; |
1159
|
31
|
100
|
|
|
|
89
|
$join_hash->{using} = \@using if @using; |
1160
|
|
|
|
|
|
|
$join_hash->{condition} |
1161
|
31
|
100
|
|
|
|
150
|
= $bracket eq '[' ? [@conditions] : {@conditions} if @conditions; |
|
|
100
|
|
|
|
|
|
1162
|
|
|
|
|
|
|
|
1163
|
31
|
|
|
|
|
116
|
return $join_hash; |
1164
|
|
|
|
|
|
|
} |
1165
|
|
|
|
|
|
|
|
1166
|
|
|
|
|
|
|
sub _single_join { |
1167
|
34
|
|
|
34
|
|
59
|
my $self = shift; |
1168
|
|
|
|
|
|
|
|
1169
|
|
|
|
|
|
|
# if right-associative, restore proper left-right order in pair |
1170
|
34
|
100
|
|
|
|
113
|
@_ = reverse @_ if $self->{join_assoc_right}; |
1171
|
34
|
|
|
|
|
76
|
my ($left, $join_spec, $right) = @_; |
1172
|
|
|
|
|
|
|
|
1173
|
|
|
|
|
|
|
# syntax for assembling all elements |
1174
|
34
|
|
|
|
|
102
|
my $syntax = $self->{join_syntax}{$join_spec->{operator}}; |
1175
|
|
|
|
|
|
|
|
1176
|
34
|
|
|
|
|
81
|
my ($sql, @bind); |
1177
|
|
|
|
|
|
|
|
1178
|
10
|
|
|
10
|
|
7873
|
{ no if $] ge '5.022000', warnings => 'redundant'; |
|
10
|
|
|
|
|
157
|
|
|
10
|
|
|
|
|
66
|
|
|
34
|
|
|
|
|
65
|
|
1179
|
|
|
|
|
|
|
# because sprintf instructions may _intentionally_ omit %.. parameters |
1180
|
|
|
|
|
|
|
|
1181
|
34
|
100
|
|
|
|
113
|
if ($join_spec->{using}) { |
|
|
100
|
|
|
|
|
|
1182
|
|
|
|
|
|
|
not $join_spec->{condition} |
1183
|
8
|
100
|
|
|
|
24
|
or puke "join specification has both {condition} and {using} fields"; |
1184
|
|
|
|
|
|
|
|
1185
|
7
|
|
|
|
|
46
|
$syntax =~ s/\bON\s+%s/USING (%s)/; |
1186
|
7
|
|
|
|
|
15
|
$sql = CORE::join ",", @{$join_spec->{using}}; |
|
7
|
|
|
|
|
20
|
|
1187
|
|
|
|
|
|
|
} |
1188
|
|
|
|
|
|
|
elsif ($join_spec->{condition}) { |
1189
|
|
|
|
|
|
|
not $join_spec->{using} |
1190
|
25
|
50
|
|
|
|
62
|
or puke "join specification has both {condition} and {using} fields"; |
1191
|
|
|
|
|
|
|
|
1192
|
|
|
|
|
|
|
# compute the "ON" clause |
1193
|
25
|
|
|
|
|
137
|
($sql, @bind) = $self->where($join_spec->{condition}); |
1194
|
25
|
|
|
|
|
19447
|
$sql =~ s/^\s*WHERE\s+//; |
1195
|
|
|
|
|
|
|
|
1196
|
|
|
|
|
|
|
# substitute left/right tables names for '%1$s', '%2$s' |
1197
|
25
|
|
|
|
|
138
|
$sql = sprintf $sql, $left->{name}, $right->{name}; |
1198
|
|
|
|
|
|
|
} |
1199
|
|
|
|
|
|
|
|
1200
|
|
|
|
|
|
|
# build the final sql |
1201
|
33
|
|
|
|
|
181
|
$sql = sprintf $syntax, $left->{sql}, $right->{sql}, $sql; |
1202
|
|
|
|
|
|
|
} |
1203
|
|
|
|
|
|
|
|
1204
|
|
|
|
|
|
|
# add left/right bind parameters (if any) into the list |
1205
|
33
|
|
|
|
|
72
|
unshift @bind, @{$left->{bind}}, @{$right->{bind}}; |
|
33
|
|
|
|
|
66
|
|
|
33
|
|
|
|
|
59
|
|
1206
|
|
|
|
|
|
|
|
1207
|
|
|
|
|
|
|
# build result and return |
1208
|
33
|
|
|
|
|
150
|
my %result = (sql => $sql, bind => \@bind); |
1209
|
33
|
100
|
|
|
|
147
|
$result{name} = ($self->{join_assoc_right} ? $left : $right)->{name}; |
1210
|
33
|
|
|
|
|
67
|
$result{aliased_tables} = $left->{aliased_tables}; |
1211
|
33
|
|
|
|
|
58
|
foreach my $alias (keys %{$right->{aliased_tables}}) { |
|
33
|
|
|
|
|
152
|
|
1212
|
6
|
|
|
|
|
16
|
$result{aliased_tables}{$alias} = $right->{aliased_tables}{$alias}; |
1213
|
|
|
|
|
|
|
} |
1214
|
|
|
|
|
|
|
|
1215
|
33
|
|
|
|
|
257
|
return \%result; |
1216
|
|
|
|
|
|
|
} |
1217
|
|
|
|
|
|
|
|
1218
|
|
|
|
|
|
|
|
1219
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1220
|
|
|
|
|
|
|
# override of parent's "_where_field_IN" |
1221
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1222
|
|
|
|
|
|
|
|
1223
|
|
|
|
|
|
|
sub _where_field_IN { |
1224
|
31
|
|
|
31
|
|
70731
|
my ($self, $k, $op, $vals) = @_; |
1225
|
|
|
|
|
|
|
|
1226
|
|
|
|
|
|
|
# special algorithm if the key is multi-columns (contains a multicols_sep) |
1227
|
31
|
100
|
|
|
|
99
|
if ($self->{multicols_sep}) { |
1228
|
8
|
|
|
|
|
50
|
my @cols = split m[$self->{multicols_sep}], $k; |
1229
|
8
|
50
|
|
|
|
19
|
if (@cols > 1) { |
1230
|
8
|
100
|
|
|
|
24
|
if ($self->{has_multicols_in_SQL}) { |
1231
|
|
|
|
|
|
|
# DBMS accepts special SQL syntax for multicolumns |
1232
|
6
|
|
|
|
|
15
|
return $self->_multicols_IN_through_SQL(\@cols, $op, $vals); |
1233
|
|
|
|
|
|
|
} |
1234
|
|
|
|
|
|
|
else { |
1235
|
|
|
|
|
|
|
# DBMS doesn't accept special syntax, so we must use boolean logic |
1236
|
2
|
|
|
|
|
7
|
return $self->_multicols_IN_through_boolean(\@cols, $op, $vals); |
1237
|
|
|
|
|
|
|
} |
1238
|
|
|
|
|
|
|
} |
1239
|
|
|
|
|
|
|
} |
1240
|
|
|
|
|
|
|
|
1241
|
|
|
|
|
|
|
# special algorithm if the number of values exceeds the allowed maximum |
1242
|
23
|
|
|
|
|
41
|
my $max_members_IN = $self->{max_members_IN}; |
1243
|
23
|
100
|
100
|
|
|
78
|
if ($max_members_IN && does($vals, 'ARRAY') |
|
|
|
100
|
|
|
|
|
1244
|
|
|
|
|
|
|
&& @$vals > $max_members_IN) { |
1245
|
4
|
|
|
|
|
15
|
my @vals = @$vals; |
1246
|
4
|
|
|
|
|
7
|
my @slices; |
1247
|
4
|
|
|
|
|
15
|
while (my @slice = splice(@vals, 0, $max_members_IN)) { |
1248
|
12
|
|
|
|
|
35
|
push @slices, \@slice; |
1249
|
|
|
|
|
|
|
} |
1250
|
4
|
|
|
|
|
9
|
my @clauses = map {{-$op, $_}} @slices; |
|
12
|
|
|
|
|
32
|
|
1251
|
4
|
100
|
|
|
|
17
|
my $connector = $op =~ /^not/i ? '-and' : '-or'; |
1252
|
4
|
|
|
|
|
11
|
unshift @clauses, $connector; |
1253
|
4
|
|
|
|
|
18
|
my ($sql, @bind) = $self->where({$k => \@clauses}); |
1254
|
4
|
|
|
|
|
3285
|
$sql =~ s/\s*where\s*\((.*)\)/$1/i; |
1255
|
4
|
|
|
|
|
77
|
return ($sql, @bind); |
1256
|
|
|
|
|
|
|
} |
1257
|
|
|
|
|
|
|
|
1258
|
|
|
|
|
|
|
|
1259
|
|
|
|
|
|
|
# otherwise, call parent method |
1260
|
19
|
100
|
|
|
|
63
|
$vals = [@$vals] if blessed $vals; # because SQLA dies on blessed arrayrefs |
1261
|
19
|
|
|
|
|
81
|
return $self->next::method($k, $op, $vals); |
1262
|
|
|
|
|
|
|
} |
1263
|
|
|
|
|
|
|
|
1264
|
|
|
|
|
|
|
|
1265
|
|
|
|
|
|
|
sub _multicols_IN_through_SQL { |
1266
|
6
|
|
|
6
|
|
14
|
my ($self, $cols, $op, $vals) = @_; |
1267
|
|
|
|
|
|
|
|
1268
|
|
|
|
|
|
|
# build initial sql |
1269
|
6
|
|
|
|
|
11
|
my $n_cols = @$cols; |
1270
|
6
|
|
|
|
|
13
|
my $sql_cols = CORE::join(',', map {$self->_quote($_)} @$cols); |
|
14
|
|
|
|
|
176
|
|
1271
|
6
|
|
|
|
|
122
|
my $sql = "($sql_cols) " . $self->_sqlcase($op); |
1272
|
|
|
|
|
|
|
|
1273
|
|
|
|
|
|
|
# dispatch according to structure of $vals |
1274
|
|
|
|
|
|
|
return $self->_SWITCH_refkind($vals, { |
1275
|
|
|
|
|
|
|
|
1276
|
|
|
|
|
|
|
ARRAYREF => sub { # list of tuples |
1277
|
|
|
|
|
|
|
# deal with special case of empty list (like the parent class) |
1278
|
4
|
|
|
4
|
|
148
|
my $n_tuples = @$vals; |
1279
|
4
|
50
|
|
|
|
10
|
if (!$n_tuples) { |
1280
|
0
|
0
|
|
|
|
0
|
my $sql = ($op =~ /\bnot\b/i) ? $self->{sqltrue} : $self->{sqlfalse}; |
1281
|
0
|
|
|
|
|
0
|
return ($sql); |
1282
|
|
|
|
|
|
|
} |
1283
|
|
|
|
|
|
|
|
1284
|
|
|
|
|
|
|
# otherwise, build SQL and bind values for the list of tuples |
1285
|
4
|
|
|
|
|
5
|
my @bind; |
1286
|
4
|
|
|
|
|
8
|
foreach my $val (@$vals) { |
1287
|
6
|
100
|
|
|
|
12
|
does($val, 'ARRAY') |
1288
|
|
|
|
|
|
|
or $val = [split m[$self->{multicols_sep}], $val]; |
1289
|
6
|
50
|
|
|
|
17
|
@$val == $n_cols |
1290
|
|
|
|
|
|
|
or puke "op '$op' with multicols: tuple with improper number of cols"; |
1291
|
6
|
|
|
|
|
16
|
push @bind, @$val; |
1292
|
|
|
|
|
|
|
} |
1293
|
4
|
|
|
|
|
23
|
my $single_tuple = "(" . CORE::join(',', (('?') x $n_cols)) . ")"; |
1294
|
|
|
|
|
|
|
|
1295
|
4
|
|
|
|
|
12
|
my $all_tuples = CORE::join(', ', (($single_tuple) x $n_tuples)); |
1296
|
4
|
|
|
|
|
12
|
$sql .= " ($all_tuples)"; |
1297
|
4
|
|
|
|
|
97
|
return ($sql, @bind); |
1298
|
|
|
|
|
|
|
}, |
1299
|
|
|
|
|
|
|
|
1300
|
|
|
|
|
|
|
SCALARREF => sub { # literal SQL |
1301
|
1
|
|
|
1
|
|
33
|
$sql .= " ($$vals)"; |
1302
|
1
|
|
|
|
|
22
|
return ($sql); |
1303
|
|
|
|
|
|
|
}, |
1304
|
|
|
|
|
|
|
|
1305
|
|
|
|
|
|
|
ARRAYREFREF => sub { # literal SQL with bind |
1306
|
1
|
|
|
1
|
|
40
|
my ($inner_sql, @bind) = @$$vals; |
1307
|
1
|
|
|
|
|
3
|
$sql .= " ($inner_sql)"; |
1308
|
1
|
|
|
|
|
26
|
return ($sql, @bind); |
1309
|
|
|
|
|
|
|
}, |
1310
|
|
|
|
|
|
|
|
1311
|
|
|
|
|
|
|
FALLBACK => sub { |
1312
|
0
|
|
|
0
|
|
0
|
puke "op '$op' with multicols requires a list of tuples or literal SQL"; |
1313
|
|
|
|
|
|
|
}, |
1314
|
|
|
|
|
|
|
|
1315
|
6
|
|
|
|
|
102
|
}); |
1316
|
|
|
|
|
|
|
} |
1317
|
|
|
|
|
|
|
|
1318
|
|
|
|
|
|
|
|
1319
|
|
|
|
|
|
|
sub _multicols_IN_through_boolean { |
1320
|
2
|
|
|
2
|
|
6
|
my ($self, $cols, $op, $vals) = @_; |
1321
|
|
|
|
|
|
|
|
1322
|
|
|
|
|
|
|
# can't handle anything else than a list of tuples |
1323
|
2
|
50
|
33
|
|
|
6
|
does($vals, 'ARRAY') && @$vals |
1324
|
|
|
|
|
|
|
or puke "op '$op' with multicols requires a non-empty list of tuples"; |
1325
|
|
|
|
|
|
|
|
1326
|
|
|
|
|
|
|
# assemble SQL |
1327
|
2
|
|
|
|
|
6
|
my $n_cols = @$cols; |
1328
|
2
|
|
|
|
|
4
|
my $sql_cols = CORE::join(' AND ', map {$self->_quote($_) . " = ?"} @$cols); |
|
5
|
|
|
|
|
57
|
|
1329
|
2
|
|
|
|
|
37
|
my $sql = "(" . CORE::join(' OR ', (("($sql_cols)") x @$vals)) . ")"; |
1330
|
2
|
100
|
|
|
|
8
|
$sql = "NOT $sql" if $op =~ /\bnot\b/i; |
1331
|
|
|
|
|
|
|
|
1332
|
|
|
|
|
|
|
# assemble bind values |
1333
|
2
|
|
|
|
|
4
|
my @bind; |
1334
|
2
|
|
|
|
|
5
|
foreach my $val (@$vals) { |
1335
|
3
|
50
|
|
|
|
7
|
does($val, 'ARRAY') |
1336
|
|
|
|
|
|
|
or $val = [split m[$self->{multicols_sep}], $val]; |
1337
|
3
|
50
|
|
|
|
9
|
@$val == $n_cols |
1338
|
|
|
|
|
|
|
or puke "op '$op' with multicols: tuple with improper number of cols"; |
1339
|
3
|
|
|
|
|
10
|
push @bind, @$val; |
1340
|
|
|
|
|
|
|
} |
1341
|
|
|
|
|
|
|
|
1342
|
|
|
|
|
|
|
# return the whole thing |
1343
|
2
|
|
|
|
|
27
|
return ($sql, @bind); |
1344
|
|
|
|
|
|
|
} |
1345
|
|
|
|
|
|
|
|
1346
|
|
|
|
|
|
|
|
1347
|
|
|
|
|
|
|
|
1348
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1349
|
|
|
|
|
|
|
# override of parent's methods for decoding arrayrefs |
1350
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1351
|
|
|
|
|
|
|
|
1352
|
|
|
|
|
|
|
sub _where_hashpair_ARRAYREF { |
1353
|
0
|
|
|
0
|
|
0
|
my ($self, $k, $v) = @_; |
1354
|
|
|
|
|
|
|
|
1355
|
0
|
0
|
|
|
|
0
|
if ($self->is_bind_value_with_type($v)) { |
1356
|
0
|
|
|
|
|
0
|
$self->_assert_no_bindtype_columns; |
1357
|
|
|
|
|
|
|
my $sql = CORE::join ' ', $self->_convert($self->_quote($k)), |
1358
|
0
|
|
|
|
|
0
|
$self->_sqlcase($self->{cmp}), |
1359
|
|
|
|
|
|
|
$self->_convert('?'); |
1360
|
0
|
|
|
|
|
0
|
my @bind = ($v); |
1361
|
0
|
|
|
|
|
0
|
return ($sql, @bind); |
1362
|
|
|
|
|
|
|
} |
1363
|
|
|
|
|
|
|
else { |
1364
|
0
|
|
|
|
|
0
|
return $self->next::method($k, $v); |
1365
|
|
|
|
|
|
|
} |
1366
|
|
|
|
|
|
|
} |
1367
|
|
|
|
|
|
|
|
1368
|
|
|
|
|
|
|
|
1369
|
|
|
|
|
|
|
sub _where_field_op_ARRAYREF { |
1370
|
0
|
|
|
0
|
|
0
|
my ($self, $k, $op, $vals) = @_; |
1371
|
|
|
|
|
|
|
|
1372
|
0
|
0
|
|
|
|
0
|
if ($self->is_bind_value_with_type($vals)) { |
1373
|
0
|
|
|
|
|
0
|
$self->_assert_no_bindtype_columns; |
1374
|
0
|
|
|
|
|
0
|
my $sql = CORE::join ' ', $self->_convert($self->_quote($k)), |
1375
|
|
|
|
|
|
|
$self->_sqlcase($op), |
1376
|
|
|
|
|
|
|
$self->_convert('?'); |
1377
|
0
|
|
|
|
|
0
|
my @bind = ($vals); |
1378
|
0
|
|
|
|
|
0
|
return ($sql, @bind); |
1379
|
|
|
|
|
|
|
} |
1380
|
|
|
|
|
|
|
else { |
1381
|
0
|
|
|
|
|
0
|
return $self->next::method($k, $op, $vals); |
1382
|
|
|
|
|
|
|
} |
1383
|
|
|
|
|
|
|
} |
1384
|
|
|
|
|
|
|
|
1385
|
|
|
|
|
|
|
sub _assert_no_bindtype_columns { |
1386
|
0
|
|
|
0
|
|
0
|
my ($self) = @_; |
1387
|
0
|
0
|
|
|
|
0
|
$self->{bindtype} ne 'columns' |
1388
|
|
|
|
|
|
|
or puke 'values of shape [$val, \%type] are not compatible' |
1389
|
|
|
|
|
|
|
. 'with ...->new(bindtype => "columns")'; |
1390
|
|
|
|
|
|
|
} |
1391
|
|
|
|
|
|
|
|
1392
|
|
|
|
|
|
|
|
1393
|
|
|
|
|
|
|
|
1394
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1395
|
|
|
|
|
|
|
# method creations through closures |
1396
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1397
|
|
|
|
|
|
|
|
1398
|
|
|
|
|
|
|
sub _make_sub_column_alias { |
1399
|
18
|
|
|
18
|
|
60
|
my ($self) = @_; |
1400
|
18
|
|
|
|
|
44
|
my $syntax = $self->{column_alias}; |
1401
|
|
|
|
|
|
|
$self->{column_alias} = sub { |
1402
|
16
|
|
|
16
|
|
49
|
my ($self, $name, $alias) = @_; |
1403
|
16
|
100
|
|
|
|
60
|
return $name if !$alias; |
1404
|
|
|
|
|
|
|
|
1405
|
|
|
|
|
|
|
# quote $name unless it is an SQL expression (then the user should quote it) |
1406
|
15
|
100
|
|
|
|
69
|
$name = $self->_quote($name) unless $name =~ /[()]/; |
1407
|
|
|
|
|
|
|
|
1408
|
|
|
|
|
|
|
# assemble syntax |
1409
|
15
|
|
|
|
|
233
|
my $sql = sprintf $syntax, $name, $self->_quote($alias); |
1410
|
|
|
|
|
|
|
|
1411
|
|
|
|
|
|
|
# return a string ref to avoid quoting by SQLA |
1412
|
15
|
|
|
|
|
300
|
return \$sql; |
1413
|
18
|
|
|
|
|
110
|
}; |
1414
|
|
|
|
|
|
|
} |
1415
|
|
|
|
|
|
|
|
1416
|
|
|
|
|
|
|
|
1417
|
|
|
|
|
|
|
sub _make_sub_table_alias { |
1418
|
18
|
|
|
18
|
|
56
|
my ($self) = @_; |
1419
|
18
|
|
|
|
|
43
|
my $syntax = $self->{table_alias}; |
1420
|
|
|
|
|
|
|
$self->{table_alias} = sub { |
1421
|
65
|
|
|
65
|
|
153
|
my ($self, $name, $alias) = @_; |
1422
|
65
|
100
|
|
|
|
493
|
return $name if !$alias; |
1423
|
|
|
|
|
|
|
|
1424
|
|
|
|
|
|
|
# assemble syntax |
1425
|
14
|
|
|
|
|
48
|
my $sql = sprintf $syntax, $self->_quote($name), $self->_quote($alias); |
1426
|
|
|
|
|
|
|
|
1427
|
14
|
|
|
|
|
616
|
return $sql; |
1428
|
18
|
|
|
|
|
82
|
}; |
1429
|
|
|
|
|
|
|
} |
1430
|
|
|
|
|
|
|
|
1431
|
|
|
|
|
|
|
|
1432
|
|
|
|
|
|
|
|
1433
|
|
|
|
|
|
|
sub _choose_LIMIT_OFFSET_dialect { |
1434
|
17
|
|
|
17
|
|
35
|
my $self = shift; |
1435
|
17
|
|
|
|
|
36
|
my $dialect = $self->{limit_offset}; |
1436
|
17
|
50
|
|
|
|
66
|
my $method = $limit_offset_dialects{$dialect} |
1437
|
|
|
|
|
|
|
or puke "no such limit_offset dialect: $dialect"; |
1438
|
17
|
|
|
|
|
35
|
$self->{limit_offset} = $method; |
1439
|
|
|
|
|
|
|
} |
1440
|
|
|
|
|
|
|
|
1441
|
|
|
|
|
|
|
|
1442
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1443
|
|
|
|
|
|
|
# utility to decide if the method was called with named or positional args |
1444
|
|
|
|
|
|
|
#---------------------------------------------------------------------- |
1445
|
|
|
|
|
|
|
|
1446
|
|
|
|
|
|
|
sub _called_with_named_args { |
1447
|
87
|
|
66
|
87
|
|
718
|
return $_[0] && !ref $_[0] && substr($_[0], 0, 1) eq '-'; |
1448
|
|
|
|
|
|
|
} |
1449
|
|
|
|
|
|
|
|
1450
|
|
|
|
|
|
|
|
1451
|
|
|
|
|
|
|
1; # End of SQL::Abstract::More |
1452
|
|
|
|
|
|
|
|
1453
|
|
|
|
|
|
|
__END__ |