[TASK] Doctrine: Enable MySQL ENGINE table option in SchemaMigrator 58/49758/7
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Fri, 2 Sep 2016 13:17:35 +0000 (15:17 +0200)
committerAndreas Fernandez <typo3@scripting-base.de>
Sat, 3 Sep 2016 16:18:45 +0000 (18:18 +0200)
Listen to the AlterTable event from Doctrine to provide change information
about table options like the ENGINE to the SchemaMigrator.

Change-Id: Ia4a59503d1bf62493f9da84df7659f24c02a7636
Resolves: #77786
Releases: master
Reviewed-on: https://review.typo3.org/49758
Tested-by: Bamboo TYPO3com <info@typo3.com>
Reviewed-by: Wouter Wolters <typo3@wouterwolters.nl>
Tested-by: Wouter Wolters <typo3@wouterwolters.nl>
Reviewed-by: Andreas Fernandez <typo3@scripting-base.de>
Tested-by: Andreas Fernandez <typo3@scripting-base.de>
typo3/sysext/core/Classes/Database/ConnectionPool.php
typo3/sysext/core/Classes/Database/Schema/Comparator.php [new file with mode: 0644]
typo3/sysext/core/Classes/Database/Schema/EventListener/SchemaAlterTableListener.php [new file with mode: 0644]
typo3/sysext/core/Classes/Database/Schema/SchemaMigrator.php
typo3/sysext/core/Classes/Database/Schema/TableDiff.php [new file with mode: 0644]
typo3/sysext/core/Tests/Functional/Database/Fixtures/alterTableEngine.sql [new file with mode: 0644]
typo3/sysext/core/Tests/Functional/Database/Fixtures/newTable.sql
typo3/sysext/core/Tests/Functional/Database/Schema/SchemaMigratorTest.php

index 489ac01..16a8131 100644 (file)
@@ -20,6 +20,7 @@ use Doctrine\DBAL\DriverManager;
 use Doctrine\DBAL\Events;
 use Doctrine\DBAL\Types\Type;
 use TYPO3\CMS\Core\Database\Query\QueryBuilder;
+use TYPO3\CMS\Core\Database\Schema\EventListener\SchemaAlterTableListener;
 use TYPO3\CMS\Core\Database\Schema\EventListener\SchemaColumnDefinitionListener;
 use TYPO3\CMS\Core\Database\Schema\Types\EnumType;
 use TYPO3\CMS\Core\Database\Schema\Types\SetType;
@@ -169,6 +170,13 @@ class ConnectionPool
             GeneralUtility::makeInstance(SchemaColumnDefinitionListener::class)
         );
 
+        // Handler for adding custom database platform options to ALTER TABLE
+        // requests in the SchemaManager
+        $conn->getDatabasePlatform()->getEventManager()->addEventListener(
+            Events::onSchemaAlterTable,
+            GeneralUtility::makeInstance(SchemaAlterTableListener::class)
+        );
+
         return $conn;
     }
 
diff --git a/typo3/sysext/core/Classes/Database/Schema/Comparator.php b/typo3/sysext/core/Classes/Database/Schema/Comparator.php
new file mode 100644 (file)
index 0000000..8a09ef9
--- /dev/null
@@ -0,0 +1,72 @@
+<?php
+declare(strict_types=1);
+namespace TYPO3\CMS\Core\Database\Schema;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use Doctrine\DBAL\Schema\Table;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
+/**
+ * Compares two Schemas and returns an instance of SchemaDiff.
+ *
+ * @internal
+ */
+class Comparator extends \Doctrine\DBAL\Schema\Comparator
+{
+    /**
+     * Returns the difference between the tables $fromTable and $toTable.
+     *
+     * If there are no differences this method returns the boolean false.
+     *
+     * @param \Doctrine\DBAL\Schema\Table $fromTable
+     * @param \Doctrine\DBAL\Schema\Table $toTable
+     * @return bool|\Doctrine\DBAL\Schema\TableDiff|\TYPO3\CMS\Core\Database\Schema\TableDiff
+     * @throws \InvalidArgumentException
+     */
+    public function diffTable(Table $fromTable, Table $toTable)
+    {
+        $newTableOptions = array_merge($fromTable->getOptions(), $toTable->getOptions());
+        $optionDiff = array_diff_assoc($newTableOptions, $fromTable->getOptions());
+        $tableDifferences = parent::diffTable($fromTable, $toTable);
+
+        // No changed table options, return parent result
+        if (count($optionDiff) === 0) {
+            return $tableDifferences;
+        }
+
+        if ($tableDifferences === false) {
+            $tableDifferences = GeneralUtility::makeInstance(TableDiff::class, $fromTable->getName());
+            $tableDifferences->fromTable = $fromTable;
+        } else {
+            // Rebuild TableDiff with enhanced TYPO3 TableDiff class
+            $tableDifferences = GeneralUtility::makeInstance(
+                TableDiff::class,
+                $tableDifferences->name,
+                $tableDifferences->addedColumns,
+                $tableDifferences->changedColumns,
+                $tableDifferences->removedColumns,
+                $tableDifferences->addedIndexes,
+                $tableDifferences->changedIndexes,
+                $tableDifferences->removedIndexes,
+                $tableDifferences->fromTable
+            );
+        }
+
+        // Set the table options to be parsed in the AlterTable event.
+        $tableDifferences->setTableOptions($optionDiff);
+
+        return $tableDifferences;
+    }
+}
diff --git a/typo3/sysext/core/Classes/Database/Schema/EventListener/SchemaAlterTableListener.php b/typo3/sysext/core/Classes/Database/Schema/EventListener/SchemaAlterTableListener.php
new file mode 100644 (file)
index 0000000..3a9d8b2
--- /dev/null
@@ -0,0 +1,68 @@
+<?php
+declare(strict_types=1);
+
+namespace TYPO3\CMS\Core\Database\Schema\EventListener;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use Doctrine\DBAL\Event\SchemaAlterTableEventArgs;
+use Doctrine\DBAL\Platforms\MySqlPlatform;
+use TYPO3\CMS\Core\Database\Schema\TableDiff;
+
+/**
+ * Event listener to handle additional processing for table alterations.
+ */
+class SchemaAlterTableListener
+{
+    /**
+     * Listener for alter table events. This intercepts the building
+     * of ALTER TABLE statements and adds the required statements to
+     * change the ENGINE type on MySQL platforms if necessary.
+     *
+     * @param \Doctrine\DBAL\Event\SchemaAlterTableEventArgs $event
+     * @return bool
+     * @throws \Doctrine\DBAL\DBALException
+     */
+    public function onSchemaAlterTable(SchemaAlterTableEventArgs $event)
+    {
+        /** @var TableDiff $tableDiff */
+        $tableDiff = $event->getTableDiff();
+
+        // Original Doctrine TableDiff without table options, continue default processing
+        if (!$tableDiff instanceof TableDiff) {
+            return false;
+        }
+
+        // Table options are only supported on MySQL, continue default processing
+        if (!$event->getPlatform() instanceof MySqlPlatform) {
+            return false;
+        }
+
+        // No changes in table options, continue default processing
+        if (count($tableDiff->getTableOptions()) === 0) {
+            return false;
+        }
+
+        $quotedTableName = $tableDiff->getName($event->getPlatform())->getQuotedName($event->getPlatform());
+
+        // Add an ALTER TABLE statement to change the table engine to the list of statements.
+        if ($tableDiff->hasTableOption('engine')) {
+            $statement = 'ALTER TABLE ' . $quotedTableName . ' ENGINE = ' . $tableDiff->getTableOption('engine');
+            $event->addSql($statement);
+        }
+
+        // continue default processing for all other changes.
+        return false;
+    }
+}
index 2f589d3..1fac4bf 100644 (file)
@@ -20,13 +20,11 @@ use Doctrine\DBAL\Platforms\MySqlPlatform;
 use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
 use Doctrine\DBAL\Schema\Column;
 use Doctrine\DBAL\Schema\ColumnDiff;
-use Doctrine\DBAL\Schema\Comparator;
 use Doctrine\DBAL\Schema\Index;
 use Doctrine\DBAL\Schema\Schema;
 use Doctrine\DBAL\Schema\SchemaConfig;
 use Doctrine\DBAL\Schema\SchemaDiff;
 use Doctrine\DBAL\Schema\Table;
-use Doctrine\DBAL\Schema\TableDiff;
 use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Database\Schema\Parser\Parser;
@@ -89,7 +87,8 @@ class SchemaMigrator
                     ['add' => [], 'create_table' => [], 'change' => [], 'change_currentValue' => []],
                     $this->getNewFieldUpdateSuggestions($schemaDiff, $connection),
                     $this->getNewTableUpdateSuggestions($schemaDiff, $connection),
-                    $this->getChangedFieldUpdateSuggestions($schemaDiff, $connection)
+                    $this->getChangedFieldUpdateSuggestions($schemaDiff, $connection),
+                    $this->getChangedTableOptions($schemaDiff, $connection)
                 );
             } else {
                 $updateSuggestions[$connectionName] = array_merge_recursive(
@@ -312,6 +311,18 @@ class SchemaMigrator
         $fromSchema = $connection->getSchemaManager()->createSchema();
         $toSchema = $this->buildExpectedSchemaDefinitions($connectionName);
 
+        // Add current table options to the fromSchema
+        $tableOptions = $this->getTableOptions($connection, $fromSchema->getTableNames());
+        foreach ($fromSchema->getTables() as $table) {
+            $tableName = $table->getName();
+            if (!array_key_exists($tableName, $tableOptions)) {
+                continue;
+            }
+            foreach ($tableOptions[$tableName] as $optionName => $optionValue) {
+                $table->addOption($optionName, $optionValue);
+            }
+        }
+
         // Build SchemaDiff and handle renames of tables and colums
         $comparator = GeneralUtility::makeInstance(Comparator::class);
         $schemaDiff = $comparator->compare($fromSchema, $toSchema);
@@ -543,6 +554,57 @@ class SchemaMigrator
     }
 
     /**
+     * Extract update suggestions (SQL statements) for changed options (like ENGINE)
+     * from the complete schema diff.
+     *
+     * @param \Doctrine\DBAL\Schema\SchemaDiff $schemaDiff
+     * @param \TYPO3\CMS\Core\Database\Connection $connection
+     * @return array
+     * @throws \Doctrine\DBAL\Schema\SchemaException
+     * @throws \InvalidArgumentException
+     */
+    protected function getChangedTableOptions(SchemaDiff $schemaDiff, Connection $connection): array
+    {
+        $updateSuggestions = [];
+
+        foreach ($schemaDiff->changedTables as $tableDiff) {
+            // Skip processing if this is the base TableDiff class or hasn no table options set.
+            if (!$tableDiff instanceof TableDiff || count($tableDiff->getTableOptions()) === 0) {
+                continue;
+            }
+
+            $tableOptions = $tableDiff->getTableOptions();
+            $tableOptionsDiff = GeneralUtility::makeInstance(
+                TableDiff::class,
+                $tableDiff->name,
+                [],
+                [],
+                [],
+                [],
+                [],
+                [],
+                $tableDiff->fromTable
+            );
+            $tableOptionsDiff->setTableOptions($tableOptions);
+
+            $tableOptionsSchemaDiff = GeneralUtility::makeInstance(
+                SchemaDiff::class,
+                [],
+                [$tableOptionsDiff],
+                [],
+                $schemaDiff->fromSchema
+            );
+
+            $statements = $tableOptionsSchemaDiff->toSaveSql($connection->getDatabasePlatform());
+            foreach ($statements as $statement) {
+                $updateSuggestions['change'][md5($statement)] = $statement;
+            }
+        }
+
+        return $updateSuggestions;
+    }
+
+    /**
      * Extract update suggestions (SQL statements) for changed fields
      * from the complete schema diff.
      *
@@ -1136,6 +1198,7 @@ class SchemaMigrator
                             // may not be quoted.
                             return $columnName;
                         }
+
                         return $connection->quoteIdentifier(preg_replace('/\(\d+\)$/', '', $columnName));
                     },
                     $index->getUnquotedColumns()
@@ -1165,4 +1228,48 @@ class SchemaMigrator
 
         return $tables;
     }
+
+    /**
+     * Get COLLATION, ROW_FORMAT, COMMENT and ENGINE table options on MySQL connections.
+     *
+     * @param \TYPO3\CMS\Core\Database\Connection $connection
+     * @param string[] $tableNames
+     * @return array[]
+     * @throws \InvalidArgumentException
+     */
+    protected function getTableOptions(Connection $connection, array $tableNames): array
+    {
+        $tableOptions = [];
+        if (!StringUtility::beginsWith($connection->getServerVersion(), 'MySQL')) {
+            foreach ($tableNames as $tableName) {
+                $tableOptions[$tableName] = [];
+            }
+
+            return $tableOptions;
+        }
+
+        $queryBuilder = $connection->createQueryBuilder();
+        $result = $queryBuilder
+            ->select(
+                'TABLE_NAME AS table',
+                'ENGINE AS engine',
+                'ROW_FORMAT AS row_format',
+                'TABLE_COLLATION AS collate',
+                'TABLE_COMMENT AS comment'
+            )
+            ->from('information_schema.TABLES')
+            ->where(
+                $queryBuilder->expr()->eq('TABLE_TYPE', $queryBuilder->quote('BASE TABLE')),
+                $queryBuilder->expr()->eq('TABLE_SCHEMA', $queryBuilder->quote($connection->getDatabase()))
+            )
+            ->execute();
+
+        while ($row = $result->fetch()) {
+            $index = $row['table'];
+            unset($row['table']);
+            $tableOptions[$index] = $row;
+        }
+
+        return $tableOptions;
+    }
 }
diff --git a/typo3/sysext/core/Classes/Database/Schema/TableDiff.php b/typo3/sysext/core/Classes/Database/Schema/TableDiff.php
new file mode 100644 (file)
index 0000000..f0ac5e4
--- /dev/null
@@ -0,0 +1,79 @@
+<?php
+declare(strict_types=1);
+namespace TYPO3\CMS\Core\Database\Schema;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+/**
+ * Helper methods to handle raw SQL input and transform it into individual statements
+ * for further processing.
+ *
+ * @internal
+ */
+class TableDiff extends \Doctrine\DBAL\Schema\TableDiff
+{
+    /**
+     * Platform specific table options
+     *
+     * @var array
+     */
+    protected $tableOptions = [];
+
+    /**
+     * Getter for table options.
+     *
+     * @return array
+     */
+    public function getTableOptions(): array
+    {
+        return $this->tableOptions;
+    }
+
+    /**
+     * Setter for table options
+     *
+     * @param array $tableOptions
+     * @return \TYPO3\CMS\Core\Database\Schema\TableDiff
+     */
+    public function setTableOptions(array $tableOptions): self
+    {
+        $this->tableOptions = $tableOptions;
+
+        return $this;
+    }
+
+    /**
+     * Check if a table options has been set.
+     *
+     * @param string $optionName
+     * @return bool
+     */
+    public function hasTableOption(string $optionName): bool
+    {
+        return array_key_exists($optionName, $this->tableOptions);
+    }
+
+    /**
+     * @param string $optionName
+     * @return string
+     */
+    public function getTableOption(string $optionName): string
+    {
+        if ($this->hasTableOption($optionName)) {
+            return (string)$this->tableOptions[$optionName];
+        }
+
+        return '';
+    }
+}
diff --git a/typo3/sysext/core/Tests/Functional/Database/Fixtures/alterTableEngine.sql b/typo3/sysext/core/Tests/Functional/Database/Fixtures/alterTableEngine.sql
new file mode 100644 (file)
index 0000000..8b61a94
--- /dev/null
@@ -0,0 +1,10 @@
+CREATE TABLE a_test_table (
+       uid     INT(11) UNSIGNED                NOT NULL AUTO_INCREMENT,
+       pid     INT(11) UNSIGNED DEFAULT '0'    NOT NULL,
+       tstamp  INT(11) UNSIGNED DEFAULT '0'    NOT NULL,
+       hidden  TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL,
+       deleted TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL,
+
+       PRIMARY KEY (uid),
+       KEY parent (pid)
+) ENGINE = MyISAM;
index 07d26be..56cf0a7 100644 (file)
@@ -7,4 +7,4 @@ CREATE TABLE a_test_table (
 
        PRIMARY KEY (uid),
        KEY parent (pid)
-);
+) ENGINE = InnoDB;
index b128fbb..0b77432 100644 (file)
@@ -336,6 +336,29 @@ class SchemaMigratorTest extends FunctionalTestCase
     }
 
     /**
+     * @test
+     */
+    public function changeTableEngine()
+    {
+        $statements = $this->readFixtureFile('alterTableEngine');
+        $updateSuggestions = $this->subject->getUpdateSuggestions($statements);
+
+        $index = array_keys($updateSuggestions[ConnectionPool::DEFAULT_CONNECTION_NAME]['change'])[0];
+        $this->assertStringEndsWith(
+            'ENGINE = MyISAM',
+            $updateSuggestions[ConnectionPool::DEFAULT_CONNECTION_NAME]['change'][$index]
+        );
+
+        $this->subject->migrate(
+            $statements,
+            $updateSuggestions[ConnectionPool::DEFAULT_CONNECTION_NAME]['change']
+        );
+
+        $updateSuggestions = $this->subject->getUpdateSuggestions($statements);
+        $this->assertEmpty($updateSuggestions[ConnectionPool::DEFAULT_CONNECTION_NAME]['change']);
+    }
+
+    /**
      * Create the base table for all migration tests
      */
     protected function prepareTestTable()