· 6 years ago · Aug 22, 2019, 04:58 PM
1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
2/*!40101 SET NAMES utf8mb4 */;
3/*!50503 SET NAMES utf8mb4 */;
4/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
5/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
6
7DELIMITER //
8CREATE PROCEDURE `AddPlayerTime`(IN `_Map` VARCHAR(255), IN `_Type` INT, IN `_Style` INT, IN `_PlayerID` INT, IN `_Time` FLOAT, IN `_Jumps` INT, IN `_Strafes` INT, IN `_Timestamp` INT, IN `_Sync` FLOAT, IN `_tas` INT, IN `_IsRecord` TINYINT)
9BEGIN
10 SET @_MapID:=(SELECT MapID FROM maps WHERE MapName = _Map);
11 DELETE FROM times WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND PlayerID=_PlayerID AND tas=_tas;
12
13 INSERT INTO times (MapID, Type, Style, PlayerID, Time, Jumps, Strafes, Timestamp, Sync, tas) VALUES (@_MapID, _Type, _Style, _PlayerID, _Time, _Jumps, _Strafes, _Timestamp, _Sync, _tas);
14 SET @_TimeInsertID:=LAST_INSERT_ID();
15
16 IF(_IsRecord = 1)
17 THEN
18 SET @TotalRecords := (SELECT count(*) from recent_records WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND TAS=_tas);
19 IF(@TotalRecords > 0)
20 THEN
21 SET @CurrentRecordHolder := (SELECT PlayerID FROM recent_records WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND TAS=_tas AND IsRecord = 1 LIMIT 0, 1);
22 if(_PlayerID != @CurrentRecordHolder)
23 THEN
24 SET @OeeTimeId := (SELECT id FROM recent_records WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND TAS=_tas AND IsRecord = 1 LIMIT 0, 1);
25 INSERT INTO overtake (Overtaker, OvertakerTimeId, Overtakee, OvertakeeTimeId, Timestamp) VALUES (_PlayerID, @_TimeInsertID, @CurrentRecordHolder, @OeeTimeId, UNIX_TIMESTAMP());
26 END IF;
27 END IF;
28
29 INSERT INTO recent_records (MapID, Type, Style, PlayerID, Time, Jumps, Strafes, Timestamp, Sync, TAS, IsRecord, StillExists, id) VALUES ((SELECT MapID FROM maps WHERE MapName=_Map LIMIT 0, 1), _Type, _Style, _PlayerID, _Time, _Jumps, _Strafes, _Timestamp, _Sync, _tas, 1, 1, @_TimeInsertID);
30 UPDATE recent_records SET IsRecord = 0 WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas AND id != @_TimeInsertID;
31 UPDATE recent_records SET StillExists = 0 WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas AND id NOT IN (SELECT rownum FROM times WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND tas = _tas);
32 END IF;
33END//
34DELIMITER ;
35
36DELIMITER //
37CREATE PROCEDURE `DeleteTimes`(IN _Map VARCHAR(255), IN _Type INT, IN _Style INT, IN _tas INT, IN _MinPos INT, IN _MaxPos INT)
38BEGIN
39 SET @_MapID:=(SELECT MapID FROM maps WHERE MapName = _Map);
40 SET @_Offset:=_MaxPos - _MinPos + 1;
41 SET @MinPos:=_MinPos;
42 SET @__Type:=_Type;
43 SET @__Style:=_Style;
44 Set @__tas:=_tas;
45
46 PREPARE stmt FROM "DELETE FROM times WHERE rownum IN (SELECT b.rownum FROM
47 (SELECT * FROM times WHERE MapID=? AND Type=? AND Style=? AND tas=? ORDER BY time ASC) a
48 JOIN
49 (SELECT * FROM times WHERE MapID=? AND Type=? AND Style=? AND tas=? ORDER BY time ASC LIMIT ?, ?) b
50 ON a.rownum=b.rownum);";
51 EXECUTE stmt USING @_MapID, @__Type, @__Style, @__tas, @_MapID, @__Type, @__Style, @__tas, @MinPos, @_Offset;
52
53 IF(_MinPos = 0)
54 THEN
55 UPDATE recent_records SET IsRecord = 0 WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas AND IsRecord = 1;
56 UPDATE recent_records SET StillExists = 0 WHERE Type = _Type AND Style = _Style AND TAS = _tas AND id NOT IN (SELECT rownum FROM times WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas);
57 END IF;
58END//
59DELIMITER ;
60
61CREATE TABLE IF NOT EXISTS `maps` (
62 `MapID` int(11) NOT NULL AUTO_INCREMENT,
63 `MapName` text,
64 `Tier` int(11) DEFAULT '1',
65 `InMapCycle` tinyint(4) NOT NULL DEFAULT '0',
66 `HasZones` int(11) NOT NULL DEFAULT '0',
67 PRIMARY KEY (`MapID`)
68) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
69
70CREATE TABLE IF NOT EXISTS `overtake` (
71 `id` int(11) NOT NULL AUTO_INCREMENT,
72 `Overtaker` int(11) DEFAULT NULL,
73 `OvertakerTimeId` int(11) DEFAULT NULL,
74 `Overtakee` int(11) DEFAULT NULL,
75 `OvertakeeTimeId` int(11) DEFAULT NULL,
76 `Timestamp` int(11) DEFAULT NULL,
77 PRIMARY KEY (`id`)
78) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
79
80CREATE TABLE IF NOT EXISTS `players` (
81 `PlayerID` int(11) NOT NULL AUTO_INCREMENT,
82 `SteamID` varchar(50) DEFAULT NULL,
83 `User` varchar(50) DEFAULT NULL,
84 `Playtime` int(11) NOT NULL DEFAULT '0',
85 `LastConnection` int(11) DEFAULT '0',
86 PRIMARY KEY (`PlayerID`)
87) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
88
89CREATE TABLE IF NOT EXISTS `ranks_maps` (
90 `MapID` int(11) DEFAULT NULL,
91 `PlayerID` int(11) DEFAULT NULL,
92 `Type` int(11) DEFAULT NULL,
93 `Style` int(11) DEFAULT NULL,
94 `Points` float DEFAULT NULL,
95 `Rank` int(11) DEFAULT NULL,
96 `rowkey` int(11) NOT NULL AUTO_INCREMENT,
97 `tas` int(11) NOT NULL DEFAULT '0',
98 PRIMARY KEY (`rowkey`)
99) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
100
101CREATE TABLE IF NOT EXISTS `ranks_overall` (
102 `PlayerID` int(11) DEFAULT NULL,
103 `Points` float DEFAULT NULL,
104 `Rank` int(11) DEFAULT NULL,
105 `rowkey` bigint(20) NOT NULL AUTO_INCREMENT,
106 PRIMARY KEY (`rowkey`)
107) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
108
109CREATE TABLE IF NOT EXISTS `ranks_styles` (
110 `PlayerID` int(11) DEFAULT NULL,
111 `Type` int(11) DEFAULT NULL,
112 `Style` int(11) DEFAULT NULL,
113 `Points` float DEFAULT NULL,
114 `Rank` int(11) DEFAULT NULL,
115 `rowkey` int(11) NOT NULL AUTO_INCREMENT,
116 PRIMARY KEY (`rowkey`)
117) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
118
119DELIMITER //
120CREATE PROCEDURE `recalcmappts`(IN `map` VARCHAR(255), IN `inType` int, IN `inStyle` int, IN `inTAS` int)
121BEGIN
122 SET @vmapid:=(SELECT MapID FROM maps WHERE MapName = map LIMIT 0, 1);
123
124 SET @MapTier:=(SELECT Tier FROM maps WHERE MapID=@vmapid);
125 IF(inType = 1)
126 THEN
127 SET @MapTier:=1;
128 END IF;
129 SET @Competition:=(SELECT count(*) FROM times WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS);
130 SET @curRank:=0;
131
132 SET @pointScale:=1;
133
134 IF(inTAS = 1)
135 THEN
136 SET @pointScale:=0;
137 END IF;
138
139 DELETE FROM ranks_maps WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS;
140 INSERT INTO ranks_maps (MapID, PlayerID, Type, Style, tas, Points, Rank)
141 SELECT @vmapid, PlayerID, inType, inStyle, inTAS, @MapTier * (@Competition - @curRank) * @pointScale AS Points, CASE
142 WHEN @curRank := @curRank + 1 THEN @curRank
143 END AS Rank
144 FROM times
145 WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS
146 ORDER BY Time, Timestamp;
147END//
148DELIMITER ;
149
150DELIMITER //
151CREATE PROCEDURE `recalcpts`(IN inMainStyleList int, IN inBonusStyleList int)
152BEGIN
153 DELETE FROM ranks_overall;
154
155 SET @curRank:=0;
156
157 INSERT INTO ranks_overall (PlayerID, Points, Rank)
158 SELECT PlayerID, t.Points AS Points,
159 CASE WHEN @curRank := @curRank + 1 THEN @curRank
160 END AS Rank
161 FROM (SELECT PlayerID, SUM(Points) AS Points FROM ranks_styles WHERE
162 CASE
163 WHEN Type = 0 THEN inMainStyleList & (1 << Style) > 0
164 WHEN Type = 1 THEN inBonusStyleList & (1 << Style) > 0
165 END
166 GROUP BY PlayerID ORDER BY SUM(Points) DESC) t;
167END//
168DELIMITER ;
169
170DELIMITER //
171CREATE PROCEDURE `recalcstylepts`(IN inType int, IN inStyle int)
172BEGIN
173 DELETE FROM ranks_styles WHERE Type=inType AND Style=inStyle;
174
175 SET @curRank:=0;
176
177 SET @zones:=3;
178 IF(inType = 1)
179 THEN
180 SET @zones:=12;
181 END IF;
182
183 INSERT INTO ranks_styles (PlayerID, Type, Style, Points, Rank)
184 SELECT PlayerID, inType, inStyle, t.Points AS Points,
185 CASE WHEN @curRank := @curRank + 1 THEN @curRank
186 END AS Rank
187 FROM (SELECT rm.PlayerID, rm.Type, rm.Style, SUM(rm.Points) AS Points FROM ranks_maps AS rm, maps AS m WHERE Type=inType AND Style=inStyle AND rm.MapID=m.MapID AND m.InMapCycle=1 AND m.HasZones & @zones = @zones GROUP BY PlayerID ORDER BY SUM(Points) DESC) t;
188END//
189DELIMITER ;
190
191CREATE TABLE IF NOT EXISTS `recent_records` (
192 `MapID` int(11) DEFAULT NULL,
193 `PlayerID` int(11) DEFAULT NULL,
194 `Type` int(11) DEFAULT NULL,
195 `Style` int(11) DEFAULT NULL,
196 `TAS` int(11) DEFAULT NULL,
197 `Time` float DEFAULT NULL,
198 `Jumps` int(11) DEFAULT NULL,
199 `Strafes` int(11) DEFAULT NULL,
200 `Timestamp` int(11) DEFAULT NULL,
201 `Sync` float DEFAULT NULL,
202 `id` int(11) NOT NULL,
203 `StillExists` tinyint(4) DEFAULT '0',
204 `IsRecord` tinyint(4) DEFAULT '0',
205 PRIMARY KEY (`id`)
206) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
207
208CREATE TABLE IF NOT EXISTS `times` (
209 `rownum` int(11) NOT NULL AUTO_INCREMENT,
210 `MapID` int(11) DEFAULT NULL,
211 `Type` int(11) DEFAULT NULL,
212 `Style` int(11) DEFAULT NULL,
213 `PlayerID` int(11) DEFAULT NULL,
214 `Time` double DEFAULT NULL,
215 `Jumps` int(11) DEFAULT NULL,
216 `Strafes` int(11) DEFAULT NULL,
217 `Timestamp` int(11) DEFAULT NULL,
218 `Sync` double DEFAULT NULL,
219 `tas` tinyint(4) DEFAULT '0',
220 PRIMARY KEY (`rownum`)
221) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
222
223CREATE TABLE IF NOT EXISTS `zones` (
224 `RowID` int(11) NOT NULL AUTO_INCREMENT,
225 `MapID` int(11) DEFAULT NULL,
226 `Type` int(11) DEFAULT NULL,
227 `point00` double DEFAULT NULL,
228 `point01` double DEFAULT NULL,
229 `point02` double DEFAULT NULL,
230 `point10` double DEFAULT NULL,
231 `point11` double DEFAULT NULL,
232 `point12` double DEFAULT NULL,
233 `unrestrict` int(11) NOT NULL DEFAULT '0',
234 `ezhop` int(11) NOT NULL DEFAULT '0',
235 `autohop` int(11) NOT NULL DEFAULT '0',
236 `nolimit` int(11) NOT NULL DEFAULT '0',
237 `actype` int(11) NOT NULL DEFAULT '0',
238 PRIMARY KEY (`RowID`)
239) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
240
241/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
242/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
243/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;