// All of these needs the systems DB to be in write mode. Make sure it is
// and check the system db rebuilding flag before using them - its above this level so can't logically be checked here
// store single system to DB
public static long StoreSystems(List <ISystem> systems)
{
JArray jlist = new JArray();
foreach (var sys in systems)
{
if (sys.EDSMID > 0 && sys.HasCoordinate)
{
JObject jo = new JObject
{
["name"] = sys.Name,
["id"] = sys.EDSMID,
["date"] = DateTime.UtcNow,
["coords"] = new JObject {
["x"] = sys.X, ["y"] = sys.Y, ["z"] = sys.Z
}
};
jlist.Add(jo);
}
}
if (jlist.Count > 0)
{
DateTime unusedate = DateTime.UtcNow;
// we need rewrite access, and run it with the cn passed to us
return(SystemsDB.ParseEDSMJSONString(jlist.ToString(), null, ref unusedate, () => false, (t) => { }, ""));
}
return(0);
}
public long StoreSystems(List <ISystem> systems) // dynamically update db
{
long count = 0;
if (!RebuildRunning)
{
WithReadWrite(() => { RebuildRunning = true; count = SystemsDB.StoreSystems(systems); RebuildRunning = false; });
}
return(count);
}
public long UpgradeSystemTableFromFile(string filename, bool[] gridids, Func <bool> cancelRequested, Action <string> reportProgress)
{
ExecuteWithDatabase(action: conn =>
{
conn.Connection.DropStarTables(TempTablePostfix); // just in case, kill the old tables
conn.Connection.CreateStarTables(TempTablePostfix); // and make new temp tables
});
DateTime maxdate = DateTime.MinValue;
long updates = SystemsDB.ParseEDSMJSONFile(filename, gridids, ref maxdate, cancelRequested, reportProgress, TempTablePostfix, presumeempty: true, debugoutputfile: DebugOutfile);
if (updates > 0)
{
ExecuteWithDatabase(action: conn =>
{
RebuildRunning = true;
reportProgress?.Invoke("Remove old data");
conn.Connection.DropStarTables(); // drop the main ones - this also kills the indexes
conn.Connection.RenameStarTables(TempTablePostfix, ""); // rename the temp to main ones
reportProgress?.Invoke("Shrinking database");
conn.Connection.Vacuum();
reportProgress?.Invoke("Creating indexes");
conn.Connection.CreateSystemDBTableIndexes();
RebuildRunning = false;
});
SetLastEDSMRecordTimeUTC(maxdate); // record last data stored in database
return(updates);
}
else
{
ExecuteWithDatabase(action: conn =>
{
conn.Connection.DropStarTables(TempTablePostfix); // clean out half prepared tables
});
return(-1);
}
}
// full table replace
public static long UpgradeSystemTableFromFile(string filename, bool[] gridids, Func <bool> cancelRequested, Action <string> reportProgress)
{
using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem(AccessMode.ReaderWriter))
{
DropStarTables(conn, tablepostfix); // just in case, kill the old tables
CreateStarTables(conn, tablepostfix); // and make new temp tables
}
DateTime maxdate = DateTime.MinValue;
long updates = SystemsDB.ParseEDSMJSONFile(filename, gridids, ref maxdate, cancelRequested, reportProgress, tablepostfix, presumeempty: true, debugoutputfile: debugoutfile);
using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem(AccessMode.ReaderWriter))
{
if (updates > 0)
{
reportProgress?.Invoke("Remove old data");
DropStarTables(conn); // drop the main ones - this also kills the indexes
RenameStarTables(conn, tablepostfix, ""); // rename the temp to main ones
reportProgress?.Invoke("Shrinking database");
conn.Vacuum();
reportProgress?.Invoke("Creating indexes");
CreateSystemDBTableIndexes(conn);
SetLastEDSMRecordTimeUTC(maxdate); // record last data stored in database
return(updates);
}
else
{
DropStarTables(conn, tablepostfix); // clean out half prepared tables
return(-1);
}
}
}
// check to see if table type 102 exists, if so, update. but only if not doing a full sync.
public static void UpgradeSystemTableFrom102TypeDB(Func <bool> cancelRequested, Action <string> reportProgress, bool fullsyncrequested)
{
bool executeupgrade = false;
// first work out if we can upgrade, if so, create temp tables
using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem(AccessMode.ReaderWriter))
{
var list = conn.Tables();
if (list.Contains("EdsmSystems"))
{
DropStarTables(conn, tablepostfix); // just in case, kill the old tables
CreateStarTables(conn, tablepostfix); // and make new temp tables
executeupgrade = true;
}
}
//drop connection, execute upgrade in another connection, this solves an issue with SQL 17 error
if (executeupgrade)
{
if (!fullsyncrequested) // if we did not request a full upgrade, we can use the current data and transmute
{
int maxgridid = int.MaxValue; // 109; // for debugging
long updates = SystemsDB.UpgradeDB102to200(cancelRequested, reportProgress, tablepostfix, tablesareempty: true, maxgridid: maxgridid);
using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem(AccessMode.ReaderWriter)) // use this special one so we don't get double init.
{
if (updates >= 0) // a cancel will result in -1
{
// keep code for checking
//if (false) // demonstrate replacement to show rows are overwitten and not duplicated in the edsmid column and that speed is okay
//{
// long countrows = conn.CountOf("Systems" + tablepostfix, "edsmid");
// long countnames = conn.CountOf("Names" + tablepostfix, "id");
// long countsectors = conn.CountOf("Sectors" + tablepostfix, "id");
// // replace takes : Sector 108 took 44525 U1 + 116 store 5627 total 532162 0.02061489 cumulative 11727
// SystemsDB.UpgradeDB102to200(cancelRequested, reportProgress, tablepostfix, tablesareempty: false, maxgridid: maxgridid);
// System.Diagnostics.Debug.Assert(countrows == conn.CountOf("Systems" + tablepostfix, "edsmid"));
// System.Diagnostics.Debug.Assert(countnames * 2 == conn.CountOf("Names" + tablepostfix, "id")); // names are duplicated.. so should be twice as much
// System.Diagnostics.Debug.Assert(countsectors == conn.CountOf("Sectors" + tablepostfix, "id"));
// System.Diagnostics.Debug.Assert(1 == conn.CountOf("Systems" + tablepostfix, "edsmid", "edsmid=6719254"));
//}
DropStarTables(conn); // drop the main ones - this also kills the indexes
RenameStarTables(conn, tablepostfix, ""); // rename the temp to main ones
reportProgress?.Invoke("Removing old system tables");
conn.ExecuteNonQueries(new string[]
{
"DROP TABLE IF EXISTS EdsmSystems",
"DROP TABLE IF EXISTS SystemNames",
});
reportProgress?.Invoke("Shrinking database");
conn.Vacuum();
reportProgress?.Invoke("Creating indexes"); // NOTE the date should be the same so we don't rewrite
CreateSystemDBTableIndexes(conn);
}
else
{
DropStarTables(conn, tablepostfix); // just in case, kill the old tables
}
}
}
else
{ // newer data is needed, so just remove
using (SQLiteConnectionSystem conn = new SQLiteConnectionSystem(AccessMode.ReaderWriter)) // use this special one so we don't get double init.
{
reportProgress?.Invoke("Removing old system tables");
conn.ExecuteNonQueries(new string[]
{
"DROP TABLE IF EXISTS EdsmSystems",
"DROP TABLE IF EXISTS SystemNames",
});
}
}
}
}
public void RemoveGridSystems(int[] gridids, Action <string> report = null)
{
RebuildRunning = true;
SystemsDB.RemoveGridSystems(gridids, report);
RebuildRunning = false;
}
