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