Migrations Diff and ORM Schema commands use schema_filter config differently

[cristi-contiu]

Bug Report

Q	A
BC Break	no
Version	3.8.2

Summary

With PostgreSQL, the migrations diff command does not include expected create table queries for tables not in public schema when using doctrine.dbal.schema_filter config. The ORM's schema update and validate commands include these queries.

Current behavior

When building the diff, the DiffGenerator->createToSchema() removes the schema name from the table name (in DiffGenerator->resolveTableName) before passing it to the $schemaAssetsFilter to be matched against the regex in schema_filter. The ORM's schema generation keeps the schema name if it's not the default (search path) schema (usually the public schema), so two different names for the same table will be matched against the regex, with and without the schema name.

I would suggest aligning the way the schema_filter is used by not removing the schema name (if any) from the table name before sending it to the $schemaAssetsFilter. This can be done simply by not calling DiffGenerator->resolveTableName in DiffGenerator->createToSchema(). I'm not sure about backwards compatibility implications on other database vendors.

It's also worth mentioning that both the ORM and Migrations commands get from the DBAL schema introspection an already filtered version of the database schema and the ORM commands do not apply filtering to the schema created from metadata - if a table is added to the mappings, it is honored and tracked even if it does not match the regex in schema_filter. The schema_filter config can be used only to ignore tables in the database, not as a way to exclude tables from the ORM's mapping metadata. To align these behaviours would mean to remove any extra operations on $toSchema in DiffGenerator->createToSchema() and to make an extra check in $fromSchema before applying the filtering.

How to reproduce

In a blank Symfony 7.2 project with Docker support, DoctrineBundle and PostgreSQL, add schema_filter set in config/packages/doctrine.yaml to filter tables by schema name:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        schema_filter: ~^app_schema\.~

Add a new Entity inside that schema:

<?php

namespace App\Entity;

use App\Repository\CustomerRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: CustomerRepository::class)]
#[ORM\Table(name: 'customer', schema: 'app_schema')]
class Customer
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    public function getId(): ?int
    {
        return $this->id;
    }
}

Running the migrations diff command generates a migration with an empty up method because "customer" does not match regex ~^app_schema\.~:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs

    }

The ORM commands properly identify the table that needs creating, as they match "app_schema.customer" against ~^app_schema\.~:

$ bin/console doctrine:schema:update --dump-sql
CREATE TABLE app_schema.customer (id SERIAL NOT NULL, PRIMARY KEY(id));

$ bin/console doctrine:schema:validate --verbose
Mapping
-------
 [OK] The mapping files are correct.

Database
--------
 [ERROR] The database schema is not in sync with the current mapping file.                                              
 // 1 schema diff(s) detected:
     CREATE TABLE app_schema.customer (id SERIAL NOT NULL, PRIMARY KEY(id));

If a migration was manually created with the query above and executed, the next migrations generated from diff would always contain a DROP TABLE app_schema.customer query.

Expected behavior

The diff migration should contain the create table query identified by the schema update command:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE app_schema.customer (id SERIAL NOT NULL, PRIMARY KEY(id))');
    }

Likewise, if the migration was manually created with the CREATE TABLE query and executed, the following migrations generated from diff should not contain the DROP TABLE query.