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