auto/share/dist/Zonemaster-Backend/patch/patch_db_zonemaster_backend_ver_11.1.0.pl untested

filter: Statementstmt 0.0% 0 / 182 Branchbran 0.0% 0 / 16 Conditioncond 0.0% 0 / 13 MC/DCmcdc 0.0% 0 / 8 Subroutinesub 0.0% 0 / 18 Podpod 0.0% 0 / 6 totaltotal 0.0% 0 / 243 CC 8 SCAR 22.1
CC
8
Cov
0%
_update_data_result_entries
30.0
_update_data_normalize_domains
12.0
_patch_db_postgresql_step1
6.0
CRAP 9.1
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 }