This commit was manufactured by cvs2svn to create tag
[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 * 146: class t3lib_sqlengine
40 * 172: function init($config, &$pObj)
41 * 180: function resetStatusVars()
42 * 193: function processAccordingToConfig(&$value,$fInfo)
43 *
44 * SECTION: SQL queries
45 * 248: function exec_INSERTquery($table,$fields_values)
46 * 315: function exec_UPDATEquery($table,$where,$fields_values)
47 * 374: function exec_DELETEquery($table,$where)
48 * 425: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit)
49 * 468: function sql_query($query)
50 * 479: function sql_error()
51 * 488: function sql_insert_id()
52 * 497: function sql_affected_rows()
53 * 507: function quoteStr($str)
54 *
55 * SECTION: SQL admin functions
56 * 532: function admin_get_tables()
57 * 543: function admin_get_fields($tableName)
58 * 554: function admin_get_keys($tableName)
59 * 565: function admin_query($query)
60 *
61 * SECTION: Data Source I/O
62 * 590: function readDataSource($table)
63 * 602: function saveDataSource($table)
64 *
65 * SECTION: SQL engine functions
66 * 631: function selectFromData($table,$where)
67 * 667: function select_evalSingle($table,$config,&$itemKeys)
68 * 788: function getResultSet($keys, $table, $fieldList)
69 *
70 * SECTION: SQL Parsing, full queries
71 * 830: function parseSQL($parseString)
72 * 890: function parseSELECT($parseString)
73 * 959: function parseUPDATE($parseString)
74 * 1013: function parseINSERT($parseString)
75 * 1073: function parseDELETE($parseString)
76 * 1111: function parseEXPLAIN($parseString)
77 * 1133: function parseCREATETABLE($parseString)
78 * 1205: function parseALTERTABLE($parseString)
79 * 1271: function parseDROPTABLE($parseString)
80 *
81 * SECTION: SQL Parsing, helper functions for parts of queries
82 * 1330: function parseFieldList(&$parseString, $stopRegex='')
83 * 1448: function parseFromTables(&$parseString, $stopRegex='')
84 * 1515: function parseWhereClause(&$parseString, $stopRegex='')
85 * 1623: function parseFieldDef(&$parseString, $stopRegex='')
86 *
87 * SECTION: Parsing: Helper functions
88 * 1684: function nextPart(&$parseString,$regex,$trimAll=FALSE)
89 * 1698: function getValue(&$parseString,$comparator='')
90 * 1753: function getValueInQuotes(&$parseString,$quote)
91 * 1778: function parseStripslashes($str)
92 * 1792: function compileAddslashes($str)
93 * 1806: function parseError($msg,$restQuery)
94 * 1820: function trimSQL($str)
95 *
96 * SECTION: Compiling queries
97 * 1848: function compileSQL($components)
98 * 1886: function compileSELECT($components)
99 * 1917: function compileUPDATE($components)
100 * 1945: function compileINSERT($components)
101 * 1985: function compileDELETE($components)
102 * 2005: function compileCREATETABLE($components)
103 * 2036: function compileALTERTABLE($components)
104 *
105 * SECTION: Compiling queries, helper functions for parts of queries
106 * 2089: function compileFieldList($selectFields)
107 * 2131: function compileFromTables($tablesArray)
108 * 2167: function compileWhereClause($clauseArray)
109 * 2221: function compileFieldCfg($fieldCfg)
110 *
111 * SECTION: Debugging
112 * 2270: function debug_parseSQLpart($part,$str)
113 * 2292: function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE)
114 * 2325: function debug_testSQL($SQLquery)
115 * 2360: function debug_printResultSet($array)
116 *
117 *
118 * 2399: class t3lib_sqlengine_resultobj
119 * 2413: function sql_num_rows()
120 * 2422: function sql_fetch_assoc()
121 * 2433: function sql_fetch_row()
122 * 2451: function sql_data_seek($pointer)
123 * 2464: 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|DROP[[:space:]]+PRIMARY[[: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 (t3lib_div::inList('ADDPRIMARYKEY,DROPPRIMARYKEY',$actionKey) || $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 case 'DROPPRIMARYKEY':
1253 // ??? todo!
1254 break;
1255 }
1256 } else return $this->parseError('No field name found',$parseString);
1257 } else return $this->parseError('No action CHANGE, DROP or ADD found!',$parseString);
1258 } else return $this->parseError('No table found!',$parseString);
1259
1260 // Should be no more content now:
1261 if ($parseString) {
1262 return $this->parseError('Still content in clause after parsing!',$parseString);
1263 }
1264
1265 return $result;
1266 }
1267
1268 /**
1269 * Parsing DROP TABLE query
1270 *
1271 * @param string SQL string starting with DROP TABLE
1272 * @return mixed Returns array with components of DROP TABLE query on success, otherwise an error message string.
1273 */
1274 function parseDROPTABLE($parseString) {
1275
1276 // Removing DROP TABLE
1277 $parseString = $this->trimSQL($parseString);
1278 $parseString = eregi_replace('^DROP[[:space:]]+TABLE[[:space:]]+','',$parseString);
1279
1280 // Init output variable:
1281 $result = array();
1282 $result['type'] = 'DROPTABLE';
1283
1284 // IF EXISTS
1285 $result['ifExists'] = $this->nextPart($parseString, '^(IF[[:space:]]+EXISTS[[:space:]]+)');
1286
1287 // Get table:
1288 $result['TABLE'] = $this->nextPart($parseString, '^([[:alnum:]_]+)[[:space:]]+');
1289
1290 if ($result['TABLE']) {
1291
1292 // Should be no more content now:
1293 if ($parseString) {
1294 return $this->parseError('Still content in clause after parsing!',$parseString);
1295 }
1296
1297 return $result;
1298 } else return $this->parseError('No table found!',$parseString);
1299 }
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317 /**************************************
1318 *
1319 * SQL Parsing, helper functions for parts of queries
1320 *
1321 **************************************/
1322
1323 /**
1324 * Parsing the fields in the "SELECT [$selectFields] FROM" part of a query into an array.
1325 * The output from this function can be compiled back into a field list with ->compileFieldList()
1326 * 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!
1327 *
1328 * @param string The string with fieldnames, eg. "title, uid AS myUid, max(tstamp), count(*)" etc. NOTICE: passed by reference!
1329 * @param string Regular expressing to STOP parsing, eg. '^(FROM)([[:space:]]*)'
1330 * @return array If successful parsing, returns an array, otherwise an error string.
1331 * @see compileFieldList()
1332 */
1333 function parseFieldList(&$parseString, $stopRegex='') {
1334
1335 // Prepare variables:
1336 $parseString = $this->trimSQL($parseString);
1337 $this->lastStopKeyWord = '';
1338 $this->parse_error = '';
1339
1340
1341 $stack = array(); // Contains the parsed content
1342 $pnt = 0; // Pointer to positions in $stack
1343 $level = 0; // Indicates the parenthesis level we are at.
1344 $loopExit = 0; // Recursivity brake.
1345
1346 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1347 while (strlen($parseString)) {
1348
1349 // Checking if we are inside / outside parenthesis (in case of a function like count(), max(), min() etc...):
1350 if ($level>0) { // Inside parenthesis here (does NOT detect if values in quotes are used, the only token is ")" or "("):
1351
1352 // Accumulate function content until next () parenthesis:
1353 $funcContent = $this->nextPart($parseString,'^([^()]*.)');
1354 $stack[$pnt]['func_content.'][] = array(
1355 'level' => $level,
1356 'func_content' => substr($funcContent,0,-1)
1357 );
1358 $stack[$pnt]['func_content'].= $funcContent;
1359
1360 // Detecting ( or )
1361 switch(substr($stack[$pnt]['func_content'],-1)) {
1362 case '(':
1363 $level++;
1364 break;
1365 case ')':
1366 $level--;
1367 if (!$level) { // If this was the last parenthesis:
1368 $stack[$pnt]['func_content'] = substr($stack[$pnt]['func_content'],0,-1);
1369 $parseString = ltrim($parseString); // Remove any whitespace after the parenthesis.
1370 }
1371 break;
1372 }
1373 } else { // Outside parenthesis, looking for next field:
1374
1375 // Looking for a known function (only known functions supported)
1376 $func = $this->nextPart($parseString,'^(count|max|min|floor|sum|avg)[[:space:]]*\(');
1377 if ($func) {
1378 $parseString = trim(substr($parseString,1)); // Strip of "("
1379 $stack[$pnt]['type'] = 'function';
1380 $stack[$pnt]['function'] = $func;
1381 $level++; // increse parenthesis level counter.
1382 } else {
1383 // Otherwise, look for regular fieldname:
1384 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]\*._]+)(,|[[:space:]]+)')) {
1385 $stack[$pnt]['type'] = 'field';
1386
1387 // Explode fieldname into field and table:
1388 $tableField = explode('.',$fieldName,2);
1389 if (count($tableField)==2) {
1390 $stack[$pnt]['table'] = $tableField[0];
1391 $stack[$pnt]['field'] = $tableField[1];
1392 } else {
1393 $stack[$pnt]['table'] = '';
1394 $stack[$pnt]['field'] = $tableField[0];
1395 }
1396 } else {
1397 return $this->parseError('No field name found as expected',$parseString);
1398 }
1399 }
1400 }
1401
1402 // After a function or field we look for "AS" alias and a comma to separate to the next field in the list:
1403 if (!$level) {
1404
1405 // Looking for "AS" alias:
1406 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
1407 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
1408 $stack[$pnt]['as_keyword'] = $as;
1409 }
1410
1411 // Looking for "ASC" or "DESC" keywords (for ORDER BY)
1412 if ($sDir = $this->nextPart($parseString,'^(ASC|DESC)([[:space:]]+|,)')) {
1413 $stack[$pnt]['sortDir'] = $sDir;
1414 }
1415
1416 // Looking for stop-keywords:
1417 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1418 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1419 return $stack;
1420 }
1421
1422 // Looking for comma (since the stop-keyword did not trigger a return...)
1423 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
1424 return $this->parseError('No comma found as expected',$parseString);
1425 }
1426
1427 // Increasing pointer:
1428 $pnt++;
1429 }
1430
1431 // Check recursivity brake:
1432 $loopExit++;
1433 if ($loopExit>500) {
1434 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1435 }
1436 }
1437
1438 // Return result array:
1439 return $stack;
1440 }
1441
1442 /**
1443 * Parsing the tablenames in the "FROM [$parseString] WHERE" part of a query into an array.
1444 * The success of this parsing determines if that part of the query is supported by TYPO3.
1445 *
1446 * @param string list of tables, eg. "pages, tt_content" or "pages A, pages B". NOTICE: passed by reference!
1447 * @param string Regular expressing to STOP parsing, eg. '^(WHERE)([[:space:]]*)'
1448 * @return array If successful parsing, returns an array, otherwise an error string.
1449 * @see compileFromTables()
1450 */
1451 function parseFromTables(&$parseString, $stopRegex='') {
1452
1453 // Prepare variables:
1454 $parseString = $this->trimSQL($parseString);
1455 $this->lastStopKeyWord = '';
1456 $this->parse_error = '';
1457
1458 $stack = array(); // Contains the parsed content
1459 $pnt = 0; // Pointer to positions in $stack
1460 $loopExit = 0; // Recursivity brake.
1461
1462 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1463 while (strlen($parseString)) {
1464
1465 // Looking for the table:
1466 if ($stack[$pnt]['table'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)')) {
1467 if ($as = $this->nextPart($parseString,'^(AS)[[:space:]]+')) {
1468 $stack[$pnt]['as'] = $this->nextPart($parseString,'^([[:alnum:]_]+)(,|[[:space:]]+)');
1469 $stack[$pnt]['as_keyword'] = $as;
1470 }
1471 } else return $this->parseError('No table name found as expected!',$parseString);
1472
1473 // Looking for JOIN
1474 if ($join = $this->nextPart($parseString,'^(JOIN|LEFT[[:space:]]+JOIN)[[:space:]]+')) {
1475 $stack[$pnt]['JOIN']['type'] = $join;
1476 if ($stack[$pnt]['JOIN']['withTable'] = $this->nextPart($parseString,'^([[:alnum:]_]+)[[:space:]]+ON[[:space:]]+',1)) {
1477 $field1 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]*=[[:space:]]*',1);
1478 $field2 = $this->nextPart($parseString,'^([[:alnum:]_.]+)[[:space:]]+');
1479 if ($field1 && $field2) {
1480 $stack[$pnt]['JOIN']['ON'] = array($field1,$field2);
1481 } else return $this->parseError('No join fields found!',$parseString);
1482 } else return $this->parseError('No join table found!',$parseString);
1483 }
1484
1485 // Looking for stop-keywords:
1486 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1487 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1488 return $stack;
1489 }
1490
1491 // Looking for comma:
1492 if (strlen($parseString) && !$this->nextPart($parseString,'^(,)')) {
1493 return $this->parseError('No comma found as expected',$parseString);
1494 }
1495
1496 // Increasing pointer:
1497 $pnt++;
1498
1499 // Check recursivity brake:
1500 $loopExit++;
1501 if ($loopExit>500) {
1502 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1503 }
1504 }
1505
1506 // Return result array:
1507 return $stack;
1508 }
1509
1510 /**
1511 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1512 * The success of this parsing determines if that part of the query is supported by TYPO3.
1513 *
1514 * @param string WHERE clause to parse. NOTICE: passed by reference!
1515 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1516 * @return mixed If successful parsing, returns an array, otherwise an error string.
1517 */
1518 function parseWhereClause(&$parseString, $stopRegex='') {
1519
1520 // Prepare variables:
1521 $parseString = $this->trimSQL($parseString);
1522 $this->lastStopKeyWord = '';
1523 $this->parse_error = '';
1524
1525 $stack = array(0 => array()); // Contains the parsed content
1526 $pnt = array(0 => 0); // Pointer to positions in $stack
1527 $level = 0; // Determines parenthesis level
1528 $loopExit = 0; // Recursivity brake.
1529
1530 // $parseString is continously shortend by the process and we keep parsing it till it is zero:
1531 while (strlen($parseString)) {
1532
1533 // Look for next parenthesis level:
1534 $newLevel = $this->nextPart($parseString,'^([(])');
1535 if ($newLevel=='(') { // If new level is started, manage stack/pointers:
1536 $level++; // Increase level
1537 $pnt[$level] = 0; // Reset pointer for this level
1538 $stack[$level] = array(); // Reset stack for this level
1539 } else { // If no new level is started, just parse the current level:
1540
1541 // Find "modifyer", eg. "NOT or !"
1542 $stack[$level][$pnt[$level]]['modifier'] = trim($this->nextPart($parseString,'^(!|NOT[[:space:]]+)'));
1543
1544 // Fieldname:
1545 if ($fieldName = $this->nextPart($parseString,'^([[:alnum:]._]+)([[:space:]]+|&|<=|>=|<|>|=|!=|IS)')) {
1546
1547 // Parse field name into field and table:
1548 $tableField = explode('.',$fieldName,2);
1549 if (count($tableField)==2) {
1550 $stack[$level][$pnt[$level]]['table'] = $tableField[0];
1551 $stack[$level][$pnt[$level]]['field'] = $tableField[1];
1552 } else {
1553 $stack[$level][$pnt[$level]]['table'] = '';
1554 $stack[$level][$pnt[$level]]['field'] = $tableField[0];
1555 }
1556 } else {
1557 return $this->parseError('No field name found as expected',$parseString);
1558 }
1559
1560 // See if the value is calculated. Support only for "&" (boolean AND) at the moment:
1561 $stack[$level][$pnt[$level]]['calc'] = $this->nextPart($parseString,'^(&)');
1562 if (strlen($stack[$level][$pnt[$level]]['calc'])) {
1563 // Finding value for calculation:
1564 $stack[$level][$pnt[$level]]['calc_value'] = $this->getValue($parseString);
1565 }
1566
1567 // Find "comparator":
1568 $stack[$level][$pnt[$level]]['comparator'] = $this->nextPart($parseString,'^(<=|>=|<|>|=|!=|NOT[[:space:]]+IN|IN|NOT[[:space:]]+LIKE|LIKE|IS)');
1569 if (strlen($stack[$level][$pnt[$level]]['comparator'])) {
1570 // Finding value for comparator:
1571 $stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString,$stack[$level][$pnt[$level]]['comparator']);
1572 if ($this->parse_error) { return $this->parse_error; }
1573 }
1574
1575 // Finished, increase pointer:
1576 $pnt[$level]++;
1577
1578 // Checking if the current level is ended, in that case do stack management:
1579 while ($this->nextPart($parseString,'^([)])')) {
1580 $level--; // Decrease level:
1581 $stack[$level][$pnt[$level]]['sub'] = $stack[$level+1]; // Copy stack
1582 $pnt[$level]++; // Increase pointer of the new level
1583
1584 // Make recursivity check:
1585 $loopExit++;
1586 if ($loopExit>500) {
1587 return $this->parseError('More than 500 loops (in search for exit parenthesis), exiting prematurely...',$parseString);
1588 }
1589 }
1590
1591 // Detecting the operator for the next level; support for AND, OR and &&):
1592 $op = $this->nextPart($parseString,'^(AND|OR|AND[[:space:]]+NOT)(\(|[[:space:]]+)');
1593 if ($op) {
1594 $stack[$level][$pnt[$level]]['operator'] = $op;
1595 } elseif (strlen($parseString)) {
1596
1597 // Looking for stop-keywords:
1598 if ($stopRegex && $this->lastStopKeyWord = $this->nextPart($parseString, $stopRegex)) {
1599 $this->lastStopKeyWord = strtoupper(ereg_replace('[[:space:]]*','',$this->lastStopKeyWord));
1600 return $stack[0];
1601 } else {
1602 return $this->parseError('No operator, but parsing not finished.',$parseString);
1603 }
1604 }
1605 }
1606
1607 // Make recursivity check:
1608 $loopExit++;
1609 if ($loopExit>500) {
1610 return $this->parseError('More than 500 loops, exiting prematurely...',$parseString);
1611 }
1612 }
1613
1614 // Return the stacks lowest level:
1615 return $stack[0];
1616 }
1617
1618 /**
1619 * Parsing the WHERE clause fields in the "WHERE [$parseString] ..." part of a query into a multidimensional array.
1620 * The success of this parsing determines if that part of the query is supported by TYPO3.
1621 *
1622 * @param string WHERE clause to parse. NOTICE: passed by reference!
1623 * @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
1624 * @return mixed If successful parsing, returns an array, otherwise an error string.
1625 */
1626 function parseFieldDef(&$parseString, $stopRegex='') {
1627 // Prepare variables:
1628 $parseString = $this->trimSQL($parseString);
1629 $this->lastStopKeyWord = '';
1630 $this->parse_error = '';
1631
1632 $result = array();
1633
1634 // Field type:
1635 if ($result['fieldType'] = $this->nextPart($parseString,'^(int|smallint|tinyint|mediumint|double|varchar|char|text|tinytext|mediumtext|blob|tinyblob|mediumblob)([[:space:]]+|\()')) {
1636
1637 // Looking for value:
1638 if (substr($parseString,0,1)=='(') {
1639 $parseString = substr($parseString,1);
1640 if ($result['value'] = $this->nextPart($parseString,'^([^)]*)')) {
1641 $parseString = ltrim(substr($parseString,1));
1642 } else return $this->parseError('No end-parenthesis for value found!',$parseString);
1643 }
1644
1645 // Looking for keywords
1646 while($keyword = $this->nextPart($parseString,'^(DEFAULT|NOT[[:space:]]+NULL|AUTO_INCREMENT|UNSIGNED)([[:space:]]+|,|\))')) {
1647 $keywordCmp = strtoupper(ereg_replace('[[:space:]]*','',$keyword));
1648
1649 $result['featureIndex'][$keywordCmp]['keyword'] = $keyword;
1650
1651 switch($keywordCmp) {
1652 case 'DEFAULT':
1653 $result['featureIndex'][$keywordCmp]['value'] = $this->getValue($parseString);
1654 break;
1655 }
1656 }
1657 } else return $this->parseError('Field type unknown!',$parseString);
1658
1659 return $result;
1660 }
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672 /************************************
1673 *
1674 * Parsing: Helper functions
1675 *
1676 ************************************/
1677
1678 /**
1679 * Strips of a part of the parseString and returns the matching part.
1680 * Helper function for the parsing methods.
1681 *
1682 * @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.
1683 * @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())
1684 * @param boolean If set the full match of the regex is stripped of the beginning of the string!
1685 * @return string The value of the first parenthesis level of the REGEX.
1686 */
1687 function nextPart(&$parseString,$regex,$trimAll=FALSE) {
1688 if (eregi($regex,$parseString.' ', $reg)) { // Adding space char because [[:space:]]+ is often a requirement in regex's
1689 $parseString = ltrim(substr($parseString,strlen($reg[$trimAll?0:1])));
1690 return $reg[1];
1691 }
1692 }
1693
1694 /**
1695 * Finds value in beginning of $parseString, returns result and strips it of parseString
1696 *
1697 * @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
1698 * @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)
1699 * @return string The value (string/integer). Otherwise an array with error message in first key (0)
1700 */
1701 function getValue(&$parseString,$comparator='') {
1702 if (t3lib_div::inList('NOTIN,IN,_LIST',strtoupper(ereg_replace('[[:space:]]','',$comparator)))) { // List of values:
1703 if ($this->nextPart($parseString,'^([(])')) {
1704 $listValues = array();
1705 $comma=',';
1706
1707 while($comma==',') {
1708 $listValues[] = $this->getValue($parseString);
1709 $comma = $this->nextPart($parseString,'^([,])');
1710 }
1711
1712 $out = $this->nextPart($parseString,'^([)])');
1713 if ($out) {
1714 if ($comparator=='_LIST') {
1715 $kVals = array();
1716 foreach ($listValues as $vArr) {
1717 $kVals[] = $vArr[0];
1718 }
1719 return $kVals;
1720 } else {
1721 return $listValues;
1722 }
1723 } else return array($this->parseError('No ) parenthesis in list',$parseString));
1724 } else return array($this->parseError('No ( parenthesis starting the list',$parseString));
1725
1726 } else { // Just plain string value, in quotes or not:
1727
1728 // Quote?
1729 $firstChar = substr($parseString,0,1);
1730
1731 switch($firstChar) {
1732 case '"':
1733 return array($this->getValueInQuotes($parseString,'"'),'"');
1734 break;
1735 case "'":
1736 return array($this->getValueInQuotes($parseString,"'"),"'");
1737 break;
1738 default:
1739 if (eregi('^([[:alnum:]._-]+)',$parseString, $reg)) {
1740 $parseString = ltrim(substr($parseString,strlen($reg[0])));
1741 return array($reg[1]);
1742 }
1743 break;
1744 }
1745 }
1746 }
1747
1748 /**
1749 * Get value in quotes from $parseString.
1750 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1751 *
1752 * @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.
1753 * @param string The quote used; input either " or '
1754 * @return string The value, passed through stripslashes() !
1755 */
1756 function getValueInQuotes(&$parseString,$quote) {
1757
1758 $parts = explode($quote,substr($parseString,1));
1759 $buffer = '';
1760 foreach($parts as $k => $v) {
1761 $buffer.=$v;
1762
1763 unset($reg);
1764 ereg('[\]*$',$v,$reg);
1765 if (strlen($reg[0])%2) {
1766 $buffer.=$quote;
1767 } else {
1768 $parseString = ltrim(substr($parseString,strlen($buffer)+2));
1769 return $this->parseStripslashes($buffer);
1770 }
1771 }
1772 }
1773
1774 /**
1775 * Strip slashes function used for parsing
1776 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1777 *
1778 * @param string Input string
1779 * @return string Output string
1780 */
1781 function parseStripslashes($str) {
1782 $search = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1783 $replace = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1784
1785 return str_replace($search, $replace, $str);
1786 }
1787
1788 /**
1789 * Add slashes function used for compiling queries
1790 * NOTICE: If a query being parsed was prepared for another database than MySQL this function should probably be changed
1791 *
1792 * @param string Input string
1793 * @return string Output string
1794 */
1795 function compileAddslashes($str) {
1796 $search = array('\\', '\'', '"', "\x00", "\x0a", "\x0d", "\x1a");
1797 $replace = array('\\\\', '\\\'', '\\"', '\0', '\n', '\r', '\Z');
1798
1799 return str_replace($search, $replace, $str);
1800 }
1801
1802 /**
1803 * Setting the internal error message value, $this->parse_error and returns that value.
1804 *
1805 * @param string Input error message
1806 * @param string Remaining query to parse.
1807 * @return string Error message.
1808 */
1809 function parseError($msg,$restQuery) {
1810 $this->parse_error = 'SQL engine parse ERROR: '.$msg.': near "'.substr($restQuery,0,50).'"';
1811 return $this->parse_error;
1812 }
1813
1814 /**
1815 * Trimming SQL as preparation for parsing.
1816 * ";" in the end is stripped of.
1817 * White space is trimmed away around the value
1818 * A single space-char is added in the end
1819 *
1820 * @param string Input string
1821 * @return string Output string
1822 */
1823 function trimSQL($str) {
1824 return trim(ereg_replace('[[:space:];]*$','',$str)).' ';
1825 }
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838 /*************************
1839 *
1840 * Compiling queries
1841 *
1842 *************************/
1843
1844 /**
1845 * Compiles an SQL query from components
1846 *
1847 * @param array Array of SQL query components
1848 * @return string SQL query
1849 * @see parseSQL()
1850 */
1851 function compileSQL($components) {
1852 switch($components['type']) {
1853 case 'SELECT':
1854 $query = $this->compileSELECT($components);
1855 break;
1856 case 'UPDATE':
1857 $query = $this->compileUPDATE($components);
1858 break;
1859 case 'INSERT':
1860 $query = $this->compileINSERT($components);
1861 break;
1862 case 'DELETE':
1863 $query = $this->compileDELETE($components);
1864 break;
1865 case 'EXPLAIN':
1866 $query = 'EXPLAIN '.$this->compileSELECT($components);
1867 break;
1868 case 'DROPTABLE':
1869 $query = 'DROP TABLE'.($components['ifExists']?' IF EXISTS':'').' '.$components['TABLE'];
1870 break;
1871 case 'CREATETABLE':
1872 $query = $this->compileCREATETABLE($components);
1873 break;
1874 case 'ALTERTABLE':
1875 $query = $this->compileALTERTABLE($components);
1876 break;
1877 }
1878
1879 return $query;
1880 }
1881
1882 /**
1883 * Compiles a SELECT statement from components array
1884 *
1885 * @param array Array of SQL query components
1886 * @return string SQL SELECT query
1887 * @see parseSELECT()
1888 */
1889 function compileSELECT($components) {
1890
1891 // Initialize:
1892 $where = $this->compileWhereClause($components['WHERE']);
1893 $groupBy = $this->compileFieldList($components['GROUPBY']);
1894 $orderBy = $this->compileFieldList($components['ORDERBY']);
1895 $limit = $components['LIMIT'];
1896
1897 // Make query:
1898 $query = 'SELECT '.($components['STRAIGHT_JOIN'] ? $components['STRAIGHT_JOIN'].'' : '').'
1899 '.$this->compileFieldList($components['SELECT']).'
1900 FROM '.$this->compileFromTables($components['FROM']).
1901 (strlen($where)?'
1902 WHERE '.$where : '').
1903 (strlen($groupBy)?'
1904 GROUP BY '.$groupBy : '').
1905 (strlen($orderBy)?'
1906 ORDER BY '.$orderBy : '').
1907 (strlen($limit)?'
1908 LIMIT '.$limit : '');
1909
1910 return $query;
1911 }
1912
1913 /**
1914 * Compiles an UPDATE statement from components array
1915 *
1916 * @param array Array of SQL query components
1917 * @return string SQL UPDATE query
1918 * @see parseUPDATE()
1919 */
1920 function compileUPDATE($components) {
1921
1922 // Where clause:
1923 $where = $this->compileWhereClause($components['WHERE']);
1924
1925 // Fields
1926 $fields = array();
1927 foreach($components['FIELDS'] as $fN => $fV) {
1928 $fields[]=$fN.'='.$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1929 }
1930
1931 // Make query:
1932 $query = 'UPDATE '.$components['TABLE'].' SET
1933 '.implode(',
1934 ',$fields).'
1935 '.(strlen($where)?'
1936 WHERE '.$where : '');
1937
1938 return $query;
1939 }
1940
1941 /**
1942 * Compiles an INSERT statement from components array
1943 *
1944 * @param array Array of SQL query components
1945 * @return string SQL INSERT query
1946 * @see parseINSERT()
1947 */
1948 function compileINSERT($components) {
1949
1950 if ($components['VALUES_ONLY']) {
1951 // Initialize:
1952 $fields = array();
1953 foreach($components['VALUES_ONLY'] as $fV) {
1954 $fields[]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1955 }
1956
1957 // Make query:
1958 $query = 'INSERT INTO '.$components['TABLE'].'
1959 VALUES
1960 ('.implode(',
1961 ',$fields).')';
1962 } else {
1963 // Initialize:
1964 $fields = array();
1965 foreach($components['FIELDS'] as $fN => $fV) {
1966 $fields[$fN]=$fV[1].$this->compileAddslashes($fV[0]).$fV[1];
1967 }
1968
1969 // Make query:
1970 $query = 'INSERT INTO '.$components['TABLE'].'
1971 ('.implode(',
1972 ',array_keys($fields)).')
1973 VALUES
1974 ('.implode(',
1975 ',$fields).')';
1976 }
1977
1978 return $query;
1979 }
1980
1981 /**
1982 * Compiles an DELETE statement from components array
1983 *
1984 * @param array Array of SQL query components
1985 * @return string SQL DELETE query
1986 * @see parseDELETE()
1987 */
1988 function compileDELETE($components) {
1989
1990 // Where clause:
1991 $where = $this->compileWhereClause($components['WHERE']);
1992
1993 // Make query:
1994 $query = 'DELETE FROM '.$components['TABLE'].
1995 (strlen($where)?'
1996 WHERE '.$where : '');
1997
1998 return $query;
1999 }
2000
2001 /**
2002 * Compiles a CREATE TABLE statement from components array
2003 *
2004 * @param array Array of SQL query components
2005 * @return string SQL CREATE TABLE query
2006 * @see parseCREATETABLE()
2007 */
2008 function compileCREATETABLE($components) {
2009
2010 // Create fields and keys:
2011 $fieldsKeys = array();
2012 foreach($components['FIELDS'] as $fN => $fCfg) {
2013 $fieldsKeys[]=$fN.' '.$this->compileFieldCfg($fCfg['definition']);
2014 }
2015 foreach($components['KEYS'] as $kN => $kCfg) {
2016 if ($kN == 'PRIMARYKEY') {
2017 $fieldsKeys[]='PRIMARY KEY ('.implode(',', $kCfg).')';
2018 } else {
2019 $fieldsKeys[]='KEY '.$kN.' ('.implode(',', $kCfg).')';
2020 }
2021 }
2022
2023 // Make query:
2024 $query = 'CREATE TABLE '.$components['TABLE'].' (
2025 '.implode(',
2026 ', $fieldsKeys).'
2027 )'.($components['tableType'] ? ' TYPE='.$components['tableType'] : '');
2028
2029 return $query;
2030 }
2031
2032 /**
2033 * Compiles an ALTER TABLE statement from components array
2034 *
2035 * @param array Array of SQL query components
2036 * @return string SQL ALTER TABLE query
2037 * @see parseALTERTABLE()
2038 */
2039 function compileALTERTABLE($components) {
2040
2041 // Make query:
2042 $query = 'ALTER TABLE '.$components['TABLE'].' '.$components['action'].' '.($components['FIELD']?$components['FIELD']:$components['KEY']);
2043
2044 // Based on action, add the final part:
2045 switch(strtoupper(ereg_replace('[[:space:]]','',$components['action']))) {
2046 case 'ADD':
2047 $query.=' '.$this->compileFieldCfg($components['definition']);
2048 break;
2049 case 'CHANGE':
2050 $query.=' '.$components['newField'].' '.$this->compileFieldCfg($components['definition']);
2051 break;
2052 case 'DROP':
2053 case 'DROPKEY':
2054 break;
2055 case 'ADDKEY':
2056 case 'ADDPRIMARYKEY':
2057 $query.=' ('.implode(',',$components['fields']).')';
2058 break;
2059 }
2060
2061 // Return query
2062 return $query;
2063 }
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078 /**************************************
2079 *
2080 * Compiling queries, helper functions for parts of queries
2081 *
2082 **************************************/
2083
2084 /**
2085 * Compiles a "SELECT [output] FROM..:" field list based on input array (made with ->parseFieldList())
2086 * Can also compile field lists for ORDER BY and GROUP BY.
2087 *
2088 * @param array Array of select fields, (made with ->parseFieldList())
2089 * @return string Select field string
2090 * @see parseFieldList()
2091 */
2092 function compileFieldList($selectFields) {
2093
2094 // Prepare buffer variable:
2095 $outputParts = array();
2096
2097 // Traverse the selectFields if any:
2098 if (is_array($selectFields)) {
2099 foreach($selectFields as $k => $v) {
2100
2101 // Detecting type:
2102 switch($v['type']) {
2103 case 'function':
2104 $outputParts[$k] = $v['function'].'('.$v['func_content'].')';
2105 break;
2106 case 'field':
2107 $outputParts[$k] = ($v['table']?$v['table'].'.':'').$v['field'];
2108 break;
2109 }
2110
2111 // Alias:
2112 if ($v['as']) {
2113 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
2114 }
2115
2116 // Specifically for ORDER BY and GROUP BY field lists:
2117 if ($v['sortDir']) {
2118 $outputParts[$k].= ' '.$v['sortDir'];
2119 }
2120 }
2121 }
2122
2123 // Return imploded buffer:
2124 return implode(', ',$outputParts);
2125 }
2126
2127 /**
2128 * Compiles a "FROM [output] WHERE..:" table list based on input array (made with ->parseFromTables())
2129 *
2130 * @param array Array of table names, (made with ->parseFromTables())
2131 * @return string Table name string
2132 * @see parseFromTables()
2133 */
2134 function compileFromTables($tablesArray) {
2135
2136 // Prepare buffer variable:
2137 $outputParts = array();
2138
2139 // Traverse the table names:
2140 if (is_array($tablesArray)) {
2141 foreach($tablesArray as $k => $v) {
2142
2143 // Set table name:
2144 $outputParts[$k] = $v['table'];
2145
2146 // Add alias AS if there:
2147 if ($v['as']) {
2148 $outputParts[$k].= ' '.$v['as_keyword'].' '.$v['as'];
2149 }
2150
2151 if (is_array($v['JOIN'])) {
2152 $outputParts[$k].= ' '.$v['JOIN']['type'].' '.$v['JOIN']['withTable'].' ON '.implode('=',$v['JOIN']['ON']);
2153 }
2154
2155 }
2156 }
2157
2158 // Return imploded buffer:
2159 return implode(', ',$outputParts);
2160 }
2161
2162 /**
2163 * Implodes an array of WHERE clause configuration into a WHERE clause.
2164 * NOTICE: MIGHT BY A TEMPORARY FUNCTION. Use for debugging only!
2165 *
2166 * @param array WHERE clause configuration
2167 * @return string WHERE clause as string.
2168 * @see explodeWhereClause()
2169 */
2170 function compileWhereClause($clauseArray) {
2171
2172 // Prepare buffer variable:
2173 $output='';
2174
2175 // Traverse clause array:
2176 if (is_array($clauseArray)) {
2177 foreach($clauseArray as $k => $v) {
2178
2179 // Set operator:
2180 $output.=$v['operator'] ? ' '.$v['operator'] : '';
2181
2182 // Look for sublevel:
2183 if (is_array($v['sub'])) {
2184 $output.=' ('.trim($this->compileWhereClause($v['sub'])).')';
2185 } else {
2186
2187 // Set field/table with modifying prefix if any:
2188 $output.=' '.trim($v['modifier'].' '.($v['table']?$v['table'].'.':'').$v['field']);
2189
2190 // Set calculation, if any:
2191 if ($v['calc']) {
2192 $output.=$v['calc'].$v['calc_value'][1].$this->compileAddslashes($v['calc_value'][0]).$v['calc_value'][1];
2193 }
2194
2195 // Set comparator:
2196 if ($v['comparator']) {
2197 $output.=' '.$v['comparator'];
2198
2199 // Detecting value type; list or plain:
2200 if (t3lib_div::inList('NOTIN,IN',strtoupper(ereg_replace('[[:space:]]','',$v['comparator'])))) {
2201 $valueBuffer = array();
2202 foreach($v['value'] as $realValue) {
2203 $valueBuffer[]=$realValue[1].$this->compileAddslashes($realValue[0]).$realValue[1];
2204 }
2205 $output.=' ('.trim(implode(',',$valueBuffer)).')';
2206 } else {
2207 $output.=' '.$v['value'][1].$this->compileAddslashes($v['value'][0]).$v['value'][1];
2208 }
2209 }
2210 }
2211 }
2212 }
2213
2214 // Return output buffer:
2215 return $output;
2216 }
2217
2218 /**
2219 * Compile field definition
2220 *
2221 * @param array Field definition parts
2222 * @return string Field definition string
2223 */
2224 function compileFieldCfg($fieldCfg) {
2225
2226 // Set type:
2227 $cfg = $fieldCfg['fieldType'];
2228
2229 // Add value, if any:
2230 if (strlen($fieldCfg['value'])) {
2231 $cfg.='('.$fieldCfg['value'].')';
2232 }
2233
2234 // Add additional features:
2235 if (is_array($fieldCfg['featureIndex'])) {
2236 foreach($fieldCfg['featureIndex'] as $featureDef) {
2237 $cfg.=' '.$featureDef['keyword'];
2238
2239 // Add value if found:
2240 if (is_array($featureDef['value'])) {
2241 $cfg.=' '.$featureDef['value'][1].$this->compileAddslashes($featureDef['value'][0]).$featureDef['value'][1];
2242 }
2243 }
2244 }
2245
2246 // Return field definition string:
2247 return $cfg;
2248 }
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260 /*************************
2261 *
2262 * Debugging
2263 *
2264 *************************/
2265
2266 /**
2267 * Check parsability of input SQL part string; Will parse and re-compile after which it is compared
2268 *
2269 * @param string Part definition of string; "SELECT" = fieldlist (also ORDER BY and GROUP BY), "FROM" = table list, "WHERE" = Where clause.
2270 * @param string SQL string to verify parsability of
2271 * @return mixed Returns array with string 1 and 2 if error, otherwise false
2272 */
2273 function debug_parseSQLpart($part,$str) {
2274 switch($part) {
2275 case 'SELECT':
2276 return $this->debug_parseSQLpartCompare($str,$this->compileFieldList($this->parseFieldList($str)));
2277 break;
2278 case 'FROM':
2279 return $this->debug_parseSQLpartCompare($str,$this->compileFromTables($this->parseFromTables($str)));
2280 break;
2281 case 'WHERE':
2282 return $this->debug_parseSQLpartCompare($str,$this->compileWhereClause($this->parseWhereClause($str)));
2283 break;
2284 }
2285 }
2286
2287 /**
2288 * Compare two query strins by stripping away whitespace.
2289 *
2290 * @param string SQL String 1
2291 * @param string SQL string 2
2292 * @param boolean If true, the strings are compared insensitive to case
2293 * @return mixed Returns array with string 1 and 2 if error, otherwise false
2294 */
2295 function debug_parseSQLpartCompare($str,$newStr,$caseInsensitive=FALSE) {
2296 if ($caseInsensitive) {
2297 $str1 = strtoupper($str);
2298 $str2 = strtoupper($newStr);
2299 } else {
2300 $str1 = $str;
2301 $str2 = $newStr;
2302 }
2303
2304 // Fixing escaped chars:
2305 $search = array('\0', '\n', '\r', '\Z');
2306 $replace = array("\x00", "\x0a", "\x0d", "\x1a");
2307 $str1 = str_replace($search, $replace, $str1);
2308 $str2 = str_replace($search, $replace, $str2);
2309
2310 # Normally, commented out since they are needed only in tricky cases...
2311 # $str1 = stripslashes($str1);
2312 # $str2 = stripslashes($str2);
2313
2314 if (strcmp(ereg_replace('[[:space:]]','',$this->trimSQL($str1)),ereg_replace('[[:space:]]','',$this->trimSQL($str2)))) {
2315 return array(
2316 ereg_replace('[[:space:]]+',' ',$str),
2317 ereg_replace('[[:space:]]+',' ',$newStr),
2318 );
2319 }
2320 }
2321
2322 /**
2323 * 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
2324 *
2325 * @param string SQL query
2326 * @return string Query if all is well, otherwise exit.
2327 */
2328 function debug_testSQL($SQLquery) {
2329 # return $SQLquery;
2330 #debug(array($SQLquery));
2331
2332 // Getting result array:
2333 $parseResult = $this->parseSQL($SQLquery);
2334
2335 // If result array was returned, proceed. Otherwise show error and exit.
2336 if (is_array($parseResult)) {
2337
2338 // Re-compile query:
2339 $newQuery = $this->compileSQL($parseResult);
2340
2341 // TEST the new query:
2342 $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);
2343
2344 // Return new query if OK, otherwise show error and exit:
2345 if (!is_array($testResult)) {
2346 return $newQuery;
2347 } else {
2348 debug(array('ERROR MESSAGE'=>'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult),'SQL parsing failed:');
2349 exit;
2350 }
2351 } else {
2352 debug(array('query' => $SQLquery, 'ERROR MESSAGE'=>$parseResult),'SQL parsing failed:');
2353 exit;
2354 }
2355 }
2356
2357 /**
2358 * Returns the result set (in array) as HTML table. For debugging.
2359 *
2360 * @param array Result set array (array of rows)
2361 * @return string HTML table
2362 */
2363 function debug_printResultSet($array) {
2364
2365 if (count($array)) {
2366 $tRows=array();
2367 $fields = array_keys(current($array));
2368 $tCell[]='
2369 <td>IDX</td>';
2370 foreach($fields as $fieldName) {
2371 $tCell[]='
2372 <td>'.htmlspecialchars($fieldName).'</td>';
2373 }
2374 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
2375
2376
2377 foreach($array as $index => $rec) {
2378
2379 $tCell=array();
2380 $tCell[]='
2381 <td>'.htmlspecialchars($index).'</td>';
2382 foreach($fields as $fieldName) {
2383 $tCell[]='
2384 <td>'.htmlspecialchars($rec[$fieldName]).'</td>';
2385 }
2386 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
2387 }
2388
2389 return '<table border="1">'.implode('',$tRows).'</table>';
2390 } else 'Empty resultset';
2391 }
2392 }
2393
2394
2395 /**
2396 * PHP SQL engine, result object
2397 *
2398 * @author Kasper Skaarhoj <kasper@typo3.com>
2399 * @package TYPO3
2400 * @subpackage t3lib
2401 */
2402 class t3lib_sqlengine_resultobj {
2403
2404 // Result array, must contain the fields in the order they were selected in the SQL statement (for sql_fetch_row())
2405 var $result = array();
2406
2407 var $TYPO3_DBAL_handlerType = '';
2408 var $TYPO3_DBAL_tableList = '';
2409
2410
2411 /**
2412 * Counting number of rows
2413 *
2414 * @return integer
2415 */
2416 function sql_num_rows() {
2417 return count($this->result);
2418 }
2419
2420 /**
2421 * Fetching next row in result array
2422 *
2423 * @return array Associative array
2424 */
2425 function sql_fetch_assoc() {
2426 $row = current($this->result);
2427 next($this->result);
2428 return $row;
2429 }
2430
2431 /**
2432 * Fetching next row, numerical indices
2433 *
2434 * @return array Numerical array
2435 */
2436 function sql_fetch_row() {
2437 $resultRow = $this->sql_fetch_assoc();
2438
2439 if (is_array($resultRow)) {
2440 $numArray = array();
2441 foreach($resultRow as $value) {
2442 $numArray[]=$value;
2443 }
2444 return $numArray;
2445 }
2446 }
2447
2448 /**
2449 * Seeking position in result
2450 *
2451 * @param integer Position pointer.
2452 * @return boolean Returns true on success
2453 */
2454 function sql_data_seek($pointer) {
2455 reset($this->result);
2456 for ($a=0;$a<$pointer;$a++) {
2457 next($this->result);
2458 }
2459 return TRUE;
2460 }
2461
2462 /**
2463 * [Describe function...]
2464 *
2465 * @return [type] ...
2466 */
2467 function sql_field_type() {
2468 return '';
2469 }
2470 }
2471
2472
2473
2474 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']) {
2475 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']);
2476 }
2477 ?>