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 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(static fn($c) => 'col' . $c['COL'], $cols);
255
256                // data_ tables
257                $s = 'SELECT pid, rid, rev, ' . implode(', ', $colnames) . " FROM data_$name";
258                $allValues = $sqlite->queryAll($s);
259
260                if (!empty($allValues)) {
261                    foreach ($allValues as $row) {
262                        [$pid, $rid, $rev, $colref, $rowno, $fixes] = $this->getFixedValues($row);
263                        // now fix the values
264                        if (!empty($fixes)) {
265                            $sql = "UPDATE data_$name
266                                       SET " . implode(', ', $fixes) . "
267                                     WHERE pid = ?
268                                       AND rid = ?
269                                       AND rev = ?";
270                            $params = [$pid, $rid, $rev];
271                            $ok = $ok && $sqlite->query($sql, $params);
272                        }
273                    }
274                }
275
276                // multi_ tables
277                $s = "SELECT colref, pid, rid, rev, row, value FROM multi_$name";
278                $allValues = $sqlite->queryAll($s);
279
280                if (!empty($allValues)) {
281                    foreach ($allValues as $row) {
282                        [$pid, $rid, $rev, $colref, $rowno, $fixes] = $this->getFixedValues($row);
283                        // now fix the values
284                        if (!empty($fixes)) {
285                            $sql = "UPDATE multi_$name
286                                       SET " . implode(', ', $fixes) . "
287                                     WHERE pid = ?
288                                       AND rid = ?
289                                       AND rev = ?
290                                       AND colref = ?
291                                       AND row = ?";
292                            $params = [$pid, $rid, $rev, $colref, $rowno];
293                            $ok = $ok && $sqlite->query($sql, $params);
294                        }
295                    }
296                }
297            }
298        }
299
300        return $ok;
301    }
302
303    /**
304     * Removes a temp table left over by migration 16
305     *
306     * @param SQLiteDB $sqlite
307     * @return bool
308     */
309    protected function migration18($sqlite)
310    {
311        $ok = true;
312
313        $sql = 'DROP TABLE IF EXISTS temp_schemas';
314        $ok = $ok && $sqlite->query($sql);
315
316        return $ok;
317    }
318
319    /**
320     * Executes Migration 19
321     *
322     * Add "published" column to all existing tables
323     *
324     * @param SQLiteDB $sqlite
325     * @return bool
326     */
327    protected function migration19($sqlite)
328    {
329        $ok = true;
330
331        /** @noinspection SqlResolve */
332        $sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND (name LIKE 'data_%' OR name LIKE 'multi_%')";
333        $tables = $sqlite->queryAll($sql);
334
335        foreach ($tables as $row) {
336            $table = $row['name']; // no escaping needed, it's our own tables
337            $sql = "ALTER TABLE $table ADD COLUMN published INT DEFAULT NULL";
338            $ok = $ok && $sqlite->query($sql);
339        }
340        return $ok;
341    }
342
343    /**
344     * Executes Migration 20
345     *
346     * Adds indexes on "latest" and "published".
347     * Those fields are not part of (autoindexed) primary key, but are used in many queries.
348     *
349     * @param SQLiteDB $sqlite
350     * @return bool
351     */
352    protected function migration20($sqlite)
353    {
354        $ok = true;
355
356        /** @noinspection SqlResolve */
357        $sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND (name LIKE 'data_%' OR name LIKE 'multi_%')";
358        $tables = $sqlite->queryAll($sql);
359
360        foreach ($tables as $row) {
361            $table = $row['name']; // no escaping needed, it's our own tables
362            $sql = "CREATE INDEX idx_$table" . "_latest ON $table(latest);";
363            $ok = $ok && $sqlite->query($sql);
364            $sql = "CREATE INDEX idx_$table" . "_published ON $table(published);";
365            $ok = $ok && $sqlite->query($sql);
366        }
367        return $ok;
368    }
369
370
371    /**
372     * Returns a select statement to fetch Lookup columns in the current schema
373     *
374     * @param int $sid Id of the schema
375     * @return string SQL statement
376     */
377    protected function getLookupColsSql($sid)
378    {
379        return "SELECT C.colref AS COL, T.class AS TYPE
380                FROM schema_cols AS C
381                LEFT OUTER JOIN types AS T
382                    ON C.tid = T.id
383                WHERE C.sid = $sid
384                AND TYPE LIKE '%Lookup'
385            ";
386    }
387
388    /**
389     * Checks for improperly migrated values and returns an array with
390     * "<column> = <fixed-value>" fragments to be used in the UPDATE statement.
391     *
392     * @param array $row
393     * @return array
394     */
395    protected function getFixedValues($row)
396    {
397        $pid = $row['pid'];
398        $rid = $row['rid'];
399        $rev = $row['rev'];
400        $colref = $row['colref'];
401        $rowno = $row['row'];
402        $fixes = [];
403        $matches = [];
404
405        // check if anything needs to be fixed in data columns
406        foreach ($row as $col => $value) {
407            if (in_array($col, ['pid', 'rid', 'rev', 'colref', 'row'])) {
408                continue;
409            }
410            preg_match('/^\["",(?<pid>.*?\D+.*?)\]$/', $value, $matches);
411            if (!empty($matches['pid'])) {
412                $fixes[$col] = '["' . $matches['pid'] . '",0]';
413            }
414        }
415
416        if (!empty($fixes)) {
417            $fixes = array_map(static fn($set, $key) => "$key = '$set'", $fixes, array_keys($fixes));
418        }
419
420        return [$pid, $rid, $rev, $colref, $rowno, $fixes];
421    }
422}
423