Вибір СУБД для мобільного Delphi-програми

Дана стаття написана за мотивами – мотивами розробки мобільного додатку, недавно вийшов на платформах 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
) 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 OPTION ADMIN

В іншому випадку застосовується команда

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_' необхідно замінити на Ваші шаблони іменування тригерів і уявлень відповідно.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.