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
|
CREATE TABLE IF NOT EXISTS users (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
username TEXT NOT NULL UNIQUE,
password TEXT,
secret_key TEXT NOT NULL,
joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
last_active_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS connections (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id),
alias_id TEXT NOT NULL,
platform TEXT NOT NULL,
data TEXT NOT NULL,
connected_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS emotes (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
code TEXT NOT NULL,
mime TEXT NOT NULL,
ext TEXT NOT NULL,
uploaded_by INTEGER REFERENCES users(id),
created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
visibility INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS emote_sets (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
owner_id INTEGER NOT NULL REFERENCES users(id),
linked_to INTEGER REFERENCES emote_sets(id),
name TEXT NOT NULL,
size INTEGER,
is_global BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS emote_set_contents (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
emote_set_id INTEGER NOT NULL REFERENCES emote_sets(id),
emote_id INTEGER NOT NULL REFERENCES emotes(id),
name TEXT,
added_by INTEGER NOT NULL REFERENCES users(id),
added_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS acquired_emote_sets (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL,
emote_set_id INTEGER NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS ratings (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id),
emote_id INTEGER NOT NULL REFERENCES emotes(id),
rate INTEGER NOT NULL,
rated_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS inbox_messages (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
recipient_id INTEGER NOT NULL REFERENCES users(id),
message_type INTEGER NOT NULL,
contents TEXT NOT NULL,
link TEXT,
sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
has_read BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS reports (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
sender_id INTEGER NOT NULL REFERENCES users(id),
contents TEXT NOT NULL,
sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
resolved_by INTEGER REFERENCES users(id),
response_message TEXT
);
CREATE TABLE IF NOT EXISTS roles (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
priority INTEGER NOT NULL DEFAULT 0,
name TEXT NOT NULL,
foreground_color TEXT NOT NULL DEFAULT '000,000,000',
background_color TEXT NOT NULL DEFAULT 'solid:255,255,255',
badge_id INTEGER NOT NULL DEFAULT 0,
-- permissions
permission_upload BOOLEAN NOT NULL DEFAULT true,
permission_rate BOOLEAN NOT NULL DEFAULT true,
permission_emoteset_own BOOLEAN NOT NULL DEFAULT true,
permission_emoteset_all BOOLEAN NOT NULL DEFAULT false,
permission_report BOOLEAN NOT NULL DEFAULT true,
permission_report_review BOOLEAN NOT NULL DEFAULT false,
permission_approve_emotes BOOLEAN NOT NULL DEFAULT false,
permission_useredit_own BOOLEAN NOT NULL DEFAULT true,
permission_useredit_all BOOLEAN NOT NULL DEFAULT false,
permission_modsystem BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS role_assigns(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
role_id INTEGER NOT NULL REFERENCES roles(id)
);
|
