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

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

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

Безусловным лидером мобильных СУБД является SQLite, однако некоторые её недостатки и наличие у приложения функционала, требующего нетривиального анализа данных, не позволили остановиться на ней – поиск альтернатив привёл к Interbase, точнее к его встраиваемой (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;

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

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

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


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) СУБД не учитывает зависимость вызова процедуры от полей таблицы, в результате значения для параметров ХП не могут быть определены из-за ещё непрочитанных записей таблицы.

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

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


Она, в случае очень медленного сетевого канала и большого объёма данных (при наличии фото), вполне может занять несколько минут – соответственно требуется её вынос в отдельный поток. Однако из-за цепочки ограничений реализация усложнится: во-первых, 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_' необходимо заменить на Ваши шаблоны именования триггеров и представлений соответственно.