Частичное обновление FILESTREAM
Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. "Введение в FILESTREAM", "Конфигурирование FILESTREAM. FILESTREAM и сборка мусора (удаление старых версий файлов)", "Загрузка данных в filestream". Под FILESTREAMом понимается возможность хранения больших бинарных объектов (BLOBов) в SQL Server 2008 в файловой системе. Частичное обновление FILESTREAM С точки зрения функциональности отличие файлстрима от обычного блоба проявляется в невозможности частичного обновления. Возьмем в качестве примера таблицу Media, с которой мы игрались на протяжении темы. База TestFS была создана в первом посте темы.
Таблица имеет для сравнения большое символьное поле clob, большое бинарное поле blob и большое бинарное поле stream, хранящееся в файловой системе. Поле uniqueidentifier unique rowguidcol not null является обязательным требованием при наличии поля файлстрим, а поле id я ввел для удобства, чтобы не писать всякий раз длинный гуид в условии where. Положим в нее какой-нибудь файл в качестве записи для примера (см. пост "Импорт/экспорт блобов в файлы"):
рис. 1 Делается cast(stream as varchar(max)), а не nvarchar, потому что файл был сохранен в кодировке Windows 1251, а не юникодовской. Практически все строковые функции Т-SQL не делают разницы файлстримовский блоб – не файлстримовский и работают с ним совершенно одинаково:
за исключением частичного обновления. Полное обновление файлстрима – не вопрос, см., напр., Импорт/экспорт блобов в файлы\Скрипт 1, FILESTREAM и старые версии файлов\Рис.4 и т.д. Частичное обновление нефайлстримовского блоба тоже:
Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. "Введение в FILESTREAM", "Конфигурирование FILESTREAM. FILESTREAM и сборка мусора (удаление старых версий файлов)", "Загрузка данных в filestream". Под FILESTREAMом понимается возможность хранения больших бинарных объектов (BLOBов) в SQL Server 2008 в файловой системе. Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12 С точки зрения функциональности отличие файлстрима от обычного блоба проявляется в невозможности частичного обновления... Однако частичный апдейт файлстримовских полей невозможен по определению:
Первое, что приходит в голову, - осуществлять частичный апдейт файлстрима через промежуточное копирование в обычный блоб:
Единственно, вызывает сомнение оптимальность такого способа, т.к. при нем происходит копирование немаленького, как можно ожидать, поля из файловой системы (поле файлстрим) в SQL Server (блобовское поле). Затем мы апдейтим блоб, причем процедура апдейта, скорее всего, происходит по принципу кусок до + апдейт + кусок после через промежуточный сторидж, затем копирование модифицированного значения из SQL Server в файловую систему. В общем, по качественным оценкам этот способ должен быть в 3 раза дороже, чем прямое обновление. Прямое обновление файлстремного поля с клиента производится при помощи класса SqlFileStream, пример на использование которой можно почерпнуть из BOL: https://msdn.microsoft.com/en-us/library/cc645940.aspx. Я только чуть-чуть его подкорректировал. Клиент соединяется с SQL Server и производит сначала чтение поля файлстрим по схеме Импорт/экспорт блобовских полей в файлы - CLR\Скрипт 2, а затем его частичное обновление. Основная функция FileStreamPartialUpdate() сочетает параметры написанной ранее функции WriteBlobFieldToFile() с параметрами стандартной функции STUFF(). В первой группе параметров задаются координаты файстримовской ячейки в таблице: название таблицы, название колонки и гуид строки. Во второй – байтовый массив, который вставляем файлстрим, позиция, с которой вставляем, и сколько старых байт при этом выкидываем. Я использовал в ней обычные дотнетовские типы вместо SQL Serverных, например, string вместо SqlString, поскольку ей все равно не светит стать хранимой процедурой SQL Server. Рутинная операция - копирование последовательности байт со стрима на стрим, поэтому я создал для нее на основе старой функции CopyBytesBetweenStreams() (см. Импорт/экспорт блобов в файлы\Скрипт 7) две вспомогательные. Первая будет копировать из одного стрима в другой кусок байтов заданной длины, а другая – с этого места и до конца. Не бог весть что. Пришлось слегка изменить функцию CheckObjectsValidity(), проверяющую, что таблица и файлстримовская колонка в ней с такими именами действительно существуют в текущей базе. Такая проверка необходима, т.к. таблица и колонка передаются в виде своих строковых имен, чтобы никакому злоумышленнику не пришло в голову написать туда какой-нибудь SQL Injection. В прошлом посте сообщение об ошибке посылалось в Sql.Pipe, т.к. это было SQL Serverной хранимой процедурой. Здесь пришлось переделать вывод на System.Diagnostics.Debug.Writeline().
Скрипт 1 Стоит еще учесть https://blogs.msdn.com/psssql/archive/2008/04/10/how-it-works-file-streams-requires-integrated-security-windows-authentication.aspx: а) SQL Server (в смысле, учетная запись сервера) has to have access to the files in order to handle the file stream file groups. Доступ происходит через шару (UNC-like facility) serverproperty ('FilestreamShareName'), так что, например, LocalSystem не катит. б) Юзер, от имени которого будет выполняться Скрипт 1, is making the call to OpenSqlFileStream that ends up calling NtCreateFile. To validate the user can access the file under the transaction the user is impersonated and checked by SQL Server for proper transaction access. Since the transaction was opened under mixed security the impersonation will fail. Сказанное не означает, что нужно бросаться в SSMS -> Server Properties -> Security менять Server Authentication. Если там стоит SQL Server and Windows Authentication mode, то и на здоровье. Сказанное означает, что при выполнении Скрипта 1 логиниться на SQL Server нужно под виндузовым логином. Короче, Integrated Security=true в строке соединения менять на SQLный логин низя. Как ни странно, данный код выполнился у меня далеко не с первого раза несмотря на то, что нечто похожее я показывал сто раз на TechDays и семинарах Russian SQL Server User Group. Неожиданно при создании базы перестала создаваться сетевая шара, через которую организуется логический путь к файлу, соответствующему ячейке. См. "Загрузка данных в filestream", Способ 4, функция PathName() от файлстримовского блоба. Соответственно, конструктор new SqlFileStream(filePath.Value, txCtx, FileAccess.ReadWrite) долго искал путь, указанный в первом аргументе, и в конце концов отсылал с Win32Exception: "The specified server cannot perform the requested operation". Если бы он, собака, при этом писал что-нибудь более осмысленное типа Network name not found, я бы, наверно, допер быстрее, что ему не хватает для счастья, а так толком не знаешь, на что грешить. Короче, select serverproperty ('FilestreamShareName') нормально возвращает ту шару, которую я прописывал в SQL Configuration Manager – см. "Конфигурирование FILESTREAM"\Рис.2, а если посмотреть шары по факту (напр., в net share в командной строке), ее там не значится. Как выяснилось, подобные вещи происходили еще в RC0 - http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=352984 и связаны они, по всей видимости, со своеобразным поведением драйвера RsFx на виртуалке, потому что у меня этот баг тоже проявляется только на Hyper-Vшных гостях. Поскольку баг в то время благополучно прикрыли, сославшись на его невоспроизводимость, он не менее благополучно переполз в релиз. Побороть его очень просто. Нужно зайти в "Конфигурирование FILESTREAM"\Рис.2 и сменить имя шары. С ходу вводить в текстбокс Windows share name ничего не нужно. Нужно отжать галку Enable filestream for file I/O streaming access и нажать ОК. После этого снова зайти сюда же в SQL Configuration Manager, нажать эту галку, ввести новое имя шары и перестартовать SQL Server. До этого она у меня называлась по умолчанию MSSQLSERVER, я задал имя fs. После этих манипуляций шара стала видеться в списке шар рис. 2 (первый результат – до переименования, второй – после) и Скрипт 1 выполнился нормально. Что касается кода, демонстрирующего частичное обновление файлстрима, то вначале идем в SSMS и присваиваем файлстримовскому полю первоначальное значение из файла:
Заодно читаем гуид записи, затем переходим в VS и подставляем его в в кач-ве параметра при вызове ф-ции FileStreamPartialUpdate(). Выполняем Скрипт 1 и убеждаемся (select cast(stream as ...), что подстрока в файлстриме в этой записи заменилась. В случае загрузки в файлстрим текста в юникодовской кодировке рис. 3 Скрипт 1 остается практически без изменений, поскольку в нем мы оперируем над байтами, а не над символами. Единственно, в параметрах вызова нужно поменять кодировку и все длины и смещения умножить на 2.
Те посетители, кто счел возможным потратить несколько минут, пролистав предыдущий пост "Загрузка данных в filestream", знают, что по-любому Скрипт 1 обречен на клиентское существование. Вынести его на сервер в виде какой-нибудь красивой процедуры или функции не судьба благодаря Win32шной функции OpenSQLFileStream, зарытой внутри класса SqlFileStream. Таким образом, некоторая ущемленность файлстримовских полей по сравнению с остальными блобами все-таки остается. Представьте себе, что для обычных строковых типов у нас есть функция SUBSTRING, а чтобы сделать STUFF, Books On-Line говорят, да идите вы в Win32 API. Как быть со всякими юзабилити и прочими ease-of-use, которые у нас от версии к версии неуклонно растут круче, чем благосостояние трудящихся в эпоху социализма? Я долго комплексовал по этому поводу, покамест по некотором размышлении не пришел к выводу, что частичный апдейт файлстрима средствами SQL Server являет собой экзотическую штуку, достаточно несбыточную в практических сценариях. Что мы храним в файлстримных полях? В целом, неструктурированный контент, который с точки зрения SQL Server есть абстрактный поток бинарщины: документы, аудиозаписи, видеофайлы и пр. Задача SQL Server есть прочитать все это безобразие по запросу клиента и отдать ему в то место, куда он скажет. Там, очевидно, у него (клиента) имеется специализированная клиентская приблуда, которая понимает этот бинарный формат и умеет с ним делать что-то осмысленное: редактировать документ, показывать киношку и т.д. SQL Serverу это по-большому счету до лампады, потому что ни Office, ни Media Player, ни что-нибудь еще на данный момент просто не обучены лазить напрямую в SQL Server. Они традиционно работают с файловой системой. Когда мы отредактировали вордовый документ и сохраняем его на диск, там ведь тоже частичным обновлением особо не пахнет. Документ перезаписывается весь и целиком, так что мы можем брать и загонять его на SQL Server обычным полным апдейтом. В этом его отличие от обычных (нефайлстремных) varchar/varbinary(max), которые хранятся в виде btree, следовательно, более приспособлены для апдейта нескольких байт где-нибудь посередке. Я не сомневаюсь, что практические применения частичного апдейта найдутся и для файлстрима. Например, во время проведения весенних TechDays в Екатеринбурге мне посчастливилось иметь очень плодотворную дискуссию со слушателями в перерыве. Один из них (увы, я не запомнил, как его зовут, о чем сожалею), предложил сценарий, с которым я с ходу согласился. Речь шла об обновлении MP3шных тэгов. Более того, я бы даже развил эту идею. Масса файлов обладают метаинформацией, которую вынуждены хранить у себя в заголовке, подвале или где-нибудь еще в своем формате. Зачем далеко ходить за примером? Тот же ворд сейчас готов снабдить этот документ свойствами Author, Title, Subject, Keywords и др. Однако с точки зрения хранения файлов в базе подобные свойства, тэги, атрибуты и т.д. – это, скорее, поля. Им не место внутри контента, их нужно из него вытаскивать и атрибутировать где-нибудь сбоку. Нынешнее положение дел, когда каждый изобретатель формата документа был вынужден предусматривать закуток, где складировать его основные метаданные, вместо того, чтобы завести эти атрибуты на уровне файловой системы (наоборот, это файловая система, знакомая с данным форматом, может залезть в документ и вытащить из него эти свойства на свой уровень), объясняется эволюционно устоявшимся подходом, сложившимся в силу недостаточных возможностей файловых систем на заре развития и слабой их интеграцией, но на данный момент NTFS и SQL Server имеют между собой много общего: структурированные свойства файловых объектов, транзакционность, права доступа на данные и операции, компрессия, шифрование, резервное копирование, полнотекстовый поиск и т.д. Осталось научить клиента обращаться за данными не к файловой системе, а к базе данных. Вообще, кто сказал, что данный документ – это файл? Когда вы будете его просматривать, он наверняка достанется из SQL Serverной базы, да и в моем случае, будучи подопытным объектом, он столько раз в разных скриптах писался в базу и обратно, что, по-моему, это просто запись в таблице, и ворду нужно только научиться при открытии/закрытии документа выводить не список файлов в диалоговом окне, а коннектиться к SQL Server и выводить список записей из нужной таблицы. Осталось написать Add-on, который заставит Ворд взаимодействовать с SQL Serverной базой вместо файлового тома. В принципе, шаропойнт это уже умеет. Автор: Алексей Шуленин |