-
Notifications
You must be signed in to change notification settings - Fork 173
/
Copy pathschema.sql
201 lines (183 loc) · 7.71 KB
/
schema.sql
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- 文件表
-- ----------------------------
DROP TABLE IF EXISTS lin_file;
CREATE TABLE lin_file
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
path varchar(500) NOT NULL,
type varchar(10) NOT NULL DEFAULT 'LOCAL' COMMENT 'LOCAL 本地，REMOTE 远程',
name varchar(100) NOT NULL,
extension varchar(50) DEFAULT NULL,
size int(11) DEFAULT NULL,
md5 varchar(40) DEFAULT NULL COMMENT 'md5值，防止上传重复文件',
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY md5_del (md5, delete_time)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 日志表
-- ----------------------------
DROP TABLE IF EXISTS lin_log;
CREATE TABLE lin_log
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
message varchar(450) DEFAULT NULL,
user_id int(10) unsigned NOT NULL,
username varchar(24) DEFAULT NULL,
status_code int(11) DEFAULT NULL,
method varchar(20) DEFAULT NULL,
path varchar(50) DEFAULT NULL,
permission varchar(100) DEFAULT NULL,
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 权限表
-- ----------------------------
DROP TABLE IF EXISTS lin_permission;
CREATE TABLE lin_permission
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(60) NOT NULL COMMENT '权限名称，例如：访问首页',
module varchar(50) NOT NULL COMMENT '权限所属模块，例如：人员管理',
mount tinyint(1) NOT NULL DEFAULT 1 COMMENT '0：关闭 1：开启',
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 分组表
-- ----------------------------
DROP TABLE IF EXISTS lin_group;
CREATE TABLE lin_group
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(60) NOT NULL COMMENT '分组名称，例如：搬砖者',
info varchar(255) DEFAULT NULL COMMENT '分组信息：例如：搬砖的人',
level tinyint(2) NOT NULL DEFAULT 3 COMMENT '分组级别 1：root 2：guest 3：user（root、guest分组只能存在一个)',
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY name_del (name, delete_time)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 分组-权限表
-- ----------------------------
DROP TABLE IF EXISTS lin_group_permission;
CREATE TABLE lin_group_permission
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
group_id int(10) unsigned NOT NULL COMMENT '分组id',
permission_id int(10) unsigned NOT NULL COMMENT '权限id',
PRIMARY KEY (id),
KEY group_id_permission_id (group_id, permission_id) USING BTREE COMMENT '联合索引'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 用户基本信息表
-- ----------------------------
DROP TABLE IF EXISTS lin_user;
CREATE TABLE lin_user
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(24) NOT NULL COMMENT '用户名，唯一',
nickname varchar(24) DEFAULT NULL COMMENT '用户昵称',
avatar varchar(500) DEFAULT NULL COMMENT '头像url',
email varchar(100) DEFAULT NULL COMMENT '邮箱',
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY username_del (username, delete_time),
UNIQUE KEY email_del (email, delete_time)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 用户授权信息表
# id
# user_id
# identity_type 登录类型（手机号 邮箱 用户名）或第三方应用名称（微信 微博等）
# identifier 标识（手机号 邮箱 用户名或第三方应用的唯一标识）
# credential 密码凭证（站内的保存密码，站外的不保存或保存token）
-- ----------------------------
DROP TABLE IF EXISTS lin_user_identity;
CREATE TABLE lin_user_identity
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(10) unsigned NOT NULL COMMENT '用户id',
identity_type varchar(100) NOT NULL,
identifier varchar(100),
credential varchar(100),
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
DROP TABLE IF EXISTS book;
CREATE TABLE book
(
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
author varchar(30) DEFAULT NULL,
summary varchar(1000) DEFAULT NULL,
image varchar(100) DEFAULT NULL,
create_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
update_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
delete_time datetime(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- ----------------------------
-- 用户-分组表
-- ----------------------------
DROP TABLE IF EXISTS lin_user_group;
CREATE TABLE lin_user_group
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(10) unsigned NOT NULL COMMENT '用户id',
group_id int(10) unsigned NOT NULL COMMENT '分组id',
PRIMARY KEY (id),
KEY user_id_group_id (user_id, group_id) USING BTREE COMMENT '联合索引'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci;
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- 插入超级管理员
-- 插入root分组
-- ----------------------------
BEGIN;
INSERT INTO lin_user(id, username, nickname)
VALUES (1, 'root', 'root');
INSERT INTO lin_user_identity (id, user_id, identity_type, identifier, credential)
VALUES (1, 1, 'USERNAME_PASSWORD', 'root',
'sha1$c419e500$1$84869e5560ebf3de26b6690386484929456d6c07');
INSERT INTO lin_group(id, name, info, level)
VALUES (1, 'root', '超级用户组', 1);
INSERT INTO lin_group(id, name, info, level)
VALUES (2, 'guest', '游客组', 2);
INSERT INTO lin_user_group(id, user_id, group_id)
VALUES (1, 1, 1);
COMMIT;