Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале. Сайт этот связан с музыкальной тематикой и, соответственно, активно отображает обложки альбомов, хранящиеся (до поры до времени) на сервере не в ФС, как обычно бывает, а в БД SQL Server (в BLOB-поле, о чём несложно догадаться); в старом варианте пора извлечения изображений из базы и сохранения их в виде файлов возникает в момент запроса альбомов пользователем, после чего (по истечении сессии) они удаляются.
![Страница сайта с обложками](https://habrastorage.org/r/w780q1/webt/ph/17/y2/ph17y2epjrtbn2hjbl30wtx8ri4.jpeg)
Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.
Прежде всего необходимо напомнить структуру HTML-тэга
У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.
На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.
Схематически и упрощённо описанное непотребство можно представить следующим образом:
![Схема с копированием обложек](https://habrastorage.org/r/w1560/webt/9g/ka/g5/9gkag5tvkha4jsbnqsqe5o5qaqs.png)
Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:
Если предаться мечтаниям и представить, что обложки по-прежнему находятся в БД в BLOB-поле, а IIS каким-то образом, почти без усилий и программирования с нашей стороны, сам извлекает их, то окажется, что SQL Server способен такую мечту исполнить – за счёт функционала FileTable, представляющего собой, если очень кратко, создаваемую разработчиком специального вида таблицу, каждая запись которой отображается в файл в NTFS; обращение к таким файлам ничем не отличается от стандартного (однако некоторые возможности не поддерживаются), т. е. их можно просматривать, изменять и удалять хоть через Проводник.
Если снова графически показать такую схему, то мы избавимся от копирования обложки в папку сайта:
![Схема без копирования обложек](https://habrastorage.org/r/w1560/webt/od/_s/dk/od_sdkm0zgvjb60hdewkelwqgsi.png)
Данный вариант полностью совместим и с отложенной загрузкой изображений, настраиваемой скажем через атрибут
Чтобы воспользоваться этой манной небесной, ниспосланной SQL Server, потребуется, само-собой, выполнить некоторые действия как на стороне СУБД, так и на стороне IIS, чему собственно и посвящена оставшаяся практическая часть статьи. Но предварительно хотелось бы сделать небольшую ремарку и отметить, что конкуренты Microsoft тоже могут предложить тонкую работу с BLOB, но функционал уровня FileTable автору удалось найти лишь у Oracle – речь о Database File System (DBFS). Остальные же игроки предоставляют лишь условно традиционный (но неизбежно СУБД-специфичный) API, несколько примеров:
Кстати, использующим Express-редакцию SQL Server наверняка весьма понравится то, что данные в файловых таблицах не учитываются при проверке лимита на размер БД в 10 Гб, т. к. реализация FileTable основывается на технологии FILESTREAM, как раз и свободной от этого ограничения у бесплатной версии.
По умолчанию FileTable-возможности отключены в SQL Server. Официальная документация содержит весьма подробную и внятную инструкцию по параметрам, которые необходимо задействовать как на уровне всего экземпляра, так и в конкретной базе данных, отчего не представляется разумным пересказывать здесь подобные тонкости – вместо этого автор сосредоточится на небольшом SQL-скрипте, выполняющем перенос обложек из существующей таблицы с альбомами в новую FileTable-таблицу (если же в Вашем случае файлы изначально в БД не хранятся, в отличие от данного проекта, то с их адаптацией к FileTable поможет документация).
Далее в скрипте предполагается следующая структура двух только что упомянутых таблиц (источника – с данными об альбомах, и приёмника – новой таблицы с обложками):
Все действия с базой логически делятся на несколько этапов:
Почти всё, теперь обложки доступны по специальному UNC-пути, возвращаемому функцией
Оговорка про «почти» связана с тем, что читатель может столкнуться с отсутствием доступа при попытке обратиться к означенным файлам – в этом случае следует ознакомиться с той частью следующего раздела, где описывается нюанс с правами. Также для определённости примем, что наш путь выглядит как \\ServerName\MSSQLSERVER\DatabaseName\AlbumCover (далее в другом примере будет отсылка к нему, где для наглядности потребуется конкретное значение).
Хотя в текущем разделе демонстрация идёт на примере IIS, но большинство вещей применимы и к любому другому веб-серверу, ибо являются скорее ОС- и СУБД-специфичными.
Если снова вернуться к атрибуту
Теперь атрибут
Доработки Delphi-кода, дающие показанное значение для
Последнее, что требуется настроить, относится непосредственно к IIS, а именно к проверке подлинности на сайте – чтобы она выполнялась от имени пула приложений, чуть выше только что получившего права на чтение обложек (если в сценарии читателя проверка обязана проходить от иного пользователя, то данный раздел можно пропустить, но при этом необходимо добавить ещё одну пару «логин-пользователь» как описано в предыдущем разделе, связав имя входа со значением из параметров проверки подлинности).
![Настройка проверки подлинности через Диспетчер служб IIS](https://habrastorage.org/r/w1560/webt/hv/ig/vq/hvigvqvvmo_gcacokcosdvppgnm.png)
В заключение осталось привести новый, значительно облегчённый по сравнению с предыдущим вариантом, код, ибо из БД уже не нужно извлекать само изображение – достаточно лишь получить имя файла с ним (из поля
![Страница сайта с обложками](https://habrastorage.org/webt/ph/17/y2/ph17y2epjrtbn2hjbl30wtx8ri4.jpeg)
Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.
Старая реализация
Прежде всего необходимо напомнить структуру HTML-тэга
img
, обязательным у которого является лишь атрибут src
:<img src="some_path/file_name.jpg">
У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.
На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.
Схематически и упрощённо описанное непотребство можно представить следующим образом:
![Схема с копированием обложек](https://habrastorage.org/webt/9g/ka/g5/9gkag5tvkha4jsbnqsqe5o5qaqs.png)
Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:
- Запрос на выборку данных возвращает, среди прочих, и BLOB-поле с обложкой (в данном случае на примере компонента
spAlbums
типаTFDStoredProc
из состава FireDAC). - Изображение из поля, в зависимости от формата (png, jpg…), загружается в соответствующего наследника TGraphic.
- Далее используется предоставляемая 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; обращение к таким файлам ничем не отличается от стандартного (однако некоторые возможности не поддерживаются), т. е. их можно просматривать, изменять и удалять хоть через Проводник.
Если снова графически показать такую схему, то мы избавимся от копирования обложки в папку сайта:
![Схема без копирования обложек](https://habrastorage.org/webt/od/_s/dk/od_sdkm0zgvjb60hdewkelwqgsi.png)
Данный вариант полностью совместим и с отложенной загрузкой изображений, настраиваемой скажем через атрибут
loading
, т. е. если пользователь не прокрутит страницу сайта до некоторых обложек, то они и не будут прочитаны из базы (написанное, разумеется, справедливо и для случая, когда в браузере вообще отключен показ изображений):<img src="some_path/file_name.jpg" loading="lazy">
Чтобы воспользоваться этой манной небесной, ниспосланной SQL Server, потребуется, само-собой, выполнить некоторые действия как на стороне СУБД, так и на стороне IIS, чему собственно и посвящена оставшаяся практическая часть статьи. Но предварительно хотелось бы сделать небольшую ремарку и отметить, что конкуренты Microsoft тоже могут предложить тонкую работу с BLOB, но функционал уровня FileTable автору удалось найти лишь у Oracle – речь о Database File System (DBFS). Остальные же игроки предоставляют лишь условно традиционный (но неизбежно СУБД-специфичный) API, несколько примеров:
Кстати, использующим 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
:Поле | Тип данных | Описание |
---|---|---|
CoverID |
hierarchyid |
Внешний ключ на приёмник. |
Прочие поля... |
Скрипт
Все действия с базой логически делятся на несколько этапов:
- Банальнейшее по реализации добавление поля
CoverID
в источник:
ALTER TABLE dbo.Album ADD CoverID hierarchyid;
- Копирование данных в приёмник и заполнение только что созданного поля
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, ...
- Удаление ставших ненужными полей в источнике:
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
, будут приведены в конце статьи, а пока же остался важный нюанс с правами (в нашем случае достаточно лишь на чтение), причём делящийся на две части:- Предоставление доступа (на уровне ФС) пользователю IUSR и группе IIS_IUSRS для созданной символической ссылки:
- Одно из неприятных ограничений файловых таблиц заключается в отсутствии поддержки 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;
- Логин создаётся на основе Windows-пользователя вида IIS APPPOOL\<Имя нужного пула>.
Проверка подлинности
Последнее, что требуется настроить, относится непосредственно к IIS, а именно к проверке подлинности на сайте – чтобы она выполнялась от имени пула приложений, чуть выше только что получившего права на чтение обложек (если в сценарии читателя проверка обязана проходить от иного пользователя, то данный раздел можно пропустить, но при этом необходимо добавить ещё одну пару «логин-пользователь» как описано в предыдущем разделе, связав имя входа со значением из параметров проверки подлинности).
![Настройка проверки подлинности через Диспетчер служб IIS](https://habrastorage.org/webt/hv/ig/vq/hvigvqvvmo_gcacokcosdvppgnm.png)
Новая реализация
В заключение осталось привести новый, значительно облегчённый по сравнению с предыдущим вариантом, код, ибо из БД уже не нужно извлекать само изображение – достаточно лишь получить имя файла с ним (из поля
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;