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