Multiple DB but ...only one at a time


(Kavitama) #1

I have two apps associated to two different DBs which are mainly doing the same job except for some specific features which are different.

One of the requests I received is to find a way to maintain the two DB separated but try to unify the code into only one version.

To be more specific almost 80% of the functions are doing exactly the same things (on two different DBs) and a reasonable 20% is specific to each DB.

Of course the same is true also for the model section, so almost 80% of the models (DB tables) have same columns but a 20% of them are quite different (more columns or more tables in one of the DBs).

Is there a chance I can decide to which DB I want to go after the authentication process (which should happen with a common third DB) and for this reason to access to a different list of models?

In my mind I cannot find the solution since I have the doubt I have reached the limitaion of the Model abstraction…

Any idea?


(Johnson) #2

Hi There,

You meant to have different databases for the same model?


(Kavitama) #3

In one of the scenarios, yes…
… but to be more precise I should think of accessing one DB (or another one) after the authetication has been done and that DB will be represented by a list of models that can be different.


(Henry Lamorski) #4

1.) create a component app\components\DbBalancer
2.) register the component in your app config.
3.) register the component for bootstrap

namespace app\components;

use Yii;

/**
 * add this component to your app-config to section 'bootstrap'.
 *
 * ```
 * // config.php
 * 'components' => [
 *		'dbbalancer' => \app\components\DbBalancer::class
 * ],
 * 'bootstrap' => ['dbbalancer']
 * 
 */
class DbBalancer extends \yii\base\Component
{

	public function init()
	{
		/**
		 * override the db-connection service after the user login
		 */
		\yii\base\Event::on(
			\yii\web\User::class,
			\yii\web\User::EVENT_AFTER_LOGIN,
			function ($event) {
				Yii::$app->set('db', $this->getDbConnection());
			}
		);
	}

	/**
	 * Implement your Logic for DB-Selection here
	 */
	public function getDbConnection()
	{
		$case = 1;

		if ($case === 1) {
			return Yii::createObject([
				'class' => \yii\db\Connection::class,
				'dsn' => 'dsn-string',
	 			'username' => 'username',
	 			'password' => 'password',
			]);
		} else {
			return Yii::createObject([
				'class' => \yii\db\Connection::class,
				'dsn' => 'dsn-string_2',
	 			'username' => 'username_2',
	 			'password' => 'password_2',
			]);
		}
	}
}

}


(Kavitama) #5

Quite interesting way of solving it.

What about having different models folder?
I mean I have two DBs which have some tables whichh are different (not same list of columns) but same name


(Henry Lamorski) #6

im afraid that i dont understand your usecase complete.
So i hope this example will help you.

// file app/models/Base.php

namespace app\models;

class Base extends \yii\db\ActiveRecord
{
	const EVENT_SET_SCENARIO = 'onSetScenario';
	const SCENARIO_FOR_DB1 = 'db1';
	const SCENARIO_FOR_DB2 = 'db2';

    public function setScenario($value)
    {
        parent::setScenario($value);
        $this->trigger(self::EVENT_SET_SCENARIO);
    }
}
// file app/models/Product

namespace app\models;

class Product extends Base
{
	/** 
	 * db1 contains data with differen col-names than db2.
	 * Business logic is via scenario manageable.
	 */
	public function scenarios()
    {
        return [
            self::SCENARIO_FOR_DB1 => ['title', 'price'],
            self::SCENARIO_FOR_DB2 => ['name', 'baseprice'],
        ];
    }
}
namespace app\components;

use Yii;
use app\models\Base;

/**
 * add this component to your app-config to section 'bootstrap'.
 *
 * ```
 * // config.php
 * 'components' => [
 *		'dbbalancer' => \app\components\DbBalancer::class
 * ],
 * 'bootstrap' => ['dbbalancer']
 * 
 */
class DbBalancer extends \yii\base\Component
{
	public function init()
	{
		/**
		 * set the db-connection service depending on AR-Scenario
		 */
		\yii\base\Event::on(
			Base::class,
			Base::EVENT_SET_SCENARIO,
			function ($event) {
				Yii::$app->set('db', $this->getDbConnection($event->sender->scenario));
			}
		);
	}

	/**
	 * Implement your Logic for DB-Selection here
	 */
	public function getDbConnection($scenario)
	{
		if ($scenario === Base::SCENARIO_FOR_DB1) {
			return Yii::createObject([
				'class' => \yii\db\Connection::class,
				'dsn' => 'dsn-string',
	 			'username' => 'username',
	 			'password' => 'password',
			]);
		} elseif ($scenario === Base::SCENARIO_FOR_DB2) {
			return Yii::createObject([
				'class' => \yii\db\Connection::class,
				'dsn' => 'dsn-string_2',
	 			'username' => 'username_2',
	 			'password' => 'password_2',
			]);
		} else {
			return Yii::$app->get('db');
		} 
	}
}
// use db1
$productDb1 = new \app\models\Product(['scenario' => 'db1']);
// use db2
$productDb2 = new \app\models\Product(['scenario' => 'db2']);