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