Creating Sub Directory via SQL INSERT using FileTable

Question

Previously, I requested how to create a directory within a FileTable without using File I/O APIs. I now want to create a subdirectory to the parent directory I just created. How do assign my parent during insert? It appears that parent_path_locator is a computed column.

This creates my parent...

INSERT INTO FileTable0 (name,is_directory,is_archive) VALUES ('Directory', 1, 0);

How do I create a child directory to this parent in my FileTable?

Brassica

Answer 1

This is what I ended up using to create a subdirectory since GetPathLocator() won't generate a new path_locator value for me - it will only interpret existing hierarchyids.

DECLARE @parentdir table(path hierarchyid not null);
DECLARE @subdir_locator hierarchyid

-- Create Parent Directory, OUTPUT inserted parent path
INSERT INTO FileTable0 (name,is_directory,is_archive) 
OUTPUT INSERTED.path_locator into @parentdir
SELECT 'Directory', 1, 0

-- Create new path_locator based upon parent
SELECT @subdir_locator = dbo.GetNewPathLocator(path) from @parentdir

-- Create Subdirectory
INSERT INTO FileTable0 (name,path_locator,is_directory,is_archive) 
VALUES ('subdirectory', @subdir_locator, 1, 0);

The above code block utilizes the default path_locator value discovered here that builds a new hierarchyid representation from a GUID (utilizing newid() method, and simple parsing). The function GetNewPathLocator() does not exist anywhere in SQL Server that I could find (hierarchyid.GetDescendant() is the closest I could find, but it didn't use the native structure that FileTable relies on). Maybe in SQL.NEXT...

CREATE FUNCTION dbo.GetNewPathLocator (@parent hierarchyid = null) RETURNS varchar(max) AS
BEGIN       
    DECLARE @result varchar(max), @newid uniqueidentifier  -- declare new path locator, newid placeholder       
    SELECT @newid = new_id FROM dbo.getNewID; -- retrieve new GUID      
    SELECT @result = ISNULL(@parent.ToString(), '/') + -- append parent if present, otherwise assume root
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 1, 6))) + '.' +
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 7, 6))) + '.' +
                     convert(varchar(20), convert(bigint, substring(convert(binary(16), @newid), 13, 4))) + '/'     
    RETURN @result -- return new path locator     
END
GO

The function GetNewPathLocator() also requires a SQL view getNewID for requesting a newid() using the trick from this SO post.

create view dbo.getNewID as select newid() as new_id 

To call GetNewPathLocator(), you can use the default parameter which will generate a new hierarchyid or pass in an existing hiearchyid string representation (.ToString()) to create a child hierarchyid as seen below...

SELECT dbo.GetNewPathLocator(DEFAULT); -- returns /260114589149012.132219338860058.565765146/
SELECT dbo.GetNewPathLocator('/260114589149012.132219338860058.565765146/'); -- returns /260114589149012.132219338860058.565765146/141008901849245.92649220230059.752793580/

Brassica

Answer 2

Rather than try to recreate the hierarchyid in code I opted to update the path_locator after SQL created it's own id:

DECLARE @pathID hierarchyid;
DECLARE @parentdir table(path hierarchyid not null);

IF NOT EXISTS(SELECT 1 FROM FileAsset WHERE is_directory = 1 AND file_stream.GetFileNamespacePath() = '\Assets\Test')
INSERT INTO FileAsset (name, is_directory) VALUES( 'Test', 1)

SELECT @pathID = FileAsset.path_locator FROM FileAsset WHERE file_stream.GetFileNamespacePath() = '\Assets\Test'

INSERT INTO FileAsset (name, file_stream) OUTPUT INSERTED.path_locator into @parentdir VALUES('MyDoc.txt', 0x)

UPDATE FileAsset SET path_locator = '/' + REPLACE(@pathID.ToString(), '/','') + path_locator.ToString() WHERE path_locator = (SELECT [path] FROM @parentdir)

Where 'Assets' is the name of my FileTable directory, 'Test' is the name of Directory I want to put my file into, 'MyDoc.txt' is the filename and 0x is zero entry for a filestream.

I'm sure I'm about to turn this into a function, easy enough.

See...

CREATE PROCEDURE InsertFileAsset

    @fileName varchar(255),
    @dirName varchar(255),
    @data varbinary(MAX),
    @stream_id uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @pathID hierarchyid;
    DECLARE @parentdir table(path hierarchyid not null);
    DECLARE @streamID table(streamID uniqueidentifier not null);

    IF NOT EXISTS(SELECT 1 FROM FileAsset WHERE is_directory = 1 AND file_stream.GetFileNamespacePath() = '\Assets\' + @dirName)
    INSERT INTO FileAsset (name, is_directory) VALUES( @dirName, 1)

    SELECT @pathID = FileAsset.path_locator FROM FileAsset WHERE file_stream.GetFileNamespacePath() = '\Assets\' + @dirName

    INSERT INTO FileAsset (name, file_stream) OUTPUT INSERTED.path_locator into @parentdir VALUES(@fileName, @data)

    UPDATE FileAsset SET path_locator = '/' + REPLACE(@pathID.ToString(), '/','') + path_locator.ToString() OUTPUT inserted.stream_id INTO @streamID WHERE path_locator = (SELECT [path] FROM @parentdir)

    SELECT @stream_id = streamID FROM @streamID

    RETURN
END
GO

Tallula

After some rigorous testing, it seems the update statement is prone to Deadlocks when this is called in parallel. – Tallula

Answer 3

Another option is to use CLR integration and create functions and stored procedures as C# code.

I just created a GitHub CLR integration project for this. https://github.com/rhyous/Db.FileTableFramework

It has various functions or procedures that you would want: CreateFile, CreateDirectory, DirectoryExists. And on GitHub it of course can be modified and improved by anyone.

Reforest

Answer 4

I made some improvement to the answer:

1. The function returns hierarchyid instead of string

2. If there is a parent, hierarchyid::GetReparentedValue function is used to generate a new ID instead of string concatenation.

   create function doc.GetNewPathLocator (@parent hierarchyid = null) returns hierarchyid
   as
   begin 
       declare @id uniqueidentifier = (select new_id from dbo.GetNewID);
       declare @path hierarchyid = (convert(hierarchyid, '/' + 
               convert(varchar(20), convert(bigint, substring(convert(binary(16), @id), 1, 6))) + '.' +     
               convert(varchar(20), convert(bigint, substring(convert(binary(16), @id), 7, 6))) + '.' +     
               convert(varchar(20), convert(bigint, substring(convert(binary(16), @id), 13, 4))) + '/'));
       return case when @parent is null then @path else @path.GetReparentedValue(hierarchyid::GetRoot(), @parent) end;
   end
   go
   

Sclerosis