Рекурсивные CLR TVF

  • Article

Давайте разберем типовую ошибку, которую совершают слушатели на лабораторках при написании сабж. Чтобы далеко не ходить за примером, возьмем нашу замечательную функцию Dir из поста "Синхронизация файловых каталогов средствами SQL Server" (https://blogs.msdn.com/alexejs/archive/2009/05/15/p20090515.aspx) и перепишем ее на рекурсивный лад. Смысл сего действа очень простой. Когда мы дергаем Directory.GetFiles(folder, "*", SearchOption.AllDirectories)

или GetDirectories может произойти ошибка в случае, например, когда она доберется до папки, на чтение которой у текущего эккаунта нет прав. Поставить ловлю ошибки

 

try

  {

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

     { ... }

  }

catch(UnauthorizedAccessException) {};

 

нас не спасет, т.к. Directory.GetFiles() при первом же возникновении исключения обломается и продолжаться не станет. Это было некритично в разобранном сценарии, т.к. понятно, что синхронизироваться будут те папки, права на которые у нас, очевидно, есть. Но если говорить о полноценной функции Dir, такую ситуацию стоит предусмотреть.

 

Выход видится в написании рекурсивной функции, которая бы самостоятельно заходила в каждую вложенную папку и исследовала ее контент. Каждый рекурсивный вызов заворачивается в try ... catch. Таким образом, при возникновении исключения, например, при отсутствии прав на текущую папку, мы не получим содержимое только данной папки. Вся остальная работа будет происходить штатно. Мы не стали заморачиваться с рекурсией в "Табличные CLR-функции для ТЧайников" (https://blogs.msdn.com/alexejs/archive/2009/05/12/clr.aspx), чтобы не усложнять пример. Теперь, когда идея написания CLRной TVF, я надеюсь, ясна, двинемся дальше. Вот типичные грабли, на которые с ходу наступают новички: if (!shallowTraversal) InitMethod(dirName, false);

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

        TableDefinition = "fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)")]

public static IEnumerable InitMethod(string folder, bool shallowTraversal)

{

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

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

    {

        ...

    }

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

    {

        if (!shallowTraversal) InitMethod(dirName, false);

        DirectoryInfo di = new DirectoryInfo(dirName);

        row_item r = new row_item(); r.fullName = dirName; r.dateModified = di.LastWriteTime; r.isDir = true;

        enumResult.Add(r);

    }

    return enumResult;

}

Скрипт 1

После этого человек тянет руку и говорит, что якорный метод TVF не может быть рекурсивным. На выходе всегда получается содержимое папки верхнего уровня независимо от рекурсии. Начинаем разбираться, почему. Заходим в дебаггер и видим по fullName, что действительно метод FillRow выполняется только для файлов и папок корневой директории. Из глубины в него ничего не попадает. Тогда идем на жирную строчку в метод InitMethod и видим, что отнюдь. Каждый раз, когда встречается вложенная папка, он честно сам в себя заходит. То есть рекурсия-то работает нормально. Почему же FillRow выдает только верхний результат и не реагирует на вложенные вызовы InitMethod? На этом месте обычно наступает просветление. IEnumerable, который возвращает InitMethod при каждом своем вызове, это всякий раз разные IEnumerable, а FillRow привязывается только к первому. Остальные ему чужды и неприятны, он их игнорирует. Следовательно, все, что мы насобирали в результате рекурсии, нужно сложить в верхний IEnumerable как-нибудь вот так:

if (!shallowTraversal) enumResult.AddRange((List<row_item>)InitMethod(dirName, false));

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;

public partial class UserDefinedFunctions

{

    struct row_item

    {

        public string fullName;

        public DateTime dateModified;

        public long size;

        public bool isDir;

    }

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

        TableDefinition = "fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)")]

    public static IEnumerable InitMethod(string folder, bool shallowTraversal)

    {

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

        try

        {

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

            {

                FileInfo fi = new FileInfo(fileName);

                row_item r = new row_item(); r.fullName = fileName; r.dateModified = fi.LastWriteTimeUtc; r.size = fi.Length; r.isDir = false;

                enumResult.Add(r);

            }

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

            {

                if (!shallowTraversal) enumResult.AddRange((List<row_item>)InitMethod(dirName, false));

                DirectoryInfo di = new DirectoryInfo(dirName);

                row_item r = new row_item(); r.fullName = dirName; r.dateModified = di.LastWriteTime; r.isDir = true;

                enumResult.Add(r);

            }

        }

        catch (UnauthorizedAccessException) { };

        return enumResult;

    }

    public static void FillRow(Object o, out SqlString fullName, out DateTime? dateModified, out SqlInt64 size, out SqlBoolean isDir, out SqlString parent)

    {

        row_item r = (row_item)o;

        fullName = r.fullName; dateModified = r.dateModified; size = r.size; isDir = r.isDir; parent = Path.GetDirectoryName(r.fullName);

    }

}

Скрипт 2

dbcc dropcleanbuffers

select * into #MyDiscC from dbo.Dir('c:\', 0)

146938 файлов, которые обнаружились у меня на диске c: на виртуалке с 1 гигом оперативки она собрала за 1 мин. 40 сек.