基于 yiisoft/yii2-app-advanced,在 GitHub 上新建仓库 yii2-app-advanced,新建接口应用(实现 RESTful 风格的 Web Service 服务的 API),实现 ActiveRecord 的软删除,生成 ActiveQuery,自定义查询类 (六) (1)
1、基于 yii2tech\ar\softdelete\SoftDeleteBehavior,实现 ActiveRecord 的软删除,打开网址:https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/concept-behaviors.md ,如图1
2、安装 Yii2 的 ActiveRecord 软删除扩展,用 self-update 命令更新 Composer 为最新版本,用 update 命令获取依赖的最新版本,并且升级 composer.lock 文件,执行命令,如图2、图3
composer require --prefer-dist yii2tech/ar-softdelete composer self-update composer global require "fxp/composer-asset-plugin" composer global update composer update
3、数据库迁移,user 表的 status 字段,之前的注释为:状态,0:删除;10:活跃,调整其注释:状态,-1:删除;0:禁用;1:启用,执行命令,如图4
.\yii migrate/create update_status_to_user
4、编辑 \console\migrations\m180711_054308_update_status_to_user.php
<?php use yii\db\Migration; /** * Class m180711_054308_update_status_to_user */ class m180711_054308_update_status_to_user extends Migration { /** * {@inheritdoc} */ public function safeUp() { $this->alterColumn('{{%user}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(1)->comment('状态,-1:删除;0:禁用;1:启用')); $this->alterColumn('{{%user}}', 'created_at', $this->integer()->notNull()->defaultValue(0)->comment('创建时间')); $this->alterColumn('{{%user}}', 'updated_at', $this->integer()->notNull()->defaultValue(0)->comment('更新时间')); } /** * {@inheritdoc} */ public function safeDown() { $this->alterColumn('{{%user}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(10)->comment('')); $this->alterColumn('{{%user}}', 'created_at', $this->integer()->notNull()->comment('')); $this->alterColumn('{{%user}}', 'updated_at', $this->integer()->notNull()->comment('')); } /* // Use up()/down() to run migration code without a transaction. public function up() { } public function down() { echo "m180711_054308_update_status_to_user cannot be reverted.\n"; return false; } */ }
5、执行数据库迁移命令,如图5
.\yii migrate
6、查看数据库表 user 结构,符合预期,如图6
7、扩展为 ActiveRecord 的所谓“软”删除提供支持,这意味着记录不会从数据库中删除,而是标记一些标记或状态,这表示它不再处于活动状态。附加行为至 \common\logics\User.php,将 STATUS_ACTIVE 替换为 STATUS_ENABLED,将 STATUS_DELETED 替换为 STATUS_DISABLED
<?php namespace common\logics; use Yii; use yii\base\NotSupportedException; use yii\behaviors\TimestampBehavior; use yii2tech\ar\softdelete\SoftDeleteBehavior; use yii\web\IdentityInterface; /** * This is the model class for table "{{%user}}". * * @property int $id * @property string $username * @property string $auth_key * @property string $password_hash * @property string $password_reset_token * @property string $email * @property int $status * @property int $created_at * @property int $updated_at * @property string $password write-only password */ class User extends \common\models\User implements IdentityInterface { const STATUS_DELETED = -1; //状态:删除 const STATUS_DISABLED = 0; //状态:禁用 const STATUS_ENABLED = 1; //状态:启用 /** * @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() { return [ [['username', 'auth_key', 'password_hash', 'email'], 'required'], [['status', 'created_at', 'updated_at'], 'integer'], [['username', 'password_hash', 'password_reset_token', 'email'], 'string', 'max' => 255], [['auth_key'], 'string', 'max' => 32], [['username'], 'unique'], [['email'], 'unique'], [['password_reset_token'], 'unique'], ['status', 'default', 'value' => self::STATUS_ENABLED], ['status', 'in', 'range' => [self::STATUS_DELETED, self::STATUS_DISABLED, self::STATUS_ENABLED]], ]; } /** * @inheritdoc */ public static function findIdentity($id) { return static::findOne(['id' => $id, 'status' => self::STATUS_ENABLED]); } /** * @inheritdoc */ public static function findIdentityByAccessToken($token, $type = null) { throw new NotSupportedException('"findIdentityByAccessToken" is not implemented.'); } /** * Finds user by username * * @param string $username * @return static|null */ public static function findByUsername($username) { return static::findOne(['username' => $username, 'status' => self::STATUS_ENABLED]); } /** * Finds user by password reset token * * @param string $token password reset token * @return static|null */ public static function findByPasswordResetToken($token) { if (!static::isPasswordResetTokenValid($token)) { return null; } return static::findOne([ 'password_reset_token' => $token, 'status' => self::STATUS_ENABLED, ]); } /** * Finds out if password reset token is valid * * @param string $token password reset token * @return bool */ public static function isPasswordResetTokenValid($token) { if (empty($token)) { return false; } $timestamp = (int) substr($token, strrpos($token, '_') + 1); $expire = Yii::$app->params['user.passwordResetTokenExpire']; return $timestamp + $expire >= time(); } /** * @inheritdoc */ public function getId() { return $this->getPrimaryKey(); } /** * @inheritdoc */ public function getAuthKey() { return $this->auth_key; } /** * @inheritdoc */ public function validateAuthKey($authKey) { return $this->getAuthKey() === $authKey; } /** * Validates password * * @param string $password password to validate * @return bool if password provided is valid for current user */ public function validatePassword($password) { return Yii::$app->security->validatePassword($password, $this->password_hash); } /** * Generates password hash from password and sets it to the model * * @param string $password */ public function setPassword($password) { $this->password_hash = Yii::$app->security->generatePasswordHash($password); } /** * Generates "remember me" authentication key */ public function generateAuthKey() { $this->auth_key = Yii::$app->security->generateRandomString(); } /** * Generates new password reset token */ public function generatePasswordResetToken() { $this->password_reset_token = Yii::$app->security->generateRandomString() . '_' . time(); } /** * Removes password reset token */ public function removePasswordResetToken() { $this->password_reset_token = null; } }
8、在目录 \api 中将 const STATUS_ACTIVE = 10; //状态:活跃 批量替换为 const STATUS_ENABLED = 1; //状态:启用,相应注释也需要替换,将 const STATUS_DELETED = 0; //状态:已删除 批量替换为 const STATUS_DISABLED = 0; //状态:禁用
9、在目录 \ 中将 STATUS_ACTIVE 批量替换为 STATUS_ENABLED
10、打开网址:http://www.github-shuijingwan-yii2-app-advanced.localhost/gii/model ,重新生成 /common/models 目录中的模型类文件,勾选 Generate ActiveQuery ,如图7
11、在common/logics目录中的MySQL模型文件为业务逻辑相关,继承至 \common\models\User 数据层,编辑 \common\logics\User.php,调整 rules(),后续数据层模型结构调整后,无需手动编辑 rules(),添加 find(),以使用自定义的查询类
/** * @inheritdoc */ public function rules() { $rules = [ ['status', 'default', 'value' => self::STATUS_ENABLED], ['status', 'in', 'range' => [self::STATUS_DELETED, self::STATUS_DISABLED, self::STATUS_ENABLED]], ]; $parentRules = parent::rules(); return ArrayHelper::merge($rules, $parentRules); } /** * {@inheritdoc} * @return UserQuery the active query used by this AR class. */ public static function find() { return new UserQuery(get_called_class()); }
12、在common/logics目录中的MySQL模型文件为业务逻辑相关,继承至 \common\models\UserQuery.php 数据层,编辑 \common\logics\UserQuery.php,其为活动查询类
<?php namespace common\logics; /** * This is the ActiveQuery class for [[User]]. * * @see User */ class UserQuery extends \common\models\UserQuery { // 默认加上一些条件(不等于 状态:删除) public function init() { $this->andWhere(['!=', 'status', User::STATUS_DELETED]); parent::init(); } // 等于 状态:禁用 public function disabled() { return $this->andWhere(['status' => User::STATUS_DISABLED]); } // 等于 状态:启用 public function enabled() { return $this->andWhere(['status' => User::STATUS_ENABLED]); } }
13、新建 \api\models\UserQuery.php,在api/models目录中的MySQL模型文件为业务逻辑相关(仅与api相关),继承至 \common\logics\UserQuery 逻辑层
<?php /** * Created by PhpStorm. * User: WangQiang * Date: 2018/07/11 * Time: 16:07 */ namespace api\models; /** * This is the ActiveQuery class for [[User]]. * * @see User */ class UserQuery extends \common\logics\UserQuery { // 默认加上一些条件(不等于 状态:禁用) public function init() { $this->andWhere(['!=', 'status', User::STATUS_DISABLED]); parent::init(); } }
14、编辑 \api\models\User.php,添加 find()
<?php /** * Created by PhpStorm. * User: WangQiang * Date: 2018/04/04 * Time: 10:44 */ namespace api\models; class User extends \common\logics\User { /** * {@inheritdoc} * @return UserQuery the active query used by this AR class. */ public static function find() { return new UserQuery(get_called_class()); } }
15、新建 \api\modules\v1\models\UserQuery.php
<?php /** * Created by PhpStorm. * User: WangQiang * Date: 2018/07/11 * Time: 16:15 */ namespace api\modules\v1\models; /** * This is the ActiveQuery class for [[User]]. * * @see User */ class UserQuery extends \api\models\UserQuery { }
16、编辑 \api\modules\v1\models\User.php,添加 find()
<?php /** * Created by PhpStorm. * User: WangQiang * Date: 2018/04/04 * Time: 16:04 */ namespace api\modules\v1\models; class User extends \api\models\User { /** * {@inheritdoc} * @return UserQuery the active query used by this AR class. */ public static function find() { return new UserQuery(get_called_class()); } }
17、编辑 \api\rests\user\IndexAction.php,调整构建查询,使用活动查询类的方法,注:如果是面向后端的接口,则过滤删除的资源,如果是面向前端的接口,则过滤删除、禁用的资源
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; use yii\data\ActiveDataProvider; /** * IndexAction implements the API endpoint for listing multiple models. * * For more details and usage information on IndexAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class IndexAction extends \yii\rest\IndexAction { /** * Prepares the data provider that should return the requested collection of the models. * @return ActiveDataProvider */ protected function prepareDataProvider() { $requestParams = Yii::$app->getRequest()->getBodyParams(); if (empty($requestParams)) { $requestParams = Yii::$app->getRequest()->getQueryParams(); } $filter = null; if ($this->dataFilter !== null) { $this->dataFilter = Yii::createObject($this->dataFilter); if ($this->dataFilter->load($requestParams)) { $filter = $this->dataFilter->build(); if ($filter === false) { return $this->dataFilter; } } } if ($this->prepareDataProvider !== null) { return call_user_func($this->prepareDataProvider, $this, $filter); } /* @var $modelClass \yii\db\BaseActiveRecord */ $modelClass = $this->modelClass; $query = $modelClass::find()->enabled(); if (!empty($filter)) { $query->andWhere($filter); } return Yii::createObject([ 'class' => ActiveDataProvider::className(), 'query' => $query, 'pagination' => [ 'params' => $requestParams, ], 'sort' => [ 'params' => $requestParams, ], ]); } }
18、在 Postman 中,GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ,200响应
{ "code": 10000, "message": "获取用户列表成功", "data": { "items": [ { "id": 2, "username": "111111", "auth_key": "bt7ZrxlSgiaWs3Zsh4w3ogvYyOvrMrcK", "password_hash": "$2y$13$ED00FoPy.mRZy2wNgvV4FurJ/JMmDvZzOEf6Xt7lo8iyCE6CXGoSW", "password_reset_token": null, "email": "222222@163.com", "status": 1, "created_at": 1531278558, "updated_at": 1531285341 }, { "id": 4, "username": "444444", "auth_key": "D9kAKrUh0tqpEvufckK94j_Xt_5oxxQ2", "password_hash": "$2y$13$n7meDsno37xnEMBj4DmYaO8esNe.uritq2VOq4.dEn.ycg6.NkS.K", "password_reset_token": null, "email": "444444@163.com", "status": 1, "created_at": 1531294458, "updated_at": 1531294458 } ], "_links": { "self": { "href": "http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users?page=1" } }, "_meta": { "totalCount": 2, "pageCount": 1, "currentPage": 1, "perPage": 20 } } }
19、查看生成的 SQL 语句,符合预期,`status` != -1 在 \common\logics\UserQuery.php 中定义,`status` != 0 在 \api\models\UserQuery.php 中定义(面向前端),`status`=1 在 \api\rests\user\IndexAction.php 中定义
SELECT COUNT(*) FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`status`=1) SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`status`=1) LIMIT 20
20、编辑 \api\rests\user\ViewAction.php
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; /** * ViewAction implements the API endpoint for returning the detailed information about a model. * * For more details and usage information on ViewAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class ViewAction extends Action { /** * Displays a model. * @param string $id the primary key of the model. * @return \yii\db\ActiveRecordInterface the model being displayed */ public function run($id) { $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } return ['code' => 10000, 'message' => Yii::t('success', '10002'), 'data' => $model]; } }
21、在 Postman 中,GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/5 ,200响应
{ "code": 10000, "message": "获取用户详情成功", "data": { "id": 5, "username": "555555", "auth_key": "BWQRanWo3mtndyCIn3ZrsG0nBNF5Wl4p", "password_hash": "$2y$13$UyWuLtWyTzX9iFaWNevcDuFHeixAWge.NzWXcxGy9Rf7b3XPKOrAS", "password_reset_token": null, "email": "555555@163.com", "status": 1, "created_at": 1531297638, "updated_at": 1531297638 } }
22、查看生成的 SQL 语句,符合预期,`status` != -1 在 \common\logics\UserQuery.php 中定义,`status` != 0 在 \api\models\UserQuery.php 中定义(面向前端),`id`=’5′ 在 \api\rests\user\ViewAction.php 中定义
SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`id`='5')
23、在 Postman 中,GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/3 ,404响应,用户不存在,其 status 值为 -1
{ "name": "Not Found", "message": "用户ID:3,不存在", "code": 20002, "status": 404, "type": "yii\\web\\NotFoundHttpException" }
24、在 Postman 中,POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ,201响应,status 值默认为 1
{ "code": 10000, "message": "Create user success", "data": { "username": "666666", "email": "666666@163.com", "password_hash": "$2y$13$T2fCO8/MvUKiBWTY4kzfXedXu.fbRwuTTxxwWhTVAewpN2fuavCeC", "auth_key": "x5kZ-25BUG_uW_7p8dqWLogridMJpAyd", "status": 1, "created_at": 1531298775, "updated_at": 1531298775, "id": 6 } }
25、编辑 \api\models\UserUpdate.php,接口面向前端,则 status 不能够更新
<?php namespace api\models; use yii\base\Model; /** * UserUpdate */ class UserUpdate extends Model { public $id; public $email; public $password; /** * {@inheritdoc} */ public function rules() { return [ ['email', 'trim'], ['email', 'required'], ['email', 'email'], ['email', 'string', 'max' => 255], ['email', 'unique', 'targetClass' => '\api\models\User', 'filter' => ['!=', 'id', $this->id]], ['password', 'required'], ['password', 'string', 'min' => 6], ]; } }
26、编辑 \api\rests\user\UpdateAction.php,删除 status 相关
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; use yii\base\Model; use yii\db\ActiveRecord; use api\models\UserUpdate; use yii\web\ServerErrorHttpException; /** * UpdateAction implements the API endpoint for updating a model. * * For more details and usage information on UpdateAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class UpdateAction extends Action { /** * @var string the scenario to be assigned to the model before it is validated and updated. */ public $scenario = Model::SCENARIO_DEFAULT; /** * Updates an existing model. * @param string $id the primary key of the model. * @return \yii\db\ActiveRecordInterface the model being updated * @throws ServerErrorHttpException if there is any error when updating the model */ public function run($id) { /* @var $model ActiveRecord */ $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } $userUpdate = new UserUpdate(); $userUpdate->id = $id; $userUpdate->load(Yii::$app->getRequest()->getBodyParams(), ''); if (!$userUpdate->validate()) { if ($userUpdate->hasErrors()) { $response = Yii::$app->getResponse(); $response->setStatusCode(422, 'Data Validation Failed.'); foreach ($userUpdate->getFirstErrors() as $message) { $firstErrors = $message; break; } return ['code' => 20004, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20004'), ['firstErrors' => $firstErrors]))]; } elseif (!$userUpdate->hasErrors()) { throw new ServerErrorHttpException('Failed to create the object for unknown reason.'); } } $model->scenario = $this->scenario; $model->email = $userUpdate->email; $model->setPassword($userUpdate->password); $model->generateAuthKey(); if ($model->save() === false) { if ($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 update the object for unknown reason.'); } } return ['code' => 10000, 'message' => Yii::t('success', '10004'), 'data' => $model]; } }
27、在 Postman 中,PUT http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/6 ,200响应
{ "code": 10000, "message": "更新用户成功", "data": { "id": 6, "username": "666666", "auth_key": "CDCjtpL1hZWtj1KRs-p3SZb3cZUjgn9z", "password_hash": "$2y$13$eM6HydN2eSHT1nqPQ7PICOZCgN3wpXAiL0zvufwzi6knmUd22X7iq", "password_reset_token": null, "email": "666666@163.com", "status": 1, "created_at": 1531298775, "updated_at": 1531299330 } }
28、编辑 \api\rests\user\DeleteAction.php,delete() 替换为 softDelete()
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; use yii\web\ServerErrorHttpException; /** * DeleteAction implements the API endpoint for deleting a model. * * For more details and usage information on DeleteAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class DeleteAction extends Action { /** * Deletes a model. * @param mixed $id id of the model to be deleted. * @throws ServerErrorHttpException on failure. */ public function run($id) { $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } if ($model->softDelete() === false) { throw new ServerErrorHttpException('Failed to delete the object for unknown reason.'); } return ['code' => 10000, 'message' => Yii::t('success', '10005')]; } }
29、浏览 user 表数据,如图8
30、在 Postman 中,DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/6 ,200响应
{ "code": 10000, "message": "删除用户成功" }
31、查看生成的 SQL 语句,符合预期,`status` != -1 在 \common\logics\UserQuery.php 中定义,`status` != 0 在 \api\models\UserQuery.php 中定义(面向前端),`id`=’5′ 在 \api\rests\user\DeleteAction.php 中定义,`status`=-1 在 行为 softDeleteBehavior 中定义,`updated_at`=1531299598 在 行为 timestampBehavior 中定义
SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`id`='6') UPDATE `user` SET `status`=-1, `updated_at`=1531299598 WHERE `id`=6
32、浏览 user 表数据,status、updated_at 已经更新,如图9
33、在 Postman 中,POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ,201响应,status 值默认为 1
email:777777@163.com password:777777 username:777777
{ "code": 10000, "message": "创建用户成功", "data": { "username": "777777", "email": "777777@163.com", "password_hash": "$2y$13$/CioMoodaC.JTdMenKuJge9j9k97Uute4DnVmZz6Er7tzmtgqDA9G", "auth_key": "qnyEofUzxsf-mgkYJiiTBlL3rMFXFtzA", "status": 1, "created_at": 1531303629, "updated_at": 1531303629, "id": 7 } }
34、查看生成的 SQL 语句,不符合预期(在执行唯一性验证时,不应该添加状态字段的条件),`status` != -1 在 \common\logics\UserQuery.php 中定义,`status` != 0 在 \api\models\UserQuery.php 中定义(面向前端),`id`=’5′ 在 \api\rests\user\DeleteAction.php 中定义,`status`=-1 在 行为 softDeleteBehavior 中定义,`updated_at`=1531299598 在 行为 timestampBehavior 中定义
SELECT EXISTS(SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`user`.`username`='777777')) SELECT EXISTS(SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`user`.`email`='777777@163.com')) INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `status`, `created_at`, `updated_at`) VALUES ('777777', '777777@163.com', '$2y$13$/CioMoodaC.JTdMenKuJge9j9k97Uute4DnVmZz6Er7tzmtgqDA9G', 'qnyEofUzxsf-mgkYJiiTBlL3rMFXFtzA', 1, 1531303629, 1531303629)
35、在 Postman 中,DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/7 ,200响应
{ "code": 10000, "message": "删除用户成功" }
36、在 Postman 中,POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ,500响应
email:777777@163.com password:777777 username:777777
{ "name": "Integrity constraint violation", "message": "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '777777' for key 'username'\nThe SQL being executed was: INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `status`, `created_at`, `updated_at`) VALUES ('777777', '777777@163.com', '$2y$13$1P6IWOqZ4kTbCPKFRnqBR.FR9FwfRVVGwBWQYTt3DxfstjyEL4X3e', 'tZPoUVJJTfjgjlu8fsynKLkaVTL0lRtq', 1, 1531303970, 1531303970)", "code": 23000, "type": "yii\\db\\IntegrityException", "file": "E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Schema.php", "line": 664, "stack-trace": [ "#0 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Command.php(1263): yii\\db\\Schema->convertException(Object(PDOException), 'INSERT INTO `us...')", "#1 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Command.php(1075): yii\\db\\Command->internalExecute('INSERT INTO `us...')", "#2 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Schema.php(433): yii\\db\\Command->execute()", "#3 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\ActiveRecord.php(549): yii\\db\\Schema->insert('{{%user}}', Array)", "#4 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\ActiveRecord.php(515): yii\\db\\ActiveRecord->insertInternal(NULL)", "#5 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\BaseActiveRecord.php(670): yii\\db\\ActiveRecord->insert(true, NULL)", "#6 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\api\\rests\\user\\CreateAction.php(72): yii\\db\\BaseActiveRecord->save()", "#7 [internal function]: api\\rests\\user\\CreateAction->run()", "#8 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Action.php(94): call_user_func_array(Array, Array)", "#9 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Controller.php(157): yii\\base\\Action->runWithParams(Array)", "#10 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Module.php(528): yii\\base\\Controller->runAction('create', Array)", "#11 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\web\\Application.php(103): yii\\base\\Module->runAction('v1/user/create', Array)", "#12 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Application.php(386): yii\\web\\Application->handleRequest(Object(yii\\web\\Request))", "#13 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\api\\web\\index.php(17): yii\\base\\Application->run()", "#14 {main}" ], "error-info": [ "23000", 1062, "Duplicate entry '777777' for key 'username'" ], "previous": { "name": "Exception", "message": "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '777777' for key 'username'", "code": "23000", "type": "PDOException", "file": "E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Command.php", "line": 1258, "stack-trace": [ "#0 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Command.php(1258): PDOStatement->execute()", "#1 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Command.php(1075): yii\\db\\Command->internalExecute('INSERT INTO `us...')", "#2 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\Schema.php(433): yii\\db\\Command->execute()", "#3 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\ActiveRecord.php(549): yii\\db\\Schema->insert('{{%user}}', Array)", "#4 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\ActiveRecord.php(515): yii\\db\\ActiveRecord->insertInternal(NULL)", "#5 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\db\\BaseActiveRecord.php(670): yii\\db\\ActiveRecord->insert(true, NULL)", "#6 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\api\\rests\\user\\CreateAction.php(72): yii\\db\\BaseActiveRecord->save()", "#7 [internal function]: api\\rests\\user\\CreateAction->run()", "#8 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Action.php(94): call_user_func_array(Array, Array)", "#9 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Controller.php(157): yii\\base\\Action->runWithParams(Array)", "#10 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Module.php(528): yii\\base\\Controller->runAction('create', Array)", "#11 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\web\\Application.php(103): yii\\base\\Module->runAction('v1/user/create', Array)", "#12 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\vendor\\yiisoft\\yii2\\base\\Application.php(386): yii\\web\\Application->handleRequest(Object(yii\\web\\Request))", "#13 E:\\wwwroot\\github-shuijingwan-yii2-app-advanced\\api\\web\\index.php(17): yii\\base\\Application->run()", "#14 {main}" ] } }
37、分析结果,username、email、password_reset_token 皆是唯一索引,可是在执行唯一性验证时,其生成的SQL语句,在第 34 步骤,额外增加了条件:(`status` != 0) AND (`status` != -1),导致唯一性验证通过,进行执行插入 SQL 语句,报错。编辑 \common\logics\UserQuery.php
<?php namespace common\logics; /** * This is the ActiveQuery class for [[User]]. * * @see User */ class UserQuery extends \common\models\UserQuery { // 不等于 状态:删除 public function notDeleted() { $this->andWhere(['!=', 'status', User::STATUS_DELETED]); } // 等于 状态:禁用 public function disabled() { return $this->andWhere(['status' => User::STATUS_DISABLED]); } // 等于 状态:启用 public function enabled() { return $this->andWhere(['status' => User::STATUS_ENABLED]); } }
38、编辑 \api\models\UserQuery.php
<?php /** * Created by PhpStorm. * User: WangQiang * Date: 2018/07/11 * Time: 16:07 */ namespace api\models; /** * This is the ActiveQuery class for [[User]]. * * @see User */ class UserQuery extends \common\logics\UserQuery { }
39、在 Postman 中,POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ,422响应,符合预期
email:777777@163.com password:777777 username:777777
{ "code": 20004, "message": "数据验证失败:Username的值\"777777\"已经被占用了。" }
40、查看 POST 生成的 SQL 语句,符合预期
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='777777') SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='777777@163.com')
41、添加状态的判断,如果未启用,响应失败,编辑 \api\rests\user\ViewAction.php
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; /** * ViewAction implements the API endpoint for returning the detailed information about a model. * * For more details and usage information on ViewAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class ViewAction extends Action { /** * Displays a model. * @param string $id the primary key of the model. * @return \yii\db\ActiveRecordInterface the model being displayed */ public function run($id) { $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } /* 判断状态,如果为删除,则返回失败 */ if ($model->status === $model::STATUS_DELETED) { return ['code' => 20003, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20003'), ['id' => $id]))]; } /* 判断状态,如果为禁用,则返回失败 */ if ($model->status === $model::STATUS_DISABLED) { return ['code' => 20804, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20804'), ['id' => $id]))]; } return ['code' => 10000, 'message' => Yii::t('success', '10002'), 'data' => $model]; } }
42、添加状态的判断,如果未启用,响应失败,编辑 \api\rests\user\UpdateAction.php
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; use yii\base\Model; use yii\db\ActiveRecord; use api\models\UserUpdate; use yii\web\ServerErrorHttpException; /** * UpdateAction implements the API endpoint for updating a model. * * For more details and usage information on UpdateAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class UpdateAction extends Action { /** * @var string the scenario to be assigned to the model before it is validated and updated. */ public $scenario = Model::SCENARIO_DEFAULT; /** * Updates an existing model. * @param string $id the primary key of the model. * @return \yii\db\ActiveRecordInterface the model being updated * @throws ServerErrorHttpException if there is any error when updating the model */ public function run($id) { /* @var $model ActiveRecord */ $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } /* 判断状态,如果为删除,则返回失败 */ if ($model->status === $model::STATUS_DELETED) { return ['code' => 20003, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20003'), ['id' => $id]))]; } /* 判断状态,如果为禁用,则返回失败 */ if ($model->status === $model::STATUS_DISABLED) { return ['code' => 20804, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20804'), ['id' => $id]))]; } $userUpdate = new UserUpdate(); $userUpdate->id = $id; $userUpdate->load(Yii::$app->getRequest()->getBodyParams(), ''); if (!$userUpdate->validate()) { if ($userUpdate->hasErrors()) { $response = Yii::$app->getResponse(); $response->setStatusCode(422, 'Data Validation Failed.'); foreach ($userUpdate->getFirstErrors() as $message) { $firstErrors = $message; break; } return ['code' => 20004, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20004'), ['firstErrors' => $firstErrors]))]; } elseif (!$userUpdate->hasErrors()) { throw new ServerErrorHttpException('Failed to create the object for unknown reason.'); } } $model->scenario = $this->scenario; $model->email = $userUpdate->email; $model->setPassword($userUpdate->password); $model->generateAuthKey(); if ($model->save() === false) { if ($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 update the object for unknown reason.'); } } return ['code' => 10000, 'message' => Yii::t('success', '10004'), 'data' => $model]; } }
43、添加状态的判断,如果未启用,响应失败,编辑 \api\rests\user\DeleteAction.php
<?php /** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */ namespace api\rests\user; use Yii; use yii\web\ServerErrorHttpException; /** * DeleteAction implements the API endpoint for deleting a model. * * For more details and usage information on DeleteAction, see the [guide article on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */ class DeleteAction extends Action { /** * Deletes a model. * @param mixed $id id of the model to be deleted. * @throws ServerErrorHttpException on failure. */ public function run($id) { $model = $this->findModel($id); if ($this->checkAccess) { call_user_func($this->checkAccess, $this->id, $model); } /* 判断状态,如果为删除,则返回失败 */ if ($model->status === $model::STATUS_DELETED) { return ['code' => 20003, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20003'), ['id' => $id]))]; } /* 判断状态,如果为禁用,则返回失败 */ if ($model->status === $model::STATUS_DISABLED) { return ['code' => 20804, 'message' => Yii::t('error', Yii::t('error', Yii::t('error', '20804'), ['id' => $id]))]; } if ($model->softDelete() === false) { throw new ServerErrorHttpException('Failed to delete the object for unknown reason.'); } return ['code' => 10000, 'message' => Yii::t('success', '10005')]; } }
44、依次查看 POST、GET /users、GET /users/14、PUT /users/14、DELETE /users/14 生成的 SQL 语句,符合预期
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='141414') SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='141414@163.com') INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `status`, `created_at`, `updated_at`) VALUES ('141414', '141414@163.com', '$2y$13$AAlTFSKvl73Q4sR2.dFJtO2TzBuxVlZIib0cQXEv7KtZ9AZTm28E.', 'nyZ5_0EZEeF4yZd_mg3D8lEWQa1-t9uk', 1, 1531362962, 1531362962) SELECT COUNT(*) FROM `user` WHERE `status`=1 SELECT * FROM `user` WHERE `status`=1 LIMIT 20 SELECT * FROM `user` WHERE `id`='14' SELECT * FROM `user` WHERE `id`='14' SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='14141414@163.com') AND (`id` != '14')) SELECT `user`.`id` FROM `user` WHERE `user`.`username`='141414' LIMIT 2 SELECT `user`.`id` FROM `user` WHERE `user`.`email`='14141414@163.com' LIMIT 2 UPDATE `user` SET `auth_key`='vQyuGpurq5EpM4G4EufxLuLwqwYfhT1n', `password_hash`='$2y$13$5dmvKnesGhoeJpG63k092OSgm0t2yN1yByOLFDump11LDmqnYLx9K', `email`='14141414@163.com', `updated_at`=1531363151 WHERE `id`=14 SELECT * FROM `user` WHERE `id`='14' UPDATE `user` SET `status`=-1, `updated_at`=1531363448 WHERE `id`=14
近期评论