Symfony1.4 multiple database with Doctrine , how to use, how to read and write , tricks, techniques

This is a quite complete and detailed guide of how to use multiple database in Symfony1.4 with doctrine. Including same model different database, cross database foreign key.

Assume we have db master, child-1, child-2 and child-3, four databases and they all have same schema.

Easiest way is:

database.yml


all:

  child_a:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox_child_a.db'
      username: root
      password: null

  child_b:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox_child_b.db'
      username: root
      password: null


  child_c:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox_child_c.db'
      username: root
      password: null
  master:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox.db'
      username: root
      password: null


in action:


          $doctrine_manager = Doctrine_Manager::getInstance();
          $doctrine_manager->setCurrentConnection('child_a');
          $oCurrentConnection = Doctrine_Manager::getInstance()->getCurrentConnection();

now you can select from database child_a.

beware:

to save to the correct db, you have to specify the db in save() method

$model = $form->save($oCurrentConnection);

To copy current model:

$model = [retrieved from master-database];
$slaveConnection = Doctrine_Manager::getInstance()
  ->getConnection('slave-connection');
$model_copy = $model->copy(true); # deep copy
$model_copy->save($slaveConnection);

To set cross db foreign key, you have to manually do:

(with child connection)

$model->setId($id);

Advanced multiple database, what if you have many child databases or you don't want to write the connection parameters to database.yml every time?

The solution is:

database.yml

all:

  child:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox_child.db'
      username: root
      password: null
  master:
    class: sfDoctrineDatabase
    param:
      dsn: 'sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox.db'
      username: root
      password: null

in you action:

  public function preExecute()

  {

          $dsn = "sqlite:/Users/abc/Sites/sf_sandbox/data/sandbox_child_x.db";

          $doctrineManager = Doctrine_Manager::getInstance();

          $doctrineManager->getConnection('child')

                          ->setOption('dsn', $dsn);

          $doctrineManager->getConnection('child')

                          ->setOption('username', 'root');

          $doctrineManager->getConnection('child')

                          ->setOption('password', '');

          $doctrineManager->setCurrentConnection('child');

          $this->oCurrentConnection = Doctrine_Manager::getInstance()->getCurrentConnection();

  }

you can save the dsn, username and password in your db for each connection, and connect to unlimited databases.

IMPORTANT:

1, better keep master db config at last one in database.yml because most versions of Symfony1.4 uses the last connection as default connection.

2, each connection in database.yml must have it's parameters in setup although you can change the parameters in on the fly in your action.

3, model connection will be defined when you do doctrine:build --all as

/Users/abc/Sites/sf_sandbox/lib/model/doctrine/base/Basesf_guard_user_profile.class.php

Doctrine_Manager::getInstance()->bindComponent('sf_guard_user_profile', 'child_a');

You can specify the model database connection in schema as:

connection: master

otherwise, default connection will be used, and most likely it will be the last connection defined in your database.yml

That's all, any question please leave a message.