auto/share/dist/Zonemaster-Backend/patch/patch_mysql_db_zonemaster_backend_ver_8.0.0.pl untested

filter: Statementstmt 0.0% 0 / 57 Branchbran 0.0% 0 / 14 Conditioncond 0.0% 0 / 5 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 0.0% 0 / 7 Podpod 0.0% 0 / 1 totaltotal 0.0% 0 / 86 CC 7 SCAR 34.4
CC
7
Cov
0%
patch_db
56.0
BEGIN
2.0
BEGIN
2.0
CRAP 31.1
1 use strict;
2 use warnings;
3 use JSON::PP;
4  
5 use DBI qw(:utils);
6  
7 use Zonemaster::Backend::Config;
8 use Zonemaster::Backend::DB::MySQL;
9  
10 my $config = Zonemaster::Backend::Config->load_config();
11 if ( $config->DB_engine ne 'MySQL' ) {
12 die "The configuration file does not contain the MySQL backend";
13 }
14 my $db = Zonemaster::Backend::DB::MySQL->from_config( $config );
15 my $dbh = $db->dbh;
16  
17  
18 sub patch_db {
19 # Remove the trigger
20 $dbh->do( 'DROP TRIGGER IF EXISTS before_insert_test_results' );
21  
22 # Set the "hash_id" field to NOT NULL
23 eval {
24 $dbh->do( 'ALTER TABLE test_results MODIFY COLUMN hash_id VARCHAR(16) NOT NULL' );
25 };
26 print( "Error while changing DB schema: " . $@ ) if ($@);
27  
28 # Rename column "params_deterministic_hash" into "fingerprint"
29 # Since MariaDB 10.5.2 (2020-03-26) <https://mariadb.com/kb/en/mariadb-1052-release-notes/>
30 # ALTER TABLE t1 RENAME COLUMN old_col TO new_col;
31 # Before that we need to use CHANGE COLUMN <https://mariadb.com/kb/en/alter-table/#change-column>
32 eval {
33 $dbh->do('ALTER TABLE test_results CHANGE COLUMN params_deterministic_hash fingerprint CHARACTER VARYING(32)');
34 };
35 print( "Error while changing DB schema: " . $@ ) if ($@);
36  
37 # Update index
38 eval {
39 # retrieve all indexes by key name
40 my $indexes = $dbh->selectall_hashref( 'SHOW INDEXES FROM test_results', 'Key_name' );
41 if ( exists($indexes->{test_results__params_deterministic_hash}) ) {
42 $dbh->do( "DROP INDEX test_results__params_deterministic_hash ON test_results" );
43 }
44 $dbh->do( "CREATE INDEX test_results__fingerprint ON test_results (fingerprint)" );
45 };
46 print( "Error while updating the index: " . $@ ) if ($@);
47  
48 # Update the "undelegated" column
49 my $sth1 = $dbh->prepare('SELECT id, params from test_results', undef);
50 $sth1->execute;
51 while ( my $row = $sth1->fetchrow_hashref ) {
52 my $id = $row->{id};
53 my $raw_params = decode_json($row->{params});
54 my $ds_info_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{ds_info}};
55 my $nameservers_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{nameservers}};
56 my $undelegated = $ds_info_values > 0 || $nameservers_values > 0 || 0;
57  
58 $dbh->do('UPDATE test_results SET undelegated = ? where id = ?', undef, $undelegated, $id);
59 }
60  
61  
62 # remove the "user_info" column from the "users" table
63 # the IF EXISTS clause is available with MariaDB but not MySQL
64 eval {
65 $dbh->do( "ALTER TABLE users DROP COLUMN user_info" );
66 };
67 print( "Error while dropping the column: " . $@ ) if ($@);
68  
69 # remove the "nb_retries" column from the "test_results" table
70 eval {
71 $dbh->do( "ALTER TABLE test_results DROP COLUMN nb_retries" );
72 };
73 print( "Error while dropping the column: " . $@ ) if ($@);
74 }
75  
76 patch_db();