sql - 事件目录 : Convert canonicalName node value from string to integer

Question

是否有任何方法可将 AD canonicalName 属性中包含的字符串文本值转换为递增的整数值？或者，这是否需要手动执行？

例如:

canonicalName (what I am getting)          hierarchyNode (what I need)
\domain.com\                               /1/
\domain.com\Corporate                      /1/1/
\domain.com\Corporate\Hr                   /1/1/1/
\domain.com\Corporate\Accounting           /1/1/2/
\domain.com\Users\                         /1/2/
\domain.com\Users\Sales                    /1/2/1/
\domain.com\Users\Whatever                 /1/2/2/
\domain.com\Security\                      /1/3/
\domain.com\Security\Servers               /1/3/1/
\domain.com\Security\Administrative        /1/3/2/
\domain.com\Security\Executive             /1/3/3/

我正在将用户对象提取到 SQL Server 数据库中以用于报告目的。用户对象分布在林中的多个 OU 中。因此，通过识别树上包含用户的最高节点，我可以利用 SQL Server GetDescendent() 方法以递归方式快速检索用户，而无需编写 1 + n 个子选择。

供引用:https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference

更新:

我能够将 canonicalName 从字符串转换为整数(使用 SQL Server 2014 见下文)。但是，这似乎并不能解决我的问题。我只是通过剥去叶子来构建树的分支，这样我就可以通过 Twig 获得 IsDescendant() 。但是现在，我无法批量插入叶子，因为看起来我需要 GetDescendant()，它似乎是为一次处理一个插入而构建的。

如何将类似于文件系统路径的 Active Directory 目录树构建为 SQL 层次结构？所有示例都将层次结构视为直接的父/子关系，并使用递归 CTE 从根构建，这需要已知父子关系。在我的例子中，父子关系只能通过“/”分隔符获知。

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_TreeHierarchy', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_TreeHierarchy
END;

-- Drop and re-create temp table(s) that are used by this procedure.
IF OBJECT_ID(N'Tempdb.dbo.#TEMP_AdTreeHierarchyNodeNames', N'U') IS NOT NULL 
BEGIN
DROP TABLE #TEMP_AdTreeHierarchyNodeNames
END;

-- CREATE TEMP TABLE(s)
CREATE TABLE #TEMP_TreeHierarchy(
        TreeHierarchyKey INT IDENTITY(1,1) NOT NULL
        ,TreeHierarchyId hierarchyid NULL
        ,TreeHierarchyNodeLevel int NULL
        ,TreeHierarchyNode varchar(255) NULL
        ,TreeCanonicalName varchar(255) NOT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
))

CREATE TABLE #TEMP_AdTreeHierarchyNodeNames (
        TreeCanonicalName VARCHAR(255) NOT NULL
        ,TreeHierarchyNodeLevel INT NOT NULL
        ,TreeHierarchyNodeName VARCHAR(255) NOT NULL
        ,IndexValueByLevel INT NULL
    PRIMARY KEY CLUSTERED 
(
    TreeCanonicalName ASC
    ,TreeHierarchyNodeLevel ASC
    ,TreeHierarchyNodeName ASC
))

-- Step 1.) INSERT the DISTINCT list of CanonicalName values into #TEMP_TreeHierarchy. 
--          Remove the reserved character '/' that has been escaped '\/'. Note: '/' is the delimiter.
--          Remove all of the leaves from the tree, leaving only the root and the branches/nodes.
    ;WITH CTE1 AS (SELECT CanonicalNameParseReserveChar = REPLACE(A.CanonicalName, '\/', '') -- Remove the reserved character '/' that has been escaped '\/'.
                   FROM dbo.AdObjects A
                  )

    -- Remove CN from end of string in order to get the distinct list (i.e., remove all of the leaves from the tree, leaving only the root and the branches/nodes).
    -- INSERT the records INTO #TEMP_TreeHierarchy
    INSERT INTO #TEMP_TreeHierarchy (TreeCanonicalName)
    SELECT DISTINCT 
        CanonicalNameTree = REVERSE(SUBSTRING(REVERSE(C1.CanonicalNameParseReserveChar), CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0) + 1, LEN(C1.CanonicalNameParseReserveChar) - CHARINDEX('/', REVERSE(C1.CanonicalNameParseReserveChar), 0)))
    FROM CTE1 C1

-- Step 2.) Get NodeLevel and NodeName (i.e., key/value pair).
--      Get the nodes for each entry by splitting out the '/' delimiter, which provides both the NodeLevel and NodeName.
--      This table will be used as scratch to build the HierarchyNodeByLvl,
--          which is where the heavy lifting of converting the canonicalName value from string to integer occurs.
--      Note: integer is required for the node name - string values are not allowed. Thus this bridge must be build dynamically.
--      Achieve dynamic result by using CROSS APPLY to convert a single delimited row into 1 + n rows, based on the number of nodes.
    -- INSERT the key/value pair results INTO a temp table.
    -- Use ROW_NUMBER() to identify each NodeLevel, which is the key.
    -- Use the string contained between the delimiter, which is the value.
    -- Combined, these create a unique identifier that will be used to roll-up the HierarchyNodeByLevel, which is a RECURSIVE key/value pair of NodeLevel and IndexValueByLevel.
    -- The rolled-up value contained in HierarchyNodeByLevel is what the SQL Server hierarchyid::Parse() function requires in order to create the hierarchyid.
    -- https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/
    INSERT INTO #TEMP_AdTreeHierarchyNodeNames (TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNodeName)
    SELECT TreeCanonicalName
        ,TreeHierarchyNodeLevel = ROW_NUMBER() OVER(PARTITION BY TreeCanonicalName ORDER BY TreeCanonicalName)
        ,TreeHierarchyNodeName = LTRIM(RTRIM(m.n.value('.[1]','VARCHAR(MAX)')))
    FROM (SELECT TH.TreeCanonicalName
            ,x = CAST('<XMLRoot><RowData>' + REPLACE(TH.TreeCanonicalName,'/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML)
          FROM #TEMP_TreeHierarchy TH
          ) SUB1
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

-- Step 3.) Get the IndexValueByLevel RECURSIVE key/value pair
--        Get the DISTINCT list of TreeHierarchyNodeLevel, TreeHierarchyNodeName first
--        Use TreeHierarchyNodeLevel is the key
--        Use ROW_NUMBER() to identify each IndexValueByLevel, which is the value. 
--        Since the IndexValueByLevel exists for each level, the value for each level must be concatenated together to create the final value that is stored in TreeHierarchyNode
    ;WITH CTE1 AS (SELECT DISTINCT TreeHierarchyNodeLevel, TreeHierarchyNodeName
                    FROM #TEMP_AdTreeHierarchyNodeNames
                    ),
        CTE2 AS (SELECT C1.*
                        ,IndexValueByLevel = ROW_NUMBER() OVER(PARTITION BY C1.TreeHierarchyNodeLevel ORDER BY C1.TreeHierarchyNodeName)
                FROM CTE1 C1
                )

    UPDATE TMP1
    SET TMP1.IndexValueByLevel = C2.IndexValueByLevel
    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
    INNER JOIN CTE2 C2
        ON TMP1.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        AND TMP1.TreeHierarchyNodeName = C2.TreeHierarchyNodeName

-- Step 4.) Build the TreeHierarchyNodeByLevel.
--          Use FOR XML to roll up all duplicate keys in order to concatenate their values into one string.
--          https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
    ;WITH CTE1 AS (SELECT DISTINCT TreeCanonicalName
                                ,TreeHierarchyNodeByLevel = 
                                   (SELECT '/' + CAST(IndexValueByLevel AS VARCHAR(10))
                                    FROM #TEMP_AdTreeHierarchyNodeNames TMP1
                                    WHERE TMP1.TreeCanonicalName = TMP2.TreeCanonicalName
                                    FOR XML PATH(''))
                    FROM #TEMP_AdTreeHierarchyNodeNames TMP2
                    ),
            CTE2 AS (SELECT C1.TreeCanonicalName
                            ,C1.TreeHierarchyNodeByLevel
                            ,TreeHierarchyNodeLevel = MAX(TMP1.TreeHierarchyNodeLevel)
                    FROM CTE1 C1
                    INNER JOIN #TEMP_AdTreeHierarchyNodeNames TMP1
                        ON TMP1.TreeCanonicalName = C1.TreeCanonicalName
                    GROUP BY C1.TreeCanonicalName, C1.TreeHierarchyNodeByLevel
                    )

    UPDATE TH
    SET TH.TreeHierarchyNodeLevel = C2.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode = C2.TreeHierarchyNodeByLevel + '/'
        ,TH.TreeHierarchyId = hierarchyid::Parse(C2.TreeHierarchyNodeByLevel + '/')
    FROM #TEMP_TreeHierarchy TH
    INNER JOIN CTE2 C2
        ON TH.TreeCanonicalName = C2.TreeCanonicalName

INSERT INTO AD.TreeHierarchy (EffectiveStartDate, EffectiveEndDate, TreeCanonicalName, TreeHierarchyNodeLevel, TreeHierarchyNode, TreeHierarchyId)
SELECT EffectiveStartDate = CAST(GETDATE() AS DATE)
        ,EffectiveEndDate = '12/31/9999'
        ,TH.TreeCanonicalName
        ,TH.TreeHierarchyNodeLevel
        ,TH.TreeHierarchyNode
        ,TH.TreeHierarchyId 
FROM #TEMP_TreeHierarchy TH
ORDER BY TH.TreeHierarchyKey

---- For testing purposes only.
SELECT * FROM AD.TreeHierarchy TH
SELECT * FROM #TEMP_AdTreeHierarchyNodeNames
SELECT * FROM #TEMP_TreeHierarchy

-- Clean-up. DROP TEMP TABLE(s).
DROP TABLE #TEMP_TreeHierarchy
DROP TABLE #TEMP_AdTreeHierarchyNodeNames

Answer 1

这是我思考的方向

我给了你9个级别，但是这个模式很容易看到和展开

如果没有正确的顺序，我默认按节点的字母顺序排列。

它也支持多个根节点

示例

Select A.*
      ,Nodes = concat('/',dense_rank() over (Order By N1),'/'
              ,left(nullif(dense_rank() over (Partition By N1                      Order By N2)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2                   Order By N3)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3                Order By N4)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4             Order By N5)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5          Order By N6)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6       Order By N7)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6,N7    Order By N8)-1,0),5)+'/'
              ,left(nullif(dense_rank() over (Partition By N1,N2,N3,N4,N5,N6,N7,N8 Order By N9)-1,0),5)+'/'
              )
 From  YourTable A
 Cross Apply (
                Select N1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,N2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,N3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,N4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,N5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,N6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,N7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,N8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,N9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(stuff([canonicalName],1,1,''),'\','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B
 Order By 1

返回

canonicalName                       Nodes
\domain.com\                        /1/
\domain.com\Corporate               /1/1/
\domain.com\Corporate\Accounting    /1/1/1/
\domain.com\Corporate\Hr            /1/1/2/
\domain.com\Security\               /1/2/
\domain.com\Security\Administrative /1/2/1/
\domain.com\Security\Executive      /1/2/2/
\domain.com\Security\Servers        /1/2/3/
\domain.com\Users\                  /1/3/
\domain.com\Users\Sales             /1/3/1/
\domain.com\Users\Whatever          /1/3/2/