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