This is the twenty third part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Here go the tests for query parser:

using System;
using FluentAssertions;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Expressions.CellExpressions;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Filtering;
using QueryLogic.Grouping;
using QueryLogic.Joins.Implementation;
using QueryLogic.Ordering;
using QueryLogic.Predicates.Complex;
using QueryLogic.Predicates.Simple;
using QueryLogic.RelationProviding;
using QueryLogic.Selecting;
using QueryLogic.Selectors;
using QueryLogic.Transformers.CellTransformers;
using QueryLogic.Transformers.RowTransformers;
using QueryParser.Parsing;

namespace QueryParserTest
{
    [TestFixture]
    internal class ParserTests
    {
        [TestFixtureSetUp]
        public void Initialize()
        {
            // ReSharper disable once UnusedVariable
            var parser = new Parser();
        }

        private static void PerformTestWithAssert(string query, Select expectedModel, string[] expectedMessage)
        {
            // Arrange
            var parser = new Parser();

            // Act
            Tuple<Select, string[]> actual = parser.Parse(query);
            Select actualModel = actual.Item1;
            string[] actualMessage = actual.Item2;

            // Assert
            Console.WriteLine(string.Join("\n", actualMessage));
            actualMessage.ShouldBeEquivalentTo(expectedMessage);
            actualModel.Should().Be(expectedModel);
        }

        [Test]
        public void Parse_CrossJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t CROSS JOIN t.table2 AS t2";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var crossJoin = new CrossJoin(firstFrom, secondFrom);
            var expectedSelect = new Select(crossJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_CrossJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t CROSS JOIN table2 AS t2";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var crossJoin = new CrossJoin(firstFrom, secondFrom);
            var expectedSelect = new Select(crossJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FromWithOneNestedTable()
        {
            // Arrange
            const string query = @"SELECT * FROM level.table AS t";

            var selector = new ChainedSelector(new TopLevelSelector("level"), new NodeSelector("table"));
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FromWithOneTableOnTopLevel()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FullJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t FULL JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new FullOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FullJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t FULL JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new FullOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FullOuterJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t FULL OUTER JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new FullOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_FullOuterJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t FULL OUTER JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new FullOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_GroupBy()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t GROUP BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var groupBy = new GroupBy(from, new[]
            {
                new GroupingSet(new[]
                {
                    new ColumnHeader("t", "id"),
                    new ColumnHeader("t", "name")
                })
            });
            var expectedSelect = new Select(groupBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_GroupByWithOrderBy()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t GROUP BY t.id ORDER BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            });
            var groupBy = new GroupBy(from, new[]
            {
                new GroupingSet(new[]
                {
                    new ColumnHeader("t", "id")
                })
            });
            var expectedSelect = new Select(groupBy, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_GroupByWithOrderByWithWhere()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t WHERE t.id = '5' GROUP BY t.id ORDER BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            });
            var groupBy = new GroupBy(where, new[]
            {
                new GroupingSet(new[]
                {
                    new ColumnHeader("t", "id")
                })
            });
            var expectedSelect = new Select(groupBy, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_GroupByWithWhere()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t WHERE t.id <> '5' GROUP BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new NotEqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var groupBy = new GroupBy(where, new[]
            {
                new GroupingSet(new[]
                {
                    new ColumnHeader("t", "id"),
                    new ColumnHeader("t", "name")
                })
            });
            var expectedSelect = new Select(groupBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_IncorrectQuery_ShouldReturnMessage()
        {
            // Arrange
            const string query = @"SELECT * FROM table";

            var expectedMessages = new[]
            {
                "Syntax error - unexpected EOF "
            };

            // Act
            // Assert
            PerformTestWithAssert(query, null, expectedMessages);
        }

        [Test]
        public void Parse_InnerJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t INNER JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var innerJoin = new InnerJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(innerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_InnerJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t INNER JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var innerJoin = new InnerJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(innerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }


        [Test]
        public void Parse_LeftJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t LEFT JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new LeftOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_LeftJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t LEFT JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new LeftOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_LeftOuterJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t LEFT OUTER JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new LeftOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_LeftOuterJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t LEFT OUTER JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new LeftOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_NaturalJoinWithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t NATURAL JOIN t.table2 AS t2";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var naturalJoin = new NaturalJoin(firstFrom, secondFrom);
            var expectedSelect = new Select(naturalJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_NaturalJoinWithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t NATURAL JOIN table2 AS t2";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var naturalJoin = new NaturalJoin(firstFrom, secondFrom);
            var expectedSelect = new Select(naturalJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_NaturalJoin_MultipleOccurences()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t NATURAL JOIN table2 AS t2 NATURAL JOIN table3 AS t3";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var thirdSelector = new TopLevelSelector("table3");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var thirdFrom = new From(thirdSelector, "t3");
            var firstJoin = new NaturalJoin(firstFrom, secondFrom);
            var secondJoin = new NaturalJoin(firstJoin, thirdFrom);
            var expectedSelect = new Select(secondJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderBy()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t ORDER BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            });
            var expectedSelect = new Select(from, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderByWithDescendingColumn()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t ORDER BY t.id DESC";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id"), OrderDirection.Descending)
            });
            var expectedSelect = new Select(from, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderByWithFetchCount()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t ORDER BY t.id, t.name FETCH 3";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            }, null, 3);
            var expectedSelect = new Select(from, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderByWithSkipCount()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t ORDER BY t.id, t.name SKIP 3";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            }, 3);
            var expectedSelect = new Select(from, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderByWithSkipCountAndFetchCount()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t ORDER BY t.id, t.name SKIP 2 FETCH 3";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            }, 2, 3);
            var expectedSelect = new Select(from, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_OrderByWithWhere()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t WHERE t.id = '5' ORDER BY t.id, t.name";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("t", "id")),
                new ColumnOrdering(new ColumnHeader("t", "name"))
            });
            var expectedSelect = new Select(where, orderBy);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }


        [Test]
        public void Parse_RightJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t RIGHT JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new RightOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_RightJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t RIGHT JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new RightOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_RightOuterJoin_WithUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t RIGHT OUTER JOIN t.table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new ChainedSelector(new TopLevelSelector("table"), new NodeSelector("table2"));
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new RightOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_RightOuterJoin_WithoutUsingAliasInSelector()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t RIGHT OUTER JOIN table2 AS t2 ON t.id = t2.id";

            var firstSelector = new TopLevelSelector("table");
            var secondSelector = new TopLevelSelector("table2");
            var firstFrom = new From(firstSelector, "t");
            var secondFrom = new From(secondSelector, "t2");
            var outerJoin = new RightOuterJoin(firstFrom, secondFrom,
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new GetCellRowExpression(new ColumnHeader("t2", "id"))));
            var expectedSelect = new Select(outerJoin);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_ColumnAggregatesWithAliases()
        {
            // Arrange
            const string query = @"SELECT MIN(t.id) AS minimum, MAX(t.id) AS maximum FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddAggregate(
                new MinimumAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", "minimum")), "minimum"));
            expectedSelect.AddAggregate(
                new MaximumAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", "maximum")), "maximum"));
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[0]));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_ColumnAggregatesWithInnerFunctions()
        {
            // Arrange
            const string query = @"SELECT MIN(LEN(TRIM(t.id))) FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            var columnHeader = new ColumnHeader("t", "id");
            expectedSelect.AddAggregate(
                new MinimumAggregate(
                    new ChainedCellExpression(new GetLengthCellExpression(columnHeader),
                        new ChainedCellExpression(new GetTrimmedCellExpression(columnHeader),
                            new GetOriginalCellCellExpression(columnHeader)))
                    , new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[0]));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_ColumnAggregatesWithInnerFunctionsAndOuterFunctions()
        {
            // Arrange
            const string query = @"SELECT LEN(MIN(LEN(TRIM(t.id)))) FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            var columnHeader = new ColumnHeader("t", "id");
            expectedSelect.AddAggregate(
                new MinimumAggregate(
                    new ChainedCellExpression(new GetLengthCellExpression(columnHeader),
                        new ChainedCellExpression(new GetTrimmedCellExpression(columnHeader),
                            new GetOriginalCellCellExpression(columnHeader)))
                    ,
                    new ChainedCellExpression(new GetLengthCellExpression(new ColumnHeader("", "")),
                        new GetOriginalCellCellExpression(new ColumnHeader("", "")))));
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[0]));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }


        [Test]
        public void Parse_Select_ColumnAggregatesWithoutAliases()
        {
            // Arrange
            const string query = @"SELECT MIN(t.id), MAX(t.id), SUM(t.id), AVG(t.id), COUNT(t.id) FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddAggregate(
                new MinimumAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddAggregate(
                new MaximumAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddAggregate(new SumAggregate(
                new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddAggregate(
                new AverageAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddAggregate(
                new CountColumnAggregate(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")),
                    new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[0]));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_CountAllAggregate()
        {
            // Arrange
            const string query = @"SELECT COUNT(*) FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddAggregate(new CountAggregate(new GetOriginalCellCellExpression(new ColumnHeader("", ""))));
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[0]));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_ParseFunctionWithManyArguments()
        {
            // Arrange
            const string query = @"SELECT SUBSTR(t.id, '2', '5'), CATENATE(t.id, 'asd') FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            var columnHeader = new ColumnHeader("t", "id");
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new[]
            {
                new CellTransformer(
                    new ChainedCellExpression(new GetSubstringCellExpression(columnHeader, 2, 5),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new CatenateStringCellExpression(columnHeader, "asd"),
                        new GetOriginalCellCellExpression(columnHeader)), "")
            }));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_ParseFunctions()
        {
            // Arrange
            const string query =
                @"SELECT LEN(TRIM(t.id)), YEAR(t.id), MONTH(t.id), DAY(t.id), HOUR(t.id), MINUTE(t.id), SECOND(t.id) FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            var columnHeader = new ColumnHeader("t", "id");
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new[]
            {
                new CellTransformer(new ChainedCellExpression(new GetLengthCellExpression(columnHeader),
                    new ChainedCellExpression(new GetTrimmedCellExpression(columnHeader),
                        new GetOriginalCellCellExpression(columnHeader))), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Year),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Month),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Day),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Hour),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Minute),
                        new GetOriginalCellCellExpression(columnHeader)), ""),
                new CellTransformer(
                    new ChainedCellExpression(new GetDatePartCellExpression(columnHeader, DatePart.Second),
                        new GetOriginalCellCellExpression(columnHeader)), "")
            }));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_SelectColumnsWithAlias()
        {
            // Arrange
            const string query = @"SELECT t.id AS ajdik, t.name as nejm FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[]
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")), "ajdik"),
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("t", "name")), "nejm")
            }));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_SelectManyColumns()
        {
            // Arrange
            const string query = @"SELECT t.id, t.name FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[]
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("t", "id"))),
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("t", "name")))
            }));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Select_SelectSingleColumn()
        {
            // Arrange
            const string query = @"SELECT t.id FROM table AS t";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var expectedSelect = new Select(from);
            expectedSelect.AddRowTransformer(new CellsRowTransformer(new ICellTransformer[]
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("t", "id")))
            }));

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_AndOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id = '5' AND t.name = 'JAN'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate =
                new AndPredicate(
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                        new ConstantRowExpression("5")),
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("JAN")));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_AndOrOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id = '5' AND t.name = 'JAN' OR t.name = 'GERWAZY' ";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new OrPredicate(
                new AndPredicate(
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                        new ConstantRowExpression("5")),
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("JAN"))),
                new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                    new ConstantRowExpression("GERWAZY")));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_EqualOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id = '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_GreaterEqualOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id >= '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new GreaterEqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_GreaterThanOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id > '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new GreaterThanPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_IsNullOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id IS NULL";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new IsNullPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_LessEqualOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id <= '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new LessEqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_LessThanOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id < '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new LessThanPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_LikeOperatorWithEscapeCharacter()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id LIKE 'abcd_%[a-z][0-9][^0-9]' ESCAPE '&'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new LikePredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("abcd_%[a-z][0-9][^0-9]"), '&');
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_LikeOperatorWithoutEscapeCharacter()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id LIKE 'abcd_%[a-z][^0-9]'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new LikePredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("abcd_%[a-z][^0-9]"), '\0');
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_NotEqualOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id <> '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new NotEqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5"));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_NotOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where NOT (t.id >= '5')";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate =
                new NotPredicate(new GreaterEqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                    new ConstantRowExpression("5")));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_OrAndOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.name = 'GERWAZY' OR t.name = 'JAN' AND t.id = '5'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new OrPredicate(new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                new ConstantRowExpression("GERWAZY")),
                new AndPredicate(
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("JAN")),
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                        new ConstantRowExpression("5"))));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_OrOperator()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id = '5' OR t.name = 'JAN'";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate =
                new OrPredicate(
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                        new ConstantRowExpression("5")),
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("JAN")));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }

        [Test]
        public void Parse_Where_Parenthesis()
        {
            // Arrange
            const string query = @"SELECT * FROM table AS t where t.id = '5' AND (t.name = 'JAN' OR t.name = 'GERWAZY')";

            var selector = new TopLevelSelector("table");
            var from = new From(selector, "t");
            var predicate = new AndPredicate(new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "id")),
                new ConstantRowExpression("5")),
                new OrPredicate(
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("JAN")),
                    new EqualPredicate(new GetCellRowExpression(new ColumnHeader("t", "name")),
                        new ConstantRowExpression("GERWAZY"))));
            var where = new Where(from, predicate);
            var expectedSelect = new Select(where);
            expectedSelect.AddRowTransformer(new IdentityRowTransformer());

            var expectedMessages = new string[0];

            // Act
            // Assert
            PerformTestWithAssert(query, expectedSelect, expectedMessages);
        }
    }
}

If you followed this series then this whole code should be very easy.

Summary

We now have engine for parsing XML documents, transforming them, and executing queries. We can easily transform rows, perform set operations, implement functions. There are things in SQL-92 which we miss, e.g., nested queries. However, we can easily extend our language and engine to handle them. For now the job is done but if you want you can easily extend the SQLxD database even more.

This is the twenty second part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

For parsing queries we use IronPython. Let’s begin with C# code for executing PLY engine:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using IronPython.Hosting;
using IronPython.Modules;
using Microsoft.Scripting.Hosting;
using QueryLogic.Filtering;
using QueryLogic.Joins.Abstraction;
using QueryLogic.RelationProviding;
using QueryLogic.Selecting;
using QueryLogic.Selectors;

namespace QueryParser.Parsing
{
    public class Parser
    {
        private static dynamic _ipy;
        private Dictionary<string, ISelector> _aliasedSelectors;

        public Parser()
        {
            _ipy = _ipy ?? CreateEngine();
        }

        private dynamic CreateEngine()
        {
            ScriptRuntimeSetup setup = Python.CreateRuntimeSetup(GetRuntimeOptions());
            var pyRuntime = new ScriptRuntime(setup);
            ScriptEngine engineInstance = Python.GetEngine(pyRuntime);

            AddPythonLibrariesToSysMetaPath(engineInstance);

            dynamic ipy = pyRuntime.UseFile(@"Parsing\Parser.py");
            ipy.initialize(GetPlyPath());

            return ipy;
        }

        private void AddPythonLibrariesToSysMetaPath(ScriptEngine engineInstance)
        {
            Assembly asm = GetType().Assembly;
            IEnumerable<string> resQuery =
                from name in asm.GetManifestResourceNames()
                where name.ToLowerInvariant().EndsWith("python27.zip")
                select name;
            string resName = resQuery.Single();
            var importer = new ResourceMetaPathImporter(asm, resName);
            dynamic sys = engineInstance.GetSysModule();
            sys.meta_path.append(importer);
            sys.path.append(importer);
        }

        private static Dictionary<string, object> GetRuntimeOptions()
        {
            var options = new Dictionary<string, object>();
            options["Debug"] = false;
            return options;
        }

        private static string GetPlyPath()
        {
            return Path.Combine(Environment.CurrentDirectory, "Lib", "ply");
        }

        public Tuple<Select, string[]> Parse(string content)
        {
            Tuple<Select, string[]> result;
            try
            {
                result = _ipy.parse(content);
            }
            catch (Exception e)
            {
                return Tuple.Create<Select, string[]>(null, new[]
                {
                    string.Format("{0}\n{1}\n{2}",e.Message, e.InnerException, e.StackTrace)
                });
            }
            FixSelectors(result.Item1);
            result = Tuple.Create(result.Item1, result.Item2);

            return result;
        }

        private void FixSelectors(Select result)
        {
            if (result == null)
            {
                return;
            }

            _aliasedSelectors = new Dictionary<string, ISelector>();
            IRelationProvider sourceProvider = result.GroupBy.SourceRelationProvider;
            FixSelectorInSourceRelationProvider(sourceProvider);
        }

        private void FixSelectorInSourceRelationProvider(IRelationProvider sourceRelationProvider)
        {
            if (sourceRelationProvider is From)
            {
                FixSelectorInFrom(sourceRelationProvider as From);
            }
            else if (sourceRelationProvider is IJoin)
            {
                FixSelectorInJoin(sourceRelationProvider as IJoin);
            }
            else if (sourceRelationProvider is Where)
            {
                FixSelectorInWhere(sourceRelationProvider as Where);
            }
        }

        private void FixSelectorInWhere(Where @where)
        {
            FixSelectorInSourceRelationProvider(@where.SourceRelation);
        }

        private void FixSelectorInJoin(IJoin @join)
        {
            FixSelectorInSourceRelationProvider(@join.FirstRelation);
            FixSelectorInSourceRelationProvider(@join.SecondRelation);
        }

        private void FixSelectorInFrom(From sourceProvider)
        {
            ISelector selector = sourceProvider.Selector;
            if (selector is NodeSelector)
            {
                sourceProvider.Selector = GetRealSelectorForNodeSelector(selector);
            }
            else if (selector is ChainedSelector)
            {
                sourceProvider.Selector = FixChainedSelector(selector as ChainedSelector);
            }

            _aliasedSelectors.Add(sourceProvider.Alias, sourceProvider.Selector);
        }

        private ChainedSelector FixChainedSelector(ChainedSelector chainedSelector)
        {
            return new ChainedSelector(GetRealSelectorForNodeSelector(chainedSelector.First), chainedSelector.Second);
        }

        private ISelector GetRealSelectorForNodeSelector(ISelector selector)
        {
            var nodeSelector = selector as NodeSelector;
            if (nodeSelector == null)
            {
                return selector;
            }

            ISelector aliasedSelector;
            _aliasedSelectors.TryGetValue(nodeSelector.Name, out aliasedSelector);

            return aliasedSelector ?? new TopLevelSelector(nodeSelector.Name);
        }
    }
}

And now goes the parser:

# -*- coding: utf-8 -*- 

def makeCaseInsensitive(s):
    return ''.join(sum(map(lambda c: ["[", c.lower(), c.upper(), "]"], s), []))

keywords = {
   'SELECT' : 'SELECT',
   'DISTINCT' : 'DISTINCT',
   'ALL' : 'ALL',
   'AS' : 'AS',
   'FROM' : 'FROM',
   'CROSS' : 'CROSS',
   'JOIN' : 'JOIN',
   'NATURAL' : 'NATURAL',
   'INNER' : 'INNER',
   'LEFT' : 'LEFT',
   'RIGHT' : 'RIGHT',
   'FULL' : 'FULL',
   'OUTER' : 'OUTER',
   'OR' : 'OR',
   'AND' : 'AND',
   'NOT' : 'NOT',
   'LIKE' : 'LIKE',
   'ESCAPE' : 'ESCAPE',
   'ON' : 'ON',
   'COUNT' : 'COUNT',
   'AVG' : 'AVG',
   'MAX' : 'MAX',
   'MIN' : 'MIN',
   'SUM' : 'SUM',
   'WHERE' : 'WHERE',
   'IS' : 'IS',
   'NULL' : 'NULL',
   'GROUP' : 'GROUP',
   'ORDER' : 'ORDER',
   'BY' : 'BY',
   'DESC' : 'DESC',
   'SKIP' : 'SKIP',
   'FETCH' : 'FETCH',
   'LEN' : 'LEN',
   'TRIM' : 'TRIM',
   'SUBSTR' : 'SUBSTR',
   'YEAR' : 'YEAR',
   'MONTH' : 'MONTH',
   'DAY' : 'DAY',
   'HOUR' : 'HOUR',
   'MINUTE' : 'MINUTE',
   'SECOND' : 'SECOND',
   'CATENATE' : 'CATENATE',
}

tokens = [
    'IDENTIFIER',
    'ASTERISK',
    'PERIOD',
    'QUESTION_MARK',
    'LEFT_PAREN',
    'RIGHT_PAREN',
    'EQUALS_OPERATOR',
    'NOT_EQUALS_OPERATOR',
    'LESS_THAN_OPERATOR',
    'GREATER_THAN_OPERATOR',
    'LESS_THAN_OR_EQUALS_OPERATOR',
    'GREATER_THAN_OR_EQUALS_OPERATOR',
    'QUOTTED_STRING',
    'COMMA',
    'NUMBER',
] + list(keywords.values())

errors = []

precedence = (
        ("right", 'NATURAL'),
)

t_ignore = ' \t\n\r'

def t_IDENTIFIER(t):
    r"[#a-zA-Z][#a-zA-Z0-9_]*"
    t.type = keywords.get(t.value.upper(),'IDENTIFIER') 
    return t

def t_ASTERISK(t):
    r"[*]"
    return t

def t_PERIOD(t):
    r"[.]"
    return t

def t_QUESTION_MARK(t):
    r"[?]"
    return t

def t_LEFT_PAREN(t):
    r"[(]"
    return t

def t_RIGHT_PAREN(t):
    r"[)]"
    return t

def t_EQUALS_OPERATOR(t):
    r"[=]"
    return t

def t_NOT_EQUALS_OPERATOR(t):
    r"[< ][>]"
    return t

def t_LESS_THAN_OR_EQUALS_OPERATOR(t):
    r"[< ][=]"
    return t

def t_GREATER_THAN_OR_EQUALS_OPERATOR(t):
    r"[>][=]"
    return t

def t_LESS_THAN_OPERATOR(t):
    r"[< ]"
    return t

def t_GREATER_THAN_OPERATOR(t):
    r"[>]"
    return t

def t_COMMA(t):
    r"[,]"
    return t

def t_NUMBER(t):
    r"[0-9]+"
    return t

def t_QUOTTED_STRING(t):
    r"['][^']*?[']"
    t.value = t.value[1:-1]
    return t


def t_error(t):
    global errors
    errors.append("Line {0:3}:\tIllegal character '{1}'".format(t.lexer.lineno, t.value[0]))
    t.lexer.skip(1)

def p_error(p):
    global errors
    if p:
        errors.append("Line {0:3}:\tSyntax error - unexpected '{1}' ".format(p.lineno, str(p.value)))
    else:
        errors.append("Syntax error - unexpected EOF ")
    print errors

def p_start(p):
    """start : querySpecification"""
    p[0] = p[1]


#< query specification>    ::= 
#          SELECT  [ < set quantifier> ] < select list> < table expression>
def p_querySpecification_without_set_quantifier(p):
    """querySpecification : SELECT selectList tableExpression"""
    p[0] = QueryLogic.Selecting.Select(p[3][0], p[3][1])
    p[0].AddRowTransformer(p[2][0])
    for aggregate in p[2][1]:
        p[0].AddAggregate(aggregate)

def p_querySpecification_with_set_quantifier(p):
    """querySpecification : SELECT setQuantifier selectList tableExpression"""
    pass

#< set quantifier>    ::=    DISTINCT  |  ALL
def p_setQuantifier_distinct(p):
    """setQuantifier : DISTINCT"""
    pass

def p_setQuantifier_all(p):
    """setQuantifier : ALL"""
    pass

#< select list>    ::= 
#         < asterisk>
#     |     < select sublist> [ { < comma> < select sublist> }... ]
def p_selectList_asterisk(p):
    """selectList : ASTERISK"""
    p[0] = (QueryLogic.Transformers.RowTransformers.IdentityRowTransformer(), [])

def p_selectList_selectListSelectSublist(p):
    """selectList : selectListSelectSublist"""
    cellTransformers = filter(lambda c: isinstance(c, QueryLogic.Transformers.CellTransformers.ICellTransformer), p[1])
    rowTransformer = QueryLogic.Transformers.RowTransformers.CellsRowTransformer(System.Array[QueryLogic.Transformers.CellTransformers.ICellTransformer](cellTransformers))
    aggregates = filter(lambda c: isinstance(c, QueryLogic.Aggregates.IAggregate), p[1])
    p[0] = (rowTransformer, aggregates)

def p_selectListSelectSublist_first(p):
    """selectListSelectSublist : selectSublist"""
    p[0] = p[1]

def p_selectListSelectSublist_next(p):
    """selectListSelectSublist : selectSublist COMMA selectListSelectSublist"""
    p[0] = p[1] + p[3]

#< select sublist>    ::=   < derived column> 
def p_selectSublist(p):
    """selectSublist : derivedColumn"""
    p[0] = [p[1]]

#< derived column>    ::=   < value expression> [ < as clause> ]
def p_derivedColumn_without_as_clause(p):
    """derivedColumn : valueExpression"""
    if isinstance(p[1], Model.ColumnHeader):
        p[0] = QueryLogic.Transformers.CellTransformers.CellTransformer(QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(p[1]))
    else:
        #p[0] = createAggregate(p[1], '')
        p[0] = parseFunctionOrAggregateStack(p[1], '')

def p_derivedColumn_with_as_clause(p):
    """derivedColumn : valueExpression asClause"""
    if isinstance(p[1], Model.ColumnHeader):
        p[0] = QueryLogic.Transformers.CellTransformers.CellTransformer(QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(p[1]), p[2])
    else:
        #p[0] = createAggregate(p[1], p[2])
        p[0] = parseFunctionOrAggregateStack(p[1], p[2])

def parseFunctionOrAggregateStack(stack, alias):
    innerFunctions, outerFunctions, aggregate = parseWholeStack(stack, [], [], None)

    if aggregate is None:
        expression = CreateChainedCellExpression(innerFunctions)
        result = QueryLogic.Transformers.CellTransformers.CellTransformer(expression, alias)
    elif aggregate == 'COUNT_*':
            result = QueryLogic.Aggregates.CountAggregate(QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(Model.ColumnHeader("", alias)), alias)
    else:
        innerExpression = CreateChainedCellExpression(innerFunctions)
        outerExpression = CreateChainedCellExpression(outerFunctions + [QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(Model.ColumnHeader("", alias))])
        if aggregate == 'AVG':
            result = QueryLogic.Aggregates.AverageAggregate(innerExpression, outerExpression, alias)
        elif aggregate == 'MAX':
            result = QueryLogic.Aggregates.MaximumAggregate(innerExpression, outerExpression, alias)
        elif aggregate == 'MIN':
            result = QueryLogic.Aggregates.MinimumAggregate(innerExpression, outerExpression, alias)
        elif aggregate == 'SUM':
            result = QueryLogic.Aggregates.SumAggregate(innerExpression, outerExpression, alias)
        elif aggregate == 'COUNT':
            result = QueryLogic.Aggregates.CountColumnAggregate(innerExpression, outerExpression, alias)
        else:
            result = None

    return result

def CreateChainedCellExpression(functions):
    if len(functions) > 1:
        selected = functions[0]
        if selected == 'LEN':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetLengthCellExpression(inner.Source), inner)
        elif selected == 'TRIM':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetTrimmedCellExpression(inner.Source), inner)
        elif selected == 'YEAR':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Year), inner)
        elif selected == 'MONTH':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Month), inner)
        elif selected == 'DAY':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Day), inner)
        elif selected == 'HOUR':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Hour), inner)
        elif selected == 'MINUTE':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Minute), inner)
        elif selected == 'SECOND':
            inner = CreateChainedCellExpression(functions[1:])
            return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetDatePartCellExpression(inner.Source, QueryLogic.Expressions.CellExpressions.DatePart.Second), inner)

        elif type(selected) is tuple:
            if selected[0] == 'SUBSTR':
                inner = CreateChainedCellExpression(functions[1:])
                return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.GetSubstringCellExpression(inner.Source, selected[1], selected[2]), inner)
            if selected[0] == 'CATENATE':
                inner = CreateChainedCellExpression(functions[1:])
                return QueryLogic.Expressions.CellExpressions.ChainedCellExpression(QueryLogic.Expressions.CellExpressions.CatenateStringCellExpression(inner.Source, selected[1]), inner)
        else:
            return None
    else:
        return functions[0]

    return None

def parseWholeStack(stack, innerFunctions, outerFunctions, aggregate):
    if isinstance(stack, Model.ColumnHeader):
        return (innerFunctions + [QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(stack)], outerFunctions, aggregate)

    function, parameters = stack
    if aggregate is None:
        if function == 'AVG' or function == 'MAX' or function == 'MIN' or function == 'SUM' or function == 'COUNT':
            return parseInnerFunctions(parameters[0], [], innerFunctions, function)
        elif function == 'COUNT_*':
            return (innerFunctions, outerFunctions, function)
        
    if function == 'LEN' or function == 'TRIM' or function == 'YEAR' or function == 'MONTH' or function == 'DAY' or function == 'HOUR' or function == 'MINUTE' or function == 'SECOND':
        return parseWholeStack(parameters[0], innerFunctions + [function], outerFunctions, aggregate)
    elif function == 'SUBSTR':
        return parseWholeStack(parameters[0], innerFunctions + [(function, int(parameters[1]), int(parameters[2]))], outerFunctions, aggregate)
    elif function == 'CATENATE':
        return parseWholeStack(parameters[0], innerFunctions + [(function, parameters[1])], outerFunctions, aggregate)
    else:
        return None

def parseInnerFunctions(stack, innerFunctions, outerFunctions, aggregate):
    if isinstance(stack, Model.ColumnHeader):
        return (innerFunctions + [QueryLogic.Expressions.CellExpressions.GetOriginalCellCellExpression(stack)], outerFunctions, aggregate)
    
    function, parameters = stack
    if function == 'LEN' or function == 'TRIM' or function == 'YEAR' or function == 'MONTH' or function == 'DAY' or function == 'HOUR' or function == 'MINUTE' or function == 'SECOND':
        return parseInnerFunctions(parameters[0], innerFunctions + [function], outerFunctions, aggregate)
    elif function == 'SUBSTR':
        return parseInnerFunctions(parameters[0], innerFunctions + [(function, int(parameters[1]), int(parameters[2]))], outerFunctions, aggregate)
    elif function == 'CATENATE':
        return parseInnerFunctions(parameters[0], innerFunctions + [(function, parameters[1])], outerFunctions, aggregate)
    else:
        return None

#< value expression>    ::=      < string value expression>
def p_valueExpression_stringValueExpression(p):
    """valueExpression : stringValueExpression"""
    p[0] = p[1]

#< string value expression>    ::=   < character value expression> | < bit value expression>
def p_stringValueExpression_characterValueExpression(p):
    """stringValueExpression : characterValueExpression"""
    p[0] = p[1]

#< character value expression>    ::=   < character factor>
def p_characterValueExpression_characterFactor(p):
    """characterValueExpression : characterFactor"""
    p[0] = p[1]

#< character factor>    ::=   < character primary>
def p_characterFactor_characterPrimary(p):
    """characterFactor : characterPrimary"""
    p[0] = p[1]

#< character primary>    ::=   < value expression primary>
def p_characterPrimary_valueExpression_Primary(p):
    """characterPrimary : valueExpressionPrimary"""
    p[0] = p[1]

#< value expression primary>    ::= 
#       < unsigned value specification>
#   | < column reference>
#   | < set function specification>
def p_valueExpressionPrimary_unsignedValueSpecification(p):
    """valueExpressionPrimary : unsignedValueSpecification"""
    p[0] = p[1]

def p_valueExpressionPrimary_columnReference(p):
    """valueExpressionPrimary : columnReference"""
    p[0] = p[1]

def p_valueExpressionPrimary_setFunctionSpecification(p):
    """valueExpressionPrimary : setFunctionSpecification"""
    p[0] = p[1]

#< set function specification>    ::= 
#          COUNT < left paren> < asterisk> < right paren>
#     | < general set function>
#< general set function>    ::= 
#         < set function type> < left paren> < value expression> < right paren>
def p_setFunctionSpecification_generalSetFunction(p):
    """setFunctionSpecification : generalSetFunction"""
    p[0] = p[1]

def p_generalSetFunction_valueExpression(p):
    """generalSetFunction : setFunctionType LEFT_PAREN manyValueExpressions RIGHT_PAREN"""
    p[0] = (p[1], p[3])

def p_manyValueExpressions_first(p):
    """manyValueExpressions : valueExpression"""
    p[0] = [p[1]]

def p_manyValueExpressions_mext(p):
    """manyValueExpressions : manyValueExpressions COMMA valueExpression"""
    p[0] = p[1] + [p[3]]


def p_generalSetFunction_ASTERISK(p):
    """generalSetFunction : setFunctionType LEFT_PAREN ASTERISK RIGHT_PAREN"""
    p[0] = ('COUNT_*', p[3])

#< set function type>    ::=    AVG  |  MAX  |  MIN  |  SUM  |  COUNT
def p_setFunctionType(p):
    """setFunctionType : AVG  
        | MAX  
        | MIN  
        | SUM  
        | COUNT
        | LEN
        | TRIM
        | SUBSTR
        | YEAR
        | MONTH
        | DAY
        | HOUR
        | MINUTE
        | SECOND
        | CATENATE"""
    p[0] = p[1]

#< column reference>    ::=   < qualifier> < period> < column name> [ < period> < column name> ... ]
def p_columnReference_columnNames(p):
    """columnReference : qualifier PERIOD columnNames"""
    p[0] = Model.ColumnHeader(p[1], p[3])

def p_columnNames_first(p):
    """columnNames : columnName"""
    p[0] = p[1]

def p_columnNames_next(p):
    """columnNames : columnName PERIOD columnNames"""
    p[0] = p[1] + '.' + p[3]

#< column name>    ::=   < identifier>
def p_columnName(p):
    """columnName : identifier"""
    p[0] = p[1]

#< as clause>    ::=   [  AS  ] < column name>
def p_asClause_without_as_keyword(p):
    """asClause : columnName"""
    p[0] = p[1]

def p_asClause_with_as_keyword(p):
    """asClause : AS columnName"""
    p[0] = p[2]

#< unsigned value specification>    ::=   < unsigned literal>
def p_unsignedValueSpecification(p):
    """unsignedValueSpecification : unsignedLiteral"""
    p[0] = p[1]

#< unsigned literal>    ::=    < unsigned numeric literal> | < general literal>
def p_unsignedLiteral_generalLiteral(p):
    """unsignedLiteral : generalLiteral"""
    p[0] = p[1]

def p_unsignedLiteral_unsignedNumericLiteral(p):
    """unsignedLiteral : unsignedNumericLiteral"""
    p[0] = p[1]

#< unsigned numeric literal>    ::= 
#         < exact numeric literal>
def p_unsignedNumericLiteral_exactNumericLiteral(p):
    """unsignedNumericLiteral : exactNumericLiteral"""
    p[0] = p[1]

#< exact numeric literal>    ::= 
#         < unsigned integer>
def p_exactNumericLiteral_unsignedInteger(p):
    """exactNumericLiteral : unsignedInteger"""
    p[0] = p[1]

#< exact numeric literal>    ::= 
#         < unsigned integer>
def p_unsignedInteger(p):
    """unsignedInteger : NUMBER"""
    p[0] = p[1]

#< general literal>    ::= 
#         < character string literal>
def p_generalLiteral(p):
    """generalLiteral : characterStringLiteral"""
    p[0] = p[1]

#< character string literal>    ::= 
#     QUOTE [ < character representation> ... ] QUOTE
#< character representation>    ::=   < nonquote character>
def p_characterStringLiteral(p):
    """characterStringLiteral : QUOTTED_STRING"""
    p[0] = p[1]

#< identifier>    ::=   < actual identifier>
def p_identifier(p):
    """identifier : actualIdentifier"""
    p[0] = p[1]

#< actual identifier>    ::=   < regular identifier>
def p_actualIdentifier(p):
    """actualIdentifier : regularIdentifier"""
    p[0] = p[1]

#< regular identifier>    ::=   < identifier body>
def p_regularIdentifier(p):
    """regularIdentifier : identifierBody"""
    p[0] = p[1]

#< identifier body>    ::=   < identifier start> [ { < underscore> | < identifier part> } ... ]
#< identifier start>    ::=   !! See the Syntax rules
#< identifier part>    ::=   < identifier start> | < digit>
def p_identifierBody(p):
    """identifierBody : IDENTIFIER"""
    p[0] = p[1]

#< table expression>    ::= 
#         < from clause>
#         [ < where clause> ]
#         [ < group by clause> ]
#         [ < order by clause]
def p_tableExpression_only_fromClause(p):
    """tableExpression : fromClause"""
    p[0] = (p[1], None)

def p_tableExpression_fromClause_whereClause(p):
    """tableExpression : fromClause whereClause"""
    p[0] = (QueryLogic.Filtering.Where(p[1], p[2]), None)

def p_tableExpression_fromClause_groupByClause(p):
    """tableExpression : fromClause groupByClause"""
    p[0] = (QueryLogic.Grouping.GroupBy(p[1], p[2]), None)

def p_tableExpression_fromClause_whereClause_groupByClause(p):
    """tableExpression : fromClause whereClause groupByClause"""
    p[0] = (QueryLogic.Grouping.GroupBy(QueryLogic.Filtering.Where(p[1], p[2]), p[3]), None)

def p_tableExpression_fromClause_orderByClause(p):
    """tableExpression : fromClause orderByClause"""
    p[0] = (p[1], p[2])

def p_tableExpression_fromClause_groupByClause_orderByClause(p):
    """tableExpression : fromClause groupByClause orderByClause"""
    p[0] = (QueryLogic.Grouping.GroupBy(p[1], p[2]), p[3])

def p_tableExpression_fromClause_whereClause_orderByClause(p):
    """tableExpression : fromClause whereClause orderByClause"""
    p[0] = (QueryLogic.Filtering.Where(p[1], p[2]), p[3])

def p_tableExpression_fromClause_whereClause_groupByClause_orderByClause(p):
    """tableExpression : fromClause whereClause groupByClause orderByClause"""
    p[0] = (QueryLogic.Grouping.GroupBy(QueryLogic.Filtering.Where(p[1], p[2]), p[3]), p[4])

#< from clause>    ::=    FROM < table reference>
def p_fromClause(p):
    """fromClause : FROM tableReference"""
    p[0] = p[2]

#< table reference>    ::= 
#         < table name>  < correlation specification> 
#     | < joined table>
def p_tableReference_tableReferenceFrom(p):
    """tableReference : tableReferenceFrom"""
    p[0] = p[1]

def p_tableReference_joinedTable(p):
    """tableReference : joinedTable"""
    p[0] = p[1]

def p_tableReferenceFrom_tableName(p):
    """tableReferenceFrom : tableName correlationSpecification"""
    p[0] = QueryLogic.RelationProviding.From(p[1], p[2])


#< table name>    ::=   < qualified name>
def p_tableName(p):
    """tableName : qualifiedName"""
    p[0] = p[1]

#< qualified name>    ::=    < qualified identifier> [ PERIOD < qualified identifier> ...]
def p_qalifiedName(p):
    """qualifiedName : qualifiedIdentifiers"""
    p[0] = p[1]

def p_qalifiedName_first(p):
    """qualifiedIdentifiers : qualifiedIdentifier"""
    p[0] = p[1]

def p_qalifiedName_next(p):
    """qualifiedIdentifiers : qualifiedIdentifier PERIOD qualifiedIdentifiers"""
    p[0] = QueryLogic.Selectors.ChainedSelector(p[1], p[3])

#< qualified identifier>    ::=   < identifier> | QUESTION_MARK | ASTERISK
def p_qalifiedIdentifier_identifier(p):
    """qualifiedIdentifier : identifier"""
    p[0] = QueryLogic.Selectors.NodeSelector(p[1])

def p_qalifiedIdentifier_QUESTION_MARK(p):
    """qualifiedIdentifier : QUESTION_MARK"""
    p[0] = QueryLogic.Selectors.LevelSelector()

def p_qalifiedIdentifier_ASTERISK(p):
    """qualifiedIdentifier : ASTERISK"""
    p[0] = QueryLogic.Selectors.AnySelector()


#< correlation specification>    ::= 
#          AS   < correlation name> 
#< correlation name>    ::=   < identifier>
def p_correlationSpecification(p):
    """correlationSpecification : AS identifier"""
    p[0] = p[2]

#< joined table>    ::= 
#         < cross join>
#     | < qualified join>
def p_joinedTable_crossJoin(p):
    """joinedTable : crossJoin"""
    p[0] = p[1]

def p_joinedTable_qualifiedJoin(p):
    """joinedTable : qualifiedJoin"""
    p[0] = p[1]

#< cross join>    ::= 
#         < table reference>  CROSS  JOIN < table reference>
def p_crossJoin(p):
    """crossJoin : tableReference CROSS JOIN tableReference"""
    p[0] = QueryLogic.Joins.Implementation.CrossJoin(p[1], p[4])

#< qualified join>    ::= 
#         < table reference>  < join type> JOIN < table reference> [ < join specification> ]
#< join type>    ::= 
#		NATURAL
#	  | INNER 
#     | < outer join type> [  OUTER  ]
#< outer join type>    ::=    LEFT  |  RIGHT  |  FULL
def p_qualifiedJoin_natural(p):
    """qualifiedJoin : tableReference NATURAL JOIN tableReferenceFrom"""
    p[0] = QueryLogic.Joins.Implementation.NaturalJoin(p[1], p[4])

def p_qualifiedJoin_inner(p):
    """qualifiedJoin : tableReference INNER JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.InnerJoin(p[1], p[4], p[5])

def p_qualifiedJoin_left_outer(p):
    """qualifiedJoin : tableReference LEFT OUTER JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.LeftOuterJoin(p[1], p[5], p[6])

def p_qualifiedJoin_left(p):
    """qualifiedJoin : tableReference LEFT JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.LeftOuterJoin(p[1], p[4], p[5])

def p_qualifiedJoin_right_outer(p):
    """qualifiedJoin : tableReference RIGHT OUTER JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.RightOuterJoin(p[1], p[5], p[6])

def p_qualifiedJoin_right(p):
    """qualifiedJoin : tableReference RIGHT JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.RightOuterJoin(p[1], p[4], p[5])

def p_qualifiedJoin_full_outer(p):
    """qualifiedJoin : tableReference FULL OUTER JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.FullOuterJoin(p[1], p[5], p[6])

def p_qualifiedJoin_full(p):
    """qualifiedJoin : tableReference FULL JOIN tableReference joinSpecification"""
    p[0] = QueryLogic.Joins.Implementation.FullOuterJoin(p[1], p[4], p[5])

#< join specification>    ::=   < join condition>
def p_joinSpecification_joinCondition(p):
    """joinSpecification : joinCondition"""
    p[0] = p[1]

#< join condition>    ::=    ON < search condition>
def p_joinCondition_searchCondition(p):
    """joinCondition : ON searchCondition"""
    p[0] = p[2]

#< search condition>    ::= 
#         < boolean term>
#     | < search condition>  OR < boolean term>
def p_searchCondition_booleanTerm(p):
    """searchCondition : booleanTerm"""
    p[0] = p[1]

def p_searchCondition_searchCondition_OR_booleanTerm(p):
    """searchCondition : booleanTerm OR searchCondition"""
    p[0] = QueryLogic.Predicates.Complex.OrPredicate(p[1], p[3])

#< boolean term>    ::= 
#         < boolean factor>
#     | < boolean term>  AND < boolean factor>
def p_booleanTerm_booleanFactor(p):
    """booleanTerm : booleanFactor"""
    p[0] = p[1]

def p_booleanTerm_booleanTerm_AND_booleanFactor(p):
    """booleanTerm : booleanFactor AND booleanTerm"""
    p[0] = QueryLogic.Predicates.Complex.AndPredicate(p[1], p[3])

#< boolean factor>    ::=   [  NOT  ] < boolean test>
def p_booleanFactor_booleanTest(p):
    """booleanFactor : booleanTest"""
    p[0] = p[1]

def p_booleanFactor_NOT_booleanTest(p):
    """booleanFactor : NOT booleanTest"""
    p[0] = QueryLogic.Predicates.Complex.NotPredicate(p[2])

#< boolean test>    ::=   < boolean primary> 
def p_booleanTest_booleanPrimary(p):
    """booleanTest : booleanPrimary"""
    p[0] = p[1]

#< boolean primary>    ::=   < predicate> | < left paren> < search condition> < right paren>
def p_booleanPrimary_predicate(p):
    """booleanPrimary : predicate"""
    p[0] = p[1]

def p_booleanPrimary_LEFT_PAREN_searchCondition_rightParen(p):
    """booleanPrimary : LEFT_PAREN searchCondition RIGHT_PAREN"""
    p[0] = p[2]

#< predicate>    ::= 
#         < comparison predicate>
#     | < like predicate>
#     | < null predicate>
def p_predicate_comparisonPredicate(p):
    """predicate : comparisonPredicate"""
    p[0] = p[1]

def p_predicate_likePredicate(p):
    """predicate : likePredicate"""
    p[0] = p[1]

def p_predicate_nullPredicate(p):
    """predicate : nullPredicate"""
    p[0] = p[1]

#< comparison predicate>    ::=   < row value constructor> < comp op> < row value constructor>
def p_comparisonPredicate(p):
    """comparisonPredicate : rowValueConstructor compOp rowValueConstructor"""
    left = p[1]
    right = p[3]
    operator = p[2]
    if operator == '=':
        predicate = QueryLogic.Predicates.Simple.EqualPredicate(left, right)
    elif operator == '< >':
        predicate = QueryLogic.Predicates.Simple.NotEqualPredicate(left, right)
    elif operator == '>':
        predicate = QueryLogic.Predicates.Simple.GreaterThanPredicate(left, right)
    elif operator == '< ':
        predicate = QueryLogic.Predicates.Simple.LessThanPredicate(left, right)
    elif operator == '>=':
        predicate = QueryLogic.Predicates.Simple.GreaterEqualPredicate(left, right)
    elif operator == '< =':
        predicate = QueryLogic.Predicates.Simple.LessEqualPredicate(left, right)
    else:
        predicate = None
    p[0] = predicate

#< row value constructor>    ::= 
#         < row value constructor element>
def p_rowValueConstructor_rowValueConstructorElement(p):
    """rowValueConstructor : rowValueConstructorElement"""
    p[0] = p[1]

def p_rowValueConstructorElement_valueExpression(p):
    """rowValueConstructorElement : valueExpression"""
    if isinstance(p[1], Model.ColumnHeader):
        p[0] = QueryLogic.Expressions.RowExpressions.GetCellRowExpression(p[1])
    else:
        p[0] = QueryLogic.Expressions.RowExpressions.ConstantRowExpression(p[1])

#< like predicate>    ::=   < match value> [  NOT  ]  LIKE < pattern> [  ESCAPE < escape character> ]
def p_likePredicate_matchValue_LIKE_pattern(p):
    """likePredicate : matchValue LIKE pattern"""
    p[0] = createLike(p[1], p[3], '\0')

def p_likePredicate_matchValue_NOT_LIKE_pattern(p):
    """likePredicate : matchValue NOT LIKE pattern"""
    p[0] = QueryLogic.Predicates.Complex.NotPredicate(createLike(p[1], p[4], '\0'))

def p_likePredicate_matchValue_LIKE_pattern_ESCAPE_escapeCharacter(p):
    """likePredicate : matchValue LIKE pattern ESCAPE escapeCharacter"""
    p[0] = createLike(p[1], p[3], p[5])

def p_likePredicate_matchValue_NOT_LIKE_pattern_ESCAPE_escapeCharacter(p):
    """likePredicate : matchValue NOT LIKE pattern ESCAPE escapeCharacter"""
    p[0] = QueryLogic.Predicates.Complex.NotPredicate(createLike(p[1], p[4], p[6]))

def createLike(value, pattern, escapeCharacter):
    return QueryLogic.Predicates.Simple.LikePredicate(QueryLogic.Expressions.RowExpressions.GetCellRowExpression(value),
                                                      QueryLogic.Expressions.RowExpressions.ConstantRowExpression(pattern),
                                                      escapeCharacter)

#< match value>    ::=   < character value expression>
def p_matchValue(p):
    """matchValue : characterValueExpression"""
    p[0] = p[1]

#< qualifier>    ::=   < table name> | < correlation name>
def p_qualifier_tableName(p):
    """qualifier : identifier"""
    p[0] = p[1]

#< pattern>    ::=   < character value expression>
def p_pattern(p):
    """pattern : characterValueExpression"""
    p[0] = p[1]

#< escape character>    ::=   < character value expression>
def p_escapeCharacter(p):
    """escapeCharacter : characterValueExpression"""
    p[0] = p[1]

#< null predicate>    ::=    IS  [  NOT  ]  NULL
#[AF] Tutaj jak dla mnie jest bug, powinno być < row value constructor> IS [ NOT ] NULL
def p_nullPredicate_isNull(p):
    """nullPredicate : rowValueConstructor IS NULL"""
    p[0] = QueryLogic.Predicates.Simple.IsNullPredicate(p[1])

#< where clause>    ::=    WHERE < search condition>
def p_whereClause(p):
    """whereClause : WHERE searchCondition"""
    p[0] = p[2]

#< group by clause>    ::=    GROUP  BY < grouping column reference list>
def p_groupByClause(p):
    """groupByClause : GROUP BY groupingColumnReferenceList"""
    p[0] = System.Array[QueryLogic.Grouping.GroupingSet]([QueryLogic.Grouping.GroupingSet(System.Array[Model.ColumnHeader](p[3]))])

#< grouping column reference list>    ::=
#         < grouping column reference> [ { < comma> < grouping column reference> }... ]
def p_groupingColumnReferenceList_next(p):
    """groupingColumnReferenceList : groupingColumnReference COMMA groupingColumnReferenceList"""
    p[0] = [p[1]] + p[3]

def p_groupingColumnReferenceList_first(p):
    """groupingColumnReferenceList : groupingColumnReference"""
    p[0] = [p[1]]
    
#< grouping column reference>    ::=   < column reference> 
def p_groupingColumnReference(p):
    """groupingColumnReference : columnReference"""
    p[0] = p[1]

#< order by clause>  ::=     ORDER BY < ordering column reference list> [ SKIP < unsigned integer> ] [ FETCH < unsigned integer> ]
def p_orderByClause_orderingColumnReferenceList(p):
    """orderByClause : ORDER BY orderingColumnReferenceList"""
    p[0] = createOrderBy(p[3], None, None)

def p_orderByClause_orderingColumnReferenceList_skip(p):
    """orderByClause : ORDER BY orderingColumnReferenceList SKIP unsignedInteger"""
    p[0] = createOrderBy(p[3], p[5], None)

def p_orderByClause_orderingColumnReferenceList_fetch(p):
    """orderByClause : ORDER BY orderingColumnReferenceList FETCH unsignedInteger"""
    p[0] = createOrderBy(p[3], None, p[5])

def p_orderByClause_orderingColumnReferenceList_skip_fetch(p):
    """orderByClause : ORDER BY orderingColumnReferenceList SKIP unsignedInteger FETCH unsignedInteger"""
    p[0] = createOrderBy(p[3], p[5], p[7])

def createOrderBy(columns, skip, fetch):
    asc = QueryLogic.Ordering.OrderDirection.Ascending
    desc = QueryLogic.Ordering.OrderDirection.Descending
    skip = castToNullableInt(skip)
    fetch = castToNullableInt(fetch)
    columns = System.Array[QueryLogic.Ordering.ColumnOrdering](
        map(lambda column: QueryLogic.Ordering.ColumnOrdering(column[0], asc if column[1] == 'ASC' else desc),
            columns))
    return QueryLogic.Ordering.OrderBy(columns, skip, fetch)

def castToNullableInt(value):
    return None if value is None else System.Nullable[int](int(value))

#< ordering column reference list>    ::=
#         < ordering column reference> [ { < comma> < ordering column reference> }... ]
def p_orderingColumnReferenceList_next(p):
    """orderingColumnReferenceList : orderingColumnReference COMMA orderingColumnReferenceList"""
    p[0] = [p[1]] + p[3]

def p_orderingColumnReferenceList_first(p):
    """orderingColumnReferenceList : orderingColumnReference"""
    p[0] = [p[1]]
    
#< ordering column reference>    ::=   < column reference> [ DESC ]
def p_orderingColumnReference(p):
    """orderingColumnReference : columnReference"""
    p[0] = (p[1], 'ASC')

def p_orderingColumnReference_DESC(p):
    """orderingColumnReference : columnReference DESC"""
    p[0] = (p[1], 'DESC')

#< comp op>    ::= 
#         < equals operator>
#     | < not equals operator>
#     | < less than operator>
#     | < greater than operator>
#     | < less than or equals operator>
#     | < greater than or equals operator>
def p_compOp_EQUALS_OPERATOR(p):
    """compOp : EQUALS_OPERATOR"""
    p[0] = p[1]

def p_compOp_NOT_EQUALS_OPERATOR(p):
    """compOp : NOT_EQUALS_OPERATOR"""
    p[0] = p[1]

def p_compOp_LESS_THAN_OPERATOR(p):
    """compOp : LESS_THAN_OPERATOR"""
    p[0] = p[1]

def p_compOp_GREATER_THAN_OPERATOR(p):
    """compOp : GREATER_THAN_OPERATOR"""
    p[0] = p[1]

def p_compOp_LESS_THAN_OR_EQUALS_OPERATOR(p):
    """compOp : LESS_THAN_OR_EQUALS_OPERATOR"""
    p[0] = p[1]

def p_compOp_GREATER_THAN_OR_EQUALS_OPERATOR(p):
    """compOp : GREATER_THAN_OR_EQUALS_OPERATOR"""
    p[0] = p[1]


def initialize(plyBasePath):
    global yacc
    global lex
    global sys
    global clr
    global parser
    global lexer
    global System
    global Model
    global QueryLogic

    import imp
    import sys
    import clr

    lex = imp.load_source('ply.lex', plyBasePath + '\\lex.py')
    yacc = imp.load_source('ply.yacc',  plyBasePath + '\\yacc.py')
    lexer = lex.lex(module = sys.modules[__name__], debug=1)
    parser = yacc.yacc(module = sys.modules[__name__])

    clr.AddReference("Model")
    clr.AddReference("QueryLogic")
    clr.AddReference("System")
    import System
    import Model
    import QueryLogic


def parse(text):
    if lex == 0 or yacc == 0:
        raise RuntimeError("Not initialized")

    global errors
    errors = []

    parsedObject = parser.parse(text, lexer=lexer)
    return System.Tuple.Create[QueryLogic.Selecting.Select, System.Array[System.String]](parsedObject, System.Array[System.String](errors))

This is simply an implementation of grammar parsing with some amendments for handling muli-part identifiers and with non-standard construct SKIP FETCH instead of OFFSET FETCH. If you understand PLY then this code should be pretty straightforward. In the next part we will implement the tests.

This is the twenty first part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

We have all things to transform rows and columns. Now it is time to implement SELECT clause.

using System.Collections.Generic;
using System.Linq;
using Model;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Grouping;
using QueryLogic.Ordering;
using QueryLogic.RelationProviding;
using QueryLogic.Transformers.CellTransformers;
using QueryLogic.Transformers.RowTransformers;

namespace QueryLogic.Selecting
{
    public class Select : IRelationProvider
    {
        public Select(IRelationProvider sourceRelation, OrderBy order = null)
            : this(new GroupBy(sourceRelation, new ColumnHeader[0]), order)
        {
        }

        public Select(GroupBy groupBy, OrderBy order = null)
        {
            GroupBy = groupBy;
            Order = order;
            RowTransformers = new List<IRowTransformer>();
            Aggregates = new List<IAggregate>();
        }

        public IList<IAggregate> Aggregates { get; private set; }
        public IList<IRowTransformer> RowTransformers { get; private set; }
        public GroupBy GroupBy { get; private set; }
        public OrderBy Order { get; private set; }

        public Relation CreateRelation(Node source)
        {
            Relation resultRelation = Aggregates.Any()
                ? CreateRelationWithAggregates(source)
                : CreateRelationWithoutAggregates(source);

            return OrderRelation(resultRelation);
        }

        private Relation OrderRelation(Relation relation)
        {
            return Order != null ? Order.OrderRelation(relation) : relation;
        }

        protected bool Equals(Select other)
        {
            return Aggregates.SequenceEqual(other.Aggregates) && RowTransformers.SequenceEqual(other.RowTransformers) &&
                   Equals(GroupBy, other.GroupBy);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((Select)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (Aggregates != null ? Aggregates.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (RowTransformers != null ? RowTransformers.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (GroupBy != null ? GroupBy.GetHashCode() : 0);
                return hashCode;
            }
        }

        public void AddRowTransformer(IRowTransformer rowTransformer)
        {
            RowTransformers.Add(rowTransformer);
        }

        public void AddAggregate(IAggregate aggregate)
        {
            Aggregates.Add(aggregate);
        }

        private Relation CreateRelationWithoutAggregates(Node source)
        {
            var relation = new Relation();

            foreach (Relation sourceRelation in GroupBy.CreateRelations(source))
            {
                foreach (Row sourceRow in sourceRelation.Rows)
                {
                    var row = new Row();

                    foreach (IRowTransformer rowTransformer in RowTransformers)
                    {
                        row.AddCells(rowTransformer.Calculate(sourceRow).Cells);
                    }

                    if (row.Columns.Any())
                    {
                        relation.AddRow(row);
                    }
                }
            }
            return relation;
        }

        private Relation CreateRelationWithAggregates(Node source)
        {
            var relation = new Relation();

            foreach (Relation sourceRelation in GroupBy.CreateRelations(source))
            {
                var row = new Row();

                AddCellsFromAggregatesToRow(row, sourceRelation);
                AddNonAggregatedCellsToRow(row, sourceRelation);

                if (row.Columns.Any())
                {
                    relation.AddRow(row);
                }
            }
            return relation;
        }

        private void AddCellsFromAggregatesToRow(Row row, Relation sourceRelation)
        {
            foreach (IAggregate aggregate in Aggregates)
            {
                row.AddCell(aggregate.Calculate(sourceRelation));
            }
        }

        private void AddNonAggregatedCellsToRow(Row row, Relation sourceRelation)
        {
            foreach (IRowTransformer rowTransformer in RowTransformers)
            {
                foreach (Cell cell in rowTransformer.Calculate(sourceRelation.Rows.FirstOrDefault()).Cells)
                {
                    ColumnHeader cellSourceHeader = FindSourceHeaderForCell(cell, rowTransformer);
                    if (GroupBy.ColumnSets.Any(columnSet => columnSet.Columns.Contains(cellSourceHeader)))
                    {
                        row.AddCell(cell);
                    }
                    else
                    {
                        throw new ColumnNotGroupedException(cell.ColumnHeader);
                    }
                }
            }
        }

        private ColumnHeader FindSourceHeaderForCell(Cell cell, IRowTransformer rowTransformer)
        {
            ColumnHeader cellSourceHeader = null;
            foreach (
                ICellTransformer cellTransformer in
                    rowTransformer.CellTransformers.Where(
                        cellTransformer => cell.ColumnHeader.Equals(cellTransformer.Result)))
            {
                cellSourceHeader = cellTransformer.Source;
            }

            return cellSourceHeader;
        }
    }
}

We have two ways: either we use aggregates or not. In the latter case we simply extract rows from source relation and transform them using transformers. In the former case we extract rows and fix aggregates.

And here go the tests:

using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;
using QueryLogic.Grouping;
using QueryLogic.Ordering;
using QueryLogic.Selecting;
using QueryLogic.Test.Mocks;
using QueryLogic.Transformers.CellTransformers;
using QueryLogic.Transformers.RowTransformers;

namespace QueryLogic.Test.Selecting
{
    [TestFixture]
    internal class SelectTests
    {
        [Test]
        [ExpectedException(typeof(ColumnNotGroupedException))]
        public void CreateRelation_AggregatePassedAndColumnNotInGroupByPassed_ShouldThrowColumnNotGroupedException()
        {
            // Arrange
            var firstRow = new Row();
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column2"), "value2"));

            var relation = new Relation();
            relation.AddRow(firstRow);

            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            var select = new Select(new DummyRelationProvider(relation));
            select.AddAggregate(aggregate);
            select.AddRowTransformer(new CellsRowTransformer(new List<ICellTransformer>
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("schema", "Column2")),
                    "Column2"),
            }));

            // Act
            // ReSharper disable once UnusedVariable
            Relation actualRelation = select.CreateRelation(null);
        }

        [Test]
        public void CreateRelation_AggregatePassedAndColumnWithNewAliasAndInGroupByPassed_ShouldNotThrowException()
        {
            // Arrange
            var firstRow = new Row();
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column2"), "value2"));

            var relation = new Relation();
            relation.AddRow(firstRow);

            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            var select = new Select(new GroupBy(new DummyRelationProvider(relation),
                new[] { new ColumnHeader("schema", "Column2") }));
            select.AddAggregate(aggregate);
            select.AddRowTransformer(new CellsRowTransformer(new List<ICellTransformer>
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("schema", "Column2")),
                    "Column2Aliased"),
            }));

            // Act
            // ReSharper disable once UnusedVariable
            Relation actualRelation = select.CreateRelation(null);
        }

        [Test]
        public void CreateRelation_AggregatePassed_ShouldReturnRelationWithCountedAggregate()
        {
            // Arrange
            var firstRow = new Row();
            var columnHeader = new ColumnHeader("schema", "Column1");
            firstRow.AddCell(new Cell(columnHeader, "value1"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "value1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "value4"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            var select = new Select(new DummyRelationProvider(relation));
            select.AddAggregate(aggregate);


            var expectedColumns = new List<ColumnHeader>
            {
                new ColumnHeader("", ""),
            };

            var firstExpectedRow = new Row();
            firstExpectedRow.AddCell(new Cell(new ColumnHeader("", ""), "3"));

            var expectedRows = new List<Row> { firstExpectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable<ColumnHeader> actualColumns = actualRelation.Columns;
            IEnumerable<Row> actualRows = actualRelation.Rows;

            // Assert
            CollectionAssert.AreEquivalent(expectedColumns, actualColumns);
            CollectionAssert.AreEquivalent(expectedRows, actualRows);
        }

        [Test]
        public void CreateRelation_ColumnMappingPassed_ShouldReturnRelationWithSelectedColumns()
        {
            // Arrange
            var firstRow = new Row();
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column2"), "value2"));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column3"), null));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column4"), null));
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column5"), null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            secondRow.AddCell(new Cell(new ColumnHeader("schema", "Column2"), null));
            secondRow.AddCell(new Cell(new ColumnHeader("schema", "Column3"), "value3"));
            secondRow.AddCell(new Cell(new ColumnHeader("schema", "Column4"), null));
            secondRow.AddCell(new Cell(new ColumnHeader("schema", "Column5"), null));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), null));
            thirdRow.AddCell(new Cell(new ColumnHeader("schema", "Column2"), null));
            thirdRow.AddCell(new Cell(new ColumnHeader("schema", "Column3"), null));
            thirdRow.AddCell(new Cell(new ColumnHeader("schema", "Column4"), "value4"));
            thirdRow.AddCell(new Cell(new ColumnHeader("schema", "Column5"), "value2"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var cellTransformers = new List<ICellTransformer>
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("schema", "Column1")),
                    "Column1"),
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("schema", "Column2")),
                    "Column164")
            };

            var columnExpression = new CellsRowTransformer(cellTransformers);

            var secondCellTransformers = new List<ICellTransformer>
            {
                new CellTransformer(new GetOriginalCellCellExpression(new ColumnHeader("schema", "Column3")),
                    "Column543")
            };

            var secondColumnExpression = new CellsRowTransformer(secondCellTransformers);

            var select = new Select(new DummyRelationProvider(relation));
            select.AddRowTransformer(columnExpression);
            select.AddRowTransformer(secondColumnExpression);


            var expectedColumns = new List<ColumnHeader>
            {
                new ColumnHeader("schema", "Column1"),
                new ColumnHeader("schema", "Column164"),
                new ColumnHeader("schema", "Column543")
            };
            var firstExpectedRow = new Row();
            firstExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            firstExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column164"), "value2"));
            firstExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column543"), null));

            var secondExpectedRow = new Row();
            secondExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            secondExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column164"), null));
            secondExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column543"), "value3"));

            var thirdExpectedRow = new Row();
            thirdExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), null));
            thirdExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column164"), null));
            thirdExpectedRow.AddCell(new Cell(new ColumnHeader("schema", "Column543"), null));

            var expectedRows = new List<Row> { firstExpectedRow, secondExpectedRow, thirdExpectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable<ColumnHeader> actualColumns = actualRelation.Columns;
            IEnumerable<Row> actualRows = actualRelation.Rows;

            // Assert
            CollectionAssert.AreEquivalent(expectedColumns, actualColumns);
            CollectionAssert.AreEquivalent(expectedRows, actualRows);
        }

        [Test]
        public void CreateRelation_OrderingPassed_ShouldSortResult()
        {
            // Arrange
            var cellWithValue1 = new Cell(new ColumnHeader("s", "c1"), "1");
            var cellWithValue2 = new Cell(new ColumnHeader("s", "c1"), "2");
            var relation = new Relation(new[]
            {
                new Row(new[]
                {
                    cellWithValue2
                }),
                new Row(new[]
                {
                    cellWithValue1
                })
            });

            var select = new Select(new DummyRelationProvider(relation), new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("s", "c1"))
            }));
            select.AddRowTransformer(new IdentityRowTransformer());
            var expectedRows = new List<Row>
            {
                new Row(new[]
                {
                    cellWithValue1
                }),
                new Row(new[]
                {
                    cellWithValue2
                })
            };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable<Row> actual = actualRelation.Rows;

            // Assert
            CollectionAssert.AreEquivalent(expectedRows, actual);
        }

        [Test]
        public void CreateRelation_TwoSameAggregates_ShouldReturnRelationWithCountedAggregates()
        {
            // Arrange
            var firstRow = new Row();
            firstRow.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));

            var relation = new Relation();
            relation.AddRow(firstRow);

            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            var select = new Select(new GroupBy(new DummyRelationProvider(relation),
                new[] { new ColumnHeader("schema", "Column1") }));
            select.AddAggregate(aggregate);
            select.AddAggregate(aggregate);

            var expectedRow = new Row();
            expectedRow.AddCell(new Cell(new ColumnHeader("", ""), "1"));
            expectedRow.AddCell(new Cell(new ColumnHeader("", ""), "1"));
            var expectedRows = new List<Row> { expectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable<Row> actual = actualRelation.Rows;

            // Assert
            CollectionAssert.AreEquivalent(expectedRows, actual);
        }
    }
}

And we have our engine working. Now we need to parse query from string and our database is finished.

This is the twentieth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

We can extract nodes, transform them into rows, filter, join, group, order, and transform them. It is high time to glue all the things together in order to have engine working.

For connecting cells and expressions we will use transformers. They execute things in correct order and allow stacking of different operations.

using System.Collections.Generic;
using Model;
using QueryLogic.Transformers.CellTransformers;

namespace QueryLogic.Transformers.RowTransformers
{
    public interface IRowTransformer
    {
        IEnumerable<ICellTransformer> CellTransformers { get; }
        Row Calculate(Row row);
    }
}

We start with transformer for row. We can see that it transforms cells of row. We can implement identity transformer:

using System.Collections.Generic;
using Model;
using QueryLogic.Transformers.CellTransformers;

namespace QueryLogic.Transformers.RowTransformers
{
    public class IdentityRowTransformer : IRowTransformer
    {
        public Row Calculate(Row row)
        {
            return row;
        }

        public IEnumerable<ICellTransformer> CellTransformers
        {
            get { return new ICellTransformer[0]; }
        }

        protected bool Equals(IdentityRowTransformer other)
        {
            return true;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((IdentityRowTransformer)obj);
        }

        public override int GetHashCode()
        {
            return 0;
        }
    }
}

We can also implement proper row transformer:

using System.Collections.Generic;
using System.Linq;
using Model;
using QueryLogic.Transformers.CellTransformers;

namespace QueryLogic.Transformers.RowTransformers
{
    public class CellsRowTransformer : IRowTransformer
    {
        public CellsRowTransformer(IEnumerable<ICellTransformer> cellTransformers)
        {
            CellTransformers = cellTransformers;
        }

        public IEnumerable<ICellTransformer> CellTransformers { get; private set; }

        public Row Calculate(Row row)
        {
            var result = new List<Cell>();
            foreach (ICellTransformer cellTransformer in CellTransformers)
            {
                result.Add(cellTransformer.TransformCell(row));
            }

            return new Row(result);
        }

        protected bool Equals(CellsRowTransformer other)
        {
            return CellTransformers.SequenceEqual(other.CellTransformers);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((CellsRowTransformer)obj);
        }

        public override int GetHashCode()
        {
            return (CellTransformers != null ? CellTransformers.GetHashCode() : 0);
        }
    }
}

Now cell transformer:

using Model;

namespace QueryLogic.Transformers.CellTransformers
{
    public interface ICellTransformer
    {
        ColumnHeader Source { get; }
        ColumnHeader Result { get; }
        Cell TransformCell(Row sourceRow);
    }
}

using Model;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Transformers.CellTransformers
{
    public class CellTransformer : ICellTransformer
    {
        public CellTransformer(ICellExpression cellExpression)
        {
            CellExpression = cellExpression;
        }

        public CellTransformer(ICellExpression cellExpression, string resultColumn)
            : this(cellExpression)
        {
            Result = new ColumnHeader("", resultColumn);
        }

        public ICellExpression CellExpression { get; private set; }

        public ColumnHeader Source
        {
            get { return CellExpression.Source; }
        }

        public ColumnHeader Result { get; private set; }

        public Cell TransformCell(Row sourceRow)
        {
            Cell tmp = CellExpression.Calculate(sourceRow);
            Result = new ColumnHeader(tmp.ColumnHeader.Schema, Result != null ? Result.Name : tmp.ColumnHeader.Name);
            return new Cell(Result, tmp.Value);
        }

        protected bool Equals(CellTransformer other)
        {
            return Equals(CellExpression, other.CellExpression) && string.Equals(Result, other.Result);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((CellTransformer)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((CellExpression != null ? CellExpression.GetHashCode() : 0) * 397) ^
                       (Result != null ? Result.GetHashCode() : 0);
            }
        }
    }
}

We can see that transformer takes expression and calculates result by using it. This is the thing which glues all other mechanisms together: we pass row to row transformer which uses cell expression to transform cell and return new row.

And now tests:

using Model;
using NUnit.Framework;
using QueryLogic.Transformers.RowTransformers;

namespace QueryLogic.Test.Transformers.RowTransformers
{
    [TestFixture]
    public class IdentityRowTransformerTests
    {
        [Test]
        public void Calculate_ShouldReturnSameRow()
        {
            // Arrage
            var row = new Row();
            row.AddCell(new Cell(new ColumnHeader("schema", "Column1"), "value1"));
            row.AddCell(new Cell(new ColumnHeader("schema", "Column2"), "value2"));

            var transformer = new IdentityRowTransformer();

            var expected = new Row(row.Cells);

            // Act
            Row actual = transformer.Calculate(row);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }
    }
}

using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;
using QueryLogic.Transformers.CellTransformers;
using QueryLogic.Transformers.RowTransformers;
using Rhino.Mocks;

namespace QueryLogic.Test.Transformers.RowTransformers
{
    [TestFixture]
    internal class CellsRowTransformerTests
    {
        [Test]
        public void Calculate_ShouldReturnTransformedColumn()
        {
            // Arrage
            var row = new Row();
            var columnHeader = new ColumnHeader("schema", "column");
            var cell = new Cell(columnHeader, "value");
            row.AddCell(cell);
            var cellExpressionMock = MockRepository.GenerateStub<ICellExpression>();
            cellExpressionMock.Stub(s => s.Calculate(null)).IgnoreArguments().Return(cell);
            var cellTransformers = new List<ICellTransformer>
            {
                new CellTransformer(cellExpressionMock),
            };

            var transformer = new CellsRowTransformer(cellTransformers);

            IEnumerable<Cell> expected = row.Cells;

            // Act
            IEnumerable<Cell> actual = transformer.Calculate(row).Cells;

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;
using QueryLogic.Transformers.CellTransformers;
using Rhino.Mocks;

namespace QueryLogic.Test.Transformers.CellTransformers
{
    [TestFixture]
    public class CellTransformerTests
    {
        [Test]
        public void TransformCell_ShouldReturnCellWithValueAndName()
        {
            // Arrange
            var cell = new Cell("testSchema", "testName", "testValue");
            var expressionMock = MockRepository.GenerateMock<ICellExpression>();
            expressionMock.Stub(m => m.Calculate(null)).IgnoreArguments().Return(cell);

            var transformer = new CellTransformer(expressionMock, "resultName");

            var expected = new Cell("testSchema", "resultName", "testValue");

            // Act
            Cell actual = transformer.TransformCell(new Row(new []{cell}));

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }
    }
}

And we are ready to implement last operator: SELECT.

This is the nineteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we implemented expressions, it is high time to test them:

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    internal class GetOriginalCellExpressionTests
    {
        [Test]
        public void Calculate_ShouldReturnOriginalCell()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "value1";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetOriginalCellCellExpression(columnHeader);

            //Act
            Cell result = cellExpression.Calculate(new Row(new []{cell}));

            // Assert
            Assert.That(result, Is.EqualTo(cell));
        }

        [Test]
        public void Calculate_NullCellPassed_ShouldReturnNull()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);

            ICellExpression cellExpression = new GetOriginalCellCellExpression(columnHeader);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result.Value, Is.Null);
        }
    }
}

using System;
using System.Globalization;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    [TestFixture]
    public class GetDatePartCellExpressionTests
    {
        [Test]
        [TestCase(DatePart.Day, "1")]
        [TestCase(DatePart.Month, "2")]
        [TestCase(DatePart.Year, "2013")]
        [TestCase(DatePart.Hour, "4")]
        [TestCase(DatePart.Minute, "5")]
        [TestCase(DatePart.Second, "6")]
        public void Calculate_DatePassed_ShouldReturnCorrectPart(DatePart part, string expectedValue)
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            string cellValue = new DateTime(2013, 2, 1, 4, 5, 6).ToString(CultureInfo.InvariantCulture);
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetDatePartCellExpression(columnHeader, part);
            var expectedCell = new Cell(columnHeader, expectedValue);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        [ExpectedException(typeof(InvalidOperationException))]
        public void Calculate_NotDatePassed_ShouldThrowException()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "abcd";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetDatePartCellExpression(columnHeader, DatePart.Year);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
        }
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    [TestFixture]
    public class CatenateStringCellExpressionTests
    {
        [Test]
        public void Calculate_EmptyColumnHeaderPassed_ShouldReturnCatenationWithEmptyColumnHeader()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "value1";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new CatenateStringCellExpression(columnHeader, "aaa");
            var expectedCell = new Cell(columnHeader, "value1aaa");

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_NullValuePassed_ShouldTreatAsEmptyString()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);

            ICellExpression cellExpression = new CatenateStringCellExpression(columnHeader, "AAA");
            var expectedCell = new Cell(columnHeader, "AAA");

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        } 
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    internal class GetLengthCellExpressionTests
    {
        [Test]
        public void Calculate_EmptyColumnHeaderPassed_ShouldReturnCellLengthWithEmptyColumnHeader()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "value1";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetLengthCellExpression(columnHeader);
            var expectedCell = new Cell(columnHeader, "6");

            //Act
            Cell result = cellExpression.Calculate(new Row(new []{cell}));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_NullCellPassed_ShouldReturnNull()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);

            ICellExpression cellExpression = new GetLengthCellExpression(columnHeader);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result.Value, Is.Null);
        }
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    internal class GetSubstringCellExpressionTests
    {
        [Test]
        public void Calculate_ShouldReturnCellWithSubstringValueAndEmptyColumnHeader()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "value1";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetSubstringCellExpression(columnHeader, 0, 3);
            var expectedCell = new Cell(columnHeader, "val");

            //Act
            Cell result = cellExpression.Calculate(new Row(new []{cell}));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_NullCellPassed_ShouldReturnNull()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);

            ICellExpression cellExpression = new GetSubstringCellExpression(columnHeader, 0, 3);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result.Value, Is.Null);
        }
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    internal class GetTrimmedCellExpressionTests
    {
        [Test]
        public void Calculate_ShouldReturnCellWithTrimmedValueAndEmptyColumnHeader()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "  value1  ";
            var cell = new Cell(columnHeader, cellValue);

            ICellExpression cellExpression = new GetTrimmedCellExpression(columnHeader);
            var expectedCell = new Cell(columnHeader, "value1");

            //Act
            Cell result = cellExpression.Calculate(new Row(new []{cell}));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_NullCellPassed_ShouldReturnNull()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);

            ICellExpression cellExpression = new GetTrimmedCellExpression(columnHeader);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result.Value, Is.Null);
        }
    }
}

using Model;
using NUnit.Framework;
using QueryLogic.Expressions.CellExpressions;
using Rhino.Mocks;

namespace QueryLogic.Test.Expressions.CellExpressions
{
    internal class ChainedCellExpressionTests
    {
        [Test]
        public void Calucate_EmptyColumnHeaderPassed_ShouldReturnExpressionWithEmptyColumnHeader()
        {
            //Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            const string cellValue = "value1";
            var cell = new Cell(columnHeader, cellValue);

            var mocks = new MockRepository();
            var leftCellExpression = mocks.Stub<ICellExpression>();
            var rightCellExpression = mocks.Stub<ICellExpression>();

            var cellFromRightExpression = new Cell(new ColumnHeader("", ""), "right" + cell.Value);
            var cellFromLeftExpression = new Cell(new ColumnHeader("", ""), "left" + cellFromRightExpression.Value);
            using (mocks.Record())
            {
                rightCellExpression.Calculate(new Row(new []{cell}));
                LastCall.Return(cellFromRightExpression);
                leftCellExpression.Calculate(new Row(new[] { cellFromRightExpression }));
                LastCall.Return(cellFromLeftExpression);
            }

            var cellExpression = new ChainedCellExpression(leftCellExpression, rightCellExpression);
            var expectedCell = new Cell(new ColumnHeader("", ""), "leftrightvalue1");

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_CellIsNull_ShouldReturnNull()
        {
            //Arrange
            var mocks = new MockRepository();
            var leftCellExpression = mocks.Stub<ICellExpression>();
            var rightCellExpression = mocks.Stub<ICellExpression>();

            var columnHeader = new ColumnHeader("schema", "Column1");
            var cell = new Cell(columnHeader, null);
            var cellFromRightExpression = new Cell(columnHeader, null);
            var cellFromLeftExpression = new Cell(columnHeader, null);
            using (mocks.Record())
            {
                rightCellExpression.Calculate(new Row(new[] { cell }));
                LastCall.Return(cellFromRightExpression);
                leftCellExpression.Calculate(new Row(new[] { cellFromRightExpression }));
                LastCall.Return(cellFromLeftExpression);
            }

            var cellExpression = new ChainedCellExpression(leftCellExpression, rightCellExpression);

            //Act
            Cell result = cellExpression.Calculate(new Row(new[] { cell }));

            // Assert
            Assert.That(result.Value, Is.Null);
        }
    }
}

Having expressions working we can use them in transformers. This is the topic of the next part of this series.

This is the eighteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we saw tests for aggregates. Today we are going to implement expressions.

We can compare columns with different value types: numbers, dates, strings. However, it would be useful if we could first transform values in some manner, e.g., extract year part from date. This is the purpose of expressions. We already saw simple expressions working on rows when we were implementing comparing operators. Now let’s implement expressions working on columns:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public interface ICellExpression
    {
        ColumnHeader Source { get; }
        Cell Calculate(Row row);
    }
}

We can see that expression works on row and source column. Let’s implement identity expression:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetOriginalCellCellExpression : ICellExpression
    {
        public ColumnHeader Source { get; private set; }

        public GetOriginalCellCellExpression(ColumnHeader source)
        {
            Source = source;
        }
        public Cell Calculate(Row row)
        {
            var cell = row.GetCell(Source);
            return new Cell(cell.ColumnHeader, cell.Value);
        }

        protected bool Equals(GetOriginalCellCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetOriginalCellCellExpression) obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

Now let’s implement first expression doing something more sophisticated:

namespace QueryLogic.Expressions.CellExpressions
{
    public enum DatePart
    {
        Year,
        Month,
        Day,
        Hour,
        Minute,
        Second
    }
}

using System;
using System.Globalization;
using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetDatePartCellExpression : ICellExpression
    {
        private readonly DatePart _part;

        public GetDatePartCellExpression(ColumnHeader source, DatePart part)
        {
            _part = part;
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            if (cell.GetTypeCode() != TypeCode.DateTime)
            {
                throw new InvalidOperationException("Value is not date");
            }

            DateTime time = Convert.ToDateTime(cell);
            string value;
            switch (_part)
            {
                case DatePart.Year:
                    value = time.Year.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Month:
                    value = time.Month.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Day:
                    value = time.Day.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Hour:
                    value = time.Hour.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Minute:
                    value = time.Minute.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Second:
                    value = time.Second.ToString(CultureInfo.InvariantCulture);
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }

            return new Cell(cell.ColumnHeader, value);
        }

        protected bool Equals(GetDatePartCellExpression other)
        {
            return _part == other._part && Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetDatePartCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((int)_part * 397) ^ (Source != null ? Source.GetHashCode() : 0);
            }
        }
    }
}

We first extract cell and then calculate date part. In this manner we can implement other expressions:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class CatenateStringCellExpression : ICellExpression
    {
        private readonly string _suffix;

        public CatenateStringCellExpression(ColumnHeader source, string suffix)
        {
            _suffix = suffix;
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);

            return new Cell(cell.ColumnHeader, (cell.Value ?? "") + _suffix);
        }

        protected bool Equals(CatenateStringCellExpression other)
        {
            return string.Equals(_suffix, other._suffix) && Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((CatenateStringCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((_suffix != null ? _suffix.GetHashCode() : 0) * 397) ^ (Source != null ? Source.GetHashCode() : 0);
            }
        }
    }
}

using System.Globalization;
using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetLengthCellExpression : ICellExpression
    {
        public GetLengthCellExpression(ColumnHeader source)
        {
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Length.ToString(CultureInfo.InvariantCulture));
        }

        protected bool Equals(GetLengthCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetLengthCellExpression)obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetSubstringCellExpression : ICellExpression
    {
        public GetSubstringCellExpression(ColumnHeader source, int startIndex, int length)
        {
            Source = source;
            StartIndex = startIndex;
            Length = length;
        }

        public Cell Calculate(Row row)
        {
            var cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Substring(StartIndex, Length));
        }

        public int Length { get; private set; }

        protected bool Equals(GetSubstringCellExpression other)
        {
            return Length == other.Length && Equals(Source, other.Source) && StartIndex == other.StartIndex;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;
            return Equals((GetSubstringCellExpression) obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = Length;
                hashCode = (hashCode*397) ^ (Source != null ? Source.GetHashCode() : 0);
                hashCode = (hashCode*397) ^ StartIndex;
                return hashCode;
            }
        }

        public ColumnHeader Source { get; private set; }
        public int StartIndex { get; private set; }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetTrimmedCellExpression : ICellExpression
    {
        public GetTrimmedCellExpression(ColumnHeader source)
        {
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Trim());
        }

        protected bool Equals(GetTrimmedCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetTrimmedCellExpression)obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

Since we might want to chain expressions, let’s implement this;

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class ChainedCellExpression : ICellExpression
    {
        public ChainedCellExpression(ICellExpression leftCellExpression, ICellExpression rightCellExpression)
        {
            LeftCellExpression = leftCellExpression;
            RightCellExpression = rightCellExpression;
        }

        public ICellExpression LeftCellExpression { get; private set; }

        public ICellExpression RightCellExpression { get; private set; }

        public ColumnHeader Source
        {
            get
            {
                return LeftCellExpression.Source;
            }
        }

        public Cell Calculate(Row row)
        {
            return LeftCellExpression.Calculate(new Row(new[] { RightCellExpression.Calculate(row) }));
        }

        protected bool Equals(ChainedCellExpression other)
        {
            return Equals(Source, other.Source) && Equals(LeftCellExpression, other.LeftCellExpression) &&
                   Equals(RightCellExpression, other.RightCellExpression);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((ChainedCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (Source != null ? Source.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (LeftCellExpression != null ? LeftCellExpression.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (RightCellExpression != null ? RightCellExpression.GetHashCode() : 0);
                return hashCode;
            }
        }
    }
}

And this is it. Next time we will test expressions.

This is the seventeenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we implemented aggregates, today we test them. Here are the snippets:

using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    internal class CountAggregateTests
    {
        [Test]
        public void Calculate_NotNullValuesPassed_ShouldReturnCount()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var expectedColumnHeader = new ColumnHeader("", "");
            var expectedCell = new Cell(expectedColumnHeader, "3");
            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(expectedColumnHeader));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnZero()
        {
            // Arrange
            var relation = new Relation();

            var expectedColumnHeader = new ColumnHeader("", "");
            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(expectedColumnHeader));
            var expectedCell = new Cell(expectedColumnHeader, "0");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }


        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithNameSet()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var expectedColumnHeader = new ColumnHeader("", "count");
            var expectedCell = new Cell(expectedColumnHeader, "1");
            var aggregate = new CountAggregate(new GetOriginalCellCellExpression(expectedColumnHeader), "count");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result, Is.EqualTo(expectedCell));
        }
    }
}

using System;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    public class CountColumnAggregateTests
    {
        [Test]
        public void Calculate_NotNullValuesPassed_ShouldReturnCount()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new CountColumnAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "3");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ValuesWithNullPassed_ShouldReturnNonNullCount()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "14"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new CountColumnAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "2");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_NullsPassed_ShouldReturnValueZero()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new CountColumnAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "0");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnValueZero()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");

            var relation = new Relation();

            var aggregate = new CountColumnAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "0");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithSetName()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new CountColumnAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "count")), "count");
            var expectedCell = new Cell(new ColumnHeader("", "count"), "1");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }
    }
}

using System;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    internal class AverageAggregateTests
    {
        [Test]
        public void Calculate_NotNullDoublesPassed_ShouldReturnAverageValue()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "8");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_DoublesWithNullPassed_ShouldReturnAverageValue()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "14"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "12");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_NullsPassed_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");

            var relation = new Relation();

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calculate_ColumnTypeNotDouble_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "abc"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(columnHeader));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }

        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithSetName()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "avg")), "avg");
            var expectedCell = new Cell(new ColumnHeader("", "avg"), "13");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ManyTimesCalled_ShouldReturnCorrectValueForEachRelation()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "13");

            // Act
            Cell firstResult = aggregate.Calculate(relation);
            Cell secondResult = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(firstResult), Is.EqualTo(Convert.ToDouble(expectedCell)));
            Assert.That(Convert.ToDouble(secondResult), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ManyTimesCalled_ShouldReturnCorrectValueForEachInvocation()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "13")
                })
            });

            var secondRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "11")
                })
            });

            var thirdRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, null)
                })
            });

            var aggregate = new AverageAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var firstExpectedCell = new Cell(new ColumnHeader("", ""), "13");
            var secondExpectedCell = new Cell(new ColumnHeader("", ""), "11");
            var thirdExpectedCell = new Cell(new ColumnHeader("", ""), null);

            // Act
            Cell firstResult = aggregate.Calculate(firstRelation);
            Cell secondResult = aggregate.Calculate(secondRelation);
            Cell thirdResult = aggregate.Calculate(thirdRelation);

            // Assert
            Assert.That(Convert.ToDouble(firstResult), Is.EqualTo(Convert.ToDouble(firstExpectedCell)));
            Assert.That(Convert.ToDouble(secondResult), Is.EqualTo(Convert.ToDouble(secondExpectedCell)));
            Assert.That(thirdResult, Is.EqualTo(thirdExpectedCell));
        }
    }
}

using System;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    internal class MaximumAggregateTests
    {
        [Test]
        public void Calculate_NotNullDoublesPassed_ShouldReturnMaximumDouble()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "13");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_DoublesWithNullPassed_ShouldReturnMaximumDouble()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "10");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_NullsPassed_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        public void Calculate_NotNullDatesPassed_ShouldReturnMaximumDate()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "2003-12-23"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "2003-12-21"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "2003-12-27");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDateTime(result), Is.EqualTo(Convert.ToDateTime(expectedCell)));
        }


        [Test]
        public void Calculate_DatesWithNullPassed_ShouldReturnMaximumDate()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "2003-12-23"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "2003-12-27");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDateTime(result), Is.EqualTo(Convert.ToDateTime(expectedCell)));
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");

            var relation = new Relation();

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calculate_ColumnTypeOtherThanDateTimeOrDouble_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "abc"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calculate_CellsWithMixedColumnType_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "123"));
            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }

        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithNameSet()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "max")), "max");
            var expectedCell = new Cell(new ColumnHeader("", "max"), "13");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ManyTimesCalled_ShouldReturnCorrectValueForEachInvocation()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "13")
                }),
                new Row(new[]
                {
                    new Cell(columnHeader, "12")
                })
            });

            var secondRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "11")
                }),
                new Row(new[]
                {
                    new Cell(columnHeader, "12")
                })
            });

            var thirdRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, null)
                })
            });

            var aggregate = new MaximumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var firstExpectedCell = new Cell(new ColumnHeader("", ""), "13");
            var secondExpectedCell = new Cell(new ColumnHeader("", ""), "12");
            var thirdExpectedCell = new Cell(new ColumnHeader("", ""), null);

            // Act
            Cell firstResult = aggregate.Calculate(firstRelation);
            Cell secondResult = aggregate.Calculate(secondRelation);
            Cell thirdResult = aggregate.Calculate(thirdRelation);

            // Assert
            Assert.That(Convert.ToDouble(firstResult), Is.EqualTo(Convert.ToDouble(firstExpectedCell)));
            Assert.That(Convert.ToDouble(secondResult), Is.EqualTo(Convert.ToDouble(secondExpectedCell)));
            Assert.That(thirdResult, Is.EqualTo(thirdExpectedCell));
        }
    }
}

using System;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    internal class MinimumAggregateTests
    {
        [Test]
        public void Calculate_NotNullDoublesPassed_ShouldReturnMinimumDouble()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "1");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_DoublesWithNullPassed_ShouldReturnMinimumDouble()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "1");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }


        [Test]
        public void Calculate_NullsPassed_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        public void Calculate_NotNullDatesPassed_ShouldReturnMinimumDate()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "2003-12-23"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "2003-12-21"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "2003-12-21");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDateTime(result), Is.EqualTo(Convert.ToDateTime(expectedCell)));
        }

        [Test]
        public void Calculate_DatesWithNullPassed_ShouldReturnMinimumDate()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "2003-12-21"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "2003-12-21");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDateTime(result), Is.EqualTo(Convert.ToDateTime(expectedCell)));
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");

            var relation = new Relation();

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calcluate_ColumnTypeDifferentThanDateTimeOrDouble_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "abc"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calculate_CellsWithMixedColumnType_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "123"));
            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "2003-12-27"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }

        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithNameSet()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "min")), "min");
            var expectedCell = new Cell(new ColumnHeader("", "min"), "13");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ManyTimesCalled_ShouldReturnCorrectValueForEachInvocation()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "13")
                }),
                new Row(new[]
                {
                    new Cell(columnHeader, "12")
                })
            });

            var secondRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, "13")
                }),
                new Row(new[]
                {
                    new Cell(columnHeader, "14")
                })
            });

            var thirdRelation = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell(columnHeader, null)
                })
            });

            var aggregate = new MinimumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var firstExpectedCell = new Cell(new ColumnHeader("", ""), "12");
            var secondExpectedCell = new Cell(new ColumnHeader("", ""), "13");
            var thirdExpectedCell = new Cell(new ColumnHeader("", ""), null);

            // Act
            Cell firstResult = aggregate.Calculate(firstRelation);
            Cell secondResult = aggregate.Calculate(secondRelation);
            Cell thirdResult = aggregate.Calculate(thirdRelation);

            // Assert
            Assert.That(Convert.ToDouble(firstResult), Is.EqualTo(Convert.ToDouble(firstExpectedCell)));
            Assert.That(Convert.ToDouble(secondResult), Is.EqualTo(Convert.ToDouble(secondExpectedCell)));
            Assert.That(thirdResult, Is.EqualTo(thirdExpectedCell));
        }
    }
}

using System;
using Model;
using NUnit.Framework;
using QueryLogic.Aggregates;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Test.Aggregates
{
    internal class SumAggregateTests
    {
        [Test]
        public void Calculate_NotNullDoublesPassed_ShouldReturnSum()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "24");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_DoublesAndNullPassed_ShouldReturnSum()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, "1"));

            var thirdRow = new Row();
            thirdRow.AddCell(new Cell(columnHeader, "10"));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);
            relation.AddRow(thirdRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "11");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }


        [Test]
        public void Calculate_NullsPassed_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, null));

            var secondRow = new Row();
            secondRow.AddCell(new Cell(columnHeader, null));

            var relation = new Relation();

            relation.AddRow(firstRow);
            relation.AddRow(secondRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        public void Calculate_EmptyRelation_ShouldReturnNull()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");

            var relation = new Relation();

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(result.Value, Is.Null);
        }

        [Test]
        [ExpectedException(typeof(InvalidCellTypeException))]
        public void Calculate_TypeNotDouble_ShouldThrowInvalidCellTypeException()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "abc"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));

            // Act
            // ReSharper disable once UnusedVariable
            Cell result = aggregate.Calculate(relation);
        }


        [Test]
        public void Calculate_AliasPassed_ShouldReturnCellWithNameSet()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "sum")), "sum");
            var expectedCell = new Cell(new ColumnHeader("", "sum"), "13");

            // Act
            Cell result = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(result), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }

        [Test]
        public void Calculate_ManyTimesCalled_ShouldReturnCorrectValueForEachInvocation()
        {
            // Arrange
            var columnHeader = new ColumnHeader("schema", "Column1");
            var firstRow = new Row();
            firstRow.AddCell(new Cell(columnHeader, "13"));

            var relation = new Relation();

            relation.AddRow(firstRow);

            var aggregate = new SumAggregate(new GetOriginalCellCellExpression(columnHeader), new GetOriginalCellCellExpression(new ColumnHeader("", "")));
            var expectedCell = new Cell(new ColumnHeader("", ""), "13");

            // Act
            Cell firstResult = aggregate.Calculate(relation);
            Cell secondResult = aggregate.Calculate(relation);

            // Assert
            Assert.That(Convert.ToDouble(firstResult), Is.EqualTo(Convert.ToDouble(expectedCell)));
            Assert.That(Convert.ToDouble(secondResult), Is.EqualTo(Convert.ToDouble(expectedCell)));
        }
    }
}

That’s all. In the next part we will implement various functions for dates, strings, etc.

This is the sixteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Today we implement various aggregates. Let’s start with the interface:

using Model;

namespace QueryLogic.Aggregates
{
    public interface IAggregate
    {
        Cell Calculate(Relation relation);
    }
}

We can see that the aggregate returns one cell for whole relation. You might wonder why is it implemented this way but it will be clear when we come to selection. And now the implementations, we begin with simple COUNT working on the whole relation:

using System.Globalization;
using System.Linq;
using Model;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class CountAggregate : IAggregate
    {
        private readonly ICellExpression _outerCellExpression;
        private readonly ColumnHeader _resultColumn;

        public CountAggregate(ICellExpression outerCellExpression, string alias = "")
        {
            _outerCellExpression = outerCellExpression;
            _resultColumn = new ColumnHeader("", alias);
        }

        public Cell Calculate(Relation relation)
        {
            return
                _outerCellExpression.Calculate(new Row
                    (new[] {new Cell(_resultColumn, relation.Rows.Count().ToString(CultureInfo.InvariantCulture))}));
        }

        protected bool Equals(CountAggregate other)
        {
            return Equals(_outerCellExpression, other._outerCellExpression) && Equals(_resultColumn, other._resultColumn);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;
            return Equals((CountAggregate) obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((_outerCellExpression != null ? _outerCellExpression.GetHashCode() : 0)*397) ^ (_resultColumn != null ? _resultColumn.GetHashCode() : 0);
            }
        }
    }
}

We simply return the number of rows.

In order to implement other aggregates, let’s start with base class for aggregate working on single column:

using Model;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public abstract class ColumnAggregate : IAggregate
    {
        private readonly ICellExpression _cellExpression;
        private readonly ICellExpression _outerCellExpression;

        protected ColumnAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias)
        {
            _cellExpression = cellExpression;
            _outerCellExpression = outerCellExpression;
            ResultColumn = new ColumnHeader("", alias);
        }

        public ColumnHeader ResultColumn { get; private set; }

        public Cell Calculate(Relation relation)
        {
            ResetAggregate();
            foreach (Row row in relation.Rows)
            {
                Cell resultFromRow = _cellExpression.Calculate(row);
                UpdateValue(resultFromRow);
            }

            object value = GetValue();
            return
                _outerCellExpression.Calculate(
                    new Row(new[] { new Cell(ResultColumn, value != null ? value.ToString() : null) }));
        }

        protected bool Equals(ColumnAggregate other)
        {
            return Equals(_outerCellExpression, other._outerCellExpression) && Equals(ResultColumn, other.ResultColumn);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((ColumnAggregate)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (_outerCellExpression != null ? _outerCellExpression.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (ResultColumn != null ? ResultColumn.GetHashCode() : 0);
                return hashCode;
            }
        }

        protected abstract object GetValue();

        protected abstract void UpdateValue(Cell cell);

        protected virtual void ResetAggregate()
        {
        }
    }
}

And now specific implementation for COUNT:

using Model;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class CountColumnAggregate : ColumnAggregate
    {
        private int _count;

        public CountColumnAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "")
            : base(cellExpression, outerCellExpression, alias)
        {
        }

        protected bool Equals(CountColumnAggregate other)
        {
            return base.Equals(other);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((CountColumnAggregate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override object GetValue()
        {
            return _count;
        }

        protected override void UpdateValue(Cell cell)
        {
            if (cell.Value != null)
            {
                _count++;
            }
        }

        protected override void ResetAggregate()
        {
            _count = 0;
        }
    }
}

As we can see, base class iterates over rows and the specific implementation handles logic. That’s all. Other aggregates are pretty straightforward:

using System;
using Model;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class AverageAggregate : ColumnAggregate
    {
        private bool _isInitialized;
        private int _count;
        private double _total;

        public AverageAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "")
            : base(cellExpression, outerCellExpression, alias)
        {
        }

        protected bool Equals(SumAggregate other)
        {
            return base.Equals(other);
        }

        protected override void ResetAggregate()
        {
            base.ResetAggregate();
            _total = 0;
            _count = 0;
            _isInitialized = false;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((AverageAggregate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override void UpdateValue(Cell cell)
        {
            if (cell.Value == null)
            {
                return;
            }

            UpdateValueOrThrow(cell);
            _isInitialized = true;
        }

        private void UpdateValueOrThrow(Cell cell)
        {
            ThrowIfNotDouble(cell);
            _total += Convert.ToDouble(cell.Value);
            _count++;
        }

        private static void ThrowIfNotDouble(Cell cell)
        {
            TypeCode actualTypeCode = cell.GetTypeCode();
            if (actualTypeCode != TypeCode.Double)
            {
                throw new InvalidCellTypeException(TypeCode.Double, actualTypeCode);
            }
        }

        protected override object GetValue()
        {
            return _isInitialized ? (object)(_total / _count) : null;
        }
    }
}<

using System;
using Model;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class MaximumAggregate : ColumnAggregate
    {
        private DateTime _currentDateTime;
        private double _currentDouble;
        private TypeCode _currentTypeCode;
        private bool _isInitialized;
        private TypeCode? _storedTypeCode;

        public MaximumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "")
            : base(cellExpression, outerCellExpression, alias)
        {
        }

        protected bool Equals(SumAggregate other)
        {
            return base.Equals(other);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((MaximumAggregate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override void UpdateValue(Cell cell)
        {
            if (cell.Value == null)
            {
                return;
            }

            _currentTypeCode = cell.GetTypeCode();
            if (_isInitialized)
            {
                UpdateValueOrThrow(cell);
            }
            else
            {
                UpdateWithFirstValueOrThrow(cell);
            }

            _isInitialized = true;
        }

        private void UpdateWithFirstValueOrThrow(Cell cell)
        {
            ThrowIfIncorrectType();

            if (_currentTypeCode == TypeCode.Double)
            {
                _currentDouble = Convert.ToDouble(cell.Value);
            }
            else if (_currentTypeCode == TypeCode.DateTime)
            {
                DateTime actualDateTime = Convert.ToDateTime(cell.Value);
                _currentDateTime = actualDateTime;
            }

            _storedTypeCode = _currentTypeCode;
        }

        private void UpdateValueOrThrow(Cell cell)
        {
            ThrowIfIncorrectType();

            if (_currentTypeCode == TypeCode.Double)
            {
                _currentDouble = Math.Max(_currentDouble, Convert.ToDouble(cell.Value));
            }
            else if (_currentTypeCode == TypeCode.DateTime)
            {
                DateTime actualDateTime = Convert.ToDateTime(cell.Value);
                _currentDateTime = _currentDateTime > actualDateTime ? _currentDateTime : actualDateTime;
            }
        }

        private void ThrowIfIncorrectType()
        {
            if ((_currentTypeCode != TypeCode.Double && _currentTypeCode != TypeCode.DateTime)
                || (_storedTypeCode != null && _currentTypeCode != _storedTypeCode))
            {
                throw new InvalidCellTypeException(_currentTypeCode);
            }
        }

        protected override object GetValue()
        {
            return _isInitialized
                ? (_currentTypeCode == TypeCode.Double
                    ? (object)_currentDouble
                    : _currentDateTime)
                : null;
        }

        protected override void ResetAggregate()
        {
            base.ResetAggregate();
            _isInitialized = false;
            _storedTypeCode = null;
        }
    }
}

using System;
using Model;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class MinimumAggregate : ColumnAggregate
    {
        private DateTime _currentDateTime;
        private double _currentDouble;
        private TypeCode _currentTypeCode;
        private bool _isInitialized;
        private TypeCode? _storedTypeCode;

        public MinimumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "")
            : base(cellExpression, outerCellExpression, alias)
        {
        }

        protected bool Equals(SumAggregate other)
        {
            return base.Equals(other);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((MinimumAggregate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override void UpdateValue(Cell cell)
        {
            if (cell.Value == null)
            {
                return;
            }

            _currentTypeCode = cell.GetTypeCode();
            if (_isInitialized)
            {
                UpdateValueOrThrow(cell);
            }
            else
            {
                UpdateWithFirstValueOrThrow(cell);
            }

            _isInitialized = true;
        }

        private void UpdateWithFirstValueOrThrow(Cell cell)
        {
            ThrowIfIncorrectType();

            if (_currentTypeCode == TypeCode.Double)
            {
                _currentDouble = Convert.ToDouble(cell.Value);
            }
            else if (_currentTypeCode == TypeCode.DateTime)
            {
                DateTime actualDateTime = Convert.ToDateTime(cell.Value);
                _currentDateTime = actualDateTime;
            }

            _storedTypeCode = _currentTypeCode;
        }

        private void UpdateValueOrThrow(Cell cell)
        {
            ThrowIfIncorrectType();

            if (_currentTypeCode == TypeCode.Double)
            {
                _currentDouble = Math.Min(_currentDouble, Convert.ToDouble(cell.Value));
            }
            else if (_currentTypeCode == TypeCode.DateTime)
            {
                DateTime actualDateTime = Convert.ToDateTime(cell.Value);
                _currentDateTime = _currentDateTime < actualDateTime ? _currentDateTime : actualDateTime;
            }
        }

        private void ThrowIfIncorrectType()
        {
            if ((_currentTypeCode != TypeCode.Double && _currentTypeCode != TypeCode.DateTime)
                || (_storedTypeCode != null && _currentTypeCode != _storedTypeCode))
            {
                throw new InvalidCellTypeException(_currentTypeCode);
            }
        }

        protected override object GetValue()
        {
            return _isInitialized
                ? (_currentTypeCode == TypeCode.Double
                    ? (object)_currentDouble
                    : _currentDateTime)
                : null;
        }

        protected override void ResetAggregate()
        {
            base.ResetAggregate();
            _isInitialized = false;
            _storedTypeCode = null;
        }
    }
}

using System;
using Model;
using QueryLogic.Exceptions;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Aggregates
{
    public class SumAggregate : ColumnAggregate
    {
        private bool _isInitialized;
        private double _total;

        public SumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "")
            : base(cellExpression, outerCellExpression, alias)
        {
        }

        protected bool Equals(SumAggregate other)
        {
            return base.Equals(other);
        }

        protected override void ResetAggregate()
        {
            base.ResetAggregate();
            _total = 0;
            _isInitialized = false;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((SumAggregate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override void UpdateValue(Cell cell)
        {
            if (cell.Value == null)
            {
                return;
            }

            UpdateValueOrThrow(cell);
            _isInitialized = true;
        }

        private void UpdateValueOrThrow(Cell cell)
        {
            ThrowIfNotDouble(cell);
            _total += Convert.ToDouble(cell.Value);
        }

        private static void ThrowIfNotDouble(Cell cell)
        {
            TypeCode actualTypeCode = cell.GetTypeCode();
            if (actualTypeCode != TypeCode.Double)
            {
                throw new InvalidCellTypeException(TypeCode.Double, actualTypeCode);
            }
        }

        protected override object GetValue()
        {
            return _isInitialized ? (object)_total : null;
        }
    }
}

And that’s all.

This is the fifteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we implemented grouping, today we focus on ordering. These two operations are very similar.

First, we need to know which column we use for sorting and the order (ascending/descending):

using Model;

namespace QueryLogic.Ordering
{
    public class ColumnOrdering
    {
        public ColumnOrdering(ColumnHeader column, OrderDirection direction = OrderDirection.Ascending)
        {
            Column = column;
            Direction = direction;
        }

        public ColumnHeader Column { get; private set; }
        public OrderDirection Direction { get; private set; }

        protected bool Equals(ColumnOrdering other)
        {
            return Equals(Column, other.Column) && Direction == other.Direction;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((ColumnOrdering)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((Column != null ? Column.GetHashCode() : 0) * 397) ^ (int)Direction;
            }
        }
    }
}

namespace QueryLogic.Ordering
{
    public enum OrderDirection
    {
        Ascending,
        Descending
    }
}

And now comes the operator:

using System;
using System.Collections.Generic;
using System.Linq;
using Model;

namespace QueryLogic.Ordering
{
    public class OrderBy
    {
        public OrderBy(IEnumerable<ColumnOrdering> columnOrderings, int? skipCount = null,
            int? fetchCount = null)
        {
            ColumnOrderings = columnOrderings;
            SkipCount = skipCount;
            FetchCount = fetchCount;
        }

        public IEnumerable<ColumnOrdering> ColumnOrderings { get; private set; }
        public int? FetchCount { get; private set; }
        public int? SkipCount { get; private set; }

        protected bool Equals(OrderBy other)
        {
            return Equals(ColumnOrderings, other.ColumnOrderings) && FetchCount == other.FetchCount &&
                   SkipCount == other.SkipCount;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((OrderBy)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (ColumnOrderings != null ? ColumnOrderings.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ FetchCount.GetHashCode();
                hashCode = (hashCode * 397) ^ SkipCount.GetHashCode();
                return hashCode;
            }
        }

        public Relation OrderRelation(Relation relation)
        {
            IEnumerable<IEnumerable<Row>> rowListLists = new List<IEnumerable<Row>>
            {
                relation.Rows
            };

            foreach (ColumnOrdering columnOrdering in ColumnOrderings)
            {
                ColumnOrdering ordering = columnOrdering;
                rowListLists =
                    rowListLists.SelectMany(rowList => GroupByRowList(rowList, ordering));
            }

            IEnumerable<Row> resultRows = rowListLists.SelectMany(x => x).Skip(SkipCount ?? 0);
            return new Relation(FetchCount != null ? resultRows.Take(FetchCount.Value) : resultRows);
        }

        private static IEnumerable<IGrouping<string, Row>> GroupByRowList(IEnumerable<Row> rowList,
            ColumnOrdering columnOrdering)
        {
            return OrderRowList(rowList, columnOrdering).GroupBy(GetCellValue(columnOrdering));
        }

        private static IEnumerable<Row> OrderRowList(IEnumerable<Row> rowList, ColumnOrdering columnOrdering)
        {
            return columnOrdering.Direction == OrderDirection.Ascending
                ? rowList.OrderBy(GetCellValue(columnOrdering))
                : rowList.OrderByDescending(GetCellValue(columnOrdering));
        }

        private static Func<Row, string> GetCellValue(ColumnOrdering columnOrdering)
        {
            return row => row.GetCellValue(columnOrdering.Column);
        }
    }
}

We first group rows and then sort them. And here are the tests:

using Model;
using NUnit.Framework;
using QueryLogic.Ordering;

namespace QueryLogic.Test.Ordering
{
    [TestFixture]
    public class OrderByTests
    {
        [Test]
        public void CreateRelation_DescendingColumnPassed_ShouldOrderWithOneColumnDescending()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("schema", "A")),
                new ColumnOrdering(new ColumnHeader("schema", "B"), OrderDirection.Descending)
            });

            var expected = new Relation(new[] { secondRow, thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_FetchCountPassed_ShouldFetchRows()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) }, fetchCount: 1);

            var expected = new Relation(new[] { secondRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_ManyColumnsPassed_ShouldOrderByManyColumns()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("schema", "A")),
                new ColumnOrdering(new ColumnHeader("schema", "B"))
            });

            var expected = new Relation(new[] { thirdRow, secondRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_OneColumnPassed_ShouldOrderByOneColumn()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) });

            var expected = new Relation(new[] { secondRow, thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_SkipCountPassed_ShouldSkipRows()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) }, 1);

            var expected = new Relation(new[] { thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }
    }
}

This is the fourteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Today we implement grouping. Since we can group in multiple ways, we need to have grouping sets:

using System.Collections.Generic;
using System.Linq;
using Model;

namespace QueryLogic.Grouping
{
    public class GroupingSet
    {
        public GroupingSet(IEnumerable<ColumnHeader> columns)
        {
            Columns = columns;
        }

        public IEnumerable<ColumnHeader> Columns { get; private set; }

        protected bool Equals(GroupingSet other)
        {
            return Columns.SequenceEqual(other.Columns);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GroupingSet)obj);
        }

        public override int GetHashCode()
        {
            return (Columns != null ? Columns.GetHashCode() : 0);
        }
    }
}

Grouping set is just a set of columns which we group by. Next comes the operator:

using System.Collections.Generic;
using System.Linq;
using Model;
using QueryLogic.RelationProviding;

namespace QueryLogic.Grouping
{
    public class GroupBy
    {
        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<GroupingSet> columnSets)
        {
            SourceRelationProvider = sourceRelationProvider;
            ColumnSets = columnSets;
        }

        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<ColumnHeader> columns)
            : this(sourceRelationProvider, new[] { new GroupingSet(columns) })
        {
        }

        public IRelationProvider SourceRelationProvider { get; private set; }
        public IEnumerable<GroupingSet> ColumnSets { get; private set; }

        protected bool Equals(GroupBy other)
        {
            return Equals(SourceRelationProvider, other.SourceRelationProvider) &&
                   ColumnSets.SequenceEqual(other.ColumnSets);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GroupBy)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((SourceRelationProvider != null ? SourceRelationProvider.GetHashCode() : 0) * 397) ^
                       (ColumnSets != null ? ColumnSets.GetHashCode() : 0);
            }
        }

        public IEnumerable<Relation> CreateRelations(Node source)
        {
            return ColumnSets.SelectMany(g => CreateRelation(g, source));
        }

        private IEnumerable<Relation> CreateRelation(GroupingSet groupingSet, Node source)
        {
            IEnumerable<IEnumerable<Row>> rowListsList = new List<IEnumerable<Row>>
            {
                SourceRelationProvider.CreateRelation(source).Rows
            };

            foreach (ColumnHeader column in groupingSet.Columns)
            {
                rowListsList = rowListsList.SelectMany(rowList => rowList.GroupBy(row => row.GetCellValue(column)));
            }

            foreach (var rowsList in rowListsList)
            {
                var result = new Relation();
                result.AddRows(rowsList);
                yield return result;
            }
        }
    }
}

And here are the tests:

using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Grouping;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Grouping
{
    [TestFixture]
    public class GroupByTests
    {
        [Test]
        public void CreateRelation_OneGroupSetPassed_ShouldReturnOneGroup()
        {
            // Arrange
            var expected = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                })
            });

            var groupBy = new GroupBy(new DummyRelationProvider(expected), new[] { new ColumnHeader("schema", "A") });

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(new[] { expected }, actual);
        }

        [Test]
        public void CreateRelations_ManyColumnsPassed_ShouldGroupByManyColumns()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow,
                secondRow,
                thirdRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source),
                new[] { new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B") });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow, secondRow}),
                new Relation(new[] {thirdRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

        [Test]
        public void CreateRelations_ManyGroupingSetsPassed_ShouldJoinResultsFromManyGroupingSets()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow,
                secondRow,
                thirdRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[]
            {
                new GroupingSet(new[] {new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B")}),
                new GroupingSet(new[] {new ColumnHeader("schema", "A")})
            });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow, secondRow}),
                new Relation(new[] {thirdRow}),
                new Relation(new[] {firstRow, firstRow, secondRow, secondRow}),
                new Relation(new[] {thirdRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

        [Test]
        public void CreateRelations_OneColumnPassed_ShouldGroupByOneColumn()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[] { new ColumnHeader("schema", "A") });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }
    }
}