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