Работа с СУБД SQLite под Android
В основе многих приложений лежат те или иные базы данных (БД). Мобильные приложения для Android не исключение.
В Android присутствует поддержка СУБД SQLite поэтому приложения на основе БД для этой ОС чаще всего используют именно её. Разумеется, что если не все, то большинство средств разработки для Android, включая Delphi, также имеют встроенную поддержку этой СУБД.
Создание приложения на основе СУБД SQLite
Рассмотрим пример создания приложения на основе СУБД SQLite.
Для этого создадим простую БД с одной единственной таблицей, которая будет представлять собой просто список видов животных, и поместим БД в папке с проектом.
Для доступа к БД воспользуемся компонентами FireDAC. Поместим на форму компоненты FDConnection, FDQuery и FDGUIxWaitCursor.
Настроим FDConnection для доступа к БД. Для этого установим следующие значения свойств
| Свойство | Значение |
| Params.Database | Путь к файлу БД (в каждом конкретном случае зависит от того где расположена папка с проектом и файл БД) |
| Params.DriverID | SQLite |
| LoginPrompt | False |
В свойстве SQL компонента FDQuery напишем простейший запрос.
Этого достаточно для получения данных из БД. Однако эти данные необходимо как-то отображать.
В отличие от VCL в FireMonkey отсутствуют специализированные компоненты для отображения данных из БД (на подобие DBGrid и т.д.). Поэтому для этих целей приходится использовать обычные компоненты и связывать их с БД посредством LiveBindings. Точнее, компонента BindSourceDB.
Для того чтобы отобразить данные из БД воспользуемся компонентом StringGrid.
Поместим на форму компонент BindSourceDB и установим в качестве DataSet компонент FDQuery.
Установим свойство Connected компонента FDConnection и свойство Active компонента FDQuery в значение true. Далее, щёлкнув правой кнопкой мыши на компоненте BindSourceDB, выберем в контекстном меню пункт «Bind Visually…». Откроется визуальный редактор связей LiveBindings (см. скриншот ниже).
LiveBindings позволяет связать с компонентом набор данных, как целиком, так и по отдельности (отдельные поля). Чтобы в StringGrid отображались все поля, свяжем его с набором данных целиком как показано на скриншоте.
Доработка созданного приложения для работы под Android
Созданное выше приложение вполне работоспособно и его можно спокойно запустить на выполнение в среде Windows. Но, если попытаться запустить это же приложение под Android, то ничего не получится. Причина в особенностях этой ОС. Поэтому, перед запуском приложения под Android, его необходимо предварительно подготовить.
Первое, что нужно сделать, это включить файл БД в состав проекта. Тогда файл БД при компиляции будет автоматически включен в состав файла приложения (apk).
Делается это с помощью стандартной процедуры добавления существующего файла в проект. Например, пункт главного меню Project -> Add to Project.
После того как файл БД добавлен нужно сделать так, чтобы приложение при запуске могло его найти в файловой системе Android.
Для этого в главном меню выберем пункт Progect -> Deployment и в открывшейся таблице найдём файл БД. Расположение файла в файловой системе Android указано в графе Remote Path
В данном случае файл БД будет расположен в папке assets\internal\.
Определив расположение файла БД можно прописать путь к нему. Лучше всего это сделать программно в обработчике события BeforeConnection компонента FDConnection, как показано ниже.
Источник
Выбор СУБД для мобильного 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. Горечь будет идти по нарастающей, чтобы сразу не шокировать читателя некоторыми, так скажем, особенностями этой СУБД.
Выше говорилось о требовании обобщать товары при выдаче рекомендаций, что реализовано, в том числе, за счёт иерархического справочника товаров. Так вот SQLite имеет средства для ускорения работы с деревьями за счёт обобщённых табличных выражений (CTE) вида
а соперник – нет, предлагая решать такие задачи через рекурсивные ХП.
Примечание. Написанное справедливо на момент публикации статьи, однако в вышедший недавно Interbase 2017 добавлена частичная поддержка CTE — без рекурсивной части, которая обещана позднее.
Полнотекстовый поиск
Следующий неприятный сюрприз связан с индексированным поиском по строковым полям. При добавлении нового товара, приложение предлагает пользователю варианты, основанные на уже введённых символах:
SQLite на такой случай даёт очень мощный (даже избыточный для этого примера) механизм полнотекстового поиска, обладающий заведомо высокой скоростью работы; Interbase же задействует индекс только при поиске по началу строки, тогда как требуется искать совпадение с любой позиции. Другими словами, это условие будет использовать индекс
а применяемое в приложении уже нет
На небольшом наборе данных проблема слабо проявляет себя – текущий справочник товаров содержит 700 записей, безындексный перебор которых на iPhone 5c занимает, в худшем случае, 240 мс, что заметно при наборе, но ещё находится в зоне комфорта.
Производные таблицы
Самым горьким, даже ошарашивающим недостатком Interbase (особенно учитывая какой сегодня год) стала невозможность применять производные (derived) таблицы:
Вместо этого необходимо создавать представление (что предпочтительнее варианта далее, потому что оно может быть «развёрнуто» оптимизатором) и выполнять соединение с ним
либо применять ХП, изменив тип соединения
Левое внешнее соединение приходится задействовать из-за одной застарелой проблемы, которая может проявиться при выполнении такого кода: при внутреннем соединении ( JOIN ) СУБД не учитывает зависимость вызова процедуры от полей таблицы, в результате значения для параметров ХП не могут быть определены из-за ещё непрочитанных записей таблицы.
Примечание. Написанное справедливо на момент публикации статьи, однако в вышедший недавно Interbase 2017 добавлена поддержка производных таблиц.
Работа с данными в потоке
Вторая важная функция приложения – синхронизация списков между устройствами.
Она, в случае очень медленного сетевого канала и большого объёма данных (при наличии фото), вполне может занять несколько минут – соответственно требуется её вынос в отдельный поток. Однако из-за цепочки ограничений реализация усложнится: во-первых, FireDAC обязывает устанавливать новое соединение к БД, которое станут использовать компоненты, работающие в неосновном потоке, но, и это во-вторых, IBLite не позволяет создать несколько одновременных соединений. Очевидным решением проблемы будет закрытие первого, основного соединения, через которое получены данные, отображаемые в интерфейсе; если проделать это обычным способом, через метод TFDConnection.Close , то все связанные с этим соединением наборы данных очистятся, в результате чего пользователь будет обескуражен опустевшими списками. К счастью, сам же FireDAC и предлагает выход из ситуации – режим работы без установленного соединения, сохраняющий наборы данных открытыми. Полная последовательность действий становится такой:
- войти в особый режим работы главного соединения через метод TFDConnection.Offline , что разорвёт физическую связь с БД, но визуальных изменений не привнесёт;
- стартовать новый поток, где выполнить второе (условно) подключение к БД;
- дождаться окончания работы потока;
- закрыть второе соединение;
- если свойство TFDConnection.ResourceOptions.AutoConnect = True , то больше ничего не требуется, ибо главное соединение автоматически перейдёт в обычный режим при любом действии, требующем обращения к БД через него.
Проблема нестабильного курсора
К сожалению, автор не знает, существует ли подобная загвоздка в SQLite, но Interbase ей подвержен, поэтому упоминание будет нелишним – суть в том, что обновление таблицы в цикле FOR , построенном на ней же, может приводить к неожидаемому поведению. Речь ведётся о конструкции, подобной этой:
Способов борьбы два: первый заключается в добавлении искусственной сортировки в цикл
а второй – в использовании временной таблицы
Защита БД
Самым надёжным способом защитить структуру базы и её данные можно назвать шифрование; оно имеется в SQLite, но беспощадно вырезано из бесплатного IBLite. Хорошей новостью будет то, что имеется другой механизм, позволяющий блокировать подключение к БД любопытствующим, не знающим пароль, причём он действует и в случае копирования БД на машину, где установлен сервер Interbase с полным административным доступом, – способ заключается во включении Embedded User Authentication (EUA) для нужной базы данных. Если БД только создаётся, то код будет выглядеть так:
В противном случае применяется команда
Переход на EUA, кроме всего прочего, даёт возможность исключить файл admin.ib из состава приложения, сэкономив почти 500 Кб:
После задействования EUA, рекомендуется повысить надёжность хранения пароля (одновременно увеличив ограничение на его длину с 8 до 32 байт):
Последним рубежом обороны – в случае извлечения пароля из исполняемого файла или ручной правки самой базы данных, может стать удаление исходного кода ХП, триггеров и представлений при помощи скрипта, модифицирующего системные таблицы:
где строки ‘TR_’ и ‘VW_’ необходимо заменить на Ваши шаблоны именования триггеров и представлений соответственно.
Источник
