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