public static long ParseAlias(JsonTextReader jr) { long updates = 0; System.Diagnostics.Debug.WriteLine("Update aliases"); using (SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.Writer)) // open the db { using (DbTransaction txn = cn.BeginTransaction()) { DbCommand selectCmd = cn.CreateSelect("Aliases", "edsmid", "edsmid = @edsmid", inparas: new string[] { "edsmid:int64" }, limit: "1", tx: txn); // 1 return matching ID DbCommand deletesystemcmd = cn.CreateDelete("Systems", "edsmid=@edsmid", paras: new string[] { "edsmid:int64" }, tx: txn); DbCommand insertCmd = cn.CreateReplace("Aliases", paras: new string[] { "edsmid:int64", "edsmid_mergedto:int64", "name:string" }, tx: txn); try { // protect against json exceptions while (true) { if (!jr.Read()) { break; } if (jr.TokenType == JsonToken.StartObject) { JObject jo = JObject.Load(jr); long edsmid = (long)jo["id"]; string name = (string)jo["system"]; string action = (string)jo["action"]; long mergedto = 0; if (jo["mergedTo"] != null) { mergedto = (long)jo["mergedTo"]; } if (action.Contains("delete system", System.StringComparison.InvariantCultureIgnoreCase)) { deletesystemcmd.Parameters[0].Value = edsmid; deletesystemcmd.ExecuteNonQuery(); } if (mergedto > 0) { selectCmd.Parameters[0].Value = edsmid; long foundedsmid = selectCmd.ExecuteScalar <long>(-1); if (foundedsmid == -1) { insertCmd.Parameters[0].Value = edsmid; insertCmd.Parameters[1].Value = mergedto; insertCmd.Parameters[2].Value = name; insertCmd.ExecuteNonQuery(); //System.Diagnostics.Debug.WriteLine("Alias " + edsmid + " -> " + mergedto + " " + name); updates++; } } } } } catch (System.Exception ex) { System.Diagnostics.Debug.WriteLine("JSON format error in aliases " + ex); } txn.Commit(); selectCmd.Dispose(); deletesystemcmd.Dispose(); insertCmd.Dispose(); } } return(updates); }
private static long StoreNewEntries(SQLiteConnectionSystem cn, string tablepostfix = "", // set to add on text to table names to redirect to another table StreamWriter sw = null ) { long updates = 0; ////////////////////////////////////////////////////////////// push all new data to the db without any selects SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>(); // not using on purpose. tl.OpenWriter(); DbTransaction txn = cn.BeginTransaction(); DbCommand replaceSectorCmd = cn.CreateReplace("Sectors" + tablepostfix, new string[] { "name", "gridid", "id" }, new DbType[] { DbType.String, DbType.Int32, DbType.Int64 }, txn); DbCommand replaceSysCmd = cn.CreateReplace("Systems" + tablepostfix, new string[] { "sectorid", "nameid", "x", "y", "z", "edsmid" }, new DbType[] { DbType.Int64, DbType.Int64, DbType.Int32, DbType.Int32, DbType.Int32, DbType.Int64 }, txn); DbCommand replaceNameCmd = cn.CreateReplace("Names" + tablepostfix, new string[] { "name", "id" }, new DbType[] { DbType.String, DbType.Int64 }, txn); foreach (var kvp in sectoridcache) // all sectors cached, id is unique so its got all sectors { Sector t = kvp.Value; if (t.insertsec) // if we have been told to insert the sector, do it { replaceSectorCmd.Parameters[0].Value = t.Name; // make a new one so we can get the ID replaceSectorCmd.Parameters[1].Value = t.GId; replaceSectorCmd.Parameters[2].Value = t.Id; // and we insert with ID, managed by us, and replace in case there are any repeat problems (which there should not be) replaceSectorCmd.ExecuteNonQuery(); //System.Diagnostics.Debug.WriteLine("Written sector " + t.GId + " " +t.Name); t.insertsec = false; } if (t.edsmdatalist != null) // if updated.. { #if DEBUG t.edsmdatalist.Sort(delegate(TableWriteData left, TableWriteData right) { return(left.edsm.id.CompareTo(right.edsm.id)); }); #endif foreach (var data in t.edsmdatalist) // now write the star list in this sector { try { if (data.classifier.IsNamed) // if its a named entry, we need a name { data.classifier.NameIdNumeric = data.edsm.id; // name is the edsm id replaceNameCmd.Parameters[0].Value = data.classifier.StarName; // insert a new name replaceNameCmd.Parameters[1].Value = data.edsm.id; // we use edsmid as the nameid, and use replace to ensure that if a prev one is there, its replaced replaceNameCmd.ExecuteNonQuery(); // System.Diagnostics.Debug.WriteLine("Make name " + data.classifier.NameIdNumeric); } replaceSysCmd.Parameters[0].Value = t.Id; replaceSysCmd.Parameters[1].Value = data.classifier.ID; replaceSysCmd.Parameters[2].Value = data.edsm.x; replaceSysCmd.Parameters[3].Value = data.edsm.y; replaceSysCmd.Parameters[4].Value = data.edsm.z; replaceSysCmd.Parameters[5].Value = data.edsm.id; // in the event a new entry has the same edsmid, the system table edsmid is replace with new data replaceSysCmd.ExecuteNonQuery(); if (sw != null) { sw.WriteLine(data.edsm.name + " " + data.edsm.x + "," + data.edsm.y + "," + data.edsm.z + ", EDSM:" + data.edsm.id + " Grid:" + data.gridid); } updates++; } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("general exception during insert - ignoring " + ex.ToString()); } } } t.edsmdatalist = null; // and delete back } txn.Commit(); replaceSectorCmd.Dispose(); replaceSysCmd.Dispose(); replaceNameCmd.Dispose(); txn.Dispose(); tl.Dispose(); return(updates); }
public static long ParseEDDBJSON(TextReader tr, Func <bool> cancelRequested) { long updated = 0; bool eof = false; while (!eof) { SQLiteConnectionSystem cn = new SQLiteConnectionSystem(mode: SQLLiteExtensions.SQLExtConnection.AccessMode.ReaderWriter); SQLExtTransactionLock <SQLiteConnectionSystem> tl = new SQLExtTransactionLock <SQLiteConnectionSystem>(); tl.OpenWriter(); DbTransaction txn = cn.BeginTransaction(); DbCommand selectCmd = cn.CreateSelect("EDDB", "eddbupdatedat", "edsmid = @edsmid", inparas: new string[] { "edsmid:int64" }, limit: "1", tx: txn); // 1 return matching ID string[] dbfields = { "edsmid", "eddbid", "eddbupdatedat", "population", "faction", "government", "allegiance", "state", "security", "primaryeconomy", "needspermit", "power", "powerstate", "properties" }; DbType[] dbfieldtypes = { DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.String, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.Int64, DbType.String, DbType.String, DbType.String }; DbCommand replaceCmd = cn.CreateReplace("EDDB", dbfields, dbfieldtypes, txn); while (!SQLiteConnectionSystem.IsReadWaiting) { string line = tr.ReadLine(); if (line == null) // End of stream { eof = true; break; } try { JObject jo = JObject.Parse(line); long jsonupdatedat = jo["updated_at"].Int(); long jsonedsmid = jo["edsm_id"].Long(); bool jsonispopulated = jo["is_populated"].Bool(); if (jsonispopulated) // double check that the flag is set - population itself may be zero, for some systems, but its the flag we care about { selectCmd.Parameters[0].Value = jsonedsmid; long dbupdated_at = selectCmd.ExecuteScalar <long>(0); if (dbupdated_at == 0 || jsonupdatedat != dbupdated_at) { replaceCmd.Parameters["@edsmid"].Value = jsonedsmid; replaceCmd.Parameters["@eddbid"].Value = jo["id"].Long(); replaceCmd.Parameters["@eddbupdatedat"].Value = jsonupdatedat; replaceCmd.Parameters["@population"].Value = jo["population"].Long(); replaceCmd.Parameters["@faction"].Value = jo["controlling_minor_faction"].Str("Unknown"); replaceCmd.Parameters["@government"].Value = EliteDangerousTypesFromJSON.Government2ID(jo["government"].Str("Unknown")); replaceCmd.Parameters["@allegiance"].Value = EliteDangerousTypesFromJSON.Allegiance2ID(jo["allegiance"].Str("Unknown")); EDState edstate = EDState.Unknown; try { if (jo["states"] != null && jo["states"].HasValues) { JToken tk = jo["states"].First; // we take the first one whatever JObject jostate = (JObject)tk; edstate = EliteDangerousTypesFromJSON.EDState2ID(jostate["name"].Str("Unknown")); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("EDDB JSON file exception for states " + ex.ToString()); } replaceCmd.Parameters["@state"].Value = edstate; replaceCmd.Parameters["@security"].Value = EliteDangerousTypesFromJSON.EDSecurity2ID(jo["security"].Str("Unknown")); replaceCmd.Parameters["@primaryeconomy"].Value = EliteDangerousTypesFromJSON.EDEconomy2ID(jo["primary_economy"].Str("Unknown")); replaceCmd.Parameters["@needspermit"].Value = jo["needs_permit"].Int(0); replaceCmd.Parameters["@power"].Value = jo["power"].Str("None"); replaceCmd.Parameters["@powerstate"].Value = jo["power_state"].Str("N/A"); replaceCmd.Parameters["@properties"].Value = RemoveFieldsFromJSON(jo); replaceCmd.ExecuteNonQuery(); updated++; } } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine("EDDB JSON file exception " + ex.ToString()); } } txn.Commit(); txn.Dispose(); selectCmd.Dispose(); replaceCmd.Dispose(); tl.Dispose(); cn.Dispose(); } return(updated); }