public static void IterateDatabase(PVPNetConnection connection) { MySqlConnection conn = null; while (true) { if (_count >= 3000) { System.Environment.Exit(1); } if (conn == null || conn.State != ConnectionState.Open) { conn = new MySqlConnection("SERVER=localhost;UID=nimashog_riot;PASSWORD=uV8vPrqX3VhC5znV;DATABASE=nimashog_riot;Convert Zero Datetime=True;Allow Zero Datetime=True;"); conn.Open(); } Dictionary<string, int> rankDictionary = new Dictionary<string, int> { {"UNRANKED", -1}, {"BRONZE V", 0}, {"BRONZE IV", 1}, {"BRONZE III", 2}, {"BRONZE II", 3}, {"BRONZE I", 4}, {"SILVER V", 5}, {"SILVER IV", 6}, {"SILVER III", 7}, {"SILVER II", 8}, {"SILVER I", 9}, {"GOLD V", 10}, {"GOLD IV", 11}, {"GOLD III", 12}, {"GOLD II", 13}, {"GOLD I", 14}, {"PLATINUM V", 15}, {"PLATINUM IV", 16}, {"PLATINUM III", 17}, {"PLATINUM II", 18}, {"PLATINUM I", 19}, {"DIAMOND V", 20}, {"DIAMOND IV", 21}, {"DIAMOND III", 22}, {"DIAMOND II", 23}, {"DIAMOND I", 24}, {"MASTER I", 25}, {"CHALLENGER I", 26} }; try { MySqlCommand queueCommand = new MySqlCommand { CommandText = "SELECT `id`, `name` " + "FROM `team_information` " + "ORDER BY `dateUpdated` ASC " + "LIMIT 0, 1", Connection = conn }; MySqlDataReader reader = queueCommand.ExecuteReader(); if (reader.Read()) { int id = reader.GetInt32(0); string teamName = reader.GetString(1); reader.Close(); try { TeamDTO teamDto = connection.FindTeamByName(teamName); MySqlCommand teamInfoCommand = new MySqlCommand { CommandText = "REPLACE INTO `team_information` (`id`, `name`, `dateUpdated`, `gameCount`, `winCount`, " + "`lossCount`, `rankNum`, `rank`, `lastMatch`, `leader`, `playerCount`, `player1`, `player2`, " + "`player3`, `player4`, `player5`, `player6`, `player7`, `player8`, `player9`) " + "VALUES (?id, ?name, CURRENT_TIMESTAMP, ?gameCount, ?winCount, ?lossCount, ?rankNum, " + "?rank, ?lastGame, ?leader, ?playerCount, ?player1, ?player2, ?player3, " + "?player4, ?player5, ?player6, ?player7, ?player8, ?player9);", Connection = conn }; teamInfoCommand.Parameters.AddWithValue("?id", id); teamInfoCommand.Parameters.AddWithValue("?name", teamDto.Name); string rank = connection.GetTeamRank(teamDto.Name); teamInfoCommand.Parameters.AddWithValue("?rankNum", rankDictionary[rank]); teamInfoCommand.Parameters.AddWithValue("?rank", rank); teamInfoCommand.Parameters.AddWithValue("?lastGame", connection.GetTeamLastPlayed(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?leader", connection.GetSummonerName(teamDto.Roster.OwnerId)); teamInfoCommand.Parameters.AddWithValue("?playerCount", teamDto.Roster.MemberList.Count); teamInfoCommand.Parameters.AddWithValue("?gameCount", connection.GetTeamGameCount(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?winCount", connection.GetTeamWinCount(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?lossCount", connection.GetTeamLossCount(teamDto.Name)); for (int i = 0; i < 9; i++) { if (teamDto.Roster.MemberList.Count > i) teamInfoCommand.Parameters.AddWithValue("?player" + (i + 1), teamDto.Roster.MemberList[i].PlayerName); else teamInfoCommand.Parameters.AddWithValue("?player" + (i + 1), DBNull.Value); } teamInfoCommand.ExecuteNonQuery(); Console.WriteLine("Updated team " + teamName + "..."); } catch (Exception e) { System.IO.File.AppendAllText(Directory.GetCurrentDirectory() + "/errors.log", DateTime.Now.ToString("MM\\/dd\\/yyyy h\\:mm tt") + ": " + e.ToString() + "\n\n\n"); Console.WriteLine("Deleting team " + teamName + ". Error logged to errors.log.\nTeam information has been added to deleted_team_info database."); MySqlCommand getInfoCommand = new MySqlCommand() { CommandText = "SELECT * FROM `team_information` " + "WHERE `id` = ?id;", Connection = conn }; MySqlCommand insertCommand = new MySqlCommand() { CommandText = "REPLACE INTO `deleted_team_info` (`id`, `name`, `hidden`, " + "`dateUpdated`, `gameCount`, `winCount`, `lossCount`, `rankNum`, " + "`rank`, `lastMatch`, `leader`, `playerCount`, `player1`, " + "`player2`, `player3`, `player4`, `player5`, `player6`, `player7`, " + "`player8`, `player9`) " + "VALUES (?field0, ?field1, ?field2, ?field3, ?field4, " + "?field5, ?field6, ?field7, ?field8, ?field9, ?field10, " + "?field11, ?field12, ?field13, ?field14, ?field15, ?field16, ?field17 " + ", ?field18, ?field19, ?field20);", Connection = conn }; getInfoCommand.Parameters.AddWithValue("?id", id); MySqlDataReader getInfoReader = getInfoCommand.ExecuteReader(); while (getInfoReader.Read()) { for (int i = 0; i < getInfoReader.FieldCount; i++) { object val = getInfoReader.GetValue(i); insertCommand.Parameters.AddWithValue("?field" + i, val); } } getInfoReader.Close(); insertCommand.ExecuteNonQuery(); MySqlCommand deleteCommand = new MySqlCommand() { CommandText = "DELETE FROM `team_information` " + "WHERE `id` = ?id;", Connection = conn }; deleteCommand.Parameters.AddWithValue("?id", id); deleteCommand.ExecuteNonQuery(); } } } catch (MySqlException e) { System.IO.File.WriteAllText(Directory.GetCurrentDirectory() + "/mysqlErrors.log", DateTime.Now.ToString("MM\\/dd\\/yyyy h\\:mm tt") + ":" + e.ToString()); Console.WriteLine("Mysql error occured. Logged to mysqlErrors.log"); } _count++; if (_count%250 == 0) { if (conn.State != ConnectionState.Closed) conn.Close(); conn = null; } } }
public static void CheckDatabase(PVPNetConnection connection) { MySqlConnection conn = null; while (true) { if (_count >= 500) { System.Environment.Exit(0); } try { if (conn == null || conn.State == ConnectionState.Closed) { Console.WriteLine("Attempting to connect to DB..."); conn = new MySqlConnection("SERVER=localhost;UID=nimashog_riot;PASSWORD=uV8vPrqX3VhC5znV;DATABASE=nimashog_riot;Convert Zero Datetime=True;Allow Zero Datetime=True;"); conn.Open(); } MySqlCommand queueCommand = new MySqlCommand { CommandText = "SELECT *" + "FROM `player_queue`" + "WHERE `processed` = '0'" + "ORDER BY RAND()" + "LIMIT 0, 1", Connection = conn }; MySqlDataReader reader = queueCommand.ExecuteReader(); if (reader.Read()) { string summonerName = reader.GetString(0); Console.WriteLine("Checking account: " + summonerName); reader.Close(); PublicSummoner summoner = connection.GetSummonerByName(summonerName); if (summoner != null) { SummonerLeaguesDTO summonerLeaguesDto = connection.GetAllLeaguesForPlayer(summoner.SummonerId); List<string> teams = new List<string>(); foreach (TeamDTO teamDto in summonerLeaguesDto.SummonerLeagues.Select(summonerLeague => connection.FindTeamByName(summonerLeague.RequestorsName)).Where(teamDto => (teamDto != null && teamDto.Name != null))) { teams.Add(teamDto.Name); MySqlCommand teamInfoCommand = new MySqlCommand { CommandText = "INSERT IGNORE INTO `team_information` (`name`, `dateUpdated`, `gameCount`, `winCount`, " + "`lossCount`, `rankNum`, `rank`, `lastMatch`, `leader`, `playerCount`, `player1`, `player2`, " + "`player3`, `player4`, `player5`, `player6`, `player7`, `player8`, `player9`) " + "VALUES (?name, CURRENT_TIMESTAMP, ?gameCount, ?winCount, ?lossCount, ?rankNum, " + "?rank, ?lastGame, ?leader, ?playerCount, ?player1, ?player2, ?player3, " + "?player4, ?player5, ?player6, ?player7, ?player8, ?player9);", Connection = conn }; teamInfoCommand.Parameters.AddWithValue("?name", teamDto.Name); teamInfoCommand.Parameters.AddWithValue("?gameCount", connection.GetTeamGameCount(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?winCount", connection.GetTeamWinCount(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?lossCount", connection.GetTeamLossCount(teamDto.Name)); string rank = connection.GetTeamRank(teamDto.Name); teamInfoCommand.Parameters.AddWithValue("?rankNum", _rankDictionary[rank]); teamInfoCommand.Parameters.AddWithValue("?rank", rank); teamInfoCommand.Parameters.AddWithValue("?lastGame", connection.GetTeamLastPlayed(teamDto.Name)); teamInfoCommand.Parameters.AddWithValue("?leader", connection.GetSummonerName(teamDto.Roster.OwnerId)); teamInfoCommand.Parameters.AddWithValue("?playerCount", teamDto.Roster.MemberList.Count); for (int i = 0; i < 9; i++) { if (teamDto.Roster.MemberList.Count > i) teamInfoCommand.Parameters.AddWithValue("?player" + (i + 1), teamDto.Roster.MemberList[i].PlayerName); else teamInfoCommand.Parameters.AddWithValue("?player" + (i + 1), DBNull.Value); } teamInfoCommand.ExecuteNonQuery(); foreach (TeamMemberInfoDTO memberInfoDto in teamDto.Roster.MemberList) { MySqlCommand addToQueueCommand = new MySqlCommand { CommandText = "INSERT IGNORE INTO `player_queue` (`name`, `processed`) " + "VALUES (?name, '0');", Connection = conn }; addToQueueCommand.Parameters.AddWithValue("?name", memberInfoDto.PlayerName); addToQueueCommand.ExecuteNonQuery(); } } MySqlCommand playerInfoCommand = new MySqlCommand { CommandText = "INSERT IGNORE INTO `player_information` (`name`, `dateUpdated`, `rankNum`, `rank`, `lastMatch`, `team1`, `team2`, `team3`, `team4`, `team5`) " + "VALUES (?name, CURRENT_TIMESTAMP, ?rankNum, ?rank, ?lastGame, ?team1, ?team2, ?team3, ?team4, ?team5);", Connection = conn }; playerInfoCommand.Parameters.AddWithValue("?name", summoner.Name); string playerRank = connection.GetSummonerSoloQueueRank(summoner.SummonerId); playerInfoCommand.Parameters.AddWithValue("?rankNum", _rankDictionary[playerRank]); playerInfoCommand.Parameters.AddWithValue("?rank", playerRank); playerInfoCommand.Parameters.AddWithValue("?lastGame", connection.GetSummonerLastPlayed(summoner.SummonerId)); for (int i = 0; i < 5; i++) { if (teams.Count > i) playerInfoCommand.Parameters.AddWithValue("?team" + (i + 1), teams[i]); else playerInfoCommand.Parameters.AddWithValue("?team" + (i + 1), DBNull.Value); } playerInfoCommand.ExecuteNonQuery(); RecentGames recentGames = connection.GetRecentGames(summoner.AcctId); if (recentGames != null) { foreach (PlayerGameStats playerGameStats in recentGames.GameStatistics) { double[] ids = new double[playerGameStats.FellowPlayers.Count]; for (int i = 0; i < playerGameStats.FellowPlayers.Count; i++) { ids[i] = playerGameStats.FellowPlayers[i].SummonerId; } string[] names = connection.GetSummonerNames(ids); foreach (string name in names) { MySqlCommand addToQueueCommand = new MySqlCommand { CommandText = "INSERT IGNORE INTO `player_queue` (`name`, `processed`) " + "VALUES (?name, '0');", Connection = conn }; addToQueueCommand.Parameters.AddWithValue("?name", name); addToQueueCommand.ExecuteNonQuery(); } } } MySqlCommand queueCheckCommand = new MySqlCommand { CommandText = "UPDATE `player_queue` SET `processed` = '1' WHERE `player_queue`.`name` = ?name", Connection = conn }; queueCheckCommand.Parameters.AddWithValue("?name", summoner.Name); queueCheckCommand.ExecuteNonQuery(); } } _count++; if (_count%50 == 0) { conn.Close(); conn = null; } } catch (Exception ex) { Console.WriteLine(ex); Thread.Sleep(TimeSpan.FromSeconds(10)); } } }