Vikram Kamath

June 30, 2005

New Look and Feel

Filed under: General — Vikram @ 21:00

I finally managed to find time and energy to get a new look and feel to this blog. I managed to actually play with HTML and style sheets after ages to get something better. I tried a couple of times earlier this week to sit with HTML and style sheets of my previous skin and try to make it better. But I lost my patience scrolling up and down the code and finally gave it up each time. This time I felt a little wiser by choosing a very simple skin and it did not take much time for me to fine tune it.

The previous skin was kind of good when I started the blog. It was after my last post that I realised it did not solve the purpose. And this time I tried and make it as simple as possible. At least I like it.

I have got to get going now. I am in the process of moving into a new home. I will be moving on Saturday and the whole house is in a big mess with cartons and bags and what not all over. Need to sort out and get packing now. More blogging later!!

Advertisements

June 20, 2005

Figure 2 (After deleting ChildGroup to ParentGroup…

Filed under: General — Vikram @ 22:43


Figure 2 (After deleting ChildGroup to ParentGroup1 link)

Figure 1 (Test Case)

Filed under: General — Vikram @ 22:42


Figure 1 (Test Case)

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.

Create a free website or blog at WordPress.com.