在 Yii 2 下,实现多租户的逻辑隔离,即在数据库的每张表中皆存在租户ID字段,自定义活动查询类,在每次查询中默认添加租户ID的查询条件
1、在 Yii 2 Starter Kit 中实现数据库连接时的动态配置,配置属性来源于多租户系统,http://www.shuijingwanwq.com/2018/01/18/2328/ ,多租户的物理隔离实现
2、在公有云中,系统要同时服务于多个租户,多个租户共用一个数据库,因此,需要实现多租户的逻辑隔离,即在数据库的每张表中皆存在租户ID字段,表结构如图1
3、/common/models 目录中的模型类文件仅允许Gii工具所生成,为公共的模型数据层,\common\models\ConfigColumn.php,\common\models\ConfigColumnQuery.php
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <?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()); } } |
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 32 33 34 | <?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
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | <?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
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 | <?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <?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的查询条件
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 | <?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
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | <?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响应,符合预期
1 2 3 4 5 6 7 8 9 10 11 12 13 | { "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 的条件重复
1 2 | 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,重新定义验证规则
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /** * @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 的条件有且仅有一次
1 2 | 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() 前的响应
1 2 3 4 | { "code": 20004, "message": "数据验证失败:The combination \"015ce30b116ce86058fa6ab4fea4ac63\"-\"wxcq\" of 租户ID and 栏目代码 has already been taken." } |
调整 rules() 后的响应
1 2 3 4 | { "code": 20004, "message": "数据验证失败:栏目代码的值\"wxbj\"已经被占用了。" } |
近期评论