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