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

Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале. Сайт этот связан с музыкальной тематикой и, соответственно, активно отображает обложки альбомов, хранящиеся (до поры до времени) на сервере не в ФС, как обычно бывает, а в БД 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;