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