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 }); }