Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале. Сайт этот связан с музыкальной тематикой и, соответственно, активно отображает обложки альбомов, хранящиеся (до поры до времени) на сервере не в ФС, как обычно бывает, а в БД SQL Server (в BLOB-поле, о чём несложно догадаться); в старом варианте пора извлечения изображений из базы и сохранения их в виде файлов возникает в момент запроса альбомов пользователем, после чего (по истечении сессии) они удаляются.
Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.
Прежде всего необходимо напомнить структуру HTML-тэга img, обязательным у которого является лишь атрибут src:
<img src="some_path/file_name.jpg">
У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.
На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.
Схематически и упрощённо описанное непотребство можно представить следующим образом:
Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:
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, несколько примеров:
Кстати, использующим Express-редакцию SQL Server наверняка весьма понравится то, что данные в файловых таблицах не учитываются при проверке лимита на размер БД в 10 Гб, т. к. реализация FileTable основывается на технологии FILESTREAM, как раз и свободной от этого ограничения у бесплатной версии.
По умолчанию FileTable-возможности отключены в SQL Server. Официальная документация содержит весьма подробную и внятную инструкцию по параметрам, которые необходимо задействовать как на уровне всего экземпляра, так и в конкретной базе данных, отчего не представляется разумным пересказывать здесь подобные тонкости – вместо этого автор сосредоточится на небольшом SQL-скрипте, выполняющем перенос обложек из существующей таблицы с альбомами в новую FileTable-таблицу (если же в Вашем случае файлы изначально в БД не хранятся, в отличие от данного проекта, то с их адаптацией к FileTable поможет документация).
Далее в скрипте предполагается следующая структура двух только что упомянутых таблиц (источника – с данными об альбомах, и приёмника – новой таблицы с обложками):
Все действия с базой логически делятся на несколько этапов:
Почти всё, теперь обложки доступны по специальному 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, будут приведены в конце статьи, а пока же остался важный нюанс с правами (в нашем случае достаточно лишь на чтение), причём делящийся на две части:
Последнее, что требуется настроить, относится непосредственно к 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;
habr.com
Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.
Старая реализация
Прежде всего необходимо напомнить структуру HTML-тэга img, обязательным у которого является лишь атрибут src:
<img src="some_path/file_name.jpg">
У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.
На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.
Схематически и упрощённо описанное непотребство можно представить следующим образом:
Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:
- Запрос на выборку данных возвращает, среди прочих, и 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; обращение к таким файлам ничем не отличается от стандартного (однако некоторые возможности не поддерживаются), т. е. их можно просматривать, изменять и удалять хоть через Проводник.
Если снова графически показать такую схему, то мы избавимся от копирования обложки в папку сайта:
Данный вариант полностью совместим и с отложенной загрузкой изображений, настраиваемой скажем через атрибут 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) Название файла (с расширением), отображаемое в ФС. Прочие поля...
| Поле | Тип данных | Описание |
|---|---|---|
| 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, а именно к проверке подлинности на сайте – чтобы она выполнялась от имени пула приложений, чуть выше только что получившего права на чтение обложек (если в сценарии читателя проверка обязана проходить от иного пользователя, то данный раздел можно пропустить, но при этом необходимо добавить ещё одну пару «логин-пользователь» как описано в предыдущем разделе, связав имя входа со значением из параметров проверки подлинности).
Новая реализация
В заключение осталось привести новый, значительно облегчённый по сравнению с предыдущим вариантом, код, ибо из БД уже не нужно извлекать само изображение – достаточно лишь получить имя файла с ним (из поля 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;
Хранение изображений сайта в БД
Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале . Сайт этот связан с музыкальной тематикой и,...
habr.com