private void ExecuteWithDatabaseInternal(Action <SystemsDatabaseConnection> action, bool usetxnlock = false, SQLExtConnection.AccessMode mode = SQLExtConnection.AccessMode.Reader) { SQLExtTransactionLock <SQLiteConnectionSystem> tl = null; try { if (usetxnlock) { tl = new SQLExtTransactionLock <SQLiteConnectionSystem>(); if (mode == SQLExtConnection.AccessMode.Reader) { tl.OpenReader(); } else { tl.OpenWriter(); } } using (var conn = new SystemsDatabaseConnection(mode: mode)) { action(conn); } } finally { tl?.Dispose(); } }
static public long ParseEDDBUpdateSystems(string filename, Action <string> logline) { StreamReader sr = new StreamReader(filename); // read directly from file.. if (sr == null) { return(0); } string line; int updated = 0; int inserted = 0; while (!sr.EndOfStream) { using (SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>()) { tl.OpenWriter(); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Writer)) // 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 (!SQLiteConnectionSystem.IsReadWaiting) { line = sr.ReadLine(); if (line == null) // End of stream { break; } { JObject jo = JObject.Parse(line); ISystem system = SystemClassDB.FromEDDB(jo); if (system.HasEDDBInformation) // screen out for speed any EDDB data with empty interesting fields { hasinfo++; selectCmd.Parameters["@EdsmId"].Value = system.EDSMID; // 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.EDSMID; updateSysCmd.Parameters["@EddbId"].Value = system.EDDBID; updateSysCmd.ExecuteNonQuery(); if (eddbid != 0) { if (updated_at != system.EDDBUpdatedAt || population != system.Population) { updateCmd.Parameters["@EddbId"].Value = system.EDDBID; 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.PrimaryEconomy; updateCmd.Parameters["@NeedsPermit"].Value = system.NeedsPermit; updateCmd.Parameters["@EddbUpdatedAt"].Value = system.EDDBUpdatedAt; updateCmd.Parameters["@EdsmId"].Value = system.EDSMID; updateCmd.ExecuteNonQuery(); updated++; } } else { insertCmd.Parameters["@EdsmId"].Value = system.EDSMID; insertCmd.Parameters["@EddbId"].Value = system.EDDBID; 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.PrimaryEconomy; insertCmd.Parameters["@NeedsPermit"].Value = system.NeedsPermit; insertCmd.Parameters["@EddbUpdatedAt"].Value = system.EDDBUpdatedAt; 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) { System.Diagnostics.Trace.WriteLine("EDDB Count " + c + " Delta " + (Environment.TickCount - lasttc) + " info " + hasinfo + " update " + updated + " new " + inserted); lasttc = Environment.TickCount; } } } txn.Commit(); } catch { ExtendedControls.MessageBoxTheme.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"); break; } finally { if (selectCmd != null) { selectCmd.Dispose(); } if (updateCmd != null) { updateCmd.Dispose(); } if (insertCmd != null) { insertCmd.Dispose(); } } } } } } return(updated + inserted); }
private static long StoreNewEntries(SQLiteConnectionSystem cn, string tablepostfix = "", // set to add on text to table names to redirect to another table StreamWriter sw = null ) { long updates = 0; ////////////////////////////////////////////////////////////// push all new data to the db without any selects SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>(); // not using on purpose. tl.OpenWriter(); DbTransaction txn = cn.BeginTransaction(); DbCommand replaceSectorCmd = cn.CreateReplace("Sectors" + tablepostfix, new string[] { "name", "gridid", "id" }, new DbType[] { DbType.String, DbType.Int32, DbType.Int64 }, txn); DbCommand replaceSysCmd = cn.CreateReplace("Systems" + tablepostfix, new string[] { "sectorid", "nameid", "x", "y", "z", "edsmid" }, new DbType[] { DbType.Int64, DbType.Int64, DbType.Int32, DbType.Int32, DbType.Int32, DbType.Int64 }, txn); DbCommand replaceNameCmd = cn.CreateReplace("Names" + tablepostfix, new string[] { "name", "id" }, new DbType[] { DbType.String, DbType.Int64 }, txn); foreach (var kvp in sectoridcache) // all sectors cached, id is unique so its got all sectors { Sector t = kvp.Value; if (t.insertsec) // if we have been told to insert the sector, do it { replaceSectorCmd.Parameters[0].Value = t.Name; // make a new one so we can get the ID replaceSectorCmd.Parameters[1].Value = t.GId; replaceSectorCmd.Parameters[2].Value = t.Id; // and we insert with ID, managed by us, and replace in case there are any repeat problems (which there should not be) replaceSectorCmd.ExecuteNonQuery(); //System.Diagnostics.Debug.WriteLine("Written sector " + t.GId + " " +t.Name); t.insertsec = false; } if (t.edsmdatalist != null) // if updated.. { #if DEBUG t.edsmdatalist.Sort(delegate(TableWriteData left, TableWriteData right) { return(left.edsm.id.CompareTo(right.edsm.id)); }); #endif foreach (var data in t.edsmdatalist) // now write the star list in this sector { try { if (data.classifier.IsNamed) // if its a named entry, we need a name { data.classifier.NameIdNumeric = data.edsm.id; // name is the edsm id replaceNameCmd.Parameters[0].Value = data.classifier.StarName; // insert a new name replaceNameCmd.Parameters[1].Value = data.edsm.id; // we use edsmid as the nameid, and use replace to ensure that if a prev one is there, its replaced replaceNameCmd.ExecuteNonQuery(); // System.Diagnostics.Debug.WriteLine("Make name " + data.classifier.NameIdNumeric); } replaceSysCmd.Parameters[0].Value = t.Id; replaceSysCmd.Parameters[1].Value = data.classifier.ID; replaceSysCmd.Parameters[2].Value = data.edsm.x; replaceSysCmd.Parameters[3].Value = data.edsm.y; replaceSysCmd.Parameters[4].Value = data.edsm.z; replaceSysCmd.Parameters[5].Value = data.edsm.id; // in the event a new entry has the same edsmid, the system table edsmid is replace with new data replaceSysCmd.ExecuteNonQuery(); if (sw != null) { sw.WriteLine(data.edsm.name + " " + data.edsm.x + "," + data.edsm.y + "," + data.edsm.z + ", EDSM:" + data.edsm.id + " Grid:" + data.gridid); } updates++; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("general exception during insert - ignoring " + ex.ToString()); } } } t.edsmdatalist = null; // and delete back } txn.Commit(); replaceSectorCmd.Dispose(); replaceSysCmd.Dispose(); replaceNameCmd.Dispose(); txn.Dispose(); tl.Dispose(); return(updates); }
// 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 (SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>()) { tl.OpenWriter(); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.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); }
public static long ParseEDDBJSON(TextReader tr, Func <bool> cancelRequested) { long updated = 0; bool eof = false; while (!eof) { SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.ReaderWriter); SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>(); tl.OpenWriter(); DbTransaction txn = cn.BeginTransaction(); DbCommand selectCmd = cn.CreateSelect("EDDB", "eddbupdatedat", "edsmid = @edsmid", inparas: new string[] { "edsmid:int64" }, limit: "1", tx: txn); // 1 return matching ID string[] dbfields = { "edsmid", "eddbid", "eddbupdatedat", "population", "faction", "government", "allegiance", "state", "security", "primaryeconomy", "needspermit", "power", "powerstate", "properties" }; DbType[] dbfieldtypes = { DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.String, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.String, DbType.String, DbType.String }; DbCommand replaceCmd = cn.CreateReplace("EDDB", dbfields, dbfieldtypes, txn); while (!SQLiteConnectionSystem.IsReadWaiting) { string line = tr.ReadLine(); if (line == null) // End of stream { eof = true; break; } try { JObject jo = JObject.Parse(line); long jsonupdatedat = jo["updated_at"].Int(); long jsonedsmid = jo["edsm_id"].Long(); bool jsonispopulated = jo["is_populated"].Bool(); if (jsonispopulated) // double check that the flag is set - population itself may be zero, for some systems, but its the flag we care about { selectCmd.Parameters[0].Value = jsonedsmid; long dbupdated_at = selectCmd.ExecuteScalar <long>(0); if (dbupdated_at == 0 || jsonupdatedat != dbupdated_at) { replaceCmd.Parameters["@edsmid"].Value = jsonedsmid; replaceCmd.Parameters["@eddbid"].Value = jo["id"].Long(); replaceCmd.Parameters["@eddbupdatedat"].Value = jsonupdatedat; replaceCmd.Parameters["@population"].Value = jo["population"].Long(); replaceCmd.Parameters["@faction"].Value = jo["controlling_minor_faction"].Str("Unknown"); replaceCmd.Parameters["@government"].Value = EliteDangerousTypesFromJSON.Government2ID(jo["government"].Str("Unknown")); replaceCmd.Parameters["@allegiance"].Value = EliteDangerousTypesFromJSON.Allegiance2ID(jo["allegiance"].Str("Unknown")); EDState edstate = EDState.Unknown; try { if (jo["states"] != null && jo["states"].HasValues) { JToken tk = jo["states"].First; // we take the first one whatever JObject jostate = (JObject)tk; edstate = EliteDangerousTypesFromJSON.EDState2ID(jostate["name"].Str("Unknown")); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("EDDB JSON file exception for states " + ex.ToString()); } replaceCmd.Parameters["@state"].Value = edstate; replaceCmd.Parameters["@security"].Value = EliteDangerousTypesFromJSON.EDSecurity2ID(jo["security"].Str("Unknown")); replaceCmd.Parameters["@primaryeconomy"].Value = EliteDangerousTypesFromJSON.EDEconomy2ID(jo["primary_economy"].Str("Unknown")); replaceCmd.Parameters["@needspermit"].Value = jo["needs_permit"].Int(0); replaceCmd.Parameters["@power"].Value = jo["power"].Str("None"); replaceCmd.Parameters["@powerstate"].Value = jo["power_state"].Str("N/A"); replaceCmd.Parameters["@properties"].Value = RemoveFieldsFromJSON(jo); replaceCmd.ExecuteNonQuery(); updated++; } } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("EDDB JSON file exception " + ex.ToString()); } } txn.Commit(); txn.Dispose(); selectCmd.Dispose(); replaceCmd.Dispose(); tl.Dispose(); cn.Dispose(); } return(updated); }