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

filter: Statementstmt 0.0% 0 / 87 Branchbran 0.0% 0 / 2 Conditioncond n/a MC/DCmcdc n/a Subroutinesub 0.0% 0 / 10 Podpod 0.0% 0 / 3 totaltotal 0.0% 0 / 102 CC 1 SCAR 6.9
CC
1
Cov
0%
patch_db_mysql
2.0
patch_db_postgresql
2.0
patch_db_sqlite
2.0
CRAP 2.0
1 use strict;
2 use warnings;
3  
4 use Try::Tiny;
5  
6 use Zonemaster::Backend::Config;
7  
8 my $config = Zonemaster::Backend::Config->load_config();
9  
10 my %patch = (
11 mysql => \&patch_db_mysql,
12 postgresql => \&patch_db_postgresql,
13 sqlite => \&patch_db_sqlite,
14 );
15  
16 my $db_engine = $config->DB_engine;
17  
18 if ( $db_engine =~ /^(MySQL|PostgreSQL|SQLite)$/ ) {
19 $patch{ lc $db_engine }();
20 }
21 else {
22 die "Unknown database engine configured: $db_engine\n";
23 }
24  
25 sub patch_db_mysql {
26 use Zonemaster::Backend::DB::MySQL;
27  
28 my $db = Zonemaster::Backend::DB::MySQL->from_config( $config );
29 my $dbh = $db->dbh;
30  
31 # add table constraints
32 $dbh->do( 'ALTER TABLE users ADD CONSTRAINT UNIQUE (username)' );
33 $dbh->do( 'ALTER TABLE test_results ADD CONSTRAINT UNIQUE (hash_id)' );
34  
35 # update columns names, data type and default value
36 $dbh->do( 'ALTER TABLE test_results MODIFY COLUMN id BIGINT AUTO_INCREMENT' );
37 $dbh->do( 'ALTER TABLE test_results CHANGE COLUMN creation_time created_at DATETIME NOT NULL' );
38 $dbh->do( 'ALTER TABLE test_results CHANGE COLUMN test_start_time started_at DATETIME DEFAULT NULL' );
39 $dbh->do( 'ALTER TABLE test_results CHANGE COLUMN test_end_time ended_at DATETIME DEFAULT NULL' );
40  
41 $dbh->do( 'ALTER TABLE batch_jobs CHANGE COLUMN creation_time created_at DATETIME NOT NULL' );
42  
43 $dbh->{AutoCommit} = 0;
44  
45 try {
46 # normalize "domain" column
47 $dbh->do(
48 q[
49 UPDATE test_results
50 SET domain = LOWER(domain)
51 WHERE CAST(domain AS BINARY) RLIKE '[A-Z]'
52 ]
53 );
54 $dbh->do(
55 q[
56 UPDATE test_results
57 SET domain = '.'
58 WHERE domain = '..' OR domain = '...' OR domain = '....'
59 ]
60 );
61 $dbh->do(
62 q[
63 UPDATE test_results
64 SET domain = TRIM( TRAILING '.' FROM domain )
65 WHERE domain != '.' AND domain LIKE '%.'
66 ]
67 );
68  
69 $dbh->commit();
70 } catch {
71 print( "Could not upgrade database: " . $_ );
72  
73 eval { $dbh->rollback() };
74 };
75 }
76  
77 sub patch_db_postgresql {
78 use Zonemaster::Backend::DB::PostgreSQL;
79  
80 my $db = Zonemaster::Backend::DB::PostgreSQL->from_config( $config );
81 my $dbh = $db->dbh;
82  
83 $dbh->{AutoCommit} = 0;
84  
85 try {
86 # update sequence data type to BIGINT
87 $dbh->do( 'ALTER SEQUENCE test_results_id_seq AS BIGINT' );
88 $dbh->do( 'ALTER TABLE test_results ALTER COLUMN id SET DATA TYPE BIGINT' );
89  
90 # remove default value for "creation_time"
91 $dbh->do( 'ALTER TABLE test_results ALTER COLUMN creation_time DROP DEFAULT' );
92 $dbh->do( 'ALTER TABLE batch_jobs ALTER COLUMN creation_time DROP DEFAULT' );
93  
94 # rename columns
95 $dbh->do( 'ALTER TABLE test_results RENAME COLUMN creation_time TO created_at' );
96 $dbh->do( 'ALTER TABLE test_results RENAME COLUMN test_start_time TO started_at' );
97 $dbh->do( 'ALTER TABLE test_results RENAME COLUMN test_end_time TO ended_at' );
98 $dbh->do( 'ALTER TABLE batch_jobs RENAME COLUMN creation_time TO created_at' );
99  
100 # add table constraints
101 $dbh->do( 'ALTER TABLE test_results ADD UNIQUE (hash_id)' );
102 $dbh->do( 'ALTER TABLE users ADD UNIQUE (username)' );
103  
104 # normalize "domain" column
105 $dbh->do(
106 q[
107 UPDATE test_results
108 SET domain = LOWER(domain)
109 WHERE domain != LOWER(domain)
110 ]
111 );
112 $dbh->do(
113 q[
114 UPDATE test_results
115 SET domain = '.'
116 WHERE domain = '..' OR domain = '...' OR domain = '....'
117 ]
118 );
119 $dbh->do(
120 q[
121 UPDATE test_results
122 SET domain = RTRIM(domain, '.')
123 WHERE domain != '.' AND domain LIKE '%.'
124 ]
125 );
126  
127 $dbh->commit();
128 } catch {
129 print( "Could not upgrade database: " . $_ );
130  
131 eval { $dbh->rollback() };
132 };
133 }
134  
135 sub patch_db_sqlite {
136 use Zonemaster::Backend::DB::SQLite;
137  
138 my $db = Zonemaster::Backend::DB::SQLite->from_config( $config );
139 my $dbh = $db->dbh;
140  
141 $dbh->{AutoCommit} = 0;
142  
143 # since we change the default value for a column, the whole table needs to
144 # be recreated
145 # 1. rename the table to "<table>_old"
146 # 2. recreate a clean table schema
147 # 3. populate it with the values from "<table>_old"
148 # 4. remove "<table>_old" and indexes
149 # 5. recreate the indexes
150 try {
151 $dbh->do('ALTER TABLE test_results RENAME TO test_results_old');
152 $dbh->do('ALTER TABLE batch_jobs RENAME TO batch_jobs_old');
153 $dbh->do('ALTER TABLE users RENAME TO users_old');
154  
155 # create the tables
156 $db->create_schema();
157  
158 # populate the tables
159 $dbh->do(
160 q[
161 INSERT INTO test_results
162 (
163 id,
164 hash_id,
165 domain,
166 batch_id,
167 created_at,
168 started_at,
169 ended_at,
170 priority,
171 queue,
172 progress,
173 fingerprint,
174 params,
175 results,
176 undelegated
177 )
178 SELECT
179 id,
180 hash_id,
181 lower(domain),
182 batch_id,
183 creation_time,
184 test_start_time,
185 test_end_time,
186 priority,
187 queue,
188 progress,
189 fingerprint,
190 params,
191 results,
192 undelegated
193 FROM test_results_old
194 ]
195 );
196 $dbh->do(
197 q[
198 UPDATE test_results
199 SET domain = '.'
200 WHERE domain = '..' OR domain = '...' OR domain = '....'
201 ]
202 );
203 $dbh->do(
204 q[
205 UPDATE test_results
206 SET domain = RTRIM(domain, '.')
207 WHERE domain != '.' AND domain LIKE '%.'
208 ]
209 );
210  
211 $dbh->do('
212 INSERT INTO batch_jobs
213 (
214 id,
215 username,
216 created_at
217 )
218 SELECT
219 id,
220 username,
221 creation_time
222 FROM batch_jobs_old
223 ');
224  
225 $dbh->do('
226 INSERT INTO users
227 (
228 id,
229 username,
230 api_key
231 )
232 SELECT
233 id,
234 username,
235 api_key
236 FROM users_old
237 ');
238  
239 # delete old tables
240 $dbh->do('DROP TABLE test_results_old');
241 $dbh->do('DROP TABLE batch_jobs_old');
242 $dbh->do('DROP TABLE users_old');
243  
244 # recreate indexes
245 $db->create_schema();
246  
247 $dbh->commit();
248 } catch {
249 print( "Error while upgrading database: " . $_ );
250  
251 eval { $dbh->rollback() };
252 };
253 }