- PVSM.RU - https://www.pvsm.ru -
Если вы используете SQLite, но не следите за его развитием [1], то возможно некоторые вещи, позволяющие сделать код проще, а запросы быстрее, прошли незамеченными. Под катом я постарался перечислить наиболее важные из них.
При построении индекса можно указать условие попадания строки в индекс, к примеру, одна из колонок не пустая, а другая равна заданному значению.
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
Если в запросах к таблице часто используется выражение, то можно построить индекс по нему. Однако следует иметь в виду, что пока оптимизатор не очень гибок и перестановка столбцов в выражении приведет к отказу от использования индекса.
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
Если данные столбца представляют собой результат вычисления выражения по другим столбцам, то можно создать виртуальный столбец. Есть два вида: 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 индекса требуется собрать 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;
Для других операций всё также предлагается создать таблицу с нужной структурой, перелить туда данные, удалить старую и переименовать новую.
Используя класс 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;
Если строка должна быть обновлена на основе данных другой таблицы, то ранее приходилось использовать вложенный запрос для каждого столбца или 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;
Класс 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;
С версии 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;
Помимо CLI sqlite3 [11] доступны еще две утилиты. Первая — sqldiff [12], позволяет сравнивать базы (или отдельную таблицу) не только по структуре, но и по данным. Вторая — sqlite3_analizer [13] используется для вывода информации о том, как эффективно используется место таблицами и индексами в файле базы данных. Аналогичную информацию можно получить из виртуальной таблицы dbstat [14] (требует флаг SQLITE_ENABLE_DBSTAT_VTAB
при компиляции SQLite).
С версии 3.22.0 CLI sqlite3 содержит (экспериментальную) команду .expert, которая может подсказать какой индекс стоит добавить для вводимого запроса.
С версии 3.27.0 команда vacuum
расширена ключевым словом into
, позволяющим создать копию базы без её остановки прямо из SQL. Является простой альтернативой Backup API [16].
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';
Функция является аналогом С-функции. При этом 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
[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')
С версии 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 [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].
'
(одинарная кавычка) для строковых констант и "
(двойная кавычка) для имен столбцов и таблиц.-- В main схеме
select * from pragma_table_info('tab1');
-- В temp схеме или подключенной (attach) базе
select * from pragma_table_info('tab1') where schema = 'temp'
Автор: little-brother
Источник [39]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/sql/358999
Ссылки в тексте:
[1] за его развитием: https://www.sqlite.org/changes.html
[2] Частичные индексы: https://www.sqlite.org/partialindex.html
[3] Индексы на выражение: https://www.sqlite.org/expridx.html
[4] Вычисляемые колонки: https://www.sqlite.org/gencol.html
[5] R-Tree индекс: https://www.sqlite.org/rtree.html
[6] Переименование колонки: https://sqlite.org/lang_altertable.html
[7] Добавить строку, иначе обновить: https://www.sqlite.org/lang_UPSERT.html
[8] Оператор Update from: https://www.sqlite.org/lang_update.html#upfrom
[9] CTE запросы, класс with: https://sqlite.org/lang_with.html
[10] Оконные функции: https://sqlite.org/windowfunctions.html
[11] sqlite3: https://sqlite.org/cli.html
[12] sqldiff: https://sqlite.org/sqldiff.html
[13] sqlite3_analizer: https://www.sqlite.org/sqlanalyze.html
[14] dbstat: https://www.sqlite.org/dbstat.html
[15] Создание резервной копии Vacuum Into: https://sqlite.org/lang_vacuum.html
[16] Backup API: https://www.sqlite.org/backup.html
[17] Функция printf: https://sqlite.org/printf.html
[18] Время и дата: https://sqlite.org/lang_datefunc.html
[19] нет типов Date
и Time
: https://www.sqlite.org/datatype3.html
[20] unix-время: https://ru.wikipedia.org/wiki/Unix-%D0%B2%D1%80%D0%B5%D0%BC%D1%8F
[21] Json: https://www.sqlite.org/json1.html
[22] Полнотекстовый поиск: https://www.sqlite.org/fts5.html
[23] json1: https://www.sqlite.org/src/file?name=ext/misc/json1.c&ci=tip
[24] fts: https://www.sqlite.org/src/dir?ci=tip&name=ext/fts5
[25] агрегирующих: https://sqlite.org/appfunc.html
[26] оконных: https://sqlite.org/windowfunctions.html#udfwinfunc
[27] обрабатываются: https://www.sqlite.org/vtab.html#tabfunc2
[28] здесь: https://sqlite.org/vtablist.html
[29] из репозитария SQLite: https://www.sqlite.org/src/dir?ci=tip&name=ext/misc
[30] Using SQLite by Jay A. Kreibich: https://www.oreilly.com/library/view/using-sqlite/9781449394592/
[31] Michael Owens: https://www.drdobbs.com/database/query-anything-with-sqlite/202802959
[32] из репозитария: https://www.sqlite.org/src/file?name=ext/misc/templatevtab.c&ci=tip
[33] generate_series: https://www.sqlite.org/src/file?name=ext/misc/series.c&ci=tip
[34] здесь: https://www.sqlite.org/vfs.html
[35] официальный форум: https://sqlite.org/forum/forummain
[36] SQLite Studio: https://sqlitestudio.pl/
[37] DB Browser for SQLite: https://sqlitebrowser.org/
[38] sqlite-gui: https://github.com/little-brother/sqlite-gui
[39] Источник: https://habr.com/ru/post/528882/?utm_source=habrahabr&utm_medium=rss&utm_campaign=528882
Нажмите здесь для печати.