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();
            Aggregates = new List();
        }

        public IList Aggregates { get; private set; }
        public IList 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
            {
                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
            {
                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
            {
                new ColumnHeader("", ""),
            };

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

            var expectedRows = new List { firstExpectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable actualColumns = actualRelation.Columns;
            IEnumerable 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
            {
                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
            {
                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
            {
                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 { firstExpectedRow, secondExpectedRow, thirdExpectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable actualColumns = actualRelation.Columns;
            IEnumerable 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
            {
                new Row(new[]
                {
                    cellWithValue1
                }),
                new Row(new[]
                {
                    cellWithValue2
                })
            };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable 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 { expectedRow };

            // Act
            Relation actualRelation = select.CreateRelation(null);
            IEnumerable 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.