Dir() и HierarchyID

  • Article

На форуме sqlclub.ru у меня была тема "Полезные запросы", где, в частности, показывалось, как получить кол-во страниц и мегабайт, которые занимают объекты базы. Но форум sqlclub.ru нынче лежит благодаря раздолбайству и кривым рукам его администратора Сергея Заворуева, который снова то ли запил, то ли просто забил, поэтому будет нелишне привести здесь этот запрос:

use tempdb

 

select object_name(i.object_id) as objectName, i.name as indexName,

       sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,

       (sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB

from sys.indexes i

join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id

join sys.allocation_units a on p.partition_id = a.container_id

group by i.object_id, i.index_id, i.name

order by 1, 2

Скрипт 1

Из него видно, что таблица #MyDiscC (см.постинг " Рекурсивные CLR TVF" https://blogs.msdn.com/alexejs/archive/2009/05/20/p20090520_5F00_1.aspx) занимает 57 метров. Практически все это место образуется из-за nvarcharовских полей fullName и parent. Как мы с вами знаем, функция datalength() позволяет определить реальный размер поля в каждой текущей строке, а не просто max_length из sys.columns:

select * from sys.columns where object_id = object_id('#MyDiscC')

Функция datalength() является достаточно легковесной, т.к. для определения фактической длины неважно строки или блоба, ей не нужно читать его содержимое до упора, она берет его из корневой структуры – см. https://blogs.msdn.com/sqltips/archive/2006/07/14/666188.aspx. Кстати, кто сказал, что fullName и parent будут именно nvarchar(1000), коль скоро #MyDiscC получается, как select * into из TVF, a FillRow возвращает для них просто SqlString? Сказал метод InitMethod, атрибут которого TableDefinition описывает структуру возвращаемого рекордсета. Смотрим, сколько места они отъедают во всей таблице:

select sum(datalength(fullName) + datalength(parent)) / 1024 / 1024.0 from #MyDiscC

Скрипт 2

О ужас! Из 57 мегов таблицы 50 занимают колонки fullName и parent. Оно и понятно. Файлы могут называться очень длинно и лежать в глубине не менее длинных каталогов. Например,

select fullName from #MyDiscC where datalength(fullName) = (select max(datalength(fullName)) from #MyDiscC)

----------------------------------------

c:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008.0.db\Adventure Works.0.cub\Fact Internet Sales 1.0.det\Internet_Sales_2003.0.prt\1.Dim Customer.Dbo Dim Geography - Country Region Name.agg.rigid.map.hdr

Очевидно, что использовать их в качестве идентификаторов записей – занятие расточительное. Вместо fullName я предлагаю хранить просто Name файла или папки, а отношение иерархии соблюсти при помощи parent-child отношения между id и parent типа int, либо при помощи одного поля нового перспективного типа HierarchyID, появившемся в SQL Server 2008. На самом деле можно считать, что HierarchyID появился в SQL Server 2005 одновременно с типом XML в качестве внутренней ORDPATH-схемы для кодирования узлов XML-документа при построении по ним XML-индекса. Такой подход оказался выгодней традиционного для реляционки представления дерева в виде parent-child таблицы, и в 2008-м его было решено предоставить для всеобщего пользования в виде готового CLR-типа. Его и другие встроенные в SQL Server CLR-типы можно также использовать отдельно от SQL Server в составе собственного приложения. Скачать их можно в составе Microsoft SQL Server 2008 Feature Pack, April 2009 (https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4). Там ближе к концу страницы есть пункт Microsoft SQL Server System CLR Types, остается только вспомнить, какая у вас платформа – х86, х64 или IA64. (The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008. This component can be installed separately from the server to allow client applications to use these types outside of the server). Лучше всего познакомиться с типом HierarchyID можно из BOL (https://msdn.microsoft.com/ru-ru/library/bb677290.aspx), это понятно, и из блога нашего MVP и активного докладчика Russian SQL Server User Group Яна Либермана (https://blogs.gotdotnet.ru/personal/yliberman/PermaLink.aspx?guid=BD3DE22F-E30B-40C2-8201-9CC5D33AD77E, https://blogs.gotdotnet.ru/personal/yliberman/PermaLink.aspx?guid=5ed219fc-1624-4961-8eb1-e83a73cb4d11). У Яна имеется также статья в RSDN на основе его постов в блоге - https://www.rsdn.ru/article/db/ordpath.xml. Можно еще почитать Ицика Бен-Гана в SQL Server Magazine (https://www.sqlmag.com/Article/ArticleID/100646/sql_server_100646.html). Этих ресурсов более, чем достаточно, чтобы самостоятельно всесторонне освоить HierarchyID, поэтому вернемся к примеру. Чтобы использовать тип HierarchyID из Visual Studio, в References проекта надо добавить Microsoft.SqlServer.Types.dll, которая (на всякий случай) при штатной установке SQL Server лежит в C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies.

image001

рис.1

Помните в постинге "Табличные CLR-функции для ТЧайников" в конце Замечание 2? У меня так и не дошли руки поставить Microsoft® Visual Studio Team System 2008 Database Edition GDR R2, хотя для очистки совести я его все-таки скачал (https://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en), считай, полработы сделал. Но она этого не оценила, и когда я добавил в TableDefinition первым полем ID HierarchyID, а в FillRow, соответственно, out SqlHierarchyId ID, снова поперла в дурь и стала писать ошибку, дескать, определение таблицы не соответствует сигнатуре метода FillRow: Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function'Dir' due to column 1. С горя я закрыл VS и совсем уже приготовился апдейтиться, как для чего-то по мелочи пришлось снова открыть проект. Ошибка исчезла, как по мановению волшебной палочки. Видите, даже близость расположения VSTS 2008 Database Edition GDR R2 что с ней делает? А если б я ее еще поставил? J

Модифицируем CLRную TVF Dir(), взяв за основу Скрипт 2 из поста Рекурсивные TVF-2 (https://blogs.msdn.com/alexejs/archive/2009/05/24/tvf-2.aspx). Сократим fullName до просто Name, отбросив полный путь и оставив только имя файла/фолдера; добавим HierarchyID, тогда parent просто не понадобится.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Collections;

using System.Collections.Generic;

using Microsoft.SqlServer.Types;

public partial class UserDefinedFunctions

{

    struct row_item

    {

        public SqlHierarchyId ID;

        public string Name;

        public DateTime dateModified;

        public long size;

        public bool isDir;

    }

    /// <summary>

    /// Якорный метод CLRной TVF, выводящей содержимое файловой папки

    /// </summary>

    /// <param name="folder">Полный путь к папке</param>

    /// <param name="shallowTraversal">Глубина погружения: true - только файлы и фолдеры данной папки, false - лезем вглубь

    /// фолдеров до упора</param>

    /// <returns>Содержимое в виде списка стр-р row_item</returns>

    [Microsoft.SqlServer.Server.SqlFunction(Name = "Dir", FillRowMethodName = "FillRow",

        TableDefinition = "ID HierarchyID, Name nvarchar(1000), dateModified datetime2, size bigint, isDir bit")]

    public static IEnumerable InitMethod(string folder, bool shallowTraversal)

    {

        List<row_item> enumResult = new List<row_item>();

        row_item r = new row_item(); r.ID = SqlHierarchyId.GetRoot(); r.Name = folder; r.dateModified = Directory.GetLastWriteTimeUtc(folder); r.isDir = true;

        enumResult.Add(r);

        Recursive(folder, shallowTraversal, enumResult, r.ID);

        return enumResult;

    }

    /// <summary>

    /// Служебная процедура для InitMethod, собственно и выполняющая всю работу

    /// </summary>

    /// <param name="folder">Берется от InitMethod</param>

    /// <param name="shallowTraversal">Берется от InitMethod</param>

    /// <param name="enumResult">Будущий результат InitMethod, наполняемый в процессе рекурсивных вызовов</param>

    /// <param name="parentId">HierarchyId текущей родительской папки, с которой погрузились в Recursive</param>

    private static void Recursive(string folder, bool shallowTraversal, List<row_item> enumResult, SqlHierarchyId parentId)

    {

        SqlHierarchyId leftSibling = SqlHierarchyId.Null;

        try

        {

            foreach (string fileName in Directory.GetFiles(folder, "*", SearchOption.TopDirectoryOnly))

            {

                FileInfo fi = new FileInfo(fileName);

                row_item r = new row_item();

                //Новый HierarchyId порождается на основе трех HierarchyId: родителя и левого и правого братьев.

                //Правый брат у нас всегда будет null, т.к., спустившись на уровень, двигаемся слева направо.

                //Левый брат = null в момент прихода на уровень, а дальше это будет предыдущий по циклу.

                //Родительский HierarchyId - это параметр parentId.

                r.ID = parentId.GetDescendant(leftSibling, SqlHierarchyId.Null); leftSibling = r.ID;

                r.Name = fi.Name; r.dateModified = fi.LastWriteTimeUtc; r.size = fi.Length; r.isDir = false;

                enumResult.Add(r);

            }

            foreach (string dirName in Directory.GetDirectories(folder, "*", SearchOption.TopDirectoryOnly))

            {

                DirectoryInfo di = new DirectoryInfo(dirName);

                row_item r = new row_item();

                //аналогично файлам

                r.ID = parentId.GetDescendant(leftSibling, SqlHierarchyId.Null); leftSibling = r.ID;

                r.Name = di.Name; r.dateModified = di.LastWriteTimeUtc; r.isDir = true;

                enumResult.Add(r);

                if (!shallowTraversal) Recursive(dirName, false, enumResult, r.ID);

            }

        }

        catch (UnauthorizedAccessException) { };

    }

    public static void FillRow(Object o, out SqlHierarchyId ID, out SqlString Name, out DateTime? dateModified, out SqlInt64 size, out SqlBoolean isDir)

    {

        row_item r = (row_item)o;

        ID = r.ID; Name = r.Name; dateModified = r.dateModified; size = r.size; isDir = r.isDir;

    }

}

Скрипт 3

Вычислить в полученном рекордсете полное имя файла можно при помощи рекурсивного запроса, подобного запросу по таблице parent-child:

with cte as (

select #t.*, Name as fullName from #t where ID = HierarchyID::GetRoot()

union all

select #t.*, cast(cte.fullName + '\' + #t.Name as nvarchar(1000)) from #t join cte on #t.ID.GetAncestor(1) = cte.ID)

select ID.ToString(), ID, fullName, Name, dateModified, size, isDir from cte order by ID

Скрипт 4

где #t содержит результаты вызова функции Dir(), например, select * into #t from dbo.Dir('c:\Temp', 0). В якоре получаем корневой фолдер, в рекурсивной части выбираем всех детей следующего уровня, т.е. все записи, у которых предок 1-го уровня, т.е. непосредственный родитель, был собран в CTE на предыдущем шаге.

image003

Рис.2

Сбор данных по всему диску select * into #t from dbo.Dir('c:\ ', 0) по сравнению с https://blogs.msdn.com/alexejs/archive/2009/05/20/p20090520_5F00_1.aspx стал выполняться секунд на 20 быстрее.