public void Save(ICollection <ItemTag> items, ProgressTracker tracker) { int commitSize = items.Count / 10; tracker.MaxValue = items.Count + commitSize; using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateQuery("DELETE FROM ItemTag").ExecuteUpdate(); foreach (ItemTag item in items) { session.Insert(item); tracker.Increment(); } transaction.Commit(); // A bit of 'fake pending progress' for commit for (int i = 0; i < commitSize; i++) { tracker.Increment(); } } } Logger.InfoFormat("Stored {0} item tags to database..", items.Count); }
public IList <ItemTag> GetClassItemTags() { using (var session = SessionCreator.OpenStatelessSession()) { using (session.BeginTransaction()) { return(session.CreateCriteria <ItemTag>() .Add(Restrictions.Like(nameof(ItemTag.Tag), "tagSkillClassName%")) .List <ItemTag>() .Select(m => new ItemTag { Name = m.Name, Tag = m.Tag.Replace("tagSkillClassName", "class") }) .ToList()); } } }
/// <summary> /// Get the existing tag dictionary /// </summary> /// <returns></returns> public Dictionary <string, string> GetTagDictionary() { Dictionary <string, string> result = new Dictionary <string, string>(); using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { foreach (ItemTag entry in session.CreateCriteria <ItemTag>().List()) { result[entry.Tag] = entry.Name; } } } return(result); }
public void Save(ICollection <ItemTag> items) { using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateQuery("DELETE FROM ItemTag").ExecuteUpdate(); foreach (ItemTag item in items) { session.Insert(item); } transaction.Commit(); } } Logger.InfoFormat("Stored {0} item tags to database..", items.Count); }
public ISet <ItemTag> GetValidClassItemTags() { using (var session = SessionCreator.OpenStatelessSession()) { using (session.BeginTransaction()) { return(session.CreateSQLQuery("SELECT * FROM ItemTag WHERE (Tag LIKE 'tagSkillClassName%' OR Tag LIKE 'tag%Class%SkillName00A') AND LENGTH(Name) > 1") .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ItemTag))) .List <ItemTag>() .Select(m => new ItemTag { Name = m.Name, Tag = m.Tag.Replace("tagSkillClassName", "class") .Replace("tagGDX1Class07SkillName00A", "class07") .Replace("tagGDX1Class08SkillName00A", "class08") // TODO: A regex or similar to auto detect new classes? }) .ToHashSet()); } } }
public IList <ItemTag> GetValidClassItemTags() { Dictionary <string, string> result = new Dictionary <string, string>(); using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { return(session.CreateSQLQuery("SELECT * FROM ItemTag WHERE (Tag LIKE 'tagSkillClassName%' OR Tag LIKE 'tag%Class%SkillName00A') AND LENGTH(Name) > 1") .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ItemTag))) .List <ItemTag>() .Select(m => new ItemTag { Name = m.Name, Tag = m.Tag.Replace("tagSkillClassName", "class") .Replace("tagGDX1Class07SkillName00A", "class07") .Replace("tagGDX1Class08SkillName00A", "class08") }) .ToList()); } } }
public IList <ItemTag> GetClassItemTags() { const string namePattern = @"\[ms\](.*)\[fs\](.*)"; using (var session = SessionCreator.OpenStatelessSession()) { using (session.BeginTransaction()) { return(session.CreateCriteria <ItemTag>() .Add(Restrictions.Like(nameof(ItemTag.Tag), "tagSkillClassName%")) .List <ItemTag>() .Select(m => new ItemTag { Name = Regex.IsMatch(m.Name, namePattern) ? Regex.Replace(m.Name, namePattern, "$1/$2") : m.Name, Tag = m.Tag.Replace("tagSkillClassName", "class") }) .ToList()); } } }
public ISet <ItemTag> GetValidClassItemTags() { const string gdxClassesPattern = @"tagGDX\dClass(\d+)SkillName00A"; const string namePattern = @"\[ms\](.*)\[fs\](.*)"; using (var session = SessionCreator.OpenStatelessSession()) { using (session.BeginTransaction()) { return(session .CreateSQLQuery( "SELECT * FROM ItemTag WHERE (Tag LIKE 'tagSkillClassName%' OR Tag LIKE 'tag%Class%SkillName00A') AND LENGTH(Name) > 1") .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ItemTag))) .List <ItemTag>() .Select(m => new ItemTag { Name = Regex.IsMatch(m.Name, namePattern) ? Regex.Replace(m.Name, namePattern, "$1/$2") : m.Name, Tag = Regex.IsMatch(m.Tag, gdxClassesPattern) ? Regex.Replace(m.Tag, gdxClassesPattern, "class$1") : m.Tag.Replace("tagSkillClassName", "class") }) .ToHashSet()); } } }
private int InsertStatsPostgres(string[] dropCommands, string[] createCommands, List <DatabaseItem> items) { int numStats = 0; void Execute(string[] commands) { using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { foreach (var command in commands) { session.CreateSQLQuery(command); } transaction.Commit(); } } } string GetQueryString(int numRows) { if (numRows > 8190) { throw new ArgumentException("Cannot create query, too many rows/args"); } string query = $"INSERT INTO {DatabaseItemStatTable.Table} ({DatabaseItemStatTable.Item}, {DatabaseItemStatTable.Stat}, {DatabaseItemStatTable.Value}, {DatabaseItemStatTable.TextValue}) VALUES "; // Batching these into 7500 inserts (~30,000 params, the postgres limit is 32767) List <string> entries = new List <string>(numRows); for (int i = 0; i < numRows; i++) { entries.Add($"(:i{i}, :s{i}, :v{i}, :tv{i})"); } return(query + string.Join(", ", entries)); } // Drop index Execute(dropCommands); // Insert stats using (var session = SessionCreator.OpenStatelessSession()) { using (ITransaction transaction = session.BeginTransaction()) { // Batching these into 7500 inserts (~30,000 params, the postgres limit is 32767) int batchSize = 7500; List <string> entries = new List <string>(batchSize); foreach (var item in items) { numStats += item.Stats.Count; // Not ideal to not use argumnets, but sending in 30,000 named arguments takes minutes, not seconds. foreach (DatabaseItemStat stat in item.Stats) { var v = stat.Value.ToString().Replace(",", "."); entries.Add($"({item.Id}, '{stat.Stat}', {v}, '{stat.TextValue?.Replace("'", "''")}')"); // 1875 items, max args if (entries.Count >= batchSize) { var query = session.CreateSQLQuery($"INSERT INTO {DatabaseItemStatTable.Table} ({DatabaseItemStatTable.Item}, {DatabaseItemStatTable.Stat}, {DatabaseItemStatTable.Value}, {DatabaseItemStatTable.TextValue}) VALUES " + string.Join(",", entries)); int affectedRows = query.ExecuteUpdate(); Logger.Info($"Inserted stats with {affectedRows} affected rows"); entries.Clear(); } } } // We got a batch that never hit 7500 if (entries.Count > 0) { var query = session.CreateSQLQuery($"INSERT INTO {DatabaseItemStatTable.Table} ({DatabaseItemStatTable.Item}, {DatabaseItemStatTable.Stat}, {DatabaseItemStatTable.Value}, {DatabaseItemStatTable.TextValue}) VALUES " + string.Join(",", entries)); int affectedRows = query.ExecuteUpdate(); Logger.Info($"Inserted stats with {affectedRows} affected rows (trailing)"); } transaction.Commit(); } } // Recreate index Execute(createCommands); // TODO: Since we're being batched higher up at 950 items, this might be... unfortunate return(numStats); }