Возможности SQLite, которые вы могли пропустить

Если вы используете SQLite, но не следите за его развитием, то возможно некоторые вещи, позволяющие сделать код проще, а запросы быстрее, прошли незамеченными. Под катом я постарался перечислить наиболее важные из них.

Частичные индексы (Partial Indexes)

При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.

create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index

Индексы на выражение (Indexes On Expressions)

Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.

create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table

Вычисляемые колонки (Generated Columns)

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

create table tab1 (
	a integer primary key,
	b int,
	c text,
	d int generated always as (a * abs(b)) virtual,
	e text generated always as (substr(c, b, b + 1)) stored
);

R-Tree индекс

Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки R-Tree индекса требуется собрать SQLite с флагом SQLITE_ENABLE_RTREE (по умолчанию не установлен).

create virtual table idx_rtree using rtree (
	id,              -- ключ
	minx, maxx,      -- мин и макc x координаты
	miny, maxy,      -- мин и макc y координаты
	data             -- дополнительные данные  
);  

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778); 
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree 
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00  and maxy <= 35.44;

Переименование колонки

В SQLite слабо поддерживает изменения в структуре таблиц, так, после создания таблицы, нельзя изменить ограничение (constraint) или удалить столбец. С версии 3.25.0 можно переименовать столбец, но не изменить его тип.

alter table tbl1 rename column a to b;

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

Добавить строку, иначе обновить (Upsert)

Используя класс on conflict оператора insert, можно добавить новую строку, а при уже имеющейся с таким же значением по ключу, обновить.

create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial') 
  on conflict (word) do update set count = count + 1;

Оператор Update from

Если строка должна быть обновлена на основе данных другой таблицы, то ранее приходилось использовать вложенный запрос для каждого столбца или with. С версии 3.33.0 оператор update расширен ключевым словом from и теперь можно делать так

update inventory
   set quantity = quantity - daily.amt
  from (select sum(quantity) as amt, itemid from sales group by 2) as daily
 where inventory.itemid = daily.itemid;

CTE запросы, класс with (Common Table Expression)

Класс with может использоваться как временное представление для запроса. В версии 3.34.0 заявлена возможность использования with внутри with.

with tab2 as (select * from tab1 where a > 10), 
  tab3 as (select * from tab2 inner join ...)
select * from tab3;

С добавлением ключевого слова recursive, with можно использовать для запросов, где требуется оперировать связанными данными.

-- Генерация значений
with recursive cnt(x) as (
  values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;

-- Нахождения дочерних элементов или родителя в таблице с иерархией
create table tab1 (id, parent_id);
insert into tab1 values 
  (1, null), (10, 1), (11, 1), (12, 10), (13, 10),
  (2, null), (20, 2), (21, 2), (22, 20), (23, 21);

-- Узлы ниже по иерархии
with recursive tc (id) as (
	select id from tab1 where id = 10	
	union 
	select tab1.id from tab1, tc where tab1.parent_id = tc.id
)

-- Узелы верхнего уровня для выбранных дочерних
with recursive tc (id, parent_id) as (
	select id, parent_id from tab1 where id in (12, 21)
	union 
	select tc.parent_id, tab1.parent_id 
	from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;

-- Формирования отступов при выводе, напр. для структуры отделов
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null), 
  ('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'), 
  ('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');

with recursive
  under_alice (name, level) as (
    values('Alice', 0)
    union all
    select org.name, under_alice.level + 1
      from org join under_alice on org.boss = under_alice.name
     order by 2
  )
select substr('..........', 1, level * 3) || name from under_alice;

Оконные функции (Window Functions)

С версии 3.25.0 в SQLite доступны оконные функции, также иногда называемые аналитическими, позволяющие проводить вычисления над частью данных (окном).

-- Номер строки в результате
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;

-- Таблица используется для следующих примеров
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
  (2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'), 
  (5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');

-- Доступ к предыдущей и следующей записи в окне
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;

-- Значения в окне (группе, определяемой колонкой c)  от текущей строки до конца окна
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;

-- Пропуск строк в окне по условию
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;

Утилиты SQLite

Помимо CLI sqlite3 доступны еще две утилиты. Первая — sqldiff, позволяет сравнивать базы (или отдельную таблицу) не только по структуре, но и по данным. Вторая — sqlite3_analizer используется для вывода информации о том, как эффективно используется место таблицами и индексами в файле базы данных. Аналогичную информацию можно получить из виртуальной таблицы dbstat (требует флаг SQLITE_ENABLE_DBSTAT_VTAB при компиляции SQLite).

С версии 3.22.0 CLI sqlite3 содержит (экспериментальную) команду .expert, которая может подсказать какой индекс стоит добавить для вводимого запроса.

Создание резервной копии Vacuum Into

С версии 3.27.0 команда vacuum расширена ключевым словом into, позволяющим создать копию базы без её остановки прямо из SQL. Является простой альтернативой Backup API.

vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';

Функция printf

Функция является аналогом С-функции. При этом NULL-значения интерпретируются как пустая строка для %s и 0 для плейсхолдера числа.

select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0

Время и дата

В SQLite нет типов Date и Time. Хотя и можно создать таблицу с колонками таких типов, это будет аналогично созданию колонок без указания типа, поэтому данные в таких колонках хранятся как текст. Это удобно при просмотре данных, однако имеет ряд недостатков: неэффективный поиск, если нет индекса, данные занимают много места, отсутсвует временная зона. Для избежания этого можно хранить данные как unix-время, т.е. число секунд, прошедших с полуночи 01.01.1970.

select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC время
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); --> местное время
select strftime('%s', 'now'); -- текущее Unix-время 
select strftime('%s', 'now', '+2 day'); --> текущее unix-время плюс два дня
-- Конвертация unix-времени в локальное для пользователя - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')

Json

С версии 3.9.0 в SQLite можно работать с json (требуется либо флаг SQLITE_ENABLE_JSON1 при компиляции или загруженное расширение). Данные json хранятся как текст. Результат функций — также текст.

select json_array(1, 2, 3); --> [1,2,3] (строка)
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} (строка)
select json_extract('{"a":[2,5],"b":10}', '$.a[0]');  --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} (строка)
select value from json_each(json_array(2, 5)); --> 2 строки 2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] (строка)

Полнотекстовый поиск

Как и json, полнотекстовый поиск требует задания флага SQLITE_ENABLE_FTS5 при компиляции или загрузки расширения. Для работы с поиском, сперва создается виртуальная таблица с индексируемыми полями, а и потом туда загружаются данные, используя обычный insert. Следует иметь в виду, что для своей работы расширение создает дополнительные таблицы и созданная виртуальная таблица использует их данные.

create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender или body содержит fts5

Расширения

Возможности SQLite могут быть добавлены через загружаемые модули. Некоторые из них уже были упомянуты выше — json1 и fts.

Расширения могут использоваться как для добавления пользовательских функций (не только скалярных, как, например, crc32, но и агрегирующих или даже оконных), так и виртуальных таблиц. Виртуальные таблицы — это таблицы, которые присутствуют в базе, но их данные обрабатываются расширением, при этом, в зависимости от реализации, некоторые из них требуют создания

create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;

Другие же, так называемые table-valued, могут использоваться сразу

select value from generate_series(5, 100, 5);

.
Часть виртуальных таблиц перечислена здесь.

Одно расширение может реализовать как функции, так и виртуальные таблицы. Например, json1 содержит 13 скалярных и 2 агрегирующие функции и две виртуальные таблицы json_each и json_tree. Чтобы написать свою функцию достаточно иметь базовые знания С и разобрать код расширений из репозитария SQLite. Реализация своих виртуальных таблиц несколько сложнее (видимо поэтому их мало). Тут можно рекомендовать не сильно устаревшую книгу Using SQLite by Jay A. Kreibich, статью Michael Owens, шаблон из репозитария и код generate_series, как table-valued функции.

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

Разное

• Используйте ' (одинарная кавычка) для строковых констант и " (двойная кавычка) для имен столбцов и таблиц.
• Чтобы получить информацию по таблице tab1 можно использовать
  
  

  -- В main схеме
  select * from pragma_table_info('tab1');
  -- В temp схеме или подключенной (attach) базе
  select * from pragma_table_info('tab1') where schema = 'temp'

• У SQLite есть свой официальный форум, где участвует и создатель SQLite — Richard Hipp, и где можно оставить сообщение о баге.
  
• Редакторы SQLite: SQLite Studio, DB Browser for SQLite и (реклама!) sqlite-gui (только Windows).