private bool UpgradeSystemsDB()
{
try
{
ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Register (ID TEXT PRIMARY KEY NOT NULL, ValueInt INTEGER, ValueDouble DOUBLE, ValueString TEXT, ValueBlob BLOB)");
// BE VERY careful with connections when creating/deleting tables - you end up with SQL Schema errors or it not seeing the table
SQLExtRegister reg = new SQLExtRegister(this);
int dbver = reg.GetSettingInt("DBVer", 0); // use reg, don't use the built in func as they create new connections and confuse the schema
ExecuteNonQueries(new string[] // always kill these old tables and make EDDB new table
{
"DROP TABLE IF EXISTS EddbSystems",
"DROP TABLE IF EXISTS Distances",
"DROP TABLE IF EXISTS Stations",
"DROP TABLE IF EXISTS SystemAliases",
"DROP TABLE IF EXISTS station_commodities",
"CREATE TABLE IF NOT EXISTS EDDB (edsmid INTEGER PRIMARY KEY NOT NULL, eddbid INTEGER, eddbupdatedat INTEGER, population INTEGER, faction TEXT, government INTEGER, allegiance INTEGER, state INTEGER, security INTEGER, primaryeconomy INTEGER, needspermit INTEGER, power TEXT, powerstate TEXT, properties TEXT)",
"CREATE TABLE IF NOT EXISTS Aliases (edsmid INTEGER PRIMARY KEY NOT NULL, edsmid_mergedto INTEGER, name TEXT COLLATE NOCASE)"
});
if (dbver < 102) // is it older than 102, its unusable
{
ExecuteNonQueries(new string[] // older than 102, not supporting, remove
{
"DROP TABLE IF EXISTS EdsmSystems",
"DROP TABLE IF EXISTS SystemNames",
});
reg.DeleteKey("EDSMLastSystems"); // no EDSM system time
}
CreateStarTables(); // ensure we have
CreateSystemDBTableIndexes(); // ensure they are there
DropStarTables(tablepostfix); // clean out any temp tables half prepared
// conn.Vacuum(); // debug
if (dbver < 200)
{
reg.PutSettingInt("DBVer", 200);
reg.DeleteKey("EDDBSystemsTime"); // force a reload of EDDB
}
return(true);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("UpgradeSystemsDB error: " + ex.Message + Environment.NewLine + ex.StackTrace);
return(false);
}
}
const string tablepostfix = "temp"; // postfix for temp tables
public bool UpgradeSystemsDB()
{
try
{
ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Register (ID TEXT PRIMARY KEY NOT NULL, ValueInt INTEGER, ValueDouble DOUBLE, ValueString TEXT, ValueBlob BLOB)");
// BE VERY careful with connections when creating/deleting tables - you end up with SQL Schema errors or it not seeing the table
SQLExtRegister reg = new SQLExtRegister(this);
int dbver = reg.GetSettingInt("DBVer", 0); // use reg, don't use the built in func as they create new connections and confuse the schema
ExecuteNonQueries(new string[] // always kill these old tables and make EDDB new table
{
"DROP TABLE IF EXISTS EDDB", // New Dec 20 - no more EDDB
"DROP TABLE IF EXISTS Distances",
"DROP TABLE IF EXISTS EddbSystems",
// keep edsmsystems
"DROP TABLE IF EXISTS Stations",
"DROP TABLE IF EXISTS SystemAliases",
// don't drop Systemnames
"DROP TABLE IF EXISTS station_commodities",
"CREATE TABLE IF NOT EXISTS Aliases (edsmid INTEGER PRIMARY KEY NOT NULL, edsmid_mergedto INTEGER, name TEXT COLLATE NOCASE)"
});
var tablesql = this.SQLMasterQuery("table");
int index = tablesql.FindIndex(x => x.TableName == "Systems");
bool oldsystems = index >= 0 && tablesql[index].SQL.Contains("commandercreate");
if (dbver < 200 || oldsystems)
{
ExecuteNonQueries(new string[] // always kill these old tables and make EDDB new table
{
"DROP TABLE IF EXISTS Systems", // New! this is an hold over which never got deleted when we moved to the 102 schema
});
}
if (dbver < 102) // is it older than 102, its unusable
{
ExecuteNonQueries(new string[] // older than 102, not supporting, remove
{
"DROP TABLE IF EXISTS EdsmSystems",
"DROP TABLE IF EXISTS SystemNames",
});
reg.DeleteKey("EDSMLastSystems"); // no EDSM system time
}
CreateStarTables(); // ensure we have
CreateSystemDBTableIndexes(); // ensure they are there
DropStarTables(tablepostfix); // clean out any temp tables half prepared
// conn.Vacuum(); // debug
if (dbver < 200)
{
reg.PutSettingInt("DBVer", 200);
reg.DeleteKey("EDDBSystemsTime"); // force a reload of EDDB
}
return(true);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("UpgradeSystemsDB error: " + ex.Message + Environment.NewLine + ex.StackTrace);
return(false);
}
}
protected static bool UpgradeUserDB(SQLiteConnectionUser conn)
{
int dbver;
try
{
conn.ExecuteQuery("CREATE TABLE IF NOT EXISTS Register (ID TEXT PRIMARY KEY NOT NULL, ValueInt INTEGER, ValueDouble DOUBLE, ValueString TEXT, ValueBlob BLOB)");
SQLExtRegister reg = new SQLExtRegister(conn);
dbver = reg.GetSettingInt("DBVer", 1); // use the constring one, as don't want to go back into ConnectionString code
DropOldUserTables(conn);
if (dbver < 2)
{
UpgradeUserDB2(conn);
}
if (dbver < 4)
{
UpgradeUserDB4(conn);
}
if (dbver < 7)
{
UpgradeUserDB7(conn);
}
if (dbver < 9)
{
UpgradeUserDB9(conn);
}
if (dbver < 10)
{
UpgradeUserDB10(conn);
}
if (dbver < 11)
{
UpgradeUserDB11(conn);
}
if (dbver < 12)
{
UpgradeUserDB12(conn);
}
if (dbver < 16)
{
UpgradeUserDB16(conn);
}
if (dbver < 101)
{
UpgradeUserDB101(conn);
}
if (dbver < 102)
{
UpgradeUserDB102(conn);
}
if (dbver < 103)
{
UpgradeUserDB103(conn);
}
if (dbver < 104)
{
UpgradeUserDB104(conn);
}
if (dbver < 105)
{
UpgradeUserDB105(conn);
}
if (dbver < 106)
{
UpgradeUserDB106(conn);
}
if (dbver < 107)
{
UpgradeUserDB107(conn);
}
if (dbver < 108)
{
UpgradeUserDB108(conn);
}
if (dbver < 109)
{
UpgradeUserDB109(conn);
}
if (dbver < 110)
{
UpgradeUserDB110(conn);
}
if (dbver < 111)
{
UpgradeUserDB111(conn);
}
if (dbver < 112)
{
UpgradeUserDB112(conn);
}
if (dbver < 113)
{
UpgradeUserDB113(conn);
}
if (dbver < 114)
{
UpgradeUserDB114(conn);
}
if (dbver < 115)
{
UpgradeUserDB115(conn);
}
if (dbver < 116)
{
UpgradeUserDB116(conn);
}
if (dbver < 117)
{
UpgradeUserDB117(conn);
}
if (dbver < 118)
{
UpgradeUserDB118(conn);
}
if (dbver < 119)
{
UpgradeUserDB119(conn);
}
CreateUserDBTableIndexes(conn);
return(true);
}
catch (Exception ex)
{
ExtendedControls.MessageBoxTheme.Show("UpgradeUserDB error: " + ex.Message + Environment.NewLine + ex.StackTrace);
return(false);
}
}