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 TestDelete_AliasedTable_WithAs() { Table table = new Table("Table"); DeleteBuilder builder = new DeleteBuilder(table, "t"); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder, new CommandOptions() { AliasColumnSourcesUsingAs = true }); string expected = "DELETE FROM Table AS t"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public void TestDelete_AliasedTable() { Table table = new Table("Table"); DeleteBuilder builder = new DeleteBuilder(table, "t"); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "DELETE FROM Table t"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
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."); }
public void TestDelete_Where() { Table table = new Table("Table"); DeleteBuilder builder = new DeleteBuilder(table); builder.AddWhere(new EqualToFilter(builder.Table.Column("Column"), new NumericLiteral(1))); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "DELETE FROM Table WHERE Column = 1"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }
public static void LogCommand(ICommand command, StringBuilder logBuilder, int indentLevel = 0) { var formatter = new Formatter(); var commandText = formatter.GetCommandText(command); var indent = GetIndent(indentLevel); logBuilder.AppendLine(string.Format("{0} {1}", indent, command.GetType().FullName.ToUpper())); logBuilder.AppendLine(string.Format("{0} Command Text: {1}", indent, commandText)); if (command is SelectBuilder) { var selCommand = command as SelectBuilder; logBuilder.AppendLine(string.Format("{0} PROJECTION ", indent)); LogProjection(selCommand.Projection, logBuilder, indentLevel); logBuilder.AppendLine(string.Format("{0} FROM ", indent)); LogFrom(selCommand.From, logBuilder, indentLevel); } }
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 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_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_Newlines() { string commandText = @"SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Column = Table2.Column WHERE Column3 = '123'"; CommandBuilder commandBuilder = new CommandBuilder(); SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(select); string expected = "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Column = Table2.Column WHERE Column3 = '123'"; Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected."); }
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_AddFilter() { const string commandText = "SELECT * FROM Customer"; CommandBuilder commandBuilder = new CommandBuilder(); SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText); Column customerId = select.Sources["Customer"].Column("CustomerId"); customerId.Qualify = false; Placeholder parameter = new Placeholder("@customerId"); select.AddWhere(new EqualToFilter(customerId, parameter)); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(select); string expected = "SELECT * FROM Customer WHERE CustomerId = @customerId"; Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected."); }
public void TestSelect_ExtraWhitespace() { string commandText = " SELECT * FROM Customer "; CommandBuilder commandBuilder = new CommandBuilder(); SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(select); string expected = "SELECT * FROM Customer"; Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected."); }
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_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_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_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_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_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_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_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."); }
private void assertCanReproduce(string commandText, CommandBuilderOptions options = null) { CommandBuilder builder = new CommandBuilder(); ICommand command = builder.GetCommand(commandText, options); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(command); Assert.AreEqual(commandText, actual, "The command builder did not generate the original command text."); }
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_ComplexCommand() { string commandText = @"SELECT r.RouteId, r.RouteNumber, o.CustomerId, o.CustomerKey AS [Outlet#], o.Name AS CustomerName, vm.VendingMachineId, vm.AssetNumber AS [Equipment#], m.ModelType AS Model, rc.FillFrequency, rc.EffectiveDate AS SettlementDate, p.ProductLookupId, p.ProductSKU AS ProductCode, rcvc.FillLevel AS ProductCapacity, st.QuantityDelivered AS FillUnits FROM Company b INNER JOIN Route r ON b.CompanyId = r.CompanyId INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId INNER JOIN ( SELECT rc.RouteCardId, rcvc.ProductLookupId, SUM(rcvc.FillLevel) AS FillLevel FROM RouteSchedule rs INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate GROUP BY rc.RouteCardId, rcvc.ProductLookupId ) as rcvc ON rc.RouteCardId = rcvc.RouteCardId INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId INNER JOIN Customer o ON vm.CustomerId = o.CustomerId INNER JOIN ServiceTransaction svc ON (rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate) INNER JOIN SettlementTransactionSKU st ON (svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId) WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3) ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC"; CommandBuilder commandBuilder = new CommandBuilder(); SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText); select.AddWhere(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1))); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(select); string expected = "SELECT" + " r.RouteId," + " r.RouteNumber," + " o.CustomerId," + " o.CustomerKey AS [Outlet#]," + " o.Name AS CustomerName," + " vm.VendingMachineId," + " vm.AssetNumber AS [Equipment#]," + " m.ModelType AS Model," + " rc.FillFrequency," + " rc.EffectiveDate AS SettlementDate," + " p.ProductLookupId," + " p.ProductSKU AS ProductCode," + " rcvc.FillLevel AS ProductCapacity," + " st.QuantityDelivered AS FillUnits" + " FROM Company b" + " INNER JOIN Route r ON b.CompanyId = r.CompanyId" + " INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId" + " INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId" + " INNER JOIN (SELECT rc.RouteCardId, rcvc.ProductLookupId, SUM(rcvc.FillLevel) AS FillLevel FROM RouteSchedule rs INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate GROUP BY rc.RouteCardId, rcvc.ProductLookupId) rcvc ON rc.RouteCardId = rcvc.RouteCardId" + " INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId" + " INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId" + " INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId" + " INNER JOIN Customer o ON vm.CustomerId = o.CustomerId" + " INNER JOIN ServiceTransaction svc ON (rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate)" + " INNER JOIN SettlementTransactionSKU st ON (svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId)" + " WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3) AND 1 = 1" + " ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC"; Assert.AreEqual(expected, actual, "The SELECT statement was not reproduced as expected."); }
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."); }
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."); }
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."); }
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_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."); }