[BUGFIX] Make ProcessedFileChecksumUpdate compatible with DBAL 76/40576/4
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Tue, 23 Jun 2015 20:22:55 +0000 (22:22 +0200)
committerChristian Kuhn <lolli@schwarzbu.ch>
Mon, 20 Jul 2015 17:27:46 +0000 (19:27 +0200)
Use explicit CAST statements in the SQL queries performed by the
updater to allow other DBMS to compare a VARCHAR with an INT field.

The SQLparser has been extended to support CAST statements as part
of a JOIN clause.

Resolves: #67708
Related: #66614
Releases: master
Change-Id: I4a13eae92290b9ce8149aa5ef93df1305c02715a
Reviewed-on: http://review.typo3.org/40576
Reviewed-by: Markus Klein <markus.klein@typo3.org>
Reviewed-by: Andreas Fernandez <typo3@scripting-base.de>
Tested-by: Andreas Fernandez <typo3@scripting-base.de>
Reviewed-by: Xavier Perseguers <xavier@typo3.org>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
typo3/sysext/core/Classes/Database/SqlParser.php
typo3/sysext/dbal/Tests/Unit/Database/SqlParserTest.php
typo3/sysext/install/Classes/Updates/ProcessedFileChecksumUpdate.php

index 06c3476..a64df9a 100644 (file)
@@ -834,6 +834,40 @@ class SqlParser {
        }
 
        /**
+        * Parsing a CAST definition in the "JOIN [$parseString] ..." part of a query into an array.
+        * The success of this parsing determines if that part of the query is supported by TYPO3.
+        *
+        * @param string $parseString JOIN clause to parse. NOTICE: passed by reference!
+        * @return mixed If successful parsing, returns an array, otherwise an error string.
+        */
+       protected function parseCastStatement(&$parseString) {
+               $this->nextPart($parseString, '^(CAST)[[:space:]]*');
+               $parseString = trim(substr($parseString, 1));
+               $castDefinition = array('type' => 'cast');
+               // Strip off "("
+               if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\\*._]+)[[:space:]]*')) {
+                       // Parse field name into field and table:
+                       $tableField = explode('.', $fieldName, 2);
+                       if (count($tableField) === 2) {
+                               $castDefinition['table'] = $tableField[0];
+                               $castDefinition['field'] = $tableField[1];
+                       } else {
+                               $castDefinition['table'] = '';
+                               $castDefinition['field'] = $tableField[0];
+                       }
+               } else {
+                       return $this->parseError('No casted join field found in parseCastStatement()!', $parseString);
+               }
+               if ($this->nextPart($parseString, '^([[:space:]]*AS[[:space:]]*)')) {
+                       $castDefinition['datatype'] = $this->getValue($parseString);
+               }
+               if (!$this->nextPart($parseString, '^([)])')) {
+                       return $this->parseError('No end parenthesis at end of CAST function', $parseString);
+               }
+               return $castDefinition;
+       }
+
+       /**
         * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
         * The success of this parsing determines if that part of the query is supported by TYPO3.
         *
@@ -896,12 +930,24 @@ class SqlParser {
                                                                $condition['left']['table'] = '';
                                                                $condition['left']['field'] = $tableField[0];
                                                        }
+                                               } elseif (preg_match('/^CAST[[:space:]]*[(]/i', $parseString)) {
+                                                       $condition['left'] = $this->parseCastStatement($parseString);
+                                                       // Return the parse error
+                                                       if (!is_array($condition['left'])) {
+                                                               return $condition['left'];
+                                                       }
                                                } else {
                                                        return $this->parseError('No join field found in parseFromTables()!', $parseString);
                                                }
                                                // Find "comparator":
                                                $condition['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=)');
-                                               if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') {
+                                               if (preg_match('/^CAST[[:space:]]*[(]/i', $parseString)) {
+                                                       $condition['right'] = $this->parseCastStatement($parseString);
+                                                       // Return the parse error
+                                                       if (!is_array($condition['right'])) {
+                                                               return $condition['right'];
+                                                       }
+                                               } elseif (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') {
                                                        // Parse field name into field and table:
                                                        $tableField = explode('.', $fieldName, 2);
                                                        $condition['right'] = array();
@@ -1150,8 +1196,10 @@ class SqlParser {
                                                                $this->nextPart($parseString, '^([(])');
                                                                $stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
                                                                // Seek to new position in parseString after parsing of the subquery
-                                                               $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
-                                                               unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
+                                                               if (!empty($stack[$level][$pnt[$level]]['subquery']['parseString'])) {
+                                                                       $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
+                                                                       unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
+                                                               }
                                                                if (!$this->nextPart($parseString, '^([)])')) {
                                                                        return 'No ) parenthesis at end of subquery';
                                                                }
@@ -1771,6 +1819,25 @@ class SqlParser {
        }
 
        /**
+        * Compile a "JOIN table ON [output] = ..." identifier
+        *
+        * @param array $identifierParts Array of identifier parts
+        * @return string
+        * @see parseCastStatement()
+        * @see parseFromTables()
+        */
+       protected function compileJoinIdentifier($identifierParts) {
+               if ($identifierParts['type'] === 'cast') {
+                       return sprintf('CAST(%s AS %s)',
+                               $identifierParts['table'] ? $identifierParts['table'] . '.' . $identifierParts['field'] : $identifierParts['field'],
+                               $identifierParts['datatype'][0]
+                       );
+               } else {
+                       return $identifierParts['table'] ? $identifierParts['table'] . '.' . $identifierParts['field'] : $identifierParts['field'];
+               }
+       }
+
+       /**
         * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
         *
         * @param array $tablesArray Array of table names, (made with ->parseFromTables())
@@ -1801,15 +1868,13 @@ class SqlParser {
                                                        if ($condition['operator'] !== '') {
                                                                $outputParts[$k] .= ' ' . $condition['operator'] . ' ';
                                                        }
-                                                       $outputParts[$k] .= $condition['left']['table'] ? $condition['left']['table'] . '.' : '';
-                                                       $outputParts[$k] .= $condition['left']['field'];
+                                                       $outputParts[$k] .= $this->compileJoinIdentifier($condition['left']);
                                                        $outputParts[$k] .= $condition['comparator'];
                                                        if (!empty($condition['right']['value'])) {
                                                                $value = $condition['right']['value'];
                                                                $outputParts[$k] .= $value[1] . $this->compileAddslashes($value[0]) . $value[1];
                                                        } else {
-                                                               $outputParts[$k] .= $condition['right']['table'] ? $condition['right']['table'] . '.' : '';
-                                                               $outputParts[$k] .= $condition['right']['field'];
+                                                               $outputParts[$k] .= $this->compileJoinIdentifier($condition['right']);
                                                        }
                                                }
                                        }
index b530931..9e1a948 100644 (file)
@@ -456,6 +456,30 @@ class SqlParserTest extends AbstractTestCase {
 
        /**
         * @test
+        * @see http://forge.typo3.org/issues/67708
+        */
+       public function canParseMultiJoinConditionsWithStringsAndLeftCast() {
+               $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER) = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
+
+               $result = $this->subject->debug_testSQL($sql);
+               $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER)=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
+               $this->assertEquals($expected, $this->cleanSql($result));
+       }
+
+       /**
+        * @test
+        * @see http://forge.typo3.org/issues/67708
+        */
+       public function canParseMultiJoinConditionsWithStringsAndRightCast() {
+               $sql = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
+
+               $result = $this->subject->debug_testSQL($sql);
+               $expected = 'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
+               $this->assertEquals($expected, $this->cleanSql($result));
+       }
+
+       /**
+        * @test
         * @see http://forge.typo3.org/issues/22501
         */
        public function canParseMultipleJoinConditionsWithLessThanOperator() {
index 575be1c..2eeb165 100644 (file)
@@ -39,7 +39,7 @@ class ProcessedFileChecksumUpdate extends AbstractUpdate {
                        return FALSE;
                }
 
-               $join = 'sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
+               $join = 'sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
                $count = $this->getDatabaseConnection()->exec_SELECTcountRows('*', $join, '(entry_key IS NULL AND sys_file_processedfile.identifier <> \'\') OR sys_file_processedfile.width IS NULL');
                if (!$count) {
                        return FALSE;
@@ -68,7 +68,7 @@ This can either happen on demand, when the processed file is first needed, or by
 
                $factory = GeneralUtility::makeInstance(ResourceFactory::class);
 
-               $join = 'sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
+               $join = 'sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
                $res = $db->exec_SELECTquery('sys_file_processedfile.*', $join, 'entry_key IS NULL AND sys_file_processedfile.identifier <> \'\'');
                while ($processedFileRow = $db->sql_fetch_assoc($res)) {
                        $storage = $factory->getStorageObject($processedFileRow['storage']);