[TASK] Replace Space Indent into Tab indent (CGL Cleanup)
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_sqlparser.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2011 Kasper Skårhøj (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 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
31 */
32
33
34 /**
35 * TYPO3 SQL parser class.
36 *
37 * @author Kasper Skårhøj <kasperYYYY@typo3.com>
38 * @package TYPO3
39 * @subpackage t3lib
40 */
41 class t3lib_sqlparser {
42
43 // Parser:
44 var $parse_error = ''; // Parsing error string
45 var $lastStopKeyWord = ''; // Last stop keyword used.
46
47
48 /*************************************
49 *
50 * SQL Parsing, full queries
51 *
52 **************************************/
53
54 /**
55 * Parses any single SQL query
56 *
57 * @param string SQL query
58 * @return array Result array with all the parts in - or error message string
59 * @see compileSQL(), debug_testSQL()
60 */
61 public function parseSQL($parseString) {
62 // Prepare variables:
63 $parseString = $this->trimSQL($parseString);
64 $this->parse_error = '';
65 $result = array();
66
67 // Finding starting keyword of string:
68 $_parseString = $parseString; // Protecting original string...
69 $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|TRUNCATE[[:space:]]+TABLE)[[:space:]]+');
70 $keyword = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $keyword));
71
72 switch ($keyword) {
73 case 'SELECT':
74 // Parsing SELECT query:
75 $result = $this->parseSELECT($parseString);
76 break;
77 case 'UPDATE':
78 // Parsing UPDATE query:
79 $result = $this->parseUPDATE($parseString);
80 break;
81 case 'INSERTINTO':
82 // Parsing INSERT query:
83 $result = $this->parseINSERT($parseString);
84 break;
85 case 'DELETEFROM':
86 // Parsing DELETE query:
87 $result = $this->parseDELETE($parseString);
88 break;
89 case 'EXPLAIN':
90 // Parsing EXPLAIN SELECT query:
91 $result = $this->parseEXPLAIN($parseString);
92 break;
93 case 'DROPTABLE':
94 // Parsing DROP TABLE query:
95 $result = $this->parseDROPTABLE($parseString);
96 break;
97 case 'ALTERTABLE':
98 // Parsing ALTER TABLE query:
99 $result = $this->parseALTERTABLE($parseString);
100 break;
101 case 'CREATETABLE':
102 // Parsing CREATE TABLE query:
103 $result = $this->parseCREATETABLE($parseString);
104 break;
105 case 'CREATEDATABASE':
106 // Parsing CREATE DATABASE query:
107 $result = $this->parseCREATEDATABASE($parseString);
108 break;
109 case 'TRUNCATETABLE':
110 // Parsing TRUNCATE TABLE query:
111 $result = $this->parseTRUNCATETABLE($parseString);
112 break;
113 default:
114 $result = $this->parseError('"' . $keyword . '" is not a keyword', $parseString);
115 break;
116 }
117
118 return $result;
119 }
120
121 /**
122 * Parsing SELECT query
123 *
124 * @param string SQL string with SELECT query to parse
125 * @param array Array holding references to either named (:name) or question mark (?) parameters found
126 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
127 * @see compileSELECT()
128 */
129 protected function parseSELECT($parseString, &$parameterReferences = NULL) {
130
131 // Removing SELECT:
132 $parseString = $this->trimSQL($parseString);
133 $parseString = ltrim(substr($parseString, 6));
134
135 // Init output variable:
136 $result = array();
137 if ($parameterReferences === NULL) {
138 $result['parameters'] = array();
139 $parameterReferences = &$result['parameters'];
140 }
141 $result['type'] = 'SELECT';
142
143 // Looking for STRAIGHT_JOIN keyword:
144 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
145
146 // Select fields:
147 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
148 if ($this->parse_error) {
149 return $this->parse_error;
150 }
151
152 // Continue if string is not ended:
153 if ($parseString) {
154
155 // Get table list:
156 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
157 if ($this->parse_error) {
158 return $this->parse_error;
159 }
160
161 // If there are more than just the tables (a WHERE clause that would be...)
162 if ($parseString) {
163
164 // Get WHERE clause:
165 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+', $parameterReferences);
166 if ($this->parse_error) {
167 return $this->parse_error;
168 }
169
170 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
171 if ($this->lastStopKeyWord) {
172
173 // GROUP BY parsing:
174 if ($this->lastStopKeyWord == 'GROUPBY') {
175 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
176 if ($this->parse_error) {
177 return $this->parse_error;
178 }
179 }
180
181 // ORDER BY parsing:
182 if ($this->lastStopKeyWord == 'ORDERBY') {
183 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
184 if ($this->parse_error) {
185 return $this->parse_error;
186 }
187 }
188
189 // LIMIT parsing:
190 if ($this->lastStopKeyWord == 'LIMIT') {
191 if (preg_match('/^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$/', trim($parseString))) {
192 $result['LIMIT'] = $parseString;
193 } else {
194 return $this->parseError('No value for limit!', $parseString);
195 }
196 }
197 }
198 }
199 } else {
200 return $this->parseError('No table to select from!', $parseString);
201 }
202
203 // Store current parseString in the result array for possible further processing (e.g., subquery support by DBAL)
204 $result['parseString'] = $parseString;
205
206 // Return result:
207 return $result;
208 }
209
210 /**
211 * Parsing UPDATE query
212 *
213 * @param string SQL string with UPDATE query to parse
214 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string.
215 * @see compileUPDATE()
216 */
217 protected function parseUPDATE($parseString) {
218
219 // Removing UPDATE
220 $parseString = $this->trimSQL($parseString);
221 $parseString = ltrim(substr($parseString, 6));
222
223 // Init output variable:
224 $result = array();
225 $result['type'] = 'UPDATE';
226
227 // Get table:
228 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
229
230 // Continue if string is not ended:
231 if ($result['TABLE']) {
232 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
233
234 $comma = TRUE;
235
236 // Get field/value pairs:
237 while ($comma) {
238 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*=')) {
239 $this->nextPart($parseString, '^(=)'); // Strip of "=" sign.
240 $value = $this->getValue($parseString);
241 $result['FIELDS'][$fieldName] = $value;
242 } else {
243 return $this->parseError('No fieldname found', $parseString);
244 }
245
246 $comma = $this->nextPart($parseString, '^(,)');
247 }
248
249 // WHERE
250 if ($this->nextPart($parseString, '^(WHERE)')) {
251 $result['WHERE'] = $this->parseWhereClause($parseString);
252 if ($this->parse_error) {
253 return $this->parse_error;
254 }
255 }
256 } else {
257 return $this->parseError('Query missing SET...', $parseString);
258 }
259 } else {
260 return $this->parseError('No table found!', $parseString);
261 }
262
263 // Should be no more content now:
264 if ($parseString) {
265 return $this->parseError('Still content in clause after parsing!', $parseString);
266 }
267
268 // Return result:
269 return $result;
270 }
271
272 /**
273 * Parsing INSERT query
274 *
275 * @param string SQL string with INSERT query to parse
276 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string.
277 * @see compileINSERT()
278 */
279 protected function parseINSERT($parseString) {
280
281 // Removing INSERT
282 $parseString = $this->trimSQL($parseString);
283 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
284
285 // Init output variable:
286 $result = array();
287 $result['type'] = 'INSERT';
288
289 // Get table:
290 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
291
292 if ($result['TABLE']) {
293
294 if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\()')) { // In this case there are no field names mentioned in the SQL!
295 // Get values/fieldnames (depending...)
296 $result['VALUES_ONLY'] = $this->getValue($parseString, 'IN');
297 if ($this->parse_error) {
298 return $this->parse_error;
299 }
300 if (preg_match('/^,/', $parseString)) {
301 $result['VALUES_ONLY'] = array($result['VALUES_ONLY']);
302 $result['EXTENDED'] = '1';
303 while ($this->nextPart($parseString, '^(,)') === ',') {
304 $result['VALUES_ONLY'][] = $this->getValue($parseString, 'IN');
305 if ($this->parse_error) {
306 return $this->parse_error;
307 }
308 }
309 }
310 } else { // There are apparently fieldnames listed:
311 $fieldNames = $this->getValue($parseString, '_LIST');
312 if ($this->parse_error) {
313 return $this->parse_error;
314 }
315
316 if ($this->nextPart($parseString, '^(VALUES)([[:space:]]+|\()')) { // "VALUES" keyword binds the fieldnames to values:
317 $result['FIELDS'] = array();
318 do {
319 $values = $this->getValue($parseString, 'IN'); // Using the "getValue" function to get the field list...
320 if ($this->parse_error) {
321 return $this->parse_error;
322 }
323
324 $insertValues = array();
325 foreach ($fieldNames as $k => $fN) {
326 if (preg_match('/^[[:alnum:]_]+$/', $fN)) {
327 if (isset($values[$k])) {
328 if (!isset($insertValues[$fN])) {
329 $insertValues[$fN] = $values[$k];
330 } else {
331 return $this->parseError('Fieldname ("' . $fN . '") already found in list!', $parseString);
332 }
333 } else {
334 return $this->parseError('No value set!', $parseString);
335 }
336 } else {
337 return $this->parseError('Invalid fieldname ("' . $fN . '")', $parseString);
338 }
339 }
340 if (isset($values[$k + 1])) {
341 return $this->parseError('Too many values in list!', $parseString);
342 }
343 $result['FIELDS'][] = $insertValues;
344 } while ($this->nextPart($parseString, '^(,)') === ',');
345
346 if (count($result['FIELDS']) === 1) {
347 $result['FIELDS'] = $result['FIELDS'][0];
348 } else {
349 $result['EXTENDED'] = '1';
350 }
351 } else {
352 return $this->parseError('VALUES keyword expected', $parseString);
353 }
354 }
355 } else {
356 return $this->parseError('No table found!', $parseString);
357 }
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 protected function parseDELETE($parseString) {
376
377 // Removing DELETE
378 $parseString = $this->trimSQL($parseString);
379 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 4));
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) {
394 return $this->parse_error;
395 }
396 }
397 } else {
398 return $this->parseError('No table found!', $parseString);
399 }
400
401 // Should be no more content now:
402 if ($parseString) {
403 return $this->parseError('Still content in clause after parsing!', $parseString);
404 }
405
406 // Return result:
407 return $result;
408 }
409
410 /**
411 * Parsing EXPLAIN query
412 *
413 * @param string SQL string with EXPLAIN query to parse
414 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string.
415 * @see parseSELECT()
416 */
417 protected function parseEXPLAIN($parseString) {
418
419 // Removing EXPLAIN
420 $parseString = $this->trimSQL($parseString);
421 $parseString = ltrim(substr($parseString, 6));
422
423 // Init output variable:
424 $result = $this->parseSELECT($parseString);
425 if (is_array($result)) {
426 $result['type'] = 'EXPLAIN';
427 }
428
429 return $result;
430 }
431
432 /**
433 * Parsing CREATE TABLE query
434 *
435 * @param string SQL string starting with CREATE TABLE
436 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string.
437 * @see compileCREATETABLE()
438 */
439 protected function parseCREATETABLE($parseString) {
440
441 // Removing CREATE TABLE
442 $parseString = $this->trimSQL($parseString);
443 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 5));
444
445 // Init output variable:
446 $result = array();
447 $result['type'] = 'CREATETABLE';
448
449 // Get table:
450 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(', TRUE);
451
452 if ($result['TABLE']) {
453
454 // While the parseString is not yet empty:
455 while (strlen($parseString) > 0) {
456 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY|UNIQUE KEY|UNIQUE)([[:space:]]+|\()')) { // Getting key
457 $key = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $key));
458
459 switch ($key) {
460 case 'PRIMARYKEY':
461 $result['KEYS']['PRIMARYKEY'] = $this->getValue($parseString, '_LIST');
462 if ($this->parse_error) {
463 return $this->parse_error;
464 }
465 break;
466 case 'UNIQUE':
467 case 'UNIQUEKEY':
468 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
469 $result['KEYS']['UNIQUE'] = array($keyName => $this->getValue($parseString, '_LIST'));
470 if ($this->parse_error) {
471 return $this->parse_error;
472 }
473 } else {
474 return $this->parseError('No keyname found', $parseString);
475 }
476 break;
477 case 'KEY':
478 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
479 $result['KEYS'][$keyName] = $this->getValue($parseString, '_LIST', 'INDEX');
480 if ($this->parse_error) {
481 return $this->parse_error;
482 }
483 } else {
484 return $this->parseError('No keyname found', $parseString);
485 }
486 break;
487 }
488 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field:
489 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
490 if ($this->parse_error) {
491 return $this->parse_error;
492 }
493 }
494
495 // Finding delimiter:
496 $delim = $this->nextPart($parseString, '^(,|\))');
497 if (!$delim) {
498 return $this->parseError('No delimiter found', $parseString);
499 } elseif ($delim == ')') {
500 break;
501 }
502 }
503
504 // Finding what is after the table definition - table type in MySQL
505 if ($delim == ')') {
506 if ($this->nextPart($parseString, '^((ENGINE|TYPE)[[:space:]]*=)')) {
507 $result['tableType'] = $parseString;
508 $parseString = '';
509 }
510 } else {
511 return $this->parseError('No fieldname found!', $parseString);
512 }
513
514 // Getting table type
515 } else {
516 return $this->parseError('No table found!', $parseString);
517 }
518
519 // Should be no more content now:
520 if ($parseString) {
521 return $this->parseError('Still content in clause after parsing!', $parseString);
522 }
523
524 return $result;
525 }
526
527 /**
528 * Parsing ALTER TABLE query
529 *
530 * @param string SQL string starting with ALTER TABLE
531 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string.
532 * @see compileALTERTABLE()
533 */
534 protected function parseALTERTABLE($parseString) {
535
536 // Removing ALTER TABLE
537 $parseString = $this->trimSQL($parseString);
538 $parseString = ltrim(substr(ltrim(substr($parseString, 5)), 5));
539
540 // Init output variable:
541 $result = array();
542 $result['type'] = 'ALTERTABLE';
543
544 // Get table:
545 $hasBackquote = ($this->nextPart($parseString, '^(`)') === '`');
546 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)' . ($hasBackquote ? '`' : '') . '[[:space:]]+');
547 if ($hasBackquote && $this->nextPart($parseString, '^(`)') !== '`') {
548 return $this->parseError('No end backquote found!', $parseString);
549 }
550
551 if ($result['TABLE']) {
552 if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|DROP[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|ADD[[:space:]]+UNIQUE|DROP|ADD|RENAME|DEFAULT[[:space:]]+CHARACTER[[:space:]]+SET|ENGINE)([[:space:]]+|\(|=)')) {
553 $actionKey = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $result['action']));
554
555 // Getting field:
556 if (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY,ENGINE', $actionKey) || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
557
558 switch ($actionKey) {
559 case 'ADD':
560 $result['FIELD'] = $fieldKey;
561 $result['definition'] = $this->parseFieldDef($parseString);
562 if ($this->parse_error) {
563 return $this->parse_error;
564 }
565 break;
566 case 'DROP':
567 case 'RENAME':
568 $result['FIELD'] = $fieldKey;
569 break;
570 case 'CHANGE':
571 $result['FIELD'] = $fieldKey;
572 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
573 $result['definition'] = $this->parseFieldDef($parseString);
574 if ($this->parse_error) {
575 return $this->parse_error;
576 }
577 } else {
578 return $this->parseError('No NEW field name found', $parseString);
579 }
580 break;
581
582 case 'ADDKEY':
583 case 'ADDPRIMARYKEY':
584 case 'ADDUNIQUE':
585 $result['KEY'] = $fieldKey;
586 $result['fields'] = $this->getValue($parseString, '_LIST', 'INDEX');
587 if ($this->parse_error) {
588 return $this->parse_error;
589 }
590 break;
591 case 'DROPKEY':
592 $result['KEY'] = $fieldKey;
593 break;
594 case 'DROPPRIMARYKEY':
595 // ??? todo!
596 break;
597 case 'DEFAULTCHARACTERSET':
598 $result['charset'] = $fieldKey;
599 break;
600 case 'ENGINE':
601 $result['engine'] = $this->nextPart($parseString, '^=[[:space:]]*([[:alnum:]]+)[[:space:]]+', TRUE);
602 break;
603 }
604 } else {
605 return $this->parseError('No field name found', $parseString);
606 }
607 } else {
608 return $this->parseError('No action CHANGE, DROP or ADD found!', $parseString);
609 }
610 } else {
611 return $this->parseError('No table found!', $parseString);
612 }
613
614 // Should be no more content now:
615 if ($parseString) {
616 return $this->parseError('Still content in clause after parsing!', $parseString);
617 }
618
619 return $result;
620 }
621
622 /**
623 * Parsing DROP TABLE query
624 *
625 * @param string SQL string starting with DROP TABLE
626 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
627 */
628 protected function parseDROPTABLE($parseString) {
629
630 // Removing DROP TABLE
631 $parseString = $this->trimSQL($parseString);
632 $parseString = ltrim(substr(ltrim(substr($parseString, 4)), 5));
633
634 // Init output variable:
635 $result = array();
636 $result['type'] = 'DROPTABLE';
637
638 // IF EXISTS
639 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
640
641 // Get table:
642 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
643
644 if ($result['TABLE']) {
645
646 // Should be no more content now:
647 if ($parseString) {
648 return $this->parseError('Still content in clause after parsing!', $parseString);
649 }
650
651 return $result;
652 } else {
653 return $this->parseError('No table found!', $parseString);
654 }
655 }
656
657 /**
658 * Parsing CREATE DATABASE query
659 *
660 * @param string SQL string starting with CREATE DATABASE
661 * @return mixed Returns array with components of CREATE DATABASE query on success, otherwise an error message string.
662 */
663 protected function parseCREATEDATABASE($parseString) {
664
665 // Removing CREATE DATABASE
666 $parseString = $this->trimSQL($parseString);
667 $parseString = ltrim(substr(ltrim(substr($parseString, 6)), 8));
668
669 // Init output variable:
670 $result = array();
671 $result['type'] = 'CREATEDATABASE';
672
673 // Get table:
674 $result['DATABASE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
675
676 if ($result['DATABASE']) {
677
678 // Should be no more content now:
679 if ($parseString) {
680 return $this->parseError('Still content in clause after parsing!', $parseString);
681 }
682
683 return $result;
684 } else {
685 return $this->parseError('No database found!', $parseString);
686 }
687 }
688
689 /**
690 * Parsing TRUNCATE TABLE query
691 *
692 * @param string SQL string starting with TRUNCATE TABLE
693 * @return mixed Returns array with components of TRUNCATE TABLE query on success, otherwise an error message string.
694 */
695 protected function parseTRUNCATETABLE($parseString) {
696
697 // Removing TRUNCATE TABLE
698 $parseString = $this->trimSQL($parseString);
699 $parseString = ltrim(substr(ltrim(substr($parseString, 8)), 5));
700
701 // Init output variable:
702 $result = array();
703 $result['type'] = 'TRUNCATETABLE';
704
705 // Get table:
706 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
707
708 if ($result['TABLE']) {
709
710 // Should be no more content now:
711 if ($parseString) {
712 return $this->parseError('Still content in clause after parsing!', $parseString);
713 }
714
715 return $result;
716 } else {
717 return $this->parseError('No table found!', $parseString);
718 }
719 }
720
721
722 /**************************************
723 *
724 * SQL Parsing, helper functions for parts of queries
725 *
726 **************************************/
727
728 /**
729 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
730 * The output from this function can be compiled back into a field list with ->compileFieldList()
731 * 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!
732 *
733 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
734 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
735 * @return array If successful parsing, returns an array, otherwise an error string.
736 * @see compileFieldList()
737 */
738 public function parseFieldList(&$parseString, $stopRegex = '') {
739
740 $stack = array(); // Contains the parsed content
741
742 if (strlen($parseString) == 0) {
743 return $stack;
744 } // FIXME - should never happen, why does it?
745
746 $pnt = 0; // Pointer to positions in $stack
747 $level = 0; // Indicates the parenthesis level we are at.
748 $loopExit = 0; // Recursivity brake.
749
750 // Prepare variables:
751 $parseString = $this->trimSQL($parseString);
752 $this->lastStopKeyWord = '';
753 $this->parse_error = '';
754
755 // Parse any SQL hint / comments
756 $stack[$pnt]['comments'] = $this->nextPart($parseString, '^(\/\*.*\*\/)');
757
758 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
759 while (strlen($parseString)) {
760
761 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
762 if ($level > 0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
763
764 // Accumulate function content until next () parenthesis:
765 $funcContent = $this->nextPart($parseString, '^([^()]*.)');
766 $stack[$pnt]['func_content.'][] = array(
767 'level' => $level,
768 'func_content' => substr($funcContent, 0, -1)
769 );
770 $stack[$pnt]['func_content'] .= $funcContent;
771
772 // Detecting ( or )
773 switch (substr($stack[$pnt]['func_content'], -1)) {
774 case '(':
775 $level++;
776 break;
777 case ')':
778 $level--;
779 if (!$level) { // If this was the last parenthesis:
780 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'], 0, -1);
781 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
782 }
783 break;
784 }
785 } else { // Outside parenthesis, looking for next field:
786
787 // Looking for a flow-control construct (only known constructs supported)
788 if (preg_match('/^case([[:space:]][[:alnum:]\*._]+)?[[:space:]]when/i', $parseString)) {
789 $stack[$pnt]['type'] = 'flow-control';
790 $stack[$pnt]['flow-control'] = $this->parseCaseStatement($parseString);
791 // Looking for "AS" alias:
792 if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
793 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
794 $stack[$pnt]['as_keyword'] = $as;
795 }
796 } else {
797 // Looking for a known function (only known functions supported)
798 $func = $this->nextPart($parseString, '^(count|max|min|floor|sum|avg)[[:space:]]*\(');
799 if ($func) {
800 $parseString = trim(substr($parseString, 1)); // Strip of "("
801 $stack[$pnt]['type'] = 'function';
802 $stack[$pnt]['function'] = $func;
803 $level++; // increse parenthesis level counter.
804 } else {
805 $stack[$pnt]['distinct'] = $this->nextPart($parseString, '^(distinct[[:space:]]+)');
806 // Otherwise, look for regular fieldname:
807 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)(,|[[:space:]]+)')) !== '') {
808 $stack[$pnt]['type'] = 'field';
809
810 // Explode fieldname into field and table:
811 $tableField = explode('.', $fieldName, 2);
812 if (count($tableField) == 2) {
813 $stack[$pnt]['table'] = $tableField[0];
814 $stack[$pnt]['field'] = $tableField[1];
815 } else {
816 $stack[$pnt]['table'] = '';
817 $stack[$pnt]['field'] = $tableField[0];
818 }
819 } else {
820 return $this->parseError('No field name found as expected in parseFieldList()', $parseString);
821 }
822 }
823 }
824 }
825
826 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
827 if (!$level) {
828
829 // Looking for "AS" alias:
830 if ($as = $this->nextPart($parseString, '^(AS)[[:space:]]+')) {
831 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)');
832 $stack[$pnt]['as_keyword'] = $as;
833 }
834
835 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
836 if ($sDir = $this->nextPart($parseString, '^(ASC|DESC)([[:space:]]+|,)')) {
837 $stack[$pnt]['sortDir'] = $sDir;
838 }
839
840 // Looking for stop-keywords:
841 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
842 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord));
843 return $stack;
844 }
845
846 // Looking for comma (since the stop-keyword did not trigger a return...)
847 if (strlen($parseString) && !$this->nextPart($parseString, '^(,)')) {
848 return $this->parseError('No comma found as expected in parseFieldList()', $parseString);
849 }
850
851 // Increasing pointer:
852 $pnt++;
853 }
854
855 // Check recursivity brake:
856 $loopExit++;
857 if ($loopExit > 500) {
858 return $this->parseError('More than 500 loops, exiting prematurely in parseFieldList()...', $parseString);
859 }
860 }
861
862 // Return result array:
863 return $stack;
864 }
865
866 /**
867 * Parsing a CASE ... WHEN flow-control construct.
868 * The output from this function can be compiled back with ->compileCaseStatement()
869 *
870 * @param string The string with the CASE ... WHEN construct, eg. "CASE field WHEN 1 THEN 0 ELSE ..." etc. NOTICE: passed by reference!
871 * @return array If successful parsing, returns an array, otherwise an error string.
872 * @see compileCaseConstruct()
873 */
874 protected function parseCaseStatement(&$parseString) {
875 $result = array();
876 $result['type'] = $this->nextPart($parseString, '^(case)[[:space:]]+');
877 if (!preg_match('/^when[[:space:]]+/i', $parseString)) {
878 $value = $this->getValue($parseString);
879 if (!(isset($value[1]) || is_numeric($value[0]))) {
880 $result['case_field'] = $value[0];
881 } else {
882 $result['case_value'] = $value;
883 }
884 }
885 $result['when'] = array();
886 while ($this->nextPart($parseString, '^(when)[[:space:]]')) {
887 $when = array();
888 $when['when_value'] = $this->parseWhereClause($parseString, '^(then)[[:space:]]+');
889 $when['then_value'] = $this->getValue($parseString);
890 $result['when'][] = $when;
891 }
892 if ($this->nextPart($parseString, '^(else)[[:space:]]+')) {
893 $result['else'] = $this->getValue($parseString);
894 }
895 if (!$this->nextPart($parseString, '^(end)[[:space:]]+')) {
896 return $this->parseError('No "end" keyword found as expected in parseCaseStatement()', $parseString);
897 }
898 return $result;
899 }
900
901 /**
902 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
903 * The success of this parsing determines if that part of the query is supported by TYPO3.
904 *
905 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
906 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
907 * @return array If successful parsing, returns an array, otherwise an error string.
908 * @see compileFromTables()
909 */
910 public function parseFromTables(&$parseString, $stopRegex = '') {
911
912 // Prepare variables:
913 $parseString = $this->trimSQL($parseString);
914 $this->lastStopKeyWord = '';
915 $this->parse_error = '';
916
917 $stack = array(); // Contains the parsed content
918 $pnt = 0; // Pointer to positions in $stack
919 $loopExit = 0; // Recursivity brake.
920
921 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
922 while (strlen($parseString)) {
923 // Looking for the table:
924 if ($stack[$pnt]['table'] = $this->nextPart($parseString, '^([[:alnum:]_]+)(,|[[:space:]]+)')) {
925 // Looking for stop-keywords before fetching potential table alias:
926 if ($stopRegex && ($this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex))) {
927 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord));
928 return $stack;
929 }
930 if (!preg_match('/^(LEFT|RIGHT|JOIN|INNER)[[:space:]]+/i', $parseString)) {
931 $stack[$pnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
932 $stack[$pnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*');
933 }
934 } else {
935 return $this->parseError('No table name found as expected in parseFromTables()!', $parseString);
936 }
937
938 // Looking for JOIN
939 $joinCnt = 0;
940 while ($join = $this->nextPart($parseString, '^(LEFT[[:space:]]+JOIN|LEFT[[:space:]]+OUTER[[:space:]]+JOIN|RIGHT[[:space:]]+JOIN|RIGHT[[:space:]]+OUTER[[:space:]]+JOIN|INNER[[:space:]]+JOIN|JOIN)[[:space:]]+')) {
941 $stack[$pnt]['JOIN'][$joinCnt]['type'] = $join;
942 if ($stack[$pnt]['JOIN'][$joinCnt]['withTable'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+', 1)) {
943 if (!preg_match('/^ON[[:space:]]+/i', $parseString)) {
944 $stack[$pnt]['JOIN'][$joinCnt]['as_keyword'] = $this->nextPart($parseString, '^(AS[[:space:]]+)');
945 $stack[$pnt]['JOIN'][$joinCnt]['as'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
946 }
947 if (!$this->nextPart($parseString, '^(ON[[:space:]]+)')) {
948 return $this->parseError('No join condition found in parseFromTables()!', $parseString);
949 }
950 $stack[$pnt]['JOIN'][$joinCnt]['ON'] = array();
951 $condition = array('operator' => '');
952 $parseCondition = TRUE;
953 while ($parseCondition) {
954 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)[[:space:]]*(<=|>=|<|>|=|!=)')) !== '') {
955 // Parse field name into field and table:
956 $tableField = explode('.', $fieldName, 2);
957 $condition['left'] = array();
958 if (count($tableField) == 2) {
959 $condition['left']['table'] = $tableField[0];
960 $condition['left']['field'] = $tableField[1];
961 } else {
962 $condition['left']['table'] = '';
963 $condition['left']['field'] = $tableField[0];
964 }
965 } else {
966 return $this->parseError('No join field found in parseFromTables()!', $parseString);
967 }
968 // Find "comparator":
969 $condition['comparator'] = $this->nextPart($parseString, '^(<=|>=|<|>|=|!=)');
970 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) !== '') {
971 // Parse field name into field and table:
972 $tableField = explode('.', $fieldName, 2);
973 $condition['right'] = array();
974 if (count($tableField) == 2) {
975 $condition['right']['table'] = $tableField[0];
976 $condition['right']['field'] = $tableField[1];
977 } else {
978 $condition['right']['table'] = '';
979 $condition['right']['field'] = $tableField[0];
980 }
981 } else {
982 return $this->parseError('No join field found in parseFromTables()!', $parseString);
983 }
984 $stack[$pnt]['JOIN'][$joinCnt]['ON'][] = $condition;
985 if (($operator = $this->nextPart($parseString, '^(AND|OR)')) !== '') {
986 $condition = array('operator' => $operator);
987 } else {
988 $parseCondition = FALSE;
989 }
990 }
991 $joinCnt++;
992 } else {
993 return $this->parseError('No join table found in parseFromTables()!', $parseString);
994 }
995 }
996
997 // Looking for stop-keywords:
998 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
999 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord));
1000 return $stack;
1001 }
1002
1003 // Looking for comma:
1004 if (strlen($parseString) && !$this->nextPart($parseString, '^(,)')) {
1005 return $this->parseError('No comma found as expected in parseFromTables()', $parseString);
1006 }
1007
1008 // Increasing pointer:
1009 $pnt++;
1010
1011 // Check recursivity brake:
1012 $loopExit++;
1013 if ($loopExit > 500) {
1014 return $this->parseError('More than 500 loops, exiting prematurely in parseFromTables()...', $parseString);
1015 }
1016 }
1017
1018 // Return result array:
1019 return $stack;
1020 }
1021
1022 /**
1023 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1024 * The success of this parsing determines if that part of the query is supported by TYPO3.
1025 *
1026 * @param string WHERE clause to parse. NOTICE: passed by reference!
1027 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1028 * @param array Array holding references to either named (:name) or question mark (?) parameters found
1029 * @return mixed If successful parsing, returns an array, otherwise an error string.
1030 */
1031 public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = array()) {
1032
1033 // Prepare variables:
1034 $parseString = $this->trimSQL($parseString);
1035 $this->lastStopKeyWord = '';
1036 $this->parse_error = '';
1037
1038 $stack = array(0 => array()); // Contains the parsed content
1039 $pnt = array(0 => 0); // Pointer to positions in $stack
1040 $level = 0; // Determines parenthesis level
1041 $loopExit = 0; // Recursivity brake.
1042
1043 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1044 while (strlen($parseString)) {
1045
1046 // Look for next parenthesis level:
1047 $newLevel = $this->nextPart($parseString, '^([(])');
1048 if ($newLevel == '(') { // If new level is started, manage stack/pointers:
1049 $level++; // Increase level
1050 $pnt[$level] = 0; // Reset pointer for this level
1051 $stack[$level] = array(); // Reset stack for this level
1052 } else { // If no new level is started, just parse the current level:
1053
1054 // Find "modifier", eg. "NOT or !"
1055 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString, '^(!|NOT[[:space:]]+)'));
1056
1057 // See if condition is EXISTS with a subquery
1058 if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) {
1059 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*');
1060 $parseString = trim(substr($parseString, 1)); // Strip of "("
1061 $stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
1062 // Seek to new position in parseString after parsing of the subquery
1063 $parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];
1064 unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']);
1065 if (!$this->nextPart($parseString, '^([)])')) {
1066 return 'No ) parenthesis at end of subquery';
1067 }
1068 } else {
1069
1070 // See if LOCATE function is found
1071 if (preg_match('/^LOCATE[[:space:]]*[(]/i', $parseString)) {
1072 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(LOCATE)[[:space:]]*');
1073 $parseString = trim(substr($parseString, 1)); // Strip of "("
1074 $stack[$level][$pnt[$level]]['func']['substr'] = $this->getValue($parseString);
1075 if (!$this->nextPart($parseString, '^(,)')) {
1076 return $this->parseError('No comma found as expected in parseWhereClause()', $parseString);
1077 }
1078 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)[[:space:]]*')) {
1079
1080 // Parse field name into field and table:
1081 $tableField = explode('.', $fieldName, 2);
1082 if (count($tableField) == 2) {
1083 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1084 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1085 } else {
1086 $stack[$level][$pnt[$level]]['func']['table'] = '';
1087 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1088 }
1089 } else {
1090 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1091 }
1092 if ($this->nextPart($parseString, '^(,)')) {
1093 $stack[$level][$pnt[$level]]['func']['pos'] = $this->getValue($parseString);
1094 }
1095 if (!$this->nextPart($parseString, '^([)])')) {
1096 return $this->parseError('No ) parenthesis at end of function', $parseString);
1097 }
1098 } elseif (preg_match('/^IFNULL[[:space:]]*[(]/i', $parseString)) {
1099 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(IFNULL)[[:space:]]*');
1100 $parseString = trim(substr($parseString, 1)); // Strip of "("
1101 if ($fieldName = $this->nextPart($parseString, '^([[:alnum:]\*._]+)[[:space:]]*')) {
1102
1103 // Parse field name into field and table:
1104 $tableField = explode('.', $fieldName, 2);
1105 if (count($tableField) == 2) {
1106 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1107 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1108 } else {
1109 $stack[$level][$pnt[$level]]['func']['table'] = '';
1110 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1111 }
1112 } else {
1113 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1114 }
1115 if ($this->nextPart($parseString, '^(,)')) {
1116 $stack[$level][$pnt[$level]]['func']['default'] = $this->getValue($parseString);
1117 }
1118 if (!$this->nextPart($parseString, '^([)])')) {
1119 return $this->parseError('No ) parenthesis at end of function', $parseString);
1120 }
1121 } elseif (preg_match('/^FIND_IN_SET[[:space:]]*[(]/i', $parseString)) {
1122 $stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(FIND_IN_SET)[[:space:]]*');
1123 $parseString = trim(substr($parseString, 1)); // Strip of "("
1124 if ($str = $this->getValue($parseString)) {
1125 $stack[$level][$pnt[$level]]['func']['str'] = $str;
1126 if ($fieldName = $this->nextPart($parseString, '^,[[:space:]]*([[:alnum:]._]+)[[:space:]]*', TRUE)) {
1127
1128 // Parse field name into field and table:
1129 $tableField = explode('.', $fieldName, 2);
1130 if (count($tableField) == 2) {
1131 $stack[$level][$pnt[$level]]['func']['table'] = $tableField[0];
1132 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[1];
1133 } else {
1134 $stack[$level][$pnt[$level]]['func']['table'] = '';
1135 $stack[$level][$pnt[$level]]['func']['field'] = $tableField[0];
1136 }
1137 } else {
1138 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1139 }
1140 if (!$this->nextPart($parseString, '^([)])')) {
1141 return $this->parseError('No ) parenthesis at end of function', $parseString);
1142 }
1143 } else {
1144 return $this->parseError('No item to look for found as expected in parseWhereClause()', $parseString);
1145 }
1146 } else {
1147
1148 // Support calculated value only for:
1149 // - "&" (boolean AND)
1150 // - "+" (addition)
1151 // - "-" (substraction)
1152 // - "*" (multiplication)
1153 // - "/" (division)
1154 // - "%" (modulo)
1155 $calcOperators = '&|\+|-|\*|\/|%';
1156
1157 // Fieldname:
1158 if (($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)([[:space:]]+|' . $calcOperators . '|<=|>=|<|>|=|!=|IS)')) !== '') {
1159
1160 // Parse field name into field and table:
1161 $tableField = explode('.', $fieldName, 2);
1162 if (count($tableField) == 2) {
1163 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
1164 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
1165 } else {
1166 $stack[$level][$pnt[$level]]['table'] = '';
1167 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
1168 }
1169 } else {
1170 return $this->parseError('No field name found as expected in parseWhereClause()', $parseString);
1171 }
1172
1173 // See if the value is calculated:
1174 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString, '^(' . $calcOperators . ')');
1175 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
1176 // Finding value for calculation:
1177 $calc_value = $this->getValue($parseString);
1178 $stack[$level][$pnt[$level]]['calc_value'] = $calc_value;
1179 if (count($calc_value) == 1 && is_string($calc_value[0])) {
1180 // Value is a field, store it to allow DBAL to post-process it (quoting, remapping)
1181 $tableField = explode('.', $calc_value[0], 2);
1182 if (count($tableField) == 2) {
1183 $stack[$level][$pnt[$level]]['calc_table'] = $tableField[0];
1184 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[1];
1185 } else {
1186 $stack[$level][$pnt[$level]]['calc_table'] = '';
1187 $stack[$level][$pnt[$level]]['calc_field'] = $tableField[0];
1188 }
1189 }
1190 }
1191 }
1192
1193 // Find "comparator":
1194 $comparatorPatterns = array(
1195 '<=', '>=', '<>', '<', '>', '=', '!=',
1196 'NOT[[:space:]]+IN', 'IN',
1197 'NOT[[:space:]]+LIKE[[:space:]]+BINARY', 'LIKE[[:space:]]+BINARY', 'NOT[[:space:]]+LIKE', 'LIKE',
1198 'IS[[:space:]]+NOT', 'IS',
1199 'BETWEEN', 'NOT[[:space]]+BETWEEN',
1200 );
1201 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString, '^(' . implode('|', $comparatorPatterns) . ')');
1202 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
1203 if (preg_match('/^CONCAT[[:space:]]*\(/', $parseString)) {
1204 $this->nextPart($parseString, '^(CONCAT[[:space:]]?[(])');
1205 $values = array(
1206 'operator' => 'CONCAT',
1207 'args' => array(),
1208 );
1209 $cnt = 0;
1210 while ($fieldName = $this->nextPart($parseString, '^([[:alnum:]._]+)')) {
1211 // Parse field name into field and table:
1212 $tableField = explode('.', $fieldName, 2);
1213 if (count($tableField) == 2) {
1214 $values['args'][$cnt]['table'] = $tableField[0];
1215 $values['args'][$cnt]['field'] = $tableField[1];
1216 } else {
1217 $values['args'][$cnt]['table'] = '';
1218 $values['args'][$cnt]['field'] = $tableField[0];
1219 }
1220 // Looking for comma:
1221 $this->nextPart($parseString, '^(,)');
1222 $cnt++;
1223 }
1224 // Look for ending parenthesis:
1225 $this->nextPart($parseString, '([)])');
1226 $stack[$level][$pnt[$level]]['value'] = $values;
1227 } else {
1228 if (t3lib_div::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {
1229 $this->nextPart($parseString, '^([(])');
1230 $stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
1231 // Seek to new position in parseString after parsing of the subquery
1232 $parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
1233 unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
1234 if (!$this->nextPart($parseString, '^([)])')) {
1235 return 'No ) parenthesis at end of subquery';
1236 }
1237 } else {
1238 if (t3lib_div::inList('BETWEEN,NOT BETWEEN', $stack[$level][$pnt[$level]]['comparator'])) {
1239 $stack[$level][$pnt[$level]]['values'] = array();
1240 $stack[$level][$pnt[$level]]['values'][0] = $this->getValue($parseString);
1241 if (!$this->nextPart($parseString, '^(AND)')) {
1242 return $this->parseError('No AND operator found as expected in parseWhereClause()', $parseString);
1243 }
1244 $stack[$level][$pnt[$level]]['values'][1] = $this->getValue($parseString);
1245 } else {
1246 // Finding value for comparator:
1247 $stack[$level][$pnt[$level]]['value'] = &$this->getValueOrParameter($parseString, $stack[$level][$pnt[$level]]['comparator'], '', $parameterReferences);
1248 if ($this->parse_error) {
1249 return $this->parse_error;
1250 }
1251 }
1252 }
1253 }
1254 }
1255 }
1256
1257 // Finished, increase pointer:
1258 $pnt[$level]++;
1259
1260 // Checking if we are back to level 0 and we should still decrease level,
1261 // meaning we were probably parsing as subquery and should return here:
1262 if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1263 // Return the stacks lowest level:
1264 return $stack[0];
1265 }
1266
1267 // Checking if we are back to level 0 and we should still decrease level,
1268 // meaning we were probably parsing a subquery and should return here:
1269 if ($level === 0 && preg_match('/^[)]/', $parseString)) {
1270 // Return the stacks lowest level:
1271 return $stack[0];
1272 }
1273
1274 // Checking if the current level is ended, in that case do stack management:
1275 while ($this->nextPart($parseString, '^([)])')) {
1276 $level--; // Decrease level:
1277 $stack[$level][$pnt[$level]]['sub'] = $stack[$level + 1]; // Copy stack
1278 $pnt[$level]++; // Increase pointer of the new level
1279
1280 // Make recursivity check:
1281 $loopExit++;
1282 if ($loopExit > 500) {
1283 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely in parseWhereClause()...', $parseString);
1284 }
1285 }
1286
1287 // Detecting the operator for the next level:
1288 $op = $this->nextPart($parseString, '^(AND[[:space:]]+NOT|&&[[:space:]]+NOT|OR[[:space:]]+NOT|OR[[:space:]]+NOT|\|\|[[:space:]]+NOT|AND|&&|OR|\|\|)(\(|[[:space:]]+)');
1289 if ($op) {
1290 // Normalize boolean operator
1291 $op = str_replace(array('&&', '||'), array('AND', 'OR'), $op);
1292 $stack[$level][$pnt[$level]]['operator'] = $op;
1293 } elseif (strlen($parseString)) {
1294
1295 // Looking for stop-keywords:
1296 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1297 $this->lastStopKeyWord = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $this->lastStopKeyWord));
1298 return $stack[0];
1299 } else {
1300 return $this->parseError('No operator, but parsing not finished in parseWhereClause().', $parseString);
1301 }
1302 }
1303 }
1304
1305 // Make recursivity check:
1306 $loopExit++;
1307 if ($loopExit > 500) {
1308 return $this->parseError('More than 500 loops, exiting prematurely in parseWhereClause()...', $parseString);
1309 }
1310 }
1311
1312 // Return the stacks lowest level:
1313 return $stack[0];
1314 }
1315
1316 /**
1317 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1318 * The success of this parsing determines if that part of the query is supported by TYPO3.
1319 *
1320 * @param string WHERE clause to parse. NOTICE: passed by reference!
1321 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1322 * @return mixed If successful parsing, returns an array, otherwise an error string.
1323 */
1324 public function parseFieldDef(&$parseString, $stopRegex = '') {
1325 // Prepare variables:
1326 $parseString = $this->trimSQL($parseString);
1327 $this->lastStopKeyWord = '';
1328 $this->parse_error = '';
1329
1330 $result = array();
1331
1332 // Field type:
1333 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:],]+|\()')) {
1334
1335 // Looking for value:
1336 if (substr($parseString, 0, 1) == '(') {
1337 $parseString = substr($parseString, 1);
1338 if ($result['value'] = $this->nextPart($parseString, '^([^)]*)')) {
1339 $parseString = ltrim(substr($parseString, 1));
1340 } else {
1341 return $this->parseError('No end-parenthesis for value found in parseFieldDef()!', $parseString);
1342 }
1343 }
1344
1345 // Looking for keywords
1346 while ($keyword = $this->nextPart($parseString, '^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
1347 $keywordCmp = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $keyword));
1348
1349 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1350
1351 switch ($keywordCmp) {
1352 case 'DEFAULT':
1353 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1354 break;
1355 }
1356 }
1357 } else {
1358 return $this->parseError('Field type unknown in parseFieldDef()!', $parseString);
1359 }
1360
1361 return $result;
1362 }
1363
1364
1365 /************************************
1366 *
1367 * Parsing: Helper functions
1368 *
1369 ************************************/
1370
1371 /**
1372 * Strips off a part of the parseString and returns the matching part.
1373 * Helper function for the parsing methods.
1374 *
1375 * @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.
1376 * @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())
1377 * @param boolean If set the full match of the regex is stripped of the beginning of the string!
1378 * @return string The value of the first parenthesis level of the REGEX.
1379 */
1380 protected function nextPart(&$parseString, $regex, $trimAll = FALSE) {
1381 $reg = array();
1382 if (preg_match('/' . $regex . '/i', $parseString . ' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1383 $parseString = ltrim(substr($parseString, strlen($reg[$trimAll ? 0 : 1])));
1384 return $reg[1];
1385 }
1386 // No match found
1387 return '';
1388 }
1389
1390 /**
1391 * Finds value or either named (:name) or question mark (?) parameter markers at the beginning
1392 * of $parseString, returns result and strips it of parseString.
1393 * This method returns a pointer to the parameter or value that was found. In case of a parameter
1394 * the pointer is a reference to the corresponding item in array $parameterReferences.
1395 *
1396 * @param string $parseString The parseString
1397 * @param string $comparator The comparator used before.
1398 * @param string $mode The mode, e.g., "INDEX"
1399 * @param mixed The value (string/integer) or parameter (:name/?). Otherwise an array with error message in first key (0)
1400 */
1401 protected function &getValueOrParameter(&$parseString, $comparator = '', $mode = '', array &$parameterReferences = array()) {
1402 $parameter = $this->nextPart($parseString, '^(\\:[[:alnum:]_]+|\\?)');
1403 if ($parameter === '?') {
1404 if (!isset($parameterReferences['?'])) {
1405 $parameterReferences['?'] = array();
1406 }
1407 $value = array('?');
1408 $parameterReferences['?'][] = &$value;
1409 } elseif ($parameter !== '') { // named parameter
1410 if (isset($parameterReferences[$parameter])) {
1411 // Use the same reference as last time we encountered this parameter
1412 $value = &$parameterReferences[$parameter];
1413 } else {
1414 $value = array($parameter);
1415 $parameterReferences[$parameter] = &$value;
1416 }
1417 } else {
1418 $value = $this->getValue($parseString, $comparator, $mode);
1419 }
1420 return $value;
1421 }
1422
1423 /**
1424 * Finds value in beginning of $parseString, returns result and strips it of parseString
1425 *
1426 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
1427 * @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)
1428 * @param string The mode, eg. "INDEX"
1429 * @return mixed The value (string/integer). Otherwise an array with error message in first key (0)
1430 */
1431 protected function getValue(&$parseString, $comparator = '', $mode = '') {
1432 $value = '';
1433
1434 if (t3lib_div::inList('NOTIN,IN,_LIST', strtoupper(str_replace(array(' ', LF, CR, TAB), '', $comparator)))) { // List of values:
1435 if ($this->nextPart($parseString, '^([(])')) {
1436 $listValues = array();
1437 $comma = ',';
1438
1439 while ($comma == ',') {
1440 $listValues[] = $this->getValue($parseString);
1441 if ($mode === 'INDEX') {
1442 // Remove any length restriction on INDEX definition
1443 $this->nextPart($parseString, '^([(]\d+[)])');
1444 }
1445 $comma = $this->nextPart($parseString, '^([,])');
1446 }
1447
1448 $out = $this->nextPart($parseString, '^([)])');
1449 if ($out) {
1450 if ($comparator == '_LIST') {
1451 $kVals = array();
1452 foreach ($listValues as $vArr) {
1453 $kVals[] = $vArr[0];
1454 }
1455 return $kVals;
1456 } else {
1457 return $listValues;
1458 }
1459 } else {
1460 return array($this->parseError('No ) parenthesis in list', $parseString));
1461 }
1462 } else {
1463 return array($this->parseError('No ( parenthesis starting the list', $parseString));
1464 }
1465
1466 } else { // Just plain string value, in quotes or not:
1467
1468 // Quote?
1469 $firstChar = substr($parseString, 0, 1);
1470 switch ($firstChar) {
1471 case '"':
1472 $value = array($this->getValueInQuotes($parseString, '"'), '"');
1473 break;
1474 case "'":
1475 $value = array($this->getValueInQuotes($parseString, "'"), "'");
1476 break;
1477 default:
1478 $reg = array();
1479 if (preg_match('/^([[:alnum:]._-]+)/i', $parseString, $reg)) {
1480 $parseString = ltrim(substr($parseString, strlen($reg[0])));
1481 $value = array($reg[1]);
1482 }
1483 break;
1484 }
1485 }
1486 return $value;
1487 }
1488
1489 /**
1490 * Get value in quotes from $parseString.
1491 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1492 *
1493 * @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.
1494 * @param string The quote used; input either " or '
1495 * @return string The value, passed through stripslashes() !
1496 */
1497 protected function getValueInQuotes(&$parseString, $quote) {
1498
1499 $parts = explode($quote, substr($parseString, 1));
1500 $buffer = '';
1501 foreach ($parts as $k => $v) {
1502 $buffer .= $v;
1503
1504 $reg = array();
1505 preg_match('/\\\\$/', $v, $reg);
1506 if ($reg AND strlen($reg[0]) % 2) {
1507 $buffer .= $quote;
1508 } else {
1509 $parseString = ltrim(substr($parseString, strlen($buffer) + 2));
1510 return $this->parseStripslashes($buffer);
1511 }
1512 }
1513 }
1514
1515 /**
1516 * Strip slashes function used for parsing
1517 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1518 *
1519 * @param string Input string
1520 * @return string Output string
1521 */
1522 protected function parseStripslashes($str) {
1523 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1524 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1525
1526 return str_replace($search, $replace, $str);
1527 }
1528
1529 /**
1530 * Add slashes function used for compiling queries
1531 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1532 *
1533 * @param string Input string
1534 * @return string Output string
1535 */
1536 protected function compileAddslashes($str) {
1537 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1538 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1539
1540 return str_replace($search, $replace, $str);
1541 }
1542
1543 /**
1544 * Setting the internal error message value, $this->parse_error and returns that value.
1545 *
1546 * @param string Input error message
1547 * @param string Remaining query to parse.
1548 * @return string Error message.
1549 */
1550 protected function parseError($msg, $restQuery) {
1551 $this->parse_error = 'SQL engine parse ERROR: ' . $msg . ': near "' . substr($restQuery, 0, 50) . '"';
1552 return $this->parse_error;
1553 }
1554
1555 /**
1556 * Trimming SQL as preparation for parsing.
1557 * ";" in the end is stripped of.
1558 * White space is trimmed away around the value
1559 * A single space-char is added in the end
1560 *
1561 * @param string Input string
1562 * @return string Output string
1563 */
1564 protected function trimSQL($str) {
1565 return trim(rtrim($str, "; \r\n\t")) . ' ';
1566 }
1567
1568
1569 /*************************
1570 *
1571 * Compiling queries
1572 *
1573 *************************/
1574
1575 /**
1576 * Compiles an SQL query from components
1577 *
1578 * @param array Array of SQL query components
1579 * @return string SQL query
1580 * @see parseSQL()
1581 */
1582 public function compileSQL($components) {
1583 switch ($components['type']) {
1584 case 'SELECT':
1585 $query = $this->compileSELECT($components);
1586 break;
1587 case 'UPDATE':
1588 $query = $this->compileUPDATE($components);
1589 break;
1590 case 'INSERT':
1591 $query = $this->compileINSERT($components);
1592 break;
1593 case 'DELETE':
1594 $query = $this->compileDELETE($components);
1595 break;
1596 case 'EXPLAIN':
1597 $query = 'EXPLAIN ' . $this->compileSELECT($components);
1598 break;
1599 case 'DROPTABLE':
1600 $query = 'DROP TABLE' . ($components['ifExists'] ? ' IF EXISTS' : '') . ' ' . $components['TABLE'];
1601 break;
1602 case 'CREATETABLE':
1603 $query = $this->compileCREATETABLE($components);
1604 break;
1605 case 'ALTERTABLE':
1606 $query = $this->compileALTERTABLE($components);
1607 break;
1608 case 'TRUNCATETABLE':
1609 $query = $this->compileTRUNCATETABLE($components);
1610 break;
1611 }
1612
1613 return $query;
1614 }
1615
1616 /**
1617 * Compiles a SELECT statement from components array
1618 *
1619 * @param array Array of SQL query components
1620 * @return string SQL SELECT query
1621 * @see parseSELECT()
1622 */
1623 protected function compileSELECT($components) {
1624
1625 // Initialize:
1626 $where = $this->compileWhereClause($components['WHERE']);
1627 $groupBy = $this->compileFieldList($components['GROUPBY']);
1628 $orderBy = $this->compileFieldList($components['ORDERBY']);
1629 $limit = $components['LIMIT'];
1630
1631 // Make query:
1632 $query = 'SELECT ' . ($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'] . '' : '') . '
1633 ' . $this->compileFieldList($components['SELECT']) . '
1634 FROM ' . $this->compileFromTables($components['FROM']) .
1635 (strlen($where) ? '
1636 WHERE ' . $where : '') .
1637 (strlen($groupBy) ? '
1638 GROUP BY ' . $groupBy : '') .
1639 (strlen($orderBy) ? '
1640 ORDER BY ' . $orderBy : '') .
1641 (strlen($limit) ? '
1642 LIMIT ' . $limit : '');
1643
1644 return $query;
1645 }
1646
1647 /**
1648 * Compiles an UPDATE statement from components array
1649 *
1650 * @param array Array of SQL query components
1651 * @return string SQL UPDATE query
1652 * @see parseUPDATE()
1653 */
1654 protected function compileUPDATE($components) {
1655
1656 // Where clause:
1657 $where = $this->compileWhereClause($components['WHERE']);
1658
1659 // Fields
1660 $fields = array();
1661 foreach ($components['FIELDS'] as $fN => $fV) {
1662 $fields[] = $fN . '=' . $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1];
1663 }
1664
1665 // Make query:
1666 $query = 'UPDATE ' . $components['TABLE'] . ' SET
1667 ' . implode(',
1668 ', $fields) . '
1669 ' . (strlen($where) ? '
1670 WHERE ' . $where : '');
1671
1672 return $query;
1673 }
1674
1675 /**
1676 * Compiles an INSERT statement from components array
1677 *
1678 * @param array Array of SQL query components
1679 * @return string SQL INSERT query
1680 * @see parseINSERT()
1681 */
1682 protected function compileINSERT($components) {
1683 $values = array();
1684
1685 if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {
1686 $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);
1687 $tableFields = array();
1688 } else {
1689 $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);
1690 $tableFields = array_keys($valuesComponents[0]);
1691 }
1692
1693 foreach ($valuesComponents as $valuesComponent) {
1694 $fields = array();
1695 foreach ($valuesComponent as $fV) {
1696 $fields[] = $fV[1] . $this->compileAddslashes($fV[0]) . $fV[1];
1697 }
1698 $values[] = '(' . implode(',
1699 ', $fields) . ')';
1700 }
1701
1702 // Make query:
1703 $query = 'INSERT INTO ' . $components['TABLE'];
1704 if (count($tableFields)) {
1705 $query .= '
1706 (' . implode(',
1707 ', $tableFields) . ')';
1708 }
1709 $query .= '
1710 VALUES
1711 ' . implode(',
1712 ', $values);
1713
1714 return $query;
1715 }
1716
1717 /**
1718 * Compiles an DELETE statement from components array
1719 *
1720 * @param array Array of SQL query components
1721 * @return string SQL DELETE query
1722 * @see parseDELETE()
1723 */
1724 protected function compileDELETE($components) {
1725
1726 // Where clause:
1727 $where = $this->compileWhereClause($components['WHERE']);
1728
1729 // Make query:
1730 $query = 'DELETE FROM ' . $components['TABLE'] .
1731 (strlen($where) ? '
1732 WHERE ' . $where : '');
1733
1734 return $query;
1735 }
1736
1737 /**
1738 * Compiles a CREATE TABLE statement from components array
1739 *
1740 * @param array Array of SQL query components
1741 * @return string SQL CREATE TABLE query
1742 * @see parseCREATETABLE()
1743 */
1744 protected function compileCREATETABLE($components) {
1745
1746 // Create fields and keys:
1747 $fieldsKeys = array();
1748 foreach ($components['FIELDS'] as $fN => $fCfg) {
1749 $fieldsKeys[] = $fN . ' ' . $this->compileFieldCfg($fCfg['definition']);
1750 }
1751 foreach ($components['KEYS'] as $kN => $kCfg) {
1752 if ($kN === 'PRIMARYKEY') {
1753 $fieldsKeys[] = 'PRIMARY KEY (' . implode(',', $kCfg) . ')';
1754 } elseif ($kN === 'UNIQUE') {
1755 $key = key($kCfg);
1756 $fields = current($kCfg);
1757 $fieldsKeys[] = 'UNIQUE KEY ' . $key . ' (' . implode(',', $fields) . ')';
1758 } else {
1759 $fieldsKeys[] = 'KEY ' . $kN . ' (' . implode(',', $kCfg) . ')';
1760 }
1761 }
1762
1763 // Make query:
1764 $query = 'CREATE TABLE ' . $components['TABLE'] . ' (
1765 ' . implode(',
1766 ', $fieldsKeys) . '
1767 )' . ($components['tableType'] ? ' TYPE=' . $components['tableType'] : '');
1768
1769 return $query;
1770 }
1771
1772 /**
1773 * Compiles an ALTER TABLE statement from components array
1774 *
1775 * @param array Array of SQL query components
1776 * @return string SQL ALTER TABLE query
1777 * @see parseALTERTABLE()
1778 */
1779 protected function compileALTERTABLE($components) {
1780
1781 // Make query:
1782 $query = 'ALTER TABLE ' . $components['TABLE'] . ' ' . $components['action'] . ' ' . ($components['FIELD'] ? $components['FIELD'] : $components['KEY']);
1783
1784 // Based on action, add the final part:
1785 switch (strtoupper(str_replace(array(' ', TAB, CR, LF), '', $components['action']))) {
1786 case 'ADD':
1787 $query .= ' ' . $this->compileFieldCfg($components['definition']);
1788 break;
1789 case 'CHANGE':
1790 $query .= ' ' . $components['newField'] . ' ' . $this->compileFieldCfg($components['definition']);
1791 break;
1792 case 'DROP':
1793 case 'DROPKEY':
1794 break;
1795 case 'ADDKEY':
1796 case 'ADDPRIMARYKEY':
1797 case 'ADDUNIQUE':
1798 $query .= ' (' . implode(',', $components['fields']) . ')';
1799 break;
1800 case 'DEFAULTCHARACTERSET':
1801 $query .= $components['charset'];
1802 break;
1803 case 'ENGINE':
1804 $query .= '= ' . $components['engine'];
1805 break;
1806 }
1807
1808 // Return query
1809 return $query;
1810 }
1811
1812 /**
1813 * Compiles a TRUNCATE TABLE statement from components array
1814 *
1815 * @param array Array of SQL query components
1816 * @return string SQL TRUNCATE TABLE query
1817 * @see parseTRUNCATETABLE()
1818 */
1819 protected function compileTRUNCATETABLE(array $components) {
1820
1821 // Make query:
1822 $query = 'TRUNCATE TABLE ' . $components['TABLE'];
1823
1824 // Return query
1825 return $query;
1826 }
1827
1828
1829 /**************************************
1830 *
1831 * Compiling queries, helper functions for parts of queries
1832 *
1833 **************************************/
1834
1835 /**
1836 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
1837 * Can also compile field lists for ORDER BY and GROUP BY.
1838 *
1839 * @param array Array of select fields, (made with ->parseFieldList())
1840 * @param boolean Whether comments should be compiled
1841 * @return string Select field string
1842 * @see parseFieldList()
1843 */
1844 public function compileFieldList($selectFields, $compileComments = TRUE) {
1845
1846 // Prepare buffer variable:
1847 $fields = '';
1848
1849 // Traverse the selectFields if any:
1850 if (is_array($selectFields)) {
1851 $outputParts = array();
1852 foreach ($selectFields as $k => $v) {
1853
1854 // Detecting type:
1855 switch ($v['type']) {
1856 case 'function':
1857 $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';
1858 break;
1859 case 'flow-control':
1860 if ($v['flow-control']['type'] === 'CASE') {
1861 $outputParts[$k] = $this->compileCaseStatement($v['flow-control']);
1862 }
1863 break;
1864 case 'field':
1865 $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];
1866 break;
1867 }
1868
1869 // Alias:
1870 if ($v['as']) {
1871 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
1872 }
1873
1874 // Specifically for ORDER BY and GROUP BY field lists:
1875 if ($v['sortDir']) {
1876 $outputParts[$k] .= ' ' . $v['sortDir'];
1877 }
1878 }
1879 if ($compileComments && $selectFields[0]['comments']) {
1880 $fields = $selectFields[0]['comments'] . ' ';
1881 }
1882 $fields .= implode(', ', $outputParts);
1883 }
1884
1885 return $fields;
1886 }
1887
1888 /**
1889 * Compiles a CASE ... WHEN flow-control construct based on input array (made with ->parseCaseStatement())
1890 *
1891 * @param array Array of case components, (made with ->parseCaseStatement())
1892 * @return string case when string
1893 * @see parseCaseStatement()
1894 */
1895 protected function compileCaseStatement(array $components) {
1896 $statement = 'CASE';
1897 if (isset($components['case_field'])) {
1898 $statement .= ' ' . $components['case_field'];
1899 } elseif (isset($components['case_value'])) {
1900 $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];
1901 }
1902 foreach ($components['when'] as $when) {
1903 $statement .= ' WHEN ';
1904 $statement .= $this->compileWhereClause($when['when_value']);
1905 $statement .= ' THEN ';
1906 $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];
1907 }
1908 if (isset($components['else'])) {
1909 $statement .= ' ELSE ';
1910 $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];
1911 }
1912 $statement .= ' END';
1913 return $statement;
1914 }
1915
1916 /**
1917 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
1918 *
1919 * @param array Array of table names, (made with ->parseFromTables())
1920 * @return string Table name string
1921 * @see parseFromTables()
1922 */
1923 public function compileFromTables($tablesArray) {
1924
1925 // Prepare buffer variable:
1926 $outputParts = array();
1927
1928 // Traverse the table names:
1929 if (is_array($tablesArray)) {
1930 foreach ($tablesArray as $k => $v) {
1931
1932 // Set table name:
1933 $outputParts[$k] = $v['table'];
1934
1935 // Add alias AS if there:
1936 if ($v['as']) {
1937 $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];
1938 }
1939
1940 if (is_array($v['JOIN'])) {
1941 foreach ($v['JOIN'] as $join) {
1942 $outputParts[$k] .= ' ' . $join['type'] . ' ' . $join['withTable'];
1943 // Add alias AS if there:
1944 if (isset($join['as']) && $join['as']) {
1945 $outputParts[$k] .= ' ' . $join['as_keyword'] . ' ' . $join['as'];
1946 }
1947 $outputParts[$k] .= ' ON ';
1948 foreach ($join['ON'] as $condition) {
1949 if ($condition['operator'] !== '') {
1950 $outputParts[$k] .= ' ' . $condition['operator'] . ' ';
1951 }
1952 $outputParts[$k] .= ($condition['left']['table']) ? $condition['left']['table'] . '.' : '';
1953 $outputParts[$k] .= $condition['left']['field'];
1954 $outputParts[$k] .= $condition['comparator'];
1955 $outputParts[$k] .= ($condition['right']['table']) ? $condition['right']['table'] . '.' : '';
1956 $outputParts[$k] .= $condition['right']['field'];
1957 }
1958 }
1959 }
1960 }
1961 }
1962
1963 // Return imploded buffer:
1964 return implode(', ', $outputParts);
1965 }
1966
1967 /**
1968 * Implodes an array of WHERE clause configuration into a WHERE clause.
1969 *
1970 * @param array WHERE clause configuration
1971 * @return string WHERE clause as string.
1972 * @see explodeWhereClause()
1973 */
1974 public function compileWhereClause($clauseArray) {
1975
1976 // Prepare buffer variable:
1977 $output = '';
1978
1979 // Traverse clause array:
1980 if (is_array($clauseArray)) {
1981 foreach ($clauseArray as $k => $v) {
1982
1983 // Set operator:
1984 $output .= $v['operator'] ? ' ' . $v['operator'] : '';
1985
1986 // Look for sublevel:
1987 if (is_array($v['sub'])) {
1988 $output .= ' (' . trim($this->compileWhereClause($v['sub'])) . ')';
1989 } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {
1990 $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';
1991 } else {
1992
1993 if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {
1994 $output .= ' ' . trim($v['modifier']) . ' LOCATE(';
1995 $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];
1996 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
1997 $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';
1998 $output .= ')';
1999 } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {
2000 $output .= ' ' . trim($v['modifier']) . ' IFNULL(';
2001 $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
2002 $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];
2003 $output .= ')';
2004 } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {
2005 $output .= ' ' . trim($v['modifier']) . ' FIND_IN_SET(';
2006 $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];
2007 $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];
2008 $output .= ')';
2009 } else {
2010
2011 // Set field/table with modifying prefix if any:
2012 $output .= ' ' . trim($v['modifier'] . ' ' . ($v['table'] ? $v['table'] . '.' : '') . $v['field']);
2013
2014 // Set calculation, if any:
2015 if ($v['calc']) {
2016 $output .= $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];
2017 }
2018 }
2019
2020 // Set comparator:
2021 if ($v['comparator']) {
2022 $output .= ' ' . $v['comparator'];
2023
2024 // Detecting value type; list or plain:
2025 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ', TAB, CR, LF), '', $v['comparator'])))) {
2026 if (isset($v['subquery'])) {
2027 $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';
2028 } else {
2029 $valueBuffer = array();
2030 foreach ($v['value'] as $realValue) {
2031 $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];
2032 }
2033 $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';
2034 }
2035 } else {
2036 if (t3lib_div::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {
2037 $lbound = $v['values'][0];
2038 $ubound = $v['values'][1];
2039 $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];
2040 $output .= ' AND ';
2041 $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];
2042 } else {
2043 if (isset($v['value']['operator'])) {
2044 $values = array();
2045 foreach ($v['value']['args'] as $fieldDef) {
2046 $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];
2047 }
2048 $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';
2049 } else {
2050 $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];
2051 }
2052 }
2053 }
2054 }
2055 }
2056 }
2057 }
2058
2059 // Return output buffer:
2060 return $output;
2061 }
2062
2063 /**
2064 * Compile field definition
2065 *
2066 * @param array Field definition parts
2067 * @return string Field definition string
2068 */
2069 public function compileFieldCfg($fieldCfg) {
2070
2071 // Set type:
2072 $cfg = $fieldCfg['fieldType'];
2073
2074 // Add value, if any:
2075 if (strlen($fieldCfg['value'])) {
2076 $cfg .= '(' . $fieldCfg['value'] . ')';
2077 }
2078
2079 // Add additional features:
2080 if (is_array($fieldCfg['featureIndex'])) {
2081 foreach ($fieldCfg['featureIndex'] as $featureDef) {
2082 $cfg .= ' ' . $featureDef['keyword'];
2083
2084 // Add value if found:
2085 if (is_array($featureDef['value'])) {
2086 $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];
2087 }
2088 }
2089 }
2090
2091 // Return field definition string:
2092 return $cfg;
2093 }
2094
2095
2096 /*************************
2097 *
2098 * Debugging
2099 *
2100 *************************/
2101
2102 /**
2103 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
2104 *
2105 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
2106 * @param string SQL string to verify parsability of
2107 * @return mixed Returns array with string 1 and 2 if error, otherwise FALSE
2108 */
2109 public function debug_parseSQLpart($part, $str) {
2110 $retVal = FALSE;
2111
2112 switch ($part) {
2113 case 'SELECT':
2114 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFieldList($this->parseFieldList($str)));
2115 break;
2116 case 'FROM':
2117 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileFromTables($this->parseFromTables($str)));
2118 break;
2119 case 'WHERE':
2120 $retVal = $this->debug_parseSQLpartCompare($str, $this->compileWhereClause($this->parseWhereClause($str)));
2121 break;
2122 }
2123 return $retVal;
2124 }
2125
2126 /**
2127 * Compare two query strins by stripping away whitespace.
2128 *
2129 * @param string SQL String 1
2130 * @param string SQL string 2
2131 * @param boolean If TRUE, the strings are compared insensitive to case
2132 * @return mixed Returns array with string 1 and 2 if error, otherwise FALSE
2133 */
2134 public function debug_parseSQLpartCompare($str, $newStr, $caseInsensitive = FALSE) {
2135 if ($caseInsensitive) {
2136 $str1 = strtoupper($str);
2137 $str2 = strtoupper($newStr);
2138 } else {
2139 $str1 = $str;
2140 $str2 = $newStr;
2141 }
2142
2143 // Fixing escaped chars:
2144 $search = array('\0', '\n', '\r', '\Z');
2145 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
2146 $str1 = str_replace($search, $replace, $str1);
2147 $str2 = str_replace($search, $replace, $str2);
2148
2149 # Normally, commented out since they are needed only in tricky cases...
2150 # $str1 = stripslashes($str1);
2151 # $str2 = stripslashes($str2);
2152
2153 if (strcmp(str_replace(array(' ', TAB, CR, LF), '', $this->trimSQL($str1)), str_replace(array(' ', TAB, CR, LF), '', $this->trimSQL($str2)))) {
2154 return array(
2155 str_replace(array(' ', TAB, CR, LF), ' ', $str),
2156 str_replace(array(' ', TAB, CR, LF), ' ', $newStr),
2157 );
2158 }
2159 }
2160
2161 /**
2162 * 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
2163 *
2164 * @param string SQL query
2165 * @return string Query if all is well, otherwise exit.
2166 */
2167 public function debug_testSQL($SQLquery) {
2168
2169 // Getting result array:
2170 $parseResult = $this->parseSQL($SQLquery);
2171
2172 // If result array was returned, proceed. Otherwise show error and exit.
2173 if (is_array($parseResult)) {
2174
2175 // Re-compile query:
2176 $newQuery = $this->compileSQL($parseResult);
2177
2178 // TEST the new query:
2179 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
2180
2181 // Return new query if OK, otherwise show error and exit:
2182 if (!is_array($testResult)) {
2183 return $newQuery;
2184 } else {
2185 debug(array('ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult), 'SQL parsing failed:');
2186 exit;
2187 }
2188 } else {
2189 debug(array('query' => $SQLquery, 'ERROR MESSAGE' => $parseResult), 'SQL parsing failed:');
2190 exit;
2191 }
2192 }
2193 }
2194
2195 ?>