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