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