1、基于 yii2tech/ar/softdelete/SoftDeleteBehavior，实现 ActiveRecord 的软删除，打开网址：https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/concept-behaviors.md ，如图1
图1
2、安装 Yii2 的 ActiveRecord 软删除扩展，用 self-update 命令更新 Composer 为最新版本，用 update 命令获取依赖的最新版本，并且升级 composer.lock 文件，执行命令，如图2、图3
图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
图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
图5
./yii migrate
6、查看数据库表 user 结构，符合预期，如图6
图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
图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
图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
图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
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/250419.html