SQLite на практике

fuzzy: Нечеткое сравнение строк в SQLite

Расширение nalgeon/fuzzy помогает сравнивать строки на похожесть и транслитерировать текст.

Одни функции считают расстояние между строками (чем оно больше, тем сильнее отличаются строки):

-- Расстояние Дамерау-Левенштейна
select fuzzy_damlev('awesome', 'aewsme');
-- 2

-- Расстояние Хэмминга
select fuzzy_hamming('awesome', 'aewsome');
-- 2

-- Расстояние Джаро-Винклера
select fuzzy_jarowin('awesome', 'aewsme');
-- 0.907142857142857

Другие функции рассчитывают фонетический код строки (помогает определять слова, которые похоже звучат):

-- Caverphone
select fuzzy_caver('awesome');
-- AWSM111111
select fuzzy_caver('owesome');
-- AWSM111111

-- Refined soundex
select fuzzy_rsoundex('awesome');
-- A03080
select fuzzy_rsoundex('awssome');
-- A03080

Транслитерация преобразует строку в ASCII-строку (только латинские символы), чтобы с ней могли работать функции расстояния и фонетики:

select fuzzy_translit('sí señor');
-- si senor

select fuzzy_translit('привет');
-- privet

Как установить расширение

fileio: Работа с файлами в SQLite

Расширение nalgeon/fileio добавляет в SQLite возможность читать и писать файлы прямо из SQL.

fileio_write записывает данные в файл:

select fileio_write('hello.txt', 'hello world');

fileio_read читает содержимое файла:

select fileio_read('hello.txt');
-- hello world

fileio_scan читает файл построчно, не загружая целиком в память:

select rowid, value from fileio_scan('lines.txt');
-- 1,one
-- 2,two
-- 3,three

Кроме того, есть fileio_append (дописывает данные в файл) и функции для работы с каталогами — fileio_mkdir (создает каталог) и fileio_ls (читает содержимое каталога).

Как установить расширение

define: Пользовательские функции в SQLite

Как известно, в SQLite нет хранимых процедур.

Предполагается, что вместо них программист реализует нужные функции в своем коде (например, на Python) и зарегистрирует их в SQLite (так называемые application-defined functions).

Но можно добавлять новые функции и прямо из SQL, если установить расширение nalgeon/define:

-- Суммирует числа от 1 до n
select define('sumn', ':n * (:n + 1) / 2');
select sumn(5);
-- 15

Еще пример:

-- Генерит случайное число от 1 до n
select define('randint', ':a + abs(random()) % (:b - :a + 1)');
select randint(10, 99);
-- 67

Расширение также включает функцию eval — можно динамически выполнять SQL из строки:

-- Создаем и заполняем таблицу
select eval('create table tmp(value int)');
select eval('insert into tmp(value) values (1), (2), (3)');
select count(*) from tmp;
-- 3

Как установить расширение

crypto: Хеши, кодирование и декодирование в SQLite

Открываю новую серию заметок. В каждом посте буду рассказывать об одном полезном расширении SQLite.

Начнем с nalgeon/crypto. Оно предоставляет функции для расчета хешей, а также кодирования и декодирования данных.

Хеши:

select crypto_blake3('abc');
select crypto_md5('abc');
select crypto_sha1('abc');
select crypto_sha256('abc'));
-- и другие

Кодирование/декодирование:

Base32:

select crypto_encode('hello', 'base32');
-- NBSWY3DP
select crypto_decode('NBSWY3DP', 'base32');
-- hello

Base64:

select crypto_encode('hello', 'base64');
select crypto_decode('aGVsbG8=', 'base64');

Hex:

select crypto_encode('hello', 'hex');
select crypto_decode('68656c6c6f', 'hex');

Еще поддерживаются Base85 и url-encoding.

Как установить расширение

Работа с датой и временем в SQLite

В sqlite есть встроенные функции для работы с датами, но они мне всегда не слишком нравились.

Поэтому разработал расширение sqlean-time. Оно поддерживает точность вплоть до наносекунд и предоставляет удобное структурированное API с большим количеством функций.

https://antonz.org/sqlean-time

Современный SQLite: Вычисляемые столбцы

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

create table stats (
  date text,
  n_total int,
  n_failed int,
  fail_perc as (n_failed*100.0 / n_total)
);

Другой распространенный сценарий — вытащить поле JSON-документа в отдельный столбец, и при необходимости проиндексировать его:

create table events (
  id integer primary key,
  event blob,
  etime text as (event ->> 'time'),
  etype text as (event ->> 'type')
);

create index events_time on events(etime);

insert into events(event) values
('{"time": "2024-05-01", "type": "credit"}'),
('{"time": "2024-05-02", "type": "debit"}'),
('{"time": "2024-05-03", "type": "close"}');

select etime, etype from events;

Результат:

┌────────────┬────────┐
│   etime    │ etype  │
├────────────┼────────┤
│ 2024-05-01 │ credit │
│ 2024-05-02 │ debit  │
│ 2024-05-03 │ close  │
└────────────┴────────┘

Так SQLite превращается в документную базу данных:

— Храним JSON-документы как текст или blob-поле.
— Извлекаем отдельные поля через вычисляемые столбцы.
— Строим индексы, если нужна быстрая выборка.

Вычисляемые столбцы могут рассчитываться на лету (virtual) или храниться на диске (stored). Stored-столбцы редко используют на практике.

Работает в SQLite 3.31+ (январь 2020).

песочница • документация

Современный SQLite: STRICT-таблицы

Я начинаю марафон! Но не марафон желаний 😅 Буду вкратце рассказывать о полезных функциях современного SQLite, про которые вы (возможно) не слышали.

Начнем со «строгих» таблиц.

Как вы наверняка знаете, SQLite обладает гибкой системой типов (за что некоторые даже называют его «джаваскриптом в мире СУБД»). Вы можете хранить любые значения в столбцах любых типов: например, строки в INTEGER-столбце или бинарные данные в REAL-столбце.

Кто-то любит SQLite за эту гибкость, другие ненавидят за нее же. Поэтому в какой-то момент авторы SQLite добавили «строгие» (STRICT) таблицы:

create table people (
  id integer primary key,
  name   text,
  salary real
) strict;

Они проверяют типы так же, как традиционные СУБД вроде PostgreSQL или MySQL:

insert into people (name, salary)
values ('Alice', 100);
-- OK

insert into people (name, salary)
values ('Bob', 90.5);
-- OK

insert into people (name, salary)
values ('Cindy', 'decent');
-- Runtime error: cannot store TEXT value
-- in REAL column people.salary

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

Работает в SQLite 3.37+ (ноябрь 2021).

песочница • документация

SQL-полиглот

Сделал онлайн-утилиту, которая выполняет заданный запрос на любой СУБД от PostgreSQL до DuckDB (SQLite тоже есть, конечно).

Удобно, чтобы быстро понять, поддерживает ли база тот или иной SQL-синтаксис.

https://codapi.org/sql

Побег из карантина macOS и точный путь к расширению

Свежий релиз sqlpkg принес две приятные фичи:

— Команда install автоматически убирает расширение из карантина macOS, чтобы можно было использовать без дополнительных приседаний.

— Команда which печатает точный путь к установленному расширению, чтобы было проще загрузить его в SQLite.

https://github.com/nalgeon/sqlpkg-cli/releases/0.2.0

Перенести SQLite-расширения с одной машины на другую

Начиная с релиза 0.1.0, sqlpkg сохраняет информацию об установленных пакетах в специальном файле (sqlpkg.lock, он же локфайл).

Используйте его, чтобы установить расширения на новой машине одной командой:

sqlpkg install

https://github.com/nalgeon/sqlpkg-cli/releases/0.1.0

SQL-файлы в песочнице

Когда я запустил Sqlime, как-то забыл поддержать загрузку из .sql файлов. Песочница умела загружать бинарные SQLite-базы из локального файла или по урлу, GitHub-гисты, но не sql-файлы.

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

Но все же странновато для SQL-песочницы не уметь загружать SQL-файлы. Так что теперь она умеет ツ

Заодно обновил иконки. Использовал svgrepo.com — опенсорсный репозиторий иконок, очень рекомендую.

Одна из моих любимых мелких штук в Sqlime — возможность выделить и запустить фрагмент запроса. Полезно, когда «распутываешь» сложный запрос.

К тому же, ее было несложно реализовать. Ах, если бы все фичи были такими 🙂

Для начала добавил в реестр те расширения, которые пробовал сам, получилось около 60 пакетов

Реестр и менеджер пакетов для SQLite

Расширений для SQLite становится все больше, и я решил, что пришло время сделать менеджер пакетов!

Встречайте sqlpkg - это реестр пакетов с веб-интерфейсом и одноименный консольный менеджер пакетов. Надеюсь, вам пригодится.

https://sqlpkg.org/

Реестр пакетов работает отдельно от менеджера и не требует его. Достаточно найти нужный пакет на сайте, скачать под свою ОС и загрузить стандартными средствами (.load или load_extension).

А менеджер пакетов работает отдельно от реестра и тоже не требует его :) Чтобы установить пакет, достаточно указать менеджеру на файл спецификации. Его можно захостить где угодно или даже разместить локально.

Удобно отлаживать небольшие запросики, а для обучения так и вовсе незаменимая штука

Больше функций в SQL-песочнице

Sqlime — это опенсорсная SQLite-песочница в браузере.

В ней всегда были стандартные функции SQLite: общего назначения, дата-время и математические.

А теперь есть намного больше, от регулярных выражений и матстатистики до динамического SQL!

По сути, я приделал к песочнице все свои расширения, которые до этого подключались только к «обычному» SQLite (кроме работы с файлами и юникода, который под веб не захотел компилироваться).

https://sqlime.org/

sqlite3 + расширения на Python

Сделал Python-пакет sqlean.py. Он полностью совместим со стандартным sqlite3, плюс добавляет множество приятных функций:

— кодирование/декодирование
— динамический SQL
— работа с файлами
— текстовые функции
— IP адреса
— мат. статистика
— UUID
— CSV

https://github.com/nalgeon/sqlean.py

Покрывающий индекс в SQL

Покрывающий индекс — самый быстрый способ выбрать данные из таблицы.

Например, есть запрос, который выбирает сотрудников с указанной зарплатой:

select id, name
from employees
where salary = 90;

Если нет индекса, он обходит всю таблицу (так называемый фулскан).

Создадим индекс по зарплате:

create index employees_idx
on employees(salary);

Теперь тот же запрос будет находить записи по зарплате в индексе (это быстрее, чем обходить всю таблицу). И для каждой найденной записи будет обращаться к таблице, чтобы получить значения id и name.

Но если создать покрывающий индекс:

create index employees_idx
on employees(salary, id, name);

То запрос будет отрабатывать только по индексу, вообще без обращения к таблице. Это еще быстрее.

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

Sqlean shell

sqlean — это набор базовых расширений SQLite, от регулярных выражений и мат. статистики до работы с файлами и динамического SQL.

И теперь он доступен в комплекте с SQLite CLI!

https://github.com/nalgeon/sqlite/releases/3.42.0-sqlean

Некоторые из них в SQLite уже были, но с полным набором удобнее