Symfony and Doctrine: multiple connections
Well, it's my first post, I don't go into a very complicated and revolutionary concept. Let's regard that as being a breaking in! So I'll try to write a small post about my recent first experience with multiple database connections, with Symfony 1.4 and Doctrine 1.2.
At the beginning, my project was connected to a unique database, a typical configuration, the routine. Then one day, I needed to update the value of a field in an other database. I had two possibilities:
- use the odious mysql_*() function, like during the 80's (who says I exaggerate?!)
- discover the marvellous world of multiple Doctrine connections
As I had never taken a look at this point, I worried that the second option was to heavy. But, with the advices of my SQL Technologies colleagues, it turned out that it was quite easy.
First of all, I had to edit the config/databases.yml file to handle the second connection:
-
all:
-
connection1:
-
class: sfDoctrineDatabase
-
param:
-
...
-
connection2:
-
class: sfDoctrineDatabase
-
param:
-
...
Then I introspected this second database in order to generate its schema (you can do it by hand if you're courageous!):
-
symfony doctrine:build-schema
From that moment, in the config/doctrine/schema.yml file, you can see that each table is linked to a connection thanks to the parameter... "connection"!
-
MyTable1:
-
connection: connection1
-
columns:
-
...
-
MyTable2:
-
connection: connection2
-
columns:
-
...
And when you re-build the model, the magic happens: in the Base*.class.php files of the model, a new line has appeared. It allows the model class to be linked to its connection:
-
// Connection Component Binding
-
Doctrine_Manager::getInstance()->bindComponent('MyTable1', 'connection1');
That's it! Our objects can be used without caring of where the data are stored.
-
// stupid actions
-
$oObject1 = Doctrine::getTable('MyTable1')->find(7);
-
$oObject2 = Doctrine::getTable('MyTable2')->find(42);
-
$oObject2->setName($oObject1->getTitle())->save();
Long live Doctrine, long live Symfony!
September 3rd, 2010 - 08:55
You can read about Doctrine bug when using multiple db connections at
http://symfony-world.blogspot.com/2010/05/multiple-database-symfony-configuration.html.
February 21st, 2011 - 01:05
Is there any way of specifing database while creating query?
Such as Doctrine_Query::create(‘system’)->from… etc. Where system is connection name in schema?
I have two sets of connections with exactly the same tables. Different content though, therefore I have to change connection when pulling data. Any ideas?
February 21st, 2011 - 08:24
I think you should declare 2 tables in your schema for each table. I mean:
MyTable1:
connection: connection1
tableName: my_table
columns:
…
MyTable2:
connection: connection2
tableName: my_table
columns:
…
And then you just have to use the right table class.