Dynamic configuration of database connection in Yii 2 Starter Kit, the configuration attribute comes from the multi-tenant system
1. The multi-tenant system contains multiple tenants, and each tenant has its own database configuration information. The existing requirement is to connect the database based on the response host name, user name and password, as shown in Figure 1.
Figure 1
2. Now it is configured as an application component, \common\config\base.php, as shown in Figure 2
Figure 2
3. Open dubug, and then check the log messages. The properties of the db component need to be initialized before yii\db\connection::open (the interface of the multi-tenant system is called), as shown in Figure 3
Figure 3
4. Edit \common\config\base.php, configure the class of the db component: common\components\db\connection, comment out all env variables
'db'=>[
'class'=>'common\components\db\Connection',
// 'dsn' => env('DB_DSN'),
// 'username' => env('DB_USERNAME'),
// 'password' => env('DB_PASSWORD'),
// 'tablePrefix' => env('DB_TABLE_PREFIX'),
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
],
5. Create a new \common\components\db\connection.php, inherit to \yii\db\connection
* @since 1.0
*/
class Connection extends \yii\db\Connection
{
public function __construct($config = [])
{
// ... 配置生效前的初始化过程
$tenantConfig = [
'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=cmcp-api',
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
'tablePrefix' => env('DB_TABLE_PREFIX'),
];
if (!empty($config)) {
$config = ArrayHelper::merge($config, $tenantConfig);
}
parent::__construct($config);
}
}
6. Repeat the third step, the properties of the db component have been initialized before yii\db\connection::open (host=localhost change to host=127.0.0.1), as shown in Figure 4
Figure 4
7. Now it is necessary to call the interface of the multi-tenant system to reassign the value in response to its response $tenantConfig (recommended: this scheme is only applicable to the case of one tenant. If there are multiple tenants, it is recommended that each tenant correspond to a different connection component, and the name of the connection component is based on the tenant ID, not the corresponding db connection component), as shown in Figure 5
Figure 5
8. Now all modifications are restored, and it is ready to use multiple databases at the same time (each tenant corresponds to different connection components, the name of the connection component is based on the tenant ID), refer to the website:https://github.com/yiichina/yii2/blob/master/docs/guide-zh-CN/db-active-record.md, as shown in Figure 6
Figure 6
9. Create a new \common\components\db\activerecord.php, inherit to \yii\db\activerecord, and rewrite[[yii\db\ActiveRecord::getDb()|getDb()]] method
* @since 1.0
*/
class ActiveRecord extends \yii\db\ActiveRecord
{
/**
* Returns the database connection used by this AR class.
* By default, the "db" application component is used as the database connection.
* You may override this method if you want to use a different database connection.
* @return Connection the database connection used by this AR class.
*/
public static function getDb()
{
$tenantDb = new \yii\db\Connection([
'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=cmcp-api',
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
'tablePrefix' => env('DB_TABLE_PREFIX'),
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
]);
return $tenantDb;
// return Yii::$app->getDb();
}
}
10. Edit \Common\Models\KeyStorageItem.php, inherit to Common\Components\DB\ActiveRecord
11. Edit \Common\Config\Base.php, comment out the db component, and it is recommended to move the db component to the development environment later to facilitate the use of GII
/*
'db'=>[
'class'=>'yii\db\Connection',
'dsn' => env('DB_DSN'),
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
'tablePrefix' => env('DB_TABLE_PREFIX'),
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
],
*/
12. Open:http://frontend.cmcp-api.localhost/, error: failed to instantiate component or class “db”. The reason is that the log target is the database table, as shown in Figure 7
Figure 7
13. View the log component configuration, \common\config\base.php
'log' => [
'traceLevel' => YII_DEBUG ? 3 : 0,
'targets' => [
'db'=>[
'class' => 'yii\log\DbTarget',
'levels' => ['error', 'warning'],
'except'=>['yii\web\HttpException:*', 'yii\i18n\I18N\*'],
'prefix'=>function () {
$url = !Yii::$app->request->isConsoleRequest ? Yii::$app->request->getUrl() : null;
return sprintf('[%s][%s]', Yii::$app->id, $url);
},
'logVars'=>[],
'logTable'=>'{{%system_log}}'
]
],
],
14. The log component must be loaded during the boot, so if the database connection is dynamically configured, it is already later than the boot stage, edit the log component configuration, \common\config\base.php, save the log message to the file
'log' => [
'traceLevel' => YII_DEBUG ? 3 : 0,
'targets' => [
'file'=>[
'class' => 'yii\log\FileTarget',
'levels' => ['error', 'warning'],
'except' => ['yii\web\HttpException:*', 'yii\i18n\I18N\*'],
'prefix' => function () {
$url = !Yii::$app->request->isConsoleRequest ? Yii::$app->request->getUrl() : null;
return sprintf('[%s][%s]', Yii::$app->id, $url);
},
'logVars'=>[],
]
],
],
15. Open:http://frontend.cmcp-api.localhost/, error: unknown component id: db, as shown in Figure 8
8
16. Replace all model files under \common\models, use yii\db\ActiveCord;
use yii\db\ActiveRecord; 替换为 use common\components\db\ActiveRecord;
\yii\db\ActiveRecord 替换为 \common\components\db\ActiveRecord
Figure 9
17. Open:http://frontend.cmcp-api.localhost/, error: Failed to instantiate component or class “db”. The reason is that the RBAC component uses the database table to store data, as shown in Figure 10
Figure 10
18. Edit \common\config\base.php, comment out the rbac component, because the default value of its db attribute is db, it needs to be dynamically adjusted to the database connection component corresponding to the tenant ID
/*
'authManager' => [
'class' => 'yii\rbac\DbManager',
'itemTable' => '{{%rbac_auth_item}}',
'itemChildTable' => '{{%rbac_auth_item_child}}',
'assignmentTable' => '{{%rbac_auth_assignment}}',
'ruleTable' => '{{%rbac_auth_rule}}'
],
*/
19. Edit \Common\Components\DB\ActiveRecord.php, through[[yii\di\ServiceLocator::setComponents()]] Method Registration Database Connection Components, RBAC Components
* @since 1.0
*/
class ActiveRecord extends \yii\db\ActiveRecord
{
/**
* Returns the database connection used by this AR class.
* By default, the "db" application component is used as the database connection.
* You may override this method if you want to use a different database connection.
* @return Connection the database connection used by this AR class.
*/
public static function getDb()
{
$tenantDb = 'defaultDb';
// 注册数据库连接组件、RBAC组件
Yii::$app->setComponents([
$tenantDb => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=127.0.0.1;port=3306;dbname=cmcp-api',
'username' => env('DB_USERNAME'),
'password' => env('DB_PASSWORD'),
'tablePrefix' => env('DB_TABLE_PREFIX'),
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
],
'authManager' => [
'class' => 'yii\rbac\DbManager',
'db' => $tenantDb,
'itemTable' => '{{%rbac_auth_item}}',
'itemChildTable' => '{{%rbac_auth_item_child}}',
'assignmentTable' => '{{%rbac_auth_assignment}}',
'ruleTable' => '{{%rbac_auth_rule}}'
],
]);
return Yii::$app->$tenantDb;
}
}
20. Repeat the third step, the properties of the DB component have been initialized definition before yii\db\connection::open (host=localhost change to host=127.0.0.1), as shown in Figure 11
Figure 11
21. In Postman, gethttp://www.cmcp-api.localhost/v1/pages, 200 responses, as shown in Figure 12
Figure 12
22. Open the URL:http://backend.cmcp-api.localhost/, error: unknown component id: db, as shown in Figure 13
Figure 13
23. Edit \backend\models\systemlog.php, inherit to \common\components\db\activecord, as shown in Figure 14
\yii\db\ActiveRecord 替换为 \common\components\db\ActiveRecord
Figure 14
24. Open the URL:http://backend.cmcp-api.localhost/, 200 responses
25. Now the interface of the multi-tenant system needs to be called, and the HTTP client extension based on the installed Yii 2 is based on the direct initialization of the response, as shown in Figure 15
Figure 15
26. Edit \.env.dist, \.env, add the relevant configuration of multiple tenants
# 多租户
# ----
TENANT_HOST_INFO = http://wjdev.chinamcloud.com:8600 # HOME URL
TENANT_BASE_URL = /interface # BASE URL
TENANT_APP_NAME = cmcpapi # 模块英文名称
TENANT_SECRET = 94030d307b160f04b88592cb9bebdd4c # 模块Secret
27. Edit \Common\Config\Bootstrap.php, set the alias
Yii::setAlias('@tenantUrl', env('TENANT_HOST_INFO') . env('TENANT_BASE_URL'));
28. Configure the client through the application component, edit \common\config\web.php, as shown in Figure 16
'tenantHttp' => [
'class' => 'yii\httpclient\Client',
'baseUrl' => Yii::getAlias('@tenantUrl'),
'transport' => 'yii\httpclient\CurlTransport'
],
Figure 16
29. Record the request sent by HTTP through the configuration log and analyze its implementation, edit \common\config\base.php, as shown in Figure 17
'httpRequest'=>[
'class' => 'yii\log\FileTarget',
'logFile' => '@runtime/logs/http-request.log',
'categories' => ['yii\httpclient\*'],
]
Figure 17
30. Yii2 HTTP client extension provides a debug panel that can be integrated with the Yii debug module, and displays the executed HTTP requests, and enables the debug panel, as shown in Figure 18
$config['modules']['debug'] = [
'class' => 'yii\debug\Module',
'allowedIPs' => ['127.0.0.1', '::1', '192.168.33.1', '172.17.42.1', '172.17.0.1', '192.168.99.1'],
'panels' => [
'httpclient' => [
'class' => 'yii\httpclient\debug\HttpClientPanel',
],
],
];
18
31. After refreshing the page, view the log panel and add an HTTP client, as shown in Figure 19
Figure 19
32. New \common\logics\http\tenant\env.php, the http directory represents the mod in this directory The type data is derived from HTTP requests, the tenant directory represents the related models of the multi-tenant system, and env.php represents the environment configuration model
* @since 1.0
*/
class Env extends Model
{
public $app_name;
public $secret;
public $tenant_id;
public function attributeLabels()
{
return [
'app_name' => \Yii::t('model/http/tenant/env', 'App Name'),
'secret' => \Yii::t('model/http/tenant/env', 'Secret'),
'tenant_id' => \Yii::t('model/http/tenant/env', 'Tenant ID'),
];
}
/**
* 返回租户模块环境配置信息
*
* @return array|false
*
* 格式如下：
*
* 租户模块环境配置信息
* [
* 'message' => '', //说明
* 'data' => [], //数据
* ]
*
* 失败(将错误保存在 [[yii\base\Model::errors]] 属性中)
* false
*
* @throws ServerErrorHttpException 如果响应状态码不等于20x
*/
public function getTenantEnv()
{
$this->app_name = env('TENANT_APP_NAME');
$this->secret = env('TENANT_SECRET');
/* 租户ID后续从请求参数中获取 */
$this->tenant_id = 'default';
$response = Yii::$app->tenantHttp->createRequest()
->setMethod('get')
->setUrl('getTenantEnvs')
->setData([
'appname' => $this->app_name,
'secret' => $this->secret,
'tenantid1' => $this->tenant_id,
])
->send();
// 检查响应状态码是否等于20x
if ($response->isOk) {
// 检查业务逻辑是否成功
if ($response->data['returnCode'] === 0) {
return ['message' => $response->data['returnDesc'], 'data' => $response->data['returnData']];
} else {
$this->addError('tenant_id', $response->data['returnDesc']);
return false;
}
} else {
throw new ServerErrorHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '20005'), ['statusCode' => $response->getStatusCode()])));
}
}
}
33. Create a new language pack file, \common\messages\en\model\http\tenant\env. php, \common\messages\zh\model\http\tenant\env.php, as shown in Figure 20
\common\messages\en\model\http\tenant\env.php
return [
'App Name' => 'English name of the module',
'Secret' => 'Secret module in multi-tenant system',
'Tenant ID' => 'Tenant ID',
];
\common\messages\zh\model\http\tenant\env.php
return [
'App Name' => '模块英文名称',
'Secret' => '模块Secret',
'Tenant ID' => '租户ID',
];
Figure 20
34. Edit the language pack file, \api\messages\en\error.php, \api\messages\zh\error.php
\API\Messages\en\Error.php
20005 => 'Multitenant HTTP request failed with status code: {statusCode}',
20006 => 'Multitenant HTTP request failed: {firstErrors}',
\API\Messages\zh\Error.php
20005 => '多租户HTTP请求失败，状态码：{statusCode}',
20006 => '多租户HTTP请求失败：{firstErrors}',
35. Copy \API\messages\en\app.php, \api\messages\en\error.php, \api\messages\zh\app.php, \api\messages\zh\error.php arrive \common\messages\en\app.php, \common\messages\en\error.php, \ common\messages\zh\app.php, \common\messages\zh\error.php, edit
\common\messages\zh\Error.php
return [
20000 => 'error',
20005 => '多租户HTTP请求失败，状态码：{statusCode}',
20006 => '多租户HTTP请求失败：{firstErrors}',
];
36. Edit \Common\Components\DB\ActiveRecord.php, the database configuration information comes from the tenant environment configuration interface
* @since 1.0
*/
class ActiveRecord extends \yii\db\ActiveRecord
{
/**
* Returns the database connection used by this AR class.
* By default, the "db" application component is used as the database connection.
* You may override this method if you want to use a different database connection.
* @return Connection the database connection used by this AR class.
*/
public static function getDb()
{
$env = new Env();
$tenantEnv = $env->getTenantEnv();
if ($tenantEnv === false) {
if ($env->hasErrors()) {
foreach ($env->getFirstErrors() as $message) {
$firstErrors = $message;
}
throw new ServerErrorHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '20006'), ['firstErrors' => $firstErrors])));
} elseif (!$env->hasErrors()) {
throw new ServerErrorHttpException('Multi-tenant HTTP requests fail for unknown reasons.');
}
}
$tenantDb = $tenantEnv['data']['tenantid'] . 'Db';
// 注册数据库连接组件、RBAC组件
Yii::$app->setComponents([
$tenantDb => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=' .$tenantEnv['data']['db_info']['host'] . ';port=3306;dbname=' . $tenantEnv['data']['db_info']['database'] . '',
'username' => $tenantEnv['data']['db_info']['login'],
'password' => $tenantEnv['data']['db_info']['password'],
'tablePrefix' => $tenantEnv['data']['db_info']['prefix'],
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
],
'authManager' => [
'class' => 'yii\rbac\DbManager',
'db' => $tenantDb,
'itemTable' => '{{%rbac_auth_item}}',
'itemChildTable' => '{{%rbac_auth_item_child}}',
'assignmentTable' => '{{%rbac_auth_assignment}}',
'ruleTable' => '{{%rbac_auth_rule}}'
],
]);
return Yii::$app->$tenantDb;
}
}
37. Get in Postmanhttp://www.cmcp-api.localhost/v1/pages, 200 responses, as shown in Figure 21
Figure 21
38. In Postman, the environment configuration interface of get multi-tenant, one parameter is wrong, as shown in Figure 22
Figure 22
39. Edit \.env, the value of tenant_app_name is wrong
TENANT_APP_NAME = cmcpapi1 # 模块英文名称
40. Get in Postmanhttp://www.cmcp-api.localhost/v1/pages, 500 responses, as shown in Figure 23
Figure 23
41. Check the log and find that the number of multi-tenant interfaces requested by the interface application and the background application is 8 or 139 times, and the background error is reported: Unable to send log via yii\log\filetarget: exception (Database Exception)yii\db\exceptionwith messagesqlstate[HY000][1040]Too many connections, as shown in Figure 24, 25
Figure 24
![后台报错：Unable to send log via yii\log\FileTarget: Exception (Database Exception) 'yii\db\Exception' with message 'SQLSTATE[HY000] [1040] Too many connections'](https://www.shuijingwanwq.com/wp-content/uploads/2018/01/25-1.png)
Figure 25
42. Check whether the database connection component and RBAC component are registered, if it has been registered, there is no need to overwrite, edit \common\components\db\activerecord.php, as shown in Figure 26
// 检查数据库连接组件、RBAC组件是否被注册
if (!(Yii::$app->has($tenantDb) && Yii::$app->has('authManager'))) {
// 注册数据库连接组件、RBAC组件
Yii::$app->setComponents([
$tenantDb => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=' . $tenantEnv['data']['db_info']['host'] . ';port=3306;dbname=' . $tenantEnv['data']['db_info']['database'] . '',
'username' => $tenantEnv['data']['db_info']['login'],
'password' => $tenantEnv['data']['db_info']['password'],
'tablePrefix' => $tenantEnv['data']['db_info']['prefix'],
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
],
'authManager' => [
'class' => 'yii\rbac\DbManager',
'db' => $tenantDb,
'itemTable' => '{{%rbac_auth_item}}',
'itemChildTable' => '{{%rbac_auth_item_child}}',
'assignmentTable' => '{{%rbac_auth_assignment}}',
'ruleTable' => '{{%rbac_auth_rule}}'
],
]);
}
Figure 26
43. The background application reports an error:sqlstate[HY000][1040]Too many connectionshas been solved, as shown in Figure 27
![后台应用报错：'SQLSTATE[HY000] [1040] Too many connections' 已经解决](https://www.shuijingwanwq.com/wp-content/uploads/2018/01/27-1.png)
Figure 27
Figure 28
45. Enable the Schema cache (only in the production environment), and cache to Redis, edit \.env.dist, \.env, as shown in Figure 29
YII_ENV = prod
# Redis
# ----
REDIS_HOSTNAME = localhost # 主机名/IP地址
REDIS_PORT = 6379 # 端口
#REDIS_PASSWORD = # 密码
REDIS_DATABASE = 0 # 数据库
# Redis cache
# ----
REDIS_CACHE_KEY_PREFIX = ca: # 唯一键前缀
Figure 29
46. Edit \common\config\base.php, configure Redis connection in the application configuration, and configure the cache component
'redisCache' => [
'class' => 'yii\redis\Cache',
'keyPrefix' => env('REDIS_CACHE_KEY_PREFIX'), // 唯一键前缀
],
'redis' => [
'class' => 'yii\redis\Connection',
'hostname' => env('REDIS_HOSTNAME'),
'port' => env('REDIS_PORT'),
'password' => env('REDIS_PASSWORD'),
'database' => env('REDIS_DATABASE'),
],
47. Enable Schema cache in the database connection, edit \common\components\db\activerecord.php
// 检查数据库连接组件、RBAC组件是否被注册
if (!(Yii::$app->has($tenantDb) && Yii::$app->has('authManager'))) {
// 注册数据库连接组件、RBAC组件
Yii::$app->setComponents([
$tenantDb => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=' . $tenantEnv['data']['db_info']['host'] . ';port=3306;dbname=' . $tenantEnv['data']['db_info']['database'] . '',
'username' => $tenantEnv['data']['db_info']['login'],
'password' => $tenantEnv['data']['db_info']['password'],
'tablePrefix' => $tenantEnv['data']['db_info']['prefix'],
'charset' => 'utf8',
'enableSchemaCache' => YII_ENV_PROD,
'schemaCache' => 'redisCache',
],
'authManager' => [
'class' => 'yii\rbac\DbManager',
'db' => $tenantDb,
'itemTable' => '{{%rbac_auth_item}}',
'itemChildTable' => '{{%rbac_auth_item_child}}',
'assignmentTable' => '{{%rbac_auth_assignment}}',
'ruleTable' => '{{%rbac_auth_rule}}'
],
]);
}
48. After running the background application, check the data in Redis, the structure of the data table has been cached, as shown in Figure 30
Figure 30
49. Realize the cache (response of the multi-tenant system), edit \common\logics\http\tenant\env.php, after running the background application, check redis The data in the multi-tenant system has been cached, as shown in Figure 31
* @since 1.0
*/
class Env extends Model
{
public $app_name;
public $secret;
public $tenant_id;
public function attributeLabels()
{
return [
'app_name' => \Yii::t('model/http/tenant/env', 'App Name'),
'secret' => \Yii::t('model/http/tenant/env', 'Secret'),
'tenant_id' => \Yii::t('model/http/tenant/env', 'Tenant ID'),
];
}
/**
* 返回租户模块环境配置信息
*
* @return array|false
*
* 格式如下：
*
* 租户模块环境配置信息
* [
* 'message' => '', //说明
* 'data' => [], //数据
* ]
*
* 失败(将错误保存在 [[yii\base\Model::errors]] 属性中)
* false
*
* @throws ServerErrorHttpException 如果响应状态码不等于20x
*/
public function getTenantEnv()
{
/* 租户ID后续从请求参数中获取 */
$this->tenant_id = 'default';
// 设置多租户数据的缓存键
$redisCache = Yii::$app->redisCache;
$tenantKey = 'tenant:' . $this->tenant_id;
// 从缓存中取回多租户数据
$tenantData = $redisCache[$tenantKey];
if ($tenantData === false) {
$this->app_name = env('TENANT_APP_NAME');
$this->secret = env('TENANT_SECRET');
$response = Yii::$app->tenantHttp->createRequest()
->setMethod('get')
->setUrl('getTenantEnv')
->setData([
'appname' => $this->app_name,
'secret' => $this->secret,
'tenantid' => $this->tenant_id,
])
->send();
// 检查响应状态码是否等于20x
if ($response->isOk) {
// 检查业务逻辑是否成功
if ($response->data['returnCode'] === 0) {
$tenantData = ['message' => $response->data['returnDesc'], 'data' => $response->data['returnData']];
// 将多租户数据存放到缓存供下次使用
$redisCache[$tenantKey] = $tenantData;
return $tenantData;
} else {
$this->addError('tenant_id', $response->data['returnDesc']);
return false;
}
} else {
throw new ServerErrorHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '20005'), ['statusCode' => $response->getStatusCode()])));
}
} else {
return $tenantData;
}
}
}
Figure 31
50. Clear Redis. In one request, there is only one HTTP request for the multi-tenant system, as shown in Figure 32
Figure 32
51. By default, the data in the cache will be permanent unless it is forcibly removed by some cache policies (for example: the cache space is full, the oldest data will be Remove), and prepare to implement the interface to clear the corresponding cache data, so that when the data of the multi-tenant system changes, the interface to clear the corresponding cache data can be called.
