Fixed bug #6196: IFNULL operator cannot be parsed
authorXavier Perseguers <typo3@perseguers.ch>
Sat, 13 Feb 2010 13:18:32 +0000 (13:18 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Sat, 13 Feb 2010 13:18:32 +0000 (13:18 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Extensions/dbal/trunk@29977 735d13b6-9817-0410-8766-e36946ffe9aa

typo3/sysext/dbal/ChangeLog
typo3/sysext/dbal/class.ux_t3lib_db.php
typo3/sysext/dbal/class.ux_t3lib_sqlparser.php
typo3/sysext/dbal/res/postgresql/postgresql-compatibility.sql
typo3/sysext/dbal/tests/db_mssql_testcase.php
typo3/sysext/dbal/tests/db_oracle_testcase.php
typo3/sysext/dbal/tests/sqlparser_general_testcase.php

index 1433ca9..a648764 100644 (file)
@@ -1,5 +1,6 @@
 2010-02-13  Xavier Perseguers  <typo3@perseguers.ch>
 
+       * Fixed bug #6196: IFNULL operator cannot be parsed
        * Fixed bug #13528: Argument 1 passed to ux_t3lib_DB::_quoteFieldNames() must be an array, string given
 
 2010-02-11  Xavier Perseguers  <typo3@perseguers.ch>
index 0275dca..2b112c6 100644 (file)
@@ -1290,6 +1290,7 @@ class ux_t3lib_DB extends t3lib_DB {
                                        case 'EXISTS':
                                                $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
                                                break;
+                                       case 'IFNULL':
                                        case 'LOCATE':
                                                if ($where_clause[$k]['func']['table'] != '') {
                                                        $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
@@ -2799,6 +2800,7 @@ class ux_t3lib_DB extends t3lib_DB {
                                                        $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['FROM'], $subqueryDefaultTable);
                                                        $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['WHERE'], $subqueryDefaultTable);
                                                        break;
+                                               case 'IFNULL':
                                                case 'LOCATE':
                                                                // For the field, look for table mapping (generic):
                                                        $t = $sqlPartArray[$k]['func']['table'] ? $sqlPartArray[$k]['func']['table'] : $defaultTable;
index 12b5c29..31eb5ac 100644 (file)
@@ -467,7 +467,24 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                                                                $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
                                                                                $output .= ')';
                                                                                break;
-                                                               } 
+                                                               }
+                                                       } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
+                                                               $output .= ' ' . trim($v['modifier']) . ' ';
+                                                               switch (TRUE) {
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') && $functionMapping):
+                                                                               $output .= 'ISNULL';
+                                                                               break;
+                                                                       case ($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') && $functionMapping):
+                                                                               $output .= 'NVL';
+                                                                               break;
+                                                                       default:
+                                                                               $output .= 'IFNULL';
+                                                                               break;
+                                                               }
+                                                               $output .= '(';
+                                                               $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
+                                                               $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
+                                                               $output .= ')';
                                                        } else {
 
                                                                        // Set field/table with modifying prefix if any:
index b5ac6e2..f6d4ace 100644 (file)
@@ -10,6 +10,9 @@
 -- 
 -- The functions add compatibility operators for PostgreSQL to make sure comparison is possible and the SQL doesn't return an error.
 --
+-- Note: You may consider having a look at project mysqlcompat on http://pgfoundry.org/projects/mysqlcompat
+--       and report in DBAL bugtracker if you need another compatibility operator added.
+--
 -- $Id$
 -- R. van Twisk <typo3@rvt.dds.nl>
 
@@ -68,6 +71,12 @@ RETURNS integer AS $$
 SELECT locate($1, $2, 1)
 $$ IMMUTABLE STRICT LANGUAGE SQL;
 
+-- IFNULL
+CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
+RETURNS anyelement AS $$
+SELECT COALESCE($1, $2)
+$$ IMMUTABLE STRICT LANGUAGE SQL;
+
 -- Remove Compatibility operators
 --
 --DROP OPERATOR ~~ (integer,text);
@@ -80,4 +89,5 @@ $$ IMMUTABLE STRICT LANGUAGE SQL;
 --DROP FUNCTION t3compat_operator_eq(text, integer);
 --DROP FUNCTION locate(text, text);
 --DROP FUNCTION locate(text, text, integer);
+--DROP FUNCTION ifnull(anyelement, anyelement);
 
index 136857e..027d85d 100644 (file)
@@ -227,5 +227,19 @@ class db_mssql_testcase extends BaseTestCase {
                $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "tx_templavoila_tmplobj"."ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
                $this->assertEquals($expected, $query);
        }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function IfNullIsProperlyRemapped() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_news_cat_mm',
+                       'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
+               ));
+               $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
+               $this->assertEquals($expected, $query);
+       }
 }
 ?>
\ No newline at end of file
index 074847f..865dde5 100644 (file)
@@ -781,5 +781,19 @@ class db_oracle_testcase extends BaseTestCase {
                $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
                $this->assertEquals($expected, $query);
        }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function IfNullIsProperlyRemapped() {
+               $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_news_cat_mm',
+                       'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
+               ));
+               $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
+               $this->assertEquals($expected, $query);
+       }
 }
 ?>
\ No newline at end of file
index 003640d..a39393b 100644 (file)
@@ -293,6 +293,50 @@ class sqlparser_general_testcase extends BaseTestCase {
                $this->assertEquals($expected, $insert);
        }
 
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canParseIfNullOperator() {
+               $parseString = 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)';
+               $whereParts = $this->fixture->parseWhereClause($parseString);
+
+               $this->assertTrue(is_array($whereParts), $whereParts);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canParseIfNullOperatorWithAdditionalClauses() {
+               $parseString = '1=1 AND IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22) AND tt_news.sys_language_uid IN (0,-1) ';
+               $parseString .= 'AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 ';
+               $parseString .= 'AND tt_news.hidden=0 AND tt_news.starttime<=1266065460 AND (tt_news.endtime=0 OR tt_news.endtime>1266065460) ';
+               $parseString .= 'AND (tt_news.fe_group=\'\' OR tt_news.fe_group IS NULL OR tt_news.fe_group=\'0\' ';
+               $parseString .= 'OR (tt_news.fe_group LIKE \'%,0,%\' OR tt_news.fe_group LIKE \'0,%\' OR tt_news.fe_group LIKE \'%,0\' ';
+               $parseString .= 'OR tt_news.fe_group=\'0\') OR (tt_news.fe_group LIKE \'%,-1,%\' OR tt_news.fe_group LIKE \'-1,%\' ';
+               $parseString .= 'OR tt_news.fe_group LIKE \'%,-1\' OR tt_news.fe_group=\'-1\'))';
+               $whereParts = $this->fixture->parseWhereClause($parseString);
+
+               $this->assertTrue(is_array($whereParts), $whereParts);
+               $this->assertTrue(empty($parseString), 'parseString is not empty');
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=6196
+        */
+       public function canCompileIfNullOperator() {
+               $parseString = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign,0) = 1';
+               $components = $this->fixture->_callRef('parseSELECT', $parseString);
+
+               $this->assertTrue(is_array($components), $components);
+               $select = $this->cleanSql($this->fixture->_callRef('compileSELECT', $components));
+               $expected = 'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign, 0) = 1';
+               $this->assertEquals($expected, $select);
+       }
+
        ///////////////////////////////////////
        // Tests concerning JOINs
        ///////////////////////////////////////