aboutsummaryrefslogtreecommitdiff
path: root/backend/database
diff options
context:
space:
mode:
authorArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2024-07-04 19:02:11 +0300
committerGitHub <noreply@github.com>2024-07-04 19:02:11 +0300
commit368f6dd461d768e835124afdd5aa0228d9e0ba0b (patch)
tree4b78a4571f6cc4b7e0f553d90f52c016c9d2b8e1 /backend/database
parentfix: fetchmaps image sql (diff)
downloadlphub-368f6dd461d768e835124afdd5aa0228d9e0ba0b.tar.gz
lphub-368f6dd461d768e835124afdd5aa0228d9e0ba0b.tar.bz2
lphub-368f6dd461d768e835124afdd5aa0228d9e0ba0b.zip
feat: update map tables db schema (#157)
Diffstat (limited to 'backend/database')
-rw-r--r--backend/database/init.sql15
-rw-r--r--backend/database/route.sql283
2 files changed, 2 insertions, 296 deletions
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 1fda15d..c33821b 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -48,25 +48,14 @@ CREATE TABLE maps (
48 FOREIGN KEY (chapter_id) REFERENCES chapters(id) 48 FOREIGN KEY (chapter_id) REFERENCES chapters(id)
49); 49);
50 50
51CREATE TABLE map_routes (
52 id SERIAL,
53 map_id SMALLINT NOT NULL,
54 category_id SMALLINT NOT NULL,
55 score_count SMALLINT NOT NULL,
56 description TEXT NOT NULL,
57 showcase TEXT NOT NULL DEFAULT,
58 PRIMARY KEY (id),
59 FOREIGN KEY (map_id) REFERENCES maps(id),
60 FOREIGN KEY (category_id) REFERENCES categories(id),
61 UNIQUE (map_id, category_id, score_count)
62);
63
64CREATE TABLE map_history ( 51CREATE TABLE map_history (
65 id SERIAL, 52 id SERIAL,
66 map_id SMALLINT NOT NULL, 53 map_id SMALLINT NOT NULL,
67 category_id SMALLINT NOT NULL, 54 category_id SMALLINT NOT NULL,
68 user_name TEXT NOT NULL, 55 user_name TEXT NOT NULL,
69 score_count SMALLINT NOT NULL, 56 score_count SMALLINT NOT NULL,
57 description TEXT NOT NULL,
58 showcase TEXT NOT NULL DEFAULT,
70 record_date DATE NOT NULL, 59 record_date DATE NOT NULL,
71 PRIMARY KEY (id), 60 PRIMARY KEY (id),
72 FOREIGN KEY (category_id) REFERENCES categories(id), 61 FOREIGN KEY (category_id) REFERENCES categories(id),
diff --git a/backend/database/route.sql b/backend/database/route.sql
deleted file mode 100644
index 8b23a8c..0000000
--- a/backend/database/route.sql
+++ /dev/null
@@ -1,283 +0,0 @@
1INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALUES
2-- Portal 2 Singleplayer
3-- 1
4(3,1,3,'',''),
5(3,1,1,'',''),
6(3,1,0,'',''),
7(4,1,1,'',''),
8(4,1,0,'',''),
9(5,1,2,'',''),
10(5,1,1,'',''),
11(6,1,4,'',''),
12(6,1,3,'',''),
13(6,1,2,'',''),
14(7,1,0,'',''),
15(9,1,4,'',''),
16(9,1,3,'',''),
17(9,1,2,'',''),
18(9,1,0,'',''),
19-- 2
20(10,1,2,'',''),
21(10,1,0,'',''),
22(11,1,2,'',''),
23(11,1,0,'',''),
24(12,1,2,'',''),
25(13,1,3,'',''),
26(13,1,2,'',''),
27(13,1,0,'',''),
28(14,1,0,'',''),
29(15,1,2,'',''),
30(16,1,2,'',''),
31(16,1,0,'',''),
32(17,1,2,'',''),
33(17,1,0,'',''),
34-- 3
35(18,1,5,'',''),
36(18,1,4,'',''),
37(18,1,3,'',''),
38(18,1,2,'',''),
39(18,1,0,'',''),
40(19,1,2,'',''),
41(20,1,5,'',''),
42(20,1,4,'',''),
43(20,1,3,'',''),
44(20,1,2,'',''),
45(21,1,4,'',''),
46(21,1,2,'',''),
47(21,1,0,'',''),
48(22,1,0,'',''),
49(23,1,2,'',''),
50(23,1,0,'',''),
51(24,1,0,'',''),
52(25,1,0,'',''),
53(26,1,3,'',''),
54(26,1,2,'',''),
55(26,1,0,'',''),
56-- 4
57(27,1,2,'',''),
58(27,1,0,'',''),
59(28,1,7,'',''),
60(28,1,2,'',''),
61(28,1,0,'',''),
62(29,1,0,'',''),
63(30,1,2,'',''),
64(31,1,0,'',''),
65-- 5
66(32,1,6,'',''),
67(32,1,5,'',''),
68(33,1,7,'',''),
69(33,1,5,'',''),
70(33,1,4,'',''),
71(34,1,3,'',''),
72(34,1,2,'',''),
73(34,1,0,'',''),
74(35,1,2,'',''),
75-- 6
76(36,1,6,'',''),
77(36,1,5,'',''),
78(36,1,4,'',''),
79(36,1,2,'',''),
80(37,1,7,'',''),
81(37,1,6,'',''),
82(37,1,5,'',''),
83(37,1,4,'',''),
84(38,1,2,'',''),
85(38,1,0,'',''),
86(39,1,6,'',''),
87(39,1,5,'',''),
88(39,1,4,'',''),
89(39,1,3,'',''),
90(40,1,7,'',''),
91(40,1,6,'',''),
92(40,1,4,'',''),
93(40,1,3,'',''),
94(40,1,2,'',''),
95(40,1,0,'',''),
96(41,1,7,'',''),
97(41,1,6,'',''),
98(41,1,5,'',''),
99-- 7
100(42,1,4,'',''),
101(42,1,2,'',''),
102(43,1,5,'',''),
103(43,1,2,'',''),
104(43,1,0,'',''),
105(44,1,18,'',''),
106(44,1,13,'',''),
107(44,1,12,'',''),
108(44,1,11,'',''),
109(44,1,9,'',''),
110(45,1,23,'',''),
111(45,1,22,'',''),
112(45,1,17,'',''),
113(45,1,16,'',''),
114(45,1,15,'',''),
115(45,1,12,'',''),
116(45,1,10,'',''),
117(45,1,7,'',''),
118(45,1,4,'',''),
119-- 8
120(46,1,6,'',''),
121(46,1,2,'',''),
122(47,1,2,'',''),
123(47,1,0,'',''),
124(48,1,5,'',''),
125(48,1,2,'',''),
126(48,1,0,'',''),
127(49,1,4,'',''),
128(49,1,2,'',''),
129(49,1,0,'',''),
130(50,1,4,'',''),
131(50,1,2,'',''),
132(50,1,0,'',''),
133(51,1,3,'',''),
134(51,1,2,'',''),
135(52,1,0,'',''),
136(53,1,9,'',''),
137(53,1,2,'',''),
138(53,1,0,'',''),
139(54,1,7,'',''),
140(54,1,6,'',''),
141(54,1,5,'',''),
142(54,1,4,'',''),
143(54,1,3,'',''),
144(55,1,7,'',''),
145(55,1,3,'',''),
146(55,1,2,'',''),
147(55,1,0,'',''),
148(56,1,9,'',''),
149(56,1,5,'',''),
150(56,1,4,'',''),
151(56,1,2,'',''),
152-- 9
153(57,1,7,'',''),
154(57,1,5,'',''),
155(57,1,0,'',''),
156(58,1,2,'',''),
157(59,1,7,'',''),
158(59,1,6,'',''),
159(60,1,7,'',''),
160(60,1,6,'',''),
161-- Portal 2 Cooperative
162-- 1
163(63,1,0,'',''),
164(64,1,3,'',''),
165(64,1,2,'',''),
166(65,1,4,'',''),
167(65,1,3,'',''),
168(65,1,2,'',''),
169(66,1,3,'',''),
170(66,1,2,'',''),
171(67,1,0,'',''),
172(68,1,0,'',''),
173-- 2
174(69,1,4,'',''),
175(70,1,6,'',''),
176(70,1,4,'',''),
177(70,1,2,'',''),
178(70,1,0,'',''),
179(71,1,3,'',''),
180(71,1,0,'',''),
181(72,1,4,'',''),
182(72,1,2,'',''),
183(73,1,9,'',''),
184(73,1,8,'',''),
185(73,1,7,'',''),
186(73,1,6,'',''),
187(73,1,4,'',''),
188(74,1,5,'',''),
189(74,1,7,'',''),
190(74,1,3,'',''),
191(74,1,2,'',''),
192(75,1,5,'',''),
193(75,1,4,'',''),
194(75,1,2,'',''),
195(75,1,0,'',''),
196(76,1,3,'',''),
197(76,1,0,'',''),
198-- 3
199(77,1,3,'',''),
200(78,1,4,'',''),
201(78,1,3,'',''),
202(78,1,2,'',''),
203(79,1,5,'',''),
204(79,1,4,'',''),
205(79,1,2,'',''),
206(79,1,0,'',''),
207(80,1,5,'',''),
208(80,1,4,'',''),
209(81,1,7,'',''),
210(81,1,6,'',''),
211(81,1,5,'',''),
212(81,1,4,'',''),
213(82,1,4,'',''),
214(83,1,5,'',''),
215(83,1,2,'',''),
216(83,1,0,'',''),
217(84,1,6,'',''),
218(84,1,4,'',''),
219(84,1,2,'',''),
220(84,1,0,'',''),
221-- 4
222(85,1,3,'',''),
223(85,1,0,'',''),
224(86,1,3,'',''),
225(86,1,0,'',''),
226(87,1,3,'',''),
227(87,1,2,'',''),
228(87,1,0,'',''),
229(88,1,4,'',''),
230(88,1,0,'',''),
231(89,1,0,'',''),
232(90,1,4,'',''),
233(90,1,2,'',''),
234(90,1,0,'',''),
235(91,1,2,'',''),
236(91,1,0,'',''),
237(92,1,5,'',''),
238(92,1,4,'',''),
239(92,1,2,'',''),
240(92,1,0,'',''),
241(93,1,2,'',''),
242(93,1,0,'',''),
243-- 5
244(94,1,2,'',''),
245(94,1,0,'',''),
246(95,1,2,'',''),
247(96,1,4,'',''),
248(96,1,2,'',''),
249(97,1,7,'',''),
250(97,1,4,'',''),
251(97,1,2,'',''),
252(98,1,0,'',''),
253(99,1,3,'',''),
254(99,1,2,'',''),
255(99,1,0,'',''),
256(100,1,0,'',''),
257(101,1,2,'',''),
258(101,1,0,'',''),
259-- 6
260(102,1,3,'',''),
261(102,1,2,'',''),
262(102,1,0,'',''),
263(103,1,0,'',''),
264(104,1,0,'',''),
265(105,1,8,'',''),
266(105,1,5,'',''),
267(105,1,4,'',''),
268(105,1,3,'',''),
269(105,1,2,'',''),
270(106,1,4,'',''),
271(106,1,3,'',''),
272(106,1,0,'',''),
273(107,1,2,'',''),
274(107,1,0,'',''),
275(108,1,0,'',''),
276(109,1,5,'',''),
277(109,1,0,'',''),
278(110,1,15,'',''),
279(110,1,6,'',''),
280(110,1,5,'',''),
281(110,1,4,'',''),
282(110,1,2,'',''),
283(110,1,0,'',''); \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 23:11:33 +0000