Modifying the Hierarchy
The script in Listing 2 performs the initial population of the Parts_hierarchy table. What if you need to add additional records into the table? Let’s look at how to use the GetDescendant method to add new records at different levels of the hierarchy.
For example, to add a child part to the Body node (node /2/), you can use the GetDescendant method without any arguments to add the new row below Body node at node /2/1/:
INSERT Parts_hierarchy (hid, partid, partname)
select hid.GetDescendant(null, null), 25, 'left front fender'
from Parts_hierarchy
where partname = 'Body'
To add a new row as a higher
descendant node at the same level as the left front fender inserted in
the previous example, you use the GetDescendant method again, but this time passing the Hierarchyid of the existing child node as the first parameter. This specifies that the new node will follow the existing node, becoming /2/2/. There are a couple of ways to specify the Hierarchyid of the existing child node. You can retrieve it from the table as a Hierarchyid data type, or if you know the string representation of the node, you can use the Parse method. The Parse method converts a canonical string representation of a hierarchical value to Hierarchyid. Parse is also called implicitly when a conversion from a string type to Hierarchyid occurs, as in CAST (input AS hierarchyid). Parse is essentially the opposite of the ToString method.
INSERT Parts_hierarchy (hid, partid, partname)
select hid.GetDescendant(hierarchyid::Parse('/2/1/'), null), 26, 'right
front fender'
from Parts_hierarchy
where partname = 'Body'
Now, what if you need to add a new node between the two existing nodes you just added? Again, you use the GetDescendant methods, but this time, you pass it the hierarchy IDs of both existing nodes between which you want to insert the new node:
declare @child1 hierarchyid,
@child2 hierarchyid
select @child1 = hid from Parts_hierarchy where partname = 'left front fender'
select @child2 = hid from Parts_hierarchy where partname = 'right front fender'
INSERT Parts_hierarchy (hid, partid, partname)
select hid.GetDescendant(@child1, @child2), 27, 'front bumper'
from Parts_hierarchy
where partname = 'Body'
Now, let’s run a query of the Body subtree to examine the newly inserted child nodes:
select child.partid, child.partname, child.lvl,
substring(child.hid.ToString(), 1, 12) as path
from
parts_hierarchy as parent
inner join
parts_hierarchy as child
on parent.partname = 'Body'
and child.hid.IsDescendantOf(parent.hid) = 1
order by child.hid
go
partid partname lvl path
----------- ------------------------------ ------ ------------
23 Body 1 /2/
25 left front fender 2 /2/1/
27 front bumper 2 /2/1.1/
26 right front fender 2 /2/2/
Notice that the first child added (left front fender) has a node path of /2/1/, and the second row added (right front fender) has a node path of /2/2/. The new child node inserted between these two nodes (front bumper) was given a node path of /2/1.1/ so that it maintains the designated topological ordering of the nodes.
What if you need to make other
types of changes within hierarchies? For example, you might need to
move a whole subtree of parts from one part to another (that is, move a
part and all its subordinates). To move nodes or subtrees in a
hierarchy, you can use the GetReparentedValue method of the Hierarchyid data type. You invoke this method on the Hierarchyid value of the node you want to reparent and provide as inputs the value of the old parent and the value of the new parent.
Note that this method doesn’t change the Hierarchyid value for the existing node that you want to move. Instead, it returns a new Hierarchyid value that you can use to update the target node’s Hierarchyid value. Logically, the GetReparentedValue
method simply substitutes the part of the existing node’s path that
represents the old parent’s path with the new parent’s path. For
example, if the path of the existing node is /1/2/1/, the path of the old parent is /1/2/, and the path of the new parent is /2/1/3/, the GetReparentedValue method would return /2/1/3/1/.
You have to be careful, though. If the target parent node already has child nodes, the GetReparentedValue method may not produce a unique hierarchy path. If you reparent node /1/2/1/ from old parent /1/2/ to new parent /2/1/3/, and /2/1/3/ already has a child /2/1/3/1/,
you generate a duplicate value. To avoid this situation when moving a
single node from one parent to another, you should not use the GetReparentedValue method but instead use the GetDescendant method to produce a completely new value for the single node. For example, let’s assume you want to move the Flywheel part from the Transmission node to the Engine node. A sample approach is shown in Listing 3. This example uses the GetDescendant method to generate a new Hierarchyid under the Engine node following the last child node and updates the hid column for the Flywheel record to the new Hierarchyid generated.
Listing 3. Moving a Single Node in a Hierarchy
declare @newhid hierarchyid,
@maxchild hierarchyid
-- first, find the max child node under the Engine node
-- this is the node we will move the Flywheel node after
select @maxchild = max(child.hid)
from
parts_hierarchy as parent
inner join
parts_hierarchy as child
on parent.partname = 'Engine'
and child.hid.GetAncestor(1) = parent.hid
select 'Child to insert after' = @maxchild.ToString()
-- Now, generate a new descendant hid for the Engine node
-- after the max child node
select @newhid = hid.GetDescendant(@maxchild, null)
from Parts_hierarchy
where partname = 'Engine'
-- Update the hid for the Flywheel node to the new hid
update Parts_hierarchy
set hid = @newhid
where partname = 'Flywheel'
go
Child to insert after
----------------------
/1/1/6/
|
If you need to move an entire subtree within a hierarchy, you can use the GetReparentedValue method in conjunction with the GetDescendant method. For example, suppose you want to move the whole Engine subtree from its current parent node of Drivetrain to the new parent node of Car. The Car node obviously already has children. If you want to avoid conflicts, the best approach is to generate a new Hierarchyid value for the root node of the subtree. You can achieve this with the following steps:
1. | Use the GetDescendant method to produce a completely new Hierarchyid value for the root node of the subtree.
|
2. | Update the Hierarchyid value of all nodes in the subtree to the value returned by the GetReparentedValue method.
|
Because you are generating a completely new Hierarchyid value under the target parent, this new child node has no existing children, which avoids any duplicate Hierarchyid values. Listing 4 provides an example for changing the parent node of the Engine subtree from Drivetrain to Car.
Listing 4. Reparenting a Subtree in a Hierarchy
DECLARE
@old_root AS HIERARCHYID,
@new_root AS HIERARCHYID,
@new_parent_hid AS HIERARCHYID,
@max_child as hierarchyid
-- Get the hid of the new parent
select @new_parent_hid = hid
FROM dbo.parts_hierarchy
WHERE partname = 'Car'
-- Get the hid of the current root of the subnode
Select @old_root = hid
FROM dbo.parts_hierarchy
WHERE partname = 'Engine'
-- Get the max hid of child nodes of the new parent
select @max_child = MAX(hid)
FROM parts_hierarchy
WHERE hid.GetAncestor(1) = @new_parent_hid
-- get a new hid for the moving child node
-- that is after the current max child node of the new parent
SET @new_root = @new_parent_hid.GetDescendant (@max_child, null)
-- Next, reparent the moving child node and all descendants
UPDATE dbo.parts_hierarchy
SET hid = hid.GetReparentedValue(@old_root, @new_root)
WHERE hid.IsDescendantOf(@old_root) = 1
|
Now, let’s reexamine the hierarchy after the updates made in Listings 3. and 4:
SELECT
left(REPLICATE('--', lvl)
+ right('>',lvl)
+ partname, 30) AS partname,
hid.ToString() AS path
FROM Parts_hierarchy
order by hid
go
partname path
------------------------------ ------------
Car /
-->DriveTrain /1/
---->Transmission /1/2/
------>Clutch /1/2/2/
------>Gear Box /1/2/3/
-------->Reverse Gear /1/2/3/1/
-------->First Gear /1/2/3/2/
-------->Second Gear /1/2/3/3/
-------->Third Gear /1/2/3/4/
-------->Fourth Gear /1/2/3/5/
---->Axle /1/3/
---->Drive Shaft /1/4/
-->Body /2/
---->left front fender /2/1/
---->front bumper /2/1.1/
---->right front fender /2/2/
-->Frame /3/
-->Engine /4/
---->Radiator /4/1/
---->Intake Manifold /4/2/
---->Exhaust Manifold /4/3/
---->Carburetor /4/4/
------>Float Valve /4/4/1/
---->Piston /4/5/
------>Piston Rings /4/5/1/
---->Crankshaft /4/6/
---->Flywheel /4/7/
As you can see from the results, the Flywheel node is now under the Engine node, and the entire Engine subtree is now under the Car node.
