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