/// <summary>
/// Sets the condition on which the source is joined with the other tables.
/// </summary>
/// <param name="filterGenerator">A function that creates the join.</param>
/// <returns>The current join.</returns>
public Join On(Func <Join, IFilter> filterGenerator)
{
if (filterGenerator == null)
{
throw new ArgumentNullException("filterGenerator");
}
on.Clear();
IFilter filter = filterGenerator(this);
on.AddFilter(filter);
return(this);
}
/// <summary>
/// Adds the filter to the where clause.
/// </summary>
/// <param name="filter">The filter to add.</param>
public void AddWhere(IFilter filter)
{
_where.AddFilter(filter);
}
/// <summary>
/// Adds the filter to the having clause.
/// </summary>
/// <param name="filter">The filter to add.</param>
public void AddHaving(IFilter filter)
{
_having.AddFilter(filter);
}
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();
}
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.");
}
