465 lines
26 KiB
SQL
465 lines
26 KiB
SQL
-- 学校租户表
|
||
DROP TABLE IF EXISTS upms.tb_sys_tenant CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_tenant (
|
||
tenant_id VARCHAR(64) PRIMARY KEY,
|
||
parent_tenant_id VARCHAR(64),
|
||
tenant_name VARCHAR(128) NOT NULL,
|
||
tenant_type VARCHAR(32) NOT NULL,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_tenant IS '学校租户表';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.parent_tenant_id IS '父租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.tenant_name IS '租户名称';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.tenant_type IS '租户类型';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.status IS '租户状态';
|
||
COMMENT ON COLUMN upms.tb_sys_tenant.created_at IS '创建时间';
|
||
|
||
DROP TABLE IF EXISTS upms.tb_sys_dept CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_dept (
|
||
dept_id VARCHAR(64) PRIMARY KEY,
|
||
parent_dept_id VARCHAR(64),
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
dept_name VARCHAR(128) NOT NULL,
|
||
dept_type VARCHAR(32) NOT NULL,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
dept_path VARCHAR(255) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_dept IS '组织部门表';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.parent_dept_id IS '父部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.dept_name IS '部门名称';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.dept_type IS '部门类型';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_sys_dept.created_at IS '创建时间';
|
||
|
||
DROP TABLE IF EXISTS upms.tb_school_class_course_rel CASCADE;
|
||
DROP TABLE IF EXISTS upms.tb_school_class_member CASCADE;
|
||
DROP TABLE IF EXISTS upms.tb_school_class CASCADE;
|
||
DROP TABLE IF EXISTS upms.tb_sys_user_role CASCADE;
|
||
DROP TABLE IF EXISTS upms.tb_sys_user CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_user (
|
||
user_id VARCHAR(64) PRIMARY KEY,
|
||
username VARCHAR(64) UNIQUE NOT NULL,
|
||
display_name VARCHAR(128) NOT NULL,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
mobile_phone VARCHAR(20),
|
||
mobile_bind_status VARCHAR(16) NOT NULL DEFAULT 'UNBOUND',
|
||
mobile_verified_at TIMESTAMP,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
dept_id VARCHAR(64) NOT NULL,
|
||
dept_path VARCHAR(255) NOT NULL,
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_user IS '系统用户表';
|
||
COMMENT ON COLUMN upms.tb_sys_user.user_id IS '用户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user.username IS '用户名';
|
||
COMMENT ON COLUMN upms.tb_sys_user.display_name IS '显示名称';
|
||
COMMENT ON COLUMN upms.tb_sys_user.password_hash IS '密码哈希';
|
||
COMMENT ON COLUMN upms.tb_sys_user.mobile_phone IS '手机号';
|
||
COMMENT ON COLUMN upms.tb_sys_user.mobile_bind_status IS '手机号绑定状态';
|
||
COMMENT ON COLUMN upms.tb_sys_user.mobile_verified_at IS '手机号验证时间';
|
||
COMMENT ON COLUMN upms.tb_sys_user.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_user.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_user.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_sys_user.status IS '用户状态';
|
||
COMMENT ON COLUMN upms.tb_sys_user.created_at IS '创建时间';
|
||
CREATE TABLE IF NOT EXISTS upms.tb_school_class (
|
||
class_id VARCHAR(64) PRIMARY KEY,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
dept_id VARCHAR(64) NOT NULL,
|
||
class_code VARCHAR(64),
|
||
class_name VARCHAR(128) NOT NULL,
|
||
grade_code VARCHAR(32),
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
adcode VARCHAR(12),
|
||
tenant_path VARCHAR(255),
|
||
dept_path VARCHAR(255),
|
||
created_by VARCHAR(64),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT chk_school_class_status
|
||
CHECK (status IN ('ACTIVE', 'INACTIVE', 'ARCHIVED')),
|
||
CONSTRAINT fk_school_class_dept
|
||
FOREIGN KEY (dept_id) REFERENCES upms.tb_sys_dept(dept_id),
|
||
CONSTRAINT fk_school_class_created_by
|
||
FOREIGN KEY (created_by) REFERENCES upms.tb_sys_user(user_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_school_class IS '班级主表';
|
||
COMMENT ON COLUMN upms.tb_school_class.class_id IS '班级ID';
|
||
COMMENT ON COLUMN upms.tb_school_class.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_school_class.dept_id IS '所属部门ID';
|
||
COMMENT ON COLUMN upms.tb_school_class.class_code IS '班级编码';
|
||
COMMENT ON COLUMN upms.tb_school_class.class_name IS '班级名称';
|
||
COMMENT ON COLUMN upms.tb_school_class.grade_code IS '年级编码';
|
||
COMMENT ON COLUMN upms.tb_school_class.status IS '班级状态';
|
||
COMMENT ON COLUMN upms.tb_school_class.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_school_class.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_school_class.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_school_class.created_by IS '创建人';
|
||
COMMENT ON COLUMN upms.tb_school_class.created_at IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_school_class.updated_at IS '更新时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS upms.tb_school_class_member (
|
||
class_id VARCHAR(64) NOT NULL,
|
||
user_id VARCHAR(64) NOT NULL,
|
||
member_role VARCHAR(16) NOT NULL DEFAULT 'STUDENT',
|
||
member_status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
left_at TIMESTAMP,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (class_id, user_id),
|
||
CONSTRAINT chk_school_class_member_role
|
||
CHECK (member_role IN ('STUDENT', 'HEAD_TEACHER', 'TEACHER', 'ASSISTANT')),
|
||
CONSTRAINT chk_school_class_member_status
|
||
CHECK (member_status IN ('ACTIVE', 'LEFT', 'SUSPENDED')),
|
||
CONSTRAINT chk_school_class_member_left_at
|
||
CHECK (left_at IS NULL OR left_at >= joined_at),
|
||
CONSTRAINT fk_school_class_member_class
|
||
FOREIGN KEY (class_id) REFERENCES upms.tb_school_class(class_id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_school_class_member_user
|
||
FOREIGN KEY (user_id) REFERENCES upms.tb_sys_user(user_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_school_class_member IS '班级成员表';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.class_id IS '班级ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.user_id IS '成员用户ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.member_role IS '成员角色';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.member_status IS '成员状态';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.joined_at IS '加入时间';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.left_at IS '离开时间';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.created_at IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_school_class_member.updated_at IS '更新时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS upms.tb_school_class_course_rel (
|
||
class_id VARCHAR(64) NOT NULL,
|
||
course_id VARCHAR(64) NOT NULL,
|
||
relation_status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (class_id, course_id),
|
||
CONSTRAINT chk_school_class_course_rel_status
|
||
CHECK (relation_status IN ('ACTIVE', 'INACTIVE')),
|
||
CONSTRAINT fk_school_class_course_rel_class
|
||
FOREIGN KEY (class_id) REFERENCES upms.tb_school_class(class_id) ON DELETE CASCADE
|
||
);
|
||
COMMENT ON TABLE upms.tb_school_class_course_rel IS '班级课程关联表';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.class_id IS '班级ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.course_id IS '课程ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.relation_status IS '关联状态';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.created_at IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_school_class_course_rel.updated_at IS '更新时间';
|
||
|
||
DROP TABLE IF EXISTS upms.tb_sys_file CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_file (
|
||
file_id VARCHAR(64) PRIMARY KEY,
|
||
media_type VARCHAR(32) NOT NULL,
|
||
object_key VARCHAR(512) NOT NULL,
|
||
file_name VARCHAR(256),
|
||
mime_type VARCHAR(128),
|
||
file_size BIGINT,
|
||
file_hash VARCHAR(128),
|
||
duration_ms INTEGER,
|
||
uploaded_by VARCHAR(64),
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT chk_sys_file_media_type
|
||
CHECK (media_type IN ('IMAGE', 'AUDIO', 'VIDEO', 'DOCUMENT', 'OTHER')),
|
||
CONSTRAINT fk_sys_file_uploaded_by
|
||
FOREIGN KEY (uploaded_by) REFERENCES upms.tb_sys_user(user_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_file IS '系统文件资源表';
|
||
COMMENT ON COLUMN upms.tb_sys_file.file_id IS '文件ID';
|
||
COMMENT ON COLUMN upms.tb_sys_file.media_type IS '媒体类型';
|
||
COMMENT ON COLUMN upms.tb_sys_file.object_key IS '对象存储Key';
|
||
COMMENT ON COLUMN upms.tb_sys_file.file_name IS '文件名';
|
||
COMMENT ON COLUMN upms.tb_sys_file.mime_type IS 'MIME类型';
|
||
COMMENT ON COLUMN upms.tb_sys_file.file_size IS '文件大小（字节）';
|
||
COMMENT ON COLUMN upms.tb_sys_file.file_hash IS '文件哈希';
|
||
COMMENT ON COLUMN upms.tb_sys_file.duration_ms IS '音频/视频时长（毫秒）';
|
||
COMMENT ON COLUMN upms.tb_sys_file.uploaded_by IS '上传人ID';
|
||
COMMENT ON COLUMN upms.tb_sys_file.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_file.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_file.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_file.created_at IS '创建时间';
|
||
|
||
DROP TABLE IF EXISTS upms.tb_sys_role CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_role (
|
||
role_id VARCHAR(64) PRIMARY KEY,
|
||
role_code VARCHAR(64) UNIQUE NOT NULL,
|
||
role_name VARCHAR(128) NOT NULL,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_role IS '角色表';
|
||
COMMENT ON COLUMN upms.tb_sys_role.role_id IS '角色ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role.role_code IS '角色编码';
|
||
COMMENT ON COLUMN upms.tb_sys_role.role_name IS '角色名称';
|
||
COMMENT ON COLUMN upms.tb_sys_role.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_role.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_role.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_sys_role.created_at IS '创建时间';
|
||
DROP TABLE IF EXISTS upms.tb_sys_user_role CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_user_role (
|
||
user_id VARCHAR(64) NOT NULL,
|
||
role_id VARCHAR(64) NOT NULL,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (user_id, role_id),
|
||
CONSTRAINT fk_sys_user_role_user
|
||
FOREIGN KEY (user_id) REFERENCES upms.tb_sys_user(user_id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_sys_user_role_role
|
||
FOREIGN KEY (role_id) REFERENCES upms.tb_sys_role(role_id) ON DELETE CASCADE
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_user_role IS '用户角色关系表';
|
||
COMMENT ON COLUMN upms.tb_sys_user_role.user_id IS '用户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user_role.role_id IS '角色ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user_role.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_user_role.created_at IS '创建时间';
|
||
|
||
|
||
DROP TABLE IF EXISTS upms.tb_sys_menu CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_menu (
|
||
route_id VARCHAR(64) PRIMARY KEY,
|
||
parent_route_id VARCHAR(64),
|
||
route_path VARCHAR(255) NOT NULL,
|
||
route_name VARCHAR(128) NOT NULL,
|
||
component_key VARCHAR(128) NOT NULL,
|
||
layout_type VARCHAR(32) NOT NULL,
|
||
title VARCHAR(128) NOT NULL,
|
||
icon VARCHAR(64),
|
||
permission_code VARCHAR(128),
|
||
hidden BOOLEAN NOT NULL DEFAULT FALSE,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_menu IS '菜单表';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.route_id IS '路由ID';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.parent_route_id IS '父路由ID';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.route_path IS '路由路径';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.route_name IS '路由名称';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.component_key IS '组件标识';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.layout_type IS '布局类型';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.title IS '菜单标题';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.icon IS '图标';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.permission_code IS '权限编码';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.hidden IS '是否隐藏';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_sys_menu.created_at IS '创建时间';
|
||
DROP TABLE IF EXISTS upms.tb_sys_role_menu CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_role_menu (
|
||
role_id VARCHAR(64) NOT NULL,
|
||
route_id VARCHAR(64) NOT NULL,
|
||
adcode VARCHAR(12) NOT NULL,
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255) NOT NULL,
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (role_id, route_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_role_menu IS '角色菜单授权表';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.role_id IS '角色ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.route_id IS '路由ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN upms.tb_sys_role_menu.created_at IS '创建时间';
|
||
|
||
DROP TABLE IF EXISTS upms.tb_sys_area CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_area (
|
||
id BIGINT PRIMARY KEY,
|
||
pid BIGINT NOT NULL,
|
||
name VARCHAR(128) NOT NULL,
|
||
letter VARCHAR(32),
|
||
adcode BIGINT NOT NULL,
|
||
location VARCHAR(255),
|
||
area_sort INTEGER,
|
||
area_status VARCHAR(8) NOT NULL DEFAULT '1',
|
||
area_type VARCHAR(8) NOT NULL DEFAULT '2',
|
||
hot VARCHAR(8) NOT NULL DEFAULT '0',
|
||
city_code VARCHAR(16),
|
||
create_by VARCHAR(64),
|
||
create_time TIMESTAMP,
|
||
update_by VARCHAR(64),
|
||
update_time TIMESTAMP,
|
||
del_flag VARCHAR(8) NOT NULL DEFAULT '0'
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_area IS '行政区划表';
|
||
COMMENT ON COLUMN upms.tb_sys_area.id IS '主键ID';
|
||
COMMENT ON COLUMN upms.tb_sys_area.pid IS '父级行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_area.name IS '名称';
|
||
COMMENT ON COLUMN upms.tb_sys_area.letter IS '首字母';
|
||
COMMENT ON COLUMN upms.tb_sys_area.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_area.location IS '经纬度';
|
||
COMMENT ON COLUMN upms.tb_sys_area.area_sort IS '排序';
|
||
COMMENT ON COLUMN upms.tb_sys_area.area_status IS '状态';
|
||
COMMENT ON COLUMN upms.tb_sys_area.area_type IS '层级类型';
|
||
COMMENT ON COLUMN upms.tb_sys_area.hot IS '是否热门';
|
||
COMMENT ON COLUMN upms.tb_sys_area.city_code IS '城市区号';
|
||
COMMENT ON COLUMN upms.tb_sys_area.create_by IS '创建人';
|
||
COMMENT ON COLUMN upms.tb_sys_area.create_time IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_sys_area.update_by IS '更新人';
|
||
COMMENT ON COLUMN upms.tb_sys_area.update_time IS '更新时间';
|
||
COMMENT ON COLUMN upms.tb_sys_area.del_flag IS '删除标记';
|
||
DROP TABLE IF EXISTS upms.tb_sys_message_recipient CASCADE;
|
||
DROP TABLE IF EXISTS upms.tb_sys_message CASCADE;
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_message (
|
||
message_id VARCHAR(64) PRIMARY KEY,
|
||
message_type VARCHAR(32) NOT NULL DEFAULT 'INFO',
|
||
biz_type VARCHAR(64) NOT NULL,
|
||
title VARCHAR(256) NOT NULL,
|
||
content TEXT NOT NULL,
|
||
content_object_type VARCHAR(64) NOT NULL,
|
||
content_object_id VARCHAR(64) NOT NULL,
|
||
web_jump_url VARCHAR(512) NOT NULL,
|
||
send_channel VARCHAR(16) NOT NULL DEFAULT 'INBOX',
|
||
sender_user_id VARCHAR(64),
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255),
|
||
message_status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
send_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
expire_at TIMESTAMP,
|
||
ext_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT chk_sys_message_type
|
||
CHECK (message_type IN ('TODO', 'INFO', 'ALERT', 'SYSTEM')),
|
||
CONSTRAINT chk_sys_message_channel
|
||
CHECK (send_channel IN ('INBOX')),
|
||
CONSTRAINT chk_sys_message_status
|
||
CHECK (message_status IN ('ACTIVE', 'CANCELLED', 'EXPIRED')),
|
||
CONSTRAINT chk_sys_message_url
|
||
CHECK (web_jump_url LIKE '/%'),
|
||
CONSTRAINT chk_sys_message_expire
|
||
CHECK (expire_at IS NULL OR expire_at >= send_at),
|
||
CONSTRAINT chk_sys_message_ext_json
|
||
CHECK (jsonb_typeof(ext_json) = 'object'),
|
||
CONSTRAINT fk_sys_message_sender
|
||
FOREIGN KEY (sender_user_id) REFERENCES upms.tb_sys_user(user_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_message IS '站内信消息主表';
|
||
COMMENT ON COLUMN upms.tb_sys_message.message_id IS '消息ID';
|
||
COMMENT ON COLUMN upms.tb_sys_message.message_type IS '消息类型（TODO/INFO/ALERT/SYSTEM）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.biz_type IS '业务类型（如 SUBJECTIVE_REVIEW）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.title IS '消息标题';
|
||
COMMENT ON COLUMN upms.tb_sys_message.content IS '消息正文';
|
||
COMMENT ON COLUMN upms.tb_sys_message.content_object_type IS '内容对象类型（如 GRADING_TASK/TENANT）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.content_object_id IS '内容对象ID（用于业务定位）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.web_jump_url IS 'Web跳转URL（前端点击消息后跳转）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.send_channel IS '发送通道（当前仅INBOX）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.sender_user_id IS '发送人ID（系统消息可为空）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN upms.tb_sys_message.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_message.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_message.message_status IS '消息状态（ACTIVE/CANCELLED/EXPIRED）';
|
||
COMMENT ON COLUMN upms.tb_sys_message.send_at IS '发送时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message.expire_at IS '过期时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message.ext_json IS '扩展字段JSON';
|
||
COMMENT ON COLUMN upms.tb_sys_message.created_at IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message.updated_at IS '更新时间';
|
||
|
||
CREATE TABLE IF NOT EXISTS upms.tb_sys_message_recipient (
|
||
message_id VARCHAR(64) NOT NULL,
|
||
recipient_user_id VARCHAR(64) NOT NULL,
|
||
delivery_status VARCHAR(16) NOT NULL DEFAULT 'DELIVERED',
|
||
read_status VARCHAR(16) NOT NULL DEFAULT 'UNREAD',
|
||
read_at TIMESTAMP,
|
||
clicked_at TIMESTAMP,
|
||
read_source VARCHAR(16),
|
||
tenant_id VARCHAR(64) NOT NULL,
|
||
tenant_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (message_id, recipient_user_id),
|
||
CONSTRAINT chk_sys_message_recipient_delivery
|
||
CHECK (delivery_status IN ('DELIVERED', 'FAILED', 'RECALLED')),
|
||
CONSTRAINT chk_sys_message_recipient_read
|
||
CHECK (read_status IN ('UNREAD', 'READ', 'ARCHIVED')),
|
||
CONSTRAINT chk_sys_message_recipient_read_source
|
||
CHECK (read_source IS NULL OR read_source IN ('WEB', 'APP', 'MINI_PROGRAM')),
|
||
CONSTRAINT chk_sys_message_recipient_read_at
|
||
CHECK (
|
||
(read_status = 'UNREAD' AND read_at IS NULL)
|
||
OR (read_status IN ('READ', 'ARCHIVED') AND read_at IS NOT NULL)
|
||
),
|
||
CONSTRAINT chk_sys_message_recipient_click_at
|
||
CHECK (clicked_at IS NULL OR read_at IS NULL OR clicked_at >= read_at),
|
||
CONSTRAINT fk_sys_message_recipient_message
|
||
FOREIGN KEY (message_id) REFERENCES upms.tb_sys_message(message_id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_sys_message_recipient_user
|
||
FOREIGN KEY (recipient_user_id) REFERENCES upms.tb_sys_user(user_id)
|
||
);
|
||
COMMENT ON TABLE upms.tb_sys_message_recipient IS '站内信收件人状态表';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.message_id IS '消息ID';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.recipient_user_id IS '收件人用户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.delivery_status IS '投递状态（DELIVERED/FAILED/RECALLED）';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.read_status IS '读取状态（UNREAD/READ/ARCHIVED）';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.read_at IS '读取时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.clicked_at IS '点击跳转时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.read_source IS '读取来源（WEB/APP/MINI_PROGRAM）';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.created_at IS '创建时间';
|
||
COMMENT ON COLUMN upms.tb_sys_message_recipient.updated_at IS '更新时间';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_sys_tenant_adcode ON upms.tb_sys_tenant(adcode);
|
||
CREATE INDEX IF NOT EXISTS idx_dept_tenant ON upms.tb_sys_dept(tenant_id, dept_path);
|
||
CREATE INDEX IF NOT EXISTS idx_user_tenant ON upms.tb_sys_user(tenant_id, dept_id);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uk_sys_user_tenant_mobile ON upms.tb_sys_user(tenant_id, mobile_phone) WHERE mobile_phone IS NOT NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_sys_user_role_user ON upms.tb_sys_user_role(user_id, tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_user_role_role ON upms.tb_sys_user_role(role_id, tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_school_class_tenant_dept ON upms.tb_school_class(tenant_id, dept_id, grade_code);
|
||
CREATE INDEX IF NOT EXISTS idx_school_class_member_tenant_user ON upms.tb_school_class_member(tenant_id, user_id, member_status);
|
||
CREATE INDEX IF NOT EXISTS idx_school_class_course_rel_tenant_course ON upms.tb_school_class_course_rel(tenant_id, course_id, relation_status);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_file_tenant_media ON upms.tb_sys_file(tenant_id, media_type, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_file_uploaded_by ON upms.tb_sys_file(uploaded_by, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_route_tenant ON upms.tb_sys_menu(tenant_id, route_path);
|
||
CREATE INDEX IF NOT EXISTS idx_role_menu_tenant ON upms.tb_sys_role_menu(tenant_id, role_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_area_pid ON upms.tb_sys_area(pid);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_area_adcode ON upms.tb_sys_area(adcode);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_tenant_biz ON upms.tb_sys_message(tenant_id, biz_type, send_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_object ON upms.tb_sys_message(content_object_type, content_object_id);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_status_send_at ON upms.tb_sys_message(message_status, send_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_recipient_user_status ON upms.tb_sys_message_recipient(recipient_user_id, read_status, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_recipient_tenant_user ON upms.tb_sys_message_recipient(tenant_id, recipient_user_id, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_sys_message_recipient_unread ON upms.tb_sys_message_recipient(recipient_user_id, created_at DESC) WHERE read_status = 'UNREAD';
|