Файловые таблицы

В Microsoft SQL Server 2012 появился новый вид таблиц: файловые таблицы. Эта технология расширяет существующую с 2008-ого года технологию filestream и предоставляет еще большую интеграцию между СУДБ MS SQL Server и файловой системой.
Что же такое файловая таблица? По сути дела такой таблице соответствует отдельная директория. Программа может просто обычным способом скопировать в эту директорию файл или каталог с другими файлами. При этом СУБД перехватит все вызовы по открытию и закрытию файлов и синхронизирует эти операции с таблицей. То есть, если скопировать в директорию файл и сразу сделать запрос к таблице, то запрос возвратит строку с новым файлом. То же самое относится и к операциям обновления и удаления файлов. Этот принцип действует и в обратную сторону: в таблицу можно вставлять записи, обновлять и удалять их, что инициирует соответствующие операции над файлами директории. При этом с файловыми таблицами можно (с небольшими ограничениями) работать как с обычными таблицами. Рассмотрим более подробно все эти операции на примере.
Сначала требуется включить технологию filestream на уровне сервера. Инструкция о том, как это сделать имеется в одной из моих предыдущих работ: Файловые потоки. Создадим базу данных:
create database FileTablesWrk

В базе данных должна быть файловая группа filestream:

alter database FileTablesWrk add filegroup FilesWrk contains filestream

alter database FileTablesWrk add file
    ( name = FilesStore, filename = 'C:\Users\edynak\Desktop\Files' ) to filegroup FilesWrk

Теперь необходимо задать директорию, которая будет хранить данные файловых таблиц для новой базы данных. Также требуется указать тип доступа к ней:
alter database FileTablesWrk set filestream
    ( directory_name = 'FilesCont', non_transacted_access = full )

В этом коде, помимо директории задана возможность нетранзакционного доступа. То есть можно просто создавать, обновлять, удалять файлы и папки в директории FilesCont, и эти операции будут синхронизированы с таблицей. В качестве опции можно использовать значение read_only для нетранзакционного доступа только на чтение. Или off. В последнем случае доступ возможен только на стороне сервера через Transact-SQL или через потоковый доступ с помощью SqlFileStream, а сами файлы будут невидимы в директории FilesCont. С помощью вышеприведенного кода можно менять имя директории и тип нетранзакционного доступа в процессе работы в зависимости от потребностей. Например, для обеспечения полной транзакционной целостности можно отключить нетранзакционый доступ и работать средствами T-SQL, а там, где важна высокая скорость и экономия буферного пула - использовать потоковый доступ через класс SqlFileStream. Если же нужна еще большая скорость вставки и приемлем риск того, что при аварийном завершении копирования результаты вставки могут не полностью откатиться, то можно использовать нетранзакционный доступ.
Полное имя директории FilesCont: //<CompName>/<ShareName>/FilesCont, где <CompName> - это имя компьютера, а <ShareName> это имя общей папки, которая выбирается при настройке доступа к filestream на уровне сервера. Чтобы получить это имя можно выполнить такой запрос: select filetablerootpath (). Этот путь на самом деле логический. Физический путь отличается. Логический путь преобразуется в физический драйвером фильтра filestream.
Когда база данных настроена на использование файловых таблиц можно проверить ее настройки, сделав такой запрос:

select *

from sys.database_filestream_options

where database_id = db_id ( 'FileTablesWrk' )

Теперь можно создать файловую таблицу. Файловые таблицы имеют предопределенную структуру, поэтому для них не требуется указывать столбцы. Код на создание такой:

create table dbo.TestFileData as filetable

       with

       (

             filetable_directory = 'Data1',

             filetable_collate_filename = database_default

       )

Здесь Data1 это директория, которая содержится в директории FilesCont. Указываются также параметры сортировки для содержимого таблицы. Список файловых таблиц можно посмотреть с помощью запроса:

select *

from sys.filetables

Когда таблица создана, к ней можно сделать запрос и посмотреть на столбцы. Есть столбец file_stream типа varbinary ( max ), содержащий содержимое файла. Столбец name это имя файла. Столбец file_type - расширение, его можно использовать для полнотекстового индексирования; is_directory - это признак директории. Если в папке Data1 есть вложенные папки, то с помощью столбцов path_locator и parent_path_locator можно определить в какой директории находится файл. Можно сделать вставку строки в таблицу средствами Transact-SQL:

insert into dbo.TestFileData ( name, file_stream )

       values ( 'test.txt', cast ( 'www' as varbinary ( max ) ) )

Можно сделать запрос и убедиться, что строка вставлена:

select *

from dbo.TestFileData

Видно, что в таблице есть атрибуты файла, например, даты создания, доступа, признак системного файла. Все эти атрибуты можно менять с помощью инструкции update к таблице, в результате чего эти атрибуты изменятся и у самого файла:

update dbo.TestFileData

       set last_access_time = '1900-01-01'

       where name = 'test.txt'

select last_access_time

from dbo.TestFileData

where name = 'test.txt'

Для того чтобы не хранить полное имя файла в приложении, можно пользоваться функцией GetFileNamespacePath, которая позволяет получить полное имя файла, зная строку файловой таблицы:

select file_stream.GetFileNamespacePath (), file_stream.GetFileNamespacePath ( 1 ), file_stream.GetFileNamespacePath ( 1, 2 )

from dbo.TestFileData

where name = 'test.txt'

Если функция запускается без параметров, то она дает путь, начиная с директории таблицы. К такому пути можно добавить путь, возвращаемый функцией filetablerootpath и получить полный путь. Либо можно воспользоваться первым параметром функции GetFileNamespacePath для получения полного пути. Если задать второй параметр этой функции равным двум, то можно вернуть полный путь, где в имени компьютера указано полное доменное имя.
Теперь создадим файл в директори:

, мы можем убедиться, что при создании файла, соответствующая строка появилась и в файловой таблице:

select cast ( file_stream as varchar ( max ) ), *

from dbo.TestFileData

Теперь файл можно обновить с помощью нетранзакционного доступа:

Как только мы нажимаем ctrl+S, то можем запустить запрос и убедиться, что данные синхронизированы с таблицей:

select cast ( file_stream as varchar ( max ) ), *

from dbo.TestFileData

Когда файл содержит данные и закрыт, то его не получится открыть с помощью такой программы как Notepad, так как файлы файловых таблиц не поддерживают отображение на память, но это можно сделать с удаленных компьютеров.
Пока файл открыт, то на строке есть блокировка и, например, такой запрос будет заблокирован:

select cast ( file_stream as varchar ( max ) ), *

from dbo.TestFileData with ( tablockx )

Если мы на уровне базы блокируем таблицу:

begin tran

select cast ( file_stream as varchar ( max ) ), *

from dbo.TestFileData with ( tablockx )

, то и при нетранзакционном доступе в папке ничего сделать нельзя. При попытке создать файл:

проводник зависает:

Если соединение делает откат или фиксацию транзакции, то проводник перестает висеть и файл создается:

Если выполнить сериализуемую блокировку одной строки, то в проводнике можно создавать и удалять файлы, но удалить файл, соответствующий заблокированной строке нельзя:

set transaction isolation level serializable

begin tran

select cast ( file_stream as varchar ( max ) ), *

from dbo.TestFileData with ( rowlock )

where name = 'test.txt'

Как видим поведение файловых таблиц согласуется с привычными правилами СУБД при параллельном доступе. Более того на файловых таблицах можно создавать триггеры. Приведем такой пример:

create table dbo.logs ( dtDate datetime not null )

go

create trigger dbo.TestFileData_ins on dbo.TestFileData for insert

as

begin

       if @@rowcount = 0

       begin

             return

       end

       insert into dbo.logs ( dtDate )

             values ( getdate () )

end

go

Теперь, если вставвить строку в таблицу средствами Transact-SQL или с помощью файловых операций, то триггер активируется и его код выполнится.
Доступ к файловым таблицам можно предоставлять с помощью привычных инструкций grant select, grant insert, grant update, grant delete. При этом, если пользователь не имеет такого доступа, то его запрос окончится ошибкой как при вызове запроса на сервере, так и при попытке работы с файловой таблицей через директорию. Теперь мы убедились, что для файловых таблиц справедливы основные законы обычных таблиц, можно даже делать полнотекстовый поиск. Есть несколько ограничений: нельзя создавать замещающие триггеры, не поддерживается секционирование и компоненты, использующие репликацию (репликация, cdc, change_tracking). Частично поддерживаются группы доступности и снэпшотные транзакции. Файловые таблицы, входят в бэкап базы данных.
Технология меня очень поразила. Это очень удобное средство для хранения файлов. Приложения могут через функции получать логические пути к директориям и копировать в них файлы обычными средствами. Затем приложения могут делать запросы к файловым таблицам, для извлечения информации о содержимом файлов. Файловые таблицы интегрированы с большинством важных компонентов СУБД. Потоковый доступ на чтение и запись, о котором я писал в статье о файловых потоках, также поддерживается, что гарантирует высокую скорость не только записи, но и чтения при выполнении потокового чтения (плюс транзакционная целостность). Для потокового доступа можно использовать тот же самый пример консольного приложения, который я привел в статье о файловых потоках, надо только подправить процедуру dbo.GetFilePath:

alter proc dbo.GetFilePath

(

       @vcFileName varchar ( 300 )

)

as

begin

       set nocount, xact_abort on

       declare @ShortName varchar ( 255 ) = reverse ( left ( reverse ( @vcFileName ), charindex ( '\', reverse ( @vcFileName ), 1 ) - 1 ) )

         

       select file_stream.PathName() [path]

          from dbo.TestFileData

       where name = @ShortName

end

go

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

select *

from sys.dm_filestream_non_transacted_handles

Если требуется закрыть все или некоторые дескрипторы, то можно воспользоваться процедурой sp_kill_filestream_non_transacted_handles.