private static Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> GetEdsmSystemsLite(SQLiteConnectionSystem cn)
{
Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> systemsByEdsmId = new Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase>();
using (DbCommand cmd = cn.CreateCommand("SELECT s.id, s.EdsmId, n.Name, s.x, s.y, s.z, s.UpdateTimestamp, s.gridid, s.randomid FROM EdsmSystems s JOIN SystemNames n ON n.EdsmId = s.EdsmId"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
EDDiscovery2.DB.InMemory.SystemClassBase sys = new EDDiscovery2.DB.InMemory.SystemClassBase
{
id = (long)reader["id"],
name = (string)reader["name"]
};
string searchname = sys.name.ToLower();
if (System.DBNull.Value == reader["x"])
{
sys.x = double.NaN;
sys.y = double.NaN;
sys.z = double.NaN;
}
else
{
sys.x = ((double)(long)reader["x"]) / XYZScalar;
sys.y = ((double)(long)reader["y"]) / XYZScalar;
sys.z = ((double)(long)reader["z"]) / XYZScalar;
}
sys.id_edsm = (long)reader["EdsmId"];
systemsByEdsmId[sys.id_edsm] = sys;
sys.gridid = reader["gridid"] == DBNull.Value ? 0 : (int)((long)reader["gridid"]);
sys.randomid = reader["randomid"] == DBNull.Value ? 0 : (int)((long)reader["randomid"]);
}
}
}
return systemsByEdsmId;
}
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;
}
