public static void AlterDB(string fileName)
{
using (IDBHelpers dbHelpers = new DBHelpers(fileName))
{
using (DataTable dt = dbHelpers.GetDataTable("pragma table_info(RecentBattles)"))
{
var rbGlobalAvgDefPoints_check = (from x in dt.AsEnumerable()
where x.Field <string>("name") == "rbGlobalAvgDefPoints"
select x).DefaultIfEmpty(null).FirstOrDefault();
if (rbGlobalAvgDefPoints_check == null)
{
string sql = "Alter table RecentBattles Add rbGlobalAvgDefPoints REAL default (0) NULL";
dbHelpers.ExecuteNonQuery(sql);
}
}
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);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
using (DBHelpers db = new DBHelpers(_dbPath))
{
dataGridView1.DataSource = db.GetDataTable(textBox1.Text);
}
}
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 Create(string fileName)
{
using (IDBHelpers dbHelpers = new DBHelpers(fileName))
{
dbHelpers.CreateDatabase(fileName);
string sql = @"SELECT name FROM sqlite_master WHERE type='table' AND name='RecentBattles';";
using (DataTable tableCheck = dbHelpers.GetDataTable(sql, true))
{
if (tableCheck.Rows.Count <= 0)
{
dbHelpers.ExecuteNonQuery(CreateRecentBattlesTable(), true);
}
}
sql = @"SELECT name FROM sqlite_master WHERE type='table' AND name='RecentBattles_Session';";
using (DataTable tableCheck = dbHelpers.GetDataTable(sql, true))
{
if (tableCheck.Rows.Count <= 0)
{
dbHelpers.ExecuteNonQuery(CreateRecentBattlesSessionTable(), true);
}
}
}
}