[BUGFIX] Increase sequence if uid gets forced for PostgreSQL 39/54239/2
authorManuel Selbach <manuel_selbach@yahoo.de>
Fri, 24 Feb 2017 09:03:54 +0000 (10:03 +0100)
committerAndreas Fernandez <typo3@scripting-base.de>
Fri, 29 Sep 2017 20:18:02 +0000 (22:18 +0200)
If a uid gets forcefully set during a insert into the database
MySQL will automatically update the auto_increment primary key.
Other databases might need post processing, i.e PostgreSQL as
it uses a sequence to generate auto increment uids. Sequences
do not get transparently updated on inserts.

Resolves: #79976
Releases: master, 8.7
Change-Id: I033deb63bf9bc2e072408885a534752573ba4c7c
Reviewed-on: https://review.typo3.org/54239
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Andreas Fernandez <typo3@scripting-base.de>
Tested-by: Andreas Fernandez <typo3@scripting-base.de>
typo3/sysext/core/Classes/DataHandling/DataHandler.php
typo3/sysext/impexp/Tests/Functional/Import/PagesAndTtContentWithImagesInEmptyDatabaseTest.php

index b1b89e1..442b1e2 100644 (file)
@@ -16,6 +16,7 @@ namespace TYPO3\CMS\Core\DataHandling;
 
 use Doctrine\DBAL\DBALException;
 use Doctrine\DBAL\Driver\Statement;
+use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
 use Doctrine\DBAL\Platforms\SQLServerPlatform;
 use Doctrine\DBAL\Types\IntegerType;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
@@ -7181,7 +7182,8 @@ class DataHandler
                     // Set mapping for NEW... -> real uid:
                     // the NEW_id now holds the 'NEW....' -id
                     $NEW_id = $id;
-                    $id = $connection->lastInsertId($table);
+                    $id = $this->postProcessDatabaseInsert($connection, $table, $suggestedUid);
+
                     if (!$dontSetNewIdIndex) {
                         $this->substNEWwithIDs[$NEW_id] = $id;
                         $this->substNEWwithIDs_table[$NEW_id] = $table;
@@ -9143,6 +9145,72 @@ class DataHandler
     }
 
     /**
+     * Entry point to post process a database insert. Currently bails early unless a UID has been forced
+     * and the database platform is not MySQL.
+     *
+     * @param \TYPO3\CMS\Core\Database\Connection $connection
+     * @param string $tableName
+     * @param int $suggestedUid
+     * @return int
+     */
+    protected function postProcessDatabaseInsert(Connection $connection, string $tableName, int $suggestedUid): int
+    {
+        if ($suggestedUid !== 0 && $connection->getDatabasePlatform() instanceof PostgreSqlPlatform) {
+            $this->postProcessPostgresqlInsert($connection, $tableName);
+            // The last inserted id on postgresql is actually the last value generated by the sequence.
+            // On a forced UID insert this might not be the actual value or the sequence might not even
+            // have generated a value yet.
+            // Return the actual ID we forced on insert as a surrogate.
+            return $suggestedUid;
+        }
+
+        return $connection->lastInsertId($tableName);
+    }
+
+    /**
+     * PostgreSQL works with sequences for auto increment columns. A sequence is not updated when a value is
+     * written to such a column. To avoid clashes when the sequence returns an existing ID this helper will
+     * update the sequence to the current max value of the column.
+     *
+     * @param \TYPO3\CMS\Core\Database\Connection $connection
+     * @param string $tableName
+     */
+    protected function postProcessPostgresqlInsert(Connection $connection, string $tableName)
+    {
+        $queryBuilder = $connection->createQueryBuilder();
+        $queryBuilder->getRestrictions()->removeAll();
+        $row = $queryBuilder->select('PGT.schemaname', 'S.relname', 'C.attname', 'T.relname AS tablename')
+            ->from('pg_class', 'S')
+            ->from('pg_depend', 'D')
+            ->from('pg_class', 'T')
+            ->from('pg_attribute', 'C')
+            ->from('pg_tables', 'PGT')
+            ->where(
+                $queryBuilder->expr()->eq('S.relkind', $queryBuilder->quote('S')),
+                $queryBuilder->expr()->eq('S.oid', $queryBuilder->quoteIdentifier('D.objid')),
+                $queryBuilder->expr()->eq('D.refobjid', $queryBuilder->quoteIdentifier('T.oid')),
+                $queryBuilder->expr()->eq('D.refobjid', $queryBuilder->quoteIdentifier('C.attrelid')),
+                $queryBuilder->expr()->eq('D.refobjsubid', $queryBuilder->quoteIdentifier('C.attnum')),
+                $queryBuilder->expr()->eq('T.relname', $queryBuilder->quoteIdentifier('PGT.tablename')),
+                $queryBuilder->expr()->eq('PGT.tablename', $queryBuilder->quote($tableName))
+            )
+            ->setMaxResults(1)
+            ->execute()
+            ->fetch();
+
+        if ($row !== false) {
+            $connection->exec(
+                sprintf(
+                    'SELECT SETVAL(%s, COALESCE(MAX(%s), 0)+1, FALSE) FROM %s',
+                    $connection->quote($row['schemaname'] . '.' . $row['relname']),
+                    $connection->quoteIdentifier($row['attname']),
+                    $connection->quoteIdentifier($row['schemaname'] . '.' . $row['tablename'])
+                )
+            );
+        }
+    }
+
+    /**
      * Return the cache entry identifier for field evals
      *
      * @param string $additionalIdentifier
index 087ef13..f881fdb 100644 (file)
@@ -170,14 +170,10 @@ class PagesAndTtContentWithImagesInEmptyDatabaseTest extends AbstractImportExpor
 
     /**
      * @test
-     * @group not-postgres
      * @group not-mssql
      */
     public function importPagesAndRelatedTtContentWithImageWithForcedUids()
     {
-        // @todo: Fix impexp / test with postgres: Probably, force uid's is not possible with postgres or needs
-        // @todo: to be adapted in somehow.
-
         $subject = GeneralUtility::makeInstance(Import::class);
         $subject->init();