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