blib/lib/DBIO/SQLMaker/ClassicExtensions.pm

filter: Statementstmt 84.2% 406 / 482 Branchbran 75.2% 167 / 222 Conditioncond 45.7% 69 / 151 MC/DCmcdc 25.2% 22 / 87 Subroutinesub 89.5% 43 / 48 Podpod 83.3% 5 / 6 totaltotal 71.4% 712 / 996 CC 187 SCAR 32.9
CC
187
Cov
75%
_where_op_multicolumn_in
210.0
_subqueried_limit_attrs
55.1
_prep_for_skimming_limit
26.4
CRAP 26.8
« blib/lib/DBIO/Test/AsyncHarness.pm ↑ Summary blib/lib/DBIO/Test/Util.pm »
1 package DBIO::SQLMaker::ClassicExtensions;
2 # ABSTRACT: Class containing generic enhancements to SQL::Abstract
3  
4 192 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 192 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 192 our $VERSION = '0.900007';
8  
9  
10 # to pull in CAG and the frame-boundary-markers
11 192 ▶use base 'DBIO::Base';
Subroutine BEGIN: called CC=1 SCAR=0.0
12 192 ▶use DBIO::Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
13 192 ▶use DBIO::Util ();
Subroutine BEGIN: called CC=1 SCAR=0.0
14 192 ▶use namespace::clean;
Subroutine BEGIN: called CC=1 SCAR=0.0
15  
16 192 __PACKAGE__->mk_group_accessors (simple => qw/quote_char name_sep/);
17  
18  
19 0 ▶sub _quoting_enabled {
Subroutine _quoting_enabled: not called CC=3 SCAR=24.8
20 0 ▶ ( defined $_[0]->{quote_char} and length $_[0]->{quote_char} ) ? 1 : 0
Branch: 1 branch
BranchTrueFalseTotal
defined $_[0]{'quote_char'} && length $_[0]{'quote_char'} ? : 0 0 0
Condition0%0/3
defined $_[0]{"quote_char"} and length $_[0]{"quote_char"}
!ll&&!rl&&r
000
MC/DC0%0/2
defined $_[0]{"quote_char"} and length $_[0]{"quote_char"}
defined $_[0]{"quote_char"} length $_[0]{"quote_char"}
Truth table0%0/3
A and B
A= defined $_[0]{"quote_char"}B= length $_[0]{"quote_char"}
ABresult
0X0
100
111
21 }
22  
23 # for when I need a normalized l/r pair
24  
25 672 ▶sub _quote_chars {
Subroutine _quote_chars: called CC=6 SCAR=20.7
26  
27 # in case we are called in the old !!$sm->_quote_chars fashion
28 672 ▶ return () if !wantarray and ( ! defined $_[0]->{quote_char} or ! length $_[0]->{quote_char} );
Branch: 1 branch
BranchTrueFalseTotal
unless wantarray or defined $_[0]{"quote_char"} and length $_[0]{"quote_char"} 0 672 672
Condition16%1/6
wantarray or defined $_[0]{"quote_char"} and length $_[0]{"quote_char"}
l!l&&r!l&&!r
67200
defined $_[0]{"quote_char"} and length $_[0]{"quote_char"}
!ll&&!rl&&r
000
MC/DC0%0/3
wantarray or defined $_[0]{"quote_char"} and length $_[0]{"quote_char"}
wantarray defined $_[0]{"quote_char"} length $_[0]{"quote_char"}
Truth table25%1/4
A or B and C
A= wantarrayB= defined $_[0]{"quote_char"}C= length $_[0]{"quote_char"}
ABCresult
1XX1
0111
00X0
0100
29  
30 map
31 1344 ▶ { defined $_ ? $_ : '' }
Branch: 2 branches
BranchTrueFalseTotal
defined $_ ? : 1340 4 1344
ref $_[0]{'quote_char'} ? : 5 667 672
32 672 ( ref $_[0]->{quote_char} ? (@{$_[0]->{quote_char}}) : ( ($_[0]->{quote_char}) x 2 ) )
33 ;
34 }
35  
36 # FIXME when we bring in the storage weaklink, check its schema
37 # weaklink and channel through $schema->throw_exception
38 11 ▶sub throw_exception { DBIO::Exception->throw($_[1]) }
Subroutine throw_exception: called CC=1 SCAR=0.0
Pod undocumented
39  
40  
41 61 ▶sub belch {
Subroutine belch: called CC=1 SCAR=0.0
Pod documented
42 61 shift; # throw away $self
43 61 carp( "Warning: ", @_ );
44 };
45  
46  
47 1 ▶sub puke {
Subroutine puke: called CC=1 SCAR=0.0
Pod documented
48 1 shift->throw_exception("Fatal: " . join ('', @_));
49 };
50  
51 # constants-methods are used not only here, but also in comparison tests
52 190 ▶sub __rows_bindtype () {
Subroutine __rows_bindtype: called CC=1 SCAR=0.0
53 190 +{ sqlt_datatype => 'integer' }
54 }
55 40 ▶sub __offset_bindtype () {
Subroutine __offset_bindtype: called CC=1 SCAR=0.0
56 40 +{ sqlt_datatype => 'integer' }
57 }
58 10 ▶sub __total_bindtype () {
Subroutine __total_bindtype: called CC=1 SCAR=0.0
59 10 +{ sqlt_datatype => 'integer' }
60 }
61  
62 # the "oh noes offset/top without limit" constant
63 # limited to 31 bits for sanity (and consistency,
64 # since it may be handed to the like of sprintf %u)
65 #
66 # Also *some* builds of SQLite fail the test
67 # some_column BETWEEN ? AND ?: 1, 4294967295
68 # with the proper integer bind attrs
69 #
70 # Implemented as a method, since ::Storage::DBI also
71 # refers to it (i.e. for the case of software_limit or
72 # as the value to abuse with MSSQL ordered subqueries)
73 sub __max_int () { 0x7FFFFFFF };
74  
75 # we ne longer need to check this - DBIO has ways of dealing with it
76 # specifically ::Storage::DBI::_resolve_bindattrs()
77 sub _assert_bindval_matches_bindtype () { 1 };
78  
79 # poor man's de-qualifier
80  
81 38986 ▶sub _quote {
Subroutine _quote: called CC=3 SCAR=11.1
82 38986 ▶ $_[0]->next::method( ( $_[0]{_dequalify_idents} and ! ref $_[1] )
Branch: 1 branch
BranchTrueFalseTotal
$_[0]{'_dequalify_idents'} && !ref($_[1]) ? : 18 38968 38986
Condition66%2/3
$_[0]{"_dequalify_idents"} and not ref $_[1]
!ll&&!rl&&r
38968018
MC/DC50%1/2
$_[0]{"_dequalify_idents"} and not ref $_[1]
$_[0]{"_dequalify_idents"} not ref $_[1]
Truth table66%2/3
A and not(B)
A= $_[0]{"_dequalify_idents"}B= not ref $_[1]
ABresult
0X0
100
111
83 ? $_[1] =~ / ([^\.]+) $ /x
84 : $_[1]
85 );
86 }
87  
88  
89 0 ▶sub _where_op_NEST {
Subroutine _where_op_NEST: not called CC=1 SCAR=6.9
90 0 carp_unique ("-nest in search conditions is deprecated, you most probably wanted:\n"
91 .q|{..., -and => [ \%cond0, \@cond1, \'cond2', \[ 'cond3', [ col => bind ] ], etc. ], ... }|
92 );
93  
94 0 shift->next::method(@_);
95 }
96  
97 # Handle limit-dialect selection
98  
99 3363 ▶sub select {
Subroutine select: called CC=13 SCAR=26.9
Pod documented
100 3363 my ($self, $table, $fields, $where, $rs_attrs, $limit, $offset) = @_;
101  
102  
103 3363 ($fields, @{$self->{select_bind}}) = $self->_recurse_fields($fields);
104  
105 3361 ▶ if (defined $offset) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $offset) 38 3323 3361
106 38 ▶ $self->throw_exception('A supplied offset must be a non-negative integer')
Branch: 1 branch
BranchTrueFalseTotal
if $offset =~ /\D/ or $offset < 0 0 38 38
Condition33%1/3
$offset =~ /\D/ or $offset < 0
l!l&&r!l&&!r
0038
MC/DC0%0/2
$offset =~ /\D/ or $offset < 0
$offset =~ /\D/ $offset < 0
Truth table33%1/3
A or B
A= $offset =~ /\D/B= $offset < 0
ABresult
1X1
011
000
107 if ( $offset =~ /\D/ or $offset < 0 );
108 }
109 3361 ▶ $offset ||= 0;
Condition100%2/2
$offset ||= 0
l!l
383323
MC/DC100%1/1
$offset ||= 0
$offset
Truth table100%2/2
A
A= $offset
Aresult
11
00
110  
111 3361 ▶ if (defined $limit) {
Branch: 2 branches
BranchTrueFalseTotal
if (defined $limit) { } 197 3164 3361
elsif ($offset) { } 0 3164 3164
112 197 ▶ $self->throw_exception('A supplied limit must be a positive integer')
Branch: 1 branch
BranchTrueFalseTotal
if $limit =~ /\D/ or $limit <= 0 0 197 197
Condition33%1/3
$limit =~ /\D/ or $limit <= 0
l!l&&r!l&&!r
00197
MC/DC0%0/2
$limit =~ /\D/ or $limit <= 0
$limit =~ /\D/ $limit <= 0
Truth table33%1/3
A or B
A= $limit =~ /\D/B= $limit <= 0
ABresult
1X1
011
000
113 if ( $limit =~ /\D/ or $limit <= 0 );
114 }
115 elsif ($offset) {
116 0 $limit = $self->__max_int;
117 }
118  
119  
120 3361 my ($sql, @bind);
121 3361 ▶ if ($limit) {
Branch: 1 branch
BranchTrueFalseTotal
if ($limit) { } 197 3164 3361
122 197 ($sql, @bind) = $self->next::method ($table, $fields, $where);
123  
124 $sql = $self->apply_limit (
125 $sql,
126 197 ▶ { %{$rs_attrs||{}}, _selector_sql => $fields },
Condition50%1/2
$rs_attrs || {}
l!l
1970
MC/DC0%0/1
$rs_attrs || {}
$rs_attrs
Truth table50%1/2
A
A= $rs_attrs
Aresult
11
00
127 $limit,
128 $offset
129 );
130 }
131 else {
132 3164 ($sql, @bind) = $self->next::method ($table, $fields, $where, $rs_attrs);
133 }
134  
135 3360 push @{$self->{where_bind}}, @bind;
136  
137 # this *must* be called, otherwise extra binds will remain in the sql-maker
138 3360 my @all_bind = $self->_assemble_binds;
139  
140 $sql .= $self->_lock_select ($rs_attrs->{for})
141 3360 ▶ if $rs_attrs->{for};
Branch: 1 branch
BranchTrueFalseTotal
if $rs_attrs->{"for"} 5 3355 3360
142  
143 3360 ▶ return wantarray ? ($sql, @all_bind) : $sql;
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 3360 0 3360
144 }
145  
146  
147 3360 ▶sub _assemble_binds {
Subroutine _assemble_binds: called CC=2 SCAR=6.9
148 3360 my $self = shift;
149 3360 ▶ return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/pre_select select from where group having order limit/);
Condition100%2/2
delete $$self{"${_}_bind"} || []
l!l
758119299
MC/DC100%1/1
delete $$self{"${_}_bind"} || []
delete $$self{"${_}_bind"}
Truth table100%2/2
A
A= delete $$self{"${_}_bind"}
Aresult
11
00
150 }
151  
152 192 my $for_syntax = {
153 update => 'FOR UPDATE',
154 shared => 'FOR SHARE',
155 };
156  
157 5 ▶sub _lock_select {
Subroutine _lock_select: called CC=3 SCAR=11.2
158 5 my ($self, $type) = @_;
159  
160 5 my $sql;
161 5 ▶ if (ref($type) eq 'SCALAR') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $type eq 'SCALAR') { } 1 4 5
162 1 $sql = "FOR $$type";
163 }
164 else {
165 4 ▶ $sql = $for_syntax->{$type} || $self->throw_exception( "Unknown SELECT .. FOR type '$type' requested" );
Condition33%1/3
$for_syntax->{$type} || $self->throw_exception("Unknown SELECT .. FOR type '${type}' requested")
l!l&&r!l&&!r
400
MC/DC0%0/2
$for_syntax->{$type} || $self->throw_exception("Unknown SELECT .. FOR type '${type}' requested")
$for_syntax->{$type} $self->throw_exception("Unknown SELECT .. FOR type '${type}' requested")
Truth table33%1/3
A || B
A= $for_syntax->{$type}B= $self->throw_exception("Unknown SELECT .. FOR type '${type}' requested")
ABresult
1X1
011
000
166 }
167  
168 5 return " $sql";
169 }
170  
171 # Handle default inserts
172  
173 3381 ▶sub insert {
Subroutine insert: called CC=8 SCAR=29.9
Pod documented
174 # optimized due to hotttnesss
175 # my ($self, $table, $data, $options) = @_;
176  
177 # ADR 0036: $options->{on_conflict} is the atomic-upsert transport. Only a
178 # driver SQLMaker can render it -- the clause has to be placed by engine
179 # grammar (before RETURNING on PostgreSQL, no conflict target at all on
180 # MySQL), so there is no generic tail to append here. A driver override
181 # consumes the key (deletes it from $options) and assembles its own
182 # statement; if the structure reaches this generic path it means the
183 # storage declared supports_upsert but its SQL generator does not
184 # implement the clause, which must be loud rather than silently dropped.
185 3381 ▶ if ( ($_[3]||{})->{on_conflict} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (+($_[3] || {})->{"on_conflict"}) 2 3379 3381
Condition100%2/2
$_[3] || {}
l!l
683313
MC/DC100%1/1
$_[3] || {}
$_[3]
Truth table100%2/2
A
A= $_[3]
Aresult
11
00
186 2 $_[0]->throw_exception(
187 ref($_[0]) . " does not implement the 'on_conflict' insert option "
188 . '(atomic upsert). A driver SQLMaker that supports it overrides insert(), '
189 . 'consumes $options->{on_conflict} and renders the engine clause itself. '
190 . 'See ADR 0036.'
191 );
192 }
193  
194 # FIXME SQLMaker will emit INSERT INTO $table ( ) VALUES ( )
195 # which is sadly understood only by MySQL. Change default behavior here,
196 # until we fold the extra pieces into SQLMaker properly
197 3379 ▶ if (! $_[2] or (ref $_[2] eq 'HASH' and !keys %{$_[2]} ) ) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $_[2] or ref $_[2] eq "HASH" and not keys %{$_[2];}) 0 3379 3379
Condition33%2/6
not $_[2] or ref $_[2] eq "HASH" and not keys %{$_[2];}
l!l&&r!l&&!r
003379
ref $_[2] eq "HASH" and not keys %{$_[2];}
!ll&&!rl&&r
033790
MC/DC0%0/3
not $_[2] or ref $_[2] eq "HASH" and not keys %{$_[2];}
not $_[2] ref $_[2] eq "HASH" not keys %{$_[2];}
Truth table25%1/4
not(A) or B and not(C)
A= not $_[2]B= ref $_[2] eq "HASH"C= not keys %{$_[2];}
ABCresult
1XX1
0111
00X0
0100
198 0 my @bind;
199 0 my $sql = sprintf(
200 'INSERT INTO %s DEFAULT VALUES', $_[0]->_quote($_[1])
201 );
202  
203 0 ▶ if ( ($_[3]||{})->{returning} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (+($_[3] || {})->{"returning"}) 0 0 0
Condition0%0/2
$_[3] || {}
l!l
00
MC/DC0%0/1
$_[3] || {}
$_[3]
Truth table0%0/2
A
A= $_[3]
Aresult
11
00
204 0 my $s;
205 0 ($s, @bind) = $_[0]->_insert_returning ($_[3]);
206 0 $sql .= $s;
207 }
208  
209 0 return ($sql, @bind);
210 }
211  
212 3379 next::method(@_);
213 }
214  
215  
216 2 ▶sub _upsert_attempted_sql {
Subroutine _upsert_attempted_sql: called CC=1 SCAR=0.0
217 2 my ($self, $col) = @_;
218  
219 2 $self->throw_exception(
220 ref($self) . ' does not implement _upsert_attempted_sql -- the reference '
221 . 'to the row an upsert tried to insert (PostgreSQL EXCLUDED."col", SQLite '
222 . 'excluded."col", MySQL VALUES(col) / new.col) is engine grammar, so core '
223 . 'has no default for it. A driver SQLMaker whose storage declares '
224 . 'supports_upsert renders the token itself. See ADR 0036.'
225 );
226 }
227  
228  
229 24 ▶sub _upsert_condition_sql {
Subroutine _upsert_condition_sql: called CC=3 SCAR=11.0
230 24 my ($self, $cond, $table) = @_;
231  
232 # { -attempted => $col } can sit at ANY depth of a condition. Rather than
233 # walking the condition here and re-deriving which positions take an operand
234 # and which take "operator plus operand", the marker is registered as an
235 # SQL::Abstract operator for the duration of this one call: the expansion
236 # pass already visits every node, so it substitutes at every depth, in nested
237 # -and/-or, in -in lists, inside -between and inside functions.
238 #
239 # make_unop_expander is what makes the bare right-hand-side position come out
240 # right. { rank => { -attempted => 'rank' } } is the caller saying "equal to
241 # the attempted value", and the expander turns that position into a
242 # comparison before the node is rendered (SQL::Abstract.pm:370 -- it
243 # dispatches to _expand_hashpair_cmp whenever the key is defined), instead of
244 # the '"rank" EXCLUDED."rank"' a raw literal substitution would produce.
245 # That is why a -literal is correct in every position here and no -ident
246 # detour is needed.
247 #
248 # local, not a constructor registration: -attempted means nothing in a
249 # SELECT, and leaking the operator into every where() would let it render a
250 # pseudo-row reference in statements that have no attempted row.
251 24 ▶ local $self->{expand_op}{attempted} = $self->make_unop_expander(sub {
Subroutine __ANON__: called CC=3 SCAR=11.0
252 24 my ($self, $op, $col) = @_;
253  
254 # One place for the marker's payload rule, however deep the node sat: the
255 # expander is the single door every -attempted in the condition comes
256 # through.
257 24 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless defined $col and not length ref $col 5 19 24
Condition100%3/3
defined $col and not length ref $col
!ll&&!rl&&r
2319
MC/DC100%2/2
defined $col and not length ref $col
defined $col not length ref $col
Truth table100%3/3
A and not(B)
A= defined $colB= not length ref $col
ABresult
0X0
100
111
258 '{ -attempted => $column } takes a plain column name, nothing else'
259 ) unless defined $col and ! length ref $col;
260  
261 19 return { -literal => [ $self->_upsert_attempted_sql($col) ] };
262 24 });
263  
264 # k205: qualify the stored-side columns with the target table.
265 #
266 # The vocabulary above lets a *bare* column mean the *stored* row -- the row
267 # the statement collided with -- while the marker spells the *attempted* row.
268 # But in an ON CONFLICT DO UPDATE branch both correlations are in scope at
269 # once (the target table and the attempted pseudo-row, PostgreSQL EXCLUDED /
270 # SQLite excluded), and a bare column reaches the engine unqualified: with the
271 # same name carried by both sides the engine cannot tell them apart, and
272 # PostgreSQL rejects it outright ("column reference is ambiguous", karr k205).
273 # This premise -- "an unqualified stored column renders correctly on its own"
274 # -- is exactly what ADR 0036 section 1 assumed and k205 disproves for the
275 # *conditional* branch.
276 #
277 # So when the caller passes the target table, every bare (single-part) column
278 # in the condition is qualified with it. It rides the SAME expansion pass as
279 # the marker above, so "at any depth" holds by construction rather than by a
280 # hand-written walk. Left untouched, deliberately: an already-qualified ident
281 # (it carries its own table part -> more than one part) and the attempted-row
282 # marker (which expanded to a -literal above, never an ident). So an EXCLUDED
283 # reference is byte-for-byte unchanged, and a caller that passes no table --
284 # including the unconditional upsert, which never reaches this method -- gets
285 # the pre-k205 rendering.
286 #
287 # This is determinate for ON CONFLICT precisely because there is exactly one
288 # target correlation plus the one pseudo-row; MERGE's two named source/target
289 # aliases are a different, broader question (karr k172) and are not touched here.
290 24 ▶ if (defined $table and length $table) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $table and length $table) 7 17 24
Condition66%2/3
defined $table and length $table
!ll&&!rl&&r
1707
MC/DC50%1/2
defined $table and length $table
defined $table length $table
Truth table66%2/3
A and B
A= defined $tableB= length $table
ABresult
0X0
100
111
291 7 my @target = @{ $self->_expand_ident(ident => $table)->{-ident} };
292 7 my $expand_ident = $self->{expand_op}{ident};
293  
294 10 ▶ local $self->{expand_op}{ident} = sub {
Subroutine __ANON__: called CC=4 SCAR=14.2
295 10 my ($self, $name, $body, $k) = @_;
296 10 my $expanded = $self->$expand_ident($name, $body, $k);
297  
298 9 return { -ident => [ @target, @{ $expanded->{-ident} } ] }
299 if ref $expanded eq 'HASH'
300 and ref $expanded->{-ident} eq 'ARRAY'
301 10 ▶ and @{ $expanded->{-ident} } == 1;
Branch: 1 branch
BranchTrueFalseTotal
if ref $expanded eq "HASH" and ref $expanded->{"-ident"} eq "ARRAY" and @{$expanded->{"-ident"};} == 1 9 1 10
Condition50%3/6
ref $expanded eq "HASH" and ref $expanded->{"-ident"} eq "ARRAY" and @{$expanded->{"-ident"};} == 1
!ll&&!rl&&r
019
ref $expanded eq "HASH" and ref $expanded->{"-ident"} eq "ARRAY"
!ll&&!rl&&r
0010
MC/DC33%1/3
ref $expanded eq "HASH" and ref $expanded->{"-ident"} eq "ARRAY" and @{$expanded->{"-ident"};} == 1
ref $expanded eq "HASH" ref $expanded->{"-ident"} eq "ARRAY" @{$expanded->{"-ident"};} == 1
Truth table50%2/4
A and B and C
A= ref $expanded eq "HASH"B= ref $expanded->{"-ident"} eq "ARRAY"C= @{$expanded->{"-ident"};} == 1
ABCresult
0XX0
10X0
1100
1111
302  
303 1 return $expanded;
304 7 };
305  
306 7 return $self->_recurse_where($cond);
307 }
308  
309 17 return $self->_recurse_where($cond);
310 }
311  
312  
313 14075 ▶sub _recurse_fields {
Subroutine _recurse_fields: called CC=13 SCAR=26.2
314 14075 my ($self, $fields) = @_;
315 14075 my $ref = ref $fields;
316 14075 ▶ return $self->_quote($fields) unless $ref;
Branch: 1 branch
BranchTrueFalseTotal
unless $ref 10131 3944 14075
317 3944 ▶ return $$fields if $ref eq 'SCALAR';
Branch: 1 branch
BranchTrueFalseTotal
if $ref eq "SCALAR" 81 3863 3944
318  
319 3863 ▶ if ($ref eq 'ARRAY') {
Branch: 3 branches
BranchTrueFalseTotal
if ($ref eq 'ARRAY') { } 3484 379 3863
elsif ($ref eq 'HASH') { } 318 61 379
elsif ($ref eq 'REF' and ref $$fields eq 'ARRAY') { } 61 0 61
320 3484 my (@select, @bind);
321 3484 for my $field (@$fields) {
322 9239 my ($select, @new_bind) = $self->_recurse_fields($field);
323 9237 push @select, $select;
324 9237 push @bind, @new_bind;
325 }
326 3482 return (join(', ', @select), @bind);
327 }
328 elsif ($ref eq 'HASH') {
329 318 my %hash = %$fields; # shallow copy
330  
331 318 my $as = delete $hash{-as}; # if supplied
332  
333 318 my ($func, $rhs, @toomany) = %hash;
334  
335 # there should be only one pair
336 318 ▶ if (@toomany) {
Branch: 1 branch
BranchTrueFalseTotal
if (@toomany) 0 318 318
337 0 $self->throw_exception( "Malformed select argument - too many keys in hash: " . join (',', keys %$fields ) );
338 }
339  
340 318 ▶ if (lc ($func) eq 'distinct' && ref $rhs eq 'ARRAY' && @$rhs > 1) {
Branch: 1 branch
BranchTrueFalseTotal
if (lc $func eq "distinct" and ref $rhs eq "ARRAY" and @$rhs > 1) 1 317 318
Condition66%4/6
lc $func eq "distinct" and ref $rhs eq "ARRAY" and @$rhs > 1
!ll&&!rl&&r
31701
lc $func eq "distinct" and ref $rhs eq "ARRAY"
!ll&&!rl&&r
31701
MC/DC33%1/3
lc $func eq "distinct" and ref $rhs eq "ARRAY" and @$rhs > 1
lc $func eq "distinct" ref $rhs eq "ARRAY" @$rhs > 1
Truth table50%2/4
A and B and C
A= lc $func eq "distinct"B= ref $rhs eq "ARRAY"C= @$rhs > 1
ABCresult
0XX0
10X0
1100
1111
341 1 $self->throw_exception (
342 'The select => { distinct => ... } syntax is not supported for multiple columns.'
343 .' Instead please use { group_by => [ qw/' . (join ' ', @$rhs) . '/ ] }'
344 .' or { select => [ qw/' . (join ' ', @$rhs) . '/ ], distinct => 1 }'
345 );
346 }
347  
348 317 DBIO::Util::assert_function_name($func, "select");
349  
350 305 my ($rhs_sql, @rhs_bind) = $self->_recurse_fields($rhs);
351 305 ▶ my $select = sprintf ('%s( %s )%s',
Branch: 1 branch
BranchTrueFalseTotal
$as ? : 60 245 305
352 $self->_sqlcase($func),
353 $rhs_sql,
354 $as
355 ? sprintf (' %s %s', $self->_sqlcase('as'), $self->_quote ($as) )
356 : ''
357 );
358  
359 305 return ($select, @rhs_bind);
360 }
361 ▶ elsif ( $ref eq 'REF' and ref($$fields) eq 'ARRAY' ) {
Condition33%1/3
$ref eq "REF" and ref $$fields eq "ARRAY"
!ll&&!rl&&r
0061
MC/DC0%0/2
$ref eq "REF" and ref $$fields eq "ARRAY"
$ref eq "REF" ref $$fields eq "ARRAY"
Truth table33%1/3
A and B
A= $ref eq "REF"B= ref $$fields eq "ARRAY"
ABresult
0X0
100
111
362 61 return @{$$fields};
363 }
364 else {
365 0 $self->throw_exception( $ref . qq{ unexpected in _recurse_fields()} );
366 }
367 }
368  
369  
370 # this used to be a part of _order_by but is broken out for clarity.
371 # What we have been doing forever is hijacking the $order arg of
372 # SQLAC::select to pass in arbitrary pieces of data (first the group_by,
373 # then pretty much the entire resultset attr-hash, as more and more
374 # things in the SQLMaker space need to have more info about the $rs they
375 # create SQL for. The alternative would be to keep expanding the
376 # signature of _select with more and more positional parameters, which
377 # is just gross.
378 #
379 # FIXME - this will have to transition out to a subclass when the effort
380 # of folding the SQL generating machinery into SQLMaker takes place
381  
382 3674 ▶sub _parse_rs_attrs {
Subroutine _parse_rs_attrs: called CC=5 SCAR=16.1
383 3674 my ($self, $arg) = @_;
384  
385 3674 my $sql = '';
386  
387 3674 ▶ if ($arg->{group_by}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($arg->{"group_by"}) 121 3553 3674
388 121 ▶ if ( my ($group_sql, @group_bind) = $self->_recurse_fields($arg->{group_by}) ) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = $self->_recurse_fields($arg->{"group_by"})) 121 0 121
389 121 $sql .= $self->_sqlcase(' group by ') . $group_sql;
390 121 push @{$self->{group_bind}}, @group_bind;
391 }
392 }
393  
394 3674 ▶ if (defined $arg->{having}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $arg->{"having"}) 25 3649 3674
395 25 my ($frag, @bind) = $self->_recurse_where($arg->{having});
396 25 push(@{$self->{having_bind}}, @bind);
397 25 $sql .= $self->_sqlcase(' having ') . $frag;
398 }
399  
400 3674 ▶ if (defined $arg->{order_by}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $arg->{"order_by"}) 342 3332 3674
401 342 $sql .= $self->_order_by ($arg->{order_by});
402 }
403  
404 3673 return $sql;
405 }
406  
407  
408 3491 ▶sub _order_by {
Subroutine _order_by: called CC=4 SCAR=13.9
409 3491 my ($self, $arg) = @_;
410  
411 # check that we are not called in legacy mode (order_by as 4th argument)
412 3491 ▶ if (ref $arg eq 'HASH' and not grep { $_ =~ /^-(?:desc|asc)/i } keys %$arg ) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $arg eq 'HASH' and not grep({$_ =~ /^-(?:desc|asc)/i;} keys %$arg)) { } 3131 360 3491
Condition100%3/3
ref $arg eq "HASH" and not grep({$_ =~ /^-(?:desc|asc)/i;} keys %$arg)
!ll&&!rl&&r
35913131
MC/DC100%2/2
ref $arg eq "HASH" and not grep({$_ =~ /^-(?:desc|asc)/i;} keys %$arg)
ref $arg eq "HASH" not grep({$_ =~ /^-(?:desc|asc)/i;} keys %$arg)
Truth table100%3/3
A and not(B)
A= ref $arg eq "HASH"B= not grep({$_ =~ /^-(?:desc|asc)/i;} keys %$arg)
ABresult
0X0
100
111
413 3131 return $self->_parse_rs_attrs ($arg);
414 }
415 else {
416 # rewrite any { -asc|-desc => \%where_cond } element into a parameterized
417 # literal so the value flows through as a bind placeholder rather than an
418 # inlined quoted literal (SQLA renders a plain HASH value as { col => val }
419 # which quotes the value). The raw $arg is never mutated, so the chunk
420 # re-parsing done by the limit dialects keeps seeing the original structure.
421 #
422 # Suppressed inside the subquery-emulation limit dialects: those re-chunk
423 # the raw order_by as *strings* to build inner-select supplements and to
424 # re-alias the OVER()/outer order. A bind-carrying literal here would
425 # desync the placeholder in the rendered order from the still-inlined
426 # supplement (the re-alias is a plain string match). See the guards in
427 # _RowNumberOver / _RowNum / _prep_for_skimming_limit / _GenericSubQ.
428 $arg = $self->_order_by_transform_bind_conds($arg)
429 360 ▶ unless $self->{_dbio_no_order_bind_transform};
Branch: 1 branch
BranchTrueFalseTotal
unless $self->{"_dbio_no_order_bind_transform"} 340 20 360
430  
431 360 my ($sql, @bind) = $self->next::method($arg);
432 359 push @{$self->{order_bind}}, @bind;
433 359 return $sql;
434 }
435 }
436  
437 # Walk an order_by spec (single element or arrayref of elements) and replace
438 # every { -asc|-desc => \%cond } whose value is a where-style condition HASHREF
439 # with { -asc|-desc => \[ $sql, @bind ] } produced via the WHERE machinery.
440 # Returns a (possibly new) structure; the input is left untouched.
441 716 ▶sub _order_by_transform_bind_conds {
Subroutine _order_by_transform_bind_conds: called CC=7 SCAR=19.5
442 716 my ($self, $arg) = @_;
443  
444 716 ▶ if (ref $arg eq 'ARRAY') {
Branch: 2 branches
BranchTrueFalseTotal
if (ref $arg eq 'ARRAY') { } 337 379 716
elsif (ref $arg eq 'HASH' and grep {$_ =~ /^-(?:desc|asc)/i;} keys %$arg) { } 187 384 571
445 337 return [ map { $self->_order_by_transform_bind_conds($_) } @$arg ];
446 }
447 192 ▶ elsif (ref $arg eq 'HASH' and grep { $_ =~ /^-(?:desc|asc)/i } keys %$arg) {
Condition66%2/3
ref $arg eq "HASH" and grep {$_ =~ /^-(?:desc|asc)/i;} keys %$arg
!ll&&!rl&&r
1920187
MC/DC50%1/2
ref $arg eq "HASH" and grep {$_ =~ /^-(?:desc|asc)/i;} keys %$arg
ref $arg eq "HASH" grep {$_ =~ /^-(?:desc|asc)/i;} keys %$arg
Truth table66%2/3
A and B
A= ref $arg eq "HASH"B= grep {$_ =~ /^-(?:desc|asc)/i;} keys %$arg
ABresult
0X0
100
111
448 187 my %copy = %$arg;
449 187 for my $dir (keys %copy) {
450 192 ▶ next unless $dir =~ /^-(?:desc|asc)/i;
Branch: 1 branch
BranchTrueFalseTotal
unless $dir =~ /^-(?:desc|asc)/i 4 188 192
451 188 my $val = $copy{$dir};
452 # only a bare where-style condition HASHREF needs rewriting; scalars,
453 # literal refs (\'...' / \[...]) and column lists pass through unchanged
454 188 ▶ $copy{$dir} = \[ $self->_recurse_where($val) ]
Branch: 1 branch
BranchTrueFalseTotal
if ref $val eq "HASH" 3 185 188
455 if ref $val eq 'HASH';
456 }
457 187 return \%copy;
458 }
459  
460 192 return $arg;
461 }
462  
463  
464 219 ▶sub _split_order_chunk {
Subroutine _split_order_chunk: called CC=4 SCAR=13.9
465 219 my ($self, $chunk) = @_;
466  
467 # strip off sort modifiers, but always succeed, so $1 gets reset
468 219 $chunk =~ s/ (?: \s+ (ASC|DESC) )? (?: \s+ NULLS \s+ (FIRST|LAST) )? \s* $//ix;
469 219 my ($dir, $nulls) = ($1, $2);
470  
471 return (
472 219 ▶ $chunk,
Branch: 2 branches
BranchTrueFalseTotal
$dir && uc $dir eq 'DESC' ? : 59 160 219
$nulls ? : 31 188 219
Condition100%3/3
$dir and uc $dir eq "DESC"
!ll&&!rl&&r
1303059
MC/DC100%2/2
$dir and uc $dir eq "DESC"
$dir uc $dir eq "DESC"
Truth table100%3/3
A and B
A= $dirB= uc $dir eq "DESC"
ABresult
0X0
100
111
473 ( $dir and uc($dir) eq 'DESC' ) ? 1 : 0,
474 ( $nulls ? uc($nulls) : undef ),
475 );
476 }
477  
478  
479 3361 ▶sub _table {
Subroutine _table: called CC=6 SCAR=18.4
480 # optimized due to hotttnesss
481 # my ($self, $from) = @_;
482 3361 ▶ if (my $ref = ref $_[1] ) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $ref = ref $_[1]) 3268 93 3361
483 3268 ▶ if ($ref eq 'ARRAY') {
Branch: 3 branches
BranchTrueFalseTotal
if ($ref eq 'ARRAY') { } 3267 1 3268
elsif ($ref eq 'HASH') { } 0 1 1
elsif ($ref eq 'REF' and ref ${$_[1];} eq 'ARRAY') { } 1 0 1
484 3267 return $_[0]->_recurse_from(@{$_[1]});
485 }
486 elsif ($ref eq 'HASH') {
487 0 return $_[0]->_recurse_from($_[1]);
488 }
489 1 ▶ elsif ($ref eq 'REF' && ref ${$_[1]} eq 'ARRAY') {
Condition33%1/3
$ref eq "REF" and ref ${$_[1];} eq "ARRAY"
!ll&&!rl&&r
001
MC/DC0%0/2
$ref eq "REF" and ref ${$_[1];} eq "ARRAY"
$ref eq "REF" ref ${$_[1];} eq "ARRAY"
Truth table33%1/3
A and B
A= $ref eq "REF"B= ref ${$_[1];} eq "ARRAY"
ABresult
0X0
100
111
490 1 my ($sql, @bind) = @{ ${$_[1]} };
491 1 push @{$_[0]->{from_bind}}, @bind;
492 1 return $sql
493 }
494 }
495 93 return $_[0]->next::method ($_[1]);
496 }
497  
498  
499 523 ▶sub _generate_join_clause {
Subroutine _generate_join_clause: called CC=3 SCAR=11.0
500 523 my ($self, $join_type) = @_;
501  
502 $join_type = $self->{_default_jointype}
503 523 ▶ if ! defined $join_type;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $join_type 255 268 523
504  
505 523 ▶ return sprintf ('%s JOIN ',
Branch: 1 branch
BranchTrueFalseTotal
$join_type ? : 266 257 523
506 $join_type ? $self->_sqlcase($join_type) : ''
507 );
508 }
509  
510  
511 3446 ▶sub _recurse_from {
Subroutine _recurse_from: called CC=1 SCAR=0.0
512 3446 my $self = shift;
513 3446 return join (' ', $self->_gen_from_blocks(@_) );
514 }
515  
516  
517 3446 ▶sub _gen_from_blocks {
Subroutine _gen_from_blocks: called CC=6 SCAR=17.9
518 3446 my ($self, $from, @joins) = @_;
519  
520 3446 my @fchunks = $self->_from_chunk_to_sql($from);
521  
522 3446 for (@joins) {
523 523 my ($to, $on) = @$_;
524  
525 # check whether a join type exists
526 523 ▶ my $to_jt = ref($to) eq 'ARRAY' ? $to->[0] : $to;
Branch: 1 branch
BranchTrueFalseTotal
ref $to eq 'ARRAY' ? : 2 521 523
527 523 my $join_type;
528 523 ▶ if (ref($to_jt) eq 'HASH' and defined($to_jt->{-join_type})) {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $to_jt eq "HASH" and defined $to_jt->{"-join_type"}) 268 255 523
Condition66%2/3
ref $to_jt eq "HASH" and defined $to_jt->{"-join_type"}
!ll&&!rl&&r
0255268
MC/DC50%1/2
ref $to_jt eq "HASH" and defined $to_jt->{"-join_type"}
ref $to_jt eq "HASH" defined $to_jt->{"-join_type"}
Truth table66%2/3
A and B
A= ref $to_jt eq "HASH"B= defined $to_jt->{"-join_type"}
ABresult
0X0
100
111
529 268 $join_type = $to_jt->{-join_type};
530 268 $join_type =~ s/^\s+ | \s+$//xg;
531 }
532  
533 523 my @j = $self->_generate_join_clause( $join_type );
534  
535 523 ▶ if (ref $to eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $to eq 'ARRAY') { } 2 521 523
536 2 push(@j, '(', $self->_recurse_from(@$to), ')');
537 }
538 else {
539 521 push(@j, $self->_from_chunk_to_sql($to));
540 }
541  
542 523 my ($sql, @bind) = $self->_join_condition($on);
543 523 push(@j, ' ON ', $sql);
544 523 push @{$self->{from_bind}}, @bind;
545  
546 523 push @fchunks, join '', @j;
547 }
548  
549 3446 return @fchunks;
550 }
551  
552  
553 7934 ▶sub _from_chunk_to_sql {
Subroutine _from_chunk_to_sql: called CC=7 SCAR=19.7
554 7934 my ($self, $fromspec) = @_;
555  
556 7934 return join (' ', do {
557 7934 ▶ if (! ref $fromspec) {
Branch: 4 branches
BranchTrueFalseTotal
if (not ref $fromspec) { } 2843 5091 7934
elsif (ref $fromspec eq 'SCALAR') { } 1013 4078 5091
elsif (ref $fromspec eq 'REF' and ref $$fromspec eq 'ARRAY') { } 111 3967 4078
elsif (ref $fromspec eq 'HASH') { } 3967 0 3967
558 2843 $self->_quote($fromspec);
559 }
560 elsif (ref $fromspec eq 'SCALAR') {
561 1013 $$fromspec;
562 }
563 ▶ elsif (ref $fromspec eq 'REF' and ref $$fromspec eq 'ARRAY') {
Condition66%2/3
ref $fromspec eq "REF" and ref $$fromspec eq "ARRAY"
!ll&&!rl&&r
39670111
MC/DC50%1/2
ref $fromspec eq "REF" and ref $$fromspec eq "ARRAY"
ref $fromspec eq "REF" ref $$fromspec eq "ARRAY"
Truth table66%2/3
A and B
A= ref $fromspec eq "REF"B= ref $$fromspec eq "ARRAY"
ABresult
0X0
100
111
564 111 push @{$self->{from_bind}}, @{$$fromspec}[1..$#$$fromspec];
565 111 $$fromspec->[0];
566 }
567 elsif (ref $fromspec eq 'HASH') {
568 my ($as, $table, $toomuch) = ( map
569 3967 { $_ => $fromspec->{$_} }
570 3967 ( grep { $_ !~ /^\-/ } keys %$fromspec )
571 );
572  
573 3967 ▶ $self->throw_exception( "Only one table/as pair expected in from-spec but an exra '$toomuch' key present" )
Branch: 1 branch
BranchTrueFalseTotal
if defined $toomuch 0 3967 3967
574 if defined $toomuch;
575  
576 3967 ($self->_from_chunk_to_sql($table), $self->_quote($as) );
577 }
578 else {
579 0 $self->throw_exception('Unsupported from refkind: ' . ref $fromspec );
580 }
581 });
582 }
583  
584  
585 523 ▶sub _join_condition {
Subroutine _join_condition: called CC=7 SCAR=23.6
586 523 my ($self, $cond) = @_;
587  
588 # Backcompat for the old days when a plain hashref
589 # { 't1.col1' => 't2.col2' } meant ON t1.col1 = t2.col2
590 523 ▶ if (
Branch: 2 branches
BranchTrueFalseTotal
if (ref $cond eq 'HASH' and keys %$cond == 1 and (keys %$cond)[0] =~ /\./ and not ref((values %$cond)[0])) { } 13 510 523
elsif (ref $cond eq 'ARRAY') { } 0 510 510
Condition66%6/9
ref $cond eq "HASH" and keys %$cond == 1 and (keys %$cond)[0] =~ /\./ and not ref((values %$cond)[0])
!ll&&!rl&&r
506413
ref $cond eq "HASH" and keys %$cond == 1 and (keys %$cond)[0] =~ /\./
!ll&&!rl&&r
050617
ref $cond eq "HASH" and keys %$cond == 1
!ll&&!rl&&r
00523
MC/DC50%2/4
ref $cond eq "HASH" and keys %$cond == 1 and (keys %$cond)[0] =~ /\./ and not ref((values %$cond)[0])
ref $cond eq "HASH" keys %$cond == 1 (keys %$cond)[0] =~ /\./ not ref((values %$cond)[0])
Truth table60%3/5
A and B and C and not(D)
A= ref $cond eq "HASH"B= keys %$cond == 1C= (keys %$cond)[0] =~ /\./D= not ref((values %$cond)[0])
ABCDresult
0XXX0
10XX0
110X0
11100
11111
591 ref $cond eq 'HASH'
592 and
593 keys %$cond == 1
594 and
595 (keys %$cond)[0] =~ /\./
596 and
597 ! ref ( (values %$cond)[0] )
598 ) {
599 13 carp_unique(
600 "ResultSet {from} structures with conditions not conforming to the "
601 . "SQL::Abstract syntax are deprecated: you either need to stop "
602 . "abusing {from} altogether, or express the condition properly using the "
603 . "{ -ident => ... } operator"
604 );
605 13 $cond = { keys %$cond => { -ident => values %$cond } }
606 }
607 elsif ( ref $cond eq 'ARRAY' ) {
608 # do our own ORing so that the hashref-shim above is invoked
609 0 my @parts;
610 my @binds;
611 0 foreach my $c (@$cond) {
612 0 my ($sql, @bind) = $self->_join_condition($c);
613 0 push @binds, @bind;
614 0 push @parts, $sql;
615 }
616 0 return join(' OR ', @parts), @binds;
617 }
618  
619 523 return $self->_recurse_where($cond);
620 }
621  
622 # !!! EXPERIMENTAL API !!! WILL CHANGE !!!
623 #
624 # This is rather odd, but vanilla SQLA* variants do not have support for
625 # multicolumn-IN expressions
626 # Currently has only one callsite in ResultSet, body moved into this subclass
627 # to raise API questions like:
628 # - how do we convey a list of idents...?
629 # - can binds reside on lhs?
630 #
631 # !!! EXPERIMENTAL API !!! WILL CHANGE !!!
632  
633 0 ▶sub _where_op_multicolumn_in {
Subroutine _where_op_multicolumn_in: not called CC=14 SCAR=53.5
634 0 my ($self, $lhs, $rhs) = @_;
635  
636 0 ▶ if (! ref $lhs or ref $lhs eq 'ARRAY') {
Branch: 3 branches
BranchTrueFalseTotal
if (not ref $lhs or ref $lhs eq 'ARRAY') { } 0 0 0
elsif (ref $lhs eq 'SCALAR') { } 0 0 0
elsif (ref $lhs eq 'REF' and ref $$lhs eq 'ARRAY') { } 0 0 0
Condition0%0/3
not ref $lhs or ref $lhs eq "ARRAY"
l!l&&r!l&&!r
000
MC/DC0%0/2
not ref $lhs or ref $lhs eq "ARRAY"
not ref $lhs ref $lhs eq "ARRAY"
Truth table0%0/3
not(A) or B
A= not ref $lhsB= ref $lhs eq "ARRAY"
ABresult
1X1
011
000
637 0 my (@sql, @bind);
638 0 ▶ for (ref $lhs ? @$lhs : $lhs) {
Branch: 1 branch
BranchTrueFalseTotal
ref $lhs ? : 0 0 0
639 0 ▶ if (! ref $_) {
Branch: 3 branches
BranchTrueFalseTotal
if (not ref $_) { } 0 0 0
elsif (ref $_ eq 'SCALAR') { } 0 0 0
elsif (ref $_ eq 'REF' and ref $$_ eq 'ARRAY') { } 0 0 0
640 0 push @sql, $self->_quote($_);
641 }
642 elsif (ref $_ eq 'SCALAR') {
643 0 push @sql, $$_;
644 }
645 ▶ elsif (ref $_ eq 'REF' and ref $$_ eq 'ARRAY') {
Condition0%0/3
ref $_ eq "REF" and ref $$_ eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
ref $_ eq "REF" and ref $$_ eq "ARRAY"
ref $_ eq "REF" ref $$_ eq "ARRAY"
Truth table0%0/3
A and B
A= ref $_ eq "REF"B= ref $$_ eq "ARRAY"
ABresult
0X0
100
111
646 0 my ($s, @b) = @$$_;
647 0 push @sql, $s;
648 0 push @bind, @b;
649 }
650 else {
651 0 $self->throw_exception("ARRAY of @{[ ref $_ ]}es unsupported for multicolumn IN lhs...");
652 }
653 }
654 0 $lhs = \[ join(', ', @sql), @bind];
655 }
656 elsif (ref $lhs eq 'SCALAR') {
657 0 $lhs = \[ $$lhs ];
658 }
659 ▶ elsif (ref $lhs eq 'REF' and ref $$lhs eq 'ARRAY' ) {
Condition0%0/3
ref $lhs eq "REF" and ref $$lhs eq "ARRAY"
!ll&&!rl&&r
000
MC/DC0%0/2
ref $lhs eq "REF" and ref $$lhs eq "ARRAY"
ref $lhs eq "REF" ref $$lhs eq "ARRAY"
Truth table0%0/3
A and B
A= ref $lhs eq "REF"B= ref $$lhs eq "ARRAY"
ABresult
0X0
100
111
660 # noop
661 }
662 else {
663 0 $self->throw_exception( ref($lhs) . "es unsupported for multicolumn IN lhs...");
664 }
665  
666 # is this proper...?
667 0 $rhs = \[ $self->_recurse_where($rhs) ];
668  
669 0 for ($lhs, $rhs) {
670 0 ▶ $$_->[0] = "( $$_->[0] )"
Branch: 1 branch
BranchTrueFalseTotal
unless $$_->[0] =~ /^ \s* \( .* \) \s* $/sx 0 0 0
671 unless $$_->[0] =~ /^ \s* \( .* \) \s* $/xs;
672 }
673  
674 0 \[ join( ' IN ', shift @$$lhs, shift @$$rhs ), @$$lhs, @$$rhs ];
675 }
676  
677  
678  
679 177 ▶sub apply_limit {
Subroutine apply_limit: called CC=2 SCAR=6.9
Pod documented
680 177 my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
681 177 $sql .= $self->_parse_rs_attrs( $rs_attrs ) . " LIMIT ?";
682 177 push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
683 177 ▶ if ($offset) {
Branch: 1 branch
BranchTrueFalseTotal
if ($offset) 25 152 177
684 25 $sql .= " OFFSET ?";
685 25 push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset ];
686 }
687 177 return $sql;
688 }
689  
690  
691 2 ▶sub _RowNumberOver {
Subroutine _RowNumberOver: called CC=7 SCAR=21.2
692 2 my ($self, $sql, $rs_attrs, $rows, $offset ) = @_;
693  
694 # this dialect re-chunks the raw order_by as strings (supplement + re-alias)
695 2 local $self->{_dbio_no_order_bind_transform} = 1;
696  
697 # get selectors, and scan the order_by (if any)
698 2 my $sq_attrs = $self->_subqueried_limit_attrs ( $sql, $rs_attrs );
699  
700 # make up an order if none exists
701 2 ▶ my $requested_order = (delete $rs_attrs->{order_by}) || $self->_rno_default_order;
Condition33%1/3
delete $rs_attrs->{'order_by'} || $self->_rno_default_order
l!l&&r!l&&!r
200
MC/DC0%0/2
delete $rs_attrs->{'order_by'} || $self->_rno_default_order
delete $rs_attrs->{'order_by'} $self->_rno_default_order
Truth table33%1/3
A || B
A= delete $rs_attrs->{'order_by'}B= $self->_rno_default_order
ABresult
1X1
011
000
702  
703 # the order binds (if any) will need to go at the end of the entire inner select
704 2 local $self->{order_bind};
705 2 my $rno_ord = $self->_order_by ($requested_order);
706 2 push @{$self->{select_bind}}, @{$self->{order_bind}};
707  
708 # this is the order supplement magic
709 2 my $mid_sel = $sq_attrs->{selection_outer};
710 2 ▶ if (my $extra_order_sel = $sq_attrs->{order_supplement}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $extra_order_sel = $sq_attrs->{"order_supplement"}) 0 2 2
711 0 for my $extra_col (sort
712 0 { $extra_order_sel->{$a} cmp $extra_order_sel->{$b} }
713 keys %$extra_order_sel
714 ) {
715 $sq_attrs->{selection_inner} .= sprintf (', %s AS %s',
716 $extra_col,
717 0 $extra_order_sel->{$extra_col},
718 );
719 }
720 }
721  
722 # and this is order re-alias magic
723 2 for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) {
724 0 ▶ for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}} ) {
Condition50%1/2
$map || {}
l!l
04
MC/DC0%0/1
$map || {}
$map
Truth table50%1/2
A
A= $map
Aresult
11
00
725 0 my $re_col = quotemeta ($col);
726 0 $rno_ord =~ s/$re_col/$map->{$col}/;
727 }
728 }
729  
730 # whatever is left of the order_by (only where is processed at this point)
731 2 my $group_having = $self->_parse_rs_attrs($rs_attrs);
732  
733 2 my $qalias = $self->_quote ($rs_attrs->{alias});
734 2 my $idx_name = $self->_quote ('rno__row__index');
735  
736 2 push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset + 1], [ $self->__total_bindtype => $offset + $rows ];
737  
738 2 return <<EOS;
739  
740 SELECT $sq_attrs->{selection_outer} FROM (
741 SELECT $mid_sel, ROW_NUMBER() OVER( $rno_ord ) AS $idx_name FROM (
742 SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${group_having}
743 ) $qalias
744 ) $qalias WHERE $idx_name >= ? AND $idx_name <= ?
745  
746 EOS
747  
748 }
749  
750 # some databases are happy with OVER (), some need OVER (ORDER BY (SELECT (1)) )
751  
752 0 ▶sub _rno_default_order {
Subroutine _rno_default_order: not called CC=1 SCAR=6.9
753 0 return undef;
754 }
755  
756  
757 2 ▶sub _SkipFirst {
Subroutine _SkipFirst: called CC=3 SCAR=11.0
758 2 my ($self, $sql, $rs_attrs, $rows, $offset) = @_;
759  
760 2 ▶ $sql =~ s/^ \s* SELECT \s+ //ix
Branch: 1 branch
BranchTrueFalseTotal
$sql =~ s/^ \s* SELECT \s+ //xi or $self->throw_exception("Unrecognizable SELECT: $sql") 2 0 2
761 or $self->throw_exception("Unrecognizable SELECT: $sql");
762  
763 return sprintf ('SELECT %s%s%s%s',
764 $offset
765 ? do {
766 1 push @{$self->{pre_select_bind}}, [ $self->__offset_bindtype => $offset];
767 1 'SKIP ? '
768 }
769 : ''
770 ,
771 2 ▶ do {
Branch: 1 branch
BranchTrueFalseTotal
$offset ? : 1 1 2
772 2 push @{$self->{pre_select_bind}}, [ $self->__rows_bindtype => $rows ];
773 2 'FIRST ? '
774 },
775 $sql,
776 $self->_parse_rs_attrs ($rs_attrs),
777 );
778 }
779  
780  
781 2 ▶sub _FirstSkip {
Subroutine _FirstSkip: called CC=3 SCAR=11.0
782 2 my ($self, $sql, $rs_attrs, $rows, $offset) = @_;
783  
784 2 ▶ $sql =~ s/^ \s* SELECT \s+ //ix
Branch: 1 branch
BranchTrueFalseTotal
$sql =~ s/^ \s* SELECT \s+ //xi or $self->throw_exception("Unrecognizable SELECT: $sql") 2 0 2
785 or $self->throw_exception("Unrecognizable SELECT: $sql");
786  
787 return sprintf ('SELECT %s%s%s%s',
788 do {
789 2 push @{$self->{pre_select_bind}}, [ $self->__rows_bindtype => $rows ];
790 2 ▶ 'FIRST ? '
Branch: 1 branch
BranchTrueFalseTotal
$offset ? : 1 1 2
791 },
792 $offset
793 2 ? do {
794 1 push @{$self->{pre_select_bind}}, [ $self->__offset_bindtype => $offset];
795 1 'SKIP ? '
796 }
797 : ''
798 ,
799 $sql,
800 $self->_parse_rs_attrs ($rs_attrs),
801 );
802 }
803  
804  
805 2 ▶sub _RowNum {
Subroutine _RowNum: called CC=4 SCAR=14.4
806 2 my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
807  
808 # this dialect re-chunks the raw order_by as strings (supplement + re-alias)
809 2 local $self->{_dbio_no_order_bind_transform} = 1;
810  
811 2 my $sq_attrs = $self->_subqueried_limit_attrs ($sql, $rs_attrs);
812  
813 2 my $qalias = $self->_quote ($rs_attrs->{alias});
814 2 my $idx_name = $self->_quote ('rownum__index');
815 2 my $order_group_having = $self->_parse_rs_attrs($rs_attrs);
816  
817  
818 # if no offset (e.g. first page) - we can skip one of the subqueries
819 2 ▶ if (! $offset) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($offset) 1 1 2
820 1 push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
821  
822 1 return <<EOS;
823 SELECT $sq_attrs->{selection_outer} FROM (
824 SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${order_group_having}
825 ) $qalias WHERE ROWNUM <= ?
826 EOS
827 }
828  
829 #
830 # There are two ways to limit in Oracle, one vastly faster than the other
831 # on large resultsets: https://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/
832 # However Oracle is retarded and does not preserve stable ROWNUM() values
833 # when called twice in the same scope. Therefore unless the resultset is
834 # ordered by a unique set of columns, it is not safe to use the faster
835 # method, and the slower BETWEEN query is used instead
836 #
837 # FIXME - this is quite expensive, and does not perform caching of any sort
838 # as soon as some of the SQLMaker-inlining work becomes viable consider adding
839 # some rudimentary caching support
840 1 ▶ if (
Branch: 1 branch
BranchTrueFalseTotal
if ($rs_attrs->{'order_by'} and $rs_attrs->{'result_source'}->storage->_order_by_is_stable(@{$rs_attrs;}{'from', 'order_by', 'where'})) { } 1 0 1
Condition33%1/3
$rs_attrs->{"order_by"} and $rs_attrs->{"result_source"}->storage->_order_by_is_stable(@{$rs_attrs;}{"from", "order_by", "where"})
!ll&&!rl&&r
001
MC/DC0%0/2
$rs_attrs->{"order_by"} and $rs_attrs->{"result_source"}->storage->_order_by_is_stable(@{$rs_attrs;}{"from", "order_by", "where"})
$rs_attrs->{"order_by"} $rs_attrs->{"result_source"}->storage->_order_by_is_stable(@{$rs_attrs;}{"from", "order_by", "where"})
Truth table33%1/3
A and B
A= $rs_attrs->{"order_by"}B= $rs_attrs->{"result_source"}->storage->_order_by_is_stable(@{$rs_attrs;}{"from", "order_by", "where"})
ABresult
0X0
100
111
841 $rs_attrs->{order_by}
842 and
843 $rs_attrs->{result_source}->storage->_order_by_is_stable(
844 1 @{$rs_attrs}{qw/from order_by where/}
845 )
846 ) {
847 1 push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
848  
849 1 return <<EOS;
850 SELECT $sq_attrs->{selection_outer} FROM (
851 SELECT $sq_attrs->{selection_outer}, ROWNUM AS $idx_name FROM (
852 SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${order_group_having}
853 ) $qalias WHERE ROWNUM <= ?
854 ) $qalias WHERE $idx_name >= ?
855 EOS
856 }
857 else {
858 0 push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset + 1 ], [ $self->__total_bindtype => $offset + $rows ];
859  
860 0 return <<EOS;
861 SELECT $sq_attrs->{selection_outer} FROM (
862 SELECT $sq_attrs->{selection_outer}, ROWNUM AS $idx_name FROM (
863 SELECT $sq_attrs->{selection_inner} $sq_attrs->{query_leftover}${order_group_having}
864 ) $qalias
865 ) $qalias WHERE $idx_name BETWEEN ? AND ?
866 EOS
867 }
868 }
869  
870 # used by _Top and _FetchFirst below
871  
872 4 ▶sub _prep_for_skimming_limit {
Subroutine _prep_for_skimming_limit: called CC=16 SCAR=32.8
873 4 my ( $self, $sql, $rs_attrs ) = @_;
874  
875 # this dialect re-chunks the raw order_by as strings (supplement + re-alias)
876 4 local $self->{_dbio_no_order_bind_transform} = 1;
877  
878 # get selectors
879 4 my $sq_attrs = $self->_subqueried_limit_attrs ($sql, $rs_attrs);
880  
881 4 my $requested_order = delete $rs_attrs->{order_by};
882 4 $sq_attrs->{order_by_requested} = $self->_order_by ($requested_order);
883 4 $sq_attrs->{grpby_having} = $self->_parse_rs_attrs ($rs_attrs);
884  
885 # without an offset things are easy
886 4 ▶ if (! $rs_attrs->{offset}) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $rs_attrs->{'offset'}) { } 2 2 4
887 2 $sq_attrs->{order_by_inner} = $sq_attrs->{order_by_requested};
888 }
889 else {
890 2 $sq_attrs->{quoted_rs_alias} = $self->_quote ($rs_attrs->{alias});
891  
892 # localise as we already have all the bind values we need
893 2 local $self->{order_bind};
894  
895 # make up an order unless supplied or sanity check what we are given
896 2 my $inner_order;
897 2 ▶ if ($sq_attrs->{order_by_requested}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sq_attrs->{'order_by_requested'}) { } 2 0 2
898 $self->throw_exception (
899 'Unable to safely perform "skimming type" limit with supplied unstable order criteria'
900 ) unless ($rs_attrs->{result_source}->schema->storage->_order_by_is_stable(
901 $rs_attrs->{from},
902 $requested_order,
903 $rs_attrs->{where},
904 2 ▶ ));
Branch: 1 branch
BranchTrueFalseTotal
unless $rs_attrs->{"result_source"}->schema->storage->_order_by_is_stable($rs_attrs->{"from"}, $requested_order, $rs_attrs->{"where"}) 0 2 2
905  
906 2 $inner_order = $requested_order;
907 }
908 else {
909 $inner_order = [ map
910 0 { "$rs_attrs->{alias}.$_" }
911 ( @{
912 0 $rs_attrs->{result_source}->_identifying_column_set
913 ||
914 $self->throw_exception(sprintf(
915 'Unable to auto-construct stable order criteria for "skimming type" limit '
916 0 ▶ . "dialect based on source '%s'", $rs_attrs->{result_source}->name) );
Condition0%0/3
$rs_attrs->{'result_source'}->_identifying_column_set || $self->throw_exception(sprintf(q[Unable to auto-construct stable order criteria for "skimming type" limit dialect based on source '%s'], $rs_attrs->{'result_source'}->name))
l!l&&r!l&&!r
000
MC/DC0%0/2
$rs_attrs->{'result_source'}->_identifying_column_set || $self->throw_exception(sprintf(q[Unable to auto-construct stable order criteria for "skimming type" limit dialect based on source '%s'], $rs_attrs->{'result_source'}->name))
$rs_attrs->{'result_source'}->_identifying_column_set $self->throw_exception(sprintf(q[Unable to auto-construct stable order criteria for "skimming type" limit dialect based on source '%s'], $rs_attrs->{'result_source'}->name))
Truth table0%0/3
A || B
A= $rs_attrs->{'result_source'}->_identifying_column_setB= $self->throw_exception(sprintf(q[Unable to auto-construct stable order criteria for "skimming type" limit dialect based on source '%s'], $rs_attrs->{'result_source'}->name))
ABresult
1X1
011
000
917 } )
918 ];
919 }
920  
921 2 $sq_attrs->{order_by_inner} = $self->_order_by ($inner_order);
922  
923 2 my @out_chunks;
924 2 for my $ch ($self->_order_by_chunks ($inner_order)) {
925 4 ▶ $ch = $ch->[0] if ref $ch eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
if ref $ch eq "ARRAY" 2 2 4
926  
927 4 ($ch, my $is_desc, my $nulls) = $self->_split_order_chunk($ch);
928  
929 # !NOTE! outside chunks come in reverse order ( !$is_desc ),
930 # and so does the position of the NULLs
931 4 ▶ push @out_chunks, {
Branch: 3 branches
BranchTrueFalseTotal
$is_desc ? : 2 2 4
$nulls ? : 2 2 4
$nulls eq 'FIRST' ? : 0 2 2
932 ($is_desc ? '-asc' : '-desc') => \$ch,
933 ( $nulls ? ( -nulls => ($nulls eq 'FIRST' ? 'last' : 'first') ) : () ),
934 };
935 }
936  
937 2 $sq_attrs->{order_by_middle} = $self->_order_by (\@out_chunks);
938  
939 # this is the order supplement magic
940 2 $sq_attrs->{selection_middle} = $sq_attrs->{selection_outer};
941 2 ▶ if (my $extra_order_sel = $sq_attrs->{order_supplement}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $extra_order_sel = $sq_attrs->{"order_supplement"}) 0 2 2
942 0 for my $extra_col (sort
943 0 { $extra_order_sel->{$a} cmp $extra_order_sel->{$b} }
944 keys %$extra_order_sel
945 ) {
946 $sq_attrs->{selection_inner} .= sprintf (', %s AS %s',
947 $extra_col,
948 0 $extra_order_sel->{$extra_col},
949 );
950  
951 0 $sq_attrs->{selection_middle} .= ', ' . $extra_order_sel->{$extra_col};
952 }
953  
954 # Whatever order bindvals there are, they will be realiased and
955 # reselected, and need to show up at end of the initial inner select
956 0 push @{$self->{select_bind}}, @{$self->{order_bind}};
957 }
958  
959 # and this is order re-alias magic
960 2 for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) {
961 0 ▶ for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}}) {
Condition50%1/2
$map || {}
l!l
04
MC/DC0%0/1
$map || {}
$map
Truth table50%1/2
A
A= $map
Aresult
11
00
962 0 my $re_col = quotemeta ($col);
963 $_ =~ s/$re_col/$map->{$col}/
964 0 for ($sq_attrs->{order_by_middle}, $sq_attrs->{order_by_requested});
965 }
966 }
967 }
968  
969 4 $sq_attrs;
970 }
971  
972  
973 2 ▶sub _Top {
Subroutine _Top: called CC=7 SCAR=19.7
974 2 my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
975  
976 2 my $lim = $self->_prep_for_skimming_limit($sql, $rs_attrs);
977  
978 $sql = sprintf ('SELECT TOP %u %s %s %s %s',
979 $rows + ($offset||0),
980 $offset ? $lim->{selection_inner} : $lim->{selection_original},
981 $lim->{query_leftover},
982 $lim->{grpby_having},
983 $lim->{order_by_inner},
984 2 ▶ );
Branch: 1 branch
BranchTrueFalseTotal
$offset ? : 1 1 2
Condition100%2/2
$offset || 0
l!l
11
MC/DC100%1/1
$offset || 0
$offset
Truth table100%2/2
A
A= $offset
Aresult
11
00
985  
986 $sql = sprintf ('SELECT TOP %u %s FROM ( %s ) %s %s',
987 $rows,
988 $lim->{selection_middle},
989 $sql,
990 $lim->{quoted_rs_alias},
991 $lim->{order_by_middle},
992 2 ▶ ) if $offset;
Branch: 1 branch
BranchTrueFalseTotal
if $offset 1 1 2
993  
994 $sql = sprintf ('SELECT %s FROM ( %s ) %s %s',
995 $lim->{selection_outer},
996 $sql,
997 $lim->{quoted_rs_alias},
998 $lim->{order_by_requested},
999 ) if $offset and (
1000 $lim->{order_by_requested} or $lim->{selection_middle} ne $lim->{selection_outer}
1001 2 ▶ );
Branch: 1 branch
BranchTrueFalseTotal
if $offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"} 1 1 2
Condition50%3/6
$offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"}
!ll&&!rl&&r
101
$lim->{"order_by_requested"} or $lim->{"selection_middle"} ne $lim->{"selection_outer"}
l!l&&r!l&&!r
100
MC/DC33%1/3
$offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"}
$offset $lim->{"order_by_requested"} $lim->{"selection_middle"} ne $lim->{"selection_outer"}
Truth table50%2/4
A and B or C
A= $offsetB= $lim->{"order_by_requested"}C= $lim->{"selection_middle"} ne $lim->{"selection_outer"}
ABCresult
0XX0
1000
11X1
1011
1002  
1003 2 return $sql;
1004 }
1005  
1006  
1007 2 ▶sub _FetchFirst {
Subroutine _FetchFirst: called CC=7 SCAR=19.7
1008 2 my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
1009  
1010 2 my $lim = $self->_prep_for_skimming_limit($sql, $rs_attrs);
1011  
1012 $sql = sprintf ('SELECT %s %s %s %s FETCH FIRST %u ROWS ONLY',
1013 $offset ? $lim->{selection_inner} : $lim->{selection_original},
1014 $lim->{query_leftover},
1015 $lim->{grpby_having},
1016 $lim->{order_by_inner},
1017 2 ▶ $rows + ($offset||0),
Branch: 1 branch
BranchTrueFalseTotal
$offset ? : 1 1 2
Condition100%2/2
$offset || 0
l!l
11
MC/DC100%1/1
$offset || 0
$offset
Truth table100%2/2
A
A= $offset
Aresult
11
00
1018 );
1019  
1020 $sql = sprintf ('SELECT %s FROM ( %s ) %s %s FETCH FIRST %u ROWS ONLY',
1021 $lim->{selection_middle},
1022 $sql,
1023 $lim->{quoted_rs_alias},
1024 $lim->{order_by_middle},
1025 2 ▶ $rows,
Branch: 1 branch
BranchTrueFalseTotal
if $offset 1 1 2
1026 ) if $offset;
1027  
1028  
1029 $sql = sprintf ('SELECT %s FROM ( %s ) %s %s',
1030 $lim->{selection_outer},
1031 $sql,
1032 $lim->{quoted_rs_alias},
1033 $lim->{order_by_requested},
1034 ) if $offset and (
1035 $lim->{order_by_requested} or $lim->{selection_middle} ne $lim->{selection_outer}
1036 2 ▶ );
Branch: 1 branch
BranchTrueFalseTotal
if $offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"} 1 1 2
Condition50%3/6
$offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"}
!ll&&!rl&&r
101
$lim->{"order_by_requested"} or $lim->{"selection_middle"} ne $lim->{"selection_outer"}
l!l&&r!l&&!r
100
MC/DC33%1/3
$offset and $lim->{"order_by_requested"} || $lim->{"selection_middle"} ne $lim->{"selection_outer"}
$offset $lim->{"order_by_requested"} $lim->{"selection_middle"} ne $lim->{"selection_outer"}
Truth table50%2/4
A and B or C
A= $offsetB= $lim->{"order_by_requested"}C= $lim->{"selection_middle"} ne $lim->{"selection_outer"}
ABCresult
0XX0
1000
11X1
1011
1037  
1038 2 return $sql;
1039 }
1040  
1041  
1042 8 ▶sub _GenericSubQ {
Subroutine _GenericSubQ: called CC=22 SCAR=31.0
1043 8 my ($self, $sql, $rs_attrs, $rows, $offset) = @_;
1044  
1045 # this dialect re-chunks the raw order_by as strings (column-name matching)
1046 8 local $self->{_dbio_no_order_bind_transform} = 1;
1047  
1048 8 my $main_rsrc = $rs_attrs->{result_source};
1049  
1050 # Explicitly require an order_by
1051 # GenSubQ is slow enough as it is, just emulating things
1052 # like in other cases is not wise - make the user work
1053 # to shoot their DBA in the foot
1054 $self->throw_exception (
1055 'Generic Subquery Limit does not work on resultsets without an order. Provide a stable, '
1056 . 'main-table-based order criteria.'
1057 8 ▶ ) unless $rs_attrs->{order_by};
Branch: 1 branch
BranchTrueFalseTotal
unless $rs_attrs->{"order_by"} 0 8 8
1058  
1059 8 my $usable_order_colinfo = $main_rsrc->storage->_extract_colinfo_of_stable_main_source_order_by_portion(
1060 $rs_attrs
1061 );
1062  
1063 $self->throw_exception(
1064 'Generic Subquery Limit can not work with order criteria based on sources other than the main one'
1065 ) if (
1066 8 ▶ ! keys %{$usable_order_colinfo||{}}
Condition50%1/2
$usable_order_colinfo || {}
l!l
80
MC/DC0%0/1
$usable_order_colinfo || {}
$usable_order_colinfo
Truth table50%1/2
A
A= $usable_order_colinfo
Aresult
11
00
1067 or
1068 grep
1069 8 ▶ { $_->{-source_alias} ne $rs_attrs->{alias} }
Branch: 1 branch
BranchTrueFalseTotal
if not keys %{$usable_order_colinfo or {};} or grep {$_->{"-source_alias"} ne $rs_attrs->{"alias"};} values %$usable_order_colinfo 0 8 8
Condition33%1/3
not keys %{$usable_order_colinfo or {};} or grep {$_->{"-source_alias"} ne $rs_attrs->{"alias"};} values %$usable_order_colinfo
l!l&&r!l&&!r
008
MC/DC0%0/2
not keys %{$usable_order_colinfo or {};} or grep {$_->{"-source_alias"} ne $rs_attrs->{"alias"};} values %$usable_order_colinfo
not keys %{$usable_order_colinfo or {};} grep {$_->{"-source_alias"} ne $rs_attrs->{"alias"};} values %$usable_order_colinfo
Truth table33%1/3
not(A) or B
A= not keys %{$usable_order_colinfo or {};}B= grep {$_->{"-source_alias"} ne $rs_attrs->{"alias"};} values %$usable_order_colinfo
ABresult
1X1
011
000
1070 (values %$usable_order_colinfo)
1071 );
1072  
1073 ###
1074 ###
1075 ### we need to know the directions after we figured out the above - reextract *again*
1076 ### this is eyebleed - trying to get it to work at first
1077 8 my $supplied_order = delete $rs_attrs->{order_by};
1078  
1079 8 my @order_bits = do {
1080 8 local $self->{quote_char};
1081 8 local $self->{order_bind};
1082 8 ▶ map { ref $_ ? $_->[0] : $_ } $self->_order_by_chunks ($supplied_order)
Branch: 1 branch
BranchTrueFalseTotal
ref $_ ? : 8 8 16
1083 };
1084  
1085 # truncate to what we'll use
1086 8 $#order_bits = ( (keys %$usable_order_colinfo) - 1 );
1087  
1088 # @order_bits likely will come back quoted (due to how the prefetch
1089 # rewriter operates
1090 # Hence supplement the column_info lookup table with quoted versions
1091 8 ▶ if ($self->quote_char) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->quote_char) 8 0 8
1092 $usable_order_colinfo->{$self->_quote($_)} = $usable_order_colinfo->{$_}
1093 8 for keys %$usable_order_colinfo;
1094 }
1095  
1096 # calculate the condition
1097 8 my $count_tbl_alias = 'rownum__emulation';
1098 8 my $main_alias = $rs_attrs->{alias};
1099 8 my $main_tbl_name = $main_rsrc->name;
1100  
1101 8 my (@unqualified_names, @qualified_names, @is_desc, @nulls_first, @new_order_by);
1102  
1103 8 for my $bit (@order_bits) {
1104  
1105 16 ($bit, my $is_desc, my $nulls) = $self->_split_order_chunk($bit);
1106  
1107 16 push @is_desc, $is_desc;
1108 # an explicit NULLS position wins, otherwise ASC has NULLs first
1109 16 ▶ push @nulls_first, $nulls ? ($nulls eq 'FIRST' ? 1 : 0) : ($is_desc ? 0 : 1);
Branch: 3 branches
BranchTrueFalseTotal
$nulls ? : 6 10 16
$nulls eq 'FIRST' ? : 3 3 6
$is_desc ? : 1 9 10
1110 16 push @unqualified_names, $usable_order_colinfo->{$bit}{-colname};
1111 16 push @qualified_names, $usable_order_colinfo->{$bit}{-fq_colname};
1112  
1113 push @new_order_by, {
1114 ($is_desc ? '-desc' : '-asc') => $usable_order_colinfo->{$bit}{-fq_colname},
1115 16 ▶ ( $nulls ? ( -nulls => lc $nulls ) : () ),
Branch: 2 branches
BranchTrueFalseTotal
$is_desc ? : 4 12 16
$nulls ? : 6 10 16
1116 };
1117 };
1118  
1119 8 my (@where_cond, @skip_colpair_stack);
1120 8 for my $i (0 .. $#order_bits) {
1121 16 my $ci = $usable_order_colinfo->{$order_bits[$i]};
1122  
1123 16 my ($subq_col, $main_col) = map { "$_.$ci->{-colname}" } ($count_tbl_alias, $main_alias);
1124 16 ▶ my $cur_cond = { $subq_col => { ($is_desc[$i] ? '>' : '<') => { -ident => $main_col } } };
Branch: 1 branch
BranchTrueFalseTotal
$is_desc[$i] ? : 4 12 16
1125  
1126 16 push @skip_colpair_stack, [
1127 { $main_col => { -ident => $subq_col } },
1128 ];
1129  
1130 # we can trust the nullability flag because
1131 # we already used it during _id_col_set resolution
1132 #
1133 16 ▶ if ($ci->{is_nullable}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($ci->{"is_nullable"}) 6 10 16
1134 6 push @{$skip_colpair_stack[-1]}, { $main_col => undef, $subq_col=> undef };
1135  
1136 6 ▶ $cur_cond = [
Branch: 2 branches
BranchTrueFalseTotal
$nulls_first[$i] ? : 3 3 6
$nulls_first[$i] ? : 3 3 6
1137 {
1138 ($nulls_first[$i] ? $main_col : $subq_col) => { '!=', undef },
1139 ($nulls_first[$i] ? $subq_col : $main_col) => undef,
1140 },
1141 {
1142 $subq_col => { '!=', undef },
1143 $main_col => { '!=', undef },
1144 -and => $cur_cond,
1145 },
1146 ];
1147 }
1148  
1149 16 push @where_cond, { '-and', => [ @skip_colpair_stack[0..$i-1], $cur_cond ] };
1150 }
1151  
1152 # reuse the sqlmaker WHERE, this will not be returning binds
1153 8 my $counted_where = do {
1154 8 local $self->{where_bind};
1155 8 $self->where(\@where_cond);
1156 };
1157  
1158 # construct the rownum condition by hand
1159 8 my $rownum_cond;
1160 8 ▶ if ($offset) {
Branch: 1 branch
BranchTrueFalseTotal
if ($offset) { } 7 1 8
1161 7 $rownum_cond = 'BETWEEN ? AND ?';
1162 7 push @{$self->{limit_bind}},
1163 [ $self->__offset_bindtype => $offset ],
1164 [ $self->__total_bindtype => $offset + $rows - 1]
1165 ;
1166 }
1167 else {
1168 1 $rownum_cond = '< ?';
1169 1 push @{$self->{limit_bind}},
1170 [ $self->__rows_bindtype => $rows ]
1171 ;
1172 }
1173  
1174 # and what we will order by inside
1175 8 my $inner_order_sql = do {
1176 8 local $self->{order_bind};
1177  
1178 8 my $s = $self->_order_by (\@new_order_by);
1179  
1180 $self->throw_exception('Inner gensubq order may not contain binds... something went wrong')
1181 8 ▶ if @{$self->{order_bind}};
Branch: 1 branch
BranchTrueFalseTotal
if @{$self->{"order_bind"};} 0 8 8
1182  
1183 8 $s;
1184 };
1185  
1186 ### resume originally scheduled programming
1187 ###
1188 ###
1189  
1190 # we need to supply the order for the supplements to be properly calculated
1191 8 my $sq_attrs = $self->_subqueried_limit_attrs (
1192 $sql, { %$rs_attrs, order_by => \@new_order_by }
1193 );
1194  
1195 8 my $in_sel = $sq_attrs->{selection_inner};
1196  
1197 # add the order supplement (if any) as this is what will be used for the outer WHERE
1198 8 $in_sel .= ", $_" for sort keys %{$sq_attrs->{order_supplement}};
1199  
1200 8 my $group_having_sql = $self->_parse_rs_attrs($rs_attrs);
1201  
1202  
1203 return sprintf ("
1204 SELECT $sq_attrs->{selection_outer}
1205 FROM (
1206 SELECT $in_sel $sq_attrs->{query_leftover}${group_having_sql}
1207 ) %s
1208 WHERE ( SELECT COUNT(*) FROM %s %s $counted_where ) $rownum_cond
1209 $inner_order_sql
1210 24 ", map { $self->_quote ($_) } (
1211 $rs_attrs->{alias},
1212 8 $main_tbl_name,
1213 $count_tbl_alias,
1214 ));
1215 }
1216  
1217  
1218 # !!! THIS IS ALSO HORRIFIC !!! /me ashamed
1219 #
1220 # Generates inner/outer select lists for various limit dialects
1221 # which result in one or more subqueries (e.g. RNO, Top, RowNum)
1222 # Any non-main-table columns need to have their table qualifier
1223 # turned into a column alias (otherwise names in subqueries clash
1224 # and/or lose their source table)
1225 #
1226 # Returns mangled proto-sql, inner/outer strings of SQL QUOTED selectors
1227 # with aliases (to be used in whatever select statement), and an alias
1228 # index hashref of QUOTED SEL => QUOTED ALIAS pairs (to maybe be used
1229 # for string-subst higher up).
1230 # If an order_by is supplied, the inner select needs to bring out columns
1231 # used in implicit (non-selected) orders, and the order condition itself
1232 # needs to be realiased to the proper names in the outer query. Thus we
1233 # also return a hashref (order doesn't matter) of QUOTED EXTRA-SEL =>
1234 # QUOTED ALIAS pairs, which is a list of extra selectors that do *not*
1235 # exist in the original select list
1236  
1237 16 ▶sub _subqueried_limit_attrs {
Subroutine _subqueried_limit_attrs: called CC=22 SCAR=40.1
1238 16 my ($self, $proto_sql, $rs_attrs) = @_;
1239  
1240 16 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless ref $rs_attrs eq "HASH" 0 16 16
1241 'Limit dialect implementation usable only in the context of DBIO (missing $rs_attrs)'
1242 ) unless ref ($rs_attrs) eq 'HASH';
1243  
1244 # mangle the input sql as we will be replacing the selector entirely
1245 16 ▶ unless (
Branch: 1 branch
BranchTrueFalseTotal
unless ($rs_attrs->{"_selector_sql"} and $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{"_selector_sql"}\E//xi) 0 16 16
Condition33%1/3
$rs_attrs->{"_selector_sql"} and $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{"_selector_sql"}\E//xi
!ll&&!rl&&r
0016
MC/DC0%0/2
$rs_attrs->{"_selector_sql"} and $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{"_selector_sql"}\E//xi
$rs_attrs->{"_selector_sql"} $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{"_selector_sql"}\E//xi
Truth table33%1/3
A and B
A= $rs_attrs->{"_selector_sql"}B= $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{"_selector_sql"}\E//xi
ABresult
0X0
100
111
1246 $rs_attrs->{_selector_sql}
1247 and
1248 $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{_selector_sql}//ix
1249 ) {
1250 0 $self->throw_exception("Unrecognizable SELECT: $proto_sql");
1251 }
1252  
1253 16 my ($re_sep, $re_alias) = map { quotemeta $_ } ( $self->{name_sep}, $rs_attrs->{alias} );
1254  
1255 # correlate select and as, build selection index
1256 16 my (@sel, $in_sel_index);
1257 16 for my $i (0 .. $#{$rs_attrs->{select}}) {
1258  
1259 96 my $s = $rs_attrs->{select}[$i];
1260 96 ▶ my $sql_alias = (ref $s) eq 'HASH' ? $s->{-as} : undef;
Branch: 1 branch
BranchTrueFalseTotal
ref $s eq 'HASH' ? : 0 96 96
1261  
1262 # we throw away the @bind here deliberately
1263 96 my ($sql_sel) = $self->_recurse_fields ($s);
1264  
1265 push @sel, {
1266 arg => $s,
1267 sql => $sql_sel,
1268 unquoted_sql => do {
1269 96 local $self->{quote_char};
1270 96 ▶ ($self->_recurse_fields ($s))[0]; # ignore binds again
Condition33%2/6
$sql_alias || $rs_attrs->{'as'}[$i] || $self->throw_exception("Select argument $i ($s) without corresponding 'as'")
l!l&&r!l&&!r
9600
$sql_alias or $rs_attrs->{"as"}[$i]
l!l&&r!l&&!r
0960
MC/DC0%0/3
$sql_alias || $rs_attrs->{'as'}[$i] || $self->throw_exception("Select argument $i ($s) without corresponding 'as'")
$sql_alias $rs_attrs->{"as"}[$i] $self->throw_exception("Select argument $i ($s) without corresponding 'as'")
Truth table25%1/4
A or B || C
A= $sql_aliasB= $rs_attrs->{"as"}[$i]C= $self->throw_exception("Select argument $i ($s) without corresponding 'as'")
ABCresult
1XX1
01X1
0011
0000
1271 },
1272 as =>
1273 $sql_alias
1274 ||
1275 96 $rs_attrs->{as}[$i]
1276 ||
1277 $self->throw_exception("Select argument $i ($s) without corresponding 'as'")
1278 ,
1279 };
1280  
1281 # anything with a placeholder in it needs re-selection
1282 96 ▶ $in_sel_index->{$sql_sel}++ unless $sql_sel =~ / (?: ^ | \W ) \? (?: \W | $ ) /x;
Branch: 1 branch
BranchTrueFalseTotal
unless $sql_sel =~ / (?: ^ | \W ) \? (?: \W | $ ) /x 96 0 96
1283  
1284 96 ▶ $in_sel_index->{$self->_quote ($sql_alias)}++ if $sql_alias;
Branch: 1 branch
BranchTrueFalseTotal
if $sql_alias 0 96 96
1285  
1286 # record unqualified versions too, so we do not have
1287 # to reselect the same column twice (in qualified and
1288 # unqualified form)
1289 96 ▶ if (! ref $s && $sql_sel =~ / $re_sep (.+) $/x) {
Branch: 1 branch
BranchTrueFalseTotal
if (not ref $s and $sql_sel =~ / $re_sep (.+) $/x) 96 0 96
Condition33%1/3
not ref $s and $sql_sel =~ / $re_sep (.+) $/x
!ll&&!rl&&r
0096
MC/DC0%0/2
not ref $s and $sql_sel =~ / $re_sep (.+) $/x
not ref $s $sql_sel =~ / $re_sep (.+) $/x
Truth table33%1/3
not(A) and B
A= not ref $sB= $sql_sel =~ / $re_sep (.+) $/x
ABresult
0X0
100
111
1290 96 $in_sel_index->{$1}++;
1291 }
1292 }
1293  
1294  
1295 # re-alias and remove any name separators from aliases,
1296 # unless we are dealing with the current source alias
1297 # (which will transcend the subqueries as it is necessary
1298 # for possible further chaining)
1299 # same for anything we do not recognize
1300 16 my ($sel, $renamed);
1301 16 for my $node (@sel) {
1302 96 push @{$sel->{original}}, $node->{sql};
1303  
1304 96 ▶ if (
Branch: 1 branch
BranchTrueFalseTotal
if (not $$in_sel_index{$node->{'sql'}} or $node->{'as'} =~ / (?<! ^ $re_alias ) \. /x or $node->{'unquoted_sql'} =~ / (?<! ^ $re_alias ) $re_sep /x) { } 0 96 96
Condition33%2/6
not $$in_sel_index{$node->{"sql"}} or $node->{"as"} =~ / (?<! ^ $re_alias ) \. /x or $node->{"unquoted_sql"} =~ / (?<! ^ $re_alias ) $re_sep /x
l!l&&r!l&&!r
0096
not $$in_sel_index{$node->{"sql"}} or $node->{"as"} =~ / (?<! ^ $re_alias ) \. /x
l!l&&r!l&&!r
0096
MC/DC0%0/3
not $$in_sel_index{$node->{"sql"}} or $node->{"as"} =~ / (?<! ^ $re_alias ) \. /x or $node->{"unquoted_sql"} =~ / (?<! ^ $re_alias ) $re_sep /x
not $$in_sel_index{$node->{"sql"}} $node->{"as"} =~ / (?<! ^ $re_alias ) \. /x $node->{"unquoted_sql"} =~ / (?<! ^ $re_alias ) $re_sep /x
Truth table25%1/4
not(A) or B or C
A= not $$in_sel_index{$node->{"sql"}}B= $node->{"as"} =~ / (?<! ^ $re_alias ) \. /xC= $node->{"unquoted_sql"} =~ / (?<! ^ $re_alias ) $re_sep /x
ABCresult
1XX1
01X1
0011
0000
1305 ! $in_sel_index->{$node->{sql}}
1306 or
1307 $node->{as} =~ / (?<! ^ $re_alias ) \. /x
1308 or
1309 $node->{unquoted_sql} =~ / (?<! ^ $re_alias ) $re_sep /x
1310 ) {
1311 0 $node->{as} = $self->_unqualify_colname($node->{as});
1312 0 my $quoted_as = $self->_quote($node->{as});
1313 0 push @{$sel->{inner}}, sprintf '%s AS %s', $node->{sql}, $quoted_as;
1314 0 push @{$sel->{outer}}, $quoted_as;
1315 0 $renamed->{$node->{sql}} = $quoted_as;
1316 }
1317 else {
1318 96 push @{$sel->{inner}}, $node->{sql};
1319 96 ▶ push @{$sel->{outer}}, $self->_quote (ref $node->{arg} ? $node->{as} : $node->{arg});
Branch: 1 branch
BranchTrueFalseTotal
ref $node->{'arg'} ? : 0 96 96
1320 }
1321 }
1322  
1323 # see if the order gives us anything
1324 16 my $extra_order_sel;
1325 16 for my $chunk ($self->_order_by_chunks ($rs_attrs->{order_by})) {
1326 # order with bind
1327 32 ▶ $chunk = $chunk->[0] if (ref $chunk) eq 'ARRAY';
Branch: 1 branch
BranchTrueFalseTotal
if ref $chunk eq "ARRAY" 24 8 32
1328 32 ($chunk) = $self->_split_order_chunk($chunk);
1329  
1330 32 ▶ next if $in_sel_index->{$chunk};
Branch: 1 branch
BranchTrueFalseTotal
if $in_sel_index->{$chunk} 32 0 32
1331  
1332 $extra_order_sel->{$chunk} ||= $self->_quote (
1333 0 ▶ 'ORDER__BY__' . sprintf '%03d', scalar keys %{$extra_order_sel||{}}
Condition0%0/5
$extra_order_sel->{$chunk} ||= $self->_quote('ORDER__BY__' . sprintf("%03d", scalar keys %{$extra_order_sel or {};}))
l!l&&r!l&&!r
000
$extra_order_sel || {}
l!l
00
MC/DC0%0/2
$extra_order_sel->{$chunk} ||= $self->_quote('ORDER__BY__' . sprintf("%03d", scalar keys %{$extra_order_sel or {};}))
$extra_order_sel->{$chunk} $self->_quote('ORDER__BY__' . sprintf("%03d", scalar keys %{$extra_order_sel or {};}))
MC/DC0%0/1
$extra_order_sel || {}
$extra_order_sel
Truth table0%0/3
A ||= B
A= $extra_order_sel->{$chunk}B= $self->_quote('ORDER__BY__' . sprintf("%03d", scalar keys %{$extra_order_sel or {};}))
ABresult
1X1
011
000
Truth table0%0/2
A
A= $extra_order_sel
Aresult
11
00
1334 );
1335 }
1336  
1337 return {
1338 query_leftover => $proto_sql,
1339 16 (map {( "selection_$_" => join (', ', @{$sel->{$_}} ) )} keys %$sel ),
1340 outer_renames => $renamed,
1341 order_supplement => $extra_order_sel,
1342 };
1343 }
1344  
1345  
1346 0 ▶sub _unqualify_colname {
Subroutine _unqualify_colname: not called CC=1 SCAR=6.9
1347 0 my ($self, $fqcn) = @_;
1348 0 $fqcn =~ s/ \. /__/xg;
1349 0 return $fqcn;
1350 }
1351  
1352  
1353 192 1;
1354  
1355 __END__
« blib/lib/DBIO/Test/AsyncHarness.pm ↑ Summary blib/lib/DBIO/Test/Util.pm »