Что нового в SQLite 3.3728.11.2021 18:31

В отличие от 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