#SQLite

Я звик думати про бази даних SQL як щось окреме, відносно далеке та чуже. Але застосування SQLite в останні дні спокушає передивитися ці підходи. Бо тут наче й база даних, але вона локальна та головне, власна.

А ще мені пощастило знайти цю бібліотеку GRDBQuery, яка інтегрує SQLite в компоненти SwiftUI та автоматично оновлює в них дані. Ну прямо як Redux в React!

Як вона це робить? А дуже просто: оскільки всі зміни бази тут відбувається через те ж підключення (це є обовʼязковою вимогою), то GRDBQuery спостерігає за змінами в окресленому “регіоні бази” - за замовчуванням це, здається, залучені в запиті таблиці — та перезапускає обʼєкт запиту для отримання нового значення.

Звісно, я б не зберігав буквально “стан застосунку” в SQLIte, бо хоч вона й швидка, але зміна значення в базі потребує більше бюрократії, ніж просто в памʼяті. Але для “змістовних даних” така модель повністю задовольняє, так що можна забути про всілякі View Model, довіритись “реактивному” оновленню та вантажити дані безпосередньо в компоненти.

(До речі: SQLite працює з файлами на диску, тобто база може бути дуже велика. Але робочу зону завантажує в памʼять, тож доки база маленька, або ми не звертаємось до старих даних — швидкість роботи буде “як памʼяттю”, а не “як з диском”.)

А далі — виходить, що SQL гарна мова для вибірки даних зі стану! Особливо, коли даних багато, а нам потрібні тільки їхня частина або підсумки. Причому не тільки можна перекласти логіку на SQL, а ще й впровадити індекси, чого в умовному Redux немає.

Цікаво, чи не використовують SQLite у “реактивному режимі” у всіляких Електронах, або навіть в вебзастосунках.

Нарешті мій внутрішній рубіст або джаваскриптівець може зрадіти: я натрапив на базу даних, в якій майже немає типів! Але… чи то гарно? Я ще не визначився.

Власне, в мене в табличці ключем є момент часу, та я хотів переконатися, що в SQLite, як і у звичному для мене PostgreSQL, таймстемпи зберігаються як цілі числа, та відповідно не гірше ніж числа на роль ключа.

⚠️ Сторінка про типи даних в SQLIte трохи спантеличує, якщо очікувати традиційної для БД моделі типів, та не читати першого речення, де сказано, що на відміну від всіх інших баз, в SQLite стовпчики не мають типу, а мають тільки схильність (affinity) до типу. Про це далі. У значень, втім, типи є.

🤔 Типів лише пʼять: ціле число, число з рухомою комою, текст, блоб та нуль. (Мало? Авжеж.) Та хоч в SQLite є підтримка дат, JSON, GIS, але все це зберігається в одному з цих типів. Дати — особливо дивно, бо функції дат приймають як рядок, так і число. Тобто ми легко можемо зберігати дати текстом та навіть не помітити.

🤯 А зі стовпчиками ще дивніше, бо команда CREATE TABLE дає можливість вказати як тип наче будь-яку назву. Яку ж “схильність” отримує стовпчик? Це (мені навіть дико це писати) визначається за змістом назви… наприклад, якщо в назві є підрядок INT, то буде схильність INTEGER. Що для мене важливо, DATETIME теж можна вказати типом, а схильність при цьому буде NUMERIC. Що наче гарно, але ні.

😱 Бо навіть зі схильністю NUMERIC, якщо писати в стовпчик рядок, то він буде конвертований в число тільки коли є числом. А якщо це рядок дати, то він рядком буде і збережений! І наче все буде працювати, тільки 1) з гіршою швидкістю та витратами памʼяті, 2) з загрозою дублікатів, якщо ми також писатимемо дати в чисельній формі, 3) з незрозумілим сортуванням. Я в шоці.

Підсумок: зробив дати чесним полем INTEGER, увімкнув режим STRICT, де тип значення повинний збігатися з типом стовпчика, та переналаштував кодування дат в клієнті. Порядок.

Сьогодні часу не дуже багато було, тому задовольнився тим, що розібрався у внутрішній структурі SQLite, щоб зрозуміти її в контексті локальної бази даних, де сидітиме більшість даних застосунку. (До речі, в документації SQLite достатньо цікавих технічних деталей, щоб просто читати її для загального розвитку. Рекомендую.)

Щодо представлення записів (рядків таблиці): воно компактне, наскільки це можливо, та чимсь нагадує формати на кшталт msgpack. Для кожного значення відзначений свій тип, як памʼятаєте, але маркер типу складається з одного байту та навіть може містити в собі значення NULL, 0 та 1. Цілі числа зберігаються зі змінною шириною. В іншому, зрозумілий компактний формат, що інколи може бути важливо. Наприклад, JavaScript на ті ж дані витратить в рази більше памʼяті.

Щодо структури таблиць: тут все напрочуд просто та, на відміну від, наприклад, PostgreSQL, однозначно. Таблиця зберігається як Б-дерево. (Б-дерево це дерево, яке мінімізує свою висоту: тобто для БД, кількість вузлів-сторінок, за якими потрібно пройтися від кореня, щоб знайти запис.)

В кожного запису є ідентифікатор (rowid) - ціле число — та воно й використовується для побудови дерева. Цікаво, що якщо зробити ключем поле типу INTEGER PRIMARY KEY, то воно фактично й буде містити цей rowid, що пришвидшить пошук за ключем. (В іншому разі, спочатку потрібно знайти rowid за індексом, а потім вже запис за rowid.)

Індекси теж зберігаються як Б-дерева, з єдиною різницею, що тут вже значення в дереві можуть мати довільний тип, та це трохи ускладнює структуру даних. Індекси посилаються на rowid, тож в запитах, що поєднують декілька індексів, можливо фільтрувати записи ще за rowid , без завантаження.

Нарешті, в SQLite вся база даних міститься в одному файлі, він розбитий на сторінки (зазвичай по декілька КБ), та в памʼять можна завантажувати тільки ті, що потрібні. Що може стати дуже корисним, якщо даних багато, але ми не плануємо всі їх читати постійно.

Отже, що ми фактично отримуємо, порівняно зі збереженням даних в простих структурах в памʼяті? Прозорий спосіб заощадити на памʼяті (відвантаженням на диск), та також можливість швидко шукати з логарифмічною складністю — особливо за наявністю індексів.

🗑️ Стикнувся сьогодні з класичним багом в Ping: видалення тегу звалювалося через порушення FOREIGN KEY. Бо ключ я додати згадав, а ON DELETE CASCADE забув. Почав було виправляти на рівні застосунку (тобто видаляти всі відмітки, а потім сам тег), та й думаю… хіба це не робота для бази даних?

🪨 Але ось що виявилося: в SQLite команда ALTER TABLE здатна тільки на найпростіші зміни схеми: додати / видалити / перейменувати стовпчик, або перейменувати саму таблицю, і все. Що мене, звиклого до PostgreSQL, ввело в ступор: невже я застряг з поганою таблицею?

✂️ Знайшов відповідь на StackOverflow з цікавою порадою: оскільки схема таблиць в SQLite зберігається в спеціальній табличці, то достатньо зняти спеціальний запобігач PRAGMA writable_schema та можна просто замінити схему та додати до неї ON DELETE CASCADE. Ясно, що це виглядає максимально сумнівно.

💣 Сумнівно чи ні, але спробував; виявилося, що на iOS цей метод взагалі не працює, бо там підключення відбувається в особливому defensive mode, де абсолютно заборонені такі “сумнівні дії”. (Якщо в схемі змінити таким чином щось суттєве — типи чи порядок стовпчиків, наприклад — то база гепнеться.)

📖 Тоді почитав трохи документацію, та виявилося, що рекомендований підхід дуже простий: створити другу, “правильну” таблицю, наповнити її даними, а потім підмінити стару таблицю новою. Мінуси цього рішення: не відчуєш себе містером роботом. Плюси: все пройшло успішно, без втрати даних та цілісності.

❓ До речі, причина цього (яка теж є на тій же ж сторінці документації), прагматична. SQLite не зберігає схему в структурній формі, а тільки в текстовій (власне, зберігається команда CREATE TABLE.) Тому будь-які зміни в схемі повинні були б перекладатися назад в текст CREATE TABLE - ще й гарантовано без втрат змісту. Творці SQLite вирішили не ускладнювати цим проєкт та залишили зміни схеми в такому ручному, явному режимі.

Коли збираєшся обчислювати статистику за тижнями, стаєш перед неуникним фактом, що в Америці тижні рахують з неділі, а не з понеділка. Як не крути, а доведеться підтримувати обидва варіанти. Поки що я елегантно про це “забув” та рахував як-небудь. Але прийшов час це виправити.

Все ще й ускладнилось тим, що в SQLite якось дивно розподілені команди форматування: в старих версіях є тільки %W - номер тижня, що починається з понеділка, та %w - номер дня, що починається з неділі. А в нових додали протилежні ним %U та %u. Та той SQLite, що є на iOS, ще старий.

В GRDB є можливість скомпілювати власну, нову версію SQLite. Але в цього підходу не все гладко із залежностями, тому мені довелося відмовитись.

Натомість просто додав до пінгів стовпчики weekCode, weekday та ще парочку. Оскільки дата не змінюється, то залишається один раз їх заповнити — та можна забути про всі ті запити із strftime.

Що таке weekCode? Комбінація року та номеру тижня: 202451. Тут, до речі, дізнався ще про один нюанс — що звичайного %Y тут недостатньо, бо тиждень 202501 почався 30 грудня 2024. Тобто потрібний “рік, до якого призначено цей тиждень”, та у Swift навіть так і називається: DateComponents.yearForWeekOfYear.

Та, до речі, всі ці обчислення дат я тепер роблю у Swift, а там календар знає всі налаштування користувача — і перший день тижня, і часовий пояс.