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