private void addJoinItem(SelectBuilder builder, Join join) { JoinStart start = join as JoinStart; if (start == null) { builder.AddJoin(join); return; } AliasedSource source = start.Source; Table table = source.Source as Table; if (table != null) { builder.AddTable(table, source.Alias); return; } ISelectBuilder select = source.Source as SelectBuilder; if (select != null) { builder.AddSelect(select, source.Alias); return; } Function functionCall = source.Source as Function; if (functionCall != null) { builder.AddFunction(functionCall, source.Alias); return; } throw new InvalidOperationException(); }
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."); }
public void TestSelect_ExistsFilter() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); SelectBuilder inner = new SelectBuilder(); inner.AddProjection(new NumericLiteral(1)); ExistsFilter filter = new ExistsFilter(inner); builder.AddWhere(filter); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE EXISTS(SELECT 1)"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_Modulus() { SelectBuilder builder = new SelectBuilder(); builder.AddProjection(new Modulus(new NumericLiteral(1), new NumericLiteral(1))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT (1 % 1)"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
private void buildGroupByList(MatchResult result, SelectBuilder builder) { MatchResult multiple = result.Matches[SqlGrammar.GroupByList.Multiple.Name]; if (multiple.IsMatch) { MatchResult firstResult = multiple.Matches[SqlGrammar.GroupByList.Multiple.First]; IGroupByItem first = (IGroupByItem)buildArithmeticItem(firstResult); builder.AddGroupBy(first); MatchResult remainingResult = multiple.Matches[SqlGrammar.GroupByList.Multiple.Remaining]; buildGroupByList(remainingResult, builder); return; } MatchResult single = result.Matches[SqlGrammar.GroupByList.Single]; if (single.IsMatch) { IGroupByItem item = (IGroupByItem)buildArithmeticItem(single); builder.AddGroupBy(item); return; } throw new InvalidOperationException(); }
public void TestSelect_FromSelect() { SelectBuilder builder = new SelectBuilder(); SelectBuilder inner = new SelectBuilder(); inner.AddProjection(new NumericLiteral(1), "Column"); builder.AddSelect(inner); builder.AddProjection(new Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Column FROM (SELECT 1 AS Column)"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
private void buildProjectionList(MatchResult result, SelectBuilder builder) { MatchResult multiple = result.Matches[SqlGrammar.ProjectionList.Multiple.Name]; if (multiple.IsMatch) { MatchResult first = multiple.Matches[SqlGrammar.ProjectionList.Multiple.First]; buildProjectionItem(first, builder); MatchResult remaining = multiple.Matches[SqlGrammar.ProjectionList.Multiple.Remaining]; buildProjectionList(remaining, builder); return; } MatchResult single = result.Matches[SqlGrammar.ProjectionList.Single]; if (single.IsMatch) { buildProjectionItem(single, builder); return; } throw new InvalidOperationException(); }
private Top buildTop(MatchResult result, SelectBuilder builder) { MatchResult expressionResult = result.Matches[SqlGrammar.SelectSpecification.Top.Expression]; IProjectionItem expression = (IProjectionItem)buildArithmeticItem(expressionResult); Top top = new Top(expression); MatchResult percentResult = result.Matches[SqlGrammar.SelectSpecification.Top.PercentKeyword]; top.IsPercent = percentResult.IsMatch; MatchResult withTiesResult = result.Matches[SqlGrammar.SelectSpecification.Top.WithTiesKeyword]; top.WithTies = withTiesResult.IsMatch; return top; }
public void TestSelect_InFilter_Negated() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); builder.AddWhere(new InFilter(table.Column("Column"), new ValueList(new NumericLiteral(1), new NumericLiteral(2), new NumericLiteral(3))) { Not = true }); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE Table.Column NOT IN (1, 2, 3)"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_MultipleProjections() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(new NumericLiteral(1)); builder.AddProjection(new StringLiteral("Hello")); builder.AddProjection(new NullLiteral()); builder.AddProjection(new Function("IsNull", table.Column("Column"), new NumericLiteral(123))); builder.AddProjection(table.Column("Column")); SelectBuilder inner = new SelectBuilder(); inner.AddProjection(new NumericLiteral(1234)); builder.AddProjection(inner); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT 1, 'Hello', NULL, IsNull(Table.Column, 123), Table.Column, (SELECT 1234) FROM Table"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_GroupBy() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(new Function("COUNT", new NumericLiteral(1))); builder.AddGroupBy(table.Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT COUNT(1) FROM Table GROUP BY Table.Column"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_InFilter_FunctionSource() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); builder.AddWhere(new InFilter(table.Column("Column"), new Function("GetData"))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE Table.Column IN GetData()"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_FunctionWithStartFraming() { SelectBuilder builder = new SelectBuilder(); AliasedSource sale = builder.AddTable(new Table("sale")); AliasedProjection productId = builder.AddProjection(sale.Column("prod_id")); AliasedProjection monthNumber = builder.AddProjection(sale.Column("month_num")); AliasedProjection sales = builder.AddProjection(sale.Column("sales")); Function function = new Function("SUM", sales.ProjectionItem); FunctionWindow window = new FunctionWindow(); window.AddPartition(productId); window.AddOrderBy(new OrderBy(monthNumber)); window.Frame = new PrecedingOnlyWindowFrame(new PrecedingBoundFrame(12)); function.FunctionWindow = window; builder.AddProjection(function); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT sale.prod_id, sale.month_num, sale.sales, SUM(sale.sales) OVER (PARTITION BY sale.prod_id ORDER BY sale.month_num ROWS 12 PRECEDING) FROM sale"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_FunctionWithPartitioning() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Employee")); AliasedProjection employeeType = builder.AddProjection(table.Column("Type"), "Type"); Function function = new Function("COUNT", new NumericLiteral(1)); FunctionWindow window = new FunctionWindow(); window.AddPartition(employeeType); function.FunctionWindow = window; builder.AddProjection(function, "Count"); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT Employee.Type AS Type, COUNT(1) OVER (PARTITION BY Employee.Type) AS Count FROM Employee"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_FunctionWithOrderingWindow() { SelectBuilder innerBuilder = new SelectBuilder(); AliasedSource table = innerBuilder.AddTable(new Table("Table")); Column column1 = table.Column("Column1"); Column column2 = table.Column("Column2"); Column column3 = table.Column("Column3"); innerBuilder.AddProjection(column1, "c1"); Function function = new Function("ROW_NUMBER"); FunctionWindow window = new FunctionWindow(); window.AddOrderBy(new OrderBy(column2)); window.AddOrderBy(new OrderBy(column3)); function.FunctionWindow = window; innerBuilder.AddProjection(function, "rn"); SelectBuilder builder = new SelectBuilder(); AliasedSource inner = builder.AddSelect(innerBuilder, "inner"); builder.AddProjection(inner.Column("c1")); builder.AddWhere(new BetweenFilter(inner.Column("rn"), new NumericLiteral(11), new NumericLiteral(20))); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT inner.c1 FROM (SELECT Table.Column1 AS c1, ROW_NUMBER() OVER (ORDER BY Table.Column2, Table.Column3) AS rn FROM Table) inner WHERE inner.rn BETWEEN 11 AND 20"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_MultipleJoinItems() { SelectBuilder builder = new SelectBuilder(); AliasedSource table1 = builder.AddTable(new Table("Table1")); AliasedSource table2 = builder.AddTable(new Table("Table2")); builder.AddProjection(table1.Column("Column")); builder.AddProjection(table2.Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table1.Column, Table2.Column FROM Table1, Table2"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_LeftOuterJoin() { SelectBuilder builder = new SelectBuilder(); Join join = Join.From(new Table("Table1")) .LeftOuterJoin(new Table("Table2")) .On(j => new EqualToFilter(j.Sources["Table1"].Column("Column"), j.Sources["Table2"].Column("Column"))); builder.AddJoin(join); builder.AddProjection(new AllColumns()); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Column = Table2.Column"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_MultipleOrderByItems() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column1")); builder.AddProjection(table.Column("Column2")); builder.AddOrderBy(new OrderBy(table.Column("Column1"))); builder.AddOrderBy(new OrderBy(table.Column("Column2"))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column1, Table.Column2 FROM Table ORDER BY Table.Column1, Table.Column2"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_LessThanEqualTo() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); builder.AddWhere(new LessThanEqualToFilter(table.Column("Column"), new NumericLiteral(1))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE Table.Column <= 1"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
private void buildProjectionItem(MatchResult result, SelectBuilder builder) { MatchResult expression = result.Matches[SqlGrammar.ProjectionItem.Expression.Name]; if (expression.IsMatch) { MatchResult itemResult = expression.Matches[SqlGrammar.ProjectionItem.Expression.Item]; IProjectionItem item = (IProjectionItem)buildArithmeticItem(itemResult); string alias = null; MatchResult aliasExpression = expression.Matches[SqlGrammar.ProjectionItem.Expression.AliasExpression.Name]; if (aliasExpression.IsMatch) { MatchResult aliasResult = aliasExpression.Matches[SqlGrammar.ProjectionItem.Expression.AliasExpression.Alias]; alias = getToken(aliasResult); } builder.AddProjection(item, alias); return; } MatchResult star = result.Matches[SqlGrammar.ProjectionItem.Star.Name]; if (star.IsMatch) { AliasedSource source = null; MatchResult qualifier = star.Matches[SqlGrammar.ProjectionItem.Star.Qualifier.Name]; if (qualifier.IsMatch) { MatchResult columnSource = qualifier.Matches[SqlGrammar.ProjectionItem.Star.Qualifier.ColumnSource]; List<string> parts = new List<string>(); buildMultipartIdentifier(columnSource, parts); string sourceName = parts[parts.Count - 1]; source = scope.GetSource(sourceName); } AllColumns all = new AllColumns(source); builder.AddProjection(all); return; } throw new InvalidOperationException(); }
public void TestSelect_LikeFilter_Negated() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); builder.AddWhere(new LikeFilter(table.Column("Column"), new StringLiteral("%Bob%")) { Not = true }); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table WHERE Table.Column NOT LIKE '%Bob%'"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
private ISelectBuilder buildSelectSpecification(MatchResult result) { SelectBuilder builder = new SelectBuilder(); MatchResult distinctQualifier = result.Matches[SqlGrammar.SelectSpecification.DistinctQualifier]; if (distinctQualifier.IsMatch) { builder.Distinct = buildDistinctQualifier(distinctQualifier); } MatchResult top = result.Matches[SqlGrammar.SelectSpecification.Top.Name]; if (top.IsMatch) { builder.Top = buildTop(top, builder); } MatchResult from = result.Matches[SqlGrammar.SelectSpecification.From.Name]; if (from.IsMatch) { MatchResult fromList = from.Matches[SqlGrammar.SelectSpecification.From.FromList]; buildFromList(fromList, builder); } scope.Push(builder.Sources); MatchResult projectionList = result.Matches[SqlGrammar.SelectSpecification.ProjectionList]; buildProjectionList(projectionList, builder); MatchResult where = result.Matches[SqlGrammar.SelectSpecification.Where.Name]; if (where.IsMatch) { MatchResult filterList = where.Matches[SqlGrammar.SelectSpecification.Where.FilterList]; IFilter innerFilter = buildOrFilter(filterList); builder.WhereFilterGroup.AddFilter(innerFilter); builder.WhereFilterGroup.Optimize(); } MatchResult groupBy = result.Matches[SqlGrammar.SelectSpecification.GroupBy.Name]; if (groupBy.IsMatch) { MatchResult groupByList = groupBy.Matches[SqlGrammar.SelectSpecification.GroupBy.GroupByList]; buildGroupByList(groupByList, builder); } MatchResult having = result.Matches[SqlGrammar.SelectSpecification.Having.Name]; if (having.IsMatch) { MatchResult filterList = having.Matches[SqlGrammar.SelectSpecification.Having.FilterList]; IFilter innerFilter = buildOrFilter(filterList); builder.HavingFilterGroup.AddFilter(innerFilter); builder.HavingFilterGroup.Optimize(); } scope.Pop(); return builder; }
/// <summary> /// Visits a SelectBuilder builder. /// </summary> /// <param name="item">The item to visit.</param> protected internal virtual void VisitSelect(SelectBuilder item) { }
private void buildFromList(MatchResult result, SelectBuilder builder) { MatchResult multiple = result.Matches[SqlGrammar.FromList.Multiple.Name]; if (multiple.IsMatch) { MatchResult first = multiple.Matches[SqlGrammar.FromList.Multiple.First]; Join join = buildJoin(first, false); addJoinItem(builder, join); MatchResult remaining = multiple.Matches[SqlGrammar.FromList.Multiple.Remaining]; buildFromList(remaining, builder); return; } MatchResult single = result.Matches[SqlGrammar.FromList.Single]; if (single.IsMatch) { Join join = buildJoin(single, false); addJoinItem(builder, join); return; } throw new InvalidOperationException(); }
private void visitSelect(SelectBuilder item) { bool needsParentheses = level > 0; if (needsParentheses) { writer.Write("("); } writer.Write("SELECT "); if (item.Distinct != DistinctQualifier.Default) { DistinctQualifierConverter converter = new DistinctQualifierConverter(); writer.Write(converter.ToString(item.Distinct)); writer.Write(" "); } if (item.Top != null) { IVisitableBuilder top = item.Top; top.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference)); writer.Write(" "); } forValueContext(ValueReferenceType.Declaration).join(", ", item.Projection); if (item.From.Any()) { writer.Write(" FROM "); forSourceContext(SourceReferenceType.Declaration).join(", ", item.From); } if (item.WhereFilterGroup.HasFilters) { writer.Write(" WHERE "); IFilter where = item.WhereFilterGroup; where.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference)); } if (item.GroupBy.Any()) { writer.Write(" GROUP BY "); forValueContext(ValueReferenceType.Reference).join(", ", item.GroupBy); } if (item.HavingFilterGroup.HasFilters) { writer.Write(" HAVING "); IFilter having = item.HavingFilterGroup; having.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference)); } if (item.OrderBy.Any()) { writer.Write(" ORDER BY "); forValueContext(ValueReferenceType.Alias).join(", ", item.OrderBy); } if (needsParentheses) { writer.Write(")"); } }
public void TestSelect_MatchCase_Else() { SelectBuilder builder = new SelectBuilder(); AliasedSource source = builder.AddTable(new Table("Table")); Column column = source.Column("Column"); MatchCase options = new MatchCase(column); options.AddBranch(new StringLiteral("Admin"), new StringLiteral("Administrator")); options.Default = new StringLiteral("User"); builder.AddProjection(options); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT CASE Table.Column WHEN 'Admin' THEN 'Administrator' ELSE 'User' END FROM Table"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_FromFunction() { SelectBuilder builder = new SelectBuilder(); builder.AddFunction(new Function("GetData"), "F"); builder.AddProjection(builder.Sources["F"].Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT F.Column FROM GetData() F"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_MatchCase_MultipleCases() { SelectBuilder builder = new SelectBuilder(); AliasedSource source = builder.AddTable(new Table("Table")); Column column = source.Column("Column"); MatchCase options = new MatchCase(column); options.AddBranch(new NumericLiteral(0), new StringLiteral("Sunday")); options.AddBranch(new NumericLiteral(1), new StringLiteral("Monday")); options.AddBranch(new NumericLiteral(2), new StringLiteral("Tuesday")); options.AddBranch(new NumericLiteral(3), new StringLiteral("Wednesday")); options.AddBranch(new NumericLiteral(4), new StringLiteral("Thursday")); options.AddBranch(new NumericLiteral(5), new StringLiteral("Friday")); options.AddBranch(new NumericLiteral(6), new StringLiteral("Saturday")); builder.AddProjection(options); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT CASE Table.Column WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday' END FROM Table"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_Minus() { SelectBuilder builder = new SelectBuilder(); builder.AddProjection(new NumericLiteral(1)); Minus union = new Minus(builder, builder); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(union); string expected = "SELECT 1 MINUS SELECT 1"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
/// <summary> /// Generates the text for a Select builder. /// </summary> /// <param name="item">The Select builder to generate the text for.</param> protected internal override void VisitSelect(SelectBuilder item) { forCommandType(CommandType.Select).visitSelect(item); }
public void TestSelect_Distinct() { SelectBuilder builder = new SelectBuilder(); builder.Distinct = DistinctQualifier.Distinct; builder.AddProjection(new NumericLiteral(1)); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT DISTINCT 1"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }