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