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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
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); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
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); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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.