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