Столбец filename имя файла в таблице в sql показывает
Изучение задач, связанных с программированием баз данных на платформе MS SQL Server.
четверг, 17 июля 2014 г.
Файловые таблицы
В базе данных должна быть файловая группа 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.
Когда база данных настроена на использование файловых таблиц можно проверить ее настройки, сделав такой запрос:
where database_id = db_id ( 'FileTablesWrk' )
Теперь можно создать файловую таблицу. Файловые таблицы имеют предопределенную структуру, поэтому для них не требуется указывать столбцы. Код на создание такой:
Здесь Data1 это директория, которая содержится в директории FilesCont. Указываются также параметры сортировки для содержимого таблицы. Список файловых таблиц можно посмотреть с помощью запроса:
from sys . filetables
Когда таблица создана, к ней можно сделать запрос и посмотреть на столбцы. Есть столбец file_stream типа varbinary ( max ), содержащий содержимое файла. Столбец name это имя файла. Столбец file_type - расширение, его можно использовать для полнотекстового индексирования; is_directory - это признак директории. Если в папке Data1 есть вложенные папки, то с помощью столбцов path_locator и parent_path_locator можно определить в какой директории находится файл. Можно сделать вставку строки в таблицу средствами Transact-SQL:
values ( 'test.txt' , cast ( 'www' as varbinary ( max ) ) )
Можно сделать запрос и убедиться, что строка вставлена:
Видно, что в таблице есть атрибуты файла, например, даты создания, доступа, признак системного файла. Все эти атрибуты можно менять с помощью инструкции update к таблице, в результате чего эти атрибуты изменятся и у самого файла:
Надо быть умнее.
суббота, 3 мая 2014 г.
SQL 2012 - FILESTREAM и FILETABLE
Начиная с 2008 версии в SQL сервере появилась возможность хранить данные (фото, музыку, видео и прочие документы) типа VARBINARY ( MAX ) в файловой системе за пределами файла БД и управлять ими используя сам SQL сервер.
Данная опция называется FILESTREAM и задается она следующим образом:
Обязательно в таблице должен быть задан столбец с указателями UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE !!
В итоге в файловой системе в специальной папке появятся файлы с названиями типа GUID которые будут связаны с этой таблицей
Использовать эту опцию будет целесообразно если средний размер данных в столбце VARBINARY ( MAX ) больше 1-2 Мб.
Подробнее можно прочитать в книге The Art of SQL Server FILESTREAM (Jacob Sebastian & Sven Aelterman ).
Однако опция FILESTREAM имеет ряд недостатков это и односторонняя целостность (если удалить файл со стороны файловой системы, информация из таблицы связанной таблицы не будет удалена и работа с таблицей будет невозможна) и неудобная работа со стороны файловой системы ( GUID названия файлов, сложно определить какой файл соответствует данному GUID, его тип и реальное название).
В 2012 версии эта опция была дополнена надстройкой FILETABLE которая позволяет обращаться к данным по сетевому пути и работать с данными уже как с сетевой папкой читать, копировать, изменять, удалять файлы. Также добавлена двусторонняя ссылочная целостность (при удалении файлов удаляются и записи в таблицах) .
Для того чтобы воспользоваться опцией FILETABLE необходимо сделать всего лишь 5 простых шагов!
1.Включить на уровне службы SQL S erver опцию FILESTREAM
2.Разрешить использование FILESTREAM на уровне экземпляра SQL сервера
3.Создать файловую группу и файл для хранения данных FILESTREAM
Включить в группу FILESTREAM файл. По сути это даже не файл, а всего лишь указатель на папку где будут физически храниться данные FILESTREAM в системе,
в поле Path задайте путь для хранения данных FILESTREAM, например, C :\ Program Files \ Microsoft SQL Server \ MSSQL 11. MSSQLSERVER \ MSSQL \ DATA
Здесь необходимо задать две опции Filestream Directory Name = это подпапка которая будет создана в общей папке FILESTREAM для нашей БД
Выставить опцию Non - Transacted Access для доступа к файлам вне транзакций
Таблица FILETABLE имеет уже жестко определенную структуру поэтому необходимо указать лишь ключевое слово AS FILETABLE
Если при создании не указано имя каталога, то в качестве него используется имя самой таблицы.
FILETABLE_COLLATE_FILENAME указывает имя параметров сортировки, применяемых к столбцу Name в таблице FileTable.
Если значение не указано или задано как database_default, столбец унаследует параметры сортировки текущей базы данных.
Теперь можно перейти к тестированию нашего механизма!
Для это можно зайти на сервер по сетевому пути или выбрав в SSMS опцию Explore FileTable Directory
Видно что на сервере появилась общая папка mssqlserver, которая была
Выполним SQL запрос к нашей файловой таблице, видим чтофайлы появились в таблице MEDIA_DATA
в столбце File_stream находится наше содержимое файлов, поэтому не стоит делать запрос ко всей таблице вместе с этим полем)
SERVERPROPERTY ( 'FilestreamEffectiveLevel' ) AS EffectiveValue ,
SERVERPROPERTY ( 'FilestreamConfiguredLevel' ) AS WindowsConfiguredValue ,
SERVERPROPERTY ( 'FilestreamShareName' ) AS FilestreamShare
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA' ) TO FILEGROUP FILESTREAM_DATA ;
--включение не транзакционного доступа для таблиц FILETABLE в текущей БД
ALTER DATABASE MEDIA SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL , DIRECTORY_NAME = 'MEDIA' )
--проверить настройки доступа к данным FILESTREAM вне транзакций
SELECT non_transacted_access , non_transacted_access_desc , directory_name
Начиная с версии 2012, в SQL Server стала доступен новый формат хранения файловых данных – файловые таблицы.
Файловые таблицы (FileTables) – особый вид таблиц, который позволяет помимо хранения файлов практически неограниченного размера (с этим легко справлялся и FileStream, появившийся в версии 2008), также получать к ним доступ из совершенно сторонних приложений, которые даже не подключены к данной БД, при помощи обычных средств файловой системы Windows.
Также поддерживается и хранение папок.
Подготовка к работе
Работа файловых таблиц основана на технологии FileStream. Поэтому перед тем как создать файловую таблицу необходимо включить поддержку FileStream для данного экземпляра SQL Server.
Включение производится в два этапа.
После того как поддержка FileStream будет включена, необходимо добавить в БД файловую группу FileStream и создать в ней хотя бы один файл.
Теперь можно приступить непосредственно к работе с файловыми таблицами.
Создание файловых таблиц
Для того чтобы создать файловую таблицу достаточно воспользоваться простейшей командой Transact-SQL
Ключевым параметров в ней является параметр FILETABLE_DIRECTORY, который определяет имя условной «папки» в файловой системе, через которую и будет осуществляться доступ к файлам, хранящимся в таблице. Почему эта папка названа условной будет пояснено далее.
В вышеприведённой инструкции отсутствуют столбцы и их типы данных. Дело в том, что структура файловых таблиц строго фиксирована и создаётся вместе с таблицей автоматически. Эта структура исчерпывающе описана в документации [1].
Работа с файловыми таблицами средствами файловой системы
После создания файловой таблицы создаётся сетевая папка со следующим адресом:
\ \ имя _ хоста \ имя _ экземпляра SQL_Server \ FT \ имя _ условной _ папкиЭта условная сетевая папка и позволяет работать с содержимым файловых таблиц как с самыми обыкновенными файлами. При этом поддерживаются файлы любых форматов. Также можно добавлять целые папки.
При добавлении, изменении или удалении файлов из этой папки изменяются соответствующие данные в самой файловой таблице. Ниже показан пример подобного добавления файла в файловую таблицу.
Что же представляет собой данная условная» папка» на самом деле?
А, на самом деле в папке, в которой хранятся файлы БД создаётся подпапка с именем файла данных FileStream. В ней, в свою очередь, создаются ещё две вложенные друг в друга подпапки с именами в формате GUID.
Наконец в папке на самом нижнем уровне этой структуры имеются два файла.
В них и сохранён добавленный в БД файл.
Важно отметить, что при работе с файловыми таблицами при помощи файловой системы следует пользоваться именно условным представлением данной папки (сетевая папка). При непосредственном использовании, добавление будет просто проигнорированы СУБД, а изменение или удаление фактически выведет файловую таблицу из строя.Работа с файловыми таблицами средствами Transact-SQL и языков программирования общего назначения
Список файлов и папок
Одной из самых распространённых задач является получение списка файлов и папок хранящихся в БД.
Решить её можно при помощи несложного SQL запроса:
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].
Рассмотрим, как мы можем сохранять файлы, в частности, файлы изображений в базу данных. Для этого добавим в базу данных новую таблицу Images с четырьмя столбцами: Id (первичный ключ и идентификатор, имеет тип int), FileName (будет хранить имя файла и имеет тип nvarchar), Title (будет хранить заголовок файла и также имеет тип nvarchar) и ImageData (будет содержать бинарные данные файла и имеет тип varbimary(MAX)).
Определим код, в котором будут загружаться данные в таблицу:
После выполнения этой программы в базе данных появится соответствующая запись:
Извлечение файлов из базы данных
Затем в коде программы определим следующий метод:
В этом методе с помощью SqlDataReader мы получаем значения из БД и по ним создаем объект Image, который потом добавляется в список. И в конце смотрим, если в списке есть элементы, то берем первый элемент и сохраняем его на локальный компьютер. И после сохранения в папке нашей программы появится загруженный из базы данных файл.
ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
Описывает структуру каталогов, в которой файлы хранятся в таблицах FileTable. Describes the directory structure in which the files are stored in FileTables.
Руководство. Работа с каталогами и путями в таблицах FileTable How To: Work with Directories and Paths in FileTables
Следующие 3 функции можно использовать для работы с каталогами FileTable в Transact-SQL Transact-SQL : You can use the following 3 functions to work with FileTable directories in Transact-SQL Transact-SQL :
| Чтобы получить этот результат, выполните следующее. To get this result | Воспользуйтесь этой функцией Use this function |
|---|---|
| Получите корневой путь UNC для конкретной таблицы FileTable или для текущей базы данных. Get the root-level UNC path for a specific FileTable or for the current database. | FileTableRootPath (Transact-SQL) FileTableRootPath (Transact-SQL) |
| Получите абсолютный или относительный путь UNC к файлу или каталогу в таблице FileTable. Get an absolute or relative UNC path for a file or directory in a FileTable. | GetFileNamespacePath (Transact-SQL) GetFileNamespacePath (Transact-SQL) |
| Получите значение идентификатора path_locator для заданного файла или каталога в таблице FileTable, указав путь к нему. Get the path locator ID value for the specified file or directory in a FileTable, by providing the path. | GetPathLocator (Transact-SQL) GetPathLocator (Transact-SQL) |
Руководство. Как использовать относительные пути для переносимого кода How to: Use Relative Paths for Portable Code
Чтобы код и приложения были независимы от текущего компьютера и базы данных, следует избегать создания кода с использованием абсолютных путей. To keep code and applications independent of the current computer and database, avoid writing code that relies on absolute file paths. Вместо этого рекомендуется получать полный путь к файлу во время выполнения с помощью функций FileTableRootPath (Transact-SQL) и GetFileNamespacePath (Transact-SQL), как показано в приведенном ниже примере. Instead, get the complete path for a file at run time by using the FileTableRootPath (Transact-SQL) and GetFileNamespacePath (Transact-SQL)) functions together, as shown in the following example. По умолчанию функция GetFileNamespacePath возвращает относительный путь к файлу, находящемуся внутри корневого пути к базе данных. By default, the GetFileNamespacePath function returns the relative path of the file under the root path for the database.
Важные ограничения Important restrictions
Уровень вложенности Nesting level
ВАЖНО! IMPORTANT!! Нельзя хранить более 15 уровней вложенных каталогов в каталоге FileTable. You cannot store more than 15 levels of subdirectories in the FileTable directory. Если сохранено 15 уровней вложенных каталогов, каталог самого нижнего уровня не сможет содержать файлы, так как эти файлы представляют собой дополнительный уровень. When you store 15 levels of subdirectories, then the lowest level cannot contain files, since these files would represent an additional level.
Длина полного имени Length of full path name
ВАЖНО! IMPORTANT!! Файловая система NTFS поддерживает пути, намного превышающие ограничение в 260 символов, установленное в оболочке Windows и большинстве других функций Windows API. The NTFS file system supports path names that are much longer than the 260-character limit of the Windows shell and most Windows APIs. Поэтому можно создавать файлы в файловой иерархии FileTable с помощью Transact-SQL, которые нельзя будет просмотреть или открыть в Проводнике Windows и многих других приложениях Windows, поскольку полный путь превышает 260 символов. Therefore it is possible to create files in the file hierarchy of a FileTable by using Transact-SQL that you cannot view or open with Windows Explorer or many other Windows applications, because the full path name exceeds 260 characters. Однако с этими файлами вы можете продолжать работать с помощью инструкций Transact-SQL. However you can continue to access these files by using Transact-SQL.
Полный путь к элементу, хранящемуся в таблице FileTable The full path to an item stored in a FileTable
Полный путь к файлу или каталогу, сохраненный в таблице FileTable, начинается со следующих элементов. The full path to a file or directory stored in a FileTable begins with the following elements:
Общий ресурс с поддержкой доступа файлового ввода-вывода к данным FILESTREAM на уровне экземпляра SQL Server SQL Server . The share enabled for FILESTREAM file I/O access at the SQL Server SQL Server instance level.
Имя DIRECTORY_NAME на уровне базы данных. The DIRECTORY_NAME specified at the database level.
FILETABLE_DIRECTORY на уровне таблицы FileTable. The FILETABLE_DIRECTORY specified at the FileTable level.
В итоге иерархия выглядит следующим образом. The resulting hierarchy looks like this:
Важно иметь в виду, что иерархия каталогов, созданная в общем ресурсе FILESTREAM на уровне экземпляра, является виртуальной иерархией каталогов. It is important to keep in mind that the directory hierarchy created under the instance-level FILESTREAM share is a virtual directory hierarchy. Иерархия хранится в базе данных SQL Server SQL Server и не представлена физически в файловой системе NTFS. This hierarchy is stored in the SQL Server SQL Server database and is not represented physically in the NTFS file system. Все операции, осуществляющие доступ к файлам и каталогам в общем ресурсе FILESTREAM в таблицах FileTable, перехватываются и обрабатываются компонентом SQL Server SQL Server , внедренным в файловую систему. All operations that access files and directories under the FILESTREAM share and in the FileTables that it contains are intercepted and handled by a SQL Server SQL Server component embedded in the file system.
Семантика корневых каталогов на уровне экземпляра, базы данных и таблицы FileTable The semantics of the root directories at the instance, database, and FileTable levels
Эта иерархия каталогов имеет следующую семантику. This directory hierarchy observes the following semantics:
Общий ресурс FILESTREAM на уровне экземпляра настраивается администратором и хранится в виде свойства сервера. The instance-level FILESTREAM share is configured by an administrator and stored as a property of the server. Этот общий ресурс можно переименовать с помощью диспетчера конфигурации SQL Server SQL Server . You can rename this share by using SQL Server SQL Server Configuration Manager. Операция переименования вступает в силу только после перезапуска сервера. A renaming operation does not take effect until the server is restarted.
Параметр DIRECTORY_NAME уровня базы данных при создании базы данных по умолчанию имеет значение null. The database-level DIRECTORY_NAME is null by default when you create a new database. Администратор может задать или изменить это имя с помощью инструкции ALTER DATABASE . An administrator can set or change this name by using the ALTER DATABASE statement. Это имя должно быть уникальным (при сравнении без учета регистра) в этом экземпляре. The name must be unique (in a case-insensitive comparison) in that instance.
Обычно имя FILETABLE_DIRECTORY указывается в составе инструкции CREATE TABLE при создании таблицы FileTable. You typically provide the FILETABLE_DIRECTORY name as part of the CREATE TABLE statement when you create a FileTable. Это имя можно изменить с помощью команды ALTER TABLE . You can change this name by using the ALTER TABLE command.
Эти корневые каталоги нельзя переименовать с помощью операций файлового ввода-вывода. You cannot rename these root directories through file I/O operations.
Эти корневые каталоги нельзя открыть с использованием дескрипторов файлов для монопольного доступа. You cannot open these root directories with exclusive file handles.
Столбец is_directory в схеме FileTable The is_directory column in the FileTable schema
В приведенной ниже таблице описывается взаимодействие между столбцом is_directory и столбцом file_stream , в котором находятся данные FILESTREAM в таблице FileTable. The following table describes the interaction between the is_directory column and the file_stream column that contains the FILESTREAM data in a FileTable.
| is_directory значение is_directory value | file_stream значение file_stream value | Поведение Behavior |
| FALSE FALSE | NULL NULL | Это недопустимое сочетание, которое будет перехвачено системным ограничением. This is an invalid combination that will be caught by a system-defined constraint. |
| FALSE FALSE | Этот элемент представляет файл. The item represents a file. | |
| TRUE TRUE | NULL NULL | Этот элемент представляет каталог. The item represents a directory. |
| TRUE TRUE | Это недопустимое сочетание, которое будет перехвачено системным ограничением. This is an invalid combination that will be caught by a system-defined constraint. |
Использование имен виртуальной сети для групп доступности AlwaysOn Using Virtual Network Names (VNNs) with AlwaysOn Availability Groups
Если база данных, содержащая данные FILESTREAM или FileTable, принадлежит группе доступности: When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:
Функции FILESTREAM и FileTable принимают или возвращают имена виртуальной сети, а не имена компьютеров. The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names. Дополнительные сведения об этих функциях см. в разделе Функции Filestream и FileTable (Transact-SQL). For more information about these functions, see Filestream and FileTable Functions (Transact-SQL).
При осуществлении любого доступа к данным FILESTREAM или FileTable посредством API-интерфейса файловой системы будут использоваться имена виртуальной сети, а не имена компьютеров. All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names. Дополнительные сведения см. в разделе FILESTREAM и FileTable с группами доступности AlwaysOn (SQL Server). For more information, see FILESTREAM and FileTable with Always On Availability Groups (SQL Server).
Самородов Федор Анатольевич: Как работать с файлами из Transact-SQL
Иногда хочется поработать с файлами прямо из SQL-кода. Например, вывести в файл какую-нибудь отладочную информацию, выгрузить XML-данные, использовать текстовый файл для ведения журнала или сохранить отчёт в HTML-файле. А может, наоборот, прочитать из файла какие-то данные, конфигурационную информацию, импортировать содержимое CSV-таблицы или XML-источника.
Есть несколько способов получить доступ к файловой системе и сдержимому файлов из базы данных SQL Server. Один из них — задействовать штатные процедуры для работы с COM-объектами. Вот простой пример:
Читайте также: