public void TestInsert_AliasedTable()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values, "t");
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "INSERT INTO Table t VALUES()";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
/// <summary>
/// Generates the text for a ValueList builder.
/// </summary>
/// <param name="item">The item to generate the text for.</param>
protected internal override void VisitValueList(ValueList item)
{
writer.Write("(");
if (item.Values.Any())
{
join(", ", item.Values);
}
writer.Write(")");
}
/// <summary>
/// Visits a ValueList builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitValueList(ValueList item)
{
}
private ICommand buildInsertStatement(MatchResult result)
{
MatchResult tableResult = result.Matches[SqlGrammar.InsertStatement.Table];
Table table = buildTable(tableResult);
IValueProvider valueProvider = null;
MatchResult valuesResult = result.Matches[SqlGrammar.InsertStatement.Values.Name];
if (valuesResult.IsMatch)
{
ValueList values = new ValueList();
MatchResult valueListResult = valuesResult.Matches[SqlGrammar.InsertStatement.Values.ValueList];
if (valueListResult.IsMatch)
{
buildValueList(valueListResult, values);
}
valueProvider = values;
}
MatchResult selectResult = result.Matches[SqlGrammar.InsertStatement.Select.Name];
if (selectResult.IsMatch)
{
MatchResult selectStatementResult = selectResult.Matches[SqlGrammar.InsertStatement.Select.SelectStatement];
ISelectBuilder selectBuilder = buildSelectStatement(selectStatementResult);
valueProvider = selectBuilder;
}
string alias = null;
MatchResult aliasExpressionResult = result.Matches[SqlGrammar.InsertStatement.AliasExpression.Name];
if (aliasExpressionResult.IsMatch)
{
MatchResult aliasResult = aliasExpressionResult.Matches[SqlGrammar.InsertStatement.AliasExpression.Alias];
alias = getToken(aliasResult);
}
InsertBuilder builder = new InsertBuilder(table, valueProvider, alias);
SourceCollection collection = new SourceCollection();
collection.AddSource(builder.Table.GetSourceName(), builder.Table);
scope.Push(collection);
MatchResult columnsResult = result.Matches[SqlGrammar.InsertStatement.Columns.Name];
if (columnsResult.IsMatch)
{
MatchResult columnListResult = columnsResult.Matches[SqlGrammar.InsertStatement.Columns.ColumnList];
buildColumnsList(columnListResult, builder);
}
scope.Pop();
return builder;
}
private Function buildFunctionCall(MatchResult result)
{
MatchResult functionNameResult = result.Matches[SqlGrammar.FunctionCall.FunctionName];
List<string> parts = new List<string>();
buildMultipartIdentifier(functionNameResult, parts);
Namespace qualifier = getNamespace(parts.Take(parts.Count - 1));
string functionName = parts[parts.Count - 1];
Function function = new Function(qualifier, functionName);
MatchResult argumentsResult = result.Matches[SqlGrammar.FunctionCall.Arguments];
if (argumentsResult.IsMatch)
{
ValueList arguments = new ValueList();
buildValueList(argumentsResult, arguments);
foreach (IProjectionItem value in arguments.Values)
{
function.AddArgument(value);
}
}
MatchResult windowResult = result.Matches[SqlGrammar.FunctionCall.Window.Name];
if (windowResult.IsMatch)
{
FunctionWindow window = new FunctionWindow();
MatchResult partitioning = windowResult.Matches[SqlGrammar.FunctionCall.Window.Partitioning.Name];
if (partitioning.IsMatch)
{
MatchResult valueListResult = partitioning.Matches[SqlGrammar.FunctionCall.Window.Partitioning.ValueList];
ValueList valueList = new ValueList();
buildValueList(valueListResult, valueList);
foreach (IProjectionItem value in valueList.Values)
{
window.AddPartition(value);
}
}
MatchResult ordering = windowResult.Matches[SqlGrammar.FunctionCall.Window.Ordering.Name];
if (ordering.IsMatch)
{
MatchResult orderByListResult = ordering.Matches[SqlGrammar.FunctionCall.Window.Ordering.OrderByList];
buildOrderByList(orderByListResult, window.OrderByList);
}
MatchResult framing = windowResult.Matches[SqlGrammar.FunctionCall.Window.Framing.Name];
if (framing.IsMatch)
{
MatchResult precedingOnlyFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.PrecedingFrame];
if (precedingOnlyFrameResult.IsMatch)
{
IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingOnlyFrameResult);
window.Frame = new PrecedingOnlyWindowFrame(precedingFrame);
}
MatchResult betweenFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.Name];
if (betweenFrameResult.IsMatch)
{
MatchResult precedingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.PrecedingFrame];
IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingFrameResult);
MatchResult followingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.FollowingFrame];
IFollowingFrame followingFrame = buildFollowingFrame(followingFrameResult);
window.Frame = new BetweenWindowFrame(precedingFrame, followingFrame);
}
MatchResult frameTypeResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.FrameType];
window.Frame.FrameType = buildFrameType(frameTypeResult);
}
function.FunctionWindow = window;
}
return function;
}
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 void buildValueList(MatchResult result, ValueList values)
{
MatchResult multiple = result.Matches[SqlGrammar.ValueList.Multiple.Name];
if (multiple.IsMatch)
{
MatchResult first = multiple.Matches[SqlGrammar.ValueList.Multiple.First];
IProjectionItem value = (IProjectionItem)buildArithmeticItem(first);
values.AddValue(value);
MatchResult remaining = multiple.Matches[SqlGrammar.ValueList.Multiple.Remaining];
buildValueList(remaining, values);
return;
}
MatchResult single = result.Matches[SqlGrammar.ValueList.Single];
if (single.IsMatch)
{
IProjectionItem value = (IProjectionItem)buildArithmeticItem(single);
values.AddValue(value);
return;
}
throw new NotImplementedException();
}
public void TestInsert_AliasedTable_WithAsKeyword()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values, "t");
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true });
string expected = "INSERT INTO Table AS t VALUES()";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_Any_ValueList()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
ValueList values = new ValueList(new NumericLiteral(1), new NumericLiteral(2), new NumericLiteral(3));
NotEqualToQuantifierFilter filter = new NotEqualToQuantifierFilter(table.Column("Column"), Quantifier.Any, values);
builder.AddWhere(filter);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column <> ANY (1, 2, 3)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestInsert_MultipleColumns()
{
Table table = new Table("Table");
ValueList values = new ValueList();
InsertBuilder builder = new InsertBuilder(table, values);
builder.AddColumn(builder.Table.Column("Column1"));
builder.AddColumn(builder.Table.Column("Column2"));
values.AddValue(new NumericLiteral(1));
values.AddValue(new NullLiteral());
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true });
string expected = "INSERT INTO Table (Column1, Column2) VALUES(1, NULL)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
/// <summary>
/// Visits a ValueList builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitValueList(ValueList item)
{
}