Частичное обновление FILESTREAM
Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. "Введение в FILESTREAM", "Конфигурирование FILESTREAM. FILESTREAM и сборка мусора (удаление старых версий файлов)", "Загрузка данных в filestream". Под FILESTREAMом понимается возможность хранения больших бинарных объектов (BLOBов) в SQL Server 2008 в файловой системе.
Частичное обновление FILESTREAM
С точки зрения функциональности отличие файлстрима от обычного блоба проявляется в невозможности частичного обновления. Возьмем в качестве примера таблицу Media, с которой мы игрались на протяжении темы. База TestFS была создана в первом посте темы.
use TestFS
if exists (select 1 from sys.tables where name = 'Media' and schema_id = schema_id()) drop table Media
create table Media (
id int identity primary key,
[guid] uniqueidentifier default newid() unique rowguidcol not null,
[fileName] nvarchar(256), contentType nvarchar(256),
clob nvarchar(max), blob varbinary(max),
stream varbinary(max) filestream)
Таблица имеет для сравнения большое символьное поле clob, большое бинарное поле blob и большое бинарное поле stream, хранящееся в файловой системе. Поле uniqueidentifier unique rowguidcol not null является обязательным требованием при наличии поля файлстрим, а поле id я ввел для удобства, чтобы не писать всякий раз длинный гуид в условии where. Положим в нее какой-нибудь файл в качестве записи для примера (см. пост "Импорт/экспорт блобов в файлы"):
insert Media([fileName], contentType, stream) select 'c:\Demo\Частичное обновление FILESTREAM.txt', 'Народные промыслы', BulkColumn from openrowset(bulk 'c:\Demo\Частичное обновление FILESTREAM.txt', single_blob) t
update Media set blob = stream, clob = cast(stream as varchar(max)) where id = 1
select clob from Media where id = 1
рис.1
Делается cast(stream as varchar(max)), а не nvarchar, потому что файл был сохранен в кодировке Windows 1251, а не юникодовской.
Практически все строковые функции Т-SQL не делают разницы файлстримовский блоб – не файлстримовский и работают с ним совершенно одинаково:
select cast(substring(stream, 351, 32) as varchar(max)) from Media where id = 1
-------------------------------
Частичное обновление FILESTREAM
за исключением частичного обновления. Полное обновление файлстрима – не вопрос, см., напр., Импорт/экспорт блобов в файлы\Скрипт 1, FILESTREAM и старые версии файлов\Рис.4 и т.д. Частичное обновление нефайлстримовского блоба тоже:
update Media set blob.Write(cast('Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12' as varbinary(max)), 350, 32) where id = 1
select cast(blob as varchar(max)) from Media where id = 1
--------------------------------------------------------------------------------
Настоящим постом предполагается завершить тему "Введение в FILESTREAM" – см. "Введение в FILESTREAM", "Конфигурирование FILESTREAM. FILESTREAM и сборка мусора (удаление старых версий файлов)", "Загрузка данных в filestream". Под FILESTREAMом понимается возможность хранения больших бинарных объектов (BLOBов) в SQL Server 2008 в файловой системе.
Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12
С точки зрения функциональности отличие файлстрима от обычного блоба проявляется в невозможности частичного обновления...
Однако частичный апдейт файлстримовских полей невозможен по определению:
update Media set stream.Write(cast('Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12' as varbinary(max)), 350, 32) where id = 1
Msg 5538, Level 16, State 1, Line 1
Partial updates are not supported on columns that have a FILESTREAM as a source.
Первое, что приходит в голову, - осуществлять частичный апдейт файлстрима через промежуточное копирование в обычный блоб:
declare @x varbinary(max)
select @x = stream from Media where id = 1
set @x.Write(cast(' Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12' as varbinary(max)), 350, 32)
update Media set stream = @x where id = 1
select cast(stream as varchar(max)) from Media where id = 1
Единственно, вызывает сомнение оптимальность такого способа, т.к. при нем происходит копирование немаленького, как можно ожидать, поля из файловой системы (поле файлстрим) в 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().
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Data.SqlTypes;
namespace FileStreamClient
{
class Program
{
/// <summary>
/// Общие переменные
/// </summary>
static SqlConnection cnn;
static void Main(string[] args)
{
cnn = new SqlConnection("Server=(local);Database=TestFS;Integrated Security=true");
cnn.Open();
FileStreamPartialUpdate("Media", "Stream", new Guid("4C8A4900-70BD-4CD3-B72C-1D0658400D8E"),
Encoding.GetEncoding(1251).GetBytes("Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12"), 350, 32);
cnn.Close();
}
/// <summary>
/// Обновление поля FileStream, аналог ф-ции Stuff().
/// Рассматривается модификация фрагмента поля, т.к. она не может быть достигнута ср-вами Т-SQL.
/// </summary>
/// Координаты файлстримовской ячейки.
/// <param name="tblName">Название таблицы</param>
/// <param name="colName">Название колонки</param>
/// <param name="guid">Строка. Задается гуидом, т.к. в табл. с полем файлстрим по опр. д. входить еще поле uniqueidentifier unique rowguidcol not null</param>
/// Параметры апдейта
/// <param name="toInsert">Какую строку вставляем</param>
/// <param name="startPosition">С какого места</param>
/// <param name="substituteExsting">Сколько прежних байт при этом затираем</param>
static void FileStreamPartialUpdate(string tblName, string colName, Guid guid, byte[] toInsert, long startPosition, long substituteExsting)
{
//Проверяем, что таблица и файлстримовская колонка в ней действительно существуют под такими именами:
if (!CheckObjectsValidity(tblName, colName)) return;
//Теперь собираем инфу для инициализации класса SqlFileStream: псевдопуть файлстримовской ячейки и текущий контекст транзакции.
//Требуется открыть транзакцию, иначе GET_FILESTREAM_TRANSACTION_CONTEXT() возвращает NULL.
SqlTransaction tx = cnn.BeginTransaction();
SqlCommand cmd = new SqlCommand(); cmd.Connection = cnn; cmd.Transaction = tx;
cmd.CommandText = "select " + colName + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() from " + tblName + " where $rowguid = @guid"; //Псевдопуть файлстримовской ячейки, потребутся для инициализации SqlFileStream.
cmd.Parameters.Add(new SqlParameter("@guid", guid));
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.SingleRow); sdr.Read();
//Получаем псевдопуть:
string filePath = sdr.GetSqlString(0).Value;
//Получаем контекст транзакции:
byte[] txCtx = sdr.GetSqlBytes(1).Value;
sdr.Close();
//Открываем SQLный файлстрим
SqlFileStream sfs = new SqlFileStream(filePath, txCtx, FileAccess.ReadWrite);
//Апдейт производится по схеме: кусок до + апдейт + кусок после. Кусок до и кусок после читаются
//из SqlFileStream, апдейт - это п-р toInsert. Результат собирается в каком-то временном назначении,
//а потом переносится обратно в файлстрим.
//В случае небольших объемов для временного назначения можно использовать MemoryStream или byte[].
//Я возьму по максимуму временный файл.
//Открываем временный файловый стрим под результат
FileStream fs = new FileStream(Path.GetTempFileName(), FileMode.Create);
//Копируем в него начальный кусок из файлстрима
CopyChunkBetweenStreams(sfs, 0, startPosition, fs, 0, 8192);
//Копируем апдейт
fs.Write(toInsert, 0, toInsert.Length);
//Пропускаем в файлстриме подлежащие замене байты
sfs.Seek(substituteExsting, SeekOrigin.Current);
//Копируем из файлстрима оставшийся кусок
CopyBytesUpToEndBetweenStreams(sfs, sfs.Position, fs, fs.Position, 8192);
//Перекладываем назад в файлстрим
sfs.SetLength(0); //очищаем стрим
CopyBytesUpToEndBetweenStreams(fs, 0, sfs, 0, 8192);
//Подчищаем за собой
fs.Close(); File.Delete(fs.Name);
sfs.Close(); tx.Commit();
}
/// <summary>
/// Это вспомогательная функция, которая копирует кусок байтов из одного стрима от оговоренного места и до конца
/// в другой стрим.
/// </summary>
/// <param name="sourceStream">Стрим, откуда копируем</param>
/// <param name="sourceOffset">Начиная с какого места</param>
/// <param name="destStream">Стрим, куда копируем</param>
/// <param name="destOffset">Начиная с какого места</param>
/// <param name="bufferLen">Емкость ведра, которым перечерпываем бочки</param>
static private void CopyBytesUpToEndBetweenStreams(Stream sourceStream, long sourceOffset, Stream destStream, long destOffset, int bufferLen)
{
sourceStream.Seek(sourceOffset, SeekOrigin.Begin); destStream.Seek(destOffset, SeekOrigin.Begin);
byte[] buffer = new byte[bufferLen]; int count;
for (; ; )
{
count = sourceStream.Read(buffer, 0, buffer.Length);
if (count == 0) break;
destStream.Write(buffer, 0, count);
}
}
/// <summary>
/// Это еще одна вспомогательная функция, которая делает то же, что и первая, но только байты копируются не до конца
/// первого стрима, а переливается кусок байтов определенной длины.
/// </summary>
/// <param name="sourceStream">Стрим, откуда копируем</param>
/// <param name="sourceOffset">Начиная с какого места</param>
/// <param name="chunkLen">Сколько байт</param>
/// <param name="destStream">Стрим, куда копируем</param>
/// <param name="destOffset">Начиная с какого места</param>
/// <param name="bufferLen">Емкость ведра</param>
static private void CopyChunkBetweenStreams(Stream sourceStream, long sourceOffset, long chunkLen, Stream destStream, long destOffset, int bufferLen)
{
sourceStream.Seek(sourceOffset, SeekOrigin.Begin); destStream.Seek(destOffset, SeekOrigin.Begin);
byte[] buffer = new byte[bufferLen]; int count; long totalBytesRead = 0;
for (; ; )
{
count = sourceStream.Read(buffer, 0, Math.Min(buffer.Length, (int)(chunkLen - totalBytesRead)));
totalBytesRead += count;
destStream.Write(buffer, 0, count);
if (count == 0 || totalBytesRead >= chunkLen) break;
}
}
/// <summary>
/// Проверяет, что в текущей базе существует таблица с таким именем и в ней колонка с таким именем.
/// Какая-никакая защита от injection.
/// </summary>
/// <param name="tblName">Имя таблицы</param>
/// <param name="fldName">Имя колонки</param>
/// <returns></returns>
private static bool CheckObjectsValidity(SqlString tblName, SqlString colName)
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = "select count(1) from sys.tables where name = @tblName";
cmd.Parameters.Add(new SqlParameter("@tblName", tblName));
if ((int)cmd.ExecuteScalar() == 0)
{
Debug.WriteLine(String.Format("Таблица {0} не найдена в текущей базе {1}!", tblName, cnn.Database));
return false;
}
cmd = cnn.CreateCommand();
cmd.CommandText = "select count(1) from sys.columns where name = @colName and object_name(object_id) = @tblName and type_name(system_type_id) = 'varbinary' and max_length = -1";
cmd.Parameters.Add(new SqlParameter("@colName", colName)); cmd.Parameters.Add(new SqlParameter("@tblName", tblName));
if ((int)cmd.ExecuteScalar() == 0)
{
Debug.WriteLine(String.Format("Колонка {0} типа varbinary(max) не найдена в таблице {1}!", colName, tblName));
return false;
}
return true;
}
}
}
Скрипт 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 - https://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 и присваиваем файлстримовскому полю первоначальное значение из файла:
use TestFS
update Media set stream = (select BulkColumn from openrowset(bulk 'c:\Demo\Частичное обновление FILESTREAM.txt', single_blob) t) where id = 1
select cast(stream as varchar(max)) from Media where id = 1
select guid from Media where id = 1
Заодно читаем гуид записи, затем переходим в VS и подставляем его в в кач-ве параметра при вызове ф-ции FileStreamPartialUpdate(). Выполняем Скрипт 1 и убеждаемся (select cast(stream as ...), что подстрока в файлстриме в этой записи заменилась.
В случае загрузки в файлстрим текста в юникодовской кодировке
рис.3
Скрипт 1 остается практически без изменений, поскольку в нем мы оперируем над байтами, а не над символами. Единственно, в параметрах вызова нужно поменять кодировку и все длины и смещения умножить на 2.
...
FileStreamPartialUpdate("Media", "Stream", new Guid("4C8A4900-70BD-4CD3-B72C-1D0658400D8E"),
Encoding.Unicode.GetBytes("Элементарное введение в базовые основы SQL Server для начинающих. Кн.8, ч.II, т.12"), 350 * 2, 32 * 2);
...
а в Т-SQLном скрипте поменять varchar на nvarchar:
select cast(stream as nvarchar(max)) from Media where id = 1
Те посетители, кто счел возможным потратить несколько минут, пролистав предыдущий пост "Загрузка данных в 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ной базой вместо файлового тома. В принципе, шаропойнт это уже умеет J.