Partager via

Синхронизация файловых каталогов средствами SQL Server

  • Article

В третьей серии нашей программы я предлагаю расширить и углУбить бизнес-смысл демонстрационного примера. Мы написали CLR TVF, которая позволяет получать parent-child таблицу с содержимым заданного каталога файловой системы (https://blogs.msdn.com/alexejs/archive/2009/05/12/clr.aspx). Также мы научились ее подписывать и деплоить на SQL Server (https://blogs.msdn.com/alexejs/archive/2009/05/11/0-9-8-7-6-5-5-6.aspx). Достаточно совершить совсем немного элементарных действий, чтобы с ее помощью решить практическую задачу синхронизации двух папок. Под синхронизацией будем понимать в данном случае merge-сценарий. Предположим, имеются два диска: текущий рабочий и архивный. Архивный повторяет структуру рабочего диска, однако на нем ничего не удаляется, как и полагается при работе с хранилищем. На него просто с некоторой периодичностью сливаются обновления. Под обновлениями будем полагать ситуации, когда папка или файл на рабочем диске отсутствуют в архиве, далее, если дата последнего обновления файла на рабочем диске больше даты последнего обновления соответствующего файла в архиве и, наконец, если размеры этих файлов отличаются. Под соответствием файлов или подкаталогов будем понимать совпадение их относительных путей. Относительный путь - это все, что идет после папки источника или назначения, передаваемых в качестве параметра. Т.е. если мы синхронизируем папку c:\Temp в папку f:\Temp1, то относительный путь у файла c:\Temp\Folder\File.ext будет Folder\File.ext, а у файла f:\Temp1\Folder\File.ext - Folder\File.ext. Они совпадают, поэтому файл c:\Temp\Folder\File.ext будет соответствовать файлу f:\Temp1\Folder\File.ext. Если файла f:\Temp1\Folder\File.ext нет или его дата модификации меньше, чем у c:\Temp\Folder\File.ext или их размеры различны, файл c:\Temp\Folder\File.ext копируется в f:\Temp1, переписывая f:\Temp1\Folder\File.ext, если таковой уже существует.

У нас имеется замечательная функция dbo.Dir() (см. «Табличные CLR-функции для ТЧайников»), которая позволяет получить содержимое c:\Temp в виде таблицы. И содержимое f:\Temp1 в виде аналогичной таблицы. Нужно всего навсего их сджойнить левым образом, чтобы определить, какие файлы/подкаталоги источника c:\Temp требуется скопировать в назначение f:\Temp1. Ну и дополнительно к функции Dir() я дописал еще две процедуры: создания каталога и копирования файла. Они элементарны. Вот, что получилось в результате.

///CLRная библиотека для SQLной задачи слияния файловых папок.

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

{

    /// <summary>

    /// Псевдозапись

    /// </summary>

    struct row_item

    {

        public string fullName;

        public DateTime dateModified;

        public long size;

        public bool isDir;

    }

    /// <summary>

    /// Якорный метод TVF. Выводит содержание файлов и подкаталогов заданного каталога в табличном виде.

    /// TableDefinition - структура рекордсета, выводимого TVF.

    /// Из атрибутов выводятся полное имя, дата посл.изм-я, размер, признак "каталог это или файл", родительский каталог.

    /// Родительский каталог в псевдозаписи не храним, считаем при выводе.

    /// </summary>

    /// <param name="folder">Папка, dir которой выводим</param>

    /// <param name="shallowTraversal">Сканируем только folder, или лезем в подфолдеры до упора?</param>

    /// <returns>IEnumerable коллекция (в дан.случае List) псевдозаписей.</returns>

    [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, "*", shallowTraversal ? SearchOption.TopDirectoryOnly : SearchOption.AllDirectories))

        {

            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, "*", shallowTraversal ? SearchOption.TopDirectoryOnly : SearchOption.AllDirectories))

        {

            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;

    }

    /// <summary>

    /// В отл-е от Т-SQLной TVF CLRная гонит поток вместо законченного снимка. Данный метод вызывается, когда в потоке сдвигаемся

    /// на след.псевдозапись.

    /// </summary>

    /// <param name="o">Очередная псевдозапись</param>

    /// Далее идет список выходных п-ров, соотв-х стр-ре рекордсета, объявленной в атрибуте TableDefinition метода InitMethod.

    /// <param name="fullName"></param>

    /// <param name="dateModified"></param>

    /// <param name="size"></param>

    /// <param name="isDir"></param>

    /// <param name="parent"></param>

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

    }

}

public partial class StoredProcedures

{

    /// <summary>

    /// Если директория не существует, она создается.

    /// </summary>

    /// <param name="destFullName"></param>

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void FolderCreate(string destFullName)

    {

        if (!Directory.Exists(destFullName)) Directory.CreateDirectory(destFullName);

    }

   

    /// <summary>

    /// Процедура копирует файл, переписывая назначение, если есть.

    /// Если родительский фолдер назначения не существовал, он предварительно создается.

    /// </summary>

    /// <param name="sourceFullName">Полное имя, кого копируем.</param>

    /// <param name="destFullName">Полное имя, куда копируем.</param>

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void FileCopy(string sourceFullName, string destFullName)

    {

        FolderCreate(Path.GetDirectoryName(destFullName));

        File.Copy(sourceFullName, destFullName, true);

    }

}

Скрипт 1

Подпишем проект, как показывалось в «Подписание внешней или небезопасной сборки внешним ключом», рис.7.

Перейдем в SQL Server Management Studio и создадим логин, ассоциированный с открытым ключом данной сборки.

use master

if exists (select 1 from sys.server_principals where name = 'SQLCLRLogin') drop login SQLCLRLogin

if exists (select 1 from sys.asymmetric_keys where name = 'SQLCLRKey') drop asymmetric key SQLCLRKey

create asymmetric key SQLCLRKey from executable file = 'C:\Demo\10.MergeFolders\SqlServerProject1\bin\Debug\SqlClassLibrary.dll'

create login SQLCLRLogin from asymmetric key SQLCLRKey

grant external access assembly to SQLCLRLogin

Скрипт 2

Теперь вернемся в VS и продеплоим проект на SQL Server. Либо это можно сделать, оставаясь здесь же, в SSMS:

use tempdb

if exists (select 1 from sys.objects where type_desc = 'CLR_TABLE_VALUED_FUNCTION' and name = 'Dir')

 drop function dbo.Dir

if exists (select 1 from sys.procedures where name = 'FileCopy' and type = 'PC') drop proc FileCopy

if exists (select 1 from sys.procedures where name = 'FolderCreate' and type = 'PC') drop proc FolderCreate

if exists (select 1 from sys.assemblies where is_user_defined = 1 and name = 'MyAssembly')

 drop assembly MyAssembly

go

create assembly MyAssembly from 'C:\Demo\10.MergeFolders\SqlServerProject1\bin\Debug\SqlClassLibrary.dll' with permission_set = external_access

select * from sys.assembly_files

go

create function dbo.Dir(@folder nvarchar(1000), @shallowTraversal bit) returns table (fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)) as external name MyAssembly.UserDefinedFunctions.InitMethod

go

-- 1 - только верхняя папка, 0 - рекурсивно вглубь.

select * from dbo.Dir('c:\Temp', 1)

go

create proc FileCopy @sourceFullName nvarchar(1000), @destFullName nvarchar(1000) as external name MyAssembly.StoredProcedures.FileCopy

go

Скрипт 3

Создадим процедуру определения несовпадений.

/* Процедура FindLeftDifferences обнаруживает несовпадения между папками @rootFolderFrom и @rootFolderTo

для синхронизации @rootFolderFrom в @rootFolderTo.

Несовпадения, которые умеет отыскивать процедура:

1 - файла/папки с относительным именем из @rootFolderFrom нет в @rootFolderTo

2 - дата модификации файла в @rootFolderFrom > даты модификации файла с таким же относительным именем в @rootFolderTo

3 - размер файла в @rootFolderFrom не совпадает с размером файла с с таким же относительным именем в @rootFolderTo

Такие объекты считаются кандидатами на копирование из @rootFolderFrom в @rootFolderTo.

В параметре @shallowTraversal задается глубина сканирования.

 1 - только непосредственные дети текущего фолдера,

 0 - все вложенные подфолдеры до упора.

*/

use tempdb

if exists (select 1 from sys.procedures where name = 'FindLeftDifferences' and schema_id() = schema_id) drop proc FindLeftDifferences

go

create proc FindLeftDifferences @rootFolderFrom nvarchar(1000), @rootFolderTo nvarchar(1000), @shallowTraversal bit as begin

--Полные имена папок источника и назначения должны заканчиваться на \. Если нет, символ добавляется.

 set @rootFolderFrom = case when right(@rootFolderFrom, 1) = '\' then @rootFolderFrom else @rootFolderFrom + '\' end

 set @rootFolderTo = case when right(@rootFolderTo, 1) = '\' then @rootFolderTo else @rootFolderTo + '\' end

 --Создаем таблицу-переменную, куда выполняем CLRную ф-цию Dir (список объектов каталога с необходимыми

 --атрибутами). В процессе вставки добавляем относительное имя (fullName минус @rootFolderFrom) и заменяем

 --parent тоже на относительный путь родительского каталога.

 declare @source table (relativeName nvarchar(1000), fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, relParent nvarchar(1000), primary key (isDir, relativeName))

 insert @source select substring(fullName, len(@rootFolderFrom) + 1, len(fullName) - len(@rootFolderFrom)),

                       fullName, dateModified, size, isDir,

                       substring(parent, len(@rootFolderFrom) + 1, len(fullName) - len(@rootFolderFrom))

                from dbo.Dir(@rootFolderFrom, @shallowTraversal)

--То же самое для фолдера назначения, получаем его содержимое.

 declare @dest table (relativeName nvarchar(1000), fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, relParent nvarchar(1000), primary key (isDir, relativeName))

 insert @dest select substring(fullName, len(@rootFolderTo) + 1, len(fullName) - len(@rootFolderTo)),

                     fullName, dateModified, size, isDir,

                     substring(parent, len(@rootFolderTo) + 1, len(fullName) - len(@rootFolderTo))

              from dbo.Dir(@rootFolderTo, @shallowTraversal)

 --Сливаем результаты сравнения содержаний каталогов в таблицу ##merge.

 if object_id('tempdb..##merge', 'table') is not null drop table ##merge

 --Сопоставление источника и назначения производится по относительному имени, при этом учитывается, файл это или фолдер.

 ;with

 cte as (

  --Выявляем файлы и фолдеры источника, которые отсутствуют в назначении. select s.*, cast(1 as tinyint) as reason, cast(1 as tinyint) as CopyStatus from @source s left join @dest d on s.relativeName = d.relativeName and s.isDir = d.isDir where d.fullName is null

  union

  --Выявляем файлы источника, дата модификации которых позже соответствующего файла в назначении.

    select s.*, 2, 1 from @source s inner join @dest d on s.relativeName = d.relativeName and s.isDir = d.isDir and s.isDir = 0 and s.dateModified > d.dateModified

  union

  --Выявляем файлы источника, размер которых не совпадает с размером соответствующего файла в назначении.

select s.*, 3, 1 from @source s inner join @dest d on s.relativeName = d.relativeName and s.isDir = d.isDir and s.isDir = 0 and s.size <> d.size

 )

 select row_number() over (order by fullname) as n,

        relativeName, convert(char(20), dateModified, 120) as dateModified,

        isDir, size, reason, CopyStatus, fullName as source, @rootFolderTo + relativeName as dest

        into ##merge from cte

--Таблица ##merge является результатом работы данной процедуры.

--Поле CopyStatus имеет следующие значения:

--0 - файл/фолдер в данной записи не будет копироваться процедурой MergeFolders.

--1 - файл/фолдер в данной записи будет копироваться процедурой MergeFolders.

--2 - файл/фолдер в данной записи скопирован процедурой MergeFolders.

--По умолчанию все выявленные несовпадения, т.е. все записи в таблице ##merge имеют CopyStatus = 1.

--При необходимости его можно откорректировать вручную. CopyStatus = 2 проставляется по мере обработки процедурой MergeFolders.

end

go

Скрипт 4

Создадим процедуру копирования. Это просто. Она пробегается вдоль таблицы ##merge, полученной в результате предыдущей процедуры, и если поле CopyStatus = 1, копирует файл, если это файл, или создает фолдер, если в этой записи лежит фолдер. CopyStatus становится 2 у этой эаписи и у всех с таким же относительным именем. Это нужно, чтобы избежать повторного копирования, т.к., например, источник может быть кандидатом на копирование в случае, если у него позже дата модификации и если его размер разнится с назначением. В этом случае в таблице ##merge будут две записи, соответствующие данному относительному имени.

use tempdb

if exists (select 1 from sys.procedures where name = 'MergeFolders' and schema_id() = schema_id) drop proc MergeFolders

go

create proc MergeFolders as begin

 declare @i int = 0, @source nvarchar(1000), @dest nvarchar(1000), @isDir bit

 while 1 = 1 begin

  select top 1 @i = n, @source = source, @dest = dest, @isDir = isDir from ##merge where CopyStatus = 1 and n > @i

  if @@rowcount = 0 break

  if @isDir = 1 exec FolderCreate @dest else exec FileCopy @source, @dest

  print ''

  print cast(@i as varchar(10)) + ') ' + @source + ' -> ' + @dest

  update ##merge set CopyStatus = 2 where source = @source

 end

end

Скрипт 5

Работа выглядит следующим образом. Выявляются несоответствия между фолдерами:

exec FindLeftDifferences 'C:\Demo\10.MergeFolders', 'C:\Demo\10.MergeFolders - Copy', 0

При желании их можно посмотреть и подправить CopyStatus у тех записей, которые мы в силу каких-то причин не желаем копировать

SELECT relativeName, dateModified, isDir, size, reason, CopyStatus

FROM [##merge]

ORDER BY source

 

После чего запускаем процедуру слияния

 

exec MergeFolders

Собственно, все. Буду признателен за выявленные ошибки, поскольку писалось это практически экспромтом вчера перед SQL Server User Group в Самаре. Можете брать, дорабатывать на свой вкус и использовать. Благое дело можно также совершить, прикрутив сюда графический интерфейс на WPF, что придаст примеру товарный вид. Да, чуть не забыл. The last but not the least. Все это действо у нас разворачивалось на SQL Server 2008 Express, который, если кто забыл, между прочим, бесплатный ;)

Comments