Выбор СУБД для мобильного Delphi-приложения

Данная статья написана по мотивам – мотивам разработки мобильного приложения, недавно вышедшего на платформах iOS и Android. Это событие можно было бы назвать заурядным и мало кому интересным, если бы не одно большое и несколько незначительных «но»: вся разработка (включая сервер) велась на Delphi, а в качестве СУБД, как ни странно, задействована совсем не SQLite. Автор, безусловно, понимает, что на текущий момент уже существуют мобильные Delphi-приложения, включая доступные в официальных магазинах, однако не наблюдает обилия русскоязычных публикаций, призванных, как минимум, предостеречь читателей от ошибок, сделанных разработчиком таких проектов. Написанное же здесь преследует цель помочь тем, кто сейчас выбирает СУБД для своего творения, либо уже остановился на каком-то варианте, но желает убедиться в правильности своего решения.

Прежде чем начать, необходимо дать краткое представление о разработанном приложении – это список покупок, имеющий в своём запасе некоторый уникальный функционал. Если кто-то сам уже пользуется (или пользовался) одним из многочисленных аналогов, то сейчас мог скептически хмыкнуть – мол зачем ещё один, и так есть из чего выбрать, на что можно лишь посоветовать продолжить чтение, чтобы ознакомиться с его принципиальной особенностью.

Меж двух огней


Безусловным лидером мобильных СУБД является SQLite, однако некоторые её недостатки и наличие у приложения функционала, требующего нетривиального анализа данных, не позволили остановиться на ней – поиск альтернатив привёл к Interbase XE7, точнее к его встраиваемой (embedded) версии, которая, что удобно, сразу поставляется с Delphi и требует минимума усилий по включению в состав приложения. Interbase, конечно же, идеалом тоже не является и обладает минусами, способы борьбы с которыми приводятся ниже. Важно отметить, что эта СУБД коммерческая, поэтому предлагаются две редакции: IBLite – бесплатная, именно о ней будет идти речь, и Interbase ToGo – платная, но с такой ценовой политикой, что полностью исключает её использование в бесплатных приложениях; ограничения IBLite суровы, но будут показаны способы существования и с ними (в связке с FireDAC).

Основное преимущество Interbase


Итак, начнём обоснование выбора СУБД с ключевой возможности проекта – рекомендаций по наполнению списков товарами. Суть действа в следующем: представьте, что в позапрошлые выходные Вы добавляли в списки такие товары, как зубная паста, апельсины и говядина, а в прошлые – свинину, снова апельсины и ириску. С немалой степенью вероятности можно утверждать, что в эти субботу и воскресенье новый список необходимо наполнить апельсинами и мясом (именно в таком обобщённом виде, т. к. о конкретном виде мяса ничего сказать нельзя). Собственно эти два продукта и будут предложены пользователю. Пример с закономерностью в выходные – это лишь один из вариантов, бывают товары, добавляемые каждый день, через день, в начале месяца и т. д. – всего приложение анализирует 21 случай, что, вкупе с необходимостью обобщения, делает объём вычислений весьма приличным.

На устройстве рекомендации выглядят примерно так:


Подобные расчёты оптимальнее всего выполнять полностью на стороне СУБД, ибо накладные расходы на копирование данных из БД в структуры приложения, а также сложность и, как следствие, подверженность ошибкам алгоритмов обработки этих структур, могут довести время ожидания до десятков секунд, что неприемлемо. Решение – хранимые процедуры (далее ХП), которые присутствуют только в Interbase.

Другим серьёзным доводом за ХП являются требования фонового выполнения операции (без блокировки интерфейса), а также её досрочной отмены – ведь речь о длительностях в несколько секунд. В случае SQLite сложность решения такой задачи много больше, т. к. требуется вынести все многочисленные запросы к БД и обработку их результатов в отдельный поток и самостоятельно реагировать на флаг отмены. Вызов же одной ХП в FireDAC можно сделать асинхронным, что автоматически решает поставленные задачи:

  • нужно лишь установить свойство TFDStoredProc.ResourceOptions.CmdExecMode в amAsync
  • вызвать метод TFDStoredProc.Open
  • для прерывания использовать вызов TFDStoredProc.AbortJob(True)
  • обработать завершение ХП в событии TFDStoredProc.AfterOpen

Хранимые процедуры обладают ещё одним, неочевидным, преимуществом – возможностью отслеживать зависимости как между собой, так и от прочих объектов БД: таблиц, представлений и всего остального. Если в ходе разработки потребовалось, к примеру, изменить или удалить поле в таблице, а код запросов хранится в приложении в TFDQuery, то задача будет простой только при их количестве до нескольких десятков; когда запросов станет более сотни, уследить за всеми – большая проблема. ХП и любая профессиональная IDE сведут такие сложности почти до нуля.

Три довода в пользу SQLite


После немаленькой ложки мёда из процедур, перейдём к такой же большой ложке дёгтя из отсутствия некоторых возможностей в Interbase. Горечь будет идти по нарастающей, чтобы сразу не шокировать читателя некоторыми, так скажем, особенностями этой СУБД.

CTE

Выше говорилось о требовании обобщать товары при выдаче рекомендаций, что реализовано, в том числе, за счёт иерархического справочника товаров. Так вот SQLite имеет средства для ускорения работы с деревьями за счёт обобщённых табличных выражений (CTE) вида

WITH RECURSIVE CTE_NAME(Field1...FieldN) AS
  (
  SELECT ...
  UNION ALL
  SELECT ...
  )
SELECT Field1...FieldN FROM CTE_NAME;

а соперник – нет, предлагая решать такие задачи через рекурсивные ХП.

Примечание. Написанное справедливо на момент публикации статьи, однако в вышедший недавно Interbase 2017 добавлена частичная поддержка CTE — без рекурсивной части, которая обещана позднее.

Полнотекстовый поиск

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


SQLite на такой случай даёт очень мощный (даже избыточный для этого примера) механизм полнотекстового поиска, обладающий заведомо высокой скоростью работы; Interbase же задействует индекс только при поиске по началу строки, тогда как требуется искать совпадение с любой позиции. Другими словами, это условие будет использовать индекс

WHERE STRING_FIELD_UPPER LIKE 'ТОР%'

а применяемое в приложении уже нет

WHERE STRING_FIELD_UPPER CONTAINING 'ТОР'

На небольшом наборе данных проблема слабо проявляет себя – текущий справочник товаров содержит 700 записей, безындексный перебор которых на iPhone 5c занимает, в худшем случае, 240 мс, что заметно при наборе, но ещё находится в зоне комфорта.

Производные таблицы

Самым горьким, даже ошарашивающим недостатком Interbase (особенно учитывая какой сегодня год) стала невозможность применять производные (derived) таблицы:

SELECT ...
FROM
  TABLE_1
  JOIN
    (
    SELECT ...
    FROM TABLE_2
    WHERE ...
    GROUP BY ...
    ) ON ...

Вместо этого необходимо создавать представление (что предпочтительнее варианта далее, потому что оно может быть «развёрнуто» оптимизатором) и выполнять соединение с ним

SELECT ...
FROM
  TABLE_1
  JOIN VIEW_NAME ON ...

либо применять ХП, изменив тип соединения

SELECT ...
FROM
  TABLE_1 T_1
  LEFT JOIN SP_NAME(T_1.FIELD_NAME) ON 0 = 0

Левое внешнее соединение приходится задействовать из-за одной застарелой проблемы, которая может проявиться при выполнении такого кода: при внутреннем соединении (JOIN) СУБД не учитывает зависимость вызова процедуры от полей таблицы, в результате значения для параметров ХП не могут быть определены из-за ещё непрочитанных записей таблицы.

Примечание. Написанное справедливо на момент публикации статьи, однако в вышедший недавно Interbase 2017 добавлена поддержка производных таблиц.

Работа с данными в потоке


Вторая важная функция приложения – синхронизация списков между устройствами.


Она, в случае очень медленного сетевого канала и большого объёма данных (при наличии фото), вполне может занять несколько минут – соответственно требуется её вынос в отдельный поток. Однако из-за цепочки ограничений реализация усложнится: во-первых, FireDAC обязывает устанавливать новое соединение к БД, которое станут использовать компоненты, работающие в неосновном потоке, но, и это во-вторых, IBLite не позволяет создать несколько одновременных соединений. Очевидным решением проблемы будет закрытие первого, основного соединения, через которое получены данные, отображаемые в интерфейсе; если проделать это обычным способом, через метод TFDConnection.Close, то все связанные с этим соединением наборы данных очистятся, в результате чего пользователь будет обескуражен опустевшими списками. К счастью, сам же FireDAC и предлагает выход из ситуации – режим работы без установленного соединения, сохраняющий наборы данных открытыми. Полная последовательность действий становится такой:

  • войти в особый режим работы главного соединения через метод TFDConnection.Offline, что разорвёт физическую связь с БД, но визуальных изменений не привнесёт;
  • стартовать новый поток, где выполнить второе (условно) подключение к БД;
  • дождаться окончания работы потока;
  • закрыть второе соединение;
  • если свойство TFDConnection.ResourceOptions.AutoConnect = True, то больше ничего не требуется, ибо главное соединение автоматически перейдёт в обычный режим при любом действии, требующем обращения к БД через него.

Проблема нестабильного курсора


К сожалению, автор не знает, существует ли подобная загвоздка в SQLite, но Interbase ей подвержен, поэтому упоминание будет нелишним – суть в том, что обновление таблицы в цикле FOR, построенном на ней же, может приводить к неожидаемому поведению. Речь ведётся о конструкции, подобной этой:

FOR
  SELECT
    REC_ID, /* Первичный ключ. */
    ...
  FROM
    TABLE_1
    ...
  WHERE
    ...
  INTO
    REC_ID,
    ...
DO
BEGIN
  UPDATE TABLE_1
  SET ...
  WHERE REC_ID = :REC_ID;
 
  ...
END

Способов борьбы два: первый заключается в добавлении искусственной сортировки в цикл

FOR
  SELECT
    REC_ID, /* Первичный ключ. */
    ...
  FROM
    TABLE_1
    ...
  WHERE
    ...
  ORDER BY
     REC_ID DESC
  INTO
    REC_ID,
    ...
DO
BEGIN
  UPDATE TABLE_1
  SET ...
  WHERE REC_ID = :REC_ID;
 
  ...
END

а второй – в использовании временной таблицы

INSERT INTO TMP_TABLE
  SELECT
    REC_ID, /* Первичный ключ. */
    ...
  FROM
    TABLE_1
    ...
  WHERE
    ...;
 
FOR
  SELECT
    REC_ID,
    ...
  FROM
    TMP_TABLE
  INTO
    REC_ID,
    ...
DO
BEGIN
  UPDATE TABLE_1
  SET ...
  WHERE REC_ID = :REC_ID;
 
  ...
END

Защита БД


Самым надёжным способом защитить структуру базы и её данные можно назвать шифрование; оно имеется в SQLite, но беспощадно вырезано из бесплатного IBLite. Хорошей новостью будет то, что имеется другой механизм, позволяющий блокировать подключение к БД любопытствующим, не знающим пароль, причём он действует и в случае копирования БД на машину, где установлен сервер Interbase с полным административным доступом, – способ заключается во включении Embedded User Authentication (EUA) для нужной базы данных. Если БД только создаётся, то код будет выглядеть так:

CREATE DATABASE 'Путь_к_файлу' WITH ADMIN OPTION

В противном случае применяется команда

ALTER DATABASE ADD ADMIN OPTION;

Переход на EUA, кроме всего прочего, даёт возможность исключить файл admin.ib из состава приложения, сэкономив почти 500 Кб:


После задействования EUA, рекомендуется повысить надёжность хранения пароля (одновременно увеличив ограничение на его длину с 8 до 32 байт):

ALTER DATABASE SET PASSWORD DIGEST 'SHA-1';
ALTER USER SYSDBA SET PASSWORD 'Ваш_пароль';

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

UPDATE RDB$PROCEDURES
SET RDB$PROCEDURE_SOURCE = NULL
WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0;

UPDATE RDB$TRIGGERS
SET RDB$TRIGGER_SOURCE = NULL
WHERE
  COALESCE(RDB$SYSTEM_FLAG, 0) = 0
  AND RDB$FLAGS = 1
  AND RDB$TRIGGER_NAME STARTING WITH 'TR_';

UPDATE RDB$RELATIONS
SET RDB$VIEW_SOURCE = NULL
WHERE
  COALESCE(RDB$SYSTEM_FLAG, 0) = 0
  AND RDB$FLAGS = 1
  AND RDB$RELATION_TYPE = 'VIEW'
  AND RDB$RELATION_NAME STARTING WITH 'VW_';

где строки 'TR_' и 'VW_' необходимо заменить на Ваши шаблоны именования триггеров и представлений соответственно.
Поделиться публикацией
Комментарии 21
    +2
    А как же Firebird?
    –2
    Давным давно в далекой галактике писал на Delphi, но помню что у неё получались большие файлы, если конечно не шпарить на чистом winapi и требования к ресурсам. Как сейчас с этим и тем более интересно для мобильных? Скока весит ваше приложение и какая отзывчивость на простых аппаратах?
      0
      на дворе 21 век, минимум сейчас 16 Гб места в девайсах, какая разница сколько весит приложение?
      в последних версиях Делфи все хорошо с отзывчивостью и на слабых девайсах в том числе
      –4
      2016 год. Делфи и сторед процедуры на Interbase в iOS. Omg, что дальше будет? Программистов на Dephi окончательно уволили с поддержки старых проектов и им больше нечем заняться?
        +3
        выбор языка программирования и средств разработки должен решать поставленную задачу. В данном случае — решает. Какие проблемы? На вкус и цвет все фломастеры разные
          0
          Во что Делфи генерит свой код чтобы компилить на Андроиде?
          Что там? Qt(native C++), Java…?
            0
            Делфи выполняет компиляцию напрямую в машинные коды, причём это касается не только Android, но и всех поддерживаемых платформ. Никакие сторонние графические фреймворки тоже не используются — за отрисовку интерфейса отвечает собственная библиотека FireMonkey.
              0
              Никакие сторонние графические фреймворки тоже не используются — за отрисовку интерфейса отвечает собственная библиотека FireMonkey.

              FireMonkey рисует компоненты, похожие на стандартные или вызывает построение реальных стандартных для ОС компоненты?
                0
                Изначально FireMonkey создавалась для полностью самостоятельной отрисовки интерфейса, но позже были добавлены некоторые родные компоненты у двух ОС: Windows и iOS; в дополнение к этому, для мобильных платформ есть как бесплатные наборы для построения родного интерфейса в Android и iOS, так и платный — но только под iOS.
          0
          Пробовал новый Delphi и Xamarin Studio. Разницы нет на чём писать код, если задача решается привычным инструментом. К примеру потеря производительности в том же Xamarin на Android всего 5-10%. Если приложение не занимается сложными расчётами или оно не из области финтеха(Платежные системы, процессинг), то любой язык хорош.
          0
          А как сейчас обстоят дела с MySQL и Delphi? У mysql есть встраиваемая версия, интересно дружит ли она с дельфи?
            0
            По меньшей мере FireDAC работает с указанной версией MySQL (не вижу причин, почему прочие универсальные библиотеки должны отставать в этом).
            0
            Спасибо за статью, узнал много нового. Сам недавно стоял перед выбором БД для Delphi-приложения, правда десктопного. Выбрал связку SQLite+FireDAC, вполне неплохо работает эта система в Delphi. У вас тоже похоже было много преференций в отношении SQLite.
              +4
              Может быть я и открыл Америку, но прямо сейчас Delphi 10.1 Berlin Starter Edition можно зарегистрировать и скачать бесплатно (со страницы покупки). Понятно, что там всё по минимуму, но это давно пора было сделать.
                +2
                image
                  0
                  Database components and drivers are not included.
                  Очень полезная сборка…
                    0
                    Так даже такая «полезная» сборка не ставится. Дальше вечернего вида центра Берлина больше ни чего не увидел, ибо на стадии Initializing .NET Services винда выдаёт, что прога сдохла и будет закрыта. Ну, умерла так умерла. Скоро и с деньгами будет не надь.
                      0
                      О надо же я не одинок. Я в итоге подцепил дебагер, проигнорил ошибку и даже все доставилось.
                      Но ощущения были те еще — хорошее такое обновление выкатили.
                  +1
                  Очень похоже, что стреляли вы из пушек по воробьям. Речь идет об обработке локально сохраненных данных объемом в жалкие пару мегабайт…
                  Ну либо есть черный пояс в области написания SQL запросов, а самостоятельное написание алгоритмов хромает на обе ноги.
                    0
                    В вышедшем недавно Interbase 2017 частично и полностью устранены две из описанных в статье проблемы — CTE и производные таблицы соответственно. Подробности можно узнать в документации.

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                    Самое читаемое