美多商城项目(五)---订单

订单建模,包含'订单基本信息'和'订单商品'两张表

from django.db import models

from utils.models import BaseModel
from users.models import UserInfo,Address
from goods.models import SKU

class OrderInfo(BaseModel):
    '''
        - 订单基本信息
        
        - 用到 User 和 Address两个外键
    '''
    PAY_METHODS_ENUM = {
        "CASH": 1,
        "ALIPAY": 2
    }
    PAY_METHOD_CHOICES = (
        (1, "货到付款"),
        (2, "支付宝"),
    )
    ORDER_STATUS_ENUM = {
        "UNPAID": 1,
        "UNSEND": 2,
        "UNRECEIVED": 3,
        "UNCOMMENT": 4,
        "FINISHED": 5
    }
    ORDER_STATUS_CHOICES = (
        (1, "待支付"),
        (2, "待发货"),
        (3, "待收货"),
        (4, "待评价"),
        (5, "已完成"),
        (6, "已取消"),
    )
    # 不使用默认自动生成的ID字段,而是自定义
    order_id = models.CharField(max_length=64, primary_key=True, verbose_name="订单号")
    user = models.ForeignKey(UserInfo, on_delete=models.PROTECT, verbose_name="下单用户")
    address = models.ForeignKey(Address, on_delete=models.PROTECT, verbose_name="收货地址")
    # 正整数
    total_count = models.PositiveIntegerField(default=1, verbose_name="商品总数")
    # 设置了精度的十进制数字(推荐使用)
    total_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="商品总金额")
    freight = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="运费")
    pay_method = models.SmallIntegerField(choices=PAY_METHOD_CHOICES, default=1, verbose_name="支付方式")
    status = models.SmallIntegerField(choices=ORDER_STATUS_CHOICES, default=1, verbose_name="订单状态")

    class Meta:
        db_table = "tb_order_info"
        verbose_name = '订单基本信息'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.order_id


class OrderGoods(BaseModel):
    """
        - 订单商品
        
        -两个外键: order & sku
    """
    SCORE_CHOICES = (
        (0, '0分'),
        (1, '20分'),
        (2, '40分'),
        (3, '60分'),
        (4, '80分'),
        (5, '100分'),
    )
    order = models.ForeignKey(OrderInfo, related_name='skus', on_delete=models.CASCADE, verbose_name="订单")
    sku = models.ForeignKey(SKU, on_delete=models.PROTECT, verbose_name="订单商品")
    count = models.PositiveIntegerField(default=1, verbose_name="数量")
    price = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="单价")
    comment = models.TextField(default="", verbose_name="评价信息")
    score = models.SmallIntegerField(choices=SCORE_CHOICES, default=5, verbose_name='满意度评分')
    is_anonymous = models.BooleanField(default=False, verbose_name='是否匿名评价')
    is_commented = models.BooleanField(default=False, verbose_name='是否评价了')

    class Meta:
        db_table = "tb_order_goods"
        verbose_name = '订单商品'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.sku.name

提交订单展示页

• 用户收货地址

• 支付方式('货到付款'/'支付宝')

• 商品信息展示页(不再提供修改功能)

• 前端要获取两块信息,当网页开始加载的时候,立即向后端发起请求

  • 获取用户的收货地址(接口之前已经写好)

  • 获取购物车结算的商品信息(后端要做的事情)

......
mounted: function(){
    // 获取地址信息
    axios.get(this.host + '/user/addresses/', {
            headers: {
                'Authorization': 'JWT ' + this.token
            },
            responseType: 'json'
        })
        .then(response => {
            this.addresses = response.data.addresses;
            this.nowsite = response.data.default_address_id;
        })
        ......

        // 获取结算商品信息
        axios.get(this.host+'/orders/settlement/', {
            headers: {
                'Authorization': 'JWT ' + this.token
            },
            responseType: 'json'
        })
        .then(response => {
            this.skus = response.data.skus;
            this.freight = response.data.freight;
            this.total_count = 0;
            this.total_amount = 0;
            for(var i=0; i<this.skus.length; i++){
                var amount = parseFloat(this.skus[i].price)*this.skus[i].count;
                this.skus[i].amount = amount.toFixed(2);
                this.total_count += this.skus[i].count;
                this.total_amount += amount;
            }
            this.payment_amount = parseFloat(this.freight) + this.total_amount;
            this.payment_amount = this.payment_amount.toFixed(2);
            this.total_amount = this.total_amount.toFixed(2);
        })
        ......

• 获取redis中,该用户购物车所有商品集,再加运费字段,构造成dict,返回给前端
  构造类似下面的数据格式,用两个序列化器来搞定

{
    "freight":"100.00",
    "skus":[
        {
            "id":10,
            "name":'华为xxxx手机',
            "default_image_url":"https://www.dfsdfsf.xsdfsdss",
            "price":"3788.00",
            "count":1
        },
        {
            "id":11,
            "name":'苹果xxxx收集',
            "default_image_url":"https://www.xxxx.yyyyyy",
            "price":"6700.00",
            "count":2
        },
    ]
}

......
### orders.serializers
class CartSKUSerializer(serializers.ModelSerializer):
    '''订单中,商品数据的序列化(提供给下面的序列化类使用)'''
    count = serializers.PositiveIntegerField(label='购买数量')

    class Meta:
        model = SKU
        fields = ['id','name','price','default_image_url','count']


class OrderSettlementSerializer(serializers.Serializer):
    '''订单序列化器'''
    # 多条数据,要加上many,skus对应一个查询集
    skus = CartSKUSerializer(many=True)
    # 简单赋值
    freight = serializers.DecimalField(max_digits=10,decimal_places=2,label='运费')

• 在上述字段中,sku_id 和 count 这两个字段,我们需要从redis获取
  在views中,我们实现这个需求

from decimal import Decimal

from rest_framework.views import APIView
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from django_redis import get_redis_connection

from goods.models import SKU
from .serializers import OrderSettlementSerializer

class OrderSettlementView(APIView):

    # 检查权限
    permission_classes = [IsAuthenticated,]

    def get(self,request):
        redis_conn = get_redis_connection('cart')
        # 这里 user无需再校验,因为权限类已经检查好了
        user = request.user
        # 获取 hash 和 set 中,该用户的所有商品数据,使用空dict来收集
        redis_cart_dict = redis_conn.hgetall('cart_{}'.format(user.id))
        selected_ids = redis_conn.smembers('selected_{}'.format(user.id))
        cart_dict = {}

        for sku_id_bytes in selected_ids:
            cart_dict[int(sku_id_bytes)] = int(redis_cart_dict[sku_id_bytes])

        skus = SKU.objects.filter(id__in=cart_dict.keys())
        # 添加count字段
        for sku in skus:
            sku.count = cart_dict[sku.id]

        # 构造数据,并序列化返回
        freight = Decimal('10.00')
        data_dict = {
            'freight':freight,
            'skus':skus
        }
        # 这里序列化的对象如果是列表,即数据格式外层包一层list,就要加many=True
        serializer = OrderSettlementSerializer(data_dict) # 序列化器可以对 模型对象/查询集/列表/字典 进行序列化
        return Response(serializer.data)

提交订单按钮

• 当用户点击'提交订单'按钮时,前端只传两个字段

  • 用户收货地址
  • 支付方式(货到付款/支付宝)
  • 商品的数据,不要从这里取(虽然用户该页面无法修改,但可以再打开购物车页面,进行编辑)

......
on_order_submit: function(){
    if (this.order_submitting == false){
        this.order_submitting = true;
        axios.post(this.host+'/orders/', {
                // 提交这两个字段
                address: this.nowsite,
                pay_method: this.pay_method
            }, {
                headers: {
                    'Authorization': 'JWT ' + this.token
                },
                responseType: 'json'
            })
            .then(response => {
                location.href = '/order_success.html?order_id='+response.data.order_id
                    +'&amount='+this.payment_amount
                    +'&pay='+this.pay_method;
            })
            .catch(error => {
                this.order_submitting = false;
                alert(error.response.data[0]);
            })
    }
}

• 保存订单功能实现,当用户提交订单以后,我们把订单信息保存下来
  以便后续用户根据订单信息支付商品费用

### views
# 标准的新增行为,使用 CreateAPIView
class CommitOrderView(CreateAPIView):
    serializer_class = CommitOrderSerializer
    permission_classes = [IsAuthenticated,]

### serializers
class CommitOrderSerializer(serializers.ModelSerializer):

    class Meta:
        model = OrderInfo
        fields = ['address','pay_method','order_id']
        # 提交订单以后,只展示一个'订单号'
        read_only_fields = ['order_id']
        extra_kwargs = {
            'address':{'write_only':True},
            'pay_method':{'write_only',True},
        }

    def create(self,validated_data):
        pass

首先保存订单信息(即生成一条OrderInfo记录)

- 根据 OrderInfo模型,确定需要传值的字段

### models
class OrderInfo(BaseModel):
   
    ......
    order_id # 手动生成
    user # request 取
    address # 前端传
    total_count = 0 # 先默认0(商品的总数/总金额等订单生成成功以后,再从redis取(确保数据不会被用户改))
    # 设置了精度的十进制数字
    total_amount = 0
    freight = 固定值
    pay_method = 前端传
    status = 根据 pay_method 确定,如果是支付宝,就显示未支付;其他显示 未发货
    ......

- 保存 OrderInfo 记录

class CommitOrderSerializer(serializers.ModelSerializer):

    class Meta:
        model = OrderInfo
        ......

    def create(self,validated_data):
        user = self.context['request'].user
        # '20230202143404'
        # order_id = timezone.localtime().strftime('%Y%m%d%H%M%S')+ ('%09d' % user.id)
        order_id = timezone.localtime().strftime('%Y%m%d%H%M%S') + '0'*9 + str(user.id)
        address = self.validated_data.get('address')
        pay_method = self.validated_data.get('pay_method')

        # 如果是支付宝,就显示未支付;其他显示 未发货
        status = (OrderInfo.ORDER_STATUS_ENUM['UNPAID']
                  if pay_method == OrderInfo.PAY_METHODS_ENUM['ALIPAY']
                  else OrderInfo.ORDER_STATUS_ENUM['UNSEND'])

        total_count = 0
        total_amount = 0
        freight = 10

        OrderInfo.objects.create(
            order_id=order_id,
            user=user,
            address=address,
            total_count=total_count,
            total_amount=total_amount,
            freight=10,
            pay_method=pay_method,
            status=status
        )

• 从redis获取被勾选的商品信息,更新库存和销量

......
def create(self,validated_data):
    ......

    order_info_obj = OrderInfo.objects.create(
        order_id=order_id,
        user=user,
        address=address,
        total_count=total_count,
        total_amount=total_amount,
        freight=10,
        pay_method=pay_method,
        status=status
    )

    ### 获取redis商品数据
    redis_conn = get_redis_connection('cart')
    cart_dict = redis_conn.hgetall('cart_{}'.format(user.id))
    selected_ids = redis_conn.smembers('selected_{}'.format(user.id))

    # queryset两个特性:惰性和缓存
    # 以下写法,生成queryset对象,会产生'缓存'问题(抢购/秒杀场景会导致库存不准确,引发严重问题!)
    # SKU.objects.filter(id__in=selected_ids)

    ### 修改商品库存,销量
    for sku_id_bytes in selected_ids:
        sku_obj = SKU.objects.get(id=sku_id_bytes)
        count = int(cart_dict['sku_id_bytes'])
        if count > sku_obj.stock:
            raise serializers.ValidationError('库存不足')

        new_stock = sku_obj.stock - count
        new_sales = sku_obj.sales + count
        sku_obj.stock = new_stock
        sku_obj.sales = new_sales
        sku_obj.save()

• 保存订单商品信息
• 累加商品总数量和总价(之前默认值均为0)
• 加入邮费并保存订单信息,最后返回订单信息模型

from datetime import datetime

from django.utils import timezone
from rest_framework import serializers
from django_redis import get_redis_connection

from goods.models import SKU
from .models import OrderInfo,OrderGoods


class CommitOrderSerializer(serializers.ModelSerializer):

    class Meta:
        ......

    def create(self,validated_data):
        ### 保存订单基本信息
        user = self.context['request'].user
        # '20230202143404'
        # order_id = timezone.localtime().strftime('%Y%m%d%H%M%S')+ ('%09d' % user.id)
        order_id = timezone.localtime().strftime('%Y%m%d%H%M%S') + '0'*9 + str(user.id)
        address = self.validated_data.get('address')
        pay_method = self.validated_data.get('pay_method')

        # 如果是支付宝,就显示未支付;其他显示 未发货
        status = (OrderInfo.ORDER_STATUS_ENUM['UNPAID']
                  if pay_method == OrderInfo.PAY_METHODS_ENUM['ALIPAY']
                  else OrderInfo.ORDER_STATUS_ENUM['UNSEND'])

        total_count = 0
        total_amount = 0
        freight = 10

        order_info_obj = OrderInfo.objects.create(
            order_id=order_id,
            user=user,
            address=address,
            total_count=total_count,
            total_amount=total_amount,
            freight=10,
            pay_method=pay_method,
            status=status
        )

        ### 获取redis商品数据
        redis_conn = get_redis_connection('cart')
        cart_dict = redis_conn.hgetall('cart_{}'.format(user.id))
        selected_ids = redis_conn.smembers('selected_{}'.format(user.id))

        # queryset两个特性:惰性和缓存
        # 以下写法,生成queryset对象,会产生'缓存'问题(抢购/秒杀场景会导致库存不准确,引发严重问题!)
        # SKU.objects.filter(id__in=selected_ids)

        ### 修改商品库存,销量
        for sku_id_bytes in selected_ids:
            sku_obj = SKU.objects.get(id=sku_id_bytes)
            count = int(cart_dict['sku_id_bytes'])
            if count > sku_obj.stock:
                raise serializers.ValidationError('库存不足')

            new_stock = sku_obj.stock - count
            new_sales = sku_obj.sales + count
            sku_obj.stock = new_stock
            sku_obj.sales = new_sales
            sku_obj.save()
            ### 保存 订单商品记录
            OrderGoods.objects.create(
                order=order_info_obj,
                sku=sku_obj,
                count=count,
                price=sku_obj.price
            )

            ### 累加商品总数量和总价(之前默认值均为0)
            order_info_obj.total_count += count
            order_info_obj.total_amount += sku_obj.price * count

        ### 加入邮费并保存订单信息
        order_info_obj.total_amount += order_info_obj.freight
        order_info_obj.save()
        return order_info_obj

• 上述代码存在的问题

- 触发异常时: raise serializers.ValidationError('库存不足')

- 此时 order_info_obj 记录已经生成,而这笔订单实际是失败的,不能生成订单让用户支付

- 在上述view中,我们同时操作了4张表,应该共同进退,要么一起成功,要么一起失败

- MySQL支持事务,刚好满足需求

    - MySQL 默认并支持开启事务,每次操作一次表会默认拷贝一个副本,在副本上操作数据
      若副本上成功操作数据,则让副本的数据覆盖源数据;若失败,就退回源数据

- 如果把上述同时操作的4张表看成一次事务,就可以满足需求,即4张表共同进退~

django事务API

• 参考网址

http://47.101.37.192/%E8%AF%BE%E4%BB%B6/%E7%BE%8E%E5%A4%9A%E5%95%86%E5%9F%8E%E8%AF%BE%E4%BB%B6/orders/commit/transaction.html

• 在 Django 中可以通过django.db.transaction 模块提供的atomic来定义一个事务
  atomic提供两种方案实现事务

- 装饰器用法

from django.db import transaction

@transaction.atomic
def viewfunc(request):
  # 这些代码会在一个事务中执行
  ......

- with语句用法

from django.db import transaction

def viewfunc(request):
  # 这部分代码不在事务中，会被 Django 自动提交
  ......

  with transaction.atomic():
      # 这部分代码会在事务中执行
      ......

• 两种方法取舍

- 装饰器用法：整个视图中所有 MySQL 数据库的操作都看做一个事务，范围太大，不够灵活
  而且无法直接作用于类视图

- with 语句用法(推荐)：可以灵活的有选择性的把某些 MySQL 数据库的操作看做一个事务
  而且不用关心视图的类型

• 事务中的保存点

在 Django 中，还提供了保存点的支持，可以在事务中创建保存点来记录数据的特定状态
数据库出现错误时，可以回滚到数据保存点的状态

from django.db import transaction

# 创建保存点
save_id = transaction.savepoint()  
# 回滚到保存点
transaction.savepoint_rollback(save_id)
# 提交从保存点到当前状态的所有数据库事务操作
transaction.savepoint_commit(save_id)

事务运用到项目,实现'共同进退'

class CommitOrderSerializer(serializers.ModelSerializer):

    class Meta:
        ......

    def create(self,validated_data):
        user = self.context['request'].user
        order_id = timezone.localtime().strftime('%Y%m%d%H%M%S') + '0'*9 + str(user.id)
        address = self.validated_data.get('address')
        pay_method = self.validated_data.get('pay_method')

        status = (OrderInfo.ORDER_STATUS_ENUM['UNPAID']
                  if pay_method == OrderInfo.PAY_METHODS_ENUM['ALIPAY']
                  else OrderInfo.ORDER_STATUS_ENUM['UNSEND'])

        total_count = 0
        total_amount = 0
        freight = 10

        # 开始事务
        with transaction.atomic():

            save_point = transaction.savepoint() # 创建事务保存点
            try: # 若出现任何异常,则捕获,并回滚
                order_info_obj = OrderInfo.objects.create(
                    order_id=order_id,
                    user=user,
                    address=address,
                    total_count=total_count,
                    total_amount=total_amount,
                    freight=10,
                    pay_method=pay_method,
                    status=status
                )

                redis_conn = get_redis_connection('cart')
                cart_dict = redis_conn.hgetall('cart_{}'.format(user.id))
                selected_ids = redis_conn.smembers('selected_{}'.format(user.id))

                for sku_id_bytes in selected_ids:
                    sku_obj = SKU.objects.get(id=sku_id_bytes)
                    count = int(cart_dict['sku_id_bytes'])
                    
                    # 模拟多个用户同时提交
                    # import time
                    # time.sleep(10)
                    
                    if count > sku_obj.stock:
                        raise serializers.ValidationError('库存不足')

                    new_stock = sku_obj.stock - count
                    new_sales = sku_obj.sales + count
                    sku_obj.stock = new_stock
                    sku_obj.sales = new_sales
                    sku_obj.save()
                   
                    OrderGoods.objects.create(
                        order=order_info_obj,
                        sku=sku_obj,
                        count=count,
                        price=sku_obj.price
                    )

                   
                    order_info_obj.total_count += count
                    order_info_obj.total_amount += sku_obj.price * count

              
                order_info_obj.total_amount += order_info_obj.freight
                order_info_obj.save()

            except Exception:
                transaction.savepoint_rollback(save_point) # 回滚并触发异常
                raise serializers.ValidationError('库存不足')
            else:
                transaction.savepoint_commit(save_point) # 操作成功则提交事务

        return order_info_obj

用户同时下单引发的库存问题

• 若A用户和B用户同时下单,可能造成库存不足而多个用户却下单成功的场景

  • 比如同时查询了库存,此时库存充足,而A用户网速好,优先修改了库存量;
    由于B用户是同时查询,所以也会查询成功,然后和A用户一样的操作
    最终结果:库存15个,却卖出18个...商家自己填坑!

• 解决办法:有三种方式,悲观锁,乐观锁,任务队列

- 悲观锁: 当查询某条记录时，即让数据库为该记录加锁，锁住记录后其他人无法操作，使用类似如下语法

    select stock from tb_sku where id=1 for update;
    SKU.objects.select_for_update().get(id=1)

    - 缺点: 类似于我们在多线程资源竞争时添加的互斥锁，容易出现死锁现象(即忘记释放锁)
      而且用户只能等,用户体验并不好

- 乐观锁(推荐): 乐观锁并不是真实存在的锁，而是在更新的时候判断此时的库存是否是之前查询出的库存
  如果相同，表示没人修改，可以更新库存，否则表示别人抢过资源，不再执行库存更新

    update tb_sku set stock=2 where id=1 and stock=7
    SKU.objects.filter(id=1, stock=7).update(stock=2)

- 任务队列: 将下单的逻辑放到任务队列中（如celery），将并行转为串行，所有人排队下单
  比如开启只有一个进程的Celery，一个订单一个订单的处理

• 项目应用

......
def create(self,validated_data):

    user = self.context['request'].user
    order_id = timezone.localtime().strftime('%Y%m%d%H%M%S') + '0'*9 + str(user.id)
    address = self.validated_data.get('address')
    pay_method = self.validated_data.get('pay_method')

    status = (OrderInfo.ORDER_STATUS_ENUM['UNPAID']
              if pay_method == OrderInfo.PAY_METHODS_ENUM['ALIPAY']
              else OrderInfo.ORDER_STATUS_ENUM['UNSEND'])

    total_count = 0
    total_amount = 0
    freight = 10


    with transaction.atomic():

        save_point = transaction.savepoint() 
        try: 
            order_info_obj = OrderInfo.objects.create(
                order_id=order_id,
                user=user,
                address=address,
                total_count=total_count,
                total_amount=total_amount,
                freight=10,
                pay_method=pay_method,
                status=status
            )


            redis_conn = get_redis_connection('cart')
            cart_dict = redis_conn.hgetall('cart_{}'.format(user.id))
            selected_ids = redis_conn.smembers('selected_{}'.format(user.id))

            for sku_id_bytes in selected_ids:
                while True: # 让用户拥有无限次下单的机会
                    sku_obj = SKU.objects.get(id=sku_id_bytes)
                    count = int(cart_dict['sku_id_bytes'])
                    origin_stock = sku_obj.stock

             
                    # if count > sku_obj.stock:
                    if count > sku_obj.origin_stock:
                        raise serializers.ValidationError('库存不足')

                    new_stock = origin_stock - count
                    new_sales = sku_obj.sales + count
                
                    # 先查一遍是否符合原始数据,再修改
                    update_nums = SKU.objects.filter(stock=origin_stock,id=sku_id_bytes).update(stock=new_stock,sales=new_sales)
                    if update_nums == 0:
                        # raise serializers.ValidationError('资源抢夺') 
                        continue # 虽然库存被修改过,若库存量仍满足B用户的需求,就继续上述操作
                   
                    OrderGoods.objects.create(
                        order=order_info_obj,
                        sku=sku_obj,
                        count=count,
                        price=sku_obj.price
                    )

                   
                    order_info_obj.total_count += count
                    order_info_obj.total_amount += sku_obj.price * count
                    break # 订单完成,就退出循环

           
            order_info_obj.total_amount += order_info_obj.freight
            order_info_obj.save()

        except Exception:
            transaction.savepoint_rollback(save_point) # 回滚并触发异常
            raise serializers.ValidationError('库存不足')
        else:
            transaction.savepoint_commit(save_point) # 操作成功则提交事务

    return order_info_obj

MySQL事务隔离级别

• 定义: 指的是在处理同一个数据的多个事务中，一个事务修改数据后
  其他事务何时能看到修改后的结果

• MySQL数据库事务隔离级别主要有四种

- Serializable：串行化，一个事务一个事务的执行

- Repeatable read(默认设置)：可重复读，无论其他事务是否修改并提交了数据，
  在这个事务中看到的数据值始终不受其他事务影响

- Read committed：读取已提交，其他事务提交了对数据的修改后，本事务就能读取到修改后的数据值

- Read uncommitted：读取未提交，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值

• 使用乐观锁的时候，如果一个事务修改了库存并提交了事务，那其他的事务应该可以读取到修改后的数据值
  所以不能使用可重复读的隔离级别，应该修改为读取已提交（Read committed）

- windows系统下,修改"my.ini",文件末尾增加这句

    transaction-isolation=READ-COMMITTED

清除购物车中,已结算的商品

......
    def create(self,validated_data):
        ......
        # 清除购物车已结算商品
        pl = redis_conn.pipeline()
        pl.hdel('cart_{}'.format(user.id),*selected_ids)
        pl.srem('selected_{}'.format(user.id),*selected_ids)
        pl.execute()

        return order_info_obj