Symfony 2: multiple and dynamic database connection

Question

I am new to SF2, and I was wondering how I can manage database connections with numeric data in an ONE bundle. At the moment, I have this solution that works fine, but I don’t know if this is the right thing to do.

in myBundle \ Ressource \ config \ config.yml:

doctrine: dbal: default_connection: default connections: default: dbname: SERVER user: root password: null host: localhost client: dbname: CLIENT_134 user: root password: null host: localhost orm: default_entity_manager: default entity_managers: default: connection: default mappings: MyBundle: ~ client: connection: client mappings: MyBundle: ~ 

And then, to switch to one of the BD or the other, I do:

 $O_ressource= $this->get('doctrine')->getEntityManager('client'); $O_ressource= $this->get('doctrine')->getEntityManager('default'); 

So guys, do you think this is a good way to handle this?

And my second question:

How to configure a dynamic database connection? I mean, I have 100 databases on my system, and I cannot install them all in the config.yml file. Therefore, I would like to be able to modify the database on the fly.

Thanks for the help!

Answer 1

If you use ConnectionFactory , your event subscribers connected to the connection will stop working, such as stofDoctrineExtensions.

Here is my method. I have a ConnectionFactory with an empty connection and EntityManager. During operation, I simply replace the connection configuration with Reflections. Powered by SF 2.0.10;)

 class YourService extends ContainerAware { public function switchDatabase($dbName, $dbUser, $dbPass) { $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_manager'); // for sure (unless you like broken transactions) } } 

UPDATE

A more elegant doctrine 2.2 / sf 2.3 solution (without deviations) created for php5.4 (I like the new array initializer: D) We can use the doctrine function called the communication wrapper, see http: //docs.doctrine-project. org / projects / doctrine-dbal / en / latest / reference / portability.html

This example uses the session service to temporarily store connection information.

First we need to create a special communication wrapper:

 namespace w3des\DoctrineBundle\Connection; use Doctrine\DBAL\Connection; use Symfony\Component\HttpFoundation\Session\Session; use Doctrine\Common\EventManager; use Doctrine\DBAL\Events; use Doctrine\DBAL\Event\ConnectionEventArgs; /* * @author Dawid zulus Pakula [[e-mail]] */ class ConnectionWrapper extends Connection { const SESSION_ACTIVE_DYNAMIC_CONN = 'active_dynamic_conn'; /** * @var Session */ private $session; /** * @var bool */ private $_isConnected = false; /** * @param Session $sess */ public function setSession(Session $sess) { $this->session = $sess; } public function forceSwitch($dbName, $dbUser, $dbPassword) { if ($this->session->has(self::SESSION_ACTIVE_DYNAMIC_CONN)) { $current = $this->session->get(self::SESSION_ACTIVE_DYNAMIC_CONN); if ($current[0] === $dbName) { return; } } $this->session->set(self::SESSION_ACTIVE_DYNAMIC_CONN, [ $dbName, $dbUser, $dbPass ]); if ($this->isConnected()) { $this->close(); } } /** * {@inheritDoc} */ public function connect() { if (! $this->session->has(self::SESSION_ACTIVE_DYNAMIC_CONN)) { throw new \InvalidArgumentException('You have to inject into valid context first'); } if ($this->isConnected()) { return true; } $driverOptions = isset($params['driverOptions']) ? $params['driverOptions'] : array(); $params = $this->getParams(); $realParams = $this->session->get(self::SESSION_ACTIVE_DYNAMIC_CONN); $params['dbname'] = $realParams[0]; $params['user'] = $realParams[1]; $params['password'] = $realParams[2]; $this->_conn = $this->_driver->connect($params, $params['user'], $params['password'], $driverOptions); if ($this->_eventManager->hasListeners(Events::postConnect)) { $eventArgs = new ConnectionEventArgs($this); $this->_eventManager->dispatchEvent(Events::postConnect, $eventArgs); } $this->_isConnected = true; return true; } /** * {@inheritDoc} */ public function isConnected() { return $this->_isConnected; } /** * {@inheritDoc} */ public function close() { if ($this->isConnected()) { parent::close(); $this->_isConnected = false; } } } 

Then register it in your doctrine configuration:

 … connections: dynamic: driver: %database_driver% host: %database_host% port: %database_port% dbname: 'empty_database' charset: UTF8 wrapper_class: 'w3des\DoctrineBundle\Connection\ConnectionWrapper' 

And our ConnectionWrapper is correctly registered. Now session injection.

First create a special CompilerPass class:

 namespace w3des\DoctrineBundle\DependencyInjection\CompilerPass; use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface; use Symfony\Component\DependencyInjection\ContainerBuilder; use Symfony\Component\DependencyInjection\Definition; use Symfony\Component\DependencyInjection\Reference; class ConnectionCompilerPass implements CompilerPassInterface { /** * {@inheritDoc} */ public function process(ContainerBuilder $container) { $connection = $container ->getDefinition('doctrine.dbal.dynamic_connection') ->addMethodCall('setSession', [ new Reference('session') ]); } } 

And we write our new compiler class to the * Bundle class:

 public function build(ContainerBuilder $container) { parent::build($container); $container->addCompilerPass(new ConnectionCompilerPass()); } 

And what is all this!

A connection will be created on demand based on session properties.

To switch the database just use:

 $this->get('doctrine.dbal.dynamic_connection')->forceSwitch($dbname, $dbuser, $dbpass); 

<strong> Benefits

• More thought
• On Demand Creation
• Elegant and powerful

disadvantages

• You must manually clear your entity manager or create a special doctrine for this
• Significantly more code

Answer 2

You can look at Symfony\Bundle\DoctrineBundle\ConnectionFactory using the container service doctrine.dbal.connection_factory :

 $connectionFactory = $this->container->get('doctrine.dbal.connection_factory'); $connection = $connectionFactory->createConnection(array( 'driver' => 'pdo_mysql', 'user' => 'root', 'password' => '', 'host' => 'localhost', 'dbname' => 'foo_database', )); 

This is just a quick example, but it should get started.

Answer 3

I came across the fact that I need to have different databases with the same schema for each client. Starting with symfony 2.3, after deprecating the resetEntityManager method, I noticed that the code works well without closing the connection and not restarting the (old Entity) Manager.

this is my current working code:

 public function switchDatabase($dbName, $dbUser, $dbPass) { $connection = $this->container->get(sprintf('doctrine.dbal.%s_connection', 'dynamic_conn')); $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); }