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