1. Based on yii2tech\ar\softdelete\SoftDeleteBehavior, implement soft delete for ActiveRecord. Open the URL: https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/concept-behaviors.md , as shown in Figure 1
2. Install the Yii2 ActiveRecord soft delete extension. Use the self-update command to update Composer to the latest version, use the update command to fetch the latest versions of dependencies, and upgrade the composer.lock file. Execute the command, as shown in Figure 2 and Figure 3
Bash
composer require --prefer-dist yii2tech/ar-softdelete
composer self-update
composer global require "fxp/composer-asset-plugin"
composer global update
composer update
3. Database migration: for the status field of the user table, the previous comment was: Status, 0: Deleted; 10: Active. Adjust the comment to: Status, -1: Deleted; 0: Disabled; 1: Enabled. Execute the command, as shown in Figure 4
Bash
.\yii migrate/create update_status_to_user
4. Edit \console\migrations\m180711_054308_update_status_to_user.php
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. Execute the database migration command, as shown in Figure 5
Bash
.\yii migrate
6. View the structure of the database table user, which meets expectations, as shown in Figure 6
7. The extension provides support for so-called “soft” deletion of ActiveRecord, meaning that records are not deleted from the database but are instead marked with a flag or status indicating they are no longer active. Attach the behavior to \common\logics\User.php, replacing STATUS_ACTIVE with STATUS_ENABLED and STATUS_DELETED with STATUS_DISABLED
PHP
<?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. In the \api directory, bulk replace const STATUS_ACTIVE = 10; //Status: Active with const STATUS_ENABLED = 1; //Status: Enabled, and replace the corresponding comments as well. Bulk replace const STATUS_DELETED = 0; //Status: Deleted with const STATUS_DISABLED = 0; //Status: Disabled
9. In the \ directory, bulk replace STATUS_ACTIVE with STATUS_ENABLED
10. Open the URL: http://www.github-shuijingwan-yii2-app-advanced.localhost/gii/model to regenerate the model class files in the /common/models directory, and check Generate ActiveQuery, as shown in Figure 7
11. The MySQL model files in the common/logics directory are business logic-related and inherit from the \common\models\User data layer. Edit \common\logics\User.php to adjust rules() so that after subsequent changes to the data layer model structure, you do not need to manually edit rules(). Add find() to use a custom query class
PHP
/**
* @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. The MySQL model files in the common/logics directory are business logic-related and inherit from the \common\models\UserQuery.php data layer. Edit \common\logics\UserQuery.php, which is the active query class
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. Create \api\models\UserQuery.php. The MySQL model files in the api/models directory are business logic-related (only related to the API) and inherit from the \common\logics\UserQuery logic layer
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
{
// 默认加上一些条件(不等于 状态：禁用)
public function init()
{
$this->andWhere(['!=', 'status', User::STATUS_DISABLED]);
parent::init();
}
}14. Edit \api\models\User.php and add find()
PHP
<?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. Create \api\modules\v1\models\UserQuery.php
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. Edit \api\modules\v1\models\User.php and add find()
PHP
<?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. Edit \api\rests\user\IndexAction.php to adjust the query construction and use the methods of the active query class. Note: If the API is backend-oriented, it filters out deleted resources; if it is frontend-oriented, it filters out deleted and disabled resources
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. In Postman, GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 200 response
JSON
{
"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. View the generated SQL statement, which meets expectations. `status` != -1 is defined in \common\logics\UserQuery.php, `status` != 0 is defined in \api\models\UserQuery.php (frontend-oriented), and `status`=1 is defined in \api\rests\user\IndexAction.php
SQL
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. Edit \api\rests\user\ViewAction.php
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. In Postman, GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/5 , 200 response
JSON
{
"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. View the generated SQL statement, which meets expectations. `status` != -1 is defined in \common\logics\UserQuery.php, `status` != 0 is defined in \api\models\UserQuery.php (frontend-oriented), and `id`=’5′ is defined in \api\rests\user\ViewAction.php
SQL
SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`id`='5')
23. In Postman, GET http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/3 , 404 response, user does not exist, its status value is -1
JSON
{
"name": "Not Found",
"message": "用户ID：3，不存在",
"code": 20002,
"status": 404,
"type": "yii\\web\\NotFoundHttpException"
}
24. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, status value defaults to 1
JSON
{
"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. Edit \api\models\UserUpdate.php. Since the API is frontend-oriented, status cannot be updated
PHP
<?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. Edit \api\rests\user\UpdateAction.php and remove status-related parts
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);
}
$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. In Postman, PUT http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/6 , 200 response
JSON
{
"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. Edit \api\rests\user\DeleteAction.php, replace delete() with softDelete()
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->softDelete() === false) {
throw new ServerErrorHttpException('Failed to delete the object for unknown reason.');
}
return ['code' => 10000, 'message' => Yii::t('success', '10005')];
}
}
29. Browse the user table data, as shown in Figure 8
30. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/6 , 200 response
JSON
{
"code": 10000,
"message": "删除用户成功"
}
31. View the generated SQL statement, which meets expectations. `status` != -1 is defined in \common\logics\UserQuery.php, `status` != 0 is defined in \api\models\UserQuery.php (frontend-oriented), `id`=’5′ is defined in \api\rests\user\DeleteAction.php, `status`=-1 is defined in the softDeleteBehavior behavior, and `updated_at`=1531299598 is defined in the timestampBehavior behavior
SQL
SELECT * FROM `user` WHERE (`status` != 0) AND (`status` != -1) AND (`id`='6')
UPDATE `user` SET `status`=-1, `updated_at`=1531299598 WHERE `id`=6
32. Browse the user table data, status and updated_at have been updated, as shown in Figure 9
33. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, status value defaults to 1
Plaintext
email:777777@163.com
password:777777
username:777777
JSON
{
"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. View the generated SQL statement, which does not meet expectations (when performing uniqueness validation, the status field condition should not be added). `status` != -1 is defined in \common\logics\UserQuery.php, `status` != 0 is defined in \api\models\UserQuery.php (frontend-oriented), `id`=’5′ is defined in \api\rests\user\DeleteAction.php, `status`=-1 is defined in the softDeleteBehavior behavior, and `updated_at`=1531299598 is defined in the timestampBehavior behavior
SQL
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. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/7 , 200 response
JSON
{
"code": 10000,
"message": "删除用户成功"
}
36. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 500 response
Plaintext
email:777777@163.com
password:777777
username:777777
JSON
{
"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. Analyze the results: username, email, and password_reset_token are all unique indexes. However, when performing uniqueness validation, the generated SQL statement in step 34 added extra conditions: (`status` != 0) AND (`status` != -1). This caused the uniqueness validation to pass and proceed to execute the insert SQL statement, resulting in an error. Edit \common\logics\UserQuery.php
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. Edit \api\models\UserQuery.php
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. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 422 response, which meets expectations
Plaintext
email:777777@163.com
password:777777
username:777777
JSON
{
"code": 20004,
"message": "数据验证失败：Username的值\"777777\"已经被占用了。"
}
40. View the SQL statement generated by POST, which meets expectations
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='777777')
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='777777@163.com')
41. Add a status check: if not enabled, respond with a failure. Edit \api\rests\user\ViewAction.php
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. Add a status check: if not enabled, respond with a failure. Edit \api\rests\user\UpdateAction.php
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. Add a status check: if not enabled, respond with a failure. Edit \api\rests\user\DeleteAction.php
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. Check the SQL statements generated by POST, GET /users, GET /users/14, PUT /users/14, and DELETE /users/14 in turn, which meet expectations
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