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