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