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