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

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

Частичные индексы ^[2] (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

Индексы на выражение ^[3] (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

Вычисляемые колонки ^[4] (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 индекс ^[5]

Индекс предназначен для быстрого поиска в диапазоне значений/вложенности объектов, т.е. задачи типичной для гео-систем, когда объекты-прямоугольники заданы своей позицией и размером и требуется найти все объекты, которые пересекаются с текущим. Данный индекс реализован в виде виртуальной таблицы (см. ниже) и это индекс только по своей сути. Для поддержки 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;

Переименование колонки ^[6]

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

alter table tbl1 rename column a to b;

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

Добавить строку, иначе обновить ^[7] (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 ^[8]

Если строка должна быть обновлена на основе данных другой таблицы, то ранее приходилось использовать вложенный запрос для каждого столбца или 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 ^[9] (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;

Оконные функции ^[10] (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 ^[11] доступны еще две утилиты. Первая — sqldiff ^[12], позволяет сравнивать базы (или отдельную таблицу) не только по структуре, но и по данным. Вторая — sqlite3_analizer ^[13] используется для вывода информации о том, как эффективно используется место таблицами и индексами в файле базы данных. Аналогичную информацию можно получить из виртуальной таблицы dbstat ^[14] (требует флаг SQLITE_ENABLE_DBSTAT_VTAB при компиляции SQLite).

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

Создание резервной копии Vacuum Into ^[15]

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

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

Функция printf ^[17]

Функция является аналогом С-функции. При этом 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

Время и дата ^[18]

В SQLite нет типов Date и Time ^[19]. Хотя и можно создать таблицу с колонками таких типов, это будет аналогично созданию колонок без указания типа, поэтому данные в таких колонках хранятся как текст. Это удобно при просмотре данных, однако имеет ряд недостатков: неэффективный поиск, если нет индекса, данные занимают много места, отсутсвует временная зона. Для избежания этого можно хранить данные как unix-время ^[20], т.е. число секунд, прошедших с полуночи 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 ^[21]

С версии 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] (строка)

Полнотекстовый поиск ^[22]

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

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

Расширения

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

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

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

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

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

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

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

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

Разное

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

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

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

Автор: little-brother

Источник ^[39]