-
Notifications
You must be signed in to change notification settings - Fork 3
/
notes.txt
89 lines (56 loc) · 5.17 KB
/
notes.txt
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
Notes:
- Season 2 began on March 18th, 2018. (See the "SoloSeasonStartDatetime" constant.)
- 536 is old Schoolbag ID, is always 2nd item (105 is first item)
- Race at 2018-04-01 03:55:49 has Schoolbag on slot 536
- Race at 2018-05-01 04:39:59 has Schoolbag on slot 536
- Race at 2018-07-01 04:54:06 has Schoolbag on slot 554
- Race at 2018-09-01 09:54:31 has Schoolbag on slot 554
- Race at 2018-10-01 01:46:12 has Schoolbag on slot 554
- Race at 2018-10-15 00:03:59 has Schoolbag on slot 554
- Race at 2018-10-23 03:37:37 has Schoolbag on slot 554
- Race at 2018-10-25 06:40:02 has Schoolbag on slot 554
- Race at 2018-10-26 02:42:17 has Schoolbag on slot 554
- Race at 2018-10-27 03:44:36 does not have Schoolbag
- Race at 2018-11-01 05:57:08 does not have Schoolbag
- Race at 2019-01-01 03:48:42 does not have Schoolbag
- Race at 2020-01-01 05:56:15 does not have Schoolbag
Helpful SQL queries
-------------------
Get ranked solo races:
SELECT races.id, races.name, races.format, users.username, races.datetime_finished FROM races JOIN race_participants ON races.id = race_participants.race_id JOIN users ON users.id = race_participants.user_id WHERE ranked = 1 AND solo = 1 ORDER BY datetime_finished LIMIT 10;
Get ranked solo races with a certain date range:
SELECT races.id, races.name, races.format, users.username, races.datetime_finished FROM races JOIN race_participants ON races.id = race_participants.race_id JOIN users ON users.id = race_participants.user_id WHERE ranked = 1 AND solo = 1 AND races.datetime_finished > "2018-10-26 00:00:00" ORDER BY datetime_finished LIMIT 1;
Get the items (collectibles) for a race:
SELECT * from race_participant_items WHERE race_participant_id = (SELECT id FROM race_participants WHERE user_id = (SELECT id FROM users WHERE users.username = "") AND race_id = 123) ORDER BY datetime_acquired;
Invalidate out of date ranked solo runs:
UPDATE races SET ranked = 0 WHERE solo = 1 AND ranked = 1 AND datetime_finished > "2018-10-26 00:00:00";
Find races with finish times of under 1 minutes:
SELECT races.id, races.datetime_finished, users.username, race_participants.run_time FROM races JOIN race_participants ON races.id = race_participants.race_id JOIN users ON users.id = race_participants.user_id WHERE race_participants.run_time < 60000 AND races.solo = 0 AND race_participants.place > 0 ORDER BY races.id;
SELECT COUNT(races.id) FROM races JOIN race_participants ON races.id = race_participants.race_id JOIN users ON users.id = race_participants.user_id WHERE race_participants.run_time < 60000 AND races.solo = 0 AND race_participants.place > 0;
Fix tables not having "ON DELETE CASCADE":
ALTER TABLE race_participants DROP FOREIGN KEY `race_participants_ibfk_2`;
ALTER TABLE race_participants ADD CONSTRAINT `race_participants_ibfk_2` FOREIGN KEY (`race_id`) REFERENCES `races` (`id`) ON DELETE CASCADE;
Find all races from user:
SELECT users.username, races.id, races.name, races.datetime_finished FROM races JOIN race_participants ON races.id = race_participants.race_id JOIN users ON users.id = race_participants.user_id WHERE users.username = "TestAccount1";
Delete corrupted races:
DELETE FROM races WHERE ranked = 1 AND solo = 1 AND datetime_finished > "2021-12-03 00:00:00" AND datetime_finished < "2021-12-05 18:15:00";
Delete old ranked solo races for specific users:
DELETE races FROM races JOIN race_participants ON race_participants.race_id = races.id JOIN users ON users.id = race_participants.user_id WHERE races.ranked = 1 AND races.solo = 1 AND races.datetime_finished > "2021-12-03 00:00:00" AND users.username = "mgln" AND races.id < 214073;
DELETE races FROM races JOIN race_participants ON race_participants.race_id = races.id JOIN users ON users.id = race_participants.user_id WHERE races.ranked = 1 AND races.solo = 1 AND races.datetime_finished > "2021-12-03 00:00:00" AND users.username = "Gamonymous" AND races.id < 213906;
DELETE races FROM races JOIN race_participants ON race_participants.race_id = races.id JOIN users ON users.id = race_participants.user_id WHERE races.ranked = 1 AND races.solo = 1 AND races.datetime_finished > "2021-12-03 00:00:00" AND users.username = "Monkeypot" AND races.id < 213818;
DELETE races FROM races JOIN race_participants ON race_participants.race_id = races.id JOIN users ON users.id = race_participants.user_id WHERE races.ranked = 1 AND races.solo = 1 AND races.datetime_finished > "2021-12-03 00:00:00" AND users.username = "Fallenfor" AND races.id < 214038;
-------------
OLD SQL NOTES
-------------
/*
UPDATE races
JOIN race_participants ON race_participants.race_id = races.id
SET ranked = 0 WHERE
races.ranked = 1 AND
races.solo = 1 AND
races.format = "unseeded" AND
races.datetime_finished > "2018-09-08 00:00:00" AND
(SELECT COUNT(id) FROM race_participant_items WHERE item_id = 554 AND race_participant_id = race_participants.id) = 0
;
UPDATE races JOIN race_participants ON race_participants.race_id = races.id SET ranked = 0 WHERE races.ranked = 1 AND races.solo = 1 AND races.format = "unseeded" AND races.datetime_finished > "2018-09-08 00:00:00" AND (SELECT COUNT(id) FROM race_participant_items WHERE item_id = 554 AND race_participant_id = race_participants.id) = 0;
*/