Represent File System in DB (using hierarchyid in SQL Server 2008)

Question

I haven't found any specific examples of this but I am interested in representing a whole directory structure with updates, etc using the hierarchyid datatype. This is a common use case cited for the hierarchyid but I can't find any articles building such an example.

I just want to represent a whole directory structure such as:

/dir1
/file1
/dir2
/dir2/dir3
/dir2/dir3/file2

** I am not trying to synch this with a file system on disk. It is purely represented through the database. **

Hexapody

How do you expect to keep the table structure in sync with the file system? If I add a file in /dir2, how quickly should the table know about it? Immediately, some delay, never? Wouldn't it make more sense to just read in the directory structure at run time? Wouldn't really need hierarchyid for that. – Moon

Thanks, I'm not trying to keep the DB in sync with the file system. It basically will act as its own file system (the file nodes will point to files on disk but there won't be a directory structure). All I really need help with is just this part of it. Thanks. – Hexapody

Why is @Aaron Bertrand's comment being upvoted when it's not really relevent? Seems odd. – Hexapody

Not sure, @user8790... you'll have to ask the people who are up-voting it. Would you like me to remove the comment? It was made before you gave any indication that you weren't really trying to represent an actual file system. Note that up-voting of comments does nothing for reputation points etc, so not sure why it matters. – Moon

Answer 1

Here is an example of representing a file system through hierarchyid:

/*
Setup:
 - Create the table to hold the files
 - nodeDepth is identifier of the depth for readability
 - fullpath is the full path of the file or directory
 - nodePath is the HierarchyID
 - nodePath identifies the row within the tree
*/

DECLARE @t TABLE (
  nodeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  nodeDepth VARCHAR(10) NOT NULL,
  fullPath VARCHAR(20) NOT NULL,
  nodePath HIERARCHYID NOT NULL
) 

Load data:

/*
Load the nodePath value with the Parse command: 
 - The root node has a single /
 - Every nodePath must begin and end with /
 - /1/2/ the second item on level 2
*/

INSERT @t (fullPath, nodeDepth, nodePath) VALUES  
('/','1',HIERARCHYID::Parse('/')),
('/dir1','1.1',HIERARCHYID::Parse('/1/1/')),
('/file1','1.2',HIERARCHYID::Parse('/1/2/')),
('/dir2','1.3',HIERARCHYID::Parse('/1/3/')),
('/dir2/dir3','1.3.1',HIERARCHYID::Parse('/1/3/1/')),
('/dir2/dir3/file2','1.3.1.1',HIERARCHYID::Parse('/1/3/1/1/'))

Show the paths:

SELECT *
FROM @t 

nodeID      nodeDepth  fullPath             nodePath
----------- ---------- -------------------- --------
1           1          /                    0x
2           1.1        /dir1                0x5AC0
3           1.2        /file1               0x5B40
4           1.3        /dir2                0x5BC0
5           1.3.1      /dir2/dir3           0x5BD6
6           1.3.1.1    /dir2/dir3/file2     0x5BD6B0

Get ancestors of file2 (up one level):

SELECT * 
FROM @t 
WHERE nodePath = 
  (SELECT nodePath.GetAncestor(1)
   FROM @t 
   WHERE fullPath = '/dir2/dir3/file2')

nodeID      nodeDepth  fullPath             nodePath
----------- ---------- -------------------- ---------
5           1.3.1      /dir2/dir3           0x5BD6

Get all descentants of dir2:

SELECT *
FROM @t 
WHERE nodePath.IsDescendantOf(
  (SELECT nodePath 
   FROM @t 
   WHERE fullPath = '/dir2')) = 1
AND fullPath <> '/dir2' /* Parent is considered its own descendant */

nodeID      nodeDepth  fullPath             nodePath
----------- ---------- -------------------- --------
5           1.3.1      /dir2/dir3           0x5BD6
6           1.3.1.1    /dir2/dir3/file2     0x5BD6B0

Get the root path:

SELECT * 
FROM @t 
WHERE nodePath = HIERARCHYID::GetRoot()

nodeID      nodeDepth  fullPath             nodePath
----------- ---------- -------------------- --------
1           1          /                    0x

Get the level of file2:

SELECT nodePath.GetLevel() AS level
FROM @t 
WHERE fullPath = '/dir2/dir3/file2'

level
------
4

References:

• Introduction to HierarchyID DataType
• HierarchyID DataType in SQL Server 2008
• hierarchyid (Transact-SQL)

Perfection

('/dir1','1.1',HIERARCHYID::Parse('/1/1/')) you assume that there are no descendants of /1/. Do you have any idea how to optimally convert a path into a hierarchy? – Enumerate