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


В третьей серии нашей программы я предлагаю расширить и углУбить бизнес-смысл демонстрационного примера. Мы написали CLR TVF, которая позволяет получать parent-child таблицу с содержимым заданного каталога файловой системы (http://blogs.msdn.com/alexejs/archive/2009/05/12/clr.aspx). Также мы научились ее подписывать и деплоить на SQL Server (http://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, который, если кто забыл, между прочим, бесплатный 😉


Skip to main content