Oracle: createSchema has problems if DB tables are lowercase

[mondrake]

Bug Report

Q	A
Version	3.4.0

Summary

Since 3.4.0, schema introspection on Oracle when the DB has lowercase named tables, leads to 'table not found' errors when e.g. dropping a column or creating an index.

Current behaviour

Create a table with lowercase name, then create a schema, clone it and use the clone to drop a column, then the SQL generated does not quote the table name e.g.

ALTER TABLE test82957842test_table_1 DROP ("test_field")

This statement is uppercased by Oracle's parser, so the table results as not found.

How to reproduce

See above

Expected behaviour

The generated SQL should have the table name quoted e.g.

ALTER TABLE "test82957842test_table_1" DROP ("test_field")

[morozov]

@mondrake please provide more details on reproducing the issue. The following scenario works as expected:

$table = new Table('"user"');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('name', Types::STRING);

$this->dropAndCreateTable($table);

$onlineTable = $this->schemaManager->listTableDetails('"user"');
$table->dropColumn('name');

$comparator = $this->schemaManager->createComparator();
$diff = $comparator->diffTable($onlineTable, $table);
$this->schemaManager->alterTable($diff);

[mondrake]

@morozov this is a simplified version of the code that fails for me

$table = new Table('"tester"');
$table->addColumn('"id"', Types::INTEGER);
$table->addColumn('"name"', Types::STRING);
$table->addColumn('"test_field"', Types::STRING);

$schemaManager = $tmpConnection->createSchemaManager();
$schemaManager->dropAndCreateTable($table);

$current_schema = $schemaManager->createSchema();
$to_schema = clone $current_schema;
$to_schema->getTable('"tester"')->dropColumn('"test_field"');
$schema_diff = (new Comparator())->compareSchemas($current_schema, $to_schema);
foreach ($schema_diff->toSql($tmpConnection->getDatabasePlatform()) as $sql) {
  print($sql . "\n");
  $tmpConnection->executeStatement($sql);
}

The output is

ALTER TABLE tester DROP ("test_field")
PHP Fatal error:  Uncaught Doctrine\DBAL\Driver\OCI8\Exception\Error: ORA-00942: table or view does not exist in /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Driver/OCI8/Exception/Error.php:27
Stack trace:
#0 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Driver/OCI8/Statement.php(169): Doctrine\DBAL\Driver\OCI8\Exception\Error::new()
#1 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Driver/OCI8/Connection.php(105): Doctrine\DBAL\Driver\OCI8\Statement->execute()
#2 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Connection.php([113](https://github.com/mondrake/drudbal/runs/7760900324?check_suite_focus=true#step:10:114)5): Doctrine\DBAL\Driver\OCI8\Connection->exec()
#3 /home/runner/work/drudbal/drudbal/install_oracle.php(42): Doctrine\DBAL\Connection->executeStatement()
#4 {main}

Next Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing a query: ORA-00942: table or view does not exist in /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Driver/API/OCI/ExceptionConverter.php:50
Stack trace:
#0 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Connection.php(1822): Doctrine\DBAL\Driver\API\OCI\ExceptionConverter->convert()
#1 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Connection.php(1763): Doctrine\DBAL\Connection->handleDriverException()
#2 /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Connection.php(1137): Doctrine\DBAL\Connection->convertExceptionDuringQuery()
#3 /home/runner/work/drudbal/drudbal/install_oracle.php(42): Doctrine\DBAL\Connection->executeStatement()
#4 {main}
  thrown in /home/runner/work/drudbal/drudbal/vendor/doctrine/dbal/src/Driver/API/OCI/ExceptionConverter.php on line 50

[morozov]

Thanks, @mondrake. It's reproducible this way.

[morozov]

This issue has the same root cause as https://github.com/doctrine/dbal/issues/5573: after the refactoring made in 3.4.x, the table names no longer get passed through AbstractSchemaManager::_getPortableTableDefinition(). We'll need to get https://github.com/doctrine/dbal/pull/5576 merged first.

[mondrake]

Thanks @morozov

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.