1. Implementing dynamic configuration for database connections in the Yii 2 Starter Kit, where configuration properties originate from a multi-tenant system, https://www.shuijingwanwq.com/2018/01/18/2328/ , implementing physical isolation for multi-tenancy
2. In a public cloud, the system must serve multiple tenants simultaneously. Because multiple tenants share a single database, we need to implement logical isolation for multi-tenancy. This means every table in the database includes a tenant ID field. The table structure is shown in Figure 1.
3. The model class files in the /common/models directory are only generated by the Gii tool and serve as the common model data layer: \common\models\ConfigColumn.php, \common\models\ConfigColumnQuery.php
<?php
namespace common\models;
use Yii;
/**
* This is the model class for table "{{%config_column}}".
*
* @property int $id
* @property string $group_id 租户ID
* @property string $code 栏目代码
* @property string $name 栏目名称
* @property int $status 状态，-1：删除；0：禁用；1：启用
* @property int $created_at 创建时间
* @property int $updated_at 更新时间
*/
class ConfigColumn extends \yii\db\ActiveRecord
{
/**
* {@inheritdoc}
*/
public static function tableName()
{
return '{{%config_column}}';
}
/**
* {@inheritdoc}
*/
public function rules()
{
return [
[['group_id', 'code', 'name'], 'required'],
[['status', 'created_at', 'updated_at'], 'integer'],
[['group_id', 'code', 'name'], 'string', 'max' => 32],
[['group_id', 'code'], 'unique', 'targetAttribute' => ['group_id', 'code']],
[['group_id', 'name'], 'unique', 'targetAttribute' => ['group_id', 'name']],
];
}
/**
* {@inheritdoc}
*/
public function attributeLabels()
{
return [
'id' => Yii::t('model/config-column', 'ID'),
'group_id' => Yii::t('model/config-column', 'Group ID'),
'code' => Yii::t('model/config-column', 'Code'),
'name' => Yii::t('model/config-column', 'Name'),
'status' => Yii::t('model/config-column', 'Status'),
'created_at' => Yii::t('model/config-column', 'Created At'),
'updated_at' => Yii::t('model/config-column', 'Updated At'),
];
}
/**
* {@inheritdoc}
* @return ConfigColumnQuery the active query used by this AR class.
*/
public static function find()
{
return new ConfigColumnQuery(get_called_class());
}
}<?php
namespace common\models;
/**
* This is the ActiveQuery class for [[ConfigColumn]].
*
* @see ConfigColumn
*/
class ConfigColumnQuery extends \yii\db\ActiveQuery
{
/*public function active()
{
return $this->andWhere('[[status]]=1');
}*/
/**
* {@inheritdoc}
* @return ConfigColumn[]|array
*/
public function all($db = null)
{
return parent::all($db);
}
/**
* {@inheritdoc}
* @return ConfigColumn|array|null
*/
public function one($db = null)
{
return parent::one($db);
}
}4. The model class files in the /common/logics directory handle business logic. They inherit from the /common/models data layer and serve as the common model logic layer: \common\logics\ConfigColumn.php
<?php
namespace common\logics;
use Yii;
use yii\behaviors\TimestampBehavior;
use yii2tech\ar\softdelete\SoftDeleteBehavior;
use yii\helpers\ArrayHelper;
class ConfigColumn extends \common\models\ConfigColumn
{
const STATUS_DELETED = -1; //状态：删除
const STATUS_DISABLED = 0; //状态：禁用
const STATUS_ENABLED = 1; //状态：启用
const SCENARIO_CREATE = 'create';
public function scenarios()
{
$scenarios = parent::scenarios();
$scenarios[self::SCENARIO_CREATE] = ['code', 'name', 'status'];
return $scenarios;
}
/**
* @inheritdoc
*/
public function behaviors()
{
return [
'timestampBehavior' => [
'class' => TimestampBehavior::className(),
'attributes' => [
self::EVENT_BEFORE_INSERT => ['created_at', 'updated_at'],
self::EVENT_BEFORE_UPDATE => 'updated_at',
SoftDeleteBehavior::EVENT_BEFORE_SOFT_DELETE => 'updated_at',
]
],
'softDeleteBehavior' => [
'class' => SoftDeleteBehavior::className(),
'softDeleteAttributeValues' => [
'status' => self::STATUS_DELETED
],
],
];
}
/**
* @inheritdoc
*/
public function rules()
{
$rules = [
];
$parentRules = parent::rules();
return ArrayHelper::merge($rules, $parentRules);
}
/**
* {@inheritdoc}
* @return ConfigColumnQuery the active query used by this AR class.
*/
public static function find()
{
return new ConfigColumnQuery(get_called_class());
}
}5. The model class files in the /common/logics directory handle business logic. They inherit from the /common/models data layer and serve as the common model logic layer: \common\logics\ConfigColumnQuery.php
<?php
namespace common\logics;
/**
* This is the ActiveQuery class for [[ConfigColumn]].
*
* @see ConfigColumn
*/
class ConfigColumnQuery extends \common\models\ConfigColumnQuery
{
// 不等于 状态：删除
public function notDeleted()
{
$this->andWhere(['!=', 'status', ConfigColumn::STATUS_DELETED]);
}
// 等于 状态：禁用
public function disabled()
{
return $this->andWhere(['status' => ConfigColumn::STATUS_DISABLED]);
}
// 等于 状态：启用
public function enabled()
{
return $this->andWhere(['status' => ConfigColumn::STATUS_ENABLED]);
}
}6. The model class files in the /api/models directory handle business logic (specific to the API application). They inherit from the /common/logics common logic layer: \api\models\ConfigColumn.php
<?php
namespace api\models;
class ConfigColumn extends \common\logics\ConfigColumn
{
/**
* {@inheritdoc}
* @return ConfigColumnQuery the active query used by this AR class.
*/
public static function find()
{
return new ConfigColumnQuery(get_called_class());
}
}7. The model class files in the /api/models directory handle business logic (specific to the API application). They inherit from the /common/logics common logic layer: \api\models\ConfigColumnQuery.php. This custom active query class automatically adds the tenant ID query condition to every query.
<?php
/**
* Created by PhpStorm.
* User: WangQiang
* Date: 2018/07/12
* Time: 16:07
*/
namespace api\models;
use Yii;
/**
* This is the ActiveQuery class for [[ConfigColumn]].
*
* @see ConfigColumn
*/
class ConfigColumnQuery extends \common\logics\ConfigColumnQuery
{
// 默认加上一些条件(字段：租户ID 等于 参数：租户ID)
public function init()
{
$this->andOnCondition(['group_id' => Yii::$app->params['groupId']]);
parent::init();
}
}8. Create action: \api\rests\config_column\CreateAction.php
<?php
/**
* @link http://www.yiiframework.com/
* @copyright Copyright (c) 2008 Yii Software LLC
* @license http://www.yiiframework.com/license/
*/
namespace api\rests\config_column;
use Yii;
use yii\base\Model;
use yii\helpers\Url;
use yii\web\ServerErrorHttpException;
class CreateAction extends Action
{
/**
* @var string the scenario to be assigned to the new model before it is validated and saved.
*/
public $scenario = Model::SCENARIO_DEFAULT;
/**
* @var string the name of the view action. This property is need to create the URL when the model is successfully created.
*/
public $viewAction = 'view';
public $createScenario = 'create';
/**
* Creates a new model.
* @return \yii\db\ActiveRecordInterface the model newly created
* @throws ServerErrorHttpException if there is any error when creating the model
*/
public function run()
{
if ($this->checkAccess) {
call_user_func($this->checkAccess, $this->id);
}
// 当前用户的身份实例，未认证用户则为 Null
$identity = Yii::$app->user->identity;
/* @var $model \yii\db\ActiveRecord */
$model = new $this->modelClass([
'scenario' => $this->createScenario,
]);
$model->load(Yii::$app->getRequest()->getBodyParams(), '');
$model->group_id = $identity->group_id;
if ($model->save()) {
$response = Yii::$app->getResponse();
$response->setStatusCode(201);
$id = implode(',', array_values($model->getPrimaryKey(true)));
$response->getHeaders()->set('Location', Url::toRoute([$this->viewAction, 'id' => $id], true));
} elseif ($model->hasErrors()) {
$response = Yii::$app->getResponse();
$response->setStatusCode(422, 'Data Validation Failed.');
foreach ($model->getFirstErrors() as $message) {
$firstErrors = $message;
break;
}
return ['code' => 20004, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20004'), ['firstErrors' => $firstErrors]))];
} elseif (!$model->hasErrors()) {
throw new ServerErrorHttpException('Failed to create the object for unknown reason.');
}
return ['code' => 10000, 'message' => Yii::t('success', '11003'), 'data' => $model];
}
}9. POST http://api.pcs-api.localhost/v1/config-columns?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=00be7753cb5ddca8bef997fa648e416d , 201 response, as expected
{
"code": 10000,
"message": "创建栏目设置成功",
"data": {
"code": "wxkm",
"name": "无线昆明",
"status": "0",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"created_at": 1531447852,
"updated_at": 1531447852,
"id": 3
}
}
10. Inspecting the generated SQL statement reveals that when performing uniqueness validation, the tenant ID query condition is added by default to every query, which causes the group_id condition to be duplicated.
SELECT EXISTS(SELECT * FROM `pa_config_column` WHERE (`pa_config_column`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column`.`code`='wxkm') AND (`group_id`='015ce30b116ce86058fa6ab4fea4ac63'))
INSERT INTO `pa_config_column` (`code`, `name`, `status`, `group_id`, `created_at`, `updated_at`) VALUES ('wxkm', '无线昆明', 0, '015ce30b116ce86058fa6ab4fea4ac63', 1531447852, 1531447852)
11. The new standard for executing queries: there is no need to manually define the group_id query condition. The model class files in the /common/logics directory handle business logic. They inherit from the /common/models data layer and serve as the common model logic layer: \common\logics\ConfigColumn.php. Redefine the validation rules.
/**
* @inheritdoc
*/
public function rules()
{
$rules = [
[['status'], 'in', 'range' => [self::STATUS_DISABLED, self::STATUS_ENABLED]],
[['code'], 'match', 'pattern' => '/^[a-z0-9]+$/', 'message' => Yii::t('app', '{attribute} should contain lowercase letters and numbers.')],
[['code'], 'unique', 'targetAttribute' => ['code']],
[['name'], 'unique', 'targetAttribute' => ['name']],
];
$parentRules = parent::rules();
unset($parentRules[3], $parentRules[4]);
return ArrayHelper::merge($rules, $parentRules);
}
12. After executing the POST request, inspect the generated SQL statement again. It meets expectations: the group_id condition appears exactly once.
SELECT EXISTS(SELECT * FROM `pa_config_column` WHERE (`pa_config_column`.`code`='wxbj') AND (`group_id`='015ce30b116ce86058fa6ab4fea4ac63'))
INSERT INTO `pa_config_column` (`code`, `name`, `status`, `group_id`, `created_at`, `updated_at`) VALUES ('wxbj', '无线北京', 0, '015ce30b116ce86058fa6ab4fea4ac63', 1531448313, 1531448313)
13. POST http://api.pcs-api.localhost/v1/config-columns?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=00be7753cb5ddca8bef997fa648e416d , 422 response, as expected
Response before adjusting rules()
{
"code": 20004,
"message": "数据验证失败：The combination \"015ce30b116ce86058fa6ab4fea4ac63\"-\"wxcq\" of 租户ID and 栏目代码 has already been taken."
}
Response after adjusting rules()
{
"code": 20004,
"message": "数据验证失败：栏目代码的值\"wxbj\"已经被占用了。"
}