1<?php 2 3/** 4 * DokuWiki Plugin struct (Action Component) 5 * 6 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html 7 * @author Andreas Gohr, Michael Große <dokuwiki@cosmocode.de> 8 */ 9 10/** 11 * Class action_plugin_struct_migration 12 * 13 * Handle migrations that need more than just SQL 14 */ 15class action_plugin_struct_migration extends DokuWiki_Action_Plugin 16{ 17 /** 18 * @inheritDoc 19 */ 20 public function register(Doku_Event_Handler $controller) 21 { 22 $controller->register_hook('PLUGIN_SQLITE_DATABASE_UPGRADE', 'BEFORE', $this, 'handleMigrations'); 23 } 24 25 /** 26 * Call our custom migrations when defined 27 * 28 * @param Doku_Event $event 29 * @param $param 30 */ 31 public function handleMigrations(Doku_Event $event, $param) 32 { 33 if ($event->data['sqlite']->getAdapter()->getDbname() !== 'struct') { 34 return; 35 } 36 $to = $event->data['to']; 37 38 if (is_callable(array($this, "migration$to"))) { 39 $event->preventDefault(); 40 $event->result = call_user_func(array($this, "migration$to"), $event->data['sqlite']); 41 } 42 } 43 44 /** 45 * Executes Migration 12 46 * 47 * Add a latest column to all existing multi tables 48 * 49 * @param helper_plugin_sqlite $sqlite 50 * @return bool 51 */ 52 protected function migration12(helper_plugin_sqlite $sqlite) 53 { 54 /** @noinspection SqlResolve */ 55 $sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE 'multi_%'"; 56 $res = $sqlite->query($sql); 57 $tables = $sqlite->res2arr($res); 58 $sqlite->res_close($res); 59 60 foreach ($tables as $row) { 61 $sql = 'ALTER TABLE ? ADD COLUMN latest INT DEFAULT 1'; 62 $sqlite->query($sql, $row['name']); 63 } 64 65 return true; 66 } 67 68 /** 69 * Executes Migration 16 70 * 71 * Unifies previous page and lookup schema types 72 * 73 * @param helper_plugin_sqlite $sqlite 74 * @return bool 75 */ 76 protected function migration16(helper_plugin_sqlite $sqlite) 77 { 78 // get tables and their SQL definitions 79 $sql = "SELECT sql, name FROM sqlite_master 80 WHERE type = 'table' 81 AND (name LIKE 'data_%' OR name LIKE 'multi_%')"; 82 $res = $sqlite->query($sql); 83 $tables = $sqlite->res2arr($res); 84 $sqlite->res_close($res); 85 86 // get latest versions of schemas with islookup property 87 $sql = "SELECT MAX(id) AS id, tbl, islookup FROM schemas 88 GROUP BY tbl 89 "; 90 $res = $sqlite->query($sql); 91 $schemas = $sqlite->res2arr($res); 92 93 $sqlite->query('BEGIN TRANSACTION'); 94 $ok = true; 95 96 // Step 1: move original data to temporary tables and create new ones with modified schemas 97 foreach ($tables as $table) { 98 $name = $table['name']; 99 $sql = $table['sql']; 100 101 // move original data to temp_* 102 $ok = $ok && $sqlite->query("ALTER TABLE $name RENAME TO temp_$name"); 103 104 // update pid definitions 105 $sql = preg_replace('/pid (\w* ?NOT NULL|\w* ?PRIMARY KEY)/', 'pid TEXT DEFAULT ""', $sql); 106 107 // add rid and new primary key to regular tables 108 $cnt = 0; 109 $sql = preg_replace('/(PRIMARY KEY ?\([^\)]+?)(\))/', ' rid INTEGER, $1, rid $2', $sql, -1, $cnt); 110 // add rid and new primary key to lookup tables 111 if (!$cnt) { 112 $sql = str_replace(')', ', rid INTEGER, PRIMARY KEY(pid,rid) )', $sql); 113 } 114 115 // create the new table 116 $ok = $ok && $sqlite->query($sql); 117 if (!$ok) return false; 118 } 119 120 // Step 2: transfer data back from original tables (temp_*) 121 foreach ($schemas as $schema) { 122 $name = $schema['tbl']; 123 $sid = $schema['id']; 124 $isLookup = $schema['islookup']; 125 126 if (!$isLookup) { 127 $s = sprintf('INSERT INTO data_%s SELECT *, 0 FROM temp_data_%s', $name, $name); 128 $ok = $ok && $sqlite->query($s); 129 if (!$ok) return false; 130 131 $s = sprintf('INSERT INTO multi_%s SELECT *, 0 FROM temp_multi_%s', $name, $name); 132 $ok = $ok && $sqlite->query($s); 133 if (!$ok) return false; 134 } else { 135 // transfer pid to rid 136 $s = sprintf('INSERT INTO data_%s SELECT *, pid FROM temp_data_%s', $name, $name); 137 $ok = $ok && $sqlite->query($s); 138 if (!$ok) return false; 139 140 $s = sprintf('INSERT INTO multi_%s SELECT *, pid FROM temp_multi_%s', $name, $name); 141 $ok = $ok && $sqlite->query($s); 142 if (!$ok) return false; 143 144 // all lookup data has empty pids at this point 145 $s = "UPDATE data_$name SET pid = ''"; 146 $ok = $ok && $sqlite->query($s); 147 if (!$ok) return false; 148 149 $s = "UPDATE multi_$name SET pid = ''"; 150 $ok = $ok && $sqlite->query($s); 151 if (!$ok) return false; 152 } 153 154 // introduce composite ids in lookup columns 155 $s = $this->getLookupColsSql($sid); 156 $res = $sqlite->query($s); 157 $cols = $sqlite->res2arr($res); 158 159 if ($cols) { 160 foreach ($cols as $col) { 161 $colno = $col['COL']; 162 $colname = "col$colno"; 163 // lookup fields pointing to pages have to be migrated first! 164 // they rely on a simplistic not-a-number check, and already migrated lookups pass the test! 165 $f = 'UPDATE data_%s 166 SET %s = \'["\'||%s||\'",0]\' 167 WHERE %s != \'\' 168 AND CAST(%s AS DECIMAL) != %s'; 169 $s = sprintf($f, $name, $colname, $colname, $colname, $colname, $colname); 170 $ok = $ok && $sqlite->query($s); 171 if (!$ok) return false; 172 // multi_ 173 $f = 'UPDATE multi_%s 174 SET value = \'["\'||value||\'",0]\' 175 WHERE colref = %s 176 AND CAST(value AS DECIMAL) != value'; 177 $s = sprintf($f, $name, $colno); 178 $ok = $ok && $sqlite->query($s); 179 if (!$ok) return false; 180 181 // simple lookup fields 182 $s = "UPDATE data_$name 183 SET col$colno = '[" . '""' . ",'||col$colno||']' 184 WHERE col$colno != '' 185 AND CAST(col$colno AS DECIMAL) = col$colno"; 186 $ok = $ok && $sqlite->query($s); 187 if (!$ok) return false; 188 // multi_ 189 $s = "UPDATE multi_$name 190 SET value = '[" . '""' . ",'||value||']' 191 WHERE colref=$colno 192 AND CAST(value AS DECIMAL) = value"; 193 $ok = $ok && $sqlite->query($s); 194 if (!$ok) return false; 195 } 196 } 197 } 198 199 // Step 3: delete temp_* tables 200 foreach ($tables as $table) { 201 $name = $table['name']; 202 $s = "DROP TABLE temp_$name"; 203 $ok = $ok && $sqlite->query($s); 204 if (!$ok) return false; 205 } 206 207 // Step 4: remove islookup in schemas table 208 $sql = "SELECT sql FROM sqlite_master 209 WHERE type = 'table' 210 AND name = 'schemas'"; 211 $res = $sqlite->query($sql); 212 $t = $sqlite->res2arr($res); 213 $sql = $t[0]['sql']; 214 $sql = str_replace('islookup INTEGER,', '', $sql); 215 216 $s = 'ALTER TABLE schemas RENAME TO temp_schemas'; 217 $ok = $ok && $sqlite->query($s); 218 if (!$ok) return false; 219 220 // create a new table without islookup 221 $ok = $ok && $sqlite->query($sql); 222 if (!$ok) return false; 223 224 $s = 'INSERT INTO schemas SELECT id, tbl, ts, user, comment, config FROM temp_schemas'; 225 $ok = $ok && $sqlite->query($s); 226 227 if (!$ok) { 228 $sqlite->query('ROLLBACK TRANSACTION'); 229 return false; 230 } 231 $sqlite->query('COMMIT TRANSACTION'); 232 return true; 233 } 234 235 /** 236 * Executes Migration 17 237 * 238 * Fixes lookup data not correctly migrated by #16 239 * All lookups were presumed to reference lookup data, not pages, so the migrated value 240 * was always ["", <previous-pid-aka-new-rid>]. For page references it is ["<previous-pid>", 0] 241 * 242 * @param helper_plugin_sqlite $sqlite 243 * @return bool 244 */ 245 protected function migration17(helper_plugin_sqlite $sqlite) 246 { 247 $sql = "SELECT MAX(id) AS id, tbl FROM schemas 248 GROUP BY tbl 249 "; 250 $res = $sqlite->query($sql); 251 $schemas = $sqlite->res2arr($res); 252 253 $sqlite->query('BEGIN TRANSACTION'); 254 $ok = true; 255 256 foreach ($schemas as $schema) { 257 // find lookup columns 258 $name = $schema['tbl']; 259 $sid = $schema['id']; 260 $s = $this->getLookupColsSql($sid); 261 $res = $sqlite->query($s); 262 $cols = $sqlite->res2arr($res); 263 264 if ($cols) { 265 $colnames = array_map(function ($c) { 266 return 'col' . $c['COL']; 267 }, $cols); 268 269 // data_ tables 270 $s = 'SELECT pid, rid, rev, ' . implode(', ', $colnames) . " FROM data_$name"; 271 $res = $sqlite->query($s); 272 $allValues = $sqlite->res2arr($res); 273 274 if (!empty($allValues)) { 275 foreach ($allValues as $row) { 276 list($pid, $rid, $rev, $colref, $rowno, $fixes) = $this->getFixedValues($row); 277 // now fix the values 278 if (!empty($fixes)) { 279 $sql = "UPDATE data_$name 280 SET " . implode(', ', $fixes) . " 281 WHERE pid = ? 282 AND rid = ? 283 AND rev = ?"; 284 $params = [$pid, $rid, $rev]; 285 $ok = $ok && $sqlite->query($sql, $params); 286 } 287 } 288 } 289 290 // multi_ tables 291 $s = "SELECT colref, pid, rid, rev, row, value FROM multi_$name"; 292 $res = $sqlite->query($s); 293 $allValues = $sqlite->res2arr($res); 294 295 if (!empty($allValues)) { 296 foreach ($allValues as $row) { 297 list($pid, $rid, $rev, $colref, $rowno, $fixes) = $this->getFixedValues($row); 298 // now fix the values 299 if (!empty($fixes)) { 300 $sql = "UPDATE multi_$name 301 SET " . implode(', ', $fixes) . " 302 WHERE pid = ? 303 AND rid = ? 304 AND rev = ? 305 AND colref = ? 306 AND row = ?"; 307 $params = [$pid, $rid, $rev, $colref, $rowno]; 308 $ok = $ok && $sqlite->query($sql, $params); 309 } 310 } 311 } 312 } 313 } 314 315 if (!$ok) { 316 $sqlite->query('ROLLBACK TRANSACTION'); 317 return false; 318 } 319 $sqlite->query('COMMIT TRANSACTION'); 320 return true; 321 } 322 323 /** 324 * Removes a temp table left over by migration 16 325 * 326 * @param helper_plugin_sqlite $sqlite 327 * @return bool 328 */ 329 protected function migration18(helper_plugin_sqlite $sqlite) 330 { 331 $ok = true; 332 $sqlite->query('BEGIN TRANSACTION'); 333 334 $sql = 'DROP TABLE IF EXISTS temp_schemas'; 335 $ok = $ok && $sqlite->query($sql); 336 337 if (!$ok) { 338 $sqlite->query('ROLLBACK TRANSACTION'); 339 return false; 340 } 341 $sqlite->query('COMMIT TRANSACTION'); 342 return true; 343 } 344 /** 345 * Executes Migration 19 346 * 347 * Add "published" column to all existing tables 348 * 349 * @param helper_plugin_sqlite $sqlite 350 * @return bool 351 */ 352 protected function migration19(helper_plugin_sqlite $sqlite) 353 { 354 $ok = true; 355 $sqlite->query('BEGIN TRANSACTION'); 356 357 /** @noinspection SqlResolve */ 358 $sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND (name LIKE 'data_%' OR name LIKE 'multi_%')"; 359 $res = $sqlite->query($sql); 360 $tables = $sqlite->res2arr($res); 361 $sqlite->res_close($res); 362 363 foreach ($tables as $row) { 364 $sql = 'ALTER TABLE ? ADD COLUMN published INT DEFAULT NULL'; 365 $ok = $ok && $sqlite->query($sql, $row['name']); 366 } 367 if (!$ok) { 368 $sqlite->query('ROLLBACK TRANSACTION'); 369 return false; 370 } 371 $sqlite->query('COMMIT TRANSACTION'); 372 return true; 373 } 374 375 376 /** 377 * Returns a select statement to fetch Lookup columns in the current schema 378 * 379 * @param int $sid Id of the schema 380 * @return string SQL statement 381 */ 382 protected function getLookupColsSql($sid) 383 { 384 return "SELECT C.colref AS COL, T.class AS TYPE 385 FROM schema_cols AS C 386 LEFT OUTER JOIN types AS T 387 ON C.tid = T.id 388 WHERE C.sid = $sid 389 AND TYPE LIKE '%Lookup' 390 "; 391 } 392 393 /** 394 * Checks for improperly migrated values and returns an array with 395 * "<column> = <fixed-value>" fragments to be used in the UPDATE statement. 396 * 397 * @param array $row 398 * @return array 399 */ 400 protected function getFixedValues($row) 401 { 402 $pid = $row['pid']; 403 $rid = $row['rid']; 404 $rev = $row['rev']; 405 $colref = $row['colref']; 406 $rowno = $row['row']; 407 $fixes = []; 408 $matches = []; 409 410 // check if anything needs to be fixed in data columns 411 foreach ($row as $col => $value) { 412 if (in_array($col, ['pid', 'rid', 'rev', 'colref', 'row'])) { 413 continue; 414 } 415 preg_match('/^\["",(?<pid>.*?\D+.*?)\]$/', $value, $matches); 416 if (!empty($matches['pid'])) { 417 $fixes[$col] = '["' . $matches['pid'] . '",0]'; 418 } 419 } 420 421 if (!empty($fixes)) { 422 $fixes = array_map(function ($set, $key) { 423 return "$key = '$set'"; 424 }, $fixes, array_keys($fixes)); 425 } 426 427 return [$pid, $rid, $rev, $colref, $rowno, $fixes]; 428 } 429} 430