internal static ISystem FindStar(string name, SQLiteConnectionSystem cn) { EliteNameClassifier ec = new EliteNameClassifier(name); if (ec.IsNamed) { // needs index on sectorid [nameid]. Relies on Names.id being the edsmid. using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "s.edsmid IN (Select id FROM Names WHERE name=@p1) AND s.sectorid IN (Select id FROM Sectors c WHERE c.name=@p2)", new Object[] { ec.StarName, ec.SectorName }, joinlist: MakeSystemQueryNamedJoinList)) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { if (reader.Read()) { return(MakeSystem(reader)); // read back and make name from db info due to case problems. } } } } else { // Numeric or Standard - all data in ID // needs index on Systems(sectorid, Nameid) using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSysStdNumericQuery, "s.nameid = @p1 AND s.sectorid IN (Select id FROM Sectors c WHERE c.name=@p2)", new Object[] { ec.ID, ec.SectorName }, joinlist: MakeSysStdNumericQueryJoinList)) { // System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { if (reader.Read()) { return(MakeSystem(reader, ec.ID)); // read back .. sector name is taken from DB for case reasons } } } } return(null); }
// Beware with no extra conditions, you get them all.. Mostly used for debugging // use starreport to avoid storing the entries instead pass back one by one public static List <ISystem> ListStars(string where = null, string orderby = null, string limit = null, bool eddbinfo = false, Action <ISystem> starreport = null) { List <ISystem> ret = new List <ISystem>(); //BaseUtils.AppTicks.TickCountLap("Star"); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Writer)) { using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", eddbinfo ? MakeSystemQueryEDDB : MakeSystemQueryNoEDDB, where, orderby, limit: limit, joinlist: (eddbinfo ? MakeSystemQueryEDDBJoinList : MakeSystemQueryJoinList))) { using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) // if there.. { SystemClass s = MakeSystem(reader, eddbinfo); if (starreport != null) { starreport(s); } else { ret.Add(s); } } } } } //System.Diagnostics.Debug.WriteLine("Find stars " + BaseUtils.AppTicks.TickCountLap("Star")); return(ret); }
public static void GetSystemListBySqDistancesFrom(BaseUtils.SortedListDoubleDuplicate <ISystem> distlist, // MUST use duplicate double list to protect against EDSM having two at the same point double x, double y, double z, int maxitems, double mindist, // 0 = no min dist, always spherical double maxdist, bool spherical, // enforces sphere on maxdist, else its a cube for maxdist SQLiteConnectionSystem cn, Action <ISystem> LookedUp = null ) { // for comparision, using the grid screener is slower than the xy index. keep code for record // grid screener.. "s.sectorid IN (Select id FROM Sectors sx where sx.gridid IN (" + strinlist + ")) " + //var gridids = GridId.Ids(x - maxdist, x + maxdist, z - maxdist, z + maxdist); // find applicable grid ids across this range.. //var strinlist = string.Join(",", (from x1 in gridids select x1.ToStringInvariant())); // here we convert using invariant for paranoia sake. int mindistint = mindist > 0 ? SystemClass.DoubleToInt(mindist) * SystemClass.DoubleToInt(mindist) : 0; // needs a xz index for speed using (DbCommand cmd = cn.CreateSelect("Systems s", MakeSystemQueryEDDB, where : "s.x >= @xv - @maxdist " + "AND s.x <= @xv + @maxdist " + "AND s.z >= @zv - @maxdist " + "AND s.z <= @zv + @maxdist " + "AND s.y >= @yv - @maxdist " + "AND s.y <= @yv + @maxdist " + (mindist > 0 ? ("AND (s.x-@xv)*(s.x-@xv)+(s.y-@yv)*(s.y-@yv)+(s.z-@zv)*(s.z-@zv)>=" + (mindistint).ToStringInvariant()) : ""), orderby: "(s.x-@xv)*(s.x-@xv)+(s.y-@yv)*(s.y-@yv)+(s.z-@zv)*(s.z-@zv)", // just use squares to order joinlist: MakeSystemQueryEDDBJoinList, limit: "@max" )) { cmd.AddParameterWithValue("@xv", SystemClass.DoubleToInt(x)); cmd.AddParameterWithValue("@yv", SystemClass.DoubleToInt(y)); cmd.AddParameterWithValue("@zv", SystemClass.DoubleToInt(z)); cmd.AddParameterWithValue("@max", maxitems + 1); // 1 more, because if we are on a System, that will be returned cmd.AddParameterWithValue("@maxdist", SystemClass.DoubleToInt(maxdist)); // System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(cmd)); using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read())// && distlist.Count < maxitems) // already sorted, and already limited to max items { SystemClass s = MakeSystem(reader); LookedUp?.Invoke(s); // callback to say looked up double distsq = s.DistanceSq(x, y, z); if ((!spherical || distsq <= maxdist * maxdist)) { distlist.Add(distsq, s); // which Rob has seen crashing the program! Bad EDSM! } } } } }
internal static ISystem GetSystemNearestTo(Point3D currentpos, Point3D wantedpos, double maxfromcurpos, double maxfromwanted, SystemsNearestMetric routemethod, SQLiteConnectionSystem cn, Action <ISystem> LookedUp = null, int limitto = 1000) { using (DbCommand cmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, where : "x >= @xc - @maxfromcurpos " + "AND x <= @xc + @maxfromcurpos " + "AND z >= @zc - @maxfromcurpos " + "AND z <= @zc + @maxfromcurpos " + "AND x >= @xw - @maxfromwanted " + "AND x <= @xw + @maxfromwanted " + "AND z >= @zw - @maxfromwanted " + "AND z <= @zw + @maxfromwanted " + "AND y >= @yc - @maxfromcurpos " + "AND y <= @yc + @maxfromcurpos " + "AND y >= @yw - @maxfromwanted " + "AND y <= @yw + @maxfromwanted ", orderby: "(s.x-@xw)*(s.x-@xw)+(s.y-@yw)*(s.y-@yw)+(s.z-@zw)*(s.z-@zw)", // orderby distance from wanted limit: limitto, joinlist: MakeSystemQueryNamedJoinList)) { cmd.AddParameterWithValue("@xw", SystemClass.DoubleToInt(wantedpos.X)); // easier to manage with named paras cmd.AddParameterWithValue("@yw", SystemClass.DoubleToInt(wantedpos.Y)); cmd.AddParameterWithValue("@zw", SystemClass.DoubleToInt(wantedpos.Z)); cmd.AddParameterWithValue("@maxfromwanted", SystemClass.DoubleToInt(maxfromwanted)); cmd.AddParameterWithValue("@xc", SystemClass.DoubleToInt(currentpos.X)); cmd.AddParameterWithValue("@yc", SystemClass.DoubleToInt(currentpos.Y)); cmd.AddParameterWithValue("@zc", SystemClass.DoubleToInt(currentpos.Z)); cmd.AddParameterWithValue("@maxfromcurpos", SystemClass.DoubleToInt(maxfromcurpos)); //System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(cmd)); using (DbDataReader reader = cmd.ExecuteReader()) { var systems = MakeSystemEnumerable(reader, callback: LookedUp); return(GetSystemNearestTo(systems, currentpos, wantedpos, maxfromcurpos, maxfromwanted, routemethod)); } } }
public static long FindAlias(long edsmid, string name, SQLiteConnectionSystem cn) { string query = "edsmid = @edsmid OR name = @name"; if (edsmid < 1) { query = "name = @name"; } else if (!name.HasChars()) { query = "edsmid = @edsmid"; } DbCommand selectCmd = cn.CreateSelect("Aliases", "edsmid_mergedto", query, inparas: new string[] { "edsmid:int64", "name:string" }); selectCmd.Parameters[0].Value = edsmid; selectCmd.Parameters[1].Value = name; return(selectCmd.ExecuteScalar <long>(-1)); }
internal static ISystem FindStar(long edsmid, SQLiteConnectionSystem cn) { // No indexes needed- edsmid is primary key using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "s.edsmid=@p1", new Object[] { edsmid }, joinlist: MakeSystemQueryNamedJoinList)) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { if (reader.Read()) { return(MakeSystem(reader)); } } } return(null); }
internal static List <ISystem> FindAliasWildcard(string name, SQLiteConnectionSystem cn) { List <ISystem> ret = new List <ISystem>(); using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryEDDB, "s.edsmid IN (Select edsmid_mergedto FROM Aliases WHERE name like @p1)", new Object[] { name + "%" }, joinlist: MakeSystemQueryEDDBJoinList)) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { ret.Add(MakeSystem(reader)); } } } return(ret); }
// randimised id % 100 < sercentage public static List <V> GetStarPositions <V>(int percentage, Func <int, int, int, V> tovect) // return all star positions.. { List <V> ret = new List <V>(); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader)) { using (DbCommand cmd = cn.CreateSelect("Systems s", outparas: "s.x,s.y,s.z", where : "((s.edsmid*2333)%100) <" + percentage.ToStringInvariant() )) { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { ret.Add(tovect(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2))); } } } } return(ret); }
// 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); }
// 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); }
public static long ParseAlias(JsonTextReader jr) { long updates = 0; System.Diagnostics.Debug.WriteLine("Update aliases"); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Writer)) // open the db { using (DbTransaction txn = cn.BeginTransaction()) { DbCommand selectCmd = cn.CreateSelect("Aliases", "edsmid", "edsmid = @edsmid", inparas: new string[] { "edsmid:int64" }, limit: "1", tx: txn); // 1 return matching ID DbCommand deletesystemcmd = cn.CreateDelete("Systems", "edsmid=@edsmid", paras: new string[] { "edsmid:int64" }, tx: txn); DbCommand insertCmd = cn.CreateReplace("Aliases", paras: new string[] { "edsmid:int64", "edsmid_mergedto:int64", "name:string" }, tx: txn); try { // protect against json exceptions while (true) { if (!jr.Read()) { break; } if (jr.TokenType == JsonToken.StartObject) { JObject jo = JObject.Load(jr); long edsmid = (long)jo["id"]; string name = (string)jo["system"]; string action = (string)jo["action"]; long mergedto = 0; if (jo["mergedTo"] != null) { mergedto = (long)jo["mergedTo"]; } if (action.Contains("delete system", System.StringComparison.InvariantCultureIgnoreCase)) { deletesystemcmd.Parameters[0].Value = edsmid; deletesystemcmd.ExecuteNonQuery(); } if (mergedto > 0) { selectCmd.Parameters[0].Value = edsmid; long foundedsmid = selectCmd.ExecuteScalar <long>(-1); if (foundedsmid == -1) { insertCmd.Parameters[0].Value = edsmid; insertCmd.Parameters[1].Value = mergedto; insertCmd.Parameters[2].Value = name; insertCmd.ExecuteNonQuery(); //System.Diagnostics.Debug.WriteLine("Alias " + edsmid + " -> " + mergedto + " " + name); updates++; } } } } } catch (System.Exception ex) { System.Diagnostics.Debug.WriteLine("JSON format error in aliases " + ex); } txn.Commit(); selectCmd.Dispose(); deletesystemcmd.Dispose(); insertCmd.Dispose(); } } return(updates); }
internal static List <ISystem> FindStarWildcard(string name, SQLiteConnectionSystem cn, int limit = int.MaxValue) { EliteNameClassifier ec = new EliteNameClassifier(name); List <ISystem> ret = new List <ISystem>(); if (ec.IsStandardParts) // normal Euk PRoc qc-l d2-3 { // needs index on Systems(sectorid, Nameid) using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "s.nameid >= @p1 AND s.nameid <= @p2 AND s.sectorid IN (Select id FROM Sectors c WHERE c.name=@p3)", new Object[] { ec.ID, ec.IDHigh, ec.SectorName }, limit: limit, joinlist: MakeSystemQueryNamedJoinList)) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { SystemClass sc = MakeSystem(reader); ret.Add(sc); } } } } else if (ec.IsNumeric) // HIP 29282 { // checked select *,s.nameid & 0x3fffffffff , cast((s.nameid & 0x3fffffffff) as text) From Systems s where (s.nameid & (1<<46)!=0) and s.sectorid=15568 USNO entries // beware, 1<<46 works, 0x40 0000 0000 does not.. // needs index on Systems(sectorid, Nameid) using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "(s.nameid & (1<<46) != 0) AND cast((s.nameid & 0x3fffffffff) as text) LIKE @p1 AND s.sectorid IN (Select id FROM Sectors c WHERE c.name=@p2)", new Object[] { ec.NameIdNumeric.ToStringInvariant() + "%", ec.SectorName }, limit: limit, joinlist: MakeSystemQueryNamedJoinList)) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { SystemClass sc = MakeSystem(reader); ret.Add(sc); } } } } else { // named if (ec.StarName.Length > 0) // if we have a starname component and a sector name, look up sectorname + starname% { // needs index on Systems(sectorid, Nameid) using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "s.nameid IN (Select id FROM Names WHERE name LIKE @p1) AND s.sectorid IN (Select id FROM Sectors c WHERE c.name=@p2)", new Object[] { ec.StarName + "%", ec.SectorName }, limit: limit, joinlist: MakeSystemQueryNamedJoinList)) { //System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { SystemClass sc = MakeSystem(reader); ret.Add(sc); } limit -= ret.Count; } } } // look up Sector. Use sectorname, unless it NoSectorName in which case use the starname as a presumed sector name // needs index on Systems(sectorid, [Nameid]) if (limit > 0) { using (DbCommand selectSysCmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, "s.sectorid IN (Select id FROM Sectors c WHERE c.name LIKE @p1)", new Object[] { (ec.SectorName != EliteNameClassifier.NoSectorName ? ec.SectorName : ec.StarName) + "%" }, limit: limit, joinlist: MakeSystemQueryNamedJoinList)) { // System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { SystemClass sc = MakeSystem(reader); ret.Add(sc); } } } } } return(ret); }
public static void GetSystemVector <V>(int gridid, ref V[] vertices1, ref uint[] colours1, ref V[] vertices2, ref uint[] colours2, int percentage, Func <int, int, int, V> tovect, SQLiteConnectionSystem cn, SystemAskType ask = SystemAskType.SplitPopulatedStars) { int numvertices1 = 0; vertices1 = vertices2 = null; int numvertices2 = 0; colours1 = colours2 = null; Color[] fixedc = new Color[4]; fixedc[0] = Color.Red; fixedc[1] = Color.Orange; fixedc[2] = Color.Yellow; fixedc[3] = Color.White; //System.Diagnostics.Debug.WriteLine("sysLap : " + BaseUtils.AppTicks.TickCountLap()); // tried xz comparision but slower than grid select using (DbCommand cmd = cn.CreateSelect("Systems s", outparas: "s.edsmid,s.x,s.y,s.z" + (ask == SystemAskType.SplitPopulatedStars ? ",e.eddbid" : ""), where : "s.sectorid IN (Select id FROM Sectors c WHERE c.gridid = @p1)" + (percentage < 100 ? (" AND ((s.edsmid*2333)%100) <" + percentage.ToStringInvariant()) : "") + (ask == SystemAskType.PopulatedStars ? " AND e.edsmid NOT NULL " : "") + (ask == SystemAskType.UnpopulatedStars ? " AND e.edsmid IS NULL " : ""), paras: new Object[] { gridid }, joinlist: ask != SystemAskType.AllStars ? new string[] { "LEFT OUTER JOIN EDDB e ON e.edsmid = s.edsmid " } : null )) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(cmd)); vertices1 = new V[250000]; colours1 = new uint[250000]; if (ask == SystemAskType.SplitPopulatedStars) { vertices2 = new V[250000]; colours2 = new uint[250000]; } using (DbDataReader reader = cmd.ExecuteReader()) { //System.Diagnostics.Debug.WriteLine("sysLapStart : " + BaseUtils.AppTicks.TickCountLap()); Object[] data = new Object[4]; while (reader.Read()) { long id = reader.GetInt64(0); // quicker than cast int x = reader.GetInt32(1); int y = reader.GetInt32(2); int z = reader.GetInt32(3); bool addtosecondary = (ask == SystemAskType.SplitPopulatedStars) ? (reader[4] is System.DBNull) : false; Color basec = fixedc[(id) & 3]; int fade = 100 - (((int)id >> 2) & 7) * 8; byte red = (byte)(basec.R * fade / 100); byte green = (byte)(basec.G * fade / 100); byte blue = (byte)(basec.B * fade / 100); if (addtosecondary) { if (numvertices2 == vertices2.Length) { Array.Resize(ref vertices2, vertices2.Length * 2); Array.Resize(ref colours2, colours2.Length * 2); } colours2[numvertices2] = BitConverter.ToUInt32(new byte[] { red, green, blue, 255 }, 0); vertices2[numvertices2++] = tovect(x, y, z); } else { if (numvertices1 == vertices1.Length) { Array.Resize(ref vertices1, vertices1.Length * 2); Array.Resize(ref colours1, colours1.Length * 2); } colours1[numvertices1] = BitConverter.ToUInt32(new byte[] { red, green, blue, 255 }, 0); vertices1[numvertices1++] = tovect(x, y, z); } } // System.Diagnostics.Debug.WriteLine("sysLapEnd : " + BaseUtils.AppTicks.TickCountLap()); } Array.Resize(ref vertices1, numvertices1); Array.Resize(ref colours1, numvertices1); if (ask == SystemAskType.SplitPopulatedStars) { Array.Resize(ref vertices2, numvertices2); Array.Resize(ref colours2, numvertices2); } if (gridid == GridId.SolGrid && vertices1 != null) // BODGE do here, better once on here than every star for every grid.. { // replace when we have a better naming system int solindex = Array.IndexOf(vertices1, tovect(0, 0, 0)); if (solindex >= 0) { colours1[solindex] = 0x00ffff; //yellow } } } }
internal static ISystem GetSystemNearestTo(Point3D currentpos, Point3D wantedpos, double maxfromcurpos, double maxfromwanted, int routemethod, SQLiteConnectionSystem cn, Action <ISystem> LookedUp = null, int limitto = 1000) { using (DbCommand cmd = cn.CreateSelect("Systems s", MakeSystemQueryEDDB, where : "x >= @xc - @maxfromcurpos " + "AND x <= @xc + @maxfromcurpos " + "AND z >= @zc - @maxfromcurpos " + "AND z <= @zc + @maxfromcurpos " + "AND x >= @xw - @maxfromwanted " + "AND x <= @xw + @maxfromwanted " + "AND z >= @zw - @maxfromwanted " + "AND z <= @zw + @maxfromwanted " + "AND y >= @yc - @maxfromcurpos " + "AND y <= @yc + @maxfromcurpos " + "AND y >= @yw - @maxfromwanted " + "AND y <= @yw + @maxfromwanted ", orderby: "(s.x-@xw)*(s.x-@xw)+(s.y-@yw)*(s.y-@yw)+(s.z-@zw)*(s.z-@zw)", // orderby distance from wanted limit: limitto, joinlist: MakeSystemQueryEDDBJoinList)) { cmd.AddParameterWithValue("@xw", SystemClass.DoubleToInt(wantedpos.X)); // easier to manage with named paras cmd.AddParameterWithValue("@yw", SystemClass.DoubleToInt(wantedpos.Y)); cmd.AddParameterWithValue("@zw", SystemClass.DoubleToInt(wantedpos.Z)); cmd.AddParameterWithValue("@maxfromwanted", SystemClass.DoubleToInt(maxfromwanted)); cmd.AddParameterWithValue("@xc", SystemClass.DoubleToInt(currentpos.X)); cmd.AddParameterWithValue("@yc", SystemClass.DoubleToInt(currentpos.Y)); cmd.AddParameterWithValue("@zc", SystemClass.DoubleToInt(currentpos.Z)); cmd.AddParameterWithValue("@maxfromcurpos", SystemClass.DoubleToInt(maxfromcurpos)); //System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(cmd)); double bestmindistance = double.MaxValue; SystemClass nearestsystem = null; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { SystemClass s = MakeSystem(reader); LookedUp?.Invoke(s); // callback to say looked up Point3D syspos = new Point3D(s.X, s.Y, s.Z); double distancefromwantedx2 = Point3D.DistanceBetweenX2(wantedpos, syspos); // range between the wanted point and this, ^2 double distancefromcurposx2 = Point3D.DistanceBetweenX2(currentpos, syspos); // range between the wanted point and this, ^2 // ENSURE its withing the circles now if (distancefromcurposx2 <= (maxfromcurpos * maxfromcurpos) && distancefromwantedx2 <= (maxfromwanted * maxfromwanted)) { if (routemethod == metric_nearestwaypoint) { if (distancefromwantedx2 < bestmindistance) { nearestsystem = s; bestmindistance = distancefromwantedx2; } } else { Point3D interceptpoint = currentpos.InterceptPoint(wantedpos, syspos); // work out where the perp. intercept point is.. double deviation = Point3D.DistanceBetween(interceptpoint, syspos); double metric = 1E39; if (routemethod == metric_mindevfrompath) { metric = deviation; } else if (routemethod == metric_maximum100ly) { metric = (deviation <= 100) ? distancefromwantedx2 : metric; // no need to sqrt it.. } else if (routemethod == metric_maximum250ly) { metric = (deviation <= 250) ? distancefromwantedx2 : metric; } else if (routemethod == metric_maximum500ly) { metric = (deviation <= 500) ? distancefromwantedx2 : metric; } else if (routemethod == metric_waypointdev2) { metric = Math.Sqrt(distancefromwantedx2) + deviation / 2; } if (metric < bestmindistance) { nearestsystem = s; bestmindistance = metric; } } } } } return(nearestsystem); } }
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); }
private static void GetSystemVector <V>(int gridid, ref V[] vertices1, ref uint[] colours1, int percentage, Func <int, int, int, V> tovect, SQLiteConnectionSystem cn) { int numvertices1 = 0; vertices1 = null; colours1 = null; Color[] fixedc = new Color[4]; fixedc[0] = Color.Red; fixedc[1] = Color.Orange; fixedc[2] = Color.Yellow; fixedc[3] = Color.White; //System.Diagnostics.Debug.WriteLine("sysLap : " + BaseUtils.AppTicks.TickCountLap()); // tried xz comparision but slower than grid select using (DbCommand cmd = cn.CreateSelect("Systems s", outparas: "s.edsmid,s.x,s.y,s.z", where : "s.sectorid IN (Select id FROM Sectors c WHERE c.gridid = @p1)" + (percentage < 100 ? (" AND ((s.edsmid*2333)%100) <" + percentage.ToStringInvariant()) : ""), paras: new Object[] { gridid } )) { //System.Diagnostics.Debug.WriteLine( cn.ExplainQueryPlanString(cmd)); vertices1 = new V[250000]; colours1 = new uint[250000]; using (DbDataReader reader = cmd.ExecuteReader()) { //System.Diagnostics.Debug.WriteLine("sysLapStart : " + BaseUtils.AppTicks.TickCountLap()); Object[] data = new Object[4]; while (reader.Read()) { long id = reader.GetInt64(0); // quicker than cast int x = reader.GetInt32(1); int y = reader.GetInt32(2); int z = reader.GetInt32(3); Color basec = fixedc[(id) & 3]; int fade = 100 - (((int)id >> 2) & 7) * 8; byte red = (byte)(basec.R * fade / 100); byte green = (byte)(basec.G * fade / 100); byte blue = (byte)(basec.B * fade / 100); if (numvertices1 == vertices1.Length) { Array.Resize(ref vertices1, vertices1.Length * 2); Array.Resize(ref colours1, colours1.Length * 2); } colours1[numvertices1] = BitConverter.ToUInt32(new byte[] { red, green, blue, 255 }, 0); vertices1[numvertices1++] = tovect(x, y, z); } // System.Diagnostics.Debug.WriteLine("sysLapEnd : " + BaseUtils.AppTicks.TickCountLap()); } Array.Resize(ref vertices1, numvertices1); Array.Resize(ref colours1, numvertices1); if (gridid == GridId.SolGrid && vertices1 != null) // BODGE do here, better once on here than every star for every grid.. { // replace when we have a better naming system int solindex = Array.IndexOf(vertices1, tovect(0, 0, 0)); if (solindex >= 0) { colours1[solindex] = 0x00ffff; //yellow } } } }
///////////////////////////////////////// List of systems near xyz between mindist and maxdist internal static void GetSystemListBySqDistancesFrom(BaseUtils.SortedListDoubleDuplicate <ISystem> distlist, // MUST use duplicate double list to protect against EDSM having two at the same point double x, double y, double z, int maxitems, double mindist, // 0 = no min dist, always spherical double maxdist, bool spherical, // enforces sphere on maxdist, else its a cube for maxdist SQLiteConnectionSystem cn, Action <ISystem> LookedUp = null ) { // for comparision, using the grid screener is slower than the xy index. keep code for record // grid screener.. "s.sectorid IN (Select id FROM Sectors sx where sx.gridid IN (" + strinlist + ")) " + //var gridids = GridId.Ids(x - maxdist, x + maxdist, z - maxdist, z + maxdist); // find applicable grid ids across this range.. //var strinlist = string.Join(",", (from x1 in gridids select x1.ToStringInvariant())); // here we convert using invariant for paranoia sake. // System.Diagnostics.Debug.WriteLine("Time1 " + BaseUtils.AppTicks.TickCountLap("SDC")); int mindistint = mindist > 0 ? SystemClass.DoubleToInt(mindist) * SystemClass.DoubleToInt(mindist) : 0; // needs a xz index for speed using (DbCommand cmd = cn.CreateSelect("Systems s", MakeSystemQueryNamed, where : "s.x >= @xv - @maxdist " + "AND s.x <= @xv + @maxdist " + "AND s.z >= @zv - @maxdist " + "AND s.z <= @zv + @maxdist " + "AND s.y >= @yv - @maxdist " + "AND s.y <= @yv + @maxdist " + (mindist > 0 ? ("AND (s.x-@xv)*(s.x-@xv)+(s.y-@yv)*(s.y-@yv)+(s.z-@zv)*(s.z-@zv)>=" + (mindistint).ToStringInvariant()) : ""), orderby: "(s.x-@xv)*(s.x-@xv)+(s.y-@yv)*(s.y-@yv)+(s.z-@zv)*(s.z-@zv)", // just use squares to order joinlist: MakeSystemQueryNamedJoinList, limit: "@max" )) { cmd.AddParameterWithValue("@xv", SystemClass.DoubleToInt(x)); cmd.AddParameterWithValue("@yv", SystemClass.DoubleToInt(y)); cmd.AddParameterWithValue("@zv", SystemClass.DoubleToInt(z)); cmd.AddParameterWithValue("@max", maxitems + 1); // 1 more, because if we are on a System, that will be returned cmd.AddParameterWithValue("@maxdist", SystemClass.DoubleToInt(maxdist)); // System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(cmd)); int xi = SystemClass.DoubleToInt(x); int yi = SystemClass.DoubleToInt(y); int zi = SystemClass.DoubleToInt(z); long maxdistsqi = (long)SystemClass.DoubleToInt(maxdist) * (long)SystemClass.DoubleToInt(maxdist); long count = 0; using (DbDataReader reader = cmd.ExecuteReader()) { // System.Diagnostics.Debug.WriteLine("Time1.5 " + BaseUtils.AppTicks.TickCountLap("SDC")); while (reader.Read()) // already sorted, and already limited to max items { int sxi = reader.GetInt32(0); int syi = reader.GetInt32(1); int szi = reader.GetInt32(2); long distsqi = (long)(xi - sxi) * (long)(xi - sxi) + (long)(yi - syi) * (long)(yi - syi) + (long)(zi - szi) * (long)(zi - szi); if (!spherical || distsqi <= maxdistsqi) { SystemClass s = MakeSystem(reader); double distnorm = ((double)distsqi) / SystemClass.XYZScalar / SystemClass.XYZScalar; //System.Diagnostics.Debug.WriteLine("System " + s.Name + " " + Math.Sqrt(distnorm).ToString("0.0")); LookedUp?.Invoke(s); // callback to say looked up distlist.Add(distnorm, s); // which Rob has seen crashing the program! Bad EDSM! } count++; } // System.Diagnostics.Debug.WriteLine("Time2 " + BaseUtils.AppTicks.TickCountLap("SDC") + " count " + count); } } }