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