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