You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/09/30 10:58:03 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #21286: Consider refactoring table models in ShardingSphere integration tests for CASE reuse
strongduanmu opened a new issue, #21286:
URL: https://github.com/apache/shardingsphere/issues/21286
## 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 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 INT [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 INT [pk]
order_id INT [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 VARCHAR [not null] // （可逆加密）
creation_date DATE [not null]
}
Table t_merchant { // 商家表（单表）
merchant_id INT [pk]
country_id INT [not null]
merchant_name VARCHAR [not null]
business_code VARCHAR [not null] // 营业执照号（可逆加密）
telephone VARCHAR [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 INT [not null]
creation_date DATE [not null]
Indexes {
(parent_id) [name:'parent_id_idx']
}
}
Table t_country { // 商家属地表（广播表）
country_id INT [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
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21286: Consider refactoring table models in ShardingSphere integration tests for CASE reuse
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21286:
URL: https://github.com/apache/shardingsphere/issues/21286#issuecomment-1263442205
@ICannerxy Welcome to join and let's optimize integration tests together.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ICannerxy commented on issue #21286: Consider refactoring table models in ShardingSphere integration tests for CASE reuse
Posted by GitBox <gi...@apache.org>.
ICannerxy commented on issue #21286:
URL: https://github.com/apache/shardingsphere/issues/21286#issuecomment-1263438716
Assign to me,Please.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Consider refactoring table models in ShardingSphere integration tests for CASE reuse [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #21286:
URL: https://github.com/apache/shardingsphere/issues/21286#issuecomment-2026018682
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org