// may return null if not found // by design, it keeps on trying. Rob thought about caching the misses but the problem is, this is done at start up // the system db may not be full at that point. So a restart would be required to clear the misses.. // difficult public static ISystem FindSystem(long edsmid, DB.SQLiteConnectionSystem conn = null) { return(FindSystem(new SystemClass(edsmid), conn)); }
public static ISystem FindSystem(string name, DB.SQLiteConnectionSystem conn = null) { return(FindSystem(new SystemClass(name), conn)); }
public static ISystem FindSystem(string name, long edsmid, SQLiteConnectionSystem cn = null) { return(FindSystem(new SystemClass(name, edsmid), cn)); }
public static ISystem FindSystem(ISystem find, SQLiteConnectionSystem cn = null) { ISystem orgsys = find; List <ISystem> foundlist = new List <ISystem>(); lock (systemsByEdsmId) // Rob seen instances of it being locked together in multiple star distance threads, we need to serialise access to these two dictionaries { // Concurrent dictionary no good, they could both be about to add the same thing at the same time and pass the contains test. if (find.EDSMID > 0 && systemsByEdsmId.ContainsKey(find.EDSMID)) // add to list { ISystem s = systemsByEdsmId[find.EDSMID]; foundlist.Add(s); } if (systemsByName.ContainsKey(find.Name)) // and all names cached { List <ISystem> s = systemsByName[find.Name]; foundlist.AddRange(s); } } ISystem found = null; if (find.HasCoordinate && foundlist.Count > 0) // if sys has a co-ord, find the best match within 0.5 ly { found = NearestTo(foundlist, find, 0.5); } if (found == null && foundlist.Count == 1 && !find.HasCoordinate) // if we did not find one, but we have only 1 candidate, use it. { found = foundlist[0]; } if (found == null) // nope, no cache, so use the db { //System.Diagnostics.Debug.WriteLine("Look up from DB " + sys.name + " " + sys.id_edsm); bool owncn = cn == null; if (owncn) { cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader); } if (find.EDSMID > 0) // if we have an ID, look it up { found = DB.SystemsDB.FindStar(find.EDSMID, cn); if (found != null && find.Name.HasChars() && find.Name != "UnKnown") // if we find it, use the find name in the return as the EDSM name may be out of date.. { found.Name = find.Name; } } if (found == null && find.Name.HasChars() && find.Name != "UnKnown") // if not found by has a name { found = DB.SystemsDB.FindStar(find.Name, cn); // find by name, no wildcards } if (found == null && find.HasCoordinate) // finally, not found, but we have a co-ord, find it from the db by distance { found = DB.SystemsDB.GetSystemByPosition(find.X, find.Y, find.Z, cn); } if (found == null) { long newid = DB.SystemsDB.FindAlias(find.EDSMID, find.Name, cn); // is there a named alias in there due to a system being renamed.. if (newid >= 0) { found = DB.SystemsDB.FindStar(newid); // find it using the new id } } if (found != null) // if we have a good db, go for it { if (find.HasCoordinate) // if find has co-ordinate, it may be more up to date than the DB, so use it { found.X = find.X; found.Y = find.Y; found.Z = find.Z; } lock (systemsByEdsmId) // lock to prevent multi change over these classes { if (systemsByName.ContainsKey(orgsys.Name)) // so, if name database already has name { systemsByName[orgsys.Name].Remove(orgsys); // and remove the ISystem if present on that orgsys } AddToCache(found); } //System.Diagnostics.Trace.WriteLine($"DB found {found.name} {found.id_edsm} sysid {found.id_edsm}"); } if (owncn) { cn.Dispose(); } return(found); } else { // FROM CACHE //System.Diagnostics.Trace.WriteLine($"Cached reference to {found.name} {found.id_edsm}"); return(found); // no need for extra work. } }
public static ISystem GetSystemByPosition(double x, double y, double z, SQLiteConnectionSystem cn = null) { return(FindNearestSystemTo(x, y, z, 0.125, cn)); }
public static ISystem FindNearestSystemTo(double x, double y, double z, double maxdistance, SQLiteConnectionSystem cn = null) { bool owncn = cn == null; if (owncn) { cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader); } ISystem s = DB.SystemsDB.GetSystemByPosition(x, y, z, cn, maxdistance); if (s != null) { AddToCache(s); } if (owncn) { cn.Dispose(); } return(s); }
// // Generally, cache is not used below, but systems are added to the cache to speed up above searches // static public List <ISystem> FindSystemWildcard(string name, int limit = int.MaxValue, SQLiteConnectionSystem cn = null) { bool owncn = cn == null; if (owncn) { cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader); } var list = DB.SystemsDB.FindStarWildcard(name, cn, limit); if (list != null) { foreach (var x in list) { AddToCache(x); } } if (owncn) { cn.Dispose(); } return(list); }
public SystemsDatabaseConnection(bool ro) { Connection = new SQLiteConnectionSystem(ro); }
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. // 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", 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)); 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())// && distlist.Count < maxitems) // 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); LookedUp?.Invoke(s); // callback to say looked up distlist.Add(((double)distsqi) / SystemClass.XYZScalar / SystemClass.XYZScalar, s); // which Rob has seen crashing the program! Bad EDSM! } count++; } // System.Diagnostics.Debug.WriteLine("Time2 " + BaseUtils.AppTicks.TickCountLap("SDC") + " count " + count); } } }
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 } } } }
public 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 ISystem GetSystemByPosition(double x, double y, double z, SQLiteConnectionSystem cn, double maxdist = 0.125) { BaseUtils.SortedListDoubleDuplicate <ISystem> distlist = new BaseUtils.SortedListDoubleDuplicate <ISystem>(); GetSystemListBySqDistancesFrom(distlist, x, y, z, 1, 0, maxdist, true, cn); // return 1 item, min dist 0, maxdist return((distlist.Count > 0) ? distlist.First().Value : null); }
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); }
public static ISystem FindSystem(ISystem find, DB.SQLiteConnectionSystem conn = null) { lock (systemsByEdsmId) // Rob seen instances of it being locked together in multiple star distance threads, we need to serialise the whole thing { // Concurrent dictionary no good, they could both be about to add the same thing at the same time and pass the contains test. ISystem orgsys = find; List <ISystem> foundlist = new List <ISystem>(); if (find.EDSMID > 0 && systemsByEdsmId.ContainsKey(find.EDSMID)) // add to list { ISystem s = systemsByEdsmId[find.EDSMID]; foundlist.Add(s); } if (systemsByName.ContainsKey(find.Name)) // and all names cached { List <ISystem> s = systemsByName[find.Name]; foundlist.AddRange(s); } ISystem found = null; if (find.HasCoordinate && foundlist.Count > 0) // if sys has a co-ord, find the best match within 0.5 ly { found = NearestTo(foundlist, find, 0.5); } if (found == null && foundlist.Count == 1 && !find.HasCoordinate) // if we did not find one, but we have only 1 candidate, use it. { found = foundlist[0]; } if (found == null) // nope, no cache, so use the db { //System.Diagnostics.Debug.WriteLine("Look up from DB " + sys.name + " " + sys.id_edsm); bool closeit = false; if (conn == null) // we may touch the db multiple times, so if we don't have it open, do it. { closeit = true; conn = new DB.SQLiteConnectionSystem(); } if (find.EDSMID > 0) // if we have an ID, look it up { found = DB.SystemClassDB.GetSystem(find.EDSMID, conn, DB.SystemClassDB.SystemIDType.EdsmId, name: find.Name); } // if not found, or no co-ord (unlikely), or its old as the hills, AND has a name if ((found == null || !found.HasCoordinate || DateTime.UtcNow.Subtract(found.UpdateDate).TotalDays > 7) && find.Name.Length >= 2) { List <ISystem> _systems = DB.SystemClassDB.GetSystemsByName(find.Name, cn: conn); // try DB via names.. if (found != null) { _systems.Add(found); // add on the EDSM ID candidate.. if present } if (_systems.Count == 1 && !find.HasCoordinate) // 1 name, no co-ord, go with it as a back stop { found = _systems[0]; } else if (_systems.Count > 0 && find.HasCoordinate) // entries, and we have a co-ord to distinguish { found = NearestTo(_systems, find, 0.5); // find it.. } // else, found will be edsmid lookup if set.. } if (found == null && find.HasCoordinate) // finally, not found, but we have a co-ord, find it from the db by distance { found = DB.SystemClassDB.GetSystemByPosition(find.X, find.Y, find.Z, conn); } if (closeit && conn != null) // finished with db, close { conn.Dispose(); } if (found != null) // if we have a good db, go for it { if ((find.HasCoordinate && !found.HasCoordinate) || find.UpdateDate > found.UpdateDate) // if found does not have co-ord, or sys is more up to date.. { found.X = find.X; found.Y = find.Y; found.Z = find.Z; } if (find.UpdateDate > found.UpdateDate) // Bravada, check that we are not overwriting newer info.. { // if the journal info is newer than the db system name, lets presume the journal data is better found.Name = find.Name; found.UpdateDate = find.UpdateDate; } if (found.EDSMID > 0) { systemsByEdsmId[found.EDSMID] = found; // must be definition the best ID found.. and if the update date of sys is better, its now been updated } if (systemsByName.ContainsKey(orgsys.Name)) // use the original name we looked it up in, if we found one.. remove it { systemsByName[orgsys.Name].Remove(orgsys); // and remove } if (systemsByName.ContainsKey(found.Name)) { systemsByName[found.Name].Add(found); // add to list.. } else { systemsByName[found.Name] = new List <ISystem> { found } }; // or make list //System.Diagnostics.Trace.WriteLine($"DB found {found.name} {found.id_edsm} sysid {found.id_edsm}"); return(found); } else { //System.Diagnostics.Trace.WriteLine($"DB NOT found {find.name} {find.id_edsm} "); return(null); } } else { // FROM CACHE //System.Diagnostics.Trace.WriteLine($"Cached reference to {found.name} {found.id_edsm}"); return(found); // no need for extra work. } } }
public static void GetSystemListBySqDistancesFrom(BaseUtils.SortedListDoubleDuplicate <ISystem> distlist, double x, double y, double z, int maxitems, double mindist, double maxdist, bool spherical, SQLiteConnectionSystem cn = null) { bool owncn = cn == null; if (owncn) { cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Reader); } DB.SystemsDB.GetSystemListBySqDistancesFrom(distlist, x, y, z, maxitems, mindist, maxdist, spherical, cn, (s) => AddToCache(s)); if (owncn) { cn.Dispose(); } }
public static ISystem FindEDSM(ISystem sys, bool usedb = false, bool useedsm = false, DB.SQLiteConnectionSystem conn = null) { List <ISystem> systems = systemsByName.ContainsKey(sys.name) ? systemsByName[sys.name] : new List <ISystem>(); ISystem sysbyid = sys.id_edsm != 0 && systemsByEdsmId.ContainsKey(sys.id_edsm) ? systemsByEdsmId[sys.id_edsm] : null; ISystem sysbynamepos = null; ISystem dbsys = null; if (systems.Count == 1 && !sys.HasCoordinate && sys.id_edsm < 1) { sysbynamepos = systems[0]; } if (systems.Count != 0 && sys.HasCoordinate) { double minsqdist = 0.25; foreach (ISystem csys in systems) { if (csys.HasCoordinate) { double sqdist = SqDist(sys, csys); if (sqdist < minsqdist) { sysbynamepos = csys; minsqdist = sqdist; } } } } if (sysbyid != null && sysbyid.HasCoordinate && sysbyid.UpdateDate > sys.UpdateDate) { return(sysbyid); } else if (sysbynamepos != null && sysbynamepos.id_edsm > 0 && sysbynamepos.HasCoordinate && sysbynamepos.UpdateDate > sys.UpdateDate) { return(sysbynamepos); } if (usedb) { dbsys = DB.SystemClassDB.FindEDSM(sys, conn: conn, useedsm: false); if (false && useedsm && dbsys == null && (sys.id_edsm <= 0 || !sys.HasCoordinate || DateTime.UtcNow.Subtract(sys.UpdateDate).TotalDays > 7)) { dbsys = DB.SystemClassDB.FindEDSM(sys, conn: conn, useedsm: true); } if (dbsys != null && dbsys.UpdateDate > sys.UpdateDate) { sys = dbsys; } } if (sys.id_edsm > 0 && (sysbyid == null || sys.UpdateDate > sysbyid.UpdateDate)) { systemsByEdsmId[sys.id_edsm] = sys; } if (sysbynamepos == null || sys.UpdateDate > sysbynamepos.UpdateDate) { if (systems != null) { if (sysbynamepos != null) { systems.Remove(sysbynamepos); } systems.Add(sys); } else { systemsByName[sys.name] = new List <ISystem> { sys }; } } return(dbsys); }
public 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", MakeSystemQueryEDDB, "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: MakeSystemQueryEDDBJoinList)) { //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", MakeSystemQueryEDDB, "(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: MakeSystemQueryEDDBJoinList)) { //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", MakeSystemQueryEDDB, "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: MakeSystemQueryEDDBJoinList)) { //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", MakeSystemQueryEDDB, "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: MakeSystemQueryEDDBJoinList)) { // System.Diagnostics.Debug.WriteLine(cn.ExplainQueryPlanString(selectSysCmd)); using (DbDataReader reader = selectSysCmd.ExecuteReader()) { while (reader.Read()) { SystemClass sc = MakeSystem(reader); ret.Add(sc); } } } } } return(ret); }