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