public static void ReplaceSystemsTable()
{
using (var slock = new SQLiteConnectionSystem.SchemaLock())
{
using (var conn = new SQLiteConnectionSystem())
{
DropSystemsTableIndexes();
using (var txn = conn.BeginTransaction())
{
SQLiteDBClass.ExecuteQuery(conn, "DROP TABLE IF EXISTS Systems");
SQLiteDBClass.ExecuteQuery(conn, "DROP TABLE IF EXISTS EdsmSystems");
SQLiteDBClass.ExecuteQuery(conn, "DROP TABLE IF EXISTS SystemNames");
SQLiteDBClass.ExecuteQuery(conn, "ALTER TABLE EdsmSystems_temp RENAME TO EdsmSystems");
SQLiteDBClass.ExecuteQuery(conn, "ALTER TABLE SystemNames_temp RENAME TO SystemNames");
txn.Commit();
}
SQLiteDBClass.ExecuteQuery(conn, "VACUUM");
CreateSystemsTableIndexes();
}
}
}
public static void ReplaceSystemsTable()
{
using (var slock = new SQLiteConnectionED.SchemaLock())
{
using (var conn = new SQLiteConnectionSystem())
{
DropSystemsTableIndexes();
using (var txn = conn.BeginTransaction())
{
ExecuteQuery(conn, "DROP TABLE IF EXISTS Systems");
ExecuteQuery(conn, "DROP TABLE IF EXISTS EdsmSystems");
ExecuteQuery(conn, "DROP TABLE IF EXISTS SystemNames");
ExecuteQuery(conn, "ALTER TABLE EdsmSystems_temp RENAME TO EdsmSystems");
ExecuteQuery(conn, "ALTER TABLE SystemNames_temp RENAME TO SystemNames");
txn.Commit();
}
ExecuteQuery(conn, "VACUUM");
CreateSystemsTableIndexes();
}
}
}
private static long DoParseEDSMUpdateSystemsReader(JsonTextReader jr, ref string date, ref bool outoforder, SQLiteConnectionSystem cn, EDDiscoveryForm discoveryform, Func<bool> cancelRequested, Action<int, string> reportProgress, bool useCache = true, bool useTempSystems = false)
{
DateTime maxdate;
if (!DateTime.TryParse(date, CultureInfo.InvariantCulture, DateTimeStyles.None, out maxdate))
{
maxdate = new DateTime(2010, 1, 1);
}
Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> systemsByEdsmId = useCache ? GetEdsmSystemsLite(cn) : new Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase>();
int count = 0;
int updatecount = 0;
int insertcount = 0;
Random rnd = new Random();
int[] histogramsystems = new int[50000];
string sysnamesTableName = useTempSystems ? "SystemNames_temp" : "SystemNames";
string edsmsysTableName = useTempSystems ? "EdsmSystems_temp" : "EdsmSystems";
Stopwatch sw = Stopwatch.StartNew();
while (!cancelRequested())
{
bool jr_eof = false;
List<JObject> objs = new List<JObject>();
while (!cancelRequested())
{
if (jr.Read())
{
if (jr.TokenType == JsonToken.StartObject)
{
objs.Add(JObject.Load(jr));
if (objs.Count >= 10000)
{
break;
}
}
}
else
{
jr_eof = true;
break;
}
}
using (DbTransaction txn = cn.BeginTransaction())
{
DbCommand updateNameCmd = null;
DbCommand updateSysCmd = null;
DbCommand insertNameCmd = null;
DbCommand insertSysCmd = null;
DbCommand selectSysCmd = null;
DbCommand selectNameCmd = null;
try
{
updateNameCmd = cn.CreateCommand("UPDATE SystemNames SET Name=@Name WHERE EdsmId=@EdsmId", txn);
updateNameCmd.AddParameter("@Name", DbType.String);
updateNameCmd.AddParameter("@EdsmId", DbType.Int64);
updateSysCmd = cn.CreateCommand("UPDATE EdsmSystems SET X=@X, Y=@Y, Z=@Z, UpdateTimestamp=@UpdateTimestamp, VersionTimestamp=@VersionTimestamp, GridId=@GridId, RandomId=@RandomId WHERE EdsmId=@EdsmId", txn);
updateSysCmd.AddParameter("@X", DbType.Int64);
updateSysCmd.AddParameter("@Y", DbType.Int64);
updateSysCmd.AddParameter("@Z", DbType.Int64);
updateSysCmd.AddParameter("@UpdateTimestamp", DbType.Int64);
updateSysCmd.AddParameter("@VersionTimestamp", DbType.Int64);
updateSysCmd.AddParameter("@GridId", DbType.Int64);
updateSysCmd.AddParameter("@RandomId", DbType.Int64);
updateSysCmd.AddParameter("@EdsmId", DbType.Int64);
insertNameCmd = cn.CreateCommand("INSERT INTO " + sysnamesTableName + " (Name, EdsmId) VALUES (@Name, @EdsmId)", txn);
insertNameCmd.AddParameter("@Name", DbType.String);
insertNameCmd.AddParameter("@EdsmId", DbType.Int64);
insertSysCmd = cn.CreateCommand("INSERT INTO " + edsmsysTableName + " (EdsmId, X, Y, Z, CreateTimestamp, UpdateTimestamp, VersionTimestamp, GridId, RandomId) VALUES (@EdsmId, @X, @Y, @Z, @CreateTimestamp, @UpdateTimestamp, @VersionTimestamp, @GridId, @RandomId)", txn);
insertSysCmd.AddParameter("@EdsmId", DbType.Int64);
insertSysCmd.AddParameter("@X", DbType.Int64);
insertSysCmd.AddParameter("@Y", DbType.Int64);
insertSysCmd.AddParameter("@Z", DbType.Int64);
insertSysCmd.AddParameter("@CreateTimestamp", DbType.Int64);
insertSysCmd.AddParameter("@UpdateTimestamp", DbType.Int64);
insertSysCmd.AddParameter("@VersionTimestamp", DbType.Int64);
insertSysCmd.AddParameter("@GridId", DbType.Int64);
insertSysCmd.AddParameter("@RandomId", DbType.Int64);
selectSysCmd = cn.CreateCommand("SELECT Id, X, Y, Z, GridId, RandomId FROM EdsmSystems WHERE EdsmId=@EdsmId");
selectSysCmd.AddParameter("@EdsmId", DbType.Int64);
selectNameCmd = cn.CreateCommand("SELECT Name FROM SystemNames WHERE EdsmId = @EdsmId");
selectNameCmd.AddParameter("@EdsmId", DbType.Int64);
IEnumerator<JObject> jo_enum = objs.GetEnumerator();
while (!cancelRequested())
{
if (!jo_enum.MoveNext())
{
reportProgress(-1, $"Syncing EDSM systems: {count} processed, {insertcount} new systems, {updatecount} updated systems");
txn.Commit();
if (jr_eof)
{
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
Console.WriteLine($"Import took {sw.ElapsedMilliseconds}ms");
for (int id = 0; id < histogramsystems.Length; id++)
{
if (histogramsystems[id] != 0)
Console.WriteLine("Id " + id + " count " + histogramsystems[id]);
}
return updatecount + insertcount;
}
break;
}
JObject jo = jo_enum.Current;
JObject coords = (JObject)jo["coords"];
if (coords != null && (coords["x"].Type == JTokenType.Float || coords["x"].Type == JTokenType.Integer))
{
double x = coords["x"].Value<double>();
double y = coords["y"].Value<double>();
double z = coords["z"].Value<double>();
long edsmid = jo["id"].Value<long>();
string name = jo["name"].Value<string>();
int gridid = GridId.Id(x, z);
int randomid = rnd.Next(0, 99);
DateTime updatedate = jo["date"].Value<DateTime>();
histogramsystems[gridid]++;
if (updatedate > maxdate)
maxdate = updatedate;
else if (updatedate < maxdate - TimeSpan.FromHours(1))
outoforder = true;
EDDiscovery2.DB.InMemory.SystemClassBase dbsys = null;
if (!useTempSystems)
{
if (useCache && systemsByEdsmId.ContainsKey(edsmid))
dbsys = systemsByEdsmId[edsmid];
if (!useCache)
{
selectSysCmd.Parameters["@EdsmId"].Value = edsmid;
using (DbDataReader reader = selectSysCmd.ExecuteReader())
{
if (reader.Read())
{
dbsys = new EDDiscovery2.DB.InMemory.SystemClassBase
{
id = (long)reader["id"],
id_edsm = edsmid
};
if (System.DBNull.Value == reader["x"])
{
dbsys.x = double.NaN;
dbsys.y = double.NaN;
dbsys.z = double.NaN;
}
else
{
dbsys.x = ((double)(long)reader["X"]) / XYZScalar;
dbsys.y = ((double)(long)reader["Y"]) / XYZScalar;
dbsys.z = ((double)(long)reader["Z"]) / XYZScalar;
}
dbsys.id_edsm = edsmid;
dbsys.gridid = reader["GridId"] == DBNull.Value ? 0 : (int)((long)reader["GridId"]);
dbsys.randomid = reader["RandomId"] == DBNull.Value ? 0 : (int)((long)reader["RandomId"]);
}
}
if (dbsys != null)
{
selectNameCmd.Parameters["@EdsmId"].Value = edsmid;
using (DbDataReader reader = selectNameCmd.ExecuteReader())
{
if (reader.Read())
{
dbsys.name = (string)reader["Name"];
}
}
}
}
}
if (dbsys != null)
{
// see if EDSM data changed..
if (!dbsys.name.Equals(name))
{
updateNameCmd.Parameters["@Name"].Value = name;
updateNameCmd.Parameters["@EdsmId"].Value = edsmid;
updateNameCmd.ExecuteNonQuery();
}
if (Math.Abs(dbsys.x - x) > 0.01 ||
Math.Abs(dbsys.y - y) > 0.01 ||
Math.Abs(dbsys.z - z) > 0.01 ||
dbsys.gridid != gridid) // position changed
{
updateSysCmd.Parameters["@X"].Value = (long)(x * XYZScalar);
updateSysCmd.Parameters["@Y"].Value = (long)(y * XYZScalar);
updateSysCmd.Parameters["@Z"].Value = (long)(z * XYZScalar);
updateSysCmd.Parameters["@UpdateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds;
updateSysCmd.Parameters["@VersionTimestamp"].Value = DateTime.UtcNow.Subtract(new DateTime(2015, 1, 1)).TotalSeconds;
updateSysCmd.Parameters["@GridId"].Value = gridid;
updateSysCmd.Parameters["@RandomId"].Value = randomid;
updateSysCmd.Parameters["@EdsmId"].Value = edsmid;
updateSysCmd.ExecuteNonQuery();
updatecount++;
}
}
else // not in database..
{
insertNameCmd.Parameters["@Name"].Value = name;
insertNameCmd.Parameters["@EdsmId"].Value = edsmid;
insertNameCmd.ExecuteNonQuery();
insertSysCmd.Parameters["@EdsmId"].Value = edsmid;
insertSysCmd.Parameters["@X"].Value = (long)(x * XYZScalar);
insertSysCmd.Parameters["@Y"].Value = (long)(y * XYZScalar);
insertSysCmd.Parameters["@Z"].Value = (long)(z * XYZScalar);
insertSysCmd.Parameters["@CreateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds;
insertSysCmd.Parameters["@UpdateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds;
insertSysCmd.Parameters["@VersionTimestamp"].Value = DateTime.UtcNow.Subtract(new DateTime(2015, 1, 1)).TotalSeconds;
insertSysCmd.Parameters["@GridId"].Value = gridid;
insertSysCmd.Parameters["@RandomId"].Value = randomid;
insertSysCmd.ExecuteNonQuery();
insertcount++;
}
}
count++;
}
}
finally
{
if (updateNameCmd != null) updateNameCmd.Dispose();
if (updateSysCmd != null) updateSysCmd.Dispose();
if (insertNameCmd != null) insertNameCmd.Dispose();
if (insertSysCmd != null) insertSysCmd.Dispose();
if (selectSysCmd != null) selectSysCmd.Dispose();
}
}
}
if (cancelRequested())
{
throw new OperationCanceledException();
}
return updatecount + insertcount;
}
public static void RemoveHiddenSystems(string json)
{
JsonTextReader jr = new JsonTextReader(new StringReader(json));
using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db
{
using (DbTransaction txn = cn2.BeginTransaction())
{
DbCommand infoinscmd = null;
DbCommand infodelcmd = null;
DbCommand namedelcmd = null;
try
{
infoinscmd = cn2.CreateCommand("INSERT OR IGNORE INTO SystemAliases (name, id_edsm, id_edsm_mergedto) VALUES (@name, @id_edsm, @id_edsm_mergedto)", txn);
infoinscmd.AddParameter("@name", DbType.String);
infoinscmd.AddParameter("@id_edsm", DbType.Int64);
infoinscmd.AddParameter("@id_edsm_mergedto", DbType.Int64);
infodelcmd = cn2.CreateCommand("DELETE FROM EdsmSystems WHERE EdsmId=@EdsmId", txn);
infodelcmd.AddParameter("@EdsmId", DbType.Int64);
namedelcmd = cn2.CreateCommand("DELETE FROM SystemNames WHERE EdsmId=@EdsmId", txn);
namedelcmd.AddParameter("@EdsmId", DbType.Int64);
while (jr.Read())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
long edsmid = (long)jo["id"];
string name = (string)jo["system"];
string action = (string)jo["action"];
long mergedto = 0;
if (jo["mergedTo"] != null)
{
mergedto = (long)jo["mergedTo"];
}
Console.Write("Remove " + edsmid);
infodelcmd.Parameters["@EdsmId"].Value = edsmid;
infodelcmd.ExecuteNonQuery();
namedelcmd.Parameters["@EdsmId"].Value = edsmid;
namedelcmd.ExecuteNonQuery();
if (mergedto > 0)
{
infoinscmd.Parameters["@name"].Value = name;
infoinscmd.Parameters["@id_edsm"].Value = edsmid;
infoinscmd.Parameters["@id_edsm_mergedto"].Value = mergedto;
infoinscmd.ExecuteNonQuery();
}
}
}
txn.Commit();
}
finally
{
if (infoinscmd != null) infoinscmd.Dispose();
if (infodelcmd != null) infodelcmd.Dispose();
if (namedelcmd != null) namedelcmd.Dispose();
}
}
}
}
public static long ParseEDDBUpdateSystems(string filename, Action<string> logline)
{
StreamReader sr = new StreamReader(filename); // read directly from file..
if (sr == null)
return 0;
JsonTextReader jr = new JsonTextReader(sr);
if (jr == null)
return 0;
int updated = 0;
int inserted = 0;
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db
{
DbCommand selectCmd = null;
DbCommand insertCmd = null;
DbCommand updateCmd = null;
DbCommand updateSysCmd = null;
using (DbTransaction txn = cn.BeginTransaction())
{
try
{
selectCmd = cn.CreateCommand("SELECT EddbId, Population, EddbUpdatedAt FROM EddbSystems WHERE EdsmId = @EdsmId LIMIT 1", txn); // 1 return matching ID
selectCmd.AddParameter("@Edsmid", DbType.Int64);
insertCmd = cn.CreateCommand("INSERT INTO EddbSystems (EdsmId, EddbId, Name, Faction, Population, GovernmentId, AllegianceId, State, Security, PrimaryEconomyId, NeedsPermit, EddbUpdatedAt) " +
"VALUES (@EdsmId, @EddbId, @Name, @Faction, @Population, @GovernmentId, @AllegianceId, @State, @Security, @PrimaryEconomyid, @NeedsPermit, @EddbUpdatedAt)", txn);
insertCmd.AddParameter("@EdsmId", DbType.Int64);
insertCmd.AddParameter("@EddbId", DbType.Int64);
insertCmd.AddParameter("@Name", DbType.String);
insertCmd.AddParameter("@Faction", DbType.String);
insertCmd.AddParameter("@Population", DbType.Int64);
insertCmd.AddParameter("@GovernmentId", DbType.Int64);
insertCmd.AddParameter("@AllegianceId", DbType.Int64);
insertCmd.AddParameter("@State", DbType.Int64);
insertCmd.AddParameter("@Security", DbType.Int64);
insertCmd.AddParameter("@PrimaryEconomyId", DbType.Int64);
insertCmd.AddParameter("@NeedsPermit", DbType.Int64);
insertCmd.AddParameter("@EddbUpdatedAt", DbType.Int64);
updateCmd = cn.CreateCommand("UPDATE EddbSystems SET EddbId=@EddbId, Name=@Name, Faction=@Faction, Population=@Population, GovernmentId=@GovernmentId, AllegianceId=@AllegianceId, State=@State, Security=@Security, PrimaryEconomyId=@PrimaryEconomyId, NeedsPermit=@NeedsPermit, EddbUpdatedAt=@EddbUpdatedAt WHERE EdsmId=@EdsmId", txn);
updateCmd.AddParameter("@EdsmId", DbType.Int64);
updateCmd.AddParameter("@EddbId", DbType.Int64);
updateCmd.AddParameter("@Name", DbType.String);
updateCmd.AddParameter("@Faction", DbType.String);
updateCmd.AddParameter("@Population", DbType.Int64);
updateCmd.AddParameter("@GovernmentId", DbType.Int64);
updateCmd.AddParameter("@AllegianceId", DbType.Int64);
updateCmd.AddParameter("@State", DbType.Int64);
updateCmd.AddParameter("@Security", DbType.Int64);
updateCmd.AddParameter("@PrimaryEconomyId", DbType.Int64);
updateCmd.AddParameter("@NeedsPermit", DbType.Int64);
updateCmd.AddParameter("@EddbUpdatedAt", DbType.Int64);
updateSysCmd = cn.CreateCommand("UPDATE EdsmSystems SET EddbId=@EddbId WHERE EdsmId=@EdsmId");
updateSysCmd.AddParameter("@EdsmId", DbType.Int64);
updateSysCmd.AddParameter("@EddbId", DbType.Int64);
int c = 0;
int hasinfo = 0;
int lasttc = Environment.TickCount;
while (jr.Read())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
SystemClass system = new SystemClass(jo, SystemInfoSource.EDDB);
if (system.HasEDDBInformation) // screen out for speed any EDDB data with empty interesting fields
{
hasinfo++;
selectCmd.Parameters["@EdsmId"].Value = system.id_edsm; // EDDB carries EDSM ID, so find entry in dB
//DEBUGif ( c > 30000 ) Console.WriteLine("EDDB ID " + system.id_eddb + " EDSM ID " + system.id_edsm + " " + system.name + " Late info system");
long updated_at = 0;
long population = 0;
long eddbid = 0;
using (DbDataReader reader1 = selectCmd.ExecuteReader()) // if found (if not, we ignore EDDB system)
{
if (reader1.Read()) // its there.. check its got the right stuff in it.
{
eddbid = (long)reader1["EddbId"];
updated_at = (long)reader1["EddbUpdatedAt"];
population = (long)reader1["Population"];
}
}
updateSysCmd.Parameters["@EdsmId"].Value = system.id_edsm;
updateSysCmd.Parameters["@EddbId"].Value = system.id_eddb;
updateSysCmd.ExecuteNonQuery();
if (eddbid != 0)
{
if (updated_at != system.eddb_updated_at || population != system.population)
{
updateCmd.Parameters["@EddbId"].Value = system.id_eddb;
updateCmd.Parameters["@Name"].Value = system.name;
updateCmd.Parameters["@Faction"].Value = system.faction;
updateCmd.Parameters["@Population"].Value = system.population;
updateCmd.Parameters["@GovernmentId"].Value = system.government;
updateCmd.Parameters["@AllegianceId"].Value = system.allegiance;
updateCmd.Parameters["@State"].Value = system.state;
updateCmd.Parameters["@Security"].Value = system.security;
updateCmd.Parameters["@PrimaryEconomyId"].Value = system.primary_economy;
updateCmd.Parameters["@NeedsPermit"].Value = system.needs_permit;
updateCmd.Parameters["@EddbUpdatedAt"].Value = system.eddb_updated_at;
updateCmd.Parameters["@EdsmId"].Value = system.id_edsm;
updateCmd.ExecuteNonQuery();
updated++;
}
}
else
{
insertCmd.Parameters["@EdsmId"].Value = system.id_edsm;
insertCmd.Parameters["@EddbId"].Value = system.id_eddb;
insertCmd.Parameters["@Name"].Value = system.name;
insertCmd.Parameters["@Faction"].Value = system.faction;
insertCmd.Parameters["@Population"].Value = system.population;
insertCmd.Parameters["@GovernmentId"].Value = system.government;
insertCmd.Parameters["@AllegianceId"].Value = system.allegiance;
insertCmd.Parameters["@State"].Value = system.state;
insertCmd.Parameters["@Security"].Value = system.security;
insertCmd.Parameters["@PrimaryEconomyId"].Value = system.primary_economy;
insertCmd.Parameters["@NeedsPermit"].Value = system.needs_permit;
insertCmd.Parameters["@EddbUpdatedAt"].Value = system.eddb_updated_at;
insertCmd.ExecuteNonQuery();
inserted++;
}
}
else
{
//Console.WriteLine("EDDB ID " + system.id_eddb + " EDSM ID " + system.id_edsm + " " + system.name + " No info reject");
}
if (++c % 10000 == 0)
{
Console.WriteLine("EDDB Count " + c + " Delta " + (Environment.TickCount - lasttc) + " info " + hasinfo + " update " + updated + " new " + inserted);
lasttc = Environment.TickCount;
}
}
}
txn.Commit();
}
catch
{
MessageBox.Show("There is a problem using the EDDB systems file." + Environment.NewLine +
"Please perform a manual EDDB sync (see Admin menu) next time you run the program ", "EDDB Sync Error");
}
finally
{
if (selectCmd != null) selectCmd.Dispose();
if (updateCmd != null) updateCmd.Dispose();
if (insertCmd != null) insertCmd.Dispose();
}
}
}
return updated + inserted;
}
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date , bool removenonedsmids, Func<bool> cancelRequested, Action<int, string> reportProgress)
{
List<DistanceClass> toupdate = new List<DistanceClass>();
List<DistanceClass> newpairs = new List<DistanceClass>();
DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE"));
bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db
{
int c = 0;
DbCommand cmd = null;
int lasttc = Environment.TickCount;
try
{
cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching
while (jr.Read() && !cancelRequested())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
DistanceClass dc = new DistanceClass(jo);
if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0)
maxdate = dc.CreateTime;
if (++c % 10000 == 0)
{
Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count());
reportProgress(-1, $"Reading EDSM distances: {c} processed, {newpairs.Count} new, {toupdate.Count} to update");
lasttc = Environment.TickCount;
}
if (emptydatabase) // empty DB, just store..
{
newpairs.Add(dc);
}
else
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("id", dc.id_edsm);
using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there..
{
if (reader1.Read()) // its there..
{
DistanceClass dbdc = new DistanceClass(reader1);
// see if EDSM data changed..
if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05)
{
dbdc.NameA = dc.NameA;
dbdc.NameB = dc.NameB;
dbdc.Dist = dc.Dist;
toupdate.Add(dbdc);
}
}
else // not in database..
{
//Console.WriteLine("Add new system " + system.name);
newpairs.Add(dc);
}
}
}
}
}
}
catch
{
MessageBox.Show("There is a problem using the EDSM distance file." + Environment.NewLine +
"Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ", "ESDM Sync Error");
}
finally
{
if (cmd != null) cmd.Dispose();
}
}
if (cancelRequested())
{
return 0;
}
using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db
{
if (toupdate.Count > 0)
{
reportProgress(-1, $"Updating EDSM distances: {toupdate.Count} distances to update");
using (DbTransaction transaction = cn2.BeginTransaction())
{
foreach (DistanceClass dc in toupdate)
dc.Update(cn2, transaction);
transaction.Commit();
}
}
if (cancelRequested())
{
return toupdate.Count();
}
if (newpairs.Count > 0)
{
int count = 0;
while (count < newpairs.Count())
{
using (DbTransaction transaction = cn2.BeginTransaction())
{
while (count < newpairs.Count())
{
newpairs[count].Store(cn2, transaction);
if (++count % 10000 == 0)
break;
}
reportProgress(count * 100 / newpairs.Count, $"Adding EDSM distances: {count} added");
Console.WriteLine("EDSM Dist Store Count " + count);
transaction.Commit();
}
if (cancelRequested())
{
return toupdate.Count() + count;
}
}
}
if (removenonedsmids) // done on a full sync..
{
reportProgress(-1, "Removing distances without an ID");
Console.WriteLine("Delete old ones");
using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null"))
{
SQLiteDBClass.SQLNonQueryText(cn2, cmddel);
}
}
}
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
return toupdate.Count + newpairs.Count;
}
public static void RemoveHiddenSystems(string json)
{
JsonTextReader jr = new JsonTextReader(new StringReader(json));
using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db
{
using (DbTransaction txn = cn2.BeginTransaction())
{
while (jr.Read())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
long edsmid = (long)jo["id"];
string name = (string)jo["system"];
string action = (string)jo["action"];
long mergedto = 0;
if (jo["mergedTo"] != null)
{
mergedto = (long)jo["mergedTo"];
}
Console.Write("Remove " + edsmid);
using (DbCommand cmd = cn2.CreateCommand("DELETE FROM EdsmSystems WHERE EdsmId=@EdsmId", txn))
{
cmd.AddParameterWithValue("@EdsmId", edsmid);
cmd.ExecuteNonQuery();
}
if (mergedto > 0)
{
using (DbCommand cmd = cn2.CreateCommand("INSERT OR IGNORE INTO SystemNames (Name, EdsmId) VALUES (@Name, @EdsmId)", txn))
{
cmd.AddParameterWithValue("@Name", name);
cmd.AddParameterWithValue("@EdsmId", edsmid);
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn2.CreateCommand("INSERT OR IGNORE INTO SystemAliases (name, id_edsm, id_edsm_mergedto) VALUES (@name, @id_edsm, @id_edsm_mergedto)", txn))
{
cmd.AddParameterWithValue("@name", name);
cmd.AddParameterWithValue("@id_edsm", edsmid);
cmd.AddParameterWithValue("@id_edsm_mergedto", mergedto);
cmd.ExecuteNonQuery();
}
}
}
}
txn.Commit();
}
}
}
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date, bool removenonedsmids, Func <bool> cancelRequested, Action <int, string> reportProgress, Action <string> logline)
{
List <DistanceClass> toupdate = new List <DistanceClass>();
List <DistanceClass> newpairs = new List <DistanceClass>();
DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE"));
bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup
using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db
{
int c = 0;
DbCommand cmd = null;
int lasttc = Environment.TickCount;
try
{
cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching
while (jr.Read() && !cancelRequested())
{
if (jr.TokenType == JsonToken.StartObject)
{
JObject jo = JObject.Load(jr);
DistanceClass dc = new DistanceClass(jo);
if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0)
{
maxdate = dc.CreateTime;
}
if (++c % 10000 == 0)
{
Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count());
reportProgress(-1, $"Reading EDSM distances: {c} processed, {newpairs.Count} new, {toupdate.Count} to update");
lasttc = Environment.TickCount;
}
if (emptydatabase) // empty DB, just store..
{
newpairs.Add(dc);
}
else
{
cmd.Parameters.Clear();
cmd.AddParameterWithValue("id", dc.id_edsm);
using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there..
{
if (reader1.Read()) // its there..
{
DistanceClass dbdc = new DistanceClass(reader1);
// see if EDSM data changed..
if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05)
{
dbdc.NameA = dc.NameA;
dbdc.NameB = dc.NameB;
dbdc.Dist = dc.Dist;
toupdate.Add(dbdc);
}
}
else // not in database..
{
//Console.WriteLine("Add new system " + system.name);
newpairs.Add(dc);
}
}
}
}
}
}
catch
{
logline("There is a problem using the EDSM distance file." + Environment.NewLine +
"Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ");
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
if (cancelRequested())
{
return(0);
}
using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db
{
if (toupdate.Count > 0)
{
reportProgress(-1, $"Updating EDSM distances: {toupdate.Count} distances to update");
using (DbTransaction transaction = cn2.BeginTransaction())
{
foreach (DistanceClass dc in toupdate)
{
dc.Update(cn2, transaction);
}
transaction.Commit();
}
}
if (cancelRequested())
{
return(toupdate.Count());
}
if (newpairs.Count > 0)
{
int count = 0;
while (count < newpairs.Count())
{
using (DbTransaction transaction = cn2.BeginTransaction())
{
while (count < newpairs.Count())
{
newpairs[count].Store(cn2, transaction);
if (++count % 10000 == 0)
{
break;
}
}
reportProgress(count * 100 / newpairs.Count, $"Adding EDSM distances: {count} added");
Console.WriteLine("EDSM Dist Store Count " + count);
transaction.Commit();
}
if (cancelRequested())
{
return(toupdate.Count() + count);
}
}
}
if (removenonedsmids) // done on a full sync..
{
reportProgress(-1, "Removing distances without an ID");
Console.WriteLine("Delete old ones");
using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null"))
{
SQLiteDBClass.SQLNonQueryText(cn2, cmddel);
}
}
}
date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
return(toupdate.Count + newpairs.Count);
}
