课程名称：当代数据管理系统	项目名称：bookstore-SQL
姓名：高宇菲	学号：10215501422

一，项目概述

本项目使用的是简化的MVC架构，实现了用户管理，买家购买和订单管理，卖家开店和订单处理，搜索图书和自动取消订单等功能。
本项目使用的是Postgresql数据库、python语言和psycopg2库来连接数据库；考虑到orm并不符合一般思维，时间较紧迫，没有使用这种实现方式。
二，项目运行

启动Postgresql服务，建立be数据库
确保fe/data下有book.db
在be/model/store.py修改数据库连接
运行以下命令

pip install requestments.txt
python app.py

三，数据库设计

3.1 ER图

在我的数据库设计中，共有四个对象（用户，商店，书籍，订单），没有多值属性；有两个多对多关系（库存，购买）。

3.2 从ER图导出关系模式

根据ER图，自然生成六张数据表，以下是数据表的字段和结构：

数据schema：

冗余：

book表中的_ts字段（用于搜索）
order表中的price字段。这个字段代表该订单所购买书籍的总价，可以通过订单信息计算出来。为了加速执行，提升用户体验，在新建订单的时候就顺便计算并存入数据库。

索引：

store库存表，order_book订单-书籍表（两个表示“联系”的表）使用双键索引，其余表都是ID索引。
没有使用额外的索引，原因是在建表时已经考虑到了查询等因素，故不建立其他索引也能有较好的表现。

3.3 与文档数据库相比，这个数据库的改动：

减少重复的书籍信息，进一步符合数据库规范。在文档数据库中，我们将书籍看做一“本”书，book_id和bookstore_id是复合索引，那么book_id和bookstore_id只要有一个不同，书籍的所以基本信息就要存一遍。关系数据库的设计中，book和bookstore是分离的，book表中仅包含一“部”书的信息，而bookstore表中仅包含商店ID和店主ID，“库存”关系是通过store表体现的。

这样做的好处：

减少重复存储书籍信息，因为不同的店可能上架相同的书籍，这样设计数据库，只有第一个上架一本书的卖家才需要插入book表，其他卖家可以跳过这一步。
**主体之间关系的存储方式不同。**文档数据库较灵活，可以存储列表、允许嵌套，所以可以在订单文档集中直接存一个列表，里面是所有的该订单购买的书籍；但是关系型数据库必须新建一张表。这是由数据库类型天然决定的。
顺便计算了订单总额，一劳永逸。

这样做的好处：

减少访问数据库的次数，提高用户体验；
及时记录下单时价格，防止之后价格变动引起业务错误。

增加了外键（如下图）

   ![image.png](https://cdn.nlark.com/yuque/0/2023/png/34343420/1703313384032-d3a63f1c-b77c-4348-9b10-e5ae11ecf260.png#averageHue=%23fcfbfb&clientId=u721df291-075f-4&from=paste&height=453&id=ufe1e97ed&originHeight=786&originWidth=794&originalType=binary&ratio=1.5&rotation=0&showTitle=false&size=60125&status=done&style=none&taskId=u5debeed9-d700-4524-80c5-229b775a0d0&title=&width=457.3333740234375)<br />增加外键约束的好处：

保证了业务正确性，一定程度避免脏数据
减少了存在性检查以及各种检查，提高了效率。

四，功能实现

4.1 前60%

前60%首先要实现数据库的连接和建表。建表的所有sql保存在create_be.sql文件中。在be/store.py中，检查be数据库是否为空，如果为空就执行create_be.sql，否则就不执行。

    def init_tables(self):
        try:
            cursor = self.database.cursor()
            cursor.execute(   # 判断数据库是否为空
                """
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = 'public' 
                    AND table_name = 'book'
                );
                """
            )
            table_exists = cursor.fetchone()[0]

            if not table_exists:
                with open('be/model/create_be.sql', 'r', encoding="utf-8") as file:
                    sql_commands = file.read()
                cursor.execute(sql_commands)

    def get_db_conn(self):
        return self.database.cursor()
	#  增加的
    def get_db(self):
        return self.database

考虑到psycopg2库的设计，后面需要psycopg2.connect对象来执行事务提交，所以我增加了一个get_db函数（如上），并在be/conn.py中调用：

# be/store.py
def get_db():
    global database_instance
    return database_instance.get_db()

# be/conn.py 中调用
class DBConn:
    def __init__(self):
        self.database = store.get_db()
        self.cursor = store.get_db_conn()

在be/conn.py中进行的用户、商店、书籍的存在性检查设计简单的SQL语句，不再赘述。

4.1.1 用户权限接口

view/auth.py中的接口，共5个，分别是注册，注销，登录，登出，更改密码。

注册

功能实现
1. 插入新用户 (user_id, password, balance, token, terminal) 到 users 表中
2. 如果用户存在，会触发psycopg2.error
性能分析：一次插入user表，访问数据库一次

登出

功能实现
1. 根据 user_id 在 users table 中查询，判断登录token是否失效
2. 更新用户 token，terminal
性能分析：一次查询 user表，一次更新 user表，访问数据库两次

注销

功能实现
1. 核对密码
2. 删除 users table中该用户条目
性能分析：一次查询 user表, 一次更新 user表，访问数据库两次

更改密码

功能实现
1. 根据 user_id 在 users table 中判断用户原先密码和用户新密码是否相同
2. 若相同，更新password，token, terminal
3. 若不同，则更新 users table 中该用户的 password
性能分析：一次查询 user表, 一次更新 user表，访问两次数据库

4.1.2 买家用户接口

因为使用了外键约束，之前所有的存在性检查全部汇集到psycopg2.Error 528, 所以增加一个错误：

error_code = {
    528: "数据库操作出错，请检查您提供的用户、商店或订单是否存在",
}
def error_database(e):
    return 528, e+error_code[528]

下单`new_order`接口

通过user_id，store_id，和唯一标识符相连生成uid，作为订单ID；
在order表中得到该商店的书籍库存和价格
对每一本书，若查询成功，那么就再检查是否库存充足；
若符合条件，就减少商店中的库存数量，并累加价格；
最后，在order表中插入以下信息, 其中设置30分钟内付款：

create_time = self.get_current_time()
pay_ddl = self.get_time_after_30_min() 
status = 0  # 0: 未支付, 1: 已支付未发货, 2: 已发货未收货, 3: 已收货, 4: 已取消
self.cursor.execute(
    'INSERT INTO "order"(order_id, user_id, store_id, create_time, pay_ddl, status, price) '
    "VALUES(%s, %s, %s, %s, %s, %s, %s);",
    (uid, user_id, store_id, create_time, pay_ddl, status, order_price),
)

性能分析：（1（插入新订单）+3*k（k是买书数量））次访问数据库

支付`payment接口`

获取购买的商店和价格
核对密码后，检查余额是否充足
若足够付款，就减少用户的balance, 相应增加卖家balance
最后修改订单状态信息；

性能分析：访问6次数据库

充值接口`add_funds`

从user表中查询密码并核对用户密码。
若密码正确，那么就在user表中更新用户余额；

性能分析：2次访问数据库

4.1.3 卖家用户接口

创建商店`create_store接口`

检查token
将store_id和user_id插入store表；

性能分析：访问两次数据库

商家图书`add_book接口`

实现以下功能：

解析并插入书籍信息到book表中；
将book_id,store_id, stock_level, price插入库存表中。

性能分析：访问两次数据库

添加库存`add_stock_level接口`

实现以下功能：

根据store_id、book_id增加stock_level。

性能分析：访问一次数据库

4.1.4 前60% 测试结果

Name                              Stmts   Miss Branch BrPart  Cover
-------------------------------------------------------------------
be\__init__.py                        0      0      0      0   100%
be\model\__init__.py                  0      0      0      0   100%
be\model\buyer.py                   113     24     44     10    75%
be\model\db_conn.py                  23      0      6      0   100%
be\model\error.py                    23      2      0      0    91%
be\model\seller.py                   55     14     24      1    73%
be\model\store.py                    32      3      0      0    91%
be\model\user.py                    119     23     38      6    76%
be\serve.py                          35      1      2      1    95%
be\view\__init__.py                   0      0      0      0   100%
be\view\auth.py                      43      0      0      0   100%
be\view\buyer.py                     37      0      2      0   100%
be\view\seller.py                    31      0      0      0   100%
fe\__init__.py                        0      0      0      0   100%
fe\access\__init__.py                 0      0      0      0   100%
fe\access\auth.py                    33      0      0      0   100%
fe\access\book.py                    70      1     12      2    96%
fe\access\buyer.py                   39      0      4      0   100%
fe\access\new_buyer.py                8      0      0      0   100%
fe\access\new_seller.py               8      0      0      0   100%
fe\access\seller.py                  31      0      0      0   100%
fe\bench\__init__.py                  0      0      0      0   100%
fe\bench\run.py                      13      0      6      0   100%
fe\bench\session.py                  47      0     12      1    98%
fe\bench\workload.py                125      1     22      2    98%
fe\conf.py                           11      0      0      0   100%
fe\conftest.py                       17      0      0      0   100%
fe\test\gen_book_data.py             49      1     16      1    97%
fe\test\test_add_book.py             37      0     10      0   100%
fe\test\test_add_funds.py            23      0      0      0   100%
fe\test\test_add_stock_level.py      45      0      8      0   100%
fe\test\test_bench.py                 6      2      0      0    67%
fe\test\test_create_store.py         20      0      0      0   100%
fe\test\test_login.py                28      0      0      0   100%
fe\test\test_new_order.py            40      0      0      0   100%
fe\test\test_password.py             33      0      0      0   100%
fe\test\test_payment.py              60      1      4      1    97%
fe\test\test_register.py             31      0      0      0   100%
-------------------------------------------------------------------
TOTAL                              1285     73    210     25    92%

4.2 后40%

后40%接口文档在doc/addtional_api.md(由APIfox导出) 中，在此不再占用篇幅。

4.2.1 发货 -> 收货

考虑到后续订单状态查询和取消的需求，设定order文档集中的state取值为以下四种。

state	0	1	2	3	4
含义	下单未付款	已付款未发货	已发货未收货	已收货	已取消

所以当卖家发货以及买家收货时，只需要修改相应order条目的state即可。但是需要注意的是，订单状态的修改是无法越级的（无法从状态0跳转到状态2）。

发货

根据订单号获取订单状态
检查订单状态是否为已支付
更新订单状态为已发货

收货

根据订单号获取订单状态
检查订单状态是否为已发货
更新订单状态为已收货

测试实现

测试正常情况
测试用户token失效的情况
测试订单状态错误的情况
测试订单号错误的情况

test_receive.py与test_send.py大同小异，在此不再赘述。

4.2.2 图书搜索

需求分析

搜索功能可以通过like语句实现，但是这样做有三个明显的缺点：

Seq Scan， O(n)复杂度。
包含了所有出现过关键词的数据，不能做到精确搜索。
当有多个关键词时，表达式较复杂，不利于维护。

因此，考虑使用全文索引。
下图是是否创建索引在100条数据上的小测试：

分析Postgresql支持情况

Postgresql的全文搜索一般解决方案是使用GIN索引+tsvector+tsquery。但经查阅资料和实际试验，Postgresql不支持中文的分词，仅支持空格和标点分词。所以不论何种语言，Postgresql都会按照空格分词，而这样的分词结果往往不是用户的搜索关键词。zhparser是常用的PG数据库中文分词插件，但是该插件对win系统并不友好。常用的中文分词库还有jieba。
我还发现Postgersql可以自动去除标点符号，这为我们提供了便利。

拟解决方案

使用结巴中文分词，对题目，标签，目录，内容进行分词后，使用空格分隔，另外存储到一个book表中名为_ts的条目，数据类型为tsvector**（这是一个冗余条目）**。在这个冗余条目上建立GIN索引。这个过程在卖家添加书籍时完成。

过程：

准备工作

a. 在建表时，book表多添加一个类型为“_ts”的字段，用于存储所有搜索源的分词；
b. 在建完book表后，加一个添加GIN索引的操作。

cursor.execute(
                "CREATE INDEX IF NOT EXISTS book_ts_idx ON book USING gin(_ts);"
            )

jieba分词并添加冗余属性

在卖家be/model/utils.py实现字段分词和汇聚函数cut，然后在seller添加书籍时，调用cut, 并和书籍信息一同插入book表。

self.cursor.execute(
    "SELECT * FROM book WHERE id = %s", (book_id,)
)
if self.cursor.fetchone() is None: 
    tsvec = cut(book_info)   # 返回一个空格分割的字符串
    self.cursor.execute(
        'INSERT into book(id, title, publisher, author, original_title, translator, pub_year, pages,currency_unit, binding, isbn, author_intro, book_intro, "content", tags, picture)'
        'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
        (
            book_id, 
            book_info['title'], 
            ..., 
            tsvec
        ),
    )

效果如下：

实现搜索

对搜索实现两个接口，分别是search_global, search_store;
search_global 接受三个参数，keyword，pageIndex(可缺省) ,pageSize(可缺省) ;search_store还要接受store_id;
首先，对用户输入的keyword分词处理成tsquery接受的格式，然后调用使用全文索引查询数据库：

key = jieba.cut(key)
key = " | ".join(key)
offset = (int(pageIndex) - 1) * int(pageSize)
self.cursor.execute(
    "select * from book where _ts @@ %s::tsquery LIMIT %s OFFSET %s",
    (key, pageSize, offset),
)

店内搜索还要检查店铺是否存在：

if not self.store_id_exist(store_id):
    return error.error_non_exist_store_id(store_id) + ([],)
key = jieba.cut(key)
key = " | ".join(key)
offset = (int(pageIndex) - 1) * int(pageSize)
self.cursor.execute(
    "select * from book where _ts @@ %s::tsquery LIMIT %s OFFSET %s",
    (key, pageSize, offset),
)

如果pageIndex为None或page为0，默认第一页；如果pageSize为None或page为0，默认5条数据。

效果展示

测试

对pageIndex和pageSize置空、小于1、非数字的情况都做了测试；对参数缺失情况做了测试；对会让非法搜索关键词也做了测试。

性能分析

全局搜索访问数据库一次，店铺搜索访问数据库两次，都可以视为复杂度。

4.2.3 订单搜索

这部分实现较简单，我的设计是如果搜索状态为-1，就搜索全部订单，否则搜索对应状态的订单。

if search_state == -1:
    self.cursor.execute(
        'SELECT * FROM "order" WHERE user_id = %s', (user_id,)
    )
else:
    self.cursor.execute(
        'SELECT * FROM "order" WHERE user_id = %s and status = %s',
        (user_id, search_state),
    )

4.2.4 订单取消

手动取消：

手动取消通过发送请求实现

根据订单号取出订单状态，如果订单状态已经是取消状态了，就直接返回200
如果不是，就相应地退款，还原库存。

自动取消：

自动取消目的是取消超时未支付的订单。可以新建一个线程实现，也可以在每次调用接口之前检查是否有超时。这里介绍多线程的解决方式：
创建文件autocancel.py：

from be.model.buyer import Buyer
def delete_order_time():
    buyer = Buyer()
    while True:
        buyer.delete_order_time()
if __name__ == '__main__':
    delete_order_time()

实现自动取消订单的功能可以在app.py中单开一个进程运行自动取消订单的程序autocancel.py，然后在运行app.run()

if __name__ == '__main__':
    p = multiprocessing.Process(target=subprocess.call, args=(["python", "auto_cancel.py"],))
    p.start()
    server.be_run()

4.3 总体测试结果

经过一番努力，**在保证业务逻辑和必要的错误捕捉的前提下，**将覆盖率提升到了95%。

Name                              Stmts   Miss Branch BrPart  Cover
-------------------------------------------------------------------
be\__init__.py                        0      0      0      0   100%
be\model\__init__.py                  0      0      0      0   100%
be\model\buyer.py                   215     31     64      5    84%
be\model\db_conn.py                  23      0      6      0   100%
be\model\error.py                    27      0      0      0   100%
be\model\seller.py                   75     14     26      2    80%
be\model\store.py                    31      3      2      1    88%
be\model\user.py                    115     23     30      2    77%
be\model\utils.py                    10      0      4      0   100%
be\serve.py                          35      1      2      1    95%
be\view\__init__.py                   0      0      0      0   100%
be\view\auth.py                      43      0      0      0   100%
be\view\buyer.py                     82      0      2      0   100%
be\view\seller.py                    40      0      0      0   100%
fe\__init__.py                        0      0      0      0   100%
fe\access\__init__.py                 0      0      0      0   100%
fe\access\auth.py                    31      0      0      0   100%
fe\access\book.py                    69      0     12      0   100%
fe\access\buyer.py                   71      0      2      0   100%
fe\access\new_buyer.py                8      0      0      0   100%
fe\access\new_seller.py               8      0      0      0   100%
fe\access\seller.py                  38      0      0      0   100%
fe\bench\__init__.py                  0      0      0      0   100%
fe\bench\run.py                      13      0      6      0   100%
fe\bench\session.py                  47      0     12      1    98%
fe\bench\workload.py                125      1     22      2    98%
fe\conf.py                           11      0      0      0   100%
fe\conftest.py                       17      0      0      0   100%
fe\test\gen_book_data.py             47      0     14      0   100%
fe\test\test_add_book.py             37      0     10      0   100%
fe\test\test_add_funds.py            23      0      0      0   100%
fe\test\test_add_stock_level.py      45      0      8      0   100%
fe\test\test_bench.py                 6      2      0      0    67%
fe\test\test_create_store.py         27      0      0      0   100%
fe\test\test_delete_order.py         59      0      2      0   100%
fe\test\test_login.py                28      0      0      0   100%
fe\test\test_new_order.py            40      0      0      0   100%
fe\test\test_password.py             33      0      0      0   100%
fe\test\test_payment.py              70      0      2      0   100%
fe\test\test_receive.py              59      0      2      0   100%
fe\test\test_register.py             31      0      0      0   100%
fe\test\test_search_global.py        54      0      0      0   100%
fe\test\test_search_order.py         67      0     10      0   100%
fe\test\test_search_store.py         59      0      2      0   100%
fe\test\test_send.py                 61      0      2      0   100%
fe\test\utils.py                     13      0      4      0   100%
-------------------------------------------------------------------
TOTAL                              1893     75    246     14    95%

五，实验心得

文档数据库和关系型数据库区别和优势比较：

关系数据库的优势：

**设计简单。**这次我几乎没有花时间在数据库设计上，因为对关系数据库来说，数据库结构是很明确的、固定的，不需要考虑吧关系存在哪一边。
**存储高度结构化。**相比于文档数据库的半结构化，关系型数据库使用表格结构存储数据，易于保持严格一致性和完整性。
**事务处理。**本次实验利用了Postgresql的事务处理，确保数据的一致性、隔离性、持久性和原子性（ACID属性）。这在我们的在线交易活动中非常重要。
外键和级联操作。在关系型数据库中，可以容易地建立外键，可以保证数据的一致性和完整性。

关系数据库的劣势：

**不够灵活。**我使用两个数据库后，最大的感觉是关系数据库不够灵活。所有的主键、外键、数据类型以及各种规则都要在一开始明确定义好；而且不能像文档数据库一样嵌套，有时为了得到一个字段值，就多增加一次访问开销。
**不适合存图片等格式的文件。**关系数据库的设计就不是用来存大文件的；在里面存大文件会让数据库大小激增，导致查询速度下降。最好是存一个图片url，图片存在其他服务器中。

认识到团队合作的重要性。和上次作业对比，这种开发任务最好还是团队分工合作效率更高。

六，实验亮点

为了方便助教老师查阅，这里列出了实验的完成度和可加分项：

使用了版本控制，有较好的版本控制规范：+3

以下是仓库提交截图，可以看到我每实现一个功能就提交一次，版本控制给我的开发提供了便利，允许我大胆实践自己的猜想，不担心丢失原来的版本。

这是github地址：https://github.com/gyfffffff/BookStroe-SQL

使用了测试驱动开发，以下是测试驱动开发的流程：

TDD的优势：

a. 更高的软件质量：在编写代码之前编写测试用例，有助于捕获和修复潜在的问题和缺陷，编写更稳健更可靠的软件代码。
b. 更好的文档和示例：在编写代码之前，首先通过需求编写后端接口文档，然后根据文档编写测试用例。该单元测试描述了每个功能的预期行为，快速理清逻辑。
c. 增量开发：TDD通过小步骤进行迭代开发，逐渐构建功能，避免了开发后期修复大量问题的发生。
在前60%中，我根据项目的已有的单元测试进行开发。在后40%中，我先确定功能需求，编写单元测试用例，然后根据测试用例进行新功能的开发。 +2

有较高覆盖率：在不牺牲必要的错误捕捉前提下，精简代码，去除冗余，得到了95%的覆盖率。+5
实现完整度：前60%和后40%的功能全部实现，测试全部通过；并对每个接口分析了访问次数。 +1
正确地使用数据库和设计分析工具： +1

ER 图
从ER图导出关系模式
规范化，事务处理，索引这些在数据库设计和功能实现时都有体现。

从文档型数据库到关系型数据库的改动，以及改动的理由在上面已经写过。

七，总结

本次实验我专注于后端开发和测试，

对关系数据库和文档数据库的区别有了更深入的理解，
加深了对pytest使用上的理解。
巩固了SQL的使用
积累了一定实践经验和debug经验，

为今后学习打下基础。实验中遇到的困难大多予以解决。最后，程序通过了66个测试，覆盖率达 95%（不考虑except出现的严重数据库错误，覆盖率达到了 **97% **），取得了较为满意的结果。

gyfffffff / bookstroe-sql Goto Github PK

bookstroe-sql's Introduction