db_schema.xml setup:db-declaration:generate-whitelist: Constraints/index changes are not applied/refreshed - (Is there cached db_schema.xml?)

Question

Magento 2.3

Question

Is there any place where db_schema.xml for your module is cached/restored from?

Issue

I need to refresh/get the recently changed db_schema.xml changes to apply, which is not happening.

Expected result:

Updated (shortened) names for indexes.

IDX_GM_CTA_CUSTOMER_ENTITY_ID
IDX_GM_CTA_TRADE_ACCOUNT_STATE_TYPE_ID

Current incorrect result:

Ignores my currently updated (shortened) names and somehow **still uses old versions of names:

VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID
IDX_0C25F86D281C81FDAE652EC2E495584F

I confirmed after setup:upgrade (deleted the database tables) that the table generated in database has these OLD index names.

Further information

This is what the db_schema_whitelist.xml currently shows:

This generated information is old, I have recently updated the index referenceId names (see below):

    "vendor_modulename_tablename": {
        "column": {
            "table_name_id": true,
            "customer_entity_id": true,
            "table_name_state_type_id": true,
            "table_name_discount": true
        },
        "index": {
            "VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID": true,
            "IDX_0C25F86D281C81FDAE652EC2E495584F": true
        },
        "constraint": {
            "PRIMARY": true
        }
    },

The current db_schema.xml for the above generated whitelist:

    <table name="vendor_modulename_tablename" resource="default" engine="innodb" comment="vendor_modulename_tablename Table">
        <column xsi:type="int" name="table_name_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Table Name ID"/>
        <column name="customer_entity_id" nullable="false" xsi:type="int" comment="Customer Entity ID (from customer_entity)" identity="false" unsigned="true"/>
        <column name="table_name_state_type_id" nullable="false" xsi:type="smallint" comment="Table Name State Type ID" unsigned="true"/>
        <column name="table_name_discount" nullable="false" xsi:type="float" comment="Table Name Discount" default="5.10"/>

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="table_name_id"/>
        </constraint>
        <index referenceId="IDX_GM_CTA_CUSTOMER_ENTITY_ID" indexType="btree">
            <column name="customer_entity_id"/>
        </index>
        <index referenceId="IDX_GM_CTA_TRADE_ACCOUNT_STATE_TYPE_ID" indexType="btree">
            <column name="table_name_state_type_id"/>
        </index>
    </table>

The old (previous commit version for the constraints):

        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="table_name_id"/>
        </constraint>
        <index referenceId="VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID" indexType="btree">
            <column name="customer_entity_id"/>
        </index>
        <index referenceId="VENDOR_MODULENAME_TABLENAME_APPLICATION_ACCOUNT_STATE_ACCOUNT_TYPE_ID" indexType="btree">
            <column name="table_name_state_type_id"/>
        </index>

MORE INFORMATION

        "index": {
            "VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID": true,
            "IDX_0C25F86D281C81FDAE652EC2E495584F": true
        },

OLD version of db_schema.xml had these:

• VENDOR_MODULENAME_TABLENAME_APPLICATION_ACCOUNT_STATE_ACCOUNT_TYPE_ID index name is too long (more than 64 characters), so Magento 2 shortened it to auto-generated name: IDX_0C25F86D281C81FDAE652EC2E495584F.

• VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID remains VENDOR_MODULENAME_TABLENAME_CUSTOMER_ENTITY_ID because the character limit is not reached.

NOTE: When generating the name originally, I wasn't fully aware of the character limit, so I recently updated all field/table/index names in module.

Help needed

Where is db_schema.xml cached for your module, so that I can delete it and regenerate the updated white list, or what am I missing?

EDIT: What I tried again now

In summary:

Where are these entries cached? I even tried to do text-searches (grep) inside the whole folder structure of Magento 2 and could not find anything. I restarted MySQL because I thought maybe it's cached there, even though that would be absolutely weird if that is the case!

I followed these steps:

• Removed module files (deleted Vendor/Module_Name).
• In other words, there is no generated db_schema_whitelist.
• Ran bin/magento setup:upgrade
• Deleted database tables related to this module.
• Stopped web services.
• Stopped MySQL service, and restarted.
• Started web services.
• Added module files back (inside Vendor/Module_Name)
• BEFORE RUNNING setup:upgrade I tried to run bin/magento setup:db-declaration-generate-whitelist --module-name=Vendor/Module_Name but of course nothing was generated since the module has not been reinstalled.
• Ran bin/magento setup:upgrade.
• Checked database and OLD NAMES were back :(
• Ran bin/magento setup:db-declaration-generate-whitelist --module-name=Vendor/Module_Name and it had the old generated names.

The behaviour/feature implemented by Magento 2 does not make sense

• After the above steps I went to the **database and ran:

ALTER TABLE database.tablename RENAME INDEX old_index_name TO new_index_name

• So I manually renamed these indexes from the Database side: These indexes are then changed, but **as soon as I run bin/magento setup:upgrade it adds those old names back, so now I have old_index_name and _new_index_name` indexes for the same coluns.

Every single time I am applying changes and need to run bin/magento setup:upgrade I currently would need to rename the OLD indexes to NEW index names.

EDIT

I investigated further, and on debugging I discovered that the core code does not apply referenceId in any way for naming indexes in the database.

I ended up creating a new module to create Plugins for features to use referenceId as my custom names for any modules I make in the future. I created filter information to ensure I only apply these renamed index names to the desired modules (in other words target column tables for the module/s I want to use this custom plugin for)

Answer 1

EDIT

I investigated further, and on debugging I discovered that the core code does not apply referenceId in any way for naming indexes in the database.

I ended up creating a new module to create Plugins for features to use referenceId as my custom names for any modules I make in the future. I created filter information to ensure I only apply these renamed index names to the desired modules (in other words target column tables for the module/s I want to use this custom plugin for)

Previous (Discovered Information)

For reference to anyone that might find this useful:

An update on the index name issue:

• *referenceId appears to only be used by whitelist as a reference for the index, but does not apply this name, even though it appeared from the documentation to do that.

I do not know if the documentation I read from Adobe is accurate or not, but it always hinted that referenceId can be used as the index name in database table as well.

• This commit claims to be named : Index names are ignored by declarative schema, however my files locally are exactly the same as this commit.

Investigation

• Looking at the code while debugging I found the relevant files:

*All of these are in vendor/magento/framework/Setup:

./Setup/Declaration/Schema/Declaration/SchemaBuilder.php 

SchemaBuilder::build() -> SchemaBuilder::processTable() -> $table->addIndexes($this->processIndexes($tableData, $resource, $table))

Relevant code for processIndexes:

    private function processIndexes(array $tableData, string $resource, Table $table): array
    {
...... trimmed
            $indexData['name'] = $this->elementNameResolver->getFullIndexName(
                $table,
                $indexData['column'],
                $indexData['indexType'] ?? null
            );
... trimmed
    }

./Setup/Declaration/Schema/Declaration/TableElement/ElementNameResolver.php:

    public function getFullIndexName(
        Table $table,
        array $columns,
        ?string $type = AdapterInterface::INDEX_TYPE_INDEX
    ): string {
        if (AdapterInterface::INDEX_TYPE_PRIMARY === $type) {
            return strtoupper(AdapterInterface::INDEX_TYPE_PRIMARY);
        }

        /**
         * Temporary solution.
         * @see MAGETWO-91365
         */
        $isIndexTypeOutOfList = false === array_search(
            $type,
            [AdapterInterface::INDEX_TYPE_FULLTEXT, AdapterInterface::INDEX_TYPE_UNIQUE]
        );
        if ($type && $isIndexTypeOutOfList) {
            $type = AdapterInterface::INDEX_TYPE_INDEX;
        }

        $tableName = $this->tableNameResolver->getNameOfOriginTable($table->getName());

        return $this->resourceConnection
            ->getIdxName(
                $tableName,
                $columns,
                $type
            );
    }

The definition appears to be the same of the commit mentioned, and there appears to be nothing here defined that uses the indexed (referenceId) name.

Summary (Solution)

I have two options:

The first option seems to be easier to implement:

• I guess creating a Plugin for ElementNameResolver::getFullIndexName() would be what I need to do (and then make sure I only change the new custom module/s I want to update, keeping everything else intact.

• Or alternatively fiddle with the DB table creation and creating a function renameIndex() that handles existing index renames, at first I thought there is a function that does this, but it appears only to have ./Setup/Declaration/Schema/Dto/Table::addIndexes (no rename index / update index)

    /**
     * Add indexes
     *
     * This is workaround, as any DTO object couldnt be changed after instantiation.
     * However there is case, when we depends on column definition we need modify our indexes
     *
     * @param array $indexes
     */
    public function addIndexes(array $indexes)
    {
        $this->indexes = array_replace($this->indexes, $indexes);
    }