2 namespace TYPO3\CMS\Dbal\Database
;
5 * This file is part of the TYPO3 CMS project.
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
14 * The TYPO3 project - inspiring people to share!
17 use TYPO3\CMS\Core\Utility\GeneralUtility
;
20 * TYPO3 database abstraction layer
22 class DatabaseConnection
extends \TYPO3\CMS\Core\Database\DatabaseConnection
27 protected $printErrors = false;
30 * Enable output of SQL errors after query executions.
33 public $debug = false;
39 public $conf = array();
42 * Configuration array, copied from TYPO3_CONF_VARS in constructor.
45 public $mapping = array();
51 protected $table2handlerKeys = array();
57 public $handlerCfg = array(
62 // Set by default (overridden)
64 // Set by default (overridden)
66 // Set by default (overridden)
68 // Set by default (overridden)
70 // ONLY "adodb" type; eg. "mysql"
72 // ONLY "adodb", first number in sequences/serials/...
74 // ONLY "adodb", whether to use NameQuote() method from ADOdb to quote names
81 * Contains instance of the handler objects as they are created.
83 * Exception is the native mySQL calls, which are registered as an array with keys
84 * "handlerType" = "native" and
85 * "link" pointing to the link object of the connection.
89 public $handlerInstance = array();
92 * Storage of the handler key of last ( SELECT) query - used for subsequent fetch-row calls etc.
95 public $lastHandlerKey = '';
98 * Storage of last SELECT query
101 protected $lastQuery = '';
104 * The last parsed query array
107 protected $lastParsedAndMappedQueryArray = array();
112 protected $resourceIdToTableNameMap = array();
117 protected $cache_handlerKeyFromTableList = array();
122 protected $cache_mappingFromTableList = array();
125 * parsed SQL from standard DB dump file
128 public $cache_autoIncFields = array();
133 public $cache_fieldType = array();
138 public $cache_primaryKeys = array();
143 protected $cacheIdentifier = 'DatabaseConnection_fieldInfo';
148 * @var \TYPO3\CMS\Dbal\Database\SqlParser
153 * @var \TYPO3\CMS\Install\Service\SqlSchemaMigrationService
155 protected $installerSql = null;
160 * @var \TYPO3\CMS\Core\Cache\Frontend\VariableFrontend
162 protected $queryCache;
165 * mysql_field_type compatibility map
166 * taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
167 * Constant numbers see http://php.net/manual/en/mysqli.constants.php
171 protected $mysqlDataTypeMapping = array(
172 MYSQLI_TYPE_TINY
=> 'tinyint',
173 MYSQLI_TYPE_CHAR
=> 'tinyint',
174 MYSQLI_TYPE_SHORT
=> 'smallint',
175 MYSQLI_TYPE_LONG
=> 'int',
176 MYSQLI_TYPE_FLOAT
=> 'float',
177 MYSQLI_TYPE_DOUBLE
=> 'double',
178 MYSQLI_TYPE_TIMESTAMP
=> 'timestamp',
179 MYSQLI_TYPE_LONGLONG
=> 'bigint',
180 MYSQLI_TYPE_INT24
=> 'mediumint',
181 MYSQLI_TYPE_DATE
=> 'date',
182 MYSQLI_TYPE_NEWDATE
=> 'date',
183 MYSQLI_TYPE_TIME
=> 'time',
184 MYSQLI_TYPE_DATETIME
=> 'datetime',
185 MYSQLI_TYPE_YEAR
=> 'year',
186 MYSQLI_TYPE_BIT
=> 'bit',
187 MYSQLI_TYPE_INTERVAL
=> 'interval',
188 MYSQLI_TYPE_ENUM
=> 'enum',
189 MYSQLI_TYPE_SET
=> 'set',
190 MYSQLI_TYPE_TINY_BLOB
=> 'blob',
191 MYSQLI_TYPE_MEDIUM_BLOB
=> 'blob',
192 MYSQLI_TYPE_LONG_BLOB
=> 'blob',
193 MYSQLI_TYPE_BLOB
=> 'blob',
194 MYSQLI_TYPE_VAR_STRING
=> 'varchar',
195 MYSQLI_TYPE_STRING
=> 'char',
196 MYSQLI_TYPE_DECIMAL
=> 'decimal',
197 MYSQLI_TYPE_NEWDECIMAL
=> 'decimal',
198 MYSQLI_TYPE_GEOMETRY
=> 'geometry'
202 * @var Specifics\AbstractSpecifics
204 protected $dbmsSpecifics;
208 * Creates SQL parser object and imports configuration from $TYPO3_CONF_VARS['EXTCONF']['dbal']
210 public function __construct()
212 // Set SQL parser object for internal use:
213 $this->SQLparser
= GeneralUtility
::makeInstance(\TYPO3\CMS\Dbal\Database\SqlParser
::class, $this);
214 $this->installerSql
= GeneralUtility
::makeInstance(\TYPO3\CMS\Install\Service\SqlSchemaMigrationService
::class);
215 $this->queryCache
= GeneralUtility
::makeInstance(\TYPO3\CMS\Core\Cache\CacheManager
::class)->getCache('dbal');
216 // Set internal variables with configuration:
217 $this->conf
= $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
221 * Initialize the database connection
225 public function initialize()
227 // Set outside configuration:
228 if (isset($this->conf
['mapping'])) {
229 $this->mapping
= $this->conf
['mapping'];
231 if (isset($this->conf
['table2handlerKeys'])) {
232 $this->table2handlerKeys
= $this->conf
['table2handlerKeys'];
235 $specificsClassName = Specifics\NullSpecifics
::class;
236 if (isset($this->conf
['handlerCfg'])) {
237 $this->handlerCfg
= $this->conf
['handlerCfg'];
239 if (isset($this->handlerCfg
['_DEFAULT']['config']['driver'])) {
240 // load DBMS specifics
241 $driver = $this->handlerCfg
['_DEFAULT']['config']['driver'];
242 $className = 'TYPO3\\CMS\\Dbal\\Database\\Specifics\\' . ucfirst(strtolower($driver)) . 'Specifics';
243 if (class_exists($className)) {
244 if (!is_subclass_of($className, Specifics\AbstractSpecifics
::class)) {
245 throw new \
InvalidArgumentException($className . ' must inherit from ' . Specifics\AbstractSpecifics
::class, 1416919866);
247 $specificsClassName = $className;
251 $this->dbmsSpecifics
= GeneralUtility
::makeInstance($specificsClassName);
252 $this->cacheFieldInfo();
253 // Debugging settings:
254 $this->printErrors
= !empty($this->conf
['debugOptions']['printErrors']);
255 $this->debug
= !empty($this->conf
['debugOptions']['enabled']);
259 * Gets the DBMS specifics object
261 * @return Specifics\AbstractSpecifics
263 public function getSpecifics()
265 return $this->dbmsSpecifics
;
269 * @return \TYPO3\CMS\Core\Cache\Frontend\PhpFrontend
271 protected function getFieldInfoCache()
273 return GeneralUtility
::makeInstance(\TYPO3\CMS\Core\Cache\CacheManager
::class)->getCache('cache_phpcode');
277 * Clears the cached field information file.
281 public function clearCachedFieldInfo()
283 $this->getFieldInfoCache()->flushByTag('DatabaseConnection');
287 * Caches the field information.
291 public function cacheFieldInfo()
293 $phpCodeCache = $this->getFieldInfoCache();
294 // try to fetch cache
295 // cache is flushed when admin_query() is called
296 if ($phpCodeCache->has($this->cacheIdentifier
)) {
297 $fieldInformation = $phpCodeCache->requireOnce($this->cacheIdentifier
);
298 $this->cache_autoIncFields
= $fieldInformation['incFields'];
299 $this->cache_fieldType
= $fieldInformation['fieldTypes'];
300 $this->cache_primaryKeys
= $fieldInformation['primaryKeys'];
302 $this->analyzeCachingTables();
303 $this->analyzeExtensionTables();
304 $completeFieldInformation = $this->getCompleteFieldInformation();
305 $phpCodeCache->set($this->cacheIdentifier
, $this->getCacheableString($completeFieldInformation), array('DatabaseConnection'));
310 * Loop through caching configurations
311 * to find the usage of database backends and
312 * parse and analyze table definitions
316 protected function analyzeCachingTables()
318 $schemaService = GeneralUtility
::makeInstance(\TYPO3\CMS\Core\Cache\DatabaseSchemaService
::class);
319 $this->parseAndAnalyzeSql($schemaService->getCachingFrameworkRequiredDatabaseSchema());
323 * Loop over all installed extensions
324 * parse and analyze table definitions (if any)
328 protected function analyzeExtensionTables()
330 if (isset($GLOBALS['TYPO3_LOADED_EXT']) && (is_array($GLOBALS['TYPO3_LOADED_EXT']) ||
$GLOBALS['TYPO3_LOADED_EXT'] instanceof \ArrayAccess
)) {
331 foreach ($GLOBALS['TYPO3_LOADED_EXT'] as $extensionConfiguration) {
332 $isArray = (is_array($extensionConfiguration) ||
$extensionConfiguration instanceof \ArrayAccess
);
333 if (!$isArray ||
($isArray && !isset($extensionConfiguration['ext_tables.sql']))) {
336 $extensionsSql = file_get_contents($extensionConfiguration['ext_tables.sql']);
337 $this->parseAndAnalyzeSql($extensionsSql);
343 * Parse and analyze given SQL string
348 protected function parseAndAnalyzeSql($sql)
350 $parsedSql = $this->installerSql
->getFieldDefinitions_fileContent($sql);
351 $this->analyzeFields($parsedSql);
355 * Returns all field information gathered during
356 * analyzing all tables and fields.
360 protected function getCompleteFieldInformation()
362 return array('incFields' => $this->cache_autoIncFields
, 'fieldTypes' => $this->cache_fieldType
, 'primaryKeys' => $this->cache_primaryKeys
);
366 * Creates a PHP code representation of the array that can be cached
367 * in the PHP code cache.
369 * @param array $fieldInformation
372 protected function getCacheableString(array $fieldInformation)
374 $cacheString = 'return ';
375 $cacheString .= var_export($fieldInformation, true);
381 * Analyzes fields and adds the extracted information to the field type, auto increment and primary key info caches.
383 * @param array $parsedExtSQL The output produced by \TYPO3\CMS\Install\Service\SqlSchemaMigrationService->getFieldDefinitions_fileContent()
386 protected function analyzeFields($parsedExtSQL)
388 foreach ($parsedExtSQL as $table => $tdef) {
389 // check if table is mapped
390 if (isset($this->mapping
[$table])) {
391 $table = $this->mapping
[$table]['mapTableName'];
393 if (is_array($tdef['fields'])) {
394 foreach ($tdef['fields'] as $field => $fdefString) {
395 $fdef = $this->SQLparser
->parseFieldDef($fdefString);
396 $fieldType = isset($fdef['fieldType']) ?
$fdef['fieldType'] : '';
397 $this->cache_fieldType
[$table][$field]['type'] = $fieldType;
398 $this->cache_fieldType
[$table][$field]['metaType'] = $this->dbmsSpecifics
->getMetaFieldType($fieldType);
399 $this->cache_fieldType
[$table][$field]['notnull'] = isset($fdef['featureIndex']['NOTNULL']) && !$this->SQLparser
->checkEmptyDefaultValue($fdef['featureIndex']) ?
1 : 0;
400 if (isset($fdef['featureIndex']['DEFAULT'])) {
401 $default = $fdef['featureIndex']['DEFAULT']['value'][0];
402 if (isset($fdef['featureIndex']['DEFAULT']['value'][1])) {
403 $default = $fdef['featureIndex']['DEFAULT']['value'][1] . $default . $fdef['featureIndex']['DEFAULT']['value'][1];
405 $this->cache_fieldType
[$table][$field]['default'] = $default;
407 if (isset($fdef['featureIndex']['AUTO_INCREMENT'])) {
408 $this->cache_autoIncFields
[$table] = $field;
410 if (isset($tdef['keys']['PRIMARY'])) {
411 $this->cache_primaryKeys
[$table] = substr($tdef['keys']['PRIMARY'], 13, -1);
419 * This function builds all definitions for mapped tables and fields
421 * @param array $fieldInfo
424 * @see cacheFieldInfo()
426 protected function mapCachedFieldInfo(array $fieldInfo)
428 if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'])) {
429 foreach ($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping'] as $mappedTable => $mappedConf) {
430 if (array_key_exists($mappedTable, $fieldInfo['incFields'])) {
431 $mappedTableAlias = $mappedConf['mapTableName'];
432 if (isset($mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]])) {
433 $fieldInfo['incFields'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['incFields'][$mappedTable]];
435 $fieldInfo['incFields'][$mappedTableAlias] = $fieldInfo['incFields'][$mappedTable];
438 if (array_key_exists($mappedTable, $fieldInfo['fieldTypes'])) {
439 $tempMappedFieldConf = array();
440 foreach ($fieldInfo['fieldTypes'][$mappedTable] as $field => $fieldConf) {
441 $tempMappedFieldConf[$mappedConf['mapFieldNames'][$field]] = $fieldConf;
443 $fieldInfo['fieldTypes'][$mappedConf['mapTableName']] = $tempMappedFieldConf;
445 if (array_key_exists($mappedTable, $fieldInfo['primaryKeys'])) {
446 $mappedTableAlias = $mappedConf['mapTableName'];
447 if (isset($mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]])) {
448 $fieldInfo['primaryKeys'][$mappedTableAlias] = $mappedConf['mapFieldNames'][$fieldInfo['primaryKeys'][$mappedTable]];
450 $fieldInfo['primaryKeys'][$mappedTableAlias] = $fieldInfo['primaryKeys'][$mappedTable];
458 /************************************
460 * Query Building (Overriding parent methods)
461 * These functions are extending counterparts in the parent class.
463 **************************************/
465 * From the ADOdb documentation, this is what we do (_Execute for SELECT, _query for the other actions)Execute()
466 * is the default way to run queries. You can use the low-level functions _Execute() and _query() to reduce query overhead.
467 * Both these functions share the same parameters as Execute().If you do not have any bind parameters or your database
468 * supports binding (without emulation), then you can call _Execute() directly.
469 * Calling this function bypasses bind emulation. Debugging is still supported in _Execute().If you do not require
470 * debugging facilities nor emulated binding, and do not require a recordset to be returned, then you can call _query.
471 * This is great for inserts, updates and deletes. Calling this function bypasses emulated binding, debugging,
472 * and recordset handling. Either the resultid, TRUE or FALSE are returned by _query().
476 * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
477 * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
479 * @param string $table Table name
480 * @param array $fields_values 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.
481 * @param bool|array|string $no_quote_fields See fullQuoteArray()
482 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
483 * @throws \RuntimeException
485 public function exec_INSERTquery($table, $fields_values, $no_quote_fields = false)
487 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
488 // Do field mapping if needed:
489 $ORIG_tableName = $table;
490 if ($tableArray = $this->map_needMapping($table)) {
491 // Field mapping of array:
492 $fields_values = $this->map_assocArray($fields_values, $tableArray);
494 if ($this->mapping
[$table]['mapTableName']) {
495 $table = $this->mapping
[$table]['mapTableName'];
499 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
500 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
504 $this->lastQuery
= $this->INSERTquery($table, $fields_values, $no_quote_fields);
505 if (is_string($this->lastQuery
)) {
506 $sqlResult = $this->query($this->lastQuery
);
508 $sqlResult = $this->query($this->lastQuery
[0]);
509 $new_id = $this->sql_insert_id();
510 $where = $this->cache_autoIncFields
[$table] . '=' . $new_id;
511 foreach ($this->lastQuery
[1] as $field => $content) {
512 $stmt = 'UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table, true) . ' WHERE ' . $this->quoteWhereClause($where);
518 // auto generate ID for auto_increment fields if not present (static import needs this!)
519 // should we check the table name here (static_*)?
520 if (isset($this->cache_autoIncFields
[$table])) {
521 if (!isset($fields_values[$this->cache_autoIncFields
[$table]]) && !$this->handlerInstance
[$this->lastHandlerKey
]->hasInsertID
) {
522 // The table does not support auto-incremented fields, fall back to
523 // using a sequence table to simulate the auto-increment
524 $fields_values[$this->cache_autoIncFields
[$table]] = $this->handlerInstance
[$this->lastHandlerKey
]->GenID($table . '_' . $this->cache_autoIncFields
[$table], $this->handlerInstance
[$this->lastHandlerKey
]->sequenceStart
);
527 $this->lastQuery
= $this->INSERTquery($table, $fields_values, $no_quote_fields);
528 if (is_string($this->lastQuery
)) {
529 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
, false);
530 $this->updateLastInsertId($table, $fields_values);
532 $this->handlerInstance
[$this->lastHandlerKey
]->StartTrans();
533 if ((string)$this->lastQuery
[0] !== '') {
534 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
[0], false);
535 $new_id = $this->updateLastInsertId($table, $fields_values);
537 if (is_array($this->lastQuery
[1])) {
538 foreach ($this->lastQuery
[1] as $field => $content) {
539 if (empty($content)) {
542 if (isset($this->cache_autoIncFields
[$table]) && isset($new_id)) {
543 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateBlob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($this->cache_autoIncFields
[$table] . '=' . $new_id));
544 } elseif (isset($this->cache_primaryKeys
[$table])) {
546 $pks = explode(',', $this->cache_primaryKeys
[$table]);
547 foreach ($pks as $pk) {
548 if (isset($fields_values[$pk])) {
549 $where .= $pk . '=' . $this->fullQuoteStr($fields_values[$pk], $table) . ' AND ';
552 $where = $this->quoteWhereClause($where . '1=1');
553 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateBlob($this->quoteFromTables($table), $field, $content, $where);
555 $this->handlerInstance
[$this->lastHandlerKey
]->CompleteTrans(false);
556 // Should never ever happen
557 throw new \
RuntimeException('Could not update BLOB >>>> no WHERE clause found!', 1321860519);
561 if (is_array($this->lastQuery
[2])) {
562 foreach ($this->lastQuery
[2] as $field => $content) {
563 if (empty($content)) {
566 if (isset($this->cache_autoIncFields
[$table]) && isset($new_id)) {
567 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateClob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($this->cache_autoIncFields
[$table] . '=' . $new_id));
568 } elseif (isset($this->cache_primaryKeys
[$table])) {
570 $pks = explode(',', $this->cache_primaryKeys
[$table]);
571 foreach ($pks as $pk) {
572 if (isset($fields_values[$pk])) {
573 $where .= $pk . '=' . $this->fullQuoteStr($fields_values[$pk], $table) . ' AND ';
576 $where = $this->quoteWhereClause($where . '1=1');
577 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateClob($this->quoteFromTables($table), $field, $content, $where);
579 $this->handlerInstance
[$this->lastHandlerKey
]->CompleteTrans(false);
580 // Should never ever happen
581 throw new \
RuntimeException('Could not update CLOB >>>> no WHERE clause found!', 1310027337);
585 $this->handlerInstance
[$this->lastHandlerKey
]->CompleteTrans();
589 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->exec_INSERTquery($table, $fields_values, $no_quote_fields);
592 if ($this->printErrors
&& $this->sql_error()) {
593 debug(array($this->lastQuery
, $this->sql_error()));
596 $this->debugHandler('exec_INSERTquery', GeneralUtility
::milliseconds() - $pt, array(
597 'handlerType' => $hType,
598 'args' => array($table, $fields_values),
599 'ORIG_tablename' => $ORIG_tableName
602 foreach ($this->postProcessHookObjects
as $hookObject) {
603 $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
610 * Creates and executes an INSERT SQL-statement for $table with multiple rows.
612 * @param string $table Table name
613 * @param array $fields Field names
614 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
615 * @param bool|array|string $no_quote_fields See fullQuoteArray()
616 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
618 public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
621 if ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type'] === 'native') {
622 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
623 $res = $this->query(parent
::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
625 foreach ($rows as $row) {
626 $fields_values = array();
627 foreach ($fields as $key => $value) {
628 $fields_values[$value] = $row[$key];
630 $res = $this->exec_INSERTquery($table, $fields_values, $no_quote_fields);
633 foreach ($this->postProcessHookObjects
as $hookObject) {
634 $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
640 * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
641 * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
643 * @param string $table Database tablename
644 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
645 * @param array $fields_values 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.
646 * @param bool|array|string $no_quote_fields See fullQuoteArray()
647 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
649 public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
651 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
652 // Do table/field mapping:
653 $ORIG_tableName = $table;
654 if ($tableArray = $this->map_needMapping($table)) {
655 // Field mapping of array:
656 $fields_values = $this->map_assocArray($fields_values, $tableArray);
657 // Where clause table and field mapping:
658 $whereParts = $this->SQLparser
->parseWhereClause($where);
659 $this->map_sqlParts($whereParts, $tableArray[0]['table']);
660 $where = $this->SQLparser
->compileWhereClause($whereParts, false);
662 if ($this->mapping
[$table]['mapTableName']) {
663 $table = $this->mapping
[$table]['mapTableName'];
667 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
668 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
672 $this->lastQuery
= $this->UPDATEquery($table, $where, $fields_values, $no_quote_fields);
673 if (is_string($this->lastQuery
)) {
674 $sqlResult = $this->query($this->lastQuery
);
676 $sqlResult = $this->query($this->lastQuery
[0]);
677 foreach ($this->lastQuery
[1] as $field => $content) {
678 $stmt = 'UPDATE ' . $this->quoteFromTables($table) . ' SET ' . $this->quoteFromTables($field) . '=' . $this->fullQuoteStr($content, $table, true) . ' WHERE ' . $this->quoteWhereClause($where);
684 $this->lastQuery
= $this->UPDATEquery($table, $where, $fields_values, $no_quote_fields);
685 if (is_string($this->lastQuery
)) {
686 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
, false);
688 $this->handlerInstance
[$this->lastHandlerKey
]->StartTrans();
689 if ((string)$this->lastQuery
[0] !== '') {
690 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
[0], false);
692 if (is_array($this->lastQuery
[1])) {
693 foreach ($this->lastQuery
[1] as $field => $content) {
694 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateBlob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($where));
697 if (is_array($this->lastQuery
[2])) {
698 foreach ($this->lastQuery
[2] as $field => $content) {
699 $this->handlerInstance
[$this->lastHandlerKey
]->UpdateClob($this->quoteFromTables($table), $field, $content, $this->quoteWhereClause($where));
702 $this->handlerInstance
[$this->lastHandlerKey
]->CompleteTrans();
706 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields);
709 if ($this->printErrors
&& $this->sql_error()) {
710 debug(array($this->lastQuery
, $this->sql_error()));
713 $this->debugHandler('exec_UPDATEquery', GeneralUtility
::milliseconds() - $pt, array(
714 'handlerType' => $hType,
715 'args' => array($table, $where, $fields_values),
716 'ORIG_from_table' => $ORIG_tableName
719 foreach ($this->postProcessHookObjects
as $hookObject) {
720 $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
727 * Creates and executes a DELETE SQL-statement for $table where $where-clause
729 * @param string $table Database tablename
730 * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
731 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
733 public function exec_DELETEquery($table, $where)
735 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
736 // Do table/field mapping:
737 $ORIG_tableName = $table;
738 if ($tableArray = $this->map_needMapping($table)) {
740 $whereParts = $this->SQLparser
->parseWhereClause($where);
741 $this->map_sqlParts($whereParts, $tableArray[0]['table']);
742 $where = $this->SQLparser
->compileWhereClause($whereParts, false);
744 if ($this->mapping
[$table]['mapTableName']) {
745 $table = $this->mapping
[$table]['mapTableName'];
749 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
750 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
754 $this->lastQuery
= $this->DELETEquery($table, $where);
755 $sqlResult = $this->query($this->lastQuery
);
758 $this->lastQuery
= $this->DELETEquery($table, $where);
759 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
, false);
762 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->exec_DELETEquery($table, $where);
765 if ($this->printErrors
&& $this->sql_error()) {
766 debug(array($this->lastQuery
, $this->sql_error()));
769 $this->debugHandler('exec_DELETEquery', GeneralUtility
::milliseconds() - $pt, array(
770 'handlerType' => $hType,
771 'args' => array($table, $where),
772 'ORIG_from_table' => $ORIG_tableName
775 foreach ($this->postProcessHookObjects
as $hookObject) {
776 $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
783 * Creates and executes a SELECT SQL-statement
784 * Using this function specifically allow us to handle the LIMIT feature independently of DB.
786 * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
787 * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
788 * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
789 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
790 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
791 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
792 * @throws \RuntimeException
793 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
795 public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
797 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
798 // Map table / field names if needed:
799 $ORIG_tableName = $from_table;
800 // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
801 $parsedFromTable = array();
802 $remappedParameters = array();
803 if ($tableArray = $this->map_needMapping($ORIG_tableName, false, $parsedFromTable)) {
804 $from = $parsedFromTable ?
$parsedFromTable : $from_table;
805 $remappedParameters = $this->map_remapSELECTQueryParts($select_fields, $from, $where_clause, $groupBy, $orderBy);
807 // Get handler key and select API:
808 if (!empty($remappedParameters)) {
809 $mappedQueryParts = $this->compileSelectParameters($remappedParameters);
810 $fromTable = $mappedQueryParts[1];
812 $fromTable = $from_table;
814 $this->lastHandlerKey
= $this->handler_getFromTableList($fromTable);
815 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
819 if (!empty($remappedParameters)) {
820 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($remappedParameters);
822 $this->lastQuery
= $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
823 $sqlResult = $this->query($this->lastQuery
);
824 $this->resourceIdToTableNameMap
[serialize($sqlResult)] = $ORIG_tableName;
828 $splitLimit = GeneralUtility
::intExplode(',', $limit);
829 // Splitting the limit values:
830 if ($splitLimit[1]) {
831 // If there are two parameters, do mapping differently than otherwise:
832 $numrows = $splitLimit[1];
833 $offset = $splitLimit[0];
835 $numrows = $splitLimit[0];
838 if (!empty($remappedParameters)) {
839 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->SelectLimit($this->SELECTqueryFromArray($remappedParameters), $numrows, $offset);
841 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->SelectLimit($this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy), $numrows, $offset);
843 $this->lastQuery
= $sqlResult->sql
;
845 if (!empty($remappedParameters)) {
846 $this->lastQuery
= $this->SELECTqueryFromArray($remappedParameters);
848 $this->lastQuery
= $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
850 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_Execute($this->lastQuery
);
852 if (!is_object($sqlResult)) {
853 throw new \
RuntimeException('ADOdb could not run this query: ' . $this->lastQuery
, 1421053336);
855 $sqlResult->TYPO3_DBAL_handlerType
= 'adodb';
856 // Setting handler type in result object (for later recognition!)
857 $sqlResult->TYPO3_DBAL_tableList
= $ORIG_tableName;
860 if (!empty($remappedParameters)) {
861 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($remappedParameters);
863 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
864 if (is_object($sqlResult)) {
865 $sqlResult->TYPO3_DBAL_handlerType
= 'userdefined';
866 // Setting handler type in result object (for later recognition!)
867 $sqlResult->TYPO3_DBAL_tableList
= $ORIG_tableName;
871 if ($this->printErrors
&& $this->sql_error()) {
872 debug(array($this->lastQuery
, $this->sql_error()));
876 'handlerType' => $hType,
877 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit),
878 'ORIG_from_table' => $ORIG_tableName
880 if ($this->conf
['debugOptions']['numberRows']) {
881 $data['numberRows'] = $this->sql_num_rows($sqlResult);
883 $this->debugHandler('exec_SELECTquery', GeneralUtility
::milliseconds() - $pt, $data);
892 * @param string $table Database tablename
893 * @return mixed Result from handler
895 public function exec_TRUNCATEquery($table)
897 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
898 // Do table/field mapping:
899 $ORIG_tableName = $table;
900 if ($tableArray = $this->map_needMapping($table)) {
902 if ($this->mapping
[$table]['mapTableName']) {
903 $table = $this->mapping
[$table]['mapTableName'];
907 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
908 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
912 $this->lastQuery
= $this->TRUNCATEquery($table);
913 $sqlResult = $this->query($this->lastQuery
);
916 $this->lastQuery
= $this->TRUNCATEquery($table);
917 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->_query($this->lastQuery
, false);
920 $sqlResult = $this->handlerInstance
[$this->lastHandlerKey
]->exec_TRUNCATEquery($table);
923 if ($this->printErrors
&& $this->sql_error()) {
924 debug(array($this->lastQuery
, $this->sql_error()));
927 $this->debugHandler('exec_TRUNCATEquery', GeneralUtility
::milliseconds() - $pt, array(
928 'handlerType' => $hType,
929 'args' => array($table),
930 'ORIG_from_table' => $ORIG_tableName
933 foreach ($this->postProcessHookObjects
as $hookObject) {
934 $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
942 * EXPERIMENTAL since TYPO3 4.4.
944 * @param array $queryParts SQL parsed by method parseSQL() of \TYPO3\CMS\Dbal\Database\SqlParser
945 * @return \mysqli_result|object MySQLi result object / DBAL object
946 * @see self::sql_query()
948 protected function exec_query(array $queryParts)
950 switch ($queryParts['type']) {
952 $selectFields = $this->SQLparser
->compileFieldList($queryParts['SELECT']);
953 $fromTables = $this->SQLparser
->compileFromTables($queryParts['FROM']);
954 $whereClause = isset($queryParts['WHERE']) ?
$this->SQLparser
->compileWhereClause($queryParts['WHERE']) : '1=1';
955 $groupBy = isset($queryParts['GROUPBY']) ?
$this->SQLparser
->compileFieldList($queryParts['GROUPBY']) : '';
956 $orderBy = isset($queryParts['ORDERBY']) ?
$this->SQLparser
->compileFieldList($queryParts['ORDERBY']) : '';
957 $limit = isset($queryParts['LIMIT']) ?
$queryParts['LIMIT'] : '';
958 return $this->exec_SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy, $limit);
960 $table = $queryParts['TABLE'];
962 foreach ($queryParts['FIELDS'] as $fN => $fV) {
963 $fields[$fN] = $fV[0];
965 $whereClause = isset($queryParts['WHERE']) ?
$this->SQLparser
->compileWhereClause($queryParts['WHERE']) : '1=1';
966 return $this->exec_UPDATEquery($table, $whereClause, $fields);
968 $table = $queryParts['TABLE'];
970 if (isset($queryParts['VALUES_ONLY']) && is_array($queryParts['VALUES_ONLY'])) {
971 $fields = $GLOBALS['TYPO3_DB']->cache_fieldType
[$table];
973 foreach ($fields as $fn => $fd) {
974 $values[$fn] = $queryParts['VALUES_ONLY'][$fc++
][0];
977 foreach ($queryParts['FIELDS'] as $fN => $fV) {
978 $values[$fN] = $fV[0];
981 return $this->exec_INSERTquery($table, $values);
983 $table = $queryParts['TABLE'];
984 $whereClause = isset($queryParts['WHERE']) ?
$this->SQLparser
->compileWhereClause($queryParts['WHERE']) : '1=1';
985 return $this->exec_DELETEquery($table, $whereClause);
986 case 'TRUNCATETABLE':
987 $table = $queryParts['TABLE'];
988 return $this->exec_TRUNCATEquery($table);
995 * Central query method. Also checks if there is a database connection.
996 * Use this to execute database queries instead of directly calling $this->link->query()
998 * @param string $query The query to send to the database
999 * @return bool|\mysqli_result
1001 protected function query($query)
1003 if (!$this->isConnected()) {
1006 return $this->handlerInstance
[$this->lastHandlerKey
]['link']->query($query);
1009 /**************************************
1013 **************************************/
1015 * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
1017 * @param string $table See exec_INSERTquery()
1018 * @param array $fields_values See exec_INSERTquery()
1019 * @param bool|array|string $no_quote_fields See fullQuoteArray()
1020 * @return string|NULL Full SQL query for INSERT, NULL if $rows is empty
1022 public function INSERTquery($table, $fields_values, $no_quote_fields = false)
1024 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
1025 if (!is_array($fields_values) ||
empty($fields_values)) {
1028 foreach ($this->preProcessHookObjects
as $hookObject) {
1029 $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
1031 if (is_string($no_quote_fields)) {
1032 $no_quote_fields = explode(',', $no_quote_fields);
1033 } elseif (!is_array($no_quote_fields)) {
1034 $no_quote_fields = array();
1036 $blobFields = $clobFields = array();
1038 $handlerKey = $this->handler_getFromTableList($table);
1039 $quoteClob = isset($this->handlerCfg
[$handlerKey]['config']['quoteClob']) ?
$this->handlerCfg
[$handlerKey]['config']['quoteClob'] : false;
1040 foreach ($fields_values as $k => $v) {
1041 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
1042 // we skip the field in the regular INSERT statement, it is only in blobfields
1043 $blobFields[$this->quoteFieldNames($k)] = $v;
1044 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
1045 // we skip the field in the regular INSERT statement, it is only in clobfields
1046 $clobFields[$this->quoteFieldNames($k)] = $quoteClob ?
$this->quoteStr($v, $table) : $v;
1048 // Add slashes old-school:
1049 // cast numerical values
1050 $mt = $this->sql_field_metatype($table, $k);
1051 if ($mt[0] == 'I') {
1053 } elseif ($mt[0] == 'F') {
1056 $nArr[$this->quoteFieldNames($k)] = !in_array($k, $no_quote_fields) ?
$this->fullQuoteStr($v, $table, true) : $v;
1059 if (!empty($blobFields) ||
!empty($clobFields)) {
1061 if (!empty($nArr)) {
1062 $query[0] = 'INSERT INTO ' . $this->quoteFromTables($table) . '
1065 ', array_keys($nArr)) . '
1071 if (!empty($blobFields)) {
1072 $query[1] = $blobFields;
1074 if (!empty($clobFields)) {
1075 $query[2] = $clobFields;
1077 if (isset($query[0]) && ($this->debugOutput ||
$this->store_lastBuiltQuery
)) {
1078 $this->debug_lastBuiltQuery
= $query[0];
1081 $query = 'INSERT INTO ' . $this->quoteFromTables($table) . '
1084 ', array_keys($nArr)) . '
1089 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1090 $this->debug_lastBuiltQuery
= $query;
1097 * Creates an INSERT SQL-statement for $table with multiple rows.
1099 * @param string $table Table name
1100 * @param array $fields Field names
1101 * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
1102 * @param bool|array|string $no_quote_fields See fullQuoteArray()
1103 * @return string|array Full SQL query for INSERT (unless $rows does not contain any elements in which case it will be FALSE)
1105 public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
1107 if ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type'] === 'native') {
1108 return parent
::INSERTmultipleRows($table, $fields, $rows, $no_quote_fields);
1111 foreach ($rows as $row) {
1112 $fields_values = array();
1113 foreach ($fields as $key => $value) {
1114 $fields_values[$value] = $row[$key];
1116 $rowQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
1117 if (is_array($rowQuery)) {
1118 $result[] = $rowQuery;
1120 $result[][0] = $rowQuery;
1127 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
1130 * @param string $table See exec_UPDATEquery()
1131 * @param string $where See exec_UPDATEquery()
1132 * @param array $fields_values See exec_UPDATEquery()
1133 * @param bool|array|string $no_quote_fields See fullQuoteArray()
1134 * @throws \InvalidArgumentException
1135 * @return string Full SQL query for UPDATE
1137 public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
1139 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
1140 if (is_string($where)) {
1141 foreach ($this->preProcessHookObjects
as $hookObject) {
1142 $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
1144 $blobFields = $clobFields = array();
1146 if (is_array($fields_values) && !empty($fields_values)) {
1147 if (is_string($no_quote_fields)) {
1148 $no_quote_fields = explode(',', $no_quote_fields);
1149 } elseif (!is_array($no_quote_fields)) {
1150 $no_quote_fields = array();
1152 $handlerKey = $this->handler_getFromTableList($table);
1153 $quoteClob = isset($this->handlerCfg
[$handlerKey]['config']['quoteClob']) ?
$this->handlerCfg
[$handlerKey]['config']['quoteClob'] : false;
1154 foreach ($fields_values as $k => $v) {
1155 if (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'B') {
1156 // we skip the field in the regular UPDATE statement, it is only in blobfields
1157 $blobFields[$this->quoteFieldNames($k)] = $v;
1158 } elseif (!$this->runningNative() && $this->sql_field_metatype($table, $k) == 'XL') {
1159 // we skip the field in the regular UPDATE statement, it is only in clobfields
1160 $clobFields[$this->quoteFieldNames($k)] = $quoteClob ?
$this->quoteStr($v, $table) : $v;
1162 // Add slashes old-school:
1163 // cast numeric values
1164 $mt = $this->sql_field_metatype($table, $k);
1165 if ($mt[0] == 'I') {
1167 } elseif ($mt[0] == 'F') {
1170 $nArr[] = $this->quoteFieldNames($k) . '=' . (!in_array($k, $no_quote_fields) ?
$this->fullQuoteStr($v, $table, true) : $v);
1174 if (!empty($blobFields) ||
!empty($clobFields)) {
1176 if (!empty($nArr)) {
1177 $query[0] = 'UPDATE ' . $this->quoteFromTables($table) . '
1180 ', $nArr) . ($where !== '' ?
'
1182 ' . $this->quoteWhereClause($where) : '');
1184 if (!empty($blobFields)) {
1185 $query[1] = $blobFields;
1187 if (!empty($clobFields)) {
1188 $query[2] = $clobFields;
1190 if (isset($query[0]) && ($this->debugOutput ||
$this->store_lastBuiltQuery
)) {
1191 $this->debug_lastBuiltQuery
= $query[0];
1194 $query = 'UPDATE ' . $this->quoteFromTables($table) . '
1197 ', $nArr) . ($where !== '' ?
'
1199 ' . $this->quoteWhereClause($where) : '');
1200 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1201 $this->debug_lastBuiltQuery
= $query;
1206 throw new \
InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853887);
1211 * Creates a DELETE SQL-statement for $table where $where-clause
1213 * @param string $table See exec_DELETEquery()
1214 * @param string $where See exec_DELETEquery()
1215 * @return string Full SQL query for DELETE
1216 * @throws \InvalidArgumentException
1218 public function DELETEquery($table, $where)
1220 if (is_string($where)) {
1221 foreach ($this->preProcessHookObjects
as $hookObject) {
1222 $hookObject->DELETEquery_preProcessAction($table, $where, $this);
1224 $table = $this->quoteFromTables($table);
1225 $where = $this->quoteWhereClause($where);
1226 $query = 'DELETE FROM ' . $table . ($where !== '' ?
' WHERE ' . $where : '');
1227 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1228 $this->debug_lastBuiltQuery
= $query;
1232 throw new \
InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1310027383);
1237 * Creates a SELECT SQL-statement
1239 * @param string $select_fields See exec_SELECTquery()
1240 * @param string $from_table See exec_SELECTquery()
1241 * @param string $where_clause See exec_SELECTquery()
1242 * @param string $groupBy See exec_SELECTquery()
1243 * @param string $orderBy See exec_SELECTquery()
1244 * @param string $limit See exec_SELECTquery()
1245 * @return string Full SQL query for SELECT
1247 public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
1249 $this->lastHandlerKey
= $this->handler_getFromTableList($from_table);
1250 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
1251 if ($hType === 'adodb' && $this->runningADOdbDriver('postgres')) {
1252 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1253 $splitLimit = GeneralUtility
::intExplode(',', $limit);
1254 // Splitting the limit values:
1255 if ($splitLimit[1]) {
1256 // If there are two parameters, do mapping differently than otherwise:
1257 $numrows = $splitLimit[1];
1258 $offset = $splitLimit[0];
1259 $limit = $numrows . ' OFFSET ' . $offset;
1262 $select_fields = $this->quoteFieldNames($select_fields);
1263 $from_table = $this->quoteFromTables($from_table);
1264 $where_clause = $this->quoteWhereClause($where_clause);
1265 $groupBy = $this->quoteGroupBy($groupBy);
1266 $orderBy = $this->quoteOrderBy($orderBy);
1267 $this->dbmsSpecifics
->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1268 // Call parent method to build actual query
1269 $query = parent
::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1270 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1271 $this->debug_lastBuiltQuery
= $query;
1277 * Creates a SELECT SQL-statement to be used with an ADOdb backend.
1279 * @param array $params parsed parameters: array($select_fields, $from_table, $where_clause, $groupBy, $orderBy)
1280 * @return string Full SQL query for SELECT
1282 protected function SELECTqueryFromArray(array $params)
1285 $params[0] = $this->_quoteFieldNames($params[0]);
1287 $params[1] = $this->_quoteFromTables($params[1]);
1289 if (!empty($params[2])) {
1290 $params[2] = $this->_quoteWhereClause($params[2]);
1293 if (!empty($params[3])) {
1294 $params[3] = $this->_quoteGroupBy($params[3]);
1297 if (!empty($params[4])) {
1298 $params[4] = $this->_quoteOrderBy($params[4]);
1300 // Compile the SELECT parameters
1301 list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($params);
1302 $this->dbmsSpecifics
->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1303 // Call parent method to build actual query
1304 $query = parent
::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1305 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1306 $this->debug_lastBuiltQuery
= $query;
1312 * Compiles and returns an array of SELECTquery parameters (without $limit) to
1313 * be used with SELECTquery() or exec_SELECTquery().
1315 * @param array $params
1316 * @return array array($select_fields, $from_table, $where_clause, $groupBy, $orderBy)
1318 protected function compileSelectParameters(array $params)
1320 $select_fields = $this->SQLparser
->compileFieldList($params[0]);
1321 $from_table = $this->SQLparser
->compileFromTables($params[1]);
1322 $where_clause = !empty($params[2]) ?
$this->SQLparser
->compileWhereClause($params[2]) : '';
1323 $groupBy = !empty($params[3]) ?
$this->SQLparser
->compileFieldList($params[3]) : '';
1324 $orderBy = !empty($params[4]) ?
$this->SQLparser
->compileFieldList($params[4]) : '';
1325 return array($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1329 * Creates a TRUNCATE TABLE SQL-statement
1331 * @param string $table See exec_TRUNCATEquery()
1332 * @return string Full SQL query for TRUNCATE TABLE
1334 public function TRUNCATEquery($table)
1336 foreach ($this->preProcessHookObjects
as $hookObject) {
1337 $hookObject->TRUNCATEquery_preProcessAction($table, $this);
1339 $table = $this->quoteFromTables($table);
1340 // Build actual query
1341 $query = 'TRUNCATE TABLE ' . $table;
1342 if ($this->debugOutput ||
$this->store_lastBuiltQuery
) {
1343 $this->debug_lastBuiltQuery
= $query;
1348 /**************************************
1350 * Prepared Query Support
1352 **************************************/
1354 * Creates a SELECT prepared SQL statement.
1356 * @param string $select_fields See exec_SELECTquery()
1357 * @param string $from_table See exec_SELECTquery()
1358 * @param string $where_clause See exec_SELECTquery()
1359 * @param string $groupBy See exec_SELECTquery()
1360 * @param string $orderBy See exec_SELECTquery()
1361 * @param string $limit See exec_SELECTquery()
1362 * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
1363 * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
1365 public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array())
1367 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
1368 $precompiledParts = array();
1369 if ($this->queryCache
) {
1370 $cacheKey = 'prepare_SELECTquery-' . \TYPO3\CMS\Dbal\QueryCache
::getCacheKey(array(
1371 'selectFields' => $select_fields,
1372 'fromTable' => $from_table,
1373 'whereClause' => $where_clause,
1374 'groupBy' => $groupBy,
1375 'orderBy' => $orderBy,
1378 if ($this->queryCache
->has($cacheKey)) {
1379 $precompiledParts = $this->queryCache
->get($cacheKey);
1382 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit, $input_parameters),
1383 'precompiledParts' => $precompiledParts
1385 $this->debugHandler('prepare_SELECTquery (cache hit)', GeneralUtility
::milliseconds() - $pt, $data);
1389 $ORIG_tableName = '';
1390 if (empty($precompiledParts)) {
1391 // Map table / field names if needed:
1392 $ORIG_tableName = $from_table;
1393 // Saving table names in $ORIG_from_table since $from_table is transformed beneath:
1394 $parsedFromTable = array();
1395 $queryComponents = array();
1396 if ($tableArray = $this->map_needMapping($ORIG_tableName, false, $parsedFromTable)) {
1397 $from = $parsedFromTable ?
$parsedFromTable : $from_table;
1398 $components = $this->map_remapSELECTQueryParts($select_fields, $from, $where_clause, $groupBy, $orderBy);
1399 $queryComponents['SELECT'] = $components[0];
1400 $queryComponents['FROM'] = $components[1];
1401 $queryComponents['WHERE'] = $components[2];
1402 $queryComponents['GROUPBY'] = $components[3];
1403 $queryComponents['ORDERBY'] = $components[4];
1404 $queryComponents['parameters'] = $components[5];
1406 $queryComponents = $this->getQueryComponents($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1408 $queryComponents['ORIG_tableName'] = $ORIG_tableName;
1409 if (!$this->runningNative()) {
1410 // Quotes all fields
1411 $queryComponents['SELECT'] = $this->_quoteFieldNames($queryComponents['SELECT']);
1412 $queryComponents['FROM'] = $this->_quoteFromTables($queryComponents['FROM']);
1413 $queryComponents['WHERE'] = $this->_quoteWhereClause($queryComponents['WHERE']);
1414 $queryComponents['GROUPBY'] = $this->_quoteGroupBy($queryComponents['GROUPBY']);
1415 $queryComponents['ORDERBY'] = $this->_quoteOrderBy($queryComponents['ORDERBY']);
1417 $precompiledParts = $this->precompileSELECTquery($queryComponents);
1418 if ($this->queryCache
) {
1420 $this->queryCache
->set($cacheKey, $precompiledParts);
1421 } catch (\TYPO3\CMS\Core\Cache\Exception
$e) {
1423 GeneralUtility
::devLog($e->getMessage(), 'dbal', 1);
1428 $preparedStatement = GeneralUtility
::makeInstance(\TYPO3\CMS\Core\Database\PreparedStatement
::class, '', $from_table, $precompiledParts);
1429 /* @var $preparedStatement \TYPO3\CMS\Core\Database\PreparedStatement */
1430 // Bind values to parameters
1431 foreach ($input_parameters as $key => $value) {
1432 $preparedStatement->bindValue($key, $value, \TYPO3\CMS\Core\Database\PreparedStatement
::PARAM_AUTOTYPE
);
1436 'args' => array($from_table, $select_fields, $where_clause, $groupBy, $orderBy, $limit, $input_parameters),
1437 'ORIG_from_table' => $ORIG_tableName
1439 $this->debugHandler('prepare_SELECTquery', GeneralUtility
::milliseconds() - $pt, $data);
1441 // Return prepared statement
1442 return $preparedStatement;
1446 * Returns the parsed query components.
1448 * @param string $select_fields
1449 * @param string $from_table
1450 * @param string $where_clause
1451 * @param string $groupBy
1452 * @param string $orderBy
1453 * @param string $limit
1454 * @throws \InvalidArgumentException
1457 protected function getQueryComponents($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit)
1459 $queryComponents = array(
1466 'parameters' => array()
1468 $this->lastHandlerKey
= $this->handler_getFromTableList($from_table);
1469 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
1470 if ($hType === 'adodb' && $this->runningADOdbDriver('postgres')) {
1471 // Possibly rewrite the LIMIT to be PostgreSQL-compatible
1472 $splitLimit = 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;
1481 $queryComponents['LIMIT'] = $limit;
1482 $queryComponents['SELECT'] = $this->SQLparser
->parseFieldList($select_fields);
1483 if ($this->SQLparser
->parse_error
) {
1484 throw new \
InvalidArgumentException($this->SQLparser
->parse_error
, 1310027408);
1486 $queryComponents['FROM'] = $this->SQLparser
->parseFromTables($from_table);
1487 $queryComponents['WHERE'] = $this->SQLparser
->parseWhereClause($where_clause, '', $queryComponents['parameters']);
1488 if (!is_array($queryComponents['WHERE'])) {
1489 throw new \
InvalidArgumentException('Could not parse where clause', 1310027427);
1491 $queryComponents['GROUPBY'] = $this->SQLparser
->parseFieldList($groupBy);
1492 $queryComponents['ORDERBY'] = $this->SQLparser
->parseFieldList($orderBy);
1493 // Return the query components
1494 return $queryComponents;
1498 * Precompiles a SELECT prepared SQL statement.
1500 * @param array $components
1501 * @return array Precompiled SQL statement
1503 protected function precompileSELECTquery(array $components)
1505 $parameterWrap = '__' . dechex(time()) . '__';
1506 foreach ($components['parameters'] as $key => $params) {
1508 foreach ($params as $index => $param) {
1509 $components['parameters'][$key][$index][0] = $parameterWrap . $param[0] . $parameterWrap;
1512 $components['parameters'][$key][0] = $parameterWrap . $params[0] . $parameterWrap;
1515 $select_fields = $this->SQLparser
->compileFieldList($components['SELECT']);
1516 $from_table = $this->SQLparser
->compileFromTables($components['FROM']);
1517 $where_clause = $this->SQLparser
->compileWhereClause($components['WHERE']);
1518 $groupBy = $this->SQLparser
->compileFieldList($components['GROUPBY']);
1519 $orderBy = $this->SQLparser
->compileFieldList($components['ORDERBY']);
1520 $limit = $components['LIMIT'];
1521 $precompiledParts = array();
1522 $this->lastHandlerKey
= $this->handler_getFromTableList($components['ORIG_tableName']);
1523 $hType = (string)$this->handlerCfg
[$this->lastHandlerKey
]['type'];
1524 $precompiledParts['handler'] = $hType;
1525 $precompiledParts['ORIG_tableName'] = $components['ORIG_tableName'];
1528 $query = parent
::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1529 $precompiledParts['queryParts'] = explode($parameterWrap, $query);
1532 $this->dbmsSpecifics
->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
1533 $query = parent
::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
1534 $precompiledParts['queryParts'] = explode($parameterWrap, $query);
1535 $precompiledParts['LIMIT'] = $limit;
1538 $precompiledParts['queryParts'] = array(
1539 'SELECT' => $select_fields,
1540 'FROM' => $from_table,
1541 'WHERE' => $where_clause,
1542 'GROUPBY' => $groupBy,
1543 'ORDERBY' => $orderBy,
1548 return $precompiledParts;
1552 * Prepares a prepared query.
1554 * @param string $query The query to execute
1555 * @param array $queryComponents The components of the query to execute
1556 * @return bool|\mysqli_statement|\TYPO3\CMS\Dbal\Database\AdodbPreparedStatement
1557 * @throws \RuntimeException
1558 * @internal This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
1560 public function prepare_PREPAREDquery($query, array $queryComponents)
1562 $pt = $this->debug ? GeneralUtility
::milliseconds() : 0;
1563 // Get handler key and select API:
1564 $preparedStatement = null;
1565 switch ($queryComponents['handler']) {
1567 $this->lastQuery
= $query;
1568 $preparedStatement = parent
::prepare_PREPAREDquery($this->lastQuery
, $queryComponents);
1569 $this->resourceIdToTableNameMap
[serialize($preparedStatement)] = $queryComponents['ORIG_tableName'];
1572 /** @var \TYPO3\CMS\Dbal\Database\AdodbPreparedStatement $preparedStatement */
1573 $preparedStatement = GeneralUtility
::makeInstance(\TYPO3\CMS\Dbal\Database\AdodbPreparedStatement
::class, $query, $queryComponents, $this);
1574 if (!$preparedStatement->prepare()) {
1575 $preparedStatement = false;
1579 throw new \
RuntimeException('prepare_PREPAREDquery is not implemented for userdefined handlers', 1394620167);
1581 $queryParts = $queryComponents['queryParts'];
1582 $preparedStatement = $this->handlerInstance[$this->lastHandlerKey]->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
1583 if (is_object($preparedStatement)) {
1584 $preparedStatement->TYPO3_DBAL_handlerType = 'userdefined';
1585 // Setting handler type in result object (for later recognition!)
1586 $preparedStatement->TYPO3_DBAL_tableList = $queryComponents['ORIG_tableName'];
1591 if ($this->printErrors
&& $this->sql_error()) {
1592 debug(array($this->lastQuery
, $this->sql_error()));
1596 'handlerType' => $queryComponents['handler'],
1597 'args' => $queryComponents,
1598 'ORIG_from_table' => $queryComponents['ORIG_tableName']
1600 $this->debugHandler('prepare_PREPAREDquery', GeneralUtility
::milliseconds() - $pt, $data);
1602 // Return result handler.
1603 return $preparedStatement;
1606 /**************************************
1608 * Functions for quoting table/field names
1610 **************************************/
1612 * Quotes components of a SELECT subquery.
1614 * @param array $components Array of SQL query components
1617 protected function quoteSELECTsubquery(array $components)
1619 $components['SELECT'] = $this->_quoteFieldNames($components['SELECT']);
1620 $components['FROM'] = $this->_quoteFromTables($components['FROM']);
1621 $components['WHERE'] = $this->_quoteWhereClause($components['WHERE']);
1626 * Quotes field (and table) names with the quote character suitable for the DB being used
1628 * @param string $select_fields List of fields to be used in query to DB
1629 * @throws \InvalidArgumentException
1630 * @return string Quoted list of fields to be in query to DB
1632 public function quoteFieldNames($select_fields)
1634 if ($select_fields == '') {
1637 if ($this->runningNative()) {
1638 return $select_fields;
1640 $select_fields = $this->SQLparser
->parseFieldList($select_fields);
1641 if ($this->SQLparser
->parse_error
) {
1642 throw new \
InvalidArgumentException($this->SQLparser
->parse_error
, 1310027490);
1644 $select_fields = $this->_quoteFieldNames($select_fields);
1645 return $this->SQLparser
->compileFieldList($select_fields);
1649 * Quotes field (and table) names in a SQL SELECT clause according to DB rules
1651 * @param array $select_fields The parsed fields to quote
1653 * @see quoteFieldNames()
1655 protected function _quoteFieldNames(array $select_fields)
1657 foreach ($select_fields as $k => $v) {
1658 if ($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*' && !is_numeric($select_fields[$k]['field'])) {
1659 $select_fields[$k]['field'] = $this->quoteName($select_fields[$k]['field']);
1661 if ($select_fields[$k]['table'] != '' && !is_numeric($select_fields[$k]['table'])) {
1662 $select_fields[$k]['table'] = $this->quoteName($select_fields[$k]['table']);
1664 if ($select_fields[$k]['as'] != '') {
1665 $select_fields[$k]['as'] = $this->quoteName($select_fields[$k]['as']);
1667 if (isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*') {
1668 $select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
1669 $select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
1671 if (isset($select_fields[$k]['flow-control'])) {
1672 // Quoting flow-control statements
1673 if ($select_fields[$k]['flow-control']['type'] === 'CASE') {
1674 if (isset($select_fields[$k]['flow-control']['case_field'])) {
1675 $select_fields[$k]['flow-control']['case_field'] = $this->quoteFieldNames($select_fields[$k]['flow-control']['case_field']);
1677 foreach ($select_fields[$k]['flow-control']['when'] as $key => $when) {
1678 $select_fields[$k]['flow-control']['when'][$key]['when_value'] = $this->_quoteWhereClause($when['when_value']);
1683 return $select_fields;
1687 * Quotes table names with the quote character suitable for the DB being used
1689 * @param string $from_table List of tables to be selected from DB
1690 * @return string Quoted list of tables to be selected from DB
1692 public function quoteFromTables($from_table)
1694 if ($from_table === '') {
1697 if ($this->runningNative()) {
1700 $from_table = $this->SQLparser
->parseFromTables($from_table);
1701 $from_table = $this->_quoteFromTables($from_table);
1702 return $this->SQLparser
->compileFromTables($from_table);
1706 * Quotes table names in a SQL FROM clause according to DB rules
1708 * @param array $from_table The parsed FROM clause to quote
1710 * @see quoteFromTables()
1712 protected function _quoteFromTables(array $from_table)
1714 foreach ($from_table as $k => $v) {
1715 $from_table[$k]['table'] = $this->quoteName($from_table[$k]['table']);
1716 if ($from_table[$k]['as'] != '') {
1717 $from_table[$k]['as'] = $this->quoteName($from_table[$k]['as']);
1719 if (is_array($v['JOIN'])) {
1720 foreach ($v['JOIN'] as $joinCnt => $join) {
1721 $from_table[$k]['JOIN'][$joinCnt]['withTable'] = $this->quoteName($join['withTable']);
1722 $from_table[$k]['JOIN'][$joinCnt]['as'] = $join['as'] ?
$this->quoteName($join['as']) : '';
1723 foreach ($from_table[$k]['JOIN'][$joinCnt]['ON'] as &$condition) {
1724 $condition['left']['table'] = $condition['left']['table'] ?
$this->quoteName($condition['left']['table']) : '';
1725 $condition['left']['field'] = $this->quoteName($condition['left']['field']);
1726 $condition['right']['table'] = $condition['right']['table'] ?
$this->quoteName($condition['right']['table']) : '';
1727 $condition['right']['field'] = $this->quoteName($condition['right']['field']);
1736 * Quotes the field (and table) names within a where clause with the quote character suitable for the DB being used
1738 * @param string $where_clause A where clause that can be parsed by parseWhereClause
1739 * @throws \InvalidArgumentException
1740 * @return string Usable where clause with quoted field/table names
1742 public function quoteWhereClause($where_clause)
1744 if ($where_clause === '' ||
$this->runningNative()) {
1745 return $where_clause;
1747 $where_clause = $this->SQLparser
->parseWhereClause($where_clause);
1748 if (is_array($where_clause)) {
1749 $where_clause = $this->_quoteWhereClause($where_clause);
1750 $where_clause = $this->SQLparser
->compileWhereClause($where_clause);
1752 throw new \
InvalidArgumentException('Could not parse where clause', 1310027511);
1754 return $where_clause;
1758 * Quotes field names in a SQL WHERE clause according to DB rules
1760 * @param array $where_clause The parsed WHERE clause to quote
1762 * @see quoteWhereClause()
1764 protected function _quoteWhereClause(array $where_clause)
1766 foreach ($where_clause as $k => $v) {
1767 // Look for sublevel:
1768 if (is_array($where_clause[$k]['sub'])) {
1769 $where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
1770 } elseif (isset($v['func'])) {
1771 switch ($where_clause[$k]['func']['type']) {
1773 $where_clause[$k]['func']['subquery'] = $this->quoteSELECTsubquery($v['func']['subquery']);
1776 // quoteStr that will be used for Oracle
1777 $pattern = str_replace($where_clause[$k]['func']['str'][1], '\\' . $where_clause[$k]['func']['str'][1], $where_clause[$k]['func']['str'][0]);
1778 // table is not really needed and may in fact be empty in real statements
1779 // but it's not overridden from \TYPO3\CMS\Core\Database\DatabaseConnection at the moment...
1780 $patternForLike = $this->escapeStrForLike($pattern, $where_clause[$k]['func']['table']);
1781 $where_clause[$k]['func']['str_like'] = $patternForLike;
1782 if ($where_clause[$k]['func']['table'] !== '') {
1783 if ($this->dbmsSpecifics
->getSpecific(Specifics\AbstractSpecifics
::CAST_FIND_IN_SET
)) {
1784 $where_clause[$k]['func']['table'] = 'CAST(' . $this->quoteName($v['func']['table']);
1786 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1789 if ($where_clause[$k]['func']['field'] !== '') {
1790 if ($this->dbmsSpecifics
->getSpecific(Specifics\AbstractSpecifics
::CAST_FIND_IN_SET
)) {
1791 if ($where_clause[$k]['func']['table'] !== '') {
1792 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']) . ' AS CHAR)';
1794 $where_clause[$k]['func']['field'] = 'CAST(' . $this->quoteName($v['func']['field']) . ' AS CHAR)';
1797 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1802 // Intentional fallthrough
1804 // Intentional fallthrough
1806 if ($where_clause[$k]['func']['table'] != '') {
1807 $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
1809 if ($where_clause[$k]['func']['field'] != '') {
1810 $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
1815 if ($where_clause[$k]['table'] != '') {
1816 $where_clause[$k]['table'] = $this->quoteName($where_clause[$k]['table']);
1818 if (!is_numeric($where_clause[$k]['field'])) {
1819 $where_clause[$k]['field'] = $this->quoteName($where_clause[$k]['field']);
1821 if (isset($where_clause[$k]['calc_table'])) {
1822 if ($where_clause[$k]['calc_table'] != '') {
1823 $where_clause[$k]['calc_table'] = $this->quoteName($where_clause[$k]['calc_table']);
1825 if ($where_clause[$k]['calc_field'] != '') {
1826 $where_clause[$k]['calc_field'] = $this->quoteName($where_clause[$k]['calc_field']);
1830 if ($where_clause[$k]['comparator']) {
1831 if (isset($v['value']['operator'])) {
1832 foreach ($where_clause[$k]['value']['args'] as $argK => $fieldDef) {
1833 $where_clause[$k]['value']['args'][$argK]['table'] = $this->quoteName($fieldDef['table']);
1834 $where_clause[$k]['value']['args'][$argK]['field'] = $this->quoteName($fieldDef['field']);
1837 // Detecting value type; list or plain:
1838 $comparator = $this->SQLparser
->normalizeKeyword($where_clause[$k]['comparator']);
1839 if ($comparator === 'NOTIN' ||
$comparator === 'IN') {
1840 if (isset($v['subquery'])) {
1841 $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
1845 (!isset($where_clause[$k]['value'][1]) ||
$where_clause[$k]['value'][1] == '')
1846 && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')
1848 $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
1849 } elseif ($this->runningADOdbDriver('mssql')) {
1850 $where_clause[$k]['value'][0] = substr($this->handlerInstance
[$this->lastHandlerKey
]->qstr($where_clause[$k]['value'][0]), 1, -1);
1856 return $where_clause;
1860 * Quotes the field (and table) names within a group by clause with the quote
1861 * character suitable for the DB being used
1863 * @param string $groupBy A group by clause that can by parsed by parseFieldList
1864 * @return string Usable group by clause with quoted field/table names
1866 protected function quoteGroupBy($groupBy)
1868 if ($groupBy === '') {
1871 if ($this->runningNative()) {
1874 $groupBy = $this->SQLparser
->parseFieldList($groupBy);
1875 $groupBy = $this->_quoteGroupBy($groupBy);
1876 return $this->SQLparser
->compileFieldList($groupBy);
1880 * Quotes field names in a SQL GROUP BY clause according to DB rules
1882 * @param array $groupBy The parsed GROUP BY clause to quote
1884 * @see quoteGroupBy()
1886 protected function _quoteGroupBy(array $groupBy)
1888 foreach ($groupBy as $k => $v) {
1889 $groupBy[$k]['field'] = $this->quoteName($groupBy[$k]['field']);
1890 if ($groupBy[$k]['table'] != '') {
1891 $groupBy[$k]['table'] = $this->quoteName($groupBy[$k]['table']);
1898 * Quotes the field (and table) names within an order by clause with the quote
1899 * character suitable for the DB being used
1901 * @param string $orderBy An order by clause that can by parsed by parseFieldList
1902 * @return string Usable order by clause with quoted field/table names
1904 protected function quoteOrderBy($orderBy)
1906 if ($orderBy === '') {
1909 if ($this->runningNative()) {
1912 $orderBy = $this->SQLparser
->parseFieldList($orderBy);
1913 $orderBy = $this->_quoteOrderBy($orderBy);
1914 return $this->SQLparser
->compileFieldList($orderBy);
1918 * Quotes field names in a SQL ORDER BY clause according to DB rules
1920 * @param array $orderBy The parsed ORDER BY clause to quote
1922 * @see quoteOrderBy()
1924 protected function _quoteOrderBy(array $orderBy)
1926 foreach ($orderBy as $k => $v) {
1927 if ($orderBy[$k]['table'] === '' && $v['field'] !== '' && ctype_digit($v['field'])) {
1930 $orderBy[$k]['field'] = $this->quoteName($orderBy[$k]['field']);
1931 if ($orderBy[$k]['table'] !== '') {
1932 $orderBy[$k]['table'] = $this->quoteName($orderBy[$k]['table']);
1938 /**************************************
1940 * Various helper functions
1942 **************************************/
1944 * Escaping and quoting values for SQL statements.
1946 * @param string $str Input string
1947 * @param string $table 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!).
1948 * @param bool $allowNull Whether to allow NULL values
1949 * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1952 public function fullQuoteStr($str, $table, $allowNull = false)
1954 if ($allowNull && $str === null) {
1957 return '\'' . $this->quoteStr($str, $table) . '\'';
1961 * Substitution for PHP function "addslashes()"
1962 * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
1963 * 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()!
1965 * @param string $str Input string
1966 * @param string $table 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!).
1967 * @throws \RuntimeException
1968 * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
1971 public function quoteStr($str, $table)
1973 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
1974 switch ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type']) {
1976 if ($this->handlerInstance
[$this->lastHandlerKey
]['link']) {
1977 if (!$this->isConnected()) {
1980 $str = $this->handlerInstance
[$this->lastHandlerKey
]['link']->real_escape_string($str);
1982 // link may be null when unit testing DBAL
1983 $str = str_replace('\'', '\\\'', $str);
1987 if (!$this->isConnected()) {
1990 $str = substr($this->handlerInstance
[$this->lastHandlerKey
]->qstr($str), 1, -1);
1993 $str = $this->handlerInstance
[$this->lastHandlerKey
]->quoteStr($str);
1996 throw new \
RuntimeException('No handler found!!!', 1310027655);
2002 * Quotes an object name (table name, field, ...)
2004 * @param string $name Object's name
2005 * @param string $handlerKey Handler key
2006 * @param bool $useBackticks If method NameQuote() is not used, whether to use backticks instead of driver-specific quotes
2007 * @return string Properly-quoted object's name
2009 public function quoteName($name, $handlerKey = null, $useBackticks = false)
2011 $handlerKey = $handlerKey ?
$handlerKey : $this->lastHandlerKey
;
2012 $useNameQuote = isset($this->handlerCfg
[$handlerKey]['config']['useNameQuote']) ?
$this->handlerCfg
[$handlerKey]['config']['useNameQuote'] : false;
2013 if ($useNameQuote) {
2014 // Sometimes DataDictionary is not properly instantiated
2015 if (!is_object($this->handlerInstance
[$handlerKey]->DataDictionary
)) {
2016 $this->handlerInstance
[$handlerKey]->DataDictionary
= NewDataDictionary($this->handlerInstance
[$handlerKey]);
2018 return $this->handlerInstance
[$handlerKey]->DataDictionary
->NameQuote($name);
2020 $quote = $useBackticks ?
'`' : $this->handlerInstance
[$handlerKey]->nameQuote
;
2021 return $quote . $name . $quote;
2026 * Return Metadata for native field type (ADOdb only!)
2028 * @param string $type Native type as reported by admin_get_fields()
2029 * @param string $table Table name for which the type is queried. Important for detection of DBMS handler of the query!
2030 * @param string $field Field name for which the type is queried. Important for accessing the field information cache.
2031 * @param int $maxLength
2032 * @throws \RuntimeException
2033 * @return string Meta type (currently ADOdb syntax only, http://phplens.com/lens/adodb/docs-adodb.htm#metatype)
2035 public function getMetadata($type, $table, $field, $maxLength = -1)
2037 $this->lastHandlerKey
= $this->handler_getFromTableList($table);
2039 switch ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2044 if (!empty($this->cache_fieldType
[$table][$field])) {
2045 $str = $this->cache_fieldType
[$table][$field]['metaType'];
2047 $rs = $this->handlerInstance
[$this->lastHandlerKey
]->SelectLimit('SELECT * FROM ' . $this->quoteFromTables($table), 1);
2048 $str = $rs->MetaType($type, $maxLength);
2052 $str = $this->handlerInstance
[$this->lastHandlerKey
]->MetaType($str, $table, $maxLength);
2055 throw new \
RuntimeException('No handler found!!!', 1310027685);
2061 * Update the id information for the last inserted record
2063 * @param string $table
2064 * @param array $fieldValues
2067 protected function updateLastInsertId($table, array $fieldValues)
2069 if ($table === 'tx_dbal_debuglog') {
2073 if (isset($fieldValues[$this->cache_autoIncFields
[$table]])) {
2074 $newId = $fieldValues[$this->cache_autoIncFields
[$table]];
2075 } elseif ($this->handlerInstance
[$this->lastHandlerKey
]->hasInsertID
&& !empty($this->cache_autoIncFields
[$table])) {
2076 // The table is able to retrieve the ID of the last insert
2077 $newId = $this->handlerInstance
[$this->lastHandlerKey
]->Insert_ID($table, $this->cache_autoIncFields
[$table]);
2079 if ($newId !== null) {
2080 $this->handlerInstance
[$this->lastHandlerKey
]->last_insert_id
= $newId;
2085 /*********************************************
2087 * SqlSchemaMigrationService helper functions
2089 *********************************************/
2091 * Remove the index prefix length information from columns in an index definition.
2092 * Partial indexes based on a prefix are not supported by all databases.
2094 * @param string $indexSQL
2097 public function getEquivalentIndexDefinition($indexSQL)
2099 if ($this->dbmsSpecifics
->specificExists(Specifics\AbstractSpecifics
::PARTIAL_STRING_INDEX
) && (bool)$this->dbmsSpecifics
->getSpecific(Specifics\AbstractSpecifics
::PARTIAL_STRING_INDEX
)) {
2103 $strippedIndexSQL = preg_replace_callback(
2104 '/\A([^(]+)\((.*)\)\Z/',
2105 function ($matches) {
2106 return $matches[1] . '(' . preg_replace('/\((\d+)\)/', '', $matches[2]) . ')';
2111 return $strippedIndexSQL === null ?
$indexSQL : $strippedIndexSQL;
2115 * Convert the native MySQL Field type to the closest matching equivalent field type supported by the DBMS.
2116 * INTEGER and TINYTEXT colums need to be further processed due to MySQL limitations / non-standard features.
2118 * @param string $fieldSQL
2121 public function getEquivalentFieldDefinition($fieldSQL)
2123 if (!preg_match('/^([a-z0-9]+)(\(([^\)]+)\))?(.*)/', $fieldSQL, $components)) {
2127 $metaType = $this->dbmsSpecifics
->getMetaFieldType($components[1]);
2128 $replacementType = $this->dbmsSpecifics
->getNativeFieldType($metaType);
2129 $replacementLength = $components[2];
2130 $replacementExtra = '';
2132 // MySQL INT types support a display length that has no effect on the
2133 // actual range of values that can be stored, normalize to the default
2134 // display length returned by DBAL.
2135 if (substr($metaType, 0, 1) === 'I') {
2136 $replacementLength = $this->dbmsSpecifics
->getNativeFieldLength($replacementType, $components[3]);
2139 // MySQL TINYTEXT is equivalent to VARCHAR(255) DEFAULT NULL. MySQL TEXT
2140 // columns can not have a default value in contrast to VARCHAR, so the
2141 // `default NULL` gets appended to avoid false-positive schema changes.
2142 if ($components[1] === 'tinytext') {
2143 $replacementLength = '(255)';
2144 if (false !== stripos($components[0], ' NOT NULL')) {
2145 $replacementExtra = ' default \'\'';
2147 $replacementExtra = ' default NULL';
2151 return str_replace($components[1] . $components[2], strtolower($replacementType) . $replacementLength, $components[0]) . $replacementExtra;
2154 /**************************************
2156 * SQL wrapper functions (Overriding parent methods)
2157 * (For use in your applications)
2159 **************************************/
2161 * Returns the error status on the last query() execution
2163 * @return string MySQLi error string.
2165 public function sql_error()
2168 switch ($this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2170 $output = $this->handlerInstance
[$this->lastHandlerKey
]['link']->error
;
2173 $output = $this->handlerInstance
[$this->lastHandlerKey
]->ErrorMsg();
2176 $output = $this->handlerInstance
[$this->lastHandlerKey
]->sql_error();
2183 * Returns the error number on the last query() execution
2185 * @return int MySQLi error number
2187 public function sql_errno()
2190 switch ($this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2192 $output = $this->handlerInstance
[$this->lastHandlerKey
]['link']->errno
;
2195 $output = $this->handlerInstance
[$this->lastHandlerKey
]->ErrorNo();
2198 $output = $this->handlerInstance
[$this->lastHandlerKey
]->sql_errno();
2205 * Returns the number of selected rows.
2207 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2208 * @return int Number of resulting rows
2210 public function sql_num_rows($res)
2212 if ($res === false) {
2215 $handlerType = $this->determineHandlerType($res);
2217 switch ($handlerType) {
2219 $output = $res->num_rows
;
2222 $output = method_exists($res, 'RecordCount') ?
$res->RecordCount() : 0;
2225 $output = $res->sql_num_rows();
2232 * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
2233 * MySQLi fetch_assoc() wrapper function
2235 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2236 * @return array|bool Associative array of result row.
2238 public function sql_fetch_assoc($res)
2242 switch ($this->determineHandlerType($res)) {
2244 $output = $res->fetch_assoc();
2245 $key = serialize($res);
2246 $tableList = $this->resourceIdToTableNameMap
[$key];
2247 unset($this->resourceIdToTableNameMap
[$key]);
2248 // Reading list of tables from SELECT query:
2251 // Check if method exists for the current $res object.
2252 // If a table exists in TCA but not in the db, an error
2253 // occurred because $res is not a valid object.
2254 if (method_exists($res, 'FetchRow')) {
2255 $output = $res->FetchRow();
2256 $tableList = $res->TYPO3_DBAL_tableList
;
2257 // Reading list of tables from SELECT query:
2258 // Removing all numeric/integer keys.
2259 // A workaround because in ADOdb we would need to know what we want before executing the query...
2260 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2261 // we don't need to remove anything.
2262 if (is_array($output)) {
2263 if ($this->runningADOdbDriver('mssql')) {
2264 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2265 foreach ($output as $key => $value) {
2266 if ($value === ' ') {
2271 foreach ($output as $key => $value) {
2272 if (is_integer($key)) {
2273 unset($output[$key]);
2281 $output = $res->sql_fetch_assoc();
2282 $tableList = $res->TYPO3_DBAL_tableList
;
2283 // Reading list of tables from SELECT query:
2286 // Table/Fieldname mapping:
2287 if (is_array($output)) {
2288 if ($tables = $this->map_needMapping($tableList, true)) {
2289 $output = $this->map_assocArray($output, $tables, 1);
2292 if ($output === null) {
2293 // Needed for compatibility
2301 * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
2302 * The array contains the values in numerical indices.
2303 * MySQLi fetch_row() wrapper function
2305 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2306 * @return array|bool Array with result rows.
2308 public function sql_fetch_row($res)
2311 switch ($this->determineHandlerType($res)) {
2313 $output = $res->fetch_row();
2314 if ($output === null) {
2315 // Needed for compatibility
2320 // Check if method exists for the current $res object.
2321 // If a table exists in TCA but not in the db, an error
2322 // occurred because $res is not a valid object.
2323 if (method_exists($res, 'FetchRow')) {
2324 $output = $res->FetchRow();
2325 // Removing all assoc. keys.
2326 // A workaround because in ADOdb we would need to know what we want before executing the query...
2327 // MSSQL does not support ADODB_FETCH_BOTH and always returns an assoc. array instead. So
2328 // we need to convert resultset.
2329 if (is_array($output)) {
2331 foreach ($output as $key => $value) {
2332 unset($output[$key]);
2333 if (is_integer($key) ||
$this->runningADOdbDriver('mssql')) {
2334 $output[$keyIndex] = $value;
2335 if ($value === ' ') {
2336 // MSSQL does not know such thing as an empty string. So it returns one space instead, which we must fix.
2337 $output[$keyIndex] = '';
2346 $output = $res->sql_fetch_row();
2349 if ($output === null) {
2350 // Needed for compatibility
2357 * Free result memory
2358 * free_result() wrapper function
2360 * @param bool|\mysqli_result|\ADORecordSet|object $res MySQLi result object / DBAL object
2361 * @return bool Returns TRUE on success or FALSE on failure.
2363 public function sql_free_result($res)
2365 if ($res === false) {
2369 switch ($this->determineHandlerType($res)) {
2374 if (method_exists($res, 'Close')) {
2390 * Determine handler type by result set
2392 * @param bool|\mysqli_result|object $res MySQLi result set / DBAL Object
2393 * @return bool|string
2395 protected function determineHandlerType($res)
2397 if (is_object($res) && !$res instanceof \mysqli_result
) {
2398 $handlerType = $res->TYPO3_DBAL_handlerType
;
2399 } elseif ($res instanceof \mysqli_result
) {
2400 $handlerType = 'native';
2402 $handlerType = false;
2404 return $handlerType;
2408 * Get the ID generated from the previous INSERT operation
2410 * @return int The uid of the last inserted record.
2412 public function sql_insert_id()
2415 switch ($this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2417 $output = $this->handlerInstance
[$this->lastHandlerKey
]['link']->insert_id
;
2420 $output = $this->handlerInstance
[$this->lastHandlerKey
]->last_insert_id
;
2423 $output = $this->handlerInstance
[$this->lastHandlerKey
]->sql_insert_id();
2430 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
2432 * @return int Number of rows affected by last query
2434 public function sql_affected_rows()
2437 switch ($this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2439 $output = $this->handlerInstance
[$this->lastHandlerKey
]['link']->affected_rows
;
2442 $output = $this->handlerInstance
[$this->lastHandlerKey
]->Affected_Rows();
2445 $output = $this->handlerInstance
[$this->lastHandlerKey
]->sql_affected_rows();
2452 * Move internal result pointer
2454 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2455 * @param int $seek Seek result number.
2456 * @return bool Returns TRUE on success or FALSE on failure.
2458 public function sql_data_seek($res, $seek)
2461 switch ($this->determineHandlerType($res)) {
2463 $output = $res->data_seek($seek);
2466 $output = $res->Move($seek);
2469 $output = $res->sql_data_seek($seek);
2476 * Get the type of the specified field in a result
2478 * If the first parameter is a string, it is used as table name for the lookup.
2480 * @param string $table MySQL result pointer (of SELECT query) / DBAL object / table name
2481 * @param int $field Field index. In case of ADOdb a string (field name!)
2482 * @return string Returns the type of the specified field index
2484 public function sql_field_metatype($table, $field)
2486 // If $table and/or $field are mapped, use the original names instead
2487 foreach ($this->mapping
as $tableName => $tableMapInfo) {
2488 if (isset($tableMapInfo['mapFieldNames'])) {
2489 foreach ($tableMapInfo['mapFieldNames'] as $fieldName => $fieldMapInfo) {
2490 if ($fieldMapInfo === $field) {
2491 // Field name is mapped => use original name
2492 $field = $fieldName;
2497 return $this->cache_fieldType
[$table][$field]['metaType'];
2501 * Get the type of the specified field in a result
2502 * mysql_field_type() wrapper function
2504 * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
2505 * @param int $pointer Field index.
2506 * @return string Returns the name of the specified field index, or FALSE on error
2508 public function sql_field_type($res, $pointer)
2510 if ($res === null) {
2511 debug(array('no res in sql_field_type!'));
2513 } elseif (is_string($res)) {
2514 if ($res === 'tx_dbal_debuglog') {
2517 $handlerType = 'adodb';
2519 $handlerType = $this->determineHandlerType($res);
2522 switch ($handlerType) {
2524 $metaInfo = $res->fetch_field_direct($pointer);
2526 $output = $this->mysqlDataTypeMapping
[$metaInfo->type
];
2532 if (is_string($pointer)) {
2533 $output = $this->cache_fieldType
[$res][$pointer]['type'];
2537 $output = $res->sql_field_type($pointer);
2545 * Legacy functions, bound to _DEFAULT handler. (Overriding parent methods)
2546 * Deprecated or still experimental.
2552 * EXPERIMENTAL - This method will make its best to handle the query correctly
2553 * but if it cannot, it will simply pass the query to DEFAULT handler.
2555 * You should use exec_* function from this class instead!
2556 * If you don't, anything that does not use the _DEFAULT handler will probably break!
2558 * MySQLi query() wrapper function
2559 * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
2560 * using exec_SELECTquery() and similar methods instead.
2562 * @param string $query Query to execute
2563 * @return bool|\mysqli_result|object MySQLi result object / DBAL object
2565 public function sql_query($query)
2567 $globalConfig = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf']['dbal'], ['allowed_classes' => false]);
2568 if ($globalConfig['sql_query.']['passthrough']) {
2569 return parent
::sql_query($query);
2571 // This method is heavily used by Extbase, try to handle it with DBAL-native methods
2572 $queryParts = $this->SQLparser
->parseSQL($query);
2573 if (is_array($queryParts)) {
2574 $operation = $queryParts['type'];
2575 if ($operation === 'SELECT' ||
$operation === 'UPDATE' ||
$operation === 'INSERT' ||
$operation === 'DELETE') {
2576 return $this->exec_query($queryParts);
2580 switch ($this->handlerCfg
['_DEFAULT']['type']) {
2582 if (!$this->isConnected()) {
2585 $sqlResult = $this->handlerInstance
['_DEFAULT']['link']->query($query);
2588 $sqlResult = $this->handlerInstance
['_DEFAULT']->Execute($query);
2589 $sqlResult->TYPO3_DBAL_handlerType
= 'adodb';
2592 $sqlResult = $this->handlerInstance
['_DEFAULT']->sql_query($query);
2593 $sqlResult->TYPO3_DBAL_handlerType
= 'userdefined';
2596 $this->lastHandlerKey
= '_DEFAULT';
2597 if ($this->printErrors
&& $this->sql_error()) {
2598 debug(array($this->lastQuery
, $this->sql_error()));
2604 * Open a (persistent) connection to a MySQL server
2608 public function sql_pconnect()
2610 return $this->handler_init('_DEFAULT');
2614 * Select a SQL database
2616 * @return bool Returns TRUE on success or FALSE on failure.
2618 public function sql_select_db()
2620 $databaseName = $this->handlerCfg
[$this->lastHandlerKey
]['config']['database'];
2622 if ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type'] === 'native') {
2623 $ret = $this->handlerInstance
[$this->lastHandlerKey
]['link']->select_db($databaseName);
2626 GeneralUtility
::sysLog(
2627 'Could not select MySQL database ' . $databaseName . ': ' . $this->sql_error(),
2629 GeneralUtility
::SYSLOG_SEVERITY_FATAL
2635 /**************************************
2637 * SQL admin functions
2638 * (For use in the Install Tool and Extension Manager)
2640 **************************************/
2642 * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
2643 * This is only used as a service function in the (1-2-3 process) of the Install Tool.
2644 * In any case a lookup should be done in the _DEFAULT handler DBMS then.
2645 * Use in Install Tool only!
2647 * @return array Each entry represents a database name
2648 * @throws \RuntimeException
2650 public function admin_get_dbs()
2653 $this->lastHandlerKey
= '_DEFAULT';
2654 switch ($this->handlerCfg
['_DEFAULT']['type']) {
2656 /** @var \mysqli_result $db_list */
2657 $db_list = $this->query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
2658 $oldDb = $this->handlerCfg
[$this->lastHandlerKey
]['config']['database'];
2659 while ($row = $db_list->fetch_object()) {
2660 $this->handlerCfg
[$this->lastHandlerKey
]['config']['database'] = $row->SCHEMA_NAME
;
2661 if ($this->sql_select_db()) {
2662 $dbArr[] = $row->SCHEMA_NAME
;
2665 $this->handlerCfg
[$this->lastHandlerKey
]['config']['database'] = $oldDb;
2669 // check needed for install tool - otherwise it will just die because the call to
2670 // MetaDatabases is done on a stdClass instance
2671 if (method_exists($this->handlerInstance
['_DEFAULT'], 'MetaDatabases')) {
2672 $sqlDBs = $this->handlerInstance
['_DEFAULT']->MetaDatabases();
2673 if (is_array($sqlDBs)) {
2674 foreach ($sqlDBs as $k => $theDB) {
2681 $dbArr = $this->handlerInstance
['_DEFAULT']->admin_get_tables();
2688 * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
2689 * In a DBAL this method should 1) look up all tables from the DBMS of
2690 * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
2692 * @return array Array with tablenames as key and arrays with status information as value
2694 public function admin_get_tables()
2696 $whichTables = array();
2697 // Getting real list of tables:
2698 switch ($this->handlerCfg
['_DEFAULT']['type']) {
2700 $tables_result = $this->query('SHOW TABLE STATUS FROM `' . TYPO3_db
. '`');
2701 if (!$this->sql_error()) {
2702 while ($theTable = $this->sql_fetch_assoc($tables_result)) {
2703 $whichTables[$theTable['Name']] = $theTable;
2706 $tables_result->free();
2709 // check needed for install tool - otherwise it will just die because the call to
2710 // MetaTables is done on a stdClass instance
2711 if (method_exists($this->handlerInstance
['_DEFAULT'], 'MetaTables')) {
2712 $sqlTables = $this->handlerInstance
['_DEFAULT']->MetaTables('TABLES');
2713 foreach ($sqlTables as $k => $theTable) {
2714 if (preg_match('/BIN\\$/', $theTable)) {
2715 // Skip tables from the Oracle 10 Recycle Bin
2718 $whichTables[$theTable] = array('Name' => $theTable);
2723 $whichTables = $this->handlerInstance
['_DEFAULT']->admin_get_tables();
2727 if (is_array($this->mapping
) && !empty($this->mapping
)) {
2728 // Mapping table names in reverse, first getting list of real table names:
2730 foreach ($this->mapping
as $tN => $tMapInfo) {
2731 if (isset($tMapInfo['mapTableName'])) {
2732 $tMap[$tMapInfo['mapTableName']] = $tN;
2737 foreach ($whichTables as $tN => $tDefinition) {
2738 if (isset($tMap[$tN])) {
2740 $tDefinition = array('Name' => $tN);
2742 $newList[$tN] = $tDefinition;
2744 $whichTables = $newList;
2746 // Adding tables configured to reside in other DBMS (handler by other handlers than the default):
2747 if (is_array($this->table2handlerKeys
)) {
2748 foreach ($this->table2handlerKeys
as $key => $handlerKey) {
2749 $whichTables[$key] = array('Name' => $key);
2752 return $whichTables;
2756 * Returns information about each field in the $table (quering the DBMS)
2757 * In a DBAL this should look up the right handler for the table and return compatible information
2758 * This function is important not only for the Install Tool but probably for
2759 * DBALs as well since they might need to look up table specific information
2760 * in order to construct correct queries. In such cases this information should
2761 * probably be cached for quick delivery.
2763 * @param string $tableName Table name
2764 * @return array Field information in an associative array with fieldname => field row
2766 public function admin_get_fields($tableName)
2769 // Do field mapping if needed:
2770 $ORIG_tableName = $tableName;
2771 if ($tableArray = $this->map_needMapping($tableName)) {
2773 if ($this->mapping
[$tableName]['mapTableName']) {
2774 $tableName = $this->mapping
[$tableName]['mapTableName'];
2778 $this->lastHandlerKey
= $this->handler_getFromTableList($tableName);
2779 switch ((string)$this->handlerCfg
[$this->lastHandlerKey
]['type']) {
2781 /** @var \mysqli_result $columns_res */
2782 $columns_res = $this->query('SHOW columns FROM ' . $tableName);
2783 while ($fieldRow = $columns_res->fetch_assoc()) {
2784 $output[$fieldRow['Field']] = $fieldRow;
2786 $columns_res->free();
2789 $fieldRows = $this->handlerInstance
[$this->lastHandlerKey
]->MetaColumns($tableName, false);
2790 if (is_array($fieldRows)) {
2791 foreach ($fieldRows as $k => $fieldRow) {
2792 settype($fieldRow, 'array');