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