张翔
f68d18fbfc
- Add TransactionManagerConfig for reactive transaction management - Add OperationLogWebFilter for operation logging - Remove deprecated AuditLogAspect in favor of WebFilter approach - Optimize service implementations (SysUserService, SysRoleService, etc.) - Enhance audit log functionality with better error handling - Update security configuration and tests - Add operation_log table migration script - Improve IP utility with better validation
408 lines
14 KiB
SQL
408 lines
14 KiB
SQL
-- Novalon管理系统数据库初始化脚本
|
||
-- 版本: V1
|
||
-- 描述: 创建所有核心表结构（合并版）
|
||
|
||
-- ============================================
|
||
-- 用户与角色相关表
|
||
-- ============================================
|
||
|
||
-- 用户表
|
||
CREATE TABLE IF NOT EXISTS sys_user (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(50) NOT NULL UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
email VARCHAR(100),
|
||
phone VARCHAR(20),
|
||
nickname VARCHAR(100),
|
||
status INTEGER DEFAULT 1,
|
||
role_id BIGINT,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 角色表
|
||
CREATE TABLE IF NOT EXISTS sys_role (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
role_name VARCHAR(100) NOT NULL,
|
||
role_key VARCHAR(100) NOT NULL UNIQUE,
|
||
role_sort INTEGER DEFAULT 0,
|
||
status INTEGER DEFAULT 1,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 用户角色关联表（支持多对多关系）
|
||
CREATE TABLE IF NOT EXISTS user_role (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
role_id BIGINT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
created_by VARCHAR(50),
|
||
CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES sys_user(id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES sys_role(id) ON DELETE CASCADE,
|
||
CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
|
||
);
|
||
|
||
-- ============================================
|
||
-- 权限相关表
|
||
-- ============================================
|
||
|
||
-- 权限表
|
||
CREATE TABLE IF NOT EXISTS sys_permission (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
permission_name VARCHAR(100) NOT NULL,
|
||
permission_code VARCHAR(100) NOT NULL UNIQUE,
|
||
resource VARCHAR(200) NOT NULL,
|
||
action VARCHAR(50) NOT NULL,
|
||
description VARCHAR(500),
|
||
status INTEGER DEFAULT 1,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 角色权限关联表
|
||
CREATE TABLE IF NOT EXISTS sys_role_permission (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
role_id BIGINT NOT NULL,
|
||
permission_id BIGINT NOT NULL,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (role_id) REFERENCES sys_role(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (permission_id) REFERENCES sys_permission(id) ON DELETE CASCADE,
|
||
UNIQUE (role_id, permission_id)
|
||
);
|
||
|
||
-- ============================================
|
||
-- 菜单相关表
|
||
-- ============================================
|
||
|
||
-- 菜单表
|
||
CREATE TABLE IF NOT EXISTS sys_menu (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
menu_name VARCHAR(50) NOT NULL,
|
||
parent_id BIGINT DEFAULT 0,
|
||
order_num INTEGER DEFAULT 0,
|
||
menu_type VARCHAR(1) DEFAULT 'C',
|
||
perms VARCHAR(100),
|
||
component VARCHAR(200),
|
||
status INTEGER DEFAULT 1,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 字典相关表
|
||
-- ============================================
|
||
|
||
-- 字典类型表
|
||
CREATE TABLE IF NOT EXISTS sys_dict_type (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
dict_name VARCHAR(100) NOT NULL,
|
||
dict_type VARCHAR(100) NOT NULL UNIQUE,
|
||
status VARCHAR(1) DEFAULT '0',
|
||
remark VARCHAR(500),
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 字典数据表
|
||
CREATE TABLE IF NOT EXISTS sys_dict_data (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
dict_sort INTEGER DEFAULT 0,
|
||
dict_label VARCHAR(100) NOT NULL,
|
||
dict_value VARCHAR(100) NOT NULL,
|
||
dict_type VARCHAR(100) NOT NULL,
|
||
css_class VARCHAR(100),
|
||
list_class VARCHAR(100),
|
||
is_default VARCHAR(1) DEFAULT 'N',
|
||
status VARCHAR(1) DEFAULT '0',
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 字典表（通用字典）
|
||
CREATE TABLE IF NOT EXISTS sys_dictionary (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
type VARCHAR(100) NOT NULL,
|
||
code VARCHAR(100) NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
value VARCHAR(500),
|
||
remark VARCHAR(500),
|
||
sort INTEGER DEFAULT 0,
|
||
create_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 系统配置表
|
||
-- ============================================
|
||
|
||
-- 系统配置表
|
||
CREATE TABLE IF NOT EXISTS sys_config (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
config_name VARCHAR(100) NOT NULL,
|
||
config_key VARCHAR(100) NOT NULL UNIQUE,
|
||
config_value VARCHAR(500) NOT NULL,
|
||
config_type VARCHAR(1) DEFAULT 'N',
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 日志相关表
|
||
-- ============================================
|
||
|
||
-- 登录日志表
|
||
CREATE TABLE IF NOT EXISTS sys_login_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(50),
|
||
ip VARCHAR(50),
|
||
location VARCHAR(255),
|
||
browser VARCHAR(50),
|
||
os VARCHAR(50),
|
||
status VARCHAR(1),
|
||
message VARCHAR(255),
|
||
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 异常日志表
|
||
CREATE TABLE IF NOT EXISTS sys_exception_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(50),
|
||
title VARCHAR(100),
|
||
exception_name VARCHAR(100),
|
||
method_name VARCHAR(255),
|
||
method_params TEXT,
|
||
exception_msg TEXT,
|
||
exception_stack TEXT,
|
||
ip VARCHAR(50),
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 操作日志表
|
||
CREATE TABLE IF NOT EXISTS operation_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(50),
|
||
operation VARCHAR(100),
|
||
method VARCHAR(200),
|
||
params TEXT,
|
||
result TEXT,
|
||
ip VARCHAR(50),
|
||
duration BIGINT,
|
||
status VARCHAR(1) DEFAULT '0',
|
||
error_msg TEXT,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 审计日志表
|
||
CREATE TABLE IF NOT EXISTS audit_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
entity_type VARCHAR(100) NOT NULL,
|
||
entity_id BIGINT,
|
||
operation_type VARCHAR(20) NOT NULL,
|
||
operator VARCHAR(100),
|
||
operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
before_data JSONB,
|
||
after_data JSONB,
|
||
changed_fields TEXT[],
|
||
ip_address VARCHAR(50),
|
||
user_agent TEXT,
|
||
description TEXT,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 审计日志归档表
|
||
CREATE TABLE IF NOT EXISTS audit_log_archive (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
entity_type VARCHAR(100) NOT NULL,
|
||
entity_id BIGINT,
|
||
operation_type VARCHAR(20) NOT NULL,
|
||
operator VARCHAR(100),
|
||
operation_time TIMESTAMP,
|
||
before_data JSONB,
|
||
after_data JSONB,
|
||
changed_fields TEXT[],
|
||
ip_address VARCHAR(50),
|
||
user_agent TEXT,
|
||
description TEXT,
|
||
created_at TIMESTAMP,
|
||
archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 通知与消息表
|
||
-- ============================================
|
||
|
||
-- 系统公告表
|
||
CREATE TABLE IF NOT EXISTS sys_notice (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
notice_title VARCHAR(50) NOT NULL,
|
||
notice_type VARCHAR(1) NOT NULL,
|
||
notice_content TEXT,
|
||
status VARCHAR(1) DEFAULT '0',
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- 用户消息表
|
||
CREATE TABLE IF NOT EXISTS sys_user_message (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
notice_id BIGINT,
|
||
message_title VARCHAR(255),
|
||
message_content TEXT,
|
||
is_read VARCHAR(1) DEFAULT '0',
|
||
read_time TIMESTAMP,
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 文件管理表
|
||
-- ============================================
|
||
|
||
-- 文件管理表
|
||
CREATE TABLE IF NOT EXISTS sys_file (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
file_name VARCHAR(255) NOT NULL,
|
||
file_path VARCHAR(500) NOT NULL,
|
||
file_size BIGINT,
|
||
file_type VARCHAR(100),
|
||
file_extension VARCHAR(10),
|
||
storage_type VARCHAR(50),
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- OAuth2相关表
|
||
-- ============================================
|
||
|
||
-- OAuth2客户端表
|
||
CREATE TABLE IF NOT EXISTS oauth2_client (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
client_id VARCHAR(100) NOT NULL UNIQUE,
|
||
client_secret VARCHAR(255) NOT NULL,
|
||
client_name VARCHAR(100),
|
||
web_server_redirect_uri VARCHAR(500),
|
||
scope VARCHAR(500),
|
||
authorized_grant_types VARCHAR(500),
|
||
access_token_validity_seconds INTEGER,
|
||
refresh_token_validity_seconds INTEGER,
|
||
auto_approve VARCHAR(1) DEFAULT 'false',
|
||
enabled VARCHAR(1) DEFAULT 'true',
|
||
create_by VARCHAR(50),
|
||
update_by VARCHAR(50),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted_at TIMESTAMP
|
||
);
|
||
|
||
-- ============================================
|
||
-- 表注释
|
||
-- ============================================
|
||
|
||
COMMENT ON TABLE sys_user IS '系统用户表';
|
||
COMMENT ON TABLE sys_role IS '系统角色表';
|
||
COMMENT ON TABLE user_role IS '用户角色关联表';
|
||
COMMENT ON TABLE sys_permission IS '系统权限表';
|
||
COMMENT ON TABLE sys_role_permission IS '角色权限关联表';
|
||
COMMENT ON TABLE sys_menu IS '系统菜单表';
|
||
COMMENT ON TABLE sys_dict_type IS '字典类型表';
|
||
COMMENT ON TABLE sys_dict_data IS '字典数据表';
|
||
COMMENT ON TABLE sys_dictionary IS '通用字典表';
|
||
COMMENT ON TABLE sys_config IS '系统配置表';
|
||
COMMENT ON TABLE sys_login_log IS '登录日志表';
|
||
COMMENT ON TABLE sys_exception_log IS '异常日志表';
|
||
COMMENT ON TABLE operation_log IS '操作日志表';
|
||
COMMENT ON TABLE audit_log IS '审计日志表';
|
||
COMMENT ON TABLE audit_log_archive IS '审计日志归档表';
|
||
COMMENT ON TABLE sys_notice IS '系统公告表';
|
||
COMMENT ON TABLE sys_user_message IS '用户消息表';
|
||
COMMENT ON TABLE sys_file IS '文件管理表';
|
||
COMMENT ON TABLE oauth2_client IS 'OAuth2客户端表';
|
||
|
||
COMMENT ON TABLE sys_exception_log IS '异常日志表';
|
||
COMMENT ON COLUMN sys_exception_log.id IS '主键ID';
|
||
COMMENT ON COLUMN sys_exception_log.username IS '操作用户';
|
||
COMMENT ON COLUMN sys_exception_log.title IS '异常标题';
|
||
COMMENT ON COLUMN sys_exception_log.exception_name IS '异常名称';
|
||
COMMENT ON COLUMN sys_exception_log.method_name IS '方法名称';
|
||
COMMENT ON COLUMN sys_exception_log.method_params IS '方法参数';
|
||
COMMENT ON COLUMN sys_exception_log.exception_msg IS '异常消息';
|
||
COMMENT ON COLUMN sys_exception_log.exception_stack IS '异常堆栈';
|
||
COMMENT ON COLUMN sys_exception_log.ip IS 'IP地址';
|
||
COMMENT ON COLUMN sys_exception_log.create_time IS '创建时间';
|
||
|
||
COMMENT ON TABLE audit_log IS '审计日志表';
|
||
COMMENT ON COLUMN audit_log.id IS '主键ID';
|
||
COMMENT ON COLUMN audit_log.entity_type IS '实体类型（如User, Role等）';
|
||
COMMENT ON COLUMN audit_log.entity_id IS '实体ID';
|
||
COMMENT ON COLUMN audit_log.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
|
||
COMMENT ON COLUMN audit_log.operator IS '操作人';
|
||
COMMENT ON COLUMN audit_log.operation_time IS '操作时间';
|
||
COMMENT ON COLUMN audit_log.before_data IS '变更前数据（JSON格式）';
|
||
COMMENT ON COLUMN audit_log.after_data IS '变更后数据（JSON格式）';
|
||
COMMENT ON COLUMN audit_log.changed_fields IS '变更字段列表';
|
||
COMMENT ON COLUMN audit_log.ip_address IS 'IP地址';
|
||
COMMENT ON COLUMN audit_log.description IS '操作描述';
|
||
COMMENT ON COLUMN audit_log.created_at IS '记录创建时间';
|
||
|
||
COMMENT ON TABLE audit_log_archive IS '审计日志归档表';
|
||
COMMENT ON COLUMN audit_log_archive.id IS '主键ID';
|
||
COMMENT ON COLUMN audit_log_archive.entity_type IS '实体类型（如User, Role等）';
|
||
COMMENT ON COLUMN audit_log_archive.entity_id IS '实体ID';
|
||
COMMENT ON COLUMN audit_log_archive.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
|
||
COMMENT ON COLUMN audit_log_archive.operator IS '操作人';
|
||
COMMENT ON COLUMN audit_log_archive.operation_time IS '操作时间';
|
||
COMMENT ON COLUMN audit_log_archive.before_data IS '变更前数据（JSON格式）';
|
||
COMMENT ON COLUMN audit_log_archive.after_data IS '变更后数据（JSON格式）';
|
||
COMMENT ON COLUMN audit_log_archive.changed_fields IS '变更字段列表';
|
||
COMMENT ON COLUMN audit_log_archive.ip_address IS 'IP地址';
|
||
COMMENT ON COLUMN audit_log_archive.user_agent IS '用户代理';
|
||
COMMENT ON COLUMN audit_log_archive.description IS '操作描述';
|
||
COMMENT ON COLUMN audit_log_archive.created_at IS '记录创建时间';
|
||
COMMENT ON COLUMN audit_log_archive.archived_at IS '归档时间';
|