[!!!][TASK] Rewrite install tool
[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 case 'IFNULL':
1711
1712 case 'LOCATE':
1713 if ($where_clause[$k]['func']['table'] != '') {
1714 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1715 }
1716 if ($where_clause[$k]['func']['field'] != '') {
1717 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1718 }
1719 break;
1720 }
1721 } else {
1722 if ($where_clause[$k]['table'] != '') {
1723 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1724 }
1725 if (!is_numeric($where_clause[$k]['field'])) {
1726 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1727 }
1728 if (isset($where_clause[$k]['calc_table'])) {
1729 if ($where_clause[$k]['calc_table'] != '') {
1730 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1731 }
1732 if ($where_clause[$k]['calc_field'] != '') {
1733 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1734 }
1735 }
1736 }
1737 if ($where_clause[$k]['comparator']) {
1738 if (isset($v['value']['operator'])) {
1739 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1740 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1741 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1742 }
1743 } else {
1744 // Detecting value type; list or plain:
1745 if (\TYPO3\CMS\Core\Utility\GeneralUtility::inList('NOTIN,IN', strtoupper(str_replace(array(' ', '
1746 ', '
1747 ', ' '), '', $where_clause[$k]['comparator'])))) {
1748 if (isset($v['subquery'])) {
1749 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1750 }
1751 } else {
1752 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], '.')) {
1753 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1754 }
1755 }
1756 }
1757 }
1758 }
1759 return $where_clause;
1760 }
1761
1762 /**
1763 * Quotes the field (and table) names within a group by clause with the quote
1764 * character suitable for the DB being used
1765 *
1766 * @param string A group by clause that can by parsed by parseFieldList
1767 * @return string Usable group by clause with quoted field/table names
1768 */
1769 protected function quoteGroupBy($groupBy) {
1770 if ($groupBy === '') {
1771 return '';
1772 }
1773 if ($this->runningNative()) {
1774 return $groupBy;
1775 }
1776 $groupBy = $this->SQLparser->parseFieldList($groupBy);
1777 $groupBy = $this->_quoteGroupBy($groupBy);
1778 return $this->SQLparser->compileFieldList($groupBy);
1779 }
1780
1781 /**
1782 * Quotes field names in a SQL GROUP BY clause acccording to DB rules
1783 *
1784 * @param array $groupBy The parsed GROUP BY clause to quote
1785 * @return array
1786 * @see quoteGroupBy()
1787 */
1788 protected function _quoteGroupBy(array $groupBy) {
1789 foreach ($groupBy as $k => $v) {
1790 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1791 if ($groupBy[$k]['table'] != '') {
1792 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1793 }
1794 }
1795 return $groupBy;
1796 }
1797
1798 /**
1799 * Quotes the field (and table) names within an order by clause with the quote
1800 * character suitable for the DB being used
1801 *
1802 * @param string An order by clause that can by parsed by parseFieldList
1803 * @return string Usable order by clause with quoted field/table names
1804 */
1805 protected function quoteOrderBy($orderBy) {
1806 if ($orderBy === '') {
1807 return '';
1808 }
1809 if ($this->runningNative()) {
1810 return $orderBy;
1811 }
1812 $orderBy = $this->SQLparser->parseFieldList($orderBy);
1813 $orderBy = $this->_quoteOrderBy($orderBy);
1814 return $this->SQLparser->compileFieldList($orderBy);
1815 }
1816
1817 /**
1818 * Quotes field names in a SQL ORDER BY clause acccording to DB rules
1819 *
1820 * @param array $orderBy The parsed ORDER BY clause to quote
1821 * @return array
1822 * @see quoteOrderBy()
1823 */
1824 protected function _quoteOrderBy(array $orderBy) {
1825 foreach ($orderBy as $k => $v) {
1826 if ($orderBy[$k]['table'] === '' && $v['field'] !== '' && ctype_digit($v['field'])) {
1827 continue;
1828 }
1829 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1830 if ($orderBy[$k]['table'] !== '') {
1831 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1832 }
1833 }
1834 return $orderBy;
1835 }
1836
1837 /**************************************
1838 *
1839 * Various helper functions
1840 *
1841 **************************************/
1842 /**
1843 * Escaping and quoting values for SQL statements.
1844 *
1845 * @param string Input string
1846 * @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!).
1847 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1848 * @see quoteStr()
1849 */
1850 public function fullQuoteStr($str, $table) {
1851 return '\'' . $this->quoteStr($str, $table) . '\'';
1852 }
1853
1854 /**
1855 * Substitution for PHP function "addslashes()"
1856 * 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()!
1857 *
1858 * @param string Input string
1859 * @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!).
1860 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1861 * @see quoteStr()
1862 */
1863 public function quoteStr($str, $table) {
1864 $this->lastHandlerKey = $this->handler_getFromTableList($table);
1865 switch ((string) $this->handlerCfg[$this->lastHandlerKey]['type']) {
1866 case 'native':
1867 if ($this->handlerInstance[$this->lastHandlerKey]['link']) {
1868 if ($this->lastHandlerKey === '_DEFAULT' && !$this->isConnected()) {
1869 $this->connectDB();
1870 }
1871 $str = mysql_real_escape_string($str, $this->handlerInstance[$this->lastHandlerKey]['link']);
1872 } else {
1873 // link may be null when unit testing DBAL
1874 $str = str_replace('\'', '\\\'', $str);
1875 }
1876 break;
1877 case 'adodb':
1878 $str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str), 1, -1);
1879 break;
1880 case 'userdefined':
1881 $str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
1882 break;
1883 default:
1884 throw new \RuntimeException('No handler found!!!', 1310027655);
1885 break;
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 break;
1939 }
1940 return $str;
1941 }
1942
1943 /**
1944 * Return MetaType for native MySQL field type
1945 *
1946 * @param string native type as reported as in mysqldump files
1947 * @return string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
1948 */
1949 public function MySQLMetaType($t) {
1950 switch (strtoupper($t)) {
1951 case 'STRING':
1952
1953 case 'CHAR':
1954
1955 case 'VARCHAR':
1956
1957 case 'TINYBLOB':
1958
1959 case 'TINYTEXT':
1960
1961 case 'ENUM':
1962
1963 case 'SET':
1964 return 'C';
1965 case 'TEXT':
1966
1967 case 'LONGTEXT':
1968
1969 case 'MEDIUMTEXT':
1970 return 'XL';
1971 case 'IMAGE':
1972
1973 case 'LONGBLOB':
1974
1975 case 'BLOB':
1976
1977 case 'MEDIUMBLOB':
1978 return 'B';
1979 case 'YEAR':
1980
1981 case 'DATE':
1982 return 'D';
1983 case 'TIME':
1984
1985 case 'DATETIME':
1986
1987 case 'TIMESTAMP':
1988 return 'T';
1989 case 'FLOAT':
1990
1991 case 'DOUBLE':
1992 return 'F';
1993 case 'INT':
1994
1995 case 'INTEGER':
1996
1997 case 'TINYINT':
1998
1999 case 'SMALLINT':
2000
2001 case 'MEDIUMINT':
2002
2003 case 'BIGINT':
2004 return 'I8';
2005 default:
2006 return 'N';
2007 }
2008 }
2009
2010 /**
2011 * Return actual MySQL type for meta field type
2012 *
2013 * @param string Meta type (currenly ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2014 * @return string native type as reported as in mysqldump files, uppercase
2015 */
2016 public function MySQLActualType($meta) {
2017 switch (strtoupper($meta)) {
2018 case 'C':
2019 return 'VARCHAR';
2020 case 'XL':
2021
2022 case 'X':
2023 return 'LONGTEXT';
2024 case 'C2':
2025 return 'VARCHAR';
2026 case 'X2':
2027 return 'LONGTEXT';
2028 case 'B':
2029 return 'LONGBLOB';
2030 case 'D':
2031 return 'DATE';
2032 case 'T':
2033 return 'DATETIME';
2034 case 'L':
2035 return 'TINYINT';
2036 case 'I':
2037
2038 case 'I1':
2039
2040 case 'I2':
2041
2042 case 'I4':
2043
2044 case 'I8':
2045 return 'BIGINT';
2046 case 'F':
2047 return 'DOUBLE';
2048 case 'N':
2049 return 'NUMERIC';
2050 default:
2051 return $meta;
2052 }
2053 }
2054
2055 /**************************************
2056 *
2057 * SQL wrapper functions (Overriding parent methods)
2058 * (For use in your applications)
2059 *
2060 **************************************/
2061 /**
2062 * Returns the error status on the most recent sql() execution (based on $this->lastHandlerKey)
2063 *
2064 * @return string Handler error strings
2065 */
2066 public function sql_error() {
2067 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2068 case 'native':
2069 $output = mysql_error($this->handlerInstance[$this->lastHandlerKey]['link']);
2070 break;
2071 case 'adodb':
2072 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorMsg();
2073 break;
2074 case 'userdefined':
2075 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_error();
2076 break;
2077 }
2078 return $output;
2079 }
2080
2081 /**
2082 * Returns the error number on the most recent sql() execution (based on $this->lastHandlerKey)
2083 *
2084 * @return int Handler error number
2085 */
2086 public function sql_errno() {
2087 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2088 case 'native':
2089 $output = mysql_errno($this->handlerInstance[$this->lastHandlerKey]['link']);
2090 break;
2091 case 'adodb':
2092 $output = $this->handlerInstance[$this->lastHandlerKey]->ErrorNo();
2093 break;
2094 case 'userdefined':
2095 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_errno();
2096 break;
2097 }
2098 return $output;
2099 }
2100
2101 /**
2102 * Returns the number of selected rows.
2103 *
2104 * @param pointer Result pointer / DBAL object
2105 * @return integer Number of resulting rows.
2106 */
2107 public function sql_num_rows($res) {
2108 if ($res === FALSE) {
2109 return FALSE;
2110 }
2111 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
2112 $output = 0;
2113 switch ($handlerType) {
2114 case 'native':
2115 $output = mysql_num_rows($res);
2116 break;
2117 case 'adodb':
2118 $output = method_exists($res, 'RecordCount') ? $res->RecordCount() : 0;
2119 break;
2120 case 'userdefined':
2121 $output = $res->sql_num_rows();
2122 break;
2123 }
2124 return $output;
2125 }
2126
2127 /**
2128 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
2129 *
2130 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
2131 * @return array Associative array of result row.
2132 */
2133 public function sql_fetch_assoc($res) {
2134 $output = FALSE;
2135 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : (is_resource($res) ? 'native' : FALSE);
2136 switch ($handlerType) {
2137 case 'native':
2138 $output = mysql_fetch_assoc($res);
2139 $tableList = $this->resourceIdToTableNameMap[(string) $res];
2140 // Reading list of tables from SELECT query:
2141 break;
2142 case 'adodb':
2143 // Check if method exists for the current $res object.
2144 // If a table exists in TCA but not in the db, a error
2145 // occured because $res is not a valid object.
2146 if (method_exists($res, 'FetchRow')) {
2147 $output = $res->FetchRow();
2148 $tableList = $res->TYPO3_DBAL_tableList;
2149 // Reading list of tables from SELECT query:
2150 // Removing all numeric/integer keys.
2151 // A workaround because in ADOdb we would need to know what we want before executing the query...
2152 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2153 // we don't need to remove anything.
2154 if (is_array($output)) {
2155 if ($this->runningADOdbDriver('mssql')) {
2156 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2157 foreach ($output as $key => $value) {
2158 if ($value === ' ') {
2159 $output[$key] = '';
2160 }
2161 }
2162 } else {
2163 foreach ($output as $key => $value) {
2164 if (is_integer($key)) {
2165 unset($output[$key]);
2166 }
2167 }
2168 }
2169 }
2170 }
2171 break;
2172 case 'userdefined':
2173 $output = $res->sql_fetch_assoc();
2174 $tableList = $res->TYPO3_DBAL_tableList;
2175 // Reading list of tables from SELECT query:
2176 break;
2177 }
2178 // Table/Fieldname mapping:
2179 if (is_array($output)) {
2180 if ($tables = $this->map_needMapping($tableList, TRUE)) {
2181 $output = $this->map_assocArray($output, $tables, 1);
2182 }
2183 }
2184 // Return result:
2185 return $output;
2186 }
2187
2188 /**
2189 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
2190 * The array contains the values in numerical indices.
2191 *
2192 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
2193 * @return array Array with result rows.
2194 */
2195 public function sql_fetch_row($res) {
2196 $output = FALSE;
2197 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
2198 switch ($handlerType) {
2199 case 'native':
2200 $output = mysql_fetch_row($res);
2201 break;
2202 case 'adodb':
2203 // Check if method exists for the current $res object.
2204 // If a table exists in TCA but not in the db, a error
2205 // occured because $res is not a valid object.
2206 if (method_exists($res, 'FetchRow')) {
2207 $output = $res->FetchRow();
2208 // Removing all assoc. keys.
2209 // A workaround because in ADOdb we would need to know what we want before executing the query...
2210 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2211 // we need to convert resultset.
2212 if (is_array($output)) {
2213 $keyIndex = 0;
2214 foreach ($output as $key => $value) {
2215 unset($output[$key]);
2216 if (is_integer($key) || $this->runningADOdbDriver('mssql')) {
2217 $output[$keyIndex] = $value;
2218 if ($value === ' ') {
2219 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2220 $output[$keyIndex] = '';
2221 }
2222 $keyIndex++;
2223 }
2224 }
2225 }
2226 }
2227 break;
2228 case 'userdefined':
2229 $output = $res->sql_fetch_row();
2230 break;
2231 }
2232 return $output;
2233 }
2234
2235 /**
2236 * Free result memory / unset result object
2237 *
2238 * @param pointer MySQL result pointer to free / DBAL object
2239 * @return boolean Returns TRUE on success or FALSE on failure.
2240 */
2241 public function sql_free_result($res) {
2242 if ($res === FALSE) {
2243 return FALSE;
2244 }
2245 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
2246 $output = TRUE;
2247 switch ($handlerType) {
2248 case 'native':
2249 $output = mysql_free_result($res);
2250 break;
2251 case 'adodb':
2252 if (method_exists($res, 'Close')) {
2253 $res->Close();
2254 unset($res);
2255 $output = TRUE;
2256 } else {
2257 $output = FALSE;
2258 }
2259 break;
2260 case 'userdefined':
2261 unset($res);
2262 break;
2263 }
2264 return $output;
2265 }
2266
2267 /**
2268 * Get the ID generated from the previous INSERT operation
2269 *
2270 * @return integer The uid of the last inserted record.
2271 */
2272 public function sql_insert_id() {
2273 $output = 0;
2274 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2275 case 'native':
2276 $output = mysql_insert_id($this->handlerInstance[$this->lastHandlerKey]['link']);
2277 break;
2278 case 'adodb':
2279 $output = $this->handlerInstance[$this->lastHandlerKey]->last_insert_id;
2280 break;
2281 case 'userdefined':
2282 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_insert_id();
2283 break;
2284 }
2285 return $output;
2286 }
2287
2288 /**
2289 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
2290 *
2291 * @return integer Number of rows affected by last query
2292 */
2293 public function sql_affected_rows() {
2294 switch ($this->handlerCfg[$this->lastHandlerKey]['type']) {
2295 case 'native':
2296 $output = mysql_affected_rows();
2297 break;
2298 case 'adodb':
2299 $output = $this->handlerInstance[$this->lastHandlerKey]->Affected_Rows();
2300 break;
2301 case 'userdefined':
2302 $output = $this->handlerInstance[$this->lastHandlerKey]->sql_affected_rows();
2303 break;
2304 }
2305 return $output;
2306 }
2307
2308 /**
2309 * Move internal result pointer
2310 *
2311 * @param pointer MySQL result pointer (of SELECT query) / DBAL object
2312 * @param integer Seek result number.
2313 * @return boolean Returns TRUE on success or FALSE on failure.
2314 */
2315 public function sql_data_seek($res, $seek) {
2316 $output = TRUE;
2317 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
2318 switch ($handlerType) {
2319 case 'native':
2320 $output = mysql_data_seek($res, $seek);
2321 break;
2322 case 'adodb':
2323 $output = $res->Move($seek);
2324 break;
2325 case 'userdefined':
2326 $output = $res->sql_data_seek($seek);
2327 break;
2328 }
2329 return $output;
2330 }
2331
2332 /**
2333 * Get the type of the specified field in a result
2334 *
2335 * If the first parameter is a string, it is used as table name for the lookup.
2336 *
2337 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
2338 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
2339 * @return string Returns the type of the specified field index
2340 */
2341 public function sql_field_metatype($table, $field) {
2342 // If $table and/or $field are mapped, use the original names instead
2343 foreach ($this->mapping as $tableName => $tableMapInfo) {
2344 if (isset($tableMapInfo['mapTableName']) && $tableMapInfo['mapTableName'] === $table) {
2345 // Table name is mapped => use original name
2346 $table = $tableName;
2347 }
2348 if (isset($tableMapInfo['mapFieldNames'])) {
2349 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
2350 if ($fieldMapInfo === $field) {
2351 // Field name is mapped => use original name
2352 $field = $fieldName;
2353 }
2354 }
2355 }
2356 }
2357 return $this->cache_fieldType[$table][$field]['metaType'];
2358 }
2359
2360 /**
2361 * Get the type of the specified field in a result
2362 *
2363 * If the first parameter is a string, it is used as table name for the lookup.
2364 *
2365 * @param pointer MySQL result pointer (of SELECT query) / DBAL object / table name
2366 * @param integer Field index. In case of ADOdb a string (field name!) FIXME
2367 * @return string Returns the type of the specified field index
2368 */
2369 public function sql_field_type($res, $pointer) {
2370 if ($res === NULL) {
2371 debug(array('no res in sql_field_type!'));
2372 return 'text';
2373 } elseif (is_string($res)) {
2374 if ($res === 'tx_dbal_debuglog') {
2375 return 'text';
2376 }
2377 $handlerType = 'adodb';
2378 } else {
2379 $handlerType = is_object($res) ? $res->TYPO3_DBAL_handlerType : 'native';
2380 }
2381 $output = '';
2382 switch ($handlerType) {
2383 case 'native':
2384 $output = mysql_field_type($res, $pointer);
2385 break;
2386 case 'adodb':
2387 if (is_string($pointer)) {
2388 $output = $this->cache_fieldType[$res][$pointer]['type'];
2389 }
2390 break;
2391 case 'userdefined':
2392 $output = $res->sql_field_type($pointer);
2393 break;
2394 }
2395 return $output;
2396 }
2397
2398 /**********
2399 *
2400 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
2401 * Deprecated or still experimental.
2402 *
2403 **********/
2404 /**
2405 * Executes a query
2406 * EXPERIMENTAL - This method will make its best to handle the query correctly
2407 * but if it cannot, it will simply pass the query to DEFAULT handler.
2408 *
2409 * You should use exec_* function from this class instead!
2410 * If you don't, anything that does not use the _DEFAULT handler will probably break!
2411 *
2412 * This method was deprecated in TYPO3 4.1 but is considered experimental since TYPO3 4.4
2413 * as it tries to handle the query correctly anyway.
2414 *
2415 * @param string Query to execute
2416 * @return pointer Result pointer / DBAL object
2417 */
2418 public function sql_query($query) {
2419 $globalConfig = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf']['dbal']);
2420 if ($globalConfig['sql_query.']['passthrough']) {
2421 return parent::sql_query($query);
2422 }
2423 // This method is heavily used by Extbase, try to handle it with DBAL-native methods
2424 $queryParts = $this->SQLparser->parseSQL($query);
2425 if (is_array($queryParts) && \TYPO3\CMS\Core\Utility\GeneralUtility::inList('SELECT,UPDATE,INSERT,DELETE', $queryParts['type'])) {
2426 return $this->exec_query($queryParts);
2427 }
2428 switch ($this->handlerCfg['_DEFAULT']['type']) {
2429 case 'native':
2430 $sqlResult = mysql_query($query, $this->handlerInstance['_DEFAULT']['link']);
2431 break;
2432 case 'adodb':
2433 $sqlResult = $this->handlerInstance['_DEFAULT']->Execute($query);
2434 $sqlResult->TYPO3_DBAL_handlerType = 'adodb';
2435 break;
2436 case 'userdefined':
2437 $sqlResult = $this->handlerInstance['_DEFAULT']->sql_query($query);
2438 $sqlResult->TYPO3_DBAL_handlerType = 'userdefined';
2439 break;
2440 }
2441 $this->lastHandlerKey = '_DEFAULT';
2442 if ($this->printErrors && $this->sql_error()) {
2443 debug(array($this->lastQuery, $this->sql_error()));
2444 }
2445 return $sqlResult;
2446 }
2447
2448 /**
2449 * Opening the _DEFAULT connection handler to the database.
2450 * 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())
2451 * You wouldn't need to use this at any time - let TYPO3 core handle this.
2452 *
2453 * @param string $host Deprecated since 6.1, will be removed in two versions. Database host IP/domain[:port]
2454 * @param string $username Deprecated since 6.1, will be removed in two versions. Username to connect with.
2455 * @param string $password Deprecated since 6.1, will be removed in two versions. Password to connect with.
2456 * @return mixed Returns handler connection value
2457 * @see handler_init()
2458 */
2459 public function sql_pconnect($host = NULL, $username = NULL, $password = NULL) {
2460 if ($host || $username || $password) {
2461 $this->handleDeprecatedConnectArguments($host, $username, $password);
2462 }
2463
2464 // Overriding the _DEFAULT handler configuration of username, password, localhost and database name:
2465 $this->handlerCfg['_DEFAULT']['config']['username'] = $this->databaseUsername;
2466 $this->handlerCfg['_DEFAULT']['config']['password'] = $this->databaseUserPassword;
2467 $this->handlerCfg['_DEFAULT']['config']['host'] = $this->databaseHost . ':' . $this->databasePort;
2468 $this->handlerCfg['_DEFAULT']['config']['database'] = $this->databaseName;
2469 // Initializing and output value:
2470 $sqlResult = $this->handler_init('_DEFAULT');
2471 return $sqlResult;
2472 }
2473
2474 /**
2475 * Select database for _DEFAULT handler.
2476 *
2477 * @param string Database to connect to.
2478 * @return boolean Always returns TRUE; function is obsolete, database selection is made in handler_init() function!
2479 */
2480 public function sql_select_db($TYPO3_db = '') {
2481 return TRUE;
2482 }
2483
2484 /**************************************
2485 *
2486 * SQL admin functions
2487 * (For use in the Install Tool and Extension Manager)
2488 *
2489 **************************************/
2490 /**
2491 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
2492 * Use in Install Tool only!
2493 * Usage count/core: 1
2494 *
2495 * @return array Each entry represents a database name
2496 */
2497 public function admin_get_dbs() {
2498 $dbArr = array();
2499 switch ($this->handlerCfg['_DEFAULT']['type']) {
2500 case 'native':
2501 $db_list = mysql_list_dbs($this->link);
2502 while ($row = mysql_fetch_object($db_list)) {
2503 if ($this->sql_select_db($row->Database)) {
2504 $dbArr[] = $row->Database;
2505 }
2506 }
2507 break;
2508 case 'adodb':
2509 // check needed for install tool - otherwise it will just die because the call to
2510 // MetaDatabases is done on a stdClass instance
2511 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaDatabases')) {
2512 $sqlDBs = $this->handlerInstance['_DEFAULT']->MetaDatabases();
2513 if (is_array($sqlDBs)) {
2514 foreach ($sqlDBs as $k => $theDB) {
2515 $dbArr[] = $theDB;
2516 }
2517 }
2518 }
2519 break;
2520 case 'userdefined':
2521 $dbArr = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2522 break;
2523 }
2524 return $dbArr;
2525 }
2526
2527 /**
2528 * Returns the list of tables from the system (quering the DBMSs)
2529 * It looks up all tables from the DBMS of the _DEFAULT handler and then add all tables *configured* to be managed by other handlers
2530 *
2531 * 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.
2532 *
2533 * @return array Tables in an array (tablename is in both key and value)
2534 * @todo Should the check for Oracle Recycle Bin stuff be moved elsewhere?
2535 * @todo Should return table details in value! see \TYPO3\CMS\Core\Database\DatabaseConnection::admin_get_tables()
2536 */
2537 public function admin_get_tables() {
2538 $whichTables = array();
2539 // Getting real list of tables:
2540 switch ($this->handlerCfg['_DEFAULT']['type']) {
2541 case 'native':
2542 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->handlerInstance['_DEFAULT']['link']);
2543 if (!$this->sql_error()) {
2544 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
2545 $whichTables[$theTable['Name']] = $theTable;
2546 }
2547 }
2548 break;
2549 case 'adodb':
2550 // check needed for install tool - otherwise it will just die because the call to
2551 // MetaTables is done on a stdClass instance
2552 if (method_exists($this->handlerInstance['_DEFAULT'], 'MetaTables')) {
2553 $sqlTables = $this->handlerInstance['_DEFAULT']->MetaTables('TABLES');
2554 foreach ($sqlTables as $k => $theTable) {
2555 if (preg_match('/BIN\\$/', $theTable)) {
2556 // Skip tables from the Oracle 10 Recycle Bin
2557 continue;
2558 }
2559 $whichTables[$theTable] = $theTable;
2560 }
2561 }
2562 break;
2563 case 'userdefined':
2564 $whichTables = $this->handlerInstance['_DEFAULT']->admin_get_tables();
2565 break;
2566 }
2567 // Check mapping:
2568 if (is_array($this->mapping) && count($this->mapping)) {
2569 // Mapping table names in reverse, first getting list of real table names:
2570 $tMap = array();
2571 foreach ($this->mapping as $tN => $tMapInfo) {
2572 if (isset($tMapInfo['mapTableName'])) {
2573 $tMap[$tMapInfo['mapTableName']] = $tN;
2574 }
2575 }
2576 // Do mapping:
2577 $newList = array();
2578 foreach ($whichTables as $tN => $tDefinition) {
2579 if (isset($tMap[$tN])) {
2580 $tN = $tMap[$tN];
2581 }
2582 $newList[$tN] = $tDefinition;
2583 }
2584 $whichTables = $newList;
2585 }
2586 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
2587 if (is_array($this->table2handlerKeys)) {
2588 foreach ($this->table2handlerKeys as $key => $handlerKey) {
2589 $whichTables[$key] = $key;
2590 }
2591 }
2592 return $whichTables;
2593 }
2594
2595 /**
2596 * Returns information about each field in the $table (quering the DBMS)
2597 * In a DBAL this should look up the right handler for the table and return compatible information
2598 * 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
2599 *
2600 * @param string Table name
2601 * @return array Field information in an associative array with fieldname => field row
2602 */
2603 public function admin_get_fields($tableName) {
2604 $output = array();
2605 // Do field mapping if needed:
2606 $ORIG_tableName = $tableName;
2607 if ($tableArray = $this->map_needMapping($tableName)) {
2608 // Table name:
2609 if ($this->mapping[$tableName]['mapTableName']) {
2610 $tableName = $this->mapping[$tableName]['mapTableName'];
2611 }
2612 }
2613 // Find columns
2614 $this->lastHandlerKey = $this->handler_getFromTableList($tableName);
2615 switch ((string) $this->handlerCfg[$this->lastHandlerKey]['type']) {
2616 case 'native':
2617 $columns_res = mysql_query('SHOW columns FROM ' . $tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
2618 while ($fieldRow = mysql_fetch_assoc($columns_res)) {
2619 $output[$fieldRow['Field']] = $fieldRow;
2620 }
2621 break;
2622 case 'adodb':
2623 $fieldRows = $this->handlerInstance[$this->lastHandlerKey]->MetaColumns($tableName, FALSE);
2624 if (is_array($fieldRows)) {
2625 foreach ($fieldRows as $k => $fieldRow) {
2626 settype($fieldRow, 'array');
2627 $fieldRow['Field'] = $fieldRow['name'];
2628 $ntype = $this->MySQLActualType($this->MetaType($fieldRow['type'], $tableName));
2629 $ntype .= $fieldRow['max_length'] != -1 ? ($ntype == 'INT' ? '(11)' : '(' . $fieldRow['max_length'] . ')') : '';
2630 $fieldRow['Type'] = strtolower($ntype);
2631 $fieldRow['Null'] = '';
2632 $fieldRow['Key'] = '';
2633 $fieldRow['Default'] = $fieldRow['default_value'];
2634 $fieldRow['Extra'] = '';
2635 $output[$fieldRow['name']] = $fieldRow;
2636 }
2637 }
2638 break;
2639 case 'userdefined':
2640 $output = $this->handlerInstance[$this->lastHandlerKey]->admin_get_fields($tableName);
2641 break;
2642 }
2643 // mapping should be done:
2644 if (is_array($tableArray) && is_array($this->mapping[$ORIG_tableName]['mapFieldNames'])) {
2645 $revFields = array_flip($this->mapping[$ORIG_tableName]['mapFieldNames']);
2646 $newOutput = array();
2647 foreach ($output as $fN => $fInfo) {
2648 if (isset($revFields[$fN])) {
2649 $fN = $revFields[$fN];
2650 $fInfo['Field'] = $fN;
2651 }
2652 $newOutput[$fN] = $fInfo;
2653 }
2654 $output = $newOutput;
2655 }
2656 return $output;
2657 }
2658
2659 /**
2660 * Returns information about each index key in the $table (quering the DBMS)
2661 * In a DBAL this should look up the right handler for the table and return compatible information
2662 *
2663 * @param string Table name
2664 * @return array Key information in a numeric array
2665 */
2666 public function admin_get_keys($tableName) {
2667 $output = array();
2668 // Do field mapping if needed:
2669 $ORIG_tableName = $tableName;
2670 if ($tableArray = $this->map_needMapping($tableName)) {
2671 // Table name:
2672 if ($this->mapping[$tableName]['mapTableName']) {
2673 $tableName = $this->mapping[$tableName]['mapTableName'];
2674 }
2675 }
2676 // Find columns
2677 $this->lastHandlerKey = $this->handler_getFromTableList($tableName);
2678 switch ((string) $this->handlerCfg[$this->lastHandlerKey]['type']) {
2679 case 'native':
2680 $keyRes = mysql_query('SHOW keys FROM ' . $tableName, $this->handlerInstance[$this->lastHandlerKey]['link']);
2681 while ($keyRow = mysql_fetch_assoc($keyRes)) {
2682 $output[] = $keyRow;
2683 }
2684 break;
2685 case 'adodb':
2686 $keyRows = $this->handlerInstance[$this->lastHandlerKey]->MetaIndexes($tableName);
2687 if ($keyRows !== FALSE) {
2688 foreach ($keyRows as $k => $theKey) {
2689 $theKey['Table'] = $tableName;
2690 $theKey['Non_unique'] = (int) (!$theKey['unique']);
2691 $theKey['Key_name'] = str_replace($tableName . '_', '', $k);
2692 // the following are probably not needed anyway...
2693 $theKey['Collation'] = '';
2694 $theKey['Cardinality'] = '';
2695 $theKey['Sub_part'] = '';
2696 $theKey['Packed'] = '';
2697 $theKey['Null'] = '';
2698 $theKey['Index_type'] = '';
2699 $theKey['Comment'] = '';
2700 // now map multiple fields into multiple rows (we mimic MySQL, remember...)
2701 $keycols = $theKey['columns'];
2702 foreach ($keycols as $c => $theCol) {
2703 $theKey['Seq_in_index'] = $c + 1;
2704 $theKey['Column_name'] = $theCol;
2705 $output[] = $theKey;
2706 }
2707 }
2708 }
2709 $priKeyRow = $this->handlerInstance[$this->lastHandlerKey]->MetaPrimaryKeys($tableName);
2710 $theKey = array();
2711 $theKey['Table'] = $tableName;
2712 $theKey['Non_unique'] = 0;
2713 $theKey['Key_name'] = 'PRIMARY';
2714 // the following are probably not needed anyway...
2715 $theKey['Collation'] = '';
2716 $theKey['Cardinality'] = '';
2717 $theKey['Sub_part'] = '';
2718