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