* Added some file icons from Ben and Emile
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_sqlengine.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 * PHP SQL engine
29 *
30 * $Id$
31 *
32 * @author Kasper Skaarhoj <kasper@typo3.com>
33 */
34 /**
35 * [CLASS/FUNCTION INDEX of SCRIPT]
36 *
37 *
38 *
39 * 145: class t3lib_sqlengine
40 * 171: function init($config, &$pObj)
41 * 179: function resetStatusVars()
42 * 192: function processAccordingToConfig(&$value,$fInfo)
43 *
44 * SECTION: SQL queries
45 * 247: function exec_INSERTquery($table,$fields_values)
46 * 314: function exec_UPDATEquery($table,$where,$fields_values)
47 * 373: function exec_DELETEquery($table,$where)
48 * 424: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit)
49 * 467: function sql_query($query)
50 * 478: function sql_error()
51 * 487: function sql_insert_id()
52 * 496: function sql_affected_rows()
53 * 506: function quoteStr($str)
54 *
55 * SECTION: SQL admin functions
56 * 531: function admin_get_tables()
57 * 542: function admin_get_fields($tableName)
58 * 553: function admin_get_keys($tableName)
59 * 564: function admin_query($query)
60 *
61 * SECTION: Data Source I/O
62 * 589: function readDataSource($table)
63 * 601: function saveDataSource($table)
64 *
65 * SECTION: SQL engine functions
66 * 630: function selectFromData($table,$where)
67 * 666: function select_evalSingle($table,$config,&$itemKeys)
68 * 787: function getResultSet($keys, $table, $fieldList)
69 *
70 * SECTION: SQL Parsing, full queries
71 * 829: function parseSQL($parseString)
72 * 889: function parseSELECT($parseString)
73 * 958: function parseUPDATE($parseString)
74 * 1012: function parseINSERT($parseString)
75 * 1072: function parseDELETE($parseString)
76 * 1110: function parseEXPLAIN($parseString)
77 * 1132: function parseCREATETABLE($parseString)
78 * 1204: function parseALTERTABLE($parseString)
79 * 1270: function parseDROPTABLE($parseString)
80 *
81 * SECTION: SQL Parsing, helper functions for parts of queries
82 * 1329: function parseFieldList(&$parseString, $stopRegex='')
83 * 1447: function parseFromTables(&$parseString, $stopRegex='')
84 * 1514: function parseWhereClause(&$parseString, $stopRegex='')
85 * 1622: function parseFieldDef(&$parseString, $stopRegex='')
86 *
87 * SECTION: Parsing: Helper functions
88 * 1683: function nextPart(&$parseString,$regex,$trimAll=FALSE)
89 * 1697: function getValue(&$parseString,$comparator='')
90 * 1752: function getValueInQuotes(&$parseString,$quote)
91 * 1777: function parseStripslashes($str)
92 * 1791: function compileAddslashes($str)
93 * 1805: function parseError($msg,$restQuery)
94 * 1819: function trimSQL($str)
95 *
96 * SECTION: Compiling queries
97 * 1847: function compileSQL($components)
98 * 1885: function compileSELECT($components)
99 * 1916: function compileUPDATE($components)
100 * 1944: function compileINSERT($components)
101 * 1984: function compileDELETE($components)
102 * 2004: function compileCREATETABLE($components)
103 * 2035: function compileALTERTABLE($components)
104 *
105 * SECTION: Compiling queries, helper functions for parts of queries
106 * 2088: function compileFieldList($selectFields)
107 * 2130: function compileFromTables($tablesArray)
108 * 2166: function compileWhereClause($clauseArray)
109 * 2220: function compileFieldCfg($fieldCfg)
110 *
111 * SECTION: Debugging
112 * 2269: function debug_parseSQLpart($part,$str)
113 * 2291: function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE)
114 * 2324: function debug_testSQL($SQLquery)
115 * 2359: function debug_printResultSet($array)
116 *
117 *
118 * 2398: class t3lib_sqlengine_resultobj
119 * 2412: function sql_num_rows()
120 * 2421: function sql_fetch_assoc()
121 * 2432: function sql_fetch_row()
122 * 2450: function sql_data_seek($pointer)
123 * 2463: function sql_field_type()
124 *
125 * TOTAL FUNCTIONS: 61
126 * (This index is automatically created/updated by the extension "extdeveval")
127 *
128 */
129
130
131
132
133
134
135
136
137
138 /**
139 * PHP SQL engine / server
140 * Some parts are experimental for now.
141 *
142 * @author Kasper Skaarhoj <kasper@typo3.com>
143 * @package TYPO3
144 * @subpackage t3lib
145 */
146 class t3lib_sqlengine {
147
148 // array with data records: [table name][num.index] = records
149 var $data = array(); // Data source storage
150
151
152 // Internal, SQL Status vars:
153 var $errorStatus = ''; // Set with error message of last operation
154 var $lastInsertedId = 0; // Set with last inserted unique ID
155 var $lastAffectedRows = 0; // Set with last number of affected rows.
156
157 // Parser:
158 var $parse_error = ''; // Parsing error string
159 var $lastStopKeyWord = ''; // Last stop keyword used.
160
161
162
163
164
165 /**
166 * Dummy function for initializing SQL handler. Create you own in derived classes.
167 *
168 * @param array Configuration array from handler
169 * @param object Parent object
170 * @return void
171 */
172 function init($config, &$pObj) {
173 }
174
175 /**
176 * Reset SQL engine status variables (insert id, affected rows, error status)
177 *
178 * @return void
179 */
180 function resetStatusVars() {
181 $this->errorStatus = '';
182 $this->lastInsertedId = 0;
183 $this->lastAffectedRows = 0;
184 }
185
186 /**
187 * Processing of update/insert values based on field type.
188 *
189 * @param [type] $$value: ...
190 * @param [type] $fInfo: ...
191 * @return [type] ...
192 */
193 function processAccordingToConfig(&$value,$fInfo) {
194 $options = $this->parseFieldDef($fInfo['Type']);
195
196 switch(strtolower($options['fieldType'])) {
197 case 'int':
198 case 'smallint':
199 case 'tinyint':
200 case 'mediumint':
201 $value = intval($value);
202 if ($options['featureIndex']['UNSIGNED']) {
203 $value = t3lib_div::intInRange($value,0);
204 }
205 break;
206 case 'double':
207 $value = (double)$value;
208 break;
209 case 'varchar':
210 case 'char':
211 $value = substr($value,0,trim($options['value']));
212 break;
213 case 'text':
214 case 'blob':
215 $value = substr($value,0,65536);
216 break;
217 case 'tinytext':
218 case 'tinyblob':
219 $value = substr($value,0,256);
220 break;
221 case 'mediumtext':
222 case 'mediumblob':
223 // ??
224 break;
225 }
226 }
227
228
229
230
231
232
233
234 /********************************
235 *
236 * SQL queries
237 * This is the SQL access functions used when this class is instantiated as a SQL handler with DBAL. Override these in derived classes.
238 *
239 ********************************/
240
241 /**
242 * Execute an INSERT query
243 *
244 * @param string Table name
245 * @param array Field values as key=>value pairs.
246 * @return boolean TRUE on success and FALSE on failure (error is set internally)
247 */
248 function exec_INSERTquery($table,$fields_values) {
249
250 // Initialize
251 $this->resetStatusVars();
252
253 // Reading Data Source if not done already.
254 $this->readDataSource($table);
255
256 // If data source is set:
257 if (is_array($this->data[$table])) {
258
259 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
260
261 // Looking for unique keys:
262 $saveArray = array();
263 foreach($fieldInformation as $fInfo) {
264
265 // Field name:
266 $fN = $fInfo['Field'];
267
268 // Set value:
269 $saveArray[$fN] = isset($fields_values[$fN]) ? $fields_values[$fN] : $options['Default'];
270
271 // Process value:
272 $this->processAccordingToConfig($saveArray[$fN], $fInfo);
273
274 // If an auto increment field is found, find the largest current uid:
275 if ($fInfo['Extra'] == 'auto_increment') {
276
277 // Get all UIDs:
278 $uidArray = array();
279 foreach($this->data[$table] as $r) {
280 $uidArray[] = $r[$fN];
281 }
282
283 // If current value is blank or already in array, we create a new:
284 if (!$saveArray[$fN] || in_array(intval($saveArray[$fN]), $uidArray)) {
285 if (count($uidArray)) {
286 $saveArray[$fN] = max($uidArray)+1;
287 } else $saveArray[$fN] = 1;
288 }
289
290 // Update "last inserted id":
291 $this->lastInsertedId = $saveArray[$fN];
292 }
293 }
294 #debug(array($fields_values,$saveArray));
295 // Insert row in table:
296 $this->data[$table][] = $saveArray;
297
298 // Save data source
299 $this->saveDataSource($table);
300
301 return TRUE;
302 } else $this->errorStatus = 'No data loaded.';
303
304 return FALSE;
305 }
306
307 /**
308 * Execute UPDATE query on table
309 *
310 * @param string Table name
311 * @param string WHERE clause
312 * @param array Field values as key=>value pairs.
313 * @return boolean TRUE on success and FALSE on failure (error is set internally)
314 */
315 function exec_UPDATEquery($table,$where,$fields_values) {
316
317 // Initialize:
318 $this->resetStatusVars();
319
320 // Reading Data Source if not done already.
321 $this->readDataSource($table);
322
323 // If anything is there:
324 if (is_array($this->data[$table])) {
325
326 // Parse WHERE clause:
327 $where = $this->parseWhereClause($where);
328
329 if (is_array($where)) {
330
331 // Field information
332 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
333
334 // Traverse fields to update:
335 foreach($fields_values as $fName => $fValue) {
336 $this->processAccordingToConfig($fields_values[$fName],$fieldInformation[$fName]);
337 }
338 #debug($fields_values);
339 // Do query, returns array with keys to the data array of the result:
340 $itemKeys = $this->selectFromData($table,$where);
341
342 // Set "last affected rows":
343 $this->lastAffectedRows = count($itemKeys);
344
345 // Update rows:
346 if ($this->lastAffectedRows) {
347 // Traverse result set here:
348 foreach($itemKeys as $dataArrayKey) {
349
350 // Traverse fields to update:
351 foreach($fields_values as $fName => $fValue) {
352 $this->data[$table][$dataArrayKey][$fName] = $fValue;
353 }
354 }
355
356 // Save data source
357 $this->saveDataSource($table);
358 }
359
360 return TRUE;
361 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
362 } else $this->errorStatus = 'No data loaded.';
363
364 return FALSE;
365 }
366
367 /**
368 * Execute DELETE query
369 *
370 * @param string Table to delete from
371 * @param string WHERE clause
372 * @return boolean TRUE on success and FALSE on failure (error is set internally)
373 */
374 function exec_DELETEquery($table,$where) {
375
376 // Initialize:
377 $this->resetStatusVars();
378
379 // Reading Data Source if not done already.
380 $this->readDataSource($table);
381
382 // If anything is there:
383 if (is_array($this->data[$table])) {
384
385 // Parse WHERE clause:
386 $where = $this->parseWhereClause($where);
387
388 if (is_array($where)) {
389
390 // Do query, returns array with keys to the data array of the result:
391 $itemKeys = $this->selectFromData($table,$where);
392
393 // Set "last affected rows":
394 $this->lastAffectedRows = count($itemKeys);
395
396 // Remove rows:
397 if ($this->lastAffectedRows) {
398 // Traverse result set:
399 foreach($itemKeys as $dataArrayKey) {
400 unset($this->data[$table][$dataArrayKey]);
401 }
402
403 // Saving data source
404 $this->saveDataSource($table);
405 }
406
407 return TRUE;
408 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
409 } else $this->errorStatus = 'No data loaded.';
410
411 return FALSE;
412 }
413
414 /**
415 * Execute SELECT query
416 *
417 * @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
418 * @param string Table(s) from which to select. This is what comes right after "FROM ...". Required value.
419 * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->quoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
420 * @param string Optional GROUP BY field(s), if none, supply blank string.
421 * @param string Optional ORDER BY field(s), if none, supply blank string.
422 * @param string Optional LIMIT value ([begin,]max), if none, supply blank string.
423 * @return object Returns result object, but if errors, returns false
424 */
425 function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit) {
426
427 // Initialize:
428 $this->resetStatusVars();
429
430 // Create result object
431 $sqlObj = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
432 $sqlObj->result = array(); // Empty result as a beginning
433
434 // Get table list:
435 $tableArray = $this->parseFromTables($from_table);
436 $table = $tableArray[0]['table'];
437
438 // Reading Data Source if not done already.
439 $this->readDataSource($table);
440
441 // If anything is there:
442 if (is_array($this->data[$table])) {
443
444 // Parse WHERE clause:
445 $where = $this->parseWhereClause($where_clause);
446 if (is_array($where)) {
447
448 // Do query, returns array with keys to the data array of the result:
449 $itemKeys = $this->selectFromData($table,$where);
450
451 // Finally, read the result rows into this variable:
452 $sqlObj->result = $this->getResultSet($itemKeys,$table,'*');
453 // Reset and return result:
454 reset($sqlObj->result);
455 return $sqlObj;
456 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
457 } else $this->errorStatus = 'No data loaded: '.$this->errorStatus;
458
459 return FALSE;
460 }
461
462 /**
463 * Performs an SQL query on the "database"
464 *
465 * @param string Query to execute
466 * @return object Result object or false if error
467 */
468 function sql_query($query) {
469 $res = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
470 $res->result = array();
471 return $res;
472 }
473
474 /**
475 * Returns most recent error
476 *
477 * @return string Error message, if any
478 */
479 function sql_error() {
480 return $this->errorStatus;
481 }
482
483 /**
484 * Returns most recently create unique ID (of INSERT queries)
485 *
486 * @return integer Last unique id created.
487 */
488 function sql_insert_id() {
489 return $this->lastInsertedId;
490 }
491
492 /**
493 * Returns affected rows (of UPDATE and DELETE queries)
494 *
495 * @return integer Last amount of affected rows.
496 */
497 function sql_affected_rows() {
498 return $this->lastAffectedRows;
499 }
500
501 /**
502 * Quoting strings for insertion in SQL queries
503 *
504 * @param string String
505 * @return string String, with quotes escaped
506 */
507 function quoteStr($str) {
508 return addslashes($str);
509 }
510
511
512
513
514
515
516
517
518
519
520 /**************************************
521 *
522 * SQL admin functions
523 * (For use in the Install Tool and Extension Manager)
524 *
525 **************************************/
526
527 /**
528 * (DUMMY) Returns the list of tables from the database
529 *
530 * @return array Tables in an array (tablename is in both key and value)
531 */
532 function admin_get_tables() {
533 $whichTables = array();
534 return $whichTables;
535 }
536
537 /**
538 * (DUMMY) Returns information about each field in the $table
539 *
540 * @param string Table name
541 * @return array Field information in an associative array with fieldname => field row
542 */
543 function admin_get_fields($tableName) {
544 $output = array();
545 return $output;
546 }
547
548 /**
549 * (DUMMY) Returns information about each index key in the $table
550 *
551 * @param string Table name
552 * @return array Key information in a numeric array
553 */
554 function admin_get_keys($tableName) {
555 $output = array();
556 return $output;
557 }
558
559 /**
560 * (DUMMY) mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
561 *
562 * @param string Query to execute
563 * @return pointer Result pointer
564 */
565 function admin_query($query) {
566 return $this->sql_query($query);
567 }
568
569
570
571
572
573
574
575
576 /********************************
577 *
578 * Data Source I/O
579 *
580 ********************************/
581
582 /**
583 * Dummy function for setting table data. Create your own.
584 * NOTICE: Handler to "table-locking" needs to be made probably!
585 *
586 * @param string Table name
587 * @return void
588 * @todo Table locking tools?
589 */
590 function readDataSource($table) {
591 $this->data[$table] = array();
592 }
593
594 /**
595 * Dummy function for setting table data. Create your own.
596 * NOTICE: Handler to "table-locking" needs to be made probably!
597 *
598 * @param string Table name
599 * @return void
600 * @todo Table locking tools?
601 */
602 function saveDataSource($table) {
603 debug($this->data[$table]);
604 }
605
606
607
608
609
610
611
612
613
614
615
616
617
618 /********************************
619 *
620 * SQL engine functions
621 *
622 ********************************/
623
624 /**
625 * [Describe function...]
626 *
627 * @param [type] $table: ...
628 * @param [type] $where: ...
629 * @return [type] ...
630 */
631 function selectFromData($table,$where) {
632
633 $output = array();
634 if (is_array($this->data[$table])) {
635
636 // All keys:
637 $OR_index = 0;
638
639 foreach($where as $config) {
640
641 if (strtoupper($config['operator'])=='OR') {
642 $OR_index++;
643 }
644
645 if (!isset($itemKeys[$OR_index])) $itemKeys[$OR_index] = array_keys($this->data[$table]);
646
647 $this->select_evalSingle($table,$config,$itemKeys[$OR_index]);
648 }
649
650 foreach($itemKeys as $uidKeys) {
651 $output = array_merge($output, $uidKeys);
652 }
653 $output = array_unique($output);
654 }
655
656 return $output;
657 }
658
659 /**
660 * [Describe function...]
661 *
662 * @param [type] $table: ...
663 * @param [type] $config: ...
664 * @param [type] $itemKeys: ...
665 * @return [type] ...
666 */
667 function select_evalSingle($table,$config,&$itemKeys) {
668 $neg = ereg('^AND[[:space:]]+NOT$',trim($config['operator']));
669
670 if (is_array($config['sub'])) {
671 $subSelKeys = $this->selectFromData($table,$config['sub']);
672 if ($neg) {
673 foreach($itemKeys as $kk => $vv) {
674 if (in_array($vv,$subSelKeys)) {
675 unset($itemKeys[$kk]);
676 }
677 }
678 } else {
679 $itemKeys = array_intersect($itemKeys, $subSelKeys);
680 }
681 } else {
682 $comp = strtoupper(ereg_replace('[[:space:]]','',$config['comparator']));
683 $mod = strtoupper($config['modifier']);
684 switch($comp) {
685 case 'NOTLIKE':
686 case 'LIKE':
687 $like_value = strtolower($config['value'][0]);
688 if (substr($like_value,0,1)=='%') {
689 $wildCard_begin = TRUE;
690 $like_value = substr($like_value,1);
691 }
692 if (substr($like_value,-1)=='%') {
693 $wildCard_end = TRUE;
694 $like_value = substr($like_value,0,-1);
695 }
696 break;
697 case 'NOTIN':
698 case 'IN':
699 $in_valueArray = array();
700 foreach($config['value'] as $vParts) {
701 $in_valueArray[] = (string)$vParts[0];
702 }
703 break;
704 }
705
706 foreach($itemKeys as $kk => $v) {
707 $field_value = $this->data[$table][$v][$config['field']];
708
709 // Calculate it:
710 if ($config['calc']=='&') {
711 $field_value&=intval($config['calc_value']);
712 }
713
714 // Compare it:
715 switch($comp) {
716 case '<=':
717 $bool = $field_value <= $config['value'][0];
718 break;
719 case '>=':
720 $bool = $field_value >= $config['value'][0];
721 break;
722 case '<':
723 $bool = $field_value < $config['value'][0];
724 break;
725 case '>':
726 $bool = $field_value > $config['value'][0];
727 break;
728 case '=':
729 $bool = !strcmp($field_value,$config['value'][0]);
730 break;
731 case '!=':
732 $bool = strcmp($field_value,$config['value'][0]);
733 break;
734 case 'NOTIN':
735 case 'IN':
736 $bool = in_array((string)$field_value, $in_valueArray);
737 if ($comp=='NOTIN') $bool = !$bool;
738 break;
739 case 'NOTLIKE':
740 case 'LIKE':
741 if (!strlen($like_value)) {
742 $bool = TRUE;
743 } elseif ($wildCard_begin && !$wildCard_end) {
744 $bool = !strcmp(substr(strtolower($field_value),-strlen($like_value)),$like_value);
745 } elseif (!$wildCard_begin && $wildCard_end) {
746 $bool = !strcmp(substr(strtolower($field_value),0,strlen($like_value)),$like_value);
747 } elseif ($wildCard_begin && $wildCard_end) {
748 $bool = strstr($field_value,$like_value);
749 } else {
750 $bool = !strcmp(strtolower($field_value),$like_value);
751 }
752 if ($comp=='NOTLIKE') $bool = !$bool;
753 break;
754 default:
755 $bool = $field_value ? TRUE : FALSE;
756 break;
757 }
758
759 // General negation:
760 if ($neg) $bool = !$bool;
761
762 // Modify?
763 switch($mod) {
764 case 'NOT':
765 case '!':
766 $bool = !$bool;
767 break;
768 }
769
770 // Action:
771 if (!$bool) {
772 unset($itemKeys[$kk]);
773 }
774 }
775
776 # echo $this->debug_printResultSet($this->getResultSet($itemKeys,$table,'uid,tstamp'));
777 }
778 }
779
780 /**
781 * [Describe function...]
782 *
783 * @param [type] $keys: ...
784 * @param [type] $table: ...
785 * @param [type] $fields: ...
786 * @return [type] ...
787 */
788 function getResultSet($keys, $table, $fieldList) {
789 $fields = t3lib_div::trimExplode(',',$fieldList);
790
791 $output = array();
792 foreach($keys as $kValue) {
793 if ($fieldList=='*') {
794 $output[$kValue] = $this->data[$table][$kValue];
795 } else {
796 foreach($fields as $fieldName) {
797 $output[$kValue][$fieldName] = $this->data[$table][$kValue][$fieldName];
798 }
799 }
800 }
801
802 return $output;
803 }
804
805
806
807
808
809
810
811
812
813
814
815
816
817 /*************************************
818 *
819 * SQL Parsing, full queries
820 *
821 **************************************/
822
823 /**
824 * Parses any single SQL query
825 *
826 * @param string SQL query
827 * @return array Result array with all the parts in - or error message string
828 * @see compileSQL(), debug_testSQL()
829 */
830 function parseSQL($parseString) {
831
832 // Prepare variables:
833 $parseString = $this->trimSQL($parseString);
834 $this->parse_error = '';
835 $result = array();
836
837 // Finding starting keyword of string:
838 $_parseString = $parseString; // Protecting original string...
839 $keyword = $this->nextPart($_parseString, '^(SELECT|UPDATE|INSERT[[:space:]]+INTO|DELETE[[:space:]]+FROM|EXPLAIN|DROP[[:space:]]+TABLE|CREATE[[:space:]]+TABLE|ALTER[[:space:]]+TABLE)[[:space:]]+');
840 $keyword = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
841
842 switch($keyword) {
843 case 'SELECT':
844 // Parsing SELECT query:
845 $result = $this->parseSELECT($parseString);
846 break;
847 case 'UPDATE':
848 // Parsing UPDATE query:
849 $result = $this->parseUPDATE($parseString);
850 break;
851 case 'INSERTINTO':
852 // Parsing INSERT query:
853 $result = $this->parseINSERT($parseString);
854 break;
855 case 'DELETEFROM':
856 // Parsing DELETE query:
857 $result = $this->parseDELETE($parseString);
858 break;
859 case 'EXPLAIN':
860 // Parsing EXPLAIN SELECT query:
861 $result = $this->parseEXPLAIN($parseString);
862 break;
863 case 'DROPTABLE':
864 // Parsing DROP TABLE query:
865 $result = $this->parseDROPTABLE($parseString);
866 break;
867 case 'ALTERTABLE':
868 // Parsing ALTER TABLE query:
869 $result = $this->parseALTERTABLE($parseString);
870 break;
871 case 'CREATETABLE':
872 // Parsing CREATE TABLE query:
873 $result = $this->parseCREATETABLE($parseString);
874 break;
875 default:
876 return $this->parseError('"'.$keyword.'" is not a keyword',$parseString);
877 break;
878 }
879
880 return $result;
881 }
882
883 /**
884 * Parsing SELECT query
885 *
886 * @param string SQL string with SELECT query to parse
887 * @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
888 * @see compileSELECT()
889 */
890 function parseSELECT($parseString) {
891
892 // Removing SELECT:
893 $parseString = $this->trimSQL($parseString);
894 $parseString = eregi_replace('^SELECT[[:space:]]+','',$parseString);
895
896 // Init output variable:
897 $result = array();
898 $result['type'] = 'SELECT';
899
900 // Looking for STRAIGHT_JOIN keyword:
901 $result['STRAIGHT_JOIN'] = $this->nextPart($parseString, '^(STRAIGHT_JOIN)[[:space:]]+');
902
903 // Select fields:
904 $result['SELECT'] = $this->parseFieldList($parseString, '^(FROM)[[:space:]]+');
905 if ($this->parse_error) { return $this->parse_error; }
906
907 // Continue if string is not ended:
908 if ($parseString) {
909
910 // Get table list:
911 $result['FROM'] = $this->parseFromTables($parseString, '^(WHERE)[[:space:]]+');
912 if ($this->parse_error) { return $this->parse_error; }
913
914 // If there are more than just the tables (a WHERE clause that would be...)
915 if ($parseString) {
916
917 // Get WHERE clause:
918 $result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
919 if ($this->parse_error) { return $this->parse_error; }
920
921 // If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
922 if ($this->lastStopKeyWord) {
923
924 // GROUP BY parsing:
925 if ($this->lastStopKeyWord == 'GROUPBY') {
926 $result['GROUPBY'] = $this->parseFieldList($parseString, '^(ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
927 if ($this->parse_error) { return $this->parse_error; }
928 }
929
930 // ORDER BY parsing:
931 if ($this->lastStopKeyWord == 'ORDERBY') {
932 $result['ORDERBY'] = $this->parseFieldList($parseString, '^(LIMIT)[[:space:]]+');
933 if ($this->parse_error) { return $this->parse_error; }
934 }
935
936 // LIMIT parsing:
937 if ($this->lastStopKeyWord == 'LIMIT') {
938 if (ereg('^([0-9]+|[0-9]+[[:space:]]*,[[:space:]]*[0-9]+)$',trim($parseString))) {
939 $result['LIMIT'] = $parseString;
940 } else {
941 return $this->parseError('No value for limit!',$parseString);
942 }
943 }
944 }
945 }
946 } else return $this->parseError('No table to select from!',$parseString);
947
948 // Return result:
949 return $result;
950 }
951
952 /**
953 * Parsing UPDATE query
954 *
955 * @param string SQL string with UPDATE query to parse
956 * @return mixed Returns array with components of UPDATE query on success, otherwise an error message string.
957 * @see compileUPDATE()
958 */
959 function parseUPDATE($parseString) {
960
961 // Removing UPDATE
962 $parseString = $this->trimSQL($parseString);
963 $parseString = eregi_replace('^UPDATE[[:space:]]+','',$parseString);
964
965 // Init output variable:
966 $result = array();
967 $result['type'] = 'UPDATE';
968
969 // Get table:
970 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
971
972 // Continue if string is not ended:
973 if ($result['TABLE']) {
974 if ($parseString && $this->nextPart($parseString, '^(SET)[[:space:]]+')) {
975
976 $comma = TRUE;
977
978 // Get field/value pairs:
979 while($comma) {
980 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]*=')) {
981 $this->nextPart($parseString,'^(=)'); // Strip of "=" sign.
982 $value = $this->getValue($parseString);
983 $result['FIELDS'][$fieldName] = $value;
984 } else return $this->parseError('No fieldname found',$parseString);
985
986 $comma = $this->nextPart($parseString,'^(,)');
987 }
988
989 // WHERE
990 if ($this->nextPart($parseString,'^(WHERE)')) {
991 $result['WHERE'] = $this->parseWhereClause($parseString);
992 if ($this->parse_error) { return $this->parse_error; }
993 }
994 } else return $this->parseError('Query missing SET...',$parseString);
995 } else return $this->parseError('No table found!',$parseString);
996
997 // Should be no more content now:
998 if ($parseString) {
999 return $this->parseError('Still content in clause after parsing!',$parseString);
1000 }
1001
1002 // Return result:
1003 return $result;
1004 }
1005
1006 /**
1007 * Parsing INSERT query
1008 *
1009 * @param string SQL string with INSERT query to parse
1010 * @return mixed Returns array with components of INSERT query on success, otherwise an error message string.
1011 * @see compileINSERT()
1012 */
1013 function parseINSERT($parseString) {
1014
1015 // Removing INSERT
1016 $parseString = $this->trimSQL($parseString);
1017 $parseString = eregi_replace('^INSERT[[:space:]]+INTO[[:space:]]+','',$parseString);
1018
1019 // Init output variable:
1020 $result = array();
1021 $result['type'] = 'INSERT';
1022
1023 // Get table:
1024 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()');
1025
1026 if ($result['TABLE']) {
1027
1028 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // In this case there are no field names mentioned in the SQL!
1029 // Get values/fieldnames (depending...)
1030 $result['VALUES_ONLY'] = $this->getValue($parseString,'IN');
1031 if ($this->parse_error) { return $this->parse_error; }
1032 } else { // There are apparently fieldnames listed:
1033 $fieldNames = $this->getValue($parseString,'_LIST');
1034 if ($this->parse_error) { return $this->parse_error; }
1035
1036 if ($this->nextPart($parseString,'^(VALUES)[[:space:]]+')) { // "VALUES" keyword binds the fieldnames to values:
1037
1038 $values = $this->getValue($parseString,'IN'); // Using the "getValue" function to get the field list...
1039 if ($this->parse_error) { return $this->parse_error; }
1040
1041 foreach($fieldNames as $k => $fN) {
1042 if (ereg('^[[:alnum:]_]+$',$fN)) {
1043 if (isset($values[$k])) {
1044 if (!isset($result['FIELDS'][$fN])) {
1045 $result['FIELDS'][$fN] = $values[$k];
1046 } else return $this->parseError('Fieldname ("'.$fN.'") already found in list!',$parseString);
1047 } else return $this->parseError('No value set!',$parseString);
1048 } else return $this->parseError('Invalid fieldname ("'.$fN.'")',$parseString);
1049 }
1050 if (isset($values[$k+1])) {
1051 return $this->parseError('Too many values in list!',$parseString);
1052 }
1053 } else return $this->parseError('VALUES keyword expected',$parseString);
1054 }
1055 } else return $this->parseError('No table found!',$parseString);
1056
1057 // Should be no more content now:
1058 if ($parseString) {
1059 return $this->parseError('Still content after parsing!',$parseString);
1060 }
1061
1062 // Return result
1063 return $result;
1064 }
1065
1066 /**
1067 * Parsing DELETE query
1068 *
1069 * @param string SQL string with DELETE query to parse
1070 * @return mixed Returns array with components of DELETE query on success, otherwise an error message string.
1071 * @see compileDELETE()
1072 */
1073 function parseDELETE($parseString) {
1074
1075 // Removing DELETE
1076 $parseString = $this->trimSQL($parseString);
1077 $parseString = eregi_replace('^DELETE[[:space:]]+FROM[[:space:]]+','',$parseString);
1078
1079 // Init output variable:
1080 $result = array();
1081 $result['type'] = 'DELETE';
1082
1083 // Get table:
1084 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
1085
1086 if ($result['TABLE']) {
1087
1088 // WHERE
1089 if ($this->nextPart($parseString,'^(WHERE)')) {
1090 $result['WHERE'] = $this->parseWhereClause($parseString);
1091 if ($this->parse_error) { return $this->parse_error; }
1092 }
1093 } else return $this->parseError('No table found!',$parseString);
1094
1095 // Should be no more content now:
1096 if ($parseString) {
1097 return $this->parseError('Still content in clause after parsing!',$parseString);
1098 }
1099
1100 // Return result:
1101 return $result;
1102 }
1103
1104 /**
1105 * Parsing EXPLAIN query
1106 *
1107 * @param string SQL string with EXPLAIN query to parse
1108 * @return mixed Returns array with components of EXPLAIN query on success, otherwise an error message string.
1109 * @see parseSELECT()
1110 */
1111 function parseEXPLAIN($parseString) {
1112
1113 // Removing EXPLAIN
1114 $parseString = $this->trimSQL($parseString);
1115 $parseString = eregi_replace('^EXPLAIN[[:space:]]+','',$parseString);
1116
1117 // Init output variable:
1118 $result = $this->parseSELECT($parseString);
1119 if (is_array($result)) {
1120 $result['type'] = 'EXPLAIN';
1121 }
1122
1123 return $result;
1124 }
1125
1126 /**
1127 * Parsing CREATE TABLE query
1128 *
1129 * @param string SQL string starting with CREATE TABLE
1130 * @return mixed Returns array with components of CREATE TABLE query on success, otherwise an error message string.
1131 * @see compileCREATETABLE()
1132 */
1133 function parseCREATETABLE($parseString) {
1134
1135 // Removing CREATE TABLE
1136 $parseString = $this->trimSQL($parseString);
1137 $parseString = eregi_replace('^CREATE[[:space:]]+TABLE[[:space:]]+','',$parseString);
1138
1139 // Init output variable:
1140 $result = array();
1141 $result['type'] = 'CREATETABLE';
1142
1143 // Get table:
1144 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]*\(',TRUE);
1145
1146 if ($result['TABLE']) {
1147
1148 // While the parseString is not yet empty:
1149 while(strlen($parseString)>0) {
1150 if ($key = $this->nextPart($parseString, '^(KEY|PRIMARY KEY)([[:space:]]+|\()')) { // Getting key
1151 $key = strtoupper(ereg_replace('[[:space:]]','',$key));
1152
1153 switch($key) {
1154 case 'PRIMARYKEY':
1155 $result['KEYS'][$key] = $this->getValue($parseString,'_LIST');
1156 if ($this->parse_error) { return $this->parse_error; }
1157 break;
1158 case 'KEY':
1159 if ($keyName = $this->nextPart($parseString, '^([[:alnum:]_]+)([[:space:]]+|\()')) {
1160 $result['KEYS'][$keyName] = $this->getValue($parseString,'_LIST');
1161 if ($this->parse_error) { return $this->parse_error; }
1162 } else return $this->parseError('No keyname found',$parseString);
1163 break;
1164 }
1165 } elseif ($fieldName = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) { // Getting field:
1166 $result['FIELDS'][$fieldName]['definition'] = $this->parseFieldDef($parseString);
1167 if ($this->parse_error) { return $this->parse_error; }
1168 }
1169
1170 // Finding delimiter:
1171 $delim = $this->nextPart($parseString, '^(,|\))');
1172 if (!$delim) {
1173 return $this->parseError('No delimiter found',$parseString);
1174 } elseif ($delim==')') {
1175 break;
1176 }
1177 }
1178
1179 // Finding what is after the table definition - table type in MySQL
1180 if ($delim==')') {
1181 if ($this->nextPart($parseString, '^(TYPE[[:space:]]*=)')) {
1182 $result['tableType'] = $parseString;
1183 $parseString = '';
1184 }
1185 } else return $this->parseError('No fieldname found!',$parseString);
1186
1187 // Getting table type
1188 } else return $this->parseError('No table found!',$parseString);
1189
1190 // Should be no more content now:
1191 if ($parseString) {
1192 return $this->parseError('Still content in clause after parsing!',$parseString);
1193 }
1194
1195 return $result;
1196 }
1197
1198 /**
1199 * Parsing ALTER TABLE query
1200 *
1201 * @param string SQL string starting with ALTER TABLE
1202 * @return mixed Returns array with components of ALTER TABLE query on success, otherwise an error message string.
1203 * @see compileALTERTABLE()
1204 */
1205 function parseALTERTABLE($parseString) {
1206
1207 // Removing ALTER TABLE
1208 $parseString = $this->trimSQL($parseString);
1209 $parseString = eregi_replace('^ALTER[[:space:]]+TABLE[[:space:]]+','',$parseString);
1210
1211 // Init output variable:
1212 $result = array();
1213 $result['type'] = 'ALTERTABLE';
1214
1215 // Get table:
1216 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
1217
1218 if ($result['TABLE']) {
1219 if ($result['action'] = $this->nextPart($parseString, '^(CHANGE|DROP[[:space:]]+KEY|ADD[[:space:]]+KEY|ADD[[:space:]]+PRIMARY[[:space:]]+KEY|DROP|ADD|RENAME)([[:space:]]+|\()')) {
1220 $actionKey = strtoupper(ereg_replace('[[:space:]]','',$result['action']));
1221
1222 // Getting field:
1223 if ($actionKey=='ADDPRIMARYKEY' || $fieldKey = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
1224
1225 switch($actionKey) {
1226 case 'ADD':
1227 $result['FIELD'] = $fieldKey;
1228 $result['definition'] = $this->parseFieldDef($parseString);
1229 if ($this->parse_error) { return $this->parse_error; }
1230 break;
1231 case 'DROP':
1232 case 'RENAME':
1233 $result['FIELD'] = $fieldKey;
1234 break;
1235 case 'CHANGE':
1236 $result['FIELD'] = $fieldKey;
1237 if ($result['newField'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+')) {
1238 $result['definition'] = $this->parseFieldDef($parseString);
1239 if ($this->parse_error) { return $this->parse_error; }
1240 } else return $this->parseError('No NEW field name found',$parseString);
1241 break;
1242
1243 case 'ADDKEY':
1244 case 'ADDPRIMARYKEY':
1245 $result['KEY'] = $fieldKey;
1246 $result['fields'] = $this->getValue($parseString,'_LIST');
1247 if ($this->parse_error) { return $this->parse_error; }
1248 break;
1249 case 'DROPKEY':
1250 $result['KEY'] = $fieldKey;
1251 break;
1252 }
1253 } else return $this->parseError('No field name found',$parseString);
1254 } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
1255 } else return $this->parseError('No table found!',$parseString);
1256
1257 // Should be no more content now:
1258 if ($parseString) {
1259 return $this->parseError('Still content in clause after parsing!',$parseString);
1260 }
1261
1262 return $result;
1263 }
1264
1265 /**
1266 * Parsing DROP TABLE query
1267 *
1268 * @param string SQL string starting with DROP TABLE
1269 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
1270 */
1271 function parseDROPTABLE($parseString) {
1272
1273 // Removing DROP TABLE
1274 $parseString = $this->trimSQL($parseString);
1275 $parseString = eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
1276
1277 // Init output variable:
1278 $result = array();
1279 $result['type'] = 'DROPTABLE';
1280
1281 // IF EXISTS
1282 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
1283
1284 // Get table:
1285 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
1286
1287 if ($result['TABLE']) {
1288
1289 // Should be no more content now:
1290 if ($parseString) {
1291 return $this->parseError('Still content in clause after parsing!',$parseString);
1292 }
1293
1294 return $result;
1295 } else return $this->parseError('No table found!',$parseString);
1296 }
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314 /**************************************
1315 *
1316 * SQL Parsing, helper functions for parts of queries
1317 *
1318 **************************************/
1319
1320 /**
1321 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
1322 * The output from this function can be compiled back into a field list with ->compileFieldList()
1323 * 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!
1324 *
1325 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
1326 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
1327 * @return array If successful parsing, returns an array, otherwise an error string.
1328 * @see compileFieldList()
1329 */
1330 function parseFieldList(&$parseString, $stopRegex='') {
1331
1332 // Prepare variables:
1333 $parseString = $this->trimSQL($parseString);
1334 $this->lastStopKeyWord = '';
1335 $this->parse_error = '';
1336
1337
1338 $stack = array(); // Contains the parsed content
1339 $pnt = 0; // Pointer to positions in $stack
1340 $level = 0; // Indicates the parenthesis level we are at.
1341 $loopExit = 0; // Recursivity brake.
1342
1343 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1344 while (strlen($parseString)) {
1345
1346 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
1347 if ($level>0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
1348
1349 // Accumulate function content until next () parenthesis:
1350 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
1351 $stack[$pnt]['func_content.'][] = array(
1352 'level' => $level,
1353 'func_content' => substr($funcContent,0,-1)
1354 );
1355 $stack[$pnt]['func_content'].= $funcContent;
1356
1357 // Detecting ( or )
1358 switch(substr($stack[$pnt]['func_content'],-1)) {
1359 case '(':
1360 $level++;
1361 break;
1362 case ')':
1363 $level--;
1364 if (!$level) { // If this was the last parenthesis:
1365 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
1366 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
1367 }
1368 break;
1369 }
1370 } else { // Outside parenthesis, looking for next field:
1371
1372 // Looking for a known function (only known functions supported)
1373 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
1374 if ($func) {
1375 $parseString = trim(substr($parseString,1)); // Strip of "("
1376 $stack[$pnt]['type'] = 'function';
1377 $stack[$pnt]['function'] = $func;
1378 $level++; // increse parenthesis level counter.
1379 } else {
1380 // Otherwise, look for regular fieldname:
1381 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)')) {
1382 $stack[$pnt]['type'] = 'field';
1383
1384 // Explode fieldname into field and table:
1385 $tableField = explode('.',$fieldName,2);
1386 if (count($tableField)==2) {
1387 $stack[$pnt]['table'] = $tableField[0];
1388 $stack[$pnt]['field'] = $tableField[1];
1389 } else {
1390 $stack[$pnt]['table'] = '';
1391 $stack[$pnt]['field'] = $tableField[0];
1392 }
1393 } else {
1394 return $this->parseError('No field name found as expected',$parseString);
1395 }
1396 }
1397 }
1398
1399 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
1400 if (!$level) {
1401
1402 // Looking for "AS" alias:
1403 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
1404 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
1405 $stack[$pnt]['as_keyword'] = $as;
1406 }
1407
1408 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
1409 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
1410 $stack[$pnt]['sortDir'] = $sDir;
1411 }
1412
1413 // Looking for stop-keywords:
1414 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1415 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1416 return $stack;
1417 }
1418
1419 // Looking for comma (since the stop-keyword did not trigger a return...)
1420 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
1421 return $this->parseError('No comma found as expected',$parseString);
1422 }
1423
1424 // Increasing pointer:
1425 $pnt++;
1426 }
1427
1428 // Check recursivity brake:
1429 $loopExit++;
1430 if ($loopExit>500) {
1431 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1432 }
1433 }
1434
1435 // Return result array:
1436 return $stack;
1437 }
1438
1439 /**
1440 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
1441 * The success of this parsing determines if that part of the query is supported by TYPO3.
1442 *
1443 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
1444 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
1445 * @return array If successful parsing, returns an array, otherwise an error string.
1446 * @see compileFromTables()
1447 */
1448 function parseFromTables(&$parseString, $stopRegex='') {
1449
1450 // Prepare variables:
1451 $parseString = $this->trimSQL($parseString);
1452 $this->lastStopKeyWord = '';
1453 $this->parse_error = '';
1454
1455 $stack = array(); // Contains the parsed content
1456 $pnt = 0; // Pointer to positions in $stack
1457 $loopExit = 0; // Recursivity brake.
1458
1459 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1460 while (strlen($parseString)) {
1461
1462 // Looking for the table:
1463 if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)')) {
1464 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
1465 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
1466 $stack[$pnt]['as_keyword'] = $as;
1467 }
1468 } else return $this->parseError('No table name found as expected!',$parseString);
1469
1470 // Looking for JOIN
1471 if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+')) {
1472 $stack[$pnt]['JOIN']['type'] = $join;
1473 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1)) {
1474 $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
1475 $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
1476 if ($field1 && $field2) {
1477 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
1478 } else return $this->parseError('No join fields found!',$parseString);
1479 } else return $this->parseError('No join table found!',$parseString);
1480 }
1481
1482 // Looking for stop-keywords:
1483 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1484 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1485 return $stack;
1486 }
1487
1488 // Looking for comma:
1489 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
1490 return $this->parseError('No comma found as expected',$parseString);
1491 }
1492
1493 // Increasing pointer:
1494 $pnt++;
1495
1496 // Check recursivity brake:
1497 $loopExit++;
1498 if ($loopExit>500) {
1499 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1500 }
1501 }
1502
1503 // Return result array:
1504 return $stack;
1505 }
1506
1507 /**
1508 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1509 * The success of this parsing determines if that part of the query is supported by TYPO3.
1510 *
1511 * @param string WHERE clause to parse. NOTICE: passed by reference!
1512 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1513 * @return mixed If successful parsing, returns an array, otherwise an error string.
1514 */
1515 function parseWhereClause(&$parseString, $stopRegex='') {
1516
1517 // Prepare variables:
1518 $parseString = $this->trimSQL($parseString);
1519 $this->lastStopKeyWord = '';
1520 $this->parse_error = '';
1521
1522 $stack = array(0 => array()); // Contains the parsed content
1523 $pnt = array(0 => 0); // Pointer to positions in $stack
1524 $level = 0; // Determines parenthesis level
1525 $loopExit = 0; // Recursivity brake.
1526
1527 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1528 while (strlen($parseString)) {
1529
1530 // Look for next parenthesis level:
1531 $newLevel = $this->nextPart($parseString,'^([(])');
1532 if ($newLevel=='(') { // If new level is started, manage stack/pointers:
1533 $level++; // Increase level
1534 $pnt[$level] = 0; // Reset pointer for this level
1535 $stack[$level] = array(); // Reset stack for this level
1536 } else { // If no new level is started, just parse the current level:
1537
1538 // Find "modifyer", eg. "NOT or !"
1539 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
1540
1541 // Fieldname:
1542 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)')) {
1543
1544 // Parse field name into field and table:
1545 $tableField = explode('.',$fieldName,2);
1546 if (count($tableField)==2) {
1547 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
1548 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
1549 } else {
1550 $stack[$level][$pnt[$level]]['table'] = '';
1551 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
1552 }
1553 } else {
1554 return $this->parseError('No field name found as expected',$parseString);
1555 }
1556
1557 // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
1558 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
1559 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
1560 // Finding value for calculation:
1561 $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
1562 }
1563
1564 // Find "comparator":
1565 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS)');
1566 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
1567 // Finding value for comparator:
1568 $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
1569 if ($this->parse_error) { return $this->parse_error; }
1570 }
1571
1572 // Finished, increase pointer:
1573 $pnt[$level]++;
1574
1575 // Checking if the current level is ended, in that case do stack management:
1576 while ($this->nextPart($parseString,'^([)])')) {
1577 $level--; // Decrease level:
1578 $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1]; // Copy stack
1579 $pnt[$level]++; // Increase pointer of the new level
1580
1581 // Make recursivity check:
1582 $loopExit++;
1583 if ($loopExit>500) {
1584 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
1585 }
1586 }
1587
1588 // Detecting the operator for the next level; support for AND, OR and &&):
1589 $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
1590 if ($op) {
1591 $stack[$level][$pnt[$level]]['operator'] = $op;
1592 } elseif (strlen($parseString)) {
1593
1594 // Looking for stop-keywords:
1595 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1596 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1597 return $stack[0];
1598 } else {
1599 return $this->parseError('No operator, but parsing not finished.',$parseString);
1600 }
1601 }
1602 }
1603
1604 // Make recursivity check:
1605 $loopExit++;
1606 if ($loopExit>500) {
1607 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1608 }
1609 }
1610
1611 // Return the stacks lowest level:
1612 return $stack[0];
1613 }
1614
1615 /**
1616 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1617 * The success of this parsing determines if that part of the query is supported by TYPO3.
1618 *
1619 * @param string WHERE clause to parse. NOTICE: passed by reference!
1620 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1621 * @return mixed If successful parsing, returns an array, otherwise an error string.
1622 */
1623 function parseFieldDef(&$parseString, $stopRegex='') {
1624 // Prepare variables:
1625 $parseString = $this->trimSQL($parseString);
1626 $this->lastStopKeyWord = '';
1627 $this->parse_error = '';
1628
1629 $result = array();
1630
1631 // Field type:
1632 if ($result['fieldType'] = $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|double|varchar|char|text|tinytext|mediumtext|blob|tinyblob|mediumblob)([[:space:]]+|\()')) {
1633
1634 // Looking for value:
1635 if (substr($parseString,0,1)=='(') {
1636 $parseString = substr($parseString,1);
1637 if ($result['value'] = $this->nextPart($parseString,'^([^)]*)')) {
1638 $parseString = ltrim(substr($parseString,1));
1639 } else return $this->parseError('No end-parenthesis for value found!',$parseString);
1640 }
1641
1642 // Looking for keywords
1643 while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
1644 $keywordCmp = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
1645
1646 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1647
1648 switch($keywordCmp) {
1649 case 'DEFAULT':
1650 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1651 break;
1652 }
1653 }
1654 } else return $this->parseError('Field type unknown!',$parseString);
1655
1656 return $result;
1657 }
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669 /************************************
1670 *
1671 * Parsing: Helper functions
1672 *
1673 ************************************/
1674
1675 /**
1676 * Strips of a part of the parseString and returns the matching part.
1677 * Helper function for the parsing methods.
1678 *
1679 * @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.
1680 * @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())
1681 * @param boolean If set the full match of the regex is stripped of the beginning of the string!
1682 * @return string The value of the first parenthesis level of the REGEX.
1683 */
1684 function nextPart(&$parseString,$regex,$trimAll=FALSE) {
1685 if (eregi($regex,$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1686 $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
1687 return $reg[1];
1688 }
1689 }
1690
1691 /**
1692 * Finds value in beginning of $parseString, returns result and strips it of parseString
1693 *
1694 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
1695 * @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)
1696 * @return string The value (string/integer). Otherwise an array with error message in first key (0)
1697 */
1698 function getValue(&$parseString,$comparator='') {
1699 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator)))) { // List of values:
1700 if ($this->nextPart($parseString,'^([(])')) {
1701 $listValues = array();
1702 $comma=',';
1703
1704 while($comma==',') {
1705 $listValues[] = $this->getValue($parseString);
1706 $comma = $this->nextPart($parseString,'^([,])');
1707 }
1708
1709 $out = $this->nextPart($parseString,'^([)])');
1710 if ($out) {
1711 if ($comparator=='_LIST') {
1712 $kVals = array();
1713 foreach ($listValues as $vArr) {
1714 $kVals[] = $vArr[0];
1715 }
1716 return $kVals;
1717 } else {
1718 return $listValues;
1719 }
1720 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1721 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1722
1723 } else { // Just plain string value, in quotes or not:
1724
1725 // Quote?
1726 $firstChar = substr($parseString,0,1);
1727
1728 switch($firstChar) {
1729 case '"':
1730 return array($this->getValueInQuotes($parseString,'"'),'"');
1731 break;
1732 case "'":
1733 return array($this->getValueInQuotes($parseString,"'"),"'");
1734 break;
1735 default:
1736 if (eregi('^([[:alnum:]._-]+)',$parseString, $reg)) {
1737 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1738 return array($reg[1]);
1739 }
1740 break;
1741 }
1742 }
1743 }
1744
1745 /**
1746 * Get value in quotes from $parseString.
1747 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1748 *
1749 * @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.
1750 * @param string The quote used; input either " or '
1751 * @return string The value, passed through stripslashes() !
1752 */
1753 function getValueInQuotes(&$parseString,$quote) {
1754
1755 $parts = explode($quote,substr($parseString,1));
1756 $buffer = '';
1757 foreach($parts as $k => $v) {
1758 $buffer.=$v;
1759
1760 unset($reg);
1761 ereg('[\]*$',$v,$reg);
1762 if (strlen($reg[0])%2) {
1763 $buffer.=$quote;
1764 } else {
1765 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
1766 return $this->parseStripslashes($buffer);
1767 }
1768 }
1769 }
1770
1771 /**
1772 * Strip slashes function used for parsing
1773 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1774 *
1775 * @param string Input string
1776 * @return string Output string
1777 */
1778 function parseStripslashes($str) {
1779 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1780 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1781
1782 return str_replace($search, $replace, $str);
1783 }
1784
1785 /**
1786 * Add slashes function used for compiling queries
1787 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1788 *
1789 * @param string Input string
1790 * @return string Output string
1791 */
1792 function compileAddslashes($str) {
1793 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1794 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1795
1796 return str_replace($search, $replace, $str);
1797 }
1798
1799 /**
1800 * Setting the internal error message value, $this->parse_error and returns that value.
1801 *
1802 * @param string Input error message
1803 * @param string Remaining query to parse.
1804 * @return string Error message.
1805 */
1806 function parseError($msg,$restQuery) {
1807 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1808 return $this->parse_error;
1809 }
1810
1811 /**
1812 * Trimming SQL as preparation for parsing.
1813 * ";" in the end is stripped of.
1814 * White space is trimmed away around the value
1815 * A single space-char is added in the end
1816 *
1817 * @param string Input string
1818 * @return string Output string
1819 */
1820 function trimSQL($str) {
1821 return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
1822 }
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835 /*************************
1836 *
1837 * Compiling queries
1838 *
1839 *************************/
1840
1841 /**
1842 * Compiles an SQL query from components
1843 *
1844 * @param array Array of SQL query components
1845 * @return string SQL query
1846 * @see parseSQL()
1847 */
1848 function compileSQL($components) {
1849 switch($components['type']) {
1850 case 'SELECT':
1851 $query = $this->compileSELECT($components);
1852 break;
1853 case 'UPDATE':
1854 $query = $this->compileUPDATE($components);
1855 break;
1856 case 'INSERT':
1857 $query = $this->compileINSERT($components);
1858 break;
1859 case 'DELETE':
1860 $query = $this->compileDELETE($components);
1861 break;
1862 case 'EXPLAIN':
1863 $query = 'EXPLAIN '.$this->compileSELECT($components);
1864 break;
1865 case 'DROPTABLE':
1866 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
1867 break;
1868 case 'CREATETABLE':
1869 $query = $this->compileCREATETABLE($components);
1870 break;
1871 case 'ALTERTABLE':
1872 $query = $this->compileALTERTABLE($components);
1873 break;
1874 }
1875
1876 return $query;
1877 }
1878
1879 /**
1880 * Compiles a SELECT statement from components array
1881 *
1882 * @param array Array of SQL query components
1883 * @return string SQL SELECT query
1884 * @see parseSELECT()
1885 */
1886 function compileSELECT($components) {
1887
1888 // Initialize:
1889 $where = $this->compileWhereClause($components['WHERE']);
1890 $groupBy = $this->compileFieldList($components['GROUPBY']);
1891 $orderBy = $this->compileFieldList($components['ORDERBY']);
1892 $limit = $components['LIMIT'];
1893
1894 // Make query:
1895 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
1896 '.$this->compileFieldList($components['SELECT']).'
1897 FROM '.$this->compileFromTables($components['FROM']).
1898 (strlen($where)?'
1899 WHERE '.$where : '').
1900 (strlen($groupBy)?'
1901 GROUP BY '.$groupBy : '').
1902 (strlen($orderBy)?'
1903 ORDER BY '.$orderBy : '').
1904 (strlen($limit)?'
1905 LIMIT '.$limit : '');
1906
1907 return $query;
1908 }
1909
1910 /**
1911 * Compiles an UPDATE statement from components array
1912 *
1913 * @param array Array of SQL query components
1914 * @return string SQL UPDATE query
1915 * @see parseUPDATE()
1916 */
1917 function compileUPDATE($components) {
1918
1919 // Where clause:
1920 $where = $this->compileWhereClause($components['WHERE']);
1921
1922 // Fields
1923 $fields = array();
1924 foreach($components['FIELDS'] as $fN => $fV) {
1925 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1926 }
1927
1928 // Make query:
1929 $query = 'UPDATE '.$components['TABLE'].' SET
1930 '.implode(',
1931 ',$fields).'
1932 '.(strlen($where)?'
1933 WHERE '.$where : '');
1934
1935 return $query;
1936 }
1937
1938 /**
1939 * Compiles an INSERT statement from components array
1940 *
1941 * @param array Array of SQL query components
1942 * @return string SQL INSERT query
1943 * @see parseINSERT()
1944 */
1945 function compileINSERT($components) {
1946
1947 if ($components['VALUES_ONLY']) {
1948 // Initialize:
1949 $fields = array();
1950 foreach($components['VALUES_ONLY'] as $fV) {
1951 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1952 }
1953
1954 // Make query:
1955 $query = 'INSERT INTO '.$components['TABLE'].'
1956 VALUES
1957 ('.implode(',
1958 ',$fields).')';
1959 } else {
1960 // Initialize:
1961 $fields = array();
1962 foreach($components['FIELDS'] as $fN => $fV) {
1963 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1964 }
1965
1966 // Make query:
1967 $query = 'INSERT INTO '.$components['TABLE'].'
1968 ('.implode(',
1969 ',array_keys($fields)).')
1970 VALUES
1971 ('.implode(',
1972 ',$fields).')';
1973 }
1974
1975 return $query;
1976 }
1977
1978 /**
1979 * Compiles an DELETE statement from components array
1980 *
1981 * @param array Array of SQL query components
1982 * @return string SQL DELETE query
1983 * @see parseDELETE()
1984 */
1985 function compileDELETE($components) {
1986
1987 // Where clause:
1988 $where = $this->compileWhereClause($components['WHERE']);
1989
1990 // Make query:
1991 $query = 'DELETE FROM '.$components['TABLE'].
1992 (strlen($where)?'
1993 WHERE '.$where : '');
1994
1995 return $query;
1996 }
1997
1998 /**
1999 * Compiles a CREATE TABLE statement from components array
2000 *
2001 * @param array Array of SQL query components
2002 * @return string SQL CREATE TABLE query
2003 * @see parseCREATETABLE()
2004 */
2005 function compileCREATETABLE($components) {
2006
2007 // Create fields and keys:
2008 $fieldsKeys = array();
2009 foreach($components['FIELDS'] as $fN => $fCfg) {
2010 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
2011 }
2012 foreach($components['KEYS'] as $kN => $kCfg) {
2013 if ($kN == 'PRIMARYKEY') {
2014 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
2015 } else {
2016 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
2017 }
2018 }
2019
2020 // Make query:
2021 $query = 'CREATE TABLE '.$components['TABLE'].' (
2022 '.implode(',
2023 ', $fieldsKeys).'
2024 )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
2025
2026 return $query;
2027 }
2028
2029 /**
2030 * Compiles an ALTER TABLE statement from components array
2031 *
2032 * @param array Array of SQL query components
2033 * @return string SQL ALTER TABLE query
2034 * @see parseALTERTABLE()
2035 */
2036 function compileALTERTABLE($components) {
2037
2038 // Make query:
2039 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
2040
2041 // Based on action, add the final part:
2042 switch(strtoupper(ereg_replace('[[:space:]]','',$components['action']))) {
2043 case 'ADD':
2044 $query.=' '.$this->compileFieldCfg($components['definition']);
2045 break;
2046 case 'CHANGE':
2047 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
2048 break;
2049 case 'DROP':
2050 case 'DROPKEY':
2051 break;
2052 case 'ADDKEY':
2053 case 'ADDPRIMARYKEY':
2054 $query.=' ('.implode(',',$components['fields']).')';
2055 break;
2056 }
2057
2058 // Return query
2059 return $query;
2060 }
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075 /**************************************
2076 *
2077 * Compiling queries, helper functions for parts of queries
2078 *
2079 **************************************/
2080
2081 /**
2082 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
2083 * Can also compile field lists for ORDER BY and GROUP BY.
2084 *
2085 * @param array Array of select fields, (made with ->parseFieldList())
2086 * @return string Select field string
2087 * @see parseFieldList()
2088 */
2089 function compileFieldList($selectFields) {
2090
2091 // Prepare buffer variable:
2092 $outputParts = array();
2093
2094 // Traverse the selectFields if any:
2095 if (is_array($selectFields)) {
2096 foreach($selectFields as $k => $v) {
2097
2098 // Detecting type:
2099 switch($v['type']) {
2100 case 'function':
2101 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
2102 break;
2103 case 'field':
2104 $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
2105 break;
2106 }
2107
2108 // Alias:
2109 if ($v['as']) {
2110 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
2111 }
2112
2113 // Specifically for ORDER BY and GROUP BY field lists:
2114 if ($v['sortDir']) {
2115 $outputParts[$k].= ' '.$v['sortDir'];
2116 }
2117 }
2118 }
2119
2120 // Return imploded buffer:
2121 return implode(', ',$outputParts);
2122 }
2123
2124 /**
2125 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
2126 *
2127 * @param array Array of table names, (made with ->parseFromTables())
2128 * @return string Table name string
2129 * @see parseFromTables()
2130 */
2131 function compileFromTables($tablesArray) {
2132
2133 // Prepare buffer variable:
2134 $outputParts = array();
2135
2136 // Traverse the table names:
2137 if (is_array($tablesArray)) {
2138 foreach($tablesArray as $k => $v) {
2139
2140 // Set table name:
2141 $outputParts[$k] = $v['table'];
2142
2143 // Add alias AS if there:
2144 if ($v['as']) {
2145 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
2146 }
2147
2148 if (is_array($v['JOIN'])) {
2149 $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
2150 }
2151
2152 }
2153 }
2154
2155 // Return imploded buffer:
2156 return implode(', ',$outputParts);
2157 }
2158
2159 /**
2160 * Implodes an array of WHERE clause configuration into a WHERE clause.
2161 * NOTICE: MIGHT BY A TEMPORARY FUNCTION. Use for debugging only!
2162 *
2163 * @param array WHERE clause configuration
2164 * @return string WHERE clause as string.
2165 * @see explodeWhereClause()
2166 */
2167 function compileWhereClause($clauseArray) {
2168
2169 // Prepare buffer variable:
2170 $output='';
2171
2172 // Traverse clause array:
2173 if (is_array($clauseArray)) {
2174 foreach($clauseArray as $k => $v) {
2175
2176 // Set operator:
2177 $output.=$v['operator'] ? ' '.$v['operator'] : '';
2178
2179 // Look for sublevel:
2180 if (is_array($v['sub'])) {
2181 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
2182 } else {
2183
2184 // Set field/table with modifying prefix if any:
2185 $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
2186
2187 // Set calculation, if any:
2188 if ($v['calc']) {
2189 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
2190 }
2191
2192 // Set comparator:
2193 if ($v['comparator']) {
2194 $output.=' '.$v['comparator'];
2195
2196 // Detecting value type; list or plain:
2197 if (t3lib_div::inList('NOTIN,IN',strtoupper(ereg_replace('[[:space:]]','',$v['comparator'])))) {
2198 $valueBuffer = array();
2199 foreach($v['value'] as $realValue) {
2200 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
2201 }
2202 $output.=' ('.trim(implode(',',$valueBuffer)).')';
2203 } else {
2204 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
2205 }
2206 }
2207 }
2208 }
2209 }
2210
2211 // Return output buffer:
2212 return $output;
2213 }
2214
2215 /**
2216 * Compile field definition
2217 *
2218 * @param array Field definition parts
2219 * @return string Field definition string
2220 */
2221 function compileFieldCfg($fieldCfg) {
2222
2223 // Set type:
2224 $cfg = $fieldCfg['fieldType'];
2225
2226 // Add value, if any:
2227 if (strlen($fieldCfg['value'])) {
2228 $cfg.='('.$fieldCfg['value'].')';
2229 }
2230
2231 // Add additional features:
2232 if (is_array($fieldCfg['featureIndex'])) {
2233 foreach($fieldCfg['featureIndex'] as $featureDef) {
2234 $cfg.=' '.$featureDef['keyword'];
2235
2236 // Add value if found:
2237 if (is_array($featureDef['value'])) {
2238 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
2239 }
2240 }
2241 }
2242
2243 // Return field definition string:
2244 return $cfg;
2245 }
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257 /*************************
2258 *
2259 * Debugging
2260 *
2261 *************************/
2262
2263 /**
2264 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
2265 *
2266 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
2267 * @param string SQL string to verify parsability of
2268 * @return mixed Returns array with string 1 and 2 if error, otherwise false
2269 */
2270 function debug_parseSQLpart($part,$str) {
2271 switch($part) {
2272 case 'SELECT':
2273 return $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
2274 break;
2275 case 'FROM':
2276 return $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
2277 break;
2278 case 'WHERE':
2279 return $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
2280 break;
2281 }
2282 }
2283
2284 /**
2285 * Compare two query strins by stripping away whitespace.
2286 *
2287 * @param string SQL String 1
2288 * @param string SQL string 2
2289 * @param boolean If true, the strings are compared insensitive to case
2290 * @return mixed Returns array with string 1 and 2 if error, otherwise false
2291 */
2292 function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
2293 if ($caseInsensitive) {
2294 $str1 = strtoupper($str);
2295 $str2 = strtoupper($newStr);
2296 } else {
2297 $str1 = $str;
2298 $str2 = $newStr;
2299 }
2300
2301 // Fixing escaped chars:
2302 $search = array('\0', '\n', '\r', '\Z');
2303 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
2304 $str1 = str_replace($search, $replace, $str1);
2305 $str2 = str_replace($search, $replace, $str2);
2306
2307 # Normally, commented out since they are needed only in tricky cases...
2308 # $str1 = stripslashes($str1);
2309 # $str2 = stripslashes($str2);
2310
2311 if (strcmp(ereg_replace('[[:space:]]','',$this->trimSQL($str1)),ereg_replace('[[:space:]]','',$this->trimSQL($str2)))) {
2312 return array(
2313 ereg_replace('[[:space:]]+',' ',$str),
2314 ereg_replace('[[:space:]]+',' ',$newStr),
2315 );
2316 }
2317 }
2318
2319 /**
2320 * 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
2321 *
2322 * @param string SQL query
2323 * @return string Query if all is well, otherwise exit.
2324 */
2325 function debug_testSQL($SQLquery) {
2326 # return $SQLquery;
2327 #debug(array($SQLquery));
2328
2329 // Getting result array:
2330 $parseResult = $this->parseSQL($SQLquery);
2331
2332 // If result array was returned, proceed. Otherwise show error and exit.
2333 if (is_array($parseResult)) {
2334
2335 // Re-compile query:
2336 $newQuery = $this->compileSQL($parseResult);
2337
2338 // TEST the new query:
2339 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
2340
2341 // Return new query if OK, otherwise show error and exit:
2342 if (!is_array($testResult)) {
2343 return $newQuery;
2344 } else {
2345 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
2346 exit;
2347 }
2348 } else {
2349 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
2350 exit;
2351 }
2352 }
2353
2354 /**
2355 * Returns the result set (in array) as HTML table. For debugging.
2356 *
2357 * @param array Result set array (array of rows)
2358 * @return string HTML table
2359 */
2360 function debug_printResultSet($array) {
2361
2362 if (count($array)) {
2363 $tRows=array();
2364 $fields = array_keys(current($array));
2365 $tCell[]='
2366 <td>IDX</td>';
2367 foreach($fields as $fieldName) {
2368 $tCell[]='
2369 <td>'.htmlspecialchars($fieldName).'</td>';
2370 }
2371 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
2372
2373
2374 foreach($array as $index => $rec) {
2375
2376 $tCell=array();
2377 $tCell[]='
2378 <td>'.htmlspecialchars($index).'</td>';
2379 foreach($fields as $fieldName) {
2380 $tCell[]='
2381 <td>'.htmlspecialchars($rec[$fieldName]).'</td>';
2382 }
2383 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
2384 }
2385
2386 return '<table border="1">'.implode('',$tRows).'</table>';
2387 } else 'Empty resultset';
2388 }
2389 }
2390
2391
2392 /**
2393 * PHP SQL engine, result object
2394 *
2395 * @author Kasper Skaarhoj <kasper@typo3.com>
2396 * @package TYPO3
2397 * @subpackage t3lib
2398 */
2399 class t3lib_sqlengine_resultobj {
2400
2401 // Result array, must contain the fields in the order they were selected in the SQL statement (for sql_fetch_row())
2402 var $result = array();
2403
2404 var $TYPO3_DBAL_handlerType = '';
2405 var $TYPO3_DBAL_tableList = '';
2406
2407
2408 /**
2409 * Counting number of rows
2410 *
2411 * @return integer
2412 */
2413 function sql_num_rows() {
2414 return count($this->result);
2415 }
2416
2417 /**
2418 * Fetching next row in result array
2419 *
2420 * @return array Associative array
2421 */
2422 function sql_fetch_assoc() {
2423 $row = current($this->result);
2424 next($this->result);
2425 return $row;
2426 }
2427
2428 /**
2429 * Fetching next row, numerical indices
2430 *
2431 * @return array Numerical array
2432 */
2433 function sql_fetch_row() {
2434 $resultRow = $this->sql_fetch_assoc();
2435
2436 if (is_array($resultRow)) {
2437 $numArray = array();
2438 foreach($resultRow as $value) {
2439 $numArray[]=$value;
2440 }
2441 return $numArray;
2442 }
2443 }
2444
2445 /**
2446 * Seeking position in result
2447 *
2448 * @param integer Position pointer.
2449 * @return boolean Returns true on success
2450 */
2451 function sql_data_seek($pointer) {
2452 reset($this->result);
2453 for ($a=0;$a<$pointer;$a++) {
2454 next($this->result);
2455 }
2456 return TRUE;
2457 }
2458
2459 /**
2460 * [Describe function...]
2461 *
2462 * @return [type] ...
2463 */
2464 function sql_field_type() {
2465 return '';
2466 }
2467 }
2468
2469
2470
2471 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']) {
2472 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']);
2473 }
2474 ?>