| 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(); |