292 lines
12 KiB
SQL
292 lines
12 KiB
SQL
CREATE TABLE IF NOT EXISTS district (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
parent_id integer NOT NULL DEFAULT 0,
|
||
code varchar(10) NOT NULL,
|
||
"name" varchar(20),
|
||
full_name varchar(100) NOT NULL,
|
||
pinyin varchar(200),
|
||
"level" smallint NOT NULL,
|
||
latitude varchar(20),
|
||
longitude varchar(20),
|
||
"order" smallint NOT NULL
|
||
);
|
||
|
||
COMMENT ON TABLE district IS '行政区划';
|
||
COMMENT ON COLUMN district.id IS '主键';
|
||
COMMENT ON COLUMN district.parent_id IS '父级id，顶级时为0';
|
||
COMMENT ON COLUMN district.code IS '编码';
|
||
COMMENT ON COLUMN district.name IS '简称';
|
||
COMMENT ON COLUMN district.full_name IS '全称';
|
||
COMMENT ON COLUMN district.pinyin IS '名称拼音';
|
||
COMMENT ON COLUMN district.level IS '层级：1-省/直辖市，2-市/直辖市无，3-区，4-街道';
|
||
COMMENT ON COLUMN district.latitude IS '经度';
|
||
COMMENT ON COLUMN district.longitude IS '纬度';
|
||
COMMENT ON COLUMN district.order IS '层级中序号';
|
||
|
||
CREATE TABLE IF NOT EXISTS "user" (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
account varchar(20) NOT NULL,
|
||
nick_name varchar(50) NOT NULL,
|
||
avatar varchar(50),
|
||
"current_role" char(1) NOT NULL DEFAULT 'c',
|
||
telephone varchar(20),
|
||
"password" varchar(128) NOT NULL,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
last_login_time timestamp
|
||
);
|
||
|
||
COMMENT ON TABLE "user" IS '用户';
|
||
COMMENT ON COLUMN "user".id IS '主键';
|
||
COMMENT ON COLUMN "user".account IS '登录账号';
|
||
COMMENT ON COLUMN "user".nick_name IS '昵称';
|
||
COMMENT ON COLUMN "user".avatar IS '头像图片名，后台生成链接';
|
||
COMMENT ON COLUMN "user"."current_role" IS '当前角色：c-买家，s-卖家';
|
||
COMMENT ON COLUMN "user".telephone IS '电话';
|
||
COMMENT ON COLUMN "user"."password" IS '密码（已加密）';
|
||
COMMENT ON COLUMN "user".create_time IS '创建时间';
|
||
COMMENT ON COLUMN "user".last_login_time IS '最后登录时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS address (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
user_id integer NOT NULL,
|
||
"name" varchar(20) NOT NULL,
|
||
telephone varchar(20) NOT NULL,
|
||
tag varchar(20),
|
||
"default" boolean NOT NULL DEFAULT FALSE,
|
||
district_level1_id integer NOT NULL,
|
||
district_level2_id integer,
|
||
district_level3_id integer NOT NULL,
|
||
district_level4_id integer NOT NULL,
|
||
detail varchar(200),
|
||
CONSTRAINT fk_UserAddress FOREIGN KEY (user_id) REFERENCES "user"(id)
|
||
);
|
||
|
||
COMMENT ON TABLE address IS '收货地址';
|
||
COMMENT ON COLUMN address.id IS '主键';
|
||
COMMENT ON COLUMN address.user_id IS '用户表id';
|
||
COMMENT ON COLUMN address.name IS '收货人地址';
|
||
COMMENT ON COLUMN address.telephone IS '联系电话';
|
||
COMMENT ON COLUMN address.tag IS '自定义标签：学校、家等';
|
||
COMMENT ON COLUMN address.default IS '是否默认地址';
|
||
COMMENT ON COLUMN address.district_level1_id IS '行政区域id，表示省/直辖市';
|
||
COMMENT ON COLUMN address.district_level2_id IS '行政区域id，表示市/直辖市为空';
|
||
COMMENT ON COLUMN address.district_level3_id IS '行政区域id，表示区';
|
||
COMMENT ON COLUMN address.district_level4_id IS '行政区域id，表示街道/镇';
|
||
COMMENT ON COLUMN address.detail IS '详细地址';
|
||
|
||
CREATE TABLE IF NOT EXISTS administrator (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
account varchar(20) NOT NULL,
|
||
nick_name varchar(50) NOT NULL,
|
||
"password" varchar(128) NOT NULL,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
last_login_time timestamp NOT NULL
|
||
);
|
||
|
||
COMMENT ON TABLE administrator IS '管理员';
|
||
COMMENT ON COLUMN administrator.id IS '主键';
|
||
COMMENT ON COLUMN administrator.account IS '登录账号';
|
||
COMMENT ON COLUMN administrator.nick_name IS '昵称';
|
||
COMMENT ON COLUMN administrator.password IS '登录密码（已加密）';
|
||
COMMENT ON COLUMN administrator.create_time IS '创建时间';
|
||
COMMENT ON COLUMN administrator.last_login_time IS '最后登录时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS category (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
parent_id integer NOT NULL DEFAULT 0,
|
||
"path" varchar(50) NOT NULL,
|
||
"name" varchar(50) NOT NULL,
|
||
"level" smallint NOT NULL,
|
||
logo varchar(50),
|
||
"order" smallint NOT NULL,
|
||
deleted boolean NOT NULL DEFAULT FALSE
|
||
);
|
||
|
||
COMMENT ON TABLE category IS '商品分类';
|
||
COMMENT ON COLUMN category.id IS '主键';
|
||
COMMENT ON COLUMN category.parent_id IS '父级id，顶级为0';
|
||
COMMENT ON COLUMN category.path IS 'id路径枚举：/1/2/3/';
|
||
COMMENT ON COLUMN category.name IS '名称';
|
||
COMMENT ON COLUMN category.level IS '层级，从1开始';
|
||
COMMENT ON COLUMN category.logo IS 'logo图片名，后台生成地址';
|
||
COMMENT ON COLUMN category.order IS '层级中序号';
|
||
COMMENT ON COLUMN category.deleted IS '软删除标识';
|
||
|
||
CREATE TABLE IF NOT EXISTS product (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
serial_no varchar(20) NOT NULL,
|
||
"name" varchar(100) NOT NULL,
|
||
description varchar(200),
|
||
logo varchar(50) NOT NULL,
|
||
category_id integer NOT NULL,
|
||
user_id integer NOT NULL,
|
||
minimum_unit varchar(20) NOT NULL,
|
||
unit_price numeric(11, 2) NOT NULL DEFAULT 0,
|
||
detail text,
|
||
sold_amount integer NOT NULL DEFAULT 0,
|
||
deleted boolean NOT NULL DEFAULT FALSE,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT fk_CategoryProduct FOREIGN KEY (category_id) REFERENCES category(id)
|
||
);
|
||
|
||
COMMENT ON TABLE product IS '卖家商品表';
|
||
COMMENT ON COLUMN product.id IS '主键';
|
||
COMMENT ON COLUMN product.serial_no IS '系统唯一编号';
|
||
COMMENT ON COLUMN product.name IS '名称';
|
||
COMMENT ON COLUMN product.description IS '商品描述';
|
||
COMMENT ON COLUMN product.logo IS '商品图片';
|
||
COMMENT ON COLUMN product.category_id IS '商品分类id';
|
||
COMMENT ON COLUMN product.user_id IS '用户id';
|
||
COMMENT ON COLUMN product.minimum_unit IS '最小销售单元';
|
||
COMMENT ON COLUMN product.unit_price IS '单价';
|
||
COMMENT ON COLUMN product.detail IS '详情';
|
||
COMMENT ON COLUMN product.sold_amount IS '已售数量';
|
||
COMMENT ON COLUMN product.deleted IS '软删除标识';
|
||
COMMENT ON COLUMN product.create_time IS '添加时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS request (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
serial_no varchar(20) NOT NULL,
|
||
"name" varchar(100) NOT NULL,
|
||
description varchar(1000),
|
||
category_id integer NOT NULL,
|
||
status smallint NOT NULL DEFAULT 0,
|
||
publisher_id integer NOT NULL,
|
||
publish_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"deadline" date NOT NULL,
|
||
deleted boolean NOT NULL DEFAULT FALSE,
|
||
CONSTRAINT fk_CategoryRequest FOREIGN KEY (category_id) REFERENCES category(id),
|
||
CONSTRAINT fk_UserRequest FOREIGN KEY (publisher_id) REFERENCES "user"(id)
|
||
);
|
||
|
||
COMMENT ON TABLE request IS '用户需求';
|
||
COMMENT ON COLUMN request.id IS '主键';
|
||
COMMENT ON COLUMN request.serial_no IS '需求单号，系统唯一，后台生成';
|
||
COMMENT ON COLUMN request.name IS '名称';
|
||
COMMENT ON COLUMN request.description IS '需求描述';
|
||
COMMENT ON COLUMN request.category_id IS '商品分类id';
|
||
COMMENT ON COLUMN request.status IS '状态：0-发布，1-有竞标，2-待发货，3-待收货，4-已完成，5-已评价';
|
||
COMMENT ON COLUMN request.publisher_id IS '发布者id';
|
||
COMMENT ON COLUMN request.publish_time IS '发布时间';
|
||
COMMENT ON COLUMN request.deadline IS '截止日期';
|
||
COMMENT ON COLUMN request.deleted IS '是否已删除';
|
||
|
||
CREATE TABLE IF NOT EXISTS reply (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
product_id integer NOT NULL,
|
||
amount integer NOT NULL,
|
||
price numeric(11, 2) NOT NULL,
|
||
user_id integer NOT NULL,
|
||
reply_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
request_id integer NOT NULL,
|
||
memo varchar(200),
|
||
rejected boolean NOT NULL DEFAULT FALSE,
|
||
CONSTRAINT fk_RequestReply FOREIGN KEY (request_id) REFERENCES request(id),
|
||
CONSTRAINT fk_ReplyProduct FOREIGN KEY (product_id) REFERENCES product(id),
|
||
CONSTRAINT fk_ReplyUser FOREIGN KEY (user_id) REFERENCES "user"(id)
|
||
);
|
||
|
||
COMMENT ON TABLE reply IS '竞标表';
|
||
COMMENT ON COLUMN reply.id IS '主键';
|
||
COMMENT ON COLUMN reply.product_id IS '商品id';
|
||
COMMENT ON COLUMN reply.amount IS '数量';
|
||
COMMENT ON COLUMN reply.price IS '价格，自动计算的价格(product.unit_price * amount)之后的优惠价格';
|
||
COMMENT ON COLUMN reply.user_id IS '竞标者id';
|
||
COMMENT ON COLUMN reply.reply_time IS '回应时间';
|
||
COMMENT ON COLUMN reply.request_id IS '需求id';
|
||
COMMENT ON COLUMN reply.memo IS '留言';
|
||
COMMENT ON COLUMN reply.rejected IS '是否已拒绝';
|
||
|
||
CREATE TABLE IF NOT EXISTS logistics (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
order_no varchar(20) NOT NULL,
|
||
company varchar(30) NOT NULL,
|
||
status smallint NOT NULL,
|
||
arrival_time timestamp NOT NULL,
|
||
"location" varchar(100) NOT NULL,
|
||
context varchar(200) NOT NULL,
|
||
request_id integer NOT NULL,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
COMMENT ON TABLE logistics IS '物流';
|
||
COMMENT ON COLUMN logistics.id IS '主键';
|
||
COMMENT ON COLUMN logistics.order_no IS '快递单号';
|
||
COMMENT ON COLUMN logistics.company IS '快递公司';
|
||
COMMENT ON COLUMN logistics.status IS '物流状态：1-揽收，0-在途，5-派件，6-退回，4-退签，3-签收，2-疑难，7-转投，8。。。-清关';
|
||
COMMENT ON COLUMN logistics.arrival_time IS '到达时间';
|
||
COMMENT ON COLUMN logistics.location IS '到达地点';
|
||
COMMENT ON COLUMN logistics.context IS '详情';
|
||
COMMENT ON COLUMN logistics.request_id IS '需求id';
|
||
COMMENT ON COLUMN logistics.create_time IS '入库时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS message (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
from_user_id integer NOT NULL,
|
||
to_user_id integer NOT NULL,
|
||
sent_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"read" boolean NOT NULL DEFAULT FALSE,
|
||
recalled boolean NOT NULL DEFAULT FALSE,
|
||
content text NOT NULL
|
||
);
|
||
|
||
COMMENT ON TABLE message IS '用户消息';
|
||
COMMENT ON COLUMN message.id IS '主键';
|
||
COMMENT ON COLUMN message.from_user_id IS '发出用户id';
|
||
COMMENT ON COLUMN message.to_user_id IS '接收用户id';
|
||
COMMENT ON COLUMN message.sent_time IS '发出时间';
|
||
COMMENT ON COLUMN message.read IS '是否已读';
|
||
COMMENT ON COLUMN message.recalled IS '是否已撤回';
|
||
COMMENT ON COLUMN message.content IS '内容';
|
||
|
||
CREATE TABLE IF NOT EXISTS notice (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
content text NOT NULL,
|
||
verified boolean NOT NULL DEFAULT FALSE,
|
||
publish_time timestamp NOT NULL default CURRENT_TIMESTAMP
|
||
);
|
||
|
||
COMMENT ON TABLE notice IS '系统通知';
|
||
COMMENT ON COLUMN notice.id IS '主键';
|
||
COMMENT ON COLUMN notice.content IS '内容';
|
||
COMMENT ON COLUMN notice.verified IS '是否已审核';
|
||
COMMENT ON COLUMN notice.publish_time IS '发布时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS operate_log (
|
||
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
operate_type char(1) NOT NULL,
|
||
target_type integer NOT NULL,
|
||
related_id integer NOT NULL,
|
||
operater_id integer NOT NULL,
|
||
description varchar(500),
|
||
operate_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
COMMENT ON TABLE operate_log IS '操作日志';
|
||
COMMENT ON COLUMN operate_log.id IS '主键';
|
||
COMMENT ON COLUMN operate_log.operate_type IS '操作类型：c-创建，u-修改，d-删除';
|
||
COMMENT ON COLUMN operate_log.target_type IS '目标类型：0-行政区划，1-商品分类。。。';
|
||
COMMENT ON COLUMN operate_log.related_id IS '关联id，目标表主键';
|
||
COMMENT ON COLUMN operate_log.operater_id IS '管理员id';
|
||
COMMENT ON COLUMN operate_log.description IS '操作描述';
|
||
COMMENT ON COLUMN operate_log.operate_time IS '操作时间';
|
||
COMMENT ON COLUMN operate_log.create_time IS '入库时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS refresh_token (
|
||
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
user_id integer NOT NULL,
|
||
system_role char(1) NOT NULL,
|
||
token varchar(50) NOT NULL,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
expires_at timestamp NOT NULL
|
||
);
|
||
|
||
COMMENT ON TABLE refresh_token IS '刷新令牌';
|
||
COMMENT ON COLUMN refresh_token.id IS '主键';
|
||
COMMENT ON COLUMN refresh_token.user_id IS '用户/管理员id';
|
||
COMMENT ON COLUMN refresh_token.system_role IS '系统角色：a-管理员，u-用户';
|
||
COMMENT ON COLUMN refresh_token.token IS '令牌';
|
||
COMMENT ON COLUMN refresh_token.create_time IS '创建时间';
|
||
COMMENT ON COLUMN refresh_token.expires_at IS '失效时间';
|