apache / apache/shardingsphere
Consider refactoring table models in ShardingSphere integration tests for CASE reuse
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 35m
- Merged PRs (30d)
- 326
Description
## Feature Request
### Is your feature request related to a problem?
No
### Describe the feature you would like.
#### Background
Now, the table model in integration test is very confusing. In addition to `t_order` and `t_order_item`, there are many tables with meaningless table names. The meaningless table names make developers do not know how to use them, so that different scenarios need to add new tables for testing, which makes most cases not well reused.
Therefore, we need to design a new table model based on the order model, which should be able to meet the usage scenarios of data sharding, data encryption, and read-write splitting, so as to facilitate case reuse in different scenarios.
#### New table model
Based on the above background and existing problems, the following table is designed for the order model, and the specific meanings are as follows.

- t_order: order table, which records user id and merchant id, which can be sharded according to `user_id` and `order_id`;
- t_order_item: order details table, which records the product information associated with the order and the number of products, which can be sharded according to `user_id` and `order_id`;
- t_user: user table, which records the basic information of the user, including `user_name`, `password`, `email` and `telephone`, which can be used for reversible and irreversible encryption algorithm testing;
- t_merchant: merchant table, used to record the information of the merchants selling the products. The table records the merchant's name, country, business code, merchant phone and other information, which can be used for reversible encryption algorithm testing;
- t_product: product table, which records the category, price and status of the product, which can be used for single-table testing;
- t_product_detail: product detail table, which records the extended description information of the product, which can be used for single-table testing;
- t_product_category: commodity category table, which records commodity category information, which can be used for broadcast table testing;
- t_country: the country table of the merchant, which records the country code, name and continent to which the merchant belongs, which can be used for broadcast table testing;
The detailed table structure pseudo code is as follows:
```sql
Table t_order { // 订单表(分片表)
order_id BIGINT [pk]
user_id INT [not null]
status VARCHAR [not null]
merchant_id INT [not null]
remark VARCHAR [not null]
creation_date DATE [not null]
Indexes {
(user_id) [name:'user_id_idx']
}
}
Table t_order_item { // 订单明细表(分片表,和 t_order 为绑定表)
item_id BIGINT [pk]
order_id BIGINT [not null]
user_id INT [not null]
product_id INT [not null]
quantity INT [not null]
creation_date DATE [not null]
Indexes {
(order_id) [name:'order_id_idx']
(user_id) [name:'user_id_idx']
(product_id) [name:'product_id_idx']
}
}
Table t_user { // 用户表(单表)
user_id INT [pk]
user_name VARCHAR [not null] // (加密)
password VARCHAR [not null] // (加密)
email VARCHAR [not null] // (加密)
telephone CHAR [not null] // (加密)
creation_date DATE [not null]
}
Table t_merchant { // 商家表(单表)
merchant_id INT [pk]
country_id SMALLINT [not null]
merchant_name VARCHAR [not null]
business_code VARCHAR [not null] // 营业执照号(加密)
telephone CHAR [not null] // 电话号码(加密)
creation_date DATE [not null]
}
Table t_product { // 商品表(单表)
product_id INT [pk]
product_name VARCHAR [not null]
category_id INT [not null]
price DECIMAL [not null] // 商品价格
status VARCHAR [not null]
creation_date DATE [not null]
Indexes {
(category_id) [name:'category_id_idx']
}
}
Table t_product_detail { // 商品明细表(单表)
detail_id INT [pk]
product_id INT [not null]
description VARCHAR [not null]
creation_date DATE [not null]
Indexes {
(product_id) [name:'product_id_idx']
}
}
Table t_product_category { // 商品分类表(广播表)
category_id INT [pk]
category_name VARCHAR [not null]
parent_id INT [not null]
level TINYINT [not null]
creation_date DATE [not null]
Indexes {
(parent_id) [name:'parent_id_idx']
}
}
Table t_country { // 商家属地表(广播表)
country_id SMALLINT [pk]
country_name VARCHAR
continent_name VARCHAR
creation_date DATE [not null]
}
Ref: t_order_item.order_id > t_order.order_id
Ref: t_order.user_id > t_user.user_id
Ref: t_merchant.merchant_id < t_order.merchant_id
Ref: t_order_item.product_id > t_product.product_id
Ref: t_merchant.country_id > t_country.country_id
Ref: t_product.category_id > t_product_category.category_id
Ref: t_product_detail.product_id > t_product.product_id
```
Original document of table model design: https://dbdiagram.io/d/63369b527b3d2034fff3691b
This task contains the following subtasks.
- [x] modify the t_order table structure and update the relevant integration tests
- [x] modify the t_order_item table structure and update the relevant integration tests
- [ ] add t_user table structure and added integration tests
- [ ] add t_merchant table structure and added integration tests
- [ ] add t_product table structure and added integration tests
- [ ] add t_product_detail table structure and added integration tests
- [ ] add t_product_category table structure and added integration tests
- [ ] add t_country table structure and added integration tests
- [ ] drop other meaningless tables and modify the integration test case
Contributor guide
Assessment
This issue has not been assessed yet.