private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into routes_expeditions (name, start, end) values (@name, @start, @end)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from routes_expeditions"))
{
Id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return(true);
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("UPDATE routes_expeditions SET name=@name, start=@start, end=@end WHERE id=@id"))
{
cmd.AddParameterWithValue("@id", Id);
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
{
cmd2.AddParameterWithValue("@routeid", Id);
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into wanted_systems (systemname) values (@systemname)"))
{
cmd.AddParameterWithValue("@systemname", system);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from wanted_systems"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
public static bool GetAllBookmarks()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from Bookmarks"))
{
DataSet ds = null;
ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return false;
}
bookmarks.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
BookmarkClass bc = new BookmarkClass(dr);
bookmarks.Add(bc);
}
return true;
}
}
}
catch
{
return false;
}
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return false;
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes[sys.Name.ToLower()] = sys;
}
return true;
}
}
}
catch
{
return false;
}
}
public static bool GetAllBookmarks()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from Bookmarks"))
{
DataSet ds = null;
ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
bookmarks.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
BookmarkClass bc = new BookmarkClass(dr);
bookmarks.Add(bc);
}
return(true);
}
}
}
catch
{
return(false);
}
}
public bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM routes_expeditions WHERE id=@id"))
{
cmd.AddParameterWithValue("@id", Id);
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
{
cmd.AddParameterWithValue("@routeid", Id);
cmd.ExecuteNonQuery();
}
return(true);
}
public static List<WantedSystemClass> GetAllWantedSystems()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return null;
}
List<WantedSystemClass> retVal = new List<WantedSystemClass>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
WantedSystemClass sys = new WantedSystemClass(dr);
retVal.Add(sys);
}
return retVal;
}
}
}
catch
{
return null;
}
}
public static List <WantedSystemClass> GetAllWantedSystems()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from wanted_systems"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
List <WantedSystemClass> retVal = new List <WantedSystemClass>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
WantedSystemClass sys = new WantedSystemClass(dr);
retVal.Add(sys);
}
return(retVal);
}
}
}
catch
{
return(null);
}
}
public static bool GetAllSystemNotes()
{
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from SystemNote"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(false);
}
globalSystemNotes.Clear();
foreach (DataRow dr in ds.Tables[0].Rows)
{
SystemNoteClass sys = new SystemNoteClass(dr);
globalSystemNotes.Add(sys);
}
return(true);
}
}
}
catch
{
return(false);
}
}
public static void ClearEDSMID()
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(utc: true))
{
using (DbCommand cmd = cn.CreateCommand("UPDATE SystemNote SET EdsmId=0"))
{
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into TravelLogUnit (Name, type, size, Path, CommanderID) values (@name, @type, @size, @Path, @CommanderID)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from TravelLogUnit"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return(true);
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM wanted_systems WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM Bookmarks WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
return(true);
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into SystemNote (Name, Time, Note, journalid, edsmid) values (@name, @time, @note, @journalid, @edsmid)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@note", Note);
cmd.AddParameterWithValue("@journalid", Journalid);
cmd.AddParameterWithValue("@edsmid", EdsmId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from SystemNote"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
globalSystemNotes.Add(this);
return(true);
}
}
public static List<SavedRouteClass> GetAllSavedRoutes()
{
List<SavedRouteClass> retVal = new List<SavedRouteClass>();
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd1 = cn.CreateCommand("select * from routes_expeditions"))
{
DataSet ds1 = SQLiteDBClass.SQLQueryText(cn, cmd1);
if (ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
using (DbCommand cmd2 = cn.CreateCommand("select * from route_systems"))
{
DataSet ds2 = SQLiteDBClass.SQLQueryText(cn, cmd2);
foreach (DataRow dr in ds1.Tables[0].Rows)
{
DataRow[] syslist = new DataRow[0];
if (ds2.Tables.Count != 0)
{
syslist = ds2.Tables[0].Select(String.Format("routeid = {0}", dr["id"]), "id ASC");
}
SavedRouteClass sys = new SavedRouteClass(dr, syslist);
retVal.Add(sys);
}
}
}
}
}
}
catch
{
}
return retVal;
}
public static List <SavedRouteClass> GetAllSavedRoutes()
{
List <SavedRouteClass> retVal = new List <SavedRouteClass>();
try
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd1 = cn.CreateCommand("select * from routes_expeditions"))
{
DataSet ds1 = SQLiteDBClass.SQLQueryText(cn, cmd1);
if (ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0)
{
using (DbCommand cmd2 = cn.CreateCommand("select * from route_systems"))
{
DataSet ds2 = SQLiteDBClass.SQLQueryText(cn, cmd2);
foreach (DataRow dr in ds1.Tables[0].Rows)
{
DataRow[] syslist = new DataRow[0];
if (ds2.Tables.Count != 0)
{
syslist = ds2.Tables[0].Select(String.Format("routeid = {0}", dr["id"]), "id ASC");
}
SavedRouteClass sys = new SavedRouteClass(dr, syslist);
retVal.Add(sys);
}
}
}
}
}
}
catch
{
}
return(retVal);
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into Bookmarks (StarName, x, y, z, Time, Heading, Note) values (@sname, @xp, @yp, @zp, @time, @head, @note)"))
{
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Bookmarks"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
bookmarks.Add(this);
return(true);
}
}
public bool Update(SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Update TravelLogUnit set Name=@Name, Type=@type, size=@size, Path=@Path, CommanderID=@CommanderID where ID=@id", tn))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return(true);
}
}
public bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update SystemNote set Name=@Name, Time=@Time, Note=@Note, Journalid=@journalid, EdsmId=@EdsmId where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Note", Note);
cmd.AddParameterWithValue("@Time", Time);
cmd.AddParameterWithValue("@journalid", Journalid);
cmd.AddParameterWithValue("@EdsmId", EdsmId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
}
GetAllSystemNotes();
return(true);
}
public static TravelLogUnit Get(string name)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM TravelLogUnit WHERE Name = @name ORDER BY Id DESC"))
{
cmd.AddParameterWithValue("@name", name);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return new TravelLogUnit(reader);
}
}
}
}
return null;
}
public static List <string> GetAllNames()
{
List <string> names = new List <string>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("SELECT DISTINCT Name FROM TravelLogUnit"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
names.Add((string)reader["Name"]);
}
}
}
}
return(names);
}
public static TravelLogUnit Get(string name)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM TravelLogUnit WHERE Name = @name ORDER BY Id DESC"))
{
cmd.AddParameterWithValue("@name", name);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new TravelLogUnit(reader));
}
}
}
}
return(null);
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update Bookmarks set StarName=@sname, x = @xp, y = @yp, z = @zp, Time=@time, Heading = @head, Note=@note where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
bookmarks.Add(this);
return(true);
}
}
private static void DropOldUserTables(SQLiteConnectionUser conn)
{
string[] queries = new[]
{
"DROP TABLE IF EXISTS Systems",
"DROP TABLE IF EXISTS SystemAliases",
"DROP TABLE IF EXISTS Distances",
"DROP TABLE IF EXISTS Stations",
"DROP TABLE IF EXISTS station_commodities",
"DROP TABLE IF EXISTS Journals",
"DROP TABLE IF EXISTS VisitedSystems",
"DROP TABLE IF EXISTS Objects"
};
foreach (string query in queries)
{
using (DbCommand cmd = conn.CreateCommand(query))
{
cmd.ExecuteNonQuery();
}
}
}
private static void CreateUserDBTableIndexes(SQLiteConnectionUser conn)
{
string[] queries = new[]
{
"CREATE INDEX IF NOT EXISTS TravelLogUnit_Name ON TravelLogUnit (Name)",
"CREATE INDEX IF NOT EXISTS TravelLogUnit_Commander ON TravelLogUnit(CommanderId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_TravelLogId ON JournalEntries (TravelLogId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventTypeId ON JournalEntries (EventTypeId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventType ON JournalEntries (EventType)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventTime ON JournalEntries (EventTime)",
"CREATE INDEX IF NOT EXISTS MaterialsCommodities_ClassName ON MaterialsCommodities (Name)",
"CREATE INDEX IF NOT EXISTS MaterialsCommodities_FDName ON MaterialsCommodities (FDName)",
};
foreach (string query in queries)
{
using (DbCommand cmd = conn.CreateCommand(query))
{
cmd.ExecuteNonQuery();
}
}
}
static public List <TravelLogUnit> GetAll()
{
List <TravelLogUnit> list = new List <TravelLogUnit>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from TravelLogUnit"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(list);
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
TravelLogUnit sys = new TravelLogUnit(dr);
list.Add(sys);
}
return(list);
}
}
}
internal static bool Exist(string name, DateTime time)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems where name=@name and Time=@time Order by Time DESC Limit 1"))
{
cmd.AddParameterWithValue("@name", name);
cmd.AddParameterWithValue("@time", time);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
return !(ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0);
}
}
}
public bool Add(SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Insert into VisitedSystems (Name, Time, Unit, Commander, Source, edsm_sync, map_colour, X, Y, Z, id_edsm_assigned) values (@name, @time, @unit, @commander, @source, @edsm_sync, @map_colour, @x, @y, @z, @id_edsm_assigned)", tn))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@unit", Unit);
cmd.AddParameterWithValue("@commander", Commander);
cmd.AddParameterWithValue("@source", Source);
cmd.AddParameterWithValue("@edsm_sync", EDSM_sync);
cmd.AddParameterWithValue("@map_colour", MapColour);
cmd.AddParameterWithValue("@x", X);
cmd.AddParameterWithValue("@y", Y);
cmd.AddParameterWithValue("@z", Z);
cmd.AddParameterWithValue("@id_edsm_assigned", id_edsm_assigned);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from VisitedSystems"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return true;
}
}
public static VisitedSystemsClass GetLast(int cmdrid, DateTime before)
{
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM VisitedSystems WHERE Commander = @commander AND Time < @before ORDER BY Time DESC LIMIT 1"))
{
cmd.AddParameterWithValue("@commander", cmdrid);
cmd.AddParameterWithValue("@before", before);
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return new VisitedSystemsClass(reader);
}
}
}
}
return null;
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into routes_expeditions (name, start, end) values (@name, @start, @end)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from routes_expeditions"))
{
Id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return true;
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update Bookmarks set StarName=@sname, x = @xp, y = @yp, z = @zp, Time=@time, Heading = @head, Note=@note where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
bookmarks.Add(this);
return true;
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into Bookmarks (StarName, x, y, z, Time, Heading, Note) values (@sname, @xp, @yp, @zp, @time, @head, @note)"))
{
cmd.AddParameterWithValue("@sname", StarName);
cmd.AddParameterWithValue("@xp", x);
cmd.AddParameterWithValue("@yp", y);
cmd.AddParameterWithValue("@zp", z);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@head", Heading);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from Bookmarks"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
bookmarks.Add(this);
return true;
}
}
private static void CreateUserDBTableIndexes()
{
string[] queries = new[]
{
"CREATE INDEX IF NOT EXISTS stationIndex ON Stations (system_id ASC)",
"CREATE INDEX IF NOT EXISTS VisitedSystemIndex ON VisitedSystems (Name ASC, Time 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)",
"CREATE INDEX IF NOT EXISTS VisitedSystems_id_edsm_assigned ON VisitedSystems (id_edsm_assigned)",
"CREATE INDEX IF NOT EXISTS VisitedSystems_position ON VisitedSystems (X, Y, Z)",
"CREATE INDEX IF NOT EXISTS TravelLogUnit_Name ON TravelLogUnit (Name)"
};
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
foreach (string query in queries)
{
using (DbCommand cmd = conn.CreateCommand(query))
{
cmd.ExecuteNonQuery();
}
}
}
}
private static void CreateUserDBTableIndexes()
{
string[] queries = new[]
{
"CREATE INDEX IF NOT EXISTS TravelLogUnit_Name ON TravelLogUnit (Name)",
"CREATE INDEX IF NOT EXISTS TravelLogUnit_Commander ON TravelLogUnit(CommanderId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_TravelLogId ON JournalEntries (TravelLogId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventTypeId ON JournalEntries (EventTypeId)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventType ON JournalEntries (EventType)",
"CREATE INDEX IF NOT EXISTS JournalEntry_EventTime ON JournalEntries (EventTime)",
};
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
foreach (string query in queries)
{
using (DbCommand cmd = conn.CreateCommand(query))
{
cmd.ExecuteNonQuery();
}
}
}
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into SystemNote (Name, Time, Note) values (@name, @time, @note)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@time", Time);
cmd.AddParameterWithValue("@note", Note);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from SystemNote"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
globalSystemNotes[Name.ToLower()]= this;
return true;
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update VisitedSystems set Name=@Name, Time=@Time, Unit=@Unit, Commander=@commander, Source=@Source, edsm_sync=@edsm_sync, map_colour=@map_colour, X=@x, Y=@y, Z=@z, id_edsm_assigned=@id_edsm_assigned where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Time", Time);
cmd.AddParameterWithValue("@unit", Unit);
cmd.AddParameterWithValue("@commander", Commander);
cmd.AddParameterWithValue("@source", Source);
cmd.AddParameterWithValue("@edsm_sync", EDSM_sync);
cmd.AddParameterWithValue("@map_colour", MapColour);
cmd.AddParameterWithValue("@x", X);
cmd.AddParameterWithValue("@y", Y);
cmd.AddParameterWithValue("@z", Z);
cmd.AddParameterWithValue("@id_edsm_assigned", id_edsm_assigned);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return true;
}
}
public static void TranferVisitedSystemstoJournalTable() // DONE purposely without using any VisitedSystem code.. so we can blow it away later.
{
List <Object[]> ehl = new List <Object[]>();
Dictionary <string, Dictionary <string, double> > dists = new Dictionary <string, Dictionary <string, double> >(StringComparer.CurrentCultureIgnoreCase);
List <EDDiscovery2.DB.TravelLogUnit> tlus = EDDiscovery2.DB.TravelLogUnit.GetAll().Where(t => t.type == 1).ToList();
using (SQLiteConnectionOld conn = new SQLiteConnectionOld())
{
// 0 1 2
using (DbCommand cmd = conn.CreateCommand("SELECT NameA, NameB, Dist FROM Distances WHERE Status >= 1")) // any distance pairs okay
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
object[] vals = new object[3];
reader.GetValues(vals);
string namea = (string)vals[0];
string nameb = (string)vals[1];
double dist = (double)vals[2];
if (!dists.ContainsKey(namea))
{
dists[namea] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase);
}
dists[namea][nameb] = dist;
if (!dists.ContainsKey(nameb))
{
dists[nameb] = new Dictionary <string, double>(StringComparer.CurrentCultureIgnoreCase);
}
dists[nameb][namea] = dist;
}
}
}
int olddbver = SQLiteConnectionOld.GetSettingInt("DBVer", 1);
if (olddbver < 7) // 2.5.2
{
System.Diagnostics.Trace.WriteLine("Database too old - unable to migrate travel log");
return;
}
string query;
if (olddbver < 8) // 2.5.6
{
query = "Select Name,Time,Unit,Commander,edsm_sync, -65536 AS Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time";
}
else if (olddbver < 14) // 3.2.1
{
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour, NULL AS X, NULL AS Y, NULL AS Z, NULL as id_edsm_assigned From VisitedSystems Order By Time";
}
else if (olddbver < 18) // 4.0.2
{
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z, NULL AS id_edsm_assigned From VisitedSystems Order By Time";
}
else
{
// 0 1 2 3 4 5 6 7 8 9
query = "Select Name,Time,Unit,Commander,edsm_sync,Map_colour,X,Y,Z,id_edsm_assigned From VisitedSystems Order By Time";
}
using (DbCommand cmd = conn.CreateCommand(query))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
string prev = "";
while (reader.Read())
{
Object[] array = new Object[17];
reader.GetValues(array); // love this call.
string tluname = (string)array[2]; // 2 is in terms of its name.. look it up
if (tluname.StartsWith("EDSM-")) // Don't migrate the entries that were synced from EDSM
{ // We can sync them from EDSM later.
continue;
}
EDDiscovery2.DB.TravelLogUnit tlu = tlus.Find(x => x.Name.Equals(tluname, StringComparison.InvariantCultureIgnoreCase));
array[15] = (tlu != null) ? (long)tlu.id : 0; // even if we don't find it, tlu may be screwed up, still want to import
array[16] = null;
if (prev.Length > 0 && dists.ContainsKey((string)array[0]))
{
Dictionary <string, double> _dists = dists[(string)array[0]];
if (_dists.ContainsKey(prev))
{
array[16] = _dists[prev];
}
}
ehl.Add(array);
prev = (string)array[0];
}
}
}
}
using (SQLiteConnectionUser conn = new SQLiteConnectionUser(utc: true))
{
using (DbTransaction txn = conn.BeginTransaction())
{
foreach (Object[] array in ehl)
{
using (DbCommand cmd = conn.CreateCommand(
"Insert into JournalEntries (TravelLogId,CommanderId,EventTypeId,EventType,EventTime,EventData,EdsmId,Synced) " +
"values (@tli,@cid,@eti,@et,@etime,@edata,@edsmid,@synced)", txn))
{
cmd.AddParameterWithValue("@tli", (long)array[15]);
cmd.AddParameterWithValue("@cid", (long)array[3]);
cmd.AddParameterWithValue("@eti", EDDiscovery.EliteDangerous.JournalTypeEnum.FSDJump);
cmd.AddParameterWithValue("@et", "FSDJump");
JObject je = new JObject();
DateTime eventtime = DateTime.SpecifyKind((DateTime)array[1], DateTimeKind.Local).ToUniversalTime();
je["timestamp"] = eventtime.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'");
je["event"] = "FSDJump";
je["StarSystem"] = ((string)array[0]);
if (System.DBNull.Value != array[6] && System.DBNull.Value != array[7] && System.DBNull.Value != array[8])
{
je["StarPos"] = new JArray()
{
array[6], array[7], array[8]
};
}
if (array[16] != null)
{
je["JumpDist"] = (double)array[16];
}
je["EDDMapColor"] = ((long)array[5]);
cmd.AddParameterWithValue("@etime", eventtime);
cmd.AddParameterWithValue("@edata", je.ToString()); // order number - look at the dbcommand above
long edsmid = 0;
if (System.DBNull.Value != array[9])
{
edsmid = (long)array[9];
}
cmd.AddParameterWithValue("@edsmid", edsmid); // order number - look at the dbcommand above
cmd.AddParameterWithValue("@synced", ((bool)array[4] == true) ? 1 : 0);
SQLiteDBClass.SQLNonQueryText(conn, cmd);
}
}
txn.Commit();
}
}
}
public bool Update(SQLiteConnectionUser cn, DbTransaction tn = null)
{
using (DbCommand cmd = cn.CreateCommand("Update TravelLogUnit set Name=@Name, Type=@type, size=@size, Path=@Path, CommanderID=@CommanderID where ID=@id", tn))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return true;
}
}
public bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM routes_expeditions WHERE id=@id"))
{
cmd.AddParameterWithValue("@id", Id);
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
{
cmd.AddParameterWithValue("@routeid", Id);
cmd.ExecuteNonQuery();
}
return true;
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into TravelLogUnit (Name, type, size, Path, CommanderID) values (@name, @type, @size, @Path, @CommanderID)"))
{
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@type", type);
cmd.AddParameterWithValue("@size", Size);
cmd.AddParameterWithValue("@Path", Path);
cmd.AddParameterWithValue("@CommanderID", CommanderId);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from TravelLogUnit"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return true;
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM wanted_systems WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
return true;
}
}
public static List<string> GetAllNames()
{
List<string> names = new List<string>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("SELECT DISTINCT Name FROM TravelLogUnit"))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
names.Add((string)reader["Name"]);
}
}
}
}
return names;
}
private bool Add(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Insert into wanted_systems (systemname) values (@systemname)"))
{
cmd.AddParameterWithValue("@systemname", system);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("Select Max(id) as id from wanted_systems"))
{
id = (long)SQLiteDBClass.SQLScalar(cn, cmd2);
}
return true;
}
}
public static List<TravelLogUnit> GetAll()
{
List<TravelLogUnit> list = new List<TravelLogUnit>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser(mode: EDDbAccessMode.Reader))
{
using (DbCommand cmd = cn.CreateCommand("select * from TravelLogUnit"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
return list;
foreach (DataRow dr in ds.Tables[0].Rows)
{
TravelLogUnit sys = new TravelLogUnit(dr);
list.Add(sys);
}
return list;
}
}
}
private static void DropOldUserTables(SQLiteConnectionUser conn)
{
string[] queries = new[]
{
"DROP TABLE IF EXISTS Systems",
"DROP TABLE IF EXISTS SystemAliases",
"DROP TABLE IF EXISTS Distances",
"DROP TABLE IF EXISTS Stations",
"DROP TABLE IF EXISTS station_commodities",
"DROP TABLE IF EXISTS Journals",
"DROP TABLE IF EXISTS VisitedSystems"
};
foreach (string query in queries)
{
using (DbCommand cmd = conn.CreateCommand(query))
{
cmd.ExecuteNonQuery();
}
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("Update SystemNote set Name=@Name, Time=@Time, Note=@Note where ID=@id"))
{
cmd.AddParameterWithValue("@ID", id);
cmd.AddParameterWithValue("@Name", Name);
cmd.AddParameterWithValue("@Note", Note);
cmd.AddParameterWithValue("@Time", Time);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
globalSystemNotes[Name.ToLower()] = this;
return true;
}
}
public void DeleteCommander(EDCommander cmdr)
{
using (SQLiteConnectionUser conn = new SQLiteConnectionUser())
{
using (DbCommand cmd = conn.CreateCommand("UPDATE Commanders SET Deleted = 1 WHERE Id = @Id"))
{
cmd.AddParameterWithValue("@Id", cmdr.Nr);
cmd.ExecuteNonQuery();
}
}
LoadCommanders(); // refresh in-memory copy
}
public static List<VisitedSystemsClass> GetAll(int commander)
{
List<VisitedSystemsClass> list = new List<VisitedSystemsClass>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems where commander=@commander Order by Time "))
{
cmd.AddParameterWithValue("@commander", commander);
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
return list;
foreach (DataRow dr in ds.Tables[0].Rows)
{
VisitedSystemsClass sys = new VisitedSystemsClass(dr);
list.Add(sys);
}
return list;
}
}
}
private bool Delete(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("DELETE FROM Bookmarks WHERE id = @id"))
{
cmd.AddParameterWithValue("@id", id);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
bookmarks.RemoveAll(x => x.id == id); // remove from list any containing id.
return true;
}
}
public static List<VisitedSystemsClass> GetAll(TravelLogUnit tlu)
{
List<VisitedSystemsClass> vsc = new List<VisitedSystemsClass>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("SELECT * FROM VisitedSystems WHERE Source = @source ORDER BY Time ASC"))
{
cmd.AddParameterWithValue("@source", tlu.id);
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
vsc.Add(new VisitedSystemsClass(reader));
}
}
}
}
return vsc;
}
public static VisitedSystemsClass GetLast()
{
List<VisitedSystemsClass> list = new List<VisitedSystemsClass>();
using (SQLiteConnectionUser cn = new SQLiteConnectionUser())
{
using (DbCommand cmd = cn.CreateCommand("select * from VisitedSystems Order by Time DESC Limit 1"))
{
DataSet ds = SQLiteDBClass.SQLQueryText(cn, cmd);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return null;
}
VisitedSystemsClass sys = new VisitedSystemsClass(ds.Tables[0].Rows[0]);
return sys;
}
}
}
private bool Update(SQLiteConnectionUser cn)
{
using (DbCommand cmd = cn.CreateCommand("UPDATE routes_expeditions SET name=@name, start=@start, end=@end WHERE id=@id"))
{
cmd.AddParameterWithValue("@id", Id);
cmd.AddParameterWithValue("@name", Name);
cmd.AddParameterWithValue("@start", StartDate);
cmd.AddParameterWithValue("@end", EndDate);
SQLiteDBClass.SQLNonQueryText(cn, cmd);
using (DbCommand cmd2 = cn.CreateCommand("DELETE FROM route_systems WHERE routeid=@routeid"))
{
cmd2.AddParameterWithValue("@routeid", Id);
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
using (DbCommand cmd2 = cn.CreateCommand("INSERT INTO route_systems (routeid, systemname) VALUES (@routeid, @name)"))
{
cmd2.AddParameter("@routeid", DbType.String);
cmd2.AddParameter("@name", DbType.String);
foreach (var sysname in Systems)
{
cmd2.Parameters["@routeid"].Value = Id;
cmd2.Parameters["@name"].Value = sysname;
SQLiteDBClass.SQLNonQueryText(cn, cmd2);
}
}
return true;
}
}