Problems with implicit indexes

[morozov]

At a high level, implicit indexes have two purposes:

1. Create the indexes that the target database platform will require for a certain database schema (DBAL-defined indexes).
2. Account for the indexes that the target database platform will create for a certain database schema in order to avoid false-positive diffs (RDBMS-defined indexes).

Besides the bugs with the tracking such indexes, DBAL does a poor job in fulfilling the design requirements:

1. For each foreign key constraint, all supported database platform require that the referenced table has a unique constraint or index with the columns that matched the columns referenced by the constraint. DBAL doesn't create those constraints or indexes.
2. For each foreign key constraint, MySQL creates an index in the referencing table on the referencing columns. Other database platforms don't do that (and that makes sense), but DBAL explicitly creates this index on all platforms.

Below are the failing tests that reproduce various bugs with implicit index tracking:

    public function testImplicitIndexExistsAsLongAsNecessary(): void
    {
        $table = new Table('t1');
        $table->addColumn('id', 'integer');
        $table->addForeignKeyConstraint('t2', ['id'], ['id']);

        // make sure we have an implicit index
        self::assertCount(1, $table->getIndexes());

        // add and drop an explicit index that matches the implicit one
        $table->addIndex(['id'], 'idx_id');
        $table->dropIndex('idx_id');

        // the implicit index should still be there
        self::assertCount(1, $table->getIndexes());
    }

    public function testImplicitIndexIsDroppedWhenForeignKeyIsDropped(): void
    {
        $table = new Table('t1');
        $table->addColumn('id', 'integer');

        // there shouldn't be any indexes
        self::assertEmpty($table->getIndexes());

        $table->addForeignKeyConstraint('t2', ['id'], ['id'], [], 'fk_id');
        $table->removeForeignKey('fk_id');

        // there still shouldn't be any indexes
        self::assertEmpty($table->getIndexes());
    }

    public function testImplicitIndexIsDroppedWhenALargerExplicitIndexIsAdded(): void
    {
        $table = new Table('t1');
        $table->addColumn('c1', 'integer');
        $table->addColumn('c2', 'integer');
        $table->addForeignKeyConstraint('t2', ['c1'], ['c1'], [], 'fk_c1');

        // make sure we have an implicit index
        self::assertCount(1, $table->getIndexes());

        // add an explicit index that matches the implicit one
        $table->addIndex(['c1', 'c2'], 'idx_c1_c2');

        // there still should be only one index
        self::assertCount(1, $table->getIndexes());
    }

    public function testOneImplicitIndexCreatedForAllMatchingForeignKeyConstraints(): void
    {
        $table = new Table('t1');
        $table->addColumn('c1', 'integer');
        $table->addColumn('c2', 'integer');
        $table->addForeignKeyConstraint('t2', ['c1', 'c2'], ['c1', 'c2'], [], 'fk_c1_c2');
        $table->addForeignKeyConstraint('t2', ['c1'], ['c1'], [], 'fk_c1');

        // the implicit indexes for fk_c1_c2 should also work for fk_c1
        self::assertCount(1, $table->getIndexes());
    }

    public function testTwoUnnamedIndexes(): void
    {
        new Table('t', [
            new Column('c1', Type::getType('integer')),
            new Column('c2', Type::getType('integer')),
        ], [
            new Index(null, ['c1']),
            new Index(null, ['c2']),
        ]);
    }

Proposed solution

Remove the concept of implicit indexes.

DBAL-defined indexes

DBAL shouldn't create the indexes that the destination platform may require (as it already doesn't). In the foreign key constraint case, DBAL may not have a full picture of the database schema (i.e. it allows a table to be created individually). Only the actor that has the full view of the schema (e.g. the ORM or the user) can create the needed indexes.

RDBMS-defined indexes

DBAL shouldn't create the indexes that the destination platform will create automatically. Instead, it should correctly compare the application schema that doesn't contain certain indexes and the RDBMS-define one which may contain them. Therefore, the logic of handling RDBMS-defined indexes should be moved to the schema-aware comparator:

1. For unique constraints on all supported platforms, ignore the corresponding unique indexes.
2. For foreign keys constraints on MySQL, ignore the indexes that cover the corresponding referencing columns in the referencing table.
3. Profit.