Что нового в SQLite 3.37

∘NOTES 28.11.21 28.11.21 244

SQLite В отличие от 3.35, релиз 3.37 принес не так много изменений. Но среди них — одно из важнейших за всю историю: «строгий» режим таблиц, в котором движок следит, чтобы данные в столбце соответствовали типу.

Возможно, теперь SQLite перестанут называть «джаваскриптом в мире СУБД» ツ Но давайте по порядку.

Проблема с типами

SQLite поддерживает 5 типов данных:

INTEGER — целые числа,
REAL — действительные числа,
TEXT — строки,
BLOB — бинарные данные,
NULL — пустое значение.

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

SQLite хранит тип не только на столбце, но и на каждом значении в таблице. Именно поэтому в одном столбце без проблем хранятся значения разных типов. Тип на столбце используется как рекомендация: при вставке SQLite пытается привести значение к рекомендуемому типу, но если не получилось — сохраняет «как есть».

С одной стороны, это удобно для исследовательского анализа данных — можно сначала все загрузить, а потом средствами SQL разбираться с проблемными значениями. Любая другая СУБД выдаст ошибку при импорте и заставит «шерстить» данные скриптами или вручную.

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

И вот, в версии 3.37 проблема решена!

STRICT-таблицы

Теперь таблицу можно объявить «строгой», после чего записать отсебятину уже не получится:

create table employees (
    id integer primary key,
    name text,
    salary integer
    ) STRICT;

insert into employees (id, name, salary)
    values (22, 'Ксения', 'hello');
    -- Error: stepping, cannot store TEXT value in INTEGER column employees.salary (19)

У Ксении явно проблема с зарплатой, на что и указывает SQLite. Кто-то ждал этого двадцать лет ツ

При этом движок все равно пытается привести данные к типу столбца, и если получится — ошибки не будет:

insert into employees (id, name, salary)
    values (22, 'Ксения', '85');

    select * from employees;
    ┌────┬────────┬────────┐
    │ id │  name  │ salary │
    ├────┼────────┼────────┤
    │ 22 │ Ксения │ 85     │
    └────┴────────┴────────┘

Документация: STRICT Tables

Новый тип данных — ANY

Чтобы в STRICT-таблицу можно было писать что душе угодно, предусмотрели новый тип ANY:

create table employees (
    id integer primary key,
    name text,
    stuff any
    ) strict;

    insert into employees (id, name, stuff)
    values
    (21, 'Елена', 84),
    (22, 'Ксения', 'hello'),
    (23, 'Леонид', randomblob(8));

    select id, name, typeof(stuff) from employees;
    ┌────┬────────┬───────────────┐
    │ id │  name  │ typeof(stuff) │
    ├────┼────────┼───────────────┤
    │ 21 │ Елена  │ integer       │
    │ 22 │ Ксения │ text          │
    │ 23 │ Леонид │ blob          │
    └────┴────────┴───────────────┘

STRICT-таблица сохраняет ANY-значение без каких-либо преобразований. В обычной таблице ANY работает почти так же, но по возможности преобразует строки в числа.

Документация: The ANY datatype

Прагма-команда table_list

Новая прагма table_list показывает список таблиц и вьюх в базе:

pragma table_list;
    ┌────────┬────────────────────┬───────┬──────┬────┬────────┐
    │ schema │        name        │ type  │ ncol │ wr │ strict │
    ├────────┼────────────────────┼───────┼──────┼────┼────────┤
    │ main   │ expenses           │ table │ 4    │ 0  │ 0      │
    │ main   │ employees          │ table │ 5    │ 0  │ 0      │
    │ main   │ sqlite_schema      │ table │ 5    │ 0  │ 0      │
    │ temp   │ sqlite_temp_schema │ table │ 5    │ 0  │ 0      │
    └────────┴────────────────────┴───────┴──────┴────┴────────┘

Раньше для этого приходилось опрашивать таблицу sqlite_schema. С прагмой удобнее.

Документация: PRAGMA table_list

Изменения в CLI

В CLI-утилите (sqlite.exe) теперь можно переключаться между несколькими соединениями с помощью дот-команды .connection:

sqlite> .connection
    ACTIVE 0: :memory:

sqlite> .open employees.ru.db
    sqlite> .connection
    ACTIVE 0: employees.ru.db

sqlite> .connection 1
    sqlite> .open employees.en.db
    sqlite> .connection
    0: employees.ru.db
    ACTIVE 1: employees.en.db

Документация: Working With Multiple Database Connections

Кроме того, добавили опцию запуска --safe. Она запрещает команды, которые могут вносить изменения где-либо кроме конкретной базы. Безопасный режим отключает .open, .shell, .import и другие «опасные» команды.

Документация: The --safe command-line option

И еще несколько мелочей

Планировщик запросов игнорирует order by на подзапросах, если они не меняют общую семантику запроса.
Функция generate_series(start, stop, step) теперь всегда требует параметр start (stop и step остались необязательными).
Пачка изменений в C API.

Если интересно, как использовать SQLite в повседневных задачах — подписывайтесь на канал @sqliter

на главную сниппетов