private static Dictionary <long, SystemClassBase> GetEdsmSystemsLite() { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: EDDbAccessMode.Reader)) { Dictionary <long, SystemClassBase> systemsByEdsmId = new Dictionary <long, 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()) { SystemClassBase sys = new 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"]) / SystemClassDB.XYZScalar; sys.Y = ((double)(long)reader["y"]) / SystemClassDB.XYZScalar; sys.Z = ((double)(long)reader["z"]) / SystemClassDB.XYZScalar; } sys.EDSMID = (long)reader["EdsmId"]; systemsByEdsmId[sys.EDSMID] = 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); } }
// returns no of updates + inserts, not no of items processed. Protect yourself against bad json private static long DoParseEDSMUpdateSystemsReader(JsonTextReader jr, bool[] grididallowed, ref DateTime maxdate, Func <bool> cancelRequested, Action <int, string> reportProgress, bool useCache = true, bool useTempSystems = false) { Dictionary <long, SystemClassBase> systemsByEdsmId = useCache ? GetEdsmSystemsLite() : new Dictionary <long, SystemClassBase>(); int count = 0; int updatecount = 0; int insertcount = 0; Random rnd = new Random(); string sysnamesTableName = useTempSystems ? "SystemNames_temp" : "SystemNames"; string edsmsysTableName = useTempSystems ? "EdsmSystems_temp" : "EdsmSystems"; Stopwatch sw = Stopwatch.StartNew(); const int BlockSize = 10000; while (!cancelRequested()) { bool jr_eof = false; List <EDSMDumpSystem> objs = new List <EDSMDumpSystem>(BlockSize); while (!cancelRequested()) { if (jr.Read()) { if (jr.TokenType == JsonToken.StartObject) { objs.Add(EDSMDumpSystem.Deserialize(jr)); if (objs.Count >= BlockSize) { break; } } } else { jr_eof = true; break; } } IEnumerator <EDSMDumpSystem> jo_enum = objs.GetEnumerator(); bool jo_enum_finished = false; while (!jo_enum_finished && !cancelRequested()) { int blkcount = 0; int oldinsertcnt = insertcount; int oldupdatecnt = updatecount; int oldcount = count; using (SQLiteTxnLockED <SQLiteConnectionSystem> tl = new SQLiteTxnLockED <SQLiteConnectionSystem>()) { tl.OpenWriter(); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: EDDbAccessMode.Writer)) { 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); while (!cancelRequested()) { if (!jo_enum.MoveNext()) { reportProgress(-1, $"Syncing EDSM systems: {count:N0} processed, {insertcount:N0} new systems, {updatecount:N0} updated systems"); txn.Commit(); if (jr_eof) { System.Diagnostics.Debug.WriteLine("Maximum date was " + maxdate.ToString()); System.Diagnostics.Debug.WriteLine($"Import took {sw.ElapsedMilliseconds}ms"); return(updatecount + insertcount); } jo_enum_finished = true; break; } else if (SQLiteConnectionSystem.IsReadWaiting) { if (blkcount < objs.Count * 3 / 4) // Let the reader barge in if we've processed less than 3/4 of the items { // Reset the counts, roll back the transaction, and let the reader through... insertcount = oldinsertcnt; updatecount = oldupdatecnt; count = oldcount; jo_enum.Reset(); txn.Rollback(); break; } } EDSMDumpSystem jo = jo_enum.Current; EDSMDumpSystemCoords coords = jo.coords; if (coords != null) { DateTime updatedate = DateTime.SpecifyKind(jo.date, DateTimeKind.Utc); if (updatedate > maxdate) // even if we reject it due to grid id, keep last date up to date { maxdate = updatedate; } double x = coords.x; double z = coords.z; int gridid = GridId.Id(x, z); if (grididallowed[gridid]) // if grid allows it to be added.. { //System.Diagnostics.Debug.WriteLine("Accept due to gridid " + gridid); double y = coords.y; long edsmid = jo.id; string name = jo.name; int randomid = rnd.Next(0, 99); 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 SystemClassBase { ID = (long)reader["id"], EDSMID = 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"]) / SystemClassDB.XYZScalar; dbsys.Y = ((double)(long)reader["Y"]) / SystemClassDB.XYZScalar; dbsys.Z = ((double)(long)reader["Z"]) / SystemClassDB.XYZScalar; } dbsys.EDSMID = 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 * SystemClassDB.XYZScalar); updateSysCmd.Parameters["@Y"].Value = (long)(y * SystemClassDB.XYZScalar); updateSysCmd.Parameters["@Z"].Value = (long)(z * SystemClassDB.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 * SystemClassDB.XYZScalar); insertSysCmd.Parameters["@Y"].Value = (long)(y * SystemClassDB.XYZScalar); insertSysCmd.Parameters["@Z"].Value = (long)(z * SystemClassDB.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++; } } else { //System.Diagnostics.Debug.WriteLine("Reject due to gridid " + gridid); } } else { System.Diagnostics.Debug.WriteLine("Reject due to coords "); } count++; blkcount++; } // WHILE END } 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); }
private static long DoParseEDSMUpdateSystemsReader(JsonTextReader jr, ref string date, ref bool outoforder, 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, SystemClassBase> systemsByEdsmId = useCache ? GetEdsmSystemsLite() : new Dictionary <long, 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(); const int BlockSize = 10000; while (!cancelRequested()) { bool jr_eof = false; List <EDSMDumpSystem> objs = new List <EDSMDumpSystem>(BlockSize); while (!cancelRequested()) { if (jr.Read()) { if (jr.TokenType == JsonToken.StartObject) { objs.Add(EDSMDumpSystem.Deserialize(jr)); if (objs.Count >= BlockSize) { break; } } } else { jr_eof = true; break; } } IEnumerator <EDSMDumpSystem> jo_enum = objs.GetEnumerator(); bool jo_enum_finished = false; while (!jo_enum_finished && !cancelRequested()) { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: EDDbAccessMode.Writer)) { 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); while (!cancelRequested() && !SQLiteConnectionSystem.IsReadWaiting) { if (!jo_enum.MoveNext()) { reportProgress(-1, $"Syncing EDSM systems: {count:N0} processed, {insertcount:N0} new systems, {updatecount:N0} 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); } jo_enum_finished = true; break; } EDSMDumpSystem jo = jo_enum.Current; EDSMDumpSystemCoords coords = jo.coords; if (coords != null) { double x = coords.x; double y = coords.y; double z = coords.z; long edsmid = jo.id; string name = jo.name; int gridid = GridId.Id(x, z); int randomid = rnd.Next(0, 99); DateTime updatedate = jo.date; histogramsystems[gridid]++; if (updatedate > maxdate) { maxdate = updatedate; } else if (updatedate < maxdate - TimeSpan.FromHours(1)) { outoforder = true; } 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 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"]) / SystemClassDB.XYZScalar; dbsys.y = ((double)(long)reader["Y"]) / SystemClassDB.XYZScalar; dbsys.z = ((double)(long)reader["Z"]) / SystemClassDB.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 * SystemClassDB.XYZScalar); updateSysCmd.Parameters["@Y"].Value = (long)(y * SystemClassDB.XYZScalar); updateSysCmd.Parameters["@Z"].Value = (long)(z * SystemClassDB.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 * SystemClassDB.XYZScalar); insertSysCmd.Parameters["@Y"].Value = (long)(y * SystemClassDB.XYZScalar); insertSysCmd.Parameters["@Z"].Value = (long)(z * SystemClassDB.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); }