1
|
<?php
|
2
|
/**
|
3
|
* Created by PhpStorm.
|
4
|
* User: crose
|
5
|
* Date: 5/9/17
|
6
|
* Time: 8:56 AM
|
7
|
*/
|
8
|
|
9
|
namespace IMATHUZH\Qfq\Core\Database;
|
10
|
|
11
|
use IMATHUZH\Qfq\Core\Exception\Thrower;
|
12
|
use IMATHUZH\Qfq\Core\Form\FormAsFile;
|
13
|
use IMATHUZH\Qfq\Core\Helper\Logger;
|
14
|
use IMATHUZH\Qfq\Core\Helper\OnString;
|
15
|
use IMATHUZH\Qfq\Core\Helper\Path;
|
16
|
use IMATHUZH\Qfq\Core\QuickFormQuery;
|
17
|
use IMATHUZH\Qfq\Core\Store\Config;
|
18
|
use IMATHUZH\Qfq\Core\Store\Store;
|
19
|
use IMATHUZH\Qfq\Core\Store\T3Info;
|
20
|
use IMATHUZH\Qfq\Core\Typo3\T3Handler;
|
21
|
|
22
|
|
23
|
/*
|
24
|
* Read the extension version number.
|
25
|
* Read the QFQ database version number: stored in the comment field of table 'Form'. Format: Version=x.y.z
|
26
|
* If versions different:
|
27
|
* * Read the update array 'DatabaseUpdateData.php'.
|
28
|
* * Play all changes from the update array after 'old' upto 'new'.
|
29
|
* * Save new QFQ database version in the comment field of table 'Form'
|
30
|
*
|
31
|
* In a new QFQ installation, the comment field of table 'Form' is empty. On the first call of QFQ, the version string
|
32
|
* will be set. Also the 'qfqDefaultTables.sql' will be played to create alls QFQ system tables initially.
|
33
|
*
|
34
|
*/
|
35
|
|
36
|
/**
|
37
|
* Class DatabaseUpdate
|
38
|
* @package qfq
|
39
|
*/
|
40
|
class DatabaseUpdate {
|
41
|
|
42
|
/**
|
43
|
* @var Database
|
44
|
*/
|
45
|
protected $db = null;
|
46
|
|
47
|
/**
|
48
|
* @var Store
|
49
|
*/
|
50
|
protected $store = null;
|
51
|
|
52
|
/**
|
53
|
* @param Database $db
|
54
|
* @param Store $store
|
55
|
*/
|
56
|
public function __construct(Database $db, Store $store) {
|
57
|
$this->db = $db;
|
58
|
$this->store = $store;
|
59
|
}
|
60
|
|
61
|
/**
|
62
|
* @return mixed
|
63
|
* @throws \CodeException
|
64
|
*/
|
65
|
private function getExtensionVersion() {
|
66
|
$path = __DIR__ . '/../../../ext_emconf.php';
|
67
|
$_EXTKEY = EXT_KEY;
|
68
|
$EM_CONF = null;
|
69
|
if (@file_exists($path)) {
|
70
|
include $path;
|
71
|
|
72
|
if (isset($EM_CONF[$_EXTKEY]['version'])) {
|
73
|
return $EM_CONF[$_EXTKEY]['version'];
|
74
|
}
|
75
|
}
|
76
|
|
77
|
throw new \CodeException('Failed to read extension version', ERROR_QFQ_VERSION);
|
78
|
}
|
79
|
|
80
|
/**
|
81
|
* Try to read the QFQ version number from 'comment' in table 'Form'.
|
82
|
* In a very special situation , there might be table 'form' and 'Form' in the same Database. This should be handled
|
83
|
* in the way that the latest version number is the active one.
|
84
|
*
|
85
|
* @return bool|string false if there is no table 'Form' or if there is no comment set in table 'Form'.
|
86
|
* @throws \CodeException
|
87
|
* @throws \DbException
|
88
|
* @throws \UserFormException
|
89
|
*/
|
90
|
private function getDatabaseVersion() {
|
91
|
|
92
|
$arr = $this->db->sql("SHOW TABLE STATUS WHERE Name='Form'", ROW_REGULAR);
|
93
|
|
94
|
$found = '';
|
95
|
//
|
96
|
foreach ($arr as $row) {
|
97
|
if (isset($row['Comment'])) {
|
98
|
parse_str($row['Comment'], $arr);
|
99
|
if (($arr[QFQ_VERSION_KEY] ?? '') !== '' && (version_compare($arr[QFQ_VERSION_KEY], $found) == 1)) {
|
100
|
$found = $arr;
|
101
|
}
|
102
|
} else {
|
103
|
continue;
|
104
|
}
|
105
|
}
|
106
|
|
107
|
return ($found === '') ? false : $found;
|
108
|
}
|
109
|
|
110
|
/**
|
111
|
* @param $version
|
112
|
*
|
113
|
* @throws \CodeException
|
114
|
* @throws \DbException
|
115
|
* @throws \UserFormException
|
116
|
*/
|
117
|
private function setDatabaseVersion($version) {
|
118
|
|
119
|
if (is_array($version)) {
|
120
|
$versionInfo = $version;
|
121
|
} else {
|
122
|
$versionInfo = $this->getDatabaseVersion();
|
123
|
$versionInfo[QFQ_VERSION_KEY] = $version;
|
124
|
}
|
125
|
|
126
|
$this->db->sql("ALTER TABLE `Form` COMMENT = '" . http_build_query($versionInfo) . "'");
|
127
|
|
128
|
}
|
129
|
|
130
|
/**
|
131
|
*
|
132
|
* @param string $dbUpdate SYSTEM_DB_UPDATE_ON | SYSTEM_DB_UPDATE_OFF | SYSTEM_DB_UPDATE_AUTO
|
133
|
*
|
134
|
* @throws \CodeException
|
135
|
* @throws \DbException
|
136
|
* @throws \UserFormException
|
137
|
* @throws \UserReportException
|
138
|
*/
|
139
|
public function checkNupdate($dbUpdate) {
|
140
|
|
141
|
$new = $this->getExtensionVersion();
|
142
|
$versionInfo = $this->getDatabaseVersion();
|
143
|
$old = $versionInfo[QFQ_VERSION_KEY] ?? false;
|
144
|
|
145
|
$this->checkT3QfqConfig($old, $new);
|
146
|
|
147
|
if ($dbUpdate === SYSTEM_DB_UPDATE_NEVER) {
|
148
|
return;
|
149
|
}
|
150
|
|
151
|
if ($dbUpdate === SYSTEM_DB_UPDATE_ALWAYS || ($dbUpdate === SYSTEM_DB_UPDATE_AUTO && $new != $old)) {
|
152
|
|
153
|
if (version_compare($old, '21.2.0') < 1 && !defined('PHPUNIT_QFQ')) {
|
154
|
$this->enforceExistenceOfFormEditorReport();
|
155
|
}
|
156
|
|
157
|
$newFunctionHash = $this->updateSqlFunctions($versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] ?? '');
|
158
|
if (null !== $newFunctionHash) {
|
159
|
$versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] = $newFunctionHash;
|
160
|
$versionInfo[QFQ_VERSION_KEY_FUNCTION_VERSION] = $new;
|
161
|
}
|
162
|
|
163
|
if (FEATURE_FORM_FILE_SYNC) {
|
164
|
if ($this->db->existTable('Form')) {
|
165
|
// If Form table exists, import all form files so everything is up to date.
|
166
|
FormAsFile::importAllForms($this->db, true); // Note: Creates path and exports all forms first if the form directory does not exist.
|
167
|
|
168
|
// create Form table and export all system forms
|
169
|
$this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');
|
170
|
FormAsFile::exportAllForms($this->db);
|
171
|
} else {
|
172
|
// If not, then create Form table and export all system forms
|
173
|
$this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');
|
174
|
FormAsFile::exportAllForms($this->db);
|
175
|
|
176
|
// import form files which existed before the new installation
|
177
|
FormAsFile::importAllForms($this->db, true);
|
178
|
}
|
179
|
} else {
|
180
|
|
181
|
$this->db->playSqlFile(__DIR__ . '/../../Sql/qfqDefaultTables.sql');
|
182
|
}
|
183
|
|
184
|
// Perform dbUpdate only after qfqDefaultTables.sql has been played:
|
185
|
// in case a new system table has been added between old and new and there is an dbUpdate on the new system table now.
|
186
|
$this->dbUpdateStatements($old, $new);
|
187
|
|
188
|
FormAsFile::importSystemForms($this->db);
|
189
|
|
190
|
Logger::logMessage(date('Y.m.d H:i:s ') . ": Updated from QFQ version '$old' to '$new'", Path::absoluteQfqLogFile());
|
191
|
|
192
|
// Finally write the latest version number.
|
193
|
$versionInfo[QFQ_VERSION_KEY] = $new;
|
194
|
$this->setDatabaseVersion($versionInfo);
|
195
|
}
|
196
|
|
197
|
if ($old === false) {
|
198
|
// A complete new installation get's some extra tables
|
199
|
$this->db->playSqlFile(__DIR__ . '/../../Sql/customTable.sql');
|
200
|
}
|
201
|
|
202
|
if (version_compare($old, '19.9.0') === -1) {
|
203
|
$this->updateSpecialColumns();
|
204
|
if (FEATURE_FORM_FILE_SYNC) {
|
205
|
FormAsFile::exportAllForms($this->db);
|
206
|
}
|
207
|
}
|
208
|
|
209
|
// run slug migration: show exception if old alias (e.g. "p:id=<Alias>") notation is used anywhere in QFQ code
|
210
|
$state = $this->t3v10SlugMigration($versionInfo[QFQ_VERSION_KEY_SLUG_MIGRATION] ?? '');
|
211
|
if (!is_null($state)) {
|
212
|
$versionInfo[QFQ_VERSION_KEY_SLUG_MIGRATION] = $state;
|
213
|
$this->setDatabaseVersion($versionInfo);
|
214
|
}
|
215
|
|
216
|
}
|
217
|
|
218
|
/**
|
219
|
* Check Typo3 config if values needs to be updated.
|
220
|
* This is typically necessary if default config values change, to guarantee existing installations behave in legacy mode.
|
221
|
*
|
222
|
* @param $old
|
223
|
* @param $new
|
224
|
*/
|
225
|
private function checkT3QfqConfig($old, $new) {
|
226
|
|
227
|
if ($new == $old || $old === false) {
|
228
|
return;
|
229
|
}
|
230
|
|
231
|
if (version_compare($old, '20.2.0') == -1) {
|
232
|
T3Handler::updateT3QfqConfig(SYSTEM_RENDER_BOTH, SYSTEM_RENDER); //Legacy behaviour.
|
233
|
}
|
234
|
}
|
235
|
|
236
|
/**
|
237
|
* Throws exception if no tt-content record exists which contains "file=_formEditor"
|
238
|
*
|
239
|
* @throws \CodeException
|
240
|
* @throws \DbException
|
241
|
* @throws \UserFormException
|
242
|
* @throws \UserReportException
|
243
|
*/
|
244
|
private function enforceExistenceOfFormEditorReport() {
|
245
|
$dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
|
246
|
$sql = "SELECT `uid` FROM " . $dbT3 . ".`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0 AND `bodytext` LIKE '%file=_formEditor%'";
|
247
|
$res = $this->db->sql($sql);
|
248
|
if (empty($res)) {
|
249
|
$message = '<h2>FormEditor Report not found</h2>'
|
250
|
. 'Please create a Typo3 QFQ content element with the following content:'
|
251
|
. '<pre>file=_formEditor</pre>'
|
252
|
. 'More information: See Section "FormEditor" of the QFQ Documentation.';
|
253
|
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
|
254
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
|
255
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
|
256
|
throw new \DbException(json_encode($errorMsg), E_ERROR);
|
257
|
}
|
258
|
}
|
259
|
|
260
|
/**
|
261
|
* @throws \CodeException
|
262
|
* @throws \DbException
|
263
|
* @throws \UserFormException
|
264
|
* @throws \UserReportException
|
265
|
*/
|
266
|
private function t3v10SlugMigration($dBMigrationFlag) {
|
267
|
|
268
|
##################################
|
269
|
##### PHPUNIT: SKIP FUNCTION #####
|
270
|
|
271
|
# php unit: skip
|
272
|
if (defined('PHPUNIT_QFQ')) {
|
273
|
return null;
|
274
|
}
|
275
|
|
276
|
|
277
|
#####################################################################################
|
278
|
##### ONLY RUN THIS FUNCTION IF TYPO3 VERSION 9 OR HIGHER AND NOT API ENDPOINT #####
|
279
|
|
280
|
if ( ! (T3Handler::isTypo3Loaded() && T3Handler::useSlugsInsteadOfPageAlias()) ) {
|
281
|
return null;
|
282
|
}
|
283
|
|
284
|
|
285
|
##################################
|
286
|
##### COMPUTE MIGRATION MODE #####
|
287
|
|
288
|
// read variable "RUN_PAGE_SLUG_MIGRATION_CHECK" from qfq.json
|
289
|
$x = Config::get(FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK);
|
290
|
$forceRunMigrationCheck = ($x === true || $x === "true");
|
291
|
|
292
|
// if db flag "done" or "skip" then skip check
|
293
|
if (!$forceRunMigrationCheck && ($dBMigrationFlag === QFQ_VERSION_KEY_SLUG_MIGRATION_DONE || $dBMigrationFlag === QFQ_VERSION_KEY_SLUG_MIGRATION_SKIP)) {
|
294
|
return null;
|
295
|
}
|
296
|
|
297
|
// get value from GET parameter ACTION_SLUG_MIGRATION_UPDATE
|
298
|
$actionSlugMigration = ($_GET[ACTION_SLUG_MIGRATION_UPDATE] ?? '');
|
299
|
|
300
|
// if GET parameter "skipForever" then write "skip" to db
|
301
|
if (!$forceRunMigrationCheck && $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_SKIP_FOREVER) {
|
302
|
return QFQ_VERSION_KEY_SLUG_MIGRATION_SKIP;
|
303
|
}
|
304
|
|
305
|
// if GET is "resume" and session is "pause" then resume.
|
306
|
if (($_SESSION[ACTION_SLUG_MIGRATION_UPDATE] ?? '') === ACTION_SLUG_MIGRATION_DO_PAUSE && $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_RESUME) {
|
307
|
$_SESSION[ACTION_SLUG_MIGRATION_UPDATE] = '';
|
308
|
}
|
309
|
|
310
|
// if GET parameter or session is "pause" then skip check and show message
|
311
|
if ($actionSlugMigration === ACTION_SLUG_MIGRATION_DO_PAUSE || ($_SESSION[ACTION_SLUG_MIGRATION_UPDATE] ?? '') === ACTION_SLUG_MIGRATION_DO_PAUSE) {
|
312
|
$_SESSION[ACTION_SLUG_MIGRATION_UPDATE] = ACTION_SLUG_MIGRATION_DO_PAUSE;
|
313
|
QuickFormQuery::$systemMessages[] = 'Page slug migration paused. '
|
314
|
. '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_RESUME . '">Resume migration</a>';
|
315
|
return null;
|
316
|
}
|
317
|
|
318
|
// is replacement mode?
|
319
|
$doReplace = $actionSlugMigration === ACTION_SLUG_MIGRATION_DO_REPLACE;
|
320
|
|
321
|
############################################################
|
322
|
##### THROW EXCEPTION IF BACKEND USER IS NOT LOGGED IN #####
|
323
|
|
324
|
if (!T3Info::beUserLoggedIn()) {
|
325
|
Thrower::dbException('Page Slug Migration: Please log in to Typo3 backend and reload this page.');
|
326
|
}
|
327
|
|
328
|
|
329
|
################################################
|
330
|
##### ONLY RUN THIS FUNCTION ONCE PER PAGE #####
|
331
|
|
332
|
# Caching: If multiple QFQ tt_content elements are present then only process this function once
|
333
|
static $hasAlreadyRunOnPage = false;
|
334
|
if ($hasAlreadyRunOnPage) {
|
335
|
Thrower::dbException('Page Slug Migration.', 'Please go to a page which contains only one QFQ content element.');
|
336
|
}
|
337
|
$hasAlreadyRunOnPage = true;
|
338
|
|
339
|
|
340
|
############################################
|
341
|
##### DEFINE SYMBOLS USED IN EXCEPTION #####
|
342
|
|
343
|
$noSuggestionSymbol = '❎'; // signifies that there no suggestion has been found for this occurrence
|
344
|
$replacedSymbol = '🆎'; // signifies that here something was replaced automatically. (only in replacement mode)
|
345
|
$preventMatchSymbol = '🍼'; // not seen by user. Used to prevent already matched strings to match again.
|
346
|
|
347
|
#######################################
|
348
|
##### DEFINE SLUG LOOKUP FUNCTION #####
|
349
|
|
350
|
// check whether column pages.zzz_deleted_alias exists
|
351
|
$dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
|
352
|
$aliasColumn = 'zzz_deleted_alias';
|
353
|
$tableDefinition = $this->db->sql("SHOW FIELDS FROM `$dbT3`.`pages`", ROW_EXPECT_GE_1);
|
354
|
$aliasColumnExists = 0 < count(array_filter($tableDefinition, function ($c) use ($aliasColumn) {return $c['Field'] === $aliasColumn;}));
|
355
|
|
356
|
// Get id, slug and alias of all Typo3 pages
|
357
|
$pages = $this->db->sql("SELECT `uid`, `slug`" . ($aliasColumnExists ? ", `$aliasColumn`" : '') . " FROM `" . $dbT3 . "`.`pages` WHERE `deleted`=0;");
|
358
|
|
359
|
// define slug lookup function
|
360
|
$getSlug = function (string $aliasOrId) use ($aliasColumn, $aliasColumnExists, $pages, $noSuggestionSymbol) {
|
361
|
|
362
|
// remove ?id=
|
363
|
if (OnString::strStartsWith($aliasOrId, '?id=')) {
|
364
|
$aliasOrId = substr($aliasOrId, 4);
|
365
|
} else if (OnString::strStartsWith($aliasOrId, 'index.php?id=')) {
|
366
|
$aliasOrId = substr($aliasOrId, 13);
|
367
|
}
|
368
|
|
369
|
if (is_numeric($aliasOrId)) {
|
370
|
|
371
|
// if its an id (number), get the page with that id
|
372
|
$uidMatches = array_filter($pages, function ($p) use ($aliasOrId) {return $p['uid'] === intval($aliasOrId);});
|
373
|
$page = reset($uidMatches);
|
374
|
return $page !== false ? $page['slug'] : $noSuggestionSymbol;
|
375
|
} else {
|
376
|
|
377
|
// search alias in page slugs. ('_' was automatically replaced by '-' Typo3 v9 Migration)
|
378
|
$slugMatches = array_filter($pages, function ($p) use ($aliasOrId) {
|
379
|
return ($p['slug'] === '/' . str_replace('_', '-', $aliasOrId)) || ($p['slug'] === '/' . $aliasOrId);});
|
380
|
$slugs = array_map(function ($p) {return $p['slug'];}, $slugMatches);
|
381
|
if (1 === count(array_unique($slugs))) {
|
382
|
return reset($slugs);
|
383
|
}
|
384
|
|
385
|
// search alias in old alias column ('zzz_deleted_alias') if it exists
|
386
|
if ($aliasColumnExists) {
|
387
|
$aliasMatches = array_filter($pages, function ($p) use ($aliasColumn, $aliasOrId) {return $p[$aliasColumn] === $aliasOrId;});
|
388
|
$slugs = array_map(function ($p) {return $p['slug'];}, $aliasMatches);
|
389
|
if (1 === count(array_unique($slugs))) {
|
390
|
return reset($slugs);
|
391
|
}
|
392
|
}
|
393
|
}
|
394
|
|
395
|
return $noSuggestionSymbol;
|
396
|
};
|
397
|
|
398
|
##################################
|
399
|
##### DEFINE REGEX PATTERNS ######
|
400
|
|
401
|
// Patterns for which we might be able to give a suggestion
|
402
|
$patternsWithSuggestions = [
|
403
|
'/([\'"\|]p:)((?:id=)?[a-zA-Z0-9_\-]*)([&\|"\'])/s',
|
404
|
'/([\'"\|]p:)((?:id=)?{{(?:pageAlias|pageId))(:T[0E]*}}[&\|"\'])/s',
|
405
|
'/(href=[\'"])((?:index\.php)?\?id=[a-zA-Z0-9_\-]*)([&"\'])/s',
|
406
|
'/(href=[\'"])((?:index\.php)?\?id={{(?:pageAlias|pageId))(:T[0E]*}}[&"\'])/s'
|
407
|
];
|
408
|
|
409
|
// Patterns for which we can't make a suggestion (applied after the the patterns with suggestions were replaced)
|
410
|
$patternsNeedManualFix = [
|
411
|
'/({{)((?:pageAlias|pageId))(:T[BCDEFLMPRSUVY0]*}})/s',
|
412
|
'/([\'"\|]u:\?)(\S+?)([&\|"\'])/s',
|
413
|
// '/([\'"\|]U:)(id=\S+?)([&\|"\'])/s', // already caught by a pattern bellow
|
414
|
// '/([\'"\|]u:\S+?[\?&])(id=\S+?)([&\|"\'])/s', // already caught by a pattern bellow
|
415
|
'/([&\|"\'\?:]id=)([^' . $noSuggestionSymbol . '\s]+?)([&\|"\'])/s'
|
416
|
];
|
417
|
|
418
|
##################################################
|
419
|
##### DEFINE SUGGESTION GENERATING FUNCTION ######
|
420
|
|
421
|
$suggestionForMatch = function (string $prefix, string $match, string $postfix) use ($getSlug, $noSuggestionSymbol) {
|
422
|
if (OnString::strEndsWith($prefix, 'p:') && !OnString::strContains($match, '{')) {
|
423
|
|
424
|
// cases 'p:id=alias& or 'p:id=5' etc. >>> remove id=
|
425
|
$match = OnString::strStartsWith($match, 'id=') ? substr($match, 3) : $match;
|
426
|
|
427
|
// replace & with ? behind slug
|
428
|
$postfix = ($postfix === '&' ? '?' : $postfix);
|
429
|
|
430
|
// cases 'p:alias& or 'p:5' etc. >>> <slug>
|
431
|
return [$prefix, $getSlug($match), $postfix];
|
432
|
|
433
|
} elseif (OnString::strEndsWith($prefix, 'p:') && OnString::strContains($match, '{{')) {
|
434
|
|
435
|
// case 'p:id={{pageAlias:T}}& >>> remove id=
|
436
|
$match = OnString::strStartsWith($match, 'id=') ? substr($match, 3) : $match;
|
437
|
|
438
|
// replace & with ? behind variable
|
439
|
$trailing = substr($postfix, -1);
|
440
|
$postfix = substr($postfix, 0, -1) . ($trailing === '&' ? '?' : $trailing);
|
441
|
|
442
|
# case 'p:{{pageAlias:T}}& >>> pageSlug
|
443
|
return [$prefix, '{{' . TYPO3_PAGE_SLUG, $postfix];
|
444
|
|
445
|
} elseif (OnString::strStartsWith($prefix, 'href=') && !OnString::strStartsWith($postfix, ':T')) {
|
446
|
|
447
|
// replace & with ? behind slug
|
448
|
$postfix = ($postfix === '&' ? '?' : $postfix);
|
449
|
|
450
|
# case href="?id=alias& or href="?id=5& >>> {{baseUrlLang:Y}}/<slug>
|
451
|
$slug = $getSlug($match);
|
452
|
return [$prefix, '{{' . SYSTEM_BASE_URL_LANG . ':Y}}' . (OnString::strStartsWith($slug, '/') ? '' : '/') . $slug, $postfix];
|
453
|
|
454
|
} elseif (OnString::strStartsWith($prefix, 'href=') && OnString::strStartsWith($postfix, ':T')) {
|
455
|
|
456
|
// replace & with ? behind variable
|
457
|
$trailing = substr($postfix, -1);
|
458
|
$postfix = substr($postfix, 0, -1) . ($trailing === '&' ? '?' : $trailing);
|
459
|
|
460
|
// case href="index.php?id={{pageAlias:T}} or href="?id={{pageAlias:T}} >>> {{baseUrlLang:Y}}/{{pageSlug
|
461
|
return [$prefix, '{{' . SYSTEM_BASE_URL_LANG . ':Y}}/{{' . TYPO3_PAGE_SLUG, $postfix];
|
462
|
|
463
|
} else {
|
464
|
return [$prefix, $noSuggestionSymbol . 'ERROR', $postfix]; // This should never happen
|
465
|
}
|
466
|
};
|
467
|
|
468
|
##################################################
|
469
|
##### GET QFQ CODE FROM DB (REPORT AND FORM) #####
|
470
|
|
471
|
# fill the array $qfqCodeBlobs with qfq code
|
472
|
|
473
|
// Array keys for the qfq code collection array $qfqCodeBlobs
|
474
|
$KEY_TITLE = 'title'; // title which will be shown in exception for that code blob
|
475
|
$KEY_CONTENT = 'content'; // the content of the blob
|
476
|
$KEY_SQL_UPDATE = 'sql_update'; // sql update statement for that blob
|
477
|
|
478
|
// get reports from tt_content.bodytext
|
479
|
$reports = $this->db->sql("SELECT tt.`pid`, tt.`uid`, tt.`header`, tt.`bodytext`, tt.`hidden`, p.`hidden` AS pageHidden FROM `" . $dbT3 . "`.`tt_content` AS tt, `" . $dbT3 . "`.`pages` AS p WHERE tt.`CType`='qfq_qfq' AND tt.`deleted`=0 AND p.`deleted`=0 AND p.uid=tt.pid");
|
480
|
$qfqCodeBlobs = array_map(function($r) use ($dbT3, $KEY_SQL_UPDATE, $KEY_CONTENT, $KEY_TITLE) {
|
481
|
$maybeHidden = (intval($r['hidden']) !== 0) || (intval($r['pageHidden']) !== 0);
|
482
|
return [
|
483
|
$KEY_TITLE => 'QFQ Report on pid=' . $r['pid'] . ' with uid=' . $r['uid'] . ' and header: ' . $r['header']
|
484
|
. ($maybeHidden ? '<br><small>Note: Content element is probably hidden / not in use.</small>' : ''),
|
485
|
$KEY_CONTENT => $r['bodytext'],
|
486
|
$KEY_SQL_UPDATE => "UPDATE `$dbT3`.`tt_content` SET `bodytext` = ? WHERE uid=" . $r['uid'] . ";"
|
487
|
];}, $reports);
|
488
|
|
489
|
// get Forms
|
490
|
$formColumnsToCheck = [
|
491
|
'title',
|
492
|
'multiMode',
|
493
|
'multiSql',
|
494
|
'multiDetailForm',
|
495
|
'multiDetailFormParameter',
|
496
|
'parameter',
|
497
|
'parameterLanguageA',
|
498
|
'parameterLanguageB',
|
499
|
'parameterLanguageC',
|
500
|
'parameterLanguageD',
|
501
|
'forwardPage'
|
502
|
];
|
503
|
$forms = $this->db->sql("SELECT `f`.`id`, `f`.`name`, `f`.`" . join("`, `f`.`", $formColumnsToCheck) . "` FROM `Form` AS f");
|
504
|
foreach ($forms as $i => $form) {
|
505
|
foreach ($formColumnsToCheck as $j => $column) {
|
506
|
$qfqCodeBlobs[] = [
|
507
|
$KEY_TITLE => "Column '$column' of table Form with id=" . $form['id'] . " and name=" . $form['name'],
|
508
|
$KEY_CONTENT => $form[$column],
|
509
|
$KEY_SQL_UPDATE => "UPDATE Form SET `$column`=? WHERE `id`=" . $form['id'] . ";"
|
510
|
];
|
511
|
}
|
512
|
}
|
513
|
|
514
|
// get FormElements
|
515
|
$formElementColumnsToCheck = [
|
516
|
'label',
|
517
|
'modeSql',
|
518
|
'checkPattern',
|
519
|
'onChange',
|
520
|
'maxLength',
|
521
|
'note',
|
522
|
'tooltip',
|
523
|
'placeholder',
|
524
|
'value',
|
525
|
'sql1',
|
526
|
'parameter',
|
527
|
'parameterLanguageA',
|
528
|
'parameterLanguageB',
|
529
|
'parameterLanguageC',
|
530
|
'parameterLanguageD',
|
531
|
'clientJs',
|
532
|
'feGroup'
|
533
|
];
|
534
|
$formElements = $this->db->sql("SELECT `fe`.`id`, `fe`.`" . join("`, `fe`.`", $formElementColumnsToCheck) . "` FROM `FormElement` AS fe");
|
535
|
foreach ($formElements as $i => $formElement) {
|
536
|
foreach ($formElementColumnsToCheck as $j => $column) {
|
537
|
$qfqCodeBlobs[] = [
|
538
|
$KEY_TITLE => "Column '$column' of table FormElement with id=" . $formElement['id'],
|
539
|
$KEY_CONTENT => $formElement[$column],
|
540
|
$KEY_SQL_UPDATE => "UPDATE FormElement SET `$column`=? WHERE `id`=" . $formElement['id'] . ";"
|
541
|
];
|
542
|
}
|
543
|
}
|
544
|
|
545
|
#####################
|
546
|
##### MAIN LOOP #####
|
547
|
|
548
|
# find occurrences of the patterns
|
549
|
# replace occurrences with suggestions and update database if replacement mode is active
|
550
|
|
551
|
// placeholders will be replaced with html after using htmlentities() on qfq code
|
552
|
$placeholderBegin = '%%%BEGIN-ALIAS-SUGGESTION%%%';
|
553
|
$placeholderArrow = '%%%ARROW-ALIAS-SUGGESTION%%%';
|
554
|
$placeholderEnd = '%%%END-ALIAS-SUGGESTION%%%';
|
555
|
|
556
|
$message = ''; // content of exception
|
557
|
$allMatches = []; // collect all matching lines for overview at beginning of exception
|
558
|
foreach ($qfqCodeBlobs as $i => $qfqCode) {
|
559
|
|
560
|
// make sure the control characters are not used in the QFQ code
|
561
|
|
562
|
if ($qfqCode[$KEY_CONTENT] && ((OnString::strContains($qfqCode[$KEY_CONTENT], $noSuggestionSymbol) || OnString::strContains($qfqCode[$KEY_CONTENT], $replacedSymbol)))) {
|
563
|
Thrower::dbException('Page Slug Migration.', "Unicode character $noSuggestionSymbol or $replacedSymbol found in" . $qfqCode[$KEY_TITLE] . ". The page slug migration script can't continue since it uses those characters as control characters. Please temporarily replace those characters.");
|
564
|
}
|
565
|
|
566
|
// add suggestion marks to all occurrences of regex patterns in $patternsWithSuggestions
|
567
|
$replaced_with_placeholder = preg_replace_callback($patternsWithSuggestions, function (array $matches) use ($preventMatchSymbol, $doReplace, $KEY_SQL_UPDATE, $qfqCode, $replacedSymbol, $suggestionForMatch, $placeholderArrow, $getSlug, $placeholderBegin, $placeholderEnd, $noSuggestionSymbol, &$allMatches) {
|
568
|
$fullMatch = $matches[0];
|
569
|
$prefix = $matches[1];
|
570
|
$match = $matches[2];
|
571
|
$postfix = $matches[3];
|
572
|
|
573
|
// get suggestion
|
574
|
list($prefix_suggestion, $match_suggestion, $postfix_suggestion) = $suggestionForMatch($prefix, $match, $postfix);
|
575
|
|
576
|
if ($doReplace) {
|
577
|
|
578
|
// if replacement mode is active replace occurrence with suggestion (add trailing replacement symbol)
|
579
|
if (!OnString::strContains($match_suggestion, $noSuggestionSymbol)) {
|
580
|
|
581
|
// only replace if there actually is a suggestion
|
582
|
$replacement = $prefix_suggestion . $match_suggestion . $postfix_suggestion . $replacedSymbol;
|
583
|
$allMatches[] = $fullMatch . ' ➡ ' . $prefix_suggestion . $match_suggestion . $postfix_suggestion . $replacedSymbol;
|
584
|
} else {
|
585
|
|
586
|
// if there is no suggestion, keep as is
|
587
|
$replacement = $fullMatch;
|
588
|
}
|
589
|
} else {
|
590
|
|
591
|
# if not replacement mode then replace occurrence with "OCCURRENCE -> SUGGESTION" (using placeholders)
|
592
|
|
593
|
// protect matched string from being matched again by interlacing it with $preventMatchSymbol ( "string" -> "XsXtXrXiXnXg" )
|
594
|
// symbol will be removed before output
|
595
|
$fullMatchProtected = join(array_map(function ($c) use ($preventMatchSymbol) {return $preventMatchSymbol . $c;}, str_split($fullMatch)));
|
596
|
|
597
|
$replacement = $placeholderBegin . $fullMatchProtected . $placeholderArrow . $prefix_suggestion . $match_suggestion . $postfix_suggestion . $placeholderEnd;
|
598
|
$allMatches[] = $fullMatch . ' ➡ ' . $prefix_suggestion . $match_suggestion . $postfix_suggestion;
|
599
|
}
|
600
|
|
601
|
return $replacement;
|
602
|
}, $qfqCode[$KEY_CONTENT]);
|
603
|
|
604
|
// find those patterns where we can't give a suggestion
|
605
|
if (!$doReplace) {
|
606
|
$replaced_with_placeholder = preg_replace($patternsNeedManualFix, $placeholderBegin . '${1}' . '${2}'. '${3}' . $placeholderArrow . $noSuggestionSymbol . $placeholderEnd, $replaced_with_placeholder);
|
607
|
}
|
608
|
|
609
|
// do the suggestion replacement if in replacement mode and construct error message
|
610
|
if (strpos($replaced_with_placeholder, $placeholderBegin) !== false) {
|
611
|
|
612
|
// We are in "display" mode, we show suggestions, no replacements are made
|
613
|
$message .= '<hr><span style="font-weight: bold; color: blue;">' . $qfqCode[$KEY_TITLE] . '</span><br><br>';
|
614
|
$message .= $qfqCode[$KEY_SQL_UPDATE];
|
615
|
$message .= '<pre>' . str_replace([$preventMatchSymbol, $placeholderBegin, $placeholderArrow, $placeholderEnd, "\r\n", "\n"], [
|
616
|
'',
|
617
|
'<span style="font-weight: bold; color: red;">',
|
618
|
'</span> ➡ <span style="font-weight: bold; color: green;">',
|
619
|
'</span>',
|
620
|
"<br>", "<br>"
|
621
|
], htmlentities($replaced_with_placeholder)) . '</pre>';
|
622
|
} elseif (strpos($replaced_with_placeholder, $replacedSymbol) !== false) {
|
623
|
|
624
|
// We are in "replacement" mode, show what is been replaced and do the replacement
|
625
|
$message .= '<hr><span style="font-weight: bold; color: blue;">' . $qfqCode[$KEY_TITLE] . '</span><br><br>';
|
626
|
$message .= '<pre>' . str_replace(["\r\n", "\n"], ["<br>", "<br>"], htmlentities($replaced_with_placeholder)) . '</pre>';
|
627
|
|
628
|
// Check again if we are actually in replacement mode, just to be sure
|
629
|
if ($doReplace) {
|
630
|
|
631
|
// DATABASE CHANGE! here the database is updated with the replaced suggestions
|
632
|
$this->db->sql($qfqCode[$KEY_SQL_UPDATE], ROW_REGULAR, [str_replace($replacedSymbol, '', $replaced_with_placeholder)]);
|
633
|
}
|
634
|
}
|
635
|
}
|
636
|
|
637
|
###################################
|
638
|
##### IF NO OCCURRENCES FOUND #####
|
639
|
|
640
|
// if no occurrences were found then set db flag to "done"
|
641
|
if ($message === '' && !$doReplace) {
|
642
|
if ($forceRunMigrationCheck) {
|
643
|
Thrower::dbException('Page Slug Migration.', 'No occurrences of page alias where found. Please disable the setting ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' in ' . Path::absoluteConf(CONFIG_QFQ_JSON));
|
644
|
}
|
645
|
return QFQ_VERSION_KEY_SLUG_MIGRATION_DONE;
|
646
|
}
|
647
|
|
648
|
###############################################
|
649
|
##### FINALIZE THE EXCEPTION AND THROW IT #####
|
650
|
|
651
|
$reportFilePostfix = '_page_slug_migration.html';
|
652
|
|
653
|
if ($doReplace) {
|
654
|
|
655
|
// save report to file
|
656
|
$reportPath = Path::absoluteLog() . '/' . date("YmdHi") . $reportFilePostfix;
|
657
|
Logger::logMessage('<meta charset="UTF-8">' . "<h2>Report: Replacement Marked with $replacedSymbol in Code</h2>" . $message, $reportPath);
|
658
|
|
659
|
$message = ''
|
660
|
. '<h2>Automatic Replacement Completed</h2>'
|
661
|
. 'The following report has been saved to <br>' . $reportPath
|
662
|
. '<br><br>Click <a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=null">check-again</a> to search for still existing usages of page alias.'
|
663
|
. '<h2>Overview Replacement Suggestions</h2>'
|
664
|
. join('<br>', array_unique($allMatches))
|
665
|
. ' <br><br>(list might not be complete)'
|
666
|
. "<h2>Report: Replacement Marked with $replacedSymbol in Code</h2>"
|
667
|
. $message;
|
668
|
} else {
|
669
|
$message = ''
|
670
|
. '<h2>Page Slug Migration</h2>'
|
671
|
. 'Usages of page alias found. The Typo3 feature "page alias" has been replaced by the "page slug" feature and can no longer be used in links with "?id=[alias]".'
|
672
|
. '<h2>SOLUTION</h2>'
|
673
|
. "<ul>"
|
674
|
. '<li>The special colums such as "_link" now treat the "p:" parameter value as a page slug.</li>'
|
675
|
. '<li>Use {{pageSlug:T}} instead of {{pageAlias:T}} or {{pageId:T}} in the _link columns. E.g. "p:{{pageSlug}}?foo=bar" AS link .</li>'
|
676
|
. '<li>Use {{baseUrlLang:Y}}/{{pageSlug:T}} instead of {{pageAlias:T}} or {{pageId:T}} in hardcoded ' . htmlentities("<a>") . ' tags. E.g. href="{{baseUrlLang:Y}}/{{pageSlug:T}}?foo=bar".</li>'
|
677
|
. '<li>Replace hardcoded aliases in QFQ code with the slugs of the pages. E.g. "p:/some/page?foo=bar" AS _link.</li>'
|
678
|
. '<li>Replace "U: ... &id=[alias]& ... " with "p:[slug]? ... " for all sepecial columns except _paged. I.e. "U: ... &id=[alias]& ... " becomes "p:[slug]? ... ". </li>'
|
679
|
. '<li>Hint: Typo3 replaces "_" with "-" when converting an alias to a slug during the Typo3 version 9 upgrade.</li>'
|
680
|
. '<li>Hint: A page slug always starts with a slash "/" and QFQ expects the slash to be there.</li>'
|
681
|
. '<li>Note: After the page slug comes a "?" not a "&". E.g. "p:/some/page?foo=bar" AS _link. </li>'
|
682
|
. "</ul>"
|
683
|
|
684
|
. '<h2>AUTO SUGGESTIONS</h2>'
|
685
|
|
686
|
. 'In the report below the suggested changes are prominently marked with colour.'
|
687
|
. "<br> If there is a $noSuggestionSymbol then there is no suggestion and you will have to fix it manually."
|
688
|
. "<br> Tip: use ctrl+f and copy $noSuggestionSymbol into the search bar to quickly jump between matches."
|
689
|
|
690
|
. '<h2>ACTIONS</h2>'
|
691
|
. "<ul>"
|
692
|
|
693
|
. '<li><a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_REPLACE . '">Auto replace</a>'
|
694
|
. ' occurrences of page aliases and page ids with the suggested page slug.'
|
695
|
. '<br>A report file with name "[timestamp]'. $reportFilePostfix .'" will be saved to "' . Path::absoluteLog() . '" after the automatic replacement. </li>'
|
696
|
|
697
|
. ' <li>To use the Form Editor you can '
|
698
|
. '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_PAUSE . '">pause the migration temporarliy</a>.</li>'
|
699
|
|
700
|
. '<li>You may also '
|
701
|
. '<a href="?' . ACTION_SLUG_MIGRATION_UPDATE . '=' . ACTION_SLUG_MIGRATION_DO_SKIP_FOREVER . '">skip this check forever</a> but your app will probably be broken.'
|
702
|
. '<br>To reenable the check you can set the config variable ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' to "true" in ' . Path::absoluteConf(CONFIG_QFQ_JSON) . '</li>'
|
703
|
|
704
|
. "</ul>"
|
705
|
|
706
|
|
707
|
|
708
|
|
709
|
|
710
|
|
711
|
. ($forceRunMigrationCheck ? '<br><br>Note: setting ' . FORCE_RUN_PAGE_SLUG_MIGRATION_CHECK . ' is active in qfq.json.' : '')
|
712
|
|
713
|
. '<h2>Overview Replacement Suggestions</h2>'
|
714
|
. join('<br>', array_unique($allMatches))
|
715
|
. '<h2>Report: Occurrences Marked with Red Color in Code</h2>'
|
716
|
. $message;
|
717
|
}
|
718
|
|
719
|
Thrower::dbException('Page Slug Migration.', $message, false, ERROR_PLAY_SQL_FILE);
|
720
|
}
|
721
|
|
722
|
/**
|
723
|
* Check if there are special columns without prepended underscore in the QFQ application. If yes, then throw an error.
|
724
|
* A link is provided to automatically prepend all found special columns. And another link to skip the auto-replacement.
|
725
|
*
|
726
|
* @throws \CodeException
|
727
|
* @throws \DbException
|
728
|
* @throws \UserFormException
|
729
|
*/
|
730
|
private function updateSpecialColumns() {
|
731
|
|
732
|
// Prepare regex patterns to find "AS <special column name>"
|
733
|
$special_columns = ['link', 'exec', 'Page', 'Pagec', 'Paged', 'Pagee', 'Pageh', 'Pagei', 'Pagen', 'Pages'
|
734
|
, 'page', 'pagec', 'paged', 'pagee', 'pageh', 'pagei', 'pagen', 'pages', 'yank', 'Pdf', 'File', 'Zip'
|
735
|
, 'pdf', 'file', 'zip', 'excel', 'savePdf', 'thumbnail', 'monitor', 'mimeType', 'fileSize', 'nl2br'
|
736
|
, 'htmlentities', 'striptags', 'XLS', 'XLSs', 'XLSb', 'XLSn', 'bullet', 'check', 'img', 'mailto'
|
737
|
, 'sendmail', 'vertical'];
|
738
|
|
739
|
$make_pattern = function ($column) {
|
740
|
return '/([aA][sS]\s+)(' . $column . ')/s';
|
741
|
};
|
742
|
|
743
|
$patterns = array_map($make_pattern, $special_columns);
|
744
|
|
745
|
// Prepare search and replace
|
746
|
$placeholder = '%%%UNDERLINE%%%'; // used temporarily to mark where '_' should go
|
747
|
$actionSpecialColumn = $_GET[ACTION_SPECIAL_COLUMN_UPDATE] ?? ''; // get parameter to decide whether to execute the replacement
|
748
|
$dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
|
749
|
$message = ''; // error message in case an old special column is found
|
750
|
|
751
|
// TT_CONTENT tt_content.bodytext
|
752
|
$message_fe = '';
|
753
|
if (defined('PHPUNIT_QFQ')) {
|
754
|
$res = array();
|
755
|
} else {
|
756
|
$res = $this->db->sql("SELECT `uid`, `header`, `bodytext` FROM `" . $dbT3 . "`.`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0;");
|
757
|
}
|
758
|
foreach ($res as $i => $tt_content) {
|
759
|
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['bodytext']);
|
760
|
if (strpos($replaced_placeholder, $placeholder) !== false) {
|
761
|
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
|
762
|
$replace = str_replace($placeholder, '_', $replaced_placeholder);
|
763
|
$query = "UPDATE `" . $dbT3 . "`.`tt_content` SET `bodytext`='" . addslashes($replace) . "' WHERE `uid`='" . $tt_content['uid'] . "'";
|
764
|
$this->db->sql($query);
|
765
|
}
|
766
|
$message_fe .= '<hr><b>' . $tt_content['header'] . ' [uid:' . $tt_content['uid'] . ']</b><br><br>';
|
767
|
$message_fe .= str_replace($placeholder,
|
768
|
'<span style="font-weight: bold; color: red;">>>>_</span>',
|
769
|
htmlentities($replaced_placeholder));
|
770
|
}
|
771
|
}
|
772
|
if ($message_fe != '') {
|
773
|
$message .= '<hr><h3>Typo3 Table: tt_content (column: bodytext)</h3>' . $message_fe;
|
774
|
}
|
775
|
|
776
|
// FORM ELEMENTS FormElement.value, FormElement.note
|
777
|
$message_ttc = '';
|
778
|
if (defined('PHPUNIT_QFQ')) {
|
779
|
$res = array();
|
780
|
} else {
|
781
|
$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%';");
|
782
|
}
|
783
|
foreach ($res as $i => $tt_content) {
|
784
|
|
785
|
foreach (['value', 'note'] as $j => $columnName) {
|
786
|
$replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content[$columnName]);
|
787
|
if (strpos($replaced_placeholder, $placeholder) !== false) {
|
788
|
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
|
789
|
$replace = str_replace($placeholder, '_', $replaced_placeholder);
|
790
|
$query = "UPDATE `FormElement` SET `" . $columnName . "`='" . addslashes($replace) . "' WHERE `id`='" . $tt_content['id'] . "'";
|
791
|
$this->db->sql($query);
|
792
|
}
|
793
|
$message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.' . $columnName . ')</b><br><br>';
|
794
|
$message_ttc .= str_replace($placeholder,
|
795
|
'<span style="font-weight: bold; color: red;">>>>_</span>',
|
796
|
htmlentities($replaced_placeholder));
|
797
|
}
|
798
|
}
|
799
|
}
|
800
|
if ($message_ttc != '') {
|
801
|
$message .= '<hr><h3>QFQ Table: FormElement (columns: value and note)</h3>' . $message_ttc;
|
802
|
}
|
803
|
|
804
|
// show error message or save log
|
805
|
if ($message != '') {
|
806
|
if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
|
807
|
// save log file
|
808
|
$message = '<h1>Special column names replaced</h1>The following special column names were replaced.<hr>' . $message;
|
809
|
Logger::logMessage($message, Path::absoluteLog() . '/' . date("YmdHi") . '_special_columns_auto_update.html');
|
810
|
} elseif ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE) {
|
811
|
// do nothing
|
812
|
} else {
|
813
|
// show error
|
814
|
$message = $actionSpecialColumn
|
815
|
. '<h2>Special Column names without prepended underscore found.</h2>'
|
816
|
. ' Those are not supported any longer.'
|
817
|
. '<h2>SOLUTION</h2>'
|
818
|
. 'Click <a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_REPLACE))) . '">Auto-Replace</a>'
|
819
|
. ' to automatically prepend the found column names with an underscore.'
|
820
|
. ' In the report below the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'
|
821
|
. ' This report will be saved in ' . Path::absoluteLog() . ' after the automatic replacement.'
|
822
|
. ' <br><br>To update qfq without changing the special columns (your app will probably be broken): '
|
823
|
. '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE))) . '">Skip Auto-Replace</a>'
|
824
|
. '<h2>Report</h2>'
|
825
|
. $message;
|
826
|
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq. ';
|
827
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
|
828
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
|
829
|
throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
|
830
|
}
|
831
|
}
|
832
|
}
|
833
|
|
834
|
/**
|
835
|
* @param $oldFunctionsHash
|
836
|
*
|
837
|
* @return string
|
838
|
*
|
839
|
* @throws \DbException
|
840
|
* @throws \UserFormException
|
841
|
*/
|
842
|
private function updateSqlFunctions($oldFunctionsHash) {
|
843
|
|
844
|
if (ACTION_FUNCTION_UPDATE_NEVER === $oldFunctionsHash) {
|
845
|
return null;
|
846
|
}
|
847
|
|
848
|
$actionFunction = $_GET[ACTION_FUNCTION_UPDATE] ?? '';
|
849
|
|
850
|
if ($actionFunction === ACTION_FUNCTION_UPDATE_NEXT_UPDATE) {
|
851
|
return ACTION_FUNCTION_UPDATE_NOT_PERFORMED;
|
852
|
} elseif ($actionFunction === ACTION_FUNCTION_UPDATE_NEVER) {
|
853
|
return ACTION_FUNCTION_UPDATE_NEVER;
|
854
|
}
|
855
|
|
856
|
$functionSql = file_get_contents(__DIR__ . '/../../Sql/' . QFQ_FUNCTION_SQL);
|
857
|
$functionHash = hash('md5', $functionSql);
|
858
|
|
859
|
if ($functionHash === $oldFunctionsHash) {
|
860
|
return null;
|
861
|
}
|
862
|
|
863
|
$query = str_replace('%%FUNCTIONSHASH%%', $functionHash, $functionSql);
|
864
|
if (stripos($query, 'delimiter')) {
|
865
|
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
|
866
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error in file " . QFQ_FUNCTION_SQL . ": The keyword DELIMITER is present " .
|
867
|
"in " . QFQ_FUNCTION_SQL . ", this usually leads to errors when trying to execute it on the database.";
|
868
|
throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
|
869
|
}
|
870
|
|
871
|
try {
|
872
|
$this->db->playMultiQuery($query);
|
873
|
$functionsHashTest = $this->db->sql('SELECT GETFUNCTIONSHASH() AS res;', ROW_EXPECT_1)['res'];
|
874
|
} catch (\DbException $e) {
|
875
|
$functionsHashTest = null;
|
876
|
} catch (\CodeException $e) {
|
877
|
$functionsHashTest = null;
|
878
|
}
|
879
|
|
880
|
$qfqFunctionSqlRelToApp = Path::appToExt('Classes/Sql/' . QFQ_FUNCTION_SQL);
|
881
|
|
882
|
if ($functionHash !== null && $functionsHashTest === $functionHash) {
|
883
|
return $functionHash;
|
884
|
} else {
|
885
|
$errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
|
886
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER] =
|
887
|
"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. " .
|
888
|
"Possible solutions: <ul>" .
|
889
|
'<li>Grant SUPER, CREATE ROUTINE, ALTER ROUTINE privileges to qfq mysql user temporarily.</li>' .
|
890
|
'<li>Play the following file manually on the database: ' .
|
891
|
'<a href="' . $qfqFunctionSqlRelToApp . '">' . $qfqFunctionSqlRelToApp . '</a><br>and grant the qfq mysql user execution privileges on the sql functions.</li>' .
|
892
|
'<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>' .
|
893
|
'<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>' .
|
894
|
'</ul>' .
|
895
|
"To enable the sql functions update again you can delete the parameter 'functionsHash' in the table comments of the table 'Form'.";
|
896
|
$errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
|
897
|
throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
|
898
|
}
|
899
|
}
|
900
|
|
901
|
/**
|
902
|
* @param $path
|
903
|
*
|
904
|
* @return array
|
905
|
* @throws \CodeException
|
906
|
*/
|
907
|
private function readUpdateData($path) {
|
908
|
|
909
|
if (!@file_exists($path)) {
|
910
|
throw new \CodeException("File '$path'' not found", ERROR_IO_OPEN);
|
911
|
}
|
912
|
|
913
|
$UPDATE_ARRAY = null;
|
914
|
|
915
|
include $path;
|
916
|
|
917
|
return $UPDATE_ARRAY;
|
918
|
|
919
|
}
|
920
|
|
921
|
/**
|
922
|
* Play all update statement with version number are '>' than $old and '<=' to $new.
|
923
|
*
|
924
|
* @param $old
|
925
|
* @param $new
|
926
|
* @throws \CodeException
|
927
|
* @throws \DbException
|
928
|
* @throws \UserFormException
|
929
|
*/
|
930
|
private function dbUpdateStatements($old, $new) {
|
931
|
|
932
|
$dummy = array();
|
933
|
|
934
|
if ($new == '' || $old === false || $old === null) {
|
935
|
return;
|
936
|
}
|
937
|
$updateArray = $this->readUpdateData(__DIR__ . '/DatabaseUpdateData.php');
|
938
|
|
939
|
$apply = false;
|
940
|
foreach ($updateArray as $key => $sqlStatements) {
|
941
|
|
942
|
// Search starting point to apply updates. Do not apply updates for $key>$new
|
943
|
$rc1 = version_compare($key, $old);
|
944
|
$rc2 = version_compare($key, $new);
|
945
|
if ($rc1 == 1 && $rc2 != 1) {
|
946
|
$apply = true;
|
947
|
}
|
948
|
|
949
|
if ($apply) {
|
950
|
// Play Statements
|
951
|
foreach ($sqlStatements as $sql) {
|
952
|
$this->db->sql($sql, ROW_REGULAR, array(),
|
953
|
"Apply updates to QFQ database. Installed version: $old. New QFQ version: $new",
|
954
|
$dummy, $dummy, [1060] /* duplicate column name */);
|
955
|
}
|
956
|
// Remember already applied updates - in case something breaks and the update has to be repeated.
|
957
|
$this->setDatabaseVersion($new);
|
958
|
}
|
959
|
}
|
960
|
}
|
961
|
}
|