public void addGameStats(int rGameId, int summonerId, PlayerGameStats pStats, GameResult stats) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT INTO gameStats SET " + "gameId = \"" + getGameId(rGameId) + "\", " + "playerId = \"" + getPlayerId(summonerId) + "\", " + "championId = \"" + pStats.championId + "\", " + "skinId = \"" + pStats.skinIndex + "\", " + "spell1Id = \"" + pStats.spell1 + "\", " + "spell2Id = \"" + pStats.spell2 + "\", " + "serverPing = \"" + pStats.userServerPing + "\", " + "kills = \"" + stats.Kills + "\", " + "deaths = \"" + stats.Deaths + "\", " + "assists = \"" + stats.Assists + "\", " + "level = \"" + stats.Level + "\", " + "minionsKilled = \"" + stats.MinionsKilled + "\", " + "neutralMinionsKilled = \"" + stats.NeutralMinionsKilled + "\", " + "goldEarned = \"" + stats.GoldEarned + "\", " + "magicDamageDealt = \"" + stats.MagicalDamageDealt + "\", " + "physicalDamageDealt = \"" + stats.PhysicalDamageDealt + "\", " + "totalDamageDealt = \"" + stats.TotalDamageDealt + "\", " + "magicDamageTaken = \"" + stats.MagicalDamageTaken + "\", " + "physicalDamageTaken = \"" + stats.PhysicalDamageTaken + "\", " + "totalDamageTaken = \"" + stats.TotalDamageTaken + "\", " + "totalHealingDone = \"" + stats.TotalHealingDone + "\", " + "largestMultiKill = \"" + stats.LargestMultiKill + "\", " + "largestKillingSpree = \"" + stats.LargestKillingSpree + "\", " + "timeSpentDead = \"" + stats.TimeSpentDead + "\", " + "turretsDestroyed = \"" + stats.TurretsDestroyed + "\", " + "inhibitorsDestroyed = \"" + stats.InhibitorsDestroyed + "\", " + "item0 = \"" + stats.Items[0] + "\", " + "item1 = \"" + stats.Items[1] + "\", " + "item2 = \"" + stats.Items[2] + "\", " + "item3 = \"" + stats.Items[3] + "\", " + "item4 = \"" + stats.Items[4] + "\", " + "item5 = \"" + stats.Items[5] + "\""; cmd.ExecuteNonQuery(); }
void UpdateSummonerGame(SummonerDescription summoner, PlayerGameStats game) { //Don't store tutorial games if (game.gameMode == "TUTORIAL" || game.gameType == "TUTORIAL_GAME") return; const int blueId = 100; //const int purpleId = 200; bool isBlueTeam = game.teamId == blueId; int gameId; int summonerTeamId; GameResult gameResult = new GameResult(game); //At first we must determine if the game is already in the database DatabaseCommand check = Command("select id, blue_team_id, purple_team_id, purple_team_won from game_result where game_result.game_id = :game_id"); check.Set("game_id", game.gameId); using (var reader = check.ExecuteReader()) { if (reader.Read()) { //The game is already in the database gameId = reader.Integer(); int purpleTeamId = reader.Integer(); int blueTeamId = reader.Integer(); bool PurpleTeamWon = reader.Boolean(); if (isBlueTeam) summonerTeamId = blueTeamId; else summonerTeamId = purpleTeamId; //Check if the game result for this player has already been stored DatabaseCommand gameCheck = Command("select count(*) from player where (team_id = :blue_team_id or team_id = :purple_team_id) and summoner_id = :summoner_id"); gameCheck.Set("blue_team_id", blueTeamId); gameCheck.Set("purple_team_id", purpleTeamId); gameCheck.Set("summoner_id", summoner.Id); long count = (long)gameCheck.ExecuteScalar(); if (count > 0) { //The result of this game for this player has already been stored in the database, there is no work to be done return; } //The game is already stored in the database but the results of this player were previously unknown //This means that this player must be removed from the list of unknown players for this game DatabaseCommand delete = Command("delete from missing_team_player where team_id = :team_id and account_id = :account_id"); delete.Set("team_id", summonerTeamId); delete.Set("account_id", summoner.AccountId); delete.Execute(); } else { //The game is not in the database yet //Need to create the team entries first DatabaseCommand newTeam = Command("insert into team default values"); newTeam.Execute(); int blueTeamId = GetInsertId("team"); newTeam.Execute(); int purpleTeamId = GetInsertId("team"); summonerTeamId = isBlueTeam ? blueTeamId : purpleTeamId; int team2Id = GetInsertId("team"); string mapEnum; string gameModeEnum; switch (game.gameMapId) { //Autumn case 1: //No idea what 2 means case 2: //Not sure either, encountered this in some games from 4 months ago on an inactive account case 3: //Winter case 6: mapEnum = "summoners_rift"; break; case 4: mapEnum = "twisted_treeline"; break; case 8: mapEnum = "dominion"; break; default: throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.Name, game.gameMapId)); } if (game.gameType == "PRACTICE_GAME") gameModeEnum = "custom"; else { switch (game.queueType) { case "RANKED_TEAM_3x3": case "RANKED_TEAM_5x5": case "RANKED_PREMADE_3x3": case "RANKED_PREMADE_5x5": gameModeEnum = "premade"; break; case "NORMAL": gameModeEnum = "normal"; break; case "ODIN_UNRANKED": gameModeEnum = "normal"; break; case "RANKED_SOLO_5x5": gameModeEnum = "solo"; break; case "BOT": gameModeEnum = "bot"; break; default: throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.Name, game.queueType)); } } List<string> fields = new List<string>() { "game_id", "map", "game_mode", "game_time", "blue_team_id", "purple_team_id", "blue_team_won", }; DatabaseCommand newGame = Command("insert into game_result ({0}) values ({1})", GetGroupString(fields), GetPlaceholderString(fields)); newGame.SetFieldNames(fields); newGame.Set(game.gameId); newGame.Set(mapEnum); newGame.Set(gameModeEnum); newGame.Set(GetTimestamp(game.createDate)); newGame.Set(blueTeamId); newGame.Set(purpleTeamId); newGame.Set(gameResult.Win && isBlueTeam); newGame.Execute(); gameId = GetInsertId("game_result"); //We need to create a list of unknown players for this game so they can get updated in future if necessary //Otherwise it is unclear who participated in this game //Retrieving their stats at this point is too expensive and hence undesirable foreach (var player in game.fellowPlayers) { DatabaseCommand missingPlayer = Command("insert into missing_team_player (team_id, champion_id, account_id) values (:team_id, :champion_id, :account_id)"); missingPlayer.Set("team_id", player.teamId == blueId ? blueTeamId : purpleTeamId); missingPlayer.Set("champion_id", player.championId); //It's called summonerId but it's really the account ID (I think) missingPlayer.Set("account_id", player.summonerId); missingPlayer.Execute(); } } } InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult); }
void UpdateSummonerGame(Summoner summoner, PlayerGameStats game, DbConnection connection) { //Don't store tutorial games if (game.gameMode == "TUTORIAL" || game.gameType == "TUTORIAL_GAME") return; const int blueId = 100; //const int purpleId = 200; bool isBlueTeam = game.teamId == blueId; int gameId; int summonerTeamId; GameResult gameResult = new GameResult(game); //At first we must determine if the game is already in the database using (var check = Command("select id, blue_team_id, purple_team_id from game where game.game_id = :game_id", connection)) { check.Set("game_id", game.gameId); using (var reader = check.ExecuteReader()) { if (reader.Read()) { //The game is already in the database gameId = reader.Integer(); int blueTeamId = reader.Integer(); int purpleTeamId = reader.Integer(); reader.Close(); if (isBlueTeam) summonerTeamId = blueTeamId; else summonerTeamId = purpleTeamId; //Check if the game result for this player has already been stored using (var gameCheck = Command("select count(*) from player where (team_id = :blue_team_id or team_id = :purple_team_id) and summoner_id = :summoner_id", connection)) { gameCheck.Set("blue_team_id", blueTeamId); gameCheck.Set("purple_team_id", purpleTeamId); gameCheck.Set("summoner_id", summoner.Id); long count = (long)gameCheck.ExecuteScalar(); if (count > 0) { //The result of this game for this player has already been stored in the database, there is no work to be done return; } } //The game is already stored in the database but the results of this player were previously unknown //This means that this player must be removed from the list of unknown players for this game using (var delete = Command("delete from unknown_player where team_id = :team_id and summoner_id = :summoner_id", connection)) { delete.Set("team_id", summonerTeamId); delete.Set("summoner_id", summoner.SummonerId); delete.Execute(); } } else { reader.Close(); //The game is not in the database yet //Need to create the team entries first using (var newTeam = Command("insert into team default values", connection)) { newTeam.Execute(); int blueTeamId = GetInsertId("team", connection); newTeam.Execute(); int purpleTeamId = GetInsertId("team", connection); summonerTeamId = isBlueTeam ? blueTeamId : purpleTeamId; MapType map; GameModeType gameMode; switch (game.gameMapId) { //Autumn case 1: //No idea what 2 means case 2: //Not sure either, encountered this in some games from 4 months ago on an inactive account case 3: //Winter case 6: map = MapType.SummonersRift; break; case 4: map = MapType.TwistedTreeline; break; case 7: map = MapType.ProvingGrounds; break; case 8: map = MapType.Dominion; break; // Apparently this is from the Twisted Treeline remake case 10: map = MapType.TwistedTreeline; break; case 12: map = MapType.HowlingAbyss; break; default: throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.SummonerName, game.gameMapId)); } if (game.gameType == "PRACTICE_GAME" || game.gameType == "CUSTOM_GAME") gameMode = GameModeType.Custom; else { switch (game.queueType) { case "RANKED_TEAM_3x3": case "RANKED_TEAM_5x5": case "RANKED_PREMADE_3x3": case "RANKED_PREMADE_5x5": gameMode = GameModeType.Premade; break; case "NORMAL": case "NORMAL_3x3": case "ODIN_UNRANKED": case "ARAM_UNRANKED_5x5": gameMode = GameModeType.Normal; break; case "RANKED_SOLO_5x5": gameMode = GameModeType.Solo; break; case "BOT": gameMode = GameModeType.Bot; break; case "BOT_3x3": gameMode = GameModeType.Bot; break; default: throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.SummonerName, game.queueType)); } } using (var newGame = Command("insert into game ({0}) values ({1})", connection, GetGroupString(NewGameFields), GetPlaceholderString(NewGameFields))) { newGame.SetFieldNames(NewGameFields); newGame.Set(game.gameId); newGame.Set(map); newGame.Set(gameMode); newGame.Set(game.createDate.ToUnixTime()); newGame.Set(blueTeamId); newGame.Set(purpleTeamId); newGame.Set(gameResult.Win == isBlueTeam); newGame.Execute(); gameId = GetInsertId("game", connection); //We need to create a list of unknown players for this game so they can get updated in future if necessary //Otherwise it is unclear who participated in this game //Retrieving their stats at this point is too expensive and hence undesirable foreach (var player in game.fellowPlayers) { using (var missingPlayer = Command("insert into unknown_player (team_id, champion_id, summoner_id) values (:team_id, :champion_id, :summoner_id)", connection)) { missingPlayer.Set("team_id", player.teamId == blueId ? blueTeamId : purpleTeamId); missingPlayer.Set("champion_id", player.championId); missingPlayer.Set("summoner_id", player.summonerId); missingPlayer.Execute(); } } } } } } } InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult, connection); }
void AnalyseRecentGames(List<string> arguments) { string summonerName = GetNameFromArguments(arguments); PublicSummoner publicSummoner = new PublicSummoner(); List<PlayerGameStats> recentGames = new List<PlayerGameStats>(); bool foundSummoner = GetRecentGames(summonerName, ref publicSummoner, ref recentGames); if (!foundSummoner) { NoSuchSummoner(); return; } foreach (var stats in recentGames) { GameResult result = new GameResult(stats); Console.Write("[{0}] [{1}] [{2}] ", stats.gameId, stats.createDate, result.Win ? "W" : "L"); if (stats.ranked) Console.Write("Ranked "); if (stats.gameType == "PRACTICE_GAME") { Console.Write("Custom "); switch (stats.gameMode) { case "CLASSIC": Console.Write("Summoner's Rift"); break; case "ODIN": Console.Write("Dominion"); break; default: Console.Write(stats.gameMode); break; } } else { switch (stats.queueType) { case "RANKED_TEAM_3x3": Output.Write("Twisted Treeline"); break; case "NORMAL": case "RANKED_SOLO_5x5": Console.Write("Summoner's Rift"); break; case "RANKED_TEAM_5x5": Console.Write("Summoner's Rift (team)"); break; case "ODIN_UNRANKED": Console.Write("Dominion"); break; case "BOT": Console.Write("Co-op vs. AI"); break; default: Console.Write(stats.queueType); break; } } Console.WriteLine(", {0}, {1}/{2}/{3}", GetChampionName(stats.championId), result.Kills, result.Deaths, result.Assists); List<string> units = new List<string>(); if (stats.adjustedRating != 0) units.Add(string.Format("Rating: {0} ({1})", stats.rating + stats.eloChange, SignPrefix(stats.eloChange))); if (stats.adjustedRating != 0) units.Add(string.Format("adjusted {0}", stats.adjustedRating)); if (stats.teamRating != 0) units.Add(string.Format("team {0} ({1})", stats.teamRating, SignPrefix(stats.teamRating - stats.rating))); PrintUnits(units); if (stats.predictedWinPct != 0.0) units.Add(string.Format("Predicted winning percentage {0}", Percentage(stats.predictedWinPct))); if (stats.premadeSize > 1) units.Add(string.Format("Queued with {0}", stats.premadeSize)); if (stats.leaver) units.Add("Left the game"); if (stats.afk) units.Add("AFK"); units.Add(string.Format("{0} ms ping", stats.userServerPing)); units.Add(string.Format("{0} s spent in queue", stats.timeInQueue)); PrintUnits(units); } }
void InsertGameResult(SummonerDescription summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult) { List<string> fields = new List<string>() { "game_id", "team_id", "summoner_id", "won", "ping", "time_spent_in_queue", "premade_size", "k_coefficient", "probability_of_winning", "rating", "rating_change", "adjusted_rating", "team_rating", "experience_earned", "boosted_experience_earned", "ip_earned", "boosted_ip_earned", "summoner_level", "summoner_spell1", "summoner_spell2", "champion_id", "skin_name", "skin_index", "champion_level", //Items are stored as an SQL array "items", "kills", "deaths", "assists", "minion_kills", "gold", "damage_dealt", "physical_damage_dealt", "magical_damage_dealt", "damage_taken", "physical_damage_taken", "magical_damage_taken", "total_healing_done", "time_spent_dead", "largest_multikill", "largest_killing_spree", "largest_critical_strike", //Summoner's Rift/Twisted Treeline "neutral_minions_killed", "turrets_destroyed", "inhibitors_destroyed", //Dominion "nodes_neutralised", "node_neutralisation_assists", "nodes_captured", "victory_points", "objectives", "total_score", "objective_score", "combat_score", "rank", }; string queryFields = GetGroupString(fields); string queryValues = GetPlaceholderString(fields); SQLCommand insert = Command("insert into team_player ({0}) values ({1})", queryFields, queryValues); insert.SetFieldNames(fields); insert.Set(gameId); insert.Set(teamId); insert.Set(summoner.Id); insert.Set(gameResult.Win); insert.Set(game.userServerPing); insert.Set(game.timeInQueue); insert.Set(game.premadeSize); insert.Set(game.KCoefficient); insert.Set(game.predictedWinPct); insert.Set(game.rating); insert.Set(game.eloChange); insert.Set(game.adjustedRating); insert.Set(game.teamRating); insert.Set(game.experienceEarned); insert.Set(game.boostXpEarned); insert.Set(game.ipEarned); insert.Set(game.boostIpEarned); insert.Set(game.level); insert.Set(game.spell1); insert.Set(game.spell2); insert.Set(game.championId); insert.Set(game.skinName); insert.Set(game.skinIndex); insert.Set(gameResult.Level); //Items require special treatment insert.Set(gameResult.Items); insert.Set(gameResult.Kills); insert.Set(gameResult.Deaths); insert.Set(gameResult.Assists); insert.Set(gameResult.MinionsKilled); insert.Set(gameResult.GoldEarned); insert.Set(gameResult.TotalDamageDealt); insert.Set(gameResult.PhysicalDamageDealt); insert.Set(gameResult.MagicalDamageDealt); insert.Set(gameResult.TotalDamageTaken); insert.Set(gameResult.PhysicalDamageTaken); insert.Set(gameResult.MagicalDamageTaken); insert.Set(gameResult.TotalHealingDone); insert.Set(gameResult.TimeSpentDead); insert.Set(gameResult.LargestMultiKill); insert.Set(gameResult.LargestKillingSpree); insert.Set(gameResult.LargestCriticalStrike); //Summoner's Rift/Twisted Treeline insert.Set(gameResult.NeutralMinionsKilled); insert.Set(gameResult.TurretsDestroyed); insert.Set(gameResult.InhibitorsDestroyed); //Dominion insert.Set(gameResult.NodesNeutralised); insert.Set(gameResult.NodeNeutralisationAssists); insert.Set(gameResult.NodesCaptured); insert.Set(gameResult.VictoryPoints); insert.Set(gameResult.Objectives); insert.Set(gameResult.TotalScore); insert.Set(gameResult.ObjectiveScore); insert.Set(gameResult.CombatScore); insert.Set(gameResult.Rank); insert.Execute(); }
void AnalyseEnvironmentalRating(List<string> arguments, bool ranked, string season) { if (arguments.Count == 0) return; string summonerName = GetSummonerName(arguments[0]); var excludedNames = new List<string>(); for (int i = 1; i < arguments.Count; i++) excludedNames.Add(GetSummonerName(arguments[i])); PublicSummoner publicSummoner = new PublicSummoner(); List<PlayerGameStats> recentGames = new List<PlayerGameStats>(); bool foundSummoner = GetRecentGames(summonerName, ref publicSummoner, ref recentGames); if (!foundSummoner) { NoSuchSummoner(); return; } var knownSummoners = new HashSet<string>(); var currentRatings = new List<int>(); var topRatings = new List<int>(); int gameCount = 0; foreach (var stats in recentGames) { GameResult result = new GameResult(stats); if ( (stats.gameType == "PRACTICE_GAME") || (!ranked && stats.queueType != "NORMAL") || (ranked && stats.queueType != "RANKED_SOLO_5x5") ) continue; var ids = new List<int>(); foreach (var fellowPlayer in stats.fellowPlayers) ids.Add(fellowPlayer.summonerId); var names = RPC.GetSummonerNames(ids); bool isValidGame = true; foreach (var name in excludedNames) { if (names.IndexOf(name) >= 0) { isValidGame = false; break; } } if (!isValidGame) continue; gameCount++; foreach (var name in names) { if (knownSummoners.Contains(name)) continue; knownSummoners.Add(name); PublicSummoner summoner = RPC.GetSummonerByName(name); if (summoner == null) { Console.WriteLine("Unable to load summoner {0}", name); return; } PlayerLifeTimeStats lifeTimeStatistics = RPC.RetrievePlayerStatsByAccountID(summoner.acctId, season); if (lifeTimeStatistics == null) { Console.WriteLine("Unable to retrieve lifetime statistics for summoner {0}", name); return; } List<PlayerStatSummary> summaries = lifeTimeStatistics.playerStatSummaries.playerStatSummarySet; const string target = "RankedSolo5x5"; foreach (var summary in summaries) { if (summary.playerStatSummaryType == target) { int games = summary.wins + summary.losses; if (games == 0) break; Console.Write("{0}: ", name); if (summary.maxRating >= 2200) Console.ForegroundColor = ConsoleColor.White; else if (summary.maxRating >= 1850) Console.ForegroundColor = ConsoleColor.DarkCyan; else if (summary.maxRating >= 1500) Console.ForegroundColor = ConsoleColor.DarkYellow; else if (summary.maxRating < 1150 && summary.maxRating != 0) Console.ForegroundColor = ConsoleColor.DarkGray; Console.Write("{0} (top {1}), ", summary.rating, summary.maxRating); Console.ResetColor(); Console.Write("{0} W - {1} L ({2})", summary.wins, summary.losses, SignPrefix(summary.wins - summary.losses)); if (summary.leaves > 0) Console.Write(", left {0} {1}", summary.leaves, (summary.leaves > 1 ? "games" : "game")); Console.WriteLine(""); currentRatings.Add(summary.rating); topRatings.Add(summary.maxRating); break; } } } } currentRatings.Sort(); topRatings.Sort(); PrintRatings("Current ratings", currentRatings); PrintRatings("Top ratings", topRatings); int playerCount = 9 * gameCount; int rankedPlayers = topRatings.Count; float rankedRatio = (float)rankedPlayers / playerCount; Console.WriteLine("Ranked players: {0}/{1} ({2:F1}%)", rankedPlayers, playerCount, rankedRatio * 100); }
DatabaseCommand GetCommand(GameResult gameResult, DbConnection connection) { string queryFields = GetGroupString(InsertGameResultFields); string queryValues = GetPlaceholderString(InsertGameResultFields); if (connection.IsMySQL()) { //MySQL doesn't support arrays so we employ separate fields in this case string itemFieldString = string.Join(", ", Player.GetItemFields()); string itemValueString = string.Join(", ", gameResult.Items); return Command("insert into player ({0}, {1}) values ({2}, {3})", connection, queryFields, itemFieldString, queryValues, itemValueString); } else { //This is the code for PostgreSQL and SQLite //Items are an array of integers and require special treatment string itemString = string.Format("'{{{0}}}'", string.Join(", ", gameResult.Items)); return Command("insert into player ({0}, items) values ({1}, {2})", connection, queryFields, queryValues, itemString); } }
void InsertGameResult(Summoner summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult, DbConnection connection) { using (var insert = GetCommand(gameResult, connection)) { insert.SetFieldNames(InsertGameResultFields); insert.Set(gameId); insert.Set(teamId); insert.Set(summoner.Id); insert.Set(game.userServerPing); insert.Set(game.timeInQueue); insert.Set(game.premadeSize); insert.Set(game.experienceEarned); insert.Set(game.boostXpEarned); insert.Set(game.ipEarned); insert.Set(game.boostIpEarned); insert.Set(game.level); insert.Set(game.spell1); insert.Set(game.spell2); insert.Set(game.championId); insert.Set(game.skinName); insert.Set(game.skinIndex); insert.Set(gameResult.Level); insert.Set(gameResult.Kills); insert.Set(gameResult.Deaths); insert.Set(gameResult.Assists); insert.Set(gameResult.MinionsKilled); insert.Set(gameResult.GoldEarned); insert.Set(gameResult.TotalDamageDealt); insert.Set(gameResult.PhysicalDamageDealt); insert.Set(gameResult.MagicalDamageDealt); insert.Set(gameResult.TotalDamageTaken); insert.Set(gameResult.PhysicalDamageTaken); insert.Set(gameResult.MagicalDamageTaken); insert.Set(gameResult.TotalHealingDone); insert.Set(gameResult.TimeSpentDead); insert.Set(gameResult.LargestMultiKill); insert.Set(gameResult.LargestKillingSpree); insert.Set(gameResult.LargestCriticalStrike); //Summoner's Rift/Twisted Treeline insert.Set(gameResult.NeutralMinionsKilled); insert.Set(gameResult.TurretsDestroyed); insert.Set(gameResult.InhibitorsDestroyed); //Dominion insert.Set(gameResult.NodesNeutralised); insert.Set(gameResult.NodeNeutralisationAssists); insert.Set(gameResult.NodesCaptured); insert.Set(gameResult.VictoryPoints); insert.Set(gameResult.Objectives); insert.Set(gameResult.TotalScore); insert.Set(gameResult.ObjectiveScore); insert.Set(gameResult.CombatScore); insert.Set(gameResult.Rank); insert.Execute(); } }
void UpdateSummonerGame(SummonerDescription summoner, PlayerGameStats game) { //Don't store tutorial games if (game.gameMode == "TUTORIAL" || game.gameType == "TUTORIAL_GAME") return; const int blueId = 100; const int purpleId = 200; bool isBlueTeam = game.teamId == blueId; //The update requires a transaction as multiple accounts might be querying data for the same game simultaneously NpgsqlTransaction transaction = Database.BeginTransaction(); int gameId; int summonerTeamId; GameResult gameResult = new GameResult(game); //At first we must determine if the game is already in the database SQLCommand check = Command("select game_result.id, game_result.team1_id, game_result.team2_id, team.is_blue_team from game_result, team where game_result.game_id = :game_id and game_result.team1_id = team.id"); check.Set("game_id", game.gameId); using (var reader = check.ExecuteReader()) { if (reader.Read()) { //The game is already in the database gameId = (int)reader[0]; int team1Id = (int)reader[1]; int team2Id = (int)reader[2]; bool team1IsBlue = (bool)reader[3]; if (isBlueTeam && team1IsBlue) summonerTeamId = team1Id; else summonerTeamId = team2Id; //Check if the game result for this player has already been stored SQLCommand gameCheck = Command("select count(*) from team_player where (team_id = :team1_id or team_id = :team2_id) and summoner_id = :summoner_id"); gameCheck.Set("team1_id", team1Id); gameCheck.Set("team2_id", team2Id); gameCheck.Set("summoner_id", summoner.Id); long count = (long)gameCheck.ExecuteScalar(); if (count > 0) { //The result of this game for this player has already been stored in the database, there is no work to be done transaction.Rollback(); return; } //The game is already stored in the database but the results of this player were previously unknown //This means that this player must be removed from the list of unknown players for this game //I'm too lazy to figure out what team the player belongs to right now so let's just perform two deletions for now, one of which will fail int[] teamIds = { team1Id, team2Id }; foreach (int teamId in teamIds) { SQLCommand delete = Command("delete from missing_team_player where team_id = :team_id and account_id = :account_id"); delete.Set("team_id", teamId); delete.Set("account_id", summoner.AccountId); delete.Execute(); } } else { //The game is not in the database yet //Need to create the team entries first SQLCommand newTeam = Command("insert into team (is_blue_team) values (:is_blue_team)"); newTeam.Set("is_blue_team", NpgsqlDbType.Boolean, isBlueTeam); newTeam.Execute(); int team1Id = GetInsertId("team"); summonerTeamId = team1Id; newTeam.Set("is_blue_team", NpgsqlDbType.Boolean, !isBlueTeam); newTeam.Execute(); int team2Id = GetInsertId("team"); Dictionary<int, int> teamIdDictionary = new Dictionary<int, int>() { {game.teamId, team1Id}, {isBlueTeam ? purpleId : blueId, team2Id}, }; List<string> fields = new List<string>() { "game_id", "result_map", "game_mode", "game_time", "team1_won", "team1_id", "team2_id", }; string mapEnum; string gameModeEnum; switch (game.gameMapId) { //Autumn case 1: //No idea what 2 means case 2: //Not sure either, encountered this in some games from 4 months ago on an inactive account case 3: //Winter case 6: mapEnum = "summoners_rift"; break; case 4: mapEnum = "twisted_treeline"; break; case 8: mapEnum = "dominion"; break; default: throw new Exception(string.Format("Unknown game map ID in the match history of {0}: {1}", summoner.Name, game.gameMapId)); } if (game.gameType == "PRACTICE_GAME") gameModeEnum = "custom"; else { switch (game.queueType) { case "RANKED_TEAM_3x3": case "RANKED_TEAM_5x5": case "RANKED_PREMADE_3x3": case "RANKED_PREMADE_5x5": gameModeEnum = "premade"; break; case "NORMAL": gameModeEnum = "normal"; break; case "ODIN_UNRANKED": gameModeEnum = "normal"; break; case "RANKED_SOLO_5x5": gameModeEnum = "solo"; break; case "BOT": gameModeEnum = "bot"; break; default: { transaction.Rollback(); throw new Exception(string.Format("Unknown queue type in the match history of {0}: {1}", summoner.Name, game.queueType)); } } } string queryFields = GetGroupString(fields); string queryValues = ":game_id, cast(:result_map as map_type), cast(:game_mode as game_mode_type), to_timestamp(:game_time), :team1_won, :team1_id, :team2_id"; SQLCommand newGame = Command("insert into game_result ({0}) values ({1})", queryFields, queryValues); newGame.SetFieldNames(fields); newGame.Set(game.gameId); newGame.Set(mapEnum); newGame.Set(gameModeEnum); newGame.Set(GetTimestamp(game.createDate)); newGame.Set(gameResult.Win); newGame.Set(team1Id); newGame.Set(team2Id); newGame.Execute(); gameId = GetInsertId("game_result"); //We need to create a list of unknown players for this game so they can get updated in future if necessary //Otherwise it is unclear who participated in this game //Retrieving their stats at this point is too expensive and hence undesirable foreach (var player in game.fellowPlayers) { SQLCommand missingPlayer = Command("insert into missing_team_player (team_id, champion_id, account_id) values (:team_id, :champion_id, :account_id)"); missingPlayer.Set("team_id", teamIdDictionary[player.teamId]); missingPlayer.Set("champion_id", player.championId); //It's called summonerId but it's really the account ID (I think) missingPlayer.Set("account_id", player.summonerId); missingPlayer.Execute(); } } } InsertGameResult(summoner, gameId, summonerTeamId, game, gameResult); transaction.Commit(); }
void InsertGameResult(Summoner summoner, int gameId, int teamId, PlayerGameStats game, GameResult gameResult, DbConnection connection) { string queryFields = GetGroupString(InsertGameResultFields); string queryValues = GetPlaceholderString(InsertGameResultFields); using (var insert = Command("insert into player ({0}) values ({1})", connection, queryFields, queryValues)) { insert.SetFieldNames(InsertGameResultFields); insert.Set(gameId); insert.Set(teamId); insert.Set(summoner.Id); insert.Set(game.userServerPing); insert.Set(game.timeInQueue); insert.Set(game.premadeSize); insert.Set(game.experienceEarned); insert.Set(game.boostXpEarned); insert.Set(game.ipEarned); insert.Set(game.boostIpEarned); insert.Set(game.level); insert.Set(game.spell1); insert.Set(game.spell2); insert.Set(game.championId); insert.Set(game.skinName); insert.Set(game.skinIndex); insert.Set(gameResult.Level); //Items are an array of integers and require special treatment string itemString = string.Format("'{{{0}}}'", string.Join(", ", gameResult.Items)); insert.Set(itemString); insert.Set(gameResult.Kills); insert.Set(gameResult.Deaths); insert.Set(gameResult.Assists); insert.Set(gameResult.MinionsKilled); insert.Set(gameResult.GoldEarned); insert.Set(gameResult.TotalDamageDealt); insert.Set(gameResult.PhysicalDamageDealt); insert.Set(gameResult.MagicalDamageDealt); insert.Set(gameResult.TotalDamageTaken); insert.Set(gameResult.PhysicalDamageTaken); insert.Set(gameResult.MagicalDamageTaken); insert.Set(gameResult.TotalHealingDone); insert.Set(gameResult.TimeSpentDead); insert.Set(gameResult.LargestMultiKill); insert.Set(gameResult.LargestKillingSpree); insert.Set(gameResult.LargestCriticalStrike); //Summoner's Rift/Twisted Treeline insert.Set(gameResult.NeutralMinionsKilled); insert.Set(gameResult.TurretsDestroyed); insert.Set(gameResult.InhibitorsDestroyed); //Dominion insert.Set(gameResult.NodesNeutralised); insert.Set(gameResult.NodeNeutralisationAssists); insert.Set(gameResult.NodesCaptured); insert.Set(gameResult.VictoryPoints); insert.Set(gameResult.Objectives); insert.Set(gameResult.TotalScore); insert.Set(gameResult.ObjectiveScore); insert.Set(gameResult.CombatScore); insert.Set(gameResult.Rank); insert.Execute(); } }
public void finishEndGameStats() { PlayerGameStats pStats; PlayerLifeTimeStats lifeStats; GameResult gRes; string summName; ConsoleOut("\tGameUpdate: FIRST Time = " + (getUnixTimestamp() - startTime).ToString()); db.updateEndGame(reportingSummoner.lastGameId, reportingSummoner.accountId); ConsoleOut("\tGameUpdate: Updating end game done, now doing fellow players"); for (int i = 0; i < endSummonerStats.Count; i++) { pStats = endSummonerStats.ElementAt(i).gameStats; gRes = new GameResult(pStats); summName = findSummNameInPublicSumm(endSummonerStats.ElementAt(i).accountId); lifeStats = findLifeStats(endSummonerStats.ElementAt(i).accountId); db.addGameStats(endSummonerStats.ElementAt(i).accountId, pStats, gRes, lifeStats, summName); } db.removeLiveGame(reportingSummoner.lastGameId); ConsoleOut("\tGameUpdate: Finished Updating End of Game Stats ----- Time = " + (getUnixTimestamp() - startTime).ToString()); }