Problem: there are several companies (clients) in my service application. Each client should have his own database, because there is potentially a lot of data related to him. Data models (and so table schemas) are of course the same in every client’s database.
I want to store database connection configuration for each client’s DB in some table in my system’s main DB.
In Yii 1.1
ActiveRecord::getDbConnection()
was not static, so it was relatively easy to use different database connections for different model instances of the same class. You could choose which connection to use based on a value of some field in a model.
Thanks, but I’d like to use DB settings for the secondary DBs from the field in the table in my main application DB (configured in default ‘db’ component on the application), not from config files.
I’m using service location capabilities from the module instance.
Following is the code I settled for for the time being. It seems to be not the best solution because companyId must be set on the module beforehand. I’d very much appreciate a best practice advice for my problem.
Company model structure (holds additional DB configs)
[spoiler]
/**
* This is the model class for table "company".
*
* @package MGC\Core\Data\Model
*
* @property integer $id
* @property string $name
* @property string $description
* @property string $dsn The DSN for a company-specific database connection
* @property string $table_name_prefix
*/
[/spoiler]
Module
[spoiler]
class DataModule extends Module
{
const LOG_CATEGORY = 'Core.Data';
public $controllerNamespace = 'MGC\Core\Data\Controller';
/** @var int */
private $companyId;
private $companyDbConnectionMap = [];
/**
* @param CompanyModel $company
*
* @return string
*/
private function getCompanyDbConnectionName(CompanyModel $company)
{
return 'company_' . $company->name . '_db';
}
private function getDbConnectionForCompanyId($company)
{
if (isset($this->companyDbConnectionMap[$company])) {
return $this->companyDbConnectionMap[$company];
}
if (! $companyModel = CompanyModel::findOneById($company)) {
throw new NoCompanyException(
"No company found with ({$company}) while getting DB-connection for a company"
);
}
return $this->companyDbConnectionMap[$company] = $this->getDbConnectionForCompanyModel($companyModel);
}
private function getDbConnectionForCompanyModel(CompanyModel $company)
{
$id = (int) $company->id;
if (isset($this->companyDbConnectionMap[$id])) {
return $this->companyDbConnectionMap[$id];
}
/** @var CompanyModel $company */
$name = $this->getCompanyDbConnectionName($company);
if (! $this->has($name)) {
$this->set($name, [
'class' => '\yii\db\Connection',
'dsn' => $company->dsn,
'tablePrefix' => $company->table_name_prefix,
]);
}
return $this->companyDbConnectionMap[$id] = $this->get($name);
}
/**
* @param int|CompanyModel $company
*
* @return Connection
* @throws NoCompanyException If no such company is registered in the system
* @throws NoCompanyException If an argument with the wrong type is given
*/
public function getDbConnectionForCompany($company)
{
if (is_int($company)) {
return $this->getDbConnectionForCompanyId($company);
}
if ($company instanceof CompanyModel) {
return $this->getDbConnectionForCompanyModel($company);
}
throw new NoCompanyException("Wrong company given while getting DB-connection for a company");
}
/**
* @return Connection
* @throws NoCompanyException If no company ID set
*/
public function getCompanyDbConnection()
{
if (! $companyId = $this->getCompanyId()) {
throw new NoCompanyException(
'No company ID set on DataModule while getting DB-connection for a company'
);
}
return static::getDbConnectionForCompany($this->getCompanyId());
}
/**
* @param int $companyId
*/
public function setCompanyId($companyId)
{
$this->companyId = $companyId;
}
/**
* @return int
*/
public function getCompanyId()
{
return $this->companyId;
}
}
[/spoiler]
Base model class:
[spoiler]
abstract class AbstractCompanyOwnedModel extends ActiveRecord
{
public static function getDb()
{
return Yii::$app->getModule('core/data')->getCompanyDbConnection();
}
}
[/spoiler]
Usage example
[spoiler]
// ...
$module->setCompanyId($companyId);
// ...
$companyOwnedModel->a = 1;
$companyOwnedModel->b = 2;
$companyOwnedModel->save(); // gets saved to the database configured in my Company data entity DB table
