Doctrine 2 Master / Slave connections

I'm looking at different ways of settings up 开发者_如何学PythonDoctrine 2 to use master/slave connections usng MySQL. The set up would be so that there is one master database with multiple slaves. All SELECT statements should come from a random live slave and any UPDATE, INSERT, DELETE statements would always be delegated to the master connection.

Has anyone set up Doctine 2 in this way or have any ideas on how to approach it?

Doctrine2 now has a MasterSlaveConnection in the \Doctrine\DBAL\Connections namespace.

EDIT: Dont read below unless this piece doesnt work

Dont need an overloader anymore, the dbal configs will take the slaves by itself. e.g.

connections:
            default:
                driver:   %database_driver%
                host:     %database_host%
                dbname:   %database_name%
                user:     %database_user%
                password: %database_password%
                slaves:
                    slave1:
                        host:     %database_slave1%
                        dbname:   %database_name%
                        user:     %database_user%
                        password: %database_password%

If the above doesnt work, try this

Something simple, just put pipes (|) between each host

default:
                driver:   %database_driver%
                host:     %database_host%|%database_slave%|%database_slave2%
                port:     3306
                dbname:   %database_name%
                user:     %database_user%
                password: %database_password%
                wrapper_class: \Foo\Bar\Symfony\Doctrine\Connections\MasterSlave


<?php
namespace Foo\Bar\Symfony\Doctrine\Connections;
use \Doctrine\DBAL\Connections\MasterSlaveConnection;
use Doctrine\DBAL\Connection,
        Doctrine\DBAL\Driver,
        Doctrine\DBAL\Configuration,
        Doctrine\Common\EventManager,
        Doctrine\DBAL\Event\ConnectionEventArgs,
        Doctrine\DBAL\Events,
        Doctrine\DBAL\Cache\QueryCacheProfile;

class MasterSlave extends MasterSlaveConnection
{
        public function __construct(array $params, Driver $driver, Configuration $config = null, EventManager $eventManager = null)
        {
                $tempParams = array(
                                'master' => array()
                                , 'slaves' => array()
                                , 'driver' => $params['driver']
                                );
                $hosts = explode('|', $params['host']);
                unset($params['host']);
                foreach($hosts as $num => $host)
                {
                        $params['host'] = $host;
                        if($num == 0)
                        {
                                $tempParams['master'] = $params;
                        }
                        else
                        {
                                $tempParams['slaves'][] = $params;
                        }
                }

                if(!isset($tempParams['master']['driver']))
                        $tempParams['master']['driver'] = "pdo_mysql";

                foreach($tempParams['slaves'] as $k => $slave)
                {
                        if(!isset($slave['driver']))
                                $tempParams['slaves'][$k]['driver'] = "pdo_mysql";
                }
                parent::__construct($tempParams, $driver, $config, $eventManager);
        }

        public function executeQuery($query, array $params = array(), $types = array(), QueryCacheProfile $qcp = null)
        {
                try
                {
                        return parent::executeQuery($query, $params, $types, $qcp);
                }
                catch(\Exception $e)
                {
                        $logger = new \Uelib\Core\Logger();
                        $message = $e->getMessage() . "\nSql: " . $query . "\nParams: \n" . print_r($params, true);
                        $logger->log($message);
                        throw $e;
                }
        }
}

As far as I know there is not built-in support for this in Doctrine 2.

It really seems as if (at least with mysql), this problem is going to be ultimately solved elsewhere. Either in mysql_proxy, or via some very recent work with mysqlnd (the mysql native driver extension for php)

Since neither of those are ready for primetime (though mysql_proxy might be), your only immediate option is, unfortunately, to start looking at the Doctrine DBAL, and extending the built-in classes to handle things smartly -- meaning it might be a good idea to detect if the current request has done any write operations, and then force any subsequent reads to use the master, avoiding any replication-delay-related issues.

Hopefully, we'll see a more formal solution from the Doctrine team in the next 6-12 months. But if you need it now, it's DIY, AFAIK.