2 /***************************************************************
5 * (c) 2004-2006 Kasper Skaarhoj (kasperYYYY@typo3.com)
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.
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.
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.
25 * This copyright notice MUST APPEAR in all copies of the script!
26 ***************************************************************/
33 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
36 * [CLASS/FUNCTION INDEX of SCRIPT]
40 * 104: class t3lib_sqlengine extends t3lib_sqlparser
41 * 126: function init($config, &$pObj)
42 * 134: function resetStatusVars()
43 * 150: function processAccordingToConfig(&$value,$fInfo)
45 * SECTION: SQL queries
46 * 205: function exec_INSERTquery($table,$fields_values)
47 * 273: function exec_UPDATEquery($table,$where,$fields_values)
48 * 332: function exec_DELETEquery($table,$where)
49 * 383: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit)
50 * 426: function sql_query($query)
51 * 437: function sql_error()
52 * 446: function sql_insert_id()
53 * 455: function sql_affected_rows()
54 * 465: function quoteStr($str)
56 * SECTION: SQL admin functions
57 * 490: function admin_get_tables()
58 * 501: function admin_get_fields($tableName)
59 * 512: function admin_get_keys($tableName)
60 * 523: function admin_query($query)
62 * SECTION: Data Source I/O
63 * 548: function readDataSource($table)
64 * 560: function saveDataSource($table)
66 * SECTION: SQL engine functions (PHP simulation of SQL) - still experimental
67 * 590: function selectFromData($table,$where)
68 * 628: function select_evalSingle($table,$config,&$itemKeys)
69 * 747: function getResultSet($keys, $table, $fieldList)
72 * 790: function debug_printResultSet($array)
75 * 829: class t3lib_sqlengine_resultobj
76 * 843: function sql_num_rows()
77 * 852: function sql_fetch_assoc()
78 * 863: function sql_fetch_row()
79 * 881: function sql_data_seek($pointer)
80 * 894: function sql_field_type()
83 * (This index is automatically created/updated by the extension "extdeveval")
92 require_once(PATH_t3lib
.'class.t3lib_sqlparser.php');
96 * PHP SQL engine / server
97 * Basically this is trying to emulation SQL record selection by PHP, thus allowing SQL queries into alternative data storages managed by PHP.
100 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
104 class t3lib_sqlengine
extends t3lib_sqlparser
{
106 // array with data records: [table name][num.index] = records
107 var $data = array(); // Data source storage
110 // Internal, SQL Status vars:
111 var $errorStatus = ''; // Set with error message of last operation
112 var $lastInsertedId = 0; // Set with last inserted unique ID
113 var $lastAffectedRows = 0; // Set with last number of affected rows.
120 * Dummy function for initializing SQL handler. Create you own in derived classes.
122 * @param array Configuration array from handler
123 * @param object Parent object
126 function init($config, &$pObj) {
130 * Reset SQL engine status variables (insert id, affected rows, error status)
134 function resetStatusVars() {
135 $this->errorStatus
= '';
136 $this->lastInsertedId
= 0;
137 $this->lastAffectedRows
= 0;
141 * Processing of update/insert values based on field type.
143 * The input value is typecast and trimmed/shortened according to the field
144 * type and the configuration options from the $fInfo parameter.
146 * @param mixed $value The input value to process
147 * @param array $fInfo Field configuration data
148 * @return mixed The processed input value
150 function processAccordingToConfig(&$value,$fInfo) {
151 $options = $this->parseFieldDef($fInfo['Type']);
153 switch(strtolower($options['fieldType'])) {
158 $value = intval($value);
159 if ($options['featureIndex']['UNSIGNED']) {
160 $value = t3lib_div
::intInRange($value,0);
164 $value = (double)$value;
168 $value = substr($value,0,trim($options['value']));
172 $value = substr($value,0,65536);
176 $value = substr($value,0,256);
191 /********************************
194 * This is the SQL access functions used when this class is instantiated as a SQL handler with DBAL. Override these in derived classes.
196 ********************************/
199 * Execute an INSERT query
201 * @param string Table name
202 * @param array Field values as key=>value pairs.
203 * @return boolean TRUE on success and FALSE on failure (error is set internally)
205 function exec_INSERTquery($table,$fields_values) {
208 $this->resetStatusVars();
210 // Reading Data Source if not done already.
211 $this->readDataSource($table);
213 // If data source is set:
214 if (is_array($this->data
[$table])) {
216 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
218 // Looking for unique keys:
219 $saveArray = array();
220 foreach($fieldInformation as $fInfo) {
223 $fN = $fInfo['Field'];
226 // FIXME $options not defined
227 $saveArray[$fN] = isset($fields_values[$fN]) ?
$fields_values[$fN] : $options['Default'];
230 $this->processAccordingToConfig($saveArray[$fN], $fInfo);
232 // If an auto increment field is found, find the largest current uid:
233 if ($fInfo['Extra'] == 'auto_increment') {
237 foreach($this->data
[$table] as $r) {
238 $uidArray[] = $r[$fN];
241 // If current value is blank or already in array, we create a new:
242 if (!$saveArray[$fN] ||
in_array(intval($saveArray[$fN]), $uidArray)) {
243 if (count($uidArray)) {
244 $saveArray[$fN] = max($uidArray)+
1;
245 } else $saveArray[$fN] = 1;
248 // Update "last inserted id":
249 $this->lastInsertedId
= $saveArray[$fN];
253 // Insert row in table:
254 $this->data
[$table][] = $saveArray;
257 $this->saveDataSource($table);
260 } else $this->errorStatus
= 'No data loaded.';
266 * Execute UPDATE query on table
268 * @param string Table name
269 * @param string WHERE clause
270 * @param array Field values as key=>value pairs.
271 * @return boolean TRUE on success and FALSE on failure (error is set internally)
273 function exec_UPDATEquery($table,$where,$fields_values) {
276 $this->resetStatusVars();
278 // Reading Data Source if not done already.
279 $this->readDataSource($table);
281 // If anything is there:
282 if (is_array($this->data
[$table])) {
284 // Parse WHERE clause:
285 $where = $this->parseWhereClause($where);
287 if (is_array($where)) {
290 $fieldInformation = $this->admin_get_fields($table); // Should cache this...!
292 // Traverse fields to update:
293 foreach($fields_values as $fName => $fValue) {
294 $this->processAccordingToConfig($fields_values[$fName],$fieldInformation[$fName]);
297 // Do query, returns array with keys to the data array of the result:
298 $itemKeys = $this->selectFromData($table,$where);
300 // Set "last affected rows":
301 $this->lastAffectedRows
= count($itemKeys);
304 if ($this->lastAffectedRows
) {
305 // Traverse result set here:
306 foreach($itemKeys as $dataArrayKey) {
308 // Traverse fields to update:
309 foreach($fields_values as $fName => $fValue) {
310 $this->data
[$table][$dataArrayKey][$fName] = $fValue;
315 $this->saveDataSource($table);
319 } else $this->errorStatus
= 'WHERE clause contained errors: '.$where;
320 } else $this->errorStatus
= 'No data loaded.';
326 * Execute DELETE query
328 * @param string Table to delete from
329 * @param string WHERE clause
330 * @return boolean TRUE on success and FALSE on failure (error is set internally)
332 function exec_DELETEquery($table,$where) {
335 $this->resetStatusVars();
337 // Reading Data Source if not done already.
338 $this->readDataSource($table);
340 // If anything is there:
341 if (is_array($this->data
[$table])) {
343 // Parse WHERE clause:
344 $where = $this->parseWhereClause($where);
346 if (is_array($where)) {
348 // Do query, returns array with keys to the data array of the result:
349 $itemKeys = $this->selectFromData($table,$where);
351 // Set "last affected rows":
352 $this->lastAffectedRows
= count($itemKeys);
355 if ($this->lastAffectedRows
) {
356 // Traverse result set:
357 foreach($itemKeys as $dataArrayKey) {
358 unset($this->data
[$table][$dataArrayKey]);
361 // Saving data source
362 $this->saveDataSource($table);
366 } else $this->errorStatus
= 'WHERE clause contained errors: '.$where;
367 } else $this->errorStatus
= 'No data loaded.';
373 * Execute SELECT query
375 * @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
376 * @param string Table(s) from which to select. This is what comes right after "FROM ...". Required value.
377 * @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!
378 * @param string Optional GROUP BY field(s), if none, supply blank string.
379 * @param string Optional ORDER BY field(s), if none, supply blank string.
380 * @param string Optional LIMIT value ([begin,]max), if none, supply blank string.
381 * @return object Returns result object, but if errors, returns false
383 function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit) {
386 $this->resetStatusVars();
388 // Create result object
389 $sqlObj = t3lib_div
::makeInstance('t3lib_sqlengine_resultobj');
390 $sqlObj->result
= array(); // Empty result as a beginning
393 $tableArray = $this->parseFromTables($from_table);
394 $table = $tableArray[0]['table'];
396 // Reading Data Source if not done already.
397 $this->readDataSource($table);
399 // If anything is there:
400 if (is_array($this->data
[$table])) {
402 // Parse WHERE clause:
403 $where = $this->parseWhereClause($where_clause);
404 if (is_array($where)) {
406 // Do query, returns array with keys to the data array of the result:
407 $itemKeys = $this->selectFromData($table,$where);
409 // Finally, read the result rows into this variable:
410 $sqlObj->result
= $this->getResultSet($itemKeys,$table,'*');
411 // Reset and return result:
412 reset($sqlObj->result
);
414 } else $this->errorStatus
= 'WHERE clause contained errors: '.$where;
415 } else $this->errorStatus
= 'No data loaded: '.$this->errorStatus
;
421 * Performs an SQL query on the "database"
423 * @param string Query to execute
424 * @return object Result object or false if error
426 function sql_query($query) {
427 $res = t3lib_div
::makeInstance('t3lib_sqlengine_resultobj');
428 $res->result
= array();
433 * Returns most recent error
435 * @return string Error message, if any
437 function sql_error() {
438 return $this->errorStatus
;
442 * Returns most recently create unique ID (of INSERT queries)
444 * @return integer Last unique id created.
446 function sql_insert_id() {
447 return $this->lastInsertedId
;
451 * Returns affected rows (of UPDATE and DELETE queries)
453 * @return integer Last amount of affected rows.
455 function sql_affected_rows() {
456 return $this->lastAffectedRows
;
460 * Quoting strings for insertion in SQL queries
462 * @param string Input String
463 * @return string String, with quotes escaped
465 function quoteStr($str) {
466 return addslashes($str);
478 /**************************************
480 * SQL admin functions
481 * (For use in the Install Tool and Extension Manager)
483 **************************************/
486 * (DUMMY) Returns the list of tables from the database
488 * @return array Tables in an array (tablename is in both key and value)
490 function admin_get_tables() {
491 $whichTables = array();
496 * (DUMMY) Returns information about each field in the $table
498 * @param string Table name
499 * @return array Field information in an associative array with fieldname => field row
501 function admin_get_fields($tableName) {
507 * (DUMMY) Returns information about each index key in the $table
509 * @param string Table name
510 * @return array Key information in a numeric array
512 function admin_get_keys($tableName) {
518 * (DUMMY) mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
520 * @param string Query to execute
521 * @return pointer Result pointer
523 function admin_query($query) {
524 return $this->sql_query($query);
534 /********************************
538 ********************************/
541 * Dummy function for setting table data. Create your own.
542 * NOTICE: Handler to "table-locking" needs to be made probably!
544 * @param string Table name
546 * @todo Table locking tools?
548 function readDataSource($table) {
549 $this->data
[$table] = array();
553 * Dummy function for setting table data. Create your own.
554 * NOTICE: Handler to "table-locking" needs to be made probably!
556 * @param string Table name
558 * @todo Table locking tools?
560 function saveDataSource($table) {
561 debug($this->data
[$table]);
576 /********************************
578 * SQL engine functions (PHP simulation of SQL) - still experimental
580 ********************************/
583 * PHP simulation of SQL "SELECT"
586 * @param string Table name
587 * @param array Where clause parsed into array
588 * @return array Array of keys pointing to result rows in $this->data[$table]
590 function selectFromData($table,$where) {
593 if (is_array($this->data
[$table])) {
598 foreach($where as $config) {
600 if (strtoupper($config['operator'])=='OR') {
604 if (!isset($itemKeys[$OR_index])) $itemKeys[$OR_index] = array_keys($this->data
[$table]);
606 $this->select_evalSingle($table,$config,$itemKeys[$OR_index]);
609 foreach($itemKeys as $uidKeys) {
610 $output = array_merge($output, $uidKeys);
612 $output = array_unique($output);
619 * Evalutaion of a WHERE-clause-array.
622 * @param string Tablename
623 * @param array WHERE-configuration array
624 * @param array Data array to work on.
625 * @return void Data array passed by reference
626 * @see selectFromData()
628 function select_evalSingle($table,$config,&$itemKeys) {
629 $neg = preg_match('/^AND[[:space:]]+NOT$/',trim($config['operator']));
631 if (is_array($config['sub'])) {
632 $subSelKeys = $this->selectFromData($table,$config['sub']);
634 foreach($itemKeys as $kk => $vv) {
635 if (in_array($vv,$subSelKeys)) {
636 unset($itemKeys[$kk]);
640 $itemKeys = array_intersect($itemKeys, $subSelKeys);
643 $comp = strtoupper(str_replace(array(' ',"\t","\r","\n"),'',$config['comparator']));
644 $mod = strtoupper($config['modifier']);
648 $like_value = strtolower($config['value'][0]);
649 if (substr($like_value,0,1)=='%') {
650 $wildCard_begin = TRUE;
651 $like_value = substr($like_value,1);
653 if (substr($like_value,-1)=='%') {
654 $wildCard_end = TRUE;
655 $like_value = substr($like_value,0,-1);
660 $in_valueArray = array();
661 foreach($config['value'] as $vParts) {
662 $in_valueArray[] = (string)$vParts[0];
667 foreach($itemKeys as $kk => $v) {
668 $field_value = $this->data
[$table][$v][$config['field']];
671 if ($config['calc']=='&') {
672 $field_value&=intval($config['calc_value']);
678 $bool = $field_value <= $config['value'][0];
681 $bool = $field_value >= $config['value'][0];
684 $bool = $field_value < $config['value'][0];
687 $bool = $field_value > $config['value'][0];
690 $bool = !strcmp($field_value,$config['value'][0]);
693 $bool = strcmp($field_value,$config['value'][0]);
697 $bool = in_array((string)$field_value, $in_valueArray);
698 if ($comp=='NOTIN') $bool = !$bool;
702 if (!strlen($like_value)) {
704 } elseif ($wildCard_begin && !$wildCard_end) {
705 $bool = !strcmp(substr(strtolower($field_value),-strlen($like_value)),$like_value);
706 } elseif (!$wildCard_begin && $wildCard_end) {
707 $bool = !strcmp(substr(strtolower($field_value),0,strlen($like_value)),$like_value);
708 } elseif ($wildCard_begin && $wildCard_end) {
709 $bool = strstr($field_value,$like_value);
711 $bool = !strcmp(strtolower($field_value),$like_value);
713 if ($comp=='NOTLIKE') $bool = !$bool;
716 $bool = $field_value ?
TRUE : FALSE;
721 if ($neg) $bool = !$bool;
733 unset($itemKeys[$kk]);
740 * Returning result set based on result keys, table and field list
742 * @param array Result keys
743 * @param string Tablename
744 * @param string Fieldlist (commaseparated)
745 * @return array Result array with "rows"
747 function getResultSet($keys, $table, $fieldList) {
748 $fields = t3lib_div
::trimExplode(',',$fieldList);
751 foreach($keys as $kValue) {
752 if ($fieldList=='*') {
753 $output[$kValue] = $this->data
[$table][$kValue];
755 foreach($fields as $fieldName) {
756 $output[$kValue][$fieldName] = $this->data
[$table][$kValue][$fieldName];
778 /*************************
782 *************************/
785 * Returns the result set (in array) as HTML table. For debugging.
787 * @param array Result set array (array of rows)
788 * @return string HTML table
790 function debug_printResultSet($array) {
794 $fields = array_keys(current($array));
797 foreach($fields as $fieldName) {
799 <td>'.htmlspecialchars($fieldName).'</td>';
801 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
804 foreach($array as $index => $rec) {
808 <td>'.htmlspecialchars($index).'</td>';
809 foreach($fields as $fieldName) {
811 <td>'.htmlspecialchars($rec[$fieldName]).'</td>';
813 $tRows[]='<tr>'.implode('',$tCell).'</tr>';
816 return '<table border="1">'.implode('',$tRows).'</table>';
817 } else 'Empty resultset';
823 * PHP SQL engine, result object
825 * @author Kasper Skaarhoj <kasperYYYY@typo3.com>
829 class t3lib_sqlengine_resultobj
{
831 // Result array, must contain the fields in the order they were selected in the SQL statement (for sql_fetch_row())
832 var $result = array();
834 var $TYPO3_DBAL_handlerType = '';
835 var $TYPO3_DBAL_tableList = '';
839 * Counting number of rows
843 function sql_num_rows() {
844 return count($this->result
);
848 * Fetching next row in result array
850 * @return array Associative array
852 function sql_fetch_assoc() {
853 $row = current($this->result
);
859 * Fetching next row, numerical indices
861 * @return array Numerical array
863 function sql_fetch_row() {
864 $resultRow = $this->sql_fetch_assoc();
866 if (is_array($resultRow)) {
868 foreach($resultRow as $value) {
876 * Seeking position in result
878 * @param integer Position pointer.
879 * @return boolean Returns true on success
881 function sql_data_seek($pointer) {
882 reset($this->result
);
883 for ($a=0;$a<$pointer;$a++
) {
890 * Returning SQL field type
892 * @return string Blank string, not supported (it seems)
894 function sql_field_type() {
901 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE
]['XCLASS']['t3lib/class.t3lib_sqlengine.php']) {
902 include_once($TYPO3_CONF_VARS[TYPO3_MODE
]['XCLASS']['t3lib/class.t3lib_sqlengine.php']);