forked from Muxi-X/forum-be
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb.sql
178 lines (171 loc) · 7.46 KB
/
db.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
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`name` varchar(20) NOT NULL,
`email` varchar(35) DEFAULT NULL,
`avatar` varchar(100) DEFAULT NULL,
`student_id` char(10) DEFAULT NULL,
`hash_password` varchar(100) DEFAULT NULL,
`role` varchar(20) NOT NULL COMMENT '权限: Normal-普通学生用户; NormalAdmin-学生管理员; Muxi-团队成员; MuxiAdmin-团队管理员; SuperAdmin-超级管理员',
`signature` varchar(200) DEFAULT NULL,
`re` tinyint(1) DEFAULT NULL COMMENT '标志是否删除，0-未删除 1-删除 删除时只要将 re 置为 1',
`is_public_collection_and_like` tinyint(1) DEFAULT NULL,
`is_public_feed` tinyint(1) DEFAULT NULL,
CONSTRAINT T_type_Chk CHECK (`role` = 'Normal' OR `role` = 'NormalAdmin' OR `role` = 'Muxi' OR
`role` = 'MuxiAdmin' OR `role` = 'SuperAdmin'),
KEY (`email`),
UNIQUE KEY (`student_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- --------------------------------------------
-- Table structure for posts
-- --------------------------------------------
DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`domain` varchar(30) NOT NULL,
`content` text NOT NULL,
`compiled_content` text NOT NULL,
`title` varchar(150) NOT NULL,
`summary` varchar(1000) NOT NULL,
`create_time` varchar(30) NOT NULL,
`category` varchar(30) NOT NULL,
`re` tinyint(1) NOT NULL,
`creator_id` int(11) NOT NULL,
`last_edit_time` varchar(30) NOT NULL,
`content_type` varchar(30) NOT NULL,
`like_num` int(11) DEFAULT 0,
`score` int(11) DEFAULT 0,
`is_report` tinyint(1) NOT NULL,
KEY (`category`),
CONSTRAINT T_type_Chk CHECK (`domain` = 'normal' OR `domain` = 'muxi'),
CONSTRAINT T_content_type_Chk CHECK (`content_type` = 'md' OR `content_type` = 'rtf'),
FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`)
# FULLTEXT KEY content_title_fulltext (`content`, `title`) # MySQL 5.7.6 才支持中文全文索引
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for comments
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`type_name` varchar(30) NOT NULL,
`content` text NOT NULL,
`father_id` int(11) DEFAULT NULL,
`create_time` varchar(30) DEFAULT NULL,
`re` tinyint(1) DEFAULT NULL COMMENT '标志是否删除，0-未删除 1-删除 删除时只要将 re 置为 1',
`creator_id` int(11) DEFAULT NULL,
`post_id` int(11) DEFAULT NULL,
`like_num` int(11) DEFAULT 0,
`is_report` tinyint(1) NOT NULL,
CONSTRAINT T_type_Chk CHECK (`type_name` = 'sub-post' OR `type_name` = 'first-level' OR
`type_name` = 'second-level'),
FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`),
FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- --------------------------------------------
-- Table structure for tags
-- --------------------------------------------
DROP TABLE IF EXISTS `tags`;
CREATE TABLE `tags`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`content` varchar(30) NOT NULL,
UNIQUE KEY (`content`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- --------------------------------------------
-- Table structure for post2tags
-- --------------------------------------------
DROP TABLE IF EXISTS `post2tags`;
CREATE TABLE `post2tags`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`post_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
KEY (`post_id`, `tag_id`),
FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for feeds
-- ----------------------------
DROP TABLE IF EXISTS `feeds`;
CREATE TABLE `feeds`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`user_id` int(11) DEFAULT NULL,
`user_name` varchar(100) DEFAULT NULL,
`user_avatar` varchar(200) DEFAULT NULL,
`action` varchar(20) DEFAULT NULL COMMENT '动作，存储如 <创建>、<编辑>、<删除>、<评论>、<加入> 等常量字符串',
`source_type_name` varchar(100) DEFAULT NULL COMMENT '动态的类型',
`source_object_name` varchar(100) DEFAULT NULL COMMENT 'object 包括 等，这里是它们的名字',
`source_object_id` int(11) DEFAULT NULL COMMENT '对象的 id',
`target_user_id` int(11) DEFAULT NULL,
`domain` varchar(100) DEFAULT NULL,
`create_time` varchar(30) DEFAULT NULL,
`re` tinyint(1) DEFAULT NULL COMMENT '标志是否删除，0-未删除 1-删除 删除时只要将 re 置为 1',
`is_public_feed` tinyint(1) DEFAULT NULL COMMENT '是否公开feed',
`is_public_collection_and_like` tinyint(1) DEFAULT NULL COMMENT '是否公开collection and like',
CONSTRAINT T_type_Chk CHECK (`domain` = 'normal' OR `domain` = 'muxi')
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- --------------------------------------------
-- Table structure for collections
-- --------------------------------------------
DROP TABLE IF EXISTS `collections`;
CREATE TABLE `collections`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`post_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`create_time` varchar(30) DEFAULT NULL,
KEY (`user_id`),
UNIQUE (`user_id`, `post_id`),
FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;
-- --------------------------------------------
-- Table structure for collections
-- --------------------------------------------
DROP TABLE IF EXISTS `reports`;
CREATE TABLE `reports`
(
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
`target_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`create_time` varchar(30) DEFAULT NULL,
`type_name` varchar(30) DEFAULT NULL,
`cause` varchar(1000) DEFAULT NULL,
`category` varchar(30) DEFAULT NULL,
KEY (`user_id`),
UNIQUE (`user_id`, `target_id`, `type_name`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
ROW_FORMAT = DYNAMIC;