Exemple #1
0
        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);
        }
Exemple #3
0
        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);
        }