[TASK] Combine lint task for ci integration and correct code violations
[Packages/TYPO3.CMS.git] / typo3 / sysext / install / Classes / Updates / SysRefindexHashUpdater.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Install\Updates;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use Doctrine\DBAL\DBALException;
19 use Doctrine\DBAL\Platforms\SqlitePlatform;
20 use Doctrine\DBAL\Platforms\SQLServerPlatform;
21 use TYPO3\CMS\Core\Database\ConnectionPool;
22 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
23 use TYPO3\CMS\Core\Utility\GeneralUtility;
24
25 /**
26 * Storing new hashes without sorting column in sys_refindex
27 */
28 class SysRefindexHashUpdater extends AbstractUpdate
29 {
30 /**
31 * @var string
32 */
33 protected $title = 'Update the hash field of sys_refindex to exclude the sorting field';
34
35 /**
36 * Fields that make up the hash value
37 *
38 * @var array
39 */
40 protected $hashMemberFields = [
41 'tablename',
42 'recuid',
43 'field',
44 'flexpointer',
45 'softref_key',
46 'softref_id',
47 'deleted',
48 'workspace',
49 'ref_table',
50 'ref_uid',
51 'ref_string'
52 ];
53
54 /**
55 * The new hash version
56 *
57 * @var int
58 */
59 protected $hashVersion = 2;
60
61 /**
62 * Checks if an update is needed
63 *
64 * @param string &$description The description for the update
65 * @return bool Whether an update is needed (true) or not (false)
66 * @throws \InvalidArgumentException
67 */
68 public function checkForUpdate(&$description)
69 {
70 if ($this->isWizardDone()) {
71 return false;
72 }
73
74 $description = 'The hash calculation for records within the table sys_refindex was changed'
75 . ' to exclude the sorting field. The records need to be updated with a newly calculated hash.<br />'
76 . '<b>Important:</b> If this online migration times out you can perform an offline update using the'
77 . ' command-line instead of the wizard, by executing the following command: '
78 . '<code>TYPO3_PATH_ROOT=$PWD/web vendor/bin/typo3 referenceindex:update</code>';
79
80 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_refindex');
81
82 // SQLite does not have any helpful string/hash functions, unless the wizard is marked done
83 // we need to assume this updater needs to run.
84 if ($connection->getDatabasePlatform() instanceof SqlitePlatform) {
85 return true;
86 }
87
88 $queryBuilder = $connection->createQueryBuilder();
89 $count = (int)$queryBuilder->count('*')
90 ->from('sys_refindex')
91 ->where($queryBuilder->expr()->neq('hash', $this->calculateHashFragment()))
92 ->execute()
93 ->fetchColumn(0);
94
95 return $count !== 0;
96 }
97
98 /**
99 * Performs the hash update for sys_refindex records
100 *
101 * @param array &$databaseQueries Queries done in this update
102 * @param string &$customMessage Custom messages
103 *
104 * @return bool
105 * @throws \InvalidArgumentException
106 * @throws \Doctrine\DBAL\DBALException
107 * @throws \Doctrine\DBAL\ConnectionException
108 */
109 public function performUpdate(array &$databaseQueries, &$customMessage)
110 {
111 $this->deleteDuplicateRecords();
112
113 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_refindex');
114 $queryBuilder = $connection->createQueryBuilder();
115
116 $statement = $queryBuilder->select('hash', ...$this->hashMemberFields)
117 ->from('sys_refindex')
118 ->where($queryBuilder->expr()->neq('hash', $this->calculateHashFragment()))
119 ->execute();
120
121 $updateQueryBuilder = $connection->createQueryBuilder();
122 $updateQueryBuilder->update('sys_refindex')
123 ->set('hash', $updateQueryBuilder->createPositionalParameter('', \PDO::PARAM_STR), false)
124 ->where(
125 $updateQueryBuilder->expr()->eq(
126 'hash',
127 $updateQueryBuilder->createPositionalParameter('', \PDO::PARAM_STR)
128 )
129 );
130 $databaseQueries[] = $updateQueryBuilder->getSQL();
131 $updateStatement = $connection->prepare($updateQueryBuilder->getSQL());
132
133 $connection->beginTransaction();
134 try {
135 while ($row = $statement->fetch()) {
136 $newHash = md5(implode('///', array_diff_key($row, ['hash' => true])) . '///' . $this->hashVersion);
137 $updateStatement->execute([$newHash, $row['hash']]);
138 }
139 $connection->commit();
140 $this->markWizardAsDone();
141 } catch (DBALException $e) {
142 $customMessage = 'SQL-ERROR: ' . htmlspecialchars($e->getPrevious()->getMessage());
143 $connection->rollBack();
144 return false;
145 }
146
147 return true;
148 }
149
150 /**
151 * Build the DBMS specific SQL fragment that calculates the MD5 hash for the given fields within the database.
152 *
153 * @return string
154 * @throws \InvalidArgumentException
155 */
156 protected function calculateHashFragment(): string
157 {
158 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_refindex');
159 $databasePlatform = $connection->getDatabasePlatform();
160
161 $quotedFields = array_map(
162 function ($fieldName) use ($connection) {
163 return sprintf('CAST(%s AS CHAR)', $connection->quoteIdentifier($fieldName));
164 },
165 $this->hashMemberFields
166 );
167
168 // Add the new hash version to the list of fields
169 $quotedFields[] = $connection->quote('2');
170
171 if ($databasePlatform instanceof SQLServerPlatform) {
172 $concatFragment = sprintf('CONCAT_WS(%s, %s)', $connection->quote('///'), implode(', ', $quotedFields));
173 return sprintf(
174 'LOWER(CONVERT(NVARCHAR(32),HashBytes(%s, %s), 2))',
175 $connection->quote('MD5'),
176 $concatFragment
177 );
178 } elseif ($databasePlatform instanceof SqlitePlatform) {
179 // SQLite cannot do MD5 in database, so update all records which have a hash
180 return $connection->quote('');
181 } else {
182 $concatFragment = sprintf('CONCAT_WS(%s, %s)', $connection->quote('///'), implode(', ', $quotedFields));
183 return sprintf('LOWER(MD5(%s))', $concatFragment);
184 }
185 }
186
187 /**
188 * Remove records from the sys_refindex table which will end up with identical hash values
189 * when used with hash version 2. These records can show up when the rows are identical in
190 * all fields besides hash and sorting. Due to sorting being ignored in the new hash version
191 * these will end up having identical hashes and resulting in a DUPLICATE KEY violation due
192 * to the hash field being the primary (unique) key.
193 */
194 public function deleteDuplicateRecords()
195 {
196 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_refindex');
197
198 // Find all rows which are identical except for the hash and sorting value
199 $dupesQueryBuilder = $connection->createQueryBuilder();
200 $dupesQueryBuilder->select(...$this->hashMemberFields)
201 ->addSelectLiteral($dupesQueryBuilder->expr()->min('sorting', 'min_sorting'))
202 ->from('sys_refindex')
203 ->groupBy(...$this->hashMemberFields)
204 ->having(
205 $dupesQueryBuilder->expr()->comparison(
206 $dupesQueryBuilder->expr()->count('sorting'),
207 ExpressionBuilder::GT,
208 1
209 )
210 );
211
212 // Find all hashes for rows which would have identical hashes using the new algorithm.
213 // This query will not return the row with the lowest sorting value. In the next step
214 // this will ensure we keep it to be updated to the new hash format.
215 $hashQueryBuilder = $connection->createQueryBuilder();
216 // Add the derived table for finding identical hashes.
217 $hashQueryBuilder->getConcreteQueryBuilder()->from(
218 sprintf('(%s)', $dupesQueryBuilder->getSQL()),
219 $hashQueryBuilder->quoteIdentifier('t')
220 );
221 $hashQueryBuilder->select('s.hash')
222 ->from('sys_refindex', 's')
223 ->where($hashQueryBuilder->expr()->gt('s.sorting', $hashQueryBuilder->quoteIdentifier('t.min_sorting')));
224
225 foreach ($this->hashMemberFields as $field) {
226 $hashQueryBuilder->andWhere(
227 $hashQueryBuilder->expr()->eq('s.' . $field, $hashQueryBuilder->quoteIdentifier('t.' . $field))
228 );
229 }
230
231 // Wrap the previous query in another derived table. This indirection is required to use the
232 // sys_refindex table in the final delete statement as well as in the subselect used to determine
233 // the records to be deleted.
234 $selectorQueryBuilder = $connection->createQueryBuilder()->select('d.hash');
235 $selectorQueryBuilder->getConcreteQueryBuilder()->from(
236 sprintf(('(%s)'), $hashQueryBuilder->getSQL()),
237 $selectorQueryBuilder->quoteIdentifier('d')
238 );
239
240 $deleteQueryBuilder = $connection->createQueryBuilder();
241 $deleteQueryBuilder->delete('sys_refindex')
242 ->where(
243 $deleteQueryBuilder->expr()->comparison(
244 $deleteQueryBuilder->quoteIdentifier('sys_refindex.hash'),
245 'IN',
246 sprintf('(%s)', $selectorQueryBuilder->getSQL())
247 )
248 )
249 ->execute();
250 }
251 }