Хранение изображений сайта в БД

Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале. Сайт этот связан с музыкальной тематикой и, соответственно, активно отображает обложки альбомов, хранящиеся (до поры до времени) на сервере не в ФС, как обычно бывает, а в БД SQL Server (в BLOB-поле, о чём несложно догадаться); в старом варианте пора извлечения изображений из базы и сохранения их в виде файлов возникает в момент запроса альбомов пользователем, после чего (по истечении сессии) они удаляются.

Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.

Старая реализация

Прежде всего необходимо напомнить структуру HTML-тэга img, обязательным у которого является лишь атрибут src:

<img src="some_path/file_name.jpg">

У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.

На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.

Схематически и упрощённо описанное непотребство можно представить следующим образом:

Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:

1. Запрос на выборку данных возвращает, среди прочих, и BLOB-поле с обложкой (в данном случае на примере компонента spAlbums типа TFDStoredProc из состава FireDAC).
2. Изображение из поля, в зависимости от формата (png, jpg…), загружается в соответствующего наследника TGraphic.
3. Далее используется предоставляемая uniGUI функция uniImageToURL, сохраняющая изображение в нужную папку и возвращающая готовый URL для атрибута src.

uses   System.SysUtils, System.Classes, Data.DB,   Vcl.Graphics, Vcl.Imaging.jpeg, Vcl.Imaging.pngimage, Vcl.Imaging.GIFImg,   uniGUIUtils;  function TMainForm.AlbumsToHTML: string;    function CoverURL: string;   var     CoverFormat: string;     Image: TGraphic;     ImageStream: TStream;   begin     CoverFormat := spAlbums['CoverFormat'];      if CoverFormat = 'jpg' then       Image := TJPEGImage.Create;     if CoverFormat = 'png' then       Image := TPngImage.Create;     if CoverFormat = 'gif' then       Image := TGIFImage.Create;     if CoverFormat = 'bmp' then       Image := TBitmap.Create;      try       ImageStream := spAlbums.CreateBlobStream         (         spAlbums.FieldByName('Cover'),         bmRead         );       try         Image.LoadFromStream(ImageStream);       finally         ImageStream.Free;       end;        Result := uniImageToURL(Image);     finally       Image.Free;     end;   end;  const   AlbumHTMLTemplate =     '<div class="album">' +       '<div class="cover-container">' +         '<img src="%s" alt="Обложка альбома">' +         ...       '</div>' +     '</div>'; begin   spAlbums.First;    while not spAlbums.Eof do   begin     Result := Result + Format( AlbumHTMLTemplate, [CoverURL, ...] );     spAlbums.Next;   end; end;

Идеальный вариант

Если предаться мечтаниям и представить, что обложки по-прежнему находятся в БД в BLOB-поле, а IIS каким-то образом, почти без усилий и программирования с нашей стороны, сам извлекает их, то окажется, что SQL Server способен такую мечту исполнить – за счёт функционала FileTable, представляющего собой, если очень кратко, создаваемую разработчиком специального вида таблицу, каждая запись которой отображается в файл в NTFS; обращение к таким файлам ничем не отличается от стандартного (однако некоторые возможности не поддерживаются), т. е. их можно просматривать, изменять и удалять хоть через Проводник.

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

Данный вариант полностью совместим и с отложенной загрузкой изображений, настраиваемой скажем через атрибут loading, т. е. если пользователь не прокрутит страницу сайта до некоторых обложек, то они и не будут прочитаны из базы (написанное, разумеется, справедливо и для случая, когда в браузере вообще отключен показ изображений):

<img src="some_path/file_name.jpg" loading="lazy">

Чтобы воспользоваться этой манной небесной, ниспосланной SQL Server, потребуется, само-собой, выполнить некоторые действия как на стороне СУБД, так и на стороне IIS, чему собственно и посвящена оставшаяся практическая часть статьи. Но предварительно хотелось бы сделать небольшую ремарку и отметить, что конкуренты Microsoft тоже могут предложить тонкую работу с BLOB, но функционал уровня FileTable автору удалось найти лишь у Oracle – речь о Database File System (DBFS). Остальные же игроки предоставляют лишь условно традиционный (но неизбежно СУБД-специфичный) API, несколько примеров:

1. PostgreSQL
2. Interbase
3. MySQL

Кстати, использующим Express-редакцию SQL Server наверняка весьма понравится то, что данные в файловых таблицах не учитываются при проверке лимита на размер БД в 10 Гб, т. к. реализация FileTable основывается на технологии FILESTREAM, как раз и свободной от этого ограничения у бесплатной версии.

Настройка SQL Server

По умолчанию FileTable-возможности отключены в SQL Server. Официальная документация содержит весьма подробную и внятную инструкцию по параметрам, которые необходимо задействовать как на уровне всего экземпляра, так и в конкретной базе данных, отчего не представляется разумным пересказывать здесь подобные тонкости – вместо этого автор сосредоточится на небольшом SQL-скрипте, выполняющем перенос обложек из существующей таблицы с альбомами в новую FileTable-таблицу (если же в Вашем случае файлы изначально в БД не хранятся, в отличие от данного проекта, то с их адаптацией к FileTable поможет документация).

Таблицы

Далее в скрипте предполагается следующая структура двух только что упомянутых таблиц (источника – с данными об альбомах, и приёмника – новой таблицы с обложками):

• Источник dbo.Album:
  

  Поле	Тип данных	Описание
  ID	integer	Первичный ключ.
  Cover	varbinary(max)	BLOB-поле с собственно изображением, которое нужно перенести в новую таблицу.
  CoverFormat	nvarchar(10)	Тип изображения (jpg, png и т. д.).
  Прочие поля (название альбома, год издания и т. п.)…

• Приёмник dbo.AlbumCover имеет жёстко заданную структуру со множеством полей (задача создания такой таблицы тривиальна и описана в справке), но нас заинтересуют лишь эти:
  

  Поле	Тип данных	Описание
  path_locator	hierarchyid	Первичный ключ.
  file_stream	varbinary(max)	Содержимое файла (в нашем случае это изображение).
  name	nvarchar(255)	Название файла (с расширением), отображаемое в ФС.
  Прочие поля…

Так как обложка является подчинённой по отношению к альбому сущностью, то, имей мы обычную таблицу, в приёмник следовало бы добавить поле со ссылкой на альбом, однако набор полей файловой таблицы не может быть расширен, как чуть выше говорилось, поэтому придётся модифицировать источник: вместо двух полей Cover и CoverFormat он в конечном итоге станет содержать лишь одно – идентификатор обложки из поля path_locator:

Скрипт

Все действия с базой логически делятся на несколько этапов:

1. Банальнейшее по реализации добавление поля CoverID в источник:

   ALTER TABLE dbo.Album ADD CoverID hierarchyid;

2. Копирование данных в приёмник и заполнение только что созданного поля CoverID (про неожиданное применение конструкции MERGE пояснение дано ниже):

   DECLARE @Covers TABLE   (   AlbumID integer NOT NULL,   path_locator hierarchyid NOT NULL   );  MERGE dbo.AlbumCover AS cover USING   (   SELECT     ID,     CoverID,     Cover,     CAST( NEWID() as nvarchar(max) ) + '.' + CoverFormat AS FileName   FROM     dbo.Album   ) AS album ON album.CoverID = cover.path_locator WHEN NOT MATCHED THEN   INSERT (file_stream, name)   VALUES (album.Cover, album.FileName) OUTPUT   album.ID,   INSERTED.path_locator INTO @Covers;  UPDATE dbo.Album SET CoverID = path_locator FROM @Covers WHERE ID = AlbumID;

   Использование MERGE вместо конструкции INSERT продиктовано невозможностью в последней обратиться в блоке OUTPUT к полям любых задействованных таблиц (допустимы только поля той, в которую и происходит вставка). Другими словами, было бы нечем заполнить поле AlbumID табличной переменной @Covers, т. к. обращение ниже выполняется к таблице-источнику, а вставка идёт в dbo.AlbumCover:

   ... OUTPUT   album.ID, ...

3. Удаление ставших ненужными полей в источнике:

   ALTER TABLE dbo.Album DROP COLUMN Cover, CoverFormat;

   Важно отметить, что исчезновение полей не приведёт к уменьшению размера БД и возврату освободившегося места ОС – для этого потребуется явно выполнить команду SHRINKDATABASE.

Почти всё, теперь обложки доступны по специальному UNC-пути, возвращаемому функцией FileTableRootPath:

SELECT FileTableRootPath('dbo.AlbumCover');

Оговорка про «почти» связана с тем, что читатель может столкнуться с отсутствием доступа при попытке обратиться к означенным файлам – в этом случае следует ознакомиться с той частью следующего раздела, где описывается нюанс с правами. Также для определённости примем, что наш путь выглядит как \\ServerName\MSSQLSERVER\DatabaseName\AlbumCover (далее в другом примере будет отсылка к нему, где для наглядности потребуется конкретное значение).

Настройка веб-сервера

Хотя в текущем разделе демонстрация идёт на примере IIS, но большинство вещей применимы и к любому другому веб-серверу, ибо являются скорее ОС- и СУБД-специфичными.

Права на чтение

Если снова вернуться к атрибуту src тэга img, то становится очевидной небольшая проблема: путь в атрибуте должен быть относительным, а нужные изображения доступны лишь по UNC-пути. Решение довольно просто́ и заключается в создании символической ссылки в папке сайта; если условиться, что физически сайт располагается в каталоге c:\IIS\SiteName, а ссылка будет называться Covers, то её добавление выполняется следующей командой в консоли:

mklink /d "c:\IIS\SiteName\Covers" "\\ServerName\MSSQLSERVER\DatabaseName\AlbumCover"

Теперь атрибут src станет возможно заполнять подобным образом:

<img src="Covers/3AE39458-8925-448A-A5F1-0C0A4524ACF0.jpg">

Доработки Delphi-кода, дающие показанное значение для src, будут приведены в конце статьи, а пока же остался важный нюанс с правами (в нашем случае достаточно лишь на чтение), причём делящийся на две части:

1. Предоставление доступа (на уровне ФС) пользователю IUSR и группе IIS_IUSRS для созданной символической ссылки:
   
2. Одно из неприятных ограничений файловых таблиц заключается в отсутствии поддержки ACL, вследствие чего пользователь, от имени которого выполняется пул приложений IIS, должен получить права на файлы иным способом – средствами самой СУБД, за доступ к ним полностью и отвечающей. Делается всё совершенно стандартным для SQL Server образом, путём создания пары «Имя входа (логин)-пользователь БД»:
   
   • Логин создаётся на основе Windows-пользователя вида IIS APPPOOL\<Имя нужного пула>.

     CREATE LOGIN [IIS APPPOOL\YourPoolName] FROM WINDOWS;

   • После чего с ним связывается новый пользователь базы.

     CREATE USER FileTableReader FOR LOGIN [IIS APPPOOL\YourPoolName];

   • Которому выдаётся разрешение на чтение таблицы.

     GRANT SELECT ON dbo.AlbumCover TO FileTableReader;

Проверка подлинности

Последнее, что требуется настроить, относится непосредственно к IIS, а именно к проверке подлинности на сайте – чтобы она выполнялась от имени пула приложений, чуть выше только что получившего права на чтение обложек (если в сценарии читателя проверка обязана проходить от иного пользователя, то данный раздел можно пропустить, но при этом необходимо добавить ещё одну пару «логин-пользователь» как описано в предыдущем разделе, связав имя входа со значением из параметров проверки подлинности).

Новая реализация

В заключение осталось привести новый, значительно облегчённый по сравнению с предыдущим вариантом, код, ибо из БД уже не нужно извлекать само изображение – достаточно лишь получить имя файла с ним (из поля dbo.AlbumCover.name, имеющего в примере псевдоним CoverFileName) и в неизменном виде подставить в атрибут src:

uses   System.SysUtils;  function TMainForm.AlbumsToHTML: string; const   AlbumHTMLTemplate =     '<div class="album">' +       '<div class="cover-container">' +         '<img src="%s/%s" alt="Обложка альбома">' +         ...       '</div>' +     '</div>';   SymbolicLinkName = 'Covers'; begin   spAlbums.First;    while not spAlbums.Eof do   begin     Result := Result + Format( AlbumHTMLTemplate, [SymbolicLinkName, spAlbums['CoverFileName'], ...] );     spAlbums.Next;   end; end;