Альтернативные методы организации и создания файловых информационных ресурсов

Ранее разработки в данном направлении уже велись, и в результате проделанной работы был создан продукт в виде десктопного приложения. К сожалению, он не получил широкого применения, так как использовался узким кругом сотрудников в довольно ограниченной сфере деятельности.

Приложение было разработано на языке программирования Visual Basic 6.0. Для описания содержимого файлов использовалось хранилище, реализованное на SQL server с типом данных varbinary(max).

Тип данных varbinary(max) позволяет записать в одну ячейку таблицы БД содержимое файла в двоичном виде объемом до 2 ГБ. Нет смысла излагать подробное описание работы продукта, так как на данный момент он устарел, хотя работает и активно используется до сих пор. Для инициализации файла используется проводник ОС. Содержимое файла преобразуется в бинарный тип данных с помощью функции VB и записывается в ячейку varbinary(max) таблицы. Так же в таблицу записываются внешние атрибуты файла, такие как название, расширение, размер и реквизиты пользователя приложения. При извлечении файла из базы данных, происходит преобразование данных в тот формат, который нам подскажет расширение файла, ранее записанное в базу данных. Все манипуляции с преобразованием форматов производятся с помощью Win32.    

Есть замечательное свойство SQL сервера и его системы управления базами данных (СУБД), с помощью которого можно создать хранилище данных, отвечающее на данный момент большинству требований, предъявляемых заказчиками подобных продуктов. Этим свойством является один из возможных типов хранения информации, предоставленных SQL сервером, который называется FILESTREAM.

Хранилище FILESTREAM появилось, начиная с SQL Server 2008, и предлагает впечатляющие улучшения производительности для хранения данных BLOB в виде полноценных файлов, управляемых операционной системой. Поясним, как воспользоваться преимуществами хранилища FILESTREAM — установим, настроим и напишем код.

Настройка сервера для хранилища FILESTREAM

Для настройки хранилища FILESTREAM требуются одновременно права администратора на ОС и на SQL сервер. Так же нужно предоставить SQL Server разрешение на работу напрямую с сервером приложений.

Если необходима потоковая передача Win32, потребуется использование встроенной системы безопасности Windows, так как входы в систему SQL Server взаимодействуют с ОС. Эти небольшие ограничения помогают защитить вашу инфраструктуру и данные.

Для того, чтобы включить функцию FILESTREAM, необходимо в диспетчере конфигурации SQL Server, зайти на страницу свойств для отдельного экземпляра SQL Server, который вы хотите включить. В процессе настройки необходимо указать, какие виды прав доступа и потоковой передачи необходимы для SQL Server и удаленных клиентов.

Код для настройки SQL Server для доступа к FILESTREAM

EXEC sp_configure 'filestream access level', 2
                                 GO
                                 RECONFIGURE
                                 GO

У функции FILESTREAM есть три варианта включения:

0 — отключает поддержку FILESTREAM для этого экземпляра;

1 — включает функции FILESTREAM для доступа к T-SQL;

2 — включает поддержку FILESTREAM для доступа T-SQL и Win32

После внесения изменений из диспетчера конфигурации SQL Server в хост-системе создается новый общий ресурс с указанным именем. Однако это не типичная общая папка, и она предназначена только для потокового взаимодействия очень низкого уровня между SQL Server и авторизованными клиентами. Таким образом, Microsoft рекомендует, чтобы только учетная запись службы, используемая экземпляром SQL Server, имела доступ к этому общему ресурсу. Более того, поскольку это изменение происходит на уровне ОС или службы, невозможно включить его из SQL Server, необходимо перезапустить экземпляр SQL Server, чтобы изменение вступило в силу.

После перезапуска необходимо включить SQL Server, чтобы использовать этот вариант хранения. Запустите sp_configure с соответствующим уровнем доступа к файловому потоку (0, 1 или 2) в соответствии с вашими потребностями.

Настройка баз данных для хранилища FILESTREAM

После настройки хранилища FILESTREAM на уровне ОС и SQL Server вы можете приступить к созданию таблиц для хранения ваших документов. Ключом для использования преимуществ хранилища FILESTREAM является добавление к выбранному полю varbinary(max) атрибута FILESTREAM. 

Лист  1. Код для создания базы данных и таблицы с поддержкой FILESTREAM

CREATE DATABASE FSTest 
  ON
    PRIMARY 
  ( NAME = Main, FILENAME = 'D:\SQLData\FSTest_main.mdf'),
  FILEGROUP FileStreamGroup1 
  CONTAINS FILESTREAM
  ( NAME = FSFiles, FILENAME = 'D:\SQLData\FSTest_files')
  LOG ON  
( NAME = Archlog1, FILENAME = 'D:\SQLData\FSTest_log.ldf')
GO
USE FSTest
GO
 
CREATE TABLE FileSamples (
  \[FileId\] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
  \[FileName\] VARCHAR(20) NOT NULL,
  \[FileData\] VARBINARY(max) FILESTREAM DEFAULT(0x)
)

После того, как создали в своей базе данных специальную файловую группу FILESTREAM, данные хранящиеся в этом столбце, не будут сохраняться в страницах или экстентах, управляемых SQL Server. Вместо этого они будут передаваться в файловую систему, где можно буде воспользоваться повышенной производительностью, когда речь идет о больших размерах файлов.  Обратите внимание, что на Лист 1 группа FILEGROUP, которая указана для использования с хранилищем FILESTREAM (с атрибутом CONTAINS FILESTREAM), на самом деле не указывает на такой файл, как files.ndf. Вместо этого она указывает на папку, в которой фактически будут храниться файлы и файловые данные. Таблицы с поддержкой FILESTREAM также требуют параметра UNIQUE ROWGUIDCOL для поддержки взаимодействия с хранилищем, на Лист 1 это сделано с помощью столбца FileId в примере таблицы. Аналогичным образом, в коде, показанном на Лист 1, также указывается значение по умолчанию (0x) в столбце FileData. Это значение по умолчанию заставляет SQL Server создавать новый пустой файл в папке хранилища FILESTREAM для клиентов потоковой передачи с помощью Win32, чтобы получить к ним доступ и использовать как путь при попытке загрузить содержимое файла. Без создания «пустого» файла по умолчанию новые файлы должны были бы создаваться путем потоковой передачи содержимого непосредственно через T-SQL, прежде чем к ним можно будет получить доступ через клиентские приложения Win32.

На Лист 2 показано типичное использование FILESTREAM, когда задействован только T-SQL. Оператор INSERT создает новую строку в таблице (где содержимое виртуального файла фактически сохраняется на диске в виде файла). Затем инструкция SELECT использует новую встроенную функцию GET_FILESTREAM_TRANSACTION_CONTEXT() и новую функцию .PathName() для получения контекста транзакции или идентификатора. Затем этот контекст транзакции или идентификатор передается клиентскому приложению Win32 для доступа к содержимому файла в указанном FileData.PathName().

Лист 2. Код для включения поддержки T-SQL для хранилища FILESTREAM

INSERT INTO FileSamples
SELECT NEWID(), 
  'newfile.txt',
  CAST(N'Some bogus "file" data' AS VARBINARY(MAX))

BEGIN TRANSACTION
SELECT \[FileData\].PathName(), 
  GET_FILESTREAM_TRANSACTION_CONTEXT()AS \[Context\]
FROM FileSamples
WHERE \[FileName\] = 'newfile.txt'
ROLLBACK

Клиентские приложения и хранилище FILESTREAM

В качестве реального примера использования данных FILESTREAM можно создать очень простое WinForms-приложение для отображения содержимого таблицы FileSamples, созданной ранее. Это простое приложение также позволяет пользователям загружать файлы со персонального компьютера (или из другого места) в хранилище SQL Server FILESTREAM.

В программном коде статьи, логика пользовательского интерфейса переведена в Form1.cs, а все взаимодействия FILESTREAM помещены во вспомогательный класс в файле FileStreamSample.cs. Этот класс, содержит три метода, метод извлечения списка текущих файлов в таблице FileSamples, и два метода позволяющих загружать и выгружать файлы по мере необходимости. Кроме того, поскольку файлы идентифицируются по столбцу UNIQUEIDENTIFIER FileId, логика всех трех методов тесно связана с идентификаторами GUID в качестве дескрипторов, помогающих выполнять прямые операции.

Код на Лист 3 создает новый файл, и с помощью оператора INSERT загружает метаданные файла. Затем код создает новую транзакцию, прикрепляет командный объект к транзакции (чтобы получить контекст транзакции) и выполняет соединение SqlDataReader, чтобы вернуть полный путь (в SQL Server) к файлу, который будет загружен, вместе с идентификатором контекста транзакции, который можно использовать для запуска процесса потоковой передачи файлов Win32.

Лист 3. Код для получения контекста транзакции

Guid fileId = Guid.NewGuid();
SqlConnection conn = new SqlConnection(connString);

conn.Open();
SqlCommand insert = new SqlCommand(
  "INSERT INTO FileSamples (\[FileId\], \[FileName\]) " +
  "VALUES (@FileId, @FileName)", conn);
insert.Parameters.Add("@FileId", 
  SqlDbType.UniqueIdentifier).Value = fileId;
insert.Parameters.Add("@FileName", 
  SqlDbType.VarChar, 20).Value = fileName;
insert.ExecuteNonQuery();

SqlTransaction fsTx = conn.BeginTransaction();
SqlCommand getTransaction = new SqlCommand(
  "SELECT \[FileData\].PathName(), " +
  "GET_FILESTREAM_TRANSACTION_CONTEXT() " +
  "FROM FileSamples " +
  "WHERE FileId = @FileID", conn);
getTransaction.Transaction = fsTx;
getTransaction.Parameters.Add("@FileId", 
  SqlDbType.UniqueIdentifier).Value = fileId;

SqlDataReader contextReader = getTransaction.ExecuteReader(CommandBehavior.SingleRow);
contextReader.Read();
string filePath = contextReader.GetString(0);
byte\[\] transactionId = (byte\[\])contextReader\[1\];
contextReader.Close();

Используя идентификатор контекста и фактический путь к хранилищу FILESTREAM для нового файла, код может затем инициировать прямую операцию потоковой передачи файлов с помощью класса System.Data.SqlTypes.SqlFileStream, впервые появившегося в .NET 3.5 SP1. Затем, см. Лист 4, создается новый объект System.IO.FileStream для локального чтения файла и буферизируются байты порциями по 512 КБ (произвольный выбор) в объект SqlFileStream, пока не останется байтов для передачи.

Лист 4. Код для потоковой передачи содержимого файла с помощью объекта SqlFileStream

using (FileStream fs = File.OpenRead(path))
\{
  using (SqlFileStream sqlFS = new SqlFileStream(filePath, transactionId, FileAccess.Write))
  \{
    byte\[\] buffer = new byte\[512 * 1024\]; 
    int location = fs.Read(buffer, 0, buffer.Length);
    while (location > 0)
    \{
  sqlFS.Write(buffer, 0, location);
  location = fs.Read(buffer, 0, buffer.Length);
    \}
  \}
\}
fsTx.Commit();
conn.Close();

На этом этапе можно закрыть транзакцию и файл успешно загружен на SQL Server, как данные файла, вместо обычных данных varbinary(max). Обратите внимание, поскольку содержимое файла передается в процесс Win32, необходимо использовать встроенную безопасность, так как входы в систему SQL не могут генерировать необходимые токены для доступа к базовой файловой системе.

Операции загрузки следуют той же парадигме создания новой транзакции, и получения идентификатора контекста транзакции и пути к файлу, который необходимо передать вашему клиенту. Когда дело доходит до операции потоковой передачи с загрузками, всё делается в обратном порядке — извлекаются данные из объекта SqlFileStream в буфер и проталкиваются в локальный объект FileStream до тех пор, пока не останется байтов для передачи.

Заключение.

Хранилище FILESTREAM стоит использовать во многих проектах. Помимо преимуществ в производительности, оно также позволяет превысить ограничение в 2 ГБ типа данных varbinary(max).

В хранилище FILESTREAM можно хранить большие двоичные объекты настолько больших размеров, насколько позволяет ОС.

Хранилище FILESTREAM также обеспечивает безопасность транзакций при хранении файлов и предлагает улучшенные возможности управления файлами, включая очевидное преимущество функций резервного копирования и восстановления файлов средствами SQL server. Другие важные преимущества включают в себя использование репликации и возможности полнотекстового индексирования непосредственно для файлов, хранящихся и управляемых SQL Server.

Не призываю повсеместно переводить все ФИР на новый метод хранения данных, описанный в статье. Использование данного метода хранения данных, по моему мнению, может подойти для хранения информации в высокой степенью конфиденциальности.