Index name is generated from input, not actual object names

[morozov]

Note

This issue falls under the #4357 umbrella ("The support for quoting identifiers is fundamentally broken"). I'm filing it separately to reference from the integration tests that will need to work around it.

When an index has no explicit name, DBAL generates the index name by hashing the table name and column names as written in the input. But the names of the table and its columns in the database depend on:

1. whether the corresponding identifier is quoted;
2. how the platform folds unquoted identifiers (PostgreSQL lower-cases them, Oracle and Db2 upper-case them).

The schema model can't compute that name: it has no platform to apply the folding. So the index name is keyed on the input names, not the ones the objects are created under. Two consequences follow:

First, two spellings that resolve to the same table get different index names. The same unquoted table name spelled Orders or orders produces byte-identical DDL except the index name:

$table = new Table($name);
$table->addColumn('id', 'integer');
$table->addIndex(['id']);

-- Orders
CREATE TABLE "orders" ("id" INT NOT NULL);
CREATE INDEX "idx_e283f8d8bf396750" ON "orders" ("id");

-- orders
CREATE TABLE "orders" ("id" INT NOT NULL);
CREATE INDEX "idx_e52ffdeebf396750" ON "orders" ("id");

The table name renders as orders in both the table and the index target; only the generated index name reflects the input spelling.

Second, two spellings that resolve to different tables get the same index name. Where index names are schema-global (PostgreSQL, Oracle, Db2), they collide:

foreach (['Orders', '"Orders"'] as $name) {
    $table = new Table($name);
    $table->addColumn('id', 'integer');
    $table->addIndex(['id']);
    $schemaManager->createTable($table);
}

The first is created as orders, the second as Orders, but the generated index name is the same, so the second CREATE TABLE fails:

Doctrine\DBAL\Exception\TableExistsException: An exception occurred while executing a query:
relation "idx_e283f8d8bf396750" already exists

The flaw is architectural: the index name must be derived from the created table and column names, which is only known at SQL generation (the platform), but it's generated in the schema model:

dbal/src/Schema/Table.php

Lines 251 to 255 in e33aed8

	$indexName ??= $this->_generateIdentifierName(
	array_merge([$this->getName()], $columnNames),
	'idx',
	$this->_getMaxIdentifierLength(),
	);

There is a second architectural flaw: Postgres supports anonymous indexes, so it's the platform that should decide whether a name for an unnamed index needs to be generated in the first place.