Одной из спорных тем является использование файла БД для хранения в нем других файлов: изображений, музыки, документов и т.д. Предлагаю вашему вниманию решение, которое, благодаря возможностям масштабирования, подходит как для небольших хранилищ, так и для файловых хранилищ корпоративного уровня.
В качестве альтернативы хранения файлов в БД обычно рассматривается хранение данных на уровне файловой системы. В таблице ниже приводится сравнение двух этих подходов.
Аспект | Хранение в БД | Хранение в ФС |
---|---|---|
Производительность | Средняя, ограничена возможностями СУБД | Высокая, если использовать многопоточность для доступа к файлам |
Управление доступом к данным | Удобно, так как реализовано на уровне СУБД | Затруднено, отдельная система выдачи прав на доступ к файловым данным |
Резервное копирование | Удобно, так как хранение данных централизовано, но затруднено при больших объемах хранимых данных | Затруднено, требуется отдельная реализация. |
Транзакции | Поддерживаются | Нарушение синхронизации между БД и файловой системой при откате транзакции |
Удаление | Файлы удаляются вместе с записью БД | Файлы не удаляются при удалении соответствующей записи БД |
Обновление | Поддерживается | Проблемы при одновременном обновлении файлов. |
Несмотря на то, что, кроме производительности, у хранения в ФС явных преимуществ нет, многие рекомендуют использовать именно данный способ. Однако, рассмотрев различные варианты реализаций, я пришёл к выводу, что производительность файлового хранилища БД можно увеличить, применив следующие подходы:
Подход | Описание |
---|---|
Хранение файлов в отдельной БД | Для хранения файлов используется своя база данных, для которой можно выделить отдельный производительный сервер. |
Кеширование данных при чтении | При чтении файлов небольшого размера их можно кешировать в памяти приложения, а крупные – в файловой системе сервера. |
Только добавление данных | Файлы в хранилище только добавляются. Если требуется модификация файла, то старая запись помечается как удаленная, добавляется новая версия файла. Таким образом реализуется версионность хранения данных. |
Регламенты контроля данных | Регламенты включают периодический запуск процедуры, которая проверяет целостность связи между основной БД и БД хранения файлов. В результате могут быть найдены два рода дефекта: неиспользуемый файл и потерянный файл, которые возможны в следствии того, что запись данных нельзя выполнить в рамках одной транзакции, по крайней мере средствами MVDB. |
Сжатие файла БД | Использовать команду DELETE для помеченных к удалению записям. Затем для СУБД SQLite выполнить VACUUM, а для MySQL- backup/restore |
Структура данных
Для хранения файлов используется всего одна таблица dbStorage. Обычно между пользовательскими таблицами и таблицей dbStorage выстраивается отношение “один-к-одному”, что позволяет реализовать большинство бизнес-задач, связанных с хранением файлов, но при необходимости можно создать связь “один-ко-многим”, при этом необходимо учесть, что алгоритм обновления файла нужно будет доработать.

Встроенный в MVDB Дизайнер БД не отображает служебные поля, поэтому привожу их полный список в таблице:
Поле | Тип | Описание |
---|---|---|
id | integer | Идентификатор, первичный ключ |
name | text | Отображаемое название. Обычно совпадает с названием файла. |
data | blob | Данные файла |
data_filename | text | Название файла с расширением (нужно для определения типа файла) |
fileSize | integer | Размер файла |
MD5 | text | Хэш файла |
CreateDT | text | Дата добавления в хранилище |
checked | integer | Служебное поле, используется при выполнении регламентных работ |
deleted | integer | Признак удаления записи |
idNext | integer | Ссылка на следующую запись в цепочке версий файла. |
В ссылающейся таблице добавляется поле типа integere, которое хранит dbStorage.id. Например, так может выглядеть ссылка на хранение аватара пользователя:

Алгоритмы
Поскольку предлагаемое решение является масштабируемым, то но включает в себя реализацию для двух схем хранения данных (в одном файле и разных) и двух СУБД (SQLite и MySQL), что позволит применять его в самых разных проектах, а при необходимости легко перейти на более производительные варианты, не меняя общей логики работы.
Традиционно используются скрипты, которых объединяет общий префикс в названии:
Процедура/функция | Описание |
---|---|
dbStorage_Create() | Инициализация системы хранения файлов. Необязательный параметр – имя дополнительной БД. |
dbStorage_Put() | Помещение файла в хранилище |
dbStorage_Get() | Извлечение файла из хранилища |
dbStorage_Update() | Замена файла на новую версию. |
dbStorage_Delete() | Пометка на удаление файла. Вызывается при удалении записи в основной таблице. |
dbStorage_Check() | Проверка целостности данных. Очистка файла хранилища или подготовка к очистке. |
Для выполнения регламентных работ понадобятся параметры, используемые для описания связей между основными таблицами и файловым хранилищем, которые представляют собой название таблицы и название поля, разделенные точкой:
<таблица>.<поле>
Для приведенного выше примера параметры будут выглядеть так:
_user.idImage
Для обеспечения взаимодействия с хранилищем необходимо дополнить стандартные компоненты несколькими скриптами, которые сделают работу с файлами более удобной. Отдельно понадобятся скрипты для реализации обработки изображений.
Скрипты
Так как предполагается работа с двумя типами базы, то необходимо это учитывать. Для этого добавляем глобальную переменную
var
dbType: integer; // тип базы данных
Code language: Delphi (delphi)
и функцию для её инициализации:
function GetDBType: integer;
// определение типа используемой в проекте СУБД
var
tmpIniFile: TIniFile;
tmpDBMS: string;
begin
tmpIniFile := TiniFile.Create( Application.SettingsFile );
tmpDBMS := tmpIniFile.ReadString('Options','DBMS','');
// для SQLite либо sqlite либо пустая строка, для MySQL - mysql
if tmpDBMS <> 'mysql' then
Result := DBT_SQLITE
else
Result := DBT_MYSQL;
tmpIniFile.Free;
end;
Code language: Delphi (delphi)
Функция инициализации хранилища (подключение к дополнительной базе данных), пока только для SQLite:
function dbStorage_Create( AParam:string; ADataBase:string = ''; ):boolean;
// инициализация хранилища
var
tmpSQL : string;
begin
Result := False;
if dbType = DBT_MYSQL then
begin
RaiseException('Ошибка при вызове dbStorage_Create(). Работа с MySQL пока не реализована');
end
else
begin
if ADataBase <> '' then
begin
// подключаемся к дополнительной БД
tmpSQL := 'ATTACH DATABASE "'+ExtractFilePath( Application.ExeName )+ADataBase+ '" as "'+DBS_ALIAS+'"';
SQLExecute(tmpSQL);
dbsBaseAlias := DBS_ALIAS+'.';
end
else
begin
dbsBaseAlias := '';
end;
// пустой файл добавится автоматически, а вот таблицы нужно создать
tmpSQL := 'SELECT count(*) FROM '+dbsBaseAlias+'sqlite_master WHERE type=''table'' AND name=''dbStorage'' ';
if SQLExecute(tmpSQL) = 0 then
dbStorage_CreateDbStorage;
Result := True;
end;
end;
Code language: Delphi (delphi)
В ней использованы константы и переменные:
const
// типы базы данных
DBT_UNKNOW = 0; // не определена
DBT_SQLITE = 1; // SQLite
DBT_MYSQL = 2; // MySQL
var
dbsBaseAlias: string; // алиас базы хранилища
Code language: JavaScript (javascript)
Для создания таблицы Storage добавлена функция dbStorage_CreateDbStorage():
procedure dbStorage_CreateDbStorage;
var
tmpSQL: string;
begin
tmpSQL :=
' CREATE TABLE '+dbsBaseAlias+'dbStorage ( '+CR+//
' id INTEGER PRIMARY KEY ASC AUTOINCREMENT, '+CR+//
' name TEXT NOT NULL, '+CR+//
' data BLOB, '+CR+//
' data_filename TEXT, '+CR+//
' fileSize INTEGER, '+CR+//
' MD5 TEXT, '+CR+//
' createDT TEXT NOT NULL, '+CR+//
' checked INTEGER NOT NULL DEFAULT 1, '+CR+//
' deleted INTEGER NOT NULL DEFAULT 0, '+CR+//
' idNext INTEGER '+CR+//
');';
SQLExecute(tmpSQL);
end;
Code language: Delphi (delphi)
Запись файла в хранилище:
function dbStorage_Put( AFileName: string; ATable:string; AID:integer; AField:string ):boolean;
// положить файл в файловое хранилище, результат записать в главную таблицу
var
tmpSQL: string;
tmpParams: TParams;
tmpMemoryStream: TMemoryStream;
tmpMD5: string;
tmpFileSize: string;
tmpID: string;
begin
if not FileExists(AFileName) then
begin
RaiseException('Ошибка при вызове dbStorage_Put(). Файл "'+AFileName+'" не найден.');
end;
tmpMD5 := GetFileMD5(AFileName);
tmpFileSize := IntToStr( GetFileSize(AFileName) );
if dbType = DBT_MYSQL then
begin
RaiseException('Ошибка при вызове dbStorage_Put(). Работа с MySQL пока не реализована');
end
else
begin
tmpSQL := 'INSERT INTO '+dbsBaseAlias+'dbStorage ( name, data, data_filename, filesize, MD5, createDT, checked, deleted, idNext) '+
'VALUES ("'+ExtractFileName(AFileName)+'", :data, "'+ExtractFileName(AFileName)+'", '+tmpFileSize+',"'+tmpMD5+'", datetime(''now''), 1, 0, NULL )';
tmpParams := TParams.Create(nil);
// поток для загрузки файла
tmpMemoryStream := TMemoryStream.Create;
tmpMemoryStream.LoadFromFile(AFileName); // load file to memory
tmpMemoryStream.Position := 0;
// параметр для блоба
tmpParams.CreateParam(ftBlob, 'data', ptInput).LoadFromStream(tmpMemoryStream, 15); // 15 ftBlob (TBlobType)
// добавляем/загружаем
SQLiteCon.Execute(tmpSQL, tmpParams);
tmpID := IntToStr( Last_Insert_ID );
// чистим
tmpMemoryStream.Free;
tmpParams.Free;
// теперь записываем в основную базу
tmpSQL := 'UPDATE '+ATable+' SET '+AField+' = '+tmpID+' WHERE id = '+IntToStr(AID);
SQLExecute(tmpSQL);
end;
Result := True;
end;
Code language: Delphi (delphi)
А вот с чтением данных вышел грандиозный облом, так как в My Visual Database нет возможности чтения данных из BLOB-полей кроме как стандартными функциями SaveFileFromDataBase и SaveFileFromDataBaseToStream, а они работают только с основной базой данных. Альтернативных вариантов в виде класса TBlobStream тоже нет.

Эпилог
У этой истории неожиданный и грустный конец. Обычно я не люблю такие расклады, но факт остаётся фактом: несмотря на то, что My Visual Database может одновременно подключаться к нескольким базам, полноценно работать с полями типа BLOB можно только из основного подключения.
На форуме разработчиков мне предложили переподключаться каждый раз, как только понадобятся файлы из другой базы, но это очень похоже на костыль, который меня не вдохновляет.
Возможно, никакой трагедии в этой истории нет, а легкое разочарование наступило вследствие обнаружения “границы мира”: возможности MVDB конечны и не совпали с моими личными ожиданиями. Этого можно было бы избежать, прочитав соответствующий раздел документации разработчика, но такого раздела не существует.
Радует то, что история с файловым хранилищем не является частью реального проекта MVDB. Она была навеяна опытом работы в других успешных проектах.
Возможно, у истории подключения к нескольким базам будет продолжение. Как и у истории файлового хранилища.