Added feature #13135: Support for CASE WHEN flow control
authorXavier Perseguers <typo3@perseguers.ch>
Sun, 3 Jan 2010 21:07:35 +0000 (21:07 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Sun, 3 Jan 2010 21:07:35 +0000 (21:07 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@6712 709f56b5-9817-0410-a4d7-c38de5d9e867

ChangeLog
t3lib/class.t3lib_sqlparser.php

index 4014107..0361094 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,7 @@
+2010-01-03  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Added feature #13135: Support for CASE WHEN flow control
+
 2010-01-03  Benjamin Mack  <benni@typo3.org>
 
        * Fixed bug #13095: Massuploader: Max file size is shown in KB (THanks to Steffen Gebert)
index 1ccd39c..f9613a8 100644 (file)
@@ -717,30 +717,41 @@ class t3lib_sqlparser {
                                }
                        } else {        // Outside parenthesis, looking for next field:
 
-                                       // Looking for a known function (only known functions supported)
-                               $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
-                               if ($func)      {
-                                       $parseString = trim(substr($parseString,1));    // Strip of "("
-                                       $stack[$pnt]['type'] = 'function';
-                                       $stack[$pnt]['function'] = $func;
-                                       $level++;       // increse parenthesis level counter.
+                                       // Looking for a flow-control construct (only known constructs supported)
+                               if (preg_match('/^case([[:space:]][[:alnum:]\*._]+)?[[:space:]]when/i', $parseString)) {
+                                       $stack[$pnt]['type'] = 'flow-control';
+                                       $stack[$pnt]['flow-control'] = $this->parseCaseStatement($parseString);
+                                               // Looking for "AS" alias:
+                                       if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
+                                               $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
+                                               $stack[$pnt]['as_keyword'] = $as;
+                                       }
                                } else {
-                                       $stack[$pnt]['distinct'] = $this->nextPart($parseString,'^(distinct[[:space:]]+)');
-                                               // Otherwise, look for regular fieldname:
-                                       if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)'))   {
-                                               $stack[$pnt]['type'] = 'field';
-
-                                                       // Explode fieldname into field and table:
-                                               $tableField = explode('.',$fieldName,2);
-                                               if (count($tableField)==2)      {
-                                                       $stack[$pnt]['table'] = $tableField[0];
-                                                       $stack[$pnt]['field'] = $tableField[1];
+                                               // Looking for a known function (only known functions supported)
+                                       $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
+                                       if ($func)      {
+                                               $parseString = trim(substr($parseString,1));    // Strip of "("
+                                               $stack[$pnt]['type'] = 'function';
+                                               $stack[$pnt]['function'] = $func;
+                                               $level++;       // increse parenthesis level counter.
+                                       } else {
+                                               $stack[$pnt]['distinct'] = $this->nextPart($parseString,'^(distinct[[:space:]]+)');
+                                                       // Otherwise, look for regular fieldname:
+                                               if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)'))   {
+                                                       $stack[$pnt]['type'] = 'field';
+       
+                                                               // Explode fieldname into field and table:
+                                                       $tableField = explode('.',$fieldName,2);
+                                                       if (count($tableField)==2)      {
+                                                               $stack[$pnt]['table'] = $tableField[0];
+                                                               $stack[$pnt]['field'] = $tableField[1];
+                                                       } else {
+                                                               $stack[$pnt]['table'] = '';
+                                                               $stack[$pnt]['field'] = $tableField[0];
+                                                       }
                                                } else {
-                                                       $stack[$pnt]['table'] = '';
-                                                       $stack[$pnt]['field'] = $tableField[0];
+                                                       return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
                                                }
-                                       } else {
-                                               return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
                                        }
                                }
                        }
@@ -786,6 +797,41 @@ class t3lib_sqlparser {
        }
 
        /**
+        * Parsing a CASE ... WHEN flow-control construct.
+        * The output from this function can be compiled back with ->compileCaseStatement()
+        *
+        * @param       string          The string with the CASE ... WHEN construct, eg. "CASE field WHEN 1 THEN 0 ELSE ..." etc. NOTICE: passed by reference!
+        * @return      array           If successful parsing, returns an array, otherwise an error string.
+        * @see compileCaseConstruct()
+        */
+       protected function parseCaseStatement(&$parseString) {
+               $result = array();
+               $result['type'] = $this->nextPart($parseString, '^(case)[[:space:]]+');
+               if (!preg_match('/^when[[:space:]]+/i', $parseString)) {
+                       $value = $this->getValue($parseString);
+                       if (!(isset($value[1]) || is_numeric($value[0]))) {
+                               $result['case_field'] = $value[0]; 
+                       } else {
+                               $result['case_value'] = $value;
+                       }
+               }
+               $result['when'] = array();
+               while ($this->nextPart($parseString, '^(when)[[:space:]]')) {
+                       $when = array();
+                       $when['when_value'] = $this->parseWhereClause($parseString, '^(then)[[:space:]]+');
+                       $when['then_value'] = $this->getValue($parseString);
+                       $result['when'][] = $when;
+               }
+               if ($this->nextPart($parseString, '^(else)[[:space:]]+')) {
+                       $result['else'] = $this->getValue($parseString);
+               }
+               if (!$this->nextPart($parseString, '^(end)[[:space:]]+')) {
+                       return $this->parseError('No "end" keyword found as expected in parseCaseStatement()', $parseString);
+               }
+               return $result;
+       }
+
+       /**
         * 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.
         *
@@ -1585,6 +1631,11 @@ class t3lib_sqlparser {
                                        case 'function':
                                                $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
                                        break;
+                                       case 'flow-control':
+                                               if ($v['flow-control']['type'] === 'CASE') {
+                                                       $outputParts[$k] = $this->compileCaseStatement($v['flow-control']);
+                                               }
+                                       break;
                                        case 'field':
                                                $outputParts[$k] = ($v['distinct']?$v['distinct']:'').($v['table']?$v['table'].'.':'').$v['field'];
                                        break;
@@ -1610,6 +1661,34 @@ class t3lib_sqlparser {
        }
 
        /**
+        * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
+        *
+        * @param       array           Array of case components, (made with ->parseCaseStatement())
+        * @return      string          case when string
+        * @see parseCaseStatement()
+        */
+       protected function compileCaseStatement(array $components) {
+               $statement = 'CASE';
+               if (isset($components['case_field'])) {
+                       $statement .= ' ' . $components['case_field'];
+               } elseif (isset($components['case_value'])) {
+                       $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
+               }
+               foreach ($components['when'] as $when) {
+                       $statement .= ' WHEN ';
+                       $statement .= $this->compileWhereClause($when['when_value']);
+                       $statement .= ' THEN ';
+                       $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
+               }
+               if (isset($components['else'])) {
+                       $statement .= ' ELSE ';
+                       $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
+               }
+               $statement .= ' END';
+               return $statement;
+       }
+
+       /**
         * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
         *
         * @param       array           Array of table names, (made with ->parseFromTables())