Repository navigation
Expand file tree
/
Copy pathschema.sql
More file actions
437 lines (284 loc) · 9.37 KB
/
Copy pathschema.sql
File metadata and controls
437 lines (284 loc) · 9.37 KB
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
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
--
-- PostgreSQL database dump
--
\restrict KUAQ90Lo8fJHFY3v1LmMcIwsGw5yuS9BS1PjgdAbFF3wLDiZDC2cOiVMbf6x5JI
-- Dumped from database version 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
-- Dumped by pg_dump version 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: pgtle; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA pgtle;
ALTER SCHEMA pgtle OWNER TO postgres;
--
-- Name: pg_stat_statements; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
--
-- Name: EXTENSION pg_stat_statements; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION pg_stat_statements IS 'track planning and execution statistics of all SQL statements executed';
--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';
--
-- Name: server; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE public.server AS (
ip integer,
port smallint,
lastseen bigint
);
ALTER TYPE public.server OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: bedrock; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.bedrock (
serverid bigint NOT NULL,
ip integer,
port smallint,
discovered bigint,
lastseen bigint,
education boolean,
version text,
protocol integer,
description text,
rawdescription text,
description2 text,
rawdescription2 text,
playercount integer,
playerlimit integer,
gamemode text,
modeid integer,
org text,
country text,
city text,
lat real,
lon real
);
ALTER TABLE public.bedrock OWNER TO postgres;
--
-- Name: bedrock_serverid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.bedrock_serverid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.bedrock_serverid_seq OWNER TO postgres;
--
-- Name: bedrock_serverid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.bedrock_serverid_seq OWNED BY public.bedrock.serverid;
--
-- Name: playerhistory; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.playerhistory (
name text,
id uuid,
serverid bigint,
lastsession bigint
);
ALTER TABLE public.playerhistory OWNER TO postgres;
--
-- Name: servers; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.servers (
serverid bigint NOT NULL,
ip integer,
port smallint,
discovered bigint,
lastseen bigint,
version text,
protocol integer,
description text,
rawdescription text,
playercount integer,
playerlimit integer,
hasfavicon boolean,
hasforgedata boolean,
enforcessecurechat boolean,
org text,
country text,
city text,
lat real,
lon real,
cracked boolean,
whitelisted boolean,
hasplayersample boolean,
descriptionvector tsvector
);
ALTER TABLE public.servers OWNER TO postgres;
--
-- Name: servers_serverid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE public.servers_serverid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.servers_serverid_seq OWNER TO postgres;
--
-- Name: servers_serverid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE public.servers_serverid_seq OWNED BY public.servers.serverid;
--
-- Name: bedrock serverid; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.bedrock ALTER COLUMN serverid SET DEFAULT nextval('public.bedrock_serverid_seq'::regclass);
--
-- Name: servers serverid; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.servers ALTER COLUMN serverid SET DEFAULT nextval('public.servers_serverid_seq'::regclass);
--
-- Name: bedrock bedrock_ip_port_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.bedrock
ADD CONSTRAINT bedrock_ip_port_key UNIQUE (ip, port);
--
-- Name: bedrock bedrock_pkey1; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.bedrock
ADD CONSTRAINT bedrock_pkey1 PRIMARY KEY (serverid);
--
-- Name: playerhistory players_name_id_serverid_constraint; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.playerhistory
ADD CONSTRAINT players_name_id_serverid_constraint UNIQUE (name, id, serverid);
--
-- Name: servers servers_ip_port_key1; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.servers
ADD CONSTRAINT servers_ip_port_key1 UNIQUE (ip, port);
--
-- Name: servers servers_pkey1; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.servers
ADD CONSTRAINT servers_pkey1 PRIMARY KEY (serverid);
--
-- Name: bedrock_discovered; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_discovered ON public.bedrock USING btree (discovered);
--
-- Name: bedrock_ip; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_ip ON public.bedrock USING btree (ip);
--
-- Name: bedrock_lastseen_desc; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_lastseen_desc ON public.bedrock USING btree (lastseen DESC);
--
-- Name: bedrock_modeid; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_modeid ON public.bedrock USING btree (modeid);
--
-- Name: bedrock_playercount; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_playercount ON public.bedrock USING btree (playercount);
--
-- Name: bedrock_playerlimit; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_playerlimit ON public.bedrock USING btree (playerlimit);
--
-- Name: bedrock_port; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_port ON public.bedrock USING btree (port);
--
-- Name: bedrock_protocol; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX bedrock_protocol ON public.bedrock USING btree (protocol);
--
-- Name: country_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX country_index ON public.servers USING btree (country);
--
-- Name: cracked_active_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX cracked_active_index ON public.servers USING btree (cracked) WHERE (cracked IS NOT NULL);
--
-- Name: description_trgm_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX description_trgm_index ON public.servers USING gin (description public.gin_trgm_ops);
--
-- Name: descriptionvector_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX descriptionvector_index ON public.servers USING gin (descriptionvector);
--
-- Name: discovered_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX discovered_index ON public.servers USING btree (discovered);
--
-- Name: ip_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX ip_index ON public.servers USING btree (ip);
--
-- Name: lastseen_desc_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX lastseen_desc_index ON public.servers USING btree (lastseen DESC);
--
-- Name: org_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX org_index ON public.servers USING btree (org);
--
-- Name: playercount_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX playercount_index ON public.servers USING btree (playercount);
--
-- Name: playerlimit_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX playerlimit_index ON public.servers USING btree (playerlimit);
--
-- Name: players_id; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX players_id ON public.playerhistory USING btree (id);
--
-- Name: players_name; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX players_name ON public.playerhistory USING btree (name);
--
-- Name: players_serverid; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX players_serverid ON public.playerhistory USING btree (serverid);
--
-- Name: port_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX port_index ON public.servers USING btree (port);
--
-- Name: protocol_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX protocol_index ON public.servers USING btree (protocol);
--
-- Name: version_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX version_index ON public.servers USING btree (version);
--
-- Name: version_trgm_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX version_trgm_index ON public.servers USING gin (version public.gin_trgm_ops);
--
-- Name: whitelisted_active_index; Type: INDEX; Schema: public; Owner: postgres
--
CREATE INDEX whitelisted_active_index ON public.servers USING btree (whitelisted) WHERE (whitelisted IS NOT NULL);
--
-- PostgreSQL database dump complete
--
\unrestrict KUAQ90Lo8fJHFY3v1LmMcIwsGw5yuS9BS1PjgdAbFF3wLDiZDC2cOiVMbf6x5JI