[TASK] dbal: convert DBMS specific key/default/extra values to MySQL SQL 56/40156/8
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Wed, 10 Jun 2015 19:28:33 +0000 (21:28 +0200)
committerAndreas Fernandez <typo3@scripting-base.de>
Fri, 19 Jun 2015 11:15:22 +0000 (13:15 +0200)
Allow DBMS specific transformations of field information to MySQL SQL
dialect. Provide the database compare in the install tool with more
details about the current database schema to avoid changes that can not
be implemented due to syntax differences.

Resolves: #67300
Resolves: #67329
Releases: master
Change-Id: Id415ba6246963fd0da97b5825fbe4fc9ac959641
Reviewed-on: http://review.typo3.org/40156
Reviewed-by: Markus Klein <markus.klein@typo3.org>
Tested-by: Markus Klein <markus.klein@typo3.org>
Reviewed-by: Andreas Fernandez <typo3@scripting-base.de>
Tested-by: Andreas Fernandez <typo3@scripting-base.de>
typo3/sysext/dbal/Classes/Database/Specifics/AbstractSpecifics.php
typo3/sysext/dbal/Classes/Database/Specifics/PostgresSpecifics.php
typo3/sysext/dbal/Tests/Unit/Database/DatabaseSpecificsPostgresqlTest.php

index 288f0fc..74e49e4 100644 (file)
@@ -163,9 +163,9 @@ abstract class AbstractSpecifics {
                $fieldRow['Field'] = $fieldRow['name'];
                $fieldRow['Type'] = strtolower($mysqlType);
                $fieldRow['Null'] = $this->getNativeNotNull($fieldRow['not_null']);
-               $fieldRow['Key'] = '';
-               $fieldRow['Default'] = $fieldRow['default_value'];
-               $fieldRow['Extra'] = '';
+               $fieldRow['Key'] = $this->getNativeKeyForField($fieldRow);
+               $fieldRow['Default'] = $this->getNativeDefaultValue($fieldRow);
+               $fieldRow['Extra'] = $this->getNativeExtraFieldAttributes($fieldRow);
 
                return $fieldRow;
        }
@@ -220,4 +220,41 @@ abstract class AbstractSpecifics {
        protected function getNativeNotNull($notNull) {
                return (bool)$notNull ? 'NO' : 'YES';
        }
+
+       /**
+        * Return the default value of a field formatted to match the native MySQL SQL dialect
+        *
+        * @param array $fieldDefinition
+        * @return mixed
+        */
+       protected function getNativeDefaultValue($fieldDefinition) {
+               return $fieldDefinition['default_value'];
+       }
+
+       /**
+        * Return the MySQL native key type indicator - https://dev.mysql.com/doc/refman/5.5/en/show-columns.html
+        * PRI - the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY
+        * UNI - the column is the first column of a UNIQUE index
+        * MUL - the column is the first column of a nonunique index
+        * If more than one of the values applies return the one with the highest priority, in the order PRI, UNI, MUL
+        * If none applies return empty value.
+        *
+        * @param array $fieldRow
+        * @return string
+        */
+       protected function getNativeKeyForField($fieldRow) {
+               return '';
+       }
+
+       /**
+        * Return the MySQL native extra field information - https://dev.mysql.com/doc/refman/5.5/en/show-columns.html
+        * auto_increment for columns that have the AUTO_INCREMENT attribute
+        * on update CURRENT_TIMESTAMP for TIMESTAMP columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.
+        *
+        * @param array $fieldRow
+        * @return string
+        */
+       protected function getNativeExtraFieldAttributes($fieldRow) {
+               return '';
+       }
 }
index fd620fc..02a375c 100644 (file)
@@ -72,4 +72,64 @@ class PostgresSpecifics extends AbstractSpecifics {
                                return '(' . $maxLength . ')';
                }
        }
+
+       /**
+        * Return the default value of a field formatted to match the native MySQL SQL dialect
+        *
+        * @param array $fieldDefinition
+        * @return mixed
+        */
+       protected function getNativeDefaultValue($fieldDefinition) {
+               if (!$fieldDefinition['has_default']) {
+                       $returnValue = NULL;
+               } elseif ($fieldDefinition['type'] === 'SERIAL' && substr($fieldDefinition['default_value'], 0, 7) === 'nextval') {
+                       $returnValue = NULL;
+               } elseif ($fieldDefinition['type'] === 'varchar') {
+                       // Strip character class and unquote string
+                       $returnValue = str_replace("\\'", "'", preg_replace('/\'(.*)\'(::(?:character\svarying|varchar|character|char|text)(?:\(\d+\))?)?\z/', '\\1', $fieldDefinition['default_value']));
+               } elseif (substr($fieldDefinition['type'], 0, 3) === 'int') {
+                       $returnValue = (int)preg_replace('/^\(?(\-?\d+)\)?$/', '\\1', $fieldDefinition['default_value']);
+               } else {
+                       $returnValue = $fieldDefinition['default_value'];
+               }
+               return $returnValue;
+       }
+
+       /**
+        * Return the MySQL native key type indicator - https://dev.mysql.com/doc/refman/5.5/en/show-columns.html
+        * PRI - the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY
+        * UNI - the column is the first column of a UNIQUE index
+        * MUL - the column is the first column of a nonunique index
+        * If more than one of the values applies return the one with the highest priority, in the order PRI, UNI, MUL
+        * If none applies return empty value.
+        *
+        * @param array $fieldDefinition
+        * @return string
+        */
+       protected function getNativeKeyForField($fieldDefinition) {
+               if (isset($fieldDefinition['primary_key']) && (bool)$fieldDefinition['primary_key']) {
+                       $returnValue = 'PRI';
+               } elseif (isset($fieldDefinition['unique']) && (bool)$fieldDefinition['unique']) {
+                       $returnValue = 'UNI';
+               } else {
+                       $returnValue = '';
+               }
+               return $returnValue;
+       }
+
+       /**
+        * Return the MySQL native extra field information - https://dev.mysql.com/doc/refman/5.5/en/show-columns.html
+        * auto_increment for columns that have the AUTO_INCREMENT attribute
+        * on update CURRENT_TIMESTAMP for TIMESTAMP columns that have the ON UPDATE CURRENT_TIMESTAMP attribute.
+        *
+        * @param array $fieldDefinition
+        * @return string
+        */
+       protected function getNativeExtraFieldAttributes($fieldDefinition) {
+               if ($fieldDefinition['type'] === 'SERIAL' || substr($fieldDefinition['default_value'], 0, 7) === 'nextval') {
+                       return 'auto_increment';
+               }
+               return '';
+       }
+
 }
index 2c1b8b3..369a40c 100644 (file)
@@ -27,7 +27,40 @@ class DatabaseSpecificsPostgresqlTest extends DatabaseSpecificsTest {
                $GLOBALS['TYPO3_LOADED_EXT'] = array();
 
                /** @var \TYPO3\CMS\Dbal\Database\Specifics\AbstractSpecifics|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
-               $this->subject = GeneralUtility::makeInstance(\TYPO3\CMS\Dbal\Database\Specifics\PostgresSpecifics::class);
+               $this->subject = GeneralUtility::makeInstance($this->buildAccessibleProxy(\TYPO3\CMS\Dbal\Database\Specifics\PostgresSpecifics::class));
+       }
+
+       /**
+        * @test
+        * @param array $fieldDefinition
+        * @param string $expected
+        * @dataProvider getNativeDefaultValueProvider
+        */
+       public function getNativeDefaultValueStripsPostgresqlCharacterClasses($fieldDefinition, $expected) {
+               $actual = $this->subject->_call('getNativeDefaultValue', $fieldDefinition);
+               $this->assertSame($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @param array $fieldDefinition
+        * @param string $expected
+        * @dataProvider getNativeExtraFieldAttributeProvider
+        */
+       public function getNativeExtraFieldAttributeSetsAutoIncrement($fieldDefinition, $expected) {
+               $actual = $this->subject->_call('getNativeExtraFieldAttributes', $fieldDefinition);
+               $this->assertSame($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @param array $fieldDefinition
+        * @param string $expected
+        * @dataProvider getNativeKeyForFieldProvider
+        */
+       public function getNativeKeyForFieldProviderIdentifiesIndexes($fieldDefinition, $expected) {
+               $actual = $this->subject->_call('getNativeKeyForField', $fieldDefinition);
+               $this->assertSame($expected, $actual);
        }
 
        /**
@@ -105,4 +138,41 @@ class DatabaseSpecificsPostgresqlTest extends DatabaseSpecificsTest {
                        array('DOUBLE', 8, '')
                );
        }
+
+       /**
+        * @return array
+        */
+       public function getNativeDefaultValueProvider() {
+               return array(
+                       array(array('type' => 'SERIAL', 'has_default' => 1, 'default_value' => "nextval('tx_extensionmanager_domain_model_repository_uid_seq'::regclass)"), NULL),
+                       array(array('type' => 'int4', 'has_default' => TRUE, 'default_value' => 0), 0),
+                       array(array('type' => 'int4', 'has_default' => TRUE, 'default_value' => '(-1)'), -1),
+                       array(array('type' => 'text', 'has_default' => FALSE, 'default_value' => NULL), NULL),
+                       array(array('type' => 'varchar', 'has_default' => TRUE, 'default_value' => "''::character varying"), ""),
+                       array(array('type' => 'varchar', 'has_default' => TRUE, 'default_value' => "'something'::character varying"), "something"),
+                       array(array('type' => 'varchar', 'has_default' => TRUE, 'default_value' => "'some''thing'::character varying"), "some''thing"),
+               );
+       }
+
+       /**
+        * @return array
+        */
+       public function getNativeExtraFieldAttributeProvider() {
+               return array(
+                       array(array('type' => 'SERIAL'), 'auto_increment'),
+                       array(array('type' => 'int4', 'default_value' => 'nextval(\'somesequence_seq\''), 'auto_increment'),
+                       array(array('type' => 'int4', 'default_value' => 0), '')
+               );
+       }
+
+       /**
+        * @return array
+        */
+       public function getNativeKeyForFieldProvider() {
+               return array(
+                       array(array('primary_key' => TRUE), 'PRI'),
+                       array(array('unique' => TRUE), 'UNI'),
+                       array(array(), '')
+               );
+       }
 }