[BUGFIX] Prevent crash in Install Tool and EM with invalid ext_tables.sql
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Schema / SchemaMigrator.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Schema;
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\MySqlPlatform;
20 use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
21 use Doctrine\DBAL\Schema\Column;
22 use Doctrine\DBAL\Schema\ColumnDiff;
23 use Doctrine\DBAL\Schema\Index;
24 use Doctrine\DBAL\Schema\Schema;
25 use Doctrine\DBAL\Schema\SchemaConfig;
26 use Doctrine\DBAL\Schema\SchemaDiff;
27 use Doctrine\DBAL\Schema\Table;
28 use TYPO3\CMS\Core\Database\Connection;
29 use TYPO3\CMS\Core\Database\ConnectionPool;
30 use TYPO3\CMS\Core\Database\Schema\Parser\Parser;
31 use TYPO3\CMS\Core\Utility\GeneralUtility;
32 use TYPO3\CMS\Core\Utility\StringUtility;
33
34 /**
35 * Helper methods to handle SQL files and transform them into individual statements
36 * for further processing.
37 *
38 * @internal
39 */
40 class SchemaMigrator
41 {
42 /**
43 * @var string Prefix of deleted tables
44 */
45 protected $deletedPrefix = 'zzz_deleted_';
46
47 /**
48 * @var Table[]
49 */
50 protected $tables = [];
51
52 /**
53 * @var Schema[]
54 */
55 protected $schema = [];
56
57 /**
58 * Compare current and expected schema definitions and provide updates suggestions in the form
59 * of SQL statements.
60 *
61 * @param string[] $statements The CREATE TABLE statements
62 * @param bool $remove TRUE for RENAME/DROP table and column suggestions, FALSE for ADD/CHANGE suggestions
63 * @return array[] SQL statements to migrate the database to the expected schema, indexed by performed operation
64 * @throws \Doctrine\DBAL\DBALException
65 * @throws \Doctrine\DBAL\Schema\SchemaException
66 * @throws \InvalidArgumentException
67 * @throws \RuntimeException
68 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\UnexpectedSignalReturnValueTypeException
69 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotException
70 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotReturnException
71 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\StatementException
72 */
73 public function getUpdateSuggestions(array $statements, bool $remove = false): array
74 {
75 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
76 $this->parseCreateTableStatements($statements);
77
78 $updateSuggestions = [];
79
80 foreach ($connectionPool->getConnectionNames() as $connectionName) {
81 $connection = $connectionPool->getConnectionByName($connectionName);
82
83 $schemaDiff = $this->buildSchemaDiff($connectionName);
84
85 if ($remove === false) {
86 $updateSuggestions[$connectionName] = array_merge_recursive(
87 ['add' => [], 'create_table' => [], 'change' => [], 'change_currentValue' => []],
88 $this->getNewFieldUpdateSuggestions($schemaDiff, $connection),
89 $this->getNewTableUpdateSuggestions($schemaDiff, $connection),
90 $this->getChangedFieldUpdateSuggestions($schemaDiff, $connection),
91 $this->getChangedTableOptions($schemaDiff, $connection)
92 );
93 } else {
94 $updateSuggestions[$connectionName] = array_merge_recursive(
95 ['change' => [], 'change_table' => [], 'drop' => [], 'drop_table' => [], 'tables_count' => []],
96 $this->getUnusedFieldUpdateSuggestions($schemaDiff, $connection),
97 $this->getUnusedTableUpdateSuggestions($schemaDiff, $connection),
98 $this->getDropTableUpdateSuggestions($schemaDiff, $connection),
99 $this->getDropFieldUpdateSuggestions($schemaDiff, $connection)
100 );
101 }
102 }
103
104 return $updateSuggestions;
105 }
106
107 /**
108 * Return the raw Doctrine SchemaDiff objects for each connection. This diff contains
109 * all changes without any pre-processing.
110 *
111 * @param array $statements
112 * @return SchemaDiff[]
113 * @throws \Doctrine\DBAL\DBALException
114 * @throws \Doctrine\DBAL\Schema\SchemaException
115 * @throws \InvalidArgumentException
116 * @throws \RuntimeException
117 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\UnexpectedSignalReturnValueTypeException
118 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotException
119 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotReturnException
120 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\StatementException
121 */
122 public function getSchemaDiffs(array $statements): array
123 {
124 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
125 $this->parseCreateTableStatements($statements);
126
127 $schemaDiffs = [];
128
129 foreach ($connectionPool->getConnectionNames() as $connectionName) {
130 $schemaDiffs[$connectionName] = $this->buildSchemaDiff($connectionName, false);
131 }
132
133 return $schemaDiffs;
134 }
135
136 /**
137 * This method executes statements from the update suggestions, or a subset of them
138 * filtered by the statements hashes, one by one.
139 *
140 * @param string[] $statements The CREATE TABLE statements
141 * @param string[] $selectedStatements The hashes of the update suggestions to execute
142 * @return array
143 * @throws \Doctrine\DBAL\DBALException
144 * @throws \Doctrine\DBAL\Schema\SchemaException
145 * @throws \InvalidArgumentException
146 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotReturnException
147 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotException
148 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\UnexpectedSignalReturnValueTypeException
149 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\StatementException
150 * @throws \RuntimeException
151 */
152 public function migrate(array $statements, array $selectedStatements): array
153 {
154 $result = [];
155 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
156 $updateSuggestionsPerConnection = array_merge_recursive(
157 $this->getUpdateSuggestions($statements),
158 $this->getUpdateSuggestions($statements, true)
159 );
160
161 foreach ($updateSuggestionsPerConnection as $connectionName => $updateSuggestions) {
162 unset($updateSuggestions['tables_count'], $updateSuggestions['change_currentValue']);
163 $updateSuggestions = array_merge(...array_values($updateSuggestions));
164 $statementsToExecute = array_intersect_key($updateSuggestions, $selectedStatements);
165 if (count($statementsToExecute) === 0) {
166 continue;
167 }
168
169 $connection = $connectionPool->getConnectionByName($connectionName);
170 foreach ($statementsToExecute as $hash => $statement) {
171 try {
172 $connection->executeUpdate($statement);
173 } catch (DBALException $e) {
174 $result[$hash] = $e->getPrevious()->getMessage();
175 }
176 }
177 }
178
179 return $result;
180 }
181
182 /**
183 * Perform add/change/create operations on tables and fields in an optimized,
184 * non-interactive, mode using the original doctrine SchemaManager ->toSaveSql()
185 * method.
186 *
187 * @param string[] $statements The CREATE TABLE statements
188 * @param bool $createOnly Only perform changes that add fields or create tables
189 * @return array[] Error messages for statements that occurred during the installation procedure.
190 * @throws \Doctrine\DBAL\DBALException
191 * @throws \Doctrine\DBAL\Schema\SchemaException
192 * @throws \InvalidArgumentException
193 * @throws \RuntimeException
194 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\UnexpectedSignalReturnValueTypeException
195 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotException
196 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotReturnException
197 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\StatementException
198 */
199 public function install(array $statements, bool $createOnly = false): array
200 {
201 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
202 $this->parseCreateTableStatements($statements);
203 $result = [];
204
205 foreach ($connectionPool->getConnectionNames() as $connectionName) {
206 $connection = $connectionPool->getConnectionByName($connectionName);
207
208 $schemaDiff = $this->buildSchemaDiff($connectionName, false);
209
210 $schemaDiff->removedTables = [];
211 foreach ($schemaDiff->changedTables as $key => $changedTable) {
212 $schemaDiff->changedTables[$key]->removedColumns = [];
213 $schemaDiff->changedTables[$key]->removedIndexes = [];
214
215 // With partial ext_tables.sql files the SchemaManager is detecting
216 // existing columns as false positives for a column rename. In this
217 // context every rename is actually a new column.
218 foreach ($changedTable->renamedColumns as $columnName => $renamedColumn) {
219 $changedTable->addedColumns[$renamedColumn->getName()] = GeneralUtility::makeInstance(
220 Column::class,
221 $renamedColumn->getName(),
222 $renamedColumn->getType(),
223 array_diff_key($renamedColumn->toArray(), ['name', 'type'])
224 );
225 unset($changedTable->renamedColumns[$columnName]);
226 }
227
228 if ($createOnly) {
229 $schemaDiff->changedTables[$key]->changedColumns = [];
230 $schemaDiff->changedTables[$key]->renamedIndexes = [];
231 }
232 }
233
234 $statements = $schemaDiff->toSaveSql($connection->getDatabasePlatform());
235
236 foreach ($statements as $hash => $statement) {
237 try {
238 $connection->executeUpdate($statement);
239 $result[$statement] = '';
240 } catch (DBALException $e) {
241 $result[$statement] = $e->getPrevious()->getMessage();
242 }
243 }
244 }
245
246 return $result;
247 }
248
249 /**
250 * Import static data (INSERT statements)
251 *
252 * @param array $statements
253 * @param bool $truncate
254 * @return array
255 */
256 public function importStaticData(array $statements, bool $truncate = false): array
257 {
258 $result = [];
259 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
260 $insertStatements = [];
261
262 foreach ($statements as $statement) {
263 // Only handle insert statements and extract the table at the same time. Extracting
264 // the table name is required to perform the inserts on the right connection.
265 if (preg_match('/^INSERT\s+INTO\s+`?(\w+)`?(.*)/i', $statement, $matches)) {
266 list(, $tableName, $sqlFragment) = $matches;
267 $insertStatements[$tableName][] = sprintf(
268 'INSERT INTO %s %s',
269 $connectionPool->getConnectionForTable($tableName)->quoteIdentifier($tableName),
270 rtrim($sqlFragment, ';')
271 );
272 }
273 }
274
275 foreach ($insertStatements as $tableName => $perTableStatements) {
276 $connection = $connectionPool->getConnectionForTable($tableName);
277
278 if ($truncate) {
279 $connection->truncate($tableName);
280 }
281
282 foreach ((array)$perTableStatements as $statement) {
283 try {
284 $connection->executeUpdate($statement);
285 $result[$statement] = '';
286 } catch (DBALException $e) {
287 $result[$statement] = $e->getPrevious()->getMessage();
288 }
289 }
290 }
291
292 return $result;
293 }
294
295 /**
296 * If the schema is not for the Default connection remove all tables from the schema
297 * that have no mapping in the TYPO3 configuration. This avoids update suggestions
298 * for tables that are in the database but have no direct relation to the TYPO3 instance.
299 *
300 * @param string $connectionName
301 * @param bool $renameUnused
302 * @return \Doctrine\DBAL\Schema\SchemaDiff
303 * @throws \Doctrine\DBAL\DBALException
304 * @throws \InvalidArgumentException
305 */
306 protected function buildSchemaDiff(string $connectionName, bool $renameUnused = true): SchemaDiff
307 {
308 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionByName($connectionName);
309
310 // Build the schema definitions
311 $fromSchema = $connection->getSchemaManager()->createSchema();
312 $toSchema = $this->buildExpectedSchemaDefinitions($connectionName);
313
314 // Add current table options to the fromSchema
315 $tableOptions = $this->getTableOptions($connection, $fromSchema->getTableNames());
316 foreach ($fromSchema->getTables() as $table) {
317 $tableName = $table->getName();
318 if (!array_key_exists($tableName, $tableOptions)) {
319 continue;
320 }
321 foreach ($tableOptions[$tableName] as $optionName => $optionValue) {
322 $table->addOption($optionName, $optionValue);
323 }
324 }
325
326 // Build SchemaDiff and handle renames of tables and colums
327 $comparator = GeneralUtility::makeInstance(Comparator::class);
328 $schemaDiff = $comparator->compare($fromSchema, $toSchema);
329
330 if ($renameUnused) {
331 $schemaDiff = $this->migrateUnprefixedRemovedTablesToRenames($schemaDiff, $connection);
332 $schemaDiff = $this->migrateUnprefixedRemovedFieldsToRenames($schemaDiff, $connection);
333 }
334
335 // All tables in the default connection are managed by TYPO3
336 if ($connectionName === ConnectionPool::DEFAULT_CONNECTION_NAME) {
337 return $schemaDiff;
338 }
339
340 // If there are no mapped tables return a SchemaDiff without any changes
341 // to avoid update suggestions for tables not related to TYPO3.
342 if (empty($GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'])
343 || !is_array($GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'])
344 ) {
345 $schemaDiff = GeneralUtility::makeInstance(SchemaDiff::class, [], [], [], $fromSchema);
346
347 return $schemaDiff;
348 }
349
350 // Collect the table names that have been mapped to this connection.
351 $tablesForConnection = array_keys(
352 array_filter(
353 $GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'],
354 function ($tableConnectionName) use ($connectionName) {
355 return $tableConnectionName === $connectionName;
356 }
357 )
358 );
359
360 // Remove all tables that are not assigned to this connection from the diff
361 $schemaDiff->newTables = $this->removeUnrelatedTables($schemaDiff->newTables, $tablesForConnection);
362 $schemaDiff->changedTables = $this->removeUnrelatedTables($schemaDiff->changedTables, $tablesForConnection);
363 $schemaDiff->removedTables = $this->removeUnrelatedTables($schemaDiff->removedTables, $tablesForConnection);
364
365 return $schemaDiff;
366 }
367
368 /**
369 * Build the expected schema definitons from raw SQL statements.
370 *
371 * @param string $connectionName
372 * @return \Doctrine\DBAL\Schema\Schema
373 * @throws \Doctrine\DBAL\DBALException
374 * @throws \InvalidArgumentException
375 */
376 protected function buildExpectedSchemaDefinitions(string $connectionName): Schema
377 {
378 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
379 $connection = $connectionPool->getConnectionByName($connectionName);
380
381 $tablesForConnection = [];
382 foreach ($this->tables as $table) {
383 $tableName = $table->getName();
384
385 // Skip tables for a different connection
386 if ($connectionName !== $this->getConnectionNameForTable($tableName)) {
387 continue;
388 }
389
390 if (!array_key_exists($tableName, $tablesForConnection)) {
391 $tablesForConnection[$tableName] = $table;
392 continue;
393 }
394
395 // Merge multiple table definitions. Later definitions overrule identical
396 // columns, indexes and foreign_keys. Order of definitions is based on
397 // extension load order.
398 $currentTableDefinition = $tablesForConnection[$tableName];
399 $tablesForConnection[$tableName] = GeneralUtility::makeInstance(
400 Table::class,
401 $tableName,
402 array_merge($currentTableDefinition->getColumns(), $table->getColumns()),
403 array_merge($currentTableDefinition->getIndexes(), $table->getIndexes()),
404 array_merge($currentTableDefinition->getForeignKeys(), $table->getForeignKeys()),
405 0,
406 array_merge($currentTableDefinition->getOptions(), $table->getOptions())
407 );
408 }
409
410 $tablesForConnection = $this->transformTablesForDatabasePlatform($tablesForConnection, $connection);
411
412 $schemaConfig = GeneralUtility::makeInstance(SchemaConfig::class);
413 $schemaConfig->setName($connection->getDatabase());
414
415 return GeneralUtility::makeInstance(Schema::class, $tablesForConnection, [], $schemaConfig);
416 }
417
418 /**
419 * Parse CREATE TABLE statements into Doctrine Table objects.
420 *
421 * @param string[] $statements The SQL CREATE TABLE statements
422 * @throws \Doctrine\DBAL\Schema\SchemaException
423 * @throws \InvalidArgumentException
424 * @throws \RuntimeException
425 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\StatementException
426 * @throws \TYPO3\CMS\Core\Database\Schema\Exception\UnexpectedSignalReturnValueTypeException
427 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotException
428 * @throws \TYPO3\CMS\Extbase\SignalSlot\Exception\InvalidSlotReturnException
429 */
430 protected function parseCreateTableStatements(array $statements)
431 {
432 $tables = [];
433 foreach ($statements as $statement) {
434 $createTableParser = GeneralUtility::makeInstance(Parser::class, $statement);
435
436 // We need to keep multiple table definitions at this point so
437 // that Extensions can modify existing tables.
438 try {
439 $tables[] = $createTableParser->parse();
440 } catch (\TYPO3\CMS\Core\Database\Schema\Exception\StatementException $statementException) {
441 // Enrich the error message with the full invalid statement
442 throw new \TYPO3\CMS\Core\Database\Schema\Exception\StatementException(
443 $statementException->getMessage() . ' in statement ' . $statement,
444 1476171315,
445 $statementException
446 );
447 }
448 }
449
450 // Flatten the array of arrays by one level
451 $this->tables = array_merge(...$tables);
452 }
453
454 /**
455 * Extract the update suggestions (SQL statements) for newly added tables
456 * from the complete schema diff.
457 *
458 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
459 * @param \TYPO3\CMS\Core\Database\Connection $connection
460 * @return array
461 * @throws \InvalidArgumentException
462 */
463 protected function getNewTableUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
464 {
465 // Build a new schema diff that only contains added tables
466 $addTableSchemaDiff = GeneralUtility::makeInstance(
467 SchemaDiff::class,
468 $schemaDiff->newTables,
469 [],
470 [],
471 $schemaDiff->fromSchema
472 );
473
474 $statements = $addTableSchemaDiff->toSql($connection->getDatabasePlatform());
475
476 return ['create_table' => $this->calculateUpdateSuggestionsHashes($statements)];
477 }
478
479 /**
480 * Extract the update suggestions (SQL statements) for newly added fields
481 * from the complete schema diff.
482 *
483 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
484 * @param \TYPO3\CMS\Core\Database\Connection $connection
485 * @return array
486 * @throws \Doctrine\DBAL\Schema\SchemaException
487 * @throws \InvalidArgumentException
488 */
489 protected function getNewFieldUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
490 {
491 $changedTables = [];
492
493 foreach ($schemaDiff->changedTables as $index => $changedTable) {
494 if (count($changedTable->addedColumns) !== 0) {
495 // Treat each added column with a new diff to get a dedicated suggestions
496 // just for this single column.
497 foreach ($changedTable->addedColumns as $addedColumn) {
498 $changedTables[$index . ':tbl_' . $addedColumn->getName()] = GeneralUtility::makeInstance(
499 TableDiff::class,
500 $changedTable->getName($connection->getDatabasePlatform()),
501 [$addedColumn],
502 [],
503 [],
504 [],
505 [],
506 [],
507 $schemaDiff->fromSchema->getTable($changedTable->name)
508 );
509 }
510 }
511
512 if (count($changedTable->addedIndexes) !== 0) {
513 // Treat each added index with a new diff to get a dedicated suggestions
514 // just for this index.
515 foreach ($changedTable->addedIndexes as $addedIndex) {
516 $changedTables[$index . ':idx_' . $addedIndex->getName()] = GeneralUtility::makeInstance(
517 TableDiff::class,
518 $changedTable->getName($connection->getDatabasePlatform()),
519 [],
520 [],
521 [],
522 [$addedIndex],
523 [],
524 [],
525 $schemaDiff->fromSchema->getTable($changedTable->name)
526 );
527 }
528 }
529
530 if (count($changedTable->addedForeignKeys) !== 0) {
531 // Treat each added foreign key with a new diff to get a dedicated suggestions
532 // just for this foreign key.
533 foreach ($changedTable->addedForeignKeys as $addedForeignKey) {
534 $fkIndex = $index . ':fk_' . $addedForeignKey->getName();
535 $changedTables[$fkIndex] = GeneralUtility::makeInstance(
536 TableDiff::class,
537 $changedTable->getName($connection->getDatabasePlatform()),
538 [],
539 [],
540 [],
541 [],
542 [],
543 [],
544 $schemaDiff->fromSchema->getTable($changedTable->name)
545 );
546 $changedTables[$fkIndex]->addedForeignKeys = [$addedForeignKey];
547 }
548 }
549 }
550
551 // Build a new schema diff that only contains added fields
552 $addFieldSchemaDiff = GeneralUtility::makeInstance(
553 SchemaDiff::class,
554 [],
555 $changedTables,
556 [],
557 $schemaDiff->fromSchema
558 );
559
560 $statements = $addFieldSchemaDiff->toSql($connection->getDatabasePlatform());
561
562 return ['add' => $this->calculateUpdateSuggestionsHashes($statements)];
563 }
564
565 /**
566 * Extract update suggestions (SQL statements) for changed options (like ENGINE)
567 * from the complete schema diff.
568 *
569 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
570 * @param \TYPO3\CMS\Core\Database\Connection $connection
571 * @return array
572 * @throws \Doctrine\DBAL\Schema\SchemaException
573 * @throws \InvalidArgumentException
574 */
575 protected function getChangedTableOptions(SchemaDiff $schemaDiff, Connection $connection): array
576 {
577 $updateSuggestions = [];
578
579 foreach ($schemaDiff->changedTables as $tableDiff) {
580 // Skip processing if this is the base TableDiff class or hasn no table options set.
581 if (!$tableDiff instanceof TableDiff || count($tableDiff->getTableOptions()) === 0) {
582 continue;
583 }
584
585 $tableOptions = $tableDiff->getTableOptions();
586 $tableOptionsDiff = GeneralUtility::makeInstance(
587 TableDiff::class,
588 $tableDiff->name,
589 [],
590 [],
591 [],
592 [],
593 [],
594 [],
595 $tableDiff->fromTable
596 );
597 $tableOptionsDiff->setTableOptions($tableOptions);
598
599 $tableOptionsSchemaDiff = GeneralUtility::makeInstance(
600 SchemaDiff::class,
601 [],
602 [$tableOptionsDiff],
603 [],
604 $schemaDiff->fromSchema
605 );
606
607 $statements = $tableOptionsSchemaDiff->toSaveSql($connection->getDatabasePlatform());
608 foreach ($statements as $statement) {
609 $updateSuggestions['change'][md5($statement)] = $statement;
610 }
611 }
612
613 return $updateSuggestions;
614 }
615
616 /**
617 * Extract update suggestions (SQL statements) for changed fields
618 * from the complete schema diff.
619 *
620 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
621 * @param \TYPO3\CMS\Core\Database\Connection $connection
622 * @return array
623 * @throws \Doctrine\DBAL\Schema\SchemaException
624 * @throws \InvalidArgumentException
625 */
626 protected function getChangedFieldUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
627 {
628 $databasePlatform = $connection->getDatabasePlatform();
629 $updateSuggestions = [];
630
631 foreach ($schemaDiff->changedTables as $index => $changedTable) {
632 if (count($changedTable->changedColumns) !== 0) {
633 // Treat each changed column with a new diff to get a dedicated suggestions
634 // just for this single column.
635 $fromTable = $schemaDiff->fromSchema->getTable($changedTable->name);
636 foreach ($changedTable->changedColumns as $changedColumn) {
637 // Field has been renamed and will be handled separately
638 if ($changedColumn->getOldColumnName()->getName() !== $changedColumn->column->getName()) {
639 continue;
640 }
641
642 // Get the current SQL declaration for the column
643 $currentColumn = $fromTable->getColumn($changedColumn->getOldColumnName()->getName());
644 $currentDeclaration = $databasePlatform->getColumnDeclarationSQL(
645 $currentColumn->getQuotedName($connection->getDatabasePlatform()),
646 $currentColumn->toArray()
647 );
648
649 // Build a dedicated diff just for the current column
650 $tableDiff = GeneralUtility::makeInstance(
651 TableDiff::class,
652 $changedTable->getName($connection->getDatabasePlatform()),
653 [],
654 [$changedColumn],
655 [],
656 [],
657 [],
658 [],
659 $schemaDiff->fromSchema->getTable($changedTable->name)
660 );
661
662 $temporarySchemaDiff = GeneralUtility::makeInstance(
663 SchemaDiff::class,
664 [],
665 [$tableDiff],
666 [],
667 $schemaDiff->fromSchema
668 );
669
670 $statements = $temporarySchemaDiff->toSql($databasePlatform);
671 foreach ($statements as $statement) {
672 $updateSuggestions['change'][md5($statement)] = $statement;
673 $updateSuggestions['change_currentValue'][md5($statement)] = $currentDeclaration;
674 }
675 }
676 }
677
678 // Treat each changed index with a new diff to get a dedicated suggestions
679 // just for this index.
680 if (count($changedTable->changedIndexes) !== 0) {
681 foreach ($changedTable->renamedIndexes as $key => $changedIndex) {
682 $indexDiff = GeneralUtility::makeInstance(
683 TableDiff::class,
684 $changedTable->getName($connection->getDatabasePlatform()),
685 [],
686 [],
687 [],
688 [],
689 [$changedIndex],
690 [],
691 $schemaDiff->fromSchema->getTable($changedTable->name)
692 );
693
694 $temporarySchemaDiff = GeneralUtility::makeInstance(
695 SchemaDiff::class,
696 [],
697 [$indexDiff],
698 [],
699 $schemaDiff->fromSchema
700 );
701
702 $statements = $temporarySchemaDiff->toSql($databasePlatform);
703 foreach ($statements as $statement) {
704 $updateSuggestions['change'][md5($statement)] = $statement;
705 }
706 }
707 }
708
709 // Treat renamed indexes as a field change as it's a simple rename operation
710 if (count($changedTable->renamedIndexes) !== 0) {
711 // Create a base table diff without any changes, there's no constructor
712 // argument to pass in renamed columns.
713 $tableDiff = GeneralUtility::makeInstance(
714 TableDiff::class,
715 $changedTable->getName($connection->getDatabasePlatform()),
716 [],
717 [],
718 [],
719 [],
720 [],
721 [],
722 $schemaDiff->fromSchema->getTable($changedTable->name)
723 );
724
725 // Treat each renamed index with a new diff to get a dedicated suggestions
726 // just for this index.
727 foreach ($changedTable->renamedIndexes as $key => $renamedIndex) {
728 $indexDiff = clone $tableDiff;
729 $indexDiff->renamedIndexes = [$key => $renamedIndex];
730
731 $temporarySchemaDiff = GeneralUtility::makeInstance(
732 SchemaDiff::class,
733 [],
734 [$indexDiff],
735 [],
736 $schemaDiff->fromSchema
737 );
738
739 $statements = $temporarySchemaDiff->toSql($databasePlatform);
740 foreach ($statements as $statement) {
741 $updateSuggestions['change'][md5($statement)] = $statement;
742 }
743 }
744 }
745
746 // Treat each changed foreign key with a new diff to get a dedicated suggestions
747 // just for this foreign key.
748 if (count($changedTable->changedForeignKeys) !== 0) {
749 $tableDiff = GeneralUtility::makeInstance(
750 TableDiff::class,
751 $changedTable->getName($connection->getDatabasePlatform()),
752 [],
753 [],
754 [],
755 [],
756 [],
757 [],
758 $schemaDiff->fromSchema->getTable($changedTable->name)
759 );
760
761 foreach ($changedTable->changedForeignKeys as $changedForeignKey) {
762 $foreignKeyDiff = clone $tableDiff;
763 $foreignKeyDiff->changedForeignKeys = [$changedForeignKey];
764
765 $temporarySchemaDiff = GeneralUtility::makeInstance(
766 SchemaDiff::class,
767 [],
768 [$foreignKeyDiff],
769 [],
770 $schemaDiff->fromSchema
771 );
772
773 $statements = $temporarySchemaDiff->toSql($databasePlatform);
774 foreach ($statements as $statement) {
775 $updateSuggestions['change'][md5($statement)] = $statement;
776 }
777 }
778 }
779 }
780
781 return $updateSuggestions;
782 }
783
784 /**
785 * Extract update suggestions (SQL statements) for tables that are
786 * no longer present in the expected schema from the schema diff.
787 * In this case the update suggestions are renames of the tables
788 * with a prefix to mark them for deletion in a second sweep.
789 *
790 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
791 * @param \TYPO3\CMS\Core\Database\Connection $connection
792 * @return array
793 * @throws \Doctrine\DBAL\Schema\SchemaException
794 * @throws \InvalidArgumentException
795 */
796 protected function getUnusedTableUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
797 {
798 $updateSuggestions = [];
799 foreach ($schemaDiff->changedTables as $tableDiff) {
800 // Skip tables that are not being renamed or where the new name isn't prefixed
801 // with the deletion marker.
802 if ($tableDiff->getNewName() === false
803 || !StringUtility::beginsWith($tableDiff->getNewName()->getName(), $this->deletedPrefix)
804 ) {
805 continue;
806 }
807 // Build a new schema diff that only contains this table
808 $changedFieldDiff = GeneralUtility::makeInstance(
809 SchemaDiff::class,
810 [],
811 [$tableDiff],
812 [],
813 $schemaDiff->fromSchema
814 );
815
816 $statements = $changedFieldDiff->toSql($connection->getDatabasePlatform());
817
818 foreach ($statements as $statement) {
819 $updateSuggestions['change_table'][md5($statement)] = $statement;
820 }
821 $updateSuggestions['tables_count'][md5($statements[0])] = $this->getTableRecordCount($tableDiff->name);
822 }
823
824 return $updateSuggestions;
825 }
826
827 /**
828 * Extract update suggestions (SQL statements) for fields that are
829 * no longer present in the expected schema from the schema diff.
830 * In this case the update suggestions are renames of the fields
831 * with a prefix to mark them for deletion in a second sweep.
832 *
833 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
834 * @param \TYPO3\CMS\Core\Database\Connection $connection
835 * @return array
836 * @throws \Doctrine\DBAL\Schema\SchemaException
837 * @throws \InvalidArgumentException
838 */
839 protected function getUnusedFieldUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
840 {
841 $changedTables = [];
842
843 foreach ($schemaDiff->changedTables as $index => $changedTable) {
844 if (count($changedTable->changedColumns) === 0) {
845 continue;
846 }
847
848 // Treat each changed column with a new diff to get a dedicated suggestions
849 // just for this single column.
850 foreach ($changedTable->changedColumns as $changedColumn) {
851 // Field has not been renamed
852 if ($changedColumn->getOldColumnName()->getName() === $changedColumn->column->getName()) {
853 continue;
854 }
855
856 $changedTables[$index . ':' . $changedColumn->column->getName()] = GeneralUtility::makeInstance(
857 TableDiff::class,
858 $changedTable->getName($connection->getDatabasePlatform()),
859 [],
860 [$changedColumn],
861 [],
862 [],
863 [],
864 [],
865 $schemaDiff->fromSchema->getTable($changedTable->name)
866 );
867 }
868 }
869
870 // Build a new schema diff that only contains unused fields
871 $changedFieldDiff = GeneralUtility::makeInstance(
872 SchemaDiff::class,
873 [],
874 $changedTables,
875 [],
876 $schemaDiff->fromSchema
877 );
878
879 $statements = $changedFieldDiff->toSql($connection->getDatabasePlatform());
880
881 return ['change' => $this->calculateUpdateSuggestionsHashes($statements)];
882 }
883
884 /**
885 * Extract update suggestions (SQL statements) for fields that can
886 * be removed from the complete schema diff.
887 * Fields that can be removed have been prefixed in a previous run
888 * of the schema migration.
889 *
890 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
891 * @param \TYPO3\CMS\Core\Database\Connection $connection
892 * @return array
893 * @throws \Doctrine\DBAL\Schema\SchemaException
894 * @throws \InvalidArgumentException
895 */
896 protected function getDropFieldUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
897 {
898 $changedTables = [];
899
900 foreach ($schemaDiff->changedTables as $index => $changedTable) {
901 if (count($changedTable->removedColumns) !== 0) {
902 // Treat each changed column with a new diff to get a dedicated suggestions
903 // just for this single column.
904 foreach ($changedTable->removedColumns as $removedColumn) {
905 $changedTables[$index . ':tbl_' . $removedColumn->getName()] = GeneralUtility::makeInstance(
906 TableDiff::class,
907 $changedTable->getName($connection->getDatabasePlatform()),
908 [],
909 [],
910 [$removedColumn],
911 [],
912 [],
913 [],
914 $schemaDiff->fromSchema->getTable($changedTable->name)
915 );
916 }
917 }
918
919 if (count($changedTable->removedIndexes) !== 0) {
920 // Treat each removed index with a new diff to get a dedicated suggestions
921 // just for this index.
922 foreach ($changedTable->removedIndexes as $removedIndex) {
923 $changedTables[$index . ':idx_' . $removedIndex->getName()] = GeneralUtility::makeInstance(
924 TableDiff::class,
925 $changedTable->getName($connection->getDatabasePlatform()),
926 [],
927 [],
928 [],
929 [],
930 [],
931 [$removedIndex],
932 $schemaDiff->fromSchema->getTable($changedTable->name)
933 );
934 }
935 }
936
937 if (count($changedTable->removedForeignKeys) !== 0) {
938 // Treat each removed foreign key with a new diff to get a dedicated suggestions
939 // just for this foreign key.
940 foreach ($changedTable->removedForeignKeys as $removedForeignKey) {
941 $fkIndex = $index . ':fk_' . $removedForeignKey->getName();
942 $changedTables[$fkIndex] = GeneralUtility::makeInstance(
943 TableDiff::class,
944 $changedTable->getName($connection->getDatabasePlatform()),
945 [],
946 [],
947 [],
948 [],
949 [],
950 [],
951 $schemaDiff->fromSchema->getTable($changedTable->name)
952 );
953 $changedTables[$fkIndex]->removedForeignKeys = [$removedForeignKey];
954 }
955 }
956 }
957
958 // Build a new schema diff that only contains removable fields
959 $removedFieldDiff = GeneralUtility::makeInstance(
960 SchemaDiff::class,
961 [],
962 $changedTables,
963 [],
964 $schemaDiff->fromSchema
965 );
966
967 $statements = $removedFieldDiff->toSql($connection->getDatabasePlatform());
968
969 return ['drop' => $this->calculateUpdateSuggestionsHashes($statements)];
970 }
971
972 /**
973 * Extract update suggestions (SQL statements) for tables that can
974 * be removed from the complete schema diff.
975 * Tables that can be removed have been prefixed in a previous run
976 * of the schema migration.
977 *
978 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
979 * @param \TYPO3\CMS\Core\Database\Connection $connection
980 * @return array
981 * @throws \Doctrine\DBAL\Schema\SchemaException
982 * @throws \InvalidArgumentException
983 */
984 protected function getDropTableUpdateSuggestions(SchemaDiff $schemaDiff, Connection $connection): array
985 {
986 $updateSuggestions = [];
987 foreach ($schemaDiff->removedTables as $removedTable) {
988 // Build a new schema diff that only contains this table
989 $tableDiff = GeneralUtility::makeInstance(
990 SchemaDiff::class,
991 [],
992 [],
993 [$removedTable],
994 $schemaDiff->fromSchema
995 );
996
997 $statements = $tableDiff->toSql($connection->getDatabasePlatform());
998 foreach ($statements as $statement) {
999 $updateSuggestions['drop_table'][md5($statement)] = $statement;
1000 }
1001
1002 // Only store the record count for this table for the first statement,
1003 // assuming that this is the actual DROP TABLE statement.
1004 $updateSuggestions['tables_count'][md5($statements[0])] = $this->getTableRecordCount(
1005 $removedTable->getName()
1006 );
1007 }
1008
1009 return $updateSuggestions;
1010 }
1011
1012 /**
1013 * Move tables to be removed that are not prefixed with the deleted prefix to the list
1014 * of changed tables and set a new prefixed name.
1015 * Without this help the Doctrine SchemaDiff has no idea if a table has been renamed and
1016 * performs a drop of the old table and creates a new table, which leads to all data in
1017 * the old table being lost.
1018 *
1019 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
1020 * @param Connection $connection
1021 * @return \Doctrine\DBAL\Schema\SchemaDiff
1022 * @throws \InvalidArgumentException
1023 */
1024 protected function migrateUnprefixedRemovedTablesToRenames(
1025 SchemaDiff $schemaDiff,
1026 Connection $connection
1027 ): SchemaDiff {
1028 foreach ($schemaDiff->removedTables as $index => $removedTable) {
1029 if (StringUtility::beginsWith($removedTable->getName(), $this->deletedPrefix)) {
1030 continue;
1031 }
1032 $tableDiff = GeneralUtility::makeInstance(
1033 TableDiff::class,
1034 $removedTable->getQuotedName($connection->getDatabasePlatform()),
1035 $addedColumns = [],
1036 $changedColumns = [],
1037 $removedColumns = [],
1038 $addedIndexes = [],
1039 $changedIndexes = [],
1040 $removedIndexes = [],
1041 $fromTable = $removedTable
1042 );
1043
1044 $tableDiff->newName = $this->deletedPrefix . $removedTable->getName();
1045 $schemaDiff->changedTables[$index] = $tableDiff;
1046 unset($schemaDiff->removedTables[$index]);
1047 }
1048
1049 return $schemaDiff;
1050 }
1051
1052 /**
1053 * Scan the list of changed tables for fields that are going to be dropped. If
1054 * the name of the field does not start with the deleted prefix mark the column
1055 * for a rename instead of a drop operation.
1056 *
1057 * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
1058 * @return \Doctrine\DBAL\Schema\SchemaDiff
1059 * @throws \InvalidArgumentException
1060 */
1061 protected function migrateUnprefixedRemovedFieldsToRenames(
1062 SchemaDiff $schemaDiff,
1063 Connection $connection
1064 ): SchemaDiff {
1065 foreach ($schemaDiff->changedTables as $tableIndex => $changedTable) {
1066 if (count($changedTable->removedColumns) === 0) {
1067 continue;
1068 }
1069
1070 foreach ($changedTable->removedColumns as $columnIndex => $removedColumn) {
1071 if (StringUtility::beginsWith($removedColumn->getName(), $this->deletedPrefix)) {
1072 continue;
1073 }
1074
1075 // Build a new column object with the same properties as the removed column
1076 $renamedColumn = new Column(
1077 $connection->quoteIdentifier($this->deletedPrefix . $removedColumn->getName()),
1078 $removedColumn->getType(),
1079 array_diff_key($removedColumn->toArray(), ['name', 'type'])
1080 );
1081
1082 // Build the diff object for the column to rename
1083 $columnDiff = GeneralUtility::makeInstance(
1084 ColumnDiff::class,
1085 $removedColumn->getQuotedName($connection->getDatabasePlatform()),
1086 $renamedColumn,
1087 $changedProperties = [],
1088 $removedColumn
1089 );
1090
1091 // Add the column with the required rename information to the changed column list
1092 $schemaDiff->changedTables[$tableIndex]->changedColumns[$columnIndex] = $columnDiff;
1093
1094 // Remove the column from the list of columns to be dropped
1095 unset($schemaDiff->changedTables[$tableIndex]->removedColumns[$columnIndex]);
1096 }
1097 }
1098
1099 return $schemaDiff;
1100 }
1101
1102 /**
1103 * Return the amount of records in the given table.
1104 *
1105 * @param string $tableName
1106 * @return int
1107 * @throws \InvalidArgumentException
1108 */
1109 protected function getTableRecordCount(string $tableName): int
1110 {
1111 return GeneralUtility::makeInstance(ConnectionPool::class)
1112 ->getConnectionForTable($tableName)
1113 ->count('*', $tableName, []);
1114 }
1115
1116 /**
1117 * Determine the connection name for a table
1118 *
1119 * @param string $tableName
1120 * @return string
1121 * @throws \InvalidArgumentException
1122 */
1123 protected function getConnectionNameForTable(string $tableName): string
1124 {
1125 $connectionNames = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionNames();
1126
1127 if (array_key_exists($tableName, (array)$GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'])) {
1128 return in_array($GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'][$tableName], $connectionNames, true)
1129 ? $GLOBALS['TYPO3_CONF_VARS']['DB']['TableMapping'][$tableName]
1130 : ConnectionPool::DEFAULT_CONNECTION_NAME;
1131 }
1132
1133 return ConnectionPool::DEFAULT_CONNECTION_NAME;
1134 }
1135
1136 /**
1137 * Replace the array keys with a md5 sum of the actual SQL statement
1138 *
1139 * @param string[] $statements
1140 * @return string[]
1141 */
1142 protected function calculateUpdateSuggestionsHashes(array $statements): array
1143 {
1144 return array_combine(array_map('md5', $statements), $statements);
1145 }
1146
1147 /**
1148 * Helper for buildSchemaDiff to filter an array of TableDiffs against a list of valid table names.
1149 *
1150 * @param TableDiff[]|Table[] $tableDiffs
1151 * @param string[] $validTableNames
1152 * @return TableDiff[]
1153 * @throws \InvalidArgumentException
1154 */
1155 protected function removeUnrelatedTables(array $tableDiffs, array $validTableNames): array
1156 {
1157 return array_filter(
1158 $tableDiffs,
1159 function ($table) use ($validTableNames) {
1160 if ($table instanceof Table) {
1161 $tableName = $table->getName();
1162 } else {
1163 $tableName = $table->newName ?: $table->name;
1164 }
1165
1166 // If the tablename has a deleted prefix strip it of before comparing
1167 // it against the list of valid table names so that drop operations
1168 // don't get removed.
1169 if (StringUtility::beginsWith($tableName, $this->deletedPrefix)) {
1170 $tableName = substr($tableName, strlen($this->deletedPrefix));
1171 }
1172 return in_array($tableName, $validTableNames, true)
1173 || in_array($this->deletedPrefix . $tableName, $validTableNames, true);
1174 }
1175 );
1176 }
1177
1178 /**
1179 * Transform the table information to conform to specific
1180 * requirements of different database platforms like removing
1181 * the index substring length for Non-MySQL Platforms.
1182 *
1183 * @param Table[] $tables
1184 * @param \TYPO3\CMS\Core\Database\Connection $connection
1185 * @return Table[]
1186 * @throws \InvalidArgumentException
1187 */
1188 protected function transformTablesForDatabasePlatform(
1189 array $tables,
1190 Connection $connection
1191 ): array {
1192 foreach ($tables as &$table) {
1193 $indexes = [];
1194 foreach ($table->getIndexes() as $key => $index) {
1195 $indexName = $index->getName();
1196 // PostgreSQL requires index names to be unique per database/schema.
1197 if ($connection->getDatabasePlatform() instanceof PostgreSqlPlatform) {
1198 $indexName = $indexName . '_' . hash('crc32b', $table->getName() . '_' . $indexName);
1199 }
1200
1201 // Remove the length information from column names for indexes if required.
1202 $cleanedColumnNames = array_map(
1203 function (string $columnName) use ($connection) {
1204 if ($connection->getDatabasePlatform() instanceof MySqlPlatform) {
1205 // Returning the unquoted, unmodified version of the column name since
1206 // it can include the length information for BLOB/TEXT columns which
1207 // may not be quoted.
1208 return $columnName;
1209 }
1210
1211 return $connection->quoteIdentifier(preg_replace('/\(\d+\)$/', '', $columnName));
1212 },
1213 $index->getUnquotedColumns()
1214 );
1215
1216 $indexes[$key] = GeneralUtility::makeInstance(
1217 Index::class,
1218 $connection->quoteIdentifier($indexName),
1219 $cleanedColumnNames,
1220 $index->isUnique(),
1221 $index->isPrimary(),
1222 $index->getFlags(),
1223 $index->getOptions()
1224 );
1225 }
1226
1227 $table = GeneralUtility::makeInstance(
1228 Table::class,
1229 $table->getQuotedName($connection->getDatabasePlatform()),
1230 $table->getColumns(),
1231 $indexes,
1232 $table->getForeignKeys(),
1233 0,
1234 $table->getOptions()
1235 );
1236 }
1237
1238 return $tables;
1239 }
1240
1241 /**
1242 * Get COLLATION, ROW_FORMAT, COMMENT and ENGINE table options on MySQL connections.
1243 *
1244 * @param \TYPO3\CMS\Core\Database\Connection $connection
1245 * @param string[] $tableNames
1246 * @return array[]
1247 * @throws \InvalidArgumentException
1248 */
1249 protected function getTableOptions(Connection $connection, array $tableNames): array
1250 {
1251 $tableOptions = [];
1252 if (!StringUtility::beginsWith($connection->getServerVersion(), 'MySQL')) {
1253 foreach ($tableNames as $tableName) {
1254 $tableOptions[$tableName] = [];
1255 }
1256
1257 return $tableOptions;
1258 }
1259
1260 $queryBuilder = $connection->createQueryBuilder();
1261 $result = $queryBuilder
1262 ->select(
1263 'TABLE_NAME AS table',
1264 'ENGINE AS engine',
1265 'ROW_FORMAT AS row_format',
1266 'TABLE_COLLATION AS collate',
1267 'TABLE_COMMENT AS comment'
1268 )
1269 ->from('information_schema.TABLES')
1270 ->where(
1271 $queryBuilder->expr()->eq('TABLE_TYPE', $queryBuilder->quote('BASE TABLE')),
1272 $queryBuilder->expr()->eq('TABLE_SCHEMA', $queryBuilder->quote($connection->getDatabase()))
1273 )
1274 ->execute();
1275
1276 while ($row = $result->fetch()) {
1277 $index = $row['table'];
1278 unset($row['table']);
1279 $tableOptions[$index] = $row;
1280 }
1281
1282 return $tableOptions;
1283 }
1284 }