migrations

migrations copied to clipboard

Bug Report

Q	A
BC Break	no
Version	3.8.2

Summary

I have a Symfony and PostgreSQL (TimescaleDB) together with Doctrine ORM and Migrations. I added a new entity ProcessedMessage as described in messenger-monitor-bundle. When I generated 1st migration, the generated file contains SQL statements that should not be there (see section below) together with relevant statements (processed_messages). When I added schema_filter: ~^(?!(_timescaledb|timescaledb))~ into doctrine.yaml and regenerated migration file - the up() method was fixed, but down() method still contains wrong statements.

Current behavior

Migration file with default settings:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.hypertable_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.tablespace_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.dimension_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.dimension_slice_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.chunk_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.chunk_constraint_name CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.chunk_column_stats_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_config.bgw_job_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE _timescaledb_internal.bgw_job_stat_history_id_seq CASCADE');
        $this->addSql('CREATE TABLE processed_messages (id SERIAL NOT NULL, run_id INT NOT NULL, attempt SMALLINT NOT NULL, message_type VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, dispatched_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, received_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, finished_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, wait_time BIGINT NOT NULL, handle_time BIGINT NOT NULL, memory_usage INT NOT NULL, transport VARCHAR(255) NOT NULL, tags VARCHAR(255) DEFAULT NULL, failure_type VARCHAR(255) DEFAULT NULL, failure_message TEXT DEFAULT NULL, results JSON DEFAULT NULL, PRIMARY KEY(id))');
        $this->addSql('COMMENT ON COLUMN processed_messages.dispatched_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.received_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.finished_at IS \'(DC2Type:datetime_immutable)\'');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA public');
        $this->addSql('CREATE SCHEMA toolkit_experimental');
        $this->addSql('CREATE SCHEMA timescaledb_information');
        $this->addSql('CREATE SCHEMA timescaledb_experimental');
        $this->addSql('CREATE SCHEMA _timescaledb_internal');
        $this->addSql('CREATE SCHEMA _timescaledb_functions');
        $this->addSql('CREATE SCHEMA _timescaledb_debug');
        $this->addSql('CREATE SCHEMA _timescaledb_config');
        $this->addSql('CREATE SCHEMA _timescaledb_catalog');
        $this->addSql('CREATE SCHEMA _timescaledb_cache');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.hypertable_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.tablespace_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.dimension_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.dimension_slice_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.chunk_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.chunk_constraint_name INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.chunk_column_stats_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE _timescaledb_config.bgw_job_id_seq INCREMENT BY 1 MINVALUE 1000 START 1000');
        $this->addSql('CREATE SEQUENCE _timescaledb_internal.bgw_job_stat_history_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('DROP TABLE processed_messages');
    }

Migration file after schema_filter: ~^(?!(_timescaledb|timescaledb))~ has been added:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE processed_messages (id SERIAL NOT NULL, run_id INT NOT NULL, attempt SMALLINT NOT NULL, message_type VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, dispatched_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, received_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, finished_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, wait_time BIGINT NOT NULL, handle_time BIGINT NOT NULL, memory_usage INT NOT NULL, transport VARCHAR(255) NOT NULL, tags VARCHAR(255) DEFAULT NULL, failure_type VARCHAR(255) DEFAULT NULL, failure_message TEXT DEFAULT NULL, results JSON DEFAULT NULL, PRIMARY KEY(id))');
        $this->addSql('COMMENT ON COLUMN processed_messages.dispatched_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.received_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.finished_at IS \'(DC2Type:datetime_immutable)\'');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA public');
        $this->addSql('CREATE SCHEMA toolkit_experimental');
        $this->addSql('CREATE SCHEMA timescaledb_information');
        $this->addSql('CREATE SCHEMA timescaledb_experimental');
        $this->addSql('CREATE SCHEMA _timescaledb_internal');
        $this->addSql('CREATE SCHEMA _timescaledb_functions');
        $this->addSql('CREATE SCHEMA _timescaledb_debug');
        $this->addSql('CREATE SCHEMA _timescaledb_config');
        $this->addSql('CREATE SCHEMA _timescaledb_catalog');
        $this->addSql('CREATE SCHEMA _timescaledb_cache');
        $this->addSql('DROP TABLE processed_messages');
    }

How to reproduce

Install Symfony 7.2, doctrine-orm, doctrine-migrations-bundle and TimescaleDB. Try to generate a migration with bin/console do:mi:di and see generated file.

Expected behavior

The generated file should be:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE processed_messages (id SERIAL NOT NULL, run_id INT NOT NULL, attempt SMALLINT NOT NULL, message_type VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL, dispatched_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, received_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, finished_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, wait_time BIGINT NOT NULL, handle_time BIGINT NOT NULL, memory_usage INT NOT NULL, transport VARCHAR(255) NOT NULL, tags VARCHAR(255) DEFAULT NULL, failure_type VARCHAR(255) DEFAULT NULL, failure_message TEXT DEFAULT NULL, results JSON DEFAULT NULL, PRIMARY KEY(id))');
        $this->addSql('COMMENT ON COLUMN processed_messages.dispatched_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.received_at IS \'(DC2Type:datetime_immutable)\'');
        $this->addSql('COMMENT ON COLUMN processed_messages.finished_at IS \'(DC2Type:datetime_immutable)\'');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP TABLE processed_messages');
    }

Code

it seems it could be fixed by updating of DiffGenerator class: createToSchema() is using filter, but createFromSchema() is not filtering... Not sure if this is correct place to take a look.