Categories: MySQLMySQL 5.7PHPPHP 7.1PostmanWeb应用开发Web框架WindowsWindows 10Windows 10 家庭中文版YiiYii 2开发工具操作系统数据库程序调试工具编程语言

基于 yiisoft/yii2-app-advanced，在 GitHub 上新建仓库 yii2-app-advanced，新建接口应用(实现 RESTful 风格的 Web Service 服务的 API)，实现 ActiveRecord 的软删除，生成 ActiveQuery，自定义查询类 (六) (1)

图9

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

 

永夜

Leave a Comment
Share
Published by
永夜
Tags: ActiveQueryActiveRecordcomposercomposer installcomposer updatedeleteinitMigrateMySQLPHP 7.1.12RESTfulRESTful APISoftDeletesoftDeleteAttributeValuesSoftDeleteBehaviorstatusUniqueValidatorYiiYii 2yii2tech\ar\softdelete\SoftDeleteBehavior
6 年 ago

Recent Posts

Laravel 9 中的 Redis 门面 hset 不允许传递多个字段/值

1、尝试基于 Redis 门面…

14 小时 ago

在 Google Chrome 中由于网站升级，打开空白，仅在无痕模式中可正常打开的解决

1、在 Google Chro…

5 天 ago

在 https://tinys.pl/cn/swap 上用 SOL 购买得到 NUTS，在 欧易 Web3 钱包 中显示数量为 0 的排查分析

1、在 https://tin…

7 天 ago

在 Laravel 9 中，基于 Spatie\QueryBuilder\QueryBuilder 来生成查询 SQL，同一字段可支持：partial、exact

1、参考：在 Laravel …

1 周 ago

基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式

1、现在导出的 Excel 文…

1 周 ago

在 OneKey 钱包中，发送 Optimism 网络中的 USDT 的流程

1、在 OneKey 钱包中，…

3 周 ago