在 Yii 2 下，实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，自定义活动查询类，在每次查询中默认添加租户ID的查询条件
1、在 Yii 2 Starter Kit 中实现数据库连接时的动态配置，配置属性来源于多租户系统，http://www.shuijingwanwq.com/2018/01/18/2328/ ，多租户的物理隔离实现
2、在公有云中，系统要同时服务于多个租户，多个租户共用一个数据库，因此，需要实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，表结构如图1
图1
3、/common/models 目录中的模型类文件仅允许Gii工具所生成，为公共的模型数据层，\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、/common/logics 目录中的模型类文件为业务逻辑相关，继承至 /common/models 数据层，为公共的模型逻辑层，\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、/common/logics 目录中的模型类文件为业务逻辑相关，继承至 /common/models 数据层，为公共的模型逻辑层，\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、/api/models 目录中的模型类文件为业务逻辑相关(仅与接口应用相关)，继承至 /common/logics 公共逻辑层，\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、/api/models 目录中的模型类文件为业务逻辑相关(仅与接口应用相关)，继承至 /common/logics 公共逻辑层，\api\models\ConfigColumnQuery.php，自定义活动查询类，在每次查询中默认添加租户ID的查询条件
<?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、创建方法，\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响应，符合预期
{
"code": 10000,
"message": "创建栏目设置成功",
"data": {
"code": "wxkm",
"name": "无线昆明",
"status": "0",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"created_at": 1531447852,
"updated_at": 1531447852,
"id": 3
}
}
10、查看生成的 SQL 语句，在执行唯一性验证时，由于在每次查询中默认添加租户ID的查询条件，导致 group_id 的条件重复
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、现在执行查询时的规范：无需手动定义 group_id 的查询条件，/common/logics 目录中的模型类文件为业务逻辑相关，继承至 /common/models 数据层，为公共的模型逻辑层，\common\logics\ConfigColumn.php，重新定义验证规则
/**
* @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、执行 POST 请求后，再次查看生成的 SQL 语句，符合预期，group_id 的条件有且仅有一次
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响应，符合预期
调整 rules() 前的响应
{
"code": 20004,
"message": "数据验证失败：The combination \"015ce30b116ce86058fa6ab4fea4ac63\"-\"wxcq\" of 租户ID and 栏目代码 has already been taken."
}
调整 rules() 后的响应
{
"code": 20004,
"message": "数据验证失败：栏目代码的值\"wxbj\"已经被占用了。"
}
近期评论