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.");
 }
Beispiel #6
0
        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.");
        }