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