| 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::SQLite; | ||
| 9 | |||
| 10 | my $config = Zonemaster::Backend::Config->load_config(); | ||
| 11 | if ( $config->DB_engine ne 'SQLite' ) { | ||
| 12 | die "The configuration file does not contain the SQLite backend"; | ||
| 13 | } | ||
| 14 | my $db = Zonemaster::Backend::DB::SQLite->from_config( $config ); | ||
| 15 | my $dbh = $db->dbh; | ||
| 16 | |||
| 17 | |||
| 18 | sub patch_db { | ||
| 19 | |||
| 20 | # since we change the default value for a column, the whole table needs to | ||
| 21 | # be recreated | ||
| 22 | # 1. rename the "test_results" table to "test_results_old" | ||
| 23 | # 2. create the new "test_results" table | ||
| 24 | # 3. populate it with the values from "test_results_old" | ||
| 25 | # 4. remove old table and indexes | ||
| 26 | # 5. recreate the indexes | ||
| 27 | eval { | ||
| 28 | $dbh->do('ALTER TABLE test_results RENAME TO test_results_old'); | ||
| 29 | |||
| 30 | # create the table | ||
| 31 | $db->create_schema(); | ||
| 32 | |||
| 33 | # populate it | ||
| 34 | # - nb_retries is omitted as we remove this column | ||
| 35 | # - params_deterministic_hash is renamed to fingerprint | ||
| 36 | $dbh->do(' | ||
| 37 | INSERT INTO test_results | ||
| 38 | SELECT id, | ||
| 39 | hash_id, | ||
| 40 | domain, | ||
| 41 | batch_id, | ||
| 42 | creation_time, | ||
| 43 | test_start_time, | ||
| 44 | test_end_time, | ||
| 45 | priority, | ||
| 46 | queue, | ||
| 47 | progress, | ||
| 48 | params_deterministic_hash, | ||
| 49 | params, | ||
| 50 | results, | ||
| 51 | undelegated | ||
| 52 | FROM test_results_old | ||
| 53 | '); | ||
| 54 | |||
| 55 | $dbh->do('DROP TABLE test_results_old'); | ||
| 56 | |||
| 57 | # recreate indexes | ||
| 58 | $db->create_schema(); | ||
| 59 | }; | ||
| 60 | print( "Error while updating the 'test_results' table schema: " . $@ ) if ($@); | ||
| 61 | |||
| 62 | # Update the "undelegated" column | ||
| 63 | my $sth1 = $dbh->prepare('SELECT id, params from test_results', undef); | ||
| 64 | $sth1->execute; | ||
| 65 | while ( my $row = $sth1->fetchrow_hashref ) { | ||
| 66 | my $id = $row->{id}; | ||
| 67 | my $raw_params = decode_json($row->{params}); | ||
| 68 | my $ds_info_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{ds_info}}; | ||
| 69 | my $nameservers_values = scalar grep !/^$/, map { values %$_ } @{$raw_params->{nameservers}}; | ||
| 70 | my $undelegated = $ds_info_values > 0 || $nameservers_values > 0 || 0; | ||
| 71 | |||
| 72 | $dbh->do('UPDATE test_results SET undelegated = ? where id = ?', undef, $undelegated, $id); | ||
| 73 | } | ||
| 74 | |||
| 75 | |||
| 76 | # in order to properly drop a column, the whole table needs to be recreated | ||
| 77 | # 1. rename the "users" table to "users_old" | ||
| 78 | # 2. create the new "users" table | ||
| 79 | # 3. populate it with the values from "users_old" | ||
| 80 | # 4. remove old table | ||
| 81 | eval { | ||
| 82 | $dbh->do('ALTER TABLE users RENAME TO users_old'); | ||
| 83 | |||
| 84 | # create the table | ||
| 85 | $db->create_schema(); | ||
| 86 | |||
| 87 | # populate it | ||
| 88 | $dbh->do('INSERT INTO users SELECT id, username, api_key FROM users_old'); | ||
| 89 | |||
| 90 | $dbh->do('DROP TABLE users_old'); | ||
| 91 | }; | ||
| 92 | print( "Error while updating the 'users' table schema: " . $@ ) if ($@); | ||
| 93 | } | ||
| 94 | |||
| 95 | patch_db(); |