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