-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathschema.sql
More file actions
69 lines (62 loc) · 2.59 KB
/
schema.sql
File metadata and controls
69 lines (62 loc) · 2.59 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 数据库表结构定义
-- 用户表
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名',
password VARCHAR(100) NOT NULL COMMENT '密码',
email VARCHAR(100) COMMENT '邮箱',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);
-- 角色表
CREATE TABLE roles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) UNIQUE NOT NULL COMMENT '角色名',
description VARCHAR(255) COMMENT '角色描述',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);
-- 权限表
CREATE TABLE permissions (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) UNIQUE NOT NULL COMMENT '权限名',
description VARCHAR(255) COMMENT '权限描述',
resource VARCHAR(100) COMMENT '资源',
action VARCHAR(50) COMMENT '操作',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);
-- 资源表
CREATE TABLE resources (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) UNIQUE NOT NULL COMMENT '资源名',
description VARCHAR(255) COMMENT '资源描述',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);
-- 用户角色映射表
CREATE TABLE user_roles (
user_id BIGINT COMMENT '用户ID',
role_id BIGINT COMMENT '角色ID',
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);
-- 角色权限映射表
CREATE TABLE role_permissions (
role_id BIGINT COMMENT '角色ID',
permission_id BIGINT COMMENT '权限ID',
PRIMARY KEY (role_id, permission_id),
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);
-- 审计日志表
CREATE TABLE audit_log (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT COMMENT '用户ID',
action VARCHAR(100) COMMENT '操作',
resource VARCHAR(100) COMMENT '资源',
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳',
details TEXT COMMENT '详情',
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);