Rendering directory structures
Ben
in a depth first manner, similar to what you would see in Windows
Explorer. the table is defined similar to
CREATE TABLE Folders (
folderID int,
parentFolderID int,
name varchar(32)
)
... I've left some things out of the definition for the simplicity of
example.
My desire is to quickly generate a record set containing the all child
folders of a specific folder along with the how many levels deep each
folder is, in a quick and memory efficient manner. I have done some
research through this group and found a few examples similar to what I
have hypothesized, however I was hoping for some feedback on what I
have decided to use.
Since this database table is already created and I cannot modify the
existing schema, it would not be very feasible to use a nested sets
model (unfortunately -- or if anyone has a suggestion?).
I plan on using a stored procedure that recusively calls itself.
something similar to the following:
/* This is assuming the table #TempFolderTable has
* already been declared globally
*/
sp_FolderDisplayRecurive @folder int, @level int AS
IF @@NESTLEVEL > 31 RETURN;
DECLARE @childFolder INT
DECLARE folders_cursor AS CURSOR LOCAL FOR
SELECT folderID FROM Folders
WHERE parentFolderID = @folderID
OPEN folders_cursor
FETCH NEXT FROM folders_cursorINTO @childFolder
while @@Fetch_status = 0
BEGIN
-- Insert current folder into
-- folder table (under parent @folder)
INSERT INTO #TempFolderTable
(FolderID, DepthLevel, ParentFolderID) VALUES
(@childFolder, @level, @folder)
-- Iterate over all of its children
EXEC sp_FolderDisplayRecursive
@childFolder, @level + 1
FETCH NEXT FROM folders_cursor INTO @childFolder
END
...
Will this incure a large overhead for a deep directory structure? Are
there any other problematic issues? Any suggestions and/or comments
are very welcome. Thanks!
Erland Sommarskog
> My desire is to quickly generate a record set containing the all child
> folders of a specific folder along with the how many levels deep each
> folder is, in a quick and memory efficient manner. I have done some
> research through this group and found a few examples similar to what I
> have hypothesized, however I was hoping for some feedback on what I
> have decided to use.
> IF @@NESTLEVEL > 31 RETURN;
> DECLARE @childFolder INT
> DECLARE folders_cursor AS CURSOR LOCAL FOR
> SELECT folderID FROM Folders
> WHERE parentFolderID = @folderID
> OPEN folders_cursor
> FETCH NEXT FROM folders_cursorINTO @childFolder
> while @@Fetch_status = 0
First, don't use sp_ for your procedure names. This prefix is reserved
for system stored procedure, and SQL Server first looks in master for
these.
Then, there is no need for neither recursive procedure nor cursor. The
example illustates:
CREATE TABLE Folders (
folderID int NOT NULL PRIMARY KEY,
parentFolderID int NULL REFERENCES Folders(folderID),
name varchar(32) NOT NULL
)
go
INSERT Folders (folderID, parentFolderID, name)
SELECT 1, NULL, 'C:\'
UNION
SELECT 2, 1, 'Windows'
UNION
SELECT 3, 1, 'Program Files'
UNION
SELECT 4, 2, 'System 32'
UNION
SELECT 5, 4, 'Drivers'
go
CREATE TABLE #tmp (
folderID int NOT NULL,
parentFolderID int NULL,
lvl tinyint NOT NULL
)
go
DECLARE @level tinyint
SELECT @level = 1
INSERT #tmp(folderID, parentFolderID, lvl)
SELECT folderID, NULL, 1
FROM Folders
WHERE parentFolderID IS NULL
WHILE 1 = 1
BEGIN
SELECT @level = @level + 1
INSERT #tmp(folderID, parentFolderID, lvl)
SELECT a.folderID, a.parentFolderID, @level
FROM Folders a
WHERE EXISTS (SELECT *
FROM #tmp t
WHERE t.folderID = a.parentFolderID
AND t.lvl = @level - 1)
IF @@rowcount = 0
BREAK
END
SELECT * FROM #tmp
go
DROP TABLE #tmp
DROP TABLE Folders
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Ben
Thanks a lot for your reply.
First off, thanks for reminding me about the "sp_," it's a very bad
habit my company has gotten me into, and I am trying to break out of
it.
I applied your algorithm to some test data and it yielded the record
set in a breadth first manner, which could be very useful, however I
would like to render the folders in a depth first manner.
For example, if I had the simple tree:
A:-->B,C; B:-->D; C-->E;
+------------------------+
|folderid| parent| name |
+------------------------+
|1 | Null | A |
|2 | 1 | B |
|3 | 1 | C |
|4 | 2 | D |
|5 | 3 | E |
+------------------------+
Then I would want to display it in the following order:
A,B,D,C,E.
That would visually be:
A
B
D
C
E
Rather than:
A
B
C
D
E
I have tried to construct some way to join the two tables (#tmp and
Folders) together to achieve this result, but I have been unable to
come up with anything so far, besides my original post.
Please let me know of any ideas you have.
Erland Sommarskog
> Then I would want to display it in the following order:
> A,B,D,C,E.
>
> That would visually be:
> A
> B
> D
> C
> E
> Rather than:
> A
> B
> C
> D
> E
>
> I have tried to construct some way to join the two tables (#tmp and
> Folders) together to achieve this result, but I have been unable to
> come up with anything so far, besides my original post.
OK. Here is a modified version of my original script.
CREATE TABLE Folders (
folderID int NOT NULL PRIMARY KEY,
parentFolderID int NULL REFERENCES Folders(folderID),
name varchar(32) NOT NULL
)
go
INSERT Folders (folderID, parentFolderID, name)
SELECT 1, NULL, 'C:\'
UNION
SELECT 2, 1, 'Windows'
UNION
SELECT 3, 1, 'Program Files'
UNION
SELECT 4, 2, 'System 32'
UNION
SELECT 5, 4, 'Drivers'
go
CREATE TABLE #tmp (
folderID int NOT NULL,
sortkey varchar(4000) NOT NULL,
lvl tinyint NOT NULL
)
go
DECLARE @level tinyint
SELECT @level = 1
INSERT #tmp(folderID, sortkey, lvl)
SELECT folderID, str(folderID), 1
FROM Folders
WHERE parentFolderID IS NULL
WHILE 1 = 1
BEGIN
SELECT @level = @level + 1
INSERT #tmp(folderID, sortkey, lvl)
SELECT a.folderID, t.sortkey + str(a.folderID), @level
FROM Folders a
JOIN #tmp t ON t.folderID = a.parentFolderID
AND t.lvl = @level - 1
IF @@rowcount = 0
BREAK
END
SELECT space((t.lvl - 1) *10) + f.name
FROM #tmp t
JOIN Folders f ON f.folderID = t.folderID
ORDER BY sortkey