« Zonemaster/Backend/Log.pm
↑ Summary
auto/share/dist/Zonemaster-Backend/patch/patch_db_zonemaster_backend_ver_11.1.0.pl »
| 1 | package Zonemaster::Backend::DB; | ||
| 2 | |||
| 3 | our $VERSION = '1.2.0'; | ||
| 4 | |||
| 5 | use Moose::Role; | ||
| 6 | |||
| 7 | use 5.14.2; | ||
| 8 | |||
| 9 | use Carp qw( croak ); | ||
| 10 | use DBI qw( :sql_types ); | ||
| 11 | use Digest::MD5 qw( md5_hex ); | ||
| 12 | use Encode; | ||
| 13 | use Exporter qw( import ); | ||
| 14 | use JSON::PP; | ||
| 15 | use Log::Any qw( $log ); | ||
| 16 | use POSIX qw( strftime ); | ||
| 17 | use Readonly; | ||
| 18 | use Try::Tiny; | ||
| 19 | |||
| 20 | use Zonemaster::Backend::Errors; | ||
| 21 | use Zonemaster::Engine::Normalization qw( normalize_name trim_space ); | ||
| 22 | use Zonemaster::Engine::Logger::Entry; | ||
| 23 | |||
| 24 | requires qw( | ||
| 25 | add_batch_job | ||
| 26 | create_schema | ||
| 27 | drop_tables | ||
| 28 | from_config | ||
| 29 | get_dbh_specific_attributes | ||
| 30 | get_relative_start_time | ||
| 31 | is_duplicate | ||
| 32 | is_unknown_table | ||
| 33 | ); | ||
| 34 | |||
| 35 | has 'data_source_name' => ( | ||
| 36 | is => 'ro', | ||
| 37 | isa => 'Str', | ||
| 38 | required => 1, | ||
| 39 | ); | ||
| 40 | |||
| 41 | has 'user' => ( | ||
| 42 | is => 'ro', | ||
| 43 | isa => 'Str', | ||
| 44 | required => 1, | ||
| 45 | ); | ||
| 46 | |||
| 47 | has 'password' => ( | ||
| 48 | is => 'ro', | ||
| 49 | isa => 'Str', | ||
| 50 | required => 1, | ||
| 51 | ); | ||
| 52 | |||
| 53 | has 'dbhandle' => ( | ||
| 54 | is => 'rw', | ||
| 55 | isa => 'Maybe[DBI::db]', | ||
| 56 | required => 1, | ||
| 57 | ); | ||
| 58 | |||
| 59 | has 'dbhandlepid' => ( | ||
| 60 | is => 'rw', | ||
| 61 | isa => 'Maybe[Int]', | ||
| 62 | required => 0, | ||
| 63 | ); | ||
| 64 | |||
| 65 | =head2 $REQUIRED_SCHEMA_VERSION | ||
| 66 | |||
| 67 | A positive integer. The database schema version that this module is compatible with. | ||
| 68 | |||
| 69 | =cut | ||
| 70 | |||
| 71 | Readonly our $REQUIRED_SCHEMA_VERSION => 1; | ||
| 72 | |||
| 73 | =head2 $TEST_WAITING | ||
| 74 | |||
| 75 | The test is waiting to be processed. | ||
| 76 | |||
| 77 | =cut | ||
| 78 | |||
| 79 | Readonly our $TEST_WAITING => 'WAITING'; | ||
| 80 | |||
| 81 | =head2 $TEST_RUNNING | ||
| 82 | |||
| 83 | The test is currently being processed. | ||
| 84 | |||
| 85 | =cut | ||
| 86 | |||
| 87 | Readonly our $TEST_RUNNING => 'RUNNING'; | ||
| 88 | |||
| 89 | =head2 $TEST_COMPLETED | ||
| 90 | |||
| 91 | The test was already processed. | ||
| 92 | |||
| 93 | This state encompasses all of the following: | ||
| 94 | |||
| 95 | =over 2 | ||
| 96 | |||
| 97 | =item | ||
| 98 | |||
| 99 | The Zonemaster Engine test terminated normally. | ||
| 100 | |||
| 101 | =item | ||
| 102 | |||
| 103 | A critical error occurred while processing. | ||
| 104 | |||
| 105 | =item | ||
| 106 | |||
| 107 | The processing was cancelled because it took too long. | ||
| 108 | |||
| 109 | =back | ||
| 110 | |||
| 111 | =cut | ||
| 112 | |||
| 113 | Readonly our $TEST_COMPLETED => 'COMPLETED'; | ||
| 114 | |||
| 115 | our @EXPORT_OK = qw( | ||
| 116 | $TEST_WAITING | ||
| 117 | $TEST_RUNNING | ||
| 118 | $TEST_COMPLETED | ||
| 119 | ); | ||
| 120 | |||
| 121 | |||
| 122 | =head2 get_db_class | ||
| 123 | |||
| 124 | Get the database adapter class for the given database type. | ||
| 125 | |||
| 126 | Throws and exception if the database adapter class cannot be loaded. | ||
| 127 | |||
| 128 | =cut | ||
| 129 | |||
| 130 | sub get_db_class { | ||
| 131 | my ( $class, $db_type ) = @_; | ||
| 132 | |||
| 133 | my $db_class = "Zonemaster::Backend::DB::$db_type"; | ||
| 134 | |||
| 135 | require( "$db_class.pm" =~ s{::}{/}gr ); | ||
| 136 | $db_class->import(); | ||
| 137 | |||
| 138 | return $db_class; | ||
| 139 | } | ||
| 140 | |||
| 141 | sub dbh { | ||
| 142 | my ( $self ) = @_; | ||
| 143 | |||
| 144 | # Do not return the DB handle if it belongs to a different process: we | ||
| 145 | # might have forked and be in the child process. If we are not careful, we | ||
| 146 | # might be messing with someone else's database connection! | ||
| 147 | if ( $self->dbhandle && $self->dbhandle->ping && $self->dbhandlepid && $self->dbhandlepid == $$ ) { | ||
| 148 | return $self->dbhandle; | ||
| 149 | } | ||
| 150 | |||
| 151 | if ( $self->user ) { | ||
| 152 | $log->noticef( "Connecting to database '%s' as user '%s'", $self->data_source_name, $self->user ); | ||
| 153 | } | ||
| 154 | else { | ||
| 155 | $log->noticef( "Connecting to database '%s'", $self->data_source_name ); | ||
| 156 | } | ||
| 157 | |||
| 158 | my $attr = { | ||
| 159 | RaiseError => 1, | ||
| 160 | AutoCommit => 1, | ||
| 161 | AutoInactiveDestroy => 1, | ||
| 162 | }; | ||
| 163 | |||
| 164 | $attr = { %$attr, %{ $self->get_dbh_specific_attributes } }; | ||
| 165 | |||
| 166 | my $dbh = DBI->connect( | ||
| 167 | $self->data_source_name, | ||
| 168 | $self->user, | ||
| 169 | $self->password, | ||
| 170 | $attr | ||
| 171 | ); | ||
| 172 | |||
| 173 | $self->dbhandle( $dbh ); | ||
| 174 | $self->dbhandlepid( $$ ); | ||
| 175 | |||
| 176 | return $self->dbhandle; | ||
| 177 | } | ||
| 178 | |||
| 179 | =head2 get_schema_version | ||
| 180 | |||
| 181 | Detect the schema version of the database. | ||
| 182 | |||
| 183 | Returns an unsigned integer. | ||
| 184 | |||
| 185 | The C<0> value means the database is in a schema state prior to schema versioning. | ||
| 186 | |||
| 187 | =cut | ||
| 188 | |||
| 189 | sub get_schema_version { | ||
| 190 | my ( $self ) = @_; | ||
| 191 | |||
| 192 | my $dbh = $self->dbh; | ||
| 193 | |||
| 194 | local $dbh->{RaiseError} = 0; | ||
| 195 | local $dbh->{PrintError} = 0; | ||
| 196 | my $result = $dbh->selectcol_arrayref( "SELECT version FROM schema_version LIMIT 2" ); | ||
| 197 | |||
| 198 | if ( $dbh->err ) { | ||
| 199 | if ( !$self->is_unknown_table ) { | ||
| 200 | croak "Failed to read schema version: " . $dbh->errstr; | ||
| 201 | } | ||
| 202 | |||
| 203 | return 0; | ||
| 204 | } | ||
| 205 | |||
| 206 | if ( @$result != 1 | ||
| 207 | || !defined $result->[0] | ||
| 208 | || $result->[0] !~ qr{^[1-9][0-9]*$} | ||
| 209 | || $result->[0] < 1 ) | ||
| 210 | { | ||
| 211 | croak 'Invalid schema version declaration'; | ||
| 212 | } | ||
| 213 | |||
| 214 | return $result->[0]; | ||
| 215 | } | ||
| 216 | |||
| 217 | =head2 assert_compatible_schema | ||
| 218 | |||
| 219 | Assert that the database has a schema version that is compatible with this version of | ||
| 220 | Zonemaster Backend. | ||
| 221 | |||
| 222 | Croaks if the database has an incompatible schema version, or if it is in an illegal state | ||
| 223 | with regard to schema version encoding. | ||
| 224 | |||
| 225 | =cut | ||
| 226 | |||
| 227 | sub assert_compatible_schema { | ||
| 228 | my ( $self ) = @_; | ||
| 229 | |||
| 230 | my $db_schema_version = $self->get_schema_version; | ||
| 231 | |||
| 232 | if ( $db_schema_version ne $REQUIRED_SCHEMA_VERSION ) { | ||
| 233 | croak "Expected schema version $REQUIRED_SCHEMA_VERSION, found $db_schema_version"; | ||
| 234 | } | ||
| 235 | |||
| 236 | return; | ||
| 237 | } | ||
| 238 | |||
| 239 | sub add_api_user { | ||
| 240 | my ( $self, $username, $api_key ) = @_; | ||
| 241 | |||
| 242 | die Zonemaster::Backend::Error::Internal->new( reason => "username or api_key not provided to the method add_api_user") | ||
| 243 | unless ( $username && $api_key ); | ||
| 244 | |||
| 245 | my $dbh = $self->dbh; | ||
| 246 | my $result; | ||
| 247 | |||
| 248 | try { | ||
| 249 | $result = $dbh->do( | ||
| 250 | "INSERT INTO users (username, api_key) VALUES (?,?)", | ||
| 251 | undef, | ||
| 252 | $username, | ||
| 253 | $api_key, | ||
| 254 | ); | ||
| 255 | } catch { | ||
| 256 | die Zonemaster::Backend::Error::Conflict->new( message => 'User already exists', data => { username => $username } ) | ||
| 257 | if ( $self->is_duplicate ); | ||
| 258 | }; | ||
| 259 | |||
| 260 | die Zonemaster::Backend::Error::Internal->new( reason => "add_api_user not successful") | ||
| 261 | unless ( $result ); | ||
| 262 | |||
| 263 | return $result; | ||
| 264 | } | ||
| 265 | |||
| 266 | sub create_new_test { | ||
| 267 | my ( $self, $domain, $test_params, $seconds_between_tests_with_same_params, $batch_id ) = @_; | ||
| 268 | |||
| 269 | my $dbh = $self->dbh; | ||
| 270 | |||
| 271 | $test_params->{domain} = _normalize_domain( $domain ); | ||
| 272 | |||
| 273 | my $fingerprint = $self->generate_fingerprint( $test_params ); | ||
| 274 | my $encoded_params = $self->encode_params( $test_params ); | ||
| 275 | my $undelegated = $self->undelegated ( $test_params ); | ||
| 276 | |||
| 277 | my $hash_id; | ||
| 278 | |||
| 279 | my $priority = $test_params->{priority}; | ||
| 280 | my $queue_label = $test_params->{queue}; | ||
| 281 | my $now = time(); | ||
| 282 | my $threshold = $now - $seconds_between_tests_with_same_params; | ||
| 283 | |||
| 284 | my $recent_hash_id = $self->recent_test_hash_id( $fingerprint, $threshold ); | ||
| 285 | |||
| 286 | if ( $recent_hash_id ) { | ||
| 287 | # A recent entry exists, so return its id | ||
| 288 | $hash_id = $recent_hash_id; | ||
| 289 | } | ||
| 290 | else { | ||
| 291 | $hash_id = substr(md5_hex($now.rand()), 0, 16); | ||
| 292 | $dbh->do( | ||
| 293 | q[ | ||
| 294 | INSERT INTO test_results ( | ||
| 295 | hash_id, | ||
| 296 | batch_id, | ||
| 297 | created_at, | ||
| 298 | priority, | ||
| 299 | queue, | ||
| 300 | fingerprint, | ||
| 301 | params, | ||
| 302 | domain, | ||
| 303 | undelegated | ||
| 304 | ) VALUES (?,?,?,?,?,?,?,?,?) | ||
| 305 | ], | ||
| 306 | undef, | ||
| 307 | $hash_id, | ||
| 308 | $batch_id, | ||
| 309 | $self->format_time( time() ), | ||
| 310 | $priority, | ||
| 311 | $queue_label, | ||
| 312 | $fingerprint, | ||
| 313 | $encoded_params, | ||
| 314 | encode_utf8( $test_params->{domain} ), | ||
| 315 | $undelegated, | ||
| 316 | ); | ||
| 317 | } | ||
| 318 | |||
| 319 | return $hash_id; | ||
| 320 | } | ||
| 321 | |||
| 322 | # Search for recent test result with the test same parameters, where | ||
| 323 | # "threshold" gives the oldest start time. | ||
| 324 | sub recent_test_hash_id { | ||
| 325 | my ( $self, $fingerprint, $threshold ) = @_; | ||
| 326 | |||
| 327 | my $dbh = $self->dbh; | ||
| 328 | my ( $recent_hash_id ) = $dbh->selectrow_array( | ||
| 329 | q[ | ||
| 330 | SELECT hash_id | ||
| 331 | FROM test_results | ||
| 332 | WHERE fingerprint = ? | ||
| 333 | AND batch_id IS NULL | ||
| 334 | AND ( started_at IS NULL | ||
| 335 | OR started_at >= ? ) | ||
| 336 | ], | ||
| 337 | undef, | ||
| 338 | $fingerprint, | ||
| 339 | $self->format_time( $threshold ), | ||
| 340 | ); | ||
| 341 | |||
| 342 | return $recent_hash_id; | ||
| 343 | } | ||
| 344 | |||
| 345 | =head2 test_progress( $test_id, $progress ) | ||
| 346 | |||
| 347 | Get/set the progress value of the test associated with C<$test_id>. | ||
| 348 | |||
| 349 | The given C<$progress> must be either C<undef> (when getting) or an number in | ||
| 350 | the range 0-100 inclusive (when setting). | ||
| 351 | |||
| 352 | If defined, C<$progress> is clamped to 1-99 inclusive. | ||
| 353 | |||
| 354 | Dies when: | ||
| 355 | |||
| 356 | =over 2 | ||
| 357 | |||
| 358 | =item | ||
| 359 | |||
| 360 | attempting to access a test that does not exist | ||
| 361 | |||
| 362 | =item | ||
| 363 | |||
| 364 | attempting to update a test that is in a state other than "running" | ||
| 365 | |||
| 366 | =item | ||
| 367 | |||
| 368 | attempting to set a progress value that is lower than the current one | ||
| 369 | |||
| 370 | =item | ||
| 371 | |||
| 372 | an error occurs in the database interface | ||
| 373 | |||
| 374 | =back | ||
| 375 | |||
| 376 | =cut | ||
| 377 | |||
| 378 | sub test_progress { | ||
| 379 | my ( $self, $test_id, $progress ) = @_; | ||
| 380 | |||
| 381 | if ( defined $progress ) { | ||
| 382 | if ( $progress < 0 || 100 < $progress ) { | ||
| 383 | die Zonemaster::Backend::Error::Internal->new( reason => "progress out of range" ); | ||
| 384 | } elsif ( $progress < 1 ) { | ||
| 385 | $progress = 1; | ||
| 386 | } elsif ( 99 < $progress ) { | ||
| 387 | $progress = 99; | ||
| 388 | } | ||
| 389 | |||
| 390 | my $rows_affected = $self->dbh->do( | ||
| 391 | q[ | ||
| 392 | UPDATE test_results | ||
| 393 | SET progress = ? | ||
| 394 | WHERE hash_id = ? | ||
| 395 | AND 1 <= progress | ||
| 396 | AND progress <= ? | ||
| 397 | ], | ||
| 398 | undef, | ||
| 399 | $progress, | ||
| 400 | $test_id, | ||
| 401 | $progress, | ||
| 402 | ); | ||
| 403 | if ( $rows_affected == 0 ) { | ||
| 404 | die Zonemaster::Backend::Error::Internal->new( reason => 'job not found or illegal update' ); | ||
| 405 | } | ||
| 406 | |||
| 407 | return $progress; | ||
| 408 | } | ||
| 409 | |||
| 410 | my ( $result ) = $self->dbh->selectrow_array( | ||
| 411 | q[ | ||
| 412 | SELECT progress | ||
| 413 | FROM test_results | ||
| 414 | WHERE hash_id = ? | ||
| 415 | ], | ||
| 416 | undef, | ||
| 417 | $test_id, | ||
| 418 | ); | ||
| 419 | if ( !defined $result ) { | ||
| 420 | die Zonemaster::Backend::Error::Internal->new( reason => 'job not found' ); | ||
| 421 | } | ||
| 422 | |||
| 423 | return $result; | ||
| 424 | } | ||
| 425 | |||
| 426 | sub test_state { | ||
| 427 | my ( $self, $test_id ) = @_; | ||
| 428 | |||
| 429 | my ( $progress ) = $self->dbh->selectrow_array( | ||
| 430 | q[ | ||
| 431 | SELECT progress | ||
| 432 | FROM test_results | ||
| 433 | WHERE hash_id = ? | ||
| 434 | ], | ||
| 435 | undef, | ||
| 436 | $test_id, | ||
| 437 | ); | ||
| 438 | if ( !defined $progress ) { | ||
| 439 | die Zonemaster::Backend::Error::Internal->new( reason => 'job not found' ); | ||
| 440 | } | ||
| 441 | |||
| 442 | if ( $progress == 0 ) { | ||
| 443 | return $TEST_WAITING; | ||
| 444 | } | ||
| 445 | elsif ( 0 < $progress && $progress < 100 ) { | ||
| 446 | return $TEST_RUNNING; | ||
| 447 | } | ||
| 448 | elsif ( $progress == 100 ) { | ||
| 449 | return $TEST_COMPLETED; | ||
| 450 | } | ||
| 451 | else { | ||
| 452 | die Zonemaster::Backend::Error::Internal->new( reason => 'state could not be determined' ); | ||
| 453 | } | ||
| 454 | } | ||
| 455 | |||
| 456 | sub set_test_completed { | ||
| 457 | my ( $self, $test_id ) = @_; | ||
| 458 | |||
| 459 | my $current_state = $self->test_state( $test_id ); | ||
| 460 | |||
| 461 | if ( $current_state ne $TEST_RUNNING ) { | ||
| 462 | die Zonemaster::Backend::Error::Internal->new( reason => 'illegal transition to COMPLETED' ); | ||
| 463 | } | ||
| 464 | |||
| 465 | my $rows_affected = $self->dbh->do( | ||
| 466 | q[ | ||
| 467 | UPDATE test_results | ||
| 468 | SET progress = 100, | ||
| 469 | ended_at = ? | ||
| 470 | WHERE hash_id = ? | ||
| 471 | AND 0 < progress | ||
| 472 | AND progress < 100 | ||
| 473 | ], | ||
| 474 | undef, | ||
| 475 | $self->format_time( time() ), | ||
| 476 | $test_id, | ||
| 477 | ); | ||
| 478 | |||
| 479 | if ( $rows_affected == 0 ) { | ||
| 480 | die Zonemaster::Backend::Error::Internal->new( reason => "job not found or illegal transition" ); | ||
| 481 | } | ||
| 482 | } | ||
| 483 | |||
| 484 | sub select_test_results { | ||
| 485 | my ( $self, $test_id ) = @_; | ||
| 486 | |||
| 487 | my ( $hrefs ) = $self->dbh->selectall_hashref( | ||
| 488 | q[ | ||
| 489 | SELECT | ||
| 490 | hash_id, | ||
| 491 | created_at, | ||
| 492 | params | ||
| 493 | FROM test_results | ||
| 494 | WHERE hash_id = ? | ||
| 495 | ], | ||
| 496 | 'hash_id', | ||
| 497 | undef, | ||
| 498 | $test_id | ||
| 499 | ); | ||
| 500 | |||
| 501 | my $result = $hrefs->{$test_id}; | ||
| 502 | |||
| 503 | die Zonemaster::Backend::Error::ResourceNotFound->new( message => "Test not found", data => { test_id => $test_id } ) | ||
| 504 | unless defined $result; | ||
| 505 | |||
| 506 | $result->{created_at} = $self->to_iso8601( $result->{created_at} ); | ||
| 507 | |||
| 508 | return $result; | ||
| 509 | } | ||
| 510 | |||
| 511 | # "$new_results" is JSON encoded | ||
| 512 | sub store_results { | ||
| 513 | my ( $self, $test_id, $new_results ) = @_; | ||
| 514 | |||
| 515 | my $rows_affected = $self->dbh->do( | ||
| 516 | q[ | ||
| 517 | UPDATE test_results | ||
| 518 | SET progress = 100, | ||
| 519 | ended_at = ?, | ||
| 520 | results = ? | ||
| 521 | WHERE hash_id = ? | ||
| 522 | AND 0 < progress | ||
| 523 | AND progress < 100 | ||
| 524 | ], | ||
| 525 | undef, | ||
| 526 | $self->format_time( time() ), | ||
| 527 | $new_results, | ||
| 528 | $test_id, | ||
| 529 | ); | ||
| 530 | |||
| 531 | if ( $rows_affected == 0 ) { | ||
| 532 | die Zonemaster::Backend::Error::Internal->new( reason => "job not found or illegal transition" ); | ||
| 533 | } | ||
| 534 | |||
| 535 | return; | ||
| 536 | } | ||
| 537 | |||
| 538 | sub test_results { | ||
| 539 | my ( $self, $test_id ) = @_; | ||
| 540 | |||
| 541 | my $result = $self->select_test_results( $test_id ); | ||
| 542 | |||
| 543 | my @result_entries = $self->dbh->selectall_array( | ||
| 544 | q[ | ||
| 545 | SELECT | ||
| 546 | l.level, | ||
| 547 | r.module, | ||
| 548 | r.testcase, | ||
| 549 | r.tag, | ||
| 550 | r.timestamp, | ||
| 551 | r.args | ||
| 552 | FROM result_entries r | ||
| 553 | INNER JOIN log_level l | ||
| 554 | ON r.level = l.value | ||
| 555 | WHERE hash_id = ? | ||
| 556 | ], | ||
| 557 | { Slice => {} }, | ||
| 558 | $test_id | ||
| 559 | ); | ||
| 560 | |||
| 561 | eval { | ||
| 562 | $result->{params} = decode_json( $result->{params} ); | ||
| 563 | |||
| 564 | @result_entries = map { | ||
| 565 | { | ||
| 566 | %$_, | ||
| 567 | args => decode_json( $_->{args} ), | ||
| 568 | } | ||
| 569 | } @result_entries; | ||
| 570 | |||
| 571 | $result->{results} = \@result_entries; | ||
| 572 | }; | ||
| 573 | |||
| 574 | die Zonemaster::Backend::Error::JsonError->new( reason => "$@", data => { test_id => $test_id } ) | ||
| 575 | if $@; | ||
| 576 | |||
| 577 | return $result; | ||
| 578 | } | ||
| 579 | |||
| 580 | sub get_test_history { | ||
| 581 | my ( $self, $p ) = @_; | ||
| 582 | |||
| 583 | my $dbh = $self->dbh; | ||
| 584 | |||
| 585 | my $undelegated = undef; | ||
| 586 | if ($p->{filter} eq "undelegated") { | ||
| 587 | $undelegated = 1; | ||
| 588 | } elsif ($p->{filter} eq "delegated") { | ||
| 589 | $undelegated = 0; | ||
| 590 | } | ||
| 591 | |||
| 592 | my @results; | ||
| 593 | my $query = q[ | ||
| 594 | SELECT | ||
| 595 | (SELECT count(*) FROM result_entries WHERE result_entries.hash_id = test_results.hash_id AND level = ?) AS nb_critical, | ||
| 596 | (SELECT count(*) FROM result_entries WHERE result_entries.hash_id = test_results.hash_id AND level = ?) AS nb_error, | ||
| 597 | (SELECT count(*) FROM result_entries WHERE result_entries.hash_id = test_results.hash_id AND level = ?) AS nb_warning, | ||
| 598 | id, | ||
| 599 | hash_id, | ||
| 600 | created_at, | ||
| 601 | undelegated | ||
| 602 | FROM test_results | ||
| 603 | WHERE progress = 100 AND domain = ? AND ( ? IS NULL OR undelegated = ? ) | ||
| 604 | ORDER BY created_at DESC | ||
| 605 | LIMIT ? | ||
| 606 | OFFSET ?]; | ||
| 607 | |||
| 608 | my $sth = $dbh->prepare( $query ); | ||
| 609 | |||
| 610 | my %levels = Zonemaster::Engine::Logger::Entry->levels(); | ||
| 611 | $sth->bind_param( 1, $levels{CRITICAL} ); | ||
| 612 | $sth->bind_param( 2, $levels{ERROR} ); | ||
| 613 | $sth->bind_param( 3, $levels{WARNING} ); | ||
| 614 | $sth->bind_param( 4, _normalize_domain( $p->{frontend_params}{domain} ) ); | ||
| 615 | $sth->bind_param( 5, $undelegated, SQL_INTEGER ); | ||
| 616 | $sth->bind_param( 6, $undelegated, SQL_INTEGER ); | ||
| 617 | $sth->bind_param( 7, $p->{limit} ); | ||
| 618 | $sth->bind_param( 8, $p->{offset} ); | ||
| 619 | |||
| 620 | $sth->execute(); | ||
| 621 | |||
| 622 | while ( my $h = $sth->fetchrow_hashref ) { | ||
| 623 | my $overall_result = 'ok'; | ||
| 624 | if ( $h->{nb_critical} ) { | ||
| 625 | $overall_result = 'critical'; | ||
| 626 | } | ||
| 627 | elsif ( $h->{nb_error} ) { | ||
| 628 | $overall_result = 'error'; | ||
| 629 | } | ||
| 630 | elsif ( $h->{nb_warning} ) { | ||
| 631 | $overall_result = 'warning'; | ||
| 632 | } | ||
| 633 | |||
| 634 | push( | ||
| 635 | @results, | ||
| 636 | { | ||
| 637 | id => $h->{hash_id}, | ||
| 638 | created_at => $self->to_iso8601( $h->{created_at} ), | ||
| 639 | undelegated => $h->{undelegated}, | ||
| 640 | overall_result => $overall_result, | ||
| 641 | } | ||
| 642 | ); | ||
| 643 | } | ||
| 644 | |||
| 645 | return \@results; | ||
| 646 | } | ||
| 647 | |||
| 648 | sub create_new_batch_job { | ||
| 649 | my ( $self, $username ) = @_; | ||
| 650 | |||
| 651 | my $dbh = $self->dbh; | ||
| 652 | $dbh->do( q[ INSERT INTO batch_jobs (username, created_at) VALUES (?,?) ], | ||
| 653 | undef, | ||
| 654 | $username, | ||
| 655 | $self->format_time( time() ), | ||
| 656 | ); | ||
| 657 | my $new_batch_id = $dbh->last_insert_id( undef, undef, "batch_jobs", undef ); | ||
| 658 | |||
| 659 | return $new_batch_id; | ||
| 660 | } | ||
| 661 | |||
| 662 | sub user_authorized { | ||
| 663 | my ( $self, $user, $api_key ) = @_; | ||
| 664 | |||
| 665 | my $dbh = $self->dbh; | ||
| 666 | my ( $id ) = $dbh->selectrow_array( | ||
| 667 | "SELECT id FROM users WHERE username = ? AND api_key = ?", | ||
| 668 | undef, | ||
| 669 | $user, | ||
| 670 | $api_key | ||
| 671 | ); | ||
| 672 | |||
| 673 | return $id; | ||
| 674 | } | ||
| 675 | |||
| 676 | sub batch_exists_in_db { | ||
| 677 | my ( $self, $batch_id ) = @_; | ||
| 678 | |||
| 679 | my $dbh = $self->dbh; | ||
| 680 | my ( $id ) = $dbh->selectrow_array( | ||
| 681 | q[ SELECT id FROM batch_jobs WHERE id = ? ], | ||
| 682 | undef, | ||
| 683 | $batch_id | ||
| 684 | ); | ||
| 685 | |||
| 686 | return $id; | ||
| 687 | } | ||
| 688 | |||
| 689 | =head2 get_test_request( $queue_label ) | ||
| 690 | |||
| 691 | Find a waiting test and claim it for processing. | ||
| 692 | |||
| 693 | If $queue_label is defined it must be an integer. | ||
| 694 | If defined, only tests in the associated queue are considered. | ||
| 695 | Otherwise tests from all queues are considered. | ||
| 696 | |||
| 697 | Returns the test id and the batch id of the claimed test. | ||
| 698 | If there are no waiting tests to claim, C<undef> is returned for both ids. | ||
| 699 | |||
| 700 | Only tests in the "waiting" state are considered. | ||
| 701 | When a test is claimed it is removed from the queue and it transitions to the | ||
| 702 | "running" state. | ||
| 703 | |||
| 704 | It is safe for multiple callers running in parallel to allocate tests from the | ||
| 705 | same queues. | ||
| 706 | |||
| 707 | Dies when an error occurs in the database interface. | ||
| 708 | |||
| 709 | =cut | ||
| 710 | |||
| 711 | sub get_test_request { | ||
| 712 | my ( $self, $queue_label ) = @_; | ||
| 713 | |||
| 714 | while ( 1 ) { | ||
| 715 | |||
| 716 | # Identify a candidate for allocation ... | ||
| 717 | my ( $hash_id, $batch_id ); | ||
| 718 | if ( defined $queue_label ) { | ||
| 719 | ( $hash_id, $batch_id ) = $self->dbh->selectrow_array( | ||
| 720 | q[ | ||
| 721 | SELECT hash_id, | ||
| 722 | batch_id | ||
| 723 | FROM test_results | ||
| 724 | WHERE progress = 0 | ||
| 725 | AND queue = ? | ||
| 726 | ORDER BY priority DESC, | ||
| 727 | id ASC | ||
| 728 | LIMIT 1 | ||
| 729 | ], | ||
| 730 | undef, | ||
| 731 | $queue_label, | ||
| 732 | ); | ||
| 733 | } | ||
| 734 | else { | ||
| 735 | ( $hash_id, $batch_id ) = $self->dbh->selectrow_array( | ||
| 736 | q[ | ||
| 737 | SELECT hash_id, | ||
| 738 | batch_id | ||
| 739 | FROM test_results | ||
| 740 | WHERE progress = 0 | ||
| 741 | ORDER BY priority DESC, | ||
| 742 | id ASC | ||
| 743 | LIMIT 1 | ||
| 744 | ], | ||
| 745 | ); | ||
| 746 | } | ||
| 747 | |||
| 748 | if ( defined $hash_id ) { | ||
| 749 | |||
| 750 | # ... and race to be the first to claim it ... | ||
| 751 | if ( $self->claim_test( $hash_id ) ) { | ||
| 752 | return ( $hash_id, $batch_id ); | ||
| 753 | } | ||
| 754 | } | ||
| 755 | else { | ||
| 756 | # ... or stop trying if there are no candidates. | ||
| 757 | return ( undef, undef ); | ||
| 758 | } | ||
| 759 | } | ||
| 760 | } | ||
| 761 | |||
| 762 | =head2 claim_test( $test_id ) | ||
| 763 | |||
| 764 | Claim a test for processing. | ||
| 765 | |||
| 766 | Transitions a test from the "waiting" state to the "running" state. | ||
| 767 | |||
| 768 | Returns true on successful transition. | ||
| 769 | Returns false if the given test does not exist or if it is not in the "waiting" | ||
| 770 | state. | ||
| 771 | |||
| 772 | Dies when an error occurs in the database interface. | ||
| 773 | |||
| 774 | =cut | ||
| 775 | |||
| 776 | sub claim_test { | ||
| 777 | my ( $self, $test_id ) = @_; | ||
| 778 | |||
| 779 | my $rows_affected = $self->dbh->do( | ||
| 780 | q[ | ||
| 781 | UPDATE test_results | ||
| 782 | SET progress = 1, | ||
| 783 | started_at = ? | ||
| 784 | WHERE hash_id = ? | ||
| 785 | AND progress = 0 | ||
| 786 | ], | ||
| 787 | undef, | ||
| 788 | $self->format_time( time() ), | ||
| 789 | $test_id, | ||
| 790 | ); | ||
| 791 | |||
| 792 | return $rows_affected == 1; | ||
| 793 | } | ||
| 794 | |||
| 795 | sub get_test_params { | ||
| 796 | my ( $self, $test_id ) = @_; | ||
| 797 | |||
| 798 | my $dbh = $self->dbh; | ||
| 799 | my ( $params_json ) = $dbh->selectrow_array( "SELECT params FROM test_results WHERE hash_id = ?", undef, $test_id ); | ||
| 800 | |||
| 801 | die Zonemaster::Backend::Error::ResourceNotFound->new( message => "Test not found", data => { test_id => $test_id } ) | ||
| 802 | unless defined $params_json; | ||
| 803 | |||
| 804 | my $result; | ||
| 805 | eval { | ||
| 806 | $result = decode_json( $params_json ); | ||
| 807 | }; | ||
| 808 | |||
| 809 | die Zonemaster::Backend::Error::JsonError->new( reason => "$@", data => { test_id => $test_id } ) | ||
| 810 | if $@; | ||
| 811 | |||
| 812 | return $result; | ||
| 813 | } | ||
| 814 | |||
| 815 | =head2 batch_status | ||
| 816 | |||
| 817 | Returns number of tests per category (finished, running, waiting) for the given | ||
| 818 | batch, provided as C<batch_id>. | ||
| 819 | |||
| 820 | If one or more of parameters C<list_running_tests>, C<list_finished_tests> or | ||
| 821 | C<list_waiting_tests> are included with true value, the C<hash_id> values for | ||
| 822 | that category is also included. | ||
| 823 | |||
| 824 | =cut | ||
| 825 | |||
| 826 | # Standard SQL, can be here | ||
| 827 | sub batch_status { | ||
| 828 | my ( $self, $test_params ) = @_; | ||
| 829 | |||
| 830 | my $batch_id = $test_params->{batch_id}; | ||
| 831 | |||
| 832 | die Zonemaster::Backend::Error::ResourceNotFound->new( message => "Unknown batch", data => { batch_id => $batch_id } ) | ||
| 833 | unless defined $self->batch_exists_in_db( $batch_id ); | ||
| 834 | |||
| 835 | my $dbh = $self->dbh; | ||
| 836 | |||
| 837 | my %result; | ||
| 838 | $result{waiting_count} = 0; | ||
| 839 | $result{running_count} = 0; | ||
| 840 | $result{finished_count} = 0; | ||
| 841 | |||
| 842 | my $query = " | ||
| 843 | SELECT hash_id, progress | ||
| 844 | FROM test_results | ||
| 845 | WHERE batch_id=?"; | ||
| 846 | |||
| 847 | my $sth1 = $dbh->prepare( $query ); | ||
| 848 | $sth1->execute( $batch_id ); | ||
| 849 | |||
| 850 | while ( my $h = $sth1->fetchrow_hashref ) { | ||
| 851 | if ( $h->{progress} eq '0' ) { | ||
| 852 | $result{waiting_count}++; | ||
| 853 | push(@{$result{waiting_tests}}, $h->{hash_id}) if $test_params->{list_waiting_tests}; | ||
| 854 | } | ||
| 855 | elsif ( $h->{progress} eq '100' ) { | ||
| 856 | $result{finished_count}++; | ||
| 857 | push(@{$result{finished_tests}}, $h->{hash_id}) if $test_params->{list_finished_tests}; | ||
| 858 | } | ||
| 859 | else { | ||
| 860 | $result{running_count}++; | ||
| 861 | push(@{$result{running_tests}}, $h->{hash_id}) if $test_params->{list_running_tests}; | ||
| 862 | } | ||
| 863 | } | ||
| 864 | |||
| 865 | return \%result; | ||
| 866 | } | ||
| 867 | |||
| 868 | |||
| 869 | =head2 process_unfinished_tests($queue_label, $test_run_timeout) | ||
| 870 | |||
| 871 | Append a new log entry C<BACKEND_TEST_AGENT:UNABLE_TO_FINISH_TEST> to all the | ||
| 872 | tests started more that $test_run_timeout seconds in the queue $queue_label. | ||
| 873 | Then store the results in database. | ||
| 874 | |||
| 875 | =cut | ||
| 876 | |||
| 877 | sub process_unfinished_tests { | ||
| 878 | my ( $self, $queue_label, $test_run_timeout ) = @_; | ||
| 879 | |||
| 880 | my $sth1 = $self->select_unfinished_tests( # | ||
| 881 | $queue_label, | ||
| 882 | $test_run_timeout, | ||
| 883 | ); | ||
| 884 | |||
| 885 | my $msg = Zonemaster::Engine::Logger::Entry->new( | ||
| 886 | { | ||
| 887 | level => "CRITICAL", | ||
| 888 | module => "Backend", | ||
| 889 | testcase => "", | ||
| 890 | tag => "UNABLE_TO_FINISH_TEST", | ||
| 891 | args => { max_execution_time => $test_run_timeout }, | ||
| 892 | timestamp => $test_run_timeout | ||
| 893 | } | ||
| 894 | ); | ||
| 895 | while ( my $h = $sth1->fetchrow_hashref ) { | ||
| 896 | $self->force_end_test($h->{hash_id}, $msg); | ||
| 897 | } | ||
| 898 | } | ||
| 899 | |||
| 900 | =head2 select_unfinished_tests($queue_label, $test_run_timeout) | ||
| 901 | |||
| 902 | Search for all tests started more than $test_run_timeout seconds in the queue | ||
| 903 | $queue_label. | ||
| 904 | |||
| 905 | =cut | ||
| 906 | |||
| 907 | sub select_unfinished_tests { | ||
| 908 | my ( $self, $queue_label, $test_run_timeout ) = @_; | ||
| 909 | |||
| 910 | if ( $queue_label ) { | ||
| 911 | my $sth = $self->dbh->prepare( " | ||
| 912 | SELECT hash_id, results | ||
| 913 | FROM test_results | ||
| 914 | WHERE started_at < ? | ||
| 915 | AND progress > 0 | ||
| 916 | AND progress < 100 | ||
| 917 | AND queue = ?" ); | ||
| 918 | $sth->execute( # | ||
| 919 | $self->format_time( time() - $test_run_timeout ), | ||
| 920 | $queue_label, | ||
| 921 | ); | ||
| 922 | return $sth; | ||
| 923 | } | ||
| 924 | else { | ||
| 925 | my $sth = $self->dbh->prepare( " | ||
| 926 | SELECT hash_id, results | ||
| 927 | FROM test_results | ||
| 928 | WHERE started_at < ? | ||
| 929 | AND progress > 0 | ||
| 930 | AND progress < 100" ); | ||
| 931 | $sth->execute( # | ||
| 932 | $self->format_time( time() - $test_run_timeout ), | ||
| 933 | ); | ||
| 934 | return $sth; | ||
| 935 | } | ||
| 936 | } | ||
| 937 | |||
| 938 | =head2 force_end_test($hash_id, $msg) | ||
| 939 | |||
| 940 | Store the L<Zonemaster::Engine::Logger::Entry> $msg log entry into the database | ||
| 941 | and mark test with $hash_id as COMPLETED. | ||
| 942 | |||
| 943 | =cut | ||
| 944 | |||
| 945 | sub force_end_test { | ||
| 946 | my ( $self, $hash_id, $msg ) = @_; | ||
| 947 | |||
| 948 | $self->add_result_entries( $hash_id, $msg ); | ||
| 949 | $self->set_test_completed( $hash_id ); | ||
| 950 | } | ||
| 951 | |||
| 952 | =head2 process_dead_test($hash_id) | ||
| 953 | |||
| 954 | Store a new log entry C<BACKEND_TEST_AGENT:TEST_DIED> in database for the test | ||
| 955 | with $hash_id. | ||
| 956 | |||
| 957 | =cut | ||
| 958 | |||
| 959 | sub process_dead_test { | ||
| 960 | my ( $self, $hash_id ) = @_; | ||
| 961 | my $msg = Zonemaster::Engine::Logger::Entry->new( | ||
| 962 | { | ||
| 963 | level => "CRITICAL", | ||
| 964 | module => "Backend", | ||
| 965 | testcase => "", | ||
| 966 | tag => "TEST_DIED", | ||
| 967 | args => {}, | ||
| 968 | timestamp => $self->get_relative_start_time($hash_id) | ||
| 969 | } | ||
| 970 | ); | ||
| 971 | $self->force_end_test($hash_id, $msg); | ||
| 972 | } | ||
| 973 | |||
| 974 | # Converts the domain to lowercase and if the domain is not the root ('.') | ||
| 975 | # removes any trailing dot | ||
| 976 | sub _normalize_domain { | ||
| 977 | my ( $domain ) = @_; | ||
| 978 | |||
| 979 | my ( $errors, $normalized_domain ) = normalize_name( trim_space( $domain ) ); | ||
| 980 | |||
| 981 | if ( scalar( @{$errors} ) ) { | ||
| 982 | die Zonemaster::Backend::Error::Internal->new( reason => "Normalizing domain returned errors.", data => [ map { $_->string } @{$errors} ] ); | ||
| 983 | } | ||
| 984 | |||
| 985 | return $normalized_domain; | ||
| 986 | } | ||
| 987 | |||
| 988 | sub _project_params { | ||
| 989 | my ( $self, $params ) = @_; | ||
| 990 | |||
| 991 | my %projection = (); | ||
| 992 | |||
| 993 | $projection{domain} = _normalize_domain( $$params{domain} // "" ); | ||
| 994 | $projection{ipv4} = $$params{ipv4}; | ||
| 995 | $projection{ipv6} = $$params{ipv6}; | ||
| 996 | $projection{profile} = lc( $$params{profile} // "default" ); | ||
| 997 | |||
| 998 | my $array_ds_info = $$params{ds_info} // []; | ||
| 999 | my @array_ds_info_sort = sort { | ||
| 1000 | $a->{algorithm} cmp $b->{algorithm} or | ||
| 1001 | $a->{digest} cmp $b->{digest} or | ||
| 1002 | $a->{digtype} <=> $b->{digtype} or | ||
| 1003 | $a->{keytag} <=> $b->{keytag} | ||
| 1004 | } @$array_ds_info; | ||
| 1005 | |||
| 1006 | $projection{ds_info} = \@array_ds_info_sort; | ||
| 1007 | |||
| 1008 | my $array_nameservers = $$params{nameservers} // []; | ||
| 1009 | for my $nameserver (@$array_nameservers) { | ||
| 1010 | if ( defined $$nameserver{ip} and $$nameserver{ip} eq "" ) { | ||
| 1011 | delete $$nameserver{ip}; | ||
| 1012 | } | ||
| 1013 | $$nameserver{ns} = _normalize_domain( $$nameserver{ns} ); | ||
| 1014 | } | ||
| 1015 | my @array_nameservers_sort = sort { | ||
| 1016 | $a->{ns} cmp $b->{ns} or | ||
| 1017 | ( defined $a->{ip} and defined $b->{ip} and $a->{ip} cmp $b->{ip} ) | ||
| 1018 | } @$array_nameservers; | ||
| 1019 | |||
| 1020 | $projection{nameservers} = \@array_nameservers_sort; | ||
| 1021 | |||
| 1022 | return \%projection; | ||
| 1023 | } | ||
| 1024 | |||
| 1025 | # Take a params object with text strings and return an UTF-8 binary string | ||
| 1026 | sub _params_to_json_str { | ||
| 1027 | my ( $self, $params ) = @_; | ||
| 1028 | |||
| 1029 | my $js = JSON::PP->new; | ||
| 1030 | $js->canonical( 1 ); | ||
| 1031 | $js->utf8( 1 ); | ||
| 1032 | |||
| 1033 | my $encoded_params = $js->encode( $params ); | ||
| 1034 | |||
| 1035 | return $encoded_params; | ||
| 1036 | } | ||
| 1037 | |||
| 1038 | =head2 encode_params | ||
| 1039 | |||
| 1040 | Encode the params object into a JSON string. First a projection of some | ||
| 1041 | parameters is performed then all additional properties are kept. | ||
| 1042 | Returns an UTF-8 binary string of the union of the given hash and its | ||
| 1043 | normalization using default values, see | ||
| 1044 | L<https://github.com/zonemaster/zonemaster/blob/master/docs/public/using/backend/rpcapi-reference.md#params-2> | ||
| 1045 | |||
| 1046 | =cut | ||
| 1047 | |||
| 1048 | sub encode_params { | ||
| 1049 | my ( $self, $params ) = @_; | ||
| 1050 | |||
| 1051 | my $projected_params = $self->_project_params( $params ); | ||
| 1052 | $params = { %$params, %$projected_params }; | ||
| 1053 | my $encoded_params = $self->_params_to_json_str( $params ); | ||
| 1054 | |||
| 1055 | return $encoded_params; | ||
| 1056 | } | ||
| 1057 | |||
| 1058 | =head2 generate_fingerprint | ||
| 1059 | |||
| 1060 | Returns a fingerprint (an UTF-8 binary string) of the hash passed in argument | ||
| 1061 | (which contain text string). | ||
| 1062 | The fingerprint is computed after projecting the hash. | ||
| 1063 | Such fingerprint are usefull to find similar tests in the database. | ||
| 1064 | |||
| 1065 | =cut | ||
| 1066 | |||
| 1067 | sub generate_fingerprint { | ||
| 1068 | my ( $self, $params ) = @_; | ||
| 1069 | |||
| 1070 | my $projected_params = $self->_project_params( $params ); | ||
| 1071 | my $encoded_params = $self->_params_to_json_str( $projected_params ); | ||
| 1072 | my $fingerprint = md5_hex( $encoded_params ); | ||
| 1073 | |||
| 1074 | return $fingerprint; | ||
| 1075 | } | ||
| 1076 | |||
| 1077 | |||
| 1078 | =head2 undelegated | ||
| 1079 | |||
| 1080 | Returns the value 1 if the test to be created is if type undelegated, | ||
| 1081 | else value 0. The test is considered to be undelegated if the "ds_info" or | ||
| 1082 | "nameservers" parameters is are defined with data after projection. | ||
| 1083 | |||
| 1084 | =cut | ||
| 1085 | |||
| 1086 | sub undelegated { | ||
| 1087 | my ( $self, $params ) = @_; | ||
| 1088 | |||
| 1089 | my $projected_params = $self->_project_params( $params ); | ||
| 1090 | |||
| 1091 | return 1 if defined( $$projected_params{ds_info}[0] ); | ||
| 1092 | return 1 if defined( $$projected_params{nameservers}[0] ); | ||
| 1093 | return 0; | ||
| 1094 | } | ||
| 1095 | |||
| 1096 | sub format_time { | ||
| 1097 | my ( $class, $time ) = @_; | ||
| 1098 | return strftime "%Y-%m-%d %H:%M:%S", gmtime( $time ); | ||
| 1099 | } | ||
| 1100 | |||
| 1101 | sub to_iso8601 { | ||
| 1102 | my ( $class, $time ) = @_; | ||
| 1103 | $time =~ s/^([^ ]+) (.*)$/$1T$2Z/; | ||
| 1104 | return $time; | ||
| 1105 | } | ||
| 1106 | |||
| 1107 | sub add_result_entries { | ||
| 1108 | my ( $self, $hash_id, @entries ) = @_; | ||
| 1109 | my @records; | ||
| 1110 | |||
| 1111 | my $json = JSON::PP->new->allow_blessed->convert_blessed->canonical; | ||
| 1112 | |||
| 1113 | my %levels = Zonemaster::Engine::Logger::Entry->levels(); | ||
| 1114 | |||
| 1115 | foreach my $e ( @entries ) { | ||
| 1116 | my $r = [ | ||
| 1117 | $hash_id, | ||
| 1118 | $levels{ $e->level }, | ||
| 1119 | $e->module, | ||
| 1120 | $e->testcase, | ||
| 1121 | $e->tag, | ||
| 1122 | $e->timestamp, | ||
| 1123 | $json->encode( $e->args // {} ), | ||
| 1124 | ]; | ||
| 1125 | |||
| 1126 | push @records, $r; | ||
| 1127 | } | ||
| 1128 | my $query_values = join ", ", ("(?, ?, ?, ?, ?, ?, ?)") x @records; | ||
| 1129 | my $query = "INSERT INTO result_entries (hash_id, level, module, testcase, tag, timestamp, args) VALUES $query_values"; | ||
| 1130 | my $sth = $self->dbh->prepare($query); | ||
| 1131 | $sth = $sth->execute(map { @$_ } @records); | ||
| 1132 | } | ||
| 1133 | |||
| 1134 | no Moose::Role; | ||
| 1135 | |||
| 1136 | 1; |