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

Данная статья написана по мотивам – мотивам разработки мобильного приложения, недавно вышедшего на платформах 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_' необходимо заменить на Ваши шаблоны именования триггеров и представлений соответственно.