在 Yii 2 下，实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，自定义活动查询类，在每次查询中默认添加租户ID的查询条件

由永夜 · 2018/07/13

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\"已经被占用了。"
}

在 Yii 2 下，实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，自定义活动查询类，在每次查询中默认添加租户ID的查询条件

您可能还喜欢...

发表回复取消回复

近期文章

近期评论

分类

其他操作

日历

2018 年 7 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

在 Yii 2 下，实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，自定义活动查询类，在每次查询中默认添加租户ID的查询条件

您可能还喜欢...

在 MySQL 5.7 中清空表时，提示：1206 – The total number of locks exceeds the lock tablesize

基于 Composer 安装 laravel/horizon 时，报错：Package laravel/horizon at version ~5.6 has requirements incompatible with your PHP version, PHP extensions and Composer version

微信第三方应用的授权设计与实现(component_verify_ticket、component_access_token、pre_auth_code)

发表回复 取消回复

近期文章

近期评论

分类

其他操作

标签

日历

发表回复取消回复