331 lines
15 KiB
SQL
331 lines
15 KiB
SQL
-- ----------------------------
|
||
-- App端数据库表结构 (PostgreSQL版本)
|
||
-- 适用于 RuoYi-Vue-Plus 5.X 版本
|
||
-- ----------------------------
|
||
|
||
-- 1、App端用户信息表
|
||
CREATE TABLE app_user (
|
||
user_id BIGINT NOT NULL,
|
||
user_name VARCHAR(30) NOT NULL,
|
||
nick_name VARCHAR(30) NOT NULL,
|
||
phone VARCHAR(11) DEFAULT '',
|
||
email VARCHAR(50) DEFAULT '',
|
||
sex CHAR(1) DEFAULT '0',
|
||
avatar BIGINT,
|
||
password VARCHAR(100) DEFAULT '',
|
||
status CHAR(1) DEFAULT '0',
|
||
del_flag CHAR(1) DEFAULT '0',
|
||
login_ip VARCHAR(128) DEFAULT '',
|
||
login_date TIMESTAMP,
|
||
register_time TIMESTAMP,
|
||
create_by BIGINT,
|
||
create_time TIMESTAMP,
|
||
update_by BIGINT,
|
||
update_time TIMESTAMP,
|
||
remark VARCHAR(500),
|
||
CONSTRAINT pk_app_user PRIMARY KEY (user_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_user IS 'App端用户信息表';
|
||
COMMENT ON COLUMN app_user.user_id IS 'App用户ID';
|
||
COMMENT ON COLUMN app_user.account IS '用户账号';
|
||
COMMENT ON COLUMN app_user.nick_name IS '用户昵称';
|
||
COMMENT ON COLUMN app_user.phone IS '手机号码';
|
||
COMMENT ON COLUMN app_user.email IS '用户邮箱';
|
||
COMMENT ON COLUMN app_user.sex IS '用户性别（0男 1女 2未知）';
|
||
COMMENT ON COLUMN app_user.avatar IS '头像地址';
|
||
COMMENT ON COLUMN app_user.password IS '密码';
|
||
COMMENT ON COLUMN app_user.status IS '帐号状态（0正常 1停用）';
|
||
COMMENT ON COLUMN app_user.del_flag IS '删除标志（0代表存在 1代表删除）';
|
||
COMMENT ON COLUMN app_user.login_ip IS '最后登录IP';
|
||
COMMENT ON COLUMN app_user.login_date IS '最后登录时间';
|
||
COMMENT ON COLUMN app_user.register_time IS '注册时间';
|
||
COMMENT ON COLUMN app_user.create_by IS '创建者';
|
||
COMMENT ON COLUMN app_user.create_time IS '创建时间';
|
||
COMMENT ON COLUMN app_user.update_by IS '更新者';
|
||
COMMENT ON COLUMN app_user.update_time IS '更新时间';
|
||
COMMENT ON COLUMN app_user.remark IS '备注';
|
||
|
||
INSERT INTO app_user VALUES (1, 'appuser', 'App测试用户', '15888888888', 'appuser@qq.com', '1', NULL,
|
||
'$2a$10$7JB720yubVSOfvVMe6LeNOKgEoXAw7tCxJRET6pVuiDDz/LQ4Gqk.',
|
||
'0', '0', '127.0.0.1', NOW(), NOW(), 1, NOW(), NULL, NULL, 'App端测试用户');
|
||
|
||
-- 2、App端角色信息表
|
||
CREATE TABLE app_role (
|
||
role_id BIGINT NOT NULL,
|
||
role_name VARCHAR(30) NOT NULL,
|
||
role_key VARCHAR(100) NOT NULL,
|
||
role_sort INTEGER NOT NULL,
|
||
status CHAR(1) NOT NULL,
|
||
del_flag CHAR(1) DEFAULT '0',
|
||
create_by BIGINT,
|
||
create_time TIMESTAMP,
|
||
update_by BIGINT,
|
||
update_time TIMESTAMP,
|
||
remark VARCHAR(500),
|
||
CONSTRAINT pk_app_role PRIMARY KEY (role_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_role IS 'App端角色信息表';
|
||
COMMENT ON COLUMN app_role.role_id IS 'App角色ID';
|
||
COMMENT ON COLUMN app_role.role_name IS '角色名称';
|
||
COMMENT ON COLUMN app_role.role_key IS '角色权限字符串';
|
||
COMMENT ON COLUMN app_role.role_sort IS '显示顺序';
|
||
COMMENT ON COLUMN app_role.status IS '角色状态（0正常 1停用）';
|
||
COMMENT ON COLUMN app_role.del_flag IS '删除标志（0代表存在 1代表删除）';
|
||
COMMENT ON COLUMN app_role.create_by IS '创建者';
|
||
COMMENT ON COLUMN app_role.create_time IS '创建时间';
|
||
COMMENT ON COLUMN app_role.update_by IS '更新者';
|
||
COMMENT ON COLUMN app_role.update_time IS '更新时间';
|
||
COMMENT ON COLUMN app_role.remark IS '备注';
|
||
|
||
INSERT INTO app_role VALUES (1, 'App普通用户', 'app_user', 1, '0', '0', 1, NOW(), NULL, NULL, 'App端普通用户');
|
||
INSERT INTO app_role VALUES (2, 'App VIP用户', 'app_vip', 2, '0', '0', 1, NOW(), NULL, NULL, 'App端VIP用户');
|
||
|
||
-- 3、App端用户和角色关联表
|
||
CREATE TABLE app_user_role (
|
||
user_id BIGINT NOT NULL,
|
||
role_id BIGINT NOT NULL,
|
||
CONSTRAINT pk_app_user_role PRIMARY KEY (user_id, role_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_user_role IS 'App端用户和角色关联表';
|
||
COMMENT ON COLUMN app_user_role.user_id IS 'App用户ID';
|
||
COMMENT ON COLUMN app_user_role.role_id IS 'App角色ID';
|
||
|
||
INSERT INTO app_user_role VALUES (1, 1);
|
||
|
||
-- 4、App端第三方登录授权表
|
||
CREATE TABLE app_social_auth (
|
||
id BIGINT NOT NULL,
|
||
user_id BIGINT NOT NULL,
|
||
platform VARCHAR(255) NOT NULL,
|
||
platform_uid VARCHAR(255) NOT NULL,
|
||
open_id VARCHAR(255),
|
||
union_id VARCHAR(255),
|
||
access_token TEXT NOT NULL,
|
||
expire_in INTEGER,
|
||
refresh_token VARCHAR(255),
|
||
scope VARCHAR(255),
|
||
create_by BIGINT,
|
||
create_time TIMESTAMP,
|
||
update_by BIGINT,
|
||
update_time TIMESTAMP,
|
||
del_flag CHAR(1) DEFAULT '0',
|
||
CONSTRAINT pk_app_social_auth PRIMARY KEY (id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_social_auth IS 'App端第三方登录授权表';
|
||
COMMENT ON COLUMN app_social_auth.id IS '主键';
|
||
COMMENT ON COLUMN app_social_auth.user_id IS 'App用户ID';
|
||
COMMENT ON COLUMN app_social_auth.platform IS '用户来源平台（wechat_mp:微信小程序 wechat_open:微信开放平台 qq:QQ weibo:微博）';
|
||
COMMENT ON COLUMN app_social_auth.platform_uid IS '表示平台唯一用户标识';
|
||
COMMENT ON COLUMN app_social_auth.open_id IS '微信openid';
|
||
COMMENT ON COLUMN app_social_auth.union_id IS '微信unionid';
|
||
COMMENT ON COLUMN app_social_auth.access_token IS '用户的授权令牌';
|
||
COMMENT ON COLUMN app_social_auth.expire_in IS '用户的授权令牌的有效期';
|
||
COMMENT ON COLUMN app_social_auth.refresh_token IS '刷新令牌';
|
||
COMMENT ON COLUMN app_social_auth.scope IS '授予的权限';
|
||
COMMENT ON COLUMN app_social_auth.create_by IS '创建者';
|
||
COMMENT ON COLUMN app_social_auth.create_time IS '创建时间';
|
||
COMMENT ON COLUMN app_social_auth.update_by IS '更新者';
|
||
COMMENT ON COLUMN app_social_auth.update_time IS '更新时间';
|
||
COMMENT ON COLUMN app_social_auth.del_flag IS '删除标志（0代表存在 1代表删除）';
|
||
|
||
-- 5、App端登录日志表
|
||
CREATE TABLE app_login_log (
|
||
info_id BIGINT NOT NULL,
|
||
user_name VARCHAR(50) DEFAULT '',
|
||
status CHAR(1) DEFAULT '0',
|
||
ipaddr VARCHAR(128) DEFAULT '',
|
||
login_location VARCHAR(255) DEFAULT '',
|
||
browser VARCHAR(50) DEFAULT '',
|
||
os VARCHAR(50) DEFAULT '',
|
||
msg VARCHAR(255) DEFAULT '',
|
||
login_time TIMESTAMP,
|
||
CONSTRAINT pk_app_login_log PRIMARY KEY (info_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_login_log IS 'App端登录日志表';
|
||
COMMENT ON COLUMN app_login_log.info_id IS '访问ID';
|
||
COMMENT ON COLUMN app_login_log.user_name IS '用户账号';
|
||
COMMENT ON COLUMN app_login_log.status IS '登录状态（0成功 1失败）';
|
||
COMMENT ON COLUMN app_login_log.ipaddr IS '登录IP地址';
|
||
COMMENT ON COLUMN app_login_log.login_location IS '登录地点';
|
||
COMMENT ON COLUMN app_login_log.browser IS '浏览器类型';
|
||
COMMENT ON COLUMN app_login_log.os IS '操作系统';
|
||
COMMENT ON COLUMN app_login_log.msg IS '提示消息';
|
||
COMMENT ON COLUMN app_login_log.login_time IS '访问时间';
|
||
|
||
CREATE INDEX idx_app_login_log_s ON app_login_log (status);
|
||
CREATE INDEX idx_app_login_log_lt ON app_login_log (login_time);
|
||
|
||
-- 6、App端操作日志记录
|
||
CREATE TABLE app_oper_log (
|
||
oper_id BIGINT NOT NULL,
|
||
title VARCHAR(50) DEFAULT '',
|
||
business_type SMALLINT DEFAULT 0,
|
||
method VARCHAR(100) DEFAULT '',
|
||
request_method VARCHAR(10) DEFAULT '',
|
||
oper_name VARCHAR(50) DEFAULT '',
|
||
oper_url VARCHAR(255) DEFAULT '',
|
||
oper_ip VARCHAR(128) DEFAULT '',
|
||
oper_location VARCHAR(255) DEFAULT '',
|
||
oper_param TEXT DEFAULT '',
|
||
json_result TEXT DEFAULT '',
|
||
status SMALLINT DEFAULT 0,
|
||
error_msg TEXT DEFAULT '',
|
||
oper_time TIMESTAMP,
|
||
cost_time BIGINT DEFAULT 0,
|
||
CONSTRAINT pk_app_oper_log PRIMARY KEY (oper_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_oper_log IS 'App端操作日志记录';
|
||
COMMENT ON COLUMN app_oper_log.oper_id IS '日志主键';
|
||
COMMENT ON COLUMN app_oper_log.title IS '模块标题';
|
||
COMMENT ON COLUMN app_oper_log.business_type IS '业务类型（0其它 1新增 2修改 3删除）';
|
||
COMMENT ON COLUMN app_oper_log.method IS '方法名称';
|
||
COMMENT ON COLUMN app_oper_log.request_method IS '请求方式';
|
||
COMMENT ON COLUMN app_oper_log.oper_name IS '操作人员';
|
||
COMMENT ON COLUMN app_oper_log.oper_url IS '请求URL';
|
||
COMMENT ON COLUMN app_oper_log.oper_ip IS '主机地址';
|
||
COMMENT ON COLUMN app_oper_log.oper_location IS '操作地点';
|
||
COMMENT ON COLUMN app_oper_log.oper_param IS '请求参数';
|
||
COMMENT ON COLUMN app_oper_log.json_result IS '返回参数';
|
||
COMMENT ON COLUMN app_oper_log.status IS '操作状态（0正常 1异常）';
|
||
COMMENT ON COLUMN app_oper_log.error_msg IS '错误消息';
|
||
COMMENT ON COLUMN app_oper_log.oper_time IS '操作时间';
|
||
COMMENT ON COLUMN app_oper_log.cost_time IS '消耗时间';
|
||
|
||
CREATE INDEX idx_app_oper_log_bt ON app_oper_log (business_type);
|
||
CREATE INDEX idx_app_oper_log_s ON app_oper_log (status);
|
||
CREATE INDEX idx_app_oper_log_ot ON app_oper_log (oper_time);
|
||
|
||
-- 7、App端设备信息表
|
||
CREATE TABLE app_device (
|
||
device_id BIGINT NOT NULL,
|
||
user_id BIGINT NOT NULL,
|
||
device_type VARCHAR(20) NOT NULL,
|
||
device_name VARCHAR(100) DEFAULT '',
|
||
device_model VARCHAR(100) DEFAULT '',
|
||
device_brand VARCHAR(50) DEFAULT '',
|
||
os_version VARCHAR(50) DEFAULT '',
|
||
app_version VARCHAR(50) DEFAULT '',
|
||
push_token VARCHAR(255) DEFAULT '',
|
||
is_active CHAR(1) DEFAULT '1',
|
||
last_active_time TIMESTAMP,
|
||
create_time TIMESTAMP,
|
||
update_time TIMESTAMP,
|
||
CONSTRAINT pk_app_device PRIMARY KEY (device_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_device IS 'App端设备信息表';
|
||
COMMENT ON COLUMN app_device.device_id IS '设备ID';
|
||
COMMENT ON COLUMN app_device.user_id IS 'App用户ID';
|
||
COMMENT ON COLUMN app_device.device_type IS '设备类型（ios android h5）';
|
||
COMMENT ON COLUMN app_device.device_name IS '设备名称';
|
||
COMMENT ON COLUMN app_device.device_model IS '设备型号';
|
||
COMMENT ON COLUMN app_device.device_brand IS '设备品牌';
|
||
COMMENT ON COLUMN app_device.os_version IS '操作系统版本';
|
||
COMMENT ON COLUMN app_device.app_version IS 'App版本';
|
||
COMMENT ON COLUMN app_device.push_token IS '推送令牌';
|
||
COMMENT ON COLUMN app_device.is_active IS '是否活跃（0否 1是）';
|
||
COMMENT ON COLUMN app_device.last_active_time IS '最后活跃时间';
|
||
COMMENT ON COLUMN app_device.create_time IS '创建时间';
|
||
COMMENT ON COLUMN app_device.update_time IS '更新时间';
|
||
|
||
CREATE INDEX idx_app_device_user_id ON app_device (user_id);
|
||
CREATE INDEX idx_app_device_type ON app_device (device_type);
|
||
|
||
-- 8、App端消息推送表
|
||
CREATE TABLE app_message (
|
||
message_id BIGINT NOT NULL,
|
||
title VARCHAR(100) NOT NULL,
|
||
content TEXT,
|
||
message_type VARCHAR(20) DEFAULT 'system',
|
||
target_type VARCHAR(20) DEFAULT 'all',
|
||
target_ids VARCHAR(1000) DEFAULT '',
|
||
send_time TIMESTAMP,
|
||
status CHAR(1) DEFAULT '0',
|
||
create_by BIGINT,
|
||
create_time TIMESTAMP,
|
||
update_by BIGINT,
|
||
update_time TIMESTAMP,
|
||
CONSTRAINT pk_app_message PRIMARY KEY (message_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_message IS 'App端消息推送表';
|
||
COMMENT ON COLUMN app_message.message_id IS '消息ID';
|
||
COMMENT ON COLUMN app_message.title IS '消息标题';
|
||
COMMENT ON COLUMN app_message.content IS '消息内容';
|
||
COMMENT ON COLUMN app_message.message_type IS '消息类型（system:系统消息 notice:通知 activity:活动）';
|
||
COMMENT ON COLUMN app_message.target_type IS '推送目标（all:全部用户 user:指定用户 role:指定角色）';
|
||
COMMENT ON COLUMN app_message.target_ids IS '目标ID列表（用户ID或角色ID，逗号分隔）';
|
||
COMMENT ON COLUMN app_message.send_time IS '发送时间';
|
||
COMMENT ON COLUMN app_message.status IS '状态（0待发送 1已发送 2发送失败）';
|
||
COMMENT ON COLUMN app_message.create_by IS '创建者';
|
||
COMMENT ON COLUMN app_message.create_time IS '创建时间';
|
||
COMMENT ON COLUMN app_message.update_by IS '更新者';
|
||
COMMENT ON COLUMN app_message.update_time IS '更新时间';
|
||
|
||
CREATE INDEX idx_app_message_type ON app_message (message_type);
|
||
CREATE INDEX idx_app_message_status ON app_message (status);
|
||
|
||
-- 9、App端用户消息读取记录表
|
||
CREATE TABLE app_user_message (
|
||
id BIGINT NOT NULL,
|
||
user_id BIGINT NOT NULL,
|
||
message_id BIGINT NOT NULL,
|
||
is_read CHAR(1) DEFAULT '0',
|
||
read_time TIMESTAMP,
|
||
create_time TIMESTAMP,
|
||
CONSTRAINT pk_app_user_message PRIMARY KEY (id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_user_message IS 'App端用户消息读取记录表';
|
||
COMMENT ON COLUMN app_user_message.id IS '主键';
|
||
COMMENT ON COLUMN app_user_message.user_id IS 'App用户ID';
|
||
COMMENT ON COLUMN app_user_message.message_id IS '消息ID';
|
||
COMMENT ON COLUMN app_user_message.is_read IS '是否已读（0未读 1已读）';
|
||
COMMENT ON COLUMN app_user_message.read_time IS '读取时间';
|
||
COMMENT ON COLUMN app_user_message.create_time IS '创建时间';
|
||
|
||
CREATE INDEX idx_app_user_message_user ON app_user_message (user_id);
|
||
CREATE INDEX idx_app_user_message_msg ON app_user_message (message_id);
|
||
CREATE INDEX idx_app_user_message_read ON app_user_message (is_read);
|
||
|
||
-- 10、App版本信息表
|
||
CREATE TABLE app_version (
|
||
version_id BIGINT NOT NULL,
|
||
version_code INTEGER NOT NULL,
|
||
version_name VARCHAR(20) NOT NULL,
|
||
update_content TEXT,
|
||
download_url VARCHAR(500) NOT NULL,
|
||
is_force CHAR(1) DEFAULT '0',
|
||
platform VARCHAR(10) DEFAULT 'android',
|
||
create_time TIMESTAMP,
|
||
CONSTRAINT pk_app_version PRIMARY KEY (version_id)
|
||
);
|
||
|
||
COMMENT ON TABLE app_version IS 'App版本信息表';
|
||
COMMENT ON COLUMN app_version.version_id IS '版本ID';
|
||
COMMENT ON COLUMN app_version.version_code IS '版本号（数字）';
|
||
COMMENT ON COLUMN app_version.version_name IS '版本名（如1.0.0）';
|
||
COMMENT ON COLUMN app_version.update_content IS '更新内容';
|
||
COMMENT ON COLUMN app_version.download_url IS '下载地址';
|
||
COMMENT ON COLUMN app_version.is_force IS '是否强制更新（0否 1是）';
|
||
COMMENT ON COLUMN app_version.platform IS '平台（android、ios）';
|
||
COMMENT ON COLUMN app_version.create_time IS '创建时间';
|
||
|
||
-- 创建序列
|
||
CREATE SEQUENCE seq_app_user START WITH 2 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_role START WITH 3 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_social_auth START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_login_log START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_oper_log START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_device START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_message START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_user_message START WITH 1 INCREMENT BY 1;
|
||
CREATE SEQUENCE seq_app_version START WITH 1 INCREMENT BY 1;
|
||
|
||
COMMIT; |