« auto/share/dist/Zonemaster-Backend/patch/patch_db_zonemaster_backend_ver_11.2.0.pl
↑ Summary
auto/share/dist/Zonemaster-Backend/patch/patch_mysql_db_zonemaster_backend_ver_1.0.3.pl »
| 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 | } |