diff options
Diffstat (limited to 'sourcemod/scripting/gokz-localranks/db/sql.sp')
| -rw-r--r-- | sourcemod/scripting/gokz-localranks/db/sql.sp | 411 |
1 files changed, 0 insertions, 411 deletions
diff --git a/sourcemod/scripting/gokz-localranks/db/sql.sp b/sourcemod/scripting/gokz-localranks/db/sql.sp deleted file mode 100644 index f768e9a..0000000 --- a/sourcemod/scripting/gokz-localranks/db/sql.sp +++ /dev/null @@ -1,411 +0,0 @@ -/* - SQL query templates. -*/ - - - -// =====[ MAPS ]===== - -char sqlite_maps_alter1[] = "\ -ALTER TABLE Maps \ - ADD InRankedPool INTEGER NOT NULL DEFAULT '0'"; - -char mysql_maps_alter1[] = "\ -ALTER TABLE Maps \ - ADD InRankedPool TINYINT NOT NULL DEFAULT '0'"; - -char sqlite_maps_insertranked[] = "\ -INSERT OR IGNORE INTO Maps \ - (InRankedPool, Name) \ - VALUES (%d, '%s')"; - -char sqlite_maps_updateranked[] = "\ -UPDATE OR IGNORE Maps \ - SET InRankedPool=%d \ - WHERE Name = '%s'"; - -char mysql_maps_upsertranked[] = "\ -INSERT INTO Maps (InRankedPool, Name) \ - VALUES (%d, '%s') \ - ON DUPLICATE KEY UPDATE \ - InRankedPool=VALUES(InRankedPool)"; - -char sql_maps_reset_mappool[] = "\ -UPDATE Maps \ - SET InRankedPool=0"; - -char sql_maps_getname[] = "\ -SELECT Name \ - FROM Maps \ - WHERE MapID=%d"; - -char sql_maps_searchbyname[] = "\ -SELECT MapID, Name \ - FROM Maps \ - WHERE Name LIKE '%%%s%%' \ - ORDER BY (Name='%s') DESC, LENGTH(Name) \ - LIMIT 1"; - - - -// =====[ PLAYERS ]===== - -char sql_players_getalias[] = "\ -SELECT Alias \ - FROM Players \ - WHERE SteamID32=%d"; - -char sql_players_searchbyalias[] = "\ -SELECT SteamID32, Alias \ - FROM Players \ - WHERE Players.Cheater=0 AND LOWER(Alias) LIKE '%%%s%%' \ - ORDER BY (LOWER(Alias)='%s') DESC, LastPlayed DESC \ - LIMIT 1"; - - - -// =====[ MAPCOURSES ]===== - -char sql_mapcourses_findid[] = "\ -SELECT MapCourseID \ - FROM MapCourses \ - WHERE MapID=%d AND Course=%d"; - - - -// =====[ GENERAL ]===== - -char sql_getpb[] = "\ -SELECT Times.RunTime, Times.Teleports \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - WHERE Times.SteamID32=%d AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d \ - ORDER BY Times.RunTime \ - LIMIT %d"; - -char sql_getpbpro[] = "\ -SELECT Times.RunTime \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - WHERE Times.SteamID32=%d AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d AND Times.Teleports=0 \ - ORDER BY Times.RunTime \ - LIMIT %d"; - -char sql_getmaptop[] = "\ -SELECT t.TimeID, t.SteamID32, p.Alias, t.RunTime AS PBTime, t.Teleports \ - FROM Times t \ - INNER JOIN MapCourses mc ON mc.MapCourseID=t.MapCourseID \ - INNER JOIN Players p ON p.SteamID32=t.SteamID32 \ - LEFT OUTER JOIN Times t2 ON t2.SteamID32=t.SteamID32 \ - AND t2.MapCourseID=t.MapCourseID AND t2.Mode=t.Mode AND t2.RunTime<t.RunTime \ - WHERE t2.TimeID IS NULL AND p.Cheater=0 AND mc.MapID=%d AND mc.Course=%d AND t.Mode=%d \ - ORDER BY PBTime \ - LIMIT %d"; - -char sql_getmaptoppro[] = "\ -SELECT t.TimeID, t.SteamID32, p.Alias, t.RunTime AS PBTime, t.Teleports \ - FROM Times t \ - INNER JOIN MapCourses mc ON mc.MapCourseID=t.MapCourseID \ - INNER JOIN Players p ON p.SteamID32=t.SteamID32 \ - LEFT OUTER JOIN Times t2 ON t2.SteamID32=t.SteamID32 AND t2.MapCourseID=t.MapCourseID \ - AND t2.Mode=t.Mode AND t2.RunTime<t.RunTime AND t.Teleports=0 AND t2.Teleports=0 \ - WHERE t2.TimeID IS NULL AND p.Cheater=0 AND mc.MapID=%d \ - AND mc.Course=%d AND t.Mode=%d AND t.Teleports=0 \ - ORDER BY PBTime \ - LIMIT %d"; - -char sql_getwrs[] = "\ -SELECT MIN(Times.RunTime), MapCourses.Course, Times.Mode \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d \ - GROUP BY MapCourses.Course, Times.Mode"; - -char sql_getwrspro[] = "\ -SELECT MIN(Times.RunTime), MapCourses.Course, Times.Mode \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d AND Times.Teleports=0 \ - GROUP BY MapCourses.Course, Times.Mode"; - -char sql_getpbs[] = "\ -SELECT MIN(Times.RunTime), MapCourses.Course, Times.Mode \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - WHERE Times.SteamID32=%d AND MapCourses.MapID=%d \ - GROUP BY MapCourses.Course, Times.Mode"; - -char sql_getpbspro[] = "\ -SELECT MIN(Times.RunTime), MapCourses.Course, Times.Mode \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - WHERE Times.SteamID32=%d AND MapCourses.MapID=%d AND Times.Teleports=0 \ - GROUP BY MapCourses.Course, Times.Mode"; - -char sql_getmaprank[] = "\ -SELECT COUNT(DISTINCT Times.SteamID32) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d AND MapCourses.Course=%d \ - AND Times.Mode=%d AND Times.RunTime < \ - (SELECT MIN(Times.RunTime) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND Times.SteamID32=%d AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d) \ - + 1"; - -char sql_getmaprankpro[] = "\ -SELECT COUNT(DISTINCT Times.SteamID32) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d AND MapCourses.Course=%d \ - AND Times.Mode=%d AND Times.Teleports=0 \ - AND Times.RunTime < \ - (SELECT MIN(Times.RunTime) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND Times.SteamID32=%d AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d AND Times.Teleports=0) \ - + 1"; - -char sql_getlowestmaprank[] = "\ -SELECT COUNT(DISTINCT Times.SteamID32) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d"; - -char sql_getlowestmaprankpro[] = "\ -SELECT COUNT(DISTINCT Times.SteamID32) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d AND Times.Teleports=0"; - -char sql_getcount_maincourses[] = "\ -SELECT COUNT(*) \ - FROM MapCourses \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course=0"; - -char sql_getcount_maincoursescompleted[] = "\ -SELECT COUNT(DISTINCT Times.MapCourseID) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course=0 \ - AND Times.SteamID32=%d AND Times.Mode=%d"; - -char sql_getcount_maincoursescompletedpro[] = "\ -SELECT COUNT(DISTINCT Times.MapCourseID) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course=0 \ - AND Times.SteamID32=%d AND Times.Mode=%d AND Times.Teleports=0"; - -char sql_getcount_bonuses[] = "\ -SELECT COUNT(*) \ - FROM MapCourses \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course>0"; - -char sql_getcount_bonusescompleted[] = "\ -SELECT COUNT(DISTINCT Times.MapCourseID) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course>0 \ - AND Times.SteamID32=%d AND Times.Mode=%d"; - -char sql_getcount_bonusescompletedpro[] = "\ -SELECT COUNT(DISTINCT Times.MapCourseID) \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - WHERE Maps.InRankedPool=1 AND MapCourses.Course>0 \ - AND Times.SteamID32=%d AND Times.Mode=%d AND Times.Teleports=0"; - -char sql_gettopplayers[] = "\ -SELECT Players.SteamID32, Players.Alias, COUNT(*) AS RecordCount \ - FROM Times \ - INNER JOIN \ - (SELECT Times.MapCourseID, Times.Mode, MIN(Times.RunTime) AS RecordTime \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND Maps.InRankedPool=1 AND MapCourses.Course=0 \ - AND Times.Mode=%d \ - GROUP BY Times.MapCourseID) Records \ - ON Times.MapCourseID=Records.MapCourseID AND Times.Mode=Records.Mode AND Times.RunTime=Records.RecordTime \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - GROUP BY Players.SteamID32, Players.Alias \ - ORDER BY RecordCount DESC \ - LIMIT %d"; // Doesn't include bonuses - -char sql_gettopplayerspro[] = "\ -SELECT Players.SteamID32, Players.Alias, COUNT(*) AS RecordCount \ - FROM Times \ - INNER JOIN \ - (SELECT Times.MapCourseID, Times.Mode, MIN(Times.RunTime) AS RecordTime \ - FROM Times \ - INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID \ - INNER JOIN Maps ON Maps.MapID=MapCourses.MapID \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - WHERE Players.Cheater=0 AND Maps.InRankedPool=1 AND MapCourses.Course=0 \ - AND Times.Mode=%d AND Times.Teleports=0 \ - GROUP BY Times.MapCourseID) Records \ - ON Times.MapCourseID=Records.MapCourseID AND Times.Mode=Records.Mode AND Times.RunTime=Records.RecordTime AND Times.Teleports=0 \ - INNER JOIN Players ON Players.SteamID32=Times.SteamID32 \ - GROUP BY Players.SteamID32, Players.Alias \ - ORDER BY RecordCount DESC \ - LIMIT %d"; // Doesn't include bonuses - -char sql_getaverage[] = "\ -SELECT AVG(PBTime), COUNT(*) \ - FROM \ - (SELECT MIN(Times.RunTime) AS PBTime \ - FROM Times \ - INNER JOIN MapCourses ON Times.MapCourseID=MapCourses.MapCourseID \ - INNER JOIN Players ON Times.SteamID32=Players.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d \ - GROUP BY Times.SteamID32) AS PBTimes"; - -char sql_getaverage_pro[] = "\ -SELECT AVG(PBTime), COUNT(*) \ - FROM \ - (SELECT MIN(Times.RunTime) AS PBTime \ - FROM Times \ - INNER JOIN MapCourses ON Times.MapCourseID=MapCourses.MapCourseID \ - INNER JOIN Players ON Times.SteamID32=Players.SteamID32 \ - WHERE Players.Cheater=0 AND MapCourses.MapID=%d \ - AND MapCourses.Course=%d AND Times.Mode=%d AND Times.Teleports=0 \ - GROUP BY Times.SteamID32) AS PBTimes"; - -char sql_getrecentrecords[] = "\ -SELECT Maps.Name, MapCourses.Course, MapCourses.MapCourseID, Players.Alias, a.RunTime \ - FROM Times AS a \ - INNER JOIN MapCourses ON a.MapCourseID=MapCourses.MapCourseID \ - INNER JOIN Maps ON MapCourses.MapID=Maps.MapID \ - INNER JOIN Players ON a.SteamID32=Players.SteamID32 \ - WHERE Players.Cheater=0 AND Maps.InRankedPool AND a.Mode=%d \ - AND NOT EXISTS \ - (SELECT * \ - FROM Times AS b \ - WHERE a.MapCourseID=b.MapCourseID AND a.Mode=b.Mode \ - AND a.Created>b.Created AND a.RunTime>b.RunTime) \ - ORDER BY a.TimeID DESC \ - LIMIT %d"; - -char sql_getrecentrecords_pro[] = "\ -SELECT Maps.Name, MapCourses.Course, MapCourses.MapCourseID, Players.Alias, a.RunTime \ - FROM Times AS a \ - INNER JOIN MapCourses ON a.MapCourseID=MapCourses.MapCourseID \ - INNER JOIN Maps ON MapCourses.MapID=Maps.MapID \ - INNER JOIN Players ON a.SteamID32=Players.SteamID32 \ - WHERE Players.Cheater=0 AND Maps.InRankedPool AND a.Mode=%d AND a.Teleports=0 \ - AND NOT EXISTS \ - (SELECT * \ - FROM Times AS b \ - WHERE b.Teleports=0 AND a.MapCourseID=b.MapCourseID AND a.Mode=b.Mode \ - AND a.Created>b.Created AND a.RunTime>b.RunTime) \ - ORDER BY a.TimeID DESC \ - LIMIT %d"; - - - -// =====[ JUMPSTATS ]===== - -char sql_jumpstats_gettop[] = "\ -SELECT j.JumpID, p.SteamID32, p.Alias, j.Block, j.Distance, j.Strafes, j.Sync, j.Pre, j.Max, j.Airtime \ - FROM \ - Jumpstats j \ - INNER JOIN \ - Players p ON \ - p.SteamID32=j.SteamID32 AND \ - p.Cheater = 0 \ - INNER JOIN \ - ( \ - SELECT j.SteamID32, j.JumpType, j.Mode, j.IsBlockJump, MAX(j.Distance) BestDistance \ - FROM \ - Jumpstats j \ - INNER JOIN \ - ( \ - SELECT SteamID32, MAX(Block) AS MaxBlockDist \ - FROM \ - Jumpstats \ - WHERE \ - JumpType = %d AND \ - Mode = %d AND \ - IsBlockJump = %d \ - GROUP BY SteamID32 \ - ) MaxBlock ON \ - j.SteamID32 = MaxBlock.SteamID32 AND \ - j.Block = MaxBlock.MaxBlockDist \ - WHERE \ - j.JumpType = %d AND \ - j.Mode = %d AND \ - j.IsBlockJump = %d \ - GROUP BY j.SteamID32, j.JumpType, j.Mode, j.IsBlockJump \ - ) MaxDist ON \ - j.SteamID32 = MaxDist.SteamID32 AND \ - j.JumpType = MaxDist.JumpType AND \ - j.Mode = MaxDist.Mode AND \ - j.IsBlockJump = MaxDist.IsBlockJump AND \ - j.Distance = MaxDist.BestDistance \ - ORDER BY j.Block DESC, j.Distance DESC \ - LIMIT %d"; - -char sql_jumpstats_getrecord[] = "\ -SELECT JumpID, Distance, Block \ - FROM \ - Jumpstats rec \ - WHERE \ - SteamID32 = %d AND \ - JumpType = %d AND \ - Mode = %d AND \ - IsBlockJump = %d \ - ORDER BY Block DESC, Distance DESC"; - -char sql_jumpstats_getpbs[] = "\ -SELECT b.JumpID, b.JumpType, b.Distance, b.Strafes, b.Sync, b.Pre, b.Max, b.Airtime \ - FROM Jumpstats b \ - INNER JOIN ( \ - SELECT a.SteamID32, a.Mode, a.JumpType, MAX(a.Distance) Distance \ - FROM Jumpstats a \ - WHERE a.SteamID32=%d AND a.Mode=%d AND NOT a.IsBlockJump \ - GROUP BY a.JumpType, a.Mode, a.SteamID32 \ - ) a ON a.JumpType=b.JumpType AND a.Distance=b.Distance \ - WHERE a.SteamID32=b.SteamID32 AND a.Mode=b.Mode AND NOT b.IsBlockJump \ - ORDER BY b.JumpType"; - -char sql_jumpstats_getblockpbs[] = "\ -SELECT c.JumpID, c.JumpType, c.Block, c.Distance, c.Strafes, c.Sync, c.Pre, c.Max, c.Airtime \ - FROM Jumpstats c \ - INNER JOIN ( \ - SELECT a.SteamID32, a.Mode, a.JumpType, a.Block, MAX(b.Distance) Distance \ - FROM Jumpstats b \ - INNER JOIN ( \ - SELECT a.SteamID32, a.Mode, a.JumpType, MAX(a.Block) Block \ - FROM Jumpstats a \ - WHERE a.SteamID32=%d AND a.Mode=%d AND a.IsBlockJump \ - GROUP BY a.JumpType, a.Mode, a.SteamID32 \ - ) a ON a.JumpType=b.JumpType AND a.Block=b.Block \ - WHERE a.SteamID32=b.SteamID32 AND a.Mode=b.Mode AND b.IsBlockJump \ - GROUP BY a.JumpType, a.Mode, a.SteamID32, a.Block \ - ) b ON b.JumpType=c.JumpType AND b.Block=c.Block AND b.Distance=c.Distance \ - WHERE b.SteamID32=c.SteamID32 AND b.Mode=c.Mode AND c.IsBlockJump \ - ORDER BY c.JumpType"; |