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