Migrate multiple same databases in propel

Question

I have a multilingual application with each language data in a separate database. This is how it’s configured in Symfony 2.2 and Propel 1.6 (excerpt from config key propel.dbal.connections):

pl_general: &GENERAL
    <<: *BASEDB
    dsn:        mysql:host=%db_host%;dbname=%db_general%_pl%db_suffix%
general:
    <<: *GENERAL
ar_general:
    <<: *BASEDB
    dsn:        mysql:host=%db_host%;dbname=%db_general%_ar%db_suffix%
at_general:
    <<: *BASEDB
    dsn:        mysql:host=%db_host%;dbname=%db_general%_at%db_suffix%
bg_general:
    <<: *BASEDB
    dsn:        mysql:host=%db_host%;dbname=%db_general%_bg%db_suffix%

And so on. There is one general connection that points to my default language. All other databases have the exact same structure as my default. I have a general-schema.xml with all models configured.

The problem begins when I try to generate a diff (propel:migration:generate-diff) or execute a migration (propel:migration:migrate). Propel does not know that those connections are siblings, and anything relating to the general connection should also be executed on every LANG_general connection. Also, when doing a diff, only the default db is compared against the schema/models, so I cannot guarantee the integrity.

I would like to:

make sure that all of the databases have the same structure
add all new changes to a group of connections at once and without much hassle

I’m looking for a by-the-book solution, preferably using Propel own mechanisms, but I am open to all suggestions. Whatever works and is stable. Opinions, comments and proven solutions very welcome! Source code appreciated

Would it perhaps be easier to have all of your language tables in the same database? — halfer
I think it would get worse. Generated migration would have Alter table user -- no way I could easily apply this to user_en, user_fr, user_es, etc. — Maciej Łebkowski

Maciej Łebkowski Maciej Łebkowski · Accepted Answer · 2013-10-22T15:34:11

What have I figured so far:

One can wrap the generate-diff command in their own and alter the migration class to copy all the SQL statements across all of my connections, i.e. turn this:
```
return [
  'general' => '/** STATEMENTS **/'
];
```
Into this:
```
return [
  'pl_general' => '/** STATEMENTS **/',
  'at_general' => '/** STATEMENTS **/',
  'ar_general' => '/** STATEMENTS **/',
  'bg_general' => '/** STATEMENTS **/',
];
```
This is a bit of hacking and is only good as long as the migration classes format does not change.
One can apply all the migrations in a loop for every connection, changing the name each time, i.e. bind LANG_general as general. I’m not sure about the consequences.
One could alter the schema/process somehow, so that any LANG_general would match to general schema — for example by copying all the schema files before the process. The diff and migrate commands would operate on each connections separately, but with the same results. This would be also more time consuming.

Migrate multiple same databases in propel

2 Answers