public void TestFilterGroup_Optimize_SimplifiesConditions() { FilterGroup topFilter = new FilterGroup(Conjunction.Or, new FilterGroup(Conjunction.And, new EqualToFilter(new Column("FirstName"), new StringLiteral("Albert")), new FilterGroup(Conjunction.And, new EqualToFilter(new Column("LastName"), new StringLiteral("Einstein")))), new FilterGroup(Conjunction.And, new EqualToFilter(new Column("FirstName"), new StringLiteral("Max")), new FilterGroup(Conjunction.And, new EqualToFilter(new Column("LastName"), new StringLiteral("Planck"))))); wrapInParentheses(topFilter, true); SelectBuilder selectBuilder = new SelectBuilder(); selectBuilder.AddTable(new Table("Person")); selectBuilder.AddProjection(new Column("FirstName")); selectBuilder.AddProjection(new Column("LastName")); selectBuilder.AddWhere(topFilter); Formatter formatter = new Formatter(); string beforeActual = formatter.GetCommandText(selectBuilder); const string beforeExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND ((LastName = 'Einstein'))) OR ((FirstName = 'Max') AND ((LastName = 'Planck'))))"; Assert.AreEqual(beforeExpected, beforeActual, "The initial query had an unexpected string representation."); wrapInParentheses(topFilter, false); topFilter.Optimize(); wrapInParentheses(topFilter, true); string afterActual = formatter.GetCommandText(selectBuilder, new CommandOptions() { WrapFiltersInParentheses = true }); const string afterExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND (LastName = 'Einstein')) OR ((FirstName = 'Max') AND (LastName = 'Planck')))"; Assert.AreEqual(afterExpected, afterActual, "The optimized query had an unexpected string representation."); }
/// <summary> /// Initializes a new instance of a DeleteBuilder. /// </summary> /// <param name="table">The table being deleted from.</param> /// <param name="alias">The alias to use to refer to the table.</param> public DeleteBuilder(Table table, string alias = null) { if (table == null) { throw new ArgumentNullException("table"); } _table = new AliasedSource(table, alias); _where = new FilterGroup(); }
/// <summary> /// Initializes a new instance of a SelectBuilder. /// </summary> public SelectBuilder() { _from = new List<IJoinItem>(); _projection = new List<AliasedProjection>(); _where = new FilterGroup(); _orderBy = new List<OrderBy>(); _groupBy = new List<IGroupByItem>(); _having = new FilterGroup(); sources = new SourceCollection(); }
/// <summary> /// Initializes a new instance of a FilteredJoin. /// </summary> /// <param name="left">The left hand item in the join.</param> /// <param name="right">The right hand item in the join.</param> protected FilteredJoin(Join left, AliasedSource right) : base(left, right) { on = new FilterGroup(Conjunction.And); }
private bool shouldWrapGroupInParentheses(FilterGroup group) { // Add parenthesis when they are explicitly requested if (group.WrapInParentheses == true) { return true; } // Add parenthesis when precedence should be automatic if (options.WrapFiltersInParentheses && group.Conjunction == Conjunction.Or) { return true; } return false; }
/// <summary> /// Generates the text for a FilterGroup builder. /// </summary> /// <param name="item">The FilterGroup builder to generate the text for.</param> protected internal override void VisitFilterGroup(FilterGroup item) { if (!item.Filters.Any()) { throw new SQLGenerationException(Resources.EmptyFilterGroup); } bool wrapInParentheses = shouldWrapGroupInParentheses(item); if (wrapInParentheses) { writer.Write("("); } ConjunctionConverter converter = new ConjunctionConverter(); string conjunction = " " + converter.ToString(item.Conjunction) + " "; join(conjunction, item.Filters); if (wrapInParentheses) { writer.Write(")"); } }
private IFilter buildFilter(MatchResult result) { MatchResult notResult = result.Matches[SqlGrammar.Filter.Not.Name]; if (notResult.IsMatch) { MatchResult filterResult = notResult.Matches[SqlGrammar.Filter.Not.Filter]; IFilter filter = buildFilter(filterResult); return new NotFilter(filter); } MatchResult wrappedResult = result.Matches[SqlGrammar.Filter.Wrapped.Name]; if (wrappedResult.IsMatch) { MatchResult filterResult = wrappedResult.Matches[SqlGrammar.Filter.Wrapped.Filter]; FilterGroup nested = new FilterGroup(); IFilter innerFilter = buildOrFilter(filterResult); nested.AddFilter(innerFilter); nested.WrapInParentheses = true; return nested; } MatchResult quantifyResult = result.Matches[SqlGrammar.Filter.Quantify.Name]; if (quantifyResult.IsMatch) { MatchResult expressionResult = quantifyResult.Matches[SqlGrammar.Filter.Quantify.Expression]; IFilterItem filterItem = (IFilterItem)buildArithmeticItem(expressionResult); MatchResult quantifierResult = quantifyResult.Matches[SqlGrammar.Filter.Quantify.Quantifier]; Quantifier quantifier = buildQuantifier(quantifierResult); IValueProvider valueProvider = null; MatchResult selectResult = quantifyResult.Matches[SqlGrammar.Filter.Quantify.SelectStatement]; if (selectResult.IsMatch) { valueProvider = buildSelectStatement(selectResult); } MatchResult valueListResult = quantifyResult.Matches[SqlGrammar.Filter.Quantify.ValueList]; if (valueListResult.IsMatch) { ValueList values = new ValueList(); buildValueList(valueListResult, values); valueProvider = values; } MatchResult operatorResult = quantifyResult.Matches[SqlGrammar.Filter.Quantify.ComparisonOperator]; return buildQuantifierFilter(operatorResult, filterItem, quantifier, valueProvider); } MatchResult functionResult = result.Matches[SqlGrammar.Filter.Function.Name]; if (functionResult.IsMatch) { MatchResult expressionResult = functionResult.Matches[SqlGrammar.Filter.Function.Expression]; return buildFunctionCall(expressionResult); } MatchResult orderResult = result.Matches[SqlGrammar.Filter.Order.Name]; if (orderResult.IsMatch) { MatchResult leftResult = orderResult.Matches[SqlGrammar.Filter.Order.Left]; IFilterItem left = (IFilterItem)buildArithmeticItem(leftResult); MatchResult rightResult = orderResult.Matches[SqlGrammar.Filter.Order.Right]; IFilterItem right = (IFilterItem)buildArithmeticItem(rightResult); MatchResult operatorResult = orderResult.Matches[SqlGrammar.Filter.Order.ComparisonOperator]; return buildOrderFilter(operatorResult, left, right); } MatchResult betweenResult = result.Matches[SqlGrammar.Filter.Between.Name]; if (betweenResult.IsMatch) { MatchResult expressionResult = betweenResult.Matches[SqlGrammar.Filter.Between.Expression]; IFilterItem expression = (IFilterItem)buildArithmeticItem(expressionResult); MatchResult lowerBoundResult = betweenResult.Matches[SqlGrammar.Filter.Between.LowerBound]; IFilterItem lowerBound = (IFilterItem)buildArithmeticItem(lowerBoundResult); MatchResult upperBoundResult = betweenResult.Matches[SqlGrammar.Filter.Between.UpperBound]; IFilterItem upperBound = (IFilterItem)buildArithmeticItem(upperBoundResult); BetweenFilter filter = new BetweenFilter(expression, lowerBound, upperBound); MatchResult betweenNotResult = betweenResult.Matches[SqlGrammar.Filter.Between.NotKeyword]; filter.Not = betweenNotResult.IsMatch; return filter; } MatchResult likeResult = result.Matches[SqlGrammar.Filter.Like.Name]; if (likeResult.IsMatch) { MatchResult leftResult = likeResult.Matches[SqlGrammar.Filter.Like.Left]; IFilterItem left = (IFilterItem)buildArithmeticItem(leftResult); MatchResult rightResult = likeResult.Matches[SqlGrammar.Filter.Like.Right]; IFilterItem right = (IFilterItem)buildArithmeticItem(rightResult); LikeFilter filter = new LikeFilter(left, right); MatchResult likeNotResult = likeResult.Matches[SqlGrammar.Filter.Like.NotKeyword]; filter.Not = likeNotResult.IsMatch; return filter; } MatchResult isResult = result.Matches[SqlGrammar.Filter.Is.Name]; if (isResult.IsMatch) { MatchResult expressionResult = isResult.Matches[SqlGrammar.Filter.Is.Expression]; IFilterItem expression = (IFilterItem)buildArithmeticItem(expressionResult); NullFilter filter = new NullFilter(expression); MatchResult isNotResult = isResult.Matches[SqlGrammar.Filter.Is.NotKeyword]; filter.Not = isNotResult.IsMatch; return filter; } MatchResult inResult = result.Matches[SqlGrammar.Filter.In.Name]; if (inResult.IsMatch) { MatchResult expressionResult = inResult.Matches[SqlGrammar.Filter.In.Expression]; IFilterItem expression = (IFilterItem)buildArithmeticItem(expressionResult); IValueProvider valueProvider = null; MatchResult valuesResult = inResult.Matches[SqlGrammar.Filter.In.Values.Name]; if (valuesResult.IsMatch) { MatchResult valueListResult = valuesResult.Matches[SqlGrammar.Filter.In.Values.ValueList]; ValueList values = new ValueList(); buildValueList(valueListResult, values); valueProvider = values; } MatchResult selectResult = inResult.Matches[SqlGrammar.Filter.In.Select.Name]; if (selectResult.IsMatch) { MatchResult selectStatementResult = selectResult.Matches[SqlGrammar.Filter.In.Select.SelectStatement]; valueProvider = buildSelectStatement(selectStatementResult); } MatchResult functionCall = inResult.Matches[SqlGrammar.Filter.In.FunctionCall]; if (functionCall.IsMatch) { valueProvider = buildFunctionCall(functionCall); } InFilter filter = new InFilter(expression, valueProvider); MatchResult inNotResult = inResult.Matches[SqlGrammar.Filter.In.NotKeyword]; filter.Not = inNotResult.IsMatch; return filter; } MatchResult existsResult = result.Matches[SqlGrammar.Filter.Exists.Name]; if (existsResult.IsMatch) { MatchResult selectExpressionResult = existsResult.Matches[SqlGrammar.Filter.Exists.SelectStatement]; ISelectBuilder builder = buildSelectStatement(selectExpressionResult); ExistsFilter filter = new ExistsFilter(builder); return filter; } throw new InvalidOperationException(); }
private bool optimize(List<IFilter> updates) { bool wasOptimized = false; foreach (IFilter filter in _filters) { FilterGroup innerGroup = filter as FilterGroup; if (innerGroup == null) { updates.Add(filter); } else { // make sure to optimize the child filter group first List<IFilter> filters = new List<IFilter>(); bool wereChildrenOptimized = innerGroup.optimize(filters); // if the code explicitly requests parenthesis, keep them in place bool requiresParenthesis = innerGroup.WrapInParentheses == true; // if a filter group only has one item, its conjunction is ignored bool hasUnusedConjunction = filters.Count == 1; // if the filter group has the same conjunction, then we can safely compact bool hasMatchingConjunction = innerGroup._conjunction == _conjunction; bool canOptimize = !requiresParenthesis && (hasUnusedConjunction || hasMatchingConjunction); if (canOptimize) { updates.AddRange(filters); wasOptimized = true; } else if (wereChildrenOptimized) { FilterGroup newGroup = new FilterGroup(innerGroup._conjunction, filters.ToArray()); if (requiresParenthesis) { newGroup.WrapInParentheses = true; } updates.Add(newGroup); wasOptimized = true; } else { updates.Add(innerGroup); } } } return wasOptimized; }
private void buildCondition(MatchResult result, ConditionalCase options) { MatchResult expressionResult = result.Matches[SqlGrammar.Condition.Filter]; IFilter innerFilter = buildOrFilter(expressionResult); MatchResult valueResult = result.Matches[SqlGrammar.Condition.Value]; IProjectionItem value = (IProjectionItem)buildArithmeticItem(valueResult); FilterGroup filterGroup = new FilterGroup(Conjunction.And, innerFilter); filterGroup.Optimize(); options.AddBranch(filterGroup, value); }
public void TestSelect_DisjunctionFilter() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); FilterGroup orGroup = new FilterGroup(Conjunction.Or); orGroup.AddFilter(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1))); orGroup.AddFilter(new NullFilter(table.Column("Column"))); builder.AddWhere(orGroup); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE 1 = 1 OR Table.Column IS NULL"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_WrappedFilters() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); FilterGroup group = new FilterGroup() { WrapInParentheses = true }; group.AddFilter(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1))); group.AddFilter(new LikeFilter(table.Column("Column"), new StringLiteral("%ABC"))); builder.AddWhere(group); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE (1 = 1 AND Table.Column LIKE '%ABC')"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
/// <summary> /// Initializes a new instance of a FilteredJoin. /// </summary> /// <param name="left">The left hand item in the join.</param> /// <param name="right">The right hand item in the join.</param> protected FilteredJoin(Join left, AliasedSource right) : base(left, right) { on = new FilterGroup(Conjunction.And); }
/// <summary> /// Visits a FilterGroup builder. /// </summary> /// <param name="item">The item to visit.</param> protected internal virtual void VisitFilterGroup(FilterGroup item) { }