private static void UpdateTableSchema(SQLiteConnectionED cn, string name, object schema)
{
HashSet <string> columns = new HashSet <string>();
using (DbCommand cmd = cn.CreateCommand("PRAGMA table_info(@tablename)"))
{
cmd.AddParameterWithValue("@tablename", name);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
columns.Add(((string)reader["name"]).ToLower());
}
}
}
EnumerateSchema(schema, (colname, coldef) =>
{
if (!columns.Contains(colname.ToLower()))
{
string altercmd = $"ALTER TABLE {name} ADD COLUMN {colname} {coldef}";
System.Diagnostics.Trace.WriteLine(altercmd);
using (DbCommand cmd = cn.CreateCommand(altercmd))
{
cmd.ExecuteNonQuery();
}
}
});
}
public static List <WantedSystemClass> GetAllWantedSystems()
{
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
List <WantedSystemClass> retVal = new List <WantedSystemClass>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
WantedSystemClass sys = new WantedSystemClass(dr);
retVal.Add(sys);
}
return(retVal);
}
}
}
catch
{
return(null);
}
}
public bool Delete()
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
return(Delete(cn));
}
}
private static void UpgradeSystemsDB15(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN versiondate DATETIME";
string query2 = "UPDATE Systems SET versiondate = datetime('now')";
SQLiteDBClass.PerformUpgrade(conn, 15, true, false, new[] { query1, query2 });
}
private static void UpgradeUserDB12(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE routes_expeditions (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL, start DATETIME, end DATETIME)";
string query2 = "CREATE TABLE route_systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, routeid INTEGER NOT NULL, systemname TEXT NOT NULL)";
PerformUpgrade(conn, 12, true, false, new[] { query1, query2 });
}
private static void UpgradeUserDB11(SQLiteConnectionED conn)
{
string query2 = "ALTER TABLE Objects ADD COLUMN Landed BOOL";
string query3 = "ALTER TABLE Objects ADD COLUMN terraform Integer";
PerformUpgrade(conn, 11, true, false, new[] { query2, query3 });
}
public static long GetTotalDistances()
{
long value = 0;
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select Count(*) from Distances"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
value = (long)reader["Count(*)"];
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return(value);
}
private static void UpgradeSystemsDB102(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE SystemNames (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL COLLATE NOCASE, " +
"EdsmId INTEGER NOT NULL)";
string query2 = "CREATE TABLE EdsmSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER, " +
"X INTEGER NOT NULL, " +
"Y INTEGER NOT NULL, " +
"Z INTEGER NOT NULL, " +
"CreateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"UpdateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"VersionTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"GridId INTEGER NOT NULL DEFAULT -1, " +
"RandomId INTEGER NOT NULL DEFAULT -1)";
string query3 = "CREATE TABLE EddbSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER NOT NULL, " +
"Population INTEGER , " +
"Faction TEXT, " +
"GovernmentId Integer, " +
"AllegianceId Integer, " +
"PrimaryEconomyId Integer, " +
"Security Integer, " +
"EddbUpdatedAt Integer, " + // Seconds since 1970-01-01 00:00:00 UTC
"State Integer, " +
"NeedsPermit Integer)";
SQLiteDBClass.PerformUpgrade(conn, 102, true, false, new[] { query1, query2, query3 });
}
private static void UpgradeSystemsDB6(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_eddb Integer";
string query2 = "ALTER TABLE Systems ADD COLUMN faction TEXT";
//string query3 = "ALTER TABLE Systems ADD COLUMN population Integer";
string query4 = "ALTER TABLE Systems ADD COLUMN government_id Integer";
string query5 = "ALTER TABLE Systems ADD COLUMN allegiance_id Integer";
string query6 = "ALTER TABLE Systems ADD COLUMN primary_economy_id Integer";
string query7 = "ALTER TABLE Systems ADD COLUMN security Integer";
string query8 = "ALTER TABLE Systems ADD COLUMN eddb_updated_at Integer";
string query9 = "ALTER TABLE Systems ADD COLUMN state Integer";
string query10 = "ALTER TABLE Systems ADD COLUMN needs_permit Integer";
string query11 = "DROP TABLE IF EXISTS Stations";
string query12 = "CREATE TABLE Stations (id INTEGER PRIMARY KEY NOT NULL ,system_id INTEGER, name TEXT NOT NULL , " +
" max_landing_pad_size INTEGER, distance_to_star INTEGER, faction Text, government_id INTEGER, allegiance_id Integer, state_id INTEGER, type_id Integer, " +
"has_commodities BOOL DEFAULT (null), has_refuel BOOL DEFAULT (null), has_repair BOOL DEFAULT (null), has_rearm BOOL DEFAULT (null), " +
"has_outfitting BOOL DEFAULT (null), has_shipyard BOOL DEFAULT (null), has_blackmarket BOOL DEFAULT (null), eddb_updated_at Integer )";
string query13 = "CREATE TABLE station_commodities (station_id INTEGER PRIMARY KEY NOT NULL, commodity_id INTEGER, type INTEGER)";
string query14 = "CREATE INDEX station_commodities_index ON station_commodities (station_id ASC, commodity_id ASC, type ASC)";
string query15 = "CREATE INDEX StationsIndex_ID ON Stations (id ASC)";
string query16 = "CREATE INDEX StationsIndex_system_ID ON Stations (system_id ASC)";
string query17 = "CREATE INDEX StationsIndex_system_Name ON Stations (Name ASC)";
SQLiteDBClass.PerformUpgrade(conn, 6, true, false, new[] {
query1, query2, query4, query5, query6, query7, query8, query9, query10,
query11, query12, query13, query14, query15, query16, query17
});
}
public bool Update()
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
return(Update(cn));
}
}
private bool Add(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into routes_expeditions (name, start, end) values (@name, @start, @end)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from routes_expeditions"))
{
Id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return(true);
}
}
private static void UpgradeSystemsDB11(SQLiteConnectionED conn)
{
//Default is Color.Red.ToARGB()
string query1 = "ALTER TABLE Systems ADD COLUMN FirstDiscovery BOOL";
SQLiteDBClass.PerformUpgrade(conn, 11, true, false, new[] { query1 });
}
private bool Update(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("UPDATE routes_expeditions SET name=@name, start=@start, end=@end WHERE id=@id"))
{
cmd.AddParameterWithValue("@id", Id);
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
{
cmd2.AddParameterWithValue("@routeid", Id);
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return(true);
}
}
public static List <DistanceClass> GetDistancesByStatus(int status)
{
List <DistanceClass> ldist = new List <DistanceClass>();
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
DistanceClass dist = new DistanceClass(dr);
ldist.Add(dist);
}
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return(ldist);
}
public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2)
{
if (s1 == null || s2 == null)
{
return(null);
}
try
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1"))
{
cmd.AddParameterWithValue("@NameA", s1.name);
cmd.AddParameterWithValue("@NameB", s2.name);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // if found.
{
DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]);
return(dist);
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message);
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
}
return(null);
}
private static void UpdateDbSchema(SQLiteConnectionED cn, dynamic schema)
{
HashSet <string> tables = new HashSet <string>();
HashSet <string> indexes = new HashSet <string>();
using (DbCommand cmd = cn.CreateCommand("SELECT name, type FROM sqlite_master"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if ((string)reader["type"] == "table")
{
tables.Add((string)reader["name"]);
}
else if ((string)reader["type"] == "index")
{
indexes.Add((string)reader["name"]);
}
}
}
}
EnumerateSchema((object)schema.Tables, (name, tblschema) =>
{
if (!tables.Contains(name))
{
CreateTable(cn, name, tblschema);
}
else
{
UpdateTableSchema(cn, name, tblschema);
}
});
EnumerateSchema((object)schema.UniqueIndexes, (name, idxschema) =>
{
if (!indexes.Contains(name))
{
string idxcmd = $"CREATE UNIQUE INDEX {name} ON {idxschema}";
System.Diagnostics.Trace.WriteLine(idxcmd);
using (DbCommand cmd = cn.CreateCommand(idxcmd))
{
cmd.ExecuteNonQuery();
}
}
});
EnumerateSchema((object)schema.Indexes, (name, idxschema) =>
{
if (!indexes.Contains(name))
{
string idxcmd = $"CREATE INDEX {name} ON {idxschema}";
System.Diagnostics.Trace.WriteLine(idxcmd);
using (DbCommand cmd = cn.CreateCommand(idxcmd))
{
cmd.ExecuteNonQuery();
}
}
});
}
public bool Add()
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
bool ret = Add(cn);
return(ret);
}
}
private static void UpgradeUserDB107(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Commanders ADD COLUMN SyncToEdsm INTEGER NOT NULL DEFAULT 1";
string query2 = "ALTER TABLE Commanders ADD COLUMN SyncFromEdsm INTEGER NOT NULL DEFAULT 0";
string query3 = "ALTER TABLE Commanders ADD COLUMN SyncToEddn INTEGER NOT NULL DEFAULT 1";
PerformUpgrade(conn, 107, true, false, new[] { query1, query2, query3 });
}
public bool Add()
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
bool ret = Add(cn); // pass it an open connection since it does multiple SQLs
return(ret);
}
}
private static void UpgradeSystemsDB17(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_edsm Integer";
string query4 = "ALTER TABLE Distances ADD COLUMN id_edsm Integer";
string query5 = "CREATE INDEX Distances_EDSM_ID_Index ON Distances (id_edsm ASC)";
SQLiteDBClass.PerformUpgrade(conn, 17, true, false, new[] { query1, query4, query5 });
}
public bool Update()
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
bool ret = Update(cn);
return(ret);
}
}
private static void UpgradeSystemsDB2(SQLiteConnectionED conn)
{
string query = "CREATE TABLE Systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , name TEXT NOT NULL COLLATE NOCASE , x FLOAT, y FLOAT, z FLOAT, cr INTEGER, commandercreate TEXT, createdate DATETIME, commanderupdate TEXT, updatedate DATETIME, status INTEGER, population INTEGER )";
string query3 = "CREATE TABLE Distances (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , NameA TEXT NOT NULL , NameB TEXT NOT NULL , Dist FLOAT NOT NULL , CommanderCreate TEXT NOT NULL , CreateTime DATETIME NOT NULL , Status INTEGER NOT NULL )";
string query5 = "CREATE INDEX DistanceName ON Distances (NameA ASC, NameB ASC)";
SQLiteDBClass.PerformUpgrade(conn, 2, false, false, new[] { query, query3, query5 });
}
private static void UpgradeSystemsDB19(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE SystemAliases (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, id_edsm INTEGER, id_edsm_mergedto INTEGER)";
string query2 = "CREATE INDEX SystemAliases_name ON SystemAliases (name)";
string query3 = "CREATE UNIQUE INDEX SystemAliases_id_edsm ON SystemAliases (id_edsm)";
string query4 = "CREATE INDEX SystemAliases_id_edsm_mergedto ON SystemAliases (id_edsm_mergedto)";
SQLiteDBClass.PerformUpgrade(conn, 19, true, false, new[] { query1, query2, query3, query4 });
}
private bool Delete(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("Delete From Distances where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
public SQLiteCommandED(DbCommand cmd, SQLiteConnectionED conn, SQLiteTxnLockED <TConn> txnlock, DbTransaction txn = null)
{
_connection = conn;
_txnlock = txnlock;
InnerCommand = cmd;
if (txn != null)
{
SetTransaction(txn);
}
}
private static void UpgradeSystemsDB20(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN gridid Integer NOT NULL DEFAULT -1";
string query2 = "ALTER TABLE Systems ADD COLUMN randomid Integer NOT NULL DEFAULT -1";
SQLiteDBClass.PerformUpgrade(conn, 20, true, false, new[] { query1, query2 }, () =>
{
conn.PutSettingStringCN("EDSMLastSystems", "2010 - 01 - 01 00:00:00"); // force EDSM sync..
});
}
private bool Delete(SQLiteConnectionED cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM wanted_systems WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
public static void TranferVisitedSystemstoJournalTableIfRequired()
{
if (System.IO.File.Exists(SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDiscovery)))
{
if (SQLiteDBClass.GetSettingBool("ImportVisitedSystems", false) == false)
{
TranferVisitedSystemstoJournalTable();
SQLiteDBClass.PutSettingBool("ImportVisitedSystems", true);
}
}
}
private static void UpgradeUserDB101(SQLiteConnectionED conn)
{
string query1 = "DROP TABLE IF EXISTS Systems";
string query2 = "DROP TABLE IF EXISTS SystemAliases";
string query3 = "DROP TABLE IF EXISTS Distances";
string query4 = "VACUUM";
PerformUpgrade(conn, 101, true, false, new[] { query1, query2, query3, query4 }, () =>
{
// PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00", conn); // force EDSM sync..
});
}
public static bool Delete(DistancsEnum distsource)
{
using (SQLiteConnectionED cn = new SQLiteConnectionED())
{
using (DbCommand cmd = cn.CreateCommand("Delete from Distances where Status=@Status"))
{
cmd.AddParameterWithValue("@Status", (int)distsource);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
return(true);
}
public static int SQLNonQueryText(SQLiteConnectionED cn, DbCommand cmd)
{
int rows = 0;
try
{
rows = cmd.ExecuteNonQuery();
return rows;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQueryText Exception: " + ex.Message);
throw;
}
}
private static void UpgradeUserDB101(SQLiteConnectionED conn)
{
string query1 = "DROP TABLE IF EXISTS Systems";
string query2 = "DROP TABLE IF EXISTS SystemAliases";
string query3 = "DROP TABLE IF EXISTS Distances";
string query4 = "VACUUM";
PerformUpgrade(conn, 101, true, false, new[] { query1, query2, query3, query4 }, () =>
{
// PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00", conn); // force EDSM sync..
});
}
private static void UpgradeSystemsDB101(SQLiteConnectionED conn)
{
string query1 = "DROP TABLE IF EXISTS Bookmarks";
string query2 = "DROP TABLE IF EXISTS SystemNote";
string query3 = "DROP TABLE IF EXISTS TravelLogUnit";
string query4 = "DROP TABLE IF EXISTS VisitedSystems";
string query5 = "DROP TABLE IF EXISTS Route_Systems";
string query6 = "DROP TABLE IF EXISTS Routes_expedition";
string query7 = "VACUUM";
PerformUpgrade(conn, 101, true, false, new[] { query1, query2, query3, query4, query5, query6, query7 }, () =>
{
// PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00", conn); // force EDSM sync..
});
}
private static void UpgradeDB8(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE VisitedSystems ADD COLUMN Map_colour INTEGER DEFAULT (-65536)";
PerformUpgrade(conn, 8, true, true, new[] { query1 });
}
private static void UpgradeDB6(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_eddb Integer";
string query2 = "ALTER TABLE Systems ADD COLUMN faction TEXT";
//string query3 = "ALTER TABLE Systems ADD COLUMN population Integer";
string query4 = "ALTER TABLE Systems ADD COLUMN government_id Integer";
string query5 = "ALTER TABLE Systems ADD COLUMN allegiance_id Integer";
string query6 = "ALTER TABLE Systems ADD COLUMN primary_economy_id Integer";
string query7 = "ALTER TABLE Systems ADD COLUMN security Integer";
string query8 = "ALTER TABLE Systems ADD COLUMN eddb_updated_at Integer";
string query9 = "ALTER TABLE Systems ADD COLUMN state Integer";
string query10 = "ALTER TABLE Systems ADD COLUMN needs_permit Integer";
string query11 = "DROP TABLE Stations";
string query12 = "CREATE TABLE Stations (id INTEGER PRIMARY KEY NOT NULL ,system_id INTEGER, name TEXT NOT NULL , " +
" max_landing_pad_size INTEGER, distance_to_star INTEGER, faction Text, government_id INTEGER, allegiance_id Integer, state_id INTEGER, type_id Integer, " +
"has_commodities BOOL DEFAULT (null), has_refuel BOOL DEFAULT (null), has_repair BOOL DEFAULT (null), has_rearm BOOL DEFAULT (null), " +
"has_outfitting BOOL DEFAULT (null), has_shipyard BOOL DEFAULT (null), has_blackmarket BOOL DEFAULT (null), eddb_updated_at Integer )";
string query13 = "CREATE TABLE station_commodities (station_id INTEGER PRIMARY KEY NOT NULL, commodity_id INTEGER, type INTEGER)";
string query14 = "CREATE INDEX station_commodities_index ON station_commodities (station_id ASC, commodity_id ASC, type ASC)";
string query15 = "CREATE INDEX StationsIndex_ID ON Stations (id ASC)";
string query16 = "CREATE INDEX StationsIndex_system_ID ON Stations (system_id ASC)";
string query17 = "CREATE INDEX StationsIndex_system_Name ON Stations (Name ASC)";
PerformUpgrade(conn, 6, true, true, new[] {
query1, query2, query4, query5, query6, query7, query8, query9, query10,
query11, query12, query13, query14, query15, query16, query17 });
}
private static void UpgradeDB4(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE SystemNote ADD COLUMN Note TEXT";
PerformUpgrade(conn, 4, true, true, new[] { query1 });
}
private static void UpgradeDB20(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN gridid Integer NOT NULL DEFAULT -1";
string query2 = "ALTER TABLE Systems ADD COLUMN randomid Integer NOT NULL DEFAULT -1";
PerformUpgrade(conn, 20, true, true, new[] { query1, query2 }, () =>
{
PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00", conn); // force EDSM sync..
});
}
private static void PerformUpgrade(SQLiteConnectionED conn, int newVersion, bool catchErrors, bool backupDbFile, string[] queries, Action doAfterQueries = null)
{
if (backupDbFile)
{
string dbfile = conn.DBFile;
try
{
File.Copy(dbfile, dbfile.Replace(".sqlite", $"{newVersion - 1}.sqlite"));
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("Exception: " + ex.Message);
System.Diagnostics.Trace.WriteLine("Trace: " + ex.StackTrace);
}
}
try
{
foreach (var query in queries)
{
ExecuteQuery(conn, query);
}
}
catch (Exception ex)
{
if (!catchErrors)
throw;
System.Diagnostics.Trace.WriteLine("Exception: " + ex.Message);
System.Diagnostics.Trace.WriteLine("Trace: " + ex.StackTrace);
MessageBox.Show($"UpgradeDB{newVersion} error: " + ex.Message);
}
doAfterQueries?.Invoke();
PutSettingInt("DBVer", newVersion, conn);
}
private static void ExecuteQuery(SQLiteConnectionED conn, string query)
{
using (DbCommand command = conn.CreateCommand(query))
command.ExecuteNonQuery();
}
public static int GetSettingInt(string key, int defaultvalue, SQLiteConnectionED cn )
{
try
{
using (DbCommand cmd = cn.CreateCommand("SELECT ValueInt from Register WHERE ID = @ID"))
{
cmd.AddParameterWithValue("@ID", key);
object ob = SQLScalar(cn, cmd);
if (ob == null)
return defaultvalue;
int val = Convert.ToInt32(ob);
return val;
}
}
catch
{
return defaultvalue;
}
}
public static bool keyExists(string sKey, SQLiteConnectionED cn)
{
try
{
using (DbCommand cmd = cn.CreateCommand("select ID from Register WHERE ID=@key"))
{
cmd.AddParameterWithValue("@key", sKey);
DataSet ds = SQLQueryText(cn, cmd);
return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0); // got a value, true
}
}
catch
{
}
return false;
}
private static bool UpgradeDB(SQLiteConnectionED conn)
{
int dbver;
try
{
dbver = GetSettingInt("DBVer", 1, conn); // use the constring one, as don't want to go back into ConnectionString code
if (dbver < 2)
UpgradeDB2(conn);
if (dbver < 3)
UpgradeDB3(conn);
if (dbver < 4)
UpgradeDB4(conn);
if (dbver < 5)
UpgradeDB5(conn);
if (dbver < 6)
UpgradeDB6(conn);
if (dbver < 7)
UpgradeDB7(conn);
if (dbver < 8)
UpgradeDB8(conn);
if (dbver < 9)
UpgradeDB9(conn);
if (dbver < 10)
UpgradeDB10(conn);
if (dbver < 11)
UpgradeDB11(conn);
if (dbver < 12)
UpgradeDB12(conn);
// 15 remove due to conflict between 2 branches...
if (dbver < 14)
UpgradeDB14(conn);
if (dbver < 15)
UpgradeDB15(conn);
if (dbver < 16)
UpgradeDB16(conn);
if (dbver < 17)
UpgradeDB17(conn);
if (dbver < 18)
UpgradeDB18(conn);
if (dbver < 19)
UpgradeDB19(conn);
if (dbver < 20)
UpgradeDB20(conn);
return true;
}
catch (Exception ex)
{
MessageBox.Show("UpgradeDB error: " + ex.Message);
MessageBox.Show(ex.StackTrace);
return false;
}
}
private static void UpgradeDB2(SQLiteConnectionED conn)
{
string query = "CREATE TABLE Systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , name TEXT NOT NULL COLLATE NOCASE , x FLOAT, y FLOAT, z FLOAT, cr INTEGER, commandercreate TEXT, createdate DATETIME, commanderupdate TEXT, updatedate DATETIME, status INTEGER, population INTEGER )";
string query3 = "CREATE TABLE Distances (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , NameA TEXT NOT NULL , NameB TEXT NOT NULL , Dist FLOAT NOT NULL , CommanderCreate TEXT NOT NULL , CreateTime DATETIME NOT NULL , Status INTEGER NOT NULL )";
string query4 = "CREATE TABLE SystemNote (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , Name TEXT NOT NULL , Time DATETIME NOT NULL )";
string query5 = "CREATE INDEX DistanceName ON Distances (NameA ASC, NameB ASC)";
string query6 = "CREATE TABLE VisitedSystems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , Name TEXT NOT NULL , Time DATETIME NOT NULL , SystemID INTEGER, Dist FLOAT)";
string query7 = "CREATE TABLE Stations (station_id INTEGER PRIMARY KEY NOT NULL ,system_id INTEGER REFERENCES Systems(id), name TEXT NOT NULL ,blackmarket BOOL DEFAULT (null) ,max_landing_pad_size INTEGER,distance_to_star INTEGER,type TEXT,faction TEXT,shipyard BOOL,outfitting BOOL, commodities_market BOOL)";
string query8 = "CREATE INDEX stationIndex ON Stations (system_id ASC)";
PerformUpgrade(conn, 2, false, false, new[] { query, query3, query4, query5, query6, query7, query8 });
}
private static void UpgradeDB10(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE wanted_systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, systemname TEXT UNIQUE NOT NULL)";
PerformUpgrade(conn, 10, true, true, new[] { query1 });
}
private static void UpgradeDB3(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN Note TEXT";
PerformUpgrade(conn, 3, false, false, new[] { query1 });
}
private static void UpgradeDB11(SQLiteConnectionED conn)
{
//Default is Color.Red.ToARGB()
string query1 = "ALTER TABLE Systems ADD COLUMN FirstDiscovery BOOL";
string query2 = "ALTER TABLE Objects ADD COLUMN Landed BOOL";
string query3 = "ALTER TABLE Objects ADD COLUMN terraform Integer";
string query4 = "ALTER TABLE VisitedSystems ADD COLUMN Status BOOL";
PerformUpgrade(conn, 11, true, true, new[] { query1, query2, query3, query4 });
}
private static void UpgradeDB5(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE VisitedSystems ADD COLUMN Unit TEXT";
string query3 = "ALTER TABLE VisitedSystems ADD COLUMN Commander Integer";
string query4 = "CREATE INDEX VisitedSystemIndex ON VisitedSystems (Name ASC, Time ASC)";
PerformUpgrade(conn, 5, true, true, new[] {query1, query3, query4});
}
private static void UpgradeDB12(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE routes_expeditions (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL, start DATETIME, end DATETIME)";
string query2 = "CREATE TABLE route_systems (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, routeid INTEGER NOT NULL, systemname TEXT NOT NULL)";
PerformUpgrade(conn, 12, true, true, new[] { query1, query2 });
}
private static void UpgradeDB7(SQLiteConnectionED conn)
{
string query1 = "DROP TABLE VisitedSystems";
string query2 = "CREATE TABLE VisitedSystems(id INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL, Time DATETIME NOT NULL, Unit Text, Commander Integer, Source Integer, edsm_sync BOOL DEFAULT (null))";
string query3 = "CREATE TABLE TravelLogUnit(id INTEGER PRIMARY KEY NOT NULL, type INTEGER NOT NULL, name TEXT NOT NULL, size INTEGER, path TEXT)";
PerformUpgrade(conn, 7, true, true, new[] { query1, query2, query3 });
}
private static bool UpgradeDB14(SQLiteConnectionED conn)
{
//Default is Color.Red.ToARGB()
string query1 = "ALTER TABLE VisitedSystems ADD COLUMN X double";
string query2 = "ALTER TABLE VisitedSystems ADD COLUMN Y double";
string query3 = "ALTER TABLE VisitedSystems ADD COLUMN Z double";
PerformUpgrade(conn, 14, true, true, new[] { query1, query2, query3 });
return true;
}
private static void UpgradeDB9(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE Objects (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , SystemName TEXT NOT NULL , ObjectName TEXT NOT NULL , ObjectType INTEGER NOT NULL , ArrivalPoint Float, Gravity FLOAT, Atmosphere Integer, Vulcanism Integer, Terrain INTEGER, Carbon BOOL, Iron BOOL, Nickel BOOL, Phosphorus BOOL, Sulphur BOOL, Arsenic BOOL, Chromium BOOL, Germanium BOOL, Manganese BOOL, Selenium BOOL NOT NULL , Vanadium BOOL, Zinc BOOL, Zirconium BOOL, Cadmium BOOL, Mercury BOOL, Molybdenum BOOL, Niobium BOOL, Tin BOOL, Tungsten BOOL, Antimony BOOL, Polonium BOOL, Ruthenium BOOL, Technetium BOOL, Tellurium BOOL, Yttrium BOOL, Commander Text, UpdateTime DATETIME, Status INTEGER )";
PerformUpgrade(conn, 9, true, true, new[] { query1 });
}
private static void UpgradeDB15(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN versiondate DATETIME";
string query2 = "UPDATE Systems SET versiondate = datetime('now')";
PerformUpgrade(conn, 15, true, true, new[] { query1, query2 });
}
private static void UpgradeSystemsDB102(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE SystemNames (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL, " +
"EdsmId INTEGER NOT NULL UNIQUE)";
string query2 = "CREATE TABLE EdsmSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER, " +
"X INTEGER NOT NULL, " +
"Y INTEGER NOT NULL, " +
"Z INTEGER NOT NULL, " +
"CreateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"UpdateTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"VersionTimestamp INTEGER NOT NULL, " + // Seconds since 2015-01-01 00:00:00 UTC
"GridId INTEGER NOT NULL DEFAULT -1, " +
"RandomId INTEGER NOT NULL DEFAULT -1)";
string query3 = "CREATE TABLE EddbSystems (" +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Name TEXT NOT NULL, " +
"EdsmId INTEGER NOT NULL, " +
"EddbId INTEGER NOT NULL, " +
"Population INTEGER , " +
"Faction TEXT, " +
"GovernmentId Integer, " +
"AllegianceId Integer, " +
"PrimaryEconomyId Integer, " +
"Security Integer, " +
"EddbUpdatedAt Integer, " + // Seconds since 1970-01-01 00:00:00 UTC
"State Integer, " +
"NeedsPermit Integer)";
PerformUpgrade(conn, 102, true, false, new[] { query1, query2, query3 });
}
private static void UpgradeDB16(SQLiteConnectionED conn)
{
string query = "CREATE TABLE Bookmarks (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , StarName TEXT, x double NOT NULL, y double NOT NULL, z double NOT NULL, Time DATETIME NOT NULL, Heading TEXT, Note TEXT NOT Null )";
PerformUpgrade(conn, 16, true, true, new[] { query });
}
private static void UpgradeUserDB102(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE Commanders (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, EdsmApiKey TEXT NOT NULL, NetLogDir TEXT, Deleted INTEGER NOT NULL)";
PerformUpgrade(conn, 102, true, false, new[] { query1 });
}
private static void UpgradeDB17(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN id_edsm Integer";
string query4 = "ALTER TABLE Distances ADD COLUMN id_edsm Integer";
string query5 = "CREATE INDEX Distances_EDSM_ID_Index ON Distances (id_edsm ASC)";
string query6 = "Update VisitedSystems set x=null, y=null, z=null where x=0 and y=0 and z=0 and name!=\"Sol\"";
PerformUpgrade(conn, 17, true, true, new[] { query1,query4,query5,query6 }, () =>
{
PutSettingString("EDSMLastSystems", "2010 - 01 - 01 00:00:00", conn); // force EDSM sync..
PutSettingString("EDDBSystemsTime", "0", conn); // force EDDB
PutSettingString("EDSCLastDist", "2010-01-01 00:00:00", conn); // force distances
});
}
public static string GetSettingString(string key, string defaultvalue, SQLiteConnectionED cn)
{
try
{
using (DbCommand cmd = cn.CreateCommand("SELECT ValueString from Register WHERE ID = @ID"))
{
cmd.AddParameterWithValue("@ID", key);
object ob = SQLScalar(cn, cmd);
if (ob == null)
return defaultvalue;
if (ob == System.DBNull.Value)
return defaultvalue;
string val = (string)ob;
return val;
}
}
catch
{
return defaultvalue;
}
}
private static void UpgradeDB18(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE VisitedSystems ADD COLUMN id_edsm_assigned Integer";
string query2 = "CREATE INDEX VisitedSystems_id_edsm_assigned ON VisitedSystems (id_edsm_assigned)";
string query3 = "CREATE INDEX VisitedSystems_position ON VisitedSystems (X, Y, Z)";
PerformUpgrade(conn, 18, true, true, new[] { query1, query2, query3 });
}
public static bool PutSettingString(string key, string strvalue , SQLiteConnectionED cn )
{
try
{
if (keyExists(key,cn))
{
using (DbCommand cmd = cn.CreateCommand("Update Register set ValueString = @ValueString Where ID=@ID"))
{
cmd.AddParameterWithValue("@ID", key);
cmd.AddParameterWithValue("@ValueString", strvalue);
SQLNonQueryText(cn, cmd);
return true;
}
}
else
{
using (DbCommand cmd = cn.CreateCommand("Insert into Register (ID, ValueString) values (@ID, @valint)"))
{
cmd.AddParameterWithValue("@ID", key);
cmd.AddParameterWithValue("@valint", strvalue);
SQLNonQueryText(cn, cmd);
return true;
}
}
}
catch
{
return false;
}
}
private static void UpgradeDB19(SQLiteConnectionED conn)
{
string query1 = "CREATE TABLE SystemAliases (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT, id_edsm INTEGER, id_edsm_mergedto INTEGER)";
string query2 = "CREATE INDEX SystemAliases_name ON SystemAliases (name)";
string query3 = "CREATE UNIQUE INDEX SystemAliases_id_edsm ON SystemAliases (id_edsm)";
string query4 = "CREATE INDEX SystemAliases_id_edsm_mergedto ON SystemAliases (id_edsm_mergedto)";
PerformUpgrade(conn, 19, true, true, new[] { query1, query2, query3, query4 });
}
