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