Doctrine migrations table collation

Question

Trying to find a way to programatically create tables with certain collation, but can't seem to find a way how to do it properly. I am using the "doctrine/doctrine-migrations-bundle": "2.1.*@dev" and Symfony 2.3,

I set up in my config.yml:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  LATIN1
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"

        entity_managers:
                    default:
                        auto_mapping: true

doctrine_migrations:
    dir_name: %kernel.root_dir%/../src/CF/EscritorioBundle/Migrations
    namespace: MyNameSpace\Migrations
    table_name: migrations
    name: Application Migrations

It creates the database with LATIN1 charset and latin1_swedish_ci as default collation, when I run doctrine:database:create tool. Then I run my migrations, and all the tables are utf8_general_ci

Looked into the $schema->createTable() function, but cannot find the way to pass the collation I need to that. What is the proper workaround here?

Answer 1

Well, thats kind of ugly, but the only way I have found is to generate after all tables another set of migrations that altered the table and converted the encoding. the migration up() and down() functions looked like:

......    
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql("ALTER TABLE MyTable CONVERT TO CHARACTER SET LATIN1 COLLATE latin1_general_ci");
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql("ALTER TABLE MyTable CONVERT TO CHARACTER SET UTF8 COLLATE utf8_general_ci");
    }

Answer 2

you can set the default_table_options connection option to achieve this: in symfony this is done through:

doctrine:
    dbal:
        default_table_options:
            charset: latin1
            collate: latin1_general_ci

for those looking to do it in plain doctrine this translates to doctrine connection option defaultDatabaseOptions, and is passed to the entity manager together with your database credentials etc:

[
    ...
    'driver' => ...
    'user' => ...
    ...
    'defaultTableOptions' => [
         'charset' => 'latin1',
         'collate' => 'latin1_general_ci'
    ]
]

Answer 3

When creating new tables, you cann add table options. So no need for a following up migration with alter sql.

Not tested if that can be done on updates to tables to.

/**
 * @param Schema $schema
 */
public function up(Schema $schema)
{
    // this up() migration is auto-generated, please modify it to your needs
    $table = $schema->createTable('new_table');
    $table->addOption('charset', 'latin1');
    $table->addOption('collate', 'latin1_general_ci');

    // ....
}