Данная статья написана по мотивам – мотивам разработки мобильного приложения, недавно вышедшего на платформах iOS и Android. Это событие можно было бы назвать заурядным и мало кому интересным, если бы не одно большое и несколько незначительных «но»: вся разработка (включая сервер) велась на Delphi, а в качестве СУБД, как ни странно, задействована совсем не SQLite. Автор, безусловно, понимает, что на текущий момент уже существуют мобильные Delphi-приложения, включая доступные в официальных магазинах, однако не наблюдает обилия русскоязычных публикаций, призванных, как минимум, предостеречь читателей от ошибок, сделанных разработчиком таких проектов. Написанное же здесь преследует цель помочь тем, кто сейчас выбирает СУБД для своего творения, либо уже остановился на каком-то варианте, но желает убедиться в правильности своего решения.
Прежде чем начать, необходимо дать краткое представление о разработанном приложении – это список покупок, имеющий в своём запасе некоторый уникальный функционал. Если кто-то сам уже пользуется (или пользовался) одним из многочисленных аналогов, то сейчас мог скептически хмыкнуть – мол зачем ещё один, и так есть из чего выбрать, на что можно лишь посоветовать продолжить чтение, чтобы ознакомиться с его принципиальной особенностью.
Меж двух огней
Безусловным лидером мобильных СУБД является SQLite, однако некоторые её недостатки и наличие у приложения функционала, требующего нетривиального анализа данных, не позволили остановиться на ней – поиск альтернатив привёл к Interbase, точнее к его встраиваемой (embedded) версии, которая, что удобно, сразу поставляется с Delphi и требует минимума усилий по включению в состав приложения. Interbase, конечно же, идеалом тоже не является и обладает минусами, способы борьбы с которыми приводятся ниже. Важно отметить, что эта СУБД коммерческая, поэтому предлагаются две редакции: IBLite – бесплатная, именно о ней будет идти речь, и Interbase ToGo – платная, но с такой ценовой политикой, что полностью исключает её использование в бесплатных приложениях; ограничения IBLite суровы, но будут показаны способы существования и с ними (в связке с FireDAC).
Основное преимущество Interbase
Итак, начнём обоснование выбора СУБД с ключевой возможности проекта – рекомендаций по наполнению списков товарами. Суть действа в следующем: представьте, что в позапрошлые выходные Вы добавляли в списки такие товары, как зубная паста, апельсины и говядина, а в прошлые – свинину, снова апельсины и ириску. С немалой степенью вероятности можно утверждать, что в эти субботу и воскресенье новый список необходимо наполнить апельсинами и мясом (именно в таком обобщённом виде, т. к. о конкретном виде мяса ничего сказать нельзя). Собственно эти два продукта и будут предложены пользователю. Пример с закономерностью в выходные – это лишь один из вариантов, бывают товары, добавляемые каждый день, через день, в начале месяца и т. д. – всего приложение анализирует 21 случай, что, вкупе с необходимостью обобщения, делает объём вычислений весьма приличным.
На устройстве рекомендации выглядят примерно так:
![Выбор СУБД для мобильного Delphi-приложения - 1 Выбор СУБД для мобильного Delphi-приложения - 1](https://www.pvsm.ru/images/2016/10/04/vybor-subd-dlya-mobilnogo-Delphi-prilojeniya.png)
Подобные расчёты оптимальнее всего выполнять полностью на стороне СУБД, ибо накладные расходы на копирование данных из БД в структуры приложения, а также сложность и, как следствие, подверженность ошибкам алгоритмов обработки этих структур, могут довести время ожидания до десятков секунд, что неприемлемо. Решение – хранимые процедуры (далее ХП), которые присутствуют только в 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;
А соперник – нет, предлагая решать такие задачи через рекурсивные ХП.
Полнотекстовый поиск
Следующий неприятный сюрприз связан с индексированным поиском по строковым полям. При добавлении нового товара, приложение предлагает пользователю варианты, основанные на уже введённых символах:
![Выбор СУБД для мобильного Delphi-приложения - 2 Выбор СУБД для мобильного Delphi-приложения - 2](https://www.pvsm.ru/images/2016/10/04/vybor-subd-dlya-mobilnogo-Delphi-prilojeniya-2.png)
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
) 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) СУБД не учитывает зависимость вызова процедуры от полей таблицы, в результате значения для параметров ХП не могут быть определены из-за ещё непрочитанных записей таблицы.
Работа с данными в потоке
Вторая важная функция приложения – синхронизация списков между устройствами.
![Выбор СУБД для мобильного Delphi-приложения - 3 Выбор СУБД для мобильного Delphi-приложения - 3](https://www.pvsm.ru/images/2016/10/04/vybor-subd-dlya-mobilnogo-Delphi-prilojeniya-3.png)
Она, в случае очень медленного сетевого канала и большого объёма данных (при наличии фото), вполне может занять несколько минут – соответственно требуется её вынос в отдельный поток. Однако из-за цепочки ограничений реализация усложнится: во-первых, 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 Кб:
![Выбор СУБД для мобильного Delphi-приложения - 4 Выбор СУБД для мобильного Delphi-приложения - 4](https://www.pvsm.ru/images/2016/10/04/vybor-subd-dlya-mobilnogo-Delphi-prilojeniya-4.png)
После задействования 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_' необходимо заменить на Ваши шаблоны именования триггеров и представлений соответственно.
Спасибо за внимание!
Автор: SergeyPyankov