"TYPO3 4.0.1: typo3_src-4.0.1/t3lib/class.t3lib_sqlparser.php Source File", "datetime" => "Sat Dec 2 19:22:18 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

class.t3lib_sqlparser.php

00001 <?php
00002 /***************************************************************
00003 *  Copyright notice
00004 *
00005 *  (c) 2004-2006 Kasper Skaarhoj (kasperYYYY@typo3.com)
00006 *  All rights reserved
00007 *
00008 *  This script is part of the TYPO3 project. The TYPO3 project is
00009 *  free software; you can redistribute it and/or modify
00010 *  it under the terms of the GNU General Public License as published by
00011 *  the Free Software Foundation; either version 2 of the License, or
00012 *  (at your option) any later version.
00013 *
00014 *  The GNU General Public License can be found at
00015 *  http://www.gnu.org/copyleft/gpl.html.
00016 *  A copy is found in the textfile GPL.txt and important notices to the license
00017 *  from the author is found in LICENSE.txt distributed with these scripts.
00018 *
00019 *
00020 *  This script is distributed in the hope that it will be useful,
00021 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00022 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00023 *  GNU General Public License for more details.
00024 *
00025 *  This copyright notice MUST APPEAR in all copies of the script!
00026 ***************************************************************/
00107 class t3lib_sqlparser {
00108 
00109                 // Parser:
00110         var $parse_error = '';                                          // Parsing error string
00111         var $lastStopKeyWord = '';                                      // Last stop keyword used.
00112 
00113 
00114 
00115 
00116         /*************************************
00117          *
00118          * SQL Parsing, full queries
00119          *
00120          **************************************/
00121 
00129         function parseSQL($parseString) {
00130                         // Prepare variables:
00131                 $parseString = $this->trimSQL($parseString);
00132                 $this->parse_error = '';
00133                 $result = array();
00134 
00135                         // Finding starting keyword of string:
00136                 $_parseString = $parseString;   // Protecting original string...
00137                 $keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|DROP[[:space:]]+TABLE|CREATE[[:space:]]+TABLE|CREATE[[:space:]]+DATABASE|ALTER[[:space:]]+TABLE)[[:space:]]+');
00138                 $keyword = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$keyword));
00139 
00140                 switch($keyword)        {
00141                         case 'SELECT':
00142                                         // Parsing SELECT query:
00143                                 $result = $this->parseSELECT($parseString);
00144                         break;
00145                         case 'UPDATE':
00146                                         // Parsing UPDATE query:
00147                                 $result = $this->parseUPDATE($parseString);
00148                         break;
00149                         case 'INSERTINTO':
00150                                         // Parsing INSERT query:
00151                                 $result = $this->parseINSERT($parseString);
00152                         break;
00153                         case 'DELETEFROM':
00154                                         // Parsing DELETE query:
00155                                 $result = $this->parseDELETE($parseString);
00156                         break;
00157                         case 'EXPLAIN':
00158                                         // Parsing EXPLAIN SELECT query:
00159                                 $result = $this->parseEXPLAIN($parseString);
00160                         break;
00161                         case 'DROPTABLE':
00162                                         // Parsing DROP TABLE query:
00163                                 $result = $this->parseDROPTABLE($parseString);
00164                         break;
00165                         case 'ALTERTABLE':
00166                                         // Parsing ALTER TABLE query:
00167                                 $result = $this->parseALTERTABLE($parseString);
00168                         break;
00169                         case 'CREATETABLE':
00170                                         // Parsing CREATE TABLE query:
00171                                 $result = $this->parseCREATETABLE($parseString);
00172                         break;
00173                         case 'CREATEDATABASE':
00174                                         // Parsing CREATE DATABASE query:
00175                                 $result = $this->parseCREATEDATABASE($parseString);
00176                         break;
00177                         default:
00178                                 $result = $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
00179                         break;
00180                 }
00181 
00182                 return $result;
00183         }
00184 
00192         function parseSELECT($parseString)      {
00193 
00194                         // Removing SELECT:
00195                 $parseString = $this->trimSQL($parseString);
00196                 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^SELECT[[:space:]]+','',$parseString);
00197 
00198                         // Init output variable:
00199                 $result = array();
00200                 $result['type'] = 'SELECT';
00201 
00202                         // Looking for STRAIGHT_JOIN keyword:
00203                 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
00204 
00205                         // Select fields:
00206                 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
00207                 if ($this->parse_error) { return $this->parse_error; }
00208 
00209                         // Continue if string is not ended:
00210                 if ($parseString)       {
00211 
00212                                 // Get table list:
00213                         $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
00214                         if ($this->parse_error) { return $this->parse_error; }
00215 
00216                                 // If there are more than just the tables (a WHERE clause that would be...)
00217                         if ($parseString)       {
00218 
00219                                         // Get WHERE clause:
00220                                 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
00221                                 if ($this->parse_error) { return $this->parse_error; }
00222 
00223                                         // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
00224                                 if ($this->lastStopKeyWord)     {
00225 
00226                                                 // GROUP BY parsing:
00227                                         if ($this->lastStopKeyWord == 'GROUPBY')        {
00228                                                 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
00229                                                 if ($this->parse_error) { return $this->parse_error; }
00230                                         }
00231 
00232                                                 // ORDER BY parsing:
00233                                         if ($this->lastStopKeyWord == 'ORDERBY')        {
00234                                                 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
00235                                                 if ($this->parse_error) { return $this->parse_error; }
00236                                         }
00237 
00238                                                 // LIMIT parsing:
00239                                         if ($this->lastStopKeyWord == 'LIMIT')  {
00240                                                 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/',trim($parseString)))        {
00241                                                         $result['LIMIT'] = $parseString;
00242                                                 } else {
00243                                                         return $this->parseError('No value for limit!',$parseString);
00244                                                 }
00245                                         }
00246                                 }
00247                         }
00248                 } else return $this->parseError('No table to select from!',$parseString);
00249 
00250                         // Return result:
00251                 return $result;
00252         }
00253 
00261         function parseUPDATE($parseString)      {
00262 
00263                         // Removing UPDATE
00264                 $parseString = $this->trimSQL($parseString);
00265                 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^UPDATE[[:space:]]+','',$parseString);
00266 
00267                         // Init output variable:
00268                 $result = array();
00269                 $result['type'] = 'UPDATE';
00270 
00271                         // Get table:
00272                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00273 
00274                         // Continue if string is not ended:
00275                 if ($result['TABLE'])   {
00276                         if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+'))        {
00277 
00278                                 $comma = TRUE;
00279 
00280                                         // Get field/value pairs:
00281                                 while($comma)   {
00282                                         if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
00283                                                 $this->nextPart($parseString,'^(=)');   // Strip of "=" sign.
00284                                                 $value = $this->getValue($parseString);
00285                                                 $result['FIELDS'][$fieldName] = $value;
00286                                         } else return $this->parseError('No fieldname found',$parseString);
00287 
00288                                         $comma = $this->nextPart($parseString,'^(,)');
00289                                 }
00290 
00291                                         // WHERE
00292                                 if ($this->nextPart($parseString,'^(WHERE)'))   {
00293                                         $result['WHERE'] = $this->parseWhereClause($parseString);
00294                                         if ($this->parse_error) { return $this->parse_error; }
00295                                 }
00296                         } else return $this->parseError('Query missing SET...',$parseString);
00297                 } else return $this->parseError('No table found!',$parseString);
00298 
00299                         // Should be no more content now:
00300                 if ($parseString)       {
00301                         return $this->parseError('Still content in clause after parsing!',$parseString);
00302                 }
00303 
00304                         // Return result:
00305                 return $result;
00306         }
00307 
00315         function parseINSERT($parseString)      {
00316 
00317                         // Removing INSERT
00318                 $parseString = $this->trimSQL($parseString);
00319                 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
00320 
00321                         // Init output variable:
00322                 $result = array();
00323                 $result['type'] = 'INSERT';
00324 
00325                         // Get table:
00326                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
00327 
00328                 if ($result['TABLE'])   {
00329 
00330                         if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+'))      {       // In this case there are no field names mentioned in the SQL!
00331                                         // Get values/fieldnames (depending...)
00332                                 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
00333                                 if ($this->parse_error) { return $this->parse_error; }
00334                         } else {        // There are apparently fieldnames listed:
00335                                 $fieldNames = $this->getValue($parseString,'_LIST');
00336                                 if ($this->parse_error) { return $this->parse_error; }
00337 
00338                                 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+'))      {       // "VALUES" keyword binds the fieldnames to values:
00339 
00340                                         $values = $this->getValue($parseString,'IN');   // Using the "getValue" function to get the field list...
00341                                         if ($this->parse_error) { return $this->parse_error; }
00342 
00343                                         foreach($fieldNames as $k => $fN)       {
00344                                                 if (preg_match('/^[[:alnum:]_]+$/',$fN))        {
00345                                                         if (isset($values[$k])) {
00346                                                                 if (!isset($result['FIELDS'][$fN]))     {
00347                                                                         $result['FIELDS'][$fN] = $values[$k];
00348                                                                 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
00349                                                         } else return $this->parseError('No value set!',$parseString);
00350                                                 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
00351                                         }
00352                                         if (isset($values[$k+1]))       {
00353                                                 return $this->parseError('Too many values in list!',$parseString);
00354                                         }
00355                                 } else return $this->parseError('VALUES keyword expected',$parseString);
00356                         }
00357                 }  else return $this->parseError('No table found!',$parseString);
00358 
00359                         // Should be no more content now:
00360                 if ($parseString)       {
00361                         return $this->parseError('Still content after parsing!',$parseString);
00362                 }
00363 
00364                         // Return result
00365                 return $result;
00366         }
00367 
00375         function parseDELETE($parseString)      {
00376 
00377                         // Removing DELETE
00378                 $parseString = $this->trimSQL($parseString);
00379                 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
00380 
00381                         // Init output variable:
00382                 $result = array();
00383                 $result['type'] = 'DELETE';
00384 
00385                         // Get table:
00386                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00387 
00388                 if ($result['TABLE'])   {
00389 
00390                                 // WHERE
00391                         if ($this->nextPart($parseString,'^(WHERE)'))   {
00392                                 $result['WHERE'] = $this->parseWhereClause($parseString);
00393                                 if ($this->parse_error) { return $this->parse_error; }
00394                         }
00395                 } else return $this->parseError('No table found!',$parseString);
00396 
00397                         // Should be no more content now:
00398                 if ($parseString)       {
00399                         return $this->parseError('Still content in clause after parsing!',$parseString);
00400                 }
00401 
00402                         // Return result:
00403                 return $result;
00404         }
00405 
00413         function parseEXPLAIN($parseString)     {
00414 
00415                         // Removing EXPLAIN
00416                 $parseString = $this->trimSQL($parseString);
00417                 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
00418 
00419                         // Init output variable:
00420                 $result = $this->parseSELECT($parseString);
00421                 if (is_array($result))  {
00422                         $result['type'] = 'EXPLAIN';
00423                 }
00424 
00425                 return $result;
00426         }
00427 
00435         function parseCREATETABLE($parseString) {
00436 
00437                         // Removing CREATE TABLE
00438                 $parseString = $this->trimSQL($parseString);
00439                 $parseString = ltrim(substr(ltrim(substr($parseString,6)),5)); // REMOVE eregi_replace('^CREATE[[:space:]]+TABLE[[:space:]]+','',$parseString);
00440 
00441                         // Init output variable:
00442                 $result = array();
00443                 $result['type'] = 'CREATETABLE';
00444 
00445                         // Get table:
00446                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
00447 
00448                 if ($result['TABLE'])   {
00449 
00450                                 // While the parseString is not yet empty:
00451                         while(strlen($parseString)>0)   {
00452                                 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\()'))      {       // Getting key
00453                                         $key = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$key));
00454 
00455                                         switch($key)    {
00456                                                 case 'PRIMARYKEY':
00457                                                         $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString,'_LIST');
00458                                                         if ($this->parse_error) { return $this->parse_error; }
00459                                                 break;
00460                                                 case 'UNIQUE':
00461                                                 case 'UNIQUEKEY':
00462                                                         if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()'))      {
00463                                                                 $result['KEYS']['UNIQUE'] = array($keyName => $this->getValue($parseString,'_LIST'));
00464                                                                 if ($this->parse_error) { return $this->parse_error; }
00465                                                         } else return $this->parseError('No keyname found',$parseString);
00466                                                 break;
00467                                                 case 'KEY':
00468                                                         if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()'))      {
00469                                                                 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
00470                                                                 if ($this->parse_error) { return $this->parse_error; }
00471                                                         } else return $this->parseError('No keyname found',$parseString);
00472                                                 break;
00473                                         }
00474                                 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))   {       // Getting field:
00475                                         $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
00476                                         if ($this->parse_error) { return $this->parse_error; }
00477                                 }
00478 
00479                                         // Finding delimiter:
00480                                 $delim = $this->nextPart($parseString, '^(,|\))');
00481                                 if (!$delim)    {
00482                                         return $this->parseError('No delimiter found',$parseString);
00483                                 } elseif ($delim==')')  {
00484                                         break;
00485                                 }
00486                         }
00487 
00488                                 // Finding what is after the table definition - table type in MySQL
00489                         if ($delim==')')        {
00490                                 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)'))      {
00491                                         $result['tableType'] = $parseString;
00492                                         $parseString = '';
00493                                 }
00494                         } else return $this->parseError('No fieldname found!',$parseString);
00495 
00496                                 // Getting table type
00497                 } else return $this->parseError('No table found!',$parseString);
00498 
00499                         // Should be no more content now:
00500                 if ($parseString)       {
00501                         return $this->parseError('Still content in clause after parsing!',$parseString);
00502                 }
00503 
00504                 return $result;
00505         }
00506 
00514         function parseALTERTABLE($parseString)  {
00515 
00516                         // Removing ALTER TABLE
00517                 $parseString = $this->trimSQL($parseString);
00518                 $parseString = ltrim(substr(ltrim(substr($parseString,5)),5)); // REMOVE eregi_replace('^ALTER[[:space:]]+TABLE[[:space:]]+','',$parseString);
00519 
00520                         // Init output variable:
00521                 $result = array();
00522                 $result['type'] = 'ALTERTABLE';
00523 
00524                         // Get table:
00525                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00526 
00527                 if ($result['TABLE'])   {
00528                         if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|DROP|ADD|RENAME)([[:space:]]+|\()'))        {
00529                                 $actionKey = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$result['action']));
00530 
00531                                         // Getting field:
00532                                 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))  {
00533 
00534                                         switch($actionKey)      {
00535                                                 case 'ADD':
00536                                                         $result['FIELD'] = $fieldKey;
00537                                                         $result['definition'] = $this->parseFieldDef($parseString);
00538                                                         if ($this->parse_error) { return $this->parse_error; }
00539                                                 break;
00540                                                 case 'DROP':
00541                                                 case 'RENAME':
00542                                                         $result['FIELD'] = $fieldKey;
00543                                                 break;
00544                                                 case 'CHANGE':
00545                                                         $result['FIELD'] = $fieldKey;
00546                                                         if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+'))        {
00547                                                                 $result['definition'] = $this->parseFieldDef($parseString);
00548                                                                 if ($this->parse_error) { return $this->parse_error; }
00549                                                         } else return $this->parseError('No NEW field name found',$parseString);
00550                                                 break;
00551 
00552                                                 case 'ADDKEY':
00553                                                 case 'ADDPRIMARYKEY':
00554                                                         $result['KEY'] = $fieldKey;
00555                                                         $result['fields'] = $this->getValue($parseString,'_LIST');
00556                                                         if ($this->parse_error) { return $this->parse_error; }
00557                                                 break;
00558                                                 case 'DROPKEY':
00559                                                         $result['KEY'] = $fieldKey;
00560                                                 break;
00561                                                 case 'DROPPRIMARYKEY':
00562                                                         // ??? todo!
00563                                                 break;
00564                                         }
00565                                 } else return $this->parseError('No field name found',$parseString);
00566                         } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
00567                 } else return $this->parseError('No table found!',$parseString);
00568 
00569                         // Should be no more content now:
00570                 if ($parseString)       {
00571                         return $this->parseError('Still content in clause after parsing!',$parseString);
00572                 }
00573 
00574                 return $result;
00575         }
00576 
00583         function parseDROPTABLE($parseString)   {
00584 
00585                         // Removing DROP TABLE
00586                 $parseString = $this->trimSQL($parseString);
00587                 $parseString = ltrim(substr(ltrim(substr($parseString,4)),5)); // eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
00588 
00589                         // Init output variable:
00590                 $result = array();
00591                 $result['type'] = 'DROPTABLE';
00592 
00593                         // IF EXISTS
00594                 $result['ifExists']     = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
00595 
00596                         // Get table:
00597                 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00598 
00599                 if ($result['TABLE'])   {
00600 
00601                                 // Should be no more content now:
00602                         if ($parseString)       {
00603                                 return $this->parseError('Still content in clause after parsing!',$parseString);
00604                         }
00605 
00606                         return $result;
00607                 } else return $this->parseError('No table found!',$parseString);
00608         }
00609 
00616         function parseCREATEDATABASE($parseString)      {
00617 
00618                         // Removing CREATE DATABASE
00619                 $parseString = $this->trimSQL($parseString);
00620                 $parseString = ltrim(substr(ltrim(substr($parseString,6)),8)); // eregi_replace('^CREATE[[:space:]]+DATABASE[[:space:]]+','',$parseString);
00621 
00622                         // Init output variable:
00623                 $result = array();
00624                 $result['type'] = 'CREATEDATABASE';
00625 
00626                         // Get table:
00627                 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
00628 
00629                 if ($result['DATABASE'])        {
00630 
00631                                 // Should be no more content now:
00632                         if ($parseString)       {
00633                                 return $this->parseError('Still content in clause after parsing!',$parseString);
00634                         }
00635 
00636                         return $result;
00637                 } else return $this->parseError('No database found!',$parseString);
00638         }
00639 
00640 
00641 
00642 
00643 
00644 
00645 
00646 
00647 
00648 
00649 
00650 
00651 
00652 
00653 
00654         /**************************************
00655          *
00656          * SQL Parsing, helper functions for parts of queries
00657          *
00658          **************************************/
00659 
00670         function parseFieldList(&$parseString, $stopRegex='')   {
00671 
00672                 $stack = array();       // Contains the parsed content
00673 
00674                 if(strlen($parseString)==0) return $stack;  // FIXME - should never happen, why does it?
00675 
00676                 $pnt = 0;                       // Pointer to positions in $stack
00677                 $level = 0;                     // Indicates the parenthesis level we are at.
00678                 $loopExit = 0;          // Recursivity brake.
00679 
00680                         // Prepare variables:
00681                 $parseString = $this->trimSQL($parseString);
00682                 $this->lastStopKeyWord = '';
00683                 $this->parse_error = '';
00684 
00685                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00686                 while (strlen($parseString)) {
00687 
00688                                 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
00689                         if ($level>0)   {       // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
00690 
00691                                         // Accumulate function content until next () parenthesis:
00692                                 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
00693                                 $stack[$pnt]['func_content.'][] = array(
00694                                         'level' => $level,
00695                                         'func_content' => substr($funcContent,0,-1)
00696                                 );
00697                                 $stack[$pnt]['func_content'].= $funcContent;
00698 
00699                                         // Detecting ( or )
00700                                 switch(substr($stack[$pnt]['func_content'],-1)) {
00701                                         case '(':
00702                                                 $level++;
00703                                         break;
00704                                         case ')':
00705                                                 $level--;
00706                                                 if (!$level)    {       // If this was the last parenthesis:
00707                                                         $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
00708                                                         $parseString = ltrim($parseString);     // Remove any whitespace after the parenthesis.
00709                                                 }
00710                                         break;
00711                                 }
00712                         } else {        // Outside parenthesis, looking for next field:
00713 
00714                                         // Looking for a known function (only known functions supported)
00715                                 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
00716                                 if ($func)      {
00717                                         $parseString = trim(substr($parseString,1));    // Strip of "("
00718                                         $stack[$pnt]['type'] = 'function';
00719                                         $stack[$pnt]['function'] = $func;
00720                                         $level++;       // increse parenthesis level counter.
00721                                 } else {
00722                                         $stack[$pnt]['distinct'] = $this->nextPart($parseString,'^(distinct[[:space:]]+)');
00723                                                 // Otherwise, look for regular fieldname:
00724                                         if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)'))   {
00725                                                 $stack[$pnt]['type'] = 'field';
00726 
00727                                                         // Explode fieldname into field and table:
00728                                                 $tableField = explode('.',$fieldName,2);
00729                                                 if (count($tableField)==2)      {
00730                                                         $stack[$pnt]['table'] = $tableField[0];
00731                                                         $stack[$pnt]['field'] = $tableField[1];
00732                                                 } else {
00733                                                         $stack[$pnt]['table'] = '';
00734                                                         $stack[$pnt]['field'] = $tableField[0];
00735                                                 }
00736                                         } else {
00737                                                 return $this->parseError('No field name found as expected in parseFieldList()',$parseString);
00738                                         }
00739                                 }
00740                         }
00741 
00742                                 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
00743                         if (!$level)    {
00744 
00745                                         // Looking for "AS" alias:
00746                                 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+'))    {
00747                                         $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
00748                                         $stack[$pnt]['as_keyword'] = $as;
00749                                 }
00750 
00751                                         // Looking for "ASC" or "DESC" keywords (for ORDER BY)
00752                                 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)'))        {
00753                                         $stack[$pnt]['sortDir'] = $sDir;
00754                                 }
00755 
00756                                         // Looking for stop-keywords:
00757                                 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
00758                                         $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
00759                                         return $stack;
00760                                 }
00761 
00762                                         // Looking for comma (since the stop-keyword did not trigger a return...)
00763                                 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
00764                                         return $this->parseError('No comma found as expected in parseFieldList()',$parseString);
00765                                 }
00766 
00767                                         // Increasing pointer:
00768                                 $pnt++;
00769                         }
00770 
00771                                 // Check recursivity brake:
00772                         $loopExit++;
00773                         if ($loopExit>500)      {
00774                                 return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...',$parseString);
00775                         }
00776                 }
00777 
00778                         // Return result array:
00779                 return $stack;
00780         }
00781 
00791         function parseFromTables(&$parseString, $stopRegex='')  {
00792 
00793                         // Prepare variables:
00794                 $parseString = $this->trimSQL($parseString);
00795                 $this->lastStopKeyWord = '';
00796                 $this->parse_error = '';
00797 
00798                 $stack = array();       // Contains the parsed content
00799                 $pnt = 0;                       // Pointer to positions in $stack
00800                 $loopExit = 0;          // Recursivity brake.
00801 
00802                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00803                 while (strlen($parseString)) {
00804                                 // Looking for the table:
00805                         if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)'))   {
00806                                         // Looking for stop-keywords before fetching potential table alias:
00807                                 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
00808                                         $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
00809                                         return $stack;
00810                                 }
00811                                 if(!preg_match('/^(LEFT|JOIN)[[:space:]]+/i',$parseString)) {
00812                                         $stack[$pnt]['as_keyword'] = $this->nextPart($parseString,'^(AS[[:space:]]+)');
00813                                         $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*');
00814                                 }
00815                         } else return $this->parseError('No table name found as expected in parseFromTables()!',$parseString);
00816 
00817                                 // Looking for JOIN
00818                         if ($join = $this->nextPart($parseString,'^(LEFT[[:space:]]+JOIN|LEFT[[:space:]]+OUTER[[:space:]]+JOIN|JOIN)[[:space:]]+'))     {
00819                                 $stack[$pnt]['JOIN']['type'] = $join;
00820                                 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1))   {
00821                                         $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
00822                                         $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
00823                                         if ($field1 && $field2) {
00824 
00825                                                 // Explode fields into field and table:
00826                                                 $tableField = explode('.',$field1,2);
00827                                                 $field1 = array();
00828                                                 if (count($tableField)!=2)      {
00829                                                         $field1['table'] = '';
00830                                                         $field1['field'] = $tableField[0];
00831                                                 } else {
00832                                                         $field1['table'] = $tableField[0];
00833                                                         $field1['field'] = $tableField[1];
00834                                                 }
00835                                                 $tableField = explode('.',$field2,2);
00836                                                 $field2 = array();
00837                                                 if (count($tableField)!=2)      {
00838                                                         $field2['table'] = '';
00839                                                         $field2['field'] = $tableField[0];
00840                                                 } else {
00841                                                         $field2['table'] = $tableField[0];
00842                                                         $field2['field'] = $tableField[1];
00843                                                 }
00844                                                 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
00845                                         } else return $this->parseError('No join fields found in parseFromTables()!',$parseString);
00846                                 } else  return $this->parseError('No join table found in parseFromTables()!',$parseString);
00847                         }
00848 
00849                                 // Looking for stop-keywords:
00850                         if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
00851                                 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
00852                                 return $stack;
00853                         }
00854 
00855                                 // Looking for comma:
00856                         if (strlen($parseString) && !$this->nextPart($parseString,'^(,)'))      {
00857                                 return $this->parseError('No comma found as expected in parseFromTables()',$parseString);
00858                         }
00859 
00860                                 // Increasing pointer:
00861                         $pnt++;
00862 
00863                                 // Check recursivity brake:
00864                         $loopExit++;
00865                         if ($loopExit>500)      {
00866                                 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...',$parseString);
00867                         }
00868                 }
00869 
00870                         // Return result array:
00871                 return $stack;
00872         }
00873 
00882         function parseWhereClause(&$parseString, $stopRegex='') {
00883 
00884                         // Prepare variables:
00885                 $parseString = $this->trimSQL($parseString);
00886                 $this->lastStopKeyWord = '';
00887                 $this->parse_error = '';
00888 
00889                 $stack = array(0 => array());   // Contains the parsed content
00890                 $pnt = array(0 => 0);                   // Pointer to positions in $stack
00891                 $level = 0;                                             // Determines parenthesis level
00892                 $loopExit = 0;                                  // Recursivity brake.
00893 
00894                         // $parseString is continously shortend by the process and we keep parsing it till it is zero:
00895                 while (strlen($parseString)) {
00896 
00897                                 // Look for next parenthesis level:
00898                         $newLevel = $this->nextPart($parseString,'^([(])');
00899                         if ($newLevel=='(')     {                       // If new level is started, manage stack/pointers:
00900                                 $level++;                                       // Increase level
00901                                 $pnt[$level] = 0;                       // Reset pointer for this level
00902                                 $stack[$level] = array();       // Reset stack for this level
00903                         } else {        // If no new level is started, just parse the current level:
00904 
00905                                         // Find "modifyer", eg. "NOT or !"
00906                                 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
00907 
00908                                         // Fieldname:
00909                                 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)'))   {
00910 
00911                                                 // Parse field name into field and table:
00912                                         $tableField = explode('.',$fieldName,2);
00913                                         if (count($tableField)==2)      {
00914                                                 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
00915                                                 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
00916                                         } else {
00917                                                 $stack[$level][$pnt[$level]]['table'] = '';
00918                                                 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
00919                                         }
00920                                 } else {
00921                                         return $this->parseError('No field name found as expected in parseWhereClause()',$parseString);
00922                                 }
00923 
00924                                         // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
00925                                 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
00926                                 if (strlen($stack[$level][$pnt[$level]]['calc']))       {
00927                                                 // Finding value for calculation:
00928                                         $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
00929                                 }
00930 
00931                                         // Find "comparator":
00932                                 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS[[:space:]]+NOT|IS)');
00933                                 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
00934                                                 // Finding value for comparator:
00935                                         $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
00936                                         if ($this->parse_error) { return $this->parse_error; }
00937                                 }
00938 
00939                                         // Finished, increase pointer:
00940                                 $pnt[$level]++;
00941 
00942                                         // Checking if the current level is ended, in that case do stack management:
00943                                 while ($this->nextPart($parseString,'^([)])'))  {
00944                                         $level--;               // Decrease level:
00945                                         $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1];         // Copy stack
00946                                         $pnt[$level]++; // Increase pointer of the new level
00947 
00948                                                 // Make recursivity check:
00949                                         $loopExit++;
00950                                         if ($loopExit>500)      {
00951                                                 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...',$parseString);
00952                                         }
00953                                 }
00954 
00955                                         // Detecting the operator for the next level:
00956                                 $op = $this->nextPart($parseString,'^(AND[[:space:]]+NOT|OR[[:space:]]+NOT|AND|OR)(\(|[[:space:]]+)');
00957                                 if ($op)        {
00958                                         $stack[$level][$pnt[$level]]['operator'] = $op;
00959                                 } elseif (strlen($parseString)) {
00960 
00961                                                 // Looking for stop-keywords:
00962                                         if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))   {
00963                                                 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
00964                                                 return $stack[0];
00965                                         } else {
00966                                                 return $this->parseError('No operator, but parsing not finished in parseWhereClause().',$parseString);
00967                                         }
00968                                 }
00969                         }
00970 
00971                                 // Make recursivity check:
00972                         $loopExit++;
00973                         if ($loopExit>500)      {
00974                                 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...',$parseString);
00975                         }
00976                 }
00977 
00978                         // Return the stacks lowest level:
00979                 return $stack[0];
00980         }
00981 
00990         function parseFieldDef(&$parseString, $stopRegex='')    {
00991                         // Prepare variables:
00992                 $parseString = $this->trimSQL($parseString);
00993                 $this->lastStopKeyWord = '';
00994                 $this->parse_error = '';
00995 
00996                 $result = array();
00997 
00998                         // Field type:
00999                 if ($result['fieldType'] =  $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|bigint|double|numeric|decimal|float|varchar|char|text|tinytext|mediumtext|longtext|blob|tinyblob|mediumblob|longblob)([[:space:],]+|\()'))   {
01000 
01001                                 // Looking for value:
01002                         if (substr($parseString,0,1)=='(')      {
01003                                 $parseString = substr($parseString,1);
01004                                 if ($result['value'] =  $this->nextPart($parseString,'^([^)]*)'))       {
01005                                         $parseString = ltrim(substr($parseString,1));
01006                                 } else return $this->parseError('No end-parenthesis for value found in parseFieldDef()!',$parseString);
01007                         }
01008 
01009                                 // Looking for keywords
01010                         while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))'))     {
01011                                 $keywordCmp = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$keyword));
01012 
01013                                 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
01014 
01015                                 switch($keywordCmp)     {
01016                                         case 'DEFAULT':
01017                                                 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
01018                                         break;
01019                                 }
01020                         }
01021                 } else {
01022                         return $this->parseError('Field type unknown in parseFieldDef()!',$parseString);
01023                 }
01024 
01025                 return $result;
01026         }
01027 
01028 
01029 
01030 
01031 
01032 
01033 
01034 
01035 
01036 
01037 
01038         /************************************
01039          *
01040          * Parsing: Helper functions
01041          *
01042          ************************************/
01043 
01053         function nextPart(&$parseString,$regex,$trimAll=FALSE)  {
01054                 $reg = array();
01055                 if (preg_match('/'.$regex.'/i',$parseString.' ', $reg)) {       // Adding space char because [[:space:]]+ is often a requirement in regex's
01056                         $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
01057                         return $reg[1];
01058                 }
01059         }
01060 
01068         function getValue(&$parseString,$comparator='') {
01069                 $value = '';
01070 
01071                 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(str_replace(array(' ',"\n","\r","\t"),'',$comparator))))      {       // List of values:
01072                         if ($this->nextPart($parseString,'^([(])'))     {
01073                                 $listValues = array();
01074                                 $comma=',';
01075 
01076                                 while($comma==',')      {
01077                                         $listValues[] = $this->getValue($parseString);
01078                                         $comma = $this->nextPart($parseString,'^([,])');
01079                                 }
01080 
01081                                 $out = $this->nextPart($parseString,'^([)])');
01082                                 if ($out)       {
01083                                         if ($comparator=='_LIST')       {
01084                                                 $kVals = array();
01085                                                 foreach ($listValues as $vArr)  {
01086                                                         $kVals[] = $vArr[0];
01087                                                 }
01088                                                 return $kVals;
01089                                         } else {
01090                                                 return $listValues;
01091                                         }
01092                                 } else return array($this->parseError('No ) parenthesis in list',$parseString));
01093                         } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
01094 
01095                 } else {        // Just plain string value, in quotes or not:
01096 
01097                                 // Quote?
01098                         $firstChar = substr($parseString,0,1);
01099                         switch($firstChar)      {
01100                                 case '"':
01101                                         $value = array($this->getValueInQuotes($parseString,'"'),'"');
01102                                 break;
01103                                 case "'":
01104                                         $value = array($this->getValueInQuotes($parseString,"'"),"'");
01105                                 break;
01106                                 default:
01107                                         $reg = array();
01108                                         if (preg_match('/^([[:alnum:]._-]+)/i',$parseString, $reg))     {
01109                                                 $parseString = ltrim(substr($parseString,strlen($reg[0])));
01110                                                 $value = array($reg[1]);
01111                                         }
01112                                 break;
01113                         }
01114                 }
01115                 return $value;
01116         }
01117 
01126         function getValueInQuotes(&$parseString,$quote) {
01127 
01128                 $parts = explode($quote,substr($parseString,1));
01129                 $buffer = '';
01130                 foreach($parts as $k => $v)     {
01131                         $buffer.=$v;
01132 
01133                         $reg = array();
01134                         //preg_match('/[\]*$/',$v,$reg); // does not work. what is the *exact* meaning of the next line?
01135                         ereg('[\]*$',$v,$reg);
01136                         if ($reg AND strlen($reg[0])%2) {
01137                                 $buffer.=$quote;
01138                         } else {
01139                                 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
01140                                 return $this->parseStripslashes($buffer);
01141                         }
01142                 }
01143         }
01144 
01152         function parseStripslashes($str)        {
01153                 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
01154                 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
01155 
01156                 return str_replace($search, $replace, $str);
01157         }
01158 
01166         function compileAddslashes($str)        {
01167 return $str;
01168                 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
01169                 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
01170 
01171                 return str_replace($search, $replace, $str);
01172         }
01173 
01181         function parseError($msg,$restQuery)    {
01182                 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
01183                 return $this->parse_error;
01184         }
01185 
01195         function trimSQL($str)  {
01196                 return trim(rtrim($str, "; \r\n\t")).' ';
01197                 //return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
01198         }
01199 
01200 
01201 
01202 
01203 
01204 
01205 
01206 
01207 
01208 
01209 
01210 
01211         /*************************
01212          *
01213          * Compiling queries
01214          *
01215          *************************/
01216 
01224         function compileSQL($components)        {
01225                 switch($components['type'])     {
01226                         case 'SELECT':
01227                                 $query = $this->compileSELECT($components);
01228                         break;
01229                         case 'UPDATE':
01230                                 $query = $this->compileUPDATE($components);
01231                         break;
01232                         case 'INSERT':
01233                                 $query = $this->compileINSERT($components);
01234                         break;
01235                         case 'DELETE':
01236                                 $query = $this->compileDELETE($components);
01237                         break;
01238                         case 'EXPLAIN':
01239                                 $query = 'EXPLAIN '.$this->compileSELECT($components);
01240                         break;
01241                         case 'DROPTABLE':
01242                                 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
01243                         break;
01244                         case 'CREATETABLE':
01245                                 $query = $this->compileCREATETABLE($components);
01246                         break;
01247                         case 'ALTERTABLE':
01248                                 $query = $this->compileALTERTABLE($components);
01249                         break;
01250                 }
01251 
01252                 return $query;
01253         }
01254 
01262         function compileSELECT($components)     {
01263 
01264                         // Initialize:
01265                 $where = $this->compileWhereClause($components['WHERE']);
01266                 $groupBy = $this->compileFieldList($components['GROUPBY']);
01267                 $orderBy = $this->compileFieldList($components['ORDERBY']);
01268                 $limit = $components['LIMIT'];
01269 
01270                         // Make query:
01271                 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
01272                                 '.$this->compileFieldList($components['SELECT']).'
01273                                 FROM '.$this->compileFromTables($components['FROM']).
01274                                         (strlen($where)?'
01275                                 WHERE '.$where : '').
01276                                         (strlen($groupBy)?'
01277                                 GROUP BY '.$groupBy : '').
01278                                         (strlen($orderBy)?'
01279                                 ORDER BY '.$orderBy : '').
01280                                         (strlen($limit)?'
01281                                 LIMIT '.$limit : '');
01282 
01283                 return $query;
01284         }
01285 
01293         function compileUPDATE($components)     {
01294 
01295                         // Where clause:
01296                 $where = $this->compileWhereClause($components['WHERE']);
01297 
01298                         // Fields
01299                 $fields = array();
01300                 foreach($components['FIELDS'] as $fN => $fV)    {
01301                         $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01302                 }
01303 
01304                         // Make query:
01305                 $query = 'UPDATE '.$components['TABLE'].' SET
01306                                 '.implode(',
01307                                 ',$fields).'
01308                                 '.(strlen($where)?'
01309                                 WHERE '.$where : '');
01310 
01311                 return $query;
01312         }
01313 
01321         function compileINSERT($components)     {
01322 
01323                 if ($components['VALUES_ONLY']) {
01324                                 // Initialize:
01325                         $fields = array();
01326                         foreach($components['VALUES_ONLY'] as $fV)      {
01327                                 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01328                         }
01329 
01330                                 // Make query:
01331                         $query = 'INSERT INTO '.$components['TABLE'].'
01332                                         VALUES
01333                                         ('.implode(',
01334                                         ',$fields).')';
01335                 } else {
01336                                 // Initialize:
01337                         $fields = array();
01338                         foreach($components['FIELDS'] as $fN => $fV)    {
01339                                 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
01340                         }
01341 
01342                                 // Make query:
01343                         $query = 'INSERT INTO '.$components['TABLE'].'
01344                                         ('.implode(',
01345                                         ',array_keys($fields)).')
01346                                         VALUES
01347                                         ('.implode(',
01348                                         ',$fields).')';
01349                 }
01350 
01351                 return $query;
01352         }
01353 
01361         function compileDELETE($components)     {
01362 
01363                         // Where clause:
01364                 $where = $this->compileWhereClause($components['WHERE']);
01365 
01366                         // Make query:
01367                 $query = 'DELETE FROM '.$components['TABLE'].
01368                                 (strlen($where)?'
01369                                 WHERE '.$where : '');
01370 
01371                 return $query;
01372         }
01373 
01381         function compileCREATETABLE($components)        {
01382 
01383                         // Create fields and keys:
01384                 $fieldsKeys = array();
01385                 foreach($components['FIELDS'] as $fN => $fCfg)  {
01386                         $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
01387                 }
01388                 foreach($components['KEYS'] as $kN => $kCfg)    {
01389                         if ($kN == 'PRIMARYKEY')        {
01390                                 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
01391                         } elseif ($kN == 'UNIQUE')      {
01392                                 $fieldsKeys[]='UNIQUE '.$kN.' ('.implode(',', $kCfg).')';
01393                         } else {
01394                                 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
01395                         }
01396                 }
01397 
01398                         // Make query:
01399                 $query = 'CREATE TABLE '.$components['TABLE'].' (
01400                         '.implode(',
01401                         ', $fieldsKeys).'
01402                         )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
01403 
01404                 return $query;
01405         }
01406 
01414         function compileALTERTABLE($components) {
01415 
01416                         // Make query:
01417                 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
01418 
01419                         // Based on action, add the final part:
01420                 switch(strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$components['action'])))     {
01421                         case 'ADD':
01422                                 $query.=' '.$this->compileFieldCfg($components['definition']);
01423                         break;
01424                         case 'CHANGE':
01425                                 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
01426                         break;
01427                         case 'DROP':
01428                         case 'DROPKEY':
01429                         break;
01430                         case 'ADDKEY':
01431                         case 'ADDPRIMARYKEY':
01432                                 $query.=' ('.implode(',',$components['fields']).')';
01433                         break;
01434                 }
01435 
01436                         // Return query
01437                 return $query;
01438         }
01439 
01440 
01441 
01442 
01443 
01444 
01445 
01446 
01447 
01448 
01449 
01450 
01451 
01452 
01453         /**************************************
01454          *
01455          * Compiling queries, helper functions for parts of queries
01456          *
01457          **************************************/
01458 
01467         function compileFieldList($selectFields)        {
01468 
01469                         // Prepare buffer variable:
01470                 $outputParts = array();
01471 
01472                         // Traverse the selectFields if any:
01473                 if (is_array($selectFields))    {
01474                         foreach($selectFields as $k => $v)      {
01475 
01476                                         // Detecting type:
01477                                 switch($v['type'])      {
01478                                         case 'function':
01479                                                 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
01480                                         break;
01481                                         case 'field':
01482                                                 $outputParts[$k] = ($v['distinct']?$v['distinct']:'').($v['table']?$v['table'].'.':'').$v['field'];
01483                                         break;
01484                                 }
01485 
01486                                         // Alias:
01487                                 if ($v['as'])   {
01488                                         $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
01489                                 }
01490 
01491                                         // Specifically for ORDER BY and GROUP BY field lists:
01492                                 if ($v['sortDir'])      {
01493                                         $outputParts[$k].= ' '.$v['sortDir'];
01494                                 }
01495                         }
01496                 }
01497 
01498                         // Return imploded buffer:
01499                 return implode(', ',$outputParts);
01500         }
01501 
01509         function compileFromTables($tablesArray)        {
01510 
01511                         // Prepare buffer variable:
01512                 $outputParts = array();
01513 
01514                         // Traverse the table names:
01515                 if (is_array($tablesArray))     {
01516                         foreach($tablesArray as $k => $v)       {
01517 
01518                                         // Set table name:
01519                                 $outputParts[$k] = $v['table'];
01520 
01521                                         // Add alias AS if there:
01522                                 if ($v['as'])   {
01523                                         $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
01524                                 }
01525 
01526                                 if (is_array($v['JOIN']))       {
01527                                         $outputParts[$k] .= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON ';
01528                                         $outputParts[$k] .= ($v['JOIN']['ON'][0]['table']) ? $v['JOIN']['ON'][0]['table'].'.' : '';
01529                                         $outputParts[$k] .= $v['JOIN']['ON'][0]['field'];
01530                                         $outputParts[$k] .= '=';
01531                                         $outputParts[$k] .= ($v['JOIN']['ON'][1]['table']) ? $v['JOIN']['ON'][1]['table'].'.' : '';
01532                                         $outputParts[$k] .= $v['JOIN']['ON'][1]['field'];
01533                                 }
01534                         }
01535                 }
01536 
01537                         // Return imploded buffer:
01538                 return implode(', ',$outputParts);
01539         }
01540 
01550         function compileWhereClause($clauseArray)       {
01551 
01552                         // Prepare buffer variable:
01553                 $output='';
01554 
01555                         // Traverse clause array:
01556                 if (is_array($clauseArray))     {
01557                         foreach($clauseArray as $k => $v)       {
01558 
01559                                         // Set operator:
01560                                 $output.=$v['operator'] ? ' '.$v['operator'] : '';
01561 
01562                                         // Look for sublevel:
01563                                 if (is_array($v['sub']))        {
01564                                         $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
01565                                 } else {
01566 
01567                                                 // Set field/table with modifying prefix if any:
01568                                         $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
01569 
01570                                                 // Set calculation, if any:
01571                                         if ($v['calc']) {
01572                                                 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
01573                                         }
01574 
01575                                                 // Set comparator:
01576                                         if ($v['comparator'])   {
01577                                                 $output.=' '.$v['comparator'];
01578 
01579                                                         // Detecting value type; list or plain:
01580                                                 if (t3lib_div::inList('NOTIN,IN',strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$v['comparator']))))       {
01581                                                         $valueBuffer = array();
01582                                                         foreach($v['value'] as $realValue)      {
01583                                                                 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
01584                                                         }
01585                                                         $output.=' ('.trim(implode(',',$valueBuffer)).')';
01586                                                 } else {
01587                                                         $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
01588                                                 }
01589                                         }
01590                                 }
01591                         }
01592                 }
01593 
01594                         // Return output buffer:
01595                 return $output;
01596         }
01597 
01604         function compileFieldCfg($fieldCfg)     {
01605 
01606                         // Set type:
01607                 $cfg = $fieldCfg['fieldType'];
01608 
01609                         // Add value, if any:
01610                 if (strlen($fieldCfg['value'])) {
01611                         $cfg.='('.$fieldCfg['value'].')';
01612                 }
01613 
01614                         // Add additional features:
01615                 if (is_array($fieldCfg['featureIndex']))        {
01616                         foreach($fieldCfg['featureIndex'] as $featureDef)       {
01617                                 $cfg.=' '.$featureDef['keyword'];
01618 
01619                                         // Add value if found:
01620                                 if (is_array($featureDef['value']))     {
01621                                         $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
01622                                 }
01623                         }
01624                 }
01625 
01626                         // Return field definition string:
01627                 return $cfg;
01628         }
01629 
01630 
01631 
01632 
01633 
01634 
01635 
01636 
01637 
01638 
01639 
01640         /*************************
01641          *
01642          * Debugging
01643          *
01644          *************************/
01645 
01653         function debug_parseSQLpart($part,$str) {
01654                 $retVal = false;
01655 
01656                 switch($part)   {
01657                         case 'SELECT':
01658                                 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
01659                         break;
01660                         case 'FROM':
01661                                 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
01662                         break;
01663                         case 'WHERE':
01664                                 $retVal = $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
01665                         break;
01666                 }
01667                 return $retVal;
01668         }
01669 
01678         function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
01679                 if ($caseInsensitive)   {
01680                         $str1 = strtoupper($str);
01681                         $str2 = strtoupper($newStr);
01682                 } else {
01683                         $str1 = $str;
01684                         $str2 = $newStr;
01685                 }
01686 
01687                         // Fixing escaped chars:
01688                 $search = array('\0', '\n', '\r', '\Z');
01689                 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
01690                 $str1 = str_replace($search, $replace, $str1);
01691                 $str2 = str_replace($search, $replace, $str2);
01692 
01693                         # Normally, commented out since they are needed only in tricky cases...
01694 #               $str1 = stripslashes($str1);
01695 #               $str2 = stripslashes($str2);
01696 
01697                 if (strcmp(str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str1)),str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str2))))    {
01698                         return array(
01699                                         str_replace(array(' ',"\t","\r","\n"),' ',$str),
01700                                         str_replace(array(' ',"\t","\r","\n"),' ',$newStr),
01701                                 );
01702                 }
01703         }
01704 
01711         function debug_testSQL($SQLquery)       {
01712 
01713                         // Getting result array:
01714                 $parseResult = $this->parseSQL($SQLquery);
01715 
01716                         // If result array was returned, proceed. Otherwise show error and exit.
01717                 if (is_array($parseResult))     {
01718 
01719                                 // Re-compile query:
01720                         $newQuery = $this->compileSQL($parseResult);
01721 
01722                                 // TEST the new query:
01723                         $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
01724 
01725                                 // Return new query if OK, otherwise show error and exit:
01726                         if (!is_array($testResult))     {
01727                                 return $newQuery;
01728                         } else {
01729                                 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
01730                                 exit;
01731                         }
01732                 } else {
01733                         debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
01734                         exit;
01735                 }
01736         }
01737 }
01738 
01739 
01740 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']) {
01741         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']);
01742 }
01743 ?>