xref: /plugin/struct/action/migration.php (revision 544dca1b89f6be64f219a93762df0888844ac819) 
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                    // simple lookup fields
163                    $s = "UPDATE data_$name SET col$colno = '[" . '""' . ",'||col$colno||']' WHERE col$colno != '' AND CAST(col$colno AS DECIMAL) = col$colno";
164                    $ok = $ok && $sqlite->query($s);
165                    if (!$ok) return false;
166                    // multi_
167                    $s = "UPDATE multi_$name SET value = '[" . '""' . ",'||value||']' WHERE colref=$colno AND CAST(value AS DECIMAL) = value";
168                    $ok = $ok && $sqlite->query($s);
169                    if (!$ok) return false;
170
171                    // lookup fields pointing to pages
172                    $colname = "col$colno";
173                    $f = 'UPDATE data_%s SET %s = \'["\'||%s||\'",0]\' WHERE %s != \'\' AND CAST(%s AS DECIMAL) != %s';
174                    $s = sprintf($f, $name, $colname, $colname, $colname, $colname, $colname);
175                    $ok = $ok && $sqlite->query($s);
176                    if (!$ok) return false;
177                    // multi_
178                    $f = 'UPDATE multi_%s SET value = \'["value",0]\' WHERE colref = %s AND CAST(value AS DECIMAL) != value';
179                    $s = sprintf($f, $name, $colno);
180                    $ok = $ok && $sqlite->query($s);
181                    if (!$ok) return false;
182                }
183            }
184        }
185
186        // Step 3: delete temp_* tables
187        foreach ($tables as $table) {
188            $name = $table['name'];
189            $s = "DROP TABLE temp_$name";
190            $ok = $ok && $sqlite->query($s);
191            if (!$ok) return false;
192        }
193
194        // Step 4: remove islookup in schemas table
195        $sql = "SELECT sql FROM sqlite_master
196                WHERE type = 'table'
197                AND name = 'schemas'";
198        $res = $sqlite->query($sql);
199        $t = $sqlite->res2arr($res);
200        $sql = $t[0]['sql'];
201        $sql = str_replace('islookup INTEGER,', '', $sql);
202
203        $s = 'ALTER TABLE schemas RENAME TO temp_schemas';
204        $ok = $ok && $sqlite->query($s);
205        if (!$ok) return false;
206
207        // create a new table without islookup
208        $ok = $ok && $sqlite->query($sql);
209        if (!$ok) return false;
210
211        $s = 'INSERT INTO schemas SELECT id, tbl, ts, user, comment, config FROM temp_schemas';
212        $ok = $ok && $sqlite->query($s);
213
214        if (!$ok) {
215            $sqlite->query('ROLLBACK TRANSACTION');
216            return false;
217        }
218        $sqlite->query('COMMIT TRANSACTION');
219        return true;
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 helper_plugin_sqlite $sqlite
230     * @return bool
231     */
232    protected function migration17(helper_plugin_sqlite $sqlite)
233    {
234        $sql = "SELECT MAX(id) AS id, tbl FROM schemas
235                    GROUP BY tbl
236            ";
237        $res = $sqlite->query($sql);
238        $schemas = $sqlite->res2arr($res);
239
240        $sqlite->query('BEGIN TRANSACTION');
241        $ok = true;
242
243        foreach ($schemas as $schema) {
244            // find lookup columns
245            $name = $schema['tbl'];
246            $sid = $schema['id'];
247            $s = $this->getLookupColsSql($sid);
248            $res = $sqlite->query($s);
249            $cols = $sqlite->res2arr($res);
250
251            if ($cols) {
252                $colnames = array_map(function ($c) {
253                    return 'col' . $c['COL'];
254                }, $cols);
255
256                // data_ tables
257                $s = 'SELECT pid, rid, rev, ' . implode(', ', $colnames) . " FROM data_$name";
258                $res = $sqlite->query($s);
259                $allValues = $sqlite->res2arr($res);
260
261                if (!empty($allValues)) {
262                    foreach ($allValues as $row) {
263                        list($pid, $rid, $rev, $colref, $rowno, $fixes) = $this->getFixedValues($row);
264                        // now fix the values
265                        if (!empty($fixes)) {
266                            $sql = "UPDATE data_$name SET " . implode(', ', $fixes) . " WHERE pid = ? and rid = ? 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                $res = $sqlite->query($s);
276                $allValues = $sqlite->res2arr($res);
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 SET " . implode(', ', $fixes) . " WHERE pid = ? AND rid = ? AND rev = ? AND colref = ? AND row = ?";
284                            $params = [$pid, $rid, $rev, $colref, $rowno];
285                            $ok = $ok && $sqlite->query($sql, $params);
286                        }
287                    }
288                }
289            }
290        }
291
292        if (!$ok) {
293            $sqlite->query('ROLLBACK TRANSACTION');
294            return false;
295        }
296        $sqlite->query('COMMIT TRANSACTION');
297        return true;
298    }
299
300    /**
301     * Returns a select statement to fetch Lookup columns in the current schema
302     *
303     * @param int $sid Id of the schema
304     * @return string SQL statement
305     */
306    protected function getLookupColsSql($sid)
307    {
308        return "SELECT C.colref AS COL, T.class AS TYPE
309                FROM schema_cols AS C
310                LEFT OUTER JOIN types AS T
311                    ON C.tid = T.id
312                WHERE C.sid = $sid
313                AND TYPE LIKE '%Lookup'
314            ";
315    }
316
317    /**
318     * Checks for improperly migrated values and returns an array with
319     * "<column> = <fixed-value>" fragments to be used in the UPDATE statement.
320     *
321     * @param array $row
322     * @return array
323     */
324    protected function getFixedValues($row)
325    {
326        $pid = $row['pid'];
327        $rid = $row['rid'];
328        $rev = $row['rev'];
329        $colref = $row['colref'];
330        $rowno = $row['row'];
331        $fixes = [];
332        $matches = [];
333
334        // check if anything needs to be fixed in data columns
335        foreach ($row as $col => $value) {
336            if (in_array($col, ['pid', 'rid', 'rev', 'colref', 'row'])) {
337                continue;
338            }
339            preg_match('/^\["",(?<pid>.*?\D+.*?)\]$/', $value, $matches);
340            if (!empty($matches['pid'])) {
341                $fixes[$col] = '["' . $matches['pid'] . '",0]';
342            }
343        }
344
345        if (!empty($fixes)) {
346            $fixes = array_map(function ($set, $key) {
347                return "$key = '$set'";
348            }, $fixes, array_keys($fixes));
349        }
350
351        return [$pid, $rid, $rev, $colref, $rowno, $fixes];
352    }
353}
354