SQL Statements Report - NFSandbox/sh_trade_backend GitHub Wiki

[!note] This report is only used as course experiments report resources. For more accurate info about ORM and database design, please check out ORM class definition code directly.

To make the SQLAlchemy Engine emit the constructed SQL Statement into stdout in realtime, please enable the engine echo by update project config file

# /config/sql.py
DB_HOST: str = "..."
DB_NAME: str = "..."
DB_USERNAME: str = "..."
DB_PASSWORD: str = "..."

- ENGINE_ECHO: bool = False
+ ENGINE_ECHO: bool = True
"""
Echo every SQL statement sent by SQLAlchemy engine
"""

Create Table

Following statements are used to initialize a database with all the tables needed for our FastAPI and SQLAlchemy Python project.

CREATE TABLE user (
    user_id BIGINT NOT NULL AUTO_INCREMENT,
    campus_id VARCHAR(20),
    username VARCHAR(20) NOT NULL,
    password VARCHAR(100) NOT NULL,
    description VARCHAR(100),
    created_time BIGINT NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (user_id),
    UNIQUE (username)
);

CREATE TABLE tag (
    tag_id BIGINT NOT NULL AUTO_INCREMENT,
    tag_type ENUM('original','user_created') NOT NULL,    
    created_time BIGINT NOT NULL,
    name VARCHAR(20) NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (tag_id)
);

CREATE TABLE `role` (
    role_id BIGINT NOT NULL AUTO_INCREMENT,
    role_name VARCHAR(20) NOT NULL,
    role_title VARCHAR(20) NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (role_id)
);

CREATE TABLE association_users_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY(user_id) REFERENCES user (user_id),       
    FOREIGN KEY(role_id) REFERENCES `role` (role_id)      
);

CREATE TABLE contact_info (
    contact_info_id BIGINT NOT NULL AUTO_INCREMENT,       
    user_id BIGINT NOT NULL,
    contact_type ENUM('phone','email') NOT NULL,
    contact_info VARCHAR(100) NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (contact_info_id),
    FOREIGN KEY(user_id) REFERENCES user (user_id)        
);

CREATE TABLE item (
    item_id BIGINT NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    name VARCHAR(20) NOT NULL,
    description VARCHAR(2000),
    created_time BIGINT NOT NULL,
    price BIGINT NOT NULL,
    state ENUM('hide','sold','valid') NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (item_id),
    FOREIGN KEY(user_id) REFERENCES user (user_id)        
);

CREATE TABLE trade (
    trade_id BIGINT NOT NULL AUTO_INCREMENT,
    buyer_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,
    created_time BIGINT NOT NULL,
    review_from_buyer VARCHAR(2000),
    review_from_seller VARCHAR(2000),
    state ENUM('processing','success','cancelled') NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (trade_id),
    FOREIGN KEY(buyer_id) REFERENCES user (user_id),      
    FOREIGN KEY(item_id) REFERENCES item (item_id)        
);

CREATE TABLE question (
    question_id BIGINT NOT NULL AUTO_INCREMENT,
    item_id BIGINT NOT NULL,
    asker_id BIGINT NOT NULL,
    question VARCHAR(500) NOT NULL,
    created_time BIGINT NOT NULL,
    answered_time BIGINT,
    answer VARCHAR(500),
    public BOOL NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (question_id),
    FOREIGN KEY(item_id) REFERENCES item (item_id),       
    FOREIGN KEY(asker_id) REFERENCES user (user_id)       
);

CREATE TABLE association_items_tags (
    association_items_tags_id BIGINT NOT NULL AUTO_INCREMENT,
    item_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,
    deleted_at BIGINT,
    PRIMARY KEY (association_items_tags_id, item_id, tag_id),
    FOREIGN KEY(item_id) REFERENCES item (item_id

You could using python create_db.py for auto metadata creation.

User

Sign up

SELECT DATABASE();

SELECT @@sql_mode;

SELECT @@lower_case_table_names;

BEGIN (implicit);

INSERT INTO user (campus_id, username, password, description, created_time, deleted_at) 
VALUES (%s, %s, %s, %s, %s, %s);

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.username = %s AND user.deleted_at IS NULL;

Get User Info

SELECT user_1.user_id AS user_1_user_id, `role`.role_id AS role_role_id, `role`.role_name AS role_role_name, `role`.role_title AS role_role_title, `role`.deleted_at AS role_deleted_at
FROM user AS user_1 
INNER JOIN association_users_roles AS association_users_roles_1 ON user_1.user_id = association_users_roles_1.user_id 
INNER JOIN `role` ON `role`.role_id = association_users_roles_1.role_id
WHERE user_1.user_id IN (%s) AND `role`.deleted_at IS NULL;

Notice the role info is also eager loaded

User Sign In

BEGIN (implicit);

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.username = %s AND user.deleted_at IS NULL;

SELECT user_1.user_id AS user_1_user_id, `role`.role_id AS role_role_id, `role`.role_name AS role_role_name, `role`.role_title AS role_role_title, `role`.deleted_at AS role_deleted_at
FROM user AS user_1 
INNER JOIN association_users_roles AS association_users_roles_1 ON user_1.user_id = association_users_roles_1.user_id 
INNER JOIN `role` ON `role`.role_id = association_users_roles_1.role_id
WHERE user_1.user_id IN (%s) AND `role`.deleted_at IS NULL;

Here notice the user.password has been selected. However the password info will not be returned as long as the response_model in endpoints does not contain that attribute.

Also, user.role is eager loaded in case of permission checking.

Add Contact Info

-- make sure user with such user_id exists
SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- make sure this contact info is not used before
SELECT DISTINCT count(*) AS count_1 
FROM contact_info
WHERE contact_info.contact_info = '[email protected]' AND contact_info.contact_type = 'email' AND contact_info.deleted_at IS NULL;

-- make sure user is not adding a duplicated contact info
SELECT contact_info.contact_info_id AS contact_info_contact_info_id, contact_info.user_id AS contact_info_user_id, contact_info.contact_type AS contact_info_contact_type, contact_info.contact_info AS contact_info_contact_info, contact_info.deleted_at AS contact_info_deleted_at
FROM contact_info
WHERE 1 = contact_info.user_id AND contact_info.deleted_at IS NULL;

-- insert info
INSERT INTO contact_info (user_id, contact_type, contact_info, deleted_at) 
VALUES (1, 'email', '[email protected]', None);

Get All Contact Info

-- make sure user exists
SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- select all contact info of this user
SELECT contact_info.contact_info_id, contact_info.user_id, contact_info.contact_type, contact_info.contact_info, contact_info.deleted_at
FROM user 
INNER JOIN contact_info ON user.user_id = contact_info.user_id 
WHERE user.user_id = 1 AND user.deleted_at IS NULL AND contact_info.deleted_at IS NULL;

Remove Contact Info

Notice, this removal is not depend on contact_id, instead it depend on the contact_info_type and contact_info

-- check current user exists
-- get current user info
SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- check contact info exists
-- also check the contact info is belong to this user
SELECT contact_info.contact_info_id, contact_info.user_id, contact_info.contact_type, contact_info.contact_info, contact_info.deleted_at
FROM user 
INNER JOIN contact_info ON user.user_id = contact_info.user_id
WHERE user.user_id = 1 AND user.deleted_at IS NULL AND contact_info.deleted_at IS NULL;

-- soft delete
UPDATE contact_info SET deleted_at = 1727240173086 WHERE contact_info.contact_info_id = 2;

Items

Get Items Of User

BEGIN (implicit);

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = %s AND user.deleted_at IS NULL AND user.deleted_at IS NULL;

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = %s AND user.deleted_at IS NULL AND user.deleted_at IS NULL;

SELECT item.item_id, item.user_id, item.name, item.description, item.created_time, item.price, item.state, item.deleted_at
FROM user 
INNER JOIN item ON user.user_id = item.user_id AND user.user_id = %s AND item.deleted_at IS NULL
WHERE user.deleted_at IS NULL AND item.deleted_at IS NULL 
ORDER BY item.created_time DESC;

Notice some of deleted_ad are redundant in one SQL statements. This may caused by the packages I used called sqlalchemy_easy_soft_delete.

Add Items

This task is way more complex then previous one. Let's check out SQL statement log first.

The generated test data is as below: (Data generated by Faker.js)

{
    "name": "vomica trado suffici",
    "description": "Turbo cupiditate toties ...",
    "price": 92536295,
    "tags": [
        "laborum ir",
        "ad e",
        "laborum occaecat"
    ]
}

There are two major things here:

Check if user has reached maximum-items-per-user limitation.
Check if the tags used by this item already exist? If no, create those tag first.
Create item
Associate item to tags.

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- check if reached maximum user item count
SELECT count(*) AS count_1
FROM user
INNER JOIN item ON user.user_id = item.user_id AND item.deleted_at IS NULL AND item.state != 'sold'
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- create item
INSERT INTO item (user_id, name, description, created_time, price, state, deleted_at) 
VALUES (1, 'vomica trado suffici', 'Turbo cupiditate ....', 1727232149462, 92536295, 'valid', NULL);


-- check if tag exists (Here all exists, so no insert operation)
SELECT tag.tag_id, tag.tag_type, tag.created_time, tag.name, tag.deleted_at
FROM tag
WHERE tag.deleted_at IS NULL;

SELECT tag.tag_id, tag.tag_type, tag.created_time, tag.name, tag.deleted_at
FROM tag
WHERE tag.name = 'ad e' AND tag.deleted_at IS NULL;

SELECT tag.tag_id, tag.tag_type, tag.created_time, tag.name, tag.deleted_at
FROM tag
WHERE tag.name = 'laborum occaecat' AND tag.deleted_at IS NULL;

SELECT tag.tag_id, tag.tag_type, tag.created_time, tag.name, tag.deleted_at
FROM tag
WHERE tag.name = 'laborum ir' AND tag.deleted_at IS NULL;

SELECT item.item_id
FROM item
WHERE item.item_id = 3 AND item.deleted_at IS NULL;

-- associate tags with item
INSERT INTO association_items_tags (item_id, tag_id, created_at, deleted_at) 
VALUES (3, 2, 1727232149504, NULL);

INSERT INTO association_items_tags (item_id, tag_id, created_at, deleted_at) 
VALUES (3, 1, 1727232149506, NULL);

INSERT INTO association_items_tags (item_id, tag_id, created_at, deleted_at) 
VALUES (3, 3, 1727232149508, NULL);

Remove Items

This is also a complicated process since we need to handle soft-delete cascade ourself. Also there is a lot of business logic:

Which user are trying to delete this item?
- Is this item belongs to this user?
- If the item NOT belongs to this user, then is this user an Admin or Moderator?
If the item id exists?
Cascade Delete
- Is there any question info related to this item?
- Is there any other user favourited this item?
- ...

We are using remove item with item_id = 2 as example.

-- check 
-- if user exists?
-- if user has permission to delete this item?
SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- if item exists?
SELECT item.item_id AS item_item_id, item.user_id AS item_user_id, item.name AS item_name, item.description AS item_description, item.created_time AS item_created_time, item.price AS item_price, item.state AS item_state, item.deleted_at AS item_deleted_at
FROM item
WHERE item.item_id = 2 AND item.deleted_at IS NULL;

-- if user try to delete other's item, then if this user an admin himsef?
SELECT `role`.role_id AS role_role_id, `role`.role_name AS role_role_name, `role`.role_title AS role_role_title, `role`.deleted_at AS role_deleted_at
FROM `role`, association_users_roles
WHERE 1 = association_users_roles.user_id AND `role`.role_id = association_users_roles.role_id AND `role`.deleted_at IS NULL;

# cascade: question
SELECT question.question_id, question.item_id, question.asker_id, question.question, question.created_time, question.answered_time, question.answer, question.public, question.deleted_at
FROM question
INNER JOIN item ON item.item_id = question.item_id
WHERE item.item_id IN (2) AND question.deleted_at IS NULL AND item.deleted_at IS NULL;

# cascade: item tag association
SELECT association_items_tags.association_items_tags_id, association_items_tags.item_id, association_items_tags.tag_id, association_items_tags.created_at, association_items_tags.deleted_at
FROM association_items_tags
WHERE association_items_tags.item_id IN (2) AND association_items_tags.deleted_at IS NULL;

UPDATE association_items_tags
SET deleted_at = 1727232423192
WHERE association_items_tags.association_items_tags_id = 1 AND association_items_tags.item_id = 2 AND association_items_tags.tag_id = 2;

UPDATE association_items_tags
SET deleted_at = 1727232423192
WHERE association_items_tags.association_items_tags_id = 2 AND association_items_tags.item_id = 2 AND association_items_tags.tag_id = 1;

UPDATE association_items_tags
SET deleted_at = 1727232423192
WHERE association_items_tags.association_items_tags_id = 3 AND association_items_tags.item_id = 2 AND association_items_tags.tag_id = 3;

# cascade: user favourite items
SELECT association_user_favourite_item.association_user_favourite_item_id, association_user_favourite_item.user_id, association_user_favourite_item.item_id, association_user_favourite_item.created_at, association_user_favourite_item.deleted_at
FROM association_user_favourite_item
WHERE association_user_favourite_item.item_id IN (2) AND association_user_favourite_item.deleted_at IS NULL;

# delete item
UPDATE item
SET deleted_at = 1727232423204
WHERE item.item_id = 2;

Questions

Add Questions

Notice, using user with user_id = 1 as example. Also, the created item has item_id = 1

In production environment, these ID parameters will be determined in Python SQLAlchemy level, then a litreal variable would be passed to database.

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- check if target item exists
SELECT item.item_id AS item_item_id, item.user_id AS item_user_id, item.name AS item_name, item.description AS item_description, item.created_time AS item_created_time, item.price AS item_price, item.state AS item_state, item.deleted_at AS item_deleted_at
FROM item
WHERE item.item_id = 1 AND item.deleted_at IS NULL;

SELECT question.question_id AS question_question_id, question.item_id AS question_item_id, question.asker_id AS question_asker_id, question.question AS question_question, question.created_time AS question_created_time, question.answered_time AS question_answered_time, question.answer AS question_answer, question.public AS question_public, question.deleted_at AS question_deleted_at
FROM question
WHERE question.item_id = 1 AND question.deleted_at IS NULL;

-- add question
INSERT INTO question (item_id, asker_id, question, created_time, answered_time, answer, public, deleted_at) 
VALUES (1, 1, 'ipsum do mollit proident', 1727240375440, 1727240375440, NULL, 1, NULL);

-- return newly created question
SELECT question.question_id, question.item_id, question.asker_id, question.question, question.created_time, question.answered_time, question.answer, question.public, question.deleted_at
FROM question
WHERE question.question_id = 1 AND question.deleted_at IS NULL;

Get Questions Of Item

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

SELECT item.item_id AS item_item_id, item.user_id AS item_user_id, item.name AS item_name, item.description AS item_description, item.created_time AS item_created_time, item.price AS item_price, item.state AS item_state, item.deleted_at AS item_deleted_at
FROM item
WHERE item.item_id = 1 AND item.deleted_at IS NULL;

-- get question info
SELECT question.question_id, question.item_id, question.asker_id, question.question, question.created_time, question.answered_time, question.answer, question.public, question.deleted_at
FROM item
JOIN question ON item.item_id = question.item_id AND question.deleted_at IS NULL AND item.item_id = 1
WHERE item.deleted_at IS NULL
ORDER BY question.created_time DESC;

Remove Questions

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

-- ensure question exists
SELECT question.question_id AS question_question_id, question.item_id AS question_item_id, question.asker_id AS question_asker_id, question.question AS question_question, question.created_time AS question_created_time, question.answered_time AS question_answered_time, question.answer AS question_answer, question.public AS question_public, question.deleted_at AS question_deleted_at
FROM question
WHERE question.question_id = 1 AND question.deleted_at IS NULL;

-- ensure item exists
SELECT item.item_id AS item_item_id, item.user_id AS item_user_id, item.name AS item_name, item.description AS item_description, item.created_time AS item_created_time, item.price AS item_price, item.state AS item_state, item.deleted_at AS item_deleted_at
FROM item
WHERE item.item_id = 1 AND item.deleted_at IS NULL;

-- soft delete
UPDATE question SET answered_time = 1727241038305, answer = 'Test question answer' WHERE question.question_id = 1;

Favourite Item

Get Fav Item

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

SELECT association_user_favourite_item.association_user_favourite_item_id AS association_user_favourite_item_association_user_favourite_item_id, association_user_favourite_item.user_id AS association_user_favourite_item_user_id, association_user_favourite_item.item_id AS association_user_favourite_item_item_id, association_user_favourite_item.created_at AS association_user_favourite_item_created_at, association_user_favourite_item.deleted_at AS association_user_favourite_item_deleted_at
FROM association_user_favourite_item
WHERE association_user_favourite_item.user_id = 1 AND association_user_favourite_item.deleted_at IS NULL;

Add Fav Item

SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = 1 AND user.deleted_at IS NULL;

SELECT item.item_id AS item_item_id, item.user_id AS item_user_id, item.name AS item_name, item.description AS item_description, item.created_time AS item_created_time, item.price AS item_price, item.state AS item_state, item.deleted_at AS item_deleted_at
FROM item
WHERE item.item_id = 1 AND item.deleted_at IS NULL;

SELECT count(*) AS count_1 
FROM association_user_favourite_item
WHERE association_user_favourite_item.user_id = 1 AND association_user_favourite_item.item_id = 1 AND association_user_favourite_item.deleted_at IS NULL;

-- add fav item
INSERT INTO association_user_favourite_item (user_id, item_id, created_at, deleted_at) VALUES (1, 1, 1727241249709, NULL);

Remove Fav Item

-- get current user
SELECT user.user_id, user.campus_id, user.username, user.password, user.description, user.created_time, user.deleted_at
FROM user
WHERE user.user_id = %s AND user.deleted_at IS NULL AND user.deleted_at IS NULL;

-- check user info, make sure it has permission to remove the association
SELECT association_user_favourite_item.association_user_favourite_item_id, association_user_favourite_item.user_id, association_user_favourite_item.item_id, association_user_favourite_item.created_at, association_user_favourite_item.deleted_at
FROM association_user_favourite_item
WHERE association_user_favourite_item.user_id = %s AND association_user_favourite_item.item_id IN (%s) AND association_user_favourite_item.deleted_at IS NULL;

-- remove user
UPDATE association_user_favourite_item SET deleted_at=%s WHERE association_user_favourite_item.association_user_favourite_item_id = %s AND association_user_favourite_item.user_id = %s AND association_user_favourite_item.item_id = %s;