* Added Karsten D.s patches for DBAL.
[Packages/TYPO3.CMS.git] / t3lib / class.t3lib_sqlengine.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 * A copy is found in the textfile GPL.txt and important notices to the license
17 * from the author is found in LICENSE.txt distributed with these scripts.
18 *
19 *
20 * This script is distributed in the hope that it will be useful,
21 * but WITHOUT ANY WARRANTY; without even the implied warranty of
22 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 * GNU General Public License for more details.
24 *
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
27 /**
28 * PHP SQL engine
29 *
30 * $Id$
31 *
32 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
33 */
34 /**
35 * [CLASS/FUNCTION INDEX of SCRIPT]
36 *
37 *
38 *
39 * 102: class t3lib_sqlengine extends t3lib_sqlparser
40 * 124: function init($config, &$pObj)
41 * 132: function resetStatusVars()
42 * 145: function processAccordingToConfig(&$value,$fInfo)
43 *
44 * SECTION: SQL queries
45 * 200: function exec_INSERTquery($table,$fields_values)
46 * 267: function exec_UPDATEquery($table,$where,$fields_values)
47 * 326: function exec_DELETEquery($table,$where)
48 * 377: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit)
49 * 420: function sql_query($query)
50 * 431: function sql_error()
51 * 440: function sql_insert_id()
52 * 449: function sql_affected_rows()
53 * 459: function quoteStr($str)
54 *
55 * SECTION: SQL admin functions
56 * 484: function admin_get_tables()
57 * 495: function admin_get_fields($tableName)
58 * 506: function admin_get_keys($tableName)
59 * 517: function admin_query($query)
60 *
61 * SECTION: Data Source I/O
62 * 542: function readDataSource($table)
63 * 554: function saveDataSource($table)
64 *
65 * SECTION: SQL engine functions
66 * 583: function selectFromData($table,$where)
67 * 619: function select_evalSingle($table,$config,&$itemKeys)
68 * 740: function getResultSet($keys, $table, $fieldList)
69 *
70 * SECTION: Debugging
71 * 785: function debug_printResultSet($array)
72 *
73 *
74 * 824: class t3lib_sqlengine_resultobj
75 * 838: function sql_num_rows()
76 * 847: function sql_fetch_assoc()
77 * 858: function sql_fetch_row()
78 * 876: function sql_data_seek($pointer)
79 * 889: function sql_field_type()
80 *
81 * TOTAL FUNCTIONS: 27
82 * (This index is automatically created/updated by the extension "extdeveval")
83 *
84 */
85
86
87
88
89
90
91 require_once(PATH_t3lib.'class.t3lib_sqlparser.php');
92
93
94 /**
95 * PHP SQL engine / server
96 * Some parts are experimental for now.
97 *
98 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
99 * @package TYPO3
100 * @subpackage t3lib
101 */
102 class t3lib_sqlengine extends t3lib_sqlparser {
103
104 // array with data records: [table name][num.index] = records
105 var $data = array(); // Data source storage
106
107
108 // Internal, SQL Status vars:
109 var $errorStatus = ''; // Set with error message of last operation
110 var $lastInsertedId = 0; // Set with last inserted unique ID
111 var $lastAffectedRows = 0; // Set with last number of affected rows.
112
113
114
115
116
117 /**
118 * Dummy function for initializing SQL handler. Create you own in derived classes.
119 *
120 * @param array Configuration array from handler
121 * @param object Parent object
122 * @return void
123 */
124 function init($config, &$pObj) {
125 }
126
127 /**
128 * Reset SQL engine status variables (insert id, affected rows, error status)
129 *
130 * @return void
131 */
132 function resetStatusVars() {
133 $this->errorStatus = '';
134 $this->lastInsertedId = 0;
135 $this->lastAffectedRows = 0;
136 }
137
138 /**
139 * Processing of update/insert values based on field type.
140 *
141 * @param [type] $$value: ...
142 * @param [type] $fInfo: ...
143 * @return [type] ...
144 */
145 function processAccordingToConfig(&$value,$fInfo) {
146 $options = $this->parseFieldDef($fInfo['Type']);
147
148 switch(strtolower($options['fieldType'])) {
149 case 'int':
150 case 'smallint':
151 case 'tinyint':
152 case 'mediumint':
153 $value = intval($value);
154 if ($options['featureIndex']['UNSIGNED']) {
155 $value = t3lib_div::intInRange($value,0);
156 }
157 break;
158 case 'double':
159 $value = (double)$value;
160 break;
161 case 'varchar':
162 case 'char':
163 $value = substr($value,0,trim($options['value']));
164 break;
165 case 'text':
166 case 'blob':
167 $value = substr($value,0,65536);
168 break;
169 case 'tinytext':
170 case 'tinyblob':
171 $value = substr($value,0,256);
172 break;
173 case 'mediumtext':
174 case 'mediumblob':
175 // ??
176 break;
177 }
178 }
179
180
181
182
183
184
185
186 /********************************
187 *
188 * SQL queries
189 * This is the SQL access functions used when this class is instantiated as a SQL handler with DBAL. Override these in derived classes.
190 *
191 ********************************/
192
193 /**
194 * Execute an INSERT query
195 *
196 * @param string Table name
197 * @param array Field values as key=>value pairs.
198 * @return boolean TRUE on success and FALSE on failure (error is set internally)
199 */
200 function exec_INSERTquery($table,$fields_values) {
201
202 // Initialize
203 $this->resetStatusVars();
204
205 // Reading Data Source if not done already.
206 $this->readDataSource($table);
207
208 // If data source is set:
209 if (is_array($this->data[$table])) {
210
211 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
212
213 // Looking for unique keys:
214 $saveArray = array();
215 foreach($fieldInformation as $fInfo) {
216
217 // Field name:
218 $fN = $fInfo['Field'];
219
220 // Set value:
221 $saveArray[$fN] = isset($fields_values[$fN]) ? $fields_values[$fN] : $options['Default'];
222
223 // Process value:
224 $this->processAccordingToConfig($saveArray[$fN], $fInfo);
225
226 // If an auto increment field is found, find the largest current uid:
227 if ($fInfo['Extra'] == 'auto_increment') {
228
229 // Get all UIDs:
230 $uidArray = array();
231 foreach($this->data[$table] as $r) {
232 $uidArray[] = $r[$fN];
233 }
234
235 // If current value is blank or already in array, we create a new:
236 if (!$saveArray[$fN] || in_array(intval($saveArray[$fN]), $uidArray)) {
237 if (count($uidArray)) {
238 $saveArray[$fN] = max($uidArray)+1;
239 } else $saveArray[$fN] = 1;
240 }
241
242 // Update "last inserted id":
243 $this->lastInsertedId = $saveArray[$fN];
244 }
245 }
246 #debug(array($fields_values,$saveArray));
247 // Insert row in table:
248 $this->data[$table][] = $saveArray;
249
250 // Save data source
251 $this->saveDataSource($table);
252
253 return TRUE;
254 } else $this->errorStatus = 'No data loaded.';
255
256 return FALSE;
257 }
258
259 /**
260 * Execute UPDATE query on table
261 *
262 * @param string Table name
263 * @param string WHERE clause
264 * @param array Field values as key=>value pairs.
265 * @return boolean TRUE on success and FALSE on failure (error is set internally)
266 */
267 function exec_UPDATEquery($table,$where,$fields_values) {
268
269 // Initialize:
270 $this->resetStatusVars();
271
272 // Reading Data Source if not done already.
273 $this->readDataSource($table);
274
275 // If anything is there:
276 if (is_array($this->data[$table])) {
277
278 // Parse WHERE clause:
279 $where = $this->parseWhereClause($where);
280
281 if (is_array($where)) {
282
283 // Field information
284 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
285
286 // Traverse fields to update:
287 foreach($fields_values as $fName => $fValue) {
288 $this->processAccordingToConfig($fields_values[$fName],$fieldInformation[$fName]);
289 }
290 #debug($fields_values);
291 // Do query, returns array with keys to the data array of the result:
292 $itemKeys = $this->selectFromData($table,$where);
293
294 // Set "last affected rows":
295 $this->lastAffectedRows = count($itemKeys);
296
297 // Update rows:
298 if ($this->lastAffectedRows) {
299 // Traverse result set here:
300 foreach($itemKeys as $dataArrayKey) {
301
302 // Traverse fields to update:
303 foreach($fields_values as $fName => $fValue) {
304 $this->data[$table][$dataArrayKey][$fName] = $fValue;
305 }
306 }
307
308 // Save data source
309 $this->saveDataSource($table);
310 }
311
312 return TRUE;
313 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
314 } else $this->errorStatus = 'No data loaded.';
315
316 return FALSE;
317 }
318
319 /**
320 * Execute DELETE query
321 *
322 * @param string Table to delete from
323 * @param string WHERE clause
324 * @return boolean TRUE on success and FALSE on failure (error is set internally)
325 */
326 function exec_DELETEquery($table,$where) {
327
328 // Initialize:
329 $this->resetStatusVars();
330
331 // Reading Data Source if not done already.
332 $this->readDataSource($table);
333
334 // If anything is there:
335 if (is_array($this->data[$table])) {
336
337 // Parse WHERE clause:
338 $where = $this->parseWhereClause($where);
339
340 if (is_array($where)) {
341
342 // Do query, returns array with keys to the data array of the result:
343 $itemKeys = $this->selectFromData($table,$where);
344
345 // Set "last affected rows":
346 $this->lastAffectedRows = count($itemKeys);
347
348 // Remove rows:
349 if ($this->lastAffectedRows) {
350 // Traverse result set:
351 foreach($itemKeys as $dataArrayKey) {
352 unset($this->data[$table][$dataArrayKey]);
353 }
354
355 // Saving data source
356 $this->saveDataSource($table);
357 }
358
359 return TRUE;
360 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
361 } else $this->errorStatus = 'No data loaded.';
362
363 return FALSE;
364 }
365
366 /**
367 * Execute SELECT query
368 *
369 * @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
370 * @param string Table(s) from which to select. This is what comes right after "FROM ...". Required value.
371 * @param string Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
372 * @param string Optional GROUP BY field(s), if none, supply blank string.
373 * @param string Optional ORDER BY field(s), if none, supply blank string.
374 * @param string Optional LIMIT value ([begin,]max), if none, supply blank string.
375 * @return object Returns result object, but if errors, returns false
376 */
377 function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit) {
378
379 // Initialize:
380 $this->resetStatusVars();
381
382 // Create result object
383 $sqlObj = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
384 $sqlObj->result = array(); // Empty result as a beginning
385
386 // Get table list:
387 $tableArray = $this->parseFromTables($from_table);
388 $table = $tableArray[0]['table'];
389
390 // Reading Data Source if not done already.
391 $this->readDataSource($table);
392
393 // If anything is there:
394 if (is_array($this->data[$table])) {
395
396 // Parse WHERE clause:
397 $where = $this->parseWhereClause($where_clause);
398 if (is_array($where)) {
399
400 // Do query, returns array with keys to the data array of the result:
401 $itemKeys = $this->selectFromData($table,$where);
402
403 // Finally, read the result rows into this variable:
404 $sqlObj->result = $this->getResultSet($itemKeys,$table,'*');
405 // Reset and return result:
406 reset($sqlObj->result);
407 return $sqlObj;
408 } else $this->errorStatus = 'WHERE clause contained errors: '.$where;
409 } else $this->errorStatus = 'No data loaded: '.$this->errorStatus;
410
411 return FALSE;
412 }
413
414 /**
415 * Performs an SQL query on the "database"
416 *
417 * @param string Query to execute
418 * @return object Result object or false if error
419 */
420 function sql_query($query) {
421 $res = t3lib_div::makeInstance('t3lib_sqlengine_resultobj');
422 $res->result = array();
423 return $res;
424 }
425
426 /**
427 * Returns most recent error
428 *
429 * @return string Error message, if any
430 */
431 function sql_error() {
432 return $this->errorStatus;
433 }
434
435 /**
436 * Returns most recently create unique ID (of INSERT queries)
437 *
438 * @return integer Last unique id created.
439 */
440 function sql_insert_id() {
441 return $this->lastInsertedId;
442 }
443
444 /**
445 * Returns affected rows (of UPDATE and DELETE queries)
446 *
447 * @return integer Last amount of affected rows.
448 */
449 function sql_affected_rows() {
450 return $this->lastAffectedRows;
451 }
452
453 /**
454 * Quoting strings for insertion in SQL queries
455 *
456 * @param string String
457 * @return string String, with quotes escaped
458 */
459 function quoteStr($str) {
460 return addslashes($str);
461 }
462
463
464
465
466
467
468
469
470
471
472 /**************************************
473 *
474 * SQL admin functions
475 * (For use in the Install Tool and Extension Manager)
476 *
477 **************************************/
478
479 /**
480 * (DUMMY) Returns the list of tables from the database
481 *
482 * @return array Tables in an array (tablename is in both key and value)
483 */
484 function admin_get_tables() {
485 $whichTables = array();
486 return $whichTables;
487 }
488
489 /**
490 * (DUMMY) Returns information about each field in the $table
491 *
492 * @param string Table name
493 * @return array Field information in an associative array with fieldname => field row
494 */
495 function admin_get_fields($tableName) {
496 $output = array();
497 return $output;
498 }
499
500 /**
501 * (DUMMY) Returns information about each index key in the $table
502 *
503 * @param string Table name
504 * @return array Key information in a numeric array
505 */
506 function admin_get_keys($tableName) {
507 $output = array();
508 return $output;
509 }
510
511 /**
512 * (DUMMY) mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
513 *
514 * @param string Query to execute
515 * @return pointer Result pointer
516 */
517 function admin_query($query) {
518 return $this->sql_query($query);
519 }
520
521
522
523
524
525
526
527
528 /********************************
529 *
530 * Data Source I/O
531 *
532 ********************************/
533
534 /**
535 * Dummy function for setting table data. Create your own.
536 * NOTICE: Handler to "table-locking" needs to be made probably!
537 *
538 * @param string Table name
539 * @return void
540 * @todo Table locking tools?
541 */
542 function readDataSource($table) {
543 $this->data[$table] = array();
544 }
545
546 /**
547 * Dummy function for setting table data. Create your own.
548 * NOTICE: Handler to "table-locking" needs to be made probably!
549 *
550 * @param string Table name
551 * @return void
552 * @todo Table locking tools?
553 */
554 function saveDataSource($table) {
555 debug($this->data[$table]);
556 }
557
558
559
560
561
562
563
564
565
566
567
568
569
570 /********************************
571 *
572 * SQL engine functions
573 *
574 ********************************/
575
576 /**
577 * [Describe function...]
578 *
579 * @param [type] $table: ...
580 * @param [type] $where: ...
581 * @return [type] ...
582 */
583 function selectFromData($table,$where) {
584
585 $output = array();
586 if (is_array($this->data[$table])) {
587
588 // All keys:
589 $OR_index = 0;
590
591 foreach($where as $config) {
592
593 if (strtoupper($config['operator'])=='OR') {
594 $OR_index++;
595 }
596
597 if (!isset($itemKeys[$OR_index])) $itemKeys[$OR_index] = array_keys($this->data[$table]);
598
599 $this->select_evalSingle($table,$config,$itemKeys[$OR_index]);
600 }
601
602 foreach($itemKeys as $uidKeys) {
603 $output = array_merge($output, $uidKeys);
604 }
605 $output = array_unique($output);
606 }
607
608 return $output;
609 }
610
611 /**
612 * [Describe function...]
613 *
614 * @param [type] $table: ...
615 * @param [type] $config: ...
616 * @param [type] $itemKeys: ...
617 * @return [type] ...
618 */
619 function select_evalSingle($table,$config,&$itemKeys) {
620 $neg = preg_match('/^AND[[:space:]]+NOT$/',trim($config['operator']));
621
622 if (is_array($config['sub'])) {
623 $subSelKeys = $this->selectFromData($table,$config['sub']);
624 if ($neg) {
625 foreach($itemKeys as $kk => $vv) {
626 if (in_array($vv,$subSelKeys)) {
627 unset($itemKeys[$kk]);
628 }
629 }
630 } else {
631 $itemKeys = array_intersect($itemKeys, $subSelKeys);
632 }
633 } else {
634 $comp = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$config['comparator']));
635 $mod = strtoupper($config['modifier']);
636 switch($comp) {
637 case 'NOTLIKE':
638 case 'LIKE':
639 $like_value = strtolower($config['value'][0]);
640 if (substr($like_value,0,1)=='%') {
641 $wildCard_begin = TRUE;
642 $like_value = substr($like_value,1);
643 }
644 if (substr($like_value,-1)=='%') {
645 $wildCard_end = TRUE;
646 $like_value = substr($like_value,0,-1);
647 }
648 break;
649 case 'NOTIN':
650 case 'IN':
651 $in_valueArray = array();
652 foreach($config['value'] as $vParts) {
653 $in_valueArray[] = (string)$vParts[0];
654 }
655 break;
656 }
657
658 foreach($itemKeys as $kk => $v) {
659 $field_value = $this->data[$table][$v][$config['field']];
660
661 // Calculate it:
662 if ($config['calc']=='&') {
663 $field_value&=intval($config['calc_value']);
664 }
665
666 // Compare it:
667 switch($comp) {
668 case '<=':
669 $bool = $field_value <= $config['value'][0];
670 break;
671 case '>=':
672 $bool = $field_value >= $config['value'][0];
673 break;
674 case '<':
675 $bool = $field_value < $config['value'][0];
676 break;
677 case '>':
678 $bool = $field_value > $config['value'][0];
679 break;
680 case '=':
681 $bool = !strcmp($field_value,$config['value'][0]);
682 break;
683 case '!=':
684 $bool = strcmp($field_value,$config['value'][0]);
685 break;
686 case 'NOTIN':
687 case 'IN':
688 $bool = in_array((string)$field_value, $in_valueArray);
689 if ($comp=='NOTIN') $bool = !$bool;
690 break;
691 case 'NOTLIKE':
692 case 'LIKE':
693 if (!strlen($like_value)) {
694 $bool = TRUE;
695 } elseif ($wildCard_begin && !$wildCard_end) {
696 $bool = !strcmp(substr(strtolower($field_value),-strlen($like_value)),$like_value);
697 } elseif (!$wildCard_begin && $wildCard_end) {
698 $bool = !strcmp(substr(strtolower($field_value),0,strlen($like_value)),$like_value);
699 } elseif ($wildCard_begin && $wildCard_end) {
700 $bool = strstr($field_value,$like_value);
701 } else {
702 $bool = !strcmp(strtolower($field_value),$like_value);
703 }
704 if ($comp=='NOTLIKE') $bool = !$bool;
705 break;
706 default:
707 $bool = $field_value ? TRUE : FALSE;
708 break;
709 }
710
711 // General negation:
712 if ($neg) $bool = !$bool;
713
714 // Modify?
715 switch($mod) {
716 case 'NOT':
717 case '!':
718 $bool = !$bool;
719 break;
720 }
721
722 // Action:
723 if (!$bool) {
724 unset($itemKeys[$kk]);
725 }
726 }
727
728 # echo $this->debug_printResultSet($this->getResultSet($itemKeys,$table,'uid,tstamp'));
729 }
730 }
731
732 /**
733 * [Describe function...]
734 *
735 * @param [type] $keys: ...
736 * @param [type] $table: ...
737 * @param [type] $fields: ...
738 * @return [type] ...
739 */
740 function getResultSet($keys, $table, $fieldList) {
741 $fields = t3lib_div::trimExplode(',',$fieldList);
742
743 $output = array();
744 foreach($keys as $kValue) {
745 if ($fieldList=='*') {
746 $output[$kValue] = $this->data[$table][$kValue];
747 } else {
748 foreach($fields as $fieldName) {
749 $output[$kValue][$fieldName] = $this->data[$table][$kValue][$fieldName];
750 }
751 }
752 }
753
754 return $output;
755 }
756
757
758
759
760
761 /*************************
762 *
763 * Debugging
764 *
765 *************************/
766
767 /**
768 * Returns the result set (in array) as HTML table. For debugging.
769 *
770 * @param array Result set array (array of rows)
771 * @return string HTML table
772 */
773 function debug_printResultSet($array) {
774
775 if (count($array)) {
776 $tRows=array();
777 $fields = array_keys(current($array));
778 $tCell[]='
779 <td>IDX</td>';
780 foreach($fields as $fieldName) {
781 $tCell[]='
782 <td>'.htmlspecialchars($fieldName).'</td>';
783 }
784 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
785
786
787 foreach($array as $index => $rec) {
788
789 $tCell=array();
790 $tCell[]='
791 <td>'.htmlspecialchars($index).'</td>';
792 foreach($fields as $fieldName) {
793 $tCell[]='
794 <td>'.htmlspecialchars($rec[$fieldName]).'</td>';
795 }
796 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
797 }
798
799 return '<table border="1">'.implode('',$tRows).'</table>';
800 } else 'Empty resultset';
801 }
802 }
803
804
805 /**
806 * PHP SQL engine, result object
807 *
808 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
809 * @package TYPO3
810 * @subpackage t3lib
811 */
812 class t3lib_sqlengine_resultobj {
813
814 // Result array, must contain the fields in the order they were selected in the SQL statement (for sql_fetch_row())
815 var $result = array();
816
817 var $TYPO3_DBAL_handlerType = '';
818 var $TYPO3_DBAL_tableList = '';
819
820
821 /**
822 * Counting number of rows
823 *
824 * @return integer
825 */
826 function sql_num_rows() {
827 return count($this->result);
828 }
829
830 /**
831 * Fetching next row in result array
832 *
833 * @return array Associative array
834 */
835 function sql_fetch_assoc() {
836 $row = current($this->result);
837 next($this->result);
838 return $row;
839 }
840
841 /**
842 * Fetching next row, numerical indices
843 *
844 * @return array Numerical array
845 */
846 function sql_fetch_row() {
847 $resultRow = $this->sql_fetch_assoc();
848
849 if (is_array($resultRow)) {
850 $numArray = array();
851 foreach($resultRow as $value) {
852 $numArray[]=$value;
853 }
854 return $numArray;
855 }
856 }
857
858 /**
859 * Seeking position in result
860 *
861 * @param integer Position pointer.
862 * @return boolean Returns true on success
863 */
864 function sql_data_seek($pointer) {
865 reset($this->result);
866 for ($a=0;$a<$pointer;$a++) {
867 next($this->result);
868 }
869 return TRUE;
870 }
871
872 /**
873 * [Describe function...]
874 *
875 * @return [type] ...
876 */
877 function sql_field_type() {
878 return '';
879 }
880 }
881
882
883
884 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']) {
885 include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_sqlengine.php']);
886 }
887 ?>