[BUGFIX] DBAL: Fix retrieving the last insert id
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Classes / Database / DatabaseConnection.php
index bfb8564..4fb6db5 100644 (file)
@@ -18,10 +18,6 @@ use TYPO3\CMS\Core\Utility\GeneralUtility;
 
 /**
  * TYPO3 database abstraction layer
- *
- * @author Kasper Skårhøj <kasper@typo3.com>
- * @author Karsten Dambekalns <k.dambekalns@fishfarm.de>
- * @author Xavier Perseguers <xavier@typo3.org>
  */
 class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
 
@@ -149,7 +145,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
        /**
         * SQL parser
         *
-        * @var \TYPO3\CMS\Core\Database\SqlParser
+        * @var \TYPO3\CMS\Dbal\Database\SqlParser
         */
        public $SQLparser;
 
@@ -213,7 +209,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
         */
        public function __construct() {
                // Set SQL parser object for internal use:
-               $this->SQLparser = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\SqlParser::class, $this);
+               $this->SQLparser = GeneralUtility::makeInstance(\TYPO3\CMS\Dbal\Database\SqlParser::class, $this);
                $this->installerSql = GeneralUtility::makeInstance(\TYPO3\CMS\Install\Service\SqlSchemaMigrationService::class);
                $this->queryCache = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Cache\CacheManager::class)->getCache('dbal');
                // Set internal variables with configuration:
@@ -312,7 +308,8 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
         * @return void
         */
        protected function analyzeCachingTables() {
-               $this->parseAndAnalyzeSql(\TYPO3\CMS\Core\Cache\Cache::getDatabaseTableDefinitions());
+               $schemaService = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Cache\DatabaseSchemaService::class);
+               $this->parseAndAnalyzeSql($schemaService->getCachingFrameworkRequiredDatabaseSchema());
        }
 
        /**
@@ -525,11 +522,18 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                $this->lastQuery = $this->INSERTquery($table, $fields_values, $no_quote_fields);
                                if (is_string($this->lastQuery)) {
                                        $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery, FALSE);
+                                       if ($this->handlerInstance[$this->lastHandlerKey]->hasInsertID && !empty($this->cache_autoIncFields[$table])) {
+                                               // The table is able to retrieve the ID of the last insert, use it to update the blob below
+                                               $new_id = $this->handlerInstance[$this->lastHandlerKey]->Insert_ID($table, $this->cache_autoIncFields[$table]);
+                                               if ($table !== 'tx_dbal_debuglog') {
+                                                       $this->handlerInstance[$this->lastHandlerKey]->last_insert_id = $new_id;
+                                               }
+                                       }
                                } else {
                                        $this->handlerInstance[$this->lastHandlerKey]->StartTrans();
                                        if ((string)$this->lastQuery[0] !== '') {
                                                $sqlResult = $this->handlerInstance[$this->lastHandlerKey]->_query($this->lastQuery[0], FALSE);
-                                               if ($this->handlerInstance[$this->lastHandlerKey]->hasInsertID) {
+                                               if ($this->handlerInstance[$this->lastHandlerKey]->hasInsertID && !empty($this->cache_autoIncFields[$table])) {
                                                        // The table is able to retrieve the ID of the last insert, use it to update the blob below
                                                        $new_id = $this->handlerInstance[$this->lastHandlerKey]->Insert_ID($table, $this->cache_autoIncFields[$table]);
                                                        if ($table !== 'tx_dbal_debuglog') {
@@ -939,7 +943,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
         * Executes a query.
         * EXPERIMENTAL since TYPO3 4.4.
         *
-        * @param array $queryParts SQL parsed by method parseSQL() of \TYPO3\CMS\Core\Database\SqlParser
+        * @param array $queryParts SQL parsed by method parseSQL() of \TYPO3\CMS\Dbal\Database\SqlParser
         * @return \mysqli_result|object MySQLi result object / DBAL object
         * @see self::sql_query()
         */
@@ -1255,6 +1259,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                $where_clause = $this->quoteWhereClause($where_clause);
                $groupBy = $this->quoteGroupBy($groupBy);
                $orderBy = $this->quoteOrderBy($orderBy);
+               $this->dbmsSpecifics->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
                // Call parent method to build actual query
                $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
                if ($this->debugOutput || $this->store_lastBuiltQuery) {
@@ -1288,6 +1293,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                }
                // Compile the SELECT parameters
                list($select_fields, $from_table, $where_clause, $groupBy, $orderBy) = $this->compileSelectParameters($params);
+               $this->dbmsSpecifics->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
                // Call parent method to build actual query
                $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
                if ($this->debugOutput || $this->store_lastBuiltQuery) {
@@ -1512,6 +1518,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                $precompiledParts['queryParts'] = explode($parameterWrap, $query);
                                break;
                        case 'adodb':
+                               $this->dbmsSpecifics->transformQueryParts($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
                                $query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
                                $precompiledParts['queryParts'] = explode($parameterWrap, $query);
                                $precompiledParts['LIMIT'] = $limit;
@@ -1753,7 +1760,17 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                                // but it's not overridden from \TYPO3\CMS\Core\Database\DatabaseConnection at the moment...
                                                $patternForLike = $this->escapeStrForLike($pattern, $where_clause[$k]['func']['table']);
                                                $where_clause[$k]['func']['str_like'] = $patternForLike;
-                                               // Intentional fallthrough
+                                               if ($where_clause[$k]['func']['table'] !== '') {
+                                                       $where_clause[$k]['func']['table'] = $this->quoteName($v['func']['table']);
+                                               }
+                                               if ($where_clause[$k]['func']['field'] !== '') {
+                                                       if ($this->dbmsSpecifics->getSpecific(Specifics\AbstractSpecifics::CAST_FIND_IN_SET)) {
+                                                               $where_clause[$k]['func']['field'] = 'CAST(' . $this->quoteName($v['func']['field']) . ' AS CHAR)';
+                                                       } else {
+                                                               $where_clause[$k]['func']['field'] = $this->quoteName($v['func']['field']);
+                                                       }
+                                               }
+                                               break;
                                        case 'IFNULL':
                                                // Intentional fallthrough
                                        case 'LOCATE':
@@ -1789,9 +1806,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                        }
                                } else {
                                        // Detecting value type; list or plain:
-                                       if (GeneralUtility::inList('NOTIN,IN', strtoupper(str_replace(array(' ', '
-', '
-', '   '), '', $where_clause[$k]['comparator'])))) {
+                                       if (GeneralUtility::inList('NOTIN,IN', strtoupper(str_replace(array(' ', LF, CR, TAB), '', $where_clause[$k]['comparator'])))) {
                                                if (isset($v['subquery'])) {
                                                        $where_clause[$k]['subquery'] = $this->quoteSELECTsubquery($v['subquery']);
                                                }
@@ -1801,6 +1816,8 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                                        && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.')
                                                ) {
                                                        $where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
+                                               } elseif ($this->runningADOdbDriver('mssql')) {
+                                                       $where_clause[$k]['value'][0] = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($where_clause[$k]['value'][0]), 1, -1);
                                                }
                                        }
                                }
@@ -2041,6 +2058,73 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                return $this->dbmsSpecifics->getNativeFieldType($meta);
        }
 
+       /*********************************************
+        *
+        * SqlSchemaMigrationService helper functions
+        *
+        *********************************************/
+       /**
+        * Remove the index prefix length information from columns in an index definition.
+        * Partial indexes based on a prefix are not supported by all databases.
+        *
+        * @param string $indexSQL
+        * @return string
+        */
+       public function getEquivalentIndexDefinition($indexSQL) {
+               if ($this->dbmsSpecifics->specificExists(Specifics\AbstractSpecifics::PARTIAL_STRING_INDEX) && (bool)$this->dbmsSpecifics->getSpecific(Specifics\AbstractSpecifics::PARTIAL_STRING_INDEX)) {
+                       return $indexSQL;
+               }
+
+               $strippedIndexSQL = preg_replace_callback(
+                       '/\A([^(]+)\((.*)\)\Z/',
+                       function($matches) {
+                               return $matches[1] . '(' . preg_replace('/\((\d+)\)/', '', $matches[2]) . ')';
+                       },
+                       $indexSQL
+               );
+
+               return $strippedIndexSQL === NULL ? $indexSQL : $strippedIndexSQL;
+       }
+
+       /**
+        * Convert the native MySQL Field type to the closest matching equivalent field type supported by the DBMS.
+        * INTEGER and TINYTEXT colums need to be further processed due to MySQL limitations / non-standard features.
+        *
+        * @param string $fieldSQL
+        * @return string
+        */
+       public function getEquivalentFieldDefinition($fieldSQL) {
+               if (!preg_match('/^([a-z0-9]+)(\(([^\)]+)\))?(.*)/', $fieldSQL, $components)) {
+                       return $fieldSQL;
+               }
+
+               $metaType = $this->dbmsSpecifics->getMetaFieldType($components[1]);
+               $replacementType = $this->dbmsSpecifics->getNativeFieldType($metaType);
+               $replacementLength = $components[2];
+               $replacementExtra = '';
+
+               // MySQL INT types support a display length that has no effect on the
+               // actual range of values that can be stored, normalize to the default
+               // display length returned by DBAL.
+               if (substr($metaType, 0, 1) === 'I') {
+                       $replacementLength = $this->dbmsSpecifics->getNativeFieldLength($replacementType, $components[3]);
+               }
+
+               // MySQL TINYTEXT is equivalent to VARCHAR(255) DEFAULT NULL. MySQL TEXT
+               // columns can not have a default value in contrast to VARCHAR, so the
+               // `default NULL` gets appended to avoid false-positive schema changes.
+               if ($components[1] === 'tinytext') {
+                       $replacementLength = '(255)';
+                       if (FALSE !== stripos($components[0], ' NOT NULL')) {
+                               $replacementExtra = ' default \'\'';
+                       } else {
+                               $replacementExtra = ' default NULL';
+                       }
+               }
+
+               return str_replace($components[1] . $components[2], strtolower($replacementType) . $replacementLength, $components[0]) . $replacementExtra;
+       }
+
        /**************************************
         *
         * SQL wrapper functions (Overriding parent methods)
@@ -2135,7 +2219,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                break;
                        case 'adodb':
                                // Check if method exists for the current $res object.
-                               // If a table exists in TCA but not in the db, a error
+                               // If a table exists in TCA but not in the db, an error
                                // occurred because $res is not a valid object.
                                if (method_exists($res, 'FetchRow')) {
                                        $output = $res->FetchRow();
@@ -2203,7 +2287,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                break;
                        case 'adodb':
                                // Check if method exists for the current $res object.
-                               // If a table exists in TCA but not in the db, a error
+                               // If a table exists in TCA but not in the db, an error
                                // occurred because $res is not a valid object.
                                if (method_exists($res, 'FetchRow')) {
                                        $output = $res->FetchRow();
@@ -2497,7 +2581,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                if (!$ret) {
                        GeneralUtility::sysLog(
                                'Could not select MySQL database ' . $databaseName . ': ' . $this->sql_error(),
-                               'Core',
+                               'core',
                                GeneralUtility::SYSLOG_SEVERITY_FATAL
                        );
                }
@@ -2824,9 +2908,9 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                // Process query based on type:
                switch ($parsedQuery['type']) {
                        case 'CREATETABLE':
-
                        case 'ALTERTABLE':
-
+                               $this->createMappingsIfRequired($parsedQuery);
+                               // Fall-through next instruction
                        case 'DROPTABLE':
                                $this->clearCachedFieldInfo();
                                $this->map_genericQueryParsed($parsedQuery);
@@ -2973,7 +3057,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                        if ($link->set_charset($this->connectionCharset) === FALSE) {
                                                GeneralUtility::sysLog(
                                                        'Error setting connection charset to "' . $this->connectionCharset . '"',
-                                                       'Core',
+                                                       'core',
                                                        GeneralUtility::SYSLOG_SEVERITY_ERROR
                                                );
                                        }
@@ -2987,7 +3071,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                                        if ($this->query($command) === FALSE) {
                                                                GeneralUtility::sysLog(
                                                                        'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
-                                                                       'Core',
+                                                                       'core',
                                                                        GeneralUtility::SYSLOG_SEVERITY_ERROR
                                                                );
                                                        }
@@ -2998,7 +3082,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
 
                                        $output = TRUE;
                                } else {
-                                       GeneralUtility::sysLog('Could not connect to MySQL server ' . $cfgArray['config']['host'] . ' with user ' . $cfgArray['config']['username'] . '.', 'Core', 4);
+                                       GeneralUtility::sysLog('Could not connect to MySQL server ' . $cfgArray['config']['host'] . ' with user ' . $cfgArray['config']['username'] . '.', 'core', GeneralUtility::SYSLOG_SEVERITY_FATAL);
                                }
                                break;
                        case 'adodb':
@@ -3028,7 +3112,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                }
                                if (!$this->handlerInstance[$handlerKey]->isConnected()) {
                                        $dsn = $cfgArray['config']['driver'] . '://' . $cfgArray['config']['username'] . ((string)$cfgArray['config']['password'] !== '' ? ':XXXX@' : '') . $cfgArray['config']['host'] . (isset($cfgArray['config']['port']) ? ':' . $cfgArray['config']['port'] : '') . '/' . $cfgArray['config']['database'] . ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'] ? '' : '?persistent=1');
-                                       GeneralUtility::sysLog('Could not connect to DB server using ADOdb on ' . $cfgArray['config']['host'] . ' with user ' . $cfgArray['config']['username'] . '.', 'Core', 4);
+                                       GeneralUtility::sysLog('Could not connect to DB server using ADOdb on ' . $cfgArray['config']['host'] . ' with user ' . $cfgArray['config']['username'] . '.', 'core', GeneralUtility::SYSLOG_SEVERITY_FATAL);
                                        error_log('DBAL error: Connection to ' . $dsn . ' failed. Maybe PHP doesn\'t support the database?');
                                        $output = FALSE;
                                } else {
@@ -3340,7 +3424,7 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
        }
 
        /**
-        * Generic mapping of table/field names arrays (as parsed by \TYPO3\CMS\Core\Database\SqlParser)
+        * Generic mapping of table/field names arrays (as parsed by \TYPO3\CMS\Dbal\Database\SqlParser)
         *
         * @param array $sqlPartArray Array with parsed SQL parts; Takes both fields, tables, where-parts, group and order-by. Passed by reference.
         * @param string $defaultTable Default table name to assume if no table is found in $sqlPartArray
@@ -3363,9 +3447,10 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                                case 'CASE':
                                                        if (isset($sqlPartArray[$k]['case_field'])) {
                                                                $fieldArray = explode('.', $sqlPartArray[$k]['case_field']);
-                                                               if (count($fieldArray) == 1 && is_array($this->mapping[$defaultTableKey]['mapFieldNames']) && isset($this->mapping[$defaultTableKey]['mapFieldNames'][$fieldArray[0]])) {
+                                                               $fieldArrayCount = count($fieldArray);
+                                                               if ($fieldArrayCount === 1 && is_array($this->mapping[$defaultTableKey]['mapFieldNames']) && isset($this->mapping[$defaultTableKey]['mapFieldNames'][$fieldArray[0]])) {
                                                                        $sqlPartArray[$k]['case_field'] = $this->mapping[$defaultTableKey]['mapFieldNames'][$fieldArray[0]];
-                                                               } elseif (count($fieldArray) == 2) {
+                                                               } elseif ($fieldArrayCount === 2) {
                                                                        // Map the external table
                                                                        $table = $fieldArray[0];
                                                                        $tableKey = $this->getMappingKey($table);
@@ -3421,10 +3506,11 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                        // Mapping field name in SQL-functions like MIN(), MAX() or SUM()
                                        if ($this->mapping[$t]['mapFieldNames']) {
                                                $fieldArray = explode('.', $sqlPartArray[$k]['func_content']);
-                                               if (count($fieldArray) == 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
+                                               $fieldArrayCount = count($fieldArray);
+                                               if ($fieldArrayCount === 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
                                                        $sqlPartArray[$k]['func_content.'][0]['func_content'] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
                                                        $sqlPartArray[$k]['func_content'] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
-                                               } elseif (count($fieldArray) == 2) {
+                                               } elseif ($fieldArrayCount === 2) {
                                                        // Map the external table
                                                        $table = $fieldArray[0];
                                                        $tableKey = $this->getMappingKey($table);
@@ -3469,9 +3555,10 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                                        // this is a very simplistic check, beware
                                        if (!is_numeric($sqlPartArray[$k]['value'][0]) && !isset($sqlPartArray[$k]['value'][1])) {
                                                $fieldArray = explode('.', $sqlPartArray[$k]['value'][0]);
-                                               if (count($fieldArray) == 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
+                                               $fieldArrayCount = count($fieldArray);
+                                               if ($fieldArrayCount === 1 && is_array($this->mapping[$t]['mapFieldNames']) && isset($this->mapping[$t]['mapFieldNames'][$fieldArray[0]])) {
                                                        $sqlPartArray[$k]['value'][0] = $this->mapping[$t]['mapFieldNames'][$fieldArray[0]];
-                                               } elseif (count($fieldArray) == 2) {
+                                               } elseif ($fieldArrayCount === 2) {
                                                        // Map the external table
                                                        $table = $fieldArray[0];
                                                        $tableKey = $this->getMappingKey($table);
@@ -3532,10 +3619,10 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
        }
 
        /**
-        * Will do table/field mapping on a general \TYPO3\CMS\Core\Database\SqlParser-compliant SQL query
+        * Will do table/field mapping on a general \TYPO3\CMS\Dbal\Database\SqlParser-compliant SQL query
         * (May still not support all query types...)
         *
-        * @param array $parsedQuery Parsed QUERY as from \TYPO3\CMS\Core\Database\SqlParser::parseSQL(). NOTICE: Passed by reference!
+        * @param array $parsedQuery Parsed QUERY as from \TYPO3\CMS\Dbal\Database\SqlParser::parseSQL(). NOTICE: Passed by reference!
         * @throws \InvalidArgumentException
         * @return void
         * @see \TYPO3\CMS\Core\Database\SqlParser::parseSQL()
@@ -3609,6 +3696,51 @@ class DatabaseConnection extends \TYPO3\CMS\Core\Database\DatabaseConnection {
                }
        }
 
+       /**
+        * Create a mapping for each table and field if required.
+        *
+        * @param array $parsedQuery The parsed query
+        * @return void
+        */
+       protected function createMappingsIfRequired($parsedQuery) {
+               if (
+                       !$this->dbmsSpecifics->specificExists(Specifics\AbstractSpecifics::TABLE_MAXLENGTH)
+                       && !$this->dbmsSpecifics->specificExists(Specifics\AbstractSpecifics::FIELD_MAXLENGTH)
+               ) {
+                       return;
+               }
+
+               $mappingConfiguration = array();
+               $table = $parsedQuery['TABLE'];
+               if (!isset($this->mapping[$table])) {
+                       $truncatedTable = $this->dbmsSpecifics->truncateIdentifier($table, Specifics\AbstractSpecifics::TABLE_MAXLENGTH);
+                       if ($table !== $truncatedTable) {
+                               $mappingConfiguration['mapTableName'] = $truncatedTable;
+                       }
+               }
+               foreach ($parsedQuery['FIELDS'] as $field => $_) {
+                       if (!isset($this->mapping[$table]['mapFieldNames'][$field])) {
+                               $truncatedField = $this->dbmsSpecifics->truncateIdentifier($field, Specifics\AbstractSpecifics::FIELD_MAXLENGTH);
+                               if ($field !== $truncatedField) {
+                                       $mappingConfiguration['mapFieldNames'][$field] = $truncatedField;
+                               }
+                       }
+               }
+               if (!empty($mappingConfiguration)) {
+                       /** @var \TYPO3\CMS\Extbase\Object\ObjectManager $objectManager */
+                       $objectManager = GeneralUtility::makeInstance(\TYPO3\CMS\Extbase\Object\ObjectManager::class);
+                       /** @var \TYPO3\CMS\Core\Configuration\ConfigurationManager $configurationManager */
+                       $configurationManager = $objectManager->get(\TYPO3\CMS\Core\Configuration\ConfigurationManager::class);
+                       $configurationManager->setLocalConfigurationValueByPath(
+                               'EXTCONF/dbal/mapping/' . $table,
+                               $mappingConfiguration
+                       );
+
+                       // renew mapping information
+                       $this->mapping = array_merge($this->mapping, array($table => $mappingConfiguration));
+               }
+       }
+
        /**************************************
         *
         * Debugging