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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
|
CREATE TABLE channel_properties (
server_id bigint,
id bigint,
ident TEXT NOT NULL,
value TEXT
);
CREATE INDEX index_channel_properties_id ON channel_properties (id);
CREATE INDEX index_channel_properties_serverid ON channel_properties (server_id);
CREATE TABLE channels (
channel_id SERIAL PRIMARY KEY NOT NULL,
channel_parent_id bigint,
server_id bigint NOT NULL
);
CREATE INDEX index_channels_id ON channels (channel_id);
CREATE INDEX index_channels_serverid ON channels (server_id);
CREATE TABLE client_properties (
server_id bigint,
id bigint,
ident text NOT NULL,
value text
);
CREATE INDEX index_client_properties_id ON client_properties (id);
CREATE INDEX index_client_properties_serverid ON client_properties (server_id);
CREATE TABLE clients (
client_id SERIAL PRIMARY KEY NOT NULL,
server_id bigint,
client_unique_id text,
client_nickname text,
client_login_name text UNIQUE,
client_login_password text,
client_lastconnected bigint,
client_totalconnections bigint default 0,
client_month_upload NUMERIC(20) default 0,
client_month_download NUMERIC(20) default 0,
client_total_upload NUMERIC(20) default 0,
client_total_download NUMERIC(20) default 0,
client_lastip text
);
CREATE INDEX index_clients_id ON clients (client_id);
CREATE INDEX index_clients_serverid ON clients (server_id);
CREATE INDEX index_clients_lastconnectedserverid ON clients (client_lastconnected, server_id);
CREATE INDEX index_clients_uid ON clients (client_unique_id, server_id);
CREATE TABLE groups_channel (
group_id SERIAL PRIMARY KEY NOT NULL,
server_id bigint NOT NULL,
name text NOT NULL,
type integer NOT NULL
);
CREATE INDEX index_groups_channel_id ON groups_channel (group_id);
CREATE INDEX index_groups_channel_serverid ON groups_channel (server_id);
CREATE TABLE groups_server (
group_id SERIAL PRIMARY KEY NOT NULL,
server_id bigint NOT NULL,
name text NOT NULL,
type integer NOT NULL
);
CREATE INDEX index_groups_server_id ON groups_server (group_id);
CREATE INDEX index_groups_server_serverid ON groups_server (server_id);
CREATE TABLE group_server_to_client (
group_id bigint NOT NULL,
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL
);
CREATE INDEX index_group_server_to_client_id ON group_server_to_client (group_id);
CREATE INDEX index_group_server_to_client_serverid ON group_server_to_client (server_id);
CREATE INDEX index_group_server_to_client_id1 ON group_server_to_client (id1);
CREATE TABLE group_channel_to_client (
group_id bigint NOT NULL,
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL
);
CREATE INDEX index_group_channel_to_client_id ON group_channel_to_client (group_id);
CREATE INDEX index_group_channel_to_client_serverid ON group_channel_to_client (server_id);
CREATE INDEX index_group_channel_to_client_id1 ON group_channel_to_client (id1);
CREATE INDEX index_group_channel_to_client_id2 ON group_channel_to_client (id2);
CREATE TABLE perm_channel (
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL,
perm_id text NOT NULL,
perm_value integer,
perm_negated integer,
perm_skip integer
);
CREATE INDEX index_perm_channel_serverid ON perm_channel (server_id);
CREATE TABLE perm_channel_clients (
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL,
perm_id text NOT NULL,
perm_value integer,
perm_negated integer,
perm_skip integer
);
CREATE INDEX index_perm_channel_clients_serverid ON perm_channel_clients (server_id);
CREATE TABLE perm_channel_groups (
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL,
perm_id text NOT NULL,
perm_value integer,
perm_negated integer,
perm_skip integer
);
CREATE INDEX index_perm_channel_groups_serverid ON perm_channel_groups (server_id);
CREATE TABLE perm_client (
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL,
perm_id text NOT NULL,
perm_value integer,
perm_negated integer,
perm_skip integer
);
CREATE INDEX index_perm_client_serverid ON perm_client (server_id);
CREATE TABLE perm_server_group (
server_id bigint NOT NULL,
id1 bigint NOT NULL,
id2 bigint NOT NULL,
perm_id text NOT NULL,
perm_value integer,
perm_negated integer,
perm_skip integer
);
CREATE INDEX index_perm_server_group_serverid ON perm_server_group (server_id);
CREATE TABLE bindings (
binding_id SERIAL PRIMARY KEY NOT NULL,
ip text NOT NULL,
type integer
);
CREATE TABLE server_properties (
server_id bigint,
id bigint,
ident text NOT NULL,
value text
);
CREATE INDEX index_server_properties_id ON server_properties (id);
CREATE INDEX index_server_properties_serverid ON server_properties (server_id);
CREATE TABLE servers (
server_id SERIAL PRIMARY KEY NOT NULL,
server_port bigint,
server_autostart bigint,
server_machine_id text,
server_month_upload NUMERIC(20) Default 0,
server_month_download NUMERIC(20) Default 0,
server_total_upload NUMERIC(20) Default 0,
server_total_download NUMERIC(20) Default 0
);
CREATE INDEX index_servers_serverid ON servers (server_id);
CREATE INDEX index_servers_port ON servers (server_port);
CREATE TABLE tokens (
server_id bigint,
token_key text NOT NULL,
token_type integer,
token_id1 bigint,
token_id2 bigint,
token_created bigint,
token_description text,
token_customset text
);
CREATE TABLE messages (
message_id SERIAL PRIMARY KEY NOT NULL,
server_id bigint,
message_from_client_id bigint,
message_from_client_uid text,
message_to_client_id bigint,
message_subject text,
message_msg text,
message_timestamp bigint,
message_flag_read integer default 0
);
CREATE INDEX index_messages_serverid ON messages (server_id);
CREATE INDEX index_messages_msgidtoclid_read ON messages (message_to_client_id, message_flag_read);
CREATE TABLE complains (
server_id bigint,
complain_from_client_id bigint,
complain_to_client_id bigint,
complain_message text,
complain_hash text,
complain_timestamp bigint
);
CREATE INDEX index_complains_serverid ON complains (server_id);
CREATE TABLE bans (
ban_id SERIAL PRIMARY KEY NOT NULL,
server_id bigint,
ban_ip text,
ban_name text,
ban_uid text,
ban_timestamp bigint,
ban_length bigint,
ban_invoker_client_id bigint,
ban_invoker_uid text,
ban_invoker_name text,
ban_reason text,
ban_hash text,
ban_enforcements bigint Default 0
);
CREATE INDEX index_bans_serverid ON bans (server_id);
CREATE TABLE instance_properties (
server_id bigint,
string_id text,
id bigint,
ident text NOT NULL,
value text
);
CREATE INDEX index_instance_properties_id ON instance_properties (id);
CREATE INDEX index_instance_properties_string_id ON instance_properties (string_id);
CREATE INDEX index_instance_properties_serverid ON instance_properties (server_id);
CREATE TABLE custom_fields (
server_id bigint,
client_id bigint,
ident text NOT NULL,
value text
);
CREATE INDEX index_custom_fields_serverid ON custom_fields (server_id);
CREATE INDEX index_custom_fields_client_id ON custom_fields (client_id);
CREATE INDEX index_custom_fields_ident ON custom_fields (ident);
|
