lib/Mail/DMARC/Report/Store/SQL/Grammars/SQLite.pm

filter: Statementstmt 95.6% 88 / 92 Branchbran 100% 6 / 6 Conditioncond 83.3% 5 / 6 MC/DCmcdc 66.6% 2 / 3 Subroutinesub 94.4% 51 / 54 Podpod 0.0% 0 / 51 totaltotal 71.7% 152 / 212 CC 10 SCAR 4.6
CC
10
Cov
95%
limit_args
5.0
and_arg
3.0
limit
3.0
CRAP 1.6
« lib/Mail/DMARC/Report/Send.pm ↑ Summary lib/Mail/DMARC/Report/Store.pm »
1 package Mail::DMARC::Report::Store::SQL::Grammars::SQLite;
2 6 our $VERSION = '2.20261110';
3 6 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 6 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 6 ▶use feature 'signatures';
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 26 ▶sub new($class) {
Subroutine new: called CC=1 SCAR=0.0
Pod undocumented
8 26 my $self = {};
9 26 bless $self, $class;
10 26 return $self;
11 }
12  
13 99 ▶sub language {
Subroutine language: called CC=1 SCAR=0.0
Pod undocumented
14 99 return 'sqlite';
15 }
16  
17 1897 ▶sub dsn {
Subroutine dsn: called CC=1 SCAR=0.0
Pod undocumented
18 1897 return 'SQLite';
19 }
20  
21 3 ▶sub select_index_names {
Subroutine select_index_names: called CC=1 SCAR=0.0
Pod undocumented
22 3 return "SELECT name FROM sqlite_master WHERE type = 'index'";
23 }
24  
25 152 ▶sub and_arg( $self, $column, $operator = undef ) {
Subroutine and_arg: called CC=3 SCAR=11.0
Pod undocumented
26 152 ▶ $operator //= '=';
Condition100%2/2
$operator //= "="
l!l
13220
MC/DC100%1/1
$operator //= "="
$operator
Truth table100%2/2
A
A= $operator
Aresult
11
00
27 152 return " AND $column $operator ?";
28 }
29  
30 19 ▶sub report_record_id {
Subroutine report_record_id: called CC=1 SCAR=0.0
Pod undocumented
31 19 return 'SELECT id FROM report_record WHERE report_id=?';
32 }
33  
34 45 ▶sub delete_from_where_record_in( $self, $table ) {
Subroutine delete_from_where_record_in: called CC=1 SCAR=0.0
Pod undocumented
35 45 return "DELETE FROM $table WHERE report_record_id IN (??)";
36 }
37  
38 57 ▶sub delete_from_where_report( $self, $table ) {
Subroutine delete_from_where_report: called CC=1 SCAR=0.0
Pod undocumented
39 57 return "DELETE FROM $table WHERE report_id=?";
40 }
41  
42 19 ▶sub delete_report {
Subroutine delete_report: called CC=1 SCAR=0.0
Pod undocumented
43 19 return "DELETE FROM report WHERE id=?";
44 }
45  
46 190 ▶sub select_domain_id {
Subroutine select_domain_id: called CC=1 SCAR=0.0
Pod undocumented
47 190 return 'SELECT id FROM domain WHERE domain=?';
48 }
49  
50 44 ▶sub insert_domain {
Subroutine insert_domain: called CC=1 SCAR=0.0
Pod undocumented
51 44 return 'INSERT INTO domain (domain) VALUES (?)';
52 }
53  
54 23 ▶sub select_author_id {
Subroutine select_author_id: called CC=1 SCAR=0.0
Pod undocumented
55 23 return 'SELECT id FROM author WHERE org_name=?';
56 }
57  
58 20 ▶sub insert_author {
Subroutine insert_author: called CC=1 SCAR=0.0
Pod undocumented
59 20 return 'INSERT INTO author (org_name,email,extra_contact) VALUES (?,?,?)';
60 }
61  
62 0 ▶sub select_report_id {
Subroutine select_report_id: not called CC=1 SCAR=6.9
Pod undocumented
63 0 return 'SELECT id FROM report WHERE uuid=? AND author_id=?';
64 }
65  
66 20 ▶sub select_id_with_end {
Subroutine select_id_with_end: called CC=1 SCAR=0.0
Pod undocumented
67 return
68 20 'SELECT id FROM report WHERE from_domain_id=? AND end > ? AND author_id=?';
69 }
70  
71 20 ▶sub insert_report {
Subroutine insert_report: called CC=1 SCAR=0.0
Pod undocumented
72 return
73 20 'INSERT INTO report (from_domain_id, begin, end, author_id, uuid) VALUES (?,?,?,?,?)';
74 }
75  
76 13 ▶sub order_by( $self, $arg, $order ) {
Subroutine order_by: called CC=1 SCAR=0.0
Pod undocumented
77 13 return " ORDER BY $arg $order";
78 }
79  
80 14 ▶sub count_reports {
Subroutine count_reports: called CC=1 SCAR=0.0
Pod undocumented
81 14 return 'SELECT COUNT(*) FROM report';
82 }
83  
84 4 ▶sub limit( $self, $number_of_entries = undef ) {
Subroutine limit: called CC=3 SCAR=11.0
Pod undocumented
85 4 ▶ $number_of_entries //= 1;
Condition100%2/2
$number_of_entries //= 1
l!l
31
MC/DC100%1/1
$number_of_entries //= 1
$number_of_entries
Truth table100%2/2
A
A= $number_of_entries
Aresult
11
00
86 4 return " LIMIT $number_of_entries";
87 }
88  
89 1 ▶sub limit_args( $self, $number_of_entries = undef ) {
Subroutine limit_args: called CC=5 SCAR=16.1
Pod undocumented
90 1 ▶ $number_of_entries //= 1;
Condition50%1/2
$number_of_entries //= 1
l!l
10
MC/DC0%0/1
$number_of_entries //= 1
$number_of_entries
Truth table50%1/2
A
A= $number_of_entries
Aresult
11
00
91 1 my $return = ' LIMIT ';
92 1 ▶ for ( my $i = 1; $i <= $number_of_entries; $i++ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($i <= $number_of_entries) 2 1 3
93 2 $return .= '?';
94 2 ▶ $return .= ',' if $i < $number_of_entries;
Branch: 1 branch
BranchTrueFalseTotal
if $i < $number_of_entries 1 1 2
95 }
96 1 return $return;
97 }
98  
99 15 ▶sub select_report_policy_published {
Subroutine select_report_policy_published: called CC=1 SCAR=0.0
Pod undocumented
100 15 return 'SELECT * from report_policy_published WHERE report_id=?';
101 }
102  
103 1 ▶sub select_report_reason {
Subroutine select_report_reason: called CC=1 SCAR=0.0
Pod undocumented
104 1 return 'SELECT type,comment FROM report_record_reason WHERE report_record_id=?';
105 }
106  
107 0 ▶sub select_report_record {
Subroutine select_report_record: not called CC=1 SCAR=6.9
Pod undocumented
108 return
109 0 'SELECT id FROM report_record WHERE report_id=? AND source_ip=? AND count=?';
110 }
111  
112 14 ▶sub select_todo_query {
Subroutine select_todo_query: called CC=1 SCAR=0.0
Pod undocumented
113 return <<'EO_TODO_QUERY'
114 SELECT r.id AS rid,
115 r.begin AS begin,
116 r.end AS end,
117 a.org_name AS author,
118 fd.domain AS from_domain
119 FROM report r
120 LEFT JOIN report_record rr ON r.id=rr.report_id
121 LEFT JOIN author a ON r.author_id=a.id
122 LEFT JOIN domain fd ON r.from_domain_id=fd.id
123 WHERE rr.count IS NULL
124 AND rr.report_id IS NOT NULL
125 AND r.end < ?
126 GROUP BY r.id
127 ORDER BY r.id ASC
128 EO_TODO_QUERY
129 14 ;
130 }
131  
132 15 ▶sub select_row_spf {
Subroutine select_row_spf: called CC=1 SCAR=0.0
Pod undocumented
133 return <<"EO_SPF_ROW"
134 SELECT d.domain AS domain,
135 s.result AS result,
136 s.scope AS scope
137 FROM report_record_spf s
138 LEFT JOIN domain d ON s.domain_id=d.id
139 WHERE s.report_record_id=?
140 EO_SPF_ROW
141 15 ;
142 }
143  
144 15 ▶sub select_row_dkim {
Subroutine select_row_dkim: called CC=1 SCAR=0.0
Pod undocumented
145 return <<"EO_DKIM_ROW"
146 SELECT d.domain AS domain,
147 k.selector AS selector,
148 k.result AS result,
149 k.human_result AS human_result
150 FROM report_record_dkim k
151 LEFT JOIN domain d ON k.domain_id=d.id
152 WHERE report_record_id=?
153 EO_DKIM_ROW
154 15 ;
155 }
156  
157 15 ▶sub select_row_reason {
Subroutine select_row_reason: called CC=1 SCAR=0.0
Pod undocumented
158 return <<"EO_ROW_QUERY"
159 SELECT type,comment
160 FROM report_record_reason
161 WHERE report_record_id=?
162 EO_ROW_QUERY
163 15 ;
164 }
165  
166 14 ▶sub select_rr_query {
Subroutine select_rr_query: called CC=1 SCAR=0.0
Pod undocumented
167 return <<'EO_ROW_QUERY'
168 SELECT rr.*,
169 etd.domain AS envelope_to,
170 efd.domain AS envelope_from,
171 hfd.domain AS header_from
172 FROM report_record rr
173 LEFT JOIN domain etd ON etd.id=rr.envelope_to_did
174 LEFT JOIN domain efd ON efd.id=rr.envelope_from_did
175 LEFT JOIN domain hfd ON hfd.id=rr.header_from_did
176 WHERE report_id = ?
177 ORDER BY id ASC
178 EO_ROW_QUERY
179 14 ;
180 }
181  
182 27 ▶sub select_report_query {
Subroutine select_report_query: called CC=1 SCAR=0.0
Pod undocumented
183 return <<'EO_REPORTS'
184 SELECT r.id AS rid,
185 r.uuid,
186 r.begin AS begin,
187 r.end AS end,
188 a.org_name AS author,
189 fd.domain AS from_domain
190 FROM report r
191 LEFT JOIN author a ON r.author_id=a.id
192 LEFT JOIN domain fd ON r.from_domain_id=fd.id
193 WHERE 1=1
194 EO_REPORTS
195 27 ;
196 }
197  
198 7 ▶sub count_filtered_report_query {
Subroutine count_filtered_report_query: called CC=1 SCAR=0.0
Pod undocumented
199 return <<'EO_SQL'
200 SELECT COUNT(*)
201 FROM report r
202 LEFT JOIN author a ON r.author_id=a.id
203 LEFT JOIN domain fd ON r.from_domain_id=fd.id
204 WHERE 1=1
205 EO_SQL
206 7 ;
207 }
208  
209 8 ▶sub insert_error( $self, $which ) {
Subroutine insert_error: called CC=2 SCAR=6.9
Pod undocumented
210 8 ▶ if ( $which == 0 ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($which == 0) { } 4 4 8
211 4 return 'UPDATE report SET end=? WHERE id=?';
212 }
213 else {
214 4 return 'INSERT INTO report_error (report_id, error) VALUES (?,?)';
215 }
216 }
217  
218 19 ▶sub insert_rr_reason {
Subroutine insert_rr_reason: called CC=1 SCAR=0.0
Pod undocumented
219 return
220 19 'INSERT INTO report_record_reason (report_record_id, type, comment) VALUES (?,?,?)';
221 }
222  
223 28 ▶sub insert_rr_dkim( $self, $fields ) {
Subroutine insert_rr_dkim: called CC=1 SCAR=0.0
Pod undocumented
224 28 my $fields_str = join ', ', @$fields;
225 return <<"EO_DKIM"
226 INSERT INTO report_record_dkim
227 (report_record_id, $fields_str)
228 VALUES (??)
229 EO_DKIM
230 28 ;
231 }
232  
233 34 ▶sub insert_rr_spf( $self, $fields ) {
Subroutine insert_rr_spf: called CC=1 SCAR=0.0
Pod undocumented
234 34 my $fields_str = join ', ', @$fields;
235 return
236 34 "INSERT INTO report_record_spf (report_record_id, $fields_str) VALUES(??)";
237 }
238  
239 36 ▶sub insert_rr {
Subroutine insert_rr: called CC=1 SCAR=0.0
Pod undocumented
240 return <<'EO_ROW_INSERT'
241 INSERT INTO report_record
242 (report_id, source_ip, count, header_from_did, envelope_to_did, envelope_from_did,
243 disposition, dkim, spf)
244 VALUES (??)
245 EO_ROW_INSERT
246 36 ;
247 }
248  
249 9 ▶sub select_from( $self, $columns, $table ) {
Subroutine select_from: called CC=1 SCAR=0.0
Pod undocumented
250 9 my $colStr = join( ', ', @$columns );
251 9 return "SELECT $colStr FROM $table WHERE 1=1";
252 }
253  
254 31 ▶sub insert_into( $self, $table, $cols ) {
Subroutine insert_into: called CC=1 SCAR=0.0
Pod undocumented
255 31 my $columns = join ', ', @$cols;
256 31 return "INSERT INTO $table ($columns) VALUES (??)";
257 }
258  
259 0 ▶sub update( $self, $table, $cols ) {
Subroutine update: not called CC=1 SCAR=6.9
Pod undocumented
260 0 my $columns = join( ' = ?, ', @$cols ) . ' = ?';
261 0 return "UPDATE $table SET $columns WHERE 1=1";
262 }
263  
264 1 ▶sub replace_into( $self, $table, $cols ) {
Subroutine replace_into: called CC=1 SCAR=0.0
Pod undocumented
265 1 my $columns = join ', ', @$cols;
266 1 return "REPLACE INTO $table ($columns) VALUES (??)";
267 }
268  
269 5 ▶sub delete_from( $self, $table ) {
Subroutine delete_from: called CC=1 SCAR=0.0
Pod undocumented
270 5 return "DELETE FROM $table WHERE 1=1";
271 }
272  
273 # Aggregate queries for the report views. Every one of these is scoped by a
274 # WHERE fragment the store builds from placeholders, so $where never carries
275 # caller-supplied text.
276  
277 # The DMARC-evaluated (aligned) results live in report_record.dkim/spf, which
278 # are nullable; COALESCE keeps a NULL out of the comparison so that no message
279 # escapes all four alignment buckets.
280 18 ▶sub outcome_buckets {
Subroutine outcome_buckets: called CC=1 SCAR=0.0
Pod undocumented
281 return <<'EO_BUCKETS'
282 SUM(CASE WHEN COALESCE(rr.dkim,'')='pass' AND COALESCE(rr.spf,'')='pass'
283 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_both,
284 SUM(CASE WHEN COALESCE(rr.dkim,'')='pass' AND COALESCE(rr.spf,'')<>'pass'
285 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_dkim,
286 SUM(CASE WHEN COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')='pass'
287 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_spf,
288 SUM(CASE WHEN COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')<>'pass'
289 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_none,
290 SUM(CASE WHEN rr.disposition='none' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_none,
291 SUM(CASE WHEN rr.disposition='quarantine' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_quarantine,
292 SUM(CASE WHEN rr.disposition='reject' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_reject
293 EO_BUCKETS
294 18 ;
295 }
296  
297 43 ▶sub agg_from_clause {
Subroutine agg_from_clause: called CC=1 SCAR=0.0
Pod undocumented
298 return <<'EO_FROM'
299 FROM report r
300 JOIN report_record rr ON r.id=rr.report_id
301 LEFT JOIN author a ON r.author_id=a.id
302 LEFT JOIN domain fd ON r.from_domain_id=fd.id
303 EO_FROM
304 43 ;
305 }
306  
307 # Reporting windows are epoch seconds, so a UTC day needs no dialect date
308 # functions. A window spanning a day boundary is attributed to its begin day;
309 # reporters almost always send daily windows, so the skew is negligible.
310 10 ▶sub select_timeseries_query( $self, $where ) {
Subroutine select_timeseries_query: called CC=1 SCAR=0.0
Pod undocumented
311 10 my $buckets = $self->outcome_buckets;
312 10 my $from = $self->agg_from_clause;
313 return <<"EO_TIMESERIES"
314 SELECT (r.begin - (r.begin % 86400)) AS day,
315 SUM(COALESCE(rr.count,1)) AS messages,
316 $buckets
317 $from WHERE 1=1$where
318 GROUP BY day
319 ORDER BY day ASC
320 EO_TIMESERIES
321 10 ;
322 }
323  
324 7 ▶sub select_sources_query( $self, $where ) {
Subroutine select_sources_query: called CC=1 SCAR=0.0
Pod undocumented
325 7 my $buckets = $self->outcome_buckets;
326 7 my $from = $self->agg_from_clause;
327 return <<"EO_SOURCES"
328 SELECT rr.source_ip AS source_ip,
329 SUM(COALESCE(rr.count,1)) AS messages,
330 $buckets,
331 COUNT(DISTINCT r.author_id) AS reporters,
332 MIN(r.begin) AS first_seen,
333 MAX(r.end) AS last_seen
334 $from WHERE 1=1$where
335 GROUP BY rr.source_ip
336 ORDER BY messages DESC
337 EO_SOURCES
338 7 ;
339 }
340  
341 # Kept out of the sources query on purpose: a record with two reasons would
342 # join twice and double every SUM in that row.
343 7 ▶sub select_source_reasons_query( $self, $where ) {
Subroutine select_source_reasons_query: called CC=1 SCAR=0.0
Pod undocumented
344 7 my $from = $self->agg_from_clause;
345 return <<"EO_REASONS"
346 SELECT rr.source_ip AS source_ip,
347 rrr.type AS type,
348 rrr.comment AS comment,
349 SUM(COALESCE(rr.count,1)) AS messages
350 $from JOIN report_record_reason rrr ON rrr.report_record_id=rr.id
351 WHERE 1=1$where
352 GROUP BY rr.source_ip, rrr.type, rrr.comment
353 EO_REASONS
354 7 ;
355 }
356  
357 4 ▶sub select_source_detail_query( $self, $where ) {
Subroutine select_source_detail_query: called CC=1 SCAR=0.0
Pod undocumented
358 4 my $from = $self->agg_from_clause;
359 return <<"EO_DETAIL"
360 SELECT hfd.domain AS header_from,
361 rr.disposition AS disposition,
362 rr.dkim AS dkim,
363 rr.spf AS spf,
364 SUM(COALESCE(rr.count,1)) AS messages
365 $from LEFT JOIN domain hfd ON hfd.id=rr.header_from_did
366 WHERE 1=1$where
367 GROUP BY hfd.domain, rr.disposition, rr.dkim, rr.spf
368 ORDER BY messages DESC
369 EO_DETAIL
370 4 ;
371 }
372  
373 # The d= domain and selector a source presented are what distinguish a
374 # misconfigured ESP from an unauthenticated spoof.
375 8 ▶sub select_source_dkim_query( $self, $where ) {
Subroutine select_source_dkim_query: called CC=1 SCAR=0.0
Pod undocumented
376 8 my $from = $self->agg_from_clause;
377 return <<"EO_SRC_DKIM"
378 SELECT d.domain AS domain,
379 k.selector AS selector,
380 k.result AS result,
381 SUM(COALESCE(rr.count,1)) AS messages
382 $from JOIN report_record_dkim k ON k.report_record_id=rr.id
383 LEFT JOIN domain d ON d.id=k.domain_id
384 WHERE 1=1$where
385 GROUP BY d.domain, k.selector, k.result
386 ORDER BY messages DESC
387 EO_SRC_DKIM
388 8 ;
389 }
390  
391 7 ▶sub select_source_spf_query( $self, $where ) {
Subroutine select_source_spf_query: called CC=1 SCAR=0.0
Pod undocumented
392 7 my $from = $self->agg_from_clause;
393 return <<"EO_SRC_SPF"
394 SELECT d.domain AS domain,
395 s.scope AS scope,
396 s.result AS result,
397 SUM(COALESCE(rr.count,1)) AS messages
398 $from JOIN report_record_spf s ON s.report_record_id=rr.id
399 LEFT JOIN domain d ON d.id=s.domain_id
400 WHERE 1=1$where
401 GROUP BY d.domain, s.scope, s.result
402 ORDER BY messages DESC
403 EO_SRC_SPF
404 7 ;
405 }
406  
407 # Per report totals for the report list, so a row can show what it contains
408 # without being expanded. Returned one row per (report, From, To) rather than
409 # aggregated into a string, because GROUP_CONCAT, group_concat and string_agg
410 # are spelled differently in all three engines; the caller assembles the sets.
411 11 ▶sub and_failing {
Subroutine and_failing: called CC=1 SCAR=0.0
Pod undocumented
412 11 return " AND COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')<>'pass'";
413 }
414  
415 1 ▶sub select_report_summary_query {
Subroutine select_report_summary_query: called CC=1 SCAR=0.0
Pod undocumented
416 1 my ($self) = @_;
417 1 my $buckets = $self->outcome_buckets;
418 return <<"EO_SUMMARY"
419 SELECT rr.report_id AS rid,
420 hfd.domain AS header_from,
421 etd.domain AS envelope_to,
422 SUM(COALESCE(rr.count,1)) AS messages,
423 $buckets
424 FROM report_record rr
425 LEFT JOIN domain hfd ON hfd.id=rr.header_from_did
426 LEFT JOIN domain etd ON etd.id=rr.envelope_to_did
427 WHERE rr.report_id IN (??)
428 GROUP BY rr.report_id, hfd.domain, etd.domain
429 EO_SUMMARY
430 1 ;
431 }
432  
433 3 ▶sub select_report_domains_query( $self, $where ) {
Subroutine select_report_domains_query: called CC=1 SCAR=0.0
Pod undocumented
434 return <<"EO_DOMAINS"
435 SELECT fd.domain AS domain,
436 COUNT(*) AS reports,
437 MIN(r.begin) AS first_seen,
438 MAX(r.end) AS last_seen
439 FROM report r
440 JOIN domain fd ON fd.id=r.from_domain_id
441 LEFT JOIN author a ON a.id=r.author_id
442 WHERE 1=1$where
443 GROUP BY fd.domain
444 ORDER BY fd.domain ASC
445 EO_DOMAINS
446 3 ;
447 }
448  
449 6 1;
450  
451 __END__
« lib/Mail/DMARC/Report/Send.pm ↑ Summary lib/Mail/DMARC/Report/Store.pm »