bug #54566 [Doctrine][Messenger] Use common sequence name to get id from Oracle (rjd22)

fabpot · fabpot · commit c898802cb964 · 2024-10-06T11:09:01.000+02:00
This PR was squashed before being merged into the 6.4 branch. Discussion ---------- [Doctrine][Messenger] Use common sequence name to get id from Oracle | Q | A | ------------- | --- | Branch? | 6.4 | Bug fix? | yes | New feature? | no | Deprecations? | no | Issues | Fix #54193 | License | MIT This is a fix for #54193 by using a common sequence name for getting the ID's for Oracle databases. This will require the user to name their sequences seq_<table_name> or SEQ_<TABLE_NAME> but at least allows you to use messenger with Oracle after properly setting up the table. Commits ------- 52c3a32 [Doctrine][Messenger] Use common sequence name to get id from Oracle
diff --git a/src/Symfony/Component/Messenger/Bridge/Doctrine/Transport/Connection.php b/src/Symfony/Component/Messenger/Bridge/Doctrine/Transport/Connection.php
@@ -470,6 +470,18 @@ private function executeInsert(string $sql, array $parameters = [], array $types
                 if (!$id) {
                     throw new TransportException('no id was returned by PostgreSQL from RETURNING clause.');
                 }
+            } elseif ($this->driverConnection->getDatabasePlatform() instanceof OraclePlatform) {
+                $sequenceName = 'seq_'.$this->configuration['table_name'];
+
+                $this->driverConnection->executeStatement($sql, $parameters, $types);
+
+                $result = $this->driverConnection->fetchOne('SELECT '.$sequenceName.'.CURRVAL FROM DUAL');
+
+                $id = (int) $result;
+
+                if (!$id) {
+                    throw new TransportException('no id was returned by Oracle from sequence: '.$sequenceName);
+                }
             } else {
                 $this->driverConnection->executeStatement($sql, $parameters, $types);
 
@@ -507,7 +519,7 @@ private function addTableToSchema(Schema $schema): void
         $table = $schema->createTable($this->configuration['table_name']);
         // add an internal option to mark that we created this & the non-namespaced table name
         $table->addOption(self::TABLE_OPTION_NAME, $this->configuration['table_name']);
-        $table->addColumn('id', Types::BIGINT)
+        $idColumn = $table->addColumn('id', Types::BIGINT)
             ->setAutoincrement(true)
             ->setNotnull(true);
         $table->addColumn('body', Types::TEXT)
@@ -527,6 +539,13 @@ private function addTableToSchema(Schema $schema): void
         $table->addIndex(['queue_name']);
         $table->addIndex(['available_at']);
         $table->addIndex(['delivered_at']);
+
+        // We need to create a sequence for Oracle and set the id column to get the correct nextval
+        if ($this->driverConnection->getDatabasePlatform() instanceof OraclePlatform) {
+            $idColumn->setDefault('seq_'.$this->configuration['table_name'].'.nextval');
+
+            $schema->createSequence('seq_'.$this->configuration['table_name']);
+        }
     }
 
     private function decodeEnvelopeHeaders(array $doctrineEnvelope): array
