Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET NAMES utf8mb4 */;
- /*!50503 SET NAMES utf8mb4 */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- DELIMITER //
- CREATE 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)
- BEGIN
- SET @_MapID:=(SELECT MapID FROM maps WHERE MapName = _Map);
- DELETE FROM times WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND PlayerID=_PlayerID AND tas=_tas;
- INSERT INTO times (MapID, Type, Style, PlayerID, Time, Jumps, Strafes, Timestamp, Sync, tas) VALUES (@_MapID, _Type, _Style, _PlayerID, _Time, _Jumps, _Strafes, _Timestamp, _Sync, _tas);
- SET @_TimeInsertID:=LAST_INSERT_ID();
- IF(_IsRecord = 1)
- THEN
- SET @TotalRecords := (SELECT count(*) from recent_records WHERE MapID=@_MapID AND Type=_Type AND Style=_Style AND TAS=_tas);
- IF(@TotalRecords > 0)
- THEN
- 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);
- if(_PlayerID != @CurrentRecordHolder)
- THEN
- 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);
- INSERT INTO overtake (Overtaker, OvertakerTimeId, Overtakee, OvertakeeTimeId, Timestamp) VALUES (_PlayerID, @_TimeInsertID, @CurrentRecordHolder, @OeeTimeId, UNIX_TIMESTAMP());
- END IF;
- END IF;
- 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);
- UPDATE recent_records SET IsRecord = 0 WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas AND id != @_TimeInsertID;
- 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);
- END IF;
- END//
- DELIMITER ;
- DELIMITER //
- CREATE PROCEDURE `DeleteTimes`(IN _Map VARCHAR(255), IN _Type INT, IN _Style INT, IN _tas INT, IN _MinPos INT, IN _MaxPos INT)
- BEGIN
- SET @_MapID:=(SELECT MapID FROM maps WHERE MapName = _Map);
- SET @_Offset:=_MaxPos - _MinPos + 1;
- SET @MinPos:=_MinPos;
- SET @__Type:=_Type;
- SET @__Style:=_Style;
- Set @__tas:=_tas;
- PREPARE stmt FROM "DELETE FROM times WHERE rownum IN (SELECT b.rownum FROM
- (SELECT * FROM times WHERE MapID=? AND Type=? AND Style=? AND tas=? ORDER BY time ASC) a
- JOIN
- (SELECT * FROM times WHERE MapID=? AND Type=? AND Style=? AND tas=? ORDER BY time ASC LIMIT ?, ?) b
- ON a.rownum=b.rownum);";
- EXECUTE stmt USING @_MapID, @__Type, @__Style, @__tas, @_MapID, @__Type, @__Style, @__tas, @MinPos, @_Offset;
- IF(_MinPos = 0)
- THEN
- UPDATE recent_records SET IsRecord = 0 WHERE MapID = @_MapID AND Type = _Type AND Style = _Style AND TAS = _tas AND IsRecord = 1;
- 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);
- END IF;
- END//
- DELIMITER ;
- CREATE TABLE IF NOT EXISTS `maps` (
- `MapID` int(11) NOT NULL AUTO_INCREMENT,
- `MapName` text,
- `Tier` int(11) DEFAULT '1',
- `InMapCycle` tinyint(4) NOT NULL DEFAULT '0',
- `HasZones` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`MapID`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `overtake` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `Overtaker` int(11) DEFAULT NULL,
- `OvertakerTimeId` int(11) DEFAULT NULL,
- `Overtakee` int(11) DEFAULT NULL,
- `OvertakeeTimeId` int(11) DEFAULT NULL,
- `Timestamp` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `players` (
- `PlayerID` int(11) NOT NULL AUTO_INCREMENT,
- `SteamID` varchar(50) DEFAULT NULL,
- `User` varchar(50) DEFAULT NULL,
- `Playtime` int(11) NOT NULL DEFAULT '0',
- `LastConnection` int(11) DEFAULT '0',
- PRIMARY KEY (`PlayerID`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `ranks_maps` (
- `MapID` int(11) DEFAULT NULL,
- `PlayerID` int(11) DEFAULT NULL,
- `Type` int(11) DEFAULT NULL,
- `Style` int(11) DEFAULT NULL,
- `Points` float DEFAULT NULL,
- `Rank` int(11) DEFAULT NULL,
- `rowkey` int(11) NOT NULL AUTO_INCREMENT,
- `tas` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`rowkey`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `ranks_overall` (
- `PlayerID` int(11) DEFAULT NULL,
- `Points` float DEFAULT NULL,
- `Rank` int(11) DEFAULT NULL,
- `rowkey` bigint(20) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`rowkey`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `ranks_styles` (
- `PlayerID` int(11) DEFAULT NULL,
- `Type` int(11) DEFAULT NULL,
- `Style` int(11) DEFAULT NULL,
- `Points` float DEFAULT NULL,
- `Rank` int(11) DEFAULT NULL,
- `rowkey` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`rowkey`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- DELIMITER //
- CREATE PROCEDURE `recalcmappts`(IN `map` VARCHAR(255), IN `inType` int, IN `inStyle` int, IN `inTAS` int)
- BEGIN
- SET @vmapid:=(SELECT MapID FROM maps WHERE MapName = map LIMIT 0, 1);
- SET @MapTier:=(SELECT Tier FROM maps WHERE MapID=@vmapid);
- IF(inType = 1)
- THEN
- SET @MapTier:=1;
- END IF;
- SET @Competition:=(SELECT count(*) FROM times WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS);
- SET @curRank:=0;
- SET @pointScale:=1;
- IF(inTAS = 1)
- THEN
- SET @pointScale:=0;
- END IF;
- DELETE FROM ranks_maps WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS;
- INSERT INTO ranks_maps (MapID, PlayerID, Type, Style, tas, Points, Rank)
- SELECT @vmapid, PlayerID, inType, inStyle, inTAS, @MapTier * (@Competition - @curRank) * @pointScale AS Points, CASE
- WHEN @curRank := @curRank + 1 THEN @curRank
- END AS Rank
- FROM times
- WHERE MapID=@vmapid AND Type=inType AND Style=inStyle AND tas=inTAS
- ORDER BY Time, Timestamp;
- END//
- DELIMITER ;
- DELIMITER //
- CREATE PROCEDURE `recalcpts`(IN inMainStyleList int, IN inBonusStyleList int)
- BEGIN
- DELETE FROM ranks_overall;
- SET @curRank:=0;
- INSERT INTO ranks_overall (PlayerID, Points, Rank)
- SELECT PlayerID, t.Points AS Points,
- CASE WHEN @curRank := @curRank + 1 THEN @curRank
- END AS Rank
- FROM (SELECT PlayerID, SUM(Points) AS Points FROM ranks_styles WHERE
- CASE
- WHEN Type = 0 THEN inMainStyleList & (1 << Style) > 0
- WHEN Type = 1 THEN inBonusStyleList & (1 << Style) > 0
- END
- GROUP BY PlayerID ORDER BY SUM(Points) DESC) t;
- END//
- DELIMITER ;
- DELIMITER //
- CREATE PROCEDURE `recalcstylepts`(IN inType int, IN inStyle int)
- BEGIN
- DELETE FROM ranks_styles WHERE Type=inType AND Style=inStyle;
- SET @curRank:=0;
- SET @zones:=3;
- IF(inType = 1)
- THEN
- SET @zones:=12;
- END IF;
- INSERT INTO ranks_styles (PlayerID, Type, Style, Points, Rank)
- SELECT PlayerID, inType, inStyle, t.Points AS Points,
- CASE WHEN @curRank := @curRank + 1 THEN @curRank
- END AS Rank
- 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;
- END//
- DELIMITER ;
- CREATE TABLE IF NOT EXISTS `recent_records` (
- `MapID` int(11) DEFAULT NULL,
- `PlayerID` int(11) DEFAULT NULL,
- `Type` int(11) DEFAULT NULL,
- `Style` int(11) DEFAULT NULL,
- `TAS` int(11) DEFAULT NULL,
- `Time` float DEFAULT NULL,
- `Jumps` int(11) DEFAULT NULL,
- `Strafes` int(11) DEFAULT NULL,
- `Timestamp` int(11) DEFAULT NULL,
- `Sync` float DEFAULT NULL,
- `id` int(11) NOT NULL,
- `StillExists` tinyint(4) DEFAULT '0',
- `IsRecord` tinyint(4) DEFAULT '0',
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `times` (
- `rownum` int(11) NOT NULL AUTO_INCREMENT,
- `MapID` int(11) DEFAULT NULL,
- `Type` int(11) DEFAULT NULL,
- `Style` int(11) DEFAULT NULL,
- `PlayerID` int(11) DEFAULT NULL,
- `Time` double DEFAULT NULL,
- `Jumps` int(11) DEFAULT NULL,
- `Strafes` int(11) DEFAULT NULL,
- `Timestamp` int(11) DEFAULT NULL,
- `Sync` double DEFAULT NULL,
- `tas` tinyint(4) DEFAULT '0',
- PRIMARY KEY (`rownum`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- CREATE TABLE IF NOT EXISTS `zones` (
- `RowID` int(11) NOT NULL AUTO_INCREMENT,
- `MapID` int(11) DEFAULT NULL,
- `Type` int(11) DEFAULT NULL,
- `point00` double DEFAULT NULL,
- `point01` double DEFAULT NULL,
- `point02` double DEFAULT NULL,
- `point10` double DEFAULT NULL,
- `point11` double DEFAULT NULL,
- `point12` double DEFAULT NULL,
- `unrestrict` int(11) NOT NULL DEFAULT '0',
- `ezhop` int(11) NOT NULL DEFAULT '0',
- `autohop` int(11) NOT NULL DEFAULT '0',
- `nolimit` int(11) NOT NULL DEFAULT '0',
- `actype` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`RowID`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
- /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
- /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement