public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2) { if (s1 == null || s2 == null) { return(null); } try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1")) { cmd.AddParameterWithValue("@NameA", s1.name); cmd.AddParameterWithValue("@NameB", s2.name); DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // if found. { DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]); return(dist); } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return(null); }
public static List <DistanceClass> GetDistancesByStatus(int status) { List <DistanceClass> ldist = new List <DistanceClass>(); try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'")) { DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { DistanceClass dist = new DistanceClass(dr); ldist.Add(dist); } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return(ldist); }
public static long GetTotalDistances() { long value = 0; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select Count(*) from Distances")) { using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { value = (long)reader["Count(*)"]; } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return(value); }
public static void DropSystemsTableIndexes() { string[] queries = new[] { "DROP INDEX IF EXISTS SystemsIndex", "DROP INDEX IF EXISTS Systems_EDSM_ID_Index", "DROP INDEX IF EXISTS Systems_EDDB_ID_Index", "DROP INDEX IF EXISTS IDX_Systems_versiondate", "DROP INDEX IF EXISTS Systems_position", "DROP INDEX IF EXISTS SystemGridId", "DROP INDEX IF EXISTS SystemRandomId", "DROP INDEX IF EXISTS EdsmSystems_EdsmId", "DROP INDEX IF EXISTS EdsmSystems_EddbId", "DROP INDEX IF EXISTS EddbSystems_EdsmId", "DROP INDEX IF EXISTS EddbSystems_EddbId", "DROP INDEX IF EXISTS EdsmSystems_Position", "DROP INDEX IF EXISTS EdsmSystems_GridId", "DROP INDEX IF EXISTS EdsmSystems_RandomId", "DROP INDEX IF EXISTS SystemNames_EdsmId", "DROP INDEX IF EXISTS SystemNames_IdName", "DROP INDEX IF EXISTS SystemNames_NameId", "DROP INDEX IF EXISTS sqlite_autoindex_SystemNames_1" }; using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem()) { foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } } }
private bool Delete(SQLiteConnectionSystem cn) { using (DbCommand cmd = cn.CreateCommand("Delete From Distances where ID=@id")) { cmd.AddParameterWithValue("@ID", id); SQLiteDBClass.SQLNonQueryText(cn, cmd); return(true); } }
public static bool Delete(DistancsEnum distsource) { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("Delete from Distances where Status=@Status")) { cmd.AddParameterWithValue("@Status", (int)distsource); SQLiteDBClass.SQLNonQueryText(cn, cmd); } } return(true); }
private bool Update(SQLiteConnectionSystem cn, DbTransaction tn = null) { using (DbCommand cmd = cn.CreateCommand("Update Distances set NameA=@NameA, NameB=@NameB, Dist=@Dist, commandercreate=@commandercreate, CreateTime=@CreateTime, status=@status, id_edsm=@id_edsm where ID=@id", tn)) { cmd.AddParameterWithValue("@ID", id); cmd.AddParameterWithValue("@NameA", NameA); cmd.AddParameterWithValue("@NameB", NameB); cmd.AddParameterWithValue("@Dist", Dist); cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate); cmd.AddParameterWithValue("@CreateTime", CreateTime); cmd.AddParameterWithValue("@Status", Status); cmd.AddParameterWithValue("@id_edsm", id_edsm); SQLiteDBClass.SQLNonQueryText(cn, cmd); return(true); } }
public bool Store() { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { bool ret; ret = Store(cn); if (ret == true) { using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Distances")) { id = (long)SQLiteDBClass.SQLScalar(cn, cmd2); } return(true); } return(ret); } }
public static void DropOldSystemTables(SQLiteConnectionSystem conn) { string[] queries = new[] { "DROP TABLE IF EXISTS Bookmarks", "DROP TABLE IF EXISTS SystemNote", "DROP TABLE IF EXISTS TravelLogUnit", "DROP TABLE IF EXISTS VisitedSystems", "DROP TABLE IF EXISTS route_Systems", "DROP TABLE IF EXISTS routes_expeditions", "DROP TABLE IF EXISTS Objects", "DROP TABLE IF EXISTS wanted_systems", }; foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } }
private bool Store(SQLiteConnectionSystem cn, DbTransaction tn = null) { if (CommanderCreate == null) { CommanderCreate = ""; } using (DbCommand cmd = cn.CreateCommand("Insert into Distances (NameA, NameB, Dist, CommanderCreate, CreateTime, Status, id_edsm) values (@NameA, @NameB, @Dist, @CommanderCreate, @CreateTime, @Status, @id_edsm)", tn)) { cmd.AddParameterWithValue("@NameA", NameA); cmd.AddParameterWithValue("@NameB", NameB); cmd.AddParameterWithValue("@Dist", Dist); cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate); cmd.AddParameterWithValue("@CreateTime", CreateTime); cmd.AddParameterWithValue("@Status", Status); cmd.AddParameterWithValue("@id_edsm", id_edsm); SQLiteDBClass.SQLNonQueryText(cn, cmd); } return(true); }
private static void UpdateSchema() { bool id_edsm_isset = false; using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { UpdateDbSchema(cn, Schema.EDDSystem); // Reset EDSM / EDDB update time if no systems have their id_edsm set using (DbCommand cmd = cn.CreateCommand("SELECT COUNT(EdsmId) FROM EdsmSystems")) { id_edsm_isset = (long)cmd.ExecuteScalar() != 0; } if (!id_edsm_isset) { System.Diagnostics.Trace.WriteLine("Resetting EDSM and EDDB last update time"); cn.PutSettingStringCN("EDSMLastSystems", "2010-01-01 00:00:00"); // force EDSM sync.. cn.PutSettingStringCN("EDDBSystemsTime", "0"); // force EDDB cn.PutSettingStringCN("EDSCLastDist", "2010-01-01 00:00:00"); // force distances } } using (SQLiteConnectionUser cn = new SQLiteConnectionUser()) { UpdateDbSchema(cn, Schema.EDDUser); // Null out any coordinates where (x,y,z) = (0,0,0) and the system is not Sol /* * using (DbCommand cmd = cn.CreateCommand("Update VisitedSystems set x=null, y=null, z=null where x=0 and y=0 and z=0 and name!=\"Sol\"")) * { * cmd.ExecuteNonQuery(); * } */ } }
public static void CreateSystemsTableIndexes() { string[] queries = new[] { "CREATE INDEX IF NOT EXISTS EdsmSystems_EdsmId ON EdsmSystems (EdsmId ASC, EddbId, X, Y, Z)", "CREATE INDEX IF NOT EXISTS EdsmSystems_EddbId ON EdsmSystems (EddbId ASC, EdsmId)", "CREATE INDEX IF NOT EXISTS EddbSystems_EdsmId ON EddbSystems (EdsmId ASC, EddbId)", "CREATE INDEX IF NOT EXISTS EddbSystems_EddbId ON EddbSystems (EddbId ASC, EdsmId)", "CREATE INDEX IF NOT EXISTS EdsmSystems_PosId ON EdsmSystems (Z, X, Y, EdsmId)", "CREATE INDEX IF NOT EXISTS EdsmSystems_StarGrid ON EdsmSystems (GridId, RandomId, EdsmId, EddbId, X, Y, Z)", "CREATE INDEX IF NOT EXISTS SystemNames_IdName ON SystemNames (EdsmId,Name)", "CREATE INDEX IF NOT EXISTS SystemNames_NameId ON SystemNames (Name,EdsmId)", }; using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem()) { foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } } }
private static void CreateSystemDBTableIndexes(SQLiteConnectionSystem conn) { string[] queries = new[] { "CREATE UNIQUE INDEX IF NOT EXISTS SystemAliases_id_edsm ON SystemAliases (id_edsm)", "CREATE INDEX IF NOT EXISTS SystemAliases_name ON SystemAliases (name)", "CREATE INDEX IF NOT EXISTS SystemAliases_id_edsm_mergedto ON SystemAliases (id_edsm_mergedto)", "CREATE INDEX IF NOT EXISTS Distances_EDSM_ID_Index ON Distances (id_edsm ASC)", "CREATE INDEX IF NOT EXISTS DistanceName ON Distances (NameA ASC, NameB ASC)", "CREATE INDEX IF NOT EXISTS stationIndex ON Stations (system_id ASC)", "CREATE INDEX IF NOT EXISTS station_commodities_index ON station_commodities (station_id ASC, commodity_id ASC, type ASC)", "CREATE INDEX IF NOT EXISTS StationsIndex_ID ON Stations (id ASC)", "CREATE INDEX IF NOT EXISTS StationsIndex_system_ID ON Stations (system_id ASC)", "CREATE INDEX IF NOT EXISTS StationsIndex_system_Name ON Stations (Name ASC)", }; foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } }
public static SystemClass GetSystemNearestTo(Point3D curpos, Point3D wantedpos, double maxfromcurpos, double maxfromwanted, int routemethod) { SystemClass nearestsystem = null; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { string sqlquery = "SELECT EdsmId, x, y, z " + "FROM EdsmSystems " + "WHERE x >= @xc - @maxfromcurpos " + "AND x <= @xc + @maxfromcurpos " + "AND y >= @yc - @maxfromcurpos " + "AND y <= @yc + @maxfromcurpos " + "AND z >= @zc - @maxfromcurpos " + "AND z <= @zc + @maxfromcurpos " + "AND x >= @xw - @maxfromwanted " + "AND x <= @xw + @maxfromwanted " + "AND y >= @yw - @maxfromwanted " + "AND y <= @yw + @maxfromwanted " + "AND z >= @zw - @maxfromwanted " + "AND z <= @zw + @maxfromwanted "; using (DbCommand cmd = cn.CreateCommand(sqlquery)) { cmd.AddParameterWithValue("@xw", wantedpos.X); cmd.AddParameterWithValue("@yw", wantedpos.Y); cmd.AddParameterWithValue("@zw", wantedpos.Z); cmd.AddParameterWithValue("@maxfromwanted", maxfromwanted * maxfromwanted); //squared cmd.AddParameterWithValue("@xc", curpos.X); cmd.AddParameterWithValue("@yc", curpos.Y); cmd.AddParameterWithValue("@zc", curpos.Z); cmd.AddParameterWithValue("@maxfromcurrent", maxfromcurpos * maxfromcurpos); //squared double bestmindistance = double.MaxValue; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string name = (string)reader["name"]; long id = (long)reader["id"]; if (System.DBNull.Value != reader["x"]) // paranoid check, it could be null in db { Point3D syspos = new Point3D((double)reader["x"], (double)reader["y"], (double)reader["z"]); double distancefromwantedx2 = Point3D.DistanceBetweenX2(wantedpos, syspos); // range between the wanted point and this, ^2 double distancefromcurposx2 = Point3D.DistanceBetweenX2(curpos, syspos); // range between the wanted point and this, ^2 if (routemethod == metric_nearestwaypoint) { if (distancefromwantedx2 < bestmindistance) { nearestsystem = GetSystem(id); bestmindistance = distancefromwantedx2; } } else { Point3D interceptpoint = curpos.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 = GetSystem(id); bestmindistance = metric; //Console.WriteLine("System " + syscheck.name + " way " + deviation.ToString("0.0") + " metric " + metric.ToString("0.0") + " *"); } else { //Console.WriteLine("System " + syscheck.name + " way " + deviation.ToString("0.0") + " metric " + metric.ToString("0.0")); } } } } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return nearestsystem; }
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date, bool removenonedsmids, Func <bool> cancelRequested, Action <int, string> reportProgress, Action <string> logline) { List <DistanceClass> toupdate = new List <DistanceClass>(); List <DistanceClass> newpairs = new List <DistanceClass>(); DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE")); bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db { int c = 0; DbCommand cmd = null; int lasttc = Environment.TickCount; try { cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching while (jr.Read() && !cancelRequested()) { if (jr.TokenType == JsonToken.StartObject) { JObject jo = JObject.Load(jr); DistanceClass dc = new DistanceClass(jo); if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0) { maxdate = dc.CreateTime; } if (++c % 10000 == 0) { Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count()); reportProgress(-1, $"Reading EDSM distances: {c} processed, {newpairs.Count} new, {toupdate.Count} to update"); lasttc = Environment.TickCount; } if (emptydatabase) // empty DB, just store.. { newpairs.Add(dc); } else { cmd.Parameters.Clear(); cmd.AddParameterWithValue("id", dc.id_edsm); using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there.. { if (reader1.Read()) // its there.. { DistanceClass dbdc = new DistanceClass(reader1); // see if EDSM data changed.. if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05) { dbdc.NameA = dc.NameA; dbdc.NameB = dc.NameB; dbdc.Dist = dc.Dist; toupdate.Add(dbdc); } } else // not in database.. { //Console.WriteLine("Add new system " + system.name); newpairs.Add(dc); } } } } } } catch { logline("There is a problem using the EDSM distance file." + Environment.NewLine + "Please perform a manual EDSM distance sync (see Admin menu) next time you run the program "); } finally { if (cmd != null) { cmd.Dispose(); } } } if (cancelRequested()) { return(0); } using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db { if (toupdate.Count > 0) { reportProgress(-1, $"Updating EDSM distances: {toupdate.Count} distances to update"); using (DbTransaction transaction = cn2.BeginTransaction()) { foreach (DistanceClass dc in toupdate) { dc.Update(cn2, transaction); } transaction.Commit(); } } if (cancelRequested()) { return(toupdate.Count()); } if (newpairs.Count > 0) { int count = 0; while (count < newpairs.Count()) { using (DbTransaction transaction = cn2.BeginTransaction()) { while (count < newpairs.Count()) { newpairs[count].Store(cn2, transaction); if (++count % 10000 == 0) { break; } } reportProgress(count * 100 / newpairs.Count, $"Adding EDSM distances: {count} added"); Console.WriteLine("EDSM Dist Store Count " + count); transaction.Commit(); } if (cancelRequested()) { return(toupdate.Count() + count); } } } if (removenonedsmids) // done on a full sync.. { reportProgress(-1, "Removing distances without an ID"); Console.WriteLine("Delete old ones"); using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null")) { SQLiteDBClass.SQLNonQueryText(cn2, cmddel); } } } date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture); return(toupdate.Count + newpairs.Count); }
public static void CreateSystemsTableIndexes() { string[] queries = new[] { "CREATE INDEX IF NOT EXISTS EdsmSystems_EdsmId ON EdsmSystems (EdsmId ASC)", "CREATE INDEX IF NOT EXISTS EdsmSystems_EddbId ON EdsmSystems (EddbId ASC)", "CREATE INDEX IF NOT EXISTS EddbSystems_EdsmId ON EddbSystems (EdsmId ASC)", "CREATE INDEX IF NOT EXISTS EddbSystems_EddbId ON EddbSystems (EddbId ASC)", "CREATE INDEX IF NOT EXISTS EdsmSystems_Position ON EdsmSystems (Z, X, Y)", "CREATE INDEX IF NOT EXISTS EdsmSystems_GridId ON EdsmSystems (gridid)", "CREATE INDEX IF NOT EXISTS EdsmSystems_RandomId ON EdsmSystems (randomid)", "CREATE INDEX IF NOT EXISTS SystemNames_EdsmId ON SystemNames (EdsmId)" }; using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem()) { foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } } }
public static SystemClass GetSystemNearestTo(Point3D curpos, Point3D wantedpos, double maxfromcurpos, double maxfromwanted, int routemethod) { SystemClass nearestsystem = null; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { string sqlquery = "SELECT EdsmId, x, y, z " + // DO a square test for speed, then double check its within the circle later.. "FROM EdsmSystems " + "WHERE x >= @xc - @maxfromcurpos " + "AND x <= @xc + @maxfromcurpos " + "AND y >= @yc - @maxfromcurpos " + "AND y <= @yc + @maxfromcurpos " + "AND z >= @zc - @maxfromcurpos " + "AND z <= @zc + @maxfromcurpos " + "AND x >= @xw - @maxfromwanted " + "AND x <= @xw + @maxfromwanted " + "AND y >= @yw - @maxfromwanted " + "AND y <= @yw + @maxfromwanted " + "AND z >= @zw - @maxfromwanted " + "AND z <= @zw + @maxfromwanted "; using (DbCommand cmd = cn.CreateCommand(sqlquery)) { cmd.AddParameterWithValue("xw", (long)(wantedpos.X * XYZScalar)); cmd.AddParameterWithValue("yw", (long)(wantedpos.Y * XYZScalar)); cmd.AddParameterWithValue("zw", (long)(wantedpos.Z * XYZScalar)); cmd.AddParameterWithValue("maxfromwanted", (long)(maxfromwanted * XYZScalar)); //squared cmd.AddParameterWithValue("xc", (long)(curpos.X * XYZScalar)); cmd.AddParameterWithValue("yc", (long)(curpos.Y * XYZScalar)); cmd.AddParameterWithValue("zc", (long)(curpos.Z * XYZScalar)); cmd.AddParameterWithValue("maxfromcurpos", (long)(maxfromcurpos * XYZScalar)); //squared double bestmindistance = double.MaxValue; long nearestedsmid = -1; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { long edsmid = (long)reader[0]; //SystemClass sys = GetSystem(edsmid, null, SystemIDType.EdsmId); Console.WriteLine("FOund {0} at {1} {2} {3}", sys.name, sys.x, sys.y, sys.z); if (System.DBNull.Value != reader["x"]) // paranoid check, it could be null in db { Point3D syspos = new Point3D(((double)(long)reader[1])/XYZScalar, ((double)(long)reader[2])/XYZScalar, ((double)(long)reader[3])/XYZScalar); double distancefromwantedx2 = Point3D.DistanceBetweenX2(wantedpos, syspos); // range between the wanted point and this, ^2 double distancefromcurposx2 = Point3D.DistanceBetweenX2(curpos, 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) { nearestedsmid = edsmid; bestmindistance = distancefromwantedx2; } } else { Point3D interceptpoint = curpos.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) { nearestedsmid = edsmid; bestmindistance = metric; } } } } } } if (nearestedsmid != -1) nearestsystem = GetSystem(nearestedsmid, cn, SystemIDType.EdsmId); } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return nearestsystem; }
public static void GetSystemAndAlternatives(EliteDangerous.JournalEvents.JournalLocOrJump vsc, out ISystem system, out List<ISystem> alternatives, out string namestatus) { system = new EDDiscovery2.DB.InMemory.SystemClass { name = vsc.StarSystem, x = vsc.HasCoordinate ? vsc.StarPos[0] : Double.NaN, y = vsc.HasCoordinate ? vsc.StarPos[1] : Double.NaN, z = vsc.HasCoordinate ? vsc.StarPos[2] : Double.NaN, id_edsm = vsc.EdsmID }; using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { // Check that the SystemAliases table is not empty using (DbCommand cmd = cn.CreateCommand("SELECT COUNT(id) FROM SystemAliases")) { long nrows = (long)cmd.ExecuteScalar(); if (nrows == 0) { //Console.WriteLine("Populating system aliases table"); RemoveHiddenSystems(); } } Dictionary<string, List<long>> aliasesByName = new Dictionary<string, List<long>>(StringComparer.InvariantCultureIgnoreCase); Dictionary<long, long> aliasesById = new Dictionary<long, long>(); using (DbCommand cmd = cn.CreateCommand("SELECT name, id_edsm, id_edsm_mergedto FROM SystemAliases")) { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string name = (string)reader["name"]; long edsmid = (long)reader["id_edsm"]; long mergedto = (long)reader["id_edsm_mergedto"]; if (!aliasesByName.ContainsKey(name)) { aliasesByName[name] = new List<long>(); } aliasesByName[name].Add(mergedto); aliasesById[edsmid] = mergedto; } } } Dictionary<long, SystemClass> altmatches = new Dictionary<long, SystemClass>(); Dictionary<long, SystemClass> matches = new Dictionary<long, SystemClass>(); SystemClass edsmidmatch = null; long sel_edsmid = vsc.EdsmID; bool hastravcoords = vsc.HasCoordinate && (vsc.StarSystem.ToLowerInvariant() == "sol" || vsc.StarPos[0] != 0 || vsc.StarPos[1] != 0 || vsc.StarPos[2] != 0); bool multimatch = false; if (sel_edsmid != 0) { edsmidmatch = GetSystem(sel_edsmid, cn, SystemIDType.EdsmId); matches.Add(edsmidmatch.id, edsmidmatch); while (aliasesById.ContainsKey(sel_edsmid)) { sel_edsmid = aliasesById[sel_edsmid]; SystemClass sys = GetSystem(sel_edsmid, cn, SystemIDType.EdsmId); altmatches.Add(sys.id, sys); edsmidmatch = null; } } //Stopwatch sw2 = new Stopwatch(); sw2.Start(); //long t2 = sw2.ElapsedMilliseconds; Tools.LogToFile(string.Format("Query names in {0}", t2)); Dictionary<long, SystemClass> namematches = GetSystemsByName(vsc.StarSystem).Where(s => s != null).ToDictionary(s => s.id, s => s); Dictionary<long, SystemClass> posmatches = new Dictionary<long, SystemClass>(); Dictionary<long, SystemClass> nameposmatches = new Dictionary<long, SystemClass>(); if (hastravcoords) { using (DbCommand selectByPosCmd = cn.CreateCommand( "SELECT s.EdsmId FROM EdsmSystems s " + // 16 is 0.125 of 1/128, so pick system near this one "WHERE s.X >= @X - 16 " + "AND s.X <= @X + 16 " + "AND s.Y >= @Y - 16 " + "AND s.Y <= @Y + 16 " + "AND s.Z >= @Z - 16 " + "AND s.Z <= @Z + 16")) { selectByPosCmd.AddParameterWithValue("@X", (long)(vsc.StarPos[0] * XYZScalar)); selectByPosCmd.AddParameterWithValue("@Y", (long)(vsc.StarPos[1] * XYZScalar)); selectByPosCmd.AddParameterWithValue("@Z", (long)(vsc.StarPos[2] * XYZScalar)); //Stopwatch sw = new Stopwatch(); sw.Start(); long t1 = sw.ElapsedMilliseconds; Tools.LogToFile(string.Format("Query pos in {0}", t1)); using (DbDataReader reader = selectByPosCmd.ExecuteReader()) // MEASURED very fast, <1ms { while (reader.Read()) { long pos_edsmid = (long)reader["EdsmId"]; SystemClass sys = GetSystem(pos_edsmid, cn, SystemIDType.EdsmId); if (sys != null) { matches[sys.id] = sys; posmatches[sys.id] = sys; if (sys.name.Equals(vsc.StarSystem, StringComparison.InvariantCultureIgnoreCase)) { nameposmatches[sys.id] = sys; } } } } } } if (aliasesByName.ContainsKey(vsc.StarSystem)) { foreach (long alt_edsmid in aliasesByName[vsc.StarSystem]) { SystemClass sys = GetSystem(alt_edsmid, cn, SystemIDType.EdsmId); if (sys != null) { altmatches[sys.id] = sys; } } } foreach (var sys in namematches.Values) { matches[sys.id] = sys; } if (altmatches.Count != 0) { foreach (var alt in altmatches.Values) { matches[alt.id] = alt; } } alternatives = matches.Values.Select(s => (ISystem)s).ToList(); if (edsmidmatch != null) { system = edsmidmatch; if (nameposmatches.ContainsKey(system.id)) // name and position matches { namestatus = "Exact match"; return; // Continue to next system } else if (posmatches.ContainsKey(system.id)) // position matches { namestatus = "Name differs"; return; // Continue to next system } else if (!hastravcoords || !system.HasCoordinate) // no coordinates available { if (namematches.ContainsKey(system.id)) // name matches { if (!system.HasCoordinate) { namestatus = "System has no known coordinates"; } else { namestatus = "Travel log entry has no coordinates"; } return; // Continue to next system } else if (!vsc.HasCoordinate) { namestatus = "Name differs"; } } } if (nameposmatches != null && nameposmatches.Count != 0) { if (nameposmatches.Count == 1) { // Both name and position matches system = nameposmatches.Values.Single(); namestatus = "Exact match"; return; // Continue to next system } else if (posmatches.Count == 1) { // Position matches system = posmatches.Values.Single(); namestatus = $"System {system.name} found at location"; return; // Continue to next system } else { multimatch = true; } } if (namematches != null && namematches.Count != 0) { if (namematches.Count == 1) { // One system name matched system = namematches.Values.Single(); namestatus = "Name matched"; return; } else if (namematches.Count > 1) { multimatch = true; } } if (multimatch) { namestatus = "Multiple system matches found"; } else { namestatus = "System not found"; } } }
// Systems in data dumps are now sorted by modify time ascending, so // the last inserted system should be the most recently modified system. // // The beta.edsm.net dumps are currently still in coordinate order, so // anything using this should check whether the last dump was ordered by date public static DateTime GetLastSystemModifiedTimeFast() { DateTime lasttime = new DateTime(2010, 1, 1, 0, 0, 0); try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT UpdateTimestamp FROM EdsmSystems ORDER BY Id DESC LIMIT 1")) { using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.Read() && System.DBNull.Value != reader["UpdateTimestamp"]) lasttime = new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromSeconds((long)reader["UpdateTimestamp"]); } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return lasttime; }
private static Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> GetEdsmSystemsLite(SQLiteConnectionSystem cn) { Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> systemsByEdsmId = new Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase>(); using (DbCommand cmd = cn.CreateCommand("SELECT s.id, s.EdsmId, n.Name, s.x, s.y, s.z, s.UpdateTimestamp, s.gridid, s.randomid FROM EdsmSystems s JOIN SystemNames n ON n.EdsmId = s.EdsmId")) { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { EDDiscovery2.DB.InMemory.SystemClassBase sys = new EDDiscovery2.DB.InMemory.SystemClassBase { id = (long)reader["id"], name = (string)reader["name"] }; string searchname = sys.name.ToLower(); if (System.DBNull.Value == reader["x"]) { sys.x = double.NaN; sys.y = double.NaN; sys.z = double.NaN; } else { sys.x = ((double)(long)reader["x"]) / XYZScalar; sys.y = ((double)(long)reader["y"]) / XYZScalar; sys.z = ((double)(long)reader["z"]) / XYZScalar; } sys.id_edsm = (long)reader["EdsmId"]; systemsByEdsmId[sys.id_edsm] = sys; sys.gridid = reader["gridid"] == DBNull.Value ? 0 : (int)((long)reader["gridid"]); sys.randomid = reader["randomid"] == DBNull.Value ? 0 : (int)((long)reader["randomid"]); } } } return systemsByEdsmId; }
public static List<string> ReturnSystemListForAutoComplete(string input) { List<string> ret = new List<string>(); if (input.Length > 0) { lock (AutoCompleteAdditionalList) { foreach (string other in AutoCompleteAdditionalList) { if (other.StartsWith(input, StringComparison.InvariantCultureIgnoreCase)) ret.Add(other); } } using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT Name,EdsmId FROM SystemNames WHERE Name>=@first AND Name<=@second LIMIT 1000")) { cmd.AddParameterWithValue("first", input); cmd.AddParameterWithValue("second", input + "~"); using (DbDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { ret.Add((string)rdr[0]); } } } } } return ret; }
public static long ParseEDDBUpdateSystems(string filename, Action<string> logline) { StreamReader sr = new StreamReader(filename); // read directly from file.. if (sr == null) return 0; JsonTextReader jr = new JsonTextReader(sr); if (jr == null) return 0; int updated = 0; int inserted = 0; using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db { DbCommand selectCmd = null; DbCommand insertCmd = null; DbCommand updateCmd = null; DbCommand updateSysCmd = null; using (DbTransaction txn = cn.BeginTransaction()) { try { selectCmd = cn.CreateCommand("SELECT EddbId, Population, EddbUpdatedAt FROM EddbSystems WHERE EdsmId = @EdsmId LIMIT 1", txn); // 1 return matching ID selectCmd.AddParameter("@Edsmid", DbType.Int64); insertCmd = cn.CreateCommand("INSERT INTO EddbSystems (EdsmId, EddbId, Name, Faction, Population, GovernmentId, AllegianceId, State, Security, PrimaryEconomyId, NeedsPermit, EddbUpdatedAt) " + "VALUES (@EdsmId, @EddbId, @Name, @Faction, @Population, @GovernmentId, @AllegianceId, @State, @Security, @PrimaryEconomyid, @NeedsPermit, @EddbUpdatedAt)", txn); insertCmd.AddParameter("@EdsmId", DbType.Int64); insertCmd.AddParameter("@EddbId", DbType.Int64); insertCmd.AddParameter("@Name", DbType.String); insertCmd.AddParameter("@Faction", DbType.String); insertCmd.AddParameter("@Population", DbType.Int64); insertCmd.AddParameter("@GovernmentId", DbType.Int64); insertCmd.AddParameter("@AllegianceId", DbType.Int64); insertCmd.AddParameter("@State", DbType.Int64); insertCmd.AddParameter("@Security", DbType.Int64); insertCmd.AddParameter("@PrimaryEconomyId", DbType.Int64); insertCmd.AddParameter("@NeedsPermit", DbType.Int64); insertCmd.AddParameter("@EddbUpdatedAt", DbType.Int64); updateCmd = cn.CreateCommand("UPDATE EddbSystems SET EddbId=@EddbId, Name=@Name, Faction=@Faction, Population=@Population, GovernmentId=@GovernmentId, AllegianceId=@AllegianceId, State=@State, Security=@Security, PrimaryEconomyId=@PrimaryEconomyId, NeedsPermit=@NeedsPermit, EddbUpdatedAt=@EddbUpdatedAt WHERE EdsmId=@EdsmId", txn); updateCmd.AddParameter("@EdsmId", DbType.Int64); updateCmd.AddParameter("@EddbId", DbType.Int64); updateCmd.AddParameter("@Name", DbType.String); updateCmd.AddParameter("@Faction", DbType.String); updateCmd.AddParameter("@Population", DbType.Int64); updateCmd.AddParameter("@GovernmentId", DbType.Int64); updateCmd.AddParameter("@AllegianceId", DbType.Int64); updateCmd.AddParameter("@State", DbType.Int64); updateCmd.AddParameter("@Security", DbType.Int64); updateCmd.AddParameter("@PrimaryEconomyId", DbType.Int64); updateCmd.AddParameter("@NeedsPermit", DbType.Int64); updateCmd.AddParameter("@EddbUpdatedAt", DbType.Int64); updateSysCmd = cn.CreateCommand("UPDATE EdsmSystems SET EddbId=@EddbId WHERE EdsmId=@EdsmId"); updateSysCmd.AddParameter("@EdsmId", DbType.Int64); updateSysCmd.AddParameter("@EddbId", DbType.Int64); int c = 0; int hasinfo = 0; int lasttc = Environment.TickCount; while (jr.Read()) { if (jr.TokenType == JsonToken.StartObject) { JObject jo = JObject.Load(jr); SystemClass system = new SystemClass(jo, SystemInfoSource.EDDB); if (system.HasEDDBInformation) // screen out for speed any EDDB data with empty interesting fields { hasinfo++; selectCmd.Parameters["@EdsmId"].Value = system.id_edsm; // EDDB carries EDSM ID, so find entry in dB //DEBUGif ( c > 30000 ) Console.WriteLine("EDDB ID " + system.id_eddb + " EDSM ID " + system.id_edsm + " " + system.name + " Late info system"); long updated_at = 0; long population = 0; long eddbid = 0; using (DbDataReader reader1 = selectCmd.ExecuteReader()) // if found (if not, we ignore EDDB system) { if (reader1.Read()) // its there.. check its got the right stuff in it. { eddbid = (long)reader1["EddbId"]; updated_at = (long)reader1["EddbUpdatedAt"]; population = (long)reader1["Population"]; } } updateSysCmd.Parameters["@EdsmId"].Value = system.id_edsm; updateSysCmd.Parameters["@EddbId"].Value = system.id_eddb; updateSysCmd.ExecuteNonQuery(); if (eddbid != 0) { if (updated_at != system.eddb_updated_at || population != system.population) { updateCmd.Parameters["@EddbId"].Value = system.id_eddb; updateCmd.Parameters["@Name"].Value = system.name; updateCmd.Parameters["@Faction"].Value = system.faction; updateCmd.Parameters["@Population"].Value = system.population; updateCmd.Parameters["@GovernmentId"].Value = system.government; updateCmd.Parameters["@AllegianceId"].Value = system.allegiance; updateCmd.Parameters["@State"].Value = system.state; updateCmd.Parameters["@Security"].Value = system.security; updateCmd.Parameters["@PrimaryEconomyId"].Value = system.primary_economy; updateCmd.Parameters["@NeedsPermit"].Value = system.needs_permit; updateCmd.Parameters["@EddbUpdatedAt"].Value = system.eddb_updated_at; updateCmd.Parameters["@EdsmId"].Value = system.id_edsm; updateCmd.ExecuteNonQuery(); updated++; } } else { insertCmd.Parameters["@EdsmId"].Value = system.id_edsm; insertCmd.Parameters["@EddbId"].Value = system.id_eddb; insertCmd.Parameters["@Name"].Value = system.name; insertCmd.Parameters["@Faction"].Value = system.faction; insertCmd.Parameters["@Population"].Value = system.population; insertCmd.Parameters["@GovernmentId"].Value = system.government; insertCmd.Parameters["@AllegianceId"].Value = system.allegiance; insertCmd.Parameters["@State"].Value = system.state; insertCmd.Parameters["@Security"].Value = system.security; insertCmd.Parameters["@PrimaryEconomyId"].Value = system.primary_economy; insertCmd.Parameters["@NeedsPermit"].Value = system.needs_permit; insertCmd.Parameters["@EddbUpdatedAt"].Value = system.eddb_updated_at; insertCmd.ExecuteNonQuery(); inserted++; } } else { //Console.WriteLine("EDDB ID " + system.id_eddb + " EDSM ID " + system.id_edsm + " " + system.name + " No info reject"); } if (++c % 10000 == 0) { Console.WriteLine("EDDB Count " + c + " Delta " + (Environment.TickCount - lasttc) + " info " + hasinfo + " update " + updated + " new " + inserted); lasttc = Environment.TickCount; } } } txn.Commit(); } catch { MessageBox.Show("There is a problem using the EDDB systems file." + Environment.NewLine + "Please perform a manual EDDB sync (see Admin menu) next time you run the program ", "EDDB Sync Error"); } finally { if (selectCmd != null) selectCmd.Dispose(); if (updateCmd != null) updateCmd.Dispose(); if (insertCmd != null) insertCmd.Dispose(); } } } return updated + inserted; }
// Only hidden systems are deleted, and the table is re-synced every // 14 days, so the maximum Id should be very close to the total // system count. public static long GetTotalSystemsFast() { long value = 0; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select MAX(Id) from EdsmSystems")) { using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) value = (long)reader["MAX(Id)"]; } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return value; }
// return a dictionary, in upper case, id is the row ID in the table, duplicates ignored. public static Dictionary<string, int> GetSystemNamesUpperCase() { Dictionary<string, int> dict = new Dictionary<string, int>(StringComparer.CurrentCultureIgnoreCase); try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select name from SystemNames")) { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string name = ((string)reader["name"]).ToUpper(); if (!dict.ContainsKey(name)) dict.Add(name, (int)reader["id"]); } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return dict; }
public static int GetSystemVector(int gridid, ref Vector3[] vertices, ref uint[] colours, SystemAskType ask, int percentage) { int numvertices = 0; vertices = null; colours = null; Color[] fixedc = new Color[4]; fixedc[0] = Color.Red; fixedc[1] = Color.Orange; fixedc[2] = Color.Yellow; fixedc[3] = Color.White; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT id,x,y,z,randomid from EdsmSystems where gridid=@gridid")) { cmd.AddParameterWithValue("gridid", gridid); if (ask == SystemAskType.PopulatedStars) cmd.CommandText += " AND (EddbId IS NOT NULL AND EddbId <> 0)"; else if (ask == SystemAskType.UnPopulatedStars) cmd.CommandText += " AND (EddbId IS NULL OR EddbId = 0)"; if (percentage < 100) cmd.CommandText += " and randomid<" + percentage; //Stopwatch ws = new Stopwatch(); ws.Start(); Object[] array = new Object[5]; // to the number of items above queried vertices = new Vector3[250000]; colours = new uint[250000]; using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { reader.GetValues(array); long id = (long)array[0]; long x = (long)array[1]; long y = (long)array[2]; long z = (long)array[3]; int rand = (int)(long)array[4]; if (numvertices == vertices.Length) { Array.Resize(ref vertices, vertices.Length + 32768); Array.Resize(ref colours, colours.Length + 32768); } Vector3 pos = new Vector3((float)(x / XYZScalar), (float)(y / XYZScalar), (float)(z / XYZScalar)); Color basec = fixedc[rand&3]; int fade = 100 - ((rand>>2)&7) * 8; byte red = (byte)(basec.R * fade / 100); byte green = (byte)(basec.G * fade / 100); byte blue = (byte)(basec.B * fade / 100); colours[numvertices] = BitConverter.ToUInt32(new byte[] { red, green, blue, 255 }, 0); vertices[numvertices++] = pos; } } Array.Resize(ref vertices, numvertices); Array.Resize(ref colours, numvertices); //Console.WriteLine("Query {0} grid {1} ret {2} took {3}", cmd.CommandText, gridid, numvertices, ws.ElapsedMilliseconds); if (gridid == 810 && vertices!=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(vertices, new Vector3(0, 0, 0)); if (solindex >= 0) colours[solindex] = 0x00ffff; //yellow } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return numvertices; }
public static bool Delete(DistancsEnum distsource) { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("Delete from Distances where Status=@Status")) { cmd.AddParameterWithValue("@Status", (int)distsource); SQLiteDBClass.SQLNonQueryText(cn, cmd); } } return true; }
public static bool IsSystemsTableEmpty() { bool isempty = true; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select Id from EdsmSystems LIMIT 1")) { using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) isempty = false; } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return isempty; }
public static void FillVisitedSystems(List<VisitedSystemsClass> visitedSystems, bool usedb) { try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE(NameA = @NameA and NameB = @NameB) OR(NameA = @NameB and NameB = @NameA) limit 1"); for (int i = 1; i < visitedSystems.Count; i++) // now we filled in current system, fill in previous system (except for last) { VisitedSystemsClass cur = visitedSystems[i]; VisitedSystemsClass prev = visitedSystems[i - 1]; cur.prevSystem = prev.curSystem; double dist = SystemClass.Distance(cur.curSystem, prev.curSystem); // Try the easy way if ( dist < 0 && usedb ) // failed, and use the db is allowed.. { cmd.Parameters.Clear(); cmd.AddParameterWithValue("@NameA", cur.Name); cmd.AddParameterWithValue("@NameB", prev.Name); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { DistanceClass dst = new DistanceClass(reader); dist = dst.Dist; } } } if (dist > 0) cur.strDistance = dist.ToString("0.00"); } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } }
public static void RemoveHiddenSystems(string json) { JsonTextReader jr = new JsonTextReader(new StringReader(json)); using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db { using (DbTransaction txn = cn2.BeginTransaction()) { DbCommand infoinscmd = null; DbCommand infodelcmd = null; DbCommand namedelcmd = null; try { infoinscmd = cn2.CreateCommand("INSERT OR IGNORE INTO SystemAliases (name, id_edsm, id_edsm_mergedto) VALUES (@name, @id_edsm, @id_edsm_mergedto)", txn); infoinscmd.AddParameter("@name", DbType.String); infoinscmd.AddParameter("@id_edsm", DbType.Int64); infoinscmd.AddParameter("@id_edsm_mergedto", DbType.Int64); infodelcmd = cn2.CreateCommand("DELETE FROM EdsmSystems WHERE EdsmId=@EdsmId", txn); infodelcmd.AddParameter("@EdsmId", DbType.Int64); namedelcmd = cn2.CreateCommand("DELETE FROM SystemNames WHERE EdsmId=@EdsmId", txn); namedelcmd.AddParameter("@EdsmId", DbType.Int64); while (jr.Read()) { 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"]; } Console.Write("Remove " + edsmid); infodelcmd.Parameters["@EdsmId"].Value = edsmid; infodelcmd.ExecuteNonQuery(); namedelcmd.Parameters["@EdsmId"].Value = edsmid; namedelcmd.ExecuteNonQuery(); if (mergedto > 0) { infoinscmd.Parameters["@name"].Value = name; infoinscmd.Parameters["@id_edsm"].Value = edsmid; infoinscmd.Parameters["@id_edsm_mergedto"].Value = mergedto; infoinscmd.ExecuteNonQuery(); } } } txn.Commit(); } finally { if (infoinscmd != null) infoinscmd.Dispose(); if (infodelcmd != null) infodelcmd.Dispose(); if (namedelcmd != null) namedelcmd.Dispose(); } } } }
public static DistanceClass GetDistanceClass(EDDiscovery2.DB.ISystem s1, EDDiscovery2.DB.ISystem s2) { if (s1 == null || s2 == null) return null; try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT * FROM Distances WHERE (NameA = @NameA and NameB = @NameB) OR (NameA = @NameB and NameB = @NameA) limit 1")) { cmd.AddParameterWithValue("@NameA", s1.name); cmd.AddParameterWithValue("@NameB", s2.name); DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) // if found. { DistanceClass dist = new DistanceClass(ds.Tables[0].Rows[0]); return dist; } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return null; }
private static long DoParseEDSMUpdateSystemsReader(JsonTextReader jr, ref string date, ref bool outoforder, SQLiteConnectionSystem cn, EDDiscoveryForm discoveryform, Func<bool> cancelRequested, Action<int, string> reportProgress, bool useCache = true, bool useTempSystems = false) { DateTime maxdate; if (!DateTime.TryParse(date, CultureInfo.InvariantCulture, DateTimeStyles.None, out maxdate)) { maxdate = new DateTime(2010, 1, 1); } Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase> systemsByEdsmId = useCache ? GetEdsmSystemsLite(cn) : new Dictionary<long, EDDiscovery2.DB.InMemory.SystemClassBase>(); int count = 0; int updatecount = 0; int insertcount = 0; Random rnd = new Random(); int[] histogramsystems = new int[50000]; string sysnamesTableName = useTempSystems ? "SystemNames_temp" : "SystemNames"; string edsmsysTableName = useTempSystems ? "EdsmSystems_temp" : "EdsmSystems"; Stopwatch sw = Stopwatch.StartNew(); while (!cancelRequested()) { bool jr_eof = false; List<JObject> objs = new List<JObject>(); while (!cancelRequested()) { if (jr.Read()) { if (jr.TokenType == JsonToken.StartObject) { objs.Add(JObject.Load(jr)); if (objs.Count >= 10000) { break; } } } else { jr_eof = true; break; } } using (DbTransaction txn = cn.BeginTransaction()) { DbCommand updateNameCmd = null; DbCommand updateSysCmd = null; DbCommand insertNameCmd = null; DbCommand insertSysCmd = null; DbCommand selectSysCmd = null; DbCommand selectNameCmd = null; try { updateNameCmd = cn.CreateCommand("UPDATE SystemNames SET Name=@Name WHERE EdsmId=@EdsmId", txn); updateNameCmd.AddParameter("@Name", DbType.String); updateNameCmd.AddParameter("@EdsmId", DbType.Int64); updateSysCmd = cn.CreateCommand("UPDATE EdsmSystems SET X=@X, Y=@Y, Z=@Z, UpdateTimestamp=@UpdateTimestamp, VersionTimestamp=@VersionTimestamp, GridId=@GridId, RandomId=@RandomId WHERE EdsmId=@EdsmId", txn); updateSysCmd.AddParameter("@X", DbType.Int64); updateSysCmd.AddParameter("@Y", DbType.Int64); updateSysCmd.AddParameter("@Z", DbType.Int64); updateSysCmd.AddParameter("@UpdateTimestamp", DbType.Int64); updateSysCmd.AddParameter("@VersionTimestamp", DbType.Int64); updateSysCmd.AddParameter("@GridId", DbType.Int64); updateSysCmd.AddParameter("@RandomId", DbType.Int64); updateSysCmd.AddParameter("@EdsmId", DbType.Int64); insertNameCmd = cn.CreateCommand("INSERT INTO " + sysnamesTableName + " (Name, EdsmId) VALUES (@Name, @EdsmId)", txn); insertNameCmd.AddParameter("@Name", DbType.String); insertNameCmd.AddParameter("@EdsmId", DbType.Int64); insertSysCmd = cn.CreateCommand("INSERT INTO " + edsmsysTableName + " (EdsmId, X, Y, Z, CreateTimestamp, UpdateTimestamp, VersionTimestamp, GridId, RandomId) VALUES (@EdsmId, @X, @Y, @Z, @CreateTimestamp, @UpdateTimestamp, @VersionTimestamp, @GridId, @RandomId)", txn); insertSysCmd.AddParameter("@EdsmId", DbType.Int64); insertSysCmd.AddParameter("@X", DbType.Int64); insertSysCmd.AddParameter("@Y", DbType.Int64); insertSysCmd.AddParameter("@Z", DbType.Int64); insertSysCmd.AddParameter("@CreateTimestamp", DbType.Int64); insertSysCmd.AddParameter("@UpdateTimestamp", DbType.Int64); insertSysCmd.AddParameter("@VersionTimestamp", DbType.Int64); insertSysCmd.AddParameter("@GridId", DbType.Int64); insertSysCmd.AddParameter("@RandomId", DbType.Int64); selectSysCmd = cn.CreateCommand("SELECT Id, X, Y, Z, GridId, RandomId FROM EdsmSystems WHERE EdsmId=@EdsmId"); selectSysCmd.AddParameter("@EdsmId", DbType.Int64); selectNameCmd = cn.CreateCommand("SELECT Name FROM SystemNames WHERE EdsmId = @EdsmId"); selectNameCmd.AddParameter("@EdsmId", DbType.Int64); IEnumerator<JObject> jo_enum = objs.GetEnumerator(); while (!cancelRequested()) { if (!jo_enum.MoveNext()) { reportProgress(-1, $"Syncing EDSM systems: {count} processed, {insertcount} new systems, {updatecount} updated systems"); txn.Commit(); if (jr_eof) { date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture); Console.WriteLine($"Import took {sw.ElapsedMilliseconds}ms"); for (int id = 0; id < histogramsystems.Length; id++) { if (histogramsystems[id] != 0) Console.WriteLine("Id " + id + " count " + histogramsystems[id]); } return updatecount + insertcount; } break; } JObject jo = jo_enum.Current; JObject coords = (JObject)jo["coords"]; if (coords != null && (coords["x"].Type == JTokenType.Float || coords["x"].Type == JTokenType.Integer)) { double x = coords["x"].Value<double>(); double y = coords["y"].Value<double>(); double z = coords["z"].Value<double>(); long edsmid = jo["id"].Value<long>(); string name = jo["name"].Value<string>(); int gridid = GridId.Id(x, z); int randomid = rnd.Next(0, 99); DateTime updatedate = jo["date"].Value<DateTime>(); histogramsystems[gridid]++; if (updatedate > maxdate) maxdate = updatedate; else if (updatedate < maxdate - TimeSpan.FromHours(1)) outoforder = true; EDDiscovery2.DB.InMemory.SystemClassBase dbsys = null; if (!useTempSystems) { if (useCache && systemsByEdsmId.ContainsKey(edsmid)) dbsys = systemsByEdsmId[edsmid]; if (!useCache) { selectSysCmd.Parameters["@EdsmId"].Value = edsmid; using (DbDataReader reader = selectSysCmd.ExecuteReader()) { if (reader.Read()) { dbsys = new EDDiscovery2.DB.InMemory.SystemClassBase { id = (long)reader["id"], id_edsm = edsmid }; if (System.DBNull.Value == reader["x"]) { dbsys.x = double.NaN; dbsys.y = double.NaN; dbsys.z = double.NaN; } else { dbsys.x = ((double)(long)reader["X"]) / XYZScalar; dbsys.y = ((double)(long)reader["Y"]) / XYZScalar; dbsys.z = ((double)(long)reader["Z"]) / XYZScalar; } dbsys.id_edsm = edsmid; dbsys.gridid = reader["GridId"] == DBNull.Value ? 0 : (int)((long)reader["GridId"]); dbsys.randomid = reader["RandomId"] == DBNull.Value ? 0 : (int)((long)reader["RandomId"]); } } if (dbsys != null) { selectNameCmd.Parameters["@EdsmId"].Value = edsmid; using (DbDataReader reader = selectNameCmd.ExecuteReader()) { if (reader.Read()) { dbsys.name = (string)reader["Name"]; } } } } } if (dbsys != null) { // see if EDSM data changed.. if (!dbsys.name.Equals(name)) { updateNameCmd.Parameters["@Name"].Value = name; updateNameCmd.Parameters["@EdsmId"].Value = edsmid; updateNameCmd.ExecuteNonQuery(); } if (Math.Abs(dbsys.x - x) > 0.01 || Math.Abs(dbsys.y - y) > 0.01 || Math.Abs(dbsys.z - z) > 0.01 || dbsys.gridid != gridid) // position changed { updateSysCmd.Parameters["@X"].Value = (long)(x * XYZScalar); updateSysCmd.Parameters["@Y"].Value = (long)(y * XYZScalar); updateSysCmd.Parameters["@Z"].Value = (long)(z * XYZScalar); updateSysCmd.Parameters["@UpdateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds; updateSysCmd.Parameters["@VersionTimestamp"].Value = DateTime.UtcNow.Subtract(new DateTime(2015, 1, 1)).TotalSeconds; updateSysCmd.Parameters["@GridId"].Value = gridid; updateSysCmd.Parameters["@RandomId"].Value = randomid; updateSysCmd.Parameters["@EdsmId"].Value = edsmid; updateSysCmd.ExecuteNonQuery(); updatecount++; } } else // not in database.. { insertNameCmd.Parameters["@Name"].Value = name; insertNameCmd.Parameters["@EdsmId"].Value = edsmid; insertNameCmd.ExecuteNonQuery(); insertSysCmd.Parameters["@EdsmId"].Value = edsmid; insertSysCmd.Parameters["@X"].Value = (long)(x * XYZScalar); insertSysCmd.Parameters["@Y"].Value = (long)(y * XYZScalar); insertSysCmd.Parameters["@Z"].Value = (long)(z * XYZScalar); insertSysCmd.Parameters["@CreateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds; insertSysCmd.Parameters["@UpdateTimestamp"].Value = updatedate.Subtract(new DateTime(2015, 1, 1)).TotalSeconds; insertSysCmd.Parameters["@VersionTimestamp"].Value = DateTime.UtcNow.Subtract(new DateTime(2015, 1, 1)).TotalSeconds; insertSysCmd.Parameters["@GridId"].Value = gridid; insertSysCmd.Parameters["@RandomId"].Value = randomid; insertSysCmd.ExecuteNonQuery(); insertcount++; } } count++; } } finally { if (updateNameCmd != null) updateNameCmd.Dispose(); if (updateSysCmd != null) updateSysCmd.Dispose(); if (insertNameCmd != null) insertNameCmd.Dispose(); if (insertSysCmd != null) insertSysCmd.Dispose(); if (selectSysCmd != null) selectSysCmd.Dispose(); } } } if (cancelRequested()) { throw new OperationCanceledException(); } return updatecount + insertcount; }
public static List<DistanceClass> GetDistancesByStatus(int status) { List<DistanceClass> ldist = new List<DistanceClass>(); try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select * from Distances WHERE status='" + status.ToString() + "'")) { DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { DistanceClass dist = new DistanceClass(dr); ldist.Add(dist); } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return ldist; }
public static List<long> GetEdsmIdsFromName(string name) { List<long> ret = new List<long>(); if (name.Length > 0) { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("SELECT Name,EdsmId FROM SystemNames WHERE Name==@first")) { cmd.AddParameterWithValue("first", name); //Console.WriteLine("Look up {0}", name); using (DbDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { ret.Add((long)rdr[1]); } } } } } return ret; }
public bool Store() { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { bool ret; ret = Store(cn); if (ret == true) { using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Distances")) { id = (long)SQLiteDBClass.SQLScalar(cn, cmd2); } return true; } return ret; } }
// return star positions.. public static List<Point3D> GetStarPositions() { List<Point3D> list = new List<Point3D>(); try { using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) { using (DbCommand cmd = cn.CreateCommand("select x,y,z from EdsmSystems")) { using (DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (System.DBNull.Value != reader["x"]) list.Add(new Point3D(((double)(long)reader["x"]) / XYZScalar, ((double)(long)reader["y"]) / XYZScalar, ((double)(long)reader["z"]) / XYZScalar)); } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine("Exception : " + ex.Message); System.Diagnostics.Trace.WriteLine(ex.StackTrace); } return list; }
private static long ParseEDSMUpdateDistancesReader(JsonTextReader jr, ref string date , bool removenonedsmids, Func<bool> cancelRequested, Action<int, string> reportProgress) { List<DistanceClass> toupdate = new List<DistanceClass>(); List<DistanceClass> newpairs = new List<DistanceClass>(); DateTime maxdate = DateTime.Parse(date, new CultureInfo("sv-SE")); bool emptydatabase = GetTotalDistances() == 0; // if empty database, we can skip the lookup using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem()) // open the db { int c = 0; DbCommand cmd = null; int lasttc = Environment.TickCount; try { cmd = cn.CreateCommand("select * from Distances where id_edsm=@id limit 1"); // 1 return matching while (jr.Read() && !cancelRequested()) { if (jr.TokenType == JsonToken.StartObject) { JObject jo = JObject.Load(jr); DistanceClass dc = new DistanceClass(jo); if (dc.CreateTime.Subtract(maxdate).TotalSeconds > 0) maxdate = dc.CreateTime; if (++c % 10000 == 0) { Console.WriteLine("Dist Count " + c + " Delta " + (Environment.TickCount - lasttc) + " newpairs " + newpairs.Count + " update " + toupdate.Count()); reportProgress(-1, $"Reading EDSM distances: {c} processed, {newpairs.Count} new, {toupdate.Count} to update"); lasttc = Environment.TickCount; } if (emptydatabase) // empty DB, just store.. { newpairs.Add(dc); } else { cmd.Parameters.Clear(); cmd.AddParameterWithValue("id", dc.id_edsm); using (DbDataReader reader1 = cmd.ExecuteReader()) // see if ESDM ID is there.. { if (reader1.Read()) // its there.. { DistanceClass dbdc = new DistanceClass(reader1); // see if EDSM data changed.. if (!dbdc.NameA.Equals(dc.NameA) || !dbdc.NameB.Equals(dc.NameB) || Math.Abs(dbdc.Dist - dc.Dist) > 0.05) { dbdc.NameA = dc.NameA; dbdc.NameB = dc.NameB; dbdc.Dist = dc.Dist; toupdate.Add(dbdc); } } else // not in database.. { //Console.WriteLine("Add new system " + system.name); newpairs.Add(dc); } } } } } } catch { MessageBox.Show("There is a problem using the EDSM distance file." + Environment.NewLine + "Please perform a manual EDSM distance sync (see Admin menu) next time you run the program ", "ESDM Sync Error"); } finally { if (cmd != null) cmd.Dispose(); } } if (cancelRequested()) { return 0; } using (SQLiteConnectionSystem cn2 = new SQLiteConnectionSystem()) // open the db { if (toupdate.Count > 0) { reportProgress(-1, $"Updating EDSM distances: {toupdate.Count} distances to update"); using (DbTransaction transaction = cn2.BeginTransaction()) { foreach (DistanceClass dc in toupdate) dc.Update(cn2, transaction); transaction.Commit(); } } if (cancelRequested()) { return toupdate.Count(); } if (newpairs.Count > 0) { int count = 0; while (count < newpairs.Count()) { using (DbTransaction transaction = cn2.BeginTransaction()) { while (count < newpairs.Count()) { newpairs[count].Store(cn2, transaction); if (++count % 10000 == 0) break; } reportProgress(count * 100 / newpairs.Count, $"Adding EDSM distances: {count} added"); Console.WriteLine("EDSM Dist Store Count " + count); transaction.Commit(); } if (cancelRequested()) { return toupdate.Count() + count; } } } if (removenonedsmids) // done on a full sync.. { reportProgress(-1, "Removing distances without an ID"); Console.WriteLine("Delete old ones"); using (DbCommand cmddel = cn2.CreateCommand("Delete from Distances where id_edsm is null")) { SQLiteDBClass.SQLNonQueryText(cn2, cmddel); } } } date = maxdate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture); return toupdate.Count + newpairs.Count; }
public static SystemClass GetSystemNearestTo(double x, double y, double z, SQLiteConnectionSystem conn) { using (DbCommand selectByPosCmd = conn.CreateCommand( "SELECT s.EdsmId FROM EdsmSystems s " + // 16 is 0.125 of 1/128, so pick system near this one "WHERE s.X >= @X - 16 " + "AND s.X <= @X + 16 " + "AND s.Y >= @Y - 16 " + "AND s.Y <= @Y + 16 " + "AND s.Z >= @Z - 16 " + "AND s.Z <= @Z + 16 LIMIT 1")) { selectByPosCmd.AddParameterWithValue("@X", (long)(x * XYZScalar)); selectByPosCmd.AddParameterWithValue("@Y", (long)(y * XYZScalar)); selectByPosCmd.AddParameterWithValue("@Z", (long)(z * XYZScalar)); using (DbDataReader reader = selectByPosCmd.ExecuteReader()) // MEASURED very fast, <1ms { while (reader.Read()) { long pos_edsmid = (long)reader["EdsmId"]; SystemClass sys = GetSystem(pos_edsmid, conn, SystemIDType.EdsmId); return sys; } } } return null; }
private bool Delete(SQLiteConnectionSystem cn) { using (DbCommand cmd = cn.CreateCommand("Delete From Distances where ID=@id")) { cmd.AddParameterWithValue("@ID", id); SQLiteDBClass.SQLNonQueryText(cn, cmd); return true; } }
private static void CreateSystemDBTableIndexes() { string[] queries = new[] { "CREATE UNIQUE INDEX IF NOT EXISTS SystemAliases_id_edsm ON SystemAliases (id_edsm)", "CREATE INDEX IF NOT EXISTS SystemAliases_name ON SystemAliases (name)", "CREATE INDEX IF NOT EXISTS SystemAliases_id_edsm_mergedto ON SystemAliases (id_edsm_mergedto)", "CREATE INDEX IF NOT EXISTS Distances_EDSM_ID_Index ON Distances (id_edsm ASC)", "CREATE INDEX IF NOT EXISTS DistanceName ON Distances (NameA ASC, NameB ASC)", }; using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem()) { foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } } }
private bool Store(SQLiteConnectionSystem cn, DbTransaction tn = null) { if (CommanderCreate == null) CommanderCreate = ""; using (DbCommand cmd = cn.CreateCommand("Insert into Distances (NameA, NameB, Dist, CommanderCreate, CreateTime, Status, id_edsm) values (@NameA, @NameB, @Dist, @CommanderCreate, @CreateTime, @Status, @id_edsm)",tn)) { cmd.AddParameterWithValue("@NameA", NameA); cmd.AddParameterWithValue("@NameB", NameB); cmd.AddParameterWithValue("@Dist", Dist); cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate); cmd.AddParameterWithValue("@CreateTime", CreateTime); cmd.AddParameterWithValue("@Status", Status); cmd.AddParameterWithValue("@id_edsm", id_edsm); SQLiteDBClass.SQLNonQueryText(cn, cmd); } return true; }
public static void DropSystemsTableIndexes() { string[] queries = new[] { "DROP INDEX IF EXISTS SystemsIndex", "DROP INDEX IF EXISTS Systems_EDSM_ID_Index", "DROP INDEX IF EXISTS Systems_EDDB_ID_Index", "DROP INDEX IF EXISTS IDX_Systems_versiondate", "DROP INDEX IF EXISTS Systems_position", "DROP INDEX IF EXISTS SystemGridId", "DROP INDEX IF EXISTS SystemRandomId", "DROP INDEX IF EXISTS EdsmSystems_EdsmId", "DROP INDEX IF EXISTS EdsmSystems_EddbId", "DROP INDEX IF EXISTS EddbSystems_EdsmId", "DROP INDEX IF EXISTS EddbSystems_EddbId", "DROP INDEX IF EXISTS EdsmSystems_Position", "DROP INDEX IF EXISTS EdsmSystems_GridId", "DROP INDEX IF EXISTS EdsmSystems_RandomId", "DROP INDEX IF EXISTS SystemNames_EdsmId" }; using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem()) { foreach (string query in queries) { using (DbCommand cmd = conn.CreateCommand(query)) { cmd.ExecuteNonQuery(); } } } }
private bool Update(SQLiteConnectionSystem cn, DbTransaction tn = null) { using (DbCommand cmd = cn.CreateCommand("Update Distances set NameA=@NameA, NameB=@NameB, Dist=@Dist, commandercreate=@commandercreate, CreateTime=@CreateTime, status=@status, id_edsm=@id_edsm where ID=@id",tn)) { cmd.AddParameterWithValue("@ID", id); cmd.AddParameterWithValue("@NameA", NameA); cmd.AddParameterWithValue("@NameB", NameB); cmd.AddParameterWithValue("@Dist", Dist); cmd.AddParameterWithValue("@CommanderCreate", CommanderCreate); cmd.AddParameterWithValue("@CreateTime", CreateTime); cmd.AddParameterWithValue("@Status", Status); cmd.AddParameterWithValue("@id_edsm", id_edsm); SQLiteDBClass.SQLNonQueryText(cn, cmd); return true; } }