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.

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

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

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

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 (читает содержимое каталога).

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

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

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