void CopyTable(DbConnection connection, string tableName, string createTableQuery)
{
//Rename the old table to a temporary name
using (var renameTable = new DatabaseCommand(string.Format("alter table {0} rename to broken_{0}", tableName), connection))
renameTable.Execute();
//Remove indices
var indexNames = GetIndexNames(createTableQuery);
foreach (var index in indexNames)
{
try
{
using (var dropIndex = new DatabaseCommand(string.Format("drop index {0}", index), connection))
dropIndex.Execute();
}
catch (Exception exception)
{
WriteLine("Warning - failed to remove index {0} while performing an upgrade: {1}", index, exception.Message);
}
}
//Create the new table
using (var createTable = new DatabaseCommand(createTableQuery, connection))
createTable.Execute();
//Insert the data from the old table into the new table
string tableFields = GetTableFieldsFromCreateTableQuery(createTableQuery);
string fieldString = tableFields.Replace("\n", " ");
using (var insert = new DatabaseCommand(string.Format("insert into {0} ({1}) select {1} from broken_{0}", tableName, fieldString), connection))
insert.Execute();
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, null);
command.Set("top_rating", DbType.Int32, null);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
{
command.Set("current_rating", DbType.Int32, null);
}
else
{
command.Set("current_rating", summary.rating);
}
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, null);
command.Set("top_rating", DbType.Int32, null);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
command.Set("current_rating", DbType.Int32, null);
else
command.Set("current_rating", summary.rating);
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
}
void CopyTable(DbConnection connection, string tableName, string createTableQuery)
{
//Rename the old table to a temporary name
using (var renameTable = new DatabaseCommand(string.Format("alter table {0} rename to broken_{0}", tableName), connection))
renameTable.Execute();
//Remove indices
var indexNames = GetIndexNames(createTableQuery);
foreach (var index in indexNames)
{
try
{
using (var dropIndex = new DatabaseCommand(string.Format("drop index {0}", index), connection))
dropIndex.Execute();
}
catch (Exception exception)
{
WriteLine("Warning - failed to remove index {0} while performing an upgrade: {1}", index, exception.Message);
}
}
//Create the new table
using (var createTable = new DatabaseCommand(createTableQuery, connection))
createTable.Execute();
//Insert the data from the old table into the new table
string tableFields = GetTableFieldsFromCreateTableQuery(createTableQuery);
string fieldString = tableFields.Replace("\n", " ");
using (var insert = new DatabaseCommand(string.Format("insert into {0} ({1}) select {1} from broken_{0}", tableName, fieldString), connection))
insert.Execute();
}
void UnknownPlayerUpgrade(DbConnection connection)
{
//This is required for the r248 fix in the unknown_player table
//The field "account_id" had to be renamed to "summoner_id" in that version
//First check if this database is actually affected by that bug
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(unknown_player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name == "account_id")
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-r248 unknown_player bug. Attempting to upgrade it.");
CopyTableTransaction(connection, "unknown_player", Properties.Resources.CreateTableUnknownPlayer);
}
}
void GameIdUpgrade(DbConnection connection)
{
//player.game_id was nullable prior to r348, should have been not null all along
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name != "game_id")
{
continue;
}
object notNullObject = reader.Get("notnull");
bool isNotNull = (int)(long)notNullObject == 1;
if (!isNotNull)
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-348 player.game_id bug. Attempting to upgrade it.");
CopyTableTransaction(connection, "player", Properties.Resources.CreateTablePlayer);
}
}
void InitialiseSummonerCache()
{
SummonerCache = new Dictionary <RegionType, Dictionary <int, Summoner> >();
foreach (RegionType regionType in Enum.GetValues(typeof(RegionType)))
{
SummonerCache[regionType] = new Dictionary <int, Summoner>();
}
using (var connection = Provider.GetConnection())
{
using (var select = new DatabaseCommand("select {0} from summoner", connection, null, Summoner.GetFields()))
{
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
Summoner summoner = new Summoner(reader);
SummonerCache[summoner.Region][summoner.AccountId] = summoner;
}
}
}
}
}
public DbConnection GetConnection()
{
DbConnection connection = Factory.CreateConnection();
connection.ConnectionString = string.Format("Data Source = {0}", Path);
connection.Open();
//Turn on SQLite foreign keys
using (var pragma = new DatabaseCommand("pragma foreign_keys = on", connection))
{
pragma.Execute();
}
return connection;
}
//This function is required to upgrade old database formats to newer ones
void UpgradeDatabase()
{
using (var connection = Provider.GetConnection())
{
//This is required for the r248 fix in the unknown_player table
//The field "account_id" had to be renamed to "summoner_id" in that version
//First check if this database is actually affected by that bug
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(unknown_player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name == "account_id")
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
const string createTableQuery = "create table unknown_player(team_id integer not null, champion_id integer not null, summoner_id integer not null, foreign key (team_id) references team(id))";
WriteLine("This database is affected by the pre-r248 unknown_player bug. Attempting to upgrade it.");
using (var transaction = connection.BeginTransaction())
{
//Rename the old table to a temporary name
using (var renameTable = new DatabaseCommand("alter table unknown_player rename to broken_unknown_player", connection))
renameTable.Execute();
//Create the new table
using (var createTable = new DatabaseCommand(createTableQuery, connection))
createTable.Execute();
//Insert the data from the old table into the new table
using (var insert = new DatabaseCommand("insert into unknown_player (team_id, champion_id, summoner_id) select team_id, champion_id, account_id from broken_unknown_player", connection))
insert.Execute();
//Remove the old table
using (var dropTable = new DatabaseCommand("drop table broken_unknown_player", connection))
dropTable.Execute();
//Commit the transaction
transaction.Commit();
//Vacuum
using (var vacuum = new DatabaseCommand("vacuum", connection))
vacuum.Execute();
}
WriteLine("Upgrade succeeded.");
}
}
}
void GameIdUpgrade(DbConnection connection)
{
//player.game_id was nullable prior to r348, should have been not null all along
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name != "game_id")
continue;
object notNullObject = reader.Get("notnull");
bool isNotNull = (int)(long)notNullObject == 1;
if (!isNotNull)
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-348 player.game_id bug. Attempting to upgrade it.");
using (var transaction = connection.BeginTransaction())
{
//Rename the old table to a temporary name
using (var renameTable = new DatabaseCommand("alter table player rename to broken_player", connection))
renameTable.Execute();
//Create the new table
using (var createTable = new DatabaseCommand(Properties.Resources.CreateTablePlayer, connection))
createTable.Execute();
//Insert the data from the old table into the new table
string fieldString = Properties.Resources.PlayerFields.Replace("\n", " ");
using (var insert = new DatabaseCommand(string.Format("insert into player ({0}) select {0} from broken_player", fieldString), connection))
insert.Execute();
//Remove the old table
using (var dropTable = new DatabaseCommand("drop table broken_player", connection))
dropTable.Execute();
//Commit the transaction
transaction.Commit();
//Vacuum
using (var vacuum = new DatabaseCommand("vacuum", connection))
vacuum.Execute();
}
WriteLine("Upgrade succeeded.");
}
}
void SetSummaryParameters(DatabaseCommand command, MapType map, GameModeType gameMode, Summoner summoner, PlayerStatSummary summary, bool forceNullRating)
{
if (forceNullRating)
{
command.Set("current_rating", DbType.Int32, DBNull.Value);
command.Set("top_rating", DbType.Int32, DBNull.Value);
}
else
{
//Zero rating means that the Elo is below 1200 and is not revealed by the server
if (summary.rating == 0)
command.Set("current_rating", DbType.Int32, DBNull.Value);
else
command.Set("current_rating", summary.rating);
command.Set("top_rating", summary.maxRating);
}
command.Set("summoner_id", summoner.Id);
command.Set("map", (int)map);
command.Set("game_mode", (int)gameMode);
command.Set("wins", summary.wins);
command.Set("losses", summary.losses);
command.Set("leaves", summary.leaves);
int k = 0, d = 0, a = 0;
var kills = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_CHAMPION_KILLS");
if (kills != null)
{
k = kills.value;
}
var deaths = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_DEATHS_PER_SESSION");
if (deaths != null)
{
d = deaths.value;
}
var assists = summary.aggregatedStats.stats.FirstOrDefault(e => e.statType == "TOTAL_ASSISTS");
if (assists != null)
{
a = assists.value;
}
command.Set("kills", k);
command.Set("deaths", d);
command.Set("assists", a);
}
public DbConnection GetConnection(bool useForeignKeys = true)
{
DbConnection connection = Factory.CreateConnection();
connection.ConnectionString = Configuration.Database;
connection.Open();
//Turn on SQLite foreign keys
if (IsSQLite() && useForeignKeys)
{
using (var pragma = new DatabaseCommand("pragma foreign_keys = on", connection))
{
pragma.Execute();
}
}
return connection;
}
void CopyTable(DbConnection connection, string tableName, string createTableQuery)
{
string oldTableName = string.Format("broken_{0}", tableName);
//Rename the old table to a temporary name
RenameTable(connection, tableName, oldTableName);
//Remove indices
RemoveIndicesBasedOnQuery(connection, createTableQuery);
//Create the new table
using (var createTable = new DatabaseCommand(createTableQuery, connection))
createTable.Execute();
//Insert the data from the old table into the new table
string tableFields = GetTableFieldsFromCreateTableQuery(createTableQuery);
string fieldString = tableFields.Replace("\n", " ");
using (var insert = new DatabaseCommand(string.Format("insert into {0} ({1}) select {1} from {2}", tableName, fieldString, oldTableName), connection))
insert.Execute();
}
public DbConnection GetConnection(bool useForeignKeys = true)
{
DbConnection connection = Factory.CreateConnection();
connection.ConnectionString = Configuration.Database;
connection.Open();
//Turn on SQLite foreign keys
if (IsSQLite() && useForeignKeys)
{
using (var pragma = new DatabaseCommand("pragma foreign_keys = on", connection))
{
pragma.Execute();
}
}
return(connection);
}
void AccountIdUpgrade(DbConnection connection)
{
//In pre-r375 databases the account_id was marked as unique even though it shouldn't have been
bool isAffected = false;
using (var pragma = new DatabaseCommand("select sql from sqlite_master where tbl_name = 'summoner'", connection))
{
using (var reader = pragma.ExecuteReader())
{
if (reader.Read())
{
string query = reader.String();
if (query.IndexOf("account_id integer unique not null") >= 0)
{
isAffected = true;
}
}
else
{
throw new Exception("Unable to locate summoner table for upgrade check");
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-375 summoner.account_id bug. Attempting to upgrade it.");
using (var transaction = connection.BeginTransaction())
{
CopyTable(connection, "summoner", Properties.Resources.CreateTableSummoner);
CopyTable(connection, "summoner_rating", Properties.Resources.CreateTableSummonerRating);
CopyTable(connection, "summoner_ranked_statistics", Properties.Resources.CreateTableSummonerRankedStatistics);
CopyTable(connection, "player", Properties.Resources.CreateTablePlayer);
CopyTable(connection, "rune_page", Properties.Resources.CreateTableRunePage);
CopyTable(connection, "rune_slot", Properties.Resources.CreateTableRuneSlot);
DropOldTable(connection, "rune_slot");
DropOldTable(connection, "rune_page");
DropOldTable(connection, "player");
DropOldTable(connection, "summoner_ranked_statistics");
DropOldTable(connection, "summoner_rating");
DropOldTable(connection, "summoner");
transaction.Commit();
}
Vacuum(connection);
WriteLine("Upgrade succeeded.");
}
}
void AccountIdUpgrade(DbConnection connection)
{
//In pre-r375 databases the account_id was marked as unique even though it shouldn't have been
bool isAffected = false;
using (var pragma = new DatabaseCommand("select sql from sqlite_master where tbl_name = 'summoner'", connection))
{
using (var reader = pragma.ExecuteReader())
{
if (reader.Read())
{
string query = reader.String();
if (query.IndexOf("account_id integer unique not null") >= 0)
isAffected = true;
}
else
throw new Exception("Unable to locate summoner table for upgrade check");
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-375 summoner.account_id bug. Attempting to upgrade it.");
using (var transaction = connection.BeginTransaction())
{
CopyTable(connection, "summoner", Properties.Resources.CreateTableSummoner);
CopyTable(connection, "summoner_rating", Properties.Resources.CreateTableSummonerRating);
CopyTable(connection, "summoner_ranked_statistics", Properties.Resources.CreateTableSummonerRankedStatistics);
CopyTable(connection, "player", Properties.Resources.CreateTablePlayer);
CopyTable(connection, "rune_page", Properties.Resources.CreateTableRunePage);
CopyTable(connection, "rune_slot", Properties.Resources.CreateTableRuneSlot);
DropOldTable(connection, "rune_slot");
DropOldTable(connection, "rune_page");
DropOldTable(connection, "player");
DropOldTable(connection, "summoner_ranked_statistics");
DropOldTable(connection, "summoner_rating");
DropOldTable(connection, "summoner");
transaction.Commit();
}
Vacuum(connection);
WriteLine("Upgrade succeeded.");
}
}
void SetSummonerRankedStatisticsParameters(DatabaseCommand update, Summoner summoner, int season, ChampionStatistics champion)
{
update.SetFieldNames(SummonerRankedStatisticsFields);
update.Set(summoner.Id);
update.Set(season);
update.Set(champion.ChampionId);
update.Set(champion.Wins);
update.Set(champion.Losses);
update.Set(champion.Kills);
update.Set(champion.Deaths);
update.Set(champion.Assists);
update.Set(champion.MinionKills);
update.Set(champion.Gold);
update.Set(champion.TurretsDestroyed);
update.Set(champion.DamageDealt);
update.Set(champion.PhysicalDamageDealt);
update.Set(champion.MagicalDamageDealt);
update.Set(champion.DamageTaken);
update.Set(champion.DoubleKills);
update.Set(champion.TripleKills);
update.Set(champion.QuadraKills);
update.Set(champion.PentaKills);
update.Set(champion.TimeSpentDead);
update.Set(champion.MaximumKills);
update.Set(champion.MaximumDeaths);
}
void SetSummonerRankedStatisticsParameters(DatabaseCommand update, Summoner summoner, int season, ChampionStatistics champion)
{
update.SetFieldNames(SummonerRankedStatisticsFields);
update.Set(summoner.Id);
update.Set(season);
update.Set(champion.ChampionId);
update.Set(champion.Wins);
update.Set(champion.Losses);
update.Set(champion.Kills);
update.Set(champion.Deaths);
update.Set(champion.Assists);
update.Set(champion.MinionKills);
update.Set(champion.Gold);
update.Set(champion.TurretsDestroyed);
update.Set(champion.DamageDealt);
update.Set(champion.PhysicalDamageDealt);
update.Set(champion.MagicalDamageDealt);
update.Set(champion.DamageTaken);
update.Set(champion.DoubleKills);
update.Set(champion.TripleKills);
update.Set(champion.QuadraKills);
update.Set(champion.PentaKills);
update.Set(champion.TimeSpentDead);
update.Set(champion.MaximumKills);
update.Set(champion.MaximumDeaths);
}
void RemoveIndex(DbConnection connection, string index)
{
try
{
using (var dropIndex = new DatabaseCommand(string.Format("drop index {0}", index), connection))
dropIndex.Execute();
}
catch (Exception exception)
{
WriteLine("Warning - failed to remove index {0} while performing an upgrade: {1}", index, exception.Message);
}
}
void TierSystemUpgrade(DbConnection connection)
{
//Pre-r431 databases were still designed for season 1/2 style stats where losses and Elo were visible
bool isAffected = false;
using (var pragma = new DatabaseCommand("select sql from sqlite_master where tbl_name = 'summoner_rating'", connection))
{
using (var reader = pragma.ExecuteReader())
{
if (reader.Read())
{
string query = reader.String();
if (query.IndexOf("current_rating") >= 0)
isAffected = true;
}
else
throw new Exception("Unable to locate summoner rating table for upgrade check");
}
}
if (isAffected)
{
WriteLine("This database is still using the pre-r431 season 1/2 rating system. Attempting to upgrade it.");
using (var transaction = connection.BeginTransaction())
{
string tableName = "summoner_rating";
string oldTableName = "old_summoner_rating";
// Rename the old table so it can be copied to the new one
RenameTable(connection, tableName, oldTableName);
// Remove the old indices
RemoveIndicesBasedOnQuery(connection, Properties.Resources.CreateTableSummonerRating);
// Create the new table and copy the old data
ExecuteScript(connection, Properties.Resources.CreateAndCopySummonerRatingSeason3);
// Remove the old table
DropTable(connection, oldTableName);
transaction.Commit();
}
Vacuum(connection);
WriteLine("Upgrade succeeded.");
}
}
void ExecuteScript(DbConnection connection, string script)
{
var tokens = script.Split(';');
foreach (var token in tokens)
{
string query = token.Replace('\n', ' ').Trim();
while(query.IndexOf(" ") >= 0)
query = query.Replace(" ", " ");
using (var command = new DatabaseCommand(query, connection))
command.Execute();
}
}
void Vacuum(DbConnection connection)
{
using (var vacuum = new DatabaseCommand("vacuum", connection))
vacuum.Execute();
}
void DropOldTable(DbConnection connection, string tableName)
{
using (var dropTable = new DatabaseCommand(string.Format("drop table broken_{0}", tableName), connection))
dropTable.Execute();
}
void InitialiseSummonerCache()
{
SummonerCache = new Dictionary<RegionType, Dictionary<int, Summoner>>();
foreach (RegionType regionType in Enum.GetValues(typeof(RegionType)))
SummonerCache[regionType] = new Dictionary<int, Summoner>();
using (var connection = Provider.GetConnection())
{
using (var select = new DatabaseCommand("select {0} from summoner", connection, null, Summoner.GetFields()))
{
using (var reader = select.ExecuteReader())
{
while (reader.Read())
{
Summoner summoner = new Summoner(reader);
SummonerCache[summoner.Region][summoner.AccountId] = summoner;
}
}
}
}
}
void DropOldTable(DbConnection connection, string tableName)
{
using (var dropTable = new DatabaseCommand(string.Format("drop table broken_{0}", tableName), connection))
dropTable.Execute();
}
void Vacuum(DbConnection connection)
{
using (var vacuum = new DatabaseCommand("vacuum", connection))
vacuum.Execute();
}
void UnknownPlayerUpgrade(DbConnection connection)
{
//This is required for the r248 fix in the unknown_player table
//The field "account_id" had to be renamed to "summoner_id" in that version
//First check if this database is actually affected by that bug
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(unknown_player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name == "account_id")
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-r248 unknown_player bug. Attempting to upgrade it.");
CopyTableTransaction(connection, "unknown_player", Properties.Resources.CreateTableUnknownPlayer);
}
}
void GameIdUpgrade(DbConnection connection)
{
//player.game_id was nullable prior to r348, should have been not null all along
bool isAffected = false;
using (var pragma = new DatabaseCommand("pragma table_info(player)", connection))
{
using (var reader = pragma.ExecuteReader())
{
while (reader.Read())
{
string name = (string)reader.Get("name");
if (name != "game_id")
continue;
object notNullObject = reader.Get("notnull");
bool isNotNull = (int)(long)notNullObject == 1;
if (!isNotNull)
{
isAffected = true;
break;
}
}
}
}
if (isAffected)
{
WriteLine("This database is affected by the pre-348 player.game_id bug. Attempting to upgrade it.");
CopyTableTransaction(connection, "player", Properties.Resources.CreateTablePlayer);
}
}
void RenameTable(DbConnection connection, string from, string to)
{
using (var renameTable = new DatabaseCommand(string.Format("alter table {0} rename to {1}", from, to), connection))
renameTable.Execute();
}