To reproduce:

Create a new entity with a column type decimal @Column(type="decimal")

Run the CLI tool "migrations:diff"

Run the CLI tool "migrations:migrate"

If you run "migrations:diff" again a new migration will be created with an ALTER TABLE that only affects the columns of type Decimal. You can apply these new migrations and generate more using Diff ad nauseum.

My assumption is there is a mismatch between the Migrations Diff and annotation's defaults, causing it to continually identify decimal columns as requiring a migration.

To avoid this issue manually set a precision and scale, everything will then work as expected.

I am playing around with the diff tool and noticing that a lot of false positives are generated, like using SERIAL creates Sequences that are not explicitly listed, causing the diff tool to believe that they should be dropped. Or using "array" as a datatype makes the diff tool believe that there was a type change since they are setup as TEXT. now the alter statement then actually specifies to alter it form TEXT to TEXT again.

I guess the fact is that just more work needs to be put in here and I guess if I want things to improve I should step up, especially if I care about such an "obsure" database as Postresql

I create entities from doctrine getting started tutorial http://www.doctrine-project.org/docs/orm/2.0/en/tutorials/getting-started-xml-edition.html
It's Bug, User, Project (in attach)
and create config migrations.yml (in attach)

after that i run command "./doctrine migrations:diff" which generated migration file Version20110115224434.php (in attach)
"./doctrine migrations:migrate" work normal
but "./doctrine migrations:migrate 0" dosn't work:

Migrating down to 0 from 20110115224434

– reverting 20110115224434

-> ALTER TABLE bug_product DROP FOREIGN KEY
Migration 20110115224434 failed during Execution. Error SQLSTATE[HY000]: General error: 1005 Can't create table 'doctrine2.#sql-531_108' (errno: 150)

[PDOException]
SQLSTATE[HY000]: General error: 1005 Can't create table 'doctrine2.#sql-531_108' (errno: 150)

I have a table design style, which has api_id field with unique key
CREATE TABLE `design_style` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`api_id` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
.....
PRIMARY KEY (`id`),
UNIQUE KEY `api_id` (`api_id`),
UNIQUE KEY `name` (`name`),
KEY `category_id_idx` (`category_id`),
CONSTRAINT `design_style_category_id_design_style_category_id` FOREIGN KEY (`category_id`) REFERENCES `design_style_category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I reverse engineered the database tables and created entities. I am using it in symfony2 app.

Then I executed
php app/console doctrine:schema:update --dump-sql
and also checked with migrations diff
php app/console doctrine:migrations:diff

but has given me
DROP INDEX api_id ON design_page

Currently schema console only asks if you really want to do the migration, however you dont know what will actually happen.

all the operations should be posted before asking the question to do the migration.

Currently when using :migrations:diff the generated output will use addSql() exclusively. However there has to be a way to generate changes on the schema objects themselves by using the SchemaDiff objects and generating code from them.

When DMIG-27 is implemented, add a new operation "callback" that is executed at the position in the stack and can do arbitrary things (for example use the EntityManager to migrate data!)

Up and down operations should only put commands on a stack that is then executed after up() or down() have been processed.

Currently there are two implicit stacks:

1. addSql()
2. The changes done to the $schema instance.

With DMIG-26 $schema is an explicit change that adds to a stack.

This should be unified to be one single stack called "operations" and they should be linearly processed.

Currently the following leads to an implicit drop table statement:

public function up(Schema $schema)
 {
    $schema->dropTable("foo");
}

However this should be an explicit operation that internally puts everything onto the "addSQL" stack, then resets the schema diffs to zero by cloning the current schema into the "diff schema" again.

public function up(Schema $schema)
 {
    $schema->dropTable("foo");
    $this->syncSchema($schema);
    $schema->dropTable("bar");
    $this->syncSchema($schema);
}

I get the following error when trying to run doctrine migrations:status in a newly configured setup:

PHP Fatal error:  Call to undefined method Doctrine\DBAL\Migrations\Tools\Console\Command\StatusCommand::getApplication() in /library/vendor/migrations/lib/Doctrine/DBAL/Migrations/Tools/Console/Command/AbstractCommand.php on line 82

Changing line 82 of AbstractCommand.php to the following gets it working, but I am unsure of the implications:

if ($this->getHelper('db')) {

When an attribute has a @Column(columnDefinition=...) parameter, migrations:diff always causes false positives.

I think it would be a good idea to put the Migrations classes under their own namespace. If you use \Doctrine\Common\ClassLoader for loading both, DBAL and Migrations, you need to load Migrations before DBAL to avoid raising an exception.

Throws exception:

$classLoader = new \Doctrine\Common\ClassLoader('Doctrine\DBAL', realpath(APPLICATION_PATH . '/../library/Doctrine/DBAL/lib'));
$classLoader->register();
$classLoader = new \Doctrine\Common\ClassLoader('Doctrine\DBAL\Migrations', realpath(APPLICATION_PATH . '/../library/Doctrine/Migrations/lib'));
$classLoader->register();

Works:

$classLoader = new \Doctrine\Common\ClassLoader('Doctrine\DBAL\Migrations', realpath(APPLICATION_PATH . '/../library/Doctrine/Migrations/lib'));
$classLoader->register();
$classLoader = new \Doctrine\Common\ClassLoader('Doctrine\DBAL', realpath(APPLICATION_PATH . '/../library/Doctrine/DBAL/lib'));
$classLoader->register();

When iterating migration versions, a call to SchemaManager::createSchema() is done for every versions. Four our schema, this call take about 1 second to execute. With already 30 migrations, this begin to be a problem.

$versions = $configuration->registerMigrationsFromDirectory($this->_migration_folder);
foreach($versions as $version){
    if(!$version->isMigrated()){
        $version->markMigrated();
    }

}

The problem is in Migrations\Configuration\Configuration::createMigrationTable() which does not set the migrationTableCreated flag when the table already exists.

public function createMigrationTable()
{
        $this->validate();

        if ($this->migrationTableCreated) {
            return false;
        }

        $schema = $this->connection->getSchemaManager()->createSchema();
        if ( ! $schema->hasTable($this->migrationsTableName)) {
            $columns = array(
                'version' => new Column('version', Type::getType('string'), array('length' => 14)),
            );
            $table = new Table($this->migrationsTableName, $columns);
            $table->setPrimaryKey(array('version'));
            $this->connection->getSchemaManager()->createTable($table);

            $this->migrationTableCreated = true;

            return true;
        }
        return false;
}

When using some reserved field names in PostgreSQL DBAL, the generated SQL statements will quote those field names. (Make a table with fieldname "left" for example)

When the migrationversion is generated, the doublequote is not escaped:

addSQL("ALTER table x add column "left" integer");

This does not compile of course, should be

addSQL("ALTER table x add column \"left\" integer");

Regards!

At the moment I found only one way how to work with default values

/**
 * @var string $isActive
 *
 * @Column(name="isActive", type="boolean", nullable=false)
 */
private $isActive;

and then in the construct I use something like $this->setIsActive(true);
on db level it looks like "isActive BIT default 1"
But the problem happened when I try to compare schemas for migrations. It always false positives on defaults.

How to set defaults for prevent false positives in Comparator?

To change this behavior:

would be nice if generated migrations could include @author tag with systems username
it would help when migrations are created by several users to find which migration belongs to who.