public static Dictionary <string, RegisterEntry> EarlyGetRegister() { Dictionary <string, RegisterEntry> reg = new Dictionary <string, RegisterEntry>(); if (File.Exists(GetSQLiteDBFile(EDDSqlDbSelection.EDDUser))) { try { using (SQLiteConnectionUser conn = new SQLiteConnectionUser(true, true, EDDbAccessMode.Reader)) { conn.GetRegister(reg); } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine($"Unable to read register table from EDDUser.sqlite\n{ex.ToString()}"); } } else { reg = SQLiteConnectionOld.EarlyGetRegister(); } return(reg); }
private static void InitializeDatabase() { string dbv4file = SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDiscovery); string dbuserfile = SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDUser); string dbsystemsfile = SQLiteConnectionED.GetSQLiteDBFile(EDDSqlDbSelection.EDDSystem); DbFactory = GetSqliteProviderFactory(); try { bool fileexist = File.Exists(dbv4file); bool UseV5Databases; UseV5Databases = File.Exists(dbuserfile); if (!fileexist && UseV5Databases==false) // no file, create it SQLiteConnection.CreateFile(dbv4file); if (UseV5Databases == false) { using (var conn = new SQLiteConnectionOld()) { if (!fileexist) // first time, create the register ExecuteQuery(conn, "CREATE TABLE Register (ID TEXT PRIMARY KEY NOT NULL UNIQUE , \"ValueInt\" INTEGER, \"ValueDouble\" DOUBLE, \"ValueString\" TEXT, \"ValueBlob\" BLOB)"); UpgradeDB(conn); // upgrade it } SplitDataBase(); } using (var conn = new SQLiteConnectionUser()) { UpgradeUserDB(conn); // upgrade it } using (var conn = new SQLiteConnectionSystem()) { UpgradeSystemsDB(conn); // upgrade it } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message, "Error creating data base file, Exception", System.Windows.Forms.MessageBoxButtons.OK); } }
// DONE purposely without using any VisitedSystem code.. so we can blow it away later. public static void TranferVisitedSystemstoJournalTable() { List<Object[]> ehl = new List<Object[]>(); Dictionary<string, Dictionary<string, double>> dists = new Dictionary<string, Dictionary<string, double>>(StringComparer.CurrentCultureIgnoreCase); List<EDDiscovery2.DB.TravelLogUnit> tlus = EDDiscovery2.DB.TravelLogUnit.GetAll().Where(t => t.type == 1).ToList(); using (SQLiteConnectionOld conn = new SQLiteConnectionOld()) { // 0 1 2 using (DbCommand cmd = conn.CreateCommand("SELECT NameA, NameB, Dist FROM Distances WHERE Status >= 1")) // any distance pairs okay { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { object[] vals = new object[3]; reader.GetValues(vals); string namea = (string)vals[0]; string nameb = (string)vals[1]; double dist = (double)vals[2]; if (!dists.ContainsKey(namea)) { dists[namea] = new Dictionary<string, double>(StringComparer.CurrentCultureIgnoreCase); } dists[namea][nameb] = dist; if (!dists.ContainsKey(nameb)) { dists[nameb] = new Dictionary<string, double>(StringComparer.CurrentCultureIgnoreCase); } dists[nameb][namea] = dist; } } } // 0 1 2 3 4 5 6 7 8 9 using (DbCommand cmd = conn.CreateCommand("Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z,id_edsm_assigned From VisitedSystems Order By Time")) { using (DbDataReader reader = cmd.ExecuteReader()) { string prev = ""; while (reader.Read()) { Object[] array = new Object[17]; reader.GetValues(array); // love this call. string tluname = (string)array[2]; // 2 is in terms of its name.. look it up EDDiscovery2.DB.TravelLogUnit tlu = tlus.Find(x => x.Name.Equals(tluname, StringComparison.InvariantCultureIgnoreCase)); array[15] = (tlu != null) ? (long)tlu.id : 0; // even if we don't find it, tlu may be screwed up, still want to import array[16] = null; if (prev.Length>0 && dists.ContainsKey((string)array[0])) { Dictionary<string, double> _dists = dists[(string)array[0]]; if (_dists.ContainsKey(prev)) { array[16] = _dists[prev]; } } ehl.Add(array); prev = (string)array[0]; } } } } using (SQLiteConnectionUserUTC conn = new SQLiteConnectionUserUTC()) { using (DbTransaction txn = conn.BeginTransaction()) { foreach (Object[] array in ehl) { using (DbCommand cmd = conn.CreateCommand( "Insert into JournalEntries (TravelLogId,CommanderId,EventTypeId,EventType,EventTime,EventData,EdsmId,Synced) " + "values (@tli,@cid,@eti,@et,@etime,@edata,@edsmid,@synced)", txn)) { cmd.AddParameterWithValue("@tli", (long)array[15]); cmd.AddParameterWithValue("@cid", (long)array[3]); cmd.AddParameterWithValue("@eti", EDDiscovery.EliteDangerous.JournalTypeEnum.FSDJump); cmd.AddParameterWithValue("@et", "FSDJump"); cmd.AddParameterWithValue("@etime", (DateTime)array[1]); JObject je = new JObject(); je["timestamp"] = DateTime.SpecifyKind((DateTime)array[1], DateTimeKind.Local).ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); je["event"] = "FSDJump"; je["StarSystem"] = ((string)array[0]); if (System.DBNull.Value != array[6] && System.DBNull.Value != array[7] && System.DBNull.Value != array[8]) { je["StarPos"] = new JArray() {array[6], array[7], array[8] }; } if (array[16] != null) { je["JumpDist"] = (double)array[16]; } je["EDDMapColor"] = ((long)array[5]); cmd.AddParameterWithValue("@edata", je.ToString()); // order number - look at the dbcommand above long edsmid = 0; if (System.DBNull.Value != array[9]) edsmid = (long)array[9]; cmd.AddParameterWithValue("@edsmid", edsmid); // order number - look at the dbcommand above cmd.AddParameterWithValue("@synced", ((bool)array[4] == true) ? 1 : 0); SQLiteDBClass.SQLNonQueryText(conn, cmd); } } txn.Commit(); } } }
public static void TranferVisitedSystemstoJournalTable() // DONE purposely without using any VisitedSystem code.. so we can blow it away later. { List <Object[]> ehl = new List <Object[]>(); Dictionary <string, Dictionary <string, double> > dists = new Dictionary <string, Dictionary <string, double> >(StringComparer.CurrentCultureIgnoreCase); List <EDDiscovery.DB.TravelLogUnit> tlus = EDDiscovery.DB.TravelLogUnit.GetAll().Where(t => t.type == 1).ToList(); using (SQLiteConnectionOld conn = new SQLiteConnectionOld()) { // 0 1 2 using (DbCommand cmd = conn.CreateCommand("SELECT NameA, NameB, Dist FROM Distances WHERE Status >= 1")) // any distance pairs okay { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { object[] vals = new object[3]; reader.GetValues(vals); string namea = (string)vals[0]; string nameb = (string)vals[1]; double dist = (double)vals[2]; if (!dists.ContainsKey(namea)) { dists[namea] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase); } dists[namea][nameb] = dist; if (!dists.ContainsKey(nameb)) { dists[nameb] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase); } dists[nameb][namea] = dist; } } } int olddbver = SQLiteConnectionOld.GetSettingInt("DBVer", 1); if (olddbver < 7) // 2.5.2 { System.Diagnostics.Trace.WriteLine("Database too old - unable to migrate travel log"); return; } string query; if (olddbver < 8) // 2.5.6 { query = "Select Name,Time,Unit,Commander,edsm_sync, -65536 AS Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time"; } else if (olddbver < 14) // 3.2.1 { query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time"; } else if (olddbver < 18) // 4.0.2 { query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z, NULL AS id_edsm_assigned From VisitedSystems Order By Time"; } else { // 0 1 2 3 4 5 6 7 8 9 query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z,id_edsm_assigned From VisitedSystems Order By Time"; } using (DbCommand cmd = conn.CreateCommand(query)) { using (DbDataReader reader = cmd.ExecuteReader()) { string prev = ""; while (reader.Read()) { Object[] array = new Object[17]; reader.GetValues(array); // love this call. string tluname = (string)array[2]; // 2 is in terms of its name.. look it up if (tluname.StartsWith("EDSM-")) // Don't migrate the entries that were synced from EDSM { // We can sync them from EDSM later. continue; } EDDiscovery.DB.TravelLogUnit tlu = tlus.Find(x => x.Name.Equals(tluname, StringComparison.InvariantCultureIgnoreCase)); array[15] = (tlu != null) ? (long)tlu.id : 0; // even if we don't find it, tlu may be screwed up, still want to import array[16] = null; if (prev.Length > 0 && dists.ContainsKey((string)array[0])) { Dictionary <string, double> _dists = dists[(string)array[0]]; if (_dists.ContainsKey(prev)) { array[16] = _dists[prev]; } } ehl.Add(array); prev = (string)array[0]; } } } } using (SQLiteConnectionUser conn = new SQLiteConnectionUser(utc: true)) { using (DbTransaction txn = conn.BeginTransaction()) { foreach (Object[] array in ehl) { using (DbCommand cmd = conn.CreateCommand( "Insert into JournalEntries (TravelLogId,CommanderId,EventTypeId,EventType,EventTime,EventData,EdsmId,Synced) " + "values (@tli,@cid,@eti,@et,@etime,@edata,@edsmid,@synced)", txn)) { cmd.AddParameterWithValue("@tli", (long)array[15]); cmd.AddParameterWithValue("@cid", (long)array[3]); cmd.AddParameterWithValue("@eti", EDDiscovery.EliteDangerous.JournalTypeEnum.FSDJump); cmd.AddParameterWithValue("@et", "FSDJump"); JObject je = new JObject(); DateTime eventtime = DateTime.SpecifyKind((DateTime)array[1], DateTimeKind.Local).ToUniversalTime(); je["timestamp"] = eventtime.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); je["event"] = "FSDJump"; je["StarSystem"] = ((string)array[0]); if (System.DBNull.Value != array[6] && System.DBNull.Value != array[7] && System.DBNull.Value != array[8]) { je["StarPos"] = new JArray() { array[6], array[7], array[8] }; } if (array[16] != null) { je["JumpDist"] = (double)array[16]; } je["EDDMapColor"] = ((long)array[5]); cmd.AddParameterWithValue("@etime", eventtime); cmd.AddParameterWithValue("@edata", je.ToString()); // order number - look at the dbcommand above long edsmid = 0; if (System.DBNull.Value != array[9]) { edsmid = (long)array[9]; } cmd.AddParameterWithValue("@edsmid", edsmid); // order number - look at the dbcommand above cmd.AddParameterWithValue("@synced", ((bool)array[4] == true) ? 1 : 0); SQLiteDBClass.SQLNonQueryText(conn, cmd); } } txn.Commit(); } } }
private static void UpdateSchema() { using (SQLiteConnectionOld cn = new SQLiteConnectionOld()) { UpdateDbSchema(cn, Schema.EDDiscovery); bool id_edsm_isset = false; // Reset EDSM / EDDB update time if no systems have their id_edsm set using (DbCommand cmd = cn.CreateCommand("SELECT COUNT(EdsmId) FROM EdsmSystems")) { id_edsm_isset = (long)cmd.ExecuteScalar() != 0; } if (!id_edsm_isset) { System.Diagnostics.Trace.WriteLine("Resetting EDSM and EDDB last update time"); PutSettingString("EDSMLastSystems", "2010-01-01 00:00:00", cn); // force EDSM sync.. PutSettingString("EDDBSystemsTime", "0", cn); // force EDDB PutSettingString("EDSCLastDist", "2010-01-01 00:00:00", cn); // force distances } // Null out any coordinates where (x,y,z) = (0,0,0) and the system is not Sol using (DbCommand cmd = cn.CreateCommand("Update VisitedSystems set x=null, y=null, z=null where x=0 and y=0 and z=0 and name!=\"Sol\"")) { cmd.ExecuteNonQuery(); } } using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { UpdateDbSchema(cn, Schema.EDDSystem); } using (SQLiteConnectionUser cn = new SQLiteConnectionUser()) { UpdateDbSchema(cn, Schema.EDDUser); } }