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

filter: Statementstmt 0.0% 0 / 78 Branchbran 0.0% 0 / 20 Conditioncond 0.0% 0 / 5 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 0.0% 0 / 8 Podpod 0.0% 0 / 1 totaltotal 0.0% 0 / 114 CC 10 SCAR 41.8
CC
10
Cov
0%
patch_db
110.0
BEGIN
2.0
BEGIN
2.0
CRAP 65.5
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();