Zonemaster/Backend/DB/PostgreSQL.pm untested

filter: Statementstmt 0.0% 0 / 117 Branchbran 0.0% 0 / 6 Conditioncond 0.0% 0 / 18 MC/DCmcdc 0.0% 0 / 10 Subroutinesub 0.0% 0 / 17 Podpod 0.0% 0 / 8 totaltotal 0.0% 0 / 176 CC 4 SCAR 13.6
CC
4
Cov
0%
create_schema
12.0
add_batch_job
6.0
from_config
2.0
CRAP 3.9
1 package Zonemaster::Backend::DB::PostgreSQL;
2  
3 our $VERSION = '1.1.0';
4  
5 use Moose;
6 use 5.14.2;
7  
8 use DBI qw(:utils);
9 use Digest::MD5 qw(md5_hex);
10 use JSON::PP;
11 use Try::Tiny;
12  
13 use Zonemaster::Backend::DB;
14 use Zonemaster::Backend::Errors;
15  
16 with 'Zonemaster::Backend::DB';
17  
18 =head1 CLASS METHODS
19  
20 =head2 from_config
21  
22 Construct a new instance from a Zonemaster::Backend::Config.
23  
24 my $db = Zonemaster::Backend::DB::PostgreSQL->from_config( $config );
25  
26 =cut
27  
28 sub from_config {
29 my ( $class, $config ) = @_;
30  
31 my $database = $config->POSTGRESQL_database;
32 my $host = $config->POSTGRESQL_host;
33 my $port = $config->POSTGRESQL_port;
34 my $user = $config->POSTGRESQL_user;
35 my $password = $config->POSTGRESQL_password;
36  
37 my $data_source_name = "DBI:Pg:dbname=$database;host=$host;port=$port";
38  
39 return $class->new(
40 {
41 data_source_name => $data_source_name,
42 user => $user,
43 password => $password,
44 dbhandle => undef,
45 }
46 );
47 }
48  
49 sub get_dbh_specific_attributes {
50 return { pg_enable_utf8 => 0 };
51 }
52  
53 sub create_schema {
54 my ( $self ) = @_;
55  
56 my $dbh = $self->dbh;
57  
58 ####################################################################
59 # TEST RESULTS
60 ####################################################################
61 $dbh->do(
62 'CREATE TABLE IF NOT EXISTS test_results (
63 id BIGSERIAL PRIMARY KEY,
64 hash_id VARCHAR(16) NOT NULL,
65 domain VARCHAR(255) NOT NULL,
66 batch_id integer,
67 created_at TIMESTAMP NOT NULL,
68 started_at TIMESTAMP DEFAULT NULL,
69 ended_at TIMESTAMP DEFAULT NULL,
70 priority integer DEFAULT 10,
71 queue integer DEFAULT 0,
72 progress integer DEFAULT 0,
73 fingerprint varchar(32),
74 params json NOT NULL,
75 undelegated integer NOT NULL DEFAULT 0,
76 results json,
77  
78 UNIQUE (hash_id)
79 )
80 '
81 ) or die Zonemaster::Backend::Error::Internal->new( reason => "PostgreSQL error, could not create 'test_results' table", data => $dbh->errstr() );
82  
83 $dbh->do(
84 'CREATE INDEX IF NOT EXISTS test_results__hash_id ON test_results (hash_id)'
85 );
86 $dbh->do(
87 'CREATE INDEX IF NOT EXISTS test_results__fingerprint ON test_results (fingerprint)'
88 );
89 $dbh->do(
90 'CREATE INDEX IF NOT EXISTS test_results__batch_id_progress ON test_results (batch_id, progress)'
91 );
92 $dbh->do(
93 'CREATE INDEX IF NOT EXISTS test_results__progress ON test_results (progress)'
94 );
95 $dbh->do(
96 'CREATE INDEX IF NOT EXISTS test_results__domain_undelegated ON test_results (domain, undelegated)'
97 );
98 # this index helps speed up query time to retrieve the next test to
99 # perform when using batches
100 $dbh->do(
101 'CREATE INDEX IF NOT EXISTS test_results__progress_priority_id ON test_results (progress, priority DESC, id) WHERE (progress = 0)'
102 );
103  
104 ####################################################################
105 # LOG LEVEL
106 ####################################################################
107 $dbh->do(
108 "CREATE TABLE IF NOT EXISTS log_level (
109 value INT,
110 level VARCHAR(15),
111  
112 UNIQUE (value)
113 )
114 "
115 ) or die Zonemaster::Backend::Error::Internal->new( reason => "PostgreSQL error, could not create 'log_level' table", data => $dbh->errstr() );
116 my ( $c ) = $dbh->selectrow_array( "SELECT count(*) FROM log_level" );
117 if ( $c == 0 ) {
118 $dbh->do(
119 "INSERT INTO log_level (value, level)
120 VALUES
121 (-2, 'DEBUG3'),
122 (-1, 'DEBUG2'),
123 ( 0, 'DEBUG'),
124 ( 1, 'INFO'),
125 ( 2, 'NOTICE'),
126 ( 3, 'WARNING'),
127 ( 4, 'ERROR'),
128 ( 5, 'CRITICAL')
129 "
130 );
131 }
132  
133 ####################################################################
134 # RESULT ENTRIES
135 ####################################################################
136 $dbh->do(
137 'CREATE TABLE IF NOT EXISTS result_entries (
138 hash_id VARCHAR(16) NOT NULL,
139 level INT NOT NULL,
140 module VARCHAR(255) NOT NULL,
141 testcase VARCHAR(255) NOT NULL,
142 tag VARCHAR(255) NOT NULL,
143 timestamp REAL NOT NULL,
144 args JSONb NOT NULL,
145  
146 CONSTRAINT fk_hash_id FOREIGN KEY (hash_id) REFERENCES test_results(hash_id),
147 CONSTRAINT fk_level FOREIGN KEY(level) REFERENCES log_level(value)
148 )
149 '
150 ) or die Zonemaster::Backend::Error::Internal->new( reason => "PostgreSQL error, could not create 'result_entries' table", data => $dbh->errstr() );
151  
152 $dbh->do(
153 'CREATE INDEX IF NOT EXISTS result_entries__hash_id ON result_entries (hash_id)'
154 );
155  
156 $dbh->do(
157 'CREATE INDEX IF NOT EXISTS result_entries__level ON result_entries (level)'
158 );
159  
160 ####################################################################
161 # BATCH JOBS
162 ####################################################################
163 $dbh->do(
164 'CREATE TABLE IF NOT EXISTS batch_jobs (
165 id serial PRIMARY KEY,
166 username varchar(50) NOT NULL,
167 created_at TIMESTAMP NOT NULL
168 )
169 '
170 ) or die Zonemaster::Backend::Error::Internal->new( reason => "PostgreSQL error, could not create 'batch_jobs' table", data => $dbh->errstr() );
171  
172  
173 ####################################################################
174 # USERS
175 ####################################################################
176 $dbh->do(
177 'CREATE TABLE IF NOT EXISTS users (
178 id serial PRIMARY KEY,
179 username VARCHAR(128),
180 api_key VARCHAR(512),
181  
182 UNIQUE (username)
183 )
184 '
185 ) or die Zonemaster::Backend::Error::Internal->new( reason => "PostgreSQL error, could not create 'users' table", data => $dbh->errstr() );
186  
187 ####################################################################
188 # SCHEMA VERSION
189 ####################################################################
190 $dbh->do(
191 "CREATE TABLE IF NOT EXISTS schema_version (
192 id INTEGER PRIMARY KEY,
193 version INTEGER NOT NULL,
194 CHECK (id = 1),
195 CHECK (version >= 1)
196 )"
197 )
198 or die Zonemaster::Backend::Error::Internal->new(
199 reason => "Failed to create 'schema_version' table",
200 data => $dbh->errstr()
201 );
202  
203 if ( !defined $dbh->selectrow_array( "SELECT 1 FROM schema_version LIMIT 1" ) ) {
204 $dbh->do( "INSERT INTO schema_version (id, version) VALUES (1, ?)", {}, $Zonemaster::Backend::DB::REQUIRED_SCHEMA_VERSION );
205 }
206  
207 return;
208 }
209  
210 =head2 drop_tables
211  
212 Drop all the tables if they exist.
213  
214 =cut
215  
216 sub drop_tables {
217 my ( $self ) = @_;
218  
219 # Temporarily set the message level just above "notice" to mute messages when the tables don't
220 # exist.
221 # Without setting this level we run the risk of tripping up Test::NoWarnings in unit tests.
222 my ( $old_client_min_messages ) = $self->dbh->selectrow_array( "SHOW client_min_messages" );
223 $self->dbh->do( "SET client_min_messages = warning" );
224  
225 try {
226 $self->dbh->do( "DROP TABLE IF EXISTS schema_version CASCADE" );
227 $self->dbh->do( "DROP TABLE IF EXISTS test_results CASCADE" );
228 $self->dbh->do( "DROP TABLE IF EXISTS result_entries CASCADE" );
229 $self->dbh->do( "DROP TABLE IF EXISTS log_level" );
230 $self->dbh->do( "DROP TABLE IF EXISTS users" );
231 $self->dbh->do( "DROP TABLE IF EXISTS batch_jobs" );
232 }
233 finally {
234 $self->dbh->do( "SET client_min_messages = ?", undef, $old_client_min_messages );
235 };
236  
237 return;
238 }
239  
240 sub add_batch_job {
241 my ( $self, $params ) = @_;
242 my $batch_id;
243  
244 my $dbh = $self->dbh;
245  
246 if ( $self->user_authorized( $params->{username}, $params->{api_key} ) ) {
247 $batch_id = $self->create_new_batch_job( $params->{username} );
248  
249 my $test_params = $params->{test_params};
250  
251 my $priority = $test_params->{priority};
252 my $queue_label = $test_params->{queue};
253  
254 my $created_at = $self->format_time( time() );
255  
256 $dbh->begin_work();
257 $dbh->do( "ALTER TABLE test_results DROP CONSTRAINT IF EXISTS test_results_pkey" );
258 $dbh->do( "DROP INDEX IF EXISTS test_results__hash_id" );
259 $dbh->do( "DROP INDEX IF EXISTS test_results__fingerprint" );
260 $dbh->do( "DROP INDEX IF EXISTS test_results__batch_id_progress" );
261 $dbh->do( "DROP INDEX IF EXISTS test_results__progress" );
262 $dbh->do( "DROP INDEX IF EXISTS test_results__domain_undelegated" );
263  
264 $dbh->do(
265 q[
266 COPY test_results (
267 hash_id,
268 domain,
269 batch_id,
270 created_at,
271 priority,
272 queue,
273 fingerprint,
274 params,
275 undelegated
276 )
277 FROM STDIN
278 ]
279 );
280  
281 foreach my $domain ( @{$params->{domains}} ) {
282 $test_params->{domain} = _normalize_domain( $domain );
283  
284 my $fingerprint = $self->generate_fingerprint( $test_params );
285 my $encoded_params = $self->encode_params( $test_params );
286 my $undelegated = $self->undelegated ( $test_params );
287  
288 my $hash_id = substr(md5_hex(time().rand()), 0, 16);
289 $dbh->pg_putcopydata(
290 "$hash_id\t$test_params->{domain}\t$batch_id\t$created_at\t$priority\t$queue_label\t$fingerprint\t$encoded_params\t$undelegated\n"
291 );
292 }
293 $dbh->pg_putcopyend();
294 $dbh->do( "ALTER TABLE test_results ADD PRIMARY KEY (id)" );
295 $dbh->do( "CREATE INDEX test_results__hash_id ON test_results (hash_id, created_at)" );
296 $dbh->do( "CREATE INDEX test_results__fingerprint ON test_results (fingerprint)" );
297 $dbh->do( "CREATE INDEX test_results__batch_id_progress ON test_results (batch_id, progress)" );
298 $dbh->do( "CREATE INDEX test_results__progress ON test_results (progress)" );
299 $dbh->do( "CREATE INDEX test_results__domain_undelegated ON test_results (domain, undelegated)" );
300  
301 $dbh->commit();
302 }
303 else {
304 die Zonemaster::Backend::Error::PermissionDenied->new( message => 'User not authorized to use batch mode', data => { username => $params->{username}} );
305 }
306  
307 return $batch_id;
308 }
309  
310 sub get_relative_start_time {
311 my ( $self, $hash_id ) = @_;
312  
313 return $self->dbh->selectrow_array(
314 q[
315 SELECT EXTRACT(EPOCH FROM ? - started_at)
316 FROM test_results
317 WHERE hash_id=?
318 ],
319 undef,
320 $self->format_time( time() ),
321 $hash_id,
322 );
323 }
324  
325 sub is_duplicate {
326 my ( $self ) = @_;
327  
328 my $state = $self->dbh->state;
329  
330 # for the list of codes see:
331 # https://www.postgresql.org/docs/current/errcodes-appendix.html
332 return defined $state
333 && $state eq '23505';
334 }
335  
336 sub is_unknown_table {
337 my ( $self ) = @_;
338  
339 my $state = $self->dbh->state;
340  
341 # for the list of codes see:
342 # https://www.postgresql.org/docs/current/errcodes-appendix.html
343 return defined $state
344 && $state eq '42P01';
345 }
346  
347 no Moose;
348 __PACKAGE__->meta()->make_immutable();
349  
350 1;