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