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