1 Разработка базы данных

1.1 Выбор базы данных и формата хранения записей

База данных представляет собой набор связанных между собой таблиц, управляемых программным комплексом СУБД. На рисунке 1 представлена архитектура SQLite базы данных. Она разделена на три разные части, называемые внутренней частью (Backend), SQL компилятором и ядром (две последние можно назвать условно внешней частью).

Рисунок 1 – Архитектура SQLite

Токенизатор, парсер и генератор кода вместе образуют SQL компилятор, генерирующий набор кодов операций, который запускается на виртуальной машине. Ядро и SQL компилятор задают порядок выполнения транзакций базы данных. Бэкенд содержит B-деревья, пейджер и интерфейс операционной системы (ОС) – виртуальную файловую систему, которая представляет собой общий программный интерфейс приложения для доступа к файлам в независимости от операционной системы. Этот API включает в себя функции для открытия/закрытия, чтения и записи с диска компьютера. Пейджер отвечает за работу со страницами. Когда пользователю необходимо прочитать данные из файла, СУБД запрашивает их как страницу (элементарную единицу для передачи). B-деревья это структура данных, которая используется для хранения информации в виде дерева, в самой простой форме – это двоичное дерево. В SQLite B-деревья хранят индексы, чтобы повысить производительность.

Таким образом движок в SQLite (SQL компилятор и виртуальная машина) представляет собой составную часть программы, не являясь отдельно взятым работающим процессом, с которым взаимодействует программа. При этом в качестве протокола обмена используются вызовы функций (API) библиотеки SQLite. Пейджер и B-деревья важны для нашего понимания, так как на их работе базируется основной инструмент манипуляции с данными в базе – курсор. С его помощью мы добавляем, удаляем и ищем записи по идентификатору страницы в таблице и показателю смещения (если индексирование недоступно, то проводится линейный поиск). А интерфейс ОС обеспечивает доступ к файловой системе, т.к. SQLite хранит всю базу данных (включая определения, таблицы, индексы и данные) в единственном стандартном файле на компьютере, на котором и исполняется программа.

Подходя к вопросу разработки структуры таблиц базы данных, прежде всего будем исходить из ограничений, накладываемых выбранным нами типом баз данных. Как уже упоминалось ранее в первой главе, SQLite позволяет множеству пользователей читать записи внутри базы одновременно, при этом накладывая ограничения на число одновременных сеансов записи. Но так как приложение не является высоконагруженным (ожидается менее 100 000 запросов в день и не превышает лимита хранения в 140 терабайт информации), было решено использовать базу SQLite. Более того, SQLite базы данных могут работать хорошо даже при очень небольшом объеме оперативной памяти, что снижает затраты на сервер. Вопрос ограничений на единовременную запись можно решить повторением запроса на запись в течение ограниченного периода времени или же созданием вспомогательной таблицы, куда бы уходили записи, не записанные по истечении времени. Сравнительно недавно появилась еще одна возможность – SQLite: Write Ahead Log. Если включить именно этот режим лога, то несколько подключений смогут одновременно модифицировать БД, но в этом режиме БД занимает несколько файлов.

SQLite поддерживает динамическое типизирование данных. Среди поддерживаемых значений: INT, REAL, TEXT и BLOB. Есть возможность указать пропущенную запись через специальное значение NULL. Преимущественно в работе предполагается использование INT, REAL и TEXT данных. Размеры TEXT данных не ограничены ничем, кроме максимальной длины SQLITE_MAX_LENGTH = 10⁹, хотя большинство ограничений носят рекомендательный характер и могут быть упразднены по желанию. Отличительной особенностью таблиц SQLite является поддержка разных форматов столбца и записей внутри него. Например, стандартным форматом данных столбца является INT, но записано число дробное. В таком случае запись будет либо приведена к заданному типу, либо сохранена в ячейке «как есть». Более того, если тип хранимого значения не соответствует типу запрашиваемого, он, по возможности, безопасно преобразуется. Но гораздо удобнее было бы хранить уникальные ключи user_id в формате INT, а все остальное в формате TEXT. Подобным образом можно избежать непредсказуемых ошибок динамической типизации. Более того, как уже упоминалось ранее, SQLite самостоятельно переведет TEXT в REAL если понадобится, например, посчитать средствами СУБД среднее avg(). Яркий пример тому – присоединенная база данных приложения DiaCompanion. Она содержит значения в форматах REAL и INT, и при экспорте нередко возникает проблема несоответствия NULL значения в SQLite и NoneType в Objective-C. Разделительные знаки не менее часто доставляют проблемы. В некоторых записях стоит точка, в других запятая – Objective-C же считает дробными числами только те, что отделяют дробную часть точкой. Аналогичная проблема может возникнуть при сохранении введенного пользователем показателя уровня сахара в крови.

Разрабатываемая база данных должна хранить как обширную коллекцию записей нутриентов по категориям съедобных продуктов и блюд, так и пользовательскую информацию. Предварительная структура базы данных приведена в таблице 1.

Таблица 1 – Предварительная структура базы данных

Название таблицы	Заполняемые поля	Назначение
activity	user_id, date, time, min, type	Здесь хранится информация о физической активности пользователя
meal_diary	user_id, week_day, date, time, class, food_id	Дневник приемов пищи
full_days	user_id, date	Список полных дней
sleep	user_id, date, time, hour	График сна
user	user_id, username, surname, email, password, BMI	Личная информация, сохраняемая при регистрации
food	food_id, name, category, carbo, prot, fat, ec, gi, water, nzhk, hol, pv, zola, na, k, ca, mg, p, fe, a, b1, b2, rr, c, re, kar, mds, kr, te, ok, ne	Уникальный идентификатор, наименование, категория (молочные продукты, колбасы, фрукты, овощи и другие) и набор нутриентов по каждому доступному продукту или блюду

Ключевое поле для всех таблиц – id, в случае с пользовательскими записями, это будет user_id, а для продуктов питания, food_id. Так как первичный ключ состоит из единственного атрибута, он является простым. Иными словами, вся информация о приемах пищи, физической активности и сне напрямую связана с регистрационными данными пользователя через его id. А вся информация о потребленных микроэлементах в таблице meal_diary связана через food_id с наименованиями блюд в таблице food. Помимо уникальных идентификаторов все блюда и продукты имеют поля name – название и category – категорию. Для удобства все доступные категории можно свести в отдельную таблицу foodGroups с полями _id – в данном случае обычный порядковый номер и category, например:

• Алкогольные напитки;

• Блюда из картофеля, овощей и грибов;

• Выпечка.

И т.д. вниз по алфавиту.

Для возможности потенциального расширения базы данных микроэлементов путем присоединения к ней некоторой таблицы №2 необходимо составить отдельный Excel файл вида: «Наименование / Наличие в нашей таблице / Наличие параметра в присоединяемой таблице / Единицы измерения табл. №1 / Единицы измерения табл. №2 / Аббревиатура». Таким образом мы решаем две проблемы: во-первых, использование разных единиц измерения, например, мкг или мг, мг или мг% и, во-вторых, контролируем отсутствие в присоединяемой базе данных некоторых особенно важных наименований, например, крахмала.