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

Let’s implement tests for joins:

using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using NUnit.Framework;
using QueryLogic.Joins.Implementation;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Joins.Implementation
{
    [TestFixture]
    public class CrossJoinTests
    {
        [Test]
        public void CreateRelation_ShouldCrossAllGuids()
        {
            // Arrange
            var row11 = new Row();
            row11.AddGuid(new GuidCell("schema1", "a", Guid.NewGuid()));
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);

            var row21 = new Row();
            row21.AddGuid(new GuidCell("schema2", "a", Guid.NewGuid()));
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);

            var expected = new List
            {
                row11.Guids.First().GuidHeader,
                row21.Guids.First().GuidHeader
            };

            // Act
            IEnumerable actual =
                new CrossJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation))
                    .CreateRelation(null).Guids;

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

        [Test]
        public void CreateRelation_ShouldCrossAllRows()
        {
            // Arrange
            var row11 = new Row();
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "c"),
                new Cell(new ColumnHeader("schema", "b"), "d")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);
            firstRelation.AddRow(row12);

            var row21 = new Row();
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var row22 = new Row();
            row22.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "f")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);
            secondRelation.AddRow(row22);

            var expectedRow1 = new Row();
            expectedRow1.AddCells(row11.Cells);
            expectedRow1.AddCells(row21.Cells);
            var expectedRow2 = new Row();
            expectedRow2.AddCells(row11.Cells);
            expectedRow2.AddCells(row22.Cells);
            var expectedRow3 = new Row();
            expectedRow3.AddCells(row12.Cells);
            expectedRow3.AddCells(row21.Cells);
            var expectedRow4 = new Row();
            expectedRow4.AddCells(row12.Cells);
            expectedRow4.AddCells(row22.Cells);
            var expected = new Relation();
            expected.AddRow(expectedRow1);
            expected.AddRow(expectedRow2);
            expected.AddRow(expectedRow3);
            expected.AddRow(expectedRow4);

            // Act
            Relation actual =
                new CrossJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation))
                    .CreateRelation(null);

            // Assert
            CollectionAssert.AreEquivalent(expected.Rows, actual.Rows);
        }
    }
}
using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Joins.Implementation;
using QueryLogic.Predicates.Simple;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Joins.Implementation
{
    [TestFixture]
    public class InnerJoinTests
    {
        [Test]
        public void CreateRelation_ShouldReturnRowsMatchingPredicates()
        {
            // Arrange
            var row11 = new Row();
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "c"),
                new Cell(new ColumnHeader("schema", "b"), "d")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);
            firstRelation.AddRow(row12);

            var row21 = new Row();
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var row22 = new Row();
            row22.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "b")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);
            secondRelation.AddRow(row22);

            var expectedRow = new Row();
            expectedRow.AddCells(row11.Cells);
            expectedRow.AddCells(row22.Cells);
            var expected = new Relation();
            expected.AddRow(expectedRow);

            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("schema", "b")),
                new GetCellRowExpression(new ColumnHeader("schema2", "e")));

            // Act
            Relation actual =
                new InnerJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation),
                    predicate).CreateRelation(null);

            // Assert
            CollectionAssert.AreEquivalent(expected.Rows, actual.Rows);
        }
    }
}
using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Joins.Implementation;
using QueryLogic.Predicates.Simple;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Joins.Implementation
{
    [TestFixture]
    internal class LeftOuterJoinTests
    {
        [Test]
        public void CreateRelation_RowsWithoutMatchPassed_ShouldReturnNotJoinedRows()
        {
            // Arrange
            var row11 = new Row();
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "c"),
                new Cell(new ColumnHeader("schema", "b"), "d")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);
            firstRelation.AddRow(row12);

            var row21 = new Row();
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "a")
            });
            var row22 = new Row();
            row22.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "e"), "f")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);
            secondRelation.AddRow(row22);

            var expectedRow1 = new Row();
            expectedRow1.AddCells(row11.Cells);
            expectedRow1.AddCells(row21.Cells);
            var expectedRow2 = new Row();
            expectedRow2.AddCells(row12.Cells);
            expectedRow2.AddCell(new Cell(new ColumnHeader("schema2", "e"), null));
            var expected = new Relation();
            expected.AddRow(expectedRow1);
            expected.AddRow(expectedRow2);

            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("schema", "a")),
                new GetCellRowExpression(new ColumnHeader("schema2", "e")));

            // Act
            Relation actual =
                new LeftOuterJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation),
                    predicate).CreateRelation(null);

            // Assert
            CollectionAssert.AreEquivalent(expected.Rows, actual.Rows);
        }
    }
}
using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Joins.Implementation;
using QueryLogic.Predicates.Simple;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Joins.Implementation
{
    [TestFixture]
    public class RightOuterJoinTests
    {
        [Test]
        public void CreateRelation_RowsWithoutMatchPassed_ShouldReturnNotJoinedRows()
        {
            // Arrange
            var row11 = new Row();
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);

            var row21 = new Row();
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "a"), "b"),
                new Cell(new ColumnHeader("schema2", "b"), "c")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);

            var expectedRow = new Row();
            expectedRow.AddCell(new Cell(new ColumnHeader("schema", "a"), null));
            expectedRow.AddCell(new Cell(new ColumnHeader("schema", "b"), null));
            expectedRow.AddCells(row21.Cells);

            var expected = new Relation();
            expected.AddRow(expectedRow);

            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("schema", "a")),
                new GetCellRowExpression(new ColumnHeader("schema2", "b")));

            // Act
            Relation actual =
                new RightOuterJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation),
                    predicate).CreateRelation(null);

            // Assert
            CollectionAssert.AreEquivalent(expected.Rows, actual.Rows);
        }
    }
}
using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Joins.Implementation;
using QueryLogic.Predicates.Simple;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Joins.Implementation
{
    [TestFixture]
    public class FullOuterJoinTests
    {
        [Test]
        public void CreateRelation_RowsWithoutMatchPassed_ShouldReturnNonJoinedRows()
        {
            // Arrange
            var row11 = new Row();
            row11.AddCells(new List
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var firstRelation = new Relation();
            firstRelation.AddRow(row11);

            var row21 = new Row();
            row21.AddCells(new List
            {
                new Cell(new ColumnHeader("schema2", "a"), "b"),
                new Cell(new ColumnHeader("schema2", "b"), "c")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);

            var expectedRow1 = new Row();
            expectedRow1.AddCell(new Cell(new ColumnHeader("schema", "a"), null));
            expectedRow1.AddCell(new Cell(new ColumnHeader("schema", "b"), null));
            expectedRow1.AddCells(row21.Cells);

            var expectedRow2 = new Row();
            expectedRow2.AddCell(new Cell(new ColumnHeader("schema2", "a"), null));
            expectedRow2.AddCell(new Cell(new ColumnHeader("schema2", "b"), null));
            expectedRow2.AddCells(row11.Cells);

            var expected = new Relation();
            expected.AddRow(expectedRow1);
            expected.AddRow(expectedRow2);

            var predicate = new EqualPredicate(new GetCellRowExpression(new ColumnHeader("schema", "a")),
                new GetCellRowExpression(new ColumnHeader("schema2", "b")));

            // Act
            Relation actual =
                new FullOuterJoin(new DummyRelationProvider(firstRelation), new DummyRelationProvider(secondRelation),
                    predicate).CreateRelation(null);

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

They are rather straightforward. Next time we are going to implement natural join.