Symfony2, Dynamic DB Connection/Early override of Doctrine Service

Question

I have a Core config Database, each row is an 'App' with some basic config etc.
Once you have chosen your app, I want to connect to a database using a property of that row (ID), and the host may also change based on the row.

What I want is to register a service that sets up the Doctrine service using these details if you are in a place on the site that it's required (which I know based on URI).

I am using the Entity manager, and various Doctrine Listeners/Event subs

I've played around with the ConnectionFactory, but this appears to cause problems with the subscribers.

What is the best way to hook something up that will transparently modify the Doctrine service, so that the controllers can act without any knowledge of which DB host and DB name they are connecting to?

Each DB of this type will have the same structure so all Entity mapping is correct.

I'm looking for a really clean implementation, hopefully using the Service Container to avoid any 'hacks'.

Does anyone have any knowledge of doing this?

Answer 1

Combined, these two postings helped me solve my own very similar problem. Here is my solution, maybe it is useful for someone else:

<?php

namespace Calitarus\CollaborationBundle\EventListener;

use Symfony\Component\HttpFoundation\Request;
use Doctrine\DBAL\Connection;
use Exception;
use Monolog\Logger;



class DatabaseSwitcherEventListener {

    private $request;
    private $connection;
    private $logger;

    public function __construct(Request $request, Connection $connection, Logger $logger) {
        $this->request = $request;
        $this->connection = $connection;
        $this->logger = $logger;
    }


    public function onKernelRequest() {
        if ($this->request->attributes->has('_site')) {
            $site = $this->request->attributes->get('_site');

            $connection = $this->connection;
            $params     = $this->connection->getParams();

            $db_name = 'br_'.$this->request->attributes->get('_site');
            // TODO: validate that this site exists
            if ($db_name != $params['dbname']) {
                $this->logger->debug('switching connection from '.$params['dbname'].' to '.$db_name);
                $params['dbname'] = $db_name;
                if ($connection->isConnected()) {
                    $connection->close();
                }
                $connection->__construct(
                    $params, $connection->getDriver(), $connection->getConfiguration(),
                    $connection->getEventManager()
                );

                try {
                    $connection->connect();
                } catch (Exception $e) {
                    // log and handle exception
                }
            }
        }
    }
}

To get this to work, I set up services.yml as follows:

services:
    cc.database_switcher:
        class:      Calitarus\CollaborationBundle\EventListener\DatabaseSwitcherEventListener
        arguments:  [@request, @doctrine.dbal.default_connection, @logger]
        scope:      request
        tags:
            - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest }

and I have this routing configuration to get the _site parameter, which in my case is part of the URL, but you can probably get it in other ways depending on your setup:

resource: "@CCollabBundle/Controller"
type:     annotation
prefix:   /{_site}
defaults:
 _site: default