SQLxD Part 21 — SELECT – Random IT Utensils

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;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

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);
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

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);

var expectedRow = new Row();

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.