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