Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.2
    • Component/s: None
    • 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

        Activity

        Hide
        Andrej added a comment -

        Thank you, Benjamin Eberlei!

        Show
        Andrej added a comment - Thank you, Benjamin Eberlei!
        Hide
        Benjamin Eberlei added a comment -

        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.

        Show
        Benjamin Eberlei added a comment - 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.
        Hide
        Benjamin Eberlei added a comment -

        Implemented

        Show
        Benjamin Eberlei added a comment - Implemented
        Hide
        Andrej added a comment -

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

        Show
        Andrej added a comment - PLEASE add master/slave support to Doctrine DBAL 2. It's very important feature!
        Hide
        Konstantin added a comment -

        Hello.

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

        Show
        Konstantin added a comment - Hello. Is there are any news? When you planed to implement this feature?
        Hide
        Lars Strojny added a comment -

        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;
            }
        }
        
        Show
        Lars Strojny added a comment - 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; } }
        Hide
        Roman S. Borschel added a comment -

        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.

        Show
        Roman S. Borschel added a comment - 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.
        Hide
        Benjamin Eberlei added a comment -

        Rescheduled for Beta4

        Show
        Benjamin Eberlei added a comment - Rescheduled for Beta4
        Hide
        Diego Lewin added a comment - - edited

        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.

        Show
        Diego Lewin added a comment - - edited 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.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Benjamin Eberlei
          • Votes:
            3 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: