Added feature #13135: Support for CASE WHEN flow control
authorXavier Perseguers <typo3@perseguers.ch>
Sun, 3 Jan 2010 21:09:21 +0000 (21:09 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Sun, 3 Jan 2010 21:09:21 +0000 (21:09 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Extensions/dbal/trunk@28324 735d13b6-9817-0410-8766-e36946ffe9aa

typo3/sysext/dbal/ChangeLog
typo3/sysext/dbal/class.ux_t3lib_db.php
typo3/sysext/dbal/tests/db_oracle_testcase.php
typo3/sysext/dbal/tests/sqlparser_general_testcase.php

index 6230cc6..10bd9d1 100644 (file)
@@ -1,3 +1,7 @@
+2010-01-03  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added feature #13135: Support for CASE WHEN flow control
+
 2009-12-30  Xavier Perseguers  <typo3@perseguers.ch>
 
        * Set version to 1.1.0
index 17c2b64..77a9ad3 100644 (file)
@@ -1053,6 +1053,12 @@ class ux_t3lib_DB extends t3lib_DB {
                                $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
                                $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
                        }
+                       if (isset($select_fields[$k]['flow-control'])) {
+                                       // Quoting flow-control statements
+                               if ($select_fields[$k]['flow-control']['type'] === 'CASE' && isset($select_fields[$k]['flow-control']['case_field'])) {
+                                       $select_fields[$k]['flow-control']['case_field'] = $this->quoteFieldNames($select_fields[$k]['flow-control']['case_field']); 
+                               }
+                       }
                }
 
                return $select_fields;
@@ -2624,6 +2630,29 @@ class ux_t3lib_DB extends t3lib_DB {
                                                        $sqlPartArray[$k]['func_content.'][0]['func_content'] = $table . '.' . $field;
                                                        $sqlPartArray[$k]['func_content'] = $table . '.' . $field;
                                                }
+
+                                                       // Mapping flow-control statements
+                                               if (isset($sqlPartArray[$k]['flow-control'])) {                                                 
+                                                       if ($sqlPartArray[$k]['flow-control']['type'] === 'CASE' && isset($sqlPartArray[$k]['flow-control']['case_field'])) {
+                                                               $fieldArray = explode('.', $sqlPartArray[$k]['flow-control']['case_field']);
+                                                               if (count($fieldArray) == 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
+                                                                       $sqlPartArray[$k]['flow-control']['case_field'] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
+                                                               }
+                                                               elseif (count($fieldArray) == 2) {
+                                                                               // Map the external table
+                                                                       $table = $fieldArray[0];
+                                                                       if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
+                                                                               $table = $this->mapping[$fieldArray[0]]['mapTableName'];
+                                                                       }
+                                                                               // Map the field itself
+                                                                       $field = $fieldArray[1];
+                                                                       if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
+                                                                               $field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
+                                                                       }
+                                                                       $sqlPartArray[$k]['flow-control']['case_field'] = $table . '.' . $field;
+                                                               }
+                                                       }
+                                               }
                                        }
 
                                                // Do we have a function (e.g., CONCAT)
index be7068c..28ea366 100644 (file)
@@ -588,5 +588,73 @@ class db_oracle_testcase extends BaseTestCase {
                $expected .= ')';
                $this->assertEquals($expected, $query);
        }
+
+       ///////////////////////////////////////
+       // Tests concerning advanced operators
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementIsProperlyQuoted() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       'process_id, CASE active' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 
+                       ' END AS number',
+                       'tx_crawler_process',
+                       '1=1'
+               ));
+               $expected = 'SELECT "process_id", CASE "active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" FROM "tx_crawler_process" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementIsProperlyRemapped() {
+               $selectFields = 'process_id, CASE active' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') . 
+                       ' END AS number';
+               $fromTables   = 'tx_crawler_process';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
+               $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseStatementWithExternalTableIsProperlyRemapped() {
+               $selectFields = 'process_id, CASE tt_news.uid' .
+                               ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
+                               ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
+                               ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') . 
+                       ' END AS number';
+               $fromTables   = 'tx_crawler_process, tt_news';
+               $whereClause  = '1=1';
+               $groupBy      = '';
+               $orderBy      = '';
+
+               $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
+
+               $expected = 'SELECT "ps_id", CASE "ext_tt_news"."news_uid" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
+               $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
+               $this->assertEquals($expected, $query);
+       }
 }
 ?>
\ No newline at end of file
index 8c92060..40f4c76 100644 (file)
@@ -356,5 +356,57 @@ class sqlparser_general_testcase extends BaseTestCase {
 
                $this->assertEquals($expected, $actual);
        }
+
+       ///////////////////////////////////////
+       // Tests concerning advanced operators
+       ///////////////////////////////////////
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithBooleanConditionIsSupportedInFields() {
+               $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
+               $fieldList = $this->fixture->parseFieldList($parseString);
+
+               $this->assertTrue(is_array($fieldList), $fieldList);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithBooleanConditionIsProperlyCompiled() {
+               $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
+               $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithMultipleWhenIsSupportedInFields() {
+               $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
+               $fieldList = $this->fixture->parseFieldList($parseString);
+
+               $this->assertTrue(is_array($fieldList), $fieldList);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=13135
+        */
+       public function caseWithMultipleWhenIsProperlyCompiled() {
+               $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
+               $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
+               $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
+
+               $this->assertEquals($expected, $actual);
+       }
 }
 ?>
\ No newline at end of file