Чтение / запись разделов с использованием Zend_Db

У меня есть приложение PHP, которое выросло в размере. База данных использовалась для одного ведущего устройства, но мы намерены изменить это с довольно стандартной репликацией master / slave для производительности и HA.

Поскольку это приложение имеет большой объем чтения, я хотел бы, чтобы чтения были делегированы подчиненным репликам и записывались в мастер.

Приложение основано на Zend Framework 1.1.10 и использует Zend_Db.

Какая была бы лучшая стратегия для этого приложения, чтобы разделить чтение и запись на БД, не переустанавливая код слишком много? (Я понимаю, что здесь, вероятно, будет задействован какой-то рефакторинг).

PS:

Я посмотрел на MySQL Proxy и кажется, что он может прозрачно разделить чтение и запись, сидя между сервером БД и приложением, но я не уверен в проблемах производительности, используя это в производственной среде. У кого-нибудь есть опыт?

Как вы сказали, MySQlProxy может быть решением, но я лично не тестировал его на производстве.

Я использую 2 Db подключения в своем коде для разделения запросов на запись и чтение. 80% обычных задач выполняется с подключением чтения. Вы можете использовать Zend_Application_Resource_Multidb, чтобы обработать это (для меня я сделал эту часть задолго до этого, и я просто храню второе Db-соединение в реестре).

Сначала ограничьте права пользователя только на операции чтения и создайте другого пользователя db с авторизацией на запись.
затем отслеживать каждый запрос на запись в вашем коде («обновление», «вставить», «удалить» – хороший старт) и попытаться сделать все эти вызовы специальным помощником.
запускать приложение и смотреть его сбой, а затем исправить проблемы 🙂

Это легче, когда вы думаете об этой проблеме в начале. Например:

У меня обычно есть фабрика Zend_Db_Table, беря параметр «читать» или «писать» и предоставляя мне Singleton из правой Zend_Db_Table (двойной синглтон, он может иметь экземпляр чтения и экземпляр записи). Тогда мне нужно только убедиться, что я использую правильный инициализированный Zend_Db_Table, когда я использую запросы / операции доступа к записи. Обратите внимание, что использование памяти значительно лучше при использовании Zend_Db_Table в качестве синглтонов.
Я пытаюсь получить все операции записи в TransactionHandler. Там я могу проверить, что я использую только объекты, связанные с правильным соединением. Транзакции затем управляются на контроллерах, я никогда не пытаюсь управлять транзакцией в слоях базы данных, все мышление начала / фиксации / отката выполняется на контроллерах (или на другом концептуальном уровне, но не на слое DAO).

Этот последний момент, транзакции, важен. Если вы хотите управлять транзакциями, важно сделать запросы READ INSIDE транзакцией с подключением WRITE . Как и все прочитанные до того, как транзакция будет считаться устаревшей, и если ваш бэкэнд базы данных блокирует блокировки, вам придется сделать запрос на чтение, чтобы получить блокировки. Если бэкэнд базы данных не выполняет неявные чтения, вам также придется выполнять блокировки строк в транзакции. И это означает, что вы не должны полагаться на ключевое слово SELECT, чтобы направить этот запрос на соединение только для чтения.

Если у вас есть хороший уровень использования слоя в вашем приложении, это изменение не очень сложно сделать. Если вы делали хаотические вещи с вашей базой данных / слоем DAO, тогда … это может быть сложнее.

h2. Zend

Я просто заплатил Zend PDO_MYSQL для разделения соединений чтения и записи. Для этого вам нужно просто указать дополнительные параметры в конфигурациях applicaiton:

'databases' => array ( 'gtf' => array( 'adapter' => 'PDO_MYSQL', 'params' => array( 'host' => 'read.com', 'host_write' => 'write-database-host.com', 'dbname' => 'database', 'username' => 'reader', 'password' => 'reader', 'username_write' => 'writer', 'password_write' => 'writer', 'charset' => 'utf8' ) ),

Здесь все запросы «SELECT …» будут использовать хост . И все остальные запросы будут использовать * host_write *. Если host_write не указан, то все запросы используют хост .

Patch:

 diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); с diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); с diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); с diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); с diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); с diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage()); не diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php index 5ed3283..d6fccd6 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Abstract.php @@ -85,6 +85,14 @@ abstract class Zend_Db_Adapter_Abstract * @var object|resource|null */ protected $_connection = null; + + + /** + * Database connection + * + * @var object|resource|null + */ + protected $_connection_write = null; /** * Specifies the case of column names retrieved in queries @@ -299,10 +307,13 @@ abstract class Zend_Db_Adapter_Abstract * * @return object|resource|null */ - public function getConnection() + public function getConnection($read_only_connection = true) { $this->_connect(); - return $this->_connection; + if (!$read_only_connection && $this->_connection_write) + return $this->_connection_write; + else + return $this->_connection; } /** diff --git a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php index d7f6d8a..ee63c59 100644 --- a/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php +++ b/Modules/Tools/Externals/Zend/Db/Adapter/Pdo/Abstract.php @@ -57,7 +57,7 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract * * @return string */ - protected function _dsn() + protected function _dsn($write_mode = false) { // baseline of DSN parts $dsn = $this->_config; @@ -65,10 +65,15 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract // don't pass the username, password, charset, persistent and driver_options in the DSN unset($dsn['username']); unset($dsn['password']); + unset($dsn['username_write']); + unset($dsn['password_write']); unset($dsn['options']); unset($dsn['charset']); unset($dsn['persistent']); unset($dsn['driver_options']); + + if ($write_mode) $dsn['host'] = $dsn['host_write']; + unset($dsn['host_write']); // use all remaining parts in the DSN foreach ($dsn as $key => $val) { @@ -91,9 +96,6 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract return; } // get the dsn first, because some adapters alter the $_pdoType $dsn = $this->_dsn(); + if ($this->_config['host_write']) + $dsn_write = $this->_dsn(true); // check for PDO extension if (!extension_loaded('pdo')) { /** @@ -120,14 +122,28 @@ abstract class Zend_Db_Adapter_Pdo_Abstract extends Zend_Db_Adapter_Abstract $this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true; } try { $this->_connection = new PDO( - $dsn, + $dsn_read, $this->_config['username'], $this->_config['password'], $this->_config['driver_options'] ); + if ($this->_config['host_write']) { + $this->_connection_write = new PDO( + $dsn_write, + $this->_config['username_write'], + $this->_config['password_write'], + $this->_config['driver_options'] + ); + } + $this->_profiler->queryEnd($q); // set the PDO connection to perform case-folding on array keys, or not diff --git a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php index 8bd9f98..4ab81bf 100644 --- a/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php +++ b/Modules/Tools/Externals/Zend/Db/Statement/Pdo.php @@ -61,8 +61,11 @@ class Zend_Db_Statement_Pdo extends Zend_Db_Statement implements IteratorAggrega */ protected function _prepare($sql) { + + $read_only_connection = preg_match("/^select/i", $sql); + try { - $this->_stmt = $this->_adapter->getConnection()->prepare($sql); + $this->_stmt = $this->_adapter->getConnection($read_only_connection)->prepare($sql); } catch (PDOException $e) { require_once 'Zend/Db/Statement/Exception.php'; throw new Zend_Db_Statement_Exception($e->getMessage());