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

filter: Statementstmt 0.0% 0 / 51 Branchbran 0.0% 0 / 6 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 / 72 CC 3 SCAR 16.7
CC
3
Cov
0%
patch_db
12.0
BEGIN
2.0
BEGIN
2.0
CRAP 5.3
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();