-
Notifications
You must be signed in to change notification settings - Fork 0
/
ts_db.sql
123 lines (108 loc) · 3.44 KB
/
ts_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
CREATE TABLE groups (
id_group INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(64),
priority INT NOT NULL
);
CREATE TABLE employees (
id_employee INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
visibleName VARCHAR(32),
login VARCHAR(32),
password VARCHAR(64),
lastPing DATETIME
);
CREATE TABLE groups_permissions (
permission VARCHAR(64),
id_group INT,
FOREIGN KEY (id_group) REFERENCES groups(id_group) ON DELETE CASCADE
);
CREATE TABLE employees_permissions (
permission VARCHAR(64),
id_employee INT,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE
);
CREATE TABLE employees_groups (
id_employee INT,
id_group INT,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE,
FOREIGN KEY (id_group) REFERENCES groups(id_group) ON DELETE CASCADE,
PRIMARY KEY(id_employee, id_group)
);
CREATE TABLE livechats (
id_chat INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
id_employee INT,
beginDate DATETIME NOT NULL,
endDate DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE SET NULL
);
CREATE TABLE livechatmessages (
id_message INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
id_chat INT NOT NULL,
id_employee INT,
message VARCHAR(256) NOT NULL,
seen BOOLEAN,
FOREIGN KEY (id_chat) REFERENCES livechats(id_chat),
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE SET NULL
);
CREATE TABLE livechatqueue (
id_queue INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
id_employee INT,
joinedDate DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE SET NULL
);
CREATE TABLE hotbar (
id_employee INT,
item INT,
slot INT,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE
);
CREATE TABLE email_bans (
email VARCHAR(320) PRIMARY KEY,
id_employee INT,
date DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee)
);
CREATE TABLE ip_bans (
ip VARCHAR(15) PRIMARY KEY,
id_employee INT,
date DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee)
);
CREATE TABLE username_bans (
username VARCHAR(64) PRIMARY KEY,
id_employee INT,
date DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee)
);
CREATE TABLE cookie_bans (
cookie VARCHAR(128) PRIMARY KEY,
id_employee INT,
date DATETIME,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee)
);
CREATE TABLE logs (
id_log INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_employee INT,
ip VARCHAR(15),
date DATETIME,
description VARCHAR(512),
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE
);
CREATE TABLE login_logs (
id_log INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_employee INT NOT NULL,
ip VARCHAR(15),
date DATETIME,
status INT,
FOREIGN KEY (id_employee) REFERENCES employees(id_employee) ON DELETE CASCADE
);
CREATE TABLE messages (
id_message INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sender INT,
receiver INT,
title VARCHAR(128),
content VARCHAR(2048),
date DATETIME,
readed BOOLEAN,
FOREIGN KEY (sender) REFERENCES employees(id_employee) ON DELETE CASCADE,
FOREIGN KEY (receiver) REFERENCES employees(id_employee) ON DELETE CASCADE
);