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();
}
}
});
}
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 UpgradeSystemsDB11(SQLiteConnectionED conn)
{
//Default is Color.Red.ToARGB()
string query1 = "ALTER TABLE Systems ADD COLUMN FirstDiscovery BOOL";
PerformUpgrade(conn, 11, true, false, new[] { query1 });
}
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)";
PerformUpgrade(conn, 6, true, false, new[] {
query1, query2, query4, query5, query6, query7, query8, query9, query10,
query11, query12, query13, query14, query15, query16, query17
});
}
private static void UpgradeSystemsDB15(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Systems ADD COLUMN versiondate DATETIME";
string query2 = "UPDATE Systems SET versiondate = datetime('now')";
PerformUpgrade(conn, 15, true, false, 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 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)";
PerformUpgrade(conn, 102, true, false, new[] { query1, query2, query3 });
}
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 });
}
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();
}
}
});
}
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 });
}
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)";
PerformUpgrade(conn, 17, true, false, new[] { query1, query4, query5 });
}
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)";
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)";
PerformUpgrade(conn, 19, true, false, new[] { query1, query2, query3, query4 });
}
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 });
}
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";
PerformUpgrade(conn, 20, true, false, new[] { query1, query2 }, () =>
{
conn.PutSettingStringCN("EDSMLastSystems", "2010 - 01 - 01 00:00:00"); // force EDSM sync.. MUST do this manually, can't use main function as it needs internal one
});
}
public SQLiteCommandED(DbCommand cmd, SQLiteConnectionED conn, SQLiteTxnLockED<TConn> txnlock, DbTransaction txn = null)
{
_connection = conn;
_txnlock = txnlock;
InnerCommand = cmd;
if (txn != null)
{
SetTransaction(txn);
}
}
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..
});
}
private static void UpgradeUserDB108(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE Commanders ADD COLUMN JournalDir TEXT";
PerformUpgrade(conn, 108, true, false, new[] { query1 }, () =>
{
try
{
List <int> commandersToMigrate = new List <int>();
using (DbCommand cmd = conn.CreateCommand("SELECT Id, NetLogDir, JournalDir FROM Commanders"))
{
using (DbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int nr = Convert.ToInt32(rdr["Id"]);
object netlogdir = rdr["NetLogDir"];
object journaldir = rdr["JournalDir"];
if (netlogdir != DBNull.Value && journaldir == DBNull.Value)
{
string logdir = Convert.ToString(netlogdir);
if (logdir != null && System.IO.Directory.Exists(logdir) && System.IO.Directory.EnumerateFiles(logdir, "journal*.log").Any())
{
commandersToMigrate.Add(nr);
}
}
}
}
}
using (DbCommand cmd2 = conn.CreateCommand("UPDATE Commanders SET JournalDir=NetLogDir WHERE Id=@Nr"))
{
cmd2.AddParameter("@Nr", System.Data.DbType.Int32);
foreach (int nr in commandersToMigrate)
{
cmd2.Parameters["@Nr"].Value = nr;
cmd2.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine("UpgradeUser108 exception: " + ex.Message);
}
});
}
static public object SQLScalar(SQLiteConnectionED cn, DbCommand cmd)
{
object ret = null;
try
{
ret = cmd.ExecuteScalar();
return(ret);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQuery Exception: " + ex.Message);
throw;
}
}
static public 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;
}
}
///----------------------------
/// STATIC code helpers for other DB classes
public static DataSet SQLQueryText(SQLiteConnectionED cn, DbCommand cmd)
{
try
{
DataSet ds = new DataSet();
DbDataAdapter da = cn.CreateDataAdapter(cmd);
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlQuery Exception: " + ex.Message);
throw;
}
}
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 }, () =>
{
});
}
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 CreateTable(SQLiteConnectionED cn, string name, object schema)
{
List <string> columndefs = new List <string>();
EnumerateSchema(schema, (colname, colschema) =>
{
columndefs.Add($"{colname} {colschema}");
});
string createstmt = $"CREATE TABLE {name} ({String.Join(",", columndefs)})";
System.Diagnostics.Trace.WriteLine(createstmt);
using (DbCommand cmd = cn.CreateCommand(createstmt))
{
cmd.ExecuteNonQuery();
}
}
public static void Initialize()
{
InitializeIfNeeded(() =>
{
string dbv4file = SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDiscovery);
string dbuserfile = SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDUser);
if (File.Exists(dbv4file) && !File.Exists(dbuserfile))
{
File.Copy(dbv4file, dbuserfile);
}
using (SQLiteConnectionUser conn = new SQLiteConnectionUser(true, true, EDDbAccessMode.Writer))
{
UpgradeUserDB(conn);
}
});
}
private static void UpgradeUserDB105(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE TravelLogUnit ADD COLUMN CommanderId INTEGER REFERENCES Commanders(Id) ";
string query2 = "DROP TABLE IF EXISTS JournalEntries";
string query3 = "CREATE TABLE JournalEntries ( " +
"Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
"TravelLogId INTEGER NOT NULL REFERENCES TravelLogUnit(Id), " +
"CommanderId INTEGER NOT NULL DEFAULT 0," +
"EventTypeId INTEGER NOT NULL, " +
"EventType TEXT, " +
"EventTime DATETIME NOT NULL, " +
"EventData TEXT, " + //--JSON String of complete line" +
"EdsmId INTEGER, " + //--0 if not set yet." +
"Synced INTEGER " +
")";
PerformUpgrade(conn, 105, true, false, new[] { query1, query2, query3 });
}
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 UpgradeUserDB16(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, false, new[] { query });
}
private static void UpgradeUserDB104(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE SystemNote ADD COLUMN journalid Integer NOT NULL DEFAULT 0";
PerformUpgrade(conn, 104, true, false, new[] { query1 });
}
private static void UpgradeUserDB106(SQLiteConnectionED conn)
{
string query1 = "ALTER TABLE SystemNote ADD COLUMN EdsmId INTEGER NOT NULL DEFAULT -1";
PerformUpgrade(conn, 106, true, false, new[] { query1 });
}
