[FEATURE] Add support to native SQL time column type 50/52250/23
authorEric Chavaillaz <eric@hemmer.ch>
Wed, 29 Mar 2017 14:47:42 +0000 (16:47 +0200)
committerMarkus Klein <markus.klein@typo3.org>
Wed, 29 Nov 2017 14:54:13 +0000 (15:54 +0100)
This patch introduce a new key "time" to the TCA property
"dbType" to allow using native SQL time column type.

Resolves: #80557
Releases: master
Change-Id: Id03045ccf219a9a7e9d851b2f4501bed60ab4ea0
Reviewed-on: https://review.typo3.org/52250
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Susanne Moog <susanne.moog@typo3.org>
Tested-by: Susanne Moog <susanne.moog@typo3.org>
Reviewed-by: Markus Klein <markus.klein@typo3.org>
Tested-by: Markus Klein <markus.klein@typo3.org>
13 files changed:
typo3/sysext/backend/Classes/Form/FormDataProvider/DatabaseRowDateTimeFields.php
typo3/sysext/backend/Classes/Form/FormDataProvider/TcaRecordTitle.php
typo3/sysext/backend/Classes/Utility/BackendUtility.php
typo3/sysext/backend/Tests/Unit/Form/FormDataProvider/DatabaseRowDateTimeFieldsTest.php
typo3/sysext/backend/Tests/Unit/Form/FormDataProvider/TcaRecordTitleTest.php
typo3/sysext/core/Classes/DataHandling/DataHandler.php
typo3/sysext/core/Classes/Database/Query/QueryHelper.php
typo3/sysext/core/Documentation/Changelog/master/Feature-80557-AddSupportForNativeSQLTimeColumnType.rst [new file with mode: 0644]
typo3/sysext/core/Tests/Unit/DataHandling/DataHandlerTest.php
typo3/sysext/extbase/Classes/Persistence/Generic/Mapper/ColumnMap.php
typo3/sysext/extbase/Classes/Persistence/Generic/Mapper/DataMapFactory.php
typo3/sysext/extbase/Classes/Persistence/Generic/Mapper/DataMapper.php
typo3/sysext/extbase/Tests/Unit/Persistence/Generic/Mapper/DataMapFactoryTest.php

index 67d3e1b..b0389b6 100644 (file)
@@ -30,13 +30,15 @@ class DatabaseRowDateTimeFields implements FormDataProviderInterface
      */
     public function addData(array $result)
     {
+        $dateTimeTypes = QueryHelper::getDateTimeTypes();
         $dateTimeFormats = QueryHelper::getDateTimeFormats();
+
         foreach ($result['processedTca']['columns'] as $column => $columnConfig) {
             if (isset($columnConfig['config']['dbType'])
-                && ($columnConfig['config']['dbType'] === 'date' || $columnConfig['config']['dbType'] === 'datetime')
+                && in_array($columnConfig['config']['dbType'], $dateTimeTypes, true)
             ) {
                 if (!empty($result['databaseRow'][$column])
-                    &&  $result['databaseRow'][$column] !== $dateTimeFormats[$columnConfig['config']['dbType']]['empty']
+                    && $result['databaseRow'][$column] !== $dateTimeFormats[$columnConfig['config']['dbType']]['empty']
                 ) {
                     // Create an ISO-8601 date from current field data; the database always contains UTC
                     // The field value is something like "2016-01-01" or "2016-01-01 10:11:12", so appending "UTC"
index c974132..c934a90 100644 (file)
@@ -16,6 +16,7 @@ namespace TYPO3\CMS\Backend\Form\FormDataProvider;
 
 use TYPO3\CMS\Backend\Form\FormDataProviderInterface;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Core\Localization\LanguageService;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 
@@ -311,9 +312,11 @@ class TcaRecordTitle implements FormDataProviderInterface
             return '';
         }
         $title = $value;
+        $dateTimeFormats = QueryHelper::getDateTimeFormats();
         if (GeneralUtility::inList($fieldConfig['eval'], 'date')) {
+            // Handle native date field
             if (isset($fieldConfig['dbType']) && $fieldConfig['dbType'] === 'date') {
-                $value = $value === '0000-00-00' ? 0 : (int)strtotime($value);
+                $value = $value === $dateTimeFormats['date']['empty'] ? 0 : (int)strtotime($value);
             } else {
                 $value = (int)$value;
             }
@@ -331,17 +334,29 @@ class TcaRecordTitle implements FormDataProviderInterface
                 $title = BackendUtility::date($value) . $ageSuffix;
             }
         } elseif (GeneralUtility::inList($fieldConfig['eval'], 'time')) {
+            // Handle native time field
+            if (isset($fieldConfig['dbType']) && $fieldConfig['dbType'] === 'time') {
+                $value = $value === $dateTimeFormats['time']['empty'] ? 0 : (int)strtotime('1970-01-01 ' . $value);
+            } else {
+                $value = (int)$value;
+            }
             if (!empty($value)) {
                 $title = gmdate('H:i', (int)$value);
             }
         } elseif (GeneralUtility::inList($fieldConfig['eval'], 'timesec')) {
+            // Handle native time field
+            if (isset($fieldConfig['dbType']) && $fieldConfig['dbType'] === 'time') {
+                $value = $value === $dateTimeFormats['time']['empty'] ? 0 : (int)strtotime('1970-01-01 ' . $value);
+            } else {
+                $value = (int)$value;
+            }
             if (!empty($value)) {
                 $title = gmdate('H:i:s', (int)$value);
             }
         } elseif (GeneralUtility::inList($fieldConfig['eval'], 'datetime')) {
-            // Handle native date/time field
+            // Handle native datetime field
             if (isset($fieldConfig['dbType']) && $fieldConfig['dbType'] === 'datetime') {
-                $value = $value === '0000-00-00 00:00:00' ? 0 : (int)strtotime($value);
+                $value = $value === $dateTimeFormats['datetime']['empty'] ? 0 : (int)strtotime($value);
             } else {
                 $value = (int)$value;
             }
index 9db93cd..2df6233 100644 (file)
@@ -95,9 +95,9 @@ class BackendUtility
             ->getQueryBuilderForTable($table)
             ->expr();
         return ' AND ' . $expressionBuilder->eq(
-            ($tableAlias ?: $table) . '.' . $GLOBALS['TCA'][$table]['ctrl']['delete'],
-            0
-        );
+                ($tableAlias ?: $table) . '.' . $GLOBALS['TCA'][$table]['ctrl']['delete'],
+                0
+            );
     }
 
     /**
@@ -2155,12 +2155,14 @@ class BackendUtility
             case 'input':
                 // Hide value 0 for dates, but show it for everything else
                 if (isset($value)) {
+                    $dateTimeFormats = QueryHelper::getDateTimeFormats();
+
                     if (GeneralUtility::inList($theColConf['eval'], 'date')) {
                         // Handle native date field
                         if (isset($theColConf['dbType']) && $theColConf['dbType'] === 'date') {
-                            $dateTimeFormats = QueryHelper::getDateTimeFormats();
-                            $emptyValue = $dateTimeFormats['date']['empty'];
-                            $value = $value !== $emptyValue ? strtotime($value) : 0;
+                            $value = $value === $dateTimeFormats['date']['empty'] ? 0 : (int)strtotime($value);
+                        } else {
+                            $value = (int)$value;
                         }
                         if (!empty($value)) {
                             $ageSuffix = '';
@@ -2183,19 +2185,31 @@ class BackendUtility
                             $l = self::date($value) . $ageSuffix;
                         }
                     } elseif (GeneralUtility::inList($theColConf['eval'], 'time')) {
+                        // Handle native time field
+                        if (isset($theColConf['dbType']) && $theColConf['dbType'] === 'time') {
+                            $value = $value === $dateTimeFormats['time']['empty'] ? 0 : (int)strtotime('1970-01-01 ' . $value);
+                        } else {
+                            $value = (int)$value;
+                        }
                         if (!empty($value)) {
                             $l = gmdate('H:i', (int)$value);
                         }
                     } elseif (GeneralUtility::inList($theColConf['eval'], 'timesec')) {
+                        // Handle native time field
+                        if (isset($theColConf['dbType']) && $theColConf['dbType'] === 'time') {
+                            $value = $value === $dateTimeFormats['time']['empty'] ? 0 : (int)strtotime('1970-01-01 ' . $value);
+                        } else {
+                            $value = (int)$value;
+                        }
                         if (!empty($value)) {
                             $l = gmdate('H:i:s', (int)$value);
                         }
                     } elseif (GeneralUtility::inList($theColConf['eval'], 'datetime')) {
-                        // Handle native date/time field
+                        // Handle native datetime field
                         if (isset($theColConf['dbType']) && $theColConf['dbType'] === 'datetime') {
-                            $dateTimeFormats = QueryHelper::getDateTimeFormats();
-                            $emptyValue = $dateTimeFormats['datetime']['empty'];
-                            $value = $value !== $emptyValue ? strtotime($value) : 0;
+                            $value = $value === $dateTimeFormats['datetime']['empty'] ? 0 : (int)strtotime($value);
+                        } else {
+                            $value = (int)$value;
                         }
                         if (!empty($value)) {
                             $l = self::datetime($value);
index 268b56a..51c33ef 100644 (file)
@@ -68,6 +68,28 @@ class DatabaseRowDateTimeFieldsTest extends \TYPO3\TestingFramework\Core\Unit\Un
     /**
      * @test
      */
+    public function addDataSetsTimestampZeroForDefaultTimeField()
+    {
+        $input = [
+            'tableName' => 'aTable',
+            'processedTca' => [
+                'columns' => [
+                    'aField' => [
+                        'config' => [
+                            'dbType' => 'time',
+                        ],
+                    ],
+                ],
+            ],
+        ];
+        $expected = $input;
+        $expected['databaseRow']['aField'] = 0;
+        $this->assertEquals($expected, (new DatabaseRowDateTimeFields())->addData($input));
+    }
+
+    /**
+     * @test
+     */
     public function addDataConvertsDateStringToTimestamp()
     {
         $oldTimezone = date_default_timezone_get();
@@ -120,4 +142,32 @@ class DatabaseRowDateTimeFieldsTest extends \TYPO3\TestingFramework\Core\Unit\Un
         $this->assertEquals($expected, (new DatabaseRowDateTimeFields())->addData($input));
         date_default_timezone_set($oldTimezone);
     }
+
+    /**
+     * @test
+     */
+    public function addDataConvertsTimeStringToTimestamp()
+    {
+        $oldTimezone = date_default_timezone_get();
+        date_default_timezone_set('UTC');
+        $input = [
+            'tableName' => 'aTable',
+            'processedTca' => [
+                'columns' => [
+                    'aField' => [
+                        'config' => [
+                            'dbType' => 'time',
+                        ],
+                    ],
+                ],
+            ],
+            'databaseRow' => [
+                'aField' => '15:25:32',
+            ],
+        ];
+        $expected = $input;
+        $expected['databaseRow']['aField'] = date('Y-m-d') . 'T15:25:32+00:00';
+        $this->assertEquals($expected, (new DatabaseRowDateTimeFields())->addData($input));
+        date_default_timezone_set($oldTimezone);
+    }
 }
index efe8a9d..f67b1e8 100644 (file)
@@ -305,6 +305,15 @@ class TcaRecordTitleTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
                 '44100',
                 '12:15',
             ],
+            'time input (dbType: time)' => [
+                [
+                    'type' => 'input',
+                    'eval' => 'time',
+                    'dbType' => 'time'
+                ],
+                '23:59:00',
+                '23:59',
+            ],
             'timesec input' => [
                 [
                     'type' => 'input',
@@ -313,6 +322,15 @@ class TcaRecordTitleTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
                 '44130',
                 '12:15:30',
             ],
+            'timesec input (dbType: time)' => [
+                [
+                    'type' => 'input',
+                    'eval' => 'timesec',
+                    'dbType' => 'time'
+                ],
+                '23:59:59',
+                '23:59:59',
+            ],
             'datetime input' => [
                 [
                     'type' => 'input',
index 9274683..55e4252 100644 (file)
@@ -1297,6 +1297,28 @@ class DataHandler implements LoggerAwareInterface
     }
 
     /**
+     * @param $table
+     * @param $row
+     * @param $key
+     *
+     * @return string
+     */
+    protected function normalizeTimeFormat(string $table, string $value, string $dbType): string
+    {
+        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
+        $platform = $connection->getDatabasePlatform();
+        if ($platform instanceof SQLServerPlatform) {
+            $defaultLength = QueryHelper::getDateTimeFormats()[$dbType]['empty'];
+            $value = substr(
+                $value,
+                0,
+                strlen($defaultLength)
+            );
+        }
+        return $value;
+    }
+
+    /**
      * Sets the "sorting" DB field and the "pid" field of an incoming record that should be added (NEW1234)
      * depending on the record that should be added or where it should be added.
      *
@@ -1806,8 +1828,9 @@ class DataHandler implements LoggerAwareInterface
         $isDateOrDateTimeField = false;
         $format = '';
         $emptyValue = '';
+        $dateTimeTypes = QueryHelper::getDateTimeTypes();
         // normal integer "date" fields (timestamps) are handled in checkValue_input_Eval
-        if (isset($tcaFieldConf['dbType']) && ($tcaFieldConf['dbType'] === 'date' || $tcaFieldConf['dbType'] === 'datetime')) {
+        if (isset($tcaFieldConf['dbType']) && in_array($tcaFieldConf['dbType'], $dateTimeTypes, true)) {
             if (empty($value)) {
                 $value = null;
             } else {
@@ -7207,6 +7230,10 @@ class DataHandler implements LoggerAwareInterface
                                 $errors[] = $key;
                             }
                         } else {
+                            $dbType = $GLOBALS['TCA'][$table]['columns'][$key]['config']['dbType'] ?? false;
+                            if ($dbType === 'datetime' || $dbType === 'time') {
+                                $row[$key] = $this->normalizeTimeFormat($table, $row[$key], $dbType);
+                            }
                             if ((string)$value !== (string)$row[$key]) {
                                 // The is_numeric check catches cases where we want to store a float/double value
                                 // and database returns the field as a string with the least required amount of
index 069a5a5..29018b9 100644 (file)
@@ -179,11 +179,31 @@ class QueryHelper
             'datetime' => [
                 'empty' => '0000-00-00 00:00:00',
                 'format' => 'Y-m-d H:i:s'
+            ],
+            'time' => [
+                'empty' => '00:00:00',
+                'format' => 'H:i:s'
             ]
         ];
     }
 
     /**
+     * Returns the date and time types compatible with the given database.
+     *
+     * This simple method should probably be deprecated and removed later.
+     *
+     * @return array
+     */
+    public static function getDateTimeTypes()
+    {
+        return [
+            'date',
+            'datetime',
+            'time'
+        ];
+    }
+
+    /**
      * Quote database table/column names indicated by {#identifier} markup in a SQL fragment string.
      * This is an intermediate step to make SQL fragments in Typoscript and TCA database agnostic.
      *
diff --git a/typo3/sysext/core/Documentation/Changelog/master/Feature-80557-AddSupportForNativeSQLTimeColumnType.rst b/typo3/sysext/core/Documentation/Changelog/master/Feature-80557-AddSupportForNativeSQLTimeColumnType.rst
new file mode 100644 (file)
index 0000000..1b9ba92
--- /dev/null
@@ -0,0 +1,25 @@
+.. include:: ../../Includes.txt
+
+=============================================================
+Feature: #80557 - Add support for native SQL time column type
+=============================================================
+
+See :issue:`80557`
+
+Description
+===========
+
+It is now possible to use the native SQL time column type.
+
+It is required to set the property `dbType` to the value `time`.
+In addition the field `eval` property must be set to `time` or `timesec`.
+
+.. code-block:: php
+
+   $GLOBALS['TCA']['tx_myext_table']['columns']['some_time']['config'] = [
+      'type' => 'input',
+      'dbType' => 'time',
+      'eval' => 'time'
+   ];
+
+.. index:: Backend, Database, TCA
\ No newline at end of file
index c7900bb..12722d4 100644 (file)
@@ -246,7 +246,7 @@ class DataHandlerTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
                     'input' => []
                 ]
             ],
-            'tca with dbType != date/datetime' => [
+            'tca with dbType != date/datetime/time' => [
                 [
                     'input' => [],
                     'dbType' => 'foo'
index 0dac6a2..bebdb03 100644 (file)
@@ -151,7 +151,7 @@ class ColumnMap
     /**
      * Alternative format for storing DataTime formats
      * (instead of using unix-time stamps). Allowed values
-     * are 'date' and 'datetime'
+     * are 'date', 'datetime' and 'time'
      *
      * @var string
      */
index a989ed0..633edd0 100644 (file)
@@ -14,6 +14,8 @@ namespace TYPO3\CMS\Extbase\Persistence\Generic\Mapper;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
+
 /**
  * A factory for a data map to map a single table configured in $TCA on a domain object.
  */
@@ -364,9 +366,9 @@ class DataMapFactory implements \TYPO3\CMS\Core\SingletonInterface
     {
         if (!empty($columnConfiguration['eval'])) {
             $fieldEvaluations = \TYPO3\CMS\Core\Utility\GeneralUtility::trimExplode(',', $columnConfiguration['eval'], true);
-            $dateTimeEvaluations = ['date', 'datetime'];
+            $dateTimeTypes = QueryHelper::getDateTimeTypes();
 
-            if (!empty(array_intersect($dateTimeEvaluations, $fieldEvaluations)) && !empty($columnConfiguration['dbType'])) {
+            if (!empty(array_intersect($dateTimeTypes, $fieldEvaluations)) && !empty($columnConfiguration['dbType'])) {
                 $columnMap->setDateTimeStorageFormat($columnConfiguration['dbType']);
             }
         }
index e696769..d26d367 100644 (file)
@@ -14,6 +14,7 @@ namespace TYPO3\CMS\Extbase\Persistence\Generic\Mapper;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Extbase\DomainObject\DomainObjectInterface;
 use TYPO3\CMS\Extbase\Object\Exception\CannotReconstituteObjectException;
 use TYPO3\CMS\Extbase\Persistence;
@@ -312,22 +313,24 @@ class DataMapper implements \TYPO3\CMS\Core\SingletonInterface
     }
 
     /**
-     * Creates a DateTime from an unix timestamp or date/datetime value.
+     * Creates a DateTime from an unix timestamp or date/datetime/time value.
      * If the input is empty, NULL is returned.
      *
-     * @param int|string $value Unix timestamp or date/datetime value
-     * @param string|null $storageFormat Storage format for native date/datetime fields
+     * @param int|string $value Unix timestamp or date/datetime/time value
+     * @param string|null $storageFormat Storage format for native date/datetime/time fields
      * @param string|null $targetType The object class name to be created
      * @return \DateTime
      */
     protected function mapDateTime($value, $storageFormat = null, $targetType = 'DateTime')
     {
-        if (empty($value) || $value === '0000-00-00' || $value === '0000-00-00 00:00:00') {
+        $dateTimeTypes = QueryHelper::getDateTimeTypes();
+
+        if (empty($value) || $value === '0000-00-00' || $value === '0000-00-00 00:00:00' || $value === '00:00:00') {
             // 0 -> NULL !!!
             return null;
         }
-        if ($storageFormat === 'date' || $storageFormat === 'datetime') {
-            // native date/datetime values are stored in UTC
+        if (in_array($storageFormat, $dateTimeTypes, true)) {
+            // native date/datetime/time values are stored in UTC
             $utcTimeZone = new \DateTimeZone('UTC');
             $utcDateTime = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance($targetType, $value, $utcTimeZone);
             $currentTimeZone = new \DateTimeZone(date_default_timezone_get());
@@ -710,8 +713,11 @@ class DataMapper implements \TYPO3\CMS\Core\SingletonInterface
                     case 'date':
                         $parameter = $timeZoneToStore->format('Y-m-d');
                         break;
+                    case 'time':
+                        $parameter = $timeZoneToStore->format('H:i');
+                        break;
                     default:
-                        throw new \InvalidArgumentException('Column map DateTime format "' . $storageFormat . '" is unknown. Allowed values are datetime or date.', 1395353470);
+                        throw new \InvalidArgumentException('Column map DateTime format "' . $storageFormat . '" is unknown. Allowed values are date, datetime or time.', 1395353470);
                 }
             } else {
                 $parameter = $input->format('U');
index 59f2f4a..194cb28 100644 (file)
@@ -393,7 +393,8 @@ class DataMapFactoryTest extends \TYPO3\TestingFramework\Core\Unit\UnitTestCase
         return [
             'date field' => ['date', 'date'],
             'datetime field' => ['datetime', 'datetime'],
-            'no date/datetime field' => ['', null],
+            'time field' => ['time', 'time'],
+            'no date/datetime/time field' => ['', null],
         ];
     }