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    /**
346     * Returns a select statement to fetch Lookup columns in the current schema
347     *
348     * @param int $sid Id of the schema
349     * @return string SQL statement
350     */
351    protected function getLookupColsSql($sid)
352    {
353        return "SELECT C.colref AS COL, T.class AS TYPE
354                FROM schema_cols AS C
355                LEFT OUTER JOIN types AS T
356                    ON C.tid = T.id
357                WHERE C.sid = $sid
358                AND TYPE LIKE '%Lookup'
359            ";
360    }
361
362    /**
363     * Checks for improperly migrated values and returns an array with
364     * "<column> = <fixed-value>" fragments to be used in the UPDATE statement.
365     *
366     * @param array $row
367     * @return array
368     */
369    protected function getFixedValues($row)
370    {
371        $pid = $row['pid'];
372        $rid = $row['rid'];
373        $rev = $row['rev'];
374        $colref = $row['colref'];
375        $rowno = $row['row'];
376        $fixes = [];
377        $matches = [];
378
379        // check if anything needs to be fixed in data columns
380        foreach ($row as $col => $value) {
381            if (in_array($col, ['pid', 'rid', 'rev', 'colref', 'row'])) {
382                continue;
383            }
384            preg_match('/^\["",(?<pid>.*?\D+.*?)\]$/', $value, $matches);
385            if (!empty($matches['pid'])) {
386                $fixes[$col] = '["' . $matches['pid'] . '",0]';
387            }
388        }
389
390        if (!empty($fixes)) {
391            $fixes = array_map(function ($set, $key) {
392                return "$key = '$set'";
393            }, $fixes, array_keys($fixes));
394        }
395
396        return [$pid, $rid, $rev, $colref, $rowno, $fixes];
397    }
398}
399