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

问题描述

以前，我要求如何在不使用文件 I/O API 的情况下在 FileTable 中创建目录.我现在想为我刚刚创建的父目录创建一个子目录.在插入期间如何分配我的父母?看起来 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);

上面的代码块使用了 此处发现的默认 path_locator 值，它根据 GUID 构建新的 hierarchyid 表示(利用 newid() 方法，以及简单的解析).函数 GetNewPathLocator() 在我能找到的 SQL Server 中的任何地方都不存在(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 以使用 这篇文章中的技巧.

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 创建子目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！