Symfony2: Doctrine Migrations and ACL tables

Edit: This is an issue with Symfony 2.0.x and looks to have been resolved for 2.1, please see Denderello's comment below for more details

If you use Doctrine migrations in a Symfony2 app then one difficulty you may run into is with database tables that do not relate to your entities. The doctine:migration:diff command will make sure that the current database schema is updated to match the schema generated from the entities in an app. Whilst this is an excellent way of managing database versions across multiple instances of the app, I have run into a problem with this. If you are also using the ACL feature of Symfony2 security then this uses database tables that do not have corresponding entities. This means that whenever you generate diffs they will include the SQL to remove the ACL tables.

I have tried various solutions to this. The easiest would be if there was a way to specify that certain tables should be ignored when running the diff command. As far as I can see there is no way to do this at the time of writing, someone please correct me if I am wrong.

I tried creating entities for the ACL tables by creating these using the init:acl command in a fresh version of the database and the following the procedure to generate entities for the tables. Unfortunately there were some differences between then entities and the tables so that running doctine:migration:diff still created SQL relating to these tables. It may not have been significant but I do not know enough about how the ACL part of the security component works to want to run these. Additionally it felt like a very hacky workaround anyway.

The successful attempt was just to move these tables to a different database which turned out to be a pretty simple procedure. So here is how to move the ACL tables to a different database:

You need to set up an additional connection in config.yml with the details of the second database:

# Doctrine Configuration
doctrine:
   dbal:
       default_connection: default
       connections:
             default:
                   driver:   %database_driver%
                   host:     %database_host%
                   port:     %database_port%
                   dbname:   %database_name%
                   user:     %database_user%
                   password: %database_password%
                   charset:  UTF8
             acl:
                   driver:   %database_driver%
                   host:     %database_host%
                   port:     %database_port%
                   dbname:   %acl_database_name%
                   user:     %database_user%
                   password: %database_password%
                   charset:  UTF8

The only differences between the connections is the database name in this case. The new database name parameter acl_database_name needs adding to parameters.ini. You also need to specify the new connection is to be used for ACL in security.yml:

security:
    acl:
        connection: acl

Then setting up the ACL tables with the usual command will add them to the new database:

php app/console init:acl

Now if you run the doctine:migration:diff command it will just look in the default connection's database and ignore the other connection's database.

This all assumes you are still at a development stage where you can just start over with a fresh database. If not then you will need to actually move the existing tables to the new database.