投稿
  1. 云图网首页
  2. 技术专区
  3. 大前端

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

• 大前端, 大数据, 研发管理, 编程笔记

1、在 Yii 2 Starter Kit 中实现数据库连接时的动态配置，配置属性来源于多租户系统，http://www.shuijingwanwq.com/2018/01/18/2328/ ，多租户的物理隔离实现

2、在公有云中，系统要同时服务于多个租户，多个租户共用一个数据库，因此，需要实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段，表结构如图1

在公有云中，系统要同时服务于多个租户，多个租户共用一个数据库，因此，需要实现多租户的逻辑隔离，即在数据库的每张表中皆存在租户ID字段

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

 

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/webdev/181174.html

赞 (0)
0 0
上一篇 2021年10月31日 18:29
下一篇 2021年10月31日 18:30

相关推荐

发表回复

登录后才能评论