DatabaseUpdate.php 16.4 KB
Newer Older
1
2
3
4
5
6
7
8
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 5/9/17
 * Time: 8:56 AM
 */

Marc Egger's avatar
Marc Egger committed
9
10
11
namespace IMATHUZH\Qfq\Core\Database;

use IMATHUZH\Qfq\Core\Helper\Logger;
12
use IMATHUZH\Qfq\Core\Store\Store;
13
14
15
16
17
18


/*
 * Read the extension version number.
 * Read the QFQ database version number: stored in the comment field of table 'Form'. Format:  Version=x.y.z
 * If versions different:
Carsten  Rose's avatar
Carsten Rose committed
19
20
 *   * Read the update array 'DatabaseUpdateData.php'.
 *   * Play all changes from the update array after 'old' upto 'new'.
21
22
 *   * Save new QFQ database version in the comment field of table 'Form'
 *
Carsten  Rose's avatar
Carsten Rose committed
23
24
 * In a new QFQ installation, the comment field of table 'Form' is empty. On the first call of QFQ, the version string
 * will be set. Also the 'formEditor.sql' will be played initially.
25
26
27
 *
 */

28
29
30
31
/**
 * Class DatabaseUpdate
 * @package qfq
 */
32
33
34
35
class DatabaseUpdate {

    /**
     * @var Database instantiated class
36
     * @var Store instantiated class
37
38
     */
    protected $db = null;
39
    protected $store = null;
40
41
42

    /**
     * @param Database $db
43
     * @param Store $store
44
     */
45
    public function __construct(Database $db, Store $store) {
46
        $this->db = $db;
47
        $this->store = $store;
48
49
50
51
    }

    /**
     * @return mixed
Marc Egger's avatar
Marc Egger committed
52
     * @throws \CodeException
53
54
55
56
     */
    private function getExtensionVersion() {
        $path = __DIR__ . '/../../../ext_emconf.php';
        $_EXTKEY = EXT_KEY;
Carsten  Rose's avatar
Carsten Rose committed
57
        $EM_CONF = null;
58
59
60
61
62
63
64
65
        if (@file_exists($path)) {
            include $path;

            if (isset($EM_CONF[$_EXTKEY]['version'])) {
                return $EM_CONF[$_EXTKEY]['version'];
            }
        }

Marc Egger's avatar
Marc Egger committed
66
        throw new \CodeException('Failed to read extension version', ERROR_QFQ_VERSION);
67
68
69
    }

    /**
70
71
72
73
     * Try to read the QFQ version number from 'comment' in table 'Form'.
     * In a very special situation , there might be table 'form' and 'Form' in the same Database. This should be handled
     *  in the way that the latest version number is the active one.
     *
Carsten  Rose's avatar
Carsten Rose committed
74
     * @return bool|string  false if there is no table 'Form' or if there is no comment set in table 'Form'.
Marc Egger's avatar
Marc Egger committed
75
76
77
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
78
79
80
     */
    private function getDatabaseVersion() {

81
82
83
84
85
86
        $arr = $this->db->sql("SHOW TABLE STATUS WHERE Name='Form'", ROW_REGULAR);

        $found = '';
        //
        foreach ($arr as $row) {
            if (isset($row['Comment'])) {
Marc Egger's avatar
Marc Egger committed
87
                parse_str($row['Comment'], $arr);
88
                if (($arr[QFQ_VERSION_KEY] ?? '') !== '' AND (version_compare($arr[QFQ_VERSION_KEY], $found) == 1)) {
Marc Egger's avatar
Marc Egger committed
89
                    $found = $arr;
90
91
92
                }
            } else {
                continue;
Carsten  Rose's avatar
Carsten Rose committed
93
            }
94
95
        }

Marc Egger's avatar
Marc Egger committed
96
        return ($found === '') ? false : $found;
97
98
99
100
    }

    /**
     * @param $version
Carsten  Rose's avatar
Carsten Rose committed
101
     *
Marc Egger's avatar
Marc Egger committed
102
103
104
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
105
106
107
     */
    private function setDatabaseVersion($version) {

Marc Egger's avatar
Marc Egger committed
108
109
110
111
        if (is_array($version)) {
            $versionInfo = $version;
        } else {
            $versionInfo = $this->getDatabaseVersion();
112
            $versionInfo[QFQ_VERSION_KEY] = $version;
Marc Egger's avatar
Marc Egger committed
113
114
115
        }

        $this->db->sql("ALTER TABLE `Form` COMMENT = '" . http_build_query($versionInfo) . "'");
116
117
118
119

    }

    /**
120
121
122
     *
     * @param string $dbUpdate SYSTEM_DB_UPDATE_ON | SYSTEM_DB_UPDATE_OFF | SYSTEM_DB_UPDATE_AUTO
     *
Marc Egger's avatar
Marc Egger committed
123
124
125
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
126
     */
127
128
129
130
131
    public function checkNupdate($dbUpdate) {

        if ($dbUpdate === SYSTEM_DB_UPDATE_NEVER) {
            return;
        }
Carsten  Rose's avatar
Carsten Rose committed
132

133
        $new = $this->getExtensionVersion();
Marc Egger's avatar
Marc Egger committed
134
        $versionInfo = $this->getDatabaseVersion();
Carsten  Rose's avatar
Carsten Rose committed
135
        $old = $versionInfo[QFQ_VERSION_KEY] ?? false;
136

137
138
139
140
        if (version_compare($old, '19.9.9') === -1) {  #TODO: change version number before new release! (replace 19.9.9 with the version of the new release)
            $this->updateSpecialColumns();
        }

141
        if ($dbUpdate === SYSTEM_DB_UPDATE_ALWAYS || ($dbUpdate === SYSTEM_DB_UPDATE_AUTO && $new != $old)) {
Marc Egger's avatar
Marc Egger committed
142

Carsten  Rose's avatar
Carsten Rose committed
143
144
145
            $newFunctionHash = $this->updateSqlFunctions($versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] ?? '');
            if (null !== $newFunctionHash) {
                $versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] = $newFunctionHash;
146
                $versionInfo[QFQ_VERSION_KEY_FUNCTION_VERSION] = $new;
Marc Egger's avatar
Marc Egger committed
147
148
            }

149
            $this->dbUpdateStatements($old, $new);
Marc Egger's avatar
Marc Egger committed
150
            $this->db->playSqlFile(__DIR__ . '/../../Sql/formEditor.sql');
151

152
            $qfqLog = $this->db->getQfqLogFile();
153
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Updated from QFQ version '$old' to '$new'", $qfqLog);
154

155
            // Finally write the latest version number.
156
            $versionInfo[QFQ_VERSION_KEY] = $new;
Marc Egger's avatar
Marc Egger committed
157
            $this->setDatabaseVersion($versionInfo);
158
        }
159
160
161

        if ($old === false) {
            // A complete new installation get's some extra tables
Marc Egger's avatar
Marc Egger committed
162
            $this->db->playSqlFile(__DIR__ . '/../../Sql/customTable.sql');
163
        }
164
165
    }

166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
    /**
     * Check if there are special columns without prepended underscore in the QFQ application. If yes, then throw an error.
     * A link is provided to automatically prepend all found special columns. And another link to skip the auto-replacement.
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function updateSpecialColumns() {
        // Prepare regex patterns to find "AS <special column name>"
        $special_columns = ['link', 'exec', 'Page', 'Pagec', 'Paged', 'Pagee', 'Pageh', 'Pagei', 'Pagen', 'Pages'
            , 'page', 'pagec', 'paged', 'pagee', 'pageh', 'pagei', 'pagen', 'pages', 'yank', 'Pdf', 'File', 'Zip'
            , 'pdf', 'file', 'zip', 'excel', 'savePdf', 'thumbnail', 'monitor', 'mimeType', 'fileSize', 'nl2br'
            , 'htmlentities', 'striptags', 'XLS', 'XLSs', 'XLSb', 'XLSn', 'bullet', 'check', 'img', 'mailto'
            , 'sendmail', 'vertical'];
        $make_pattern = function ($column) {
            return '/([aA][sS]\s+)(' . $column . ')/s';
        };
        $patterns = array_map($make_pattern, $special_columns);

        // Prepare search and replace
        $placeholder = '%%%UNDERLINE%%%';  // used temporarily to mark where '_' should go
        $actionSpecialColumn = $_GET[ACTION_SPECIAL_COLUMN_UPDATE] ?? ''; // get parameter to decide whether to execute the replacement
        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
        $message = ''; // error message in case an old special column is found

        // TT_CONTENT tt_content.bodytext
        $message_fe = '';
        if (defined('PHPUNIT_QFQ')) {
            $res = array();
        } else {
            $res = $this->db->sql("SELECT uid, header, bodytext FROM " . $dbT3 . ".tt_content WHERE CType='qfq_qfq' AND deleted=0;");
        }
        foreach ($res as $i => $tt_content) {
            $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['bodytext']);
            if (strpos($replaced_placeholder, $placeholder) !== false) {
                if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                    $replace = str_replace($placeholder, '_', $replaced_placeholder);
                    $query = "UPDATE " . $dbT3 . ".tt_content SET bodytext='" . addslashes($replace) . "' WHERE uid='" . $tt_content['uid'] . "'";
                    $this->db->sql($query);
                }
                $message_fe .= '<hr><b>' . $tt_content['header'] . ' [uid:' . $tt_content['uid'] . ']</b><br><br>';
                $message_fe .= str_replace($placeholder,
                    '<span style="font-weight: bold; color: red;">>>>_</span>',
                    htmlentities($replaced_placeholder));
            }
        }
        if ($message_fe != '') {
            $message .= '<hr><h3>Typo3 Table: tt_content (column: bodytext)</h3>' . $message_fe;
        }

        // FORM ELEMENTS FormElement.value, FormElement.note
        $message_ttc = '';
        if (defined('PHPUNIT_QFQ')) {
            $res = array();
        } else {
            $res = $this->db->sql("SELECT fe.id, fe.name, fe.value, fe.note FROM FormElement as fe WHERE fe.type='note' AND fe.value LIKE '#!report%' OR fe.note LIKE '%#!report%';");
        }
        foreach ($res as $i => $tt_content) {

            foreach (['value', 'note'] as $j => $columnName) {
                $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content[$columnName]);
                if (strpos($replaced_placeholder, $placeholder) !== false) {
                    if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                        $replace = str_replace($placeholder, '_', $replaced_placeholder);
                        $query = "UPDATE FormElement SET " . $columnName . "='" . addslashes($replace) . "' WHERE id='" . $tt_content['id'] . "'";
                        $this->db->sql($query);
                    }
                    $message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.' . $columnName . ')</b><br><br>';
                    $message_ttc .= str_replace($placeholder,
                        '<span style="font-weight: bold; color: red;">>>>_</span>',
                        htmlentities($replaced_placeholder));
                }
            }
        }
        if ($message_ttc != '') {
            $message .= '<hr><h3>QFQ Table: FormElement (columns: value and note)</h3>' . $message_ttc;
        }

        // show error message or save log
        if ($message != '') {
            if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                // save log file
                $message = '<h1>Special column names replaced</h1>The following special column names were replaced.<hr>' . $message;
                Logger::logMessage($message, SYSTEM_FILEADMIN_PROTECTED_LOG . '/' . date("YmdHi") . '_special_columns_auto_update.html');
            } elseif ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE) {
                // do nothing
            } else {
                // show error
                $message = $actionSpecialColumn
                    . '<h2>Special Column names without prepended underscore found.</h2>'
                    . ' Those are not supported any longer.'
                    . '<h2>SOLUTION</h2>'
                    . 'Click <a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_REPLACE))) . '">Auto-Replace</a>'
                    . ' to automatically prepend the found column names with an underscore.'
                    . ' In the report below the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'
                    . ' This report will be saved in ' . SYSTEM_FILEADMIN_PROTECTED_LOG . ' after the automatic replacement.'
                    . ' <br><br>To update qfq without changing the special columns (your app will probably be broken): '
                    . '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE))) . '">Skip Auto-Replace</a>'
                    . '<h2>Report</h2>'
                    . $message;
                $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq. ';
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
                throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
            }
        }
    }

Marc Egger's avatar
Marc Egger committed
275
276
277
278
279
    /**
     * @param $oldFunctionsHash
     *
     * @return string
     *
Marc Egger's avatar
Marc Egger committed
280
281
     * @throws \DbException
     * @throws \UserFormException
Marc Egger's avatar
Marc Egger committed
282
283
     */
    private function updateSqlFunctions($oldFunctionsHash) {
Carsten  Rose's avatar
Carsten Rose committed
284

285
        if (ACTION_FUNCTION_UPDATE_NEVER === $oldFunctionsHash) {
Marc Egger's avatar
Marc Egger committed
286
287
288
            return null;
        }

Carsten  Rose's avatar
Carsten Rose committed
289
290
291
        $actionFunction = $_GET[ACTION_FUNCTION_UPDATE] ?? '';

        if ($actionFunction === ACTION_FUNCTION_UPDATE_NEXT_UPDATE) {
292
            return ACTION_FUNCTION_UPDATE_NOT_PERFORMED;
Carsten  Rose's avatar
Carsten Rose committed
293
        } elseif ($actionFunction === ACTION_FUNCTION_UPDATE_NEVER) {
294
295
296
            return ACTION_FUNCTION_UPDATE_NEVER;
        }

Marc Egger's avatar
Marc Egger committed
297
        $functionSql = file_get_contents(__DIR__ . '/../../Sql/function.sql');
298
        $functionHash = hash('md5', $functionSql);
Marc Egger's avatar
Marc Egger committed
299

300
        if ($functionHash === $oldFunctionsHash) {
Marc Egger's avatar
Marc Egger committed
301
302
303
            return null;
        }

304
        $query = str_replace('%%FUNCTIONSHASH%%', $functionHash, $functionSql);
Marc Egger's avatar
Marc Egger committed
305
306
        if (stripos($query, 'delimiter')) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
307
308
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error in file " . QFQ_FUNCTION_SQL . ": The keyword DELIMITER is present " .
                "in " . QFQ_FUNCTION_SQL . ", this usually leads to errors when trying to execute it on the database.";
Marc Egger's avatar
Marc Egger committed
309
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
Marc Egger's avatar
Marc Egger committed
310
        }
Carsten  Rose's avatar
Carsten Rose committed
311

Marc Egger's avatar
Marc Egger committed
312
        try {
Nicola Chiapolini's avatar
Nicola Chiapolini committed
313
            $this->db->playMultiQuery($query);
Marc Egger's avatar
Marc Egger committed
314
            $functionsHashTest = $this->db->sql('SELECT GETFUNCTIONSHASH() AS res;', ROW_EXPECT_1)['res'];
Marc Egger's avatar
Marc Egger committed
315
        } catch (\DbException $e) {
Marc Egger's avatar
Marc Egger committed
316
            $functionsHashTest = null;
Nicola Chiapolini's avatar
Nicola Chiapolini committed
317
318
        } catch (\CodeException $e) {
            $functionsHashTest = null;
Marc Egger's avatar
Marc Egger committed
319
        }
Carsten  Rose's avatar
Carsten Rose committed
320

321
322
        if ($functionHash !== null AND $functionsHashTest === $functionHash) {
            return $functionHash;
Marc Egger's avatar
Marc Egger committed
323
324
        } else {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
325
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] =
326
                "Failed to play " . QFQ_FUNCTION_SQL . ", probably not enough <a href='https://mariadb.com/kb/en/library/stored-routine-privileges/'>permissions</a> for the qfq mysql user. " .
327
                "Possible solutions: <ul>" .
Nicola Chiapolini's avatar
Nicola Chiapolini committed
328
                '<li>Grant SUPER, CREATE ROUTINE, ALTER ROUTINE privileges to qfq mysql user temporarily.</li>' .
329
                '<li>Play the following file manually on the database: ' .
330
                '<a href="typo3conf/ext/qfq/Classes/Sql/' . QFQ_FUNCTION_SQL . '">typo3conf/ext/qfq/Classes/Sql/' . QFQ_FUNCTION_SQL . '</a><br>and grant the qfq mysql user execution privileges on the sql functions.</li>' .
331
332
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEXT_UPDATE))) . '">Click here</a> to skip the sql functions update until next qfq release update</li>' .
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEVER))) . '">Click here</a> to skip the sql functions update forever</li>' .
333
334
335
                '</ul>' .
                "To enable the sql functions update again you can delete the parameter 'functionsHash' in the table comments of the table 'Form'.";
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
Marc Egger's avatar
Marc Egger committed
336
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
Marc Egger's avatar
Marc Egger committed
337
338
339
        }
    }

340
    /**
Carsten  Rose's avatar
Carsten Rose committed
341
     * @param $path
Carsten  Rose's avatar
Carsten Rose committed
342
     *
Carsten  Rose's avatar
Carsten Rose committed
343
     * @return array
Marc Egger's avatar
Marc Egger committed
344
     * @throws \CodeException
345
     */
Carsten  Rose's avatar
Carsten Rose committed
346
    private function readUpdateData($path) {
347
348

        if (!@file_exists($path)) {
Marc Egger's avatar
Marc Egger committed
349
            throw new \CodeException("File '$path'' not found", ERROR_IO_OPEN);
350
351
352
        }

        $UPDATE_ARRAY = null;
Carsten  Rose's avatar
Carsten Rose committed
353

354
        include $path;
Carsten  Rose's avatar
Carsten Rose committed
355

356
357
358
359
360
        return $UPDATE_ARRAY;

    }

    /**
361
362
     * Play all update statement with version number are '>' than $old and '<=' to $new.
     *
363
364
     * @param $old
     * @param $new
Marc Egger's avatar
Marc Egger committed
365
366
367
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
368
369
     */
    private function dbUpdateStatements($old, $new) {
Marc Egger's avatar
Marc Egger committed
370
        if ($new == '' || $old === false || $old === null) {
371
372
            return;
        }
Carsten  Rose's avatar
Carsten Rose committed
373
        $updateArray = $this->readUpdateData(__DIR__ . '/DatabaseUpdateData.php');
374
375
376
377

        $apply = false;
        foreach ($updateArray as $key => $sqlStatements) {

378
379
380
381
            // Search starting point to apply updates. Do not apply updates for $key>$new
            $rc1 = version_compare($key, $old);
            $rc2 = version_compare($key, $new);
            if ($rc1 == 1 && $rc2 != 1) {
382
383
384
                $apply = true;
            }

385
386
387
            if ($apply) {
                // Play Statements
                foreach ($sqlStatements as $sql) {
388
                    $this->db->sql($sql, ROW_REGULAR, array(), "Apply updates to QFQ database. Installed version: $old. New QFQ version: $new");
389
                }
390
391
                // Remember already applied updates - in case something breaks and the update has to be repeated.
                $this->setDatabaseVersion($new);
392
393
394
395
            }
        }
    }
}