blib/lib/DBIO/SQLite/SQLMaker.pm

filter: Statementstmt 94.1% 81 / 86 Branchbran 83.9% 47 / 56 Conditioncond 55.5% 15 / 27 MC/DCmcdc 37.5% 6 / 16 Subroutinesub 91.6% 11 / 12 Podpod 100% 1 / 1 totaltotal 81.3% 161 / 198 CC 39 SCAR 28.5
CC
39
Cov
85%
_insert_on_conflict
30.6
insert
8.0
__ANON__
7.6
CRAP 17.4
« blib/lib/DBIO/SQLite.pm ↑ Summary blib/lib/DBIO/SQLite/Diff.pm »
1 package DBIO::SQLite::SQLMaker;
2 # ABSTRACT: SQLite-specific SQL generation for DBIO
3  
4 169 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 169 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 169 our $VERSION = '0.900007';
8  
9 169 ▶use base qw( DBIO::SQLMaker );
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11  
12 #
13 # SQLite does not understand SELECT ... FOR UPDATE
14 # Disable it here
15 sub _lock_select () { '' };
16  
17 # ADR 0036 -- atomic upsert. The conflict clause sits between the VALUES list
18 # and RETURNING, so it cannot simply be appended to whatever the generic
19 # insert() produced; the statement is assembled here in grammar order instead.
20 #
21 # $options->{on_conflict} MUST be consumed (deleted) before delegating: the
22 # generic DBIO::SQLMaker::insert throws when the structure reaches it, which is
23 # what keeps a driver that declares supports_upsert without implementing the
24 # clause loud instead of silently dropping it.
25 9297 ▶sub insert {
Subroutine insert: called CC=8 SCAR=20.8
Pod documented
26 9297 my ($self, $table, $data, $options) = @_;
27  
28 # SQLite's OR-conflict clause -- INSERT OR IGNORE / INSERT OR REPLACE (karr
29 # k38). It sits between the INSERT keyword and INTO, a DIFFERENT mechanism
30 # from the statement-tail ON CONFLICT clause below. It rides the same
31 # $options transport: consumed (deleted) here before delegating, then spliced
32 # into the finished statement, so a plain insert without it is byte-for-byte
33 # unchanged.
34 my $insert_or = ( ref $options eq 'HASH' )
35 ? delete $options->{insert_or}
36 : undef
37 9297 ▶ ;
Branch: 1 branch
BranchTrueFalseTotal
ref $options eq 'HASH' ? : 1442 7855 9297
38  
39 my $on_conflict = ( ref $options eq 'HASH' )
40 ? delete $options->{on_conflict}
41 : undef
42 9297 ▶ ;
Branch: 1 branch
BranchTrueFalseTotal
ref $options eq 'HASH' ? : 1442 7855 9297
43  
44 9297 my ($sql, @bind);
45  
46 9297 ▶ if (! $on_conflict) {
Branch: 1 branch
BranchTrueFalseTotal
if (not $on_conflict) { } 9250 47 9297
47 9250 ($sql, @bind) = $self->next::method($table, $data, $options);
48 }
49 else {
50 # Build the statement without its RETURNING tail, then append both tails:
51 # INSERT INTO ... VALUES ( ... ) ON CONFLICT ( ... ) DO ... RETURNING ...
52 47 my $returning = delete $options->{returning};
53  
54 47 ($sql, @bind) = $self->next::method($table, $data, $options);
55  
56 # Put it back -- DBIO::Storage::DBI::insert reads $sqla_opts->{returning}
57 # after the statement ran, to know which columns the handle hands back.
58 47 ▶ $options->{returning} = $returning if defined $returning;
Branch: 1 branch
BranchTrueFalseTotal
if defined $returning 13 34 47
59  
60 47 my ($conflict_sql, @conflict_bind) = $self->_insert_on_conflict($on_conflict, $table);
61 38 $sql .= $conflict_sql;
62 38 push @bind, @conflict_bind;
63  
64 38 ▶ if (defined $returning) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $returning) 13 25 38
65 13 my ($returning_sql, @returning_bind) = $self->_insert_returning($options);
66 13 $sql .= $returning_sql;
67 13 push @bind, @returning_bind;
68 }
69 }
70  
71 9288 ▶ $sql = $self->_insert_or_clause($sql, $insert_or) if defined $insert_or;
Branch: 1 branch
BranchTrueFalseTotal
if defined $insert_or 7 9281 9288
72  
73 9287 ▶ return wantarray ? ($sql, @bind) : $sql;
Branch: 1 branch
BranchTrueFalseTotal
wantarray ? : 9285 2 9287
74 }
75  
76  
77 # Splices SQLite's OR-conflict clause (INSERT OR IGNORE / INSERT OR REPLACE)
78 # into a finished statement, between the INSERT keyword and INTO. Only 'ignore'
79 # and 'replace' are exposed (karr k38); an unknown mode throws rather than
80 # emitting something the engine would reject or, worse, silently accept.
81 7 ▶sub _insert_or_clause {
Subroutine _insert_or_clause: called CC=4 SCAR=13.9
82 7 my ($self, $sql, $insert_or) = @_;
83  
84 7 my $mode = lc $insert_or;
85  
86 7 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless $mode eq "ignore" or $mode eq "replace" 1 6 7
Condition100%3/3
$mode eq "ignore" or $mode eq "replace"
l!l&&r!l&&!r
421
MC/DC100%2/2
$mode eq "ignore" or $mode eq "replace"
$mode eq "ignore" $mode eq "replace"
Truth table100%3/3
A or B
A= $mode eq "ignore"B= $mode eq "replace"
ABresult
1X1
011
000
87 "insert_or must be 'ignore' or 'replace', not '$insert_or'"
88 ) unless $mode eq 'ignore' or $mode eq 'replace';
89  
90 6 my $clause = $self->_sqlcase("or $mode"); # OR IGNORE / OR REPLACE
91  
92 # The generic insert always emits the INSERT keyword first; splice the clause
93 # in right after it, without assuming its letter case. If the statement does
94 # not start with INSERT, something upstream changed -- fail loud rather than
95 # mangle it.
96 6 ▶ $sql =~ s/\A(\s*insert)\b/$1 $clause/i
Branch: 1 branch
BranchTrueFalseTotal
$sql =~ s/\A(\s*insert)\b/$1 $clause/i or $self->throw_exception("expected the generated statement to start with INSERT, got '${sql}'") 6 0 6
97 or $self->throw_exception(
98 "expected the generated statement to start with INSERT, got '$sql'"
99 );
100  
101 6 return $sql;
102 }
103  
104  
105 # Renders the ON CONFLICT tail from core's engine-neutral structure:
106 #
107 # { target => \@cols | { constraint => $name, columns => \@cols } | 'any',
108 # action => 'update' | 'nothing',
109 # set => { $col => { -attempted => $col } | \'literal' | $scalar },
110 # where => $cond } # only when the caller passed update_where
111 #
112 # Returns ($sql_tail, @bind), mirroring _insert_returning.
113 47 ▶sub _insert_on_conflict {
Subroutine _insert_on_conflict: called CC=24 SCAR=34.2
114 47 my ($self, $on_conflict, $table) = @_;
115  
116 47 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless ref $on_conflict eq "HASH" 0 47 47
117 'on_conflict must be a hashref of { target => ..., action => ..., set => ... }'
118 ) unless ref $on_conflict eq 'HASH';
119  
120 47 ▶ my $action = defined $on_conflict->{action} ? $on_conflict->{action} : 'update';
Branch: 1 branch
BranchTrueFalseTotal
defined $on_conflict->{'action'} ? : 47 0 47
121 47 my $target = $on_conflict->{target};
122  
123 47 my @target_cols;
124  
125 47 ▶ if (ref $target eq 'ARRAY') {
Branch: 3 branches
BranchTrueFalseTotal
if (ref $target eq 'ARRAY') { } 39 8 47
elsif (ref $target eq 'HASH') { } 5 3 8
elsif (defined $target and not length ref $target and $target eq 'any') { } 3 0 3
126 39 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless @$target 0 39 39
127 'ON CONFLICT needs at least one conflict target column'
128 ) unless @$target;
129  
130 39 @target_cols = @$target;
131 }
132 elsif (ref $target eq 'HASH') {
133 # core's upsert(..., { key => $constraint }), resolved: the hashref carries
134 # the constraint *name* and the columns core resolved it to (ADR 0036 s2).
135 # SQLite takes the columns -- its grammar has no ON CONSTRAINT form, and a
136 # DBIO constraint name is client-side add_unique_constraint metadata that
137 # need not name anything the server knows anyway.
138 5 my $cols = $target->{columns};
139  
140 # A SQLMaker has no ResultSource, so it cannot resolve the name itself: if
141 # the resolved half is missing there is nothing to render, and emitting
142 # "ON CONFLICT ()" would be a syntax error at best and a wrongly targeted
143 # write at worst. Fail here so a core that stops sending it is loud.
144 $self->throw_exception(
145 'ON CONFLICT target hashref carries no resolved columns -- core resolves '
146 . 'key => $constraint once and passes '
147 . '{ constraint => $name, columns => \@columns } (ADR 0036 section 2), and '
148 . "SQLite's conflict target is that column list"
149 5 ▶ . ( defined $target->{constraint} ? " (constraint '$target->{constraint}')" : '' )
Branch: 2 branches
BranchTrueFalseTotal
unless ref $cols eq "ARRAY" and @$cols 2 3 5
defined $target->{'constraint'} ? : 2 0 2
Condition100%3/3
ref $cols eq "ARRAY" and @$cols
!ll&&!rl&&r
113
MC/DC100%2/2
ref $cols eq "ARRAY" and @$cols
ref $cols eq "ARRAY" @$cols
Truth table100%3/3
A and B
A= ref $cols eq "ARRAY"B= @$cols
ABresult
0X0
100
111
150 ) unless ref $cols eq 'ARRAY' and @$cols;
151  
152 3 @target_cols = @$cols;
153 }
154 ▶ elsif (defined $target and ! length ref $target and $target eq 'any') {
Condition33%2/6
defined $target and not length ref $target and $target eq "any"
!ll&&!rl&&r
003
defined $target and not length ref $target
!ll&&!rl&&r
003
MC/DC0%0/3
defined $target and not length ref $target and $target eq "any"
defined $target not length ref $target $target eq "any"
Truth table25%1/4
A and not(B) and C
A= defined $targetB= not length ref $targetC= $target eq "any"
ABCresult
0XX0
10X0
1100
1111
155 # "whichever unique constraint collides" is MySQL's native shape. SQLite
156 # can express it only as a targetless DO NOTHING -- DO UPDATE requires a
157 # target, so the clause is left empty here and rejected below.
158 3 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
if $action eq "update" 1 2 3
159 "conflict_target => 'any' cannot be combined with on_conflict => 'update' "
160 . "on SQLite: the ON CONFLICT grammar requires a conflict target for DO "
161 . "UPDATE. Name the columns with conflict_target => \\\@columns, or use "
162 . "on_conflict => 'nothing', which needs no target"
163 ) if $action eq 'update';
164 }
165 else {
166 0 $self->throw_exception(
167 'ON CONFLICT target must be an arrayref of columns, '
168 . "{ constraint => \$name, columns => \\\@columns } or the string 'any'"
169 );
170 }
171  
172 my $target_sql = @target_cols
173 44 ▶ ? ' (' . join(', ', map { $self->_quote($_) } @target_cols) . ')'
Branch: 1 branch
BranchTrueFalseTotal
@target_cols ? : 42 2 44
174 : ''
175 ;
176  
177 44 ▶ if ($action eq 'nothing') {
Branch: 1 branch
BranchTrueFalseTotal
if ($action eq "nothing") 7 37 44
178 # Core refuses update_where together with on_conflict => 'nothing' before
179 # the structure ever gets here, and sets the key only when it has a
180 # condition. If one arrives anyway -- in any state, hence exists and not
181 # defined -- dropping it is the one mistake this whole feature is guarded
182 # against, so say so.
183 $self->throw_exception(
184 'ON CONFLICT DO NOTHING takes no condition -- there is no update branch '
185 . 'for a WHERE to gate'
186 7 ▶ ) if exists $on_conflict->{where};
Branch: 1 branch
BranchTrueFalseTotal
if exists $on_conflict->{"where"} 2 5 7
187  
188 5 return ' ' . $self->_sqlcase('on conflict') . $target_sql
189 . ' ' . $self->_sqlcase('do nothing');
190 }
191  
192 $self->throw_exception(
193 37 ▶ "unknown ON CONFLICT action '$action' -- SQLite has DO UPDATE and DO NOTHING"
Branch: 1 branch
BranchTrueFalseTotal
unless $action eq "update" 0 37 37
194 ) unless $action eq 'update';
195  
196 37 my $set = $on_conflict->{set};
197 37 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless ref $set eq "HASH" and keys %$set 0 37 37
Condition33%1/3
ref $set eq "HASH" and keys %$set
!ll&&!rl&&r
0037
MC/DC0%0/2
ref $set eq "HASH" and keys %$set
ref $set eq "HASH" keys %$set
Truth table33%1/3
A and B
A= ref $set eq "HASH"B= keys %$set
ABresult
0X0
100
111
198 'ON CONFLICT DO UPDATE needs a non-empty set of column => value pairs'
199 ) unless ref $set eq 'HASH' and keys %$set;
200  
201 37 my (@set_sql, @bind);
202  
203 37 for my $col (sort keys %$set) {
204 39 my $label = $self->_quote($col);
205 39 my $value = $set->{$col};
206  
207 $self->_SWITCH_refkind($value, {
208  
209 # { -attempted => $col } is core's engine-neutral "the value that was
210 # attempted to be inserted". _upsert_attempted_sql spells it for this
211 # engine, and both halves of the statement go through that one method so
212 # they can never diverge.
213 34 ▶ HASHREF => sub {
Subroutine __ANON__: called CC=6 SCAR=20.3
214 34 my ($op, $arg, @rest) = %$value;
215  
216 34 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
if @rest or not defined $op or $op ne "-attempted" 1 33 34
Condition50%3/6
@rest or not defined $op or $op ne "-attempted"
l!l&&r!l&&!r
0133
@rest or not defined $op
l!l&&r!l&&!r
0034
MC/DC33%1/3
@rest or not defined $op or $op ne "-attempted"
@rest not defined $op $op ne "-attempted"
Truth table50%2/4
A or not(B) or C
A= @restB= not defined $opC= $op ne "-attempted"
ABCresult
1XX1
01X1
0011
0000
217 "the only operator an ON CONFLICT update set understands is "
218 . "-attempted (the value that was attempted to be inserted), not '$op'"
219 ) if @rest or ! defined $op or $op ne '-attempted';
220  
221 33 ▶ $self->throw_exception('-attempted takes a plain column name')
Branch: 1 branch
BranchTrueFalseTotal
if not defined $arg or length ref $arg 0 33 33
Condition33%1/3
not defined $arg or length ref $arg
l!l&&r!l&&!r
0033
MC/DC0%0/2
not defined $arg or length ref $arg
not defined $arg length ref $arg
Truth table33%1/3
not(A) or B
A= not defined $argB= length ref $arg
ABresult
1X1
011
000
222 if ! defined $arg or length ref $arg;
223  
224 33 push @set_sql, "$label = " . $self->_upsert_attempted_sql($arg);
225 },
226  
227 2 ▶ ARRAYREFREF => sub { # \[ 'literal SQL', @bind ]
Subroutine __ANON__: called CC=1 SCAR=0.0
228 2 my ($literal, @literal_bind) = @$$value;
229 2 $self->_assert_bindval_matches_bindtype(@literal_bind);
230 2 push @set_sql, "$label = $literal";
231 2 push @bind, @literal_bind;
232 },
233  
234 0 ▶ ARRAYREF => sub { # [ 'literal SQL', @bind ], as update() takes it
Subroutine __ANON__: not called CC=1 SCAR=6.9
235 0 my ($literal, @literal_bind) = @$value;
236 0 $self->_assert_bindval_matches_bindtype(@literal_bind);
237 0 push @set_sql, "$label = $literal";
238 0 push @bind, @literal_bind;
239 },
240  
241 1 ▶ SCALARREF => sub { # \'literal SQL'
Subroutine __ANON__: called CC=1 SCAR=0.0
242 1 push @set_sql, "$label = $$value";
243 },
244  
245 2 ▶ SCALAR_or_UNDEF => sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
246 2 push @set_sql, "$label = ?";
247 2 push @bind, $self->_bindtype($col, $value);
248 }
249  
250 39 });
251 }
252  
253 36 my $sql = ' ' . $self->_sqlcase('on conflict') . $target_sql
254 . ' ' . $self->_sqlcase('do update set') . ' ' . join(', ', @set_sql);
255  
256 # The conditional conflict branch (ADR 0036, core k105). The key is present
257 # only when the caller passed update_where, so an unconditional upsert still
258 # renders byte for byte what it rendered before the option existed.
259 #
260 # Placement: after the SET list, before RETURNING -- which follows from where
261 # this tail is appended, since insert() holds RETURNING back over the call to
262 # the generic insert and appends it after this.
263 36 ▶ if (exists $on_conflict->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $on_conflict->{"where"}) 22 14 36
264 22 my $where = $on_conflict->{where};
265  
266 22 ▶ my ($where_sql, @where_bind) = defined $where
Branch: 1 branch
BranchTrueFalseTotal
defined $where ? : 21 1 22
267 ? $self->_upsert_condition_sql($where, $table)
268 : ()
269 ;
270  
271 # Every route that would leave the clause without a condition ends here,
272 # because emitting it anyway is an UNCONDITIONAL DO UPDATE -- the
273 # last-write-wins overwrite update_where exists to prevent, and the one
274 # failure mode with no symptom of its own. Core rejects the empty forms it
275 # can see ({} / []) and sets the key only when it has a condition, but it
276 # deliberately does not walk the condition, so { -and => [] } and friends
277 # arrive here.
278 21 ▶ $self->throw_exception(
Branch: 1 branch
BranchTrueFalseTotal
unless defined $where_sql and length $where_sql 2 19 21
Condition66%2/3
defined $where_sql and length $where_sql
!ll&&!rl&&r
2019
MC/DC50%1/2
defined $where_sql and length $where_sql
defined $where_sql length $where_sql
Truth table66%2/3
A and B
A= defined $where_sqlB= length $where_sql
ABresult
0X0
100
111
279 'the ON CONFLICT DO UPDATE condition rendered to nothing -- emitting the '
280 . 'clause without it would be an unconditional overwrite, which is exactly '
281 . 'what update_where is there to prevent'
282 ) unless defined $where_sql and length $where_sql;
283  
284 19 $sql .= ' ' . $self->_sqlcase('where') . ' ' . $where_sql;
285 19 push @bind, @where_bind;
286 }
287  
288 33 return ($sql, @bind);
289 }
290  
291  
292 # The one engine-specific piece of an atomic upsert (ADR 0036): the token for
293 # the pseudo-row that holds the value the statement tried to insert. Core's
294 # engine-neutral { -attempted => $col } marker becomes excluded.<col> here. The
295 # update set and the conditional branch both render through this single method,
296 # so the two halves of one statement can never write the pseudo-row
297 # differently. Core's _upsert_condition_sql (DBIO::SQLMaker::ClassicExtensions)
298 # does the condition-half substitution and is parameterized over this method.
299 49 ▶sub _upsert_attempted_sql {
Subroutine _upsert_attempted_sql: called CC=1 SCAR=0.0
300 49 my ($self, $col) = @_;
301 49 return 'excluded.' . $self->_quote($col);
302 }
303  
304  
305 169 1;
306  
307 __END__
« blib/lib/DBIO/SQLite.pm ↑ Summary blib/lib/DBIO/SQLite/Diff.pm »