Symfony: change database dynamically

问题:

let's say I have 3 databases:

• prefix_db1
• prefix_db2
• prefix_db3
  

And I want to connect to them dynamically from the url like this http://localhost/my-project/web/app_dev.php/db1/books so I know which database to conenct to from the url (in this case prefix_db1)
And basically the idea was to prepare a listener that will be fired with each http request, get the database name from the url and then override doctrin's params, something like this:
Within services.yml:

dynamic_connection:
        class: AppBundle\service\DynamicDBConnector
        arguments:  ['@request_stack']
        calls:
            - [ setDoctrineConnection, ['@doctrine.dbal.default_connection'] ]
        tags:
            - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest }

My listener:

<?php    
namespace AppBundle\service;

use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\RequestStack;
use Exception;

class DynamicDBConnector
{
    /**
     * @var Connection
     */
    private $connection;

    /*
     * @var Request
     */
    private $request;


    public function __construct(RequestStack $requestStack)
    {
        $this->request = $requestStack->getCurrentRequest();

    }

    /**
     * Sets the DB Name prefix to use when selecting the database to connect to
     *
     * @param  Connection       $connection
     * @return DynamicDBConnector $this
     */
    public function setDoctrineConnection(Connection $connection)
    {
        $this->connection = $connection;
        return $this;
    }

    public function onKernelRequest()
    {
        if ($this->request->attributes->has('_company')) {

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

            $companyName = $this->request->get('_company');
            // I did the concatenation here because in paramaters.yml I just put the prefix (database_name: prefix_) so after the concatenation I get the whole database name "prefix_db1"
            $params['dbname'] = $params['dbname'] . $companyName;

            // Set up the parameters for the parent
            $connection->__construct(
                $params,
                $connection->getDriver(),
                $connection->getConfiguration(),
                $connection->getEventManager()
            );

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

        return $this;
    }
}

Now this worked very well I have tested it using a simple list of books and each time I change the url I get the list related to each database:

http://localhost/my-project/web/app_dev.php/db1/books // I get books of database prefix_db1

http://localhost/my-project/web/app_dev.php/db2/books // I get books of database prefix_db2

Now let's get to the problem shall we :):
The problem now is that when I secure my project with authentication system and try to login (of course each database has user table) using this url http://localhost/my-project/web/app_dev.php/db1/login I get this exception :

An exception occured in driver: SQLSTATE[HY000] [1049] Base 'prefix_' unknown

As you can see symfony tried to login the user using the database_name declared in parameters.yml which means that the security_checker of symfony has been fired before my listener and before overriding Doctrine's params.

My question:
Is there any way to fire my listener before any other http request listener ? or maybe an alternative solution to make sure that any request to database must be with the right database name.
Sorry for the long post.

EDIT:
From the official documentation of symfony:
https://symfony.com/doc/2.3/cookbook/event_dispatcher/event_listener.html

The other optional tag attribute is called priority, which defaults to 0 and it controls the order in which listeners are executed (the highest the priority, the earlier a listener is executed). This is useful when you need to guarantee that one listener is executed before another. The priorities of the internal Symfony listeners usually range from -255 to 255 but your own listeners can use any positive or negative integer.

I set the priority of my listener to 10000:

tags:
    - { name: kernel.event_listener, event: kernel.request, method: onKernelRequest, priority: 10000 }

But the problem persist, still can't fire my listener before symfony!

回答1:

Great solution but if you want get the parameter _company from the URL you can retrieve the container inside the constructor through the EventManager object passed in parameters and get the current request from it, in fact the container is injected into ContainerAwareEventManager the sub class of EventManager

class DynamicDBConnector extends Connection
{
    public function __construct($params, $driver, $config, $eventManager)
    {
        if(!$this->isConnected()){
            // Create default config and event manager if none given (case in command line)
            if (!$config) {
                $config = new Configuration();
            }
            if (!$eventManager) {
                $eventManager = new EventManager();
            }

            $refEventManager = new \ReflectionObject($eventManager);
            $refContainer = $refEventManager->getProperty('container');
            $refContainer->setAccessible('public'); //We have to change it for a moment

            /*
             * @var \Symfony\Component\DependencyInjection\ContainerInterface $container
             */
            $conrainer = $refContainer->getValue($eventManager);

            /*
             * @var Symfony\Component\HttpFoundation\Request
             */
            $request = $conrainer->get('request_stack')->getCurrentRequest();

            if ($request != null && $request->attributes->has('_company')) {
                $params['dbname'] .= $request->attributes->get('_company');
            }

            $refContainer->setAccessible('private'); //We put in private again
            parent::__construct($params, $driver, $config, $eventManager);
        }
    }
}

回答2:

I found a solution
The idea is tochange the default Connection class that symfony uses to create a database connection:

doctrine:
    dbal:
        connections:
            default:
                wrapper_class: AppBundle\Doctrine\DynamicConnection
                driver: pdo_mysql
                host: '%database_host%'
                port: '%database_port%'
                dbname: '%database_name%'
                user: '%database_user%'
                password: '%database_password%'
                charset: UTF8

After that we can change the given params in the constructor:

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver;

class DynamicConnection extends Connection
{
    public function __construct(array $params, Driver $driver, $config, $eventManager)
    {
        $params['dbname'] = 'teqsdqsdqst';
        parent::__construct($params, $driver, $config, $eventManager);
    }
}

Now we just need to get the parameter from the url and set inside $params['dbname'].
In this way we make sure that symfony will always use this class to create the connection and we no longer need to fire listeners with http requestes

回答3:

you should add the database name in your config.yml like this :

 orm:
        auto_generate_proxy_classes: '%kernel.debug%'
#        naming_strategy: doctrine.orm.naming_strategy.underscore
#        auto_mapping: true
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                mappings:
                    DataMiningBundle: ~
                    AppBundle: ~
                    UserBundle: ~

            your_second_db:
                connection: your_second_db (decalared in parameters.yml)
                mappings:
                    yourBundle: ~

and call it from your controller :

 $em = $doctrine->getConnection('your_second_db');