| 1 | use strict; | ||
| 2 | use warnings; | ||
| 3 | |||
| 4 | use List::MoreUtils qw(zip_unflatten); | ||
| 5 | use JSON::PP; | ||
| 6 | use Try::Tiny; | ||
| 7 | use File::Temp qw(tempfile); | ||
| 8 | use Encode qw(find_encoding); | ||
| 9 | |||
| 10 | use Zonemaster::Backend::Config; | ||
| 11 | use Zonemaster::Engine; | ||
| 12 | |||
| 13 | my $config = Zonemaster::Backend::Config->load_config(); | ||
| 14 | |||
| 15 | my %module_mapping; | ||
| 16 | for my $module ( Zonemaster::Engine->modules ) { | ||
| 17 | $module_mapping{lc $module} = $module; | ||
| 18 | } | ||
| 19 | |||
| 20 | my %patch = ( | ||
| 21 | mysql => \&patch_db_mysql, | ||
| 22 | postgresql => \&patch_db_postgresql, | ||
| 23 | sqlite => \&patch_db_sqlite, | ||
| 24 | ); | ||
| 25 | |||
| 26 | my $db_engine = $config->DB_engine; | ||
| 27 | print "Configured database engine: $db_engine\n"; | ||
| 28 | |||
| 29 | if ( $db_engine =~ /^(MySQL|PostgreSQL|SQLite)$/ ) { | ||
| 30 | print( "Starting database migration\n" ); | ||
| 31 | $patch{ lc $db_engine }(); | ||
| 32 | print( "\nMigration done\n" ); | ||
| 33 | } | ||
| 34 | else { | ||
| 35 | die "Unknown database engine configured: $db_engine\n"; | ||
| 36 | } | ||
| 37 | |||
| 38 | # depending on the resources available to select all data in database | ||
| 39 | # update $row_count to your needs | ||
| 40 | sub _update_data_result_entries { | ||
| 41 | my ( $dbh, $row_count ) = @_; | ||
| 42 | |||
| 43 | my $json = JSON::PP->new->allow_blessed->convert_blessed->canonical; | ||
| 44 | |||
| 45 | # update only jobs with results | ||
| 46 | my ( $row_total ) = $dbh->selectrow_array( 'SELECT count(*) FROM test_results WHERE results IS NOT NULL' ); | ||
| 47 | print "Will update $row_total rows\n"; | ||
| 48 | |||
| 49 | my %levels = Zonemaster::Engine::Logger::Entry->levels(); | ||
| 50 | |||
| 51 | my $row_done = 0; | ||
| 52 | while ( $row_done < $row_total ) { | ||
| 53 | print "Progress update: $row_done / $row_total\n"; | ||
| 54 | my $row_updated = 0; | ||
| 55 | my $sth1 = $dbh->prepare( 'SELECT hash_id, results FROM test_results WHERE results IS NOT NULL ORDER BY id ASC LIMIT ? OFFSET ?' ); | ||
| 56 | $sth1->execute( $row_count, $row_done ); | ||
| 57 | while ( my $row = $sth1->fetchrow_arrayref ) { | ||
| 58 | my ( $hash_id, $results ) = @$row; | ||
| 59 | |||
| 60 | next unless $results; | ||
| 61 | |||
| 62 | my @records; | ||
| 63 | my $entries = $json->decode( $results ); | ||
| 64 | |||
| 65 | foreach my $m ( @$entries ) { | ||
| 66 | my $module = $module_mapping{ lc $m->{module} } // ucfirst lc $m->{module}; | ||
| 67 | my $testcase = | ||
| 68 | ( !defined $m->{testcase} or $m->{testcase} eq 'UNSPECIFIED' ) | ||
| 69 | ? 'Unspecified' | ||
| 70 | : $m->{testcase} =~ s/[a-z_]*/$module/ir; | ||
| 71 | |||
| 72 | if ($testcase eq 'Delegation01' and $m->{tag} =~ /^(NOT_)?ENOUGH_IPV[46]_NS_(CHILD|DEL)$/) { | ||
| 73 | my @ips = split( /;/, delete $m->{args}{ns_ip_list} ); | ||
| 74 | my @names = split( /;/, delete $m->{args}{nsname_list} ); | ||
| 75 | my @ns_list = map { join( '/', @$_ ) } zip_unflatten(@names, @ips); | ||
| 76 | $m->{args}{ns_list} = join( ';', @ns_list ); | ||
| 77 | } | ||
| 78 | |||
| 79 | my $r = [ | ||
| 80 | $hash_id, | ||
| 81 | $levels{ $m->{level} }, | ||
| 82 | $module, | ||
| 83 | $testcase, | ||
| 84 | $m->{tag}, | ||
| 85 | $m->{timestamp}, | ||
| 86 | $json->encode( $m->{args} // {} ), | ||
| 87 | ]; | ||
| 88 | |||
| 89 | push @records, $r; | ||
| 90 | } | ||
| 91 | |||
| 92 | my $query_values = join ", ", ("(?, ?, ?, ?, ?, ?, ?)") x @records; | ||
| 93 | my $query = "INSERT INTO result_entries (hash_id, level, module, testcase, tag, timestamp, args) VALUES $query_values"; | ||
| 94 | my $sth = $dbh->prepare( $query ); | ||
| 95 | $sth = $sth->execute( map { @$_ } @records ); | ||
| 96 | |||
| 97 | $row_updated += $dbh->do( "UPDATE test_results SET results = NULL WHERE hash_id = ?", undef, $hash_id ); | ||
| 98 | } | ||
| 99 | |||
| 100 | # increase by min(row_updated, row_count) | ||
| 101 | $row_done += ( $row_updated < $row_count ) ? $row_updated : $row_count; | ||
| 102 | } | ||
| 103 | print "Progress update: $row_done / $row_total\n"; | ||
| 104 | } | ||
| 105 | |||
| 106 | sub _update_data_normalize_domains { | ||
| 107 | my ( $db ) = @_; | ||
| 108 | |||
| 109 | my ( $row_total ) = $db->dbh->selectrow_array( 'SELECT count(*) FROM test_results' ); | ||
| 110 | print "Will update $row_total rows\n"; | ||
| 111 | |||
| 112 | |||
| 113 | my $sth1 = $db->dbh->prepare( 'SELECT hash_id, params FROM test_results' ); | ||
| 114 | $sth1->execute; | ||
| 115 | |||
| 116 | my $row_done = 0; | ||
| 117 | my $progress = 0; | ||
| 118 | |||
| 119 | while ( my $row = $sth1->fetchrow_hashref ) { | ||
| 120 | my $hash_id = $row->{hash_id}; | ||
| 121 | eval { | ||
| 122 | my $raw_params = decode_json($row->{params}); | ||
| 123 | my $domain = $raw_params->{domain}; | ||
| 124 | |||
| 125 | # This has never been cleaned | ||
| 126 | delete $raw_params->{user_ip}; | ||
| 127 | |||
| 128 | my $params = $db->encode_params( $raw_params ); | ||
| 129 | my $fingerprint = $db->generate_fingerprint( $raw_params ); | ||
| 130 | |||
| 131 | $domain = Zonemaster::Backend::DB::_normalize_domain( $domain ); | ||
| 132 | |||
| 133 | $db->dbh->do('UPDATE test_results SET domain = ?, params = ?, fingerprint = ? where hash_id = ?', undef, $domain, $params, $fingerprint, $hash_id); | ||
| 134 | }; | ||
| 135 | if ($@) { | ||
| 136 | warn "Caught error while updating record with hash id $hash_id, ignoring: $@\n"; | ||
| 137 | } | ||
| 138 | $row_done += 1; | ||
| 139 | my $new_progress = int(($row_done / $row_total) * 100); | ||
| 140 | if ( $new_progress != $progress ) { | ||
| 141 | $progress = $new_progress; | ||
| 142 | print("$progress%\n"); | ||
| 143 | } | ||
| 144 | } | ||
| 145 | } | ||
| 146 | |||
| 147 | sub patch_db_mysql { | ||
| 148 | use Zonemaster::Backend::DB::MySQL; | ||
| 149 | |||
| 150 | my $db = Zonemaster::Backend::DB::MySQL->from_config( $config ); | ||
| 151 | my $dbh = $db->dbh; | ||
| 152 | |||
| 153 | $dbh->{AutoCommit} = 0; | ||
| 154 | |||
| 155 | try { | ||
| 156 | $db->create_schema(); | ||
| 157 | |||
| 158 | print( "\n-> (1/2) Populating new result_entries table\n" ); | ||
| 159 | _update_data_result_entries( $dbh, 50000 ); | ||
| 160 | |||
| 161 | print( "\n-> (2/2) Normalizing domain names\n" ); | ||
| 162 | _update_data_normalize_domains( $db ); | ||
| 163 | |||
| 164 | $dbh->commit(); | ||
| 165 | } catch { | ||
| 166 | print( "\nCould not upgrade database: " . $_ ); | ||
| 167 | |||
| 168 | $dbh->rollback(); | ||
| 169 | }; | ||
| 170 | } | ||
| 171 | |||
| 172 | sub _patch_db_postgresql_step1 { | ||
| 173 | my ($dbh, $chunk_size) = @_; | ||
| 174 | $chunk_size //= 100_000; | ||
| 175 | |||
| 176 | # This is used later for backslash-escaping data supplied to COPY … FROM | ||
| 177 | # STDIN commands. | ||
| 178 | my %conv = ( 8 => '\b', 9 => '\t', 10 => '\n', 11 => '\v', 12 => '\f', 13 => '\r', 92 => '\\\\' ); | ||
| 179 | |||
| 180 | my $utf8 = find_encoding('utf8'); | ||
| 181 | |||
| 182 | # Why a cursor instead of a plain SELECT statement? Because DBD::Pg does | ||
| 183 | # not use server-side cursors itself when reading the result of a SELECT | ||
| 184 | # query. | ||
| 185 | # | ||
| 186 | # And why is that a problem? That’s because the DBMS will try to compute | ||
| 187 | # the entire result set before handing it to the client. With large | ||
| 188 | # Zonemaster setups with years of history and millions of tests, this | ||
| 189 | # SELECT statement will generate hundreds of millions of rows. So without | ||
| 190 | # the appropriate precautions, a plain SELECT query like this one will | ||
| 191 | # definitely take out the machine it is running on! | ||
| 192 | print("Starting up\n"); | ||
| 193 | $dbh->do(q[ | ||
| 194 | DECLARE curs NO SCROLL CURSOR FOR | ||
| 195 | SELECT | ||
| 196 | test_results.hash_id, | ||
| 197 | log_level.value AS level, | ||
| 198 | CASE res.module | ||
| 199 | WHEN 'DNSSEC' THEN res.module | ||
| 200 | ELSE initcap(res.module) | ||
| 201 | END AS module, | ||
| 202 | CASE | ||
| 203 | WHEN res.testcase IS NULL THEN '' | ||
| 204 | WHEN res.testcase LIKE 'DNSSEC%' THEN res.testcase | ||
| 205 | ELSE initcap(res.testcase) | ||
| 206 | END AS testcase, | ||
| 207 | res.tag AS tag, | ||
| 208 | res.timestamp AS timestamp, | ||
| 209 | COALESCE(migrated_args.args, '{}') AS args | ||
| 210 | FROM test_results, | ||
| 211 | json_to_recordset(results) | ||
| 212 | AS res(module TEXT, testcase TEXT, tag TEXT, level TEXT, timestamp REAL, args JSONB) | ||
| 213 | LEFT JOIN log_level ON (res.level = log_level.level) | ||
| 214 | LEFT JOIN LATERAL ( | ||
| 215 | SELECT CASE WHEN res.testcase = 'DELEGATION01' | ||
| 216 | AND res.tag ~ '^(NOT_)?ENOUGH_IPV[46]_NS_(CHILD|DEL)$' | ||
| 217 | AND (NOT res.args ? 'ns_list') | ||
| 218 | THEN ( | ||
| 219 | SELECT res.args | ||
| 220 | - ARRAY['ns_ip_list', 'nsname_list'] | ||
| 221 | || jsonb_build_object('ns_list', string_agg(name || '/' || ip, ';')) | ||
| 222 | FROM unnest( | ||
| 223 | string_to_array(res.args->>'ns_ip_list', ';'), | ||
| 224 | string_to_array(res.args->>'nsname_list', ';')) | ||
| 225 | AS unnest(ip, name)) | ||
| 226 | ELSE res.args | ||
| 227 | END) AS migrated_args(args) ON TRUE]); | ||
| 228 | |||
| 229 | # I’ve tried to avoid hardcoding numbers but FETCH statements somehow | ||
| 230 | # don’t like being parameterized with placeholders. This will have to do. | ||
| 231 | my $read_sth = $dbh->prepare(sprintf(q[FETCH FORWARD %d FROM curs], $chunk_size)); | ||
| 232 | my $row_inserted = 0; | ||
| 233 | |||
| 234 | while ($read_sth->execute(), (my $row_count = $read_sth->rows()) > 0) { | ||
| 235 | my @copydata = (); | ||
| 236 | |||
| 237 | print("Progress update: ${row_inserted} rows inserted\n"); | ||
| 238 | $row_inserted += $row_count; | ||
| 239 | |||
| 240 | $dbh->do(q[COPY result_entries FROM STDIN]); | ||
| 241 | while (my $row = $read_sth->fetchrow_arrayref) { | ||
| 242 | my @columns = map { | ||
| 243 | if (defined $_) { | ||
| 244 | # Replaces invalid UTF-8 sequences with U+FFFD and escapes | ||
| 245 | # characters as required by PostgreSQL’s text COPY data | ||
| 246 | # format. | ||
| 247 | $utf8->encode($utf8->decode($_) =~ s/[\x08-\x0D\\]/$conv{ord $&}/aegr); | ||
| 248 | } else { | ||
| 249 | '\N'; | ||
| 250 | } | ||
| 251 | } @$row; | ||
| 252 | my $line = join("\t", @columns) . "\n"; | ||
| 253 | push @copydata, $line; | ||
| 254 | $dbh->pg_putcopydata( $line ); | ||
| 255 | } | ||
| 256 | |||
| 257 | try { | ||
| 258 | $dbh->pg_putcopyend(); | ||
| 259 | } | ||
| 260 | catch { | ||
| 261 | print("An error occurred while trying to copy some data.\n"); | ||
| 262 | my ($fh, $filename) = tempfile(); | ||
| 263 | print $fh @copydata; | ||
| 264 | close $fh; | ||
| 265 | print("The data supplied to COPY causing the failure has been ", | ||
| 266 | "stored in $filename for inspection\n"); | ||
| 267 | die $_; | ||
| 268 | } | ||
| 269 | } | ||
| 270 | $dbh->do(q[CLOSE curs]); | ||
| 271 | print("Done inserting ${row_inserted} rows\n"); | ||
| 272 | } | ||
| 273 | |||
| 274 | sub patch_db_postgresql { | ||
| 275 | use Zonemaster::Backend::DB::PostgreSQL; | ||
| 276 | |||
| 277 | my $db = Zonemaster::Backend::DB::PostgreSQL->from_config( $config ); | ||
| 278 | my $dbh = $db->dbh; | ||
| 279 | |||
| 280 | $dbh->{AutoCommit} = 0; | ||
| 281 | |||
| 282 | try { | ||
| 283 | $db->create_schema(); | ||
| 284 | |||
| 285 | # Make sure the planner knows that log_level is a small table | ||
| 286 | # so it can optimize step 1 appropriately | ||
| 287 | $dbh->do(q[ANALYZE log_level]); | ||
| 288 | |||
| 289 | print( "\n-> (1/2) Populating new result_entries table\n" ); | ||
| 290 | _patch_db_postgresql_step1( $dbh ); | ||
| 291 | |||
| 292 | $dbh->do( | ||
| 293 | 'UPDATE test_results SET results = NULL WHERE results IS NOT NULL' | ||
| 294 | ); | ||
| 295 | |||
| 296 | print( "\n-> (2/2) Normalizing domain names\n" ); | ||
| 297 | _update_data_normalize_domains( $db ); | ||
| 298 | |||
| 299 | $dbh->commit(); | ||
| 300 | } catch { | ||
| 301 | print( "\nCould not upgrade database: " . $_ ); | ||
| 302 | |||
| 303 | $dbh->rollback(); | ||
| 304 | }; | ||
| 305 | } | ||
| 306 | |||
| 307 | sub patch_db_sqlite { | ||
| 308 | use Zonemaster::Backend::DB::SQLite; | ||
| 309 | |||
| 310 | my $db = Zonemaster::Backend::DB::SQLite->from_config( $config ); | ||
| 311 | my $dbh = $db->dbh; | ||
| 312 | |||
| 313 | $dbh->{AutoCommit} = 0; | ||
| 314 | |||
| 315 | try { | ||
| 316 | $db->create_schema(); | ||
| 317 | |||
| 318 | print( "\n-> (1/2) Populating new result_entries table\n" ); | ||
| 319 | _update_data_result_entries( $dbh, 142 ); | ||
| 320 | |||
| 321 | print( "\n-> (2/2) Normalizing domain names\n" ); | ||
| 322 | _update_data_normalize_domains( $db ); | ||
| 323 | |||
| 324 | $dbh->commit(); | ||
| 325 | } catch { | ||
| 326 | print( "\nError while upgrading database: " . $_ ); | ||
| 327 | |||
| 328 | $dbh->rollback(); | ||
| 329 | }; | ||
| 330 | } |