[DBAL-20] Add Connection Resolver Created: 13/Jun/10  Updated: 01/Apr/12  Resolved: 19/Dec/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.2

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 3
Labels: None


 Description   

There should be an additional, optional "connectionResolver" which returns a driver instance for differentation between different drivers in Doctrine\DBAL\Connection instead of using $this->_conn (which stays the default case)

This is useful for master/slave, master/master or failover strategies inside the application



 Comments   
Comment by Diego Lewin [ 21/Jun/10 ]

I have done some working code, I extended \Doctrine\DBAL\Connection:

From the controllers/ service layer:

$em->getConnection()->setConnection('write');

try {
    $em->persist($user);
} catch (Exception $e) {
     echo $e->getMessage();
}


$em->getConnection()->setConnection('read_1');

...

$em->getConnection()->setConnection('read_2');

Obviously all the statements related with a unit of work will be associated with only one connection.

_________________________________________

When setting the event manager:

 $connectionOptions =
              array(
            'driver'    => $doctrineConfig['conn']['driv'],
            'user'      => $doctrineConfig['conn']['user'],
            'password'  => $doctrineConfig['conn']['pass'],
            'dbname'    => $doctrineConfig['conn']['dbname'],
            'host'      => $doctrineConfig['conn']['host'],
       
            'wrapperClass' =>'\Fishpond\Doctrine\DBAL\Connections\Multiple'
           
        );


        $connectionOptions['multiple_connections']['read'] = array(
        "driver" =>  "pdo_mysql",
        "user" =
        "password" =>
        "dbname"=>
        "host" =>
     

        );


        $connectionOptions['multiple_connections']['write'] = array(
        "driver" =>  "pdo_mysql",
        "user" => 
        "password" =>
        "dbname"=>
        "host" =>

        );


$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

_________________________________________

The new connection class itself:

namespace Fishpond\Doctrine\DBAL\Connections;


class Multiple extends \Doctrine\DBAL\Connection
{
    /**
     * An array with the different database connection used by the EntityManager.
     *
     * @var array of Doctrine\DBAL\Connection
     */
    private $_connections;

    /**
     * The index that indicate which
     * connection is used, if this index is null
     * the defaul connection is used
     *
     * @var String
     */
    private $_selectedConnection;


    /**
     * Initializes a new instance of the Connection class.
     *
     * @param array $params  The connection parameters.
     * @param Driver $driver
     * @param Configuration $config
     * @param EventManager $eventManager
     */

    public function __construct(array $params, Driver  $driver, Configuration $config = null,
       EventManager     $eventManager = null)
    {

        parent::__construct($params, $driver, $config, $eventManager);
      
        foreach ($params['multiple_connections'] as $connectionKey => $paramsMultipleConnection) {
            $this->_connections[$connectionKey] = new \Doctrine\DBAL\Connection($paramsMultipleConnection, $driver, $config, $eventManager);
        }
    }
 


    public function setConnection($selectedConnection) {
        $this->_selectedConnection = $selectedConnection;
   
        return $this;
    }


    /**
     * Establishes the connection with the database.
     *
     * @return boolean TRUE if the connection was successfully established, FALSE if
     *                 the connection is already open.
     */
    public function connect()
    {
        $status = parent::connect();
        if ($status == false) {
            return false;
        }
       
        foreach ($this->_connections as $connection) {
            $connection->connect();
        }


        return true;
    }

    /**
     * Executes an, optionally parameterized, SQL query.
     *
     * If the query is parameterized, a prepared statement is used.
     * If an SQLLogger is configured, the execution is logged.
     *
     * @param string $query The SQL query to execute.
     * @param array $params The parameters to bind to the query, if any.
     * @return Doctrine\DBAL\Driver\Statement The executed statement.
     * @internal PERF: Directly prepares a driver statement, not a wrapper.
     */
    public function executeQuery($query, array $params = array(), $types = array())
    {
    
        if ($this->_selectedConnection == null) {
            return parent::executeQuery($query, $params, $types);
        }

        return $this->_connections[$this->_selectedConnection]->executeQuery($query, $params, $types);
       
       
    }



    /**
     * Starts a transaction by suspending auto-commit mode.
     *
     * @return void
     */
    public function beginTransaction()
    {
       parent::beginTransaction();
       foreach ($this->_connections as $connection) {
            $connection->beginTransaction();
       }
    }

    /**
     * Commits the current transaction.
     *
     * @return void
     * @throws ConnectionException If the commit failed due to no active transaction or
     *                             because the transaction was marked for rollback only.
     */
    public function commit()
    {
       parent::commit();
       foreach ($this->_connections as $connection) {
            $connection->commit();
       }
    }


  /**
     * Gets the wrapped driver connection.
     *
     * @return Doctrine\DBAL\Driver\Connection
     */
    public function getWrappedConnection()
    {
        if ($this->_selectedConnection == null) {
            return parent::getWrappedConnection();
        }

        return $this->_connections[$this->_selectedConnection]->getWrappedConnection();
    }

..........
........

The class is working so far (need much more testing), but the idea is if is not selected a connection it will work as it is now. Also, an 'automatic' selection of the connection could happen in an event listener, where we could define rules about which connection should be used for different conditions/entities.

Comment by Benjamin Eberlei [ 25/Jul/10 ]

Rescheduled for Beta4

Comment by Roman S. Borschel [ 16/Aug/10 ]

We could even move this post-2.0 since this is a new feature and carries the risk of opening a set of new issues.

Comment by Lars Strojny [ 02/May/11 ]

Here is what we use in production for a few weeks now:

<?php
namespace Jarlssen\Doctrine2\DBAL;

use Doctrine\DBAL\Connection,
    Doctrine\DBAL\Driver,
    Doctrine\ORM\Configuration,
    Doctrine\Common\EventManager,
    Doctrine\DBAL\Events;

class MasterSlaveConnection extends Connection
{
    /**
     * Master connection
     *
     * @var Doctrine\DBAL\Driver\Connection
     */
    protected $_masterConn;

    /**
     * Slave connection
     *
     * @var Doctrine\DBAL\Driver\Connection
     */
    protected $_slaveConn;

    public function __construct(
        array $params,
        Driver $driver,
        Configuration $config = null,
        EventManager $eventManager = null
    )
    {
        if (!isset($params['slaves']) or !isset($params['master'])) {
            throw new \InvalidArgumentsException('master or slaves configuration missing');
        }

        $params['master']['driver'] = $params['driver'];
        foreach ($params['slaves'] as &$slave) {
            $slave['driver'] = $params['driver'];
        }

        parent::__construct($params, $driver, $config, $eventManager);
    }

    public function connect($connectionName = 'slave')
    {
        $forceMasterAsSlave = false;

        if ($this->getTransactionNestingLevel() > 0) {
            $connectionName = 'master';
            $forceMasterAsSlave = true;
        }

        $connectionProperty = '_' . $connectionName . 'Conn';
        if ($this->{$connectionProperty}) {
            if ($forceMasterAsSlave) {
                $this->_slaveConn = $this->_conn = $this->_masterConn;
            } else {
                $this->_conn = $this->{$connectionProperty};
            }
            return false;
        }

        if ($connectionName === 'master') {
            /** Set master and slave connection to master to avoid invalid reads */
            $this->_masterConn = $this->_slaveConn = $this->_conn = $this->_connectTo($connectionName);
        } else {
            $this->_slaveConn = $this->_conn = $this->_connectTo($connectionName);
        }

        if ($this->_eventManager->hasListeners(Events::postConnect)) {
            $eventArgs = new Event\ConnectionEventArgs($this);
            $this->_eventManager->dispatchEvent(Events::postConnect, $eventArgs);
        }

        return true;
    }

    protected function _connectTo($connectionName)
    {
        $params = $this->getParams();

        $driverOptions = isset($params['driverOptions']) ? $params['driverOptions'] : array();

        $connectionParams = $this->_chooseConnectionConfiguration($connectionName, $params);

        $user = isset($connectionParams['user']) ? $connectionParams['user'] : null;
        $password = isset($connectionParams['password']) ? $connectionParams['password'] : null;

        return $this->_driver->connect($connectionParams, $user, $password, $driverOptions);
    }

    protected function _chooseConnectionConfiguration($connectionName, $params)
    {
        if ($connectionName === 'master') {
            return $params['master'];
        }

        return $params['slaves'][array_rand($params['slaves'])];
    }

    public function executeUpdate($query, array $params = array(), array $types = array())
    {
        try {
            if (strpos(strtolower($query), 'delete from member_profile') === 0) {
                throw new \Exception($query);
            }

            $this->connect('master');
            return parent::executeUpdate($query, $params, $types);
        } catch(\Exception $e) {
            $errorLog = 'error_'.'log';
            $errorLog(__METHOD__);
            $errorLog($e->getMessage());
            $errorLog($e->getTraceAsString());
            return false;
        }
    }

    public function beginTransaction()
    {
        $this->connect('master');
        return parent::beginTransaction();
    }

    public function commit()
    {
        $this->connect('master');
        return parent::commit();
    }

    public function rollback()
    {
        $this->connect('master');
        return parent::rollback();
    }

    public function delete($tableName, array $identifier)
    {
        $this->connect('master');
        return parent::delete($tableName, $identifier);
    }

    public function update($tableName, array $data, array $identifier)
    {
        $this->connect('master');
        return parent::update($tableName, $data, $identifier);
    }

    public function insert($tableName, array $data)
    {
        $this->connect('master');
        return parent::insert($tableName, $data);
    }

    public function exec($statement)
    {
        $this->connect('master');
        return parent::exec($statement);
    }

    public function getWrappedConnection()
    {
        $this->connect('master');

        return $this->_conn;
    }
}
Comment by Konstantin [ 02/Oct/11 ]

Hello.

Is there are any news? When you planed to implement this feature?

Comment by Andrej [ 01/Nov/11 ]

PLEASE add master/slave support to Doctrine DBAL 2. It's very important feature!

Comment by Benjamin Eberlei [ 19/Dec/11 ]

Implemented

Comment by Benjamin Eberlei [ 19/Dec/11 ]

I would consider this experimental, i have tested this in a master slave setup, but i can't guarantee it works 100% in combination with the ORM.

See the class docblock comments of lib/doctrine/DBAL/connections/MasterSlaveConnection.php for more information on how it works.

Comment by Andrej [ 01/Apr/12 ]

Thank you, Benjamin Eberlei!

Generated at Thu Oct 30 12:39:38 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.