[FIXED] How to avoid DROP DEFAULT statements with Doctrine 2 Migrations diff on first run?

Issue

I had an existing PostgreSQL database with a table created like this:

CREATE TABLE product (id SERIAL PRIMARY KEY, name VARCHAR(100) DEFAULT NULL)

This table is described in a YML Doctrine2 file within a Symfony2 project:

Acme\DemoBundle\Entity\Product:
    type: entity
    table: product
    fields:
        id:
            id: true
            type: integer
            nullable: false
            generator:
                strategy: SEQUENCE
        name:
            type: string
            length: 100
            nullable: true

When I run for the first time the Doctrine Migrations diff task, I should get a versioning file with no data in the up and down methods. But what I get instead is this :

// ...

class Version20120807125808 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is autogenerated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql");

        $this->addSql("ALTER TABLE product ALTER id DROP DEFAULT");
    }

    public function down(Schema $schema)
    {
        // this down() migration is autogenerated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql");

        $this->addSql("CREATE SEQUENCE product_id_seq");
        $this->addSql("SELECT setval('product_id_seq', (SELECT MAX(id) FROM product))");
        $this->addSql("ALTER TABLE product ALTER id SET DEFAULT nextval('product_id_seq')");
    }
}

Why are differences detected? How can I avoid this? I tried several sequence strategies with no success.

Solution

A little update on this question.

Using Doctrine 2.4, the solution is to use the IDENTITY generator strategy :

Acme\DemoBundle\Entity\Product:
    type: entity
    table: product
    id:
        type: integer
        generator:
            strategy: IDENTITY
    fields:
        name:
            type: string
            length: 100
            nullable: true

To avoid DROP DEFAULT on fields that have a default value in the database, the default option on the field is the way to go. Of course this can be done with lifecycle callbacks, but it's necessary to keep the default value in the database if this database is used by other apps.

For a "DEFAULT NOW()" like default value, the solution is the following one:

Acme\DemoBundle\Entity\Product:
    type: entity
    table: product
    id:
        type: integer
        generator:
            strategy: IDENTITY
    fields:
        creation_date:
            type: datetime
            nullable: false
            options:
                default: CURRENT_TIMESTAMP

Answered By - Michaël Perrin