使用FileTable通过SQL INSERT创建子目录

问题描述

以前，我要求如何在FileTable中创建目录而不使用文件I/O API .现在，我想为我刚刚创建的父目录创建一个子目录.在插入期间如何分配我的父母?看来parent_path_locator是计算列.

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);

如何在FileTable中为此父级创建子目录?

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

推荐答案

这是我最终用来创建子目录的原因，因为GetPathLocator()不会为我生成新的path_locator值-它只会解释现有的hierarchyids.

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);

以上代码块使用在此处发现默认路径定位器值，它从GUID构建新的hierarchyid表示形式(利用 newid() 方法，并进行简单解析).我可以在SQL Server的任何位置找到功能GetNewPathLocator()( hierarchyid.GetDescendant() 是我能找到的最接近的，但它没有使用FileTable依赖的本机结构).也许在SQL.NEXT中...

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

函数GetNewPathLocator()还需要一个SQL视图getNewID，以便使用此SO帖子中的技巧来请求newid() .

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

要调用GetNewPathLocator()，可以使用默认参数，该参数将生成一个新的hierarchyid或传入现有的hiearchyid字符串表示形式( .ToString() )创建一个子hierarchyid如下所示...

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/

这篇关于使用FileTable通过SQL INSERT创建子目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！