7acf61fe24d090c4fd85c471d158e0ed9fafc799
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / class.ux_t3lib_db.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2004-2009 Kasper Skaarhoj (kasperYYYY@typo3.com)
6 * (c) 2004-2009 Karsten Dambekalns <karsten@typo3.org>
7 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
8 * All rights reserved
9 *
10 * This script is part of the TYPO3 project. The TYPO3 project is
11 * free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * The GNU General Public License can be found at
17 * http://www.gnu.org/copyleft/gpl.html.
18 * A copy is found in the textfile GPL.txt and important notices to the license
19 * from the author is found in LICENSE.txt distributed with these scripts.
20 *
21 *
22 * This script is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 * GNU General Public License for more details.
26 *
27 * This copyright notice MUST APPEAR in all copies of the script!
28 ***************************************************************/
29 /**
30 * Contains a database abstraction layer class for TYPO3
31 *
32 * $Id$
33 *
34 * @author Kasper Skaarhoj <kasper@typo3.com>
35 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
36 * @author Xavier Perseguers <typo3@perseguers.ch>
37 */
38 /**
39 * [CLASS/FUNCTION INDEX of SCRIPT]
40 *
41 *
42 *
43 * 123: class ux_t3lib_DB extends t3lib_DB
44 * 169: function ux_t3lib_DB()
45 * 184: function initInternalVariables()
46 *
47 * SECTION: Query Building (Overriding parent methods)
48 * 217: function exec_INSERTquery($table,$fields_values)
49 * 275: function exec_UPDATEquery($table,$where,$fields_values)
50 * 334: function exec_DELETEquery($table,$where)
51 * 387: function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy = '',$orderBy = '',$limit = '')
52 *
53 * SECTION: Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
54 * 533: function SELECTquery($select_fields,$from_table,$where_clause,$groupBy = '',$orderBy = '',$limit = '')
55 * 556: function quoteSelectFields(&$select_fields)
56 * 573: function quoteFromTables(&$from_table)
57 * 595: function quoteWhereClause(&$where_clause)
58 * 620: function quoteGroupBy(&$groupBy)
59 * 637: function quoteOrderBy(&$orderBy)
60 *
61 * SECTION: Various helper functions
62 * 663: function quoteStr($str, $table)
63 *
64 * SECTION: SQL wrapper functions (Overriding parent methods)
65 * 707: function sql_error()
66 * 734: function sql_num_rows(&$res)
67 * 760: function sql_fetch_assoc(&$res)
68 * 808: function sql_fetch_row(&$res)
69 * 842: function sql_free_result(&$res)
70 * 868: function sql_insert_id()
71 * 893: function sql_affected_rows()
72 * 919: function sql_data_seek(&$res,$seek)
73 * 946: function sql_field_type(&$res,$pointer)
74 *
75 * SECTION: Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
76 * 987: function sql($db,$query)
77 * 999: function sql_query($query)
78 * 1035: function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
79 * 1055: function sql_select_db($TYPO3_db)
80 *
81 * SECTION: SQL admin functions
82 * 1086: function admin_get_tables()
83 * 1149: function admin_get_fields($tableName)
84 * 1210: function admin_get_keys($tableName)
85 * 1270: function admin_query($query)
86 *
87 * SECTION: Handler management
88 * 1333: function handler_getFromTableList($tableList)
89 * 1379: function handler_init($handlerKey)
90 *
91 * SECTION: Table/Field mapping
92 * 1488: function map_needMapping($tableList,$fieldMappingOnly = FALSE)
93 * 1524: function map_assocArray($input,$tables,$rev = FALSE)
94 * 1573: function map_remapSELECTQueryParts(&$select_fields,&$from_table,&$where_clause,&$groupBy,&$orderBy)
95 * 1615: function map_sqlParts(&$sqlPartArray, $defaultTable)
96 * 1650: function map_genericQueryParsed(&$parsedQuery)
97 * 1717: function map_fieldNamesInArray($table,&$fieldArray)
98 *
99 * SECTION: Debugging
100 * 1758: function debugHandler($function,$execTime,$inData)
101 * 1823: function debug_log($query,$ms,$data,$join,$errorFlag)
102 * 1849: function debug_explain($query)
103 *
104 * TOTAL FUNCTIONS: 41
105 * (This index is automatically created/updated by the extension "extdeveval")
106 *
107 */
108 /**
109 * TYPO3 database abstraction layer
110 *
111 * @author Kasper Skaarhoj <kasper@typo3.com>
112 * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
113 * @package TYPO3
114 * @subpackage tx_dbal
115 */
116 class ux_t3lib_DB extends t3lib_DB {
117
118 // Internal, static:
119 var $printErrors = FALSE; // Enable output of SQL errors after query executions. Set through TYPO3_CONF_VARS, see init()
120 var $debug = FALSE; // Enable debug mode. Set through TYPO3_CONF_VARS, see init()
121 var $conf = array(); // Configuration array, copied from TYPO3_CONF_VARS in constructor.
122
123 var $mapping = array(); // See manual.
124 var $table2handlerKeys = array(); // See manual.
125 var $handlerCfg = array( // See manual.
126 '_DEFAULT' => array(
127 'type' => 'native',
128 'config' => array(
129 'username' => '', // Set by default (overridden)
130 'password' => '', // Set by default (overridden)
131 'host' => '', // Set by default (overridden)
132 'database' => '', // Set by default (overridden)
133 'driver' => '', // ONLY "adodb" type; eg. "mysql"
134 'sequenceStart' => 1, // ONLY "adodb", first number in sequences/serials/...
135 'useNameQuote' => 0 // ONLY "adodb", whether to use NameQuote() method from ADOdb to quote names
136 )
137 ),
138 );
139
140
141 // Internal, dynamic:
142 var $handlerInstance = array(); // Contains instance of the handler objects as they are created. Exception is the native mySQL calls which are registered as an array with keys "handlerType" = "native" and "link" pointing to the link resource for the connection.
143 var $lastHandlerKey = ''; // Storage of the handler key of last ( SELECT) query - used for subsequent fetch-row calls etc.
144 var $lastQuery = ''; // Storage of last SELECT query
145 var $lastParsedAndMappedQueryArray = array(); // Query array, the last one parsed
146
147 var $resourceIdToTableNameMap = array(); // Mapping of resource ids to table names.
148
149 // Internal, caching:
150 var $cache_handlerKeyFromTableList = array(); // Caching handlerKeys for table lists
151 var $cache_mappingFromTableList = array(); // Caching mapping information for table lists
152 var $cache_autoIncFields = array(); // parsed SQL from standard DB dump file
153 var $cache_fieldType = array(); // field types for tables/fields
154 var $cache_primaryKeys = array(); // primary keys
155
156 /**
157 * SQL parser
158 *
159 * @var tx_dbal_sqlengine
160 */
161 var $SQLparser;
162
163 /**
164 * Installer
165 *
166 * @var t3lib_install
167 */
168 var $Installer;
169
170
171 /**
172 * Constructor.
173 * Creates SQL parser object and imports configuration from $TYPO3_CONF_VARS['EXTCONF']['dbal']
174 */
175 public function __construct() {
176 // Set SQL parser object for internal use:
177 $this->SQLparser = t3lib_div::makeInstance('tx_dbal_sqlengine');
178 $this->Installer = t3lib_div::makeInstance('t3lib_install');
179
180 // Set internal variables with configuration:
181 $this->conf = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
182 $this->initInternalVariables();
183 }
184
185 /**
186 * Setting internal variables from $this->conf.
187 *
188 * @return void
189 */
190 protected function initInternalVariables() {
191 // Set outside configuration:
192 if (isset($this->conf['mapping'])) {
193 $this->mapping = $this->conf['mapping'];
194 }
195 if (isset($this->conf['table2handlerKeys'])) {
196 $this->table2handlerKeys = $this->conf['table2handlerKeys'];
197 }
198 if (isset($this->conf['handlerCfg'])) {
199 $this->handlerCfg = $this->conf['handlerCfg'];
200 }
201
202 $this->cacheFieldInfo();
203 // Debugging settings:
204 $this->printErrors = $this->conf['debugOptions']['printErrors'] ? TRUE : FALSE;
205 $this->debug = $this->conf['debugOptions']['enabled'] ? TRUE : FALSE;
206 }
207
208 /**
209 * Clears the cached field information file.
210 *
211 * @return void
212 */
213 public function clearCachedFieldInfo() {
214 if (file_exists(PATH_typo3conf . 'temp_fieldInfo.php')) {
215 unlink(PATH_typo3conf . 'temp_fieldInfo.php');
216 }
217 }
218
219 /**
220 * Caches the field information.
221 *
222 * @return void
223 */
224 public function cacheFieldInfo() {
225 $extSQL = '';
226 $parsedExtSQL = array();
227
228 // try to fetch cached file first
229 // file is removed when admin_query() is called
230 if (file_exists(PATH_typo3conf . 'temp_fieldInfo.php')) {
231 $fdata = unserialize(t3lib_div::getUrl(PATH_typo3conf . 'temp_fieldInfo.php'));
232 $this->cache_autoIncFields = $fdata['incFields'];
233 $this->cache_fieldType = $fdata['fieldTypes'];
234 $this->cache_primaryKeys = $fdata['primaryKeys'];
235 } else {
236 // handle stddb.sql, parse and analyze
237 $extSQL = t3lib_div::getUrl(PATH_site . 't3lib/stddb/tables.sql');
238 $parsedExtSQL = $this->Installer->getFieldDefinitions_fileContent($extSQL);
239 $this->analyzeFields($parsedExtSQL);
240
241 // loop over all installed extensions
242 foreach ($GLOBALS['TYPO3_LOADED_EXT'] as $ext => $v) {
243 if (!is_array($v) || !isset($v['ext_tables.sql'])) {
244 continue;
245 }
246
247 // fetch db dump (if any) and parse it, then analyze
248 $extSQL = t3lib_div::getUrl($v['ext_tables.sql']);
249 $parsedExtSQL = $this->Installer->getFieldDefinitions_fileContent($extSQL);
250 $this->analyzeFields($parsedExtSQL);
251 }
252
253 $cachedFieldInfo = array('incFields' => $this->cache_autoIncFields, 'fieldTypes' => $this->cache_fieldType, 'primaryKeys' => $this->cache_primaryKeys);
254 $cachedFieldInfo = serialize($this->mapCachedFieldInfo($cachedFieldInfo));
255
256 // write serialized content to file
257 t3lib_div::writeFile(PATH_typo3conf . 'temp_fieldInfo.php', $cachedFieldInfo);
258
259 if (strcmp(t3lib_div::getUrl(PATH_typo3conf . 'temp_fieldInfo.php'), $cachedFieldInfo)) {
260 die('typo3temp/temp_incfields.php was NOT updated properly (written content didn\'t match file content) - maybe write access problem?');
261 }
262 }
263 }
264
265 /**
266 * Analyzes fields and adds the extracted information to the field type, auto increment and primary key info caches.
267 *
268 * @param array $parsedExtSQL The output produced by t3lib_install::getFieldDefinitions_fileContent()
269 * @return void
270 * @see t3lib_install::getFieldDefinitions_fileContent()
271 */
272 protected function analyzeFields($parsedExtSQL) {
273 foreach ($parsedExtSQL as $table => $tdef) {
274 if (is_array($tdef['fields'])) {
275 foreach ($tdef['fields'] as $field => $fdef) {
276 $fdef = $this->SQLparser->parseFieldDef($fdef);
277 $this->cache_fieldType[$table][$field]['type'] = $fdef['fieldType'];
278 $this->cache_fieldType[$table][$field]['metaType'] = $this->MySQLMetaType($fdef['fieldType']);
279 $this->cache_fieldType[$table][$field]['notnull'] = (isset($fdef['featureIndex']['NOTNULL']) && !$this->SQLparser->checkEmptyDefaultValue($fdef['featureIndex'])) ? 1 : 0;
280 if (isset($fdef['featureIndex']['DEFAULT'])) {
281 $default = $fdef['featureIndex']['DEFAULT']['value'][0];
282 if (isset($fdef['featureIndex']['DEFAULT']['value'][1])) {
283 $default = $fdef['featureIndex']['DEFAULT']['value'][1] . $default . $fdef['featureIndex']['DEFAULT']['value'][1];
284 }
285 $this->cache_fieldType[$table][$field]['default'] = $default;
286 }
287 if (isset($fdef['featureIndex']['AUTO_INCREMENT'])) {
288 $this->cache_autoIncFields[$table] = $field;
289 }
290 if (isset($tdef['keys']['PRIMARY'])) {
291 $this->cache_primaryKeys[$table] = substr($tdef['keys']['PRIMARY'], 13, -1);
292 }
293 }
294 }
295 }
296 }
297
298 /**
299 * This function builds all definitions for mapped tables and fields
300 * @see cacheFieldInfo()
301 */
302 protected function mapCachedFieldInfo($fieldInfo) {
303 if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'])) {
304 foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'] as $mappedTable => $mappedConf) {
305 if (array_key_exists($mappedTable, $fieldInfo['incFields'])) {
306 $mappedTableAlias = $mappedConf['mapTableName'];
307 if (isset($mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]])) {
308 $fieldInfo['incFields'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]];
309 } else {
310 $fieldInfo['incFields'][$mappedTableAlias] = $fieldInfo['incFields'][$mappedTable];
311 }
312 }
313
314 if (array_key_exists($mappedTable, $fieldInfo['fieldTypes'])) {
315 foreach ($fieldInfo['fieldTypes'][$mappedTable] as $field => $fieldConf) {
316 $tempMappedFieldConf[$mappedConf['mapFieldNames'][$field]] = $fieldConf;
317 }
318
319 $fieldInfo['fieldTypes'][$mappedConf['mapTableName']] = $tempMappedFieldConf;
320 }
321
322 if (array_key_exists($mappedTable, $fieldInfo['primaryKeys'])) {
323 $mappedTableAlias = $mappedConf['mapTableName'];
324 if (isset($mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]])) {
325 $fieldInfo['primaryKeys'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]];
326 } else {
327 $fieldInfo['primaryKeys'][$mappedTableAlias] = $fieldInfo['primaryKeys'][$mappedTable];
328 }
329 }
330 }
331 }
332
333 return $fieldInfo;
334 }
335
336
337 /************************************
338 *
339 * Query Building (Overriding parent methods)
340 * These functions are extending counterparts in the parent class.
341 *
342 **************************************/
343
344 /* From the ADOdb documentation, this is what we do (_Execute for SELECT, _query for the other actions)
345
346 Execute() is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead.
347 Both these functions share the same parameters as Execute().
348
349 If you do not have any bind parameters or your database supports binding (without emulation), then you can call _Execute() directly.
350 Calling this function bypasses bind emulation. Debugging is still supported in _Execute().
351
352 If you do not require debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query.
353 This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging, and recordset handling. Either
354 the resultid, TRUE or FALSE are returned by _query().
355 */
356
357 /**
358 * Inserts a record for $table from the array with field/value pairs $fields_values.
359 *
360 * @param string Table name
361 * @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
362 * @param mixed List/array of keys NOT to quote (eg. SQL functions)
363 * @return mixed Result from handler, usually TRUE when success and FALSE on failure
364 */
365 public function exec_INSERTquery($table, $fields_values, $no_quote_fields = '') {
366
367 if ($this->debug) {
368 $pt = t3lib_div::milliseconds();
369 }
370
371 // Do field mapping if needed:
372 $ORIG_tableName = $table;
373 if ($tableArray = $this->map_needMapping($table)) {
374
375 // Field mapping of array:
376 $fields_values = $this->map_assocArray($fields_values, $tableArray);
377
378 // Table name:
379 if ($this->mapping[$table]['mapTableName']) {
380 $table = $this->mapping[$table]['mapTableName'];
381 }
382 }
383 // Select API:
384 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
385 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
386 case 'native':
387 $this->lastQuery = $this->INSERTquery($table,$fields_values,$no_quote_fields);
388 if (is_string($this->lastQuery)) {
389 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
390 } else {
391 $sqlResult = mysql_query($this->lastQuery[0], $this->handlerInstance[$this->lastHandlerKey]['link']);
392 foreach ($this->lastQuery[1] as $field => $content) {
393 mysql_query('UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table) . ' WHERE ' . $this->quoteWhereClause($where), $this->handlerInstance[$this->lastHandlerKey]['link']);
394 }
395 }
396 break;
397 case 'adodb':
398 // auto generate ID for auto_increment fields if not present (static import needs this!)
399 // should we check the table name here (static_*)?
400 if (isset($this->cache_autoIncFields[$table])) {
401 if (isset($fields_values[$this->cache_autoIncFields[$table]])) {
402 $new_id = $fields_values[$this->cache_autoIncFields[$table]];
403 if ($table != 'tx_dbal_debuglog') {
404 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
405 }
406 } else {
407 $new_id = $this->handlerInstance[$this->lastHandlerKey]->GenID($table.'_'.$this->cache_autoIncFields[$table], $this->handlerInstance[$this->lastHandlerKey]->sequenceStart);
408 $fields_values[$this->cache_autoIncFields[$table]] = $new_id;
409 if ($table != 'tx_dbal_debuglog') {
410 $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
411 }
412 }
413 }
414
415 $this->lastQuery = $this->INSERTquery($table,$fields_values,$no_quote_fields);
416 if (is_string($this->lastQuery)) {
417 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
418 } else {
419 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
420 if (strlen($this->lastQuery[0])) {
421 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0],FALSE);
422 }
423 if (is_array($this->lastQuery[1])) {
424 foreach ($this->lastQuery[1] as $field => $content) {
425 if (empty($content)) continue;
426
427 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
428 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($this->cache_autoIncFields[$table].'='.$new_id));
429 } elseif (isset($this->cache_primaryKeys[$table])) {
430 $where = '';
431 $pks = explode(',', $this->cache_primaryKeys[$table]);
432 foreach ($pks as $pk) {
433 if (isset($fields_values[$pk]))
434 $where .= $pk.'='.$this->fullQuoteStr($fields_values[$pk], $table).' AND ';
435 }
436 $where = $this->quoteWhereClause($where.'1=1');
437 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$where);
438 } else {
439 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
440 die('Could not update BLOB >>>> no WHERE clause found!'); // should never ever happen
441 }
442 }
443 }
444 if (is_array($this->lastQuery[2])) {
445 foreach ($this->lastQuery[2] as $field => $content) {
446 if (empty($content)) continue;
447
448 if (isset($this->cache_autoIncFields[$table]) && isset($new_id)) {
449 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($this->cache_autoIncFields[$table].'='.$new_id));
450 } elseif (isset($this->cache_primaryKeys[$table])) {
451 $where = '';
452 $pks = explode(',', $this->cache_primaryKeys[$table]);
453 foreach ($pks as $pk) {
454 if (isset($fields_values[$pk]))
455 $where .= $pk.'='.$this->fullQuoteStr($fields_values[$pk], $table).' AND ';
456 }
457 $where = $this->quoteWhereClause($where.'1=1');
458 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$where);
459 } else {
460 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans(FALSE);
461 die('Could not update CLOB >>>> no WHERE clause found!'); // should never ever happen
462 }
463 }
464 }
465 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
466 }
467 break;
468 case 'userdefined':
469 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_INSERTquery($table,$fields_values,$no_quote_fields);
470 break;
471 }
472
473 if ($this->printErrors && $this->sql_error()) {
474 debug(array($this->lastQuery, $this->sql_error()));
475 }
476
477 if ($this->debug) {
478 $this->debugHandler(
479 'exec_INSERTquery',
480 t3lib_div::milliseconds()-$pt,
481 array(
482 'handlerType' => $hType,
483 'args' => array($table,$fields_values),
484 'ORIG_tablename' => $ORIG_tableName
485 )
486 );
487 }
488 // Return output:
489 return $sqlResult;
490 }
491
492 /**
493 * Updates a record from $table
494 *
495 * @param string Database tablename
496 * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
497 * @param array Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
498 * @param mixed List/array of keys NOT to quote (eg. SQL functions)
499 * @return mixed Result from handler, usually TRUE when success and FALSE on failure
500 */
501 public function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields = '') {
502 if ($this->debug) {
503 $pt = t3lib_div::milliseconds();
504 }
505
506 // Do table/field mapping:
507 $ORIG_tableName = $table;
508 if ($tableArray = $this->map_needMapping($table)) {
509
510 // Field mapping of array:
511 $fields_values = $this->map_assocArray($fields_values,$tableArray);
512
513 // Where clause table and field mapping:
514 $whereParts = $this->SQLparser->parseWhereClause($where);
515 $this->map_sqlParts($whereParts,$tableArray[0]['table']);
516 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
517
518 // Table name:
519 if ($this->mapping[$table]['mapTableName']) {
520 $table = $this->mapping[$table]['mapTableName'];
521 }
522 }
523
524 // Select API
525 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
526 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
527 case 'native':
528 $this->lastQuery = $this->UPDATEquery($table,$where,$fields_values,$no_quote_fields);
529 if (is_string($this->lastQuery)) {
530 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
531 }
532 else {
533 $sqlResult = mysql_query($this->lastQuery[0], $this->handlerInstance[$this->lastHandlerKey]['link']);
534 foreach ($this->lastQuery[1] as $field => $content) {
535 mysql_query('UPDATE '.$this->quoteFromTables($table).' SET '.$this->quoteFromTables($field).'='.$this->fullQuoteStr($content,$table).' WHERE '.$this->quoteWhereClause($where), $this->handlerInstance[$this->lastHandlerKey]['link']);
536 }
537 }
538 break;
539 case 'adodb':
540 $this->lastQuery = $this->UPDATEquery($table,$where,$fields_values,$no_quote_fields);
541 if (is_string($this->lastQuery)) {
542 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
543 } else {
544 $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
545 if (strlen($this->lastQuery[0])) {
546 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0],FALSE);
547 }
548 if (is_array($this->lastQuery[1])) {
549 foreach ($this->lastQuery[1] as $field => $content) {
550 $this->handlerInstance[$this->lastHandlerKey]->UpdateBlob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($where));
551 }
552 }
553 if (is_array($this->lastQuery[2])) {
554 foreach ($this->lastQuery[2] as $field => $content) {
555 $this->handlerInstance[$this->lastHandlerKey]->UpdateClob($this->quoteFromTables($table),$field,$content,$this->quoteWhereClause($where));
556 }
557 }
558 $this->handlerInstance[$this->lastHandlerKey]->CompleteTrans();
559 }
560 break;
561 case 'userdefined':
562 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields);
563 break;
564 }
565
566 if ($this->printErrors && $this->sql_error()) {
567 debug(array($this->lastQuery, $this->sql_error()));
568 }
569
570 if ($this->debug) {
571 $this->debugHandler(
572 'exec_UPDATEquery',
573 t3lib_div::milliseconds()-$pt,
574 array(
575 'handlerType' => $hType,
576 'args' => array($table,$where, $fields_values),
577 'ORIG_from_table' => $ORIG_tableName
578 )
579 );
580 }
581
582 // Return result:
583 return $sqlResult;
584 }
585
586 /**
587 * Deletes records from table
588 *
589 * @param string Database tablename
590 * @param string WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
591 * @return mixed Result from handler
592 */
593 public function exec_DELETEquery($table, $where) {
594 if ($this->debug) {
595 $pt = t3lib_div::milliseconds();
596 }
597
598 // Do table/field mapping:
599 $ORIG_tableName = $table;
600 if ($tableArray = $this->map_needMapping($table)) {
601
602 // Where clause:
603 $whereParts = $this->SQLparser->parseWhereClause($where);
604 $this->map_sqlParts($whereParts,$tableArray[0]['table']);
605 $where = $this->SQLparser->compileWhereClause($whereParts, FALSE);
606
607 // Table name:
608 if ($this->mapping[$table]['mapTableName']) {
609 $table = $this->mapping[$table]['mapTableName'];
610 }
611 }
612
613 // Select API
614 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
615 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
616 case 'native':
617 $this->lastQuery = $this->DELETEquery($table,$where);
618 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
619 break;
620 case 'adodb':
621 $this->lastQuery = $this->DELETEquery($table,$where);
622 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery,FALSE);
623 break;
624 case 'userdefined':
625 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_DELETEquery($table,$where);
626 break;
627 }
628
629 if ($this->printErrors && $this->sql_error()) {
630 debug(array($this->lastQuery, $this->sql_error()));
631 }
632
633 if ($this->debug) {
634 $this->debugHandler(
635 'exec_DELETEquery',
636 t3lib_div::milliseconds()-$pt,
637 array(
638 'handlerType' => $hType,
639 'args' => array($table,$where),
640 'ORIG_from_table' => $ORIG_tableName
641 )
642 );
643 }
644
645 // Return result:
646 return $sqlResult;
647 }
648
649 /**
650 * Selects records from Data Source
651 *
652 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
653 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
654 * @param string $where_clause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQquoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
655 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
656 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
657 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
658 * @return mixed Result from handler. Typically object from DBAL layers.
659 */
660 public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
661 if ($this->debug) {
662 $pt = t3lib_div::milliseconds();
663 }
664
665 // Map table / field names if needed:
666 $ORIG_tableName = $from_table; // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
667 if ($tableArray = $this->map_needMapping($ORIG_tableName)) {
668 $this->map_remapSELECTQueryParts($select_fields,$from_table,$where_clause,$groupBy,$orderBy); // Variables passed by reference!
669 }
670
671 // Get handler key and select API:
672 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
673 $hType = (string)$this->handlerCfg[$this->lastHandlerKey]['type'];
674 switch ($hType) {
675 case 'native':
676 $this->lastQuery = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
677 $sqlResult = mysql_query($this->lastQuery, $this->handlerInstance[$this->lastHandlerKey]['link']);
678 $this->resourceIdToTableNameMap[(string)$sqlResult] = $ORIG_tableName;
679 break;
680 case 'adodb':
681 if ($limit != '') {
682 $splitLimit = t3lib_div::intExplode(',', $limit); // Splitting the limit values:
683 if ($splitLimit[1]) { // If there are two parameters, do mapping differently than otherwise:
684 $numrows = $splitLimit[1];
685 $offset = $splitLimit[0];
686 } else {
687 $numrows = $splitLimit[0];
688 $offset = 0;
689 }
690
691 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy), $numrows, $offset);
692 $this->lastQuery = $sqlResult->sql;
693 } else {
694 $this->lastQuery = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy);
695 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_Execute($this->lastQuery);
696 }
697
698 $sqlResult->TYPO3_DBAL_handlerType = 'adodb'; // Setting handler type in result object (for later recognition!)
699 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
700 break;
701 case 'userdefined':
702 $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
703 if (is_object($sqlResult)) {
704 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined'; // Setting handler type in result object (for later recognition!)
705 $sqlResult->TYPO3_DBAL_tableList = $ORIG_tableName;
706 }
707 break;
708 }
709
710 if ($this->printErrors && $this->sql_error()) {
711 debug(array($this->lastQuery, $this->sql_error()));
712 }
713
714 if ($this->debug) {
715 $this->debugHandler(
716 'exec_SELECTquery',
717 t3lib_div::milliseconds()-$pt,
718 array(
719 'handlerType' => $hType,
720 'args' => array($from_table,$select_fields,$where_clause,$groupBy,$orderBy,$limit),
721 'ORIG_from_table' => $ORIG_tableName
722 )
723 );
724 }
725
726 // Return result handler.
727 return $sqlResult;
728 }
729
730
731
732 /**************************************
733 *
734 * Query building
735 *
736 **************************************/
737
738 /**
739 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
740 * Usage count/core: 4
741 *
742 * @param string See exec_INSERTquery()
743 * @param array See exec_INSERTquery()
744 * @param mixed See exec_INSERTquery()
745 * @return mixed Full SQL query for INSERT as string or array (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields
746 */
747 public function INSERTquery($table, $fields_values, $no_quote_fields = '') {
748 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
749 if (is_array($fields_values) && count($fields_values)) {
750
751 if (is_string($no_quote_fields)) {
752 $no_quote_fields = explode(',', $no_quote_fields);
753 } elseif (!is_array($no_quote_fields)) {
754 $no_quote_fields = array();
755 }
756
757 $blobfields = array();
758 $nArr = array();
759 foreach ($fields_values as $k => $v) {
760 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
761 // we skip the field in the regular INSERT statement, it is only in blobfields
762 $blobfields[$this->quoteFieldNames($k)] = $v;
763 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
764 // we skip the field in the regular INSERT statement, it is only in clobfields
765 $clobfields[$this->quoteFieldNames($k)] = $v;
766 } else {
767 // Add slashes old-school:
768 // cast numerical values
769 $mt = $this->sql_field_metatype($table, $k);
770 if ($mt{0} == 'I') {
771 $v = (int)$v;
772 } else if ($mt{0} == 'F') {
773 $v = (double)$v;
774 }
775
776 $nArr[$this->quoteFieldNames($k)] = (!in_array($k,$no_quote_fields)) ? $this->fullQuoteStr($v, $table) : $v;
777 }
778 }
779
780 if (count($blobfields) || count($clobfields)) {
781 if (count($nArr)) {
782 $query[0] = 'INSERT INTO ' . $this->quoteFromTables($table) . '
783 (
784 ' . implode(',
785 ', array_keys($nArr)) . '
786 ) VALUES (
787 ' . implode(',
788 ', $nArr) . '
789 )';
790 }
791 if (count($blobfields)) $query[1] = $blobfields;
792 if (count($clobfields)) $query[2] = $clobfields;
793 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query[0];
794 } else {
795 $query = 'INSERT INTO '.$this->quoteFromTables($table).'
796 (
797 ' . implode(',
798 ', array_keys($nArr)) . '
799 ) VALUES (
800 ' . implode(',
801 ', $nArr) . '
802 )';
803
804 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
805 }
806
807 return $query;
808 }
809 }
810
811 /**
812 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
813 * Usage count/core: 6
814 *
815 * @param string See exec_UPDATEquery()
816 * @param string See exec_UPDATEquery()
817 * @param array See exec_UPDATEquery()
818 * @param mixed See exec_UPDATEquery()
819 * @return mixed Full SQL query for UPDATE as string or array (unless $fields_values does not contain any elements in which case it will be FALSE). If BLOB fields will be affected and one is not running the native type, an array will be returned, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields
820 */
821 public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = '') {
822 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
823 if (is_string($where)) {
824 if (is_array($fields_values) && count($fields_values)) {
825
826 if (is_string($no_quote_fields)) {
827 $no_quote_fields = explode(',', $no_quote_fields);
828 } elseif (!is_array($no_quote_fields)) {
829 $no_quote_fields = array();
830 }
831
832 $blobfields = array();
833 $nArr = array();
834 foreach ($fields_values as $k => $v) {
835 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
836 // we skip the field in the regular UPDATE statement, it is only in blobfields
837 $blobfields[$this->quoteFieldNames($k)] = $v;
838 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
839 // we skip the field in the regular UPDATE statement, it is only in clobfields
840 $clobfields[$this->quoteFieldNames($k)] = $v;
841 } else {
842 // Add slashes old-school:
843 // cast numeric values
844 $mt = $this->sql_field_metatype($table, $k);
845 if ($mt{0} == 'I') {
846 $v = (int)$v;
847 } else if ($mt{0} == 'F') {
848 $v = (double)$v;
849 }
850 $nArr[] = $this->quoteFieldNames($k) . '=' . ((!in_array($k, $no_quote_fields)) ? $this->fullQuoteStr($v, $table) : $v);
851 }
852 }
853
854 if (count($blobfields) || count($clobfields)) {
855 if (count($nArr)) {
856 $query[0] = 'UPDATE '.$this->quoteFromTables($table).'
857 SET
858 '.implode(',
859 ',$nArr).
860 (strlen($where)>0 ? '
861 WHERE
862 '.$this->quoteWhereClause($where) : '');
863 }
864 if (count($blobfields)) $query[1] = $blobfields;
865 if (count($clobfields)) $query[2] = $clobfields;
866 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query[0];
867 } else {
868 $query = 'UPDATE '.$this->quoteFromTables($table).'
869 SET
870 '.implode(',
871 ',$nArr).
872 (strlen($where)>0 ? '
873 WHERE
874 '.$this->quoteWhereClause($where) : '');
875
876 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
877 }
878
879 return $query;
880 }
881 } else {
882 die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !');
883 }
884 }
885
886 /**
887 * Creates a DELETE SQL-statement for $table where $where-clause
888 * Usage count/core: 3
889 *
890 * @param string See exec_DELETEquery()
891 * @param string See exec_DELETEquery()
892 * @return string Full SQL query for DELETE
893 */
894 public function DELETEquery($table, $where) {
895 if (is_string($where)) {
896 $table = $this->quoteFromTables($table);
897 $where = $this->quoteWhereClause($where);
898
899 $query = parent::DELETEquery($table, $where);
900
901 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
902 return $query;
903 } else {
904 die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
905 }
906 }
907
908 /**
909 * Creates a SELECT SQL-statement
910 * Usage count/core: 11
911 *
912 * @param string See exec_SELECTquery()
913 * @param string See exec_SELECTquery()
914 * @param string See exec_SELECTquery()
915 * @param string See exec_SELECTquery()
916 * @param string See exec_SELECTquery()
917 * @param string See exec_SELECTquery()
918 * @return string Full SQL query for SELECT
919 */
920 public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
921 $select_fields = $this->quoteFieldNames($select_fields);
922 $from_table = $this->quoteFromTables($from_table);
923 $where_clause = $this->quoteWhereClause($where_clause);
924 $groupBy = $this->quoteGroupBy($groupBy);
925 $orderBy = $this->quoteOrderBy($orderBy);
926
927 // Call parent method to build actual query
928 $query = parent::SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
929
930 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
931
932 return $query;
933 }
934
935
936 /**************************************
937 *
938 * Functions for quoting table/field names
939 *
940 **************************************/
941
942 /**
943 * Quotes components of a SELECT subquery.
944 *
945 * @param array $components Array of SQL query components
946 * @return array
947 */
948 protected function quoteSELECTsubquery(array $components) {
949 $components['SELECT'] = $this->_quoteFieldNames($components['SELECT']);
950 $components['FROM'] = $this->_quoteFromTables($components['FROM']);
951 $components['WHERE'] = $this->_quoteWhereClause($components['WHERE']);
952 return $components;
953 }
954
955 /**
956 * Quotes field (and table) names with the quote character suitable for the DB being used
957 * Use quoteFieldNames instead!
958 *
959 * @param string List of fields to be selected from DB
960 * @return string Quoted list of fields to be selected from DB
961 * @deprecated since TYPO3 4.0
962 */
963 public function quoteSelectFields($select_fields) {
964 $this->quoteFieldNames($select_fields);
965 }
966
967 /**
968 * Quotes field (and table) names with the quote character suitable for the DB being used
969 *
970 * @param string List of fields to be used in query to DB
971 * @return string Quoted list of fields to be in query to DB
972 */
973 public function quoteFieldNames($select_fields) {
974 if ($select_fields == '') return '';
975 if ($this->runningNative()) return $select_fields;
976
977 $select_fields = $this->SQLparser->parseFieldList($select_fields);
978 $select_fields = $this->_quoteFieldNames($select_fields);
979
980 return $this->SQLparser->compileFieldList($select_fields);
981 }
982
983 /**
984 * Quotes field (and table) names in a SQL SELECT clause acccording to DB rules
985 *
986 * @param array $select_fields The parsed fields to quote
987 * @return array
988 * @see quoteFieldNames()
989 */
990 protected function _quoteFieldNames(array $select_fields) {
991 foreach ($select_fields as $k => $v) {
992 if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*') {
993 $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
994 }
995 if ($select_fields[$k]['table'] != '') {
996 $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
997 }
998 if ($select_fields[$k]['as'] != '') {
999 $select_fields[$k]['as'] = $this->quoteName($select_fields[$k]['as']);
1000 }
1001 if (isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*'){
1002 $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
1003 $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
1004 }
1005 }
1006
1007 return $select_fields;
1008 }
1009
1010 /**
1011 * Quotes table names with the quote character suitable for the DB being used
1012 *
1013 * @param string List of tables to be selected from DB
1014 * @return string Quoted list of tables to be selected from DB
1015 */
1016 public function quoteFromTables($from_table) {
1017 if ($from_table == '') return '';
1018 if ($this->runningNative()) return $from_table;
1019
1020 $from_table = $this->SQLparser->parseFromTables($from_table);
1021 $from_table = $this->_quoteFromTables($from_table);
1022 return $this->SQLparser->compileFromTables($from_table);
1023 }
1024
1025 /**
1026 * Quotes table names in a SQL FROM clause acccording to DB rules
1027 *
1028 * @param array $from_table The parsed FROM clause to quote
1029 * @return array
1030 * @see quoteFromTables()
1031 */
1032 protected function _quoteFromTables(array $from_table) {
1033 foreach ($from_table as $k => $v) {
1034 $from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
1035 if ($from_table[$k]['as'] != '') {
1036 $from_table[$k]['as'] = $this->quoteName($from_table[$k]['as']);
1037 }
1038 if (is_array($v['JOIN'])) {
1039 foreach ($v['JOIN'] as $joinCnt => $join) {
1040 $from_table[$k]['JOIN'][$joinCnt]['withTable'] = $this->quoteName($join['withTable']);
1041 $from_table[$k]['JOIN'][$joinCnt]['as'] = ($join['as']) ? $this->quoteName($join['as']) : '';
1042 $from_table[$k]['JOIN'][$joinCnt]['ON'][0]['table'] = ($join['ON'][0]['table']) ? $this->quoteName($join['ON'][0]['table']) : '';
1043 $from_table[$k]['JOIN'][$joinCnt]['ON'][0]['field'] = $this->quoteName($join['ON'][0]['field']);
1044 $from_table[$k]['JOIN'][$joinCnt]['ON'][1]['table'] = ($join['ON'][1]['table']) ? $this->quoteName($join['ON'][1]['table']) : '';
1045 $from_table[$k]['JOIN'][$joinCnt]['ON'][1]['field'] = $this->quoteName($join['ON'][1]['field']);
1046 }
1047 }
1048 }
1049
1050 return $from_table;
1051 }
1052
1053 /**
1054 * Quotes the field (and table) names within a where clause with the quote character suitable for the DB being used
1055 *
1056 * @param string A where clause that can e parsed by parseWhereClause
1057 * @return string Usable where clause with quoted field/table names
1058 */
1059 public function quoteWhereClause($where_clause) {
1060 if ($where_clause === '' || $this->runningNative()) return $where_clause;
1061
1062 $where_clause = $this->SQLparser->parseWhereClause($where_clause);
1063 if (is_array($where_clause)) {
1064 $where_clause = $this->_quoteWhereClause($where_clause);
1065 $where_clause = $this->SQLparser->compileWhereClause($where_clause);
1066 } else {
1067 die('Could not parse where clause in ' . __FILE__ . ' : ' . __LINE__);
1068 }
1069
1070 return $where_clause;
1071 }
1072
1073 /**
1074 * Quotes field names in a SQL WHERE clause acccording to DB rules
1075 *
1076 * @param array $where_clause The parsed WHERE clause to quote
1077 * @return array
1078 * @see quoteWhereClause()
1079 */
1080 protected function _quoteWhereClause(array $where_clause) {
1081 foreach ($where_clause as $k => $v) {
1082 // Look for sublevel:
1083 if (is_array($where_clause[$k]['sub'])) {
1084 $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
1085 } elseif (isset($v['func'])) {
1086 $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
1087 } else {
1088 if ($where_clause[$k]['table'] != '') {
1089 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1090 }
1091 if (!is_numeric($where_clause[$k]['field'])) {
1092 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1093 }
1094 if (isset($where_clause[$k]['calc_table'])) {
1095 if ($where_clause[$k]['calc_table'] != '') {
1096 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1097 }
1098 if ($where_clause[$k]['calc_field'] != '') {
1099 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1100 }
1101 }
1102 }
1103 if ($where_clause[$k]['comparator']) {
1104 if (isset($v['value']['operator'])) {
1105 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1106 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1107 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1108 }
1109 } else {
1110 // Detecting value type; list or plain:
1111 if (t3lib_div::inList('NOTIN,IN', strtoupper(str_replace(array(' ',"\n", "\r", "\t"), '', $where_clause[$k]['comparator'])))) {
1112 if (isset($v['subquery'])) {
1113 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1114 }
1115 } else {
1116 if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')) {
1117 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1118 }
1119 }
1120 }
1121 }
1122 }
1123
1124 return $where_clause;
1125 }
1126
1127 /**
1128 * [Describe function...]
1129 *
1130 * @param [type] $$groupBy: ...
1131 * @return [type] ...
1132 */
1133 protected function quoteGroupBy($groupBy) {
1134 if ($groupBy === '') return '';
1135 if ($this->runningNative()) return $groupBy;
1136
1137 $groupBy = $this->SQLparser->parseFieldList($groupBy);
1138 foreach ($groupBy as $k => $v) {
1139 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1140 if ($groupBy[$k]['table'] != '') {
1141 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1142 }
1143 }
1144 return $this->SQLparser->compileFieldList($groupBy);
1145 }
1146
1147 /**
1148 * [Describe function...]
1149 *
1150 * @param [type] $$orderBy: ...
1151 * @return [type] ...
1152 */
1153 protected function quoteOrderBy($orderBy) {
1154 if ($orderBy === '') return '';
1155 if ($this->runningNative()) return $orderBy;
1156
1157 $orderBy = $this->SQLparser->parseFieldList($orderBy);
1158 foreach ($orderBy as $k => $v) {
1159 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1160 if ($orderBy[$k]['table'] != '') {
1161 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1162 }
1163 }
1164 return $this->SQLparser->compileFieldList($orderBy);
1165 }
1166
1167
1168
1169 /**************************************
1170 *
1171 * Various helper functions
1172 *
1173 **************************************/
1174
1175 /**
1176 * Escaping and quoting values for SQL statements.
1177 *
1178 * @param string Input string
1179 * @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
1180 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1181 * @see quoteStr()
1182 */
1183 public function fullQuoteStr($str, $table) {
1184 return '\'' . $this->quoteStr($str, $table) . '\'';
1185 }
1186
1187 /**
1188 * Substitution for PHP function "addslashes()"
1189 * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
1190 *
1191 * @param string Input string
1192 * @param string Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
1193 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1194 * @see quoteStr()
1195 */
1196 public function quoteStr($str, $table) {
1197 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1198 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1199 case 'native':
1200 $str = mysql_real_escape_string($str, $this->handlerInstance[$this->lastHandlerKey]['link']);
1201 break;
1202 case 'adodb':
1203 $str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str), 1, -1);
1204 break;
1205 case 'userdefined':
1206 $str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
1207 break;
1208 default:
1209 die('No handler found!!!');
1210 break;
1211 }
1212
1213 return $str;
1214 }
1215
1216 /**
1217 * Quotes an object name (table name, field, ...)
1218 *
1219 * @param string Object's name
1220 * @param string Handler key
1221 * @param boolean If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
1222 * @return string Properly-quoted object's name
1223 */
1224 public function quoteName($name, $handlerKey = NULL, $useBackticks = FALSE) {
1225 $handlerKey = $handlerKey ? $handlerKey : $this->lastHandlerKey;
1226 $useNameQuote = isset($this->handlerCfg[$handlerKey]['config']['useNameQuote']) ? $this->handlerCfg[$handlerKey]['config']['useNameQuote'] : FALSE;
1227 if ($useNameQuote) {
1228 return $this->handlerInstance[$handlerKey]->DataDictionary->NameQuote($name);
1229 } else {
1230 $quote = $useBackticks ? '`' : $this->handlerInstance[$handlerKey]->nameQuote;
1231 return $quote . $name . $quote;
1232 }
1233 }
1234
1235 /**
1236 * Return MetaType for native field type (ADOdb only!)
1237 *
1238 * @param string native type as reported by admin_get_fields()
1239 * @param string Table name for which query type string. Important for detection of DBMS handler of the query!
1240 * @return string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1241 */
1242 public function MetaType($type, $table, $max_length = -1) {
1243 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1244 $str = '';
1245 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1246 case 'native':
1247 $str = $type;
1248 break;
1249 case 'adodb':
1250 if (in_array($table, $this->cache_fieldType)) {
1251 $rs = $this->handlerInstance[$this->lastHandlerKey]->SelectLimit('SELECT * FROM ' . $this->quoteFromTables($table), 1);
1252 $str = $rs->MetaType($type, $max_length);
1253 }
1254 break;
1255 case 'userdefined':
1256 $str = $this->handlerInstance[$this->lastHandlerKey]->MetaType($str,$table,$max_length);
1257 break;
1258 default:
1259 die('No handler found!!!');
1260 break;
1261 }
1262
1263 return $str;
1264 }
1265
1266
1267 /**
1268 * Return MetaType for native MySQL field type
1269 *
1270 * @param string native type as reported as in mysqldump files
1271 * @return string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1272 */
1273 public function MySQLMetaType($t) {
1274
1275 switch (strtoupper($t)) {
1276 case 'STRING':
1277 case 'CHAR':
1278 case 'VARCHAR':
1279 case 'TINYBLOB':
1280 case 'TINYTEXT':
1281 case 'ENUM':
1282 case 'SET': return 'C';
1283
1284 case 'TEXT':
1285 case 'LONGTEXT':
1286 case 'MEDIUMTEXT': return 'XL';
1287
1288 case 'IMAGE':
1289 case 'LONGBLOB':
1290 case 'BLOB':
1291 case 'MEDIUMBLOB': return 'B';
1292
1293 case 'YEAR':
1294 case 'DATE': return 'D';
1295
1296 case 'TIME':
1297 case 'DATETIME':
1298 case 'TIMESTAMP': return 'T';
1299
1300 case 'FLOAT':
1301 case 'DOUBLE': return 'F';
1302
1303 case 'INT':
1304 case 'INTEGER':
1305 case 'TINYINT':
1306 case 'SMALLINT':
1307 case 'MEDIUMINT':
1308 case 'BIGINT': return 'I8'; // we always return I8 to be on the safe side. Under some circumstances the fields are to small otherwise...
1309
1310 default: return 'N';
1311 }
1312 }
1313
1314 /**
1315 * Return actual MySQL type for meta field type
1316 *
1317 * @param string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1318 * @return string native type as reported as in mysqldump files, uppercase
1319 */
1320 public function MySQLActualType($meta) {
1321 switch (strtoupper($meta)) {
1322 case 'C': return 'VARCHAR';
1323 case 'XL':
1324 case 'X': return 'LONGTEXT';
1325
1326 case 'C2': return 'VARCHAR';
1327 case 'X2': return 'LONGTEXT';
1328
1329 case 'B': return 'LONGBLOB';
1330
1331 case 'D': return 'DATE';
1332 case 'T': return 'DATETIME';
1333 case 'L': return 'TINYINT';
1334
1335 case 'I':
1336 case 'I1':
1337 case 'I2':
1338 case 'I4':
1339 case 'I8': return 'BIGINT'; // we only have I8 in DBAL, see MySQLMetaType()
1340
1341 case 'F': return 'DOUBLE';
1342 case 'N': return 'NUMERIC';
1343
1344 default: return $meta;
1345 }
1346 }
1347
1348
1349
1350
1351 /**************************************
1352 *
1353 * SQL wrapper functions (Overriding parent methods)
1354 * (For use in your applications)
1355 *
1356 **************************************/
1357
1358 /**
1359 * Returns the error status on the most recent sql() execution (based on $this->lastHandlerKey)
1360 *
1361 * @return string Handler error strings
1362 */
1363 public function sql_error() {
1364 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1365 case 'native':
1366 $output = mysql_error($this->handlerInstance[$this->lastHandlerKey]['link']);
1367 break;
1368 case 'adodb':
1369 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorMsg();
1370 break;
1371 case 'userdefined':
1372 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_error();
1373 break;
1374 }
1375 return $output;
1376 }
1377
1378 /**
1379 * Returns the error number on the most recent sql() execution (based on $this->lastHandlerKey)
1380 *
1381 * @return int Handler error number
1382 */
1383 public function sql_errno() {
1384 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1385 case 'native':
1386 $output = mysql_errno($this->handlerInstance[$this->lastHandlerKey]['link']);
1387 break;
1388 case 'adodb':
1389 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorNo();
1390 break;
1391 case 'userdefined':
1392 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_errno();
1393 break;
1394 }
1395 return $output;
1396 }
1397
1398 /**
1399 * Returns the number of selected rows.
1400 *
1401 * @param pointer Result pointer / DBAL object
1402 * @return integer Number of resulting rows.
1403 */
1404 public function sql_num_rows(&$res) {
1405 if ($res === FALSE) return 0;
1406
1407 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1408 switch ($handlerType) {
1409 case 'native':
1410 $output = mysql_num_rows($res);
1411 break;
1412 case 'adodb':
1413 $output = method_exists($res, 'RecordCount') ? $res->RecordCount() : 0;
1414 break;
1415 case 'userdefined':
1416 $output = $res->sql_num_rows();
1417 break;
1418 }
1419 return $output;
1420 }
1421
1422 /**
1423 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
1424 *
1425 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1426 * @return array Associative array of result row.
1427 */
1428 public function sql_fetch_assoc(&$res) {
1429 $output = array();
1430
1431 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : (is_resource($res) ? 'native' : FALSE);
1432 switch ($handlerType) {
1433 case 'native':
1434 $output = mysql_fetch_assoc($res);
1435 $tableList = $this->resourceIdToTableNameMap[(string)$res]; // Reading list of tables from SELECT query:
1436 break;
1437 case 'adodb':
1438 // Check if method exists for the current $res object.
1439 // If a table exists in TCA but not in the db, a error
1440 // occured because $res is not a valid object.
1441 if (method_exists($res, 'FetchRow')) {
1442 $output = $res->FetchRow();
1443 $tableList = $res->TYPO3_DBAL_tableList; // Reading list of tables from SELECT query:
1444
1445 // Removing all numeric/integer keys.
1446 // A workaround because in ADOdb we would need to know what we want before executing the query...
1447 if (is_array($output)) {
1448 foreach ($output as $key => $value) {
1449 if (is_integer($key)) {
1450 unset($output[$key]);
1451 }
1452 elseif ($value === ' ' && $this->runningADOdbDriver('mssql')) $output[$key] = ''; // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
1453 }
1454 }
1455 }
1456 break;
1457 case 'userdefined':
1458 $output = $res->sql_fetch_assoc();
1459 $tableList = $res->TYPO3_DBAL_tableList; // Reading list of tables from SELECT query:
1460 break;
1461 }
1462
1463 // Table/Fieldname mapping:
1464 if (is_array($output)) {
1465 if ($tables = $this->map_needMapping($tableList,TRUE)) {
1466 $output = $this->map_assocArray($output,$tables,1);
1467 }
1468 }
1469
1470 // Return result:
1471 return $output;
1472 }
1473
1474 /**
1475 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
1476 * The array contains the values in numerical indices.
1477 *
1478 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1479 * @return array Array with result rows.
1480 */
1481 public function sql_fetch_row(&$res) {
1482 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1483 switch ($handlerType) {
1484 case 'native':
1485 $output = mysql_fetch_row($res);
1486 break;
1487 case 'adodb':
1488 // Check if method exists for the current $res object.
1489 // If a table exists in TCA but not in the db, a error
1490 // occured because $res is not a valid object.
1491 if (method_exists($res, 'FetchRow')) {
1492 $output = $res->FetchRow();
1493
1494 // Removing all assoc. keys.
1495 // A workaround because in ADOdb we would need to know what we want before executing the query...
1496 if (is_array($output)) {
1497 foreach ($output as $key => $value) {
1498 if (!is_integer($key)) unset($output[$key]);
1499 elseif ($value === ' ' && $this->runningADOdbDriver('mssql')) $output[$key] = ''; // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
1500 }
1501 }
1502 }
1503 break;
1504 case 'userdefined':
1505 $output = $res->sql_fetch_row();
1506 break;
1507 }
1508 return $output;
1509 }
1510
1511 /**
1512 * Free result memory / unset result object
1513 *
1514 * @param pointer MySQL result pointer to free / DBAL object
1515 * @return boolean Returns TRUE on success or FALSE on failure.
1516 */
1517 public function sql_free_result(&$res) {
1518 if ($res === FALSE) return FALSE;
1519
1520 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1521 switch ($handlerType) {
1522 case 'native':
1523 $output = mysql_free_result($res);
1524 break;
1525 case 'adodb':
1526 if (method_exists($res, 'Close')) {
1527 $res->Close();
1528 unset($res);
1529 $output = TRUE;
1530 } else {
1531 $output = FALSE;
1532 }
1533 break;
1534 case 'userdefined':
1535 unset($res);
1536 break;
1537 }
1538 return $output;
1539 }
1540
1541 /**
1542 * Get the ID generated from the previous INSERT operation
1543 *
1544 * @return integer The uid of the last inserted record.
1545 */
1546 public function sql_insert_id() {
1547 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1548 case 'native':
1549 $output = mysql_insert_id($this->handlerInstance[$this->lastHandlerKey]['link']);
1550 break;
1551 case 'adodb':
1552 $output = $this->handlerInstance[$this->lastHandlerKey]->last_insert_id;
1553 break;
1554 case 'userdefined':
1555 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_insert_id();
1556 break;
1557 }
1558 return $output;
1559 }
1560
1561 /**
1562 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1563 *
1564 * @return integer Number of rows affected by last query
1565 */
1566 public function sql_affected_rows() {
1567 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
1568 case 'native':
1569 $output = mysql_affected_rows();
1570 break;
1571 case 'adodb':
1572 $output = $this->handlerInstance[$this->lastHandlerKey]->Affected_Rows();
1573 break;
1574 case 'userdefined':
1575 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_affected_rows();
1576 break;
1577 }
1578 return $output;
1579 }
1580
1581 /**
1582 * Move internal result pointer
1583 *
1584 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
1585 * @param integer Seek result number.
1586 * @return boolean Returns TRUE on success or FALSE on failure.
1587 */
1588 public function sql_data_seek(&$res, $seek) {
1589 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1590 switch ($handlerType) {
1591 case 'native':
1592 $output = mysql_data_seek($res,$seek);
1593 break;
1594 case 'adodb':
1595 $output = $res->Move($seek);
1596 break;
1597 case 'userdefined':
1598 $output = $res->sql_data_seek($seek);
1599 break;
1600 }
1601 return $output;
1602 }
1603
1604 /**
1605 * Get the type of the specified field in a result
1606 *
1607 * If the first parameter is a string, it is used as table name for the lookup.
1608 *
1609 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
1610 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
1611 * @return string Returns the type of the specified field index
1612 */
1613 public function sql_field_metatype($table, $field) {
1614 // If $table and/or $field are mapped, use the original names instead
1615 foreach ($this->mapping as $tableName => $tableMapInfo) {
1616 if (isset($tableMapInfo['mapTableName']) && $tableMapInfo['mapTableName'] === $table) {
1617 // Table name is mapped => use original name
1618 $table = $tableName;
1619 }
1620
1621 if (isset($tableMapInfo['mapFieldNames'])) {
1622 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
1623 if ($fieldMapInfo === $field) {
1624 // Field name is mapped => use original name
1625 $field = $fieldName;
1626 }
1627 }
1628 }
1629 }
1630
1631 return $this->cache_fieldType[$table][$field]['metaType'];
1632 }
1633
1634 /**
1635 * Get the type of the specified field in a result
1636 *
1637 * If the first parameter is a string, it is used as table name for the lookup.
1638 *
1639 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
1640 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
1641 * @return string Returns the type of the specified field index
1642 */
1643 public function sql_field_type(&$res,$pointer) {
1644 if ($res === null) {
1645 debug(array('no res in sql_field_type!'));
1646 return 'text';
1647 }
1648 elseif (is_string($res)){
1649 if ($res === 'tx_dbal_debuglog') return 'text';
1650 $handlerType = 'adodb';
1651 }
1652 else {
1653 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
1654 }
1655
1656 switch ($handlerType) {
1657 case 'native':
1658 $output = mysql_field_type($res,$pointer);
1659 break;
1660 case 'adodb':
1661 if (is_string($pointer)){
1662 $output = $this->cache_fieldType[$res][$pointer]['type'];
1663 }
1664
1665 break;
1666 case 'userdefined':
1667 $output = $res->sql_field_type($pointer);
1668 break;
1669 }
1670
1671 return $output;
1672 }
1673
1674
1675
1676
1677
1678
1679
1680
1681 /**********
1682 *
1683 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
1684 * Deprecated.
1685 *
1686 **********/
1687
1688 /**
1689 * Executes query (on DEFAULT handler!)
1690 * DEPRECATED - use exec_* functions from this class instead!
1691 *
1692 * @param string Database name
1693 * @param string Query to execute
1694 * @return pointer Result pointer
1695 * @deprecated since TYPO3 4.1
1696 */
1697 public function sql($db,$query) {
1698 return $this->sql_query($query);
1699 }
1700
1701 /**
1702 * Executes query (on DEFAULT handler!)
1703 * DEPRECATED - use exec_* functions from this class instead!
1704 *
1705 * If you don't, anything that uses not the _DEFAULT handler will break!
1706 *
1707 * @param string Query to execute
1708 * @return pointer Result pointer / DBAL object
1709 * @deprecated since TYPO3 4.1
1710 */
1711 public function sql_query($query) {
1712 switch ($this->handlerCfg['_DEFAULT']['type']) {
1713 case 'native':
1714 $sqlResult = mysql_query($query, $this->handlerInstance['_DEFAULT']['link']);
1715 break;
1716 case 'adodb':
1717 $sqlResult = $this->handlerInstance['_DEFAULT']->Execute($query);
1718 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
1719 break;
1720 case 'userdefined':
1721 $sqlResult = $this->handlerInstance['_DEFAULT']->sql_query($query);
1722 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
1723 break;
1724 }
1725
1726 if ($this->printErrors && $this->sql_error()) {
1727 debug(array($this->lastQuery, $this->sql_error()));
1728 }
1729
1730 return $sqlResult;
1731 }
1732
1733 /**
1734 * Opening the _DEFAULT connection handler to the database.
1735 * This is typically done by the scripts "init.php" in the backend or "index_ts.php" in the frontend (tslib_fe->connectToMySQL())
1736 * You wouldn't need to use this at any time - let TYPO3 core handle this.
1737 *
1738 * @param string Database host IP/domain
1739 * @param string Username to connect with.
1740 * @param string Password to connect with.
1741 * @return mixed Returns handler connection value
1742 * @deprecated since TYPO3 4.1
1743 * @see handler_init()
1744 */
1745 public function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
1746 // Overriding the _DEFAULT handler configuration of username, password, localhost and database name:
1747 $this->handlerCfg['_DEFAULT']['config']['username'] = $TYPO3_db_username;
1748 $this->handlerCfg['_DEFAULT']['config']['password'] = $TYPO3_db_password;
1749 $this->handlerCfg['_DEFAULT']['config']['host'] = $TYPO3_db_host;
1750 $this->handlerCfg['_DEFAULT']['config']['database'] = TYPO3_db;
1751
1752 // Initializing and output value:
1753 $sqlResult = $this->handler_init('_DEFAULT');
1754 return $sqlResult;
1755 }
1756
1757 /**
1758 * Select database for _DEFAULT handler.
1759 *
1760 * @param string Database to connect to.
1761 * @return boolean Always returns TRUE; function is obsolete, database selection is made in handler_init() function!
1762 * @deprecated since TYPO3 4.1
1763 */
1764 public function sql_select_db($TYPO3_db) {
1765 return TRUE;
1766 }
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782 /**************************************
1783 *
1784 * SQL admin functions
1785 * (For use in the Install Tool and Extension Manager)
1786 *
1787 **************************************/
1788
1789 /**
1790 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1791 * Use in Install Tool only!
1792 * Usage count/core: 1
1793 *
1794 * @return array Each entry represents a database name
1795 */
1796 public function admin_get_dbs() {
1797 $dbArr = array();
1798 switch ($this->handlerCfg['_DEFAULT']['type']) {
1799 case 'native':
1800 $db_list = mysql_list_dbs($this->link);
1801 while ($row = mysql_fetch_object($db_list)) {
1802 if ($this->sql_select_db($row->Database)) {
1803 $dbArr[] = $row->Database;
1804 }
1805 }
1806 break;
1807 case 'adodb':
1808 // check needed for install tool - otherwise it will just die because the call to
1809 // MetaDatabases is done on a stdClass instance
1810 if (method_exists($this->handlerInstance['_DEFAULT'],'MetaDatabases')) {
1811 $sqlDBs = $this->handlerInstance['_DEFAULT']->MetaDatabases();
1812 if (is_array($sqlDBs)) {
1813 foreach ($sqlDBs as $k => $theDB) {
1814 $dbArr[] = $theDB;
1815 }
1816 }
1817 }
1818 break;
1819 case 'userdefined':
1820 $dbArr = $this->handlerInstance['_DEFAULT']->admin_get_tables();
1821 break;
1822 }
1823
1824 return $dbArr;
1825 }
1826
1827 /**
1828 * Returns the list of tables from the system (quering the DBMSs)
1829 * It looks up all tables from the DBMS of the _DEFAULT handler and then add all tables *configured* to be managed by other handlers
1830 *
1831 * When fetching the tables, it skips tables whose names begin with BIN$, as this is taken as a table coming from the "Recycle Bin" on Oracle.
1832 *
1833 * @return array Tables in an array (tablename is in both key and value)
1834 * @todo Should the check for Oracle Recycle Bin stuff be moved elsewhere?
1835 * @todo Should return table details in value! see t3lib_db::admin_get_tables()
1836 */
1837 public function admin_get_tables() {
1838 $whichTables = array();
1839
1840 // Getting real list of tables:
1841 switch ($this->handlerCfg['_DEFAULT']['type']) {
1842 case 'native':
1843 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->handlerInstance['_DEFAULT']['link']);
1844 if (!$this->sql_error()) {
1845 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
1846 $whichTables[current($theTable)] = current($theTable);
1847 }
1848 }
1849 break;
1850 case 'adodb':
1851 $sqlTables = $this->handlerInstance['_DEFAULT']->MetaTables('TABLES');
1852 while (list($k, $theTable) = each($sqlTables)) {
1853 if (preg_match('/BIN\$/', $theTable)) continue; // skip tables from the Oracle 10 Recycle Bin
1854 $whichTables[$theTable] = $theTable;
1855 }
1856 break;
1857 case 'userdefined':
1858 $whichTables = $this->handlerInstance['_DEFAULT']->admin_get_tables();
1859 break;
1860 }
1861
1862 // Check mapping:
1863 if (is_array($this->mapping) && count($this->mapping)) {
1864
1865 // Mapping table names in reverse, first getting list of real table names:
1866 $tMap = array();
1867 foreach ($this->mapping as $tN => $tMapInfo) {
1868 if (isset($tMapInfo['mapTableName'])) $tMap[$tMapInfo['mapTableName']]=$tN;
1869 }
1870
1871 // Do mapping:
1872 $newList=array();
1873 foreach ($whichTables as $tN) {
1874 if (isset($tMap[$tN])) $tN = $tMap[$tN];
1875 $newList[$tN] = $tN;
1876 }
1877
1878 $whichTables = $newList;
1879 }
1880
1881 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
1882 if (is_array($this->table2handlerKeys)) {
1883 foreach ($this->table2handlerKeys as $key => $handlerKey) {
1884 $whichTables[$key] = $key;
1885 }
1886 }
1887
1888 return $whichTables;
1889 }
1890
1891 /**
1892 * Returns information about each field in the $table (quering the DBMS)
1893 * In a DBAL this should look up the right handler for the table and return compatible information
1894 * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery
1895 *
1896 * @param string Table name
1897 * @return array Field information in an associative array with fieldname => field row
1898 */
1899 public function admin_get_fields($tableName) {
1900 $output = array();
1901
1902 // Do field mapping if needed:
1903 $ORIG_tableName = $tableName;
1904 if ($tableArray = $this->map_needMapping($tableName)) {
1905
1906 // Table name:
1907 if ($this->mapping[$tableName]['mapTableName']) {
1908 $tableName = $this->mapping[$tableName]['mapTableName'];
1909 }
1910 }
1911
1912 // Find columns
1913 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
1914 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1915 case 'native':
1916 $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
1917 while($fieldRow = mysql_fetch_assoc($columns_res)) {
1918 $output[$fieldRow['Field']] = $fieldRow;
1919 }
1920 break;
1921 case 'adodb':
1922 $fieldRows = $this->handlerInstance[$this->lastHandlerKey]->MetaColumns($tableName, FALSE);
1923 if (is_array($fieldRows)) {
1924 foreach ($fieldRows as $k => $fieldRow) {
1925 settype($fieldRow, 'array');
1926 $fieldRow['Field'] = $fieldRow['name'];
1927 $ntype = $this->MySQLActualType($this->MetaType($fieldRow['type'],$tableName));
1928 $ntype .= (($fieldRow['max_length'] != -1) ? (($ntype == 'INT') ? '(11)' :'('.$fieldRow['max_length'].')') : '');
1929 $fieldRow['Type'] = strtolower($ntype);
1930 $fieldRow['Null'] = '';
1931 $fieldRow['Key'] = '';
1932 $fieldRow['Default'] = $fieldRow['default_value'];
1933 $fieldRow['Extra'] = '';
1934 $output[$fieldRow['name']] = $fieldRow;
1935 }
1936 }
1937 break;
1938 case 'userdefined':
1939 $output = $this->handlerInstance[$this->lastHandlerKey]->admin_get_fields($tableName);
1940 break;
1941 }
1942
1943 // mapping should be done:
1944 if (is_array($tableArray) && is_array($this->mapping[$ORIG_tableName]['mapFieldNames'])) {
1945 $revFields = array_flip($this->mapping[$ORIG_tableName]['mapFieldNames']);
1946
1947 $newOutput = array();
1948 foreach ($output as $fN => $fInfo) {
1949 if (isset($revFields[$fN])) {
1950 $fN = $revFields[$fN];
1951 $fInfo['Field'] = $fN;
1952 }
1953 $newOutput[$fN] = $fInfo;
1954 }
1955 $output = $newOutput;
1956 }
1957
1958 return $output;
1959 }
1960
1961 /**
1962 * Returns information about each index key in the $table (quering the DBMS)
1963 * In a DBAL this should look up the right handler for the table and return compatible information
1964 *
1965 * @param string Table name
1966 * @return array Key information in a numeric array
1967 */
1968 public function admin_get_keys($tableName) {
1969 $output = array();
1970
1971 // Do field mapping if needed:
1972 $ORIG_tableName = $tableName;
1973 if ($tableArray = $this->map_needMapping($tableName)) {
1974
1975 // Table name:
1976 if ($this->mapping[$tableName]['mapTableName']) {
1977 $tableName = $this->mapping[$tableName]['mapTableName'];
1978 }
1979 }
1980
1981 // Find columns
1982 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_tableName);
1983 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
1984 case 'native':
1985 $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
1986 while($keyRow = mysql_fetch_assoc($keyRes)) {
1987 $output[] = $keyRow;
1988 }
1989 break;
1990 case 'adodb':
1991 $keyRows = $this->handlerInstance[$this->lastHandlerKey]->MetaIndexes($tableName);
1992 if ($keyRows !== FALSE) {
1993 while (list($k, $theKey) = each($keyRows)) {
1994 $theKey['Table'] = $tableName;
1995 $theKey['Non_unique'] = (int) !$theKey['unique'];
1996 $theKey['Key_name'] = str_replace($tableName.'_','',$k);
1997
1998 // the following are probably not needed anyway...
1999 $theKey['Collation'] = '';
2000 $theKey['Cardinality'] = '';
2001 $theKey['Sub_part'] = '';
2002 $theKey['Packed'] = '';
2003 $theKey['Null'] = '';
2004 $theKey['Index_type'] = '';
2005 $theKey['Comment'] = '';
2006
2007 // now map multiple fields into multiple rows (we mimic MySQL, remember...)
2008 $keycols = $theKey['columns'];
2009 while (list($c, $theCol) = each($keycols)) {
2010 $theKey['Seq_in_index'] = $c+1;
2011 $theKey['Column_name'] = $theCol;
2012 $output[] = $theKey;
2013 }
2014 }
2015 }
2016 $priKeyRow = $this->handlerInstance[$this->lastHandlerKey]->MetaPrimaryKeys($tableName);
2017 $theKey = array();
2018 $theKey['Table'] = $tableName;
2019 $theKey['Non_unique'] = 0;
2020 $theKey['Key_name'] = 'PRIMARY';
2021
2022 // the following are probably not needed anyway...
2023 $theKey['Collation'] = '';
2024 $theKey['Cardinality'] = '';
2025 $theKey['Sub_part'] = '';
2026 $theKey['Packed'] = '';
2027 $theKey['Null'] = '';
2028 $theKey['Index_type'] = '';
2029 $theKey['Comment'] = '';
2030
2031 // now map multiple fields into multiple rows (we mimic MySQL, remember...)
2032 if ($priKeyRow !== FALSE) {
2033 while (list($c, $theCol) = each($priKeyRow)) {
2034 $theKey['Seq_in_index'] = $c+1;
2035 $theKey['Column_name'] = $theCol;
2036 $output[] = $theKey;
2037 }
2038 }
2039 break;
2040 case 'userdefined':
2041 $output = $this->handlerInstance[$this->lastHandlerKey]->admin_get_keys($tableName);
2042 break;
2043 }
2044
2045 // mapping should be done:
2046 if (is_array($tableArray) && is_array($this->mapping[$ORIG_tableName]['mapFieldNames'])) {
2047 $revFields = array_flip($this->mapping[$ORIG_tableName]['mapFieldNames']);
2048
2049 $newOutput = array();
2050 foreach ($output as $kN => $kInfo) {
2051 // Table:
2052 $kInfo['Table'] = $ORIG_tableName;
2053
2054 // Column
2055 if (isset($revFields[$kInfo['Column_name']])) {
2056 $kInfo['Column_name'] = $revFields[$kInfo['Column_name']];
2057 }
2058
2059 // Write it back:
2060 $newOutput[$kN] = $kInfo;
2061 }
2062 $output = $newOutput;
2063 }
2064
2065 return $output;
2066 }
2067
2068 /**
2069 * mysql() wrapper function, used by the Install Tool.
2070 *
2071 * @return array
2072 */
2073 public function admin_get_charsets() {
2074 return array();
2075 }
2076
2077 /**
2078 * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
2079 *
2080 * @param string Query to execute
2081 * @return pointer Result pointer
2082 */
2083 public function admin_query($query) {
2084 $parsedQuery = $this->SQLparser->parseSQL($query);
2085 $ORIG_table = $parsedQuery['TABLE'];
2086
2087 if (is_array($parsedQuery)) {
2088
2089 // Process query based on type:
2090 switch ($parsedQuery['type']) {
2091 case 'CREATETABLE':
2092 case 'ALTERTABLE':
2093 case 'DROPTABLE':
2094 if (file_exists(PATH_typo3conf.'temp_fieldInfo.php')) unlink(PATH_typo3conf.'temp_fieldInfo.php');
2095 $this->map_genericQueryParsed($parsedQuery);
2096 break;
2097 case 'INSERT':
2098 $this->map_genericQueryParsed($parsedQuery);
2099 break;
2100 case 'CREATEDATABASE':
2101 die('Creating a database with DBAL is not supported. Did you really read the manual?');
2102 break;
2103 default:
2104 die('ERROR: Invalid Query type ('.$parsedQuery['type'].') for ->admin_query() function!: "'.htmlspecialchars($query).'"');
2105 break;
2106 }
2107
2108 // Setting query array (for other applications to access if needed)
2109 $this->lastParsedAndMappedQueryArray = $parsedQuery;
2110
2111 // Execute query (based on handler derived from the TABLE name which we actually know for once!)
2112 $this->lastHandlerKey = $this->handler_getFromTableList($ORIG_table);
2113 switch ((string)$this->handlerCfg[$this->lastHandlerKey]['type']) {
2114 case 'native':
2115 // Compiling query:
2116 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2117
2118 if ($this->lastParsedAndMappedQueryArray['type']=='INSERT') {
2119 return mysql_query($compiledQuery, $this->link);
2120 }
2121 return mysql_query($compiledQuery[0], $this->link);
2122 break;
2123 case 'adodb':
2124 // Compiling query:
2125 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2126 if ($this->lastParsedAndMappedQueryArray['type']=='INSERT') {
2127 return $this->exec_INSERTquery($this->lastParsedAndMappedQueryArray['TABLE'],$compiledQuery);
2128 }
2129 return $this->handlerInstance[$this->lastHandlerKey]->DataDictionary->ExecuteSQLArray($compiledQuery);
2130 break;
2131 case 'userdefined':
2132 // Compiling query:
2133 $compiledQuery = $this->SQLparser->compileSQL($this->lastParsedAndMappedQueryArray);
2134
2135 return $this->handlerInstance[$this->lastHandlerKey]->admin_query($compiledQuery);
2136 break;
2137 }
2138 } else die('ERROR: Query could not be parsed: "'.htmlspecialchars($parsedQuery).'". Query: "'.htmlspecialchars($query).'"');
2139 }
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150 /************************************
2151 *
2152 * Handler management
2153 *
2154 **************************************/
2155
2156 /**
2157 * Return the handler key pointing to an appropriate database handler as found in $this->handlerCfg array
2158 * Notice: TWO or more tables in the table list MUST use the SAME handler key - otherwise a fatal error is thrown! (Logically, no database can possibly join two tables from separate sources!)
2159 *
2160 * @param string Table list, eg. "pages" or "pages, tt_content" or "pages AS A, tt_content AS B"
2161 * @return string Handler key (see $this->handlerCfg array) for table
2162 */
2163 public function handler_getFromTableList($tableList) {
2164
2165 $key = $tableList;
2166
2167 if (!isset($this->cache_handlerKeyFromTableList[$key])) {
2168
2169 // Get tables separated:
2170 $_tableList = $tableList;
2171 $tableArray = $this->SQLparser->parseFromTables($_tableList);
2172
2173 // If success, traverse the tables:
2174 if (is_array($tableArray) && count($tableArray)) {
2175 $outputHandlerKey = '';
2176
2177 foreach ($tableArray as $vArray) {
2178 // Find handler key, select "_DEFAULT" if none is specifically configured:
2179 $handlerKey = $this->table2handlerKeys[$vArray['table']] ? $this->table2handlerKeys[$vArray['table']] : '_DEFAULT';
2180
2181 // In case of separate handler keys for joined tables:
2182 if ($outputHandlerKey && $handlerKey != $outputHandlerKey) {
2183 die('DBAL fatal error: Tables in this list "'.$tableList.'" didn\'t use the same DB handler!');
2184 }
2185
2186 $outputHandlerKey = $handlerKey;
2187 }
2188
2189 // Check initialized state; if handler is NOT initialized (connected) then we will connect it!
2190 if (!isset($this->handlerInstance[$outputHandlerKey])) {
2191 $this->handler_init($outputHandlerKey);
2192 }
2193
2194 // Return handler key:
2195 $this->cache_handlerKeyFromTableList[$key] = $outputHandlerKey;
2196 } else {
2197 die('DBAL fatal error: No handler found in handler_getFromTableList() for: "'.$tableList.'" ('.$tableArray.')');
2198 }
2199 }
2200
2201 return $this->cache_handlerKeyFromTableList[$key];
2202 }
2203
2204 /**
2205 * Initialize handler (connecting to database)
2206 *
2207 * @param string Handler key
2208 * @return boolean If connection went well, return TRUE
2209 * @see handler_getFromTableList()
2210 */
2211 public function handler_init($handlerKey) {
2212
2213 // Find handler configuration:
2214 $cfgArray = $this->handlerCfg[$handlerKey];
2215 $handlerType = (string)$cfgArray['type'];
2216 $output = FALSE;
2217
2218 if (is_array($cfgArray)) {
2219 switch ($handlerType) {
2220 case 'native':
2221 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
2222 $link = mysql_connect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''), $cfgArray['config']['username'], $cfgArray['config']['password'], TRUE);
2223 } else {
2224 $link = mysql_pconnect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''), $cfgArray['config']['username'], $cfgArray['config']['password']);
2225 }
2226
2227 // Set handler instance:
2228 $this->handlerInstance[$handlerKey] = array('handlerType' => 'native', 'link' => $link);
2229
2230 // If link succeeded:
2231 if ($link) {
2232 // For default, set ->link (see t3lib_DB)
2233 if ($handlerKey == '_DEFAULT') {
2234 $this->link = $link;
2235 }
2236
2237 // Select database as well:
2238 if (mysql_select_db($cfgArray['config']['database'], $link)) {
2239 $output = TRUE;
2240 }
2241 $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'], 1);
2242 foreach ($setDBinit as $v) {
2243 if (mysql_query($v, $this->link) === FALSE) {
2244 t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'".','Core',3);
2245 }
2246 }
2247 } else {
2248 t3lib_div::sysLog('Could not connect to MySQL server '.$cfgArray['config']['host'].' with user '.$cfgArray['config']['username'].'.','Core',4);
2249 }
2250 break;
2251 case 'adodb':
2252 $output = TRUE;
2253 require_once(t3lib_extMgm::extPath('adodb').'adodb/adodb.inc.php');
2254 if (!defined('ADODB_FORCE_NULLS')) define('ADODB_FORCE_NULLS', 1);
2255 $GLOBALS['ADODB_FORCE_TYPE'] = ADODB_FORCE_VALUE;
2256 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_BOTH;
2257
2258 $this->handlerInstance[$handlerKey] = &ADONewConnection($cfgArray['config']['driver']);
2259
2260 // Set driver-specific options
2261 if (isset($cfgArray['config']['driverOptions'])) {
2262 foreach ($cfgArray['config']['driverOptions'] as $optionName => $optionValue) {
2263 $optionSetterName = 'set' . ucfirst($optionName);
2264 if (method_exists($this->handlerInstance[$handlerKey], $optionSetterName)) {
2265 $this->handlerInstance[$handlerKey]->$optionSetterName($optionValue);
2266 } else {
2267 $this->handlerInstance[$handlerKey]->$optionName = $optionValue;
2268 }
2269 }
2270 }
2271
2272 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
2273 $this->handlerInstance[$handlerKey]->Connect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''),$cfgArray['config']['username'],$cfgArray['config']['password'],$cfgArray['config']['database']);
2274 } else {
2275 $this->handlerInstance[$handlerKey]->PConnect($cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : ''),$cfgArray['config']['username'],$cfgArray['config']['password'],$cfgArray['config']['database']);
2276 }
2277 if (!$this->handlerInstance[$handlerKey]->isConnected()) {
2278 $dsn = $cfgArray['config']['driver'].'://'.$cfgArray['config']['username'].
2279 (strlen($cfgArray['config']['password']) ? ':XXXX@' : '').
2280 $cfgArray['config']['host'].(isset($cfgArray['config']['port']) ? ':'.$cfgArray['config']['port'] : '').'/'.$cfgArray['config']['database'].
2281 ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'] ? '' : '?persistent=1');
2282 t3lib_div::sysLog('Could not connect to DB server using ADOdb on '.$cfgArray['config']['host'].' with user '.$cfgArray['config']['username'].'.','Core',4);
2283 error_log('DBAL error: Connection to '.$dsn.' failed. Maybe PHP doesn\'t support the database?');
2284 $output = FALSE;
2285 } else {
2286 $this->handlerInstance[$handlerKey]->DataDictionary = NewDataDictionary($this->handlerInstance[$handlerKey]);
2287 $this->handlerInstance[$handlerKey]->last_insert_id = 0;
2288 if (isset($cfgArray['config']['sequenceStart'])) {
2289 $this->handlerInstance[$handlerKey]->sequenceStart = $cfgArray['config']['sequenceStart'];
2290 } else {
2291 $this->handlerInstance[$handlerKey]->sequenceStart = 1;
2292 }
2293 }
2294 break;
2295 case 'userdefined':
2296 // Find class file:
2297 $fileName = t3lib_div::getFileAbsFileName($cfgArray['config']['classFile']);
2298 if (@is_file($fileName)) {
2299 require_once($fileName);
2300 } else die('DBAL error: "'.$fileName.'" was not a file to include.');
2301
2302 // Initialize:
2303 $this->handlerInstance[$handlerKey] = t3lib_div::makeInstance($cfgArray['config']['class']);
2304 $this->handlerInstance[$handlerKey]->init($cfgArray,$this);
2305
2306 if (is_object($this->handlerInstance[$handlerKey])) {
2307 $output = TRUE;
2308 }
2309 break;
2310 default:
2311 die('ERROR: Invalid handler type: "'.$cfgArray['type'].'"');
2312 break;
2313 }
2314
2315 return $output;
2316 } else die('ERROR: No handler for key "'.$handlerKey.'"');
2317 }
2318
2319
2320 /**
2321 * Checks whether the DBAL is currently inside an operation running on the "native" DB handler (i.e. MySQL)
2322 *
2323 * @return boolean True if running on "native" DB handler (i.e. MySQL)
2324 */
2325 public function runningNative() {
2326 return ((string)$this->handlerCfg[$this->lastHandlerKey]['type']==='native');
2327 }
2328
2329
2330 /**
2331 * Checks whether the ADOdb handler is running with a driver that contains the argument
2332 *
2333 * @param string $driver Driver name, matched with strstr().
2334 * @return boolean True if running with the given driver
2335 */
2336 public function runningADOdbDriver($driver) {
2337 return strstr($this->handlerCfg[$this->lastHandlerKey]['config']['driver'], $driver);
2338 }
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349 /************************************
2350 *
2351 * Table/Field mapping
2352 *
2353 **************************************/
2354
2355 /**
2356 * Checks if mapping is needed for a table(list)
2357 *
2358 * @param string List of tables in query
2359 * @param boolean If TRUE, it will check only if FIELDs are configured and ignore the mapped table name if any.
2360 * @return mixed Returns an array of table names (parsed version of input table) if mapping is needed, otherwise just FALSE.
2361 */
2362 protected function map_needMapping($tableList, $fieldMappingOnly = FALSE) {
2363 $key = $tableList.'|'.$fieldMappingOnly;
2364 if (!isset($this->cache_mappingFromTableList[$key])) {
2365 $this->cache_mappingFromTableList[$key] = FALSE; // Default:
2366
2367 $tables = $this->SQLparser->parseFromTables($tableList);
2368 if (is_array($tables)) {
2369 foreach ($tables as $tableCfg) {
2370 if ($fieldMappingOnly) {
2371 if (is_array($this->mapping[$tableCfg['table']]['mapFieldNames'])) {
2372 $this->cache_mappingFromTableList[$key] = $tables;
2373 } elseif (is_array($tableCfg['JOIN'])) {
2374 foreach ($tableCfg['JOIN'] as $join) {
2375 if (is_array($this->mapping[$join['withTable']]['mapFieldNames'])) {
2376 $this->cache_mappingFromTableList[$key] = $tables;
2377 break;
2378 }
2379 }
2380 }
2381 } else {
2382 if (is_array($this->mapping[$tableCfg['table']])) {
2383 $this->cache_mappingFromTableList[$key] = $tables;
2384 } elseif (is_array($tableCfg['JOIN'])) {
2385 foreach ($tableCfg['JOIN'] as $join) {
2386 if (is_array($this->mapping[$join['withTable']])) {
2387 $this->cache_mappingFromTableList[$key] = $tables;
2388 break;
2389 }
2390 }
2391 }
2392 }
2393 }
2394 }
2395 }
2396
2397 return $this->cache_mappingFromTableList[$key];
2398 }
2399
2400 /**
2401 * Takes an associated array with field => value pairs and remaps the field names if configured for this table in $this->mapping array.
2402 * Be careful not to map a field name to another existing fields name (although you can use this to swap fieldnames of course...:-)
2403 * Observe mapping problems with join-results (more than one table): Joined queries should always prefix the table name to avoid problems with this.
2404 * Observe that alias fields are not mapped of course (should not be a problem though)
2405 *
2406 * @param array Input array, associative keys
2407 * @param array Array of tables from the query. Normally just one table; many tables in case of a join. NOTICE: for multiple tables (with joins) there MIGHT occur trouble with fields of the same name in the two tables: This function traverses the mapping information for BOTH tables and applies mapping without checking from which table the field really came!
2408 * @param boolean If TRUE, reverse direction. Default direction is to map an array going INTO the database (thus mapping TYPO3 fieldnames to PHYSICAL field names!)
2409 * @return array Output array, with mapped associative keys.
2410 */
2411 protected function map_assocArray($input, $tables, $rev = FALSE) {
2412 // Traverse tables from query (hopefully only one table):
2413 foreach ($tables as $tableCfg) {
2414 if (is_array($this->mapping[$tableCfg['table']]['mapFieldNames'])) {
2415
2416 // Get the map (reversed if needed):
2417 if ($rev) {
2418 $theMap = array_flip($this->mapping[$tableCfg['table']]['mapFieldNames']);
2419 } else {
2420 $theMap = $this->mapping[$tableCfg['table']]['mapFieldNames'];
2421 }
2422
2423 // Traverse selected record, map fieldnames:
2424 $output = array();
2425 foreach ($input as $fN => $value) {
2426
2427 // Set the field name, change it if found in mapping array:
2428 if ($theMap[$fN]) {
2429 $newKey = $theMap[$fN];
2430 } else {
2431 $newKey = $fN;
2432 }
2433
2434 // Set value to fieldname:
2435 $output[$newKey] = $value;
2436 }
2437
2438 // When done, override the $input array with the result:
2439 $input = $output;
2440 }
2441 }
2442
2443 // Return input array (which might have been altered in the mean time)
2444 return $input;
2445 }
2446
2447 /**
2448 * Remaps table/field names in a SELECT query's parts
2449 * Notice: All arguments are passed by reference!
2450 *
2451 * @param string List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
2452 * @param string Table(s) from which to select. This is what comes right after "FROM ...". Require value.
2453 * @param string Where clause. This is what comes right after "WHERE ...". Can be blank.
2454 * @param string Group by field(s)
2455 * @param string Order by field(s)
2456 * @return void
2457 * @see exec_SELECTquery()
2458 */
2459 protected function map_remapSELECTQueryParts(&$select_fields, &$from_table, &$where_clause, &$groupBy, &$orderBy) {
2460 // Tables:
2461 $tables = $this->SQLparser->parseFromTables($from_table);
2462 $defaultTable = $tables[0]['table'];
2463 foreach ($tables as $k => $v) {
2464 if ($this->mapping[$v['table']]['mapTableName']) {
2465 $tables[$k]['table'] = $this->mapping[$v['table']]['mapTableName'];
2466 }
2467 // Mapping JOINS
2468 if (is_array($v['JOIN'])) {
2469 foreach($v['JOIN'] as $joinCnt => $join) {
2470 // Mapping withTable of the JOIN
2471 if ($this->mapping[$join['withTable']]['mapTableName']) {
2472 $tables[$k]['JOIN'][$joinCnt]['withTable'] = $this->mapping[$join['withTable']]['mapTableName'];
2473 }
2474 $onPartsArray = array();
2475 // Mapping ON parts of the JOIN
2476 if (is_array($join['ON'])) {
2477 foreach ($join['ON'] as $onParts) {
2478 if (isset($this->mapping[$onParts['table']]['mapFieldNames'][$onParts['field']])) {
2479 $onParts['field'] = $this->mapping[$onParts['table']]['mapFieldNames'][$onParts['field']];
2480 }
2481 if (isset($this->mapping[$onParts['table']]['mapTableName'])) {
2482 $onParts['table'] = $this->mapping[$onParts['table']]['mapTableName'];
2483 }
2484 $onPartsArray[] = $onParts;
2485 }
2486 $tables[$k]['JOIN'][$joinCnt]['ON'] = $onPartsArray;
2487 }
2488 }
2489 }
2490 }
2491 $from_table = $this->SQLparser->compileFromTables($tables);
2492
2493 // Where clause:
2494 $whereParts = $this->SQLparser->parseWhereClause($where_clause);
2495 $this->map_sqlParts($whereParts,$defaultTable);
2496 $where_clause = $this->SQLparser->compileWhereClause($whereParts, FALSE);
2497
2498 // Select fields:
2499 $expFields = $this->SQLparser->parseFieldList($select_fields);
2500 $this->map_sqlParts($expFields,$defaultTable);
2501 $select_fields = $this->SQLparser->compileFieldList($expFields);
2502
2503 // Group By fields
2504 $expFields = $this->SQLparser->parseFieldList($groupBy);
2505 $this->map_sqlParts($expFields,$defaultTable);
2506 $groupBy = $this->SQLparser->compileFieldList($expFields);
2507
2508 // Order By fields
2509 $expFields = $this->SQLparser->parseFieldList($orderBy);
2510 $this->map_sqlParts($expFields,$defaultTable);
2511 $orderBy = $this->SQLparser->compileFieldList($expFields);
2512 }
2513
2514 /**
2515 * Generic mapping of table/field names arrays (as parsed by tx_dbal_sqlengine)
2516 *
2517 * @param array Array with parsed SQL parts; Takes both fields, tables, where-parts, group and order-by. Passed by reference.
2518 * @param string Default table name to assume if no table is found in $sqlPartArray
2519 * @return void
2520 * @access private
2521 * @see map_remapSELECTQueryParts()
2522 */
2523 protected function map_sqlParts(&$sqlPartArray, $defaultTable) {
2524 // Traverse sql Part array:
2525 if (is_array($sqlPartArray)) {
2526 foreach ($sqlPartArray as $k => $v) {
2527
2528 // Look for sublevel (WHERE parts only)
2529 if (is_array($sqlPartArray[$k]['sub'])) {
2530 $this->map_sqlParts($sqlPartArray[$k]['sub'], $defaultTable); // Call recursively!
2531 } elseif (isset($sqlPartArray[$k]['func'])) {
2532 $subqueryDefaultTable = $sqlPartArray[$k]['func']['subquery']['FROM'][0]['table'];
2533 $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['SELECT'], $subqueryDefaultTable);
2534 $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['FROM'], $subqueryDefaultTable);
2535 $this->map_sqlParts($sqlPartArray[$k]['func']['subquery']['WHERE'], $subqueryDefaultTable);
2536 } else {
2537 // For the field, look for table mapping (generic):
2538 $t = $sqlPartArray[$k]['table'] ? $sqlPartArray[$k]['table'] : $defaultTable;
2539
2540 // Mapping field name, if set:
2541 if (is_array($this->mapping[$t]['mapFieldNames']) && $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']]) {
2542 $sqlPartArray[$k]['field'] = $this->mapping[$t]['mapFieldNames'][$sqlPartArray[$k]['field']];
2543 }
2544
2545 // Mapping field name in SQL-functions like MIN(), MAX() or SUM()
2546 if ($this->mapping[$t]['mapFieldNames']) {
2547 $fieldArray = explode('.', $sqlPartArray[$k]['func_content']);
2548 if (count($fieldArray) == 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
2549 $sqlPartArray[$k]['func_content.'][0]['func_content'] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
2550 $sqlPartArray[$k]['func_content'] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
2551 }
2552 elseif (count($fieldArray) == 2) {
2553 // Map the external table
2554 $table = $fieldArray[0];
2555 if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
2556 $table = $this->mapping[$fieldArray[0]]['mapTableName'];
2557 }
2558 // Map the field itself
2559 $field = $fieldArray[1];
2560 if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
2561 $field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
2562 }
2563 $sqlPartArray[$k]['func_content.'][0]['func_content'] = $table . '.' . $field;
2564 $sqlPartArray[$k]['func_content'] = $table . '.' . $field;
2565 }
2566 }
2567
2568 // Do we have a function (e.g., CONCAT)
2569 if (isset($v['value']['operator'])) {
2570 foreach ($sqlPartArray[$k]['value']['args'] as $argK => $fieldDef) {
2571 if (isset($this->mapping[$fieldDef['table']]['mapTableName'])) {
2572 $sqlPartArray[$k]['value']['args'][$argK]['table'] = $this->mapping[$fieldDef['table']]['mapTableName'];
2573 }
2574 if (is_array($this->mapping[$fieldDef['table']]['mapFieldNames']) && isset($this->mapping[$fieldDef['table']]['mapFieldNames'][$fieldDef['field']])) {
2575 $sqlPartArray[$k]['value']['args'][$argK]['field'] = $this->mapping[$fieldDef['table']]['mapFieldNames'][$fieldDef['field']];
2576 }
2577 }
2578 }
2579
2580 // Do we have a subquery (WHERE parts only)?
2581 if (isset($sqlPartArray[$k]['subquery'])) {
2582 $subqueryDefaultTable = $sqlPartArray[$k]['subquery']['FROM'][0]['table'];
2583 $this->map_sqlParts($sqlPartArray[$k]['subquery']['SELECT'], $subqueryDefaultTable);
2584 $this->map_sqlParts($sqlPartArray[$k]['subquery']['FROM'], $subqueryDefaultTable);
2585 $this->map_sqlParts($sqlPartArray[$k]['subquery']['WHERE'], $subqueryDefaultTable);
2586 }
2587
2588 // do we have a field name in the value?
2589 // this is a very simplistic check, beware
2590 if (!is_numeric($sqlPartArray[$k]['value'][0]) && !isset($sqlPartArray[$k]['value'][1])) {
2591 $fieldArray = explode('.', $sqlPartArray[$k]['value'][0]);
2592 if (count($fieldArray) == 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
2593 $sqlPartArray[$k]['value'][0] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
2594 } elseif (count($fieldArray) == 2) {
2595 // Map the external table
2596 $table = $fieldArray[0];
2597 if (isset($this->mapping[$fieldArray[0]]['mapTableName'])) {
2598 $table = $this->mapping[$fieldArray[0]]['mapTableName'];
2599 }
2600 // Map the field itself
2601 $field = $fieldArray[1];
2602 if (is_array($this->mapping[$fieldArray[0]]['mapFieldNames']) && isset($this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]])) {
2603 $field = $this->mapping[$fieldArray[0]]['mapFieldNames'][$fieldArray[1]];
2604 }
2605 $sqlPartArray[$k]['value'][0] = $table . '.' . $field;
2606 }
2607 }
2608
2609 // Map table?
2610 if ($sqlPartArray[$k]['table'] && $this->mapping[$sqlPartArray[$k]['table']]['mapTableName']) {
2611 $sqlPartArray[$k]['table'] = $this->mapping[$sqlPartArray[$k]['table']]['mapTableName'];
2612 }
2613 }
2614 }
2615 }
2616 }
2617
2618 /**
2619 * Will do table/field mapping on a general tx_dbal_sqlengine-compliant SQL query
2620 * (May still not support all query types...)
2621 *
2622 * @param array Parsed QUERY as from tx_dbal_sqlengine::parseSQL(). NOTICE: Passed by reference!
2623 * @return void
2624 * @see tx_dbal_sqlengine::parseSQL()
2625 */
2626 protected function map_genericQueryParsed(&$parsedQuery) {
2627
2628 // Getting table - same for all:
2629 $table = $parsedQuery['TABLE'];
2630 if ($table) {
2631 // Do field mapping if needed:
2632 if ($tableArray = $this->map_needMapping($table)) {
2633
2634 // Table name:
2635 if ($this->mapping[$table]['mapTableName']) {
2636 $parsedQuery['TABLE'] = $this->mapping[$table]['mapTableName'];
2637 }
2638
2639 // Based on type, do additional changes:
2640 switch ($parsedQuery['type']) {
2641 case 'ALTERTABLE':
2642
2643 // Changing field name:
2644 $newFieldName = $this->mapping[$table]['mapFieldNames'][$parsedQuery['FIELD']];
2645 if ($newFieldName) {
2646 if ($parsedQuery['FIELD'] == $parsedQuery['newField']) {
2647 $parsedQuery['FIELD'] = $parsedQuery['newField'] = $newFieldName;
2648 } else $parsedQuery['FIELD'] = $newFieldName;
2649 }
2650
2651 // Changing key field names:
2652 if (is_array($parsedQuery['fields'])) {
2653 $this->map_fieldNamesInArray($table,$parsedQuery['fields']);
2654 }
2655 break;
2656 case 'CREATETABLE':
2657 // Remapping fields:
2658 if (is_array($parsedQuery['FIELDS'])) {
2659 $newFieldsArray = array();
2660 foreach ($parsedQuery['FIELDS'] as $fN => $fInfo) {
2661 if ($this->mapping[$table]['mapFieldNames'][$fN]) {
2662 $fN = $this->mapping[$table]['mapFieldNames'][$fN];
2663 }
2664 $newFieldsArray[$fN] = $fInfo;
2665 }
2666 $parsedQuery['FIELDS'] = $newFieldsArray;
2667 }
2668
2669 // Remapping keys:
2670 if (is_array($parsedQuery['KEYS'])) {
2671 foreach ($parsedQuery['KEYS'] as $kN => $kInfo) {
2672 $this->map_fieldNamesInArray($table,$parsedQuery['KEYS'][$kN]);
2673 }
2674 }
2675 break;
2676
2677 /// ... and here support for all other query types should be!
2678
2679 }
2680 }
2681 } else die('ERROR, mapping: No table found in parsed Query array...');
2682 }
2683
2684 /**
2685 * Re-mapping field names in array
2686 *
2687 * @param string (TYPO3) Table name for fields.
2688 * @param array Array of fieldnames to remap. Notice: Passed by reference!
2689 * @return void
2690 */
2691 protected function map_fieldNamesInArray($table,&$fieldArray) {
2692 if (is_array($this->mapping[$table]['mapFieldNames'])) {
2693 foreach ($fieldArray as $k => $v) {
2694 if ($this->mapping[$table]['mapFieldNames'][$v]) {
2695 $fieldArray[$k] = $this->mapping[$table]['mapFieldNames'][$v];
2696 }
2697 }
2698 }
2699 }
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717 /**************************************
2718 *
2719 * Debugging
2720 *
2721 **************************************/
2722
2723 /**
2724 * Debug handler for query execution
2725 *
2726 * @param string Function name from which this function is called.
2727 * @param string Execution time in ms of the query
2728 * @param array In-data of various kinds.
2729 * @return void
2730 * @access private
2731 */
2732 public function debugHandler($function,$execTime,$inData) {
2733 // we don't want to log our own log/debug SQL
2734 $script = substr(PATH_thisScript,strlen(PATH_site));
2735
2736 if (substr($script,-strlen('dbal/mod1/index.php'))!='dbal/mod1/index.php' && !strstr($inData['args'][0], 'tx_dbal_debuglog')) {
2737 $data = array();
2738 $errorFlag = 0;
2739 $joinTable = '';
2740
2741 if ($this->sql_error()) {
2742 $data['sqlError'] = $this->sql_error();
2743 $errorFlag|=1;
2744 }
2745
2746 // if lastQuery is empty (for whatever reason) at least log inData.args
2747 if (empty($this->lastQuery))
2748 $query = implode(' ',$inData['args']);
2749 else
2750 $query = $this->lastQuery;
2751
2752 if ($this->conf['debugOptions']['backtrace']) {
2753 $backtrace = debug_backtrace();
2754 unset($backtrace[0]); // skip this very method :)
2755 $data['backtrace'] = array_slice($backtrace, 0, $this->conf['debugOptions']['backtrace']);
2756 }
2757
2758 switch ($function) {
2759 case 'exec_INSERTquery':
2760 case 'exec_UPDATEquery':
2761 case 'exec_DELETEquery':
2762 $this->debug_log($query,$execTime,$data,$joinTable,$errorFlag, $script);
2763 break;
2764
2765 case 'exec_SELECTquery':
2766 // Get explain data:
2767 if ($this->conf['debugOptions']['EXPLAIN'] && t3lib_div::inList('adodb,native',$inData['handlerType'])) {
2768 $data['EXPLAIN'] = $this->debug_explain($this->lastQuery);
2769 }
2770
2771 // Check parsing of Query:
2772 if ($this->conf['debugOptions']['parseQuery']) {
2773 $parseResults = array();
2774 $parseResults['SELECT'] = $this->SQLparser->debug_parseSQLpart('SELECT',$inData['args'][1]);
2775 $parseResults['FROM'] = $this->SQLparser->debug_parseSQLpart('FROM',$inData['args'][0]);
2776 $parseResults['WHERE'] = $this->SQLparser->debug_parseSQLpart('WHERE',$inData['args'][2]);
2777 $parseResults['GROUPBY'] = $this->SQLparser->debug_parseSQLpart('SELECT',$inData['args'][3]); // Using select field list syntax
2778 $parseResults['ORDERBY'] = $this->SQLparser->debug_parseSQLpart('SELECT',$inData['args'][4]); // Using select field list syntax
2779
2780 foreach ($parseResults as $k => $v) {
2781 if (!strlen($parseResults[$k])) unset($parseResults[$k]);
2782 }
2783 if (count($parseResults)) {
2784 $data['parseError'] = $parseResults;
2785 $errorFlag|=2;
2786 }
2787 }
2788
2789 // Checking joinTables:
2790 if ($this->conf['debugOptions']['joinTables']) {
2791 if (count(explode(',', $inData['ORIG_from_table']))>1) {
2792 $joinTable = $inData['args'][0];
2793 }
2794 }
2795
2796 // Logging it:
2797 $this->debug_log($query,$execTime,$data,$joinTable,$errorFlag, $script);
2798 if (!empty($inData['args'][2]))
2799 $this->debug_WHERE($inData['args'][0], $inData['args'][2], $script);
2800 break;
2801 }
2802 }
2803 }
2804
2805 /**
2806 * Logs the where clause for debugging purposes.
2807 *
2808 * @param string $table Table name(s) the query was targeted at
2809 * @param string $where The WHERE clause to be logged
2810 * @param string $script The script calling the logging
2811 * @return void
2812 */
2813 public function debug_WHERE($table, $where, $script = '') {
2814 $insertArray = array (
2815 'tstamp' => $GLOBALS['EXEC_TIME'],
2816 'beuser_id' => intval($GLOBALS['BE_USER']->user['uid']),
2817 'script' => $script,
2818 'tablename' => $table,
2819 'whereclause' => $where
2820 );
2821
2822 $this->exec_INSERTquery('tx_dbal_debuglog_where', $insertArray);
2823 }
2824
2825 /**
2826 * Inserts row in the log table
2827 *
2828 * @param string The current query
2829 * @param integer Execution time of query in milliseconds
2830 * @param array Data to be stored serialized.
2831 * @param string Join string if there IS a join.
2832 * @param integer Error status.
2833 * @param string $script The script calling the logging
2834 * @return void
2835 */
2836 public function debug_log($query,$ms,$data,$join,$errorFlag, $script='') {
2837 if (is_array($query)) {
2838 $queryToLog = $query[0].' -- ';
2839 if (count($query[1])) {
2840 $queryToLog .= count($query[1]).' BLOB FIELDS: '.implode(', ',array_keys($query[1]));
2841 }
2842 if (count($query[2])) {
2843 $queryToLog .= count($query[2]).' CLOB FIELDS: '.implode(', ',array_keys($query[2]));
2844 }
2845 } else {
2846 $queryToLog = $query;
2847 }
2848 $insertArray = array (
2849 'tstamp' => $GLOBALS['EXEC_TIME'],
2850 'beuser_id' => intval($GLOBALS['BE_USER']->user['uid']),
2851 'script' => $script,
2852 'exec_time' => $ms,
2853 'table_join' => $join,
2854 'serdata' => serialize($data),
2855 'query' => $queryToLog,
2856 'errorFlag' => $errorFlag
2857 );
2858
2859 $this->exec_INSERTquery('tx_dbal_debuglog', $insertArray);
2860 }
2861
2862 /**
2863 * Perform EXPLAIN query on DEFAULT handler!
2864 *
2865 * @param string SELECT Query
2866 * @return array The Explain result rows in an array
2867 * @todo Not supporting other than the default handler? And what about DBMS of other kinds than MySQL - support for EXPLAIN?
2868 */
2869 public function debug_explain($query) {
2870 $output = array();
2871 $hType = (string)$this->handlerCfg[$this->lastHandlerKey]['type'];
2872 switch ($hType) {
2873 case 'native':
2874 $res = $this->sql_query('EXPLAIN '.$query);
2875 while($row = $this->sql_fetch_assoc($res)) {
2876 $output[] = $row;
2877 }
2878 break;
2879 case 'adodb':
2880 switch ($this->handlerCfg['_DEFAULT']['config']['driver']) {
2881 case 'oci8':
2882 $res = $this->sql_query('EXPLAIN PLAN '.$query);
2883 $output[] = 'EXPLAIN PLAN data logged to default PLAN_TABLE';
2884 break;
2885 default:
2886 $res = $this->sql_query