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."); }