Search This Blog

Loading...

Thursday, April 9, 2009

How to Configure Multiple Databases for Symfony

Manipulating two or more databases in a single Symfony application proved to be quite a daunting challenge to me ( and not to mentioned that there is a possibility for a Symfony bug). So the whole purpose of this post is to help those who are facing the same problem as I do; may this post saves them from unnecessary hassle.

Here's how you can get multiple databases access to work in Symfony.

Define the following in db1.schema.yml:

db1:
  lkp_User:
    pk_User:                     { type: integer, required: true, primaryKey: true, autoIncrement: true }
    UserName:                    { type: varchar(45), required: true }
    Password:                    longvarchar
    _uniques:
      Unique:                    [ UserName ]


And define the following in db2.schema.yml
db2:
  tesco:
    Id:                  { type: integer, required: true, primaryKey: true, autoIncrement: true }
    Name:                { type: varchar(45), required: true }
    Description:         longvarchar

The schema.yml files must be of the format %dbname%.schema.yml, or else it won't work.

Now, define the following in databases.yml:
dev:
  db1:
    param:
      classname: DebugPDO
test:
  db1:
    param:
      classname: DebugPDO
all:
  db1:
    class: sfPropelDatabase
    param:
      classname: PropelPDO
      dsn: 'mysql:dbname=bpodb;host=localhost'   #where the db is located
      username: root
      password: #pass
      encoding: utf8
      persistent: true
      pooling: true


  db2:
    class: sfPropelDatabase
    param:
      classname: PropelPDO
      dsn: 'mysql:dbname=mystore2;host=localhost'   #where the db is located
      username: root
      password: #pass
      encoding: utf8
      persistent: true
      pooling: true

Note that we define two connections in databases.yml, one for db1, another for db2.

Now run the following command:

php symfony propel-build-model

And you are done!

This is how you can workaround the deficiencies in Symfony documentation and configure multiple databases.

9 comments:

LeVieux said...

Hello, nice tutorial

But it does not handle the case where you want to link entities from different databases.

So if you set a tesco_id for the entity lkp_User, propel is failing to build that. Do you have an idea how to fix it?

Thanks

Soon Hui said...

Hi LeVieux,

not sure I understand you. tesco_id and lkp_User are not related, so how to "set a tesco_id for the entity lkp_User"?

LeVieux said...

Suppose we have a database for profiling and access controls which contains "user", "group" etc

We might want to share it between multiple systems/applications.

In a blog system for example, we might want to link a comment to a user i.e link db1.comment to db2.user (user_id in comment table as a foreign key)

After some tries, I found out that this is simply impossible without hacking propel code to handle this.

Regards

Soon Hui said...

Hi,

I believe you are talking about enforcing foreign key relationship across two databases in Propel code. This, I afraid, is something I also don't know.

Did you put your question on Propel's ( or Symfony's) forum? Maybe people there can help/

LeVieux said...

Hi

Well, i dont think it is possible to set foreign keys that jump across databases even in pure SQL.

But even without foreign keys (using MyISAM for example), the fact of handling links between databases is not possible, I dived into propel's code and figured out that. Propel is not generating queries in the form "FROM [DB_NAME].[TABLE_NAME]" or "WHERE [DB_NAME].[TABLE_NAME].[COLUMN_NAME] = something".

But i think hacking the code to accomplish this wouldn't be difficult.

Soon Hui said...

I haven't look into the code generated by Propel. But I did two independent queries on the two databases, and there was no problem in getting the results out.

I didn't try to do a join operation between two databases, though.

Jon said...

Hi,

Does this technique allow the use of the propel-build-schema function?

I presume this command will be unaware that there are 2 databases being in use?

Soon Hui said...

Jon, I don't think the above technique work for schema building because if you look carefully, you need to have the schema in the first place, before you can execute the technique

Kai said...

Worth to see this symfony schema.yml reference:
http://www.symfonyreference.com/schema-yml