Файловое хранилище

Одной из спорных тем является использование файла БД для хранения в нем других файлов: изображений, музыки, документов и т.д. Предлагаю вашему вниманию решение, которое, благодаря возможностям масштабирования, подходит как для небольших хранилищ, так и для файловых хранилищ корпоративного уровня.

В качестве альтернативы хранения файлов в БД обычно рассматривается хранение данных на уровне файловой системы. В таблице ниже приводится сравнение двух этих подходов.

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

Структура данных

Для хранения файлов используется всего одна таблица dbStorage. Обычно между пользовательскими таблицами и таблицей dbStorage выстраивается отношение “один-к-одному”, что позволяет реализовать большинство бизнес-задач, связанных с хранением файлов, но при необходимости можно создать связь “один-ко-многим”, при этом необходимо учесть, что алгоритм обновления файла нужно будет доработать.

Встроенный в MVDB Дизайнер БД не отображает служебные поля, поэтому привожу их полный список в таблице:

В ссылающейся таблице добавляется поле типа integere, которое хранит dbStorage.id. Например, так может выглядеть ссылка на хранение аватара пользователя:

Алгоритмы

Поскольку предлагаемое решение является масштабируемым, то но включает в себя реализацию для двух схем хранения данных (в одном файле и разных) и двух СУБД (SQLite и MySQL), что позволит применять его в самых разных проектах, а при необходимости легко перейти на более производительные варианты, не меняя общей логики работы.

Традиционно используются скрипты, которых объединяет общий префикс в названии:

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

<таблица>.<поле>

Для приведенного выше примера параметры будут выглядеть так:

_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. Она была навеяна опытом работы в других успешных проектах.

Возможно, у истории подключения к нескольким базам будет продолжение. Как и у истории файлового хранилища.