Импорт/экспорт блобовских полей в файлы - CLR
В предыдущей серии нашей картины рассматривались варианты импорта / экспорта файлов в BLOBы на SQL Server средствами T-SQL и CLR. Мы остановились на том, что написанная на CLR хранимая функция ReadBlob (Скрипт 7) работала только с файлами детского объема, выдавая ошибку конца памяти при попытке чтения более-менее крупных файлов. Различные вариации написания читалки не помогли:
MemoryStream ms = new MemoryStream(File.ReadAllBytes(fileName.ToString()));
SqlBytes blob = new SqlBytes(ms);
return blob;
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
MemoryStream ms = new MemoryStream();
CopyBytesBetweenStreams(fs, ms);
SqlBytes blob = new SqlBytes(ms);
return blob;
Попытки увеличить CLRную память SQL Serverу (см. стартапный параметр –G - https://msdn.microsoft.com/en-us/library/ms190737.aspx, https://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx) тоже.
рис.1
У меня, в общем-то, на виртуалке, где проводились эксперименты, ее и так негусто. В то же время T-SQLный способ чтения в блоб (см. Пред.пост\Скрипт 1) прекрасно работает на больших файлах, да и CLRная процедура записи блоба в файл (см. Пред.пост\Скрипт 7), в общем, тоже без вопросов
declare @x varbinary(max)
select @x = BulkColumn from openrowset(bulk 'c:\Temp\Book1.csv', single_blob) as stream
exec WriteBlob @x, 'c:\Temp\Book2.csv'
--00:00:42 туда-обратно. Размер Book1.csv ~ 150 метров.
Скрипт 1
Облом с CLRным чтением происходит из-за того, что соответствующий varbinary тип SqlBytes инициализируется массивом байт по размеру стрима. Понятно, что на здоровых файлах память при этом тут же заканчивается и до свидания, а по другому договориться с конструктором класса не удается. Вот здесь https://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/f52c7ca5-8d91-4c70-8090-d66aa79a4714 мужик в свое время тоже обломался на эту тему и пишет: какого хрена стрим должен полностью читаться в память? Преимуществом стрима является возможность оперировать с некоторым буфером в каждый момент времени, по барабану тянется стрим 1 кило или 100 гиг. Йа тут немного прошелся .NET-рефлектором (в смысле, это мужик пишет), и здаецца мне (ему), что хотя один из перегруженных конструкторов SqlBytes принимает тип Stream, внутри он все равно хочет все иметь в байтовом массиве. В отличие от мужика я не пользовался рефлектором, потому что лицензионное соглашение это как Устав для солдата. Это святое. Согласно военного Устава реверс-инжинирить ПО не положено. Точка. You may not reverse engineer, decompile, or disassemble the Software, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation. Поэтому будем исходить из общих соображений. Посмотрим внимательно на функцию ReadBlob. Куда она должна возвращать этот самый SqlBytes? В память, больше некуда. Неважно, что это будет: bytes[] или MemoryStream, на здоровый блоб ее все равно не хватит. Вывод: надо переделать ее, чтобы она сразу писала в какой-нибудь персистентный сторидж. Например, блобовское поле таблицы. В процессе экспериментов по затаскиванию / выносу в блобовское поле брался файл весом 150 метров. Поскольку экспериментов было много, то, чтобы не плодить старых версий файлстрима и не делать всякий раз чекпойнт (см. ранний пост на эту тему - https://blogs.msdn.com/alexejs/archive/2009/06/03/filestream-2.aspx), я упражнялся с обычным (нефайлстримовским) блобовским полем, добавив его в таблицу 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)
insert Media(stream) values (cast(N'aaa' as varbinary(max)))
Вот хранимая процедура, которая читает файл в varbinary(max) поле таблицы. Имя таблицы и имя колонки задаются в качестве параметров. Еще нам нужно определить ординату, т.е. запись, в поле которой пишем. Не мудрствуя лукаво, я накладываю в качестве требования к таблице наличие колонки uniqueidentifier unique rowguidcol, на которую, по определению, вне зависимости от ее имени можно сослаться так: $rowguid. Все равно, если varbinary(max) сопровождать атрибутом filestream, от такой колонки никуда не деться.
/// <summary>
/// Процедура читает файл в блобовское поле таблицы.
/// Пример вызова: exec ReadFileToBlobField 'Media', 'blob', 'B6DFBCA5-665D-4E8B-8BDB-8CA9CB0CC1A0', 'c:\Temp\Book1.csv'
/// </summary>
/// <param name="tblName">Имя таблицы</param>
/// <param name="colName">Название блобовской колонки</param>
/// <param name="guid">Идентификатор записи. Предполагается, что в таблица имеет поле uniqueidentifier unique rowguidcol, однозначно определяющее запись. </param>
/// <param name="fileName">Полное имя файла</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ReadFileToBlobField(SqlString tblName, SqlString colName, SqlGuid guid, SqlString fileName)
{
if (! CheckObjectsValidity(tblName, colName)) return;
SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
SqlCommand cmd = new SqlCommand("update " + tblName.ToString() + " set " + colName.ToString() + " = @colValue where $rowguid = @guid", cnn);
cmd.Parameters.Add("@colValue", SqlDbType.VarBinary);
cmd.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
FileStream fs = new FileStream(fileName.ToString(), FileMode.Open);
cmd.Parameters["@colValue"].Value = new SqlBytes(fs);
cmd.Parameters["@guid"].Value = guid;
cmd.ExecuteNonQuery();
fs.Close(); cnn.Close();
}
Скрипт 1
Пускаю с замиранием сердца, потому что предыдущий пост на сем действе, как вы помните, вылетал с нехваткой памяти,
select * from Media
dbcc dropcleanbuffers
dbcc freeproccache
exec ReadFileToBlobField 'Media', 'blob', 'B6DFBCA5-665D-4E8B-8BDB-8CA9CB0CC1A0', 'c:\Temp\Book1.csv'
Вы не поверите, но она работает J. Нынче все проходит гладко (тьфу-тьфу-тьфу). 150 метров со свистом втягиваются в SQL Server за 5 – 10 сек. Вдохновленный успехом, я решил написать еще закачку блобовского поля в файл, хотя особой надобности в этом не было. Процедура WriteBlob (см. Пред.пост\Скрипт 7) нареканий не вызывала. Можно было скопировать блоб в переменную и отправить его в файл этой процедурой. Ну пусть будет еще вариант напрямую для симметрии. Параметры процедуры будут такие же, как у предыдущей. Она будет выполнять запрос, вытаскивающий рекордсет из одной записи с одним полем. ExecuteScalar не проходит, не знаю, почему. Другие поля в списке вывода – пожалуйста, а как только блобовское – ThreadAbortException и до свидания. Ладно, я не гордый, взял DataReader. Вытащил из него блобовское поле (GetSqlBytes ()), остается сохранить его св-во Stream в файл. Как оптимальнее сохранить стрим в файл? Наверное, каждый по молодости озадачивался подобным вопросом. Полезные практические советы на эту тему можно почерпнуть здесь: https://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/dc7776e0-84b7-444c-9b82-15ff5ac5db41. Microsoft did not include such a method, but as you said you can easily implement it yourself. Фактически это функция CopyBytesBetweenStreams() из предыдущего поста. Я не передирал ее у тов. BinaryCoder, т.к. она присутствовала у меня еще в теме про частичные обновления filestream на sqlclub.ru минимум полгода назад. Но бесчестный человек Сергей Заворуев положил sqlclub и сбежал, так что сейчас все равно никому ничего не докажешь. Да черт с ним, три элементарные строчки.
/// <summary>
/// Процедура пишет блобовское поле таблицы в файл.
/// Пример вызова: exec WriteBlobFieldToFile 'Media', 'blob', '01588060-47FD-425B-997A-96375885395A', 'c:\Temp\Book2.csv'
/// </summary>
/// <param name="tblName">Имя таблицы</param>
/// <param name="colName">Название блобовской колонки</param>
/// <param name="guid">Идентификатор записи. Предполагается, что в таблица имеет поле uniqueidentifier unique rowguidcol, однозначно определяющее запись. </param>
/// <param name="fileName">Полное имя файла</param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WriteBlobFieldToFile(SqlString tblName, SqlString colName, SqlGuid guid, SqlString fileName)
{
if (!CheckObjectsValidity(tblName, colName)) return;
SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
SqlCommand cmd = new SqlCommand("select " + colName.ToString() + " from " + tblName.ToString() + " where $rowguid = @guid", cnn);
cmd.Parameters.Add(new SqlParameter("@guid", guid));
SqlDataReader sdr = cmd.ExecuteReader(); //ExecuteScalar этой же команды вызывает exception Thread aborted. DataAdapter, кстати, тоже. В момент sda.Fill(dt);
if (sdr.HasRows) sdr.Read();
FileStream fs = new FileStream(fileName.ToString(), FileMode.OpenOrCreate);
CopyBytesBetweenStreams(sdr.GetSqlBytes(0).Stream, fs);
fs.Close(); sdr.Close(); cnn.Close();
}
Скрипт 2
Экспорт того же файла занял порядка 20 сек.
И до кучи приводится вспомогательная внутренняя процедура к первым двум, которая проверяет, что такая таблица есть в базе, а в ней имеется поле varbinary(max) с таким именем, дабы не было искуса подсунуть в параметры процедур ReadFileToBlobField и WriteBlobFieldToFile вместо названия таблицы/колонки всякую гадость.
/// <summary>
/// Проверяет, что в текущей базе существует таблица с таким именем и в ней колонка с таким именем.
/// Какая-никакая защита от injection.
/// </summary>
/// <param name="tblName">Имя таблицы</param>
/// <param name="fldName">Имя колонки</param>
/// <returns></returns>
private static bool CheckObjectsValidity(SqlString tblName, SqlString colName)
{
SqlConnection cnn = new SqlConnection("context connection=true"); cnn.Open();
string dbName = cnn.Database;
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)
{
cnn.Close(); //Поскольку Pipe.ExecuteAndSend(cmd) открывает свое контекстное соединение, предыдущее к этому моменту нужно закрыть.
cmd = new SqlCommand("raiserror('Таблица %s не найдена в текущей базе %s!', 16, 1, '" + tblName.ToString() + "', '" + dbName + "')");
try { SqlContext.Pipe.ExecuteAndSend(cmd); } //raiserror поднимает еще .NETовскую ошибку Msg 6522, Level 16, State 1 ...
catch (SqlException) { }; //которая со всем стеком вызовов будет добавлена к пользовательской, если ее вовремя не перехватить.
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)
{
cnn.Close(); //Поскольку Pipe.ExecuteAndSend(cmd) открывает свое контекстное соединение, предыдущее к этому моменту нужно закрыть.
cmd = new SqlCommand(String.Format("raiserror('Колонка {0} типа varbinary(max) не найдена в таблице {1}!', 16, 1)", colName, tblName));
try { SqlContext.Pipe.ExecuteAndSend(cmd); } //raiserror поднимает еще .NETовскую ошибку Msg 6522, Level 16, State 1 ...
catch (SqlException) { }; //которая со всем стеком вызовов будет добавлена к пользовательской, если ее вовремя не перехватить.
return false;
}
cnn.Close(); return true;
}