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

filter: Statementstmt 0.0% 0 / 93 Branchbran 0.0% 0 / 4 Conditioncond 0.0% 0 / 6 MC/DCmcdc 0.0% 0 / 3 Subroutinesub 0.0% 0 / 55 Podpod 0.0% 0 / 52 totaltotal 0.0% 0 / 213 CC 3 SCAR 8.2
CC
3
Cov
0%
limit_args
6.0
insert_error
6.0
new
2.0
CRAP 2.3
1 package Mail::DMARC::Report::Store::SQL::Grammars::SQLite;
2 our $VERSION = '2.20260827';
3 use strict;
4 use warnings;
5 use feature 'signatures';
6  
7 sub new($class) {
8 my $self = {};
9 bless $self, $class;
10 return $self;
11 }
12  
13 sub language {
14 return 'sqlite';
15 }
16  
17 sub dsn {
18 return 'SQLite';
19 }
20  
21 sub and_arg( $self, $column, $operator = undef ) {
22 $operator //= '=';
23 return " AND $column $operator ?";
24 }
25  
26 sub report_record_id {
27 return 'SELECT id FROM report_record WHERE report_id=?';
28 }
29  
30 sub delete_from_where_record_in( $self, $table ) {
31 return "DELETE FROM $table WHERE report_record_id IN (??)";
32 }
33  
34 sub delete_from_where_report( $self, $table ) {
35 return "DELETE FROM $table WHERE report_id=?";
36 }
37  
38 sub delete_report {
39 return "DELETE FROM report WHERE id=?";
40 }
41  
42 sub select_domain_id {
43 return 'SELECT id FROM domain WHERE domain=?';
44 }
45  
46 sub insert_domain {
47 return 'INSERT INTO domain (domain) VALUES (?)';
48 }
49  
50 sub select_author_id {
51 return 'SELECT id FROM author WHERE org_name=?';
52 }
53  
54 sub insert_author {
55 return 'INSERT INTO author (org_name,email,extra_contact) VALUES (?,?,?)';
56 }
57  
58 sub select_report_id {
59 return 'SELECT id FROM report WHERE uuid=? AND author_id=?';
60 }
61  
62 sub select_id_with_end {
63 return
64 'SELECT id FROM report WHERE from_domain_id=? AND end > ? AND author_id=?';
65 }
66  
67 sub insert_report {
68 return
69 'INSERT INTO report (from_domain_id, begin, end, author_id, uuid) VALUES (?,?,?,?,?)';
70 }
71  
72 sub order_by( $self, $arg, $order ) {
73 return " ORDER BY $arg $order";
74 }
75  
76 sub count_reports {
77 return 'SELECT COUNT(*) FROM report';
78 }
79  
80 sub limit( $self, $number_of_entries = undef ) {
81 $number_of_entries //= 1;
82 return " LIMIT $number_of_entries";
83 }
84  
85 sub limit_args( $self, $number_of_entries = undef ) {
86 $number_of_entries //= 1;
87 my $return = ' LIMIT ';
88 for ( my $i = 1; $i <= $number_of_entries; $i++ ) {
89 $return .= '?';
90 $return .= ',' if $i < $number_of_entries;
91 }
92 return $return;
93 }
94  
95 sub select_report_policy_published {
96 return 'SELECT * from report_policy_published WHERE report_id=?';
97 }
98  
99 sub select_report_reason {
100 return 'SELECT type,comment FROM report_record_reason WHERE report_record_id=?';
101 }
102  
103 sub select_report_error {
104 return 'SELECT error FROM report_error WHERE report_id=?';
105 }
106  
107 sub select_report_record {
108 return
109 'SELECT id FROM report_record WHERE report_id=? AND source_ip=? AND count=?';
110 }
111  
112 sub select_todo_query {
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 ;
130 }
131  
132 sub select_row_spf {
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 ;
142 }
143  
144 sub select_row_dkim {
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 ;
155 }
156  
157 sub select_row_reason {
158 return <<"EO_ROW_QUERY"
159 SELECT type,comment
160 FROM report_record_reason
161 WHERE report_record_id=?
162 EO_ROW_QUERY
163 ;
164 }
165  
166 sub select_rr_query {
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 ;
180 }
181  
182 sub select_report_query {
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 ;
196 }
197  
198 sub count_filtered_report_query {
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 ;
207 }
208  
209 sub insert_error( $self, $which ) {
210 if ( $which == 0 ) {
211 return 'UPDATE report SET end=? WHERE id=?';
212 }
213 else {
214 return 'INSERT INTO report_error (report_id, error) VALUES (?,?)';
215 }
216 }
217  
218 sub insert_rr_reason {
219 return
220 'INSERT INTO report_record_reason (report_record_id, type, comment) VALUES (?,?,?)';
221 }
222  
223 sub insert_rr_dkim( $self, $fields ) {
224 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 ;
231 }
232  
233 sub insert_rr_spf( $self, $fields ) {
234 my $fields_str = join ', ', @$fields;
235 return
236 "INSERT INTO report_record_spf (report_record_id, $fields_str) VALUES(??)";
237 }
238  
239 sub insert_rr {
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 ;
247 }
248  
249 sub insert_policy_published {
250 return <<"EO_RPP"
251 INSERT INTO report_policy_published
252 (report_id, adkim, aspf, p, sp, pct, rua)
253 VALUES (??)
254 EO_RPP
255 ;
256 }
257  
258 sub select_from( $self, $columns, $table ) {
259 my $colStr = join( ', ', @$columns );
260 return "SELECT $colStr FROM $table WHERE 1=1";
261 }
262  
263 sub insert_into( $self, $table, $cols ) {
264 my $columns = join ', ', @$cols;
265 return "INSERT INTO $table ($columns) VALUES (??)";
266 }
267  
268 sub update( $self, $table, $cols ) {
269 my $columns = join( ' = ?, ', @$cols ) . ' = ?';
270 return "UPDATE $table SET $columns WHERE 1=1";
271 }
272  
273 sub replace_into( $self, $table, $cols ) {
274 my $columns = join ', ', @$cols;
275 return "REPLACE INTO $table ($columns) VALUES (??)";
276 }
277  
278 sub delete_from( $self, $table ) {
279 return "DELETE FROM $table WHERE 1=1";
280 }
281  
282 # Aggregate queries for the report views. Every one of these is scoped by a
283 # WHERE fragment the store builds from placeholders, so $where never carries
284 # caller-supplied text.
285  
286 # The DMARC-evaluated (aligned) results live in report_record.dkim/spf, which
287 # are nullable; COALESCE keeps a NULL out of the comparison so that no message
288 # escapes all four alignment buckets.
289 sub outcome_buckets {
290 return <<'EO_BUCKETS'
291 SUM(CASE WHEN COALESCE(rr.dkim,'')='pass' AND COALESCE(rr.spf,'')='pass'
292 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_both,
293 SUM(CASE WHEN COALESCE(rr.dkim,'')='pass' AND COALESCE(rr.spf,'')<>'pass'
294 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_dkim,
295 SUM(CASE WHEN COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')='pass'
296 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_spf,
297 SUM(CASE WHEN COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')<>'pass'
298 THEN COALESCE(rr.count,1) ELSE 0 END) AS aligned_none,
299 SUM(CASE WHEN rr.disposition='none' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_none,
300 SUM(CASE WHEN rr.disposition='quarantine' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_quarantine,
301 SUM(CASE WHEN rr.disposition='reject' THEN COALESCE(rr.count,1) ELSE 0 END) AS disp_reject
302 EO_BUCKETS
303 ;
304 }
305  
306 sub agg_from_clause {
307 return <<'EO_FROM'
308 FROM report r
309 JOIN report_record rr ON r.id=rr.report_id
310 LEFT JOIN author a ON r.author_id=a.id
311 LEFT JOIN domain fd ON r.from_domain_id=fd.id
312 EO_FROM
313 ;
314 }
315  
316 # Reporting windows are epoch seconds, so a UTC day needs no dialect date
317 # functions. A window spanning a day boundary is attributed to its begin day;
318 # reporters almost always send daily windows, so the skew is negligible.
319 sub select_timeseries_query( $self, $where ) {
320 my $buckets = $self->outcome_buckets;
321 my $from = $self->agg_from_clause;
322 return <<"EO_TIMESERIES"
323 SELECT (r.begin - (r.begin % 86400)) AS day,
324 SUM(COALESCE(rr.count,1)) AS messages,
325 $buckets
326 $from WHERE 1=1$where
327 GROUP BY day
328 ORDER BY day ASC
329 EO_TIMESERIES
330 ;
331 }
332  
333 sub select_sources_query( $self, $where ) {
334 my $buckets = $self->outcome_buckets;
335 my $from = $self->agg_from_clause;
336 return <<"EO_SOURCES"
337 SELECT rr.source_ip AS source_ip,
338 SUM(COALESCE(rr.count,1)) AS messages,
339 $buckets,
340 COUNT(DISTINCT r.author_id) AS reporters,
341 MIN(r.begin) AS first_seen,
342 MAX(r.end) AS last_seen
343 $from WHERE 1=1$where
344 GROUP BY rr.source_ip
345 ORDER BY messages DESC
346 EO_SOURCES
347 ;
348 }
349  
350 # Kept out of the sources query on purpose: a record with two reasons would
351 # join twice and double every SUM in that row.
352 sub select_source_reasons_query( $self, $where ) {
353 my $from = $self->agg_from_clause;
354 return <<"EO_REASONS"
355 SELECT rr.source_ip AS source_ip,
356 rrr.type AS type,
357 rrr.comment AS comment,
358 SUM(COALESCE(rr.count,1)) AS messages
359 $from JOIN report_record_reason rrr ON rrr.report_record_id=rr.id
360 WHERE 1=1$where
361 GROUP BY rr.source_ip, rrr.type, rrr.comment
362 EO_REASONS
363 ;
364 }
365  
366 sub select_source_detail_query( $self, $where ) {
367 my $from = $self->agg_from_clause;
368 return <<"EO_DETAIL"
369 SELECT hfd.domain AS header_from,
370 rr.disposition AS disposition,
371 rr.dkim AS dkim,
372 rr.spf AS spf,
373 SUM(COALESCE(rr.count,1)) AS messages
374 $from LEFT JOIN domain hfd ON hfd.id=rr.header_from_did
375 WHERE 1=1$where
376 GROUP BY hfd.domain, rr.disposition, rr.dkim, rr.spf
377 ORDER BY messages DESC
378 EO_DETAIL
379 ;
380 }
381  
382 # The d= domain and selector a source presented are what distinguish a
383 # misconfigured ESP from an unauthenticated spoof.
384 sub select_source_dkim_query( $self, $where ) {
385 my $from = $self->agg_from_clause;
386 return <<"EO_SRC_DKIM"
387 SELECT d.domain AS domain,
388 k.selector AS selector,
389 k.result AS result,
390 SUM(COALESCE(rr.count,1)) AS messages
391 $from JOIN report_record_dkim k ON k.report_record_id=rr.id
392 LEFT JOIN domain d ON d.id=k.domain_id
393 WHERE 1=1$where
394 GROUP BY d.domain, k.selector, k.result
395 ORDER BY messages DESC
396 EO_SRC_DKIM
397 ;
398 }
399  
400 sub select_source_spf_query( $self, $where ) {
401 my $from = $self->agg_from_clause;
402 return <<"EO_SRC_SPF"
403 SELECT d.domain AS domain,
404 s.scope AS scope,
405 s.result AS result,
406 SUM(COALESCE(rr.count,1)) AS messages
407 $from JOIN report_record_spf s ON s.report_record_id=rr.id
408 LEFT JOIN domain d ON d.id=s.domain_id
409 WHERE 1=1$where
410 GROUP BY d.domain, s.scope, s.result
411 ORDER BY messages DESC
412 EO_SRC_SPF
413 ;
414 }
415  
416 # Per report totals for the report list, so a row can show what it contains
417 # without being expanded. Returned one row per (report, From, To) rather than
418 # aggregated into a string, because GROUP_CONCAT, group_concat and string_agg
419 # are spelled differently in all three engines; the caller assembles the sets.
420 sub and_failing {
421 return " AND COALESCE(rr.dkim,'')<>'pass' AND COALESCE(rr.spf,'')<>'pass'";
422 }
423  
424 sub select_report_summary_query {
425 my ($self) = @_;
426 my $buckets = $self->outcome_buckets;
427 return <<"EO_SUMMARY"
428 SELECT rr.report_id AS rid,
429 hfd.domain AS header_from,
430 etd.domain AS envelope_to,
431 SUM(COALESCE(rr.count,1)) AS messages,
432 $buckets
433 FROM report_record rr
434 LEFT JOIN domain hfd ON hfd.id=rr.header_from_did
435 LEFT JOIN domain etd ON etd.id=rr.envelope_to_did
436 WHERE rr.report_id IN (??)
437 GROUP BY rr.report_id, hfd.domain, etd.domain
438 EO_SUMMARY
439 ;
440 }
441  
442 sub select_report_domains_query( $self, $where ) {
443 return <<"EO_DOMAINS"
444 SELECT fd.domain AS domain,
445 COUNT(*) AS reports,
446 MIN(r.begin) AS first_seen,
447 MAX(r.end) AS last_seen
448 FROM report r
449 JOIN domain fd ON fd.id=r.from_domain_id
450 LEFT JOIN author a ON a.id=r.author_id
451 WHERE 1=1$where
452 GROUP BY fd.domain
453 ORDER BY fd.domain ASC
454 EO_DOMAINS
455 ;
456 }
457  
458 1;
459  
460 __END__
461  
462 =pod
463  
464 =head1 NAME
465  
466 Mail::DMARC::Report::Store::SQL::Grammars::SQLite - Grammar for working with sqlite databases.
467  
468 =head1 VERSION
469  
470 version 2.20260827
471  
472 =head1 SYPNOSIS
473  
474 Allow DMARC to be able to speak to SQLite databases.
475  
476 =head1 DESCRIPTION
477  
478 Uses ANSI SQL syntax, keeping the SQL as portable as possible.
479  
480 DB engine specific features are to be avoided.
481  
482 =head1 AUTHORS
483  
484 =over 4
485  
486 =item *
487  
488 Matt Simerson <msimerson@cpan.org>
489  
490 =item *
491  
492 Davide Migliavacca <shari@cpan.org>
493  
494 =item *
495  
496 Marc Bradshaw <marc@marcbradshaw.net>
497  
498 =back
499  
500 =head1 COPYRIGHT AND LICENSE
501  
502 This software is copyright (c) 2026 by Matt Simerson.
503  
504 This is free software; you can redistribute it and/or modify it under
505 the same terms as the Perl 5 programming language system itself.
506  
507 =cut