| 1 | use strict; | ||
| 2 | use warnings; | ||
| 3 | use JSON::PP; | ||
| 4 | use Encode; | ||
| 5 | |||
| 6 | use DBI qw(:utils); | ||
| 7 | |||
| 8 | use Zonemaster::Backend::Config; | ||
| 9 | use Zonemaster::Backend::DB::PostgreSQL; | ||
| 10 | |||
| 11 | my $config = Zonemaster::Backend::Config->load_config(); | ||
| 12 | if ( $config->DB_engine ne 'PostgreSQL' ) { | ||
| 13 | die "The configuration file does not contain the PostgreSQL backend"; | ||
| 14 | } | ||
| 15 | my $db = Zonemaster::Backend::DB::PostgreSQL->from_config( $config ); | ||
| 16 | my $dbh = $db->dbh; | ||
| 17 | |||
| 18 | |||
| 19 | sub patch_db { | ||
| 20 | # Drop default value for the "hash_id" field | ||
| 21 | $dbh->do( 'ALTER TABLE test_results ALTER COLUMN hash_id DROP DEFAULT' ); | ||
| 22 | |||
| 23 | # Rename column "params_deterministic_hash" into "fingerprint" | ||
| 24 | eval { | ||
| 25 | $dbh->do( 'ALTER TABLE test_results RENAME COLUMN params_deterministic_hash TO fingerprint' ); | ||
| 26 | }; | ||
| 27 | print( "Error while changing DB schema: " . $@ ) if ($@); | ||
| 28 | |||
| 29 | # Update index | ||
| 30 | eval { | ||
| 31 | $dbh->do( "DROP INDEX IF EXISTS test_results__params_deterministic_hash" ); | ||
| 32 | $dbh->do( "CREATE INDEX test_results__fingerprint ON test_results (fingerprint)" ); | ||
| 33 | }; | ||
| 34 | print( "Error while updating the index: " . $@ ) if ($@); | ||
| 35 | |||
| 36 | # test_start_time and test_end_time default to NULL | ||
| 37 | eval { | ||
| 38 | $dbh->do('ALTER TABLE test_results ALTER COLUMN test_start_time SET DEFAULT NULL'); | ||
| 39 | $dbh->do('ALTER TABLE test_results ALTER COLUMN test_end_time SET DEFAULT NULL'); | ||
| 40 | }; | ||
| 41 | print( "Error while changing DB schema: " . $@ ) if ($@); | ||
| 42 | |||
| 43 | |||
| 44 | # Add missing "domain" and "undelegated" columns | ||
| 45 | eval { | ||
| 46 | $dbh->do( "ALTER TABLE test_results ADD COLUMN domain VARCHAR(255) NOT NULL DEFAULT ''" ); | ||
| 47 | $dbh->do( 'ALTER TABLE test_results ADD COLUMN undelegated integer NOT NULL DEFAULT 0' ); | ||
| 48 | }; | ||
| 49 | print( "Error while changing DB schema: " . $@ ) if ($@); | ||
| 50 | |||
| 51 | # Update index | ||
| 52 | eval { | ||
| 53 | $dbh->do( "DROP INDEX IF EXISTS test_results__domain_undelegated" ); | ||
| 54 | $dbh->do( "CREATE INDEX test_results__domain_undelegated ON test_results (domain, undelegated)" ); | ||
| 55 | }; | ||
| 56 | print( "Error while updating the index: " . $@ ) if ($@); | ||
| 57 | |||
| 58 | # New index | ||
| 59 | eval { | ||
| 60 | $dbh->do( 'CREATE INDEX IF NOT EXISTS test_results__progress_priority_id ON test_results (progress, priority DESC, id) WHERE (progress = 0)' ); | ||
| 61 | }; | ||
| 62 | print( "Error while creating the index: " . $@ ) if ($@); | ||
| 63 | |||
| 64 | # Update the "domain" column | ||
| 65 | $dbh->do( "UPDATE test_results SET domain = (params->>'domain')" ); | ||
| 66 | # remove default value to "domain" column | ||
| 67 | $dbh->do( "ALTER TABLE test_results ALTER COLUMN domain DROP DEFAULT" ); | ||
| 68 | |||
| 69 | # Update the "undelegated" column | ||
| 70 | my $sth1 = $dbh->prepare('SELECT id, params from test_results', undef); | ||
| 71 | $sth1->execute; | ||
| 72 | while ( my $row = $sth1->fetchrow_hashref ) { | ||
| 73 | my $id = $row->{id}; | ||
| 74 | my $raw_params; | ||
| 75 | |||
| 76 | if (utf8::is_utf8($row->{params}) ) { | ||
| 77 | $raw_params = decode_json( encode_utf8 ( $row->{params} ) ); | ||
| 78 | } else { | ||
| 79 | $raw_params = decode_json( $row->{params} ); | ||
| 80 | } | ||
| 81 | |||
| 82 | my $ds_info_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{ds_info}}; | ||
| 83 | my $nameservers_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{nameservers}}; | ||
| 84 | my $undelegated = $ds_info_values > 0 || $nameservers_values > 0 || 0; | ||
| 85 | |||
| 86 | $dbh->do('UPDATE test_results SET undelegated = ? where id = ?', undef, $undelegated, $id); | ||
| 87 | } | ||
| 88 | |||
| 89 | # add "username" and "api_key" columns to the "users" table | ||
| 90 | eval { | ||
| 91 | $dbh->do( 'ALTER TABLE users ADD COLUMN username VARCHAR(128)' ); | ||
| 92 | $dbh->do( 'ALTER TABLE users ADD COLUMN api_key VARCHAR(512)' ); | ||
| 93 | }; | ||
| 94 | print( "Error while changing DB schema: " . $@ ) if ($@); | ||
| 95 | |||
| 96 | # update the columns | ||
| 97 | eval { | ||
| 98 | $dbh->do( "UPDATE users SET username = (user_info->>'username'), api_key = (user_info->>'api_key')" ); | ||
| 99 | }; | ||
| 100 | print( "Error while updating the users table: " . $@ ) if ($@); | ||
| 101 | |||
| 102 | # remove the "user_info" column from the "users" table | ||
| 103 | $dbh->do( "ALTER TABLE users DROP COLUMN IF EXISTS user_info" ); | ||
| 104 | |||
| 105 | # remove the "nb_retries" column from the "test_results" table | ||
| 106 | $dbh->do( "ALTER TABLE test_results DROP COLUMN IF EXISTS nb_retries" ); | ||
| 107 | } | ||
| 108 | |||
| 109 | patch_db(); |