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
<?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/"已经被占用了。" }
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/tech/pnotes/250421.html