aboutsummaryrefslogtreecommitdiff
path: root/backend/database
diff options
context:
space:
mode:
authorArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2023-04-24 21:28:10 +0300
committerArda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>2023-04-24 21:28:10 +0300
commitcd31abc1bf4a97b04afb95fc10a88070f1a72cc4 (patch)
tree03f7667c7ee423d6fb448c6bf5c04016fc00bbd9 /backend/database
parentfix: able to have null first completionist (#38) (diff)
downloadlphub-cd31abc1bf4a97b04afb95fc10a88070f1a72cc4.tar.gz
lphub-cd31abc1bf4a97b04afb95fc10a88070f1a72cc4.tar.bz2
lphub-cd31abc1bf4a97b04afb95fc10a88070f1a72cc4.zip
feat: history data from lp sheet ported
Diffstat (limited to 'backend/database')
-rw-r--r--backend/database/history.sql279
-rw-r--r--backend/database/init.sql4
2 files changed, 280 insertions, 3 deletions
diff --git a/backend/database/history.sql b/backend/database/history.sql
new file mode 100644
index 0000000..0840bf3
--- /dev/null
+++ b/backend/database/history.sql
@@ -0,0 +1,279 @@
1INSERT INTO map_history(map_id,user_name,score_count,record_date) VALUES
2-- Portal 2 Singleplayer
3-- 1
4(3,'slmid1995',3,'2011-10-05 00:00:00'),
5(3,'LookLikeAKango',1,'2011-10-06 00:00:00'),
6(3,'Bananasaurus Rex',0,'2011-10-24 00:00:00'),
7(4,'Tyronis',1,'2011-10-05 00:00:00'),
8(4,'Krzyhau',0,'2019-05-10 00:00:00'),
9(5,'LookLikeAKango',2,'2011-10-05 00:00:00'),
10(5,'Jetwash',1,'2013-12-03 00:00:00'),
11(6,'Stimich',4,'2011-10-08 00:00:00'),
12(6,'aepaePolakrn',3,'2011-10-19 00:00:00'),
13(6,'Krzyhau',2,'2020-10-10 00:00:00'),
14(9,'slmid1995',4,'2011-10-05 00:00:00'),
15(9,'Jokie',3,'2011-10-05 00:00:00'),
16(9,'Tyronis',2,'2011-10-05 00:00:00'),
17(9,'sicklebrick',0,'2013-03-13 00:00:00'),
18-- 2
19(10,'Paraxade0',2,'2011-04-21 00:00:00'),
20(10,'PerOculos',0,'2011-04-21 00:00:00'),
21(11,'Tyronis',2,'2011-10-05 00:00:00'),
22(11,'Krzyhau',0,'2018-06-09 00:00:00'),
23(12,'slmid1995',2,'2011-10-04 00:00:00'),
24(13,'LookLikeAKango',3,'2011-10-05 00:00:00'),
25(13,'Imanex',2,'2011-12-08 00:00:00'),
26(13,'jyjey',0,'2012-08-22 00:00:00'),
27(15,'Tyronis',2,'2011-10-05 00:00:00'),
28(16,'LookLikeAKango',2,'2011-10-05 00:00:00'),
29(16,'jyjey',0,'2012-08-25 00:00:00'),
30(17,'rocoty',0,'2011-10-05 00:00:00'),
31(17,'Nidboj132',0,'2023-02-05 00:00:00'),
32-- 3
33(18,'The Last Tofus',5,'2011-05-08 00:00:00'),
34(18,'Schlepian',4,'2011-10-08 00:00:00'),
35(18,'szeimartin',3,'2013-10-08 00:00:00'),
36(18,'Krzyhau',2,'2020-05-15 00:00:00'),
37(18,'Krzyhau',0,'2022-07-02 00:00:00'),
38(19,'LookLikeAKango',2,'2011-10-06 00:00:00'),
39(20,'Djinndrache',5,'2011-10-20 00:00:00'),
40(20,'Schlepian',4,'2011-10-30 00:00:00'),
41(20,'Jetwash',3,'2014-09-04 00:00:00'),
42(20,'Krzyhau',2,'2022-04-24 00:00:00'),
43(21,'LookLikeAKango',4,'2011-10-06 00:00:00'),
44(21,'ncla',2,'2011-10-30 00:00:00'),
45(21,'PerOculos',0,'2019-07-08 00:00:00'),
46(22,'Tyronis',0,'2011-10-05 00:00:00'),
47(23,'LookLikeAKango',2,'2011-10-06 00:00:00'),
48(23,'Krzyhau',0,'2018-08-01 00:00:00'),
49(24,'LeviHB',0,'2011-04-30 00:00:00'),
50(25,'Tyronis',0,'2011-10-06 00:00:00'),
51(26,'Schlepian',3,'2011-10-30 00:00:00'),
52(26,'Tyronis',2,'2012-01-08 00:00:00'),
53(26,'PerOculos',0,'2016-06-08 00:00:00'),
54-- 4
55(27,'LeviHB',2,'2011-05-01 00:00:00'),
56(27,'PerOculos',0,'2020-07-13 00:00:00'),
57(28,'LeviHB',7,'2011-05-01 00:00:00'),
58(28,'Andy M.J.',2,'2011-10-07 00:00:00'),
59(28,'Krzyhau',0,'2018-05-19 00:00:00'),
60(29,'LeviHB',0,'2011-05-01 00:00:00'),
61(30,'Schlepian',2,'2011-10-30 00:00:00'),
62(31,'Tyronis',0,'2011-10-06 00:00:00'),
63-- 5
64(32,'Tyronis',6,'2011-10-21 00:00:00'),
65(32,'Nidboj132',5,'2022-04-24 00:00:00'),
66(33,'Tyronis',7,'2011-10-06 00:00:00'),
67(33,'ISimmo',5,'2011-11-02 00:00:00'),
68(33,'PerOculos',4,'2017-05-30 00:00:00'),
69(34,'Schlepian',3,'2011-11-01 00:00:00'),
70(34,'Krzyhau',2,'2020-10-14 00:00:00'),
71(34,'zach',0,'2022-11-02 00:00:00'),
72(35,'Krank',2,'2012-07-28 00:00:00'),
73-- 6
74(36,'Tyronis',6,'2011-10-06 00:00:00'),
75(36,'CalmlyFrenetic',5,'2011-10-09 00:00:00'),
76(36,'sicklebrick',4,'2012-09-13 00:00:00'),
77(36,'Nidboj132',2,'2023-03-04 00:00:00'),
78(37,'LookLikeAKango',7,'2011-10-06 00:00:00'),
79(37,'Schlepian',6,'2011-11-01 00:00:00'),
80(37,'Tyronis',5,'2012-01-28 00:00:00'),
81(37,'Nidboj132',4,'2021-08-22 00:00:00'),
82(38,'Andy M.J.',2,'2011-10-06 00:00:00'),
83(38,'Sanguine Dagger',0,'2012-03-19 00:00:00'),
84(39,'Lambda Core',6,'2011-05-13 00:00:00'),
85(39,'The Last Tofus',5,'2011-05-13 00:00:00'),
86(39,'LookLikeAKango',4,'2011-10-16 00:00:00'),
87(39,'Kittaye',3,'2013-03-25 00:00:00'),
88(40,'LookLikeAKango',7,'2011-10-07 00:00:00'),
89(40,'Schlepian',6,'2011-11-05 00:00:00'),
90(40,'Kittaye',4,'2013-04-01 00:00:00'),
91(40,'Kittaye',3,'2014-09-13 00:00:00'),
92(40,'szeimartin',2,'2014-09-13 00:00:00'),
93(40,'Kittaye',0,'2014-09-15 00:00:00'),
94(41,'CalmlyFrenetic',7,'2011-10-09 00:00:00'),
95(41,'Jaso',6,'2011-10-11 00:00:00'),
96(41,'Krank',5,'2012-07-17 00:00:00'),
97-- 7
98(42,'LookLikeAKango',4,'2011-05-17 00:00:00'),
99(42,'ISimmo',2,'2011-11-07 00:00:00'),
100(43,'lmao4ever',5,'2011-10-30 00:00:00'),
101(43,'Jaso',2,'2011-11-09 00:00:00'),
102(43,'feliser',0,'2022-06-26 00:00:00'),
103(44,'LookLikeAKango',18,'2011-10-07 00:00:00'),
104(44,'Tyronis',13,'2011-10-30 00:00:00'),
105(44,'Tyronis',12,'2011-11-10 00:00:00'),
106(44,'Jetwash',11,'2017-06-12 00:00:00'),
107(44,'Krzyhau',9,'2022-01-02 00:00:00'),
108(45,'LookLikeAKango',23,'2011-10-08 00:00:00'),
109(45,'CalmlyFrenetic',22,'2011-10-09 00:00:00'),
110(45,'cgreactor',17,'2011-10-09 00:00:00'),
111(45,'CalmlyFrenetic',16,'2011-10-10 00:00:00'),
112(45,'LookLikeAKango',15,'2011-10-19 00:00:00'),
113(45,'Jaso',12,'2012-07-19 00:00:00'),
114(45,'Krank',10,'2013-01-31 00:00:00'),
115(45,'Kittaye',7,'2013-04-04 00:00:00'),
116(45,'PerOculos',4,'2014-09-13 00:00:00'),
117-- 8
118(46,'sparkle1princess',6,'2012-03-24 00:00:00'),
119(46,'Krzyhau',2,'2019-11-21 00:00:00'),
120(47,'holydevel',2,'2011-10-06 00:00:00'),
121(47,'JesusCatFace',0,'2015-01-16 00:00:00'),
122(48,'LookLikeAKango',5,'2011-10-08 00:00:00'),
123(48,'Tyronis',2,'2011-10-08 00:00:00'),
124(48,'adzicents',0,'2011-10-09 00:00:00'),
125(49,'adzicents',4,'2011-10-07 00:00:00'),
126(49,'Schlepian',2,'2011-10-08 00:00:00'),
127(49,'Nidboj132',0,'2022-09-26 00:00:00'),
128(50,'LookLikeAKango',4,'2011-10-08 00:00:00'),
129(50,'Tyronis',2,'2011-10-11 00:00:00'),
130(50,'sicklebrick',0,'2013-03-20 00:00:00'),
131(51,'Andy M.J.',3,'2011-10-08 00:00:00'),
132(51,'LookLikeAKango',2,'2011-10-20 00:00:00'),
133(52,'Jaso',0,'2011-10-10 00:00:00'),
134(53,'LookLikeAKango',9,'2011-10-08 00:00:00'),
135(53,'LookLikeAKango',2,'2011-10-20 00:00:00'),
136(53,'Schlepian',0,'2011-11-06 00:00:00'),
137(54,'LookLikeAKango',7,'2011-06-01 00:00:00'),
138(54,'Jaso',6,'2011-10-09 00:00:00'),
139(54,'Schlepian',5,'2011-11-06 00:00:00'),
140(54,'Spyrunite',4,'2012-08-30 00:00:00'),
141(54,'Krzyhau',3,'2019-04-22 00:00:00'),
142(55,'LookLikeAKango',7,'2011-10-08 00:00:00'),
143(55,'CalmlyFrenetic',3,'2011-10-09 00:00:00'),
144(55,'Jaso',2,'2011-11-26 00:00:00'),
145(55,'PerOculos',0,'2021-02-06 00:00:00'),
146(56,'CalmlyFrenetic',9,'2011-10-08 00:00:00'),
147(56,'LookLikeAKango',5,'2011-10-09 00:00:00'),
148(56,'CalmlyFrenetic',4,'2011-10-09 00:00:00'),
149(56,'Jetwash',2,'2014-09-05 00:00:00'),
150-- 9
151(57,'JNS',7,'2011-07-21 00:00:00'),
152(57,'Krank',5,'2012-07-29 00:00:00'),
153(57,'Krzyhau',0,'2017-10-29 00:00:00'),
154(58,'Stimich',2,'2011-10-11 00:00:00'),
155(59,'Isimmo',7,'2011-11-04 00:00:00'),
156(59,'sicklebrick',6,'2013-03-20 00:00:00'),
157(60,'CalmlyFrenetic',7,'2011-10-19 00:00:00'),
158(60,'Tyronis',6,'2011-11-01 00:00:00'),
159-- Portal 2 Cooperative
160-- 1
161(63,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
162(64,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
163(64,'Chubfish & Exhale',2,'2011-11-01 00:00:00'),
164(65,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
165(65,'Nidboj132 & Oryn',3,'2022-02-03 00:00:00'),
166(66,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
167(66,'Schlepian & Chubfish',2,'2011-10-01 00:00:00'),
168(67,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
169(68,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
170-- 2
171(69,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
172(70,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
173(70,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
174(70,'Gocnak & z1mb0bw4y',2,'2012-08-03 00:00:00'),
175(70,'DM_ & VEGA',0,'2017-10-01 00:00:00'),
176(71,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
177(71,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
178(72,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
179(72,'Schlepian & LongJohnDickWeed',2,'2011-10-01 00:00:00'),
180(73,'Stimich & HiTMaRkS',9,'2011-05-09 00:00:00'),
181(73,'Mathias123961 & Sir Spawn Alot',8,'2011-08-01 00:00:00'),
182(73,'Schlepian & Lemonsunshine',7,'2011-11-01 00:00:00'),
183(73,'DM_ & LsDK_',6,'2018-01-01 00:00:00'),
184(73,'Krzyhau & Klooger',4,'2018-11-01 00:00:00'),
185(74,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
186(74,'Stimich & Pitkakorva',7,'2011-10-11 00:00:00'),
187(74,'Schlepian & Isimmo',3,'2011-10-28 00:00:00'),
188(74,'Zypeh & szeimartin',2,'2013-11-01 00:00:00'),
189(75,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
190(75,'Schlepian & Urination',4,'2011-10-01 00:00:00'),
191(75,'Schlepian & Lemonsunshine',2,'2012-02-01 00:00:00'),
192(75,'DM_ & follon',0,'2015-04-01 00:00:00'),
193(76,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
194(76,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
195-- 3
196(77,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
197(78,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
198(78,'DM_ & marK',3,'2016-11-01 00:00:00'),
199(78,'Nidboj132 & Oryn',2,'2021-09-04 00:00:00'),
200(79,'ganonscrub & ?',5,'2011-04-01 00:00:00'),
201(79,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
202(79,'Chubfish & Exhale',2,'2012-08-04 00:00:00'),
203(80,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
204(80,'Chubfish & Exhale',4,'2011-12-01 00:00:00'),
205(81,'Mathias123961 & Sir Spawn Alot',7,'2011-08-01 00:00:00'),
206(81,'Schlepian & Lemonsunshine',6,'2011-10-01 00:00:00'),
207(81,'takz & dawn',5,'2011-11-01 00:00:00'),
208(81,'Nidboj132 & Oryn',4,'2021-03-25 00:00:00'),
209(82,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
210(83,'Mathias123961 & Sir Spawn Alot',5,'2011-08-01 00:00:00'),
211(83,'Schlepian & Lemonsunshine',2,'2011-10-01 00:00:00'),
212(83,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
213(84,'Mathias123961 & Sir Spawn Alot',6,'2011-08-01 00:00:00'),
214(84,'Schlepian & Chubfish',4,'2011-10-01 00:00:00'),
215(84,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
216(84,'DM_ & wS',0,'2015-05-01 00:00:00'),
217-- 4
218(85,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
219(85,'Chubfish & Exhale',0,'2011-10-01 00:00:00'),
220(86,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
221(86,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
222(87,'Mathias123961 & Sir Spawn Alot',3,'2011-08-01 00:00:00'),
223(87,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
224(87,'DM_ & follon',0,'2015-04-01 00:00:00'),
225(88,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
226(88,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
227(89,'Mathias123961 & Sir Spawn Alot',0,'2011-08-01 00:00:00'),
228(90,'Mathias123961 & Sir Spawn Alot',4,'2011-09-01 00:00:00'),
229(90,'Schlepian & Urination',2,'2011-10-01 00:00:00'),
230(90,'Klooger & Jetwash',0,'2016-08-01 00:00:00'),
231(91,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
232(91,'Undead & Zypeh',0,'2013-05-19 00:00:00'),
233(92,'txx478 & ?',5,'2011-05-01 00:00:00'),
234(92,'Mathias123961 & Sir Spawn Alot',4,'2011-08-01 00:00:00'),
235(92,'Schlepian & Gopherdude',2,'2011-10-01 00:00:00'),
236(92,'ncla & takz',0,'2012-02-01 00:00:00'),
237(93,'Mathias123961 & Sir Spawn Alot',2,'2011-08-01 00:00:00'),
238(93,'Schlepian & Gopherdude',0,'2011-10-01 00:00:00'),
239-- 5
240(94,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
241(94,'Klooger & Imanex',0,'2013-08-01 00:00:00'),
242(95,'Schlepian & Issimoi',2,'2011-10-01 00:00:00'),
243(96,'ThePortalPatrol & ?',4,'2011-04-01 00:00:00'),
244(96,'sparkle1princess & Zypeh',2,'2014-01-01 00:00:00'),
245(97,'Stimich & HiTMaRkS',7,'2011-05-13 00:00:00'),
246(97,'Schlepian & Lemonsunshine',4,'2011-10-01 00:00:00'),
247(97,'DM_ & wS',2,'2014-05-01 00:00:00'),
248(98,'Imanex & 00svo',0,'2011-11-01 00:00:00'),
249(99,'Schlepian & Gopherdude',3,'2011-10-01 00:00:00'),
250(99,'Imanex & Klooger',2,'2013-08-01 00:00:00'),
251(99,'DM_ & wS',0,'2015-05-01 00:00:00'),
252(100,'Schlepian & Bananasaurus Rex',0,'2011-10-01 00:00:00'),
253(101,'Chubfish & Exhale',2,'2011-12-01 00:00:00'),
254(101,'DM_ & follon',0,'2015-04-01 00:00:00'),
255-- 6
256(102,'dawn & takz',3,'2011-11-18 00:00:00'),
257(102,'Chubfish & Exhale',2,'2012-01-01 00:00:00'),
258(102,'Imanex & Klooger',0,'2013-08-01 00:00:00'),
259(103,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
260(104,'Schlepian & Lemonsunshine',0,'2011-10-01 00:00:00'),
261(105,'Blaizerazer & ?',8,'2011-10-01 00:00:00'),
262(105,'Schlepian & Lemonsunshine',5,'2011-11-01 00:00:00'),
263(105,'Imanex & Klooger',4,'2013-08-01 00:00:00'),
264(105,'DM_ & wS',3,'2014-05-01 00:00:00'),
265(105,'DM_ & follon',2,'2015-04-01 00:00:00'),
266(106,'Schlepian & Bananasaurus Rex',4,'2011-10-01 00:00:00'),
267(106,'Gig & takz',3,'2012-06-01 00:00:00'),
268(106,'Imanex & Klooger',0,'2013-06-01 00:00:00'),
269(107,'Chubfish & Exhale',2,'2011-10-01 00:00:00'),
270(107,'DM_ & follon',0,'2015-04-01 00:00:00'),
271(108,'DaFox & P',0,'2011-12-01 00:00:00'),
272(109,'Schlepian & Tyronis',5,'2011-10-01 00:00:00'),
273(109,'Chubfish & Exhale',0,'2011-12-01 00:00:00'),
274(110,'Tyronis & mr.bob806',15,'2011-10-01 00:00:00'),
275(110,'Schlepian & Chubfish',6,'2011-11-01 00:00:00'),
276(110,'00svo & z1mb0bw4y',5,'2012-08-08 00:00:00'),
277(110,'00svo & z1mb0bw4y',4,'2012-08-10 00:00:00'),
278(110,'Klooger & z1mb0bw4y',2,'2014-02-01 00:00:00'),
279(110,'DM_ & follon',0,'2015-04-01 00:00:00'); \ No newline at end of file
diff --git a/backend/database/init.sql b/backend/database/init.sql
index 81461c2..51a4881 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -44,13 +44,11 @@ CREATE TABLE maps (
44CREATE TABLE map_history ( 44CREATE TABLE map_history (
45 id SMALLSERIAL, 45 id SMALLSERIAL,
46 map_id SMALLINT NOT NULL, 46 map_id SMALLINT NOT NULL,
47 user_id TEXT,
48 user_name TEXT NOT NULL, 47 user_name TEXT NOT NULL,
49 score_count SMALLINT NOT NULL, 48 score_count SMALLINT NOT NULL,
50 record_date TIMESTAMP NOT NULL, 49 record_date TIMESTAMP NOT NULL,
51 PRIMARY KEY (id), 50 PRIMARY KEY (id),
52 FOREIGN KEY (map_id) REFERENCES maps(id), 51 FOREIGN KEY (map_id) REFERENCES maps(id)
53 FOREIGN KEY (user_id) REFERENCES users(steam_id)
54); 52);
55 53
56CREATE TABLE map_ratings ( 54CREATE TABLE map_ratings (
generated by cgit v1.2.3 (git 2.39.1) at 2025-11-08 21:14:36 +0000