DoctrineEnumBundle Include enum changes in doctrine migration

Hi!

Suppose I add or remove an option from my enum.

When creating a doctrine migration, this change is not included in the migration. Do I have to do this manually? Example: before

final class EnumActionType extends AbstractEnumType {
    
    const LOGIN = 'login';
    const CREATE_USER = 'createUser';
    const CHANGE_PASSWORD = 'changePw';
   
    protected static $choices = [ 
        self::LOGIN =>'Nutzer angemeldet',
        self::CREATE_USER =>'Neuen Nutzer erzeugt',
        self::CHANGE_PASSWORD => 'Passwort geändert'];
}

after:

final class EnumActionType extends AbstractEnumType {
    
    const LOGIN = 'login';
    const CREATE_USER = 'createUser';
    const CHANGE_PASSWORD = 'changePw';
    const NEW_VAL = 'newVal';

    protected static $choices = [ 
        self::LOGIN =>'Nutzer angemeldet',
        self::CREATE_USER =>'Neuen Nutzer erzeugt',
        self::CHANGE_PASSWORD => 'Passwort geändert',
        self::NEW_VAL => 'Neuer Wert'];
}

The automatically created migration by doctrine:migrations:diff does not care about this change. What would be necessary is to first drop each CHECK-constraint, and then recreate it with the new set of enum values:

for postgres:

$values = \implode(
            ', ',
            \array_map(
                function ($value) {
                    return "'{$value}'";
                },
                EnumActionType::getValues()
                )
            );
        $this->addSql('ALTER TABLE action_log_entry DROP CONSTRAINT action_log_entry_action_check');
        $this->addSql("ALTER TABLE action_log_entry ADD CONSTRAINT action_log_entry_action_check CHECK(action IN ($values))");

for MS SQL Server:

$stmt = $this->connection->prepare("SELECT OBJECT_NAME(object_id) AS ConstraintName FROM sys.objects WHERE type_desc='CHECK_CONSTRAINT' AND OBJECT_NAME(parent_object_id)='action_log_entry'");
        $stmt->execute();
        $values = \implode(
            ', ',
            \array_map(
                function ($value) {
                    return "'{$value}'";
                },
                EnumActionType::getValues()
                )
            );
        foreach($stmt->fetchAll() as $row) {
            $this->addSql("ALTER TABLE action_log_entry DROP CONSTRAINT {$row['ConstraintName']}");
            $this->addSql("ALTER TABLE action_log_entry ADD CONSTRAINT {$row['ConstraintName']} CHECK(action IN ($values))");
        }

However, I would additionally have to find all usages of the enum in my entities manually.

I think automating this would be great !?

Sep 18 '18 15:09 flaushi

https://github.com/fre5h/DoctrineEnumBundle/blob/master/Resources/docs/hook_for_doctrine_migrations.md

Oct 17 '18 18:10 ph-il

Thanks a lot for this hint, however it only works for MySQL. In postgres and mssql, this check is realized in a different way: there is a named check or constraint (c.f. action_log_entry_action_check see above), and the migration would have to look up this name, drop the check and then create the new one.

Oct 18 '18 19:10 flaushi

At the database level, all constraints are stored in the context of a table, not a specific field. Get all constraints of type CHECK can be next SQL:

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema_name>' AND rel.relname = '<table_name>' AND con.contype = 'c';

In the results, you can see the real name of the constraint for your column. This will allow us to write the correct migration.

ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> CHECK(action IN ('newValue', 'value1', 'value2', 'value3'));

Also, don't forget to implement down method for new migration. Alters must contain the previous type list.

 -- without newValue
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> CHECK(action IN ('value1', 'value2', 'value3'));