// take old system table and turn to new. tablesarempty=false is normal, only set to true if using this code for checking replace algorithm
public static long UpgradeDB102to200(Func <bool> cancelRequested, Action <string> reportProgress, string tablepostfix, bool tablesareempty = false, int maxgridid = int.MaxValue)
{
sectoridcache = new Dictionary <long, Sector>();
sectornamecache = new Dictionary <string, Sector>();
SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.ReaderWriter);
int nextsectorid = GetNextSectorID();
long updates = 0;
long Limit = long.MaxValue;
DateTime maxdate = DateTime.MinValue; // we don't pass this back due to using the same date
reportProgress?.Invoke("Being System DB upgrade");
DbCommand selectSectorCmd = cn.CreateSelect("Sectors" + tablepostfix, "id", "name = @sname AND gridid = @gid", null,
new string[] { "sname", "gid" }, new DbType[] { DbType.String, DbType.Int32 });
List <int> gridids = DB.GridId.AllId();
BaseUtils.AppTicks.TickCountLap("UTotal");
//int debug_z = 0;
foreach (int gridid in gridids) // using grid id to keep chunks a good size.. can't read and write so can't just read the whole.
{
if (cancelRequested())
{
updates = -1;
break;
}
if (gridid == maxgridid) // for debugging
{
break;
}
DbCommand selectPrev = cn.CreateSelect("EdsmSystems s", "s.EdsmId,s.x,s.y,s.z,n.Name,s.UpdateTimeStamp", "s.GridId = " + gridid.ToStringInvariant(),
joinlist: new string[] { "LEFT OUTER JOIN SystemNames n ON n.EdsmId=s.EdsmId" });
int recordstostore = 0;
using (DbDataReader reader = selectPrev.ExecuteReader()) // find name:gid
{
BaseUtils.AppTicks.TickCountLap("U1");
while (reader.Read())
{
try
{
EDSMFileEntry d = new EDSMFileEntry();
d.id = (long)reader[0];
d.x = (int)(long)reader[1];
d.y = (int)(long)reader[2];
d.z = (int)(long)reader[3];
d.name = (string)reader[4];
d.date = new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromSeconds((long)reader["UpdateTimestamp"]);
int grididentry = GridId.Id(d.x, d.z); // because i don't trust the previous gridid - it should be the same as the outer loop, but lets recalc
//if (!tablesareempty) d.z = debug_z++; // for debug checking
CreateNewUpdate(selectSectorCmd, d, grididentry, tablesareempty, ref maxdate, ref nextsectorid); // not using gridid on purpose to double check it.
recordstostore++;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Reading prev table" + ex);
}
}
}
selectPrev.Dispose();
//System.Diagnostics.Debug.WriteLine("Reader took " + BaseUtils.AppTicks.TickCountLap("U1") + " in " + gridid + " " + recordpos + " total " + recordstostore);
if (recordstostore >= 0)
{
updates += StoreNewEntries(cn, tablepostfix, null);
reportProgress?.Invoke("System DB upgrade processed " + updates);
Limit -= recordstostore;
if (Limit <= 0)
{
break;
}
if (SQLiteConnectionSystem.IsReadWaiting)
{
System.Threading.Thread.Sleep(20); // just sleepy for a bit to let others use the db
}
}
var tres1 = BaseUtils.AppTicks.TickCountLapDelta("U1");
var tres2 = BaseUtils.AppTicks.TickCountFrom("UTotal");
System.Diagnostics.Debug.WriteLine("Sector " + gridid + " took " + tres1.Item1 + " store " + recordstostore + " total " + updates + " " + ((float)tres1.Item2 / (float)recordstostore) + " cumulative " + tres2);
}
reportProgress?.Invoke("System DB complete, processed " + updates);
selectSectorCmd.Dispose();
cn.Dispose();
PutNextSectorID(nextsectorid); // and store back
sectoridcache = null;
sectornamecache = null;
return(updates);
}
// set tempostfix to use another set of tables
public static long ParseEDSMJSON(JsonTextReader jr,
bool[] grididallowed, // null = all, else grid bool value
ref DateTime maxdate, // updated with latest date
Func <bool> cancelRequested,
Action <string> reportProgress,
string tablepostfix, // set to add on text to table names to redirect to another table
bool tablesareempty = false, // set to presume table is empty, so we don't have to do look up queries
string debugoutputfile = null
)
{
sectoridcache = new Dictionary <long, Sector>();
sectornamecache = new Dictionary <string, Sector>();
int nextsectorid = GetNextSectorID();
SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Writer);
StreamWriter sw = debugoutputfile != null ? new StreamWriter(debugoutputfile) : null;
long updates = 0;
const int BlockSize = 100000;
int Limit = int.MaxValue;
bool jr_eof = false;
DbCommand selectSectorCmd = cn.CreateSelect("Sectors" + tablepostfix, "id", "name = @sname AND gridid = @gid", null,
new string[] { "sname", "gid" }, new DbType[] { DbType.String, DbType.Int32 });
while (jr_eof == false)
{
if (cancelRequested())
{
updates = -1;
break;
}
int recordstostore = 0;
while (true)
{
try
{
if (jr.Read()) // collect a decent amount
{
if (jr.TokenType == JsonToken.StartObject)
{
EDSMFileEntry d = new EDSMFileEntry();
if (d.Deserialize(jr) && d.id >= 0 && d.name.HasChars() && d.z != int.MinValue) // if we have a valid record
{
int gridid = GridId.Id(d.x, d.z);
if (grididallowed == null || (grididallowed.Length > gridid && grididallowed[gridid])) // allows a null or small grid
{
CreateNewUpdate(selectSectorCmd, d, gridid, tablesareempty, ref maxdate, ref nextsectorid);
recordstostore++;
}
}
if (--Limit == 0)
{
jr_eof = true;
break;
}
if (recordstostore >= BlockSize)
{
break;
}
}
}
else
{
jr_eof = true;
break;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("EDSM JSON file exception " + ex.ToString());
jr_eof = true; // stop read, but let it continue to finish this section
}
}
System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + " " + updates);
if (recordstostore > 0)
{
updates += StoreNewEntries(cn, tablepostfix, sw);
reportProgress?.Invoke("EDSM Star database updated " + updates);
}
if (jr_eof)
{
break;
}
if (SQLiteConnectionSystem.IsReadWaiting)
{
System.Threading.Thread.Sleep(20); // just sleepy for a bit to let others use the db
}
}
System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + " " + updates);
reportProgress?.Invoke("EDSM Star database updated " + updates);
if (sw != null)
{
sw.Close();
}
selectSectorCmd.Dispose();
cn.Dispose();
PutNextSectorID(nextsectorid); // and store back
sectoridcache = null;
sectornamecache = null;
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);
}
