public override void Save(ReplicaItem obj) { using (ISession session = SessionCreator.OpenSession()) { try { using (ITransaction transaction = session.BeginTransaction()) { int numRowsAffected = 0; // We may be receiving item info from a MouseOver, prior to the item being looted by IA. if (obj.PlayerItemId != 0) { numRowsAffected = session.CreateSQLQuery("UPDATE ReplicaItem SET playeritemid = :pid, Text = :text WHERE uqhash = :hash") .SetParameter("pid", obj.PlayerItemId) .SetParameter("hash", obj.UqHash) .SetParameter("text", obj.Text) .ExecuteUpdate(); } if (numRowsAffected == 0) { session.Save(obj); } transaction.Commit(); } } catch (GenericADOException ex) { // If the playerItemId is 0, we're most likely just hovering over armor in-game and getting multiple hits. // Might want some kind of caching service infront of the DB layer, which remembers the last ~5000 hashes this session. Logger.Debug("Error storing replica item", ex); } } }
public void Save(DatabaseItem item) { using (var session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateQuery("DELETE FROM DatabaseItemStat as S WHERE S.Parent.Id = :id") .SetParameter("id", item.Id) .ExecuteUpdate(); session.CreateQuery("DELETE FROM DatabaseItem WHERE Record = :record") .SetParameter("record", item.Record) .ExecuteUpdate(); transaction.Commit(); } using (ITransaction transaction = session.BeginTransaction()) { foreach (DatabaseItemStat stat in item.Stats) { stat.Parent = item; //session.Insert(stat); } session.Save(item); transaction.Commit(); } } }
public void Clean() { if (Dialect == SqlDialect.Sqlite) { // CREATE TABLE DatabaseItemStat_v2 (id_databaseitemstat integer primary key autoincrement, id_databaseitem BIGINT, Stat TEXT, TextValue TEXT, val1 DOUBLE, constraint FK9663A5FC6B4AFA92 foreign key (id_databaseitem) references DatabaseItem_v2) string[] tables = new[] { "DatabaseItemStat_v2", "DatabaseItem_v2", "ItemTag" }; string fetchCreateTableQuery = "SELECT sql FROM sqlite_master WHERE type='table' AND name = :table"; using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { foreach (var table in tables) { string recreateQuery = session.CreateSQLQuery(fetchCreateTableQuery).SetParameter("table", table).UniqueResult <string>(); session.CreateSQLQuery("DROP TABLE IF EXISTS " + table).ExecuteUpdate(); session.CreateSQLQuery(recreateQuery).ExecuteUpdate(); } transaction.Commit(); } } } else { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateSQLQuery("DELETE FROM DatabaseItemStat_v2 cascade").ExecuteUpdate(); session.CreateSQLQuery("DELETE FROM DatabaseItem_v2 cascade").ExecuteUpdate(); session.CreateSQLQuery("DELETE FROM ItemTag cascade").ExecuteUpdate(); transaction.Commit(); } } } }
public void UpdatePlayerItemId(int uqHash, long playerItemId) { if (playerItemId == 0) { throw new ArgumentException("The argument playerItemId cannot be 0"); } using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { int numRowsAffected = session.CreateSQLQuery("UPDATE ReplicaItem SET playeritemid = :pid WHERE uqhash = :hash") .SetParameter("pid", playerItemId) .SetParameter("hash", uqHash) .ExecuteUpdate(); // Maybe IA was not running when the item was added? Or it's from another player on the same cloud sync. // IA will have to request these stats from GD. if (numRowsAffected == 0) { Logger.Debug($"Could not update Replica for {uqHash} to playerItemId={playerItemId}, does not exist."); } transaction.Commit(); } } }
/// <summary> /// List all player items /// </summary> /// <returns></returns> public IList <PlayerItem> GetByRecord(string prefixRecord, string baseRecord, string suffixRecord, string materiaRecord, string mod, bool isHardcore) { using (var session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { // TODO: var crits = session.CreateCriteria <PlayerItem>() .Add(Restrictions.Eq("BaseRecord", baseRecord)) .Add(Restrictions.Eq("PrefixRecord", prefixRecord)) .Add(Restrictions.Eq("SuffixRecord", suffixRecord)) .Add(Restrictions.Eq("MateriaRecord", materiaRecord)); if (string.IsNullOrEmpty(mod)) { crits = crits.Add(Restrictions.Or(Restrictions.Eq("Mod", ""), Restrictions.IsNull("Mod"))); } else { crits = crits.Add(Restrictions.Eq("Mod", mod)); } if (isHardcore) { crits = crits.Add(Restrictions.Eq("IsHardcore", true)); } else { crits = crits.Add(Restrictions.Not(Restrictions.Eq("IsHardcore", true))); } return(crits.List <PlayerItem>()); } } }
public Dictionary <string, float> GetSkillTiers() { string sql = @" select baserecord as BaseRecord, val1 as Tier from DatabaseItem_v2 item, DatabaseItemStat_v2 s WHERE s.id_databaseitem = item.id_databaseitem AND item.baserecord IN ( select TextValue from DatabaseItemStat_v2 statItem WHERE statItem.stat = 'modifiedSkillName1' ) AND Stat = 'skillTier'"; Dictionary <string, float> dict = new Dictionary <string, float>(100); using (var session = SessionCreator.OpenSession()) { var results = session.CreateSQLQuery(sql) .SetResultTransformer(Transformers.AliasToBean <SkillTierMapping>()) .List <SkillTierMapping>(); foreach (var m in results) { dict[m.BaseRecord] = (float)m.Tier; } return(dict); } }
public IList <PlayerItem> ListWithMissingStatCache() { var sql = $@" select name as Name, {PlayerItemTable.Id} as Id, {PlayerItemTable.Stackcount}, rarity as Rarity, levelrequirement as LevelRequirement, {PlayerItemTable.Record} as BaseRecord, {PlayerItemTable.Prefix} as PrefixRecord, {PlayerItemTable.Suffix} as SuffixRecord, {PlayerItemTable.ModifierRecord} as ModifierRecord, MateriaRecord as MateriaRecord, {PlayerItemTable.PrefixRarity} as PrefixRarity, {PlayerItemTable.AzureUuid} as AzureUuid, {PlayerItemTable.CloudId} as CloudId, {PlayerItemTable.IsCloudSynchronized} as IsCloudSynchronizedValue, coalesce((SELECT group_concat(Record, '|') FROM PlayerItemRecord pir WHERE pir.PlayerItemId = PI.Id AND NOT Record IN (PI.BaseRecord, PI.SuffixRecord, PI.MateriaRecord, PI.PrefixRecord)),'') AS PetRecord FROM PlayerItem PI WHERE SearchableText IS NULL OR SearchableText = '' LIMIT 50"; using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { return(session.CreateSQLQuery(sql) .SetResultTransformer(new AliasToBeanResultTransformer(typeof(PlayerItem))) .List <PlayerItem>()); } } }
public List <DatabaseItemDto> GetCraftableItems() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { var subquery1 = string.Join(" ", $"SELECT {DatabaseItemStatTable.TextValue} FROM {DatabaseItemStatTable.Table} ", $"WHERE {DatabaseItemStatTable.Stat} = 'artifactName'" ); var subquery2 = string.Join(" ", $"SELECT {DatabaseItemStatTable.Item} FROM {DatabaseItemStatTable.Table} ", $" WHERE {DatabaseItemStatTable.Stat} = 'Class' AND {DatabaseItemStatTable.TextValue} = 'ItemRelic'" ); var sql = string.Join(" ", $" SELECT {DatabaseItemTable.Record} as Record,", $"{DatabaseItemTable.Name} as Name, {DatabaseItemStatTable.Stat} as Stat, ", $"{DatabaseItemStatTable.Value} as Value, {DatabaseItemStatTable.TextValue} as TextValue", $" FROM {DatabaseItemTable.Table} i, {DatabaseItemStatTable.Table} s ", $" WHERE i.{DatabaseItemTable.Id} = s.{DatabaseItemStatTable.Item} ", $" AND ({DatabaseItemTable.Record} IN ({subquery1}) OR i.{DatabaseItemTable.Id} IN ({subquery2}))" ); Logger.Debug(sql); IQuery query = session.CreateSQLQuery(sql) .SetResultTransformer(Transformers.AliasToBean <InteralRowStat>()); return(ToDto(query.List <InteralRowStat>())); } } }
public bool Exists(PlayerItem item) { string sql = $@" SELECT 1 FROM {PlayerItemTable.Table} WHERE {PlayerItemTable.Record} = :base AND {PlayerItemTable.Prefix} = :prefix AND {PlayerItemTable.Suffix} = :suffix AND {PlayerItemTable.Seed} = :seed "; using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { var result = session.CreateSQLQuery(sql) .SetParameter("base", item.BaseRecord) .SetParameter("prefix", item.PrefixRecord) .SetParameter("suffix", item.SuffixRecord) .SetParameter("seed", item.Seed) .UniqueResult(); if (result != null) { return(true); } return(false); } } }
public void Save(BuddySubscription subscription, List <BuddyItem> items) { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { foreach (var item in items) { item.BuddyId = subscription.Id; session.Save(item); } // Update / Insert records into lookup table foreach (var item in items) { foreach (var record in new[] { item.BaseRecord, item.PrefixRecord, item.SuffixRecord, item.MateriaRecord }) { if (!string.IsNullOrEmpty(record)) { session.CreateSQLQuery( $@"INSERT OR IGNORE INTO {BuddyItemRecordTable.Table} ({BuddyItemRecordTable.Item}, {BuddyItemRecordTable.Record}) VALUES (:id, :record)") .SetParameter("id", item.RemoteItemId) .SetParameter("record", record) .ExecuteUpdate(); } } } transaction.Commit(); } UpdatePetRecords(session, items); } }
public void UpdateState() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { Populate(session); IList <AugmentationItem> items = session.CreateCriteria <AugmentationItem>().List <AugmentationItem>(); var stats = _databaseItemStatDao.GetStats(session, StatFetch.AugmentItems); foreach (var item in items) { var rarity = ItemOperationsUtility.GetRarityForRecord(stats, item.BaseRecord); var minimumLevel = ItemOperationsUtility.GetMinimumLevelForRecord(stats, item.BaseRecord); var name = ItemOperationsUtility.GetItemName(session, stats, item); item.Rarity = rarity; item.MinimumLevel = minimumLevel; item.Name = name; session.Update(item); } transaction.Commit(); } } }
public void Save(List <DatabaseItem> items, ProgressTracker progressTracker) { int idx = 0; const int range = 950; List <DatabaseItem> updated; using (var session = SessionCreator.OpenSession()) { var hashes = new HashSet <string>( session.CreateSQLQuery($"SELECT distinct({DatabaseItemTable.Record} || {DatabaseItemTable.Hash}) from {DatabaseItemTable.Table}") .List <string>() ); // This is absurdly slow updated = items.Where(m => !hashes.Contains(m.Record + m.Hash)).ToList(); } progressTracker.MaxValue = updated.Count; while (idx < updated.Count) { var numItems = Math.Min(updated.Count - idx, range); var batch = updated.GetRange(idx, numItems); Save(batch, progressTracker, true); idx += range; } }
public IList <ItemSetAssociation> GetItemSetAssociations() { const string sql = @" SELECT * FROM ( SELECT BaseRecord, ( SELECT T.name FROM DatabaseItemStat_v2 ST, ItemTag T WHERE id_databaseitem IN (SELECT id_databaseitem FROM DatabaseItem_v2 db WHERE db.baserecord = S.TextValue) AND ST.stat = 'setName' AND T.tag = ST.TextValue ) as SetName FROM DatabaseItemStat_v2 S, DatabaseItem_v2 I WHERE Stat = 'itemSetName' AND S.id_databaseitem = I.id_databaseitem ) x WHERE SetName IS NOT NULL"; using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { return(session.CreateSQLQuery(sql) .SetResultTransformer(Transformers.AliasToBean <ItemSetAssociation>()) .List <ItemSetAssociation>()); } } }
public Dictionary <string, ISet <DBSTatRow> > GetExpacSkillModifierSkills() { string sql = $"SELECT {DatabaseItemTable.Record} AS Record, " + $"{DatabaseItemStatTable.Stat} AS Stat, " + $"{DatabaseItemStatTable.TextValue} AS TextValue, " + $"{DatabaseItemStatTable.Value} AS Value " + $"FROM {DatabaseItemTable.Table} item, {DatabaseItemStatTable.Table} stat " + $"WHERE {DatabaseItemTable.Record} LIKE '%/itemskillsgdx1/%' " + $"AND stat.{DatabaseItemStatTable.Item} = item.{DatabaseItemTable.Id} ORDER BY item.{DatabaseItemTable.Id}"; Dictionary <string, ISet <DBSTatRow> > stats = new Dictionary <string, ISet <DBSTatRow> >(); using (var session = SessionCreator.OpenSession()) { IQuery query = session.CreateSQLQuery(sql).SetResultTransformer(Transformers.AliasToBean <DBSTatRow>()); foreach (DBSTatRow row in query.List()) { if (!stats.ContainsKey(row.Record)) { stats[row.Record] = new HashSet <DBSTatRow>(); } stats[row.Record].Add(row); } } return(stats); }
public IList <CollectionItem> GetItemCollection() { const string sql = @" select baserecord as BaseRecord, name as Name, (select count(*) from PlayerItem P where P.baserecord = item.baserecord) as NumOwned, (select textvalue from DatabaseItemStat_v2 s2 where s2.id_databaseitem = s.id_databaseitem and s2.stat like '%itmap%' limit 1) as Icon from DatabaseItemStat_v2 s, DatabaseItem_v2 item where s.stat = 'itemClassification' and (s.textvalue = 'Legendary' OR s.textvalue = 'Epic') and item.id_databaseitem = s.id_databaseitem and baserecord not like '%/crafting/%' and name is not null and name != '' order by name asc "; using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { return(session.CreateSQLQuery(sql) .SetResultTransformer(Transformers.AliasToBean <CollectionItem>()) .List <CollectionItem>()); } } }
public IList <RecipeItem> SearchForRecipeItems(ItemSearchRequest query) { // No idea when recipes were added, the user probably wants owned items only. if (query.RecentOnly) { return(new List <RecipeItem>()); } using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { ICriteria criterias = session.CreateCriteria <RecipeItem>(); if (!string.IsNullOrEmpty(query.Wildcard)) { criterias.Add(Subqueries.PropertyIn("BaseRecord", DetachedCriteria.For <DatabaseItem>() .Add(Restrictions.InsensitiveLike("Name", string.Format("%{0}%", query.Wildcard.Replace(' ', '%')))) .SetProjection(Projections.Property("Record")))); } AddItemSearchCriterias(criterias, query); criterias.Add(Restrictions.Eq("IsHardcore", query.IsHardcore)); IList <RecipeItem> items = criterias.List <RecipeItem>(); return(items); } } }
public IList <AugmentationItem> Search(ItemSearchRequest query) { // User only wants recent items, so definitely not buyable. if (query.RecentOnly) { return(new List <AugmentationItem>()); } using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { ICriteria criterias = session.CreateCriteria <AugmentationItem>(); if (!string.IsNullOrEmpty(query.Wildcard)) { criterias.Add(Subqueries.PropertyIn("BaseRecord", DetachedCriteria.For <DatabaseItem>() .Add(Restrictions.InsensitiveLike("Name", string.Format("%{0}%", query.Wildcard.Replace(' ', '%')))) .SetProjection(Projections.Property("Record")))); } DatabaseItemDaoImpl.AddItemSearchCriterias(criterias, query); IList <AugmentationItem> items = criterias.List <AugmentationItem>(); return(items); } } }
public DatabaseItemDto FindDtoByRecord(string record) { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { var sql = string.Join(" ", $" SELECT {DatabaseItemTable.Record} as Record, {DatabaseItemTable.Name} as Name, ", $"{DatabaseItemStatTable.Stat} as Stat, {DatabaseItemStatTable.Value} as Value, ", $"{DatabaseItemStatTable.TextValue} as TextValue", $" FROM {DatabaseItemTable.Table} i, {DatabaseItemStatTable.Table} s ", $" WHERE i.{DatabaseItemTable.Id} = s.{DatabaseItemStatTable.Item} ", $" AND {DatabaseItemTable.Record} = :record " ); IQuery query = session.CreateSQLQuery(sql) .SetParameter("record", record) .SetResultTransformer(Transformers.AliasToBean <InteralRowStat>()); foreach (DatabaseItemDto elem in ToDto(query.List <InteralRowStat>())) { return(elem); } } } return(null); }
private IList <BuddyItem> ListAll(string where) { if (string.IsNullOrEmpty(where)) { where = "WHERE 1=1"; } var sql = $@"SELECT {BuddyItemsTable.BaseRecord} as BaseRecord, {BuddyItemsTable.PrefixRecord} as PrefixRecord, {BuddyItemsTable.SuffixRecord} as SuffixRecord, {BuddyItemsTable.ModifierRecord} as ModifierRecord, {BuddyItemsTable.TransmuteRecord} as TransmuteRecord, {BuddyItemsTable.MateriaRecord} as MateriaRecord, {BuddyItemsTable.Rarity} as Rarity, {BuddyItemsTable.Name} as Name, {BuddyItemsTable.LevelRequirement} as MinimumLevel, {BuddyItemsTable.Id} as Id, MAX(1, {BuddyItemsTable.StackCount}) as Count, {BuddyStashTable.Name} as Buddy FROM {BuddyItemsTable.Table}, {BuddyStashTable.Table} {where} AND {BuddyItemsTable.BuddyId} = {BuddyStashTable.User} "; using (ISession session = SessionCreator.OpenSession()) { return(session.CreateSQLQuery(sql) .List <object>() .Select(ToDomainObject) .ToList()); } }
public bool Exists(PlayerItem item) { using (ISession session = SessionCreator.OpenSession()) { using (session.BeginTransaction()) { return(Exists(session, item)); } } }
/// <summary> /// List all items cached /// </summary> /// <returns></returns> public override IList <BuddySubscription> ListAll() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { return(session.CreateCriteria <BuddySubscription>().List <BuddySubscription>()); } } }
public IList <DeletedPlayerItem> GetItemsMarkedForOnlineDeletion() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { return(session.QueryOver <DeletedPlayerItem>().List()); } } }
/// <summary> /// Delete duplicate items (items duplicated via bugs, not simply similar items) /// </summary> public void DeleteDuplicates() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { // Mark all duplicates for deletion from online backups session.CreateSQLQuery(@" insert into deletedplayeritem_v3(id) select cloudid FROM playeritem WHERE Id IN ( SELECT Id FROM ( SELECT MAX(Id) as Id, (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) as UQ FROM PlayerItem WHERE (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) IN ( SELECT UQ FROM ( SELECT (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) as UQ, COUNT(*) as C FROM PlayerItem WHERE baserecord NOT LIKE '%materia%' AND baserecord NOT LIKE '%questitems%' AND baserecord NOT LIKE '%potions%' AND baserecord NOT LIKE '%crafting%' AND StackCount < 2 -- Potions, components, etc GROUP BY UQ ) X WHERE C > 1 ) Group BY UQ ) Z ) AND cloud_hassync AND cloudid IS NOT NULL AND cloudid NOT IN (SELECT id FROM deletedplayeritem_v3) AND cloudid != '' ").ExecuteUpdate(); // Delete duplicates (if there are multiple, only one will be deleted) int duplicatesDeleted = session.CreateSQLQuery(@" DELETE FROM PlayerItem WHERE Id IN ( SELECT Id FROM ( SELECT MAX(Id) as Id, (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) as UQ FROM PlayerItem WHERE (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) IN ( SELECT UQ FROM ( SELECT (baserecord || prefixrecord || modifierrecord || suffixrecord || materiarecord || transmuterecord || seed) as UQ, COUNT(*) as C FROM PlayerItem WHERE baserecord NOT LIKE '%materia%' AND baserecord NOT LIKE '%questitems%' AND baserecord NOT LIKE '%potions%' AND baserecord NOT LIKE '%crafting%' AND StackCount < 2 -- Potions, components, etc GROUP BY UQ ) X WHERE C > 1 ) Group BY UQ ) Z ) ").ExecuteUpdate(); transaction.Commit(); } } }
/// <summary> /// Get a single item which has not been synchronized with online backups /// </summary> public PlayerItem GetSingleUnsynchronizedItem() { using (var session = SessionCreator.OpenSession()) { return(session.QueryOver <PlayerItem>() .Where(m => m.OnlineId == null) .Take(1) .SingleOrDefault()); } }
public long GetNumUnsynchronizedItems() { using (var session = SessionCreator.OpenSession()) { return(session.QueryOver <PlayerItem>() .Where(m => m.OnlineId == null) .ToRowCountInt64Query() .SingleOrDefault <long>()); } }
/// <summary> /// Simply delete the 'mark for deletion' tags /// </summary> /// <returns></returns> public void ClearItemsMarkedForOnlineDeletion() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateQuery($"DELETE FROM {nameof(DeletedPlayerItem)}").ExecuteUpdate(); transaction.Commit(); } } }
public IList <PlayerItem> GetUnsynchronizedItems() { using (var session = SessionCreator.OpenSession()) { return(session.QueryOver <PlayerItem>() .Where(m => m.AzureUuid == null) //.Where(m => string.IsNullOrEmpty(m.AzureUuid)) .List <PlayerItem>()); } }
public void DeleteAll() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.CreateSQLQuery("DELETE FROM PlayerItemStat").ExecuteUpdate(); session.CreateSQLQuery("DELETE FROM PlayerItem").ExecuteUpdate(); transaction.Commit(); } } }
public Int64 GetRowCount() { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { return(session.CreateCriteria <DatabaseItem>() .SetProjection(Projections.RowCountInt64()) .UniqueResult <Int64>()); } } }
/// <summary> /// Find an item based on it's online ID /// </summary> /// <returns></returns> public PlayerItem GetByOnlineId(long oid) { using (ISession session = SessionCreator.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { return(session.QueryOver <PlayerItem>() .Where(m => m.OnlineId == oid) .SingleOrDefault()); } } }