Vikram Kamath

June 20, 2005

Deleting User/Group Hierarchies in SQL

Filed under: General — Vikram @ 21:40

Hello to all. I am back after a long break from blogging, or should I say I could not think of anything to blog about.

But this time I have something good to blog about. I have never contemplated about blogging about technical stuffs. But this time it’s different. This particular post can be considered as my first technical post since I started blogging.

This post will be meaningless if you have not read James’s post on User/Group Hierarchies in SQL, where he mentioned how he managed to link children with their respective grand parents and great grand parent and so on and so forth, in a proper hierarchy. So before you read further here, I reckon you should check out what James achieved.
………..
………………
……………………
…………………………

I would now assume that you are back after going through the User/Group Hierarchies in SQL posted by James, and continue.

I was given the task to do the reverse of what James had achieved i.e. when a parent child relation is removed; we need to remove the corresponding links up the hierarchy of the parent and down the hierarchy of the child. At first I thought I was simple. But as I started drawing sets of users and groups on paper and lines representing the memberships just as James did, I realised it was not a simple affair. I just got lost is the forest of trees.

After spending an enormous amount of time scribbling on paper, typing queries on the laptop and swearing at myself after each failed approach, I came with this simple approach (yeah yeah!! It seems simple to me now) which I think is the solution. I am sure James would let me know if this is THE solution or there is a better one. I would not be surprised if there is one.

I had to make some minor changes to the schema that James created by adding a column CalculatedParentID in the Members table, which specified the mediator between the parent and the child relation. I also added a column MemberID which uniquely identified the rows in the table, just for the sake of simplicity. So the insert statement changed as under:

declare @rowcount int
set @rowcount = 1
while @rowcount > 0
begin
insert into dbo.Members (ParentID, ChildID, CalculatedParentID, Calculated)
select distinct p.UserID, c.UserID, cm.ParentID, 1
from dbo.Members pm
join dbo.Users p ON p.UserID = pm.ParentID
join dbo.Members cm ON cm.ParentID = pm.ChildID
join dbo.Users c ON c.UserID = cm.ChildID
where not exists
(
select * from dbo.Members
where ParentID = p.UserID
and ChildID = c.UserID
and CalculatedParentID = cm.ParentID
)
select @rowcount = @@rowcount
end

As a test case I created the following parent child hierarchy as shown in the Figure 1 (Test Case)using the above approach.

The black hard lines indicate the direct relationships. And the dotted lines indicate the calculated links and colour coded accordingly with respect to the mediator in the relationship.

The task was to delete all the relationships calculated relationships, when say for example, the link between ChildGroup and the ParentGroup1 is broken.

So the theoretical approach that I took was as under. This is what I said to myself when I broke the relationship:

Case 1: Give me all the calculated links where I am the child (User1) and check whether my CalculatedParent(s) (ChildGroup) are the Children of my parents in the calculated relationship (ParentGroup1). If there are no such relations then this link is invalid.

Case 2: Give me all the calculated links where my (ChidGroup) parents in the calculated relation (GrandParent1) are the parents of my CalculatedParent(s) (ParentGroup1).

This may sound a little confusing, or I may not have expressed is correctly. My apologies if thats what has happened. But that’s what I said to myself and moved on.

So after extensive scribbling and typing code I came down to the following code.

DECLARE @rowcount AS INT,@error AS INT
SET @rowcount = 1
WHILE @rowcount > 0

BEGIN
DELETE dbo.Members
WHERE MemberID IN
(
SELECT c1.MemberID

FROM dbo.Members AS cm
JOIN (SELECT * FROM dbo.Members WHERE Calculated = 1) AS c1 ON cm.ChildID = c1.ChildID AND cm.ParentID = c1.CalculatedParentID
WHERE NOT EXISTS
(
SELECT * FROM dbo.Members AS s
WHERE s.ParentID = c1.ParentID
AND s.ChildID = cm.ParentID
)
UNION
SELECT c2.MemberID

FROM dbo.Members AS pm
JOIN (SELECT * FROM dbo.Members WHERE Calculated = 1) AS c2 ON pm.ParentID = c2.ParentID AND pm.ChildID = c2.CalculatedParentID
WHERE NOT EXISTS
(
SELECT * FROM dbo.Members AS s
WHERE s.ParentID = pm.ChildID
AND s.ChildID = c2.ChildID
)
)
SET @rowcount = @@ROWCOUNT
END

If you look at the delete statement in the code the first part of the union in the select is supposed to do what the Case 1 says. And the second part is supposed to do what Case 2 says.

Finally, what the above code will do is illustrated in Figure 2 (After deleting ChildGroup to ParentGroup1 link)

Once you delete the link between ChildGroup and ParentGroup1 (shown in red) and run the above code, it will, in the first iteration of the loop delete the links numbered 1 and 2. It will delete the links numbered 3 and 4 in its second iteration and will come out of the loop.

Advertisements

1 Comment »

  1. Vikram, this is just what i’m looking for (and I have looked long and hard for a suitable example). I managed to get the original tables & such built and working but cannot get your modifications to work quite right (I either get constraint issues or multiple entries for user & group). Would you kindly post the complete DDL with any easy stored procedures you might have added along the way (AddUser / AddGroup / DeleteUser / DeleteGroup) etc.

    Many thanks, Smac.

    Comment by Smac — December 31, 2007 @ 00:59


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: