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