File Coverage

lib/Mail/DMARC/Report/Store/SQL/Grammars/PostgreSQL.pm
Criterion Covered Total %
statement 6 91 6.5
branch 0 10 0.0
condition 0 6 0.0
subroutine 2 42 4.7
pod 0 40 0.0
total 8 189 4.2


line stmt bran cond sub pod time code
1             package Mail::DMARC::Report::Store::SQL::Grammars::PostgreSQL;
2             our $VERSION = '1.20230215';
3 4     4   34 use strict;
  4         12  
  4         115  
4 4     4   22 use warnings;
  4         9  
  4         5427  
5              
6             sub new {
7 0     0 0   my $class = shift;
8 0           my $self = { };
9 0           bless $self, $class;
10 0           return $self;
11             }
12              
13             sub language {
14 0     0 0   return 'postgresql';
15             }
16              
17             sub dsn {
18 0     0 0   return 'Pg';
19             }
20              
21             sub and_arg {
22 0     0 0   my ($self, $column, $operator) = @_;
23 0   0       $operator //= '=';
24 0 0         $column =~ s/(\w+)\.(\w+)/"$1"."$2"/ if $column =~ /\./;
25              
26 0           return " AND $column $operator ?";
27             }
28              
29             sub report_record_id {
30 0     0 0   return 'SELECT "id" FROM "report_record" WHERE "report_id"=?';
31             }
32              
33             sub delete_from_where_record_in {
34 0     0 0   my ($self, $table, $row_ids) = @_;
35 0           return "DELETE FROM \"$table\" WHERE \"report_record_id\" IN (??)"
36             }
37              
38             sub delete_from_where_report {
39 0     0 0   my ($self, $table) = @_;
40 0           return "DELETE FROM \"$table\" WHERE \"report_id\"=?";
41             }
42              
43             sub delete_report {
44 0     0 0   return "DELETE FROM \"report\" WHERE \"id\"=?";
45             }
46              
47             sub select_domain_id {
48 0     0 0   return 'SELECT "id" FROM "domain" WHERE "domain"=?';
49             }
50              
51             sub select_report_id {
52 0     0 0   return 'SELECT "id" FROM "report" WHERE "uuid"=? AND "author_id"=?';
53             }
54              
55             sub select_id_with_end {
56 0     0 0   return 'SELECT "id" FROM "report" WHERE "from_domain_id"=? AND "end" > ? AND "author_id"=?';
57             }
58              
59             sub insert_domain {
60 0     0 0   return 'INSERT INTO "domain" ("domain") VALUES (?)';
61             }
62              
63             sub select_author_id {
64 0     0 0   return 'SELECT "id" FROM "author" WHERE "org_name"=?';
65             }
66              
67             sub insert_author {
68 0     0 0   return 'INSERT INTO "author" ("org_name", "email", "extra_contact") VALUES (?,?,?)';
69             }
70              
71             sub insert_report {
72 0     0 0   return 'INSERT INTO "report" ("from_domain_id", "begin", "end", "author_id", "uuid") VALUES (?,?,?,?,?)';
73             }
74              
75             sub order_by {
76 0     0 0   my ($self, $arg, $order) = @_;
77 0           return " ORDER BY \"$arg\" $order";
78             }
79              
80             sub count_reports {
81 0     0 0   return 'SELECT COUNT(*) FROM "report"';
82             }
83              
84             sub limit {
85 0     0 0   my ($self, $number_of_entries) = @_;
86 0   0       $number_of_entries //= 1;
87 0           return " LIMIT $number_of_entries";
88             }
89              
90             sub limit_args {
91 0     0 0   my ($self, $number_of_entries) = @_;
92 0           my $return = ' LIMIT ?';
93 0   0       $number_of_entries //= 1;
94 0 0         if ($number_of_entries > 1) {
95 0           $return = " OFFSET ? $return";
96             }
97 0           return $return;
98             }
99              
100             sub select_report_policy_published {
101 0     0 0   return 'SELECT * from "report_policy_published" WHERE "report_id"=?';
102             }
103              
104             sub select_report_reason {
105 0     0 0   return 'SELECT "type","comment" FROM "report_record_reason" WHERE "report_record_id"=?';
106             }
107              
108             sub select_report_error {
109 0     0 0   return 'SELECT "error" FROM "report_error" WHERE "report_id"=?';
110             }
111              
112             sub select_report_record {
113 0     0 0   return 'SELECT "id" FROM "report_record" WHERE "report_id"=? AND "source_ip"=? AND "count"=?'
114             }
115              
116             sub select_todo_query {
117             return <<'EO_TODO_QUERY'
118             SELECT "r"."id" AS "rid",
119             "r"."begin" AS "begin",
120             "r"."end" AS "end",
121             "a"."org_name" AS "author",
122             "fd"."domain" AS "from_domain"
123             FROM "report" "r"
124             LEFT JOIN "report_record" "rr" ON "r"."id"="rr"."report_id"
125             LEFT JOIN "author" "a" ON "r"."author_id"="a"."id"
126             LEFT JOIN "domain" "fd" ON "r"."from_domain_id"="fd"."id"
127             WHERE "rr"."count" IS NULL
128             AND "rr"."report_id" IS NOT NULL
129             AND "r"."end" < ?
130             GROUP BY "r"."id", "r"."begin", "r"."end", "a"."org_name", "fd"."domain"
131             ORDER BY "r"."id" ASC
132             EO_TODO_QUERY
133 0     0 0   ;
134             }
135              
136             sub select_row_spf {
137             return <<"EO_SPF_ROW"
138             SELECT "d"."domain" AS "domain",
139             "s"."result" AS "result",
140             "s"."scope" AS "scope"
141             FROM "report_record_spf" "s"
142             LEFT JOIN "domain" "d" ON "s"."domain_id"="d"."id"
143             WHERE "s"."report_record_id"=?
144             ORDER BY "s"."id" ASC
145             EO_SPF_ROW
146 0     0 0   ;
147             }
148              
149              
150             sub select_row_dkim {
151             return <<"EO_DKIM_ROW"
152             SELECT "d"."domain" AS "domain",
153             "k"."selector" AS "selector",
154             "k"."result" AS "result",
155             "k"."human_result" AS "human_result"
156             FROM "report_record_dkim" "k"
157             LEFT JOIN "domain" "d" ON "k"."domain_id"="d"."id"
158             WHERE "report_record_id"=?
159             ORDER BY "k"."id" ASC
160             EO_DKIM_ROW
161 0     0 0   ;
162             }
163              
164             sub select_row_reason {
165             return <<"EO_ROW_QUERY"
166             SELECT "type","comment"
167             FROM "report_record_reason"
168             WHERE "report_record_id"=?
169             EO_ROW_QUERY
170 0     0 0   ;
171             }
172              
173             sub select_rr_query {
174             return <<'EO_ROW_QUERY'
175             SELECT "rr".*,
176             "etd"."domain" AS "envelope_to",
177             "efd"."domain" AS "envelope_from",
178             "hfd"."domain" AS "header_from"
179             FROM "report_record" "rr"
180             LEFT JOIN "domain" "etd" ON "etd"."id"="rr"."envelope_to_did"
181             LEFT JOIN "domain" "efd" ON "efd"."id"="rr"."envelope_from_did"
182             LEFT JOIN "domain" "hfd" ON "hfd"."id"="rr"."header_from_did"
183             WHERE "report_id" = ?
184             ORDER BY "id" ASC
185             EO_ROW_QUERY
186 0     0 0   ;
187             }
188              
189             sub select_report_query {
190             return <<'EO_REPORTS'
191             SELECT "r"."id" AS "rid",
192             "r"."uuid",
193             "r"."begin" AS "begin",
194             "r"."end" AS "end",
195             "a"."org_name" AS "author",
196             "fd"."domain" AS "from_domain"
197             FROM "report" "r"
198             LEFT JOIN "author" "a" ON "r"."author_id"="a"."id"
199             LEFT JOIN "domain" "fd" ON "r"."from_domain_id"="fd"."id"
200             WHERE 1=1
201             EO_REPORTS
202 0     0 0   ;
203             }
204              
205             sub insert_error {
206 0     0 0   my ( $self, $which ) = @_;
207 0 0         if ( $which == 0 ) {
208 0           return 'UPDATE "report" SET "end"=? WHERE "id"=?';
209             } else {
210 0           return 'INSERT INTO "report_error" ("report_id", "error") VALUES (?,?)';
211             }
212             }
213              
214             sub insert_rr_reason {
215 0     0 0   return 'INSERT INTO "report_record_reason" ("report_record_id", "type", "comment") VALUES (?,?,?)'
216             }
217              
218             sub insert_rr_dkim {
219 0     0 0   my ( $self, $fields ) = @_;
220 0           my $fields_str = join '", "', @$fields;
221             return <<"EO_DKIM"
222             INSERT INTO "report_record_dkim"
223             ("report_record_id", \"$fields_str\")
224             VALUES (??)
225             EO_DKIM
226 0           ;
227             }
228              
229             sub insert_rr_spf {
230 0     0 0   my ( $self, $fields ) = @_;
231 0           my $fields_str = join '", "', @$fields;
232 0           return "INSERT INTO \"report_record_spf\" (\"report_record_id\", \"$fields_str\") VALUES(??)";
233             }
234              
235             sub insert_rr {
236             return <<'EO_ROW_INSERT'
237             INSERT INTO report_record
238             (report_id, source_ip, count, header_from_did, envelope_to_did, envelope_from_did,
239             disposition, dkim, spf)
240             VALUES (??)
241             EO_ROW_INSERT
242 0     0 0   ;
243             }
244              
245             sub insert_policy_published {
246             return <<"EO_RPP"
247             INSERT INTO report_policy_published
248             (report_id, adkim, aspf, p, sp, pct, rua)
249             VALUES (??)
250             EO_RPP
251 0     0 0   ;
252             }
253              
254             sub select_from {
255 0     0 0   my ($self, $columns, $table) = @_;
256 0           my $colStr = '*';
257 0 0         if ( @{$columns}[0] ne '*' ) {
  0            
258 0           my @cols;
259 0           foreach my $col (@$columns) {
260 0 0         if ( $col =~ /(\w+)(?:\s+as\s+(\w+))/i ) {
261 0           $col = "$1\" AS \"$2";
262             }
263 0           $col = "\"$col\"";
264 0           push @cols, $col;
265             }
266 0           $colStr = join( ', ', @cols );
267             }
268 0           return "SELECT $colStr FROM \"$table\" WHERE 1=1";
269             }
270              
271             sub insert_into {
272 0     0 0   my ($self, $table, $cols) = @_;
273 0           my $columns = '"' . join( '", "', @$cols ) . '"';
274 0           return "INSERT INTO \"$table\" ($columns) VALUES (??)";
275             }
276              
277             sub update {
278 0     0 0   my ($self, $table, $cols) = @_;
279 0           my $columns = '"' . join( '" = ?, "') . '" = ?';
280 0           return "UPDATE \"$table\" SET $columns WHERE 1=1";
281             }
282              
283             sub delete_from {
284 0     0 0   my ($self, $table) = @_;
285 0           return "DELETE FROM \"$table\" WHERE 1=1";
286             }
287              
288             sub replace_into {
289 0     0 0   my ($self, $table, $cols) = @_;
290 0           my $insertColumns = '"' . join( '", "', @$cols ) . '"';
291 0           my @ucols;
292 0           foreach my $col (@$cols) {
293 0           push @ucols, "\"$col\" = EXCLUDED.\"$col\""
294             }
295 0           my $updateColumns = join ', ', @ucols;
296 0           return "INSERT INTO \"$table\" ($insertColumns) VALUES (??)
297             ON CONFLICT ($insertColumns) DO UPDATE SET $updateColumns";
298             }
299              
300             1;
301              
302             __END__