Dynamic database connection symfony2

Question

My symfony2 project has a main database and many child databases. Each child database is created for each user, the database credentials are stored in the main database. When the user logins, the user specific database credentials are fetched from the main database and the child database connection ideally should be established. I googled for the same, and I came accross a number of solutions and finally did the following:

#config.yml

doctrine:
dbal:
    default_connection:       default
    connections:
        default:
            dbname:           maindb
            user:             root
            password:         null
            host:             localhost
        dynamic_conn:
            dbname:           ~
            user:             ~
            password:         ~
            host:             localhost
orm:
    default_entity_manager:   default
    entity_managers:
        default:
            connection:       default
            auto_mapping:     true
        dynamic_em:
            connection:       dynamic_conn
            auto_mapping:     true

I created a default connection to connect to the main database and an empty connection for the child database, similarly I created entity managers. Then I created default event listener and added the following code to the 'onKernelRequest':

public function onKernelRequest(GetResponseEvent $event) //works like preDispatch in Zend
{
    //code to get db credentials from master database and stored in varaiables
    ....
    $connection = $this->container->get(sprintf('doctrine.dbal.%s_connection', 'dynamic_conn'));
    $connection->close();

    $refConn = new \ReflectionObject($connection);
    $refParams = $refConn->getProperty('_params');
    $refParams->setAccessible('public'); //we have to change it for a moment

    $params = $refParams->getValue($connection);
    $params['dbname'] = $dbName;
    $params['user'] = $dbUser;
    $params['password'] = $dbPass;

    $refParams->setAccessible('private');
    $refParams->setValue($connection, $params);
    $this->container->get('doctrine')->resetEntityManager('dynamic_em');
    ....
}

The above code sets the child database parameters and resets the dynamic_em entity manager.

When I do the following in some controller, it works fine and the data if fetched from the child database.

$getblog= $em->getRepository('BloggerBlogBundle:Blog')->findById($id); //uses doctrine

But, when I use security context as seen in the following code, I get an error 'NO DATABASE SELECTED'.

$securityContext = $this->container->get('security.context');
$loggedinUserid = $securityContext->getToken()->getUser()->getId();

How can I set database connection dynamically and use security context as well?

UPDATE:-

After much time spent on trial and error, and googling around, I realized that security.context is set before the execution of onKernelRequest. Now the question is how to inject the database connection details into the security.context, and where to inject?

We need to get to a point where the DBAL and security context is set and security token is created, and we can manipulate database connection details.

Hence, as the person in the following link stated, I made changes to my code, as thats exactly what I would want to do. http://forum.symfony-project.org/viewtopic.php?t=37398&p=124413

That leaves me the following code add to my project:

#config.yml //remains unchanged, similar to above code

A compiler pass is created as follows:

// src/Blogger/BlogBundle/BloggerBlogBundle.php
namespace Blogger\BlogBundle;

use Symfony\Component\HttpKernel\Bundle\Bundle;
use Symfony\Component\DependencyInjection\ContainerBuilder;

use Blogger\BlogBundle\DependencyInjection\Compiler\CustomCompilerPass;

class BloggerBlogBundle extends Bundle
{
    public function build(ContainerBuilder $container)
    {
        parent::build($container);

        $container->addCompilerPass(new CustomCompilerPass());
    }
}

The compiler pass is as follows:

# src/Blogger/BlogBundle/DependencyInjection/Compiler/CustomCompilerPass.php

class CustomCompilerPassimplements CompilerPassInterface
{
    public function process(ContainerBuilder $container)
    {
        $connection_service = 'doctrine.dbal.dynamic_conn_connection';
        if ($container->hasDefinition($connection_service))
        {
            $def = $container->getDefinition($connection_service);
            $args = $def->getArguments();
            $args[0]['driverClass'] = 'Blogger\BlogBundle\UserDependentMySqlDriver';
            $args[0]['driverOptions'][] = array(new Reference('security.context'));
            $def->replaceArgument(0, $args[0]);
        }
   }
}

The driver class code is as follows:

# src/Blogger/BlogBundle/UserDependentMySqlDriver.php

use Doctrine\DBAL\Driver\PDOMySql\Driver;

class UserDependentMySqlDriver extends Driver
{    
    public function connect(array $params, $username = null, $password = null, array $driverOptions = array())
    {
        $dbname = .....  //store database name in variable
        $params['dbname'] = $dbname;
        return parent::connect($params, $username, $password, array());
    }
}

The above code were added to my project, and I assume that this is the actual work around for to my problem.

But now I get the following error:

ServiceCircularReferenceException: Circular reference detected for service "security.context", path: "profiler_listener -> profiler -> security.context -> security.authentication.manager -> fos_user.user_provider.username_email -> fos_user.user_manager -> doctrine.orm.dynamic_manager_entity_manager -> doctrine.dbal.dynamic_conn_connection".

How, can I get my code to work? I bet that I am doing something wrong here and I would appreciate any hints and help.

Answer 1

Here, you need to implement your own logic on your own, in your own business.

Take a look at documentation of Doctrine on "how to create an entity manager".

Then create a service with a clear API:

$this->get('em_factory')->getManager('name-of-my-client'); // returns an EntityManager

You can't do it with default DoctrineBundle, it's not usable for dynamic features.

class EmFactory
{
    public function getManager($name)
    {
        // you can get those values:
        // - autoguess, based on name
        // - injection through constructor
        // - other database connection
        // just create constructor and inject what you need
        $params = array('username' => $name, 'password' => $name, ....);

        // get an EM up and running
        // see http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html#obtaining-the-entitymanager

        return $em;
    }
}

And declare as a service.

Answer 2

I'd like to propose different solution to your original problem.You can use PhpFileLoader to dynamically define parameters for your config.yml.

1. Extract you main database connection parameters into separate file:

   # src/Blogger/BlogBundle/Resources/config/parameters.yml
   
   parameters:
       main_db_name:           maindb
       main_db_user:           root
       main_db_password:       null
       main_db_host:           localhost
   

2. Create new PHP script (say DynamicParametersLoader.php) which will inject new parameters in app container. I think you cannot use your symfony app in this script, but you can read main db credentials from $container variable. Like the following:

   # src/Blogger/BlogBundle/DependecyInjection/DynamicParametersLoader.php
   <?php
   
   $mainDbName = $container->getParameter('main_db_name'); 
   $mainDbUser = $container->getParameter('main_db_user');
   $mainDbPassword = $container->getParameter('main_db_password');
   $mainDbHost = $container->getParameter('main_db_host');
   
   # whatever code to query your main database for dynamic DB credentials. You cannot use your symfony2 app services here, so it ought to be plain PHP.
   ...
   
   # creating new parameters in container
   $container->setParameter('dynamic_db_name', $dbName);
   $container->setParameter('dynamic_db_user', $dbUser);
   $container->setParameter('dynamic_db_password', $dbPass);
   

3. Now you need to tell Symfony about your script and new parameters.yml file:

   # config.yml
   imports:
       - { resource: parameters.yml }
       - { resource: ../../DependencyInjection/DynamicParametersLoader.php }
   

4. At this step you can freely use injected parameters in you config:

   # config.yml
   ...
           dynamic_conn:
               dbname:           %dynamic_db_name%
               user:             %dynamic_db_user%
               password:         %dynamic_db_password%
   ...
   

Answer 3

There is a very good solution using an event listener posted here:

Symfony2, Dynamic DB Connection/Early override of Doctrine Service