‪TYPO3CMS  9.5
SeparateSysHistoryFromSysLogUpdate.php
Go to the documentation of this file.
1 <?php
2 declare(strict_types = 1);
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 
23 
29 {
30 
32  private const ‪BATCH_SIZE = 100;
33 
35  private const ‪MOVE_DATA = 0;
36 
38  private const ‪UPDATE_HISTORY = 1;
39 
43  public function ‪getIdentifier(): string
44  {
45  return 'separateSysHistoryFromLog';
46  }
47 
51  public function ‪getTitle(): string
52  {
53  return 'Migrates existing sys_log entries into sys_history';
54  }
55 
59  public function ‪getDescription(): string
60  {
61  return 'The history of changes of a record is now solely stored within sys_history.'
62  . ' Previous data within sys_log needs to be migrated into sys_history now.';
63  }
64 
70  public function ‪updateNecessary(): bool
71  {
72  // sys_log field has been removed, no need to do something.
73  if (!$this->‪checkIfFieldInTableExists('sys_history', 'sys_log_uid')) {
74  return false;
75  }
76 
77  // Check if there is data to migrate
78  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
79  ->getQueryBuilderForTable('sys_history');
80  $queryBuilder->getRestrictions()->removeAll();
81  $count = $queryBuilder->count('*')
82  ->from('sys_history')
83  ->where($queryBuilder->expr()->neq('sys_log_uid', 0))
84  ->execute()
85  ->fetchColumn(0);
86 
87  return $count > 0;
88  }
89 
93  public function ‪getPrerequisites(): array
94  {
95  return [
96  DatabaseUpdatedPrerequisite::class
97  ];
98  }
99 
108  public function ‪executeUpdate(): bool
109  {
110  // If rows from the target table that is updated and the sys_registry table are on the
111  // same connection, the update statement and sys_registry position update will be
112  // handled in a transaction to have an atomic operation in case of errors during execution.
113  $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
114  $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_history');
115  $connectionForSysRegistry = $connectionPool->getConnectionForTable('sys_registry');
116 
117  // In case the PHP ended for whatever reason, fetch the last position from registry
118  // and only execute the phase(s) that has/have not been executed yet
119  $startPositionAndPhase = $this->‪getStartPositionAndPhase();
120 
121  if ($startPositionAndPhase['phase'] === self::MOVE_DATA) {
122  $startPositionAndPhase = $this->‪moveDataFromSysLogToSysHistory(
123  $connection,
124  $connectionForSysRegistry,
125  $startPositionAndPhase
126  );
127  }
128 
129  if ($startPositionAndPhase['phase'] === self::UPDATE_HISTORY) {
131  $connectionForSysRegistry,
132  $startPositionAndPhase
133  );
134  }
135 
136  return true;
137  }
138 
148  ‪Connection $connection,
149  ‪Connection $connectionForSysRegistry,
150  array $startPositionAndPhase
151  ): array {
152  do {
153  $processedRows = 0;
154 
155  // update "modify" statements (= decoupling)
156  $queryBuilder = $connection->‪createQueryBuilder();
157  $rows = $queryBuilder->‪select('sys_history.uid AS history_uid', 'sys_history.history_data', 'sys_log.*')
158  ->‪from('sys_history')
159  ->‪leftJoin(
160  'sys_history',
161  'sys_log',
162  'sys_log',
163  $queryBuilder->expr()->eq('sys_history.sys_log_uid', $queryBuilder->quoteIdentifier('sys_log.uid'))
164  )
165  ->‪where($queryBuilder->expr()->gt('sys_history.uid', $queryBuilder->createNamedParameter($startPositionAndPhase['uid'])))
166  ->‪setMaxResults(self::BATCH_SIZE)
167  ->‪orderBy('sys_history.uid', 'ASC')
168  ->‪execute()
169  ->fetchAll();
170 
171  foreach ($rows as $row) {
172  $logData = $this->‪unserializeToArray((string)($row['log_data'] ?? ''));
173  $historyData = $this->‪unserializeToArray((string)($row['history_data'] ?? ''));
174  $updateData = [
175  'actiontype' => ‪RecordHistoryStore::ACTION_MODIFY,
176  'usertype' => 'BE',
177  'userid' => $row['userid'],
178  'sys_log_uid' => 0,
179  'history_data' => json_encode($historyData),
180  'originaluserid' => empty($logData['originalUser']) ? null : $logData['originalUser']
181  ];
182 
183  if ($connection === $connectionForSysRegistry) {
184  // sys_history and sys_registry tables are on the same connection, use a transaction
185  $connection->beginTransaction();
186  try {
187  $startPositionAndPhase = $this->‪updateTablesAndTrackProgress(
188  $connection,
189  $connection,
190  $updateData,
191  $logData,
192  $row
193  );
194  $connection->commit();
195  } catch (\‪Exception $up) {
196  $connection->rollBack();
197  throw ($up);
198  }
199  } else {
200  // Different connections for sys_history and sys_registry -> execute two
201  // distinct queries and hope for the best.
202  $startPositionAndPhase = $this->‪updateTablesAndTrackProgress(
203  $connection,
204  $connectionForSysRegistry,
205  $updateData,
206  $logData,
207  $row
208  );
209  }
210 
211  $processedRows++;
212  }
213  // repeat until a resultset smaller than the batch size was processed
214  } while ($processedRows === self::BATCH_SIZE);
215 
216  // phase 0 is finished
217  $registry = GeneralUtility::makeInstance(Registry::class);
218  $startPositionAndPhase = [
219  'phase' => ‪self::UPDATE_HISTORY,
220  'uid' => 0,
221  ];
222  $registry->set('installSeparateHistoryFromSysLog', 'phaseAndPosition', $startPositionAndPhase);
223 
224  return $startPositionAndPhase;
225  }
226 
240  ‪Connection $connection,
241  ‪Connection $connectionForSysRegistry,
242  array $updateData,
243  array $logData,
244  array $row
245  ): array {
246  $connection->‪update(
247  'sys_history',
248  $updateData,
249  ['uid' => (int)$row['history_uid']],
250  ['uid' => ‪Connection::PARAM_INT]
251  );
252 
253  // Store information about history entry in sys_log table
254  $logData['history'] = $row['history_uid'];
255  $connection->‪update(
256  'sys_log',
257  ['log_data' => serialize($logData)],
258  ['uid' => (int)$row['uid']],
259  ['uid' => ‪Connection::PARAM_INT]
260  );
261  $startPositionAndPhase = [
262  'phase' => ‪self::MOVE_DATA,
263  'uid' => $row['history_uid'],
264  ];
265  $connectionForSysRegistry->‪update(
266  'sys_registry',
267  [
268  'entry_value' => serialize($startPositionAndPhase)
269  ],
270  [
271  'entry_namespace' => 'installSeparateHistoryFromSysLog',
272  'entry_key' => 'phaseAndPosition',
273  ]
274  );
275 
276  return $startPositionAndPhase;
277  }
278 
286  ‪Connection $connectionForSysRegistry,
287  array $startPositionAndPhase
288  ) {
289  do {
290  $processedRows = 0;
291 
292  // Add insert/delete calls
293  $logQueryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_log');
294  $result = $logQueryBuilder->select('uid', 'userid', 'action', 'tstamp', 'log_data', 'tablename', 'recuid')
295  ->from('sys_log')
296  ->where(
297  $logQueryBuilder->expr()->eq('type', $logQueryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),
298  $logQueryBuilder->expr()->orX(
299  $logQueryBuilder->expr()->eq('action', $logQueryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),
300  $logQueryBuilder->expr()->eq('action', $logQueryBuilder->createNamedParameter(3, \PDO::PARAM_INT))
301  )
302  )
303  ->andWhere(
304  $logQueryBuilder->expr()->gt('uid', $logQueryBuilder->createNamedParameter($startPositionAndPhase['uid']))
305  )
306  ->orderBy('uid', 'ASC')
307  ->setMaxResults(self::BATCH_SIZE)
308  ->execute();
309 
310  foreach ($result as $row) {
311  $logData = $this->‪unserializeToArray((string)($row['log_data'] ?? ''));
312  $store = GeneralUtility::makeInstance(
313  RecordHistoryStore::class,
315  $row['userid'],
316  (empty($logData['originalUser']) ? null : $logData['originalUser']),
317  $row['tstamp']
318  );
319 
320  switch ($row['action']) {
321  // Insert
322  case 1:
323  $store->addRecord($row['tablename'], (int)$row['recuid'], $logData);
324  break;
325  // Delete
326  case 3:
327  $store->deleteRecord($row['tablename'], (int)$row['recuid']);
328  break;
329  }
330 
331  $startPositionAndPhase = [
332  'phase' => ‪self::UPDATE_HISTORY,
333  'uid' => $row['uid'],
334  ];
335  $connectionForSysRegistry->‪update(
336  'sys_registry',
337  [
338  'entry_value' => serialize($startPositionAndPhase)
339  ],
340  [
341  'entry_namespace' => 'installSeparateHistoryFromSysLog',
342  'entry_key' => 'phaseAndPosition',
343  ]
344  );
345 
346  $processedRows++;
347  }
348  // repeat until a result set smaller than the batch size was processed
349  } while ($processedRows === self::BATCH_SIZE);
350  }
351 
359  protected function ‪checkIfFieldInTableExists($table, $fieldName): bool
360  {
361  $tableColumns = GeneralUtility::makeInstance(ConnectionPool::class)
362  ->getConnectionForTable($table)
363  ->getSchemaManager()
364  ->listTableColumns($table);
365  return isset($tableColumns[$fieldName]);
366  }
367 
374  protected function ‪getStartPositionAndPhase(): array
375  {
376  $registry = GeneralUtility::makeInstance(Registry::class);
377  $startPosition = $registry->get('installSeparateHistoryFromSysLog', 'phaseAndPosition', []);
378  if (empty($startPosition)) {
379  $startPosition = [
380  'phase' => ‪self::MOVE_DATA,
381  'uid' => 0,
382  ];
383  $registry->set('installSeparateHistoryFromSysLog', 'phaseAndPosition', $startPosition);
384  }
385 
386  return $startPosition;
387  }
388 
389  protected function ‪unserializeToArray(string $serialized): array
390  {
391  $unserialized = unserialize($serialized, ['allowed_classes' => false]);
392  return is_array($unserialized) ? $unserialized : [];
393  }
394 }
‪TYPO3\CMS\Core\DataHandling\History\RecordHistoryStore\USER_BACKEND
‪const USER_BACKEND
Definition: RecordHistoryStore.php:35
‪TYPO3\CMS\Core\Database\Connection\PARAM_INT
‪const PARAM_INT
Definition: Connection.php:42
‪TYPO3\CMS\Install\Updates\RepeatableInterface
Definition: RepeatableInterface.php:25
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\select
‪QueryBuilder select(string ... $selects)
Definition: QueryBuilder.php:390
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\keepHistoryForInsertAndDeleteActions
‪keepHistoryForInsertAndDeleteActions(Connection $connectionForSysRegistry, array $startPositionAndPhase)
Definition: SeparateSysHistoryFromSysLogUpdate.php:285
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\moveDataFromSysLogToSysHistory
‪array moveDataFromSysLogToSysHistory(Connection $connection, Connection $connectionForSysRegistry, array $startPositionAndPhase)
Definition: SeparateSysHistoryFromSysLogUpdate.php:147
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\checkIfFieldInTableExists
‪bool checkIfFieldInTableExists($table, $fieldName)
Definition: SeparateSysHistoryFromSysLogUpdate.php:359
‪TYPO3\CMS\Core\Registry
Definition: Registry.php:32
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\getPrerequisites
‪string[] getPrerequisites()
Definition: SeparateSysHistoryFromSysLogUpdate.php:93
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\BATCH_SIZE
‪const BATCH_SIZE
Definition: SeparateSysHistoryFromSysLogUpdate.php:32
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\orderBy
‪QueryBuilder orderBy(string $fieldName, string $order=null)
Definition: QueryBuilder.php:794
‪TYPO3\CMS\Core\DataHandling\History\RecordHistoryStore\ACTION_MODIFY
‪const ACTION_MODIFY
Definition: RecordHistoryStore.php:30
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\updateNecessary
‪bool updateNecessary()
Definition: SeparateSysHistoryFromSysLogUpdate.php:70
‪TYPO3\CMS\Core\Database\Connection\update
‪int update($tableName, array $data, array $identifier, array $types=[])
Definition: Connection.php:287
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\getDescription
‪string getDescription()
Definition: SeparateSysHistoryFromSysLogUpdate.php:59
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\MOVE_DATA
‪const MOVE_DATA
Definition: SeparateSysHistoryFromSysLogUpdate.php:35
‪TYPO3\CMS\Install\Updates
Definition: AbstractDownloadExtensionUpdate.php:3
‪TYPO3\CMS\Core\DataHandling\History\RecordHistoryStore
Definition: RecordHistoryStore.php:28
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\from
‪QueryBuilder from(string $from, string $alias=null)
Definition: QueryBuilder.php:506
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate
Definition: SeparateSysHistoryFromSysLogUpdate.php:29
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\execute
‪Doctrine DBAL Driver Statement int execute()
Definition: QueryBuilder.php:178
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\setMaxResults
‪QueryBuilder setMaxResults(int $maxResults)
Definition: QueryBuilder.php:329
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\updateTablesAndTrackProgress
‪array updateTablesAndTrackProgress(Connection $connection, Connection $connectionForSysRegistry, array $updateData, array $logData, array $row)
Definition: SeparateSysHistoryFromSysLogUpdate.php:239
‪TYPO3\CMS\Core\Resource\Exception
Definition: Exception.php:21
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:31
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\unserializeToArray
‪unserializeToArray(string $serialized)
Definition: SeparateSysHistoryFromSysLogUpdate.php:389
‪TYPO3\CMS\Install\Updates\UpgradeWizardInterface
Definition: UpgradeWizardInterface.php:22
‪TYPO3\CMS\Core\Database\Connection\createQueryBuilder
‪TYPO3 CMS Core Database Query QueryBuilder createQueryBuilder()
Definition: Connection.php:110
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\executeUpdate
‪bool executeUpdate()
Definition: SeparateSysHistoryFromSysLogUpdate.php:108
‪TYPO3\CMS\Core\Database\ConnectionPool
Definition: ConnectionPool.php:44
‪TYPO3\CMS\Core\Utility\GeneralUtility
Definition: GeneralUtility.php:45
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\where
‪QueryBuilder where(... $predicates)
Definition: QueryBuilder.php:630
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\UPDATE_HISTORY
‪const UPDATE_HISTORY
Definition: SeparateSysHistoryFromSysLogUpdate.php:38
‪TYPO3\CMS\Core\Database\Query\QueryBuilder\leftJoin
‪QueryBuilder leftJoin(string $fromAlias, string $join, string $alias, string $condition=null)
Definition: QueryBuilder.php:570
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\getTitle
‪string getTitle()
Definition: SeparateSysHistoryFromSysLogUpdate.php:51
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\getIdentifier
‪string getIdentifier()
Definition: SeparateSysHistoryFromSysLogUpdate.php:43
‪TYPO3\CMS\Install\Updates\SeparateSysHistoryFromSysLogUpdate\getStartPositionAndPhase
‪array getStartPositionAndPhase()
Definition: SeparateSysHistoryFromSysLogUpdate.php:374