Updated years from 2004 to 2005 + all function indexes. NO actual code changes done!
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_sqlparser.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2005 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 * A copy is found in the textfile GPL.txt and important notices to the license
17 * from the author is found in LICENSE.txt distributed with these scripts.
18 *
19 *
20 * This script is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
27 /**
28 * TYPO3 SQL parser
29 *
30 * $Id$
31 *
32 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
33 */
34 /**
35 * [CLASS/FUNCTION INDEX of SCRIPT]
36 *
37 *
38 *
39 * 107: class t3lib_sqlparser
40 *
41 * SECTION: SQL Parsing, full queries
42 * 129: function parseSQL($parseString)
43 * 192: function parseSELECT($parseString)
44 * 261: function parseUPDATE($parseString)
45 * 315: function parseINSERT($parseString)
46 * 375: function parseDELETE($parseString)
47 * 413: function parseEXPLAIN($parseString)
48 * 435: function parseCREATETABLE($parseString)
49 * 507: function parseALTERTABLE($parseString)
50 * 576: function parseDROPTABLE($parseString)
51 * 609: function parseCREATEDATABASE($parseString)
52 *
53 * SECTION: SQL Parsing, helper functions for parts of queries
54 * 663: function parseFieldList(&$parseString, $stopRegex='')
55 * 781: function parseFromTables(&$parseString, $stopRegex='')
56 * 844: function parseWhereClause(&$parseString, $stopRegex='')
57 * 952: function parseFieldDef(&$parseString, $stopRegex='')
58 *
59 * SECTION: Parsing: Helper functions
60 * 1013: function nextPart(&$parseString,$regex,$trimAll=FALSE)
61 * 1028: function getValue(&$parseString,$comparator='')
62 * 1084: function getValueInQuotes(&$parseString,$quote)
63 * 1110: function parseStripslashes($str)
64 * 1124: function compileAddslashes($str)
65 * 1139: function parseError($msg,$restQuery)
66 * 1153: function trimSQL($str)
67 *
68 * SECTION: Compiling queries
69 * 1182: function compileSQL($components)
70 * 1220: function compileSELECT($components)
71 * 1251: function compileUPDATE($components)
72 * 1279: function compileINSERT($components)
73 * 1319: function compileDELETE($components)
74 * 1339: function compileCREATETABLE($components)
75 * 1370: function compileALTERTABLE($components)
76 *
77 * SECTION: Compiling queries, helper functions for parts of queries
78 * 1423: function compileFieldList($selectFields)
79 * 1465: function compileFromTables($tablesArray)
80 * 1502: function compileWhereClause($clauseArray)
81 * 1556: function compileFieldCfg($fieldCfg)
82 *
83 * SECTION: Debugging
84 * 1605: function debug_parseSQLpart($part,$str)
85 * 1627: function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE)
86 * 1660: function debug_testSQL($SQLquery)
87 *
88 * TOTAL FUNCTIONS: 35
89 * (This index is automatically created/updated by the extension "extdeveval")
90 *
91 */
92
93
94
95
96
97
98
99
100 /**
101 * TYPO3 SQL parser class.
102 *
103 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
104 * @package TYPO3
105 * @subpackage t3lib
106 */
107 class t3lib_sqlparser {
108
109 // Parser:
110 var $parse_error = ''; // Parsing error string
111 var $lastStopKeyWord = ''; // Last stop keyword used.
112
113
114
115
116 /*************************************
117 *
118 * SQL Parsing, full queries
119 *
120 **************************************/
121
122 /**
123 * Parses any single SQL query
124 *
125 * @param string SQL query
126 * @return array Result array with all the parts in - or error message string
127 * @see compileSQL(), debug_testSQL()
128 */
129 function parseSQL($parseString) {
130 // Prepare variables:
131 $parseString = $this->trimSQL($parseString);
132 $this->parse_error = '';
133 $result = array();
134
135 // Finding starting keyword of string:
136 $_parseString = $parseString; // Protecting original string...
137 $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:]]+');
138 $keyword = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$keyword));
139
140 switch($keyword) {
141 case 'SELECT':
142 // Parsing SELECT query:
143 $result = $this->parseSELECT($parseString);
144 break;
145 case 'UPDATE':
146 // Parsing UPDATE query:
147 $result = $this->parseUPDATE($parseString);
148 break;
149 case 'INSERTINTO':
150 // Parsing INSERT query:
151 $result = $this->parseINSERT($parseString);
152 break;
153 case 'DELETEFROM':
154 // Parsing DELETE query:
155 $result = $this->parseDELETE($parseString);
156 break;
157 case 'EXPLAIN':
158 // Parsing EXPLAIN SELECT query:
159 $result = $this->parseEXPLAIN($parseString);
160 break;
161 case 'DROPTABLE':
162 // Parsing DROP TABLE query:
163 $result = $this->parseDROPTABLE($parseString);
164 break;
165 case 'ALTERTABLE':
166 // Parsing ALTER TABLE query:
167 $result = $this->parseALTERTABLE($parseString);
168 break;
169 case 'CREATETABLE':
170 // Parsing CREATE TABLE query:
171 $result = $this->parseCREATETABLE($parseString);
172 break;
173 case 'CREATEDATABASE':
174 // Parsing CREATE DATABASE query:
175 $result = $this->parseCREATEDATABASE($parseString);
176 break;
177 default:
178 return $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
179 break;
180 }
181
182 return $result;
183 }
184
185 /**
186 * Parsing SELECT query
187 *
188 * @param string SQL string with SELECT query to parse
189 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
190 * @see compileSELECT()
191 */
192 function parseSELECT($parseString) {
193
194 // Removing SELECT:
195 $parseString = $this->trimSQL($parseString);
196 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^SELECT[[:space:]]+','',$parseString);
197
198 // Init output variable:
199 $result = array();
200 $result['type'] = 'SELECT';
201
202 // Looking for STRAIGHT_JOIN keyword:
203 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
204
205 // Select fields:
206 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
207 if ($this->parse_error) { return $this->parse_error; }
208
209 // Continue if string is not ended:
210 if ($parseString) {
211
212 // Get table list:
213 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
214 if ($this->parse_error) { return $this->parse_error; }
215
216 // If there are more than just the tables (a WHERE clause that would be...)
217 if ($parseString) {
218
219 // Get WHERE clause:
220 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
221 if ($this->parse_error) { return $this->parse_error; }
222
223 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
224 if ($this->lastStopKeyWord) {
225
226 // GROUP BY parsing:
227 if ($this->lastStopKeyWord == 'GROUPBY') {
228 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
229 if ($this->parse_error) { return $this->parse_error; }
230 }
231
232 // ORDER BY parsing:
233 if ($this->lastStopKeyWord == 'ORDERBY') {
234 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
235 if ($this->parse_error) { return $this->parse_error; }
236 }
237
238 // LIMIT parsing:
239 if ($this->lastStopKeyWord == 'LIMIT') {
240 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/',trim($parseString))) {
241 $result['LIMIT'] = $parseString;
242 } else {
243 return $this->parseError('No value for limit!',$parseString);
244 }
245 }
246 }
247 }
248 } else return $this->parseError('No table to select from!',$parseString);
249
250 // Return result:
251 return $result;
252 }
253
254 /**
255 * Parsing UPDATE query
256 *
257 * @param string SQL string with UPDATE query to parse
258 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string.
259 * @see compileUPDATE()
260 */
261 function parseUPDATE($parseString) {
262
263 // Removing UPDATE
264 $parseString = $this->trimSQL($parseString);
265 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^UPDATE[[:space:]]+','',$parseString);
266
267 // Init output variable:
268 $result = array();
269 $result['type'] = 'UPDATE';
270
271 // Get table:
272 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
273
274 // Continue if string is not ended:
275 if ($result['TABLE']) {
276 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
277
278 $comma = TRUE;
279
280 // Get field/value pairs:
281 while($comma) {
282 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
283 $this->nextPart($parseString,'^(=)'); // Strip of "=" sign.
284 $value = $this->getValue($parseString);
285 $result['FIELDS'][$fieldName] = $value;
286 } else return $this->parseError('No fieldname found',$parseString);
287
288 $comma = $this->nextPart($parseString,'^(,)');
289 }
290
291 // WHERE
292 if ($this->nextPart($parseString,'^(WHERE)')) {
293 $result['WHERE'] = $this->parseWhereClause($parseString);
294 if ($this->parse_error) { return $this->parse_error; }
295 }
296 } else return $this->parseError('Query missing SET...',$parseString);
297 } else return $this->parseError('No table found!',$parseString);
298
299 // Should be no more content now:
300 if ($parseString) {
301 return $this->parseError('Still content in clause after parsing!',$parseString);
302 }
303
304 // Return result:
305 return $result;
306 }
307
308 /**
309 * Parsing INSERT query
310 *
311 * @param string SQL string with INSERT query to parse
312 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string.
313 * @see compileINSERT()
314 */
315 function parseINSERT($parseString) {
316
317 // Removing INSERT
318 $parseString = $this->trimSQL($parseString);
319 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
320
321 // Init output variable:
322 $result = array();
323 $result['type'] = 'INSERT';
324
325 // Get table:
326 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
327
328 if ($result['TABLE']) {
329
330 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // In this case there are no field names mentioned in the SQL!
331 // Get values/fieldnames (depending...)
332 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
333 if ($this->parse_error) { return $this->parse_error; }
334 } else { // There are apparently fieldnames listed:
335 $fieldNames = $this->getValue($parseString,'_LIST');
336 if ($this->parse_error) { return $this->parse_error; }
337
338 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // "VALUES" keyword binds the fieldnames to values:
339
340 $values = $this->getValue($parseString,'IN'); // Using the "getValue" function to get the field list...
341 if ($this->parse_error) { return $this->parse_error; }
342
343 foreach($fieldNames as $k => $fN) {
344 if (preg_match('/^[[:alnum:]_]+$/',$fN)) {
345 if (isset($values[$k])) {
346 if (!isset($result['FIELDS'][$fN])) {
347 $result['FIELDS'][$fN] = $values[$k];
348 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
349 } else return $this->parseError('No value set!',$parseString);
350 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
351 }
352 if (isset($values[$k+1])) {
353 return $this->parseError('Too many values in list!',$parseString);
354 }
355 } else return $this->parseError('VALUES keyword expected',$parseString);
356 }
357 } else return $this->parseError('No table found!',$parseString);
358
359 // Should be no more content now:
360 if ($parseString) {
361 return $this->parseError('Still content after parsing!',$parseString);
362 }
363
364 // Return result
365 return $result;
366 }
367
368 /**
369 * Parsing DELETE query
370 *
371 * @param string SQL string with DELETE query to parse
372 * @return mixed Returns array with components of DELETE query on success, otherwise an error message string.
373 * @see compileDELETE()
374 */
375 function parseDELETE($parseString) {
376
377 // Removing DELETE
378 $parseString = $this->trimSQL($parseString);
379 $parseString = ltrim(substr(ltrim(substr($parseString,6)),4)); // REMOVE eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
380
381 // Init output variable:
382 $result = array();
383 $result['type'] = 'DELETE';
384
385 // Get table:
386 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
387
388 if ($result['TABLE']) {
389
390 // WHERE
391 if ($this->nextPart($parseString,'^(WHERE)')) {
392 $result['WHERE'] = $this->parseWhereClause($parseString);
393 if ($this->parse_error) { return $this->parse_error; }
394 }
395 } else return $this->parseError('No table found!',$parseString);
396
397 // Should be no more content now:
398 if ($parseString) {
399 return $this->parseError('Still content in clause after parsing!',$parseString);
400 }
401
402 // Return result:
403 return $result;
404 }
405
406 /**
407 * Parsing EXPLAIN query
408 *
409 * @param string SQL string with EXPLAIN query to parse
410 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string.
411 * @see parseSELECT()
412 */
413 function parseEXPLAIN($parseString) {
414
415 // Removing EXPLAIN
416 $parseString = $this->trimSQL($parseString);
417 $parseString = ltrim(substr($parseString,6)); // REMOVE eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
418
419 // Init output variable:
420 $result = $this->parseSELECT($parseString);
421 if (is_array($result)) {
422 $result['type'] = 'EXPLAIN';
423 }
424
425 return $result;
426 }
427
428 /**
429 * Parsing CREATE TABLE query
430 *
431 * @param string SQL string starting with CREATE TABLE
432 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string.
433 * @see compileCREATETABLE()
434 */
435 function parseCREATETABLE($parseString) {
436
437 // Removing CREATE TABLE
438 $parseString = $this->trimSQL($parseString);
439 $parseString = ltrim(substr(ltrim(substr($parseString,6)),5)); // REMOVE eregi_replace('^CREATE[[:space:]]+TABLE[[:space:]]+','',$parseString);
440
441 // Init output variable:
442 $result = array();
443 $result['type'] = 'CREATETABLE';
444
445 // Get table:
446 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
447
448 if ($result['TABLE']) {
449
450 // While the parseString is not yet empty:
451 while(strlen($parseString)>0) {
452 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY)([[:space:]]+|\()')) { // Getting key
453 $key = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$key));
454
455 switch($key) {
456 case 'PRIMARYKEY':
457 $result['KEYS'][$key] = $this->getValue($parseString,'_LIST');
458 if ($this->parse_error) { return $this->parse_error; }
459 break;
460 case 'KEY':
461 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
462 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
463 if ($this->parse_error) { return $this->parse_error; }
464 } else return $this->parseError('No keyname found',$parseString);
465 break;
466 }
467 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field:
468 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
469 if ($this->parse_error) { return $this->parse_error; }
470 }
471
472 // Finding delimiter:
473 $delim = $this->nextPart($parseString, '^(,|\))');
474 if (!$delim) {
475 return $this->parseError('No delimiter found',$parseString);
476 } elseif ($delim==')') {
477 break;
478 }
479 }
480
481 // Finding what is after the table definition - table type in MySQL
482 if ($delim==')') {
483 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)')) {
484 $result['tableType'] = $parseString;
485 $parseString = '';
486 }
487 } else return $this->parseError('No fieldname found!',$parseString);
488
489 // Getting table type
490 } else return $this->parseError('No table found!',$parseString);
491
492 // Should be no more content now:
493 if ($parseString) {
494 return $this->parseError('Still content in clause after parsing!',$parseString);
495 }
496
497 return $result;
498 }
499
500 /**
501 * Parsing ALTER TABLE query
502 *
503 * @param string SQL string starting with ALTER TABLE
504 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string.
505 * @see compileALTERTABLE()
506 */
507 function parseALTERTABLE($parseString) {
508
509 // Removing ALTER TABLE
510 $parseString = $this->trimSQL($parseString);
511 $parseString = ltrim(substr(ltrim(substr($parseString,5)),5)); // REMOVE eregi_replace('^ALTER[[:space:]]+TABLE[[:space:]]+','',$parseString);
512
513 // Init output variable:
514 $result = array();
515 $result['type'] = 'ALTERTABLE';
516
517 // Get table:
518 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
519
520 if ($result['TABLE']) {
521 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:]]+|\()')) {
522 $actionKey = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$result['action']));
523
524 // Getting field:
525 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
526
527 switch($actionKey) {
528 case 'ADD':
529 $result['FIELD'] = $fieldKey;
530 $result['definition'] = $this->parseFieldDef($parseString);
531 if ($this->parse_error) { return $this->parse_error; }
532 break;
533 case 'DROP':
534 case 'RENAME':
535 $result['FIELD'] = $fieldKey;
536 break;
537 case 'CHANGE':
538 $result['FIELD'] = $fieldKey;
539 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
540 $result['definition'] = $this->parseFieldDef($parseString);
541 if ($this->parse_error) { return $this->parse_error; }
542 } else return $this->parseError('No NEW field name found',$parseString);
543 break;
544
545 case 'ADDKEY':
546 case 'ADDPRIMARYKEY':
547 $result['KEY'] = $fieldKey;
548 $result['fields'] = $this->getValue($parseString,'_LIST');
549 if ($this->parse_error) { return $this->parse_error; }
550 break;
551 case 'DROPKEY':
552 $result['KEY'] = $fieldKey;
553 break;
554 case 'DROPPRIMARYKEY':
555 // ??? todo!
556 break;
557 }
558 } else return $this->parseError('No field name found',$parseString);
559 } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
560 } else return $this->parseError('No table found!',$parseString);
561
562 // Should be no more content now:
563 if ($parseString) {
564 return $this->parseError('Still content in clause after parsing!',$parseString);
565 }
566
567 return $result;
568 }
569
570 /**
571 * Parsing DROP TABLE query
572 *
573 * @param string SQL string starting with DROP TABLE
574 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
575 */
576 function parseDROPTABLE($parseString) {
577
578 // Removing DROP TABLE
579 $parseString = $this->trimSQL($parseString);
580 $parseString = ltrim(substr(ltrim(substr($parseString,4)),5)); // eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
581
582 // Init output variable:
583 $result = array();
584 $result['type'] = 'DROPTABLE';
585
586 // IF EXISTS
587 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
588
589 // Get table:
590 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
591
592 if ($result['TABLE']) {
593
594 // Should be no more content now:
595 if ($parseString) {
596 return $this->parseError('Still content in clause after parsing!',$parseString);
597 }
598
599 return $result;
600 } else return $this->parseError('No table found!',$parseString);
601 }
602
603 /**
604 * Parsing CREATE DATABASE query
605 *
606 * @param string SQL string starting with CREATE DATABASE
607 * @return mixed Returns array with components of CREATE DATABASE query on success, otherwise an error message string.
608 */
609 function parseCREATEDATABASE($parseString) {
610
611 // Removing CREATE DATABASE
612 $parseString = $this->trimSQL($parseString);
613 $parseString = ltrim(substr(ltrim(substr($parseString,6)),8)); // eregi_replace('^CREATE[[:space:]]+DATABASE[[:space:]]+','',$parseString);
614
615 // Init output variable:
616 $result = array();
617 $result['type'] = 'CREATEDATABASE';
618
619 // Get table:
620 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
621
622 if ($result['DATABASE']) {
623
624 // Should be no more content now:
625 if ($parseString) {
626 return $this->parseError('Still content in clause after parsing!',$parseString);
627 }
628
629 return $result;
630 } else return $this->parseError('No database found!',$parseString);
631 }
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647 /**************************************
648 *
649 * SQL Parsing, helper functions for parts of queries
650 *
651 **************************************/
652
653 /**
654 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
655 * The output from this function can be compiled back into a field list with ->compileFieldList()
656 * Will detect the keywords "DESC" and "ASC" after the table name; thus is can be used for parsing the more simply ORDER BY and GROUP BY field lists as well!
657 *
658 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
659 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
660 * @return array If successful parsing, returns an array, otherwise an error string.
661 * @see compileFieldList()
662 */
663 function parseFieldList(&$parseString, $stopRegex='') {
664
665 // Prepare variables:
666 $parseString = $this->trimSQL($parseString);
667 $this->lastStopKeyWord = '';
668 $this->parse_error = '';
669
670
671 $stack = array(); // Contains the parsed content
672 $pnt = 0; // Pointer to positions in $stack
673 $level = 0; // Indicates the parenthesis level we are at.
674 $loopExit = 0; // Recursivity brake.
675
676 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
677 while (strlen($parseString)) {
678
679 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
680 if ($level>0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
681
682 // Accumulate function content until next () parenthesis:
683 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
684 $stack[$pnt]['func_content.'][] = array(
685 'level' => $level,
686 'func_content' => substr($funcContent,0,-1)
687 );
688 $stack[$pnt]['func_content'].= $funcContent;
689
690 // Detecting ( or )
691 switch(substr($stack[$pnt]['func_content'],-1)) {
692 case '(':
693 $level++;
694 break;
695 case ')':
696 $level--;
697 if (!$level) { // If this was the last parenthesis:
698 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
699 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
700 }
701 break;
702 }
703 } else { // Outside parenthesis, looking for next field:
704
705 // Looking for a known function (only known functions supported)
706 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
707 if ($func) {
708 $parseString = trim(substr($parseString,1)); // Strip of "("
709 $stack[$pnt]['type'] = 'function';
710 $stack[$pnt]['function'] = $func;
711 $level++; // increse parenthesis level counter.
712 } else {
713 // Otherwise, look for regular fieldname:
714 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)')) {
715 $stack[$pnt]['type'] = 'field';
716
717 // Explode fieldname into field and table:
718 $tableField = explode('.',$fieldName,2);
719 if (count($tableField)==2) {
720 $stack[$pnt]['table'] = $tableField[0];
721 $stack[$pnt]['field'] = $tableField[1];
722 } else {
723 $stack[$pnt]['table'] = '';
724 $stack[$pnt]['field'] = $tableField[0];
725 }
726 } else {
727 return $this->parseError('No field name found as expected',$parseString);
728 }
729 }
730 }
731
732 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
733 if (!$level) {
734
735 // Looking for "AS" alias:
736 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
737 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
738 $stack[$pnt]['as_keyword'] = $as;
739 }
740
741 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
742 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
743 $stack[$pnt]['sortDir'] = $sDir;
744 }
745
746 // Looking for stop-keywords:
747 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
748 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
749 return $stack;
750 }
751
752 // Looking for comma (since the stop-keyword did not trigger a return...)
753 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
754 return $this->parseError('No comma found as expected',$parseString);
755 }
756
757 // Increasing pointer:
758 $pnt++;
759 }
760
761 // Check recursivity brake:
762 $loopExit++;
763 if ($loopExit>500) {
764 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
765 }
766 }
767
768 // Return result array:
769 return $stack;
770 }
771
772 /**
773 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
774 * The success of this parsing determines if that part of the query is supported by TYPO3.
775 *
776 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
777 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
778 * @return array If successful parsing, returns an array, otherwise an error string.
779 * @see compileFromTables()
780 */
781 function parseFromTables(&$parseString, $stopRegex='') {
782
783 // Prepare variables:
784 $parseString = $this->trimSQL($parseString);
785 $this->lastStopKeyWord = '';
786 $this->parse_error = '';
787
788 $stack = array(); // Contains the parsed content
789 $pnt = 0; // Pointer to positions in $stack
790 $loopExit = 0; // Recursivity brake.
791
792 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
793 while (strlen($parseString)) {
794 // Looking for the table:
795 if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)')) {
796 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*');
797 } else return $this->parseError('No table name found as expected!',$parseString);
798
799 // Looking for JOIN
800 if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+')) {
801 $stack[$pnt]['JOIN']['type'] = $join;
802 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1)) {
803 $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
804 $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
805 if ($field1 && $field2) {
806 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
807 } else return $this->parseError('No join fields found!',$parseString);
808 } else return $this->parseError('No join table found!',$parseString);
809 }
810
811 // Looking for stop-keywords:
812 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
813 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
814 return $stack;
815 }
816
817 // Looking for comma:
818 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
819 return $this->parseError('No comma found as expected',$parseString);
820 }
821
822 // Increasing pointer:
823 $pnt++;
824
825 // Check recursivity brake:
826 $loopExit++;
827 if ($loopExit>500) {
828 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
829 }
830 }
831
832 // Return result array:
833 return $stack;
834 }
835
836 /**
837 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
838 * The success of this parsing determines if that part of the query is supported by TYPO3.
839 *
840 * @param string WHERE clause to parse. NOTICE: passed by reference!
841 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
842 * @return mixed If successful parsing, returns an array, otherwise an error string.
843 */
844 function parseWhereClause(&$parseString, $stopRegex='') {
845
846 // Prepare variables:
847 $parseString = $this->trimSQL($parseString);
848 $this->lastStopKeyWord = '';
849 $this->parse_error = '';
850
851 $stack = array(0 => array()); // Contains the parsed content
852 $pnt = array(0 => 0); // Pointer to positions in $stack
853 $level = 0; // Determines parenthesis level
854 $loopExit = 0; // Recursivity brake.
855
856 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
857 while (strlen($parseString)) {
858
859 // Look for next parenthesis level:
860 $newLevel = $this->nextPart($parseString,'^([(])');
861 if ($newLevel=='(') { // If new level is started, manage stack/pointers:
862 $level++; // Increase level
863 $pnt[$level] = 0; // Reset pointer for this level
864 $stack[$level] = array(); // Reset stack for this level
865 } else { // If no new level is started, just parse the current level:
866
867 // Find "modifyer", eg. "NOT or !"
868 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
869
870 // Fieldname:
871 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)')) {
872
873 // Parse field name into field and table:
874 $tableField = explode('.',$fieldName,2);
875 if (count($tableField)==2) {
876 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
877 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
878 } else {
879 $stack[$level][$pnt[$level]]['table'] = '';
880 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
881 }
882 } else {
883 return $this->parseError('No field name found as expected',$parseString);
884 }
885
886 // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
887 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
888 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
889 // Finding value for calculation:
890 $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
891 }
892
893 // Find "comparator":
894 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS)');
895 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
896 // Finding value for comparator:
897 $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
898 if ($this->parse_error) { return $this->parse_error; }
899 }
900
901 // Finished, increase pointer:
902 $pnt[$level]++;
903
904 // Checking if the current level is ended, in that case do stack management:
905 while ($this->nextPart($parseString,'^([)])')) {
906 $level--; // Decrease level:
907 $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1]; // Copy stack
908 $pnt[$level]++; // Increase pointer of the new level
909
910 // Make recursivity check:
911 $loopExit++;
912 if ($loopExit>500) {
913 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
914 }
915 }
916
917 // Detecting the operator for the next level; support for AND, OR and &&):
918 $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
919 if ($op) {
920 $stack[$level][$pnt[$level]]['operator'] = $op;
921 } elseif (strlen($parseString)) {
922
923 // Looking for stop-keywords:
924 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
925 $this->lastStopKeyWord = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$this->lastStopKeyWord));
926 return $stack[0];
927 } else {
928 return $this->parseError('No operator, but parsing not finished.',$parseString);
929 }
930 }
931 }
932
933 // Make recursivity check:
934 $loopExit++;
935 if ($loopExit>500) {
936 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
937 }
938 }
939
940 // Return the stacks lowest level:
941 return $stack[0];
942 }
943
944 /**
945 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
946 * The success of this parsing determines if that part of the query is supported by TYPO3.
947 *
948 * @param string WHERE clause to parse. NOTICE: passed by reference!
949 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
950 * @return mixed If successful parsing, returns an array, otherwise an error string.
951 */
952 function parseFieldDef(&$parseString, $stopRegex='') {
953 // Prepare variables:
954 $parseString = $this->trimSQL($parseString);
955 $this->lastStopKeyWord = '';
956 $this->parse_error = '';
957
958 $result = array();
959
960 // Field type:
961 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:]]+|\()')) {
962
963 // Looking for value:
964 if (substr($parseString,0,1)=='(') {
965 $parseString = substr($parseString,1);
966 if ($result['value'] = $this->nextPart($parseString,'^([^)]*)')) {
967 $parseString = ltrim(substr($parseString,1));
968 } else return $this->parseError('No end-parenthesis for value found!',$parseString);
969 }
970
971 // Looking for keywords
972 while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
973 $keywordCmp = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$keyword));
974
975 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
976
977 switch($keywordCmp) {
978 case 'DEFAULT':
979 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
980 break;
981 }
982 }
983 } else return $this->parseError('Field type unknown!',$parseString);
984
985 return $result;
986 }
987
988
989
990
991
992
993
994
995
996
997
998 /************************************
999 *
1000 * Parsing: Helper functions
1001 *
1002 ************************************/
1003
1004 /**
1005 * Strips of a part of the parseString and returns the matching part.
1006 * Helper function for the parsing methods.
1007 *
1008 * @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.
1009 * @param string Regex to find a matching part in the beginning of the string. Rules: You MUST start the regex with "^" (finding stuff in the beginning of string) and the result of the first parenthesis is what will be returned to you (and stripped of the string). Eg. '^(AND|OR|&&)[[:space:]]+' will return AND, OR or && if found and having one of more whitespaces after it, plus shorten $parseString with that match and any space after (by ltrim())
1010 * @param boolean If set the full match of the regex is stripped of the beginning of the string!
1011 * @return string The value of the first parenthesis level of the REGEX.
1012 */
1013 function nextPart(&$parseString,$regex,$trimAll=FALSE) {
1014 //if (eregi($regex,$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1015 if (preg_match('/'.$regex.'/i',$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1016 $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
1017 return $reg[1];
1018 }
1019 }
1020
1021 /**
1022 * Finds value in beginning of $parseString, returns result and strips it of parseString
1023 *
1024 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
1025 * @param string The comparator used before. If "NOT IN" or "IN" then the value is expected to be a list of values. Otherwise just an integer (un-quoted) or string (quoted)
1026 * @return string The value (string/integer). Otherwise an array with error message in first key (0)
1027 */
1028 function getValue(&$parseString,$comparator='') {
1029 //if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator)))) { // List of values:
1030 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(str_replace(array(' ',"\n","\r","\t"),'',$comparator)))) { // List of values:
1031 if ($this->nextPart($parseString,'^([(])')) {
1032 $listValues = array();
1033 $comma=',';
1034
1035 while($comma==',') {
1036 $listValues[] = $this->getValue($parseString);
1037 $comma = $this->nextPart($parseString,'^([,])');
1038 }
1039
1040 $out = $this->nextPart($parseString,'^([)])');
1041 if ($out) {
1042 if ($comparator=='_LIST') {
1043 $kVals = array();
1044 foreach ($listValues as $vArr) {
1045 $kVals[] = $vArr[0];
1046 }
1047 return $kVals;
1048 } else {
1049 return $listValues;
1050 }
1051 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1052 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1053
1054 } else { // Just plain string value, in quotes or not:
1055
1056 // Quote?
1057 $firstChar = substr($parseString,0,1);
1058
1059 switch($firstChar) {
1060 case '"':
1061 return array($this->getValueInQuotes($parseString,'"'),'"');
1062 break;
1063 case "'":
1064 return array($this->getValueInQuotes($parseString,"'"),"'");
1065 break;
1066 default:
1067 if (preg_match('/^([[:alnum:]._-]+)/i',$parseString, $reg)) {
1068 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1069 return array($reg[1]);
1070 }
1071 break;
1072 }
1073 }
1074 }
1075
1076 /**
1077 * Get value in quotes from $parseString.
1078 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1079 *
1080 * @param string String from which to find value in quotes. Notice that $parseString is passed by reference and is shortend by the output of this function.
1081 * @param string The quote used; input either " or '
1082 * @return string The value, passed through stripslashes() !
1083 */
1084 function getValueInQuotes(&$parseString,$quote) {
1085
1086 $parts = explode($quote,substr($parseString,1));
1087 $buffer = '';
1088 foreach($parts as $k => $v) {
1089 $buffer.=$v;
1090
1091 unset($reg);
1092 //preg_match('/[\]*$/',$v,$reg); // does not work. what is the *exact* meaning of the next line?
1093 ereg('[\]*$',$v,$reg);
1094 if (strlen($reg[0])%2) {
1095 $buffer.=$quote;
1096 } else {
1097 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
1098 return $this->parseStripslashes($buffer);
1099 }
1100 }
1101 }
1102
1103 /**
1104 * Strip slashes function used for parsing
1105 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1106 *
1107 * @param string Input string
1108 * @return string Output string
1109 */
1110 function parseStripslashes($str) {
1111 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1112 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1113
1114 return str_replace($search, $replace, $str);
1115 }
1116
1117 /**
1118 * Add slashes function used for compiling queries
1119 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1120 *
1121 * @param string Input string
1122 * @return string Output string
1123 */
1124 function compileAddslashes($str) {
1125 return $str;
1126 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1127 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1128
1129 return str_replace($search, $replace, $str);
1130 }
1131
1132 /**
1133 * Setting the internal error message value, $this->parse_error and returns that value.
1134 *
1135 * @param string Input error message
1136 * @param string Remaining query to parse.
1137 * @return string Error message.
1138 */
1139 function parseError($msg,$restQuery) {
1140 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1141 return $this->parse_error;
1142 }
1143
1144 /**
1145 * Trimming SQL as preparation for parsing.
1146 * ";" in the end is stripped of.
1147 * White space is trimmed away around the value
1148 * A single space-char is added in the end
1149 *
1150 * @param string Input string
1151 * @return string Output string
1152 */
1153 function trimSQL($str) {
1154 return trim(rtrim($str, "; \r\n\t")).' ';
1155 //return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
1156 }
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169 /*************************
1170 *
1171 * Compiling queries
1172 *
1173 *************************/
1174
1175 /**
1176 * Compiles an SQL query from components
1177 *
1178 * @param array Array of SQL query components
1179 * @return string SQL query
1180 * @see parseSQL()
1181 */
1182 function compileSQL($components) {
1183 switch($components['type']) {
1184 case 'SELECT':
1185 $query = $this->compileSELECT($components);
1186 break;
1187 case 'UPDATE':
1188 $query = $this->compileUPDATE($components);
1189 break;
1190 case 'INSERT':
1191 $query = $this->compileINSERT($components);
1192 break;
1193 case 'DELETE':
1194 $query = $this->compileDELETE($components);
1195 break;
1196 case 'EXPLAIN':
1197 $query = 'EXPLAIN '.$this->compileSELECT($components);
1198 break;
1199 case 'DROPTABLE':
1200 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
1201 break;
1202 case 'CREATETABLE':
1203 $query = $this->compileCREATETABLE($components);
1204 break;
1205 case 'ALTERTABLE':
1206 $query = $this->compileALTERTABLE($components);
1207 break;
1208 }
1209
1210 return $query;
1211 }
1212
1213 /**
1214 * Compiles a SELECT statement from components array
1215 *
1216 * @param array Array of SQL query components
1217 * @return string SQL SELECT query
1218 * @see parseSELECT()
1219 */
1220 function compileSELECT($components) {
1221
1222 // Initialize:
1223 $where = $this->compileWhereClause($components['WHERE']);
1224 $groupBy = $this->compileFieldList($components['GROUPBY']);
1225 $orderBy = $this->compileFieldList($components['ORDERBY']);
1226 $limit = $components['LIMIT'];
1227
1228 // Make query:
1229 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
1230 '.$this->compileFieldList($components['SELECT']).'
1231 FROM '.$this->compileFromTables($components['FROM']).
1232 (strlen($where)?'
1233 WHERE '.$where : '').
1234 (strlen($groupBy)?'
1235 GROUP BY '.$groupBy : '').
1236 (strlen($orderBy)?'
1237 ORDER BY '.$orderBy : '').
1238 (strlen($limit)?'
1239 LIMIT '.$limit : '');
1240
1241 return $query;
1242 }
1243
1244 /**
1245 * Compiles an UPDATE statement from components array
1246 *
1247 * @param array Array of SQL query components
1248 * @return string SQL UPDATE query
1249 * @see parseUPDATE()
1250 */
1251 function compileUPDATE($components) {
1252
1253 // Where clause:
1254 $where = $this->compileWhereClause($components['WHERE']);
1255
1256 // Fields
1257 $fields = array();
1258 foreach($components['FIELDS'] as $fN => $fV) {
1259 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1260 }
1261
1262 // Make query:
1263 $query = 'UPDATE '.$components['TABLE'].' SET
1264 '.implode(',
1265 ',$fields).'
1266 '.(strlen($where)?'
1267 WHERE '.$where : '');
1268
1269 return $query;
1270 }
1271
1272 /**
1273 * Compiles an INSERT statement from components array
1274 *
1275 * @param array Array of SQL query components
1276 * @return string SQL INSERT query
1277 * @see parseINSERT()
1278 */
1279 function compileINSERT($components) {
1280
1281 if ($components['VALUES_ONLY']) {
1282 // Initialize:
1283 $fields = array();
1284 foreach($components['VALUES_ONLY'] as $fV) {
1285 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1286 }
1287
1288 // Make query:
1289 $query = 'INSERT INTO '.$components['TABLE'].'
1290 VALUES
1291 ('.implode(',
1292 ',$fields).')';
1293 } else {
1294 // Initialize:
1295 $fields = array();
1296 foreach($components['FIELDS'] as $fN => $fV) {
1297 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1298 }
1299
1300 // Make query:
1301 $query = 'INSERT INTO '.$components['TABLE'].'
1302 ('.implode(',
1303 ',array_keys($fields)).')
1304 VALUES
1305 ('.implode(',
1306 ',$fields).')';
1307 }
1308
1309 return $query;
1310 }
1311
1312 /**
1313 * Compiles an DELETE statement from components array
1314 *
1315 * @param array Array of SQL query components
1316 * @return string SQL DELETE query
1317 * @see parseDELETE()
1318 */
1319 function compileDELETE($components) {
1320
1321 // Where clause:
1322 $where = $this->compileWhereClause($components['WHERE']);
1323
1324 // Make query:
1325 $query = 'DELETE FROM '.$components['TABLE'].
1326 (strlen($where)?'
1327 WHERE '.$where : '');
1328
1329 return $query;
1330 }
1331
1332 /**
1333 * Compiles a CREATE TABLE statement from components array
1334 *
1335 * @param array Array of SQL query components
1336 * @return string SQL CREATE TABLE query
1337 * @see parseCREATETABLE()
1338 */
1339 function compileCREATETABLE($components) {
1340
1341 // Create fields and keys:
1342 $fieldsKeys = array();
1343 foreach($components['FIELDS'] as $fN => $fCfg) {
1344 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
1345 }
1346 foreach($components['KEYS'] as $kN => $kCfg) {
1347 if ($kN == 'PRIMARYKEY') {
1348 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
1349 } else {
1350 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
1351 }
1352 }
1353
1354 // Make query:
1355 $query = 'CREATE TABLE '.$components['TABLE'].' (
1356 '.implode(',
1357 ', $fieldsKeys).'
1358 )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
1359
1360 return $query;
1361 }
1362
1363 /**
1364 * Compiles an ALTER TABLE statement from components array
1365 *
1366 * @param array Array of SQL query components
1367 * @return string SQL ALTER TABLE query
1368 * @see parseALTERTABLE()
1369 */
1370 function compileALTERTABLE($components) {
1371
1372 // Make query:
1373 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
1374
1375 // Based on action, add the final part:
1376 switch(strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$components['action']))) {
1377 case 'ADD':
1378 $query.=' '.$this->compileFieldCfg($components['definition']);
1379 break;
1380 case 'CHANGE':
1381 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
1382 break;
1383 case 'DROP':
1384 case 'DROPKEY':
1385 break;
1386 case 'ADDKEY':
1387 case 'ADDPRIMARYKEY':
1388 $query.=' ('.implode(',',$components['fields']).')';
1389 break;
1390 }
1391
1392 // Return query
1393 return $query;
1394 }
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409 /**************************************
1410 *
1411 * Compiling queries, helper functions for parts of queries
1412 *
1413 **************************************/
1414
1415 /**
1416 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
1417 * Can also compile field lists for ORDER BY and GROUP BY.
1418 *
1419 * @param array Array of select fields, (made with ->parseFieldList())
1420 * @return string Select field string
1421 * @see parseFieldList()
1422 */
1423 function compileFieldList($selectFields) {
1424
1425 // Prepare buffer variable:
1426 $outputParts = array();
1427
1428 // Traverse the selectFields if any:
1429 if (is_array($selectFields)) {
1430 foreach($selectFields as $k => $v) {
1431
1432 // Detecting type:
1433 switch($v['type']) {
1434 case 'function':
1435 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
1436 break;
1437 case 'field':
1438 $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
1439 break;
1440 }
1441
1442 // Alias:
1443 if ($v['as']) {
1444 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1445 }
1446
1447 // Specifically for ORDER BY and GROUP BY field lists:
1448 if ($v['sortDir']) {
1449 $outputParts[$k].= ' '.$v['sortDir'];
1450 }
1451 }
1452 }
1453
1454 // Return imploded buffer:
1455 return implode(', ',$outputParts);
1456 }
1457
1458 /**
1459 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1460 *
1461 * @param array Array of table names, (made with ->parseFromTables())
1462 * @return string Table name string
1463 * @see parseFromTables()
1464 */
1465 function compileFromTables($tablesArray) {
1466
1467 // Prepare buffer variable:
1468 $outputParts = array();
1469
1470 // Traverse the table names:
1471 if (is_array($tablesArray)) {
1472 foreach($tablesArray as $k => $v) {
1473
1474 // Set table name:
1475 $outputParts[$k] = $v['table'];
1476
1477 // Add alias AS if there:
1478 if ($v['as']) {
1479 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
1480 }
1481
1482 if (is_array($v['JOIN'])) {
1483 $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
1484 }
1485
1486 }
1487 }
1488
1489 // Return imploded buffer:
1490 return implode(', ',$outputParts);
1491 }
1492
1493 /**
1494 * Implodes an array of WHERE clause configuration into a WHERE clause.
1495 * NOTICE: MIGHT BY A TEMPORARY FUNCTION. Use for debugging only!
1496 * BUT IT IS NEEDED FOR DBAL - MAKE IT PERMANENT?!?!
1497 *
1498 * @param array WHERE clause configuration
1499 * @return string WHERE clause as string.
1500 * @see explodeWhereClause()
1501 */
1502 function compileWhereClause($clauseArray) {
1503
1504 // Prepare buffer variable:
1505 $output='';
1506
1507 // Traverse clause array:
1508 if (is_array($clauseArray)) {
1509 foreach($clauseArray as $k => $v) {
1510
1511 // Set operator:
1512 $output.=$v['operator'] ? ' '.$v['operator'] : '';
1513
1514 // Look for sublevel:
1515 if (is_array($v['sub'])) {
1516 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
1517 } else {
1518
1519 // Set field/table with modifying prefix if any:
1520 $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
1521
1522 // Set calculation, if any:
1523 if ($v['calc']) {
1524 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
1525 }
1526
1527 // Set comparator:
1528 if ($v['comparator']) {
1529 $output.=' '.$v['comparator'];
1530
1531 // Detecting value type; list or plain:
1532 if (t3lib_div::inList('NOTIN,IN',strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$v['comparator'])))) {
1533 $valueBuffer = array();
1534 foreach($v['value'] as $realValue) {
1535 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
1536 }
1537 $output.=' ('.trim(implode(',',$valueBuffer)).')';
1538 } else {
1539 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
1540 }
1541 }
1542 }
1543 }
1544 }
1545
1546 // Return output buffer:
1547 return $output;
1548 }
1549
1550 /**
1551 * Compile field definition
1552 *
1553 * @param array Field definition parts
1554 * @return string Field definition string
1555 */
1556 function compileFieldCfg($fieldCfg) {
1557
1558 // Set type:
1559 $cfg = $fieldCfg['fieldType'];
1560
1561 // Add value, if any:
1562 if (strlen($fieldCfg['value'])) {
1563 $cfg.='('.$fieldCfg['value'].')';
1564 }
1565
1566 // Add additional features:
1567 if (is_array($fieldCfg['featureIndex'])) {
1568 foreach($fieldCfg['featureIndex'] as $featureDef) {
1569 $cfg.=' '.$featureDef['keyword'];
1570
1571 // Add value if found:
1572 if (is_array($featureDef['value'])) {
1573 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
1574 }
1575 }
1576 }
1577
1578 // Return field definition string:
1579 return $cfg;
1580 }
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592 /*************************
1593 *
1594 * Debugging
1595 *
1596 *************************/
1597
1598 /**
1599 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
1600 *
1601 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
1602 * @param string SQL string to verify parsability of
1603 * @return mixed Returns array with string 1 and 2 if error, otherwise false
1604 */
1605 function debug_parseSQLpart($part,$str) {
1606 switch($part) {
1607 case 'SELECT':
1608 return $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
1609 break;
1610 case 'FROM':
1611 return $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
1612 break;
1613 case 'WHERE':
1614 return $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
1615 break;
1616 }
1617 }
1618
1619 /**
1620 * Compare two query strins by stripping away whitespace.
1621 *
1622 * @param string SQL String 1
1623 * @param string SQL string 2
1624 * @param boolean If true, the strings are compared insensitive to case
1625 * @return mixed Returns array with string 1 and 2 if error, otherwise false
1626 */
1627 function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
1628 if ($caseInsensitive) {
1629 $str1 = strtoupper($str);
1630 $str2 = strtoupper($newStr);
1631 } else {
1632 $str1 = $str;
1633 $str2 = $newStr;
1634 }
1635
1636 // Fixing escaped chars:
1637 $search = array('\0', '\n', '\r', '\Z');
1638 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
1639 $str1 = str_replace($search, $replace, $str1);
1640 $str2 = str_replace($search, $replace, $str2);
1641
1642 # Normally, commented out since they are needed only in tricky cases...
1643 # $str1 = stripslashes($str1);
1644 # $str2 = stripslashes($str2);
1645
1646 if (strcmp(str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str1)),str_replace(array(' ',"\t","\r","\n"),'',$this->trimSQL($str2)))) {
1647 return array(
1648 str_replace(array(' ',"\t","\r","\n"),' ',$str),
1649 str_replace(array(' ',"\t","\r","\n"),' ',$newStr),
1650 );
1651 }
1652 }
1653
1654 /**
1655 * Performs the ultimate test of the parser: Direct a SQL query in; You will get it back (through the parsed and re-compiled) if no problems, otherwise the script will print the error and exit
1656 *
1657 * @param string SQL query
1658 * @return string Query if all is well, otherwise exit.
1659 */
1660 function debug_testSQL($SQLquery) {
1661 # return $SQLquery;
1662 #debug(array($SQLquery));
1663
1664 // Getting result array:
1665 $parseResult = $this->parseSQL($SQLquery);
1666
1667 // If result array was returned, proceed. Otherwise show error and exit.
1668 if (is_array($parseResult)) {
1669
1670 // Re-compile query:
1671 $newQuery = $this->compileSQL($parseResult);
1672
1673 // TEST the new query:
1674 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
1675
1676 // Return new query if OK, otherwise show error and exit:
1677 if (!is_array($testResult)) {
1678 return $newQuery;
1679 } else {
1680 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
1681 exit;
1682 }
1683 } else {
1684 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
1685 exit;
1686 }
1687 }
1688 }
1689
1690
1691 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']) {
1692 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlparser.php']);
1693 }
1694 ?>