private static bool TryCreateNewUpdate(SectorCache cache, TableWriteData data, bool tablesareempty, ref DateTime maxdate, ref int nextsectorid, out Sector t, bool makenew = false)
        {
            if (data.edsm.date > maxdate)                                   // for all, record last recorded date processed
            {
                maxdate = data.edsm.date;
            }

            Sector prev = null;

            t = null;

            if (!cache.SectorNameCache.ContainsKey(data.classifier.SectorName))   // if unknown to cache
            {
                if (!tablesareempty && !makenew)
                {
                    return(false);
                }

                cache.SectorNameCache[data.classifier.SectorName] = t = new Sector(data.classifier.SectorName, gridid: data.gridid);   // make a sector of sectorname and with gridID n , id == -1
            }
            else
            {
                t = cache.SectorNameCache[data.classifier.SectorName]; // find the first sector of name
                while (t != null && t.GId != data.gridid)              // if GID of sector disagrees
                {
                    prev = t;                                          // go thru list
                    t    = t.NextSector;
                }

                if (t == null)      // still not got it, its a new one.
                {
                    if (!tablesareempty && !makenew)
                    {
                        return(false);
                    }

                    prev.NextSector = t = new Sector(data.classifier.SectorName, gridid: data.gridid);   // make a sector of sectorname and with gridID n , id == -1
                }
            }

            if (t.Id == -1)                        // if unknown sector ID..
            {
                if (tablesareempty)                // if tables are empty, we can just presume its id
                {
                    t.Id        = nextsectorid++;  // insert the sector with the guessed ID
                    t.insertsec = true;
                    cache.SectorIDCache[t.Id] = t; // and cache
                    //System.Diagnostics.Debug.WriteLine("Made sector " + t.Name + ":" + t.GId);
                }
            }

            if (t.edsmdatalist == null)
            {
                t.edsmdatalist = new List <TableWriteData>(5000);
            }

            t.edsmdatalist.Add(data);                       // add to list of systems to process for this sector

            return(true);
        }
        private static void CreateNewUpdate(SectorCache cache, DbCommand selectSectorCmd, TableWriteData data, bool tablesareempty, ref DateTime maxdate, ref int nextsectorid)
        {
            TryCreateNewUpdate(cache, data, tablesareempty, ref maxdate, ref nextsectorid, out Sector t, true);

            if (t.Id == -1)   // if unknown sector ID..
            {
                selectSectorCmd.Parameters[0].Value = t.Name;
                selectSectorCmd.Parameters[1].Value = t.GId;

                using (DbDataReader reader = selectSectorCmd.ExecuteReader()) // find name:gid
                {
                    if (reader.Read())                                        // if found name:gid
                    {
                        t.Id = (long)reader[0];
                    }
                    else
                    {
                        t.Id        = nextsectorid++; // insert the sector with the guessed ID
                        t.insertsec = true;
                    }

                    cache.SectorIDCache[t.Id] = t;                // and cache
                    //  System.Diagnostics.Debug.WriteLine("Made sector " + t.Name + ":" + t.GId);
                }
            }
        }
        // create a new entry for insert in the sector tables
        private static void CreateNewUpdate(SectorCache cache, DbCommand selectSectorCmd, EDSMFileEntry d, int gid, bool tablesareempty, ref DateTime maxdate, ref int nextsectorid)
        {
            TableWriteData data = new TableWriteData()
            {
                edsm = d, classifier = new EliteNameClassifier(d.name), gridid = gid
            };

            CreateNewUpdate(cache, selectSectorCmd, data, tablesareempty, ref maxdate, ref nextsectorid);
        }
        // set tempostfix to use another set of tables

        public static long ParseEDSMJSON(TextReader textreader,
                                         bool[] grididallowed,      // null = all, else grid bool value
                                         ref DateTime maxdate,      // updated with latest date
                                         Func <bool> cancelRequested,
                                         Action <string> reportProgress,
                                         string tablepostfix,            // set to add on text to table names to redirect to another table
                                         bool tablesareempty    = false, // set to presume table is empty, so we don't have to do look up queries
                                         string debugoutputfile = null
                                         )
        {
            var cache = new SectorCache();

            long updates = 0;

            int          nextsectorid = GetNextSectorID();
            StreamWriter sw           = null;

            try
            {
#if DEBUG
                try
                {
                    if (debugoutputfile != null)
                    {
                        sw = new StreamWriter(debugoutputfile);
                    }
                }
                catch
                {
                }
#endif
                var parser     = new BaseUtils.StringParserQuickTextReader(textreader, 32768);
                var enumerator = JToken.ParseToken(parser, JToken.ParseOptions.None).GetEnumerator();       // Parser may throw note

                while (true)
                {
                    if (cancelRequested())
                    {
                        updates = -1;
                        break;
                    }

                    int recordstostore = ProcessBlock(cache, enumerator, grididallowed, tablesareempty, tablepostfix, ref maxdate, ref nextsectorid, out bool jr_eof);

                    System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + "   " + updates);

                    if (recordstostore > 0)
                    {
                        updates += StoreNewEntries(cache, tablepostfix, sw);

                        reportProgress?.Invoke("EDSM Star database updated " + updates);
                    }

                    if (jr_eof)
                    {
                        break;
                    }

                    System.Threading.Thread.Sleep(20);      // just sleepy for a bit to let others use the db
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception during EDSM parse " + ex);
            }
            finally
            {
                if (sw != null)
                {
                    sw.Close();
                }
            }

            System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + "   " + updates);
            reportProgress?.Invoke("EDSM Star database updated " + updates);

            PutNextSectorID(nextsectorid);    // and store back

            return(updates);
        }
        private static long StoreNewEntries(SectorCache cache, string tablepostfix = "",        // set to add on text to table names to redirect to another table
                                            StreamWriter sw = null
                                            )
        {
            ////////////////////////////////////////////////////////////// push all new data to the db without any selects

            return(SystemsDatabase.Instance.ExecuteWithDatabase(func: db =>
            {
                long updates = 0;

                DbTransaction txn = null;
                DbCommand replaceSectorCmd = null;
                DbCommand replaceSysCmd = null;
                DbCommand replaceNameCmd = null;
                try
                {
                    var cn = db.Connection;
                    txn = cn.BeginTransaction();

                    replaceSectorCmd = cn.CreateReplace("Sectors" + tablepostfix, new string[] { "name", "gridid", "id" }, new DbType[] { DbType.String, DbType.Int32, DbType.Int64 }, txn);

                    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);

                    replaceNameCmd = cn.CreateReplace("Names" + tablepostfix, new string[] { "name", "id" }, new DbType[] { DbType.String, DbType.Int64 }, txn);

                    foreach (var kvp in cache.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();

                    return updates;
                }
                finally
                {
                    replaceSectorCmd?.Dispose();
                    replaceSysCmd?.Dispose();
                    replaceNameCmd?.Dispose();
                    txn?.Dispose();
                }
            }, warnthreshold: 5000));
        }
        private static int ProcessBlock(SectorCache cache,
                                        IEnumerator <JToken> enumerator,
                                        bool[] grididallowed,        // null = all, else grid bool value
                                        bool tablesareempty,
                                        string tablepostfix,
                                        ref DateTime maxdate,        // updated with latest date
                                        ref int nextsectorid,
                                        out bool jr_eof)
        {
            int       recordstostore  = 0;
            DbCommand selectSectorCmd = null;
            DateTime  cpmaxdate       = maxdate;
            int       cpnextsectorid  = nextsectorid;
            const int BlockSize       = 10000;
            int       Limit           = int.MaxValue;
            var       entries         = new List <TableWriteData>();

            jr_eof = false;

            while (true)
            {
                if (!enumerator.MoveNext())         // get next token, if not, stop eof
                {
                    jr_eof = true;
                    break;
                }

                JToken t = enumerator.Current;

                if (t.IsObject)                     // if start of object..
                {
                    EDSMFileEntry d = new EDSMFileEntry();

                    if (d.Deserialize(enumerator) && d.id >= 0 && d.name.HasChars() && d.z != int.MinValue)     // if we have a valid record
                    {
                        int gridid = GridId.Id128(d.x, d.z);
                        if (grididallowed == null || (grididallowed.Length > gridid && grididallowed[gridid]))    // allows a null or small grid
                        {
                            TableWriteData data = new TableWriteData()
                            {
                                edsm = d, classifier = new EliteNameClassifier(d.name), gridid = gridid
                            };

                            if (!TryCreateNewUpdate(cache, data, tablesareempty, ref cpmaxdate, ref cpnextsectorid, out Sector sector))
                            {
                                entries.Add(data);
                            }

                            recordstostore++;
                        }
                    }

                    if (--Limit == 0)
                    {
                        jr_eof = true;
                        break;
                    }

                    if (recordstostore >= BlockSize)
                    {
                        break;
                    }
                }
            }

            SystemsDatabase.Instance.ExecuteWithDatabase(action: db =>
            {
                try
                {
                    var cn = db.Connection;

                    selectSectorCmd = cn.CreateSelect("Sectors" + tablepostfix, "id", "name = @sname AND gridid = @gid", null,
                                                      new string[] { "sname", "gid" }, new DbType[] { DbType.String, DbType.Int32 });

                    foreach (var entry in entries)
                    {
                        CreateNewUpdate(cache, selectSectorCmd, entry, tablesareempty, ref cpmaxdate, ref cpnextsectorid);
                    }
                }
                finally
                {
                    if (selectSectorCmd != null)
                    {
                        selectSectorCmd.Dispose();
                    }
                }
            });

            maxdate      = cpmaxdate;
            nextsectorid = cpnextsectorid;

            return(recordstostore);
        }
        // take old system table and turn to new.  tablesarempty=false is normal, only set to true if using this code for checking replace algorithm

        public static long UpgradeDB102to200(Func <bool> cancelRequested, Action <string> reportProgress, string tablepostfix, bool tablesareempty = false, int maxgridid = int.MaxValue)
        {
            var cache = new SectorCache();

            int  nextsectorid = GetNextSectorID();
            long updates      = 0;

            long Limit = long.MaxValue;

            DateTime maxdate = DateTime.MinValue;       // we don't pass this back due to using the same date

            reportProgress?.Invoke("Begin System DB upgrade");
            List <int> gridids = DB.GridId.AllId();

            BaseUtils.AppTicks.TickCountLap("UTotal");

            //int debug_z = 0;

            foreach (int gridid in gridids)  // using grid id to keep chunks a good size.. can't read and write so can't just read the whole.
            {
                if (cancelRequested())
                {
                    updates = -1;
                    break;
                }

                if (gridid == maxgridid)        // for debugging
                {
                    break;
                }

                int       recordstostore  = 0;
                DbCommand selectSectorCmd = null;
                DbCommand selectPrev      = null;

                SystemsDatabase.Instance.ExecuteWithDatabase(db =>
                {
                    try
                    {
                        var cn = db.Connection;

                        selectSectorCmd = cn.CreateSelect("Sectors" + tablepostfix, "id", "name = @sname AND gridid = @gid", null,
                                                          new string[] { "sname", "gid" }, new DbType[] { DbType.String, DbType.Int32 });
                        selectPrev = cn.CreateSelect("EdsmSystems s", "s.EdsmId,s.x,s.y,s.z,n.Name,s.UpdateTimeStamp", "s.GridId = @gid", null,
                                                     new string[] { "gid" }, new DbType[] { DbType.Int32 },
                                                     joinlist: new string[] { "LEFT OUTER JOIN SystemNames n ON n.EdsmId=s.EdsmId" });

                        selectPrev.Parameters[0].Value = gridid;

                        using (DbDataReader reader = selectPrev.ExecuteReader())       // find name:gid
                        {
                            BaseUtils.AppTicks.TickCountLap("U1");

                            while (reader.Read())
                            {
                                try
                                {
                                    EDSMFileEntry d = new EDSMFileEntry();
                                    d.id            = (long)reader[0];
                                    d.x             = (int)(long)reader[1];
                                    d.y             = (int)(long)reader[2];
                                    d.z             = (int)(long)reader[3];
                                    d.name          = (string)reader[4];
                                    d.date          = new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromSeconds((long)reader["UpdateTimestamp"]);
                                    int grididentry = GridId.Id128(d.x, d.z);  // because i don't trust the previous gridid - it should be the same as the outer loop, but lets recalc

                                    //if (!tablesareempty)  d.z = debug_z++;  // for debug checking

                                    CreateNewUpdate(cache, selectSectorCmd, d, grididentry, tablesareempty, ref maxdate, ref nextsectorid);      // not using gridid on purpose to double check it.
                                    recordstostore++;
                                }
                                catch (Exception ex)
                                {
                                    System.Diagnostics.Debug.WriteLine("Reading prev table" + ex);
                                }
                            }
                        }
                    }
                    finally
                    {
                        selectSectorCmd?.Dispose();
                    }
                }, 5000);

                //System.Diagnostics.Debug.WriteLine("Reader took " + BaseUtils.AppTicks.TickCountLap("U1") + " in " + gridid + "  " + recordpos + " total " + recordstostore);

                if (recordstostore >= 0)
                {
                    updates += StoreNewEntries(cache, tablepostfix, null);
                    reportProgress?.Invoke("System DB upgrade processed " + updates);

                    Limit -= recordstostore;

                    if (Limit <= 0)
                    {
                        break;
                    }

                    System.Threading.Thread.Sleep(20);      // just sleepy for a bit to let others use the db
                }

                var tres1 = BaseUtils.AppTicks.TickCountLapDelta("U1");
                var tres2 = BaseUtils.AppTicks.TickCountFrom("UTotal");
                System.Diagnostics.Debug.WriteLine("Sector " + gridid + " took " + tres1.Item1 + " store " + recordstostore + " total " + updates + " " + ((float)tres1.Item2 / (float)recordstostore) + " cumulative " + tres2);
            }

            reportProgress?.Invoke("System DB complete, processed " + updates);

            PutNextSectorID(nextsectorid);    // and store back

            return(updates);
        }
Exemplo n.º 8
0
        private static int ProcessBlock(SectorCache cache,
                                        JsonTextReader jr,
                                        bool[] grididallowed,        // null = all, else grid bool value
                                        bool tablesareempty,
                                        string tablepostfix,
                                        ref DateTime maxdate,        // updated with latest date
                                        ref int nextsectorid,
                                        ref bool jr_eof)
        {
            int       recordstostore  = 0;
            DbCommand selectSectorCmd = null;
            DateTime  cpmaxdate       = maxdate;
            int       cpnextsectorid  = nextsectorid;
            const int BlockSize       = 10000;
            int       Limit           = int.MaxValue;
            var       entries         = new List <TableWriteData>();

            while (jr_eof == false)
            {
                try
                {
                    if (jr.Read())
                    {
                        if (jr.TokenType == JsonToken.StartObject)
                        {
                            EDSMFileEntry d = new EDSMFileEntry();

                            if (d.Deserialize(jr) && d.id >= 0 && d.name.HasChars() && d.z != int.MinValue)     // if we have a valid record
                            {
                                int gridid = GridId.Id128(d.x, d.z);
                                if (grididallowed == null || (grididallowed.Length > gridid && grididallowed[gridid]))    // allows a null or small grid
                                {
                                    TableWriteData data = new TableWriteData()
                                    {
                                        edsm = d, classifier = new EliteNameClassifier(d.name), gridid = gridid
                                    };

                                    if (!TryCreateNewUpdate(cache, data, tablesareempty, ref cpmaxdate, ref cpnextsectorid, out Sector sector))
                                    {
                                        entries.Add(data);
                                    }

                                    recordstostore++;
                                }
                            }

                            if (--Limit == 0)
                            {
                                jr_eof = true;
                                break;
                            }

                            if (recordstostore >= BlockSize)
                            {
                                break;
                            }
                        }
                    }
                    else
                    {
                        jr_eof = true;
                        break;
                    }
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine("EDSM JSON file exception " + ex.ToString());
                    jr_eof = true;                                                                              // stop read, but let it continue to finish this section
                }
            }

            SystemsDatabase.Instance.ExecuteWithDatabase(action: db =>
            {
                try
                {
                    var cn = db.Connection;

                    selectSectorCmd = cn.CreateSelect("Sectors" + tablepostfix, "id", "name = @sname AND gridid = @gid", null,
                                                      new string[] { "sname", "gid" }, new DbType[] { DbType.String, DbType.Int32 });

                    foreach (var entry in entries)
                    {
                        CreateNewUpdate(cache, selectSectorCmd, entry, tablesareempty, ref cpmaxdate, ref cpnextsectorid);
                    }
                }
                finally
                {
                    if (selectSectorCmd != null)
                    {
                        selectSectorCmd.Dispose();
                    }
                }
            });

            maxdate      = cpmaxdate;
            nextsectorid = cpnextsectorid;

            return(recordstostore);
        }
Exemplo n.º 9
0
        // set tempostfix to use another set of tables

        public static long ParseEDSMJSON(JsonTextReader jr,
                                         bool[] grididallowed,      // null = all, else grid bool value
                                         ref DateTime maxdate,      // updated with latest date
                                         Func <bool> cancelRequested,
                                         Action <string> reportProgress,
                                         string tablepostfix,            // set to add on text to table names to redirect to another table
                                         bool tablesareempty    = false, // set to presume table is empty, so we don't have to do look up queries
                                         string debugoutputfile = null
                                         )
        {
            var cache = new SectorCache();

            long updates = 0;

            int          nextsectorid = GetNextSectorID();
            bool         jr_eof       = false;
            StreamWriter sw           = null;

            try
            {
#if DEBUG
                try
                {
                    if (debugoutputfile != null)
                    {
                        sw = new StreamWriter(debugoutputfile);
                    }
                }
                catch
                {
                }
#endif

                while (jr_eof == false)
                {
                    if (cancelRequested())
                    {
                        updates = -1;
                        break;
                    }

                    int recordstostore = ProcessBlock(cache, jr, grididallowed, tablesareempty, tablepostfix, ref maxdate, ref nextsectorid, ref jr_eof);

                    System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + "   " + updates);

                    if (recordstostore > 0)
                    {
                        updates += StoreNewEntries(cache, tablepostfix, sw);

                        reportProgress?.Invoke("EDSM Star database updated " + updates);
                    }

                    if (jr_eof)
                    {
                        break;
                    }

                    if (SQLiteConnectionSystem.IsReadWaiting)
                    {
                        System.Threading.Thread.Sleep(20);      // just sleepy for a bit to let others use the db
                    }
                }
            }
            finally
            {
                if (sw != null)
                {
                    sw.Close();
                }
            }

            System.Diagnostics.Debug.WriteLine("Process " + BaseUtils.AppTicks.TickCountLap("L1") + "   " + updates);
            reportProgress?.Invoke("EDSM Star database updated " + updates);


            PutNextSectorID(nextsectorid);    // and store back

            return(updates);
        }