Committed DBAL-related changes, see RFC mail from Dec 24th.
authorKarsten Dambekalns <karsten.dambekalns@typo3.org>
Tue, 27 Dec 2005 13:18:20 +0000 (13:18 +0000)
committerKarsten Dambekalns <karsten.dambekalns@typo3.org>
Tue, 27 Dec 2005 13:18:20 +0000 (13:18 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@944 709f56b5-9817-0410-a4d7-c38de5d9e867

ChangeLog
t3lib/class.t3lib_db.php
t3lib/class.t3lib_page.php
t3lib/class.t3lib_sqlengine.php
t3lib/class.t3lib_sqlparser.php
t3lib/stddb/tbl_be.php
typo3/sysext/cms/tbl_cms.php

index b1e2989..02b21fe 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,7 @@
+2005-12-27  Karsten Dambekalns <karsten@typo3.org>
+
+       * Applied DBAL-related changes. This fixes or helps to fix some issues, including bugs #1649, #1781, #2077 and others.
+
 2005-12-26  Sebastian Kurfuerst  <sebastian@garbage-group.de>
 
        * Fixed bug #0752: scope of BE- and FE-Cookie: can't cope with multidomain-setup
index 7dbba66..4624fd6 100755 (executable)
@@ -241,12 +241,17 @@ class t3lib_DB {
         * @see exec_SELECTquery()
         */
        function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')  {
+               if($foreign_table == $local_table) {
+                       $foreign_table_as = $foreign_table.uniqid('_join');
+               }
+
                $mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
                $mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
-               $mmWhere.= $foreign_table ? $foreign_table.'.uid='.$mm_table.'.uid_foreign' : '';
+               $mmWhere.= $foreign_table ? ($foreign_table_as ? $foreign_table_as : $foreign_table).'.uid='.$mm_table.'.uid_foreign' : '';
+
                return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
                                        $select,
-                                       ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','.$foreign_table : ''),
+                                       ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','. $foreign_table.($foreign_table_as ? ' AS '.$foreign_table_as : '') : ''),
                                        $mmWhere.' '.$whereClause,              // whereClauseMightContainGroupOrderBy
                                        $groupBy,
                                        $orderBy,
index 4da3f57..b620c3e 100755 (executable)
@@ -141,7 +141,7 @@ class t3lib_pageSelect {
 
                        // Filter out new place-holder pages in case we are NOT in a versioning preview (that means we are online!)
                if (!$this->versioningPreview)  {
-                       $this->where_hid_del.= ' AND pages.t3ver_state!=1';
+                       $this->where_hid_del.= ' AND NOT(pages.t3ver_state=1)';
                } else {
                                // For version previewing, make sure that enable-fields are not de-selecting hidden pages - we need versionOL() to unset them only if the overlay record instructs us to.
                        $this->versioningPreview_where_hid_del = $this->where_hid_del;  // Copy where_hid_del to other variable (used in relation to versionOL())
@@ -1004,8 +1004,8 @@ class t3lib_pageSelect {
        function getMultipleGroupsWhereClause($field, $table)   {
                $memberGroups = t3lib_div::intExplode(',',$GLOBALS['TSFE']->gr_list);
                $orChecks=array();
-               $orChecks[]=$field.'=""';       // If the field is empty, then OK
-               $orChecks[]=$field.'="0"';      // If the field contsains zero, then OK
+               $orChecks[]=$field.'=\'\'';     // If the field is empty, then OK
+               $orChecks[]=$field.'=\'0\'';    // If the field contsains zero, then OK
 
                foreach($memberGroups as $value)        {
                        // if ($value > 0)      {       // outcommented by Ingmar Schlecht because we want those pseudo groups like "hide at login" etc. to work. Original comment from Kasper was: "If user is member of a real group, not zero or negative pseudo group"
index 93066f9..4368424 100755 (executable)
@@ -248,7 +248,7 @@ class t3lib_sqlengine extends t3lib_sqlparser {
                                        $this->lastInsertedId = $saveArray[$fN];
                                }
                        }
-#debug(array($fields_values,$saveArray));
+
                                // Insert row in table:
                        $this->data[$table][] = $saveArray;
 
@@ -292,7 +292,7 @@ class t3lib_sqlengine extends t3lib_sqlparser {
                                foreach($fields_values as $fName => $fValue)    {
                                        $this->processAccordingToConfig($fields_values[$fName],$fieldInformation[$fName]);
                                }
-#debug($fields_values);
+
                                        // Do query, returns array with keys to the data array of the result:
                                $itemKeys = $this->selectFromData($table,$where);
 
@@ -732,8 +732,6 @@ class t3lib_sqlengine extends t3lib_sqlparser {
                                        unset($itemKeys[$kk]);
                                }
                        }
-
-#                      echo $this->debug_printResultSet($this->getResultSet($itemKeys,$table,'uid,tstamp'));
                }
        }
 
index c4d87a2..f1959c7 100755 (executable)
@@ -662,17 +662,19 @@ class t3lib_sqlparser {
         */
        function parseFieldList(&$parseString, $stopRegex='')   {
 
-                       // Prepare variables:
-               $parseString = $this->trimSQL($parseString);
-               $this->lastStopKeyWord = '';
-               $this->parse_error = '';
+               $stack = array();       // Contains the parsed content
 
+               if(strlen($parseString)==0) return $stack;  // FIXME - should never happen, why does it?
 
-               $stack = array();       // Contains the parsed content
                $pnt = 0;                       // Pointer to positions in $stack
                $level = 0;                     // Indicates the parenthesis level we are at.
                $loopExit = 0;          // Recursivity brake.
 
+                       // Prepare variables:
+               $parseString = $this->trimSQL($parseString);
+               $this->lastStopKeyWord = '';
+               $this->parse_error = '';
+
                        // $parseString is continously shortend by the process and we keep parsing it till it is zero:
                while (strlen($parseString)) {
 
@@ -710,6 +712,7 @@ class t3lib_sqlparser {
                                        $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';
@@ -724,7 +727,7 @@ class t3lib_sqlparser {
                                                        $stack[$pnt]['field'] = $tableField[0];
                                                }
                                        } else {
-                                               return $this->parseError('No field name found as expected',$parseString);
+                                               return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
                                        }
                                }
                        }
@@ -751,7 +754,7 @@ class t3lib_sqlparser {
 
                                        // Looking for comma (since the stop-keyword did not trigger a return...)
                                if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
-                                       return $this->parseError('No comma found as expected',$parseString);
+                                       return $this->parseError('No comma found as expected in parseFieldList()',$parseString);
                                }
 
                                        // Increasing pointer:
@@ -761,7 +764,7 @@ class t3lib_sqlparser {
                                // Check recursivity brake:
                        $loopExit++;
                        if ($loopExit>500)      {
-                               return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
+                               return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...',$parseString);
                        }
                }
 
@@ -793,19 +796,47 @@ class t3lib_sqlparser {
                while (strlen($parseString)) {
                                // Looking for the table:
                        if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)'))   {
+                                       // Looking for stop-keywords before fetching potential table alias:
+                                       if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
+                                               $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
+                                               return $stack;
+                                       }
+                                       if(!preg_match('/^(LEFT|JOIN)[[:space:]]+/i',$parseString)) {
+                                               $stack[$pnt]['as_keyword'] = $this->nextPart($parseString,'^(AS[[:space:]]+)');
                            $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*');
-                       } else return $this->parseError('No table name found as expected!',$parseString);
+                                       }
+                       } else return $this->parseError('No table name found as expected in parseFromTables()!',$parseString);
 
                                // Looking for JOIN
-                       if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+'))   {
+                       if ($join = $this->nextPart($parseString,'^(LEFT[[:space:]]+JOIN|JOIN)[[:space:]]+'))   {
                                $stack[$pnt]['JOIN']['type'] = $join;
                                if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1))   {
                                        $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
                                        $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
                                        if ($field1 && $field2) {
+
+                                               // Explode fields into field and table:
+                                               $tableField = explode('.',$field1,2);
+                                               $field1 = array();
+                                               if (count($tableField)!=2)      {
+                                                       $field1['table'] = '';
+                                                       $field1['field'] = $tableField[0];
+                                               } else {
+                                                       $field1['table'] = $tableField[0];
+                                                       $field1['field'] = $tableField[1];
+                                               }
+                                               $tableField = explode('.',$field2,2);
+                                               $field2 = array();
+                                               if (count($tableField)!=2)      {
+                                                       $field2['table'] = '';
+                                                       $field2['field'] = $tableField[0];
+                                               } else {
+                                                       $field2['table'] = $tableField[0];
+                                                       $field2['field'] = $tableField[1];
+                                               }
                                                $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
-                                       } else return $this->parseError('No join fields found!',$parseString);
-                               } else  return $this->parseError('No join table found!',$parseString);
+                                       } else return $this->parseError('No join fields found in parseFromTables()!',$parseString);
+                               } else  return $this->parseError('No join table found in parseFromTables()!',$parseString);
                        }
 
                                // Looking for stop-keywords:
@@ -816,7 +847,7 @@ class t3lib_sqlparser {
 
                                // Looking for comma:
                        if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
-                               return $this->parseError('No comma found as expected',$parseString);
+                               return $this->parseError('No comma found as expected in parseFromTables()',$parseString);
                        }
 
                                // Increasing pointer:
@@ -825,7 +856,7 @@ class t3lib_sqlparser {
                                // Check recursivity brake:
                        $loopExit++;
                        if ($loopExit>500)      {
-                               return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
+                               return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...',$parseString);
                        }
                }
 
@@ -880,7 +911,7 @@ class t3lib_sqlparser {
                                                $stack[$level][$pnt[$level]]['field'] = $tableField[0];
                                        }
                                } else {
-                                       return $this->parseError('No field name found as expected',$parseString);
+                                       return $this->parseError('No field name found as expected in parseWhereClause()',$parseString);
                                }
 
                                        // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
@@ -910,12 +941,12 @@ class t3lib_sqlparser {
                                                // Make recursivity check:
                                        $loopExit++;
                                        if ($loopExit>500)      {
-                                               return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
+                                               return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...',$parseString);
                                        }
                                }
 
                                        // Detecting the operator for the next level; support for AND, OR and &&):
-                               $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
+                               $op = $this->nextPart($parseString,'^(AND[[:space:]]+NOT|OR[[:space:]]+NOT|AND|OR)(\(|[[:space:]]+)');
                                if ($op)        {
                                        $stack[$level][$pnt[$level]]['operator'] = $op;
                                } elseif (strlen($parseString)) {
@@ -925,7 +956,7 @@ class t3lib_sqlparser {
                                                $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
                                                return $stack[0];
                                        } else {
-                                               return $this->parseError('No operator, but parsing not finished.',$parseString);
+                                               return $this->parseError('No operator, but parsing not finished in parseWhereClause().',$parseString);
                                        }
                                }
                        }
@@ -933,7 +964,7 @@ class t3lib_sqlparser {
                                // Make recursivity check:
                        $loopExit++;
                        if ($loopExit>500)      {
-                               return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
+                               return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...',$parseString);
                        }
                }
 
@@ -958,14 +989,14 @@ class t3lib_sqlparser {
                $result = array();
 
                        // Field type:
-               if ($result['fieldType'] =  $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|bigint|double|numeric|decimal|varchar|char|text|tinytext|mediumtext|longtext|blob|tinyblob|mediumblob|longblob)([[:space:]]+|\()'))  {
+               if ($result['fieldType'] =  $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|bigint|double|numeric|decimal|varchar|char|text|tinytext|mediumtext|longtext|blob|tinyblob|mediumblob|longblob)([[:space:],]+|\()')) {
 
                                // Looking for value:
                        if (substr($parseString,0,1)=='(')      {
                                $parseString = substr($parseString,1);
                                if ($result['value'] =  $this->nextPart($parseString,'^([^)]*)'))       {
                                        $parseString = ltrim(substr($parseString,1));
-                               } else return $this->parseError('No end-parenthesis for value found!',$parseString);
+                               } else return $this->parseError('No end-parenthesis for value found in parseFieldDef()!',$parseString);
                        }
 
                                // Looking for keywords
@@ -980,7 +1011,9 @@ class t3lib_sqlparser {
                                        break;
                                }
                        }
-               } else return $this->parseError('Field type unknown!',$parseString);
+               } else {
+                       return $this->parseError('Field type unknown in parseFieldDef()!',$parseString);
+               }
 
                return $result;
        }
@@ -1002,7 +1035,7 @@ class t3lib_sqlparser {
         ************************************/
 
        /**
-        * Strips of a part of the parseString and returns the matching part.
+        * Strips off a part of the parseString and returns the matching part.
         * Helper function for the parsing methods.
         *
         * @param       string          Parse string; if $regex finds anything the value of the first () level will be stripped of the string in the beginning. Further $parseString is left-trimmed (on success). Notice; parsestring is passed by reference.
@@ -1011,7 +1044,6 @@ class t3lib_sqlparser {
         * @return      string          The value of the first parenthesis level of the REGEX.
         */
        function nextPart(&$parseString,$regex,$trimAll=FALSE)  {
-               //if (eregi($regex,$parseString.' ', $reg))     {       // Adding space char because [[:space:]]+ is often a requirement in regex's
                if (preg_match('/'.$regex.'/i',$parseString.' ', $reg)) {       // Adding space char because [[:space:]]+ is often a requirement in regex's
                        $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
                        return $reg[1];
@@ -1026,7 +1058,6 @@ class t3lib_sqlparser {
         * @return      string          The value (string/integer). Otherwise an array with error message in first key (0)
         */
        function getValue(&$parseString,$comparator='') {
-               //if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator))))       {       // List of values:
                if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(str_replace(array(' ',"\n","\r","\t"),'',$comparator))))      {       // List of values:
                        if ($this->nextPart($parseString,'^([(])'))     {
                                $listValues = array();
@@ -1435,7 +1466,7 @@ return $str;
                                                $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
                                        break;
                                        case 'field':
-                                               $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
+                                               $outputParts[$k] = ($v['distinct']?$v['distinct']:'').($v['table']?$v['table'].'.':'').$v['field'];
                                        break;
                                }
 
@@ -1480,9 +1511,13 @@ return $str;
                                }
 
                                if (is_array($v['JOIN']))       {
-                                       $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
+                                       $outputParts[$k] .= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON ';
+                                       $outputParts[$k] .= ($v['JOIN']['ON'][0]['table']) ? $v['JOIN']['ON'][0]['table'].'.' : '';
+                                       $outputParts[$k] .= $v['JOIN']['ON'][0]['field'];
+                                       $outputParts[$k] .= '=';
+                                       $outputParts[$k] .= ($v['JOIN']['ON'][1]['table']) ? $v['JOIN']['ON'][0]['table'].'.' : '';
+                                       $outputParts[$k] .= $v['JOIN']['ON'][1]['field'];
                                }
-
                        }
                }
 
@@ -1658,8 +1693,6 @@ return $str;
         * @return      string          Query if all is well, otherwise exit.
         */
        function debug_testSQL($SQLquery)       {
-#              return $SQLquery;
-#debug(array($SQLquery));
 
                        // Getting result array:
                $parseResult = $this->parseSQL($SQLquery);
index 28b6059..1443888 100755 (executable)
@@ -624,7 +624,7 @@ $TCA['be_groups'] = Array (
                        'config' => Array (
                                'type' => 'select',
                                'foreign_table' => 'be_groups',
-                               'foreign_table_where' => 'AND be_groups.uid != ###THIS_UID### AND NOT be_groups.hidden ORDER BY be_groups.title',
+                               'foreign_table_where' => 'AND NOT(be_groups.uid = ###THIS_UID###) AND be_groups.hidden=0 ORDER BY be_groups.title',
                                'size' => '5',
                                'autoSizeMax' => 50,
                                'maxitems' => 20,
index 6c4c562..ff778c6 100755 (executable)
@@ -315,7 +315,7 @@ $TCA['fe_groups'] = Array (
                        'config' => Array (
                                'type' => 'select',
                                'foreign_table' => 'fe_groups',
-                               'foreign_table_where' => 'AND fe_groups.uid != ###THIS_UID### AND NOT fe_groups.hidden ORDER BY fe_groups.title',
+                               'foreign_table_where' => 'AND NOT(fe_groups.uid = ###THIS_UID###) AND fe_groups.hidden=0 ORDER BY fe_groups.title',
                                'size' => 4,
                                'autoSizeMax' => 10,
                                'minitems' => 0,