Насправді їх тут дві з половиною штуки. Перша (InitMethod + FillRow + fnRecursive) - це TVF (table - valued function. Функція, яка повертає результат в табличному вигляді), яка бере на вході повний шлях до файлової папці, і повертає в даному випадку рекордсет наступної структури:
ID HierarchyID, FullName nvarchar (1000), DateModified datetime2, DateCreated datetime2, LastAccessed datetime2, size bigint, isDir bit, Properties xml
Перше поле використовується в якості ідентифікатора запису в таблиці. Заодно воно визначає положення вузла в ієрархії, що дозволяє при побудові в таблиці дерева обійтися без традиційної зв'язки parent - child. Див. На цю тему пости HierarchyID і parent-child. Dir () і HierarchyID.
Наступні поля - це традиційні атрибути файлу: його повне ім'я (з шляхом), дата останнього зміни, дата створення і дата останнього доступу, а також розмір (в байтах) і булевий ознака того, чи є даний об'єкт Фолдер або файлом.
Пристрій CLR ної TVF розбиралася в пості Табличні CLR-функції для ТЧайніков. З нього можна почерпнути, для чого потрібні якірний метод InitMethod, ітераційний метод FillRow, а також структурний тип row_item і список з елементів цього типу. У разі глибокої навігації по Фолдер, потрібно обшарювати вкладені подфолдери, рекурсивно викликаючи для них ті ж дії, що і для батьківського фолдера. В принципі, рекурсивна TVF нічим ідейно не відрізняється від звичайної, але про всяк випадок можна прочитати пости Рекурсивні CLR TVF і Рекурсивні TVF-2. В якості допоміжної функції при організації рекурсії виступає fnRecursive.
У міру того, як перебираються файли, хотілося б виводити будь-яку інформацію про прогрес процесу. TVF не може виводити нічого статусного, крім свого результуючого рекордсета, тому я вирішив створити деяку подобу журналу, куди записувати поточний файл і час. Було б логічно тримати його разом з іншими журналами повідомлень SQL Server. Функція GetSqlErrLogPath () отримує фолдер, в якому SQL Server тримає свої Error Logі. Місцезнаходження службових об'єктів SQL Server розбиралася в пості Як визначити дефолтну локацію для файлів БД. Оскільки ця функція звертається до реджістрі, то методом InitMethod, з якого вона викликається, ставиться атрибут SystemDataAccessKind.Read.
Рекурсивна процедура fnRecursive формує новий запис типу row_item в списку для кожного перебрання файлу або подфолдера, наповнюючи її поля. HierarchyId виходить від батьківського значення і найближчого лівого сусіда. Розширені атрибути збираються функцією FileExtProps у вигляді XML. який також вставляється в row_item. Інші атрибути беруться з об'єкта IO.FileSystemInfo, що представляє собою узагальнення файлу і директорії. Якщо це файл, для нього ще береться розмір, якщо директорія, процедура fnRecursive викликає сама себе з параметром цієї директорії.
Збірка оформляється у вигляді бібліотеки класів і деплоітся на SQL Server вручну, оскільки в ній використовується Сомовська бібліотека% windir% \ System32 \ SHELL32.dll. Про це можна прочитати в пості Використання COMовскіх dll в SQL CLR. З цієї ж причини збірка повинна деплоіться як unsafe - см. Підписання зовнішньої або небезпечною збірки. А сопло ми їй прикрутимо деревянненькое, тому що до цього місця все одно ніхто не дочитає. Згідно з останніми звітами Росстат видалення mssqlsystemresource .mdf дозволяє підняти продуктивність SQL Server на 12.3%. Деплоймент скомпілірованой збірки виконується засобами Т-SQL:
--select * from sys.objects
declare @fullName nvarchar (500), @cmd nvarchar (1000), @i int = 0
declare cur cursor forward_only scroll_locks for select fullName from t where isDir = 0 for update of content; open cur
while 1 = 1 begin
fetch next from cur into @fullName
if @@ fetch_status <> 0 break
print cast (@i as varchar (10)) + '. '+ @fullName
set @cmd = 'update t set content = (select BulkColumn from openrowset (bulk' '' + @fullName + '' ', single_blob) as f) where current of cur'
begin try; exec (@cmd); end try
begin catch; print 'Не можу завантажити файл через' + Error_Message (); end catch
close cur; deallocate cur
set nocount off
Я підготував ще один різновид закачування у вигляді процедури LoadDirWithFileContent. Вона відрізняється від функції InitMethod тим, що відвантажує фолдер безпосередньо в таблицю, ім'я якої передається їй в якості одного з параметрів, отже, БЛОБ, які ми аплоудім на SQL Server, відразу персистуюча і їх можна заливати тут же без остраху переповнити пам'ять. Таблиця, в яку відбувається завантаження, за умовою, повинна мати обов'язковий набір полів з обумовленими іменами і типами (див. Таблицю Dir в скрипт 5). Додатково до них в таблиці можуть існувати інші поля. При заливці Блоб всередині CLRной процедури використовується рішення, розглянуте в пості Імпорт / експорт блобовскіх полів в файли - CLR. Якщо хто-небудь, який чув про SqlFileStream API, здивується, чому тут не використовується цей підхід, почитайте пост Часткове оновлення FILESTREAM. Службова процедура spRecursive грає для збереженої процедури LoadDir таку ж роль, як fnRecursive для TVF Dir (). Функція Dir () і процедура LoadDir можуть використовуватися незалежно, і у мене була ідея оформити їх у вигляді методів деякого UDT, який просто розглядати як контейнер для бібліотеки функцій. На жаль, TVF не допускаються у вигляді методів UDT, тому все буде лежати розсипом. Створення процедури LoadDir з збірки і її запуск виглядають так:
drop function Dir
create proc LoadDir @folder nvarchar (255), @shallowTraversal bit. @tblName sysname as external name MyAssembly. FileSystem. LoadDirWithFileContent
if object_id ( 'Dir'. 'U') is not null drop table Dir
create table Dir (ID HierarchyID. FullName nvarchar (1000), DateModified datetime2. DateCreated datetime2. LastAccessed datetime2. Properties xml. size bigint. isDir bit. Content varbinary (max))
exec LoadDir 'c: \ Temp'. 0. 'Dir' --0, якщо пірнаємо в подфолдери; 1, якщо плавати дрібно
Це завантаження рівно тією ж з: \ Temp об'ємом 450 МБ в іншу таблицю. Виконання тривало 4 хв. 30 сек. що нетипово. Поки я налагоджувати, типові часи для неї були 3 хв. +/- пара секунд. Здається, справа в тому, що я не зробив drop table t. Зазвичай видаляв таблицю t з результатами функції, перш ніж переходити до процедури, а зараз вийшов задвоєння розмір бази - не 450 метрів, а 936. Вона стала автопріращать mdfнік, а оскільки він лежить там же в c: \ temp, вони з читанням стали втручатися один одному.
В ході завантаження створюється журнал. Він лежить в тій же папці, що і ErrorLog, і називається жорстко SqlFSLoader.log. Оскільки Flush () робиться після кожної готової рядки, можна перевідкривати його і спостерігати за перебігом процесу завантаження.
SqlCommand cmd = cnn.CreateCommand ();
cmd.CommandText = "insert" + tableName + "(ID, FullName, DateModified, DateCreated, LastAccessed, Properties, size, isDir, Content) values (@hid, @fullName, @dateModified, @dateCreated, @lastAccessed, @properties, @size, @isDir, @content) ";
cmd.Parameters.Add (new SqlParameter ( "@hid". SqlHierarchyId .Null)); //SqlDbType.SqlHierarchyId в списку немає, доводиться обійтися значенням.
cmd.Parameters [ "@hid"] .UdtTypeName = "HierarchyID"; // Інакше System.ArgumentException: UdtTypeName property must be set for UDT parameters.
cmd.Parameters.Add (new SqlParameter ( "@fullName". SqlDbType .NVarChar));
cmd.Parameters.Add (new SqlParameter ( "@dateModified". SqlDbType .DateTime2));
cmd.Parameters.Add (new SqlParameter ( "@dateCreated". SqlDbType .DateTime2));
cmd.Parameters.Add (new SqlParameter ( "@lastAccessed". SqlDbType .DateTime2));
cmd.Parameters.Add (new SqlParameter ( "@properties". SqlDbType .Xml));
cmd.Parameters.Add (new SqlParameter ( "@size". SqlDbType .BigInt));
cmd.Parameters.Add (new SqlParameter ( "@isDir". SqlDbType .Bit));
cmd.Parameters.Add (new SqlParameter ( "@content". SqlDbType .VarBinary));
SqlHierarchyId leftSibling = SqlHierarchyId .Null;
Shell32. Folder shellFolder = shell.NameSpace (folderPath);
FileStream fs = null;
foreach (FileSystemInfo fd in fsi)
cmd.Parameters [ "@hid"] .Value = leftSibling = parentId.GetDescendant (leftSibling, SqlHierarchyId .Null);
cmd.Parameters [ "@fullName"] .Value = fd.FullName;
cmd.Parameters [ "@properties"] .Value = FileExtProps (fd.FullName, shellFolder);
cmd.Parameters [ "@isDir"] .Value = (fd is DirectoryInfo);
cmd.Parameters [ "@dateModified"] .Value = fd.LastWriteTimeUtc;
cmd.Parameters [ "@dateCreated"] .Value = fd.CreationTimeUtc;
cmd.Parameters [ "@lastAccessed"] .Value = fd.LastAccessTimeUtc;
try // На випадок, якщо до файлу немає доступу, OpenRead () вилетить в виняток
string spaces = new string ( ''. leftSibling.GetLevel (). Value);
log.Write (spaces + fd.FullName + "." + DateTime .Now.ToString () + "."); log.Flush ();
if (fd is FileInfo)
cmd.Parameters [ "@size"] .Value = ((FileInfo) fd) .Length;
fs = ((FileInfo) fd) .OpenRead ();
cmd.Parameters [ "@content"] .Value = new SqlBytes (fs);
else if (! shallowTraversal) spRecursive (fd.FullName, false. tableName, leftSibling);