public void Should_handle_GetDescendants_operation_over_larger_hierarchy()
{
// build a sample hierarchy
var hierarchyEntry = new HierarchyEntry
{
Name = "Test Hierarchy",
SchemaName = "dbo",
TableName = "test"
};
// initialize the provider
var dataProvider = new MssqlDataProvider();
var provider = new MssqlHierarchyDataProvider(dataProvider);
// generate the complex hierarchy
TestHierarchy.BuildTestHierarchy(hierarchyEntry, provider);
var rootNode = provider.GetRootNode(hierarchyEntry);
var descendants = provider.GetDescendants(hierarchyEntry, rootNode, true, true);
Assert.That(descendants.Count, Is.EqualTo(7));
Assert.That(descendants[0].LeftId, Is.EqualTo(1));
Assert.That(descendants[0].RightId, Is.EqualTo(14));
Assert.That(descendants[1].LeftId, Is.EqualTo(2));
Assert.That(descendants[1].RightId, Is.EqualTo(7));
Assert.That(descendants[2].LeftId, Is.EqualTo(3));
Assert.That(descendants[2].RightId, Is.EqualTo(4));
Assert.That(descendants[3].LeftId, Is.EqualTo(5));
Assert.That(descendants[3].RightId, Is.EqualTo(6));
Assert.That(descendants[4].LeftId, Is.EqualTo(8));
Assert.That(descendants[4].RightId, Is.EqualTo(13));
Assert.That(descendants[5].LeftId, Is.EqualTo(9));
Assert.That(descendants[5].RightId, Is.EqualTo(10));
Assert.That(descendants[6].LeftId, Is.EqualTo(11));
Assert.That(descendants[6].RightId, Is.EqualTo(12));
Assert.That(rootNode.LeftId, Is.EqualTo(1));
Assert.That(rootNode.RightId, Is.EqualTo(14));
// clean up
dataProvider.DropTable(hierarchyEntry.SchemaName, hierarchyEntry.TableName);
}
public void Should_call_Update_on_DataProvider()
{
// arrange
var hierarchyEntry = new HierarchyEntry
{
Name = "NewHierarchy",
TableName = "new_hierarchy"
};
var mockDataProvider = new Mock<IDataProvider>();
mockDataProvider
.Setup(p => p.Update(It.IsAny<string>(), It.IsAny<int>(), It.IsAny<IEnumerable<KeyValuePair<string, object>>>()))
.Returns(34);
var repository = new HierarchyEntryRepository(mockDataProvider.Object);
// act
var result = repository.Update(hierarchyEntry);
// assert
Assert.That(result, Is.EqualTo(34));
}
public void Should_call_Get_on_DataProvider()
{
// arrange
var hierarchyEntry = new HierarchyEntry
{
Id = 34,
Name = "NewHierarchy",
TableName = "new_hierarchy"
};
var mockDataProvider = new Mock<IDataProvider>();
mockDataProvider
.Setup(p => p.Get(It.IsAny<string>(), It.IsAny<int>(), It.IsAny<Func<IDataRecord, HierarchyEntry>>()))
.Returns(hierarchyEntry);
var repository = new HierarchyEntryRepository(mockDataProvider.Object);
// act
var result = repository.Get(34);
// assert
Assert.That(result.Id, Is.EqualTo(34));
}
public void Should_handle_GetAncestors_operation_over_larger_hierarchy()
{
// build a sample hierarchy
var hierarchyEntry = new HierarchyEntry
{
Name = "Test Hierarchy",
SchemaName = "dbo",
TableName = "test"
};
// initialize the provider
var dataProvider = new MssqlDataProvider();
var provider = new MssqlHierarchyDataProvider(dataProvider);
// generate the complex hierarchy
TestHierarchy.BuildTestHierarchy(hierarchyEntry, provider);
var rootNode = provider.GetRootNode(hierarchyEntry);
var primaryChildren = provider.GetChildren(hierarchyEntry, rootNode);
var primaryChild1 = primaryChildren.First();
var secondaryChildren = provider.GetChildren(hierarchyEntry, primaryChild1);
var leafChild = secondaryChildren.First();
var ancestors = provider.GetAncestors(hierarchyEntry, leafChild, true, true);
Assert.That(ancestors.Count, Is.EqualTo(3));
Assert.That(ancestors[0].LeftId, Is.EqualTo(1));
Assert.That(ancestors[0].RightId, Is.EqualTo(14));
Assert.That(ancestors[1].LeftId, Is.EqualTo(2));
Assert.That(ancestors[1].RightId, Is.EqualTo(7));
Assert.That(ancestors[2].LeftId, Is.EqualTo(leafChild.LeftId));
Assert.That(ancestors[2].RightId, Is.EqualTo(leafChild.RightId));
// clean up
dataProvider.DropTable(hierarchyEntry.SchemaName, hierarchyEntry.TableName);
}
public bool Delete(HierarchyEntry hierarchy, HierarchyNode node)
{
// can only delete leaf nodes - let consumer handle errors.
if (node.RightId != node.LeftId + 1) return false;
var deleteSql = string.Format(@"
DELETE FROM [{0}].[{1}] WHERE id = @NodeId ",
hierarchy.SchemaName,
hierarchy.TableName);
using (var conn = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand(deleteSql, conn))
{
conn.Open();
command.Parameters.AddWithValue("@NodeId", node.Id);
command.ExecuteNonQuery();
if (conn.State == ConnectionState.Open) conn.Close();
}
}
HandleGap(hierarchy, node, false);
return true;
}
public int Add(HierarchyEntry hierarchyEntry, HierarchyNode node)
{
if (!HierarchyInitialized(hierarchyEntry)) return 0;
var insertQuery = node.Id == 0
? string.Format(@"INSERT INTO [{0}].[{1}] (target_id, left_id, right_id) OUTPUT inserted.id VALUES (@TargetId, @LeftId, @RightId)", hierarchyEntry.SchemaName, hierarchyEntry.TableName)
: string.Format(@"UPDATE [{0}].[{1}] SET target_id=@TargetId, left_id=@LeftId, right_id=@RightId WHERE id=@Id", hierarchyEntry.SchemaName, hierarchyEntry.TableName);
int result = 0;
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand command = new SqlCommand(insertQuery, conn))
{
conn.Open();
command.Parameters.AddWithValue("@LeftId", node.LeftId);
command.Parameters.AddWithValue("@RightId", node.RightId);
command.Parameters.AddWithValue("@TargetId", node.TargetId);
if (node.Id == 0)
{
var rawResult = command.ExecuteScalar();
if (rawResult != null)
{
result = rawResult.CastTo<int>();
}
}
else
{
command.Parameters.AddWithValue("@Id", node.Id);
var rowsAffected = command.ExecuteNonQuery();
result = rowsAffected == 0 ? 0 : node.Id;
}
if (conn.State == ConnectionState.Open) conn.Close();
}
}
return result;
}
private void ReloadLeftRight(HierarchyEntry hierarchyEntry, HierarchyNode node)
{
var reloadedNode = GetNode(hierarchyEntry, node.Id);
node.LeftId = reloadedNode.LeftId;
node.RightId = reloadedNode.RightId;
}
/// <summary>
/// Handle the opening (insert) or closing (delete) of a gap
/// </summary>
/// <param name="hierarchyEntry"></param>
/// <param name="node"></param>
/// <param name="isInsert"></param>
private void HandleGap(HierarchyEntry hierarchyEntry, HierarchyNode node, bool isInsert)
{
var offset = (isInsert) ? 2 : -2;
var queryString = string.Format(@"
UPDATE [{0}].[{1}]
SET left_id = CASE
WHEN left_id > @LeftId
THEN left_id + @Offset
ELSE left_id END,
right_id = CASE
WHEN right_id >= @RightId
THEN right_id + @Offset
ELSE right_id END
WHERE right_id >= @RightId", hierarchyEntry.SchemaName, hierarchyEntry.TableName);
using (var conn = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand(queryString, conn))
{
conn.Open();
command.Parameters.AddWithValue("@LeftId", node.LeftId);
command.Parameters.AddWithValue("@RightId", node.RightId);
command.Parameters.AddWithValue("@Offset", offset);
command.ExecuteNonQuery();
if (conn.State == ConnectionState.Open) conn.Close();
}
}
}
private HierarchyNode GetNode(HierarchyEntry hierarchyEntry, int id)
{
var sql = string.Format("SELECT id, left_id, right_id, target_id FROM [{0}].[{1}] WHERE id = {2}", hierarchyEntry.SchemaName, hierarchyEntry.TableName, id);
HierarchyNode returnNode = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
returnNode = PopulateHierarchyNode(reader);
}
}
return returnNode;
}
public void PrepareForInsertNode(HierarchyEntry hierarchy, HierarchyNode parent)
{
HandleGap(hierarchy, parent, true);
// update parent with newly generated right_id. Could just get a new object from the repo, but don't want to kill the object itself.
ReloadLeftRight(hierarchy, parent);
}
public IList<HierarchyNode> GetDescendants(HierarchyEntry hierarchyEntry, HierarchyNode parentNode, bool orderTopDown, bool includeParent)
{
return hierarchyEntryDataProvider.GetDescendants(hierarchyEntry, parentNode, orderTopDown, includeParent);
}
public void DeleteNode(HierarchyEntry hierarchyEntry, HierarchyNode node)
{
hierarchyEntryDataProvider.Delete(hierarchyEntry, node);
}
public void Should_properly_create_and_handle_simple_hierarchy_with_basic_operations()
{
// build a sample hierarchy
var hierarchyEntry = new HierarchyEntry
{
Name = "Test Hierarchy",
SchemaName = "dbo",
TableName = "test"
};
// initialize the provider
var dataProvider = new MssqlDataProvider();
var provider = new MssqlHierarchyDataProvider(dataProvider);
// initialize the hierarchy
var originalRootNode = new HierarchyNode
{
LeftId = 1,
RightId = 2
};
var rootId = provider.Add(hierarchyEntry, originalRootNode);
// test get root node
var persistedRootNode = provider.GetRootNode(hierarchyEntry);
Assert.That(persistedRootNode.Id, Is.EqualTo(rootId));
Assert.That(persistedRootNode.LeftId, Is.EqualTo(1));
Assert.That(persistedRootNode.RightId, Is.EqualTo(2));
// then insert first primary child
var firstChild = new HierarchyNode();
firstChild.LeftId = persistedRootNode.RightId;
provider.PrepareForInsertNode(hierarchyEntry, persistedRootNode);
firstChild.RightId = firstChild.LeftId + 1;
provider.Add(hierarchyEntry, firstChild);
// verify new hierarchy
persistedRootNode = provider.GetRootNode(hierarchyEntry);
var firstChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
Assert.That(firstChildren.Count, Is.EqualTo(1));
Assert.That(firstChildren[0].Id, Is.Not.EqualTo(0));
Assert.That(firstChildren[0].LeftId, Is.EqualTo(2));
Assert.That(firstChildren[0].RightId, Is.EqualTo(3));
Assert.That(persistedRootNode.LeftId, Is.EqualTo(1));
Assert.That(persistedRootNode.RightId, Is.EqualTo(4));
// insert second primary child
var secondChild = new HierarchyNode();
secondChild.LeftId = persistedRootNode.RightId;
provider.PrepareForInsertNode(hierarchyEntry, persistedRootNode);
secondChild.RightId = secondChild.LeftId + 1;
provider.Add(hierarchyEntry, secondChild);
// verify new hierarchy
persistedRootNode = provider.GetRootNode(hierarchyEntry);
var secondChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
Assert.That(secondChildren.Count, Is.EqualTo(2));
Assert.That(secondChildren[1].Id, Is.Not.EqualTo(0));
Assert.That(secondChildren[1].LeftId, Is.EqualTo(4));
Assert.That(secondChildren[1].RightId, Is.EqualTo(5));
Assert.That(secondChildren[0].LeftId, Is.EqualTo(2));
Assert.That(secondChildren[0].RightId, Is.EqualTo(3));
Assert.That(persistedRootNode.LeftId, Is.EqualTo(1));
Assert.That(persistedRootNode.RightId, Is.EqualTo(6));
// delete a node
var deleteNode = secondChildren[1];
provider.Delete(hierarchyEntry, deleteNode);
// verify revised hierarchy
persistedRootNode = provider.GetRootNode(hierarchyEntry);
var children = provider.GetChildren(hierarchyEntry, persistedRootNode);
Assert.That(children.Count, Is.EqualTo(1));
Assert.That(children[0].Id, Is.Not.EqualTo(0));
Assert.That(children[0].LeftId, Is.EqualTo(2));
Assert.That(children[0].RightId, Is.EqualTo(3));
Assert.That(persistedRootNode.LeftId, Is.EqualTo(1));
Assert.That(persistedRootNode.RightId, Is.EqualTo(4));
// clean up
dataProvider.DropTable(hierarchyEntry.SchemaName, hierarchyEntry.TableName);
}
public List<HierarchyNode> GetChildren(HierarchyEntry hierarchy, HierarchyNode parent)
{
var children = new List<HierarchyNode>();
var sql =
string.Format(
@"SELECT id, left_id, right_id, target_id
FROM [{0}].[{1}] n
WHERE n.left_id > @ParentLeft
AND n.right_id < @ParentRight
AND NOT EXISTS (
SELECT *
FROM [{0}].[{1}] mid
WHERE mid.left_id BETWEEN @ParentLeft AND @ParentRight
AND n.left_id BETWEEN mid.left_id AND mid.right_id
AND mid.id != n.id AND mid.id != @ParentId)
ORDER BY n.left_id",
hierarchy.SchemaName, hierarchy.TableName);
using (var conn = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@ParentId", parent.Id);
command.Parameters.AddWithValue("@ParentLeft", parent.LeftId);
command.Parameters.AddWithValue("@ParentRight", parent.RightId);
conn.Open();
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while(reader.Read())
{
children.Add(PopulateHierarchyNode(reader));
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
}
return children;
}
public static void BuildTestHierarchy(HierarchyEntry hierarchyEntry, IHierarchyDataProvider provider)
{
// initialize the hierarchy
var originalRootNode = new HierarchyNode
{
LeftId = 1,
RightId = 2
};
var rootId = provider.Add(hierarchyEntry, originalRootNode);
// test get root node
var persistedRootNode = provider.GetRootNode(hierarchyEntry);
// then insert first primary child
var firstChild = new HierarchyNode();
firstChild.LeftId = persistedRootNode.RightId;
provider.PrepareForInsertNode(hierarchyEntry, persistedRootNode);
firstChild.RightId = firstChild.LeftId + 1;
provider.Add(hierarchyEntry, firstChild);
persistedRootNode = provider.GetRootNode(hierarchyEntry);
// insert second primary child
var secondChild = new HierarchyNode();
secondChild.LeftId = persistedRootNode.RightId;
provider.PrepareForInsertNode(hierarchyEntry, persistedRootNode);
secondChild.RightId = secondChild.LeftId + 1;
provider.Add(hierarchyEntry, secondChild);
// reset root node and get children
persistedRootNode = provider.GetRootNode(hierarchyEntry);
var secondChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
// insert first secondary child
var primaryChildAsParent = secondChildren[0];
var secondaryChild1 = new HierarchyNode();
secondaryChild1.LeftId = primaryChildAsParent.RightId;
provider.PrepareForInsertNode(hierarchyEntry, primaryChildAsParent);
secondaryChild1.RightId = secondaryChild1.LeftId + 1;
provider.Add(hierarchyEntry, secondaryChild1);
// reset root node and get children
persistedRootNode = provider.GetRootNode(hierarchyEntry);
secondChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
// insert second secondary child
primaryChildAsParent = secondChildren[0];
var secondaryChild2 = new HierarchyNode();
secondaryChild2.LeftId = primaryChildAsParent.RightId;
provider.PrepareForInsertNode(hierarchyEntry, primaryChildAsParent);
secondaryChild2.RightId = secondaryChild2.LeftId + 1;
provider.Add(hierarchyEntry, secondaryChild2);
// reset root node and get children of primary2
persistedRootNode = provider.GetRootNode(hierarchyEntry);
secondChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
// insert first secondary child
var primaryChild2AsParent = secondChildren[1];
var secondaryChild3 = new HierarchyNode();
secondaryChild3.LeftId = primaryChild2AsParent.RightId;
provider.PrepareForInsertNode(hierarchyEntry, primaryChild2AsParent);
secondaryChild3.RightId = secondaryChild3.LeftId + 1;
provider.Add(hierarchyEntry, secondaryChild3);
// reset root node and get children
persistedRootNode = provider.GetRootNode(hierarchyEntry);
secondChildren = provider.GetChildren(hierarchyEntry, persistedRootNode);
// insert second secondary child
primaryChild2AsParent = secondChildren[1];
var secondaryChild4 = new HierarchyNode();
secondaryChild4.LeftId = primaryChild2AsParent.RightId;
provider.PrepareForInsertNode(hierarchyEntry, primaryChild2AsParent);
secondaryChild4.RightId = secondaryChild4.LeftId + 1;
provider.Add(hierarchyEntry, secondaryChild4);
}
public HierarchyNode InitializeHierarchy(HierarchyEntry hierarchyEntry, ITarget rootTarget = null)
{
var rootNode = new HierarchyNode
{
LeftId = 1,
RightId = 2,
TargetId = rootTarget == null ? 0 : rootTarget.Id
};
// initialize the target table for the node
hierarchyEntryDataProvider.Add(hierarchyEntry, rootNode);
return rootNode;
}
public HierarchyNode GetRootNode(HierarchyEntry hierarchyEntry)
{
return hierarchyEntryDataProvider.GetRootNode(hierarchyEntry);
}
public HierarchyNode GetParent(HierarchyEntry hierarchyEntry, HierarchyNode node)
{
return hierarchyEntryDataProvider.GetParent(hierarchyEntry, node);
}
public IList<HierarchyNode> GetAncestors(HierarchyEntry hierarchyEntry, HierarchyNode node, bool orderTopDown, bool includeChild)
{
return hierarchyEntryDataProvider.GetAncestors(hierarchyEntry, node, orderTopDown, includeChild);
}
public List<HierarchyNode> GetAncestors(HierarchyEntry hierarchyEntry, HierarchyNode child, bool orderTopDown, bool includeChild)
{
var ancestors = new List<HierarchyNode>();
var orderString = (orderTopDown) ? "ASC" : "DESC";
var includeChildString = (includeChild) ? "=" : "";
var sql = string.Format(@"
SELECT id, left_id, right_id, target_id
FROM [{0}].[{1}] n
WHERE n.left_id <{3} @ChildLeft
AND n.right_id >{3} @ChildRight
ORDER BY n.left_id {2}", hierarchyEntry.SchemaName, hierarchyEntry.TableName, orderString,
includeChildString);
using (var conn = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand(sql, conn))
{
command.Parameters.AddWithValue("@ChildLeft", child.LeftId);
command.Parameters.AddWithValue("@ChildRight", child.RightId);
conn.Open();
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ancestors.Add(PopulateHierarchyNode(reader));
}
}
if (conn.State == ConnectionState.Open) conn.Close();
}
}
return ancestors;
}
public HierarchyNode PrepareForInsertNode(HierarchyEntry hierarchyEntry, HierarchyNode parentNode, HierarchyNode childNode)
{
childNode.LeftId = parentNode.RightId;
hierarchyEntryDataProvider.PrepareForInsertNode(hierarchyEntry, parentNode);
childNode.RightId = childNode.LeftId + 1;
return childNode;
}
public HierarchyNode GetParent(HierarchyEntry hierarchy, HierarchyNode child)
{
var nodeList = GetAncestors(hierarchy, child, false, false);
if (nodeList != null && nodeList.Count > 0)
return nodeList[0];
return null;
}
public IList<HierarchyNode> GetChildren(HierarchyEntry hierarchyEntry, HierarchyNode parentNode)
{
return hierarchyEntryDataProvider.GetChildren(hierarchyEntry, parentNode);
}
public HierarchyNode GetRootNode(HierarchyEntry hierarchyEntry)
{
var sql = string.Format("SELECT id, left_id, right_id, target_id FROM [{0}].[{1}] WHERE left_id = 1", hierarchyEntry.SchemaName, hierarchyEntry.TableName);
HierarchyNode returnNode = null;
using (var connection = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand(sql, connection))
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
returnNode = PopulateHierarchyNode(reader);
}
}
}
return returnNode;
}
public bool HierarchyInitialized(HierarchyEntry hierarchyEntry)
{
if (_hierarchyInitialized.ContainsKey(hierarchyEntry.Id))
{
return true;
}
var verified = true;
if (!_dataProvider.TableExists(hierarchyEntry.SchemaName, hierarchyEntry.TableName))
{
var createSql = string.Format(@"CREATE TABLE [{0}].[{1}](
[id] [int] IDENTITY(1,1) NOT NULL,
[left_id] [int] NOT NULL,
[right_id] [int] NOT NULL,
[target_id] [int] NOT NULL,
CONSTRAINT [PK_{0}_{1}] PRIMARY KEY CLUSTERED
([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]", hierarchyEntry.SchemaName, hierarchyEntry.TableName);
_dataProvider.ExecuteSql(createSql);
verified = _dataProvider.TableExists(hierarchyEntry.SchemaName, hierarchyEntry.TableName);
}
_hierarchyInitialized.Add(hierarchyEntry.Id, hierarchyEntry.TableName);
return verified;
}
public void Should_handle_GetParent_operation_over_larger_hierarchy()
{
// build a sample hierarchy
var hierarchyEntry = new HierarchyEntry
{
Name = "Test Hierarchy",
SchemaName = "dbo",
TableName = "test"
};
// initialize the provider
var dataProvider = new MssqlDataProvider();
var provider = new MssqlHierarchyDataProvider(dataProvider);
// generate the complex hierarchy
TestHierarchy.BuildTestHierarchy(hierarchyEntry, provider);
var rootNode = provider.GetRootNode(hierarchyEntry);
var primaryChildren = provider.GetChildren(hierarchyEntry, rootNode);
var primaryChild1 = primaryChildren.First();
var secondaryChildren = provider.GetChildren(hierarchyEntry, primaryChild1);
var leafChild = secondaryChildren.First();
var parent = provider.GetParent(hierarchyEntry, leafChild);
Assert.That(parent.LeftId, Is.LessThan(leafChild.LeftId));
Assert.That(parent.RightId, Is.GreaterThan(leafChild.RightId));
// clean up
dataProvider.DropTable(hierarchyEntry.SchemaName, hierarchyEntry.TableName);
}
public HierarchyNode InsertNode(HierarchyEntry hierarchyEntry, HierarchyNode parentNode, ITarget childTarget)
{
var childNode = new HierarchyNode { TargetId = childTarget.Id };
childNode = PrepareForInsertNode(hierarchyEntry, parentNode, childNode);
hierarchyEntryDataProvider.Add(hierarchyEntry, childNode);
return childNode;
}
