public static void ReIndexDB(string fileName)
{
using (IDBHelpers dbHelpers = new DBHelpers(fileName))
{
string sql = @"SELECT name FROM sqlite_master WHERE type='table' AND name='RecentBattles';";
using (DataTable tableCheck = dbHelpers.GetDataTable(sql))
{
foreach (DataRow row in tableCheck.Rows)
{
dbHelpers.ExecuteNonQuery(String.Format("reindex '{0}'", row["name"]));
}
}
}
}
public static void CorrectBrokenData(string fileName)
{
using (IDBHelpers db = new DBHelpers(fileName))
{
db.ExecuteNonQuery(@"update RecentBattles_Session set rsUEDateTo = rsUEDateFrom + 1 where rsUEDateTo = 2114373600 and rsID <> (select max(rsID) from RecentBattles_Session where rsUEDateTo = 2114373600)");
db.ExecuteNonQuery(@"delete from RecentBattles where cast(rbDamageReceived as int) < 0");
db.ExecuteNonQuery(@"delete from File_Battles where ifnull(cast(bpMaxFrags as int),0) > 15");
db.ExecuteNonQuery(@"delete from RecentBattles where cast(rbXPReceived as int) / 100 > 10000 and cast(rbBattleTime as real) <= 1384113533");
db.ExecuteNonQuery(@"delete from RecentBattles where rbBattleTime = 0");
db.ExecuteNonQuery(@"delete from RecentBattles
where rbID in (
select y.rbID
from RecentBattles y
inner join (
select rbBattleTime, max(rbID) id from RecentBattles b2 group by rbBattleTime) x
on y.rbBattleTime = x.rbBattleTime
where y.rbID <> x.id)");
}
}
public static void AlterDB(string fileName)
{
using (IDBHelpers dbHelpers = new DBHelpers(fileName))
{
Console.WriteLine("Creating Tables...");
CreateTables(dbHelpers);
Console.WriteLine("Altering DB...");
using (DataTable dt = dbHelpers.GetDataTable("pragma table_info(File_Battles)"))
{
CheckAndCreateColumn("File_Battles", "bpMileage", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingEff", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingEffWeight", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingBR", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingBRWeight", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingWN7", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingWN7Weight", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingWN8", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingWN8Weight", "INTEGER null", dbHelpers, dt);
CheckAndCreateColumn("File_Battles", "bpRatingVersion", "INTEGER default (0) null", dbHelpers, dt);
//CheckAndCreateColumn("File_Battles", "bpRatingExpDamage", "real default (0) null", dbHelpers, dt);
//CheckAndCreateColumn("File_Battles", "bpRatingExpFrag", "real default (0) null", dbHelpers, dt);
//CheckAndCreateColumn("File_Battles", "bpRatingExpSpot", "real default (0) null", dbHelpers, dt);
//CheckAndCreateColumn("File_Battles", "bpRatingExpDef", "real default (0) null", dbHelpers, dt);
//CheckAndCreateColumn("File_Battles", "bpRatingExpWin", "real default (0) null", dbHelpers, dt);
}
using (DataTable dt = dbHelpers.GetDataTable("pragma table_info(RecentBattles)"))
{
CheckAndCreateColumn("RecentBattles", "rbMileage", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbRatingEff", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbRatingBR", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbRatingWN7", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbRatingWN8", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbDamageAssistedRadio", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbDamageAssistedTracks", "real default(0)", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbGlobalAvgDefPoints", "REAL default (0) NULL", dbHelpers, dt);
CheckAndCreateColumn("RecentBattles", "rbBattleMode", "INTEGER default (15) NULL", dbHelpers, dt);
}
using (DataTable dt = dbHelpers.GetDataTable("pragma table_info(RecentBattles_Session)"))
{
var fieldCheck = (from x in dt.AsEnumerable()
where x.Field <string>("name") == "rsUEDateFrom"
select x).DefaultIfEmpty(null).FirstOrDefault();
if (fieldCheck == null)
{
string sql = "Alter table RecentBattles_Session Add rsUEDateFrom real NULL";
dbHelpers.ExecuteNonQuery(sql);
sql = "Alter table RecentBattles_Session Add rsUEDateTo real NULL";
dbHelpers.ExecuteNonQuery(sql);
sql = @"UPDATE RecentBattles_Session
SET
rsUEDateFrom = (SELECT ifnull(min(RecentBattles.rbBattleTime), datetime('now', 'unixepoch'))
FROM RecentBattles
WHERE RecentBattles.rbSessionID = RecentBattles_Session.rsKey )
, rsUEDateTo = (SELECT ifnull(Max(RecentBattles.rbBattleTime), datetime('now', 'unixepoch'))
FROM RecentBattles
WHERE RecentBattles.rbSessionID = RecentBattles_Session.rsKey )
WHERE
EXISTS (
SELECT *
FROM RecentBattles
WHERE RecentBattles.rbSessionID = RecentBattles_Session.rsKey
)";
dbHelpers.ExecuteNonQuery(sql);
sql = @"UPDATE RecentBattles_Session
SET
rsUEDateFrom = strftime('%s', '2000-01-01 00:00:00')
, rsUEDateTo = strftime( '%s', '2000-01-01 00:00:01')
WHERE
rsUEDateFrom is null";
dbHelpers.ExecuteNonQuery(sql);
dbHelpers.ExecuteNonQuery(sql);
sql = @"update RecentBattles_Session
set rsUEDateTo = strftime('%s', '2037-01-01 00:00:00')
where rsID = (select max(rsID) from RecentBattles_Session)";
dbHelpers.ExecuteNonQuery(sql);
}
}
Console.WriteLine("Altering DB done");
}
}