String tokens in one string not in another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String tokens in one string not in another

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-14 : 10:47:18

harborcaptain writes "Ok, heres the gist of the situation:

I have a tree structure in ASP which allows me to browse a hierarchy stored in SQL Server. When a user requests a node expand or contract, the page is refreshed and that nodeID is passed on the URL. Because the tree is slow when a lot of nodes are inserted, I developed a stored procedure to only select nodes visible to the user when building the tree.

I do this by adding each node that is expanded to a comma delimited String. This String is passed to the stored procedure and the stored procedure checks this string and returns all nodes whos parent appears in the String (or whos parent is the root).

This works fine until the user contracts a node which is midway through the tree. Because the tree only passes this node, I have no way of removing its children from the String. This presents a problem, because the stored procedure now returns a list of nodes with a "hole" in it, that is, there are children without a valid parent. The poorly written third party tree structure crashes when this happens.

That being said, what I really need is a way in SQL to compare the Hierarchy of each node to the String. The Hierarchy is also a comma delimited string, listing all nodeIDs that preceded a node in the structure.

I have a table with three columns (for the sake of argument anyway). NodeID int, Parent int, and Hierarchy String. I also have a String, AllOpenNodes.

NodeID | Parent | Hierarchy

@AllOpenNodes

I need to find all rows in which all NodeIDs in the Hierarchy String are in the @AllOpenNodes String.

Or, alternatively, any rows in which any of the NodeIDs in the Hierarchy is not in the @AllOpenNodes String. I can put this in a subselect and select NodeIDs not in what is returned.

I have come up with a couple ideas for how to solve this but there are issues with each one.
One is to break up the strings into rows in temp tables, with one temp table T1 having a nodeid and ancestorid (containing all valid combinations), and the other T2 containing valid ancestors. Note that T1 would be massive. One could then outer join the tables on ancestors and return rows where T1.ancestor is null. Finally, this would all be running in a subselect inside my real select! You can see how I'm reluctant to use this solution. The other solution is recursive and would also have to use a temp table to insert valid rows as we went along. But the whole point of my hierarchy table was not to recurse in the first place, and I'm trying to avoid temp tables because not everyone in the past has been good about writing stored procs that don't lock them.

So if I had:

Table:


0 | 0 | 0
1 | 0 | 0,1
2 | 1 | 0,1,2
3 | 1 | 0,1,3
4 | 2 | 0,1,2,4
5 | 4 | 0,1,2,4,5
6 | 0 | 0,6

AllOpenNodes:
0,1,2,6

I want to return:
0
1
2
6"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-14 : 11:56:50

Hmmmmmm, looks vaguely familiar:

http://www.sqlteam.com/item.asp?ItemID=8866

It's not really a table design issue, but a presentation issue. I imagine that your ASP page submits a request each time a node is expanded, which essentially has to redraw the entire tree display, and since you're only passing one node to expand, the others are collapsed, or completely ignored.

Take a look on [url]http://www.15seconds.com[/url], there are a number of articles on tree views/folder lists. Their solutions use XML, I don't know if you want to go that route, but you can look at their code and adapt it to fit your page.

When I have parts of a page that I want to update without refreshing the whole thing, I use a hidden frame and some JavaScript. The frame page would receive the nodeid, generate the expanded tree, and return it. Since it's hidden, it doesn't show in the browser window, but the contents can be read using JavaScript, and the main window can be updated with dynamic HTML methods. It's tricky for a beginner, but if you know DHTML a little you'll get the hang of it pretty quickly.

As far as detecting the children of the node you want to expand, you can use the following:

CREATE PROCEDURE FindKids @nodeID int AS
SELECT * FROM TreeTable
WHERE CharIndex( CAST(@nodeID as varchar)+ ',', Hierarchy) > 0

If you want to expand a list of nodes, pass them as a comma-separated string, and use a little dynamic SQL:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

There's also some CSV parsing stuff that might work for you too:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Edited by - robvolk on 05/14/2002 11:57:21

harborcaptain
Starting Member

2 Posts

Posted - 2002-05-28 : 17:42:21

I came up with a solution for this, took a little string manipulation and some thinking, but I finally wrapped my head around it.

First, I decided that the only way to get this right was to create a separate stored procedure that would remove closed nodes from the list of opened nodes. The ASP would assign this to its AllOpenNodes variable and then send it to the procedure that gets returns all opened nodes and their children for the tree display. This way the ASP stays in sync with the SQL.

So here's how the removal procedure works:

First, it selects the Hierarchy string for the node which has been closed into a variable called, appropriately, RemoveHierarchy.

Then, it selects NodeIDs of all nodes whos Hierarchy contains the RemoveHierarchy string using the Charindex > 0 test. This gets that node, plus all its child nodes.

Finally, it removes each node that was returned from the select out of the opened nodes string using the Replace function.

Then, it returns the new hierarchy string.

The nice thing is that this method completely avoids recursion, temp tables, cursors, looping, and dynamic sql.

It looks very similar to this:
CREATE PROC

@NodeID int,
@AllOpenNodes varchar(999)

AS

Declare @RemoveHierarchy varchar(200)

/*
Select the hierarchy from the node being removed
*/
SELECT @RemoveHierarchy = Hierarchy FROM HierarchyTable WHERE NodeID = @NodeID

/*
Replace all nodes in the String whos hiearchy contains the hierarchy of the node
being removed with nothing. In other words, select the node and all its child
nodes and remove them from the @AllOpenNodes string.
*/
SELECT @AllOpenNodes = REPLACE(@AllOpenNodes, ','+CONVERT(varchar,NodeID)+',', '')
FROM HierarchyTable
WHERE CharIndex(@RemoveHierarchy,Hierarchy,1) > 0

/*
Return the modified @AllOpenNodes string.
*/
SELECT @AllOpenNodes AS AllOpenNodes

By the way, thanks for the help, I'd already looked at all of those sources before posting, but I hadn't quit gotten my brain wrapped around the problem in such a way as to devise an elegant solution. Really, the whole key to this was obtaining the Hierarchy of the node to be removed and using it, rather than the NodeID, to find and eliminate any child nodes present in the string. The other key was that I realized child nodes that were returned but not in the AllOpenNodes list would be ignored, because the Replace function would not find them inside AllOpenNodes. Hopefully this will help someone else in the future, but for now at least you know that this is one less problem to be solved in this forum.

Please also note that a slight modification of this would be an excellent way to delete a Node and all its Child Nodes permanently from a Hierarchy table, which might come in handy for forums or something like that.

- harborcaptain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources