Начиная с версии 2012, в SQL Server стала доступен новый формат хранения файловых данных – файловые таблицы.
Файловые таблицы (FileTables) – особый вид таблиц, который позволяет помимо хранения файлов практически неограниченного размера (с этим легко справлялся и FileStream, появившийся в версии 2008), также получать к ним доступ из совершенно сторонних приложений, которые даже не подключены к данной БД, при помощи обычных средств файловой системы Windows.
Также поддерживается и хранение папок.
Подготовка к работе
Работа файловых таблиц основана на технологии FileStream. Поэтому перед тем как создать файловую таблицу необходимо включить поддержку FileStream для данного экземпляра SQL Server.
Включение производится в два этапа.
- Включение поддержки FileStream на уровне сервера в окне его свойств;
- Включение поддержки FileStream для службы SQL Server данного экземпляра и её последующая перезагрузка. Это проще всего сделать при помощи Sql Server Configuration Manager.
После того как поддержка FileStream будет включена, необходимо добавить в БД файловую группу FileStream и создать в ней хотя бы один файл.
Теперь можно приступить непосредственно к работе с файловыми таблицами.
Создание файловых таблиц
Для того чтобы создать файловую таблицу достаточно воспользоваться простейшей командой Transact-SQL
|
1 2 3 4 5 6 7 |
CREATE TABLE TestFileTAble AS FILETABLE WITH ( FILETABLE_DIRECTORY = 'TestFT', FILETABLE_COLLATE_FILENAME = database_default ) GO |
Ключевым параметров в ней является параметр FILETABLE_DIRECTORY, который определяет имя условной «папки» в файловой системе, через которую и будет осуществляться доступ к файлам, хранящимся в таблице. Почему эта папка названа условной будет пояснено далее.
В вышеприведённой инструкции отсутствуют столбцы и их типы данных. Дело в том, что структура файловых таблиц строго фиксирована и создаётся вместе с таблицей автоматически. Эта структура исчерпывающе описана в документации [1].
Работа с файловыми таблицами средствами файловой системы
После создания файловой таблицы создаётся сетевая папка со следующим адресом:
|
1 |
\\имя_хоста\имя_экземпляра SQL_Server\FT\имя_условной_папки |
Эта условная сетевая папка и позволяет работать с содержимым файловых таблиц как с самыми обыкновенными файлами. При этом поддерживаются файлы любых форматов. Также можно добавлять целые папки.
При добавлении, изменении или удалении файлов из этой папки изменяются соответствующие данные в самой файловой таблице. Ниже показан пример подобного добавления файла в файловую таблицу.
Что же представляет собой данная условная» папка» на самом деле?
А, на самом деле в папке, в которой хранятся файлы БД создаётся подпапка с именем файла данных FileStream. В ней, в свою очередь, создаются ещё две вложенные друг в друга подпапки с именами в формате GUID.
Наконец в папке на самом нижнем уровне этой структуры имеются два файла.
В них и сохранён добавленный в БД файл.
Работа с файловыми таблицами средствами Transact-SQL и языков программирования общего назначения
Список файлов и папок
Одной из самых распространённых задач является получение списка файлов и папок хранящихся в БД.
Решить её можно при помощи несложного SQL запроса:
|
1 |
SELECT name, is_directory, path_locator, parent_path_locator FROM dbo.TestFileTAble |
Если значение поля is_directory равно 1, значит это папка (0 – обычный файл).
Поля path_locator и parent_path_locator – соответственно первичный и внешний ключи для хранения иерархических связей.
Например, поле parent_path_locator у файла WeatherForecast.apk указывает на папку а которой он расположен (см.скриншот).
Это позволяет, в частности, легко визуализировать структуру хранящихся данных.
Выгрузка файлов с помощью клиентской программы
Содержимое файлов в файловых таблицах физически хранится в формате varbinary(max) в поле file_stream.
Поэтому оно может быть без труда получено клиентской программы при помощи метода, который был рассмотрен ранее для хранения файлов универсальным способом [2].
Загрузка, изменение и удаление файлов с помощью клиентской программы
Загрузка файлов в файловую таблицу также не имеет принципиальных отличий от универсального способа загрузки в поле формата varbinary(max) [2].
Существуют некоторые ограничения связанные с поддержкой хранения папок и общей структурой таблицы [3]. В частности:
- Все столбцы атрибутов файла имеют ограничения NO NULL.Если значения не заданы явным образом, предоставляются соответствующие значения по умолчанию;
- Если инструкция INSERT устанавливает name, path_locator, parent_path_locator или атрибуты файлов, то применяются системные ограничения;
- Приложение может получить path_locator для файла или каталога при указании пути файловой системы для функции GetPathLocator (Transact-SQL);
- Разрешается обновлять любые данные, определяемые пользователем;
- Обновление данных FILESTREAM в столбце file_stream не влияет на другие столбцы, включая отметки времени;
- При удалении строки удаляется соответствующий файл или каталог из файловой системы (подробное пояснение см. выше);
- Невозможно удалить строку, если она относится к каталогу, который содержит другие файлы или каталоги.
Резюме
Использование файловых таблиц предоставляет ряд существенных преимуществ:
- Расширенные возможности работы за счёт доступа к данным средствами файловой системы;
- Уменьшение размера БД и увеличения быстродействия за счёт хранения больших объёмов данных во внешних файлах;
- Возможность хранения неограниченного объёма данных, так как данные файловых таблиц (как и «обычные» данные FileStream) не подпадают под ограничение 2ГБ для типа данных varbinary(max).
Недостатки:
- Более сложное развёртывание и сопровождение. В частности потому, что на файловые таблицы распространяются те же ограничения, что и на FileStream;
- Более сложное взаимодействие с клиентскими программами вследствие технологических ограничений;
- Несмотря на кажущуюся простоту и удобство использование файловых таблиц в значительной е снижает надёжность и отказоустойчивость.
Применение файловых таблиц, как и любой другой технологии должно быть, прежде всего, оправданным.
Несмотря на все свои преимущества это узкоспециализированное решение предназначенное, прежде всего для организации хранения файлов. Также не рекомендуется их использование для хранения файлов малого размера (менее 1 МБ)
Важно отметить, что для корректной работы необходимо организовать достаточную защиту физического места хранения данных файловой таблицы от несанкционированного доступа и предусмотреть дополнительные меры в плане резервного копирования и восстановления. Если же это по каким-либо причинам не возможно, лучше не рисковать и по возможности прибегнуть к «обычной» форме хранения данных в varbinary(max) [2].
Источники
Добавить комментарий