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