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