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

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