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 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();
}
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.");
}
}
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;
}
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();
}
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 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 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 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();
}
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);
}
}