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

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

В целом, отличный релиз! Строгие таблицы предлагают долгожданную альтернативу гибким типам, any делает эту гибкость явной, а прагма table_list просто приятна.

Официальные заметки к релизу | Скачать

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