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