Современный SQLite: Вычисляемые столбцы

Вычисляемые (generated) столбцы рассчитываются на основании других столбцов той же таблицы. Например, мы можем рассчитать процент отказов на основе количества запросов:

create table stats ( date text, n\_total int, n\_failed int, fail\_perc as (n\_failed*100.0 / n\_total) );

Другой распространенный сценарий — вытащить поле JSON-документа в отдельный столбец, и при необходимости проиндексировать его:

```
create table events (
id integer primary key,
event blob,
etime text as (event ->> 'time'),
etype text as (event ->> 'type')
);

create index events_time on events(etime);

insert into events(event) values
('{"time": "2024-05-01", "type": "credit"}'),
('{"time": "2024-05-02", "type": "debit"}'),
('{"time": "2024-05-03", "type": "close"}');

select etime, etype from events;
```

Результат:

┌────────────┬────────┐ │ etime │ etype │ ├────────────┼────────┤ │ 2024\-05\-01 │ credit │ │ 2024\-05\-02 │ debit │ │ 2024\-05\-03 │ close │ └────────────┴────────┘

Так SQLite превращается в документную базу данных:

— Храним JSON-документы как текст или blob-поле.
— Извлекаем отдельные поля через вычисляемые столбцы.
— Строим индексы, если нужна быстрая выборка.

Вычисляемые столбцы могут рассчитываться на лету (virtual) или храниться на диске (stored). Stored-столбцы редко используют на практике.

Работает в SQLite 3.31+ (январь 2020).

песочница • документация

Современный SQLite: STRICT-таблицы

Я начинаю марафон! Но не марафон желаний ? Буду вкратце рассказывать о полезных функциях современного SQLite, про которые вы (возможно) не слышали.

Начнем со «строгих» таблиц.

Как вы наверняка знаете, SQLite обладает гибкой системой типов (за что некоторые даже называют его «джаваскриптом в мире СУБД»). Вы можете хранить любые значения в столбцах любых типов: например, строки в INTEGER-столбце или бинарные данные в REAL-столбце.

Кто-то любит SQLite за эту гибкость, другие ненавидят за нее же. Поэтому в какой-то момент авторы SQLite добавили «строгие» (STRICT) таблицы:

create table people ( id integer primary key, name text, salary real ) strict;

Они проверяют типы так же, как традиционные СУБД вроде PostgreSQL или MySQL:

```
insert into people (name, salary)
values ('Alice', 100);
-- OK

insert into people (name, salary)
values ('Bob', 90.5);
-- OK

insert into people (name, salary)
values ('Cindy', 'decent');
-- Runtime error: cannot store TEXT value
-- in REAL column people.salary
```

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

Работает в SQLite 3.37+ (ноябрь 2021).

песочница • документация

Telegram

SQLite на практике

Гибкие типы данных Люди часто не понимают, что за ерунда происходит в SQLite с типами данных. Давайте разберемся. SQLite использует пять типов данных: — INTEGER — целые числа, — REAL — действительные числа, — TEXT — строки, — BLOB — бинарные данные, —…

Побег из карантина macOS и точный путь к расширению

Свежий релиз sqlpkg принес две приятные фичи:

— Команда install автоматически убирает расширение из карантина macOS, чтобы можно было использовать без дополнительных приседаний.

— Команда which печатает точный путь к установленному расширению, чтобы было проще загрузить его в SQLite.

https://github.com/nalgeon/sqlpkg-cli/releases/0.2.0

Перенести SQLite-расширения с одной машины на другую

Начиная с релиза 0.1.0, sqlpkg сохраняет информацию об установленных пакетах в специальном файле (sqlpkg.lock, он же локфайл).

Используйте его, чтобы установить расширения на новой машине одной командой:

sqlpkg install

https://github.com/nalgeon/sqlpkg-cli/releases/0.1.0

SQL-файлы в песочнице

Когда я запустил Sqlime, как-то забыл поддержать загрузку из .sql файлов. Песочница умела загружать бинарные SQLite-базы из локального файла или по урлу, GitHub-гисты, но не sql-файлы.

Наверно я полагал, что люди в основном будут копипастить SQL руками, так что файлы им не нужны. Возможно, не так уж и ошибался — никто не попросил эту возможность.

Но все же странновато для SQL-песочницы не уметь загружать SQL-файлы. Так что теперь она умеет ツ

Заодно обновил иконки. Использовал svgrepo.com — опенсорсный репозиторий иконок, очень рекомендую.

Реестр и менеджер пакетов для SQLite

Расширений для SQLite становится все больше, и я решил, что пришло время сделать менеджер пакетов!

Встречайте sqlpkg - это реестр пакетов с веб-интерфейсом и одноименный консольный менеджер пакетов. Надеюсь, вам пригодится.

https://sqlpkg.org/

Реестр пакетов работает отдельно от менеджера и не требует его. Достаточно найти нужный пакет на сайте, скачать под свою ОС и загрузить стандартными средствами (.load или load_extension).

А менеджер пакетов работает отдельно от реестра и тоже не требует его :) Чтобы установить пакет, достаточно указать менеджеру на файл спецификации. Его можно захостить где угодно или даже разместить локально.