Doctrine migrations fallback

Question

We're using doctrine migrations and there often are problems when the migration contains multiple actions and one of them fails.

For example, if there is a migration adding 5 foreign keys and the 5th of them fails while fields aren't of the same length, fixing the error with the fields and regenerating migrations does not fix the whole thing, while now there is an error connected with the fact 4 of the keys already exists and don't allow the migration to run successfully.

Is there a stable way to use Doctrine migrations without such obvious problems as mentioned? We've used .sql files previosly, which aren't much better actually, but I'm pretty sure there is the right way of database versioning for a Doctrine-using project?

Generating migrations based on the difference between models and schema is great and I'd like to keep this possibility furthermore.

Thanks

Answer 1

Doctrine migrations can not handle this. Sorry to say that we all have these problems, because the migrations didn't run in a transaction.

You can improve this by adding a plugin. See: Blog-Post

The other possibility is to do a database backup before migrating and if something goes wrong you can reinstall the backup. You can automate this by a shell script

Answer 2

If you're using the doctrine-cli you can write your own migration task that backs up the database before the migration and restores the back up if the migration fails. I wrote something similar for our symfony/doctrine migrations.

If you put your task class in the correct directory the doctrine cli will display it in the list of available commands

Answer 3

I kind of solved this, the solution isn't all that nice, but still, I guess it will be useful to other people. I'm using CLI indeed I've already done the file making every migration update the number in the database, similar to the one in the Timo's answer before asking this question, but that still isn't very effective but worth doing anyway.

What I've done next kind of solves stuff, go to doctrine/lib/Doctrine/Migration/Builder.php line 531. There is the definition of the default class every migration will extends. Since I'm using CLI and could not find a way to pass parameters to this place I've just replaced Doctrine_Migration_Base to another class MY_Doctrine_Migration_Base which is below.

If you're not using CLI I'd say you should try to pass options and not replace source.

So the below class extends Doctrine_Migration_Base and overwrites a bunch of methods to the ones, checking whether it's OK to make changes and then calling parent method to do them. It doesn't cover all the methods currently, just the ones I've encountered when I wrote this.

Now every migration Doctrine creates extends my class which is aimed at preventing the problems I mentioned originally.

<?php

class MY_Doctrine_Migration_Base extends Doctrine_Migration_Base {
    public function __construct() {
        $this->connection = Doctrine_Manager::getInstance()->getCurrentConnection();
    }

    public function addIndex($tableName, $indexName, array $definition) {
        foreach ($this->connection->execute("SHOW INDEXES IN $tableName")->fetchAll(PDO::FETCH_ASSOC) as $index) {
            if ($index['Key_name'] === $indexName.'_idx') {
                echo "Index $indexName already exists in table $tableName. Skipping\n";
                return;
            }
        }

        parent::addIndex($tableName, $indexName, $definition);
    }

    public function removeColumn($tableName, $columnName) {
        if ($this->column_exists($tableName, $columnName)) {
            parent::removeColumn($tableName, $columnName);
        } else {
            echo "Column $columnName doesn't exist in $tableName. Can't drop\n";
        }
    }

    public function createTable($tableName, array $fields = array(), array $options = array()) {
        if ($this->connection->execute("SHOW TABLES LIKE '$tableName'")->fetchAll(PDO::FETCH_ASSOC)) {
            echo "Table $tableName already exists. Can't create\n";
        } else {
            parent::createTable($tableName, $fields, $options);
        }
    }

    public function addColumn($tableName, $columnName, $type, $length = null, array $options = array()) {
        if (! $this->column_exists($tableName, $columnName)) {
            parent::addColumn($tableName, $columnName, $type, $length, $options);
        } else {
            echo "Column $columnName already exists in $tableName. Can't add\n";
        }
    }

    private function column_exists($tableName, $columnName) {
        $exception = FALSE;

        try { //parsing information_schema sucks because security will hurt too bad if we have access to it. This lame shit is still better
            $this->connection->execute("SELECT $columnName FROM $tableName")->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $exception) {}
        //if someone knows how to check for column existence without exceptions AND WITHOUT INFORMATION SCHEMA please rewrite this stuff

        return $exception === FALSE;
    }
}

Suggestions on how to improve this are welcome.