[BUGFIX] mssql: Proper types inserting / updating rows 52/53152/2
authorChristian Kuhn <lolli@schwarzbu.ch>
Mon, 22 May 2017 13:39:09 +0000 (15:39 +0200)
committerFrank Naegler <frank.naegler@typo3.org>
Wed, 7 Jun 2017 13:00:39 +0000 (15:00 +0200)
MS SQL server is more picky about types than postgres and
mysql. This is especially true for LOB columns - even empty
strings need a proper cast and specific handling.

Various parts of the core deal with arbitrary tables and
don't know if a column is int, text or lob, or whatever.
Those are blindly updated / inserted, resulting in mssql
saying "no".

Solution is to fetch column schema and to set proper types
based on that schema. This is expensive. We will have to
refactor that again, and we will probably end up with a
(cache?) entry that knows the entire table schema of an
instance.

Solving that in a good way would also fix various mysql strict
issues we still have in the core. However, this needs more work.

Goal of the current patch is to bring mssql to a working state.
The solution must be seen as hacky, but is restricted to that
platform only and can be relaxed and improved as soon as we
take the next steps with schema handling in the TYPO3 core.

Change-Id: I9b582a9bde7461cfbcc2414192518fb7b7b1341d
Resolves: #81498
Releases: master, 8.7
Reviewed-on: https://review.typo3.org/53152
Reviewed-by: Frank Naegler <frank.naegler@typo3.org>
Tested-by: Frank Naegler <frank.naegler@typo3.org>
typo3/sysext/core/Classes/DataHandling/DataHandler.php
typo3/sysext/core/Classes/Resource/Index/MetaDataRepository.php
typo3/sysext/core/Tests/Functional/DataHandling/IRRE/ForeignField/AbstractActionTestCase.php
typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbBackend.php
typo3/sysext/version/Classes/Hook/DataHandlerHook.php

index 8faa1e3..1ea96ef 100644 (file)
@@ -16,6 +16,7 @@ namespace TYPO3\CMS\Core\DataHandling;
 
 use Doctrine\DBAL\DBALException;
 use Doctrine\DBAL\Driver\Statement;
+use Doctrine\DBAL\Platforms\SQLServerPlatform;
 use Doctrine\DBAL\Types\IntegerType;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
 use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
@@ -2557,9 +2558,9 @@ class DataHandler
             $statement = $this->getUniqueCountStatement($newValue, $table, $field, (int)$id, (int)$newPid);
             // For as long as records with the test-value existing, try again (with incremented numbers appended)
             if ($statement->fetchColumn()) {
-                $statement->bindParam(1, $newValue);
                 for ($counter = 0; $counter <= 100; $counter++) {
                     $newValue = $value . $counter;
+                    $statement->bindValue(1, $newValue);
                     $statement->execute();
                     if (!$statement->fetchColumn()) {
                         break;
@@ -6947,12 +6948,23 @@ class DataHandler
             unset($fieldArray['uid']);
             if (!empty($fieldArray)) {
                 $fieldArray = $this->insertUpdateDB_preprocessBasedOnFieldType($table, $fieldArray);
+
+                $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
+
+                $types = [];
+                $platform = $connection->getDatabasePlatform();
+                if ($platform instanceof SQLServerPlatform) {
+                    // mssql needs to set proper PARAM_LOB and others to update fields
+                    $tableDetails = $connection->getSchemaManager()->listTableDetails($table);
+                    foreach ($fieldArray as $columnName => $columnValue) {
+                        $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                    }
+                }
+
                 // Execute the UPDATE query:
                 $updateErrorMessage = '';
                 try {
-                    GeneralUtility::makeInstance(ConnectionPool::class)
-                        ->getConnectionForTable($table)
-                        ->update($table, $fieldArray, ['uid' => (int)$id]);
+                    $connection->update($table, $fieldArray, ['uid' => (int)$id], $types);
                 } catch (DBALException $e) {
                     $updateErrorMessage = $e->getPrevious()->getMessage();
                 }
@@ -6961,7 +6973,6 @@ class DataHandler
                     // Update reference index:
                     $this->updateRefIndex($table, $id);
                     if ($this->enableLogging) {
-                        $newRow = [];
                         if ($this->checkStoredRecords) {
                             $newRow = $this->checkStoredRecord($table, $id, $fieldArray, 2);
                         } else {
index f085b24..3435ca2 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Core\Resource\Index;
  * The TYPO3 project - inspiring people to share!
  */
 
+use Doctrine\DBAL\Platforms\SQLServerPlatform;
 use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Database\Query\Restriction\RootLevelRestriction;
@@ -174,14 +175,22 @@ class MetaDataRepository implements SingletonInterface
         $row = $this->findByFileUid($fileUid);
         if (!empty($updateRow)) {
             $updateRow['tstamp'] = time();
-            GeneralUtility::makeInstance(ConnectionPool::class)
-                ->getConnectionForTable($this->tableName)
-                ->update(
+            $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->tableName);
+            $types = [];
+            if ($connection->getDatabasePlatform() instanceof SQLServerPlatform) {
+                // mssql needs to set proper PARAM_LOB and others to update fields
+                $tableDetails = $connection->getSchemaManager()->listTableDetails($this->tableName);
+                foreach ($updateRow as $columnName => $columnValue) {
+                    $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                }
+            }
+            $connection->update(
                     $this->tableName,
                     $updateRow,
                     [
                         'uid' => (int)$row['uid']
-                    ]
+                    ],
+                    $types
                 );
 
             $this->emitRecordUpdatedSignal(array_merge($row, $updateRow));
index 6cc6dbe..4a07afe 100644 (file)
@@ -512,7 +512,6 @@ abstract class AbstractActionTestCase extends \TYPO3\CMS\Core\Tests\Functional\D
     {
         unset($GLOBALS['TCA'][self::TABLE_Hotel]['ctrl']['languageField']);
         unset($GLOBALS['TCA'][self::TABLE_Hotel]['ctrl']['transOrigPointerField']);
-        unset($GLOBALS['TCA'][self::TABLE_Hotel]['ctrl']['transOrigDiffSourceField']);
         $GLOBALS['TCA'][self::TABLE_PageOverlay]['columns'][self::FIELD_PageHotel]['config']['behaviour']['allowLanguageSynchronization'] = true;
         $localizedTableIds = $this->actionService->localizeRecord(self::TABLE_Page, self::VALUE_PageId, self::VALUE_LanguageId);
         $this->recordIds['localizedPageId'] = $localizedTableIds[self::TABLE_Page][self::VALUE_PageId];
index 6af89d6..f5f515a 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Extbase\Persistence\Generic\Storage;
  */
 
 use Doctrine\DBAL\DBALException;
+use Doctrine\DBAL\Platforms\SQLServerPlatform;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
 use TYPO3\CMS\Core\Database\ConnectionPool;
 use TYPO3\CMS\Core\Database\Query\QueryBuilder;
@@ -141,7 +142,18 @@ class Typo3DbBackend implements BackendInterface, SingletonInterface
         }
         try {
             $connection = $this->connectionPool->getConnectionForTable($tableName);
-            $connection->insert($tableName, $fieldValues);
+
+            $types = [];
+            $platform = $connection->getDatabasePlatform();
+            if ($platform instanceof SQLServerPlatform) {
+                // mssql needs to set proper PARAM_LOB and others to update fields
+                $tableDetails = $connection->getSchemaManager()->listTableDetails($tableName);
+                foreach ($fieldValues as $columnName => $columnValue) {
+                    $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                }
+            }
+
+            $connection->insert($tableName, $fieldValues, $types);
         } catch (DBALException $e) {
             throw new SqlErrorException($e->getPrevious()->getMessage(), 1470230766);
         }
@@ -175,8 +187,19 @@ class Typo3DbBackend implements BackendInterface, SingletonInterface
         unset($fieldValues['uid']);
 
         try {
-            $this->connectionPool->getConnectionForTable($tableName)
-                ->update($tableName, $fieldValues, ['uid' => $uid]);
+            $connection = $this->connectionPool->getConnectionForTable($tableName);
+
+            $types = [];
+            $platform = $connection->getDatabasePlatform();
+            if ($platform instanceof SQLServerPlatform) {
+                // mssql needs to set proper PARAM_LOB and others to update fields
+                $tableDetails = $connection->getSchemaManager()->listTableDetails($tableName);
+                foreach ($fieldValues as $columnName => $columnValue) {
+                    $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                }
+            }
+
+            $connection->update($tableName, $fieldValues, ['uid' => $uid], $types);
         } catch (DBALException $e) {
             throw new SqlErrorException($e->getPrevious()->getMessage(), 1470230767);
         }
index 548fc08..1c0e946 100644 (file)
@@ -15,6 +15,7 @@ namespace TYPO3\CMS\Version\Hook;
  */
 
 use Doctrine\DBAL\DBALException;
+use Doctrine\DBAL\Platforms\SQLServerPlatform;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
 use TYPO3\CMS\Core\Database\Connection;
 use TYPO3\CMS\Core\Database\ConnectionPool;
@@ -907,11 +908,28 @@ class DataHandlerHook
         // Execute swapping:
         $sqlErrors = [];
         $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
+
+        $platform = $connection->getDatabasePlatform();
+        $tableDetails = null;
+        if ($platform instanceof SQLServerPlatform) {
+            // mssql needs to set proper PARAM_LOB and others to update fields
+            $tableDetails = $connection->getSchemaManager()->listTableDetails($table);
+        }
+
         try {
+            $types = [];
+
+            if ($platform instanceof SQLServerPlatform) {
+                foreach ($curVersion as $columnName => $columnValue) {
+                    $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                }
+            }
+
             $connection->update(
                 $table,
                 $swapVersion,
-                ['uid' => (int)$id]
+                ['uid' => (int)$id],
+                $types
             );
         } catch (DBALException $e) {
             $sqlErrors[] = $e->getPrevious()->getMessage();
@@ -919,10 +937,18 @@ class DataHandlerHook
 
         if (empty($sqlErrors)) {
             try {
+                $types = [];
+                if ($platform instanceof SQLServerPlatform) {
+                    foreach ($curVersion as $columnName => $columnValue) {
+                        $types[$columnName] = $tableDetails->getColumn($columnName)->getType()->getBindingType();
+                    }
+                }
+
                 $connection->update(
                     $table,
                     $curVersion,
-                    ['uid' => (int)$swapWith]
+                    ['uid' => (int)$swapWith],
+                    $types
                 );
                 unlink($lockFileName);
             } catch (DBALException $e) {