List<TeamExtendedPlayer> GetGameTeam(DbConnection connection, int gameId)
{
var listParticipants = new List<TeamExtendedPlayer>();
using (var select = Command("select {0} from game, player where game.game_id = {1} and player.game_id = game.id order by player.team_id", connection, ExtendedPlayer.GetFields(false), gameId))
using (var reader = select.ExecuteReader())
{
while(reader.Read())
{
var participant = new TeamExtendedPlayer(reader, !(connection.IsMsSQL() || connection.IsMySQL()));
listParticipants.Add(participant);
}
}
foreach(var participant in listParticipants)
{
using (var nameSelect = Command("select TOP 1 summoner_name from summoner where id = {0}", connection, participant.SummonerId))
using (var nameReader = nameSelect.ExecuteReader())
{
participant.SummonerName = nameReader.Read() ? nameReader.String() : "Unknown";
}
}
return listParticipants.Count == 0 ? null : listParticipants;
}
public DatabaseCommand(string query, DbConnection connection, Profiler profiler = null, params object[] arguments)
{
Fields = null;
//CommandProfiler = profiler;
Query = string.Format(query, arguments);
Command = connection.CreateCommand();
if (connection.IsMySQL())
{
ParameterPrefix = MySQLParameterPrefix;
//This could break some things...
Query = Query.Replace(StandardParameterPrefix, MySQLParameterPrefix);
}
else if (connection.IsMsSQL())
{
ParameterPrefix = MsSQLParameterPrefix;
Query = Query.Replace(StandardParameterPrefix, MsSQLParameterPrefix);
}
else
ParameterPrefix = StandardParameterPrefix;
Command.CommandText = Query;
}
void UpdateSummoner(Summoner summoner, ConcurrentRPC concurrentRPC, DbConnection connection)
{
int accountId = summoner.AccountId;
lock (ActiveAccountIds)
{
//Avoid concurrent updates of the same account, it's asking for trouble and is redundant anyways
//We might obtain outdated results in one query but that's a minor issue in comparison to corrupted database results
if (ActiveAccountIds.Contains(accountId))
return;
ActiveAccountIds.Add(accountId);
}
//Use a transaction because we're going to insert a fair amount of data
using (var transaction = connection.BeginTransaction())
{
if (connection.IsMsSQL())
connection = new MsSqlTransactedConnection(connection, transaction);
UpdateSummonerFields(summoner, connection, true);
UpdateRunes(summoner, concurrentRPC.PublicSummonerData, connection);
UpdateSummonerRatings(summoner, concurrentRPC.LifeTimeStatistics, connection);
//A season value of zero indicates the current season only
UpdateSummonerRankedStatistics(summoner, 0, concurrentRPC.AggregatedStatistics, connection);
transaction.Commit();
}
UpdateSummonerGames(summoner, concurrentRPC.RecentGameData, connection);
lock (ActiveAccountIds)
ActiveAccountIds.Remove(accountId);
}
List<AggregatedChampionStatistics> LoadAggregatedChampionStatisticsWithTemporaryView(Summoner summoner, MapType map, GameModeType gameMode, DbConnection connection)
{
string viewName = GetViewName();
try
{
//Create a temporary view with a dynamically generated name to emulate the former CTE
string temporaryString = connection.IsMySQL() || connection.IsMsSQL() ? "" : "temporary ";
string createViewQuery = "create " + temporaryString + "view {0} as select game.map, game.game_mode, game.blue_team_id, game.purple_team_id, game.blue_team_won, player.team_id, player.summoner_id, player.champion_id, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game, player where game.blue_team_id = player.team_id or game.purple_team_id = player.team_id";
using (var createView = Command(createViewQuery, connection, viewName))
{
createView.Execute();
string commonWhereClause = string.Format("{0}.summoner_id = :summoner_id and {0}.map = :map and {0}.game_mode = :game_mode", viewName);
string selectQuery =
"select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " +
"(select {0}.champion_id, sum({0}.kills) as kills, sum({0}.deaths) as deaths, sum({0}.assists) as assists, sum({0}.gold) as gold, sum({0}.minion_kills) as minion_kills from {0} where {1} group by {0}.champion_id) " +
"as statistics " +
"left outer join " +
"(select champion_id, count(*) as wins from {0} where {1} and ((blue_team_won = 1 and blue_team_id = team_id) or (blue_team_won = 0 and purple_team_id = team_id)) group by champion_id) " +
"as champion_wins " +
"on statistics.champion_id = champion_wins.champion_id " +
"left outer join " +
"(select champion_id, count(*) as losses from {0} where {1} and ((blue_team_won = 0 and blue_team_id = team_id) or (blue_team_won = 1 and purple_team_id = team_id)) group by champion_id) " +
"as champion_losses " +
"on statistics.champion_id = champion_losses.champion_id";
if (connection.IsMsSQL())
selectQuery = selectQuery.Replace("statistics", "stats"); // statistics is a keyword in mssql :/
using (var select = Command(selectQuery, connection, viewName, commonWhereClause))
{
select.Set("map", map);
select.Set("game_mode", gameMode);
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>();
while (reader.Read())
{
AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader);
output.Add(statistics);
}
reader.Close();
using (var dropView = Command("drop view {0}", connection, viewName))
dropView.Execute();
return output;
}
}
}
}
finally
{
ReleaseViewName(viewName);
}
}
List<ExtendedPlayer> GetSummonerGames(Summoner summoner, DbConnection connection)
{
bool useItemArray = (connection.IsMySQL() || connection.IsMsSQL()) == false;
List<ExtendedPlayer> output = new List<ExtendedPlayer>();
using (var select = Command("select {0} from game, player where game.id = player.game_id and player.summoner_id = :summoner_id order by game.time desc", connection, ExtendedPlayer.GetFields(useItemArray)))
{
select.Set("summoner_id", summoner.Id);
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
ExtendedPlayer player = new ExtendedPlayer(reader, useItemArray);
output.Add(player);
}
}
}
foreach(var player in output)
{
using (var select = Command("select champion_id, team_id from player where game_id = ( select TOP 1 id from game where game_id = :game_id )", connection))
{
select.Set("game_id", player.InternalGameId);
using (var reader = select.ExecuteReader())
{
int cnt = 0;
while (reader.Read())
{
long teamChampions = ((long)reader.Integer() << 32) | (reader.Integer());
player.TeamChampions[cnt++] = teamChampions;
}
}
}
}
return output;
}
DatabaseCommand GetCommand(GameResult gameResult, DbConnection connection)
{
string queryFields = GetGroupString(InsertGameResultFields);
string queryValues = GetPlaceholderString(InsertGameResultFields);
if (connection.IsMySQL() || connection.IsMsSQL())
{
//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);
}
}
