SQLxD Part 12 — JOINs tests – Random IT Utensils

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<Cell>
            {
                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<Cell>
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var secondRelation = new Relation();
            secondRelation.AddRow(row21);

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

            // Act
            IEnumerable<GuidHeader> 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<Cell>
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List<Cell>
            {
                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<Cell>
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var row22 = new Row();
            row22.AddCells(new List<Cell>
            {
                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);
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

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<Cell>

{

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<Cell>

{

new Cell(new ColumnHeader("schema2", "e"), "e")

});

var secondRelation = new Relation();

secondRelation.AddRow(row21);

var expected = new List<GuidHeader>

{

row11.Guids.First().GuidHeader,

row21.Guids.First().GuidHeader

};

// Act

IEnumerable<GuidHeader> 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<Cell>

{

new Cell(new ColumnHeader("schema", "a"), "a"),

new Cell(new ColumnHeader("schema", "b"), "b")

});

var row12 = new Row();

row12.AddCells(new List<Cell>

{

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<Cell>

{

new Cell(new ColumnHeader("schema2", "e"), "e")

});

var row22 = new Row();

row22.AddCells(new List<Cell>

{

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<Cell>
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List<Cell>
            {
                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<Cell>
            {
                new Cell(new ColumnHeader("schema2", "e"), "e")
            });
            var row22 = new Row();
            row22.AddCells(new List<Cell>
            {
                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]

public class InnerJoinTests

{

[Test]

public void CreateRelation_ShouldReturnRowsMatchingPredicates()

{

// Arrange

var row11 = new Row();

row11.AddCells(new List<Cell>

{

new Cell(new ColumnHeader("schema", "a"), "a"),

new Cell(new ColumnHeader("schema", "b"), "b")

});

var row12 = new Row();

row12.AddCells(new List<Cell>

{

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<Cell>

{

new Cell(new ColumnHeader("schema2", "e"), "e")

});

var row22 = new Row();

row22.AddCells(new List<Cell>

{

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<Cell>
            {
                new Cell(new ColumnHeader("schema", "a"), "a"),
                new Cell(new ColumnHeader("schema", "b"), "b")
            });
            var row12 = new Row();
            row12.AddCells(new List<Cell>
            {
                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<Cell>
            {
                new Cell(new ColumnHeader("schema2", "e"), "a")
            });
            var row22 = new Row();
            row22.AddCells(new List<Cell>
            {
                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]

internal class LeftOuterJoinTests

{

[Test]

public void CreateRelation_RowsWithoutMatchPassed_ShouldReturnNotJoinedRows()

{

// Arrange

var row11 = new Row();

row11.AddCells(new List<Cell>

{

new Cell(new ColumnHeader("schema", "a"), "a"),

new Cell(new ColumnHeader("schema", "b"), "b")

});

var row12 = new Row();

row12.AddCells(new List<Cell>

{

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<Cell>

{

new Cell(new ColumnHeader("schema2", "e"), "a")

});

var row22 = new Row();

row22.AddCells(new List<Cell>

{

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<Cell>
            {
                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<Cell>
            {
                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 RightOuterJoinTests

{

[Test]

public void CreateRelation_RowsWithoutMatchPassed_ShouldReturnNotJoinedRows()

{

// Arrange

var row11 = new Row();

row11.AddCells(new List<Cell>

{

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<Cell>

{

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

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<Cell>

{

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<Cell>

{

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.