1. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, the status value defaults to 1
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 10000,
"message": "创建用户成功",
"data": {
"username": "111111",
"email": "111111@163.com",
"password_hash": "$2y$13$DcZR8PZnlOUqWXRqpbfrxelU6uJIq4dTJTsHLVcnUylOnhAX27SQe",
"auth_key": "dq78OPYSn4yIsUtYI7xp90zhZIVoE3Yi",
"status": 1,
"created_at": 1550042976,
"updated_at": 1550042976,
"id": 1
}
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='111111')
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='111111@163.com')
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `status`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$DcZR8PZnlOUqWXRqpbfrxelU6uJIq4dTJTsHLVcnUylOnhAX27SQe', 'dq78OPYSn4yIsUtYI7xp90zhZIVoE3Yi', 1, 1550042976, 1550042976)
2. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 , 200 response
JSON
{
"code": 10000,
"message": "删除用户成功"
}
SQL
SELECT * FROM `user` WHERE `id`='1'
UPDATE `user` SET `status`=-1, `updated_at`=1550043516 WHERE `id`=1
3. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 422 response, which does not meet expectations. The expected response is 201, because “username”: “111111” has already been deleted
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 20004,
"message": "数据验证失败：Username的值\"111111\"已经被占用了。"
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='111111')
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='111111@163.com')
4. Create \console\migrations\m180925_054952_add_is_deleted_and_deleted_at_to_user.php. Update the field status: 0: disabled; 1: enabled, default: 1. Add the field is_deleted, whether it is deleted: 0: no; 1: yes, default: 0. Add the field deleted_at, deletion time, default: 0.
PHP
<?php
use yii\db\Migration;
/**
* Class m180925_054952_add_is_deleted_and_deleted_at_to_user
*/
class m180925_054952_add_is_deleted_and_deleted_at_to_user extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->alterColumn('{{%user}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(1)->comment('状态，0：禁用；1：启用'));
$this->addColumn('{{%user}}', 'is_deleted', $this->smallInteger(6)->notNull()->defaultValue(0)->comment('是否被删除，0：否；1：是')->after('status'));
$this->addColumn('{{%user}}', 'deleted_at', $this->integer()->notNull()->defaultValue(0)->comment('删除时间')->after('updated_at'));
$this->dropIndex('username', '{{%user}}');
$this->dropIndex('email', '{{%user}}');
$this->createIndex('uc_username_is_deleted_deleted_at', '{{%user}}', ['username', 'is_deleted', 'deleted_at'], $unique = true);
$this->createIndex('uc_email_is_deleted_deleted_at', '{{%user}}', ['email', 'is_deleted', 'deleted_at'], $unique = true);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m180925_054952_add_is_deleted_and_deleted_at_to_user cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m180925_054952_add_is_deleted_and_deleted_at_to_user cannot be reverted.\n";
return false;
}
*/
}
5. Create \console\migrations\m180925_060709_add_is_deleted_and_deleted_at_to_page.php
PHP
<?php
use yii\db\Migration;
/**
* Class m180925_060709_add_is_deleted_and_deleted_at_to_page
*/
class m180925_060709_add_is_deleted_and_deleted_at_to_page extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->alterColumn('{{%page}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(1)->comment('状态，0：禁用；1：草稿；2：发布'));
$this->addColumn('{{%page}}', 'is_deleted', $this->smallInteger(6)->notNull()->defaultValue(0)->comment('是否被删除，0：否；1：是')->after('status'));
$this->addColumn('{{%page}}', 'deleted_at', $this->integer()->notNull()->defaultValue(0)->comment('删除时间')->after('updated_at'));
$this->dropIndex('uc_uuid', '{{%page}}');
$this->createIndex('uc_uuid_is_deleted_deleted_at', '{{%page}}', ['uuid', 'is_deleted', 'deleted_at'], $unique = true);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m180925_060709_add_is_deleted_and_deleted_at_to_page cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m180925_060709_add_is_deleted_and_deleted_at_to_page cannot be reverted.\n";
return false;
}
*/
}
6. Create \console\migrations\m180925_060709_add_is_deleted_and_deleted_at_to_page.php, update records where status equals -1 to set is_deleted to 1, status to 0, and deleted_at to the updated_at of the current record.
PHP
<?php
use yii\db\Migration;
use yii\db\Expression;
/**
* Class m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page
*/
class m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->update('{{%user}}', ['is_deleted' => 1, 'status' => 0, 'deleted_at' => new Expression('updated_at')], ['status' => -1]);
$this->update('{{%page}}', ['is_deleted' => 1, 'status' => 0, 'deleted_at' => new Expression('updated_at')], ['status' => -1]);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page cannot be reverted.\n";
return false;
}
*/
}
7. Execute the database migration command and check the structure of the database table user, which meets expectations, as shown in Figure 10
Bash
./yii migrate
8. Regenerate the model class files in the /common/models directory based on Gii, and attach the behavior to \common\logics\User.php. Upon soft deletion, the value of is_deleted is 1, and the value of deleted_at is the current timestamp
PHP
<?php
namespace common\logics;
use Yii;
use yii\base\NotSupportedException;
use yii\behaviors\TimestampBehavior;
use yii2tech\ar\softdelete\SoftDeleteBehavior;
use yii\web\IdentityInterface;
use yii\helpers\ArrayHelper;
/**
* 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_DISABLED = 0; //状态：禁用
const STATUS_ENABLED = 1; //状态：启用
const IS_DELETED_NO = 0; //是否被删除：否
const IS_DELETED_YES = 1; //是否被删除：是
const DELETED_AT_DEFAULT = 0; //删除时间：默认值
/**
* @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' => [
'class' => SoftDeleteBehavior::className(),
'softDeleteAttributeValues' => [
'is_deleted' => self::IS_DELETED_YES,
'deleted_at' => function ($model) {
return time();
},
],
],
];
}
/**
* @inheritdoc
*/
public function rules()
{
$rules = [
['is_deleted', 'default', 'value' => self::IS_DELETED_NO],
['status', 'default', 'value' => self::STATUS_ENABLED],
['status', 'in', 'range' => [self::STATUS_DISABLED, self::STATUS_ENABLED]],
['deleted_at', 'default', 'value' => self::DELETED_AT_DEFAULT],
];
$parentRules = parent::rules();
return ArrayHelper::merge($rules, $parentRules);
}
/**
* @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;
}
/**
* {@inheritdoc}
* @return UserQuery the active query used by this AR class.
*/
public static function find()
{
return new UserQuery(get_called_class());
}
}
9. The MySQL model files in the common/logics directory are related to business logic and inherit from the \common\models\UserQuery.php data layer. Edit \common\logics\UserQuery.php, which is an 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 isDeletedNo()
{
return $this->andWhere(['is_deleted' => User::IS_DELETED_NO]);
}
// 等于 状态：禁用
public function disabled()
{
return $this->andWhere(['status' => User::STATUS_DISABLED]);
}
// 等于 状态：启用
public function enabled()
{
return $this->andWhere(['status' => User::STATUS_ENABLED]);
}
}10. Soft deletion status: replacement of -1
11. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, the status value defaults to 1
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 10000,
"message": "创建用户成功",
"data": {
"username": "111111",
"email": "111111@163.com",
"password_hash": "$2y$13$wm0kbyttNUh.2mPGBRkC8u44c9ZvQLnqfoNyyhafP5d/ELb4KcU6G",
"auth_key": "qgpNse_KMphc_rskoq3HPA6piQ3KWPKU",
"is_deleted": 0,
"status": 1,
"deleted_at": 0,
"created_at": 1550046406,
"updated_at": 1550046406,
"id": 1
}
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$wm0kbyttNUh.2mPGBRkC8u44c9ZvQLnqfoNyyhafP5d/ELb4KcU6G', 'qgpNse_KMphc_rskoq3HPA6piQ3KWPKU', 0, 1, 0, 1550046406, 1550046406)
12. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 , 200 response
JSON
{
"code": 10000,
"message": "删除用户成功"
}
SQL
SELECT * FROM `user` WHERE `id`='1'
UPDATE `user` SET `is_deleted`=1, `deleted_at`=1550046622 WHERE `id`=1
13. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, as expected, “username”: “111111” is created successfully
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 10000,
"message": "创建用户成功",
"data": {
"username": "111111",
"email": "111111@163.com",
"password_hash": "$2y$13$PzqSuKYN8Np.criGjatAruZQzz/azR6rlnYX5UNMkoGBiDKv2agWO",
"auth_key": "tsw1Yir0dtaVbg3jWYDrS0CY09wV2W5c",
"is_deleted": 0,
"status": 1,
"deleted_at": 0,
"created_at": 1550046672,
"updated_at": 1550046672,
"id": 2
}
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$PzqSuKYN8Np.criGjatAruZQzz/azR6rlnYX5UNMkoGBiDKv2agWO', 'tsw1Yir0dtaVbg3jWYDrS0CY09wV2W5c', 0, 1, 0, 1550046672, 1550046672)
14. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 , 200 response
JSON
{
"code": 226003,
"message": "用户ID：1，的状态为已删除"
}
SQL
SELECT * FROM `user` WHERE `id`='1'
15. In Postman, DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/2 , 200 response
JSON
{
"code": 10000,
"message": "删除用户成功"
}
SQL
SELECT * FROM `user` WHERE `id`='2'
UPDATE `user` SET `is_deleted`=1, `deleted_at`=1550047048 WHERE `id`=2
16. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 201 response, as expected, “username”: “111111” is created successfully
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 10000,
"message": "创建用户成功",
"data": {
"username": "111111",
"email": "111111@163.com",
"password_hash": "$2y$13$QtoJ.eOjzgrLfLTyQIwsf.8kPz8eYW54g3dPVj1QQeAECvLp85aQK",
"auth_key": "Z1y-Lzliyrgaf0Us5d4LqYHqUtFwtMYK",
"is_deleted": 0,
"status": 1,
"deleted_at": 0,
"created_at": 1550047120,
"updated_at": 1550047120,
"id": 3
}
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$QtoJ.eOjzgrLfLTyQIwsf.8kPz8eYW54g3dPVj1QQeAECvLp85aQK', 'Z1y-Lzliyrgaf0Us5d4LqYHqUtFwtMYK', 0, 1, 0, 1550047120, 1550047120)
17. In Postman, POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users , 422 response, as expected, “username”: “111111” already exists. Browse the user table data, as shown in Figure 11
JSON
{
"email": "111111@163.com",
"password": "111111",
"username": "111111"
}
JSON
{
"code": 226004,
"message": "数据验证失败：The combination \"111111\"-\"0\"-\"0\" of 用户名, Is Deleted and Deleted At has already been taken."
}
SQL
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
18. In Postman, PUT http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/3 , 200 response, as expected
JSON
{
"email": "333333@163.com",
"password": "333333",
"status": 1
}
JSON
{
"code": 10000,
"message": "更新用户成功",
"data": {
"id": 3,
"username": "111111",
"auth_key": "qc5qVXKkHNKJC1ybac6coQqFiePH2hfE",
"password_hash": "$2y$13$R6dQZgoFVT0mWV9kBl8uo.K7VtdualY.pK0fTtWHMpGfa3cqVIoXe",
"password_reset_token": null,
"email": "333333@163.com",
"status": 1,
"is_deleted": 0,
"created_at": 1550047120,
"updated_at": 1550049609,
"deleted_at": 0
}
}
SQL
SELECT * FROM `user` WHERE `id`='3'
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='333333@163.com') AND ((`id` != '3') AND (`is_deleted`=0)))
SELECT `user`.`id` FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0) LIMIT 2
SELECT `user`.`id` FROM `user` WHERE (`user`.`email`='333333@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0) LIMIT 2
UPDATE `user` SET `auth_key`='qc5qVXKkHNKJC1ybac6coQqFiePH2hfE', `password_hash`='$2y$13$R6dQZgoFVT0mWV9kBl8uo.K7VtdualY.pK0fTtWHMpGfa3cqVIoXe', `email`='333333@163.com', `updated_at`=1550049609 WHERE `id`=3
19. It is recommended to add the condition `is_deleted`=0 by default when querying resource lists and resource details, so that only undeleted resources are queried