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."); }
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 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_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_ProjectionWithAlias() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column"), "c"); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column AS c FROM Table"; 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_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_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."); }
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 TestSelect_AliasTableUsingAs() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table"), "t"); builder.AddProjection(new AllColumns()); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true }); string expected = "SELECT * FROM Table AS t"; 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."); }
public void TestSelect_TopWithTies() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.Top = new Top(new NumericLiteral(5)) { WithTies = true }; builder.AddProjection(table.Column("Column")); builder.AddOrderBy(new OrderBy(table.Column("Column"))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT TOP 5 WITH TIES Table.Column FROM Table ORDER BY Table.Column"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_TopPercent() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.Top = new Top(new NumericLiteral(5)) { IsPercent = true }; builder.AddProjection(table.Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT TOP 5 PERCENT Table.Column FROM Table"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_RemoveSource() { var builder = new SelectBuilder(); var table1 = builder.AddTable(new Table("Table1")); var table2 = builder.AddTable(new Table("Table2")); builder.AddProjection(table1.Column("Column")); var table2Column = builder.AddProjection(table2.Column("Column")); builder.RemoveSource(table2); builder.RemoveProjection(table2Column); var formatter = new Formatter(); var commandText = formatter.GetCommandText(builder); var expected = "SELECT Table1.Column FROM Table1"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestSelect_ProjectTableQualifiedStar() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(new AllColumns(table)); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.* FROM Table"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
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_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."); }
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_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_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_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_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_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_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_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_OrderNullsFirst() { SelectBuilder builder = new SelectBuilder(); AliasedSource table = builder.AddTable(new Table("Table")); builder.AddProjection(table.Column("Column")); builder.AddOrderBy(new OrderBy(table.Column("Column"), nullPlacement: NullPlacement.First)); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Table.Column FROM Table ORDER BY Table.Column NULLS FIRST"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }