Zonemaster/Backend/DB/MySQL.pm untested

filter: Statementstmt 0.0% 0 / 129 Branchbran 0.0% 0 / 30 Conditioncond 0.0% 0 / 12 MC/DCmcdc 0.0% 0 / 6 Subroutinesub 0.0% 0 / 16 Podpod 0.0% 0 / 8 totaltotal 0.0% 0 / 201 CC 16 SCAR 37.3
CC
16
Cov
0%
create_schema
110.0
drop_tables
30.0
from_config
6.0
CRAP 41.9
1 package Zonemaster::Backend::DB::MySQL;
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  
12 use Zonemaster::Backend::Validator qw( untaint_ipv6_address );
13 use Zonemaster::Backend::Errors;
14  
15 with 'Zonemaster::Backend::DB';
16  
17 =head1 CLASS METHODS
18  
19 =head2 from_config
20  
21 Construct a new instance from a Zonemaster::Backend::Config.
22  
23 my $db = Zonemaster::Backend::DB::MySQL->from_config( $config );
24  
25 =cut
26  
27 sub from_config {
28 my ( $class, $config ) = @_;
29  
30 my $database = $config->MYSQL_database;
31 my $host = $config->MYSQL_host;
32 my $port = $config->MYSQL_port;
33 my $user = $config->MYSQL_user;
34 my $password = $config->MYSQL_password;
35  
36 if ( untaint_ipv6_address( $host ) ) {
37 $host = "[$host]";
38 }
39  
40 my $data_source_name = "DBI:mysql:database=$database;host=$host;port=$port";
41  
42 return $class->new(
43 {
44 data_source_name => $data_source_name,
45 user => $user,
46 password => $password,
47 dbhandle => undef,
48 }
49 );
50 }
51  
52 sub get_dbh_specific_attributes {
53 return {};
54 }
55  
56 sub create_schema {
57 my ( $self ) = @_;
58  
59 my $dbh = $self->dbh;
60  
61 ####################################################################
62 # TEST RESULTS
63 ####################################################################
64 $dbh->do(
65 'CREATE TABLE IF NOT EXISTS test_results (
66 id BIGINT AUTO_INCREMENT PRIMARY KEY,
67 hash_id VARCHAR(16) NOT NULL,
68 domain varchar(255) NOT NULL,
69 batch_id integer NULL,
70 created_at DATETIME NOT NULL,
71 started_at DATETIME DEFAULT NULL,
72 ended_at DATETIME DEFAULT NULL,
73 priority integer DEFAULT 10,
74 queue integer DEFAULT 0,
75 progress integer DEFAULT 0,
76 fingerprint character varying(32),
77 params blob NOT NULL,
78 results mediumblob DEFAULT NULL,
79 undelegated integer NOT NULL DEFAULT 0,
80  
81 UNIQUE (hash_id)
82 ) ENGINE=InnoDB
83 '
84 ) or die Zonemaster::Backend::Error::Internal->new( reason => "MySQL error, could not create 'test_results' table", data => $dbh->errstr() );
85  
86 # Manually create the index if it does not exist
87 # the clause IF NOT EXISTS is not available for MySQL (used with FreeBSD)
88  
89 # retrieve all indexes by key name
90 my $indexes = $dbh->selectall_hashref( 'SHOW INDEXES FROM test_results', 'Key_name' );
91  
92 if ( not exists($indexes->{test_results__hash_id}) ) {
93 $dbh->do(
94 'CREATE INDEX test_results__hash_id ON test_results (hash_id)'
95 );
96 }
97 if ( not exists($indexes->{test_results__fingerprint}) ) {
98 $dbh->do(
99 'CREATE INDEX test_results__fingerprint ON test_results (fingerprint)'
100 );
101 }
102 if ( not exists($indexes->{test_results__batch_id_progress}) ) {
103 $dbh->do(
104 'CREATE INDEX test_results__batch_id_progress ON test_results (batch_id, progress)'
105 );
106 }
107 if ( not exists($indexes->{test_results__progress}) ) {
108 $dbh->do(
109 'CREATE INDEX test_results__progress ON test_results (progress)'
110 );
111 }
112 if ( not exists($indexes->{test_results__domain_undelegated}) ) {
113 $dbh->do(
114 'CREATE INDEX test_results__domain_undelegated ON test_results (domain, undelegated)'
115 );
116 }
117  
118 ####################################################################
119 # LOG LEVEL
120 ####################################################################
121 $dbh->do(
122 "CREATE TABLE IF NOT EXISTS log_level (
123 value INT,
124 level VARCHAR(15),
125  
126 UNIQUE (value)
127 ) ENGINE=InnoDB
128 "
129 ) or die Zonemaster::Backend::Error::Internal->new( reason => "MySQL error, could not create 'log_level' table", data => $dbh->errstr() );
130  
131 my ( $c ) = $dbh->selectrow_array( "SELECT count(*) FROM log_level" );
132 if ( $c == 0 ) {
133 $dbh->do(
134 "INSERT INTO log_level (value, level)
135 VALUES
136 (-2, 'DEBUG3'),
137 (-1, 'DEBUG2'),
138 ( 0, 'DEBUG'),
139 ( 1, 'INFO'),
140 ( 2, 'NOTICE'),
141 ( 3, 'WARNING'),
142 ( 4, 'ERROR'),
143 ( 5, 'CRITICAL')
144 "
145 );
146 }
147  
148 ####################################################################
149 # RESULT ENTRIES
150 ####################################################################
151 $dbh->do(
152 "CREATE TABLE IF NOT EXISTS result_entries (
153 hash_id VARCHAR(16) NOT NULL,
154 level INT NOT NULL,
155 module VARCHAR(255) NOT NULL,
156 testcase VARCHAR(255) NOT NULL,
157 tag VARCHAR(255) NOT NULL,
158 timestamp REAL NOT NULL,
159 args BLOB NOT NULL,
160  
161 CONSTRAINT fk_hash_id FOREIGN KEY (hash_id) REFERENCES test_results(hash_id),
162 CONSTRAINT fk_level FOREIGN KEY (level) REFERENCES log_level(value)
163 ) ENGINE=InnoDB
164 "
165 ) or die Zonemaster::Backend::Error::Internal->new( reason => "MySQL error, could not create 'result_entries' table", data => $dbh->errstr() );
166  
167 $indexes = $dbh->selectall_hashref( 'SHOW INDEXES FROM result_entries', 'Key_name' );
168 if ( not exists($indexes->{result_entries__hash_id}) ) {
169 $dbh->do(
170 'CREATE INDEX result_entries__hash_id ON result_entries (hash_id)'
171 );
172 }
173  
174 if ( not exists($indexes->{result_entries__level}) ) {
175 $dbh->do(
176 'CREATE INDEX result_entries__level ON result_entries (level)'
177 );
178 }
179  
180  
181 ####################################################################
182 # BATCH JOBS
183 ####################################################################
184 $dbh->do(
185 'CREATE TABLE IF NOT EXISTS batch_jobs (
186 id integer AUTO_INCREMENT PRIMARY KEY,
187 username character varying(50) NOT NULL,
188 created_at DATETIME NOT NULL
189 ) ENGINE=InnoDB;
190 '
191 ) or die Zonemaster::Backend::Error::Internal->new( reason => "MySQL error, could not create 'batch_jobs' table", data => $dbh->errstr() );
192  
193  
194 ####################################################################
195 # USERS
196 ####################################################################
197 $dbh->do(
198 'CREATE TABLE IF NOT EXISTS users (
199 id integer AUTO_INCREMENT primary key,
200 username varchar(128),
201 api_key varchar(512),
202  
203 UNIQUE (username)
204 ) ENGINE=InnoDB;
205 '
206 ) or die Zonemaster::Backend::Error::Internal->new( reason => "MySQL error, could not create 'users' table", data => $dbh->errstr() );
207  
208 ####################################################################
209 # SCHEMA VERSION
210 ####################################################################
211 $dbh->do(
212 "CREATE TABLE IF NOT EXISTS schema_version (
213 id INTEGER PRIMARY KEY,
214 version INTEGER NOT NULL,
215 CHECK (id = 1),
216 CHECK (version >= 1)
217 )"
218 )
219 or die Zonemaster::Backend::Error::Internal->new(
220 reason => "Failed to create 'schema_version' table",
221 data => $dbh->errstr()
222 );
223  
224 if ( !defined $dbh->selectrow_array( "SELECT 1 FROM schema_version LIMIT 1" ) ) {
225 $dbh->do( "INSERT INTO schema_version (id, version) VALUES (1, ?)", {}, $Zonemaster::Backend::DB::REQUIRED_SCHEMA_VERSION );
226 }
227  
228 return;
229 }
230  
231 =head2 drop_tables
232  
233 Drop all the tables if they exist.
234  
235 =cut
236  
237 sub drop_tables {
238 my ( $self ) = @_;
239  
240 # remove any FOREIGN KEY before droping the table
241 # MariaDB <10.4 and MySQL do not support the IF EXISTS syntax
242 # on ALTER TABLE and DROP FOREIGN KEY
243 # MariaDB 10.3 is used on Ubuntu 20.04 LTS (eol 2023-04)
244 # MySQL is used on FreeBSD
245 my $tables = $self->dbh->selectall_hashref( 'SHOW TABLE STATUS', 'Name' );
246 if ( exists $tables->{result_entries} ) {
247 my @fk = $self->dbh->selectall_array( 'SELECT constraint_name FROM information_schema.referential_constraints' );
248 @fk = map { ref eq 'ARRAY' ? @$_ : $_ } @fk;
249 if ( grep( /^fk_hash_id$/, @fk ) ) {
250 $self->dbh->do( "ALTER TABLE result_entries DROP FOREIGN KEY fk_hash_id" );
251 }
252 if ( grep( /^fk_level$/, @fk ) ) {
253 $self->dbh->do( "ALTER TABLE result_entries DROP FOREIGN KEY fk_level" );
254 }
255 }
256  
257 $self->dbh->do( "DROP TABLE IF EXISTS schema_version" );
258 $self->dbh->do( "DROP TABLE IF EXISTS test_results" );
259 $self->dbh->do( "DROP TABLE IF EXISTS result_entries" );
260 $self->dbh->do( "DROP TABLE IF EXISTS log_level" );
261 $self->dbh->do( "DROP TABLE IF EXISTS users" );
262 $self->dbh->do( "DROP TABLE IF EXISTS batch_jobs" );
263  
264 return;
265 }
266  
267 sub add_batch_job {
268 my ( $self, $params ) = @_;
269 my $batch_id;
270  
271 my $dbh = $self->dbh;
272  
273 if ( $self->user_authorized( $params->{username}, $params->{api_key} ) ) {
274 $batch_id = $self->create_new_batch_job( $params->{username} );
275  
276 my $test_params = $params->{test_params};
277 my $priority = $test_params->{priority};
278 my $queue_label = $test_params->{queue};
279  
280 $dbh->{AutoCommit} = 0;
281 eval {$dbh->do( "DROP INDEX test_results__hash_id ON test_results" );};
282 eval {$dbh->do( "DROP INDEX test_results__fingerprint ON test_results" );};
283 eval {$dbh->do( "DROP INDEX test_results__batch_id_progress ON test_results" );};
284 eval {$dbh->do( "DROP INDEX test_results__progress ON test_results" );};
285 eval {$dbh->do( "DROP INDEX test_results__domain_undelegated ON test_results" );};
286  
287 my $sth = $dbh->prepare(
288 q[
289 INSERT INTO test_results (
290 hash_id,
291 domain,
292 batch_id,
293 created_at,
294 priority,
295 queue,
296 fingerprint,
297 params,
298 undelegated
299 ) VALUES (?,?,?,?,?,?,?,?,?)
300 ],
301 );
302 foreach my $domain ( @{$params->{domains}} ) {
303 $test_params->{domain} = _normalize_domain( $domain );
304  
305 my $fingerprint = $self->generate_fingerprint( $test_params );
306 my $encoded_params = $self->encode_params( $test_params );
307 my $undelegated = $self->undelegated ( $test_params );
308  
309 my $hash_id = substr(md5_hex(time().rand()), 0, 16);
310 $sth->execute(
311 $hash_id,
312 $test_params->{domain},
313 $batch_id,
314 $self->format_time( time() ),
315 $priority,
316 $queue_label,
317 $fingerprint,
318 $encoded_params,
319 $undelegated,
320 );
321 }
322 $dbh->do( "CREATE INDEX test_results__hash_id ON test_results (hash_id, created_at)" );
323 $dbh->do( "CREATE INDEX test_results__fingerprint ON test_results (fingerprint)" );
324 $dbh->do( "CREATE INDEX test_results__batch_id_progress ON test_results (batch_id, progress)" );
325 $dbh->do( "CREATE INDEX test_results__progress ON test_results (progress)" );
326 $dbh->do( "CREATE INDEX test_results__domain_undelegated ON test_results (domain, undelegated)" );
327  
328 $dbh->commit();
329 $dbh->{AutoCommit} = 1;
330 }
331 else {
332 die Zonemaster::Backend::Error::PermissionDenied->new( message => 'User not authorized to use batch mode', data => { username => $params->{username}} );
333 }
334  
335 return $batch_id;
336 }
337  
338 sub get_relative_start_time {
339 my ( $self, $hash_id ) = @_;
340  
341 return $self->dbh->selectrow_array(
342 q[
343 SELECT ? - started_at
344 FROM test_results
345 WHERE hash_id = ?
346 ],
347 undef,
348 $self->format_time( time() ),
349 $hash_id,
350 );
351 }
352  
353 sub is_duplicate {
354 my ( $self ) = @_;
355  
356 # for the list of codes see:
357 # https://mariadb.com/kb/en/mariadb-error-codes/
358 # https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
359 return ( $self->dbh->err == 1062 );
360 }
361  
362 sub is_unknown_table {
363 my ( $self ) = @_;
364  
365 # for the list of codes see:
366 # https://mariadb.com/kb/en/mariadb-error-codes/
367 # https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
368 return ( $self->dbh->err == 1146 );
369 }
370  
371 no Moose;
372 __PACKAGE__->meta()->make_immutable();
373  
374 1;