public void NH_1085_WillIgnoreParametersIfDoesNotAppearInQuery() { using (ISession s = sessions.OpenSession()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add("from Item i where i.Id in (:ids)") .Add("from Item i where i.Id in (:ids2)") .SetParameterList("ids", new[] { 50 }) .SetParameterList("ids2", new[] { 50 }); multiQuery.List(); } }
public void DoesntThrowExceptionWhenHqlQueryIsGiven() { using (ISession session = OpenSession()) using (ITransaction tx = session.BeginTransaction()) { IQuery sqlQuery = session.CreateQuery("from Document"); IMultiQuery q = session .CreateMultiQuery() .Add(sqlQuery); q.List(); } }
public void NH_1085_WillIgnoreParametersIfDoesNotAppearInQuery() { using (ISession s = sessions.OpenSession()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add(s.CreateSQLQuery("select * from ITEM where Id in (:ids)").AddEntity(typeof(Item))) .Add(s.CreateSQLQuery("select * from ITEM where Id in (:ids2)").AddEntity(typeof(Item))) .SetParameterList("ids", new[] { 50 }) .SetParameterList("ids2", new[] { 50 }); multiQuery.List(); } }
public async Task <IQuery> GenerateSql(SolutionFolderItem item) { IMultiQuery query = Queries.BeginTransaction(); query.Comment(item.MyName); foreach (ISolutionItem i in item.Items) { if (i.IsExportable) { query.Add(await registry.Value.GenerateSql(i)); } } return(query.Close()); }
private static void GeneratePrimaryKeyDeletion(DatabaseTableDefinitionJson definition, IReadOnlyList <DatabaseKey>?keys, IMultiQuery query) { if (keys == null || keys.Count == 0) { return; } ITable table; if (definition.ForeignTable != null) { foreach (var foreign in definition.ForeignTable) { table = query.Table(foreign.TableName); if (keys.Count > 1 && keys[0].Count == 1) { foreach (var chunk in keys.Select(k => k[0]).Chunk(128)) { table.WhereIn(foreign.ForeignKeys[0], chunk).Delete(); } } else { foreach (var key in keys) { GenerateWherePrimaryKey(foreign.ForeignKeys, table, key).Delete(); } } } } table = query.Table(definition.TableName); if (keys.Count > 1 && keys[0].Count == 1) { foreach (var chunk in keys.Select(k => k[0]).Chunk(128)) { table.WhereIn(definition.TablePrimaryKeyColumnName, chunk).Delete(); } } else { foreach (var key in keys) { GenerateWherePrimaryKey(definition, table, key).Delete(); } } }
public void AliasToBeanTransformerShouldApplyCorrectlyToMultiQueryAsync() { using (var s = OpenSession()) using (var t = s.BeginTransaction()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add(s.CreateQuery("select entity.Id as EntityId from Entity entity") .SetResultTransformer(Transformers.AliasToBean(typeof(EntityDTO))) ); IList results = null; Assert.That(async() => results = await(multiQuery.ListAsync()), Throws.Nothing); var elementOfFirstResult = ((IList)results[0])[0]; Assert.That(elementOfFirstResult, Is.TypeOf <EntityDTO>().And.Property("EntityId").EqualTo(1)); } }
public void AliasToBeanTransformerShouldApplyCorrectlyToMultiQuery() { using (var s = OpenSession()) using (var t = s.BeginTransaction()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add(s.CreateQuery("select entity.Id as EntityId from Entity entity") .SetResultTransformer(Transformers.AliasToBean(typeof(EntityDTO))) ); IList results = null; Executing.This(() => results = multiQuery.List()).Should().NotThrow(); var elementOfFirstResult = ((IList)results[0])[0]; elementOfFirstResult.Should().Be.OfType <EntityDTO>().And.ValueOf.EntityId.Should().Be(1); } }
private void DoMutiQueryAndAssert() { using (ISession s = OpenSession()) { IMultiQuery MultiQuery = s.CreateMultiQuery() .Add(s.CreateQuery("from Item i where i.Id > ?") .SetInt32(0, 50) .SetFirstResult(10)) .Add(s.CreateQuery("select count(*) from Item i where i.Id > ?") .SetInt32(0, 50)); MultiQuery.SetCacheable(true); IList results = MultiQuery.List(); IList items = (IList)results[0]; Assert.AreEqual(89, items.Count); long count = (long)((IList)results[1])[0]; Assert.AreEqual(99L, count); } }
private void BuildInsert(IMultiQuery query, ISmartScriptLine[] serializedScript, IConditionLine[] serializedConditions) { if (serializedScript.Length == 0) { return; } var lines = serializedScript.Select(s => GenerateSingleSai(query, s)); query.Table(SmartScriptTableName).BulkInsert(lines); query.BlankLine(); query.Add(conditionQueryGenerator.BuildDeleteQuery(new IDatabaseProvider.ConditionKey( SmartConstants.ConditionSourceSmartScript, null, script.EntryOrGuid, (int)script.SourceType))); query.Add(conditionQueryGenerator.BuildInsertQuery(serializedConditions)); }
public void TwoMultiQueriesWithDifferentPagingGetDifferentResultsWhenUsingCachedQueries() { CreateItems(); using (ISession s = OpenSession()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add(s.CreateQuery("from Item i where i.Id > ?") .SetInt32(0, 50) .SetFirstResult(10)) .Add(s.CreateSQLQuery("select count(*) as count from ITEM where Id > ?").AddScalar("count", NHibernateUtil.Int64) .SetInt32(0, 50)); multiQuery.SetCacheable(true); IList results = multiQuery.List(); IList items = (IList)results[0]; Assert.AreEqual(89, items.Count); long count = (long)((IList)results[1])[0]; Assert.AreEqual(99L, count); } using (ISession s = OpenSession()) { IMultiQuery multiQuery = s.CreateMultiQuery() .Add(s.CreateSQLQuery("select * from ITEM where Id > ?").AddEntity(typeof(Item)) .SetInt32(0, 50) .SetFirstResult(20)) .Add(s.CreateQuery("select count(*) from Item i where i.Id > ?") .SetInt32(0, 50)); multiQuery.SetCacheable(true); IList results = multiQuery.List(); IList items = (IList)results[0]; Assert.AreEqual(79, items.Count, "Should have gotten different result here, because the paging is different"); long count = (long)((IList)results[1])[0]; Assert.AreEqual(99L, count); } RemoveAllItems(); }
/// <summary> /// Executes the specified query and return the results /// </summary> /// <param name="session">The session to execute the query in.</param> /// <returns>an array of results, one for each query added</returns> public object Execute(ISession session) { // create a multi-query IMultiQuery multiQuery = session.CreateMultiQuery(); foreach (ActiveRecordBaseQuery arQuery in _queryList) { // rule: if a query implements InternalExecute, it will fail // (we are depending on the ability to call CreateQuery() // and get a self-contained query object) if ((arQuery is CountQuery) || (arQuery is ActiveRecordCriteriaQuery)) { throw new ActiveRecordException("Criteria Based queries are not supported!"); } // add the executable IQuery to our multi-query arQuery.AddQuery(session, multiQuery); } // execute multiquery object resultSetArray = multiQuery.List(); return(resultSetArray); }
private object GenerateSingleSai(IMultiQuery query, ISmartScriptLine line) { return(new { entryorguid = query.Variable("ENTRY"), source_type = (int)script.SourceType, id = line.Id, link = line.Link, event_type = line.EventType, event_phase_mask = line.EventPhaseMask, event_chance = line.EventChance, event_flags = line.EventFlags, event_param1 = line.EventParam1, event_param2 = line.EventParam2, event_param3 = line.EventParam3, event_param4 = line.EventParam4, action_type = line.ActionType, action_param1 = line.ActionParam1, action_param2 = line.ActionParam2, action_param3 = line.ActionParam3, action_param4 = line.ActionParam4, action_param5 = line.ActionParam5, action_param6 = line.ActionParam6, target_type = line.TargetType, target_param1 = line.TargetParam1, target_param2 = line.TargetParam2, target_param3 = line.TargetParam3, target_x = line.TargetX, target_y = line.TargetY, target_z = line.TargetZ, target_o = line.TargetO, comment = line.Comment }); }
private void BuildInsert(IMultiQuery query) { var(serializedScript, serializedConditions) = scriptExporter.ToDatabaseCompatibleSmartScript(script); if (serializedScript.Length == 0) { return; } var lines = serializedScript.Select(s => GenerateSingleSai(query, s)); query.Table("smart_scripts").BulkInsert(lines); query.BlankLine(); query.Add(conditionQueryGenerator.BuildDeleteQuery(new IDatabaseProvider.ConditionKey( SmartConstants.ConditionSourceSmartScript, null, script.EntryOrGuid, (int)script.SourceType))); query.Add(conditionQueryGenerator.BuildInsertQuery(serializedConditions)); }
public void CanNotRetrieveQueryResultWithUnknownKey() { using (ISession session = OpenSession()) { IMultiQuery multiQuery = session.CreateMultiQuery(); multiQuery.Add("firstQuery", session.CreateSQLQuery("select * from ITEM").AddEntity(typeof(Item))); try { multiQuery.GetResult("unknownKey"); Assert.Fail("This should've thrown an InvalidOperationException"); } catch (InvalidOperationException) { } catch (Exception) { Assert.Fail("This should've thrown an InvalidOperationException"); } } }
public void CanNotRetrieveCriteriaResultWithUnknownKey() { using (ISession session = OpenSession()) { IMultiQuery multiQuery = session.CreateMultiQuery(); multiQuery.Add("firstCriteria", session.CreateQuery("from Item")); try { IList firstResult = (IList)multiQuery.GetResult("unknownKey"); Assert.Fail("This should've thrown an InvalidOperationException"); } catch (InvalidOperationException) { } catch (Exception) { Assert.Fail("This should've thrown an InvalidOperationException"); } } }
public void CanGetMultiQueryFromSecondLevelCache() { CreateItems(); //set the query in the cache DoMutiQueryAndAssert(); Hashtable cacheHashtable = GetHashTableUsedAsQueryCache(); IList cachedListEntry = (IList) new ArrayList(cacheHashtable.Values)[0]; IList cachedQuery = (IList)cachedListEntry[1]; IList firstQueryResults = (IList)cachedQuery[0]; firstQueryResults.Clear(); firstQueryResults.Add(3); firstQueryResults.Add(4); IList secondQueryResults = (IList)cachedQuery[1]; secondQueryResults[0] = 2L; using (ISession s = sessions.OpenSession()) { IMultiQuery MultiQuery = s.CreateMultiQuery() .Add(s.CreateQuery("from Item i where i.Id > ?") .SetInt32(0, 50) .SetFirstResult(10)) .Add(s.CreateQuery("select count(*) from Item i where i.Id > ?") .SetInt32(0, 50)); MultiQuery.SetCacheable(true); IList results = MultiQuery.List(); IList items = (IList)results[0]; Assert.AreEqual(2, items.Count); long count = (long)((IList)results[1])[0]; Assert.AreEqual(2L, count); } RemoveAllItems(); }
public void CanNotAddCriteriaWithKeyThatAlreadyExists() { using (ISession session = OpenSession()) { IMultiQuery multiQuery = session.CreateMultiQuery(); IQuery firstQuery = session.CreateQuery("from Item i where i.Id < :id") .SetInt32("id", 50); try { IQuery secondQuery = session.CreateQuery("from Item"); multiQuery.Add("first", firstQuery).Add("second", secondQuery); } catch (InvalidOperationException) { } catch (Exception) { Assert.Fail("This should've thrown an InvalidOperationException"); } } }
public void CanAddIQueryWithKeyAndRetrieveResultsWithKey() { CreateItems(); using (ISession session = OpenSession()) { IMultiQuery multiQuery = session.CreateMultiQuery(); IQuery firstQuery = session.CreateQuery("from Item i where i.Id < :id") .SetInt32("id", 50); IQuery secondQuery = session.CreateQuery("from Item"); multiQuery.Add("first", firstQuery).Add("second", secondQuery); IList secondResult = (IList)multiQuery.GetResult("second"); IList firstResult = (IList)multiQuery.GetResult("first"); Assert.Greater(secondResult.Count, firstResult.Count); } RemoveAllItems(); }
public void CanNotAddQueryWithKeyThatAlreadyExists() { using (ISession session = OpenSession()) { IMultiQuery multiQuery = session.CreateMultiQuery(); IQuery firstQuery = session.CreateSQLQuery("select * from ITEM where Id < :id").AddEntity(typeof(Item)) .SetInt32("id", 50); try { IQuery secondQuery = session.CreateSQLQuery("select * from ITEM").AddEntity(typeof(Item)); multiQuery.Add("first", firstQuery).Add("second", secondQuery); } catch (InvalidOperationException) { } catch (Exception) { Assert.Fail("This should've thrown an InvalidOperationException"); } } }
public static IVariable Variable(this IMultiQuery query, string name) { return(new Variable(name)); }
public static IQuery BlankLine(this IMultiQuery query) { return(new Query(query, "")); }
public static IQuery DefineVariable(this IMultiQuery query, string variableName, object?value) { return(new Query(query, $"SET @{variableName} := {value.ToSql()};")); }
public static IQuery Comment(this IMultiQuery query, string comment) { return(new Query(query, $" -- {comment}")); }
private void BuildUpdate(IMultiQuery query) { switch (script.SourceType) { case SmartScriptType.Creature: { uint?entry; if (script.EntryOrGuid >= 0) { entry = (uint)script.EntryOrGuid; } else { entry = databaseProvider.GetCreatureByGuid((uint)-script.EntryOrGuid)?.Entry; } if (entry.HasValue) { var condition = query .Table("creature_template") .Where(t => t.Column <int>("entry") == t.Variable <int>("ENTRY")); if (script.EntryOrGuid != entry.Value) { condition = query .Table("creature_template") .Where(t => t.Column <uint>("entry") == entry.Value); } var update = condition .Set("AIName", currentCoreVersion.Current.SmartScriptFeatures.CreatureSmartAiName) .Set("ScriptName", ""); if (currentCoreVersion.Current.DatabaseFeatures.HasAiEntry) { update = update.Set("AIEntry", 0); } update.Update(); } else { query.Comment("[WARNING] cannot set creature AI to SmartAI, because guid not found in `creature` table!"); } break; } case SmartScriptType.GameObject: { uint?entry; if (script.EntryOrGuid >= 0) { entry = (uint)script.EntryOrGuid; } else { entry = databaseProvider.GetCreatureByGuid((uint)-script.EntryOrGuid)?.Entry; } if (entry.HasValue) { var condition = query .Table("gameobject_template") .Where(t => t.Column <int>("entry") == t.Variable <int>("ENTRY")); if (script.EntryOrGuid != entry.Value) { condition = query .Table("gameobject_template") .Where(t => t.Column <uint>("entry") == entry.Value); } condition .Set("AIName", currentCoreVersion.Current.SmartScriptFeatures.GameObjectSmartAiName) .Update(); } else { query.Comment("[WARNING] cannot set gameobject AI to SmartGameObjectAI, because guid not found in `gameobject` table!"); } break; } case SmartScriptType.Quest: query.Table("quest_template_addon") .InsertIgnore(new { ID = query.Variable("ENTRY") }); query.Table("quest_template_addon") .Where(r => r.Column <int>("ID") == r.Variable <int>("ENTRY")) .Set("ScriptName", "SmartQuest") .Update(); break; case SmartScriptType.Spell: query.Comment("TrinityCore doesn't support Smart Spell Script"); break; case SmartScriptType.Aura: query.Comment("TrinityCore doesn't support Smart Aura Script"); break; case SmartScriptType.Cinematic: query.Comment("TrinityCore doesn't support Smart Cinematic Script"); break; case SmartScriptType.AreaTrigger: query.Table("areatrigger_scripts") .Where(r => r.Column <int>("entry") == r.Variable <int>("ENTRY")) .Delete(); query.Table("areatrigger_scripts") .Insert(new { entry = query.Variable("ENTRY"), ScriptName = "SmartTrigger" }); break; case SmartScriptType.AreaTriggerEntityServerSide: query.Table("areatrigger_template") .Where(r => r.Column <int>("Id") == r.Variable <int>("ENTRY") && r.Column <bool>("IsServerSide")) .Set("ScriptName", "SmartAreaTriggerAI") .Update(); break; case SmartScriptType.AreaTriggerEntity: query.Table("areatrigger_template") .Where(r => r.Column <int>("Id") == r.Variable <int>("ENTRY") && !r.Column <bool>("IsServerSide")) .Set("ScriptName", "SmartAreaTriggerAI") .Update(); break; } }
private void BuildDelete(IMultiQuery query) { query.Table("smart_scripts") .Where(r => r.Column <int>("entryOrGuid") == r.Variable <int>("ENTRY") && r.Column <int>("source_type") == (int)script.SourceType).Delete(); }
private void BuildDelete(IMultiQuery query, ISmartScriptLine[] serializedLines) { foreach (var pair in serializedLines.Select(l => (l.ScriptSourceType, l.EntryOrGuid)) .Concat(new (int ScriptSourceType, int EntryOrGuid)[] { ((int)script.SourceType, script.EntryOrGuid) })
public IQuery GenerateInsertQuery(IReadOnlyList <DatabaseKey> keys, IDatabaseTableData tableData) { if (keys.Count == 0) { return(Queries.Empty()); } IMultiQuery query = Queries.BeginTransaction(); query.Add(GenerateDeleteQuery(tableData.TableDefinition, keys)); if (tableData.Entities.Count == 0) { return(query.Close()); } var columns = tableData.TableDefinition.TableColumns .Select(c => c.Value) .Where(col => !col.IsMetaColumn && !col.IsConditionColumn) .GroupBy(columns => columns.ForeignTable ?? tableData.TableDefinition.TableName) .ToDictionary(g => g.Key, g => g.ToList()); HashSet <EntityKey> entityKeys = new(); Dictionary <string, List <Dictionary <string, object?> > > inserts = new(tableData.Entities.Count); List <string> duplicates = new List <string>(); var comparer = new EntityComparer(tableData.TableDefinition); foreach (var entity in tableData.Entities.OrderBy(t => t, comparer)) { if (!entity.Phantom && !keys.Contains(entity.Key)) { continue; } bool duplicate = tableData.TableDefinition.PrimaryKey != null && !entityKeys.Add(new EntityKey(entity, tableData.TableDefinition)); foreach (var table in columns) { bool isDefault = true; bool isMainTable = table.Key == tableData.TableDefinition.TableName; var cells = table.Value.ToDictionary(c => c.DbColumnName, c => { var cell = entity.GetCell(c.DbColumnName) !; if (c.AutogenerateComment != null && cell is DatabaseField <string> sField) { var evaluator = new DatabaseExpressionEvaluator(calculatorService, parameterFactory, tableData.TableDefinition, c.AutogenerateComment !); var comment = evaluator.Evaluate(entity); if (comment is string s) { return(s.AddComment(sField.Current.Value)); } } var columnDefinition = tableData.TableDefinition.TableColumns[cell.FieldName]; if (!columnDefinition.CanBeNull && cell.Object is null) { return(columnDefinition.Default ?? 0L); } if (columnDefinition.CanBeNull && cell.Object is null) { return(null); } if (!isMainTable && columnDefinition.IsTypeFloat && ((cell.Object == null && columnDefinition.Default != null) || cell.Object != null && !cell.Object.Equals(columnDefinition.Default ?? 0f))) { isDefault = false; } if (!isMainTable && columnDefinition.IsTypeLong && ((cell.Object == null && columnDefinition.Default != null) || cell.Object != null && !cell.Object.Equals(columnDefinition.Default ?? 0L))) { isDefault = false; } return(FixUnixTimestampAndNullability(columnDefinition, cell.Object)); }); if (!isMainTable) { if (isDefault) { continue; } var newCells = new Dictionary <string, object?>(); var foreignKeys = tableData.TableDefinition.ForeignTableByName ![table.Key].ForeignKeys;
/// <summary> /// Add this query to a multiquery /// </summary> /// <param name="session">an <c>ISession</c> shared by all queries in the multiquery</param> /// <param name="multiquery">the <c>IMultiQuery</c> that will receive the newly created query</param> internal void AddQuery(ISession session, IMultiQuery multiquery) { IQuery query = CreateQuery(session); multiquery.Add(query); }
public static IRawText Raw(this IMultiQuery query, string text) { return(new RawText(text)); }
internal QueryBatch(PersistenceContext context) { _multiQuery = context.Session.CreateMultiQuery(); _tokens = new List <object>(); }
internal Query(IMultiQuery multiQuery, string query) { multiQuery.Add(this); QueryString = query; }