The purpose of this topic is to engage people into discussion of a new feature or improvement.
===
Background:
Phalcon\Mvc\Model has basic support for sharding, see https://blog.phalcon.io/post/44715359754/phalcon-1-0-0-beta-released, Vertical/Horizontal Sharding Improvements
How to work with federated data / sharding is demonstrated in the blog post above.
Scenario:
Company A uses single database on their website.
Company B uses one database instance for writing and one database instance for reading.
Company C uses one master and one slave (fallback connection) for writing and one master and one slave (fallback connection) for reading.
Company D utilizes a small database cluster for saving statistical data (write only). Considering that the cluster works under heavy load some databases in it may not be available immediately and so they require their application to use “the first successful connection”.
Company E hosts their invoicing application which runs queries against a number of databases servers to fetch (federated) data. They require their application to cause even load across database servers.
Proposal:
Implement “connection pool” functionality which can:
- manage single and multiple connections
- provide connections separation by operation (read / write)
- provide different access strategies to databases
- support “default” (always available fallback) connection
- be compatible with Phalcon\Mvc\Model; and
- exist as a standalone object to be reused in other areas besides Model
API and object structure:
namespace Phalcon\Db;
class ConnectionPool
{
const STRATEGY_DEFAULT = 'default';
const STRATEGY_ROUND_ROBIN = 'round_robin';
/**
* One of ConnectionPool::STRATEGY_DEFAULT or
* ConnectionPool::STRATEGY_ROUND_ROBIN constants
*
* @var string
*/
protected $strategy = self::STRATEGY_DEFAULT;
/**
* Default connection
*
* @var null|array|Adapter
*/
protected $defaultConnection = null;
/**
* Write connections stack
*
* @var array
*/
protected $writeConnections = array();
/**
* Write connection parameters stack
*
* @var array
*/
protected $writeConnectionParameters = array();
/**
* Write connection counter (for round-robin strategy)
*
* @var integer
*/
protected $writeConnectionCounter = 0;
/**
* Read connections stack
*
* @var array
*/
protected $readConnections = array();
/**
* Read connection parameters stack
*
* @var array
*/
protected $readConnectionParameters = array();
/**
* Read connection counter (for round-robin strategy)
*
* @var integer
*/
protected $readConnectionCounter = 0;
/**
* Constructor
*/
public function __construct($defaultConnection = null, $strategy = self::STRATEGY_DEFAULT) {}
/**
* Set connection selection strategy
*
* @param string $strategy
* One of ConnectionPool::STRATEGY_DEFAULT or
* ConnectionPool::STRATEGY_ROUND_ROBIN constants
* @return ConnectionPool
* @throws ConnectionPool\InvalidArgumentException
*/
public function setStrategy($strategy) {}
/**
* Get connection selection strategy
*
* @return string
*/
public function getStrategy() {}
/**
* Set default database connection
*
* @param null|array|Adapter $connection
* Connection parameters or Adapter object
* @return ConnectionPool
* @throws ConnectionPool\InvalidArgumentException
*/
public function setDefaultConnection($connection) {}
/**
* Get default database connection
*
* @return null|Adapter $connection
* @throws ConnectionPool\RuntimeException
*/
public function getDefaultConnection() {}
/**
* Add database connection to the write connections pool
*
* @param array|Adapter $connection
* Connection parameters or Adapter object
* @return ConnectionPool
* @throws ConnectionPool\InvalidArgumentException
*/
public function addWriteConnection($connection) {}
/**
* Get write database connection
*
* @return Adapter $connection
* @throws ConnectionPool\RuntimeException
*/
public function getWriteConnection() {}
/**
* Add database connection to the read connections pool
*
* @param array|Adapter $connection
* Connection parameters or Adapter object
* @return ConnectionPool
* @throws ConnectionPool\InvalidArgumentException
*/
public function addReadConnection($connection) {}
/**
* Get read database connection
*
* @return Adapter $connection
* @throws ConnectionPool\RuntimeException
*/
public function getReadConnection() {}
/**
* Create database connection from $parameters
*
* @param array $parameters
* Connection parameters
* @return Adapter $connection
* @throws ConnectionPool\RuntimeException
*/
protected function createConnectionFromParameters(array $parameters) {}
}
namespace Phalcon\Db\ConnectionPool;
class InvalidArgumentException extends \InvalidArgumentException
{
}
class RuntimeException extends \RuntimeException
{
}```
Theory of Operation:
Phalcon\Db\ConnectionPool::setStrategy($strategy) and Phalcon\Db\ConnectionPool::getStrategy()
Allow to set new connection selection strategy and retrieve the current one. Strategy can be either:
Phalcon\Db\ConnectionPool::STRATEGY_DEFAULT - returns first successful connection; or
Phalcon\Db\ConnectionPool::STRATEGY_ROUND_ROBIN - returns connections one-by-one on each access
Phalcon\Db\ConnectionPool::setDefaultConnection($connection) and Phalcon\Db\ConnectionPool::getDefaultConnection()
Allow setting up default (always available fallback) connection and retrieve it. If multiple connections are configured then using default connection is optional.
$connection can be either array or instance of Phalcon\Db\Adapter. If connection is array then Adapter is created from it at first request.
Phalcon\Db\ConnectionPool::addWriteConnection($connection)
Adds write connection to the pool of write connections.
$connection can be either array or instance of Phalcon\Db\Adapter. If connection is array then $connection parameters are added to Phalcon\Db\ConnectionPool::$writeConnectionParameters stack and Adapter is created at first request.
This “lazy loading” implementation saves resources and establishes connections “when needed”.
Otherwise $connection is added to Phalcon\Db\ConnectionPool::$writeConnections stack.
Phalcon\Db\ConnectionPool::addReadConnection($connection)
Adds read connection to the pool of read connections.
$connection can be either array or instance of Phalcon\Db\Adapter. If connection is array then $connection parameters are added to Phalcon\Db\ConnectionPool::$readConnectionParameters stack and Adapter is created at first request.
This “lazy loading” implementation saves resources and establishes connections “when needed”.
Otherwise $connection is added to Phalcon\Db\ConnectionPool::$readConnections stack.
Phalcon\Db\ConnectionPool::getWriteConnection()
Returns database connection for writing.
If Phalcon\Db\ConnectionPool::STRATEGY_DEFAULT is set then first database connection is returned from Phalcon\Db\ConnectionPool::$writeConnections stack.
if Phalcon\Db\ConnectionPool::STRATEGY_ROUND_ROBIN is set then next created connection is returned.
Algo:
```php
$connectionId = (Phalcon\Db\ConnectionPool::$writeConnectionCounter++) % ( sizeof(Phalcon\Db\ConnectionPool::$writeConnections) + sizeof(Phalcon\Db\ConnectionPool::$writeConnectionParameters) )if $connectionId is in Phalcon\Db\ConnectionPool::$writeConnections then Phalcon\Db\ConnectionPool::$writeConnections[$connectionId] is returned.
Otherwise a new Adapter instance is created:
$parameters = array_shift(Phalcon\Db\ConnectionPool::$writeConnectionParameters)
try {
$adapter = Phalcon\Db\ConnectionPool::createConnectionFromParameters($parameters);
} catch {\Exception $exception} {
// connection parameters is invalid or database is not available.
// since multiple connections are configured, we can proceed with next connection
// try next Phalcon\Db\ConnectionPool::$writeConnectionParameters
}
Phalcon\Db\ConnectionPool::$writeConnections[] = $adapter;
return $adapter;If adapter cannot be created / retrieved from the stack (e.g., no connections configured or all connections failed or invalid) and default connection is set, then default connection is returned.
Otherwise exception should be thrown.
Phalcon\Db\ConnectionPool::getReadConnection()
Return read database connection (see Phalcon\Db\ConnectionPool::getWriteConnection() for details).
Phalcon\Db\ConnectionPool::createConnectionFromParameters(array $parameters)
Creates and returns an instance of Phalcon\Db\Adapter from array $parameters.
