# Website backend of online book purchase function based on Python

2022-05-15 02:58:16

## One . The experimental requirements

Realize a website backend that provides the function of online book purchase .
The website supports booksellers to open stores on it , Buyers may buy through the website .
Both buyers and sellers can register their own accounts .
A seller can open one or more online stores , Buyers can recharge their accounts , Buy books at any store .
Support ordering -> payment -> deliver goods -> Receiving goods , technological process .

1. Realize the function of the corresponding interface , see doc Below .md File description （60% fraction ）

These include ：

1) User authority interface , If registered 、 Sign in 、 Log out 、 Cancellation

2) Buyer user interface , If recharge 、 Place an order 、 payment

3) Seller user interface , Such as creating a store 、 Fill in the book information and description 、 Increase inventory
Pass the corresponding function test , all test case all pass
Test the performance of order and payment interfaces （ It's best to separate load generation and backend ）, Measure the number of transactions supported per minute , Delay, etc

2. Add other functions to the project ：（40% fraction ）

1) Realize the subsequent process
deliver goods -> Receiving goods

2) Search for books
Users can search by keyword , Parameterized search method ; If the search scope includes , subject , label , Catalog , Content ; Full site search or current store search . If the display result is large , Paging required ( Use full-text indexing to optimize lookup )

3) The order status , Order inquiry and cancellation
Users can check their own historical orders , The user can also cancel the order .
Cancel the order （ optional , Bonus points +5~10）, The buyer voluntarily cancels the order , If the buyer places an order after a time-out , If the buyer fails to pay , The order will also be automatically cancelled .

## Two . Project operation

You need to initialize the database before running the project .

stay MongoDB Create a file named bookstore The database of . Then create two document sets , The document set names are book and history_order, And according to the following 3.5.4 in MongoDB Part of the index .

PostgreSQL The initialization of the database is already in store.py Establish a good , There is no need to manually create .

Then enter the following statement to run the project .

python app.py


Or according to seven . The steps in the cloud on the project run the project using the cloud database .

## 3、 ... and . Database design

### 3.1 Overall design concept

The following design is based on the following reasonable assumptions ,

1. Users often place orders, pay and cancel
2. Users often make mistakes, but they usually don't make mistakes deliberately
3. Relative to the seller , There are more buyers and their rights and interests should be protected
4. Users don't know about books when searching

In order to ensure the system function , And try to improve the performance of the system , This project ER The diagram and database design are as follows .

The optimal design of database is in 3.5 Section .

### 3.3 relational database

Most of the content in the project is stored in Postsql In a relational database . Next, we will introduce the relational database .

After many tests , Added foreign keys 、 only 、 After non empty constraints, the database performance is greatly reduced . So while the program is running , Use code and error handling to ensure the correctness and rationality of the data in the database . For the database itself, no constraints are explicitly added . This can ensure the high performance of the database .

#### 3.3.1 users table

• Design thinking

users Table is used to store user entity classes and their attributes . Each row in the table corresponds to a user , Its basic properties are recorded .

• Table structure

user_id：string type , It is the only primary key of this table . Used to record user name .

password：string type . A secret text used to record a user's password . For security reasons , The database does not store plaintext passwords .

balance：integer type , The initial value is 0. Used to record the amount in the user's account .

token：string type . Used to record the user name at login 、 Mark generated by time and terminal number . When carrying out important operations , This attribute needs to be checked to determine that the message comes from the same terminal .

terminal：string type . It is used to record the terminal number at login .

#### 3.3.2 user_store table

• Design thinking

user_store Table is used to store store store entity classes and store contact classes . Each line in the table is a store .

• Table structure

user_id：string type . Used to record the user name of the owner of the store .

store_id：string type , Is the primary key of this table . Used to record store name .

#### 3.3.3 store table

• Design thinking

This table is used to store the inventory information of the bookstore , Each row in the table represents the inventory information of a book in a store .

• Table structure

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-Hk4qjwkw-1652166156113)(https://www.writebug.com/myres/static/uploads/2022/4/25/f4a1898816fe188be3b018337e58f296.writebug)]

store_id：string type , Is one of the primary keys of this table . Used to record the name of the store where the book is located .

book_id：string type , Is one of the primary keys of this table . Used to record the corresponding books id.

stock_level：integer type . Used to record the number of books in stock .

price：integer type . Used to record the unit price of books . In different stores , The unit price of books can be different .

#### 3.3.4 new_order table

• Design thinking

The form stores information about the order , Each record records information not related to books in the order .

• Table structure

order_id：string type , Is the primary key of this table . The order number of the order is recorded .

user_id：string type . The user name of the order buyer is recorded .

store_id：string type . The store name of the merchant who recorded the order .

status：integer type . The status of the order is recorded , The default value is 1. among 1 Indicates that an order has been placed but no payment has been made ,2 Indicates paid but not shipped ,3 On behalf of shipped but not received ,4 Indicates that the goods have been received ,0 Indicates that the order has been cancelled .

total_price：integer type . The total price of the order is recorded .

order_time：integer type . The order placing time of the order is recorded , In the form of a timestamp . Used for automatic cancellation and other businesses .

#### 3.3.5 new_order_detail_table

• Design thinking

The form stores details related to the order and book purchase contact class , Each record records information about the purchase of a book in an order .

• Table structure

order_id：string type , Is one of the primary keys of this table . The order number of the order is recorded .

book_id：string type , Is one of the primary keys of this table . Used to record the corresponding books in the order id.

count：integer type . Record the quantity of this book purchased in this order .

#### 3.3.6 invert_index table

• Design thinking

invert_index table Inverted table is used to store the index relationship between keywords and books . The specific construction and search functions are described in detail .

• Table structure

search_key：string type , One of the primary keys of the inverted table . Records the primary key of the inverted table , When the user enters the contents of the inverted table , This table can return the information of the bibliography found .

search_id： Sequence type , One of the primary keys of the inverted table . According to key Insert in turn to generate self increasing integer Sequence . Used for page query .

book_id：string type . Records the corresponding books id, Is one of the return values of the inverted table .

book_title：string type . The title of the corresponding book is recorded . Return as a redundant attribute for buyers to view .

book_author：string type . The author of the corresponding book is recorded . Return as a redundant attribute for buyers to view .

### 3.4 Document database

#### 3.4.1 book collection

• Design thinking

book collection Used to store book information , Each document object stores information about a Book .

• Table structure

id：string type . Of the record book id, Every book id only .

title：string type . The title of the record book , Theoretically, it cannot be empty .

author： Most of them are string type , It can be for null. The author of the record book .

publisher：string type . The publisher of the record book .

original_title： Most of them are null, Non null value is string type . Record the original title of the foreign language book .

translator： Most of them are null, Non null value is string type . The translator who records foreign language books .

country： Most of it is empty , Non null value is string type . Record the nationality of the author of the foreign language book .

pub_year：string type . Date of publication of the record book .

pages：Int32 type . The number of pages in the record book .

currency_unit：string type , Can be an empty string . The monetary unit of record price . Most of them are “ element ”.

isbn：string type . Of the record book ISBN Number .

author_intro：string type , Can be an empty string . A brief introduction to the author of the record book .

book_intro：string type , Can be an empty string . Introduction to the record book .

content：string type , Can be an empty string . Catalogue of record books .

tags：array type , Each item is a string . Labels and keywords of the record book .

pictures：array type , Each item is a picture . Pictures of the record book .

#### 3.4.2 history_order collection

• Design thinking

history_order collection Used to store historical orders , Each document object records an order record .

• Table structure

order_id,user_id,store_id,status,total_price,order_time： Record information and format PostgreSQL The data in the database is exactly the same . Specially ,status The value can only be 0 and 4, That is, cancelled and received . This part of the information of the order in progress is saved in new_order in .

books： An array type , Each of the items in the array is a child document . There are... In the sub document book_id and count two , It records the book information in the order . Record information and format PostgreSQL The data in the database is exactly the same . This part of the information of the order in progress is saved in new_order_detail in .

### 3.5 Relational schema optimization

#### 3.5.1 Splitting and merging of tables

Due to the large number of orders , These are mainly historical orders , The number of orders in progress accounts for a very small part of the total number of orders . So split the order table , Divided into completed orders and unfinished orders . Two completed orders are used SQL Table to record , And the unfinished order uses a MongoDB Document set to record .

For outstanding orders , Because he needs to update the status at any time , And it involves automatic cancellation and other operations . If put together with historical orders , It will increase the complexity of the query . And for these orders , What is often visited is the total price , Information such as status , And not often visit new_order_detail Book information in , And often new_order Change the status in , So we decided to use SQL To store .

Only completed historical orders will hardly change , It is mainly used to show users' historical orders , This way is very suitable for directly recording with one document . And another task of taking out historical orders is to make recommendations , Recommendation is the information needed, the information of the books purchased , If you still use SQL The way of storage , You need to take out many book records in the order each time , Lead to problems such as low efficiency . So the historical order adopts mangoDB To store .

#### 3.5.2 Redundant attributes are introduced

In this project , The most important redundant attribute is the attribute in the inverted table . In the inverted list search_key The keyword records the title of the book 、 author 、 label 、 Introduce the inverted information of keywords and other information , Using this table can speed up the search for books . If the inverted table only provides book_id, Do not add the title and author attributes of redundant books in the database . Users can only use the ID Look it up again MongoDB Book information in , This is not only inefficient , And not in line with user habits . Therefore, these two important information for books are added to the inverted table as redundant attributes , Is in line with the user's search habits , And it improves efficiency .

Besides , The total order price as the derivation attribute is also added to the order table . This is because incomplete orders need to access the total price of the order during payment and receipt . If the information in other tables is used for field calculation every time , It will cause a great waste of resources .

Although redundant information is added to this database , Part of the paradigm is still good . Remove inverted index , The tables of this project conform to BCNF.

#### 3.5.4 Index creation

This project involves two kinds of databases , Respectively Postgresql and MongoDB

##### 3.5.4.1 Postgresql

Only the primary key index is used , No other index is used . The reason is that query and other factors have been taken into account when creating tables , Therefore, it can have better performance without establishing other indexes .

##### 3.5.4.2 MongoDB

In order to speed up the business , about MongoDB Two tables of , We indexed it

##### Index1(B-tree Indexes ): Book information sheet

Reason for adding index ：id The index is used to speed up the query business ,author、publisher、 as well as tags The index is used to improve the speed of recommending Services .

##### Index2(B-tree Indexes ): Historical order information table

Reason for adding index ：user_id The index is used to speed up the query of buyer history information ,user_id The index of is used to speed up the query of seller's historical information . At the same time, they can indirectly accelerate the speed of recommendation business .

## Four . Function function

#### summary

We have achieved auth, seller, buyer Business logic of three roles , The performance optimization and corresponding interface test are completed .

Features include registration , Sign in , Books on the shelves, etc Basic function ; Receipt and delivery , Manually cancel the order , Check the order （ At present / history ）,（ Multiple keywords ） Search, etc advanced function ; Besides , We have realized automatic cancellation of orders , Search for books , real time OCR Search for , Small recommendation system , In store search, etc Expand functions .

In terms of performance improvement , We took RETURNING keyword Reduce unnecessary queries , Corresponding MongoDB Operation for FindOneAndUpdate. meanwhile , introduce **“ Soft real time ”** Concept to reduce the resource consumption of multithreading .

The following is the of this project Highlight features

4.1.8 Recommendation system / 4.2.2 Books on the shelves / 4.2.5 Query the current order / 4.2.6 Query historical orders / 4.3.2 Place an order / 4.3.3 payment / 4.3.6 Cancel the order automatically / 4.3.7 Search for / 4.3.8 Multi keyword search / 4.3.9 Search for books / 4.3.10 real time OCR Search for / 4.3.11 Get book information / 4.3.12 Search in the store

### Transaction processing （ Bright spot ）

This project has made relatively perfect transaction processing , stay UPDATE and DELETE And we used that RETURNING keyword , Improved efficiency , But there are still the following possibilities ： After updating or deleting , After taking out the data , The system may judge that the operation cannot be completed according to the extracted data , At this point... Must appear Roll back rollback. The important functions of this project are As a business complete , At the end of the transaction , perform commit Submit . We are interested in basic functions , Advanced functions and so on have carried out transaction processing , Ensure the normal operation of the project .

### 4.1 User rights function

#### 4.1.1 register

• Function realization

1. according to user_id stay users Query whether the user already exists in the table
2. If it does not exist , Then insert a new user (user_id, password, balance, token, terminal) To users In the table
• Performance analysis

A query users table, Insert... Once users table, Access the database twice

• Function realization

1. according to user_id stay users Get the password from the table password
2. Will get password Compare with the password entered by the user
3. Update user's token, terminal
• Performance analysis

A query users table, An update users table, Access the database twice

#### 4.1.3 Log out

• Function realization

1. according to user_id stay users table Query in , Judge whether the login information is invalid
2. Update user token
• Performance analysis

A query users table, An update users table, Access the database twice

#### 4.1.4 Cancellation

• Function realization

1. according to user_id stay users table Query whether the user exists
2. Delete users table The user entry in
• Performance analysis

A query users table, An update users table , Access the database twice

• Function realization

1. according to user_id stay users table Query the user's original password
2. Judge whether the user's original password is the same as the user's new password
3. If different , Update users table Of this user in password
• Performance analysis

A query users table, An update users table, Access the database twice

#### 4.1.6 Query the current order

• Function realization

1. according to user_id stay new_order table Query all order information of users in
2. For each of these order_id stay new_order_detail Query book information in
• Performance analysis

A query new_order table , A query new_order_detail table, Access the database twice

#### 4.1.7 Query historical orders

• Function realization

1. according to user_id stay MongoDB history_order collection Query all order information in
• Performance analysis

A query MongoDB history_order collection, visit MongoDB Database once

#### 4.1.8 Recommendation system

Recommendation system is one of the features of this experiment , It's more complicated to realize , The complexity of execution is also relatively high . This recommendation system uses the user's historical order information , Personalized recommendation for users . The recommendation system will recommend the user and the user's historical purchase label Jarcard Recommend books with high similarity .

• Function realization

1. First we start with MongoDB Find out the user's historical orders , These include books purchased by users , Take it out ID、 title 、 author 、 label 、 And publishers . The recommendation weight of books that have been purchased will be set to the lowest .
2. Use this information to find books similar to the book . from MongoDB In the database, find out the same author of a Book purchased by the user in turn 、 Or the same publishing house 、 Or other books with the same label . In the process, the books that have been taken out are removed , Take out the spare label, etc .
3. Calculate the value of all the books taken out and the user's historical purchase labels Jarcard Similarity degree ( J a r c a r d ( A , B ) = ∣ A ∩ B ∣ ∣ A ∪ B ∣ ) (Jarcard(A,B)=\frac{|A\cap B|}{|A\cup B|}) And sort , Take out top5 And return the book information to the user .
• Performance analysis

The recommended function requires Visit multiple times MongoDB database . The number of visits to the database is positively correlated with the number of historical orders and the number of books purchased . Although each query will use the index established on the document set , However, due to the complexity of query , The cost of time is still high . Under the current recommended algorithm , If you don't use databases and indexes , Need to traverse every book , With O ( n 2 ) O(n^2) Time complexity or space complexity are recommended . Therefore, reasonable use of database and establishment of appropriate index can reduce the complexity .

eachbook = self.mongo['book'].find_one({
'id': boughtbook['book_id']},{
'_id': 0, 'id': 1, 'title': 1, 'author': 1, 'tags': 1, 'publisher':1})

books = self.mongo['book'].find({
'$or': [{ 'author': eachbook['author']},{ 'publisher': eachbook['publisher']},{ 'tags': { '$elemMatch': {
'$in': eachbook['tags']}}}]},{ '_id': 0, 'id': 1, 'title': 1, 'author': 1, 'tags': 1})  （ The next highlight feature is 4.2.2） ### 4.2 Seller function #### 4.2.1 Create a store • Function realization 1. Respectively in users table and store table Query in users_id and store_id Does it already exist 2. If it does not exist , Insert user user_id and store_id To user_store table • Performance analysis A query users table, A query store table, Insert... Once user_store table, Access the database three times #### 4.2.2 Books on the shelves • Function realization 1. Check user_id,store_id,book_id Whether there is 2. according to book_id stay Mongodb Of book collection Query in book Whether there is . If exist , Then there is no need to add MongoDB Medium book Document set . If it does not exist , Then sort out the data according to the following steps . 3. take book_json_str Convert to dictionary format . First match the author string according to the parentheses , Separate the author's name and nationality . This is to prevent the brackets and nationality included at the beginning of the author's name from making the inverted list nonstandard . 4. Then extract the Book Introduction 、 Author's brief introduction 、 Keywords in the directory . utilize jieba In the participle textrank Take out the key words , Remove the weight and add the label to the book . 5. Create an inverted table , Break the title of the book into words , Form a suffix according to the word segmentation results , Separate the prefix of each suffix and add it to the inverted table as a keyword , The author of the book 、 Similar treatment for labels, etc , Insert this information into the inverted table invert_index table. 6. take (store_id, book_id, stock_level, price) Insert store table • Performance analysis A query users table, A query user_store table, A query store table, A query Mongodb Of book collection, Insert... Once store table, Insert invert_index table Several times , Access the database at least five times  # --- Join the inverted index and start --- # Put the title of the new book 、 author 、 The label is added to the inverted index table preffixs = [] title = book_info_json.get("title") preffixs += nlp.get_middle_ffix(title) if "author" in book_info_json.keys(): names = parse_name(book_info_json.get("author")) for i in range(1,len(names)): preffixs += nlp.get_preffix(names[i]) preffixs += nlp.get_preffix(book_info_json.get("author")) if "original_title" in book_info_json.keys(): preffixs += nlp.get_preffix(book_info_json.get("original_title")) if "translator" in book_info_json.keys(): names = parse_name(book_info_json.get("translator")) for i in range(1, len(names)): preffixs += nlp.get_preffix(names[i]) preffixs += nlp.get_preffix(book_info_json.get("translator")) preffixs = list(set(preffixs)) for preffix in preffixs: self.conn.execute( "INSERT into invert_index(search_key, book_id, book_title, book_author) " "VALUES (:sky, :bid, :til, :asr)", { 'sky': preffix, 'bid': book_id, 'til': title, 'asr': author}) # --- End of adding inverted index ---  （ The next highlight feature is 4.2.5 and 4.2.6） #### 4.2.3 Add inventory • Function realization 1. Check user_id, store_id,book_id Whether there is 2. according to store_id,book_id Look for the stock of a book in the store , And in store table In the update • Performance analysis A query users table, A query user_store table, A query store table, An update store table, Access the database four times #### 4.2.4 Seller delivery • Function realization 1. Check store_id,book_id Whether there is 2. according to order_id stay new_order table Update order status in • Performance analysis A query user_store table, A query store table, An update new_order table, Access the database three times #### 4.2.5 Query the current order • Function realization 1. according to seller_id stay new_order and user_store table Query all order information in 2. For each of these order_id stay new_order_detail Query book information in • Performance analysis A query user table , A connection query new_order and user_store table, Multiple queries new_order_detail table, Access the database at least three times . Because join queries use primary keys , So the performance is not very poor .  cursor = self.conn.execute( "SELECT o.order_id, o.store_id, o.status, o.total_price, o.order_time " "FROM new_order o, user_store s " "WHERE s.user_id = :user_id AND s.store_id = o.store_id ", { "user_id": seller_id, })  #### 4.2.6 Query historical orders • Function realization 1. according to store_id stay MongoDB history_order collection Query all order information of the store • Performance analysis A query MongoDB history_order collection, visit MongoDB Database once orders = self.mongo['history_order'].find({ 'store_id': store_id}, { '_id': 0})  （ The next highlight feature is 4.3.2 and 4.3.3） ### 4.3 Buyer function #### 4.3.1 Recharge • Function realization 1. according to user_id Get the user password 2. Compare the user password with the password entered by the user 3. If the passwords match , Update the user in users table Balance in • Performance analysis A query users table, An update users table, Access the database twice #### 4.3.2 Place an order • Function realization 1. Check user_id, store_id Whether there is 2. According to the order information （store_id,book_id） stay store Check whether there are corresponding books and sufficient inventory in the merchant in the table . 3. If stock is sufficient , Update store table stock . At this time, we found that , The only information that needs to be retrieved is price The unit price . Inventory attributes can be judged when updating , If there is no qualified line , It will not be updated . thus , We can find that using a select Statements in store The query efficiency is low when executing in the table , Because there is an update statement for the same line after . At this time, we can use RETURNING Keyword will directly return the updated result . Here is the return price I.e. unit price . In this way, through an update operation and RETURNING Keyword to achieve the effect of accessing the database twice , This will reduce and save time . When cancelling or completing an order DELETE The operation also uses RETURNING keyword . 4. Create new order information , take (order_id, book_id, count, price) Insert new_order_detail table 5. Create this order , Calculate the total price of the order total_price, take (order_id, store_id, user_id, total_price, order_time) Insert new_order table, At the same time, the order number order_id Add to unpaid_order Array  cursor = self.conn.execute( "UPDATE store set stock_level = stock_level - :count " "WHERE store_id = :store_id and book_id = :book_id and stock_level >= :count " "RETURNING price", { "count":count, "store_id":store_id, "book_id":book_id, "count":count})  • Performance analysis A query users table, A query user_store table, An update store table, Insert... Once new_order_detail table, Insert... Once new_order table, Access the database five times . And no use RETURNING Less than one query store table. #### 4.3.3 payment • Function realization 1. according to order_id stay new_order table Query order information in 2. Check whether the order timed out 3. If the order does not time out , According to buyer_id stay users table Get the buyer's balance and password 4. according to store_id stay user_store table Query the seller seller_id 5. stay users table Update the buyer's balance in 6. stay new_order table Update order status in status=2 • Performance analysis A query new_order table, A query users table, A query user_store table, An update users , An update new_order table, Access the database five times . This business speeds up execution by deleting unnecessary queries . （4.3.6 And later are the highlights of the project , I hope you take the time to check ） #### 4.3.4 The buyer receives the goods • Function realization 1. according to order_id stay new_order table The status of the corresponding order in the query , buyers id 2. Check whether the order status is shipped , Order id With the buyer id Whether it corresponds to 3. If the conditions are met , Then update the seller's balance , Update order status to received and add history • Performance analysis A query new_order table, A query user_store table, Two updates users table, A query users table, Access the database five times #### 4.3.5 Manually cancel the order • Function realization 1. according to order_id stay new_order table Query whether the order status is placed 2. Check if the user exists , Whether the order number exists 3. From the overall situation unpaid_orders Delete the order from the dictionary 4. according to order_id from new_order table Delete the order in , from new_order_detail table Delete order information in 5. according to store_id and book_id Roll back inventory stock_level 6. Add order information to MongoDB Historical record history_order collection in cursor = self.conn.execute( "DELETE FROM new_order WHERE order_id = :order_id RETURNING order_id, user_id, store_id ,total_price, order_time ", { "order_id": order_id, }) cursor = self.conn.execute( "DELETE FROM new_order_detail WHERE order_id = :order_id RETURNING book_id, count ",{ "order_id": order_id, })  • Here we take RETURNING Unnecessary keyword queries are reduced (1) At the same time SELECT and UPDATE operation (2) Act on INSERT,UPDATE,DELETE (3) DELETE Return the data before the operation ,INSERT,UPDATE Data returned after operation Corresponding MongoDB operation ：FindOneAndUpdate • Performance analysis A query new_order, Delete... At a time new_order table, Delete many times new_order_detail , An update store table, Insert... Once MongoDB history_order collection, Access the database at least five times #### 4.3.6 Cancel the order automatically Automatic cancellation of orders uses a “ Soft real time ” Thought • Function realization 1. Maintain a global dictionary , Used to record the start time of each order （ Order time ） 2. Use datetime Wait for the time tool to record the time when the user places an order , Stored in the global list 3. utilize Apscheduler Scheduler , Achieve each 30 Check the status of orders in the global list once a minute 4. If the order does not time out , Don't do anything , If timeout is detected , Cancel this order . When users place an order , Will reconfirm the payment time . • Performance analysis It's a kind of similarity “ Soft real time ” Order status maintenance method based on , The overhead of the timeout cancellation function can be greatly reduced . Not dedicated to a thread “ Stare ” User's order , Instead, check every cycle , Cancel the order that is checked to be overtime . When users place an order , Will check again for timeout , If the time is out, the order will be cancelled directly . Realization way ： time_limit = 30 # Order lifetime unpaid_orders = { }  Use a global dictionary to record the order time status , And set the maximum lifetime of unpaid orders # advantage ： By maintaining the global array to_be_paid, No additional new threads , Minimize the cost def add_unpaid_order(orderID): unpaid_orders[orderID] = get_time_stamp() print("add successfully") print(unpaid_orders) return 200, "ok" def delete_unpaid_order(orderID): try: unpaid_orders.pop(orderID) print(unpaid_orders) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok" def check_order_time(order_time): cur_time = get_time_stamp() time_diff = cur_time - order_time if time_diff > time_limit: return False else: return True  Whenever the cycle of deleting a task comes , Just perform a probe on the global dictionary , Change the status of orders not paid at the specified time to “ Cancel ” def time_exceed_delete(): del_temp=[] o = Order() print("new cycle start") for (oid,tim) in unpaid_orders.items(): if check_order_time(tim) == False: del_temp.append(oid) # remenber, not to append the index of the array, we need the orderID for oid in del_temp: delete_unpaid_order(oid) o.cancel_order(oid) return 0  By configuring a apscheduler Scheduler to perform scheduled tasks class Config(object): JOBS = [ { 'id': 'soft_real_time', 'func': '__main__:time_exceed_delete', 'trigger': 'interval', 'seconds': 30,#30s For testing purposes only , In actual use, it should be set to 30mins } ]  #### 4.3.7 Search for The search function is mainly based on the inverted table , And there are two forms of paged search and non paged search . • Function realization 1. First read the search keywords from the user, whether pagination and page number are needed , Such parameters page Is equal to zero , No paging . Such as page If it is equal to other numbers, it is the page number that the user needs to find . 2. If pagination is not required , Then find out the keyword corresponding to all books from the inverted table ID、 Title and author , And organize it into an appropriate form and return it to the user . 3. Pagination if necessary , First use SQL In the keyword order by to search_id Ascending sort 、 Reuse limit Limit the number of results per page , Last use offset The keyword offset takes out the query result of the corresponding page . Sort it out into an appropriate form and return it to the user .  cursor = self.conn.execute( "SELECT book_id, book_title, book_author from invert_index " "where search_key = '%s' " "ORDER BY search_id limit '%d' offset '%d';" % (search_key, self.page_size, page_lower))  • Performance analysis Due to the existence of inverted table , Single keyword search only needs Access the database once , And because search_key and search_id Is the primary key of the inverted table , Cluster index is built on , So the query speed is very fast . #### 4.3.8 Multi keyword search The result of multi keyword search query is the union of single keyword search results . • Function realization 1. First read the keyword list of this search from the user . 2. Single keyword search for each keyword , The results are stored in the list . At the same time, the dictionary is used to realize de duplication . 3. Organize the de duplication results into appropriate forms and return them to the user . • Performance analysis Multi keyword search requires access to X Secondary database ,X Is the number of keywords . Because the number of keywords is often limited , It can be said that visit O(1) An order of magnitude database . #### 4.3.9 Search for books • Function realization 1. We called Baidu OCR Of API To search for books , The specific operation method is as follows （ Take probability theory and mathematical statistics as an example ）, We try to call OCR Function to retrieve this book in the library . 1. Let's go through OCR Technology to extract text from pictures , Return... As a text message , Re pass TextRank The algorithm extracts information from text key word , Include title,author Etc , Take this as key stay invert_index Inversion lists Search in , Single keyword search only needs Access the database once , Get the book name and related information , And because search_key and search_id Is the primary key of the inverted table , Cluster index is built on , So the query speed is very fast . • TextRank Extract keyword results 1. Definition OCR Method  def OCR_pic(self, path): try: print(path) image = get_file_content(path) # Invoke universal literal recognition , The picture is a local picture res = client.general(image) print(res) text = [] for item in res['words_result']: print(item['words']) text.append(item['words']) print(text) text_Seg = [] text_len = len(text) doc = "" for i in range(0, text_len): doc += text[i] print(doc) sentence_Seg = ana.textrank(doc) # sentence_Seg = str(sentence_Seg) # sentence_Seg = sentence_Seg.strip(',') print(sentence_Seg) b = Buyer() result = b.search_many(sentence_Seg) except sqlalchemy.exc.IntegrityError as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok", result  • Test and test results You can use it directly postman Wait for the test tool to upload pictures , Extract cover text , Search after word segmentation , Return book information . #### 4.3.10 real time OCR Search for **Remark： Camera function is required （ It's best to have plenty of light ) ** APP_ID = '14544448' API_KEY = 'yRZGUXAlCd0c9vQj1kAjBEfY' SECRET_KEY = '**********************' # initialization AipFace object client = AipOcr(APP_ID, API_KEY, SECRET_KEY)  You need to get an authorization yourself , To call Baidu OCR Of API class OCR(db_conn.DBConn): def __init__(self): db_conn.DBConn.__init__(self) def OCR_pic_cv(self): try: # Get photo saveDir = 'data/' ''' Call the computer camera to automatically get pictures ''' if not os.path.exists(saveDir): os.makedirs(saveDir) count = 1 # Picture count index cap = cv2.VideoCapture(0) width, height, w = 640, 480, 360 cap.set(cv2.CAP_PROP_FRAME_WIDTH, width) cap.set(cv2.CAP_PROP_FRAME_HEIGHT, height) crop_w_start = (width - w) // 2 crop_h_start = (height - w) // 2 print('width: ', width) print('height: ', height) ret, frame = cap.read() # Get photo frame frame = frame[crop_h_start:crop_h_start + w, crop_w_start:crop_w_start + w] # Show the picture frame # frame=cv2.flip(frame,1,dst=None) cv2.imshow("capture", frame) action = cv2.waitKey(1) & 0xFF time.sleep(3) cv2.imwrite("%s/%d.jpg" % (saveDir, count), cv2.resize(frame, (224, 224), interpolation=cv2.INTER_AREA)) print(u"%s: %d A picture " % (saveDir, count)) count += 1 cap.release() # Release camera cv2.destroyAllWindows() # Drop window #ocr Picture get picture text path='./data/1.jpg' image = get_file_content(path) # Invoke universal literal recognition , The picture is a local picture res = client.general(image) print(res) result = [] for item in res['words_result']: print(item['words']) result.append(item['words']) print(result) except sqlalchemy.exc.IntegrityError as e: return 528, "{}".format(str(e)) except BaseException as e: return 530, "{}".format(str(e)) return 200, "ok", result  After calling this function, it returns , Aim the cover at the camera to extract the text of the book cover （ It's best to wait five seconds ）, Return book information , The result of keyword search query is the union of single keyword search results . #### 4.3.11 Get book information This function is very important for buyers , Used to view the introduction of the book . However, the interface provided by the original project does not give , Therefore, it is included in the highlight function . • Function realization 1. according to bid_list Each of them bid stay MongoDB book collection Find book information in • Performance analysis A query MongoDB book collection, Access the database once book = self.mongo['book'].find_one({ 'id': bid},{ '_id':0})  #### 4.3.12 Search in the store In store search is similar to single keyword search , But you need to use join queries . In store search also has two forms: paging search and non paging search . • Function realization 1. First read the search keywords from the user 、 Store name and whether pagination and page number are required , Such parameters page Is equal to zero , No paging . Such as page If it is equal to other numbers, it is the page number that the user needs to find . 2. If pagination is not required , Find out the number of the book corresponding to the keyword and in the store from the inverted list , Of all the books ID、 Title and author , Simultaneously from store Look up the price and inventory in the table , Finally, it is sorted into an appropriate form and returned to the user . 3. Pagination if necessary , The above principle is still used for paging , Sort it out into an appropriate form and return it to the user . cursor = self.conn.execute( "SELECT i.book_id, i.book_title, i.book_author, s.price, s.stock_level " "from invert_index i, store s " "where i.search_key = '%s' and i.book_id = s.book_id and s.store_id = '%s' " "ORDER BY i.search_id limit '%d' offset '%d' ;" % (search_key, store_id, self.page_size, page_lower))  • Performance analysis In store search only needs Access the database once . Although the connection query is used , However, because the attributes used for filtering are the primary key of the corresponding table , Cluster indexes are built , Therefore, the complexity of query is not high . ## 5、 ... and . version control #### active branch: improve #### active branch: modification ## 6、 ... and . test ### 6.1 utilize pytest and coverage Test and evaluate code As shown in the figure below , The coverage rate of this project is 90% about • #### coverage test Code coverage assessment ### 6.2 throughput & Delay test The test results of throughput and delay are as follows , It can be seen that the effect is ideal , Throughput is maintained at 40,000 about , The delay is about 0.01. ### 6.3 Test interface & Examples #### 6.3.1 test result Basic tests 35 All passed , besides , We are advanced function Write the 37 One test , Also all through #### 6.3.2 The test sample • deliver goods send_books Test situation The ginseng result message Successful delivery after payment correct store_id, Correct order_idcode == 200 Cannot ship without payment correct store_id, Error status order_idcode != 200 Shipping books that don't exist FALSE book_idcode != 200 Ship orders that do not exist FALSE order_idcode != 200 The shop doesn't exist FALSE store_idcode != 200 • Receiving goods receive_books Test situation The ginseng result message After the payment is successful and the delivery is successful, the goods are received Correct buyer_id,password,order_idcode == 200 Outstanding orders Correct buyer_id, Error status order_idcode != 200 The buyer does not exist There is no the buyer_idcode != 200 The order does not exist There is no the order_idcode != 200 • Manually cancel the order cancel Test situation The ginseng result message Cancel the order without payment after placing the order Correct buyer_id,order_idcode == 200 The buyer does not exist There is no the buyer_idcode != 200 The order does not exist There is no the order_idcode != 200 • Search for search Test situation The ginseng result message Paging query (“ Sanmao ”, 0)code == 200 Show all queries (“ Sanmao ”, 1)code == 200 Keywords that don't exist (“ Sanmao +”, 1)result==[] Empty page (“ Sanmao ”, 1000)result==[] Keywords that don't exist + Empty page (“ Sanmao +”, 1000)result==[] • Multi keyword search Test situation The ginseng result message The query is successful [“ Sanmao ”,“ Yuan family ”]code == 200 The query is successful [“ Sanmao ”, “ Yuan family ”, “ The mind ”]code == 200 Query with or without keywords [“ Sanmao ”,“ Yuan family ++”]code == 200 There are no keywords [“ Sanmao +”,“ Yuan family ++”]result==[] • The buyer inquires about the current order Test situation The ginseng result message Query the current order after placing an order Correct buyer_idcode == 200 Query the current order after shipment Correct buyer_idcode == 200 Query the current order after receiving , It's empty Correct buyer_idresult==[‘NO Processing Order’] • Buyer inquires about historical orders Test situation The ginseng result message Query historical orders after placing an order , empty Correct buyer_idresult == [] Query historical orders after shipment , empty Correct buyer_idresult == [] Query historical order after receiving Correct buyer_idcode == 200 • Recommendation system Test situation The ginseng result message Did not place the order , No historical orders , It is recommended to be empty Correct buyer_idcode == 200 The recommendation is successful Correct buyer_idcode == 200 • The seller inquires about the current order Test situation The ginseng result message Query the current order after payment Correct seller_idcode == 200 Query the current order after shipment Correct seller_idcode == 200 Query the current order after receiving , It's empty Correct seller_idresult==[‘NO Processing Order’] • Buyers view historical orders Test situation The ginseng result message Query historical orders after placing an order , empty Correct store_idresult == [] Query historical orders after shipment , empty Correct store_idresult == [] Query historical order after receiving Correct store_idcode == 200 • Get book information Test situation The ginseng result message The query is successful buy_book_id_list[0]code == 200 There is no the book_idbuy_book_id_list[0]book_title==[] • In store query Test situation The ginseng result message The query is successful Correct store_id,search_key,pagecode == 200 Paging query succeeded Correct store_id,search_key,pagecode == 200 ## 7、 ... and . Project cloud ### The project is deployed on ECs The cloud services needed are ： 1. Ucloud Postgres UDB/ Alibaba cloud RDS（ Relational database , choice Postgres） 2. Ucloud MongoDB/ Alibaba cloud MongoDB（ Document database ） 3. UHost Cloud server / Aliyun server Remarks： At present, the document databases of major cloud service manufacturers are expensive （ And there are few student discounts ）, Alibaba cloud is recommended MongoDB serverless edition （ It can be understood as a shared economical and applicable document database ）, If there are high response speed requirements for a large number of user visits . It is recommended to use additional services provided by cloud service providers **Load Balance（ Load balancing ）** service . Need to achieve relatively ideal test results , It is recommended to set the cloud service according to the following configuration ： #### 1. Virtual machine settings High concurrency testing requires bandwidth support , current Ucloud Press Bandwidth billing Relatively expensive , Our strengths ### 2. Cloud relational database settings Attention！ Be sure to add your current name to the rented Alibaba cloud server white list / Cloud server ip Address ！ Otherwise, you cannot connect to the server （ And throw “wrong password” Error of .） adopt navicat take Sqlite Data format converted to csv, then csv After all the data is imported into the cloud database （ The book information about climbing down from Douban in this project is about 5GB） You can make a simple query to confirm that the data is normal , As shown in the figure below （ Don't go straight to Navicat Premium use Export Wizard export Sqlite Data to Postgres, It's not right .） Remark： Can pass ip.cip.cc Get the current information of the machine quickly ip Address , Facilitate the use of cloud services Subsequently passed psycopg2 The database can be connected to the relational database , You can first establish a simple connection to test whether the connection with the cloud is normal . import psycopg2 Base = declarative_base() #base class engine = create_engine('postgresql://${username}:${password}@${public ip}:${port}/${databse-name}')
DBSession = sessionmaker(bind=engine)  #  Establish a conversation
bookstore_engine = DBSession()


### 3. Document database

cloud MongoDB The deployment of is similar to that of a relational database , Also pay attention to the white list during deployment .

## 8、 ... and . Division and cooperation

(1) ** Zheng Jiachen ：** Database design , Recommendation system , Inverted list and search system , Historical orders , performance optimization , The interface test , The product manager

Contribution ：33.3%

(2) ** Sun Qiushi ：** The order is cancelled automatically , Basic function realization , The interface test , Throughput test , Project cloud ,slides Make Contribution ：33.3%

(3) Tang Qiong ： The interface test , Basic function realization , The receipt and delivery function is realized , Order cancellation , Search for books , real time OCR,postman test , Throughput test Contribution ：33.3%

## Nine . The experimental conclusion

	 This experiment covers from table design to functional packaging and testing of the project , Then to the whole process of deploying to the cloud , It is a very complete project development experience . stay Bookstore Project development , The group deepened its understanding of ** normal form 、 Table index 、 Inverted index 、 data redundancy 、 High concurrency and transaction processing ** The understanding of the , And tried to use open api Advanced functions required to complete the project . We spent a lot of time on project performance optimization , The introduction of “ The order is cancelled automatically ” For example “ Soft real time ” Concept to reduce the resource consumption of multithreading, as well as RETURNING Unnecessary keyword queries are reduced . Members of the project team are interested in Bookstore The functions of the are 72 One test （35 A basic test + Self written 37 An advanced function test ）, And take this to test the high concurrency performance and code coverage , Satisfactory results have been achieved .