Антон Жиянов
проекты блог контакты en

Все расширения для SQLite

Мне очень нравится SQLite. Это миниатюрная встраиваемая база, которая отлично подходит как для исследовательского анализа данных, так и в качестве хранилища для небольших приложений (писал об этом, не буду повторяться).

Но есть у нее один недостаток: маловато встроенных функций по сравнению с PostgreSQL или Oracle. К счастью, авторы заложили в SQLite механизм расширений, на котором можно сделать почти все что угодно. В результате интернет заполнен обрывочными расширениями, которые добавляют функцию-другую.

Мне хотелось большой системности. В результате появился проект sqlean — в нем я собираю вместе недостающие в SQLite функции, распределяю их по модулям, рефакторю код, пишу тесты и документацию. Получается что-то вроде стандартной библиотеки, как в Python или Go, только для SQLite.

Я планирую подробно написать про каждый модуль в отдельной статье, а пока — вот краткий обзор.

Основной набор

Это самые популярные функции, которых не хватало в SQLite:

  • crypto: хеш-функции, кодирование и декодирование.
  • define: пользовательские функции и динамический SQL.
  • fileio: чтение и запись файлов, создание каталогов.
  • fuzzy: нечеткое сравнение строк, фонетические алгоритмы, транслитерация.
  • ipaddr: манипуляция IP-адресами и подсетями.
  • math: математические функции.
  • regexp: регулярные выражения.
  • stats: статистика — медиана, процентили, стандартное отклонение.
  • text: работа со строками.
  • unicode: поддержка юникода для функций upper(), lower() и LIKE.
  • uuid: генерация уникальных идентификаторов.
  • vsv: работа с CSV-файлами как с таблицами базы.

Расширение sqlean объединяет все перечисленные в один файл, чтобы их можно было подключить разом.

Все расширения можно скачать для Windows, Linix и macOS.

Инкубатор

Функции, которые пока не вошли в основной набор. Эти расширения могут быть плохо структурированы, но я их постепенно рефакторю и переношу в основные.

  • array: работа с массивами (почти как в постгресе).
  • besttype: преобразует строку в подходящий числовой тип.
  • bloom: быстрый вероятностный способ понять, есть значение в таблице или нет.
  • btreeinfo, memstat, recsize и stmt: интроспекция базы данных
  • classifier: бинарный классификатор на логистической регрессии.
  • compress и sqlar: архивация и распаковка данных.
  • cron: проверяет даты по cron-шаблонам.
  • dbdump: экспортирует базу как SQL
  • decimal, fcmp and [ieee754])(https://github.com/nalgeon/sqlean/issues/27#issuecomment-1007375162): десятичные дроби и числа с плавающей запятой
  • envfuncs: читает переменные среды.
  • isodate: дополнительные функции для работы с датами.
  • json1: работа с JSON
  • math2: больше математических функций и битовой арифметики.
  • pearson: корреляция Пирсона между двумя наборами данных.
  • pivotvtab: сводные таблицы.
  • prefixes: генерация строковых префиксов
  • rotate: обфускация строк
  • spellfix: быстрый поиск похожих слов в словаре.
  • stats2 и stats3: еще больше матстатистики.
  • uint: натуральная сортировка и сравнение строк, содержащих числа (natsort).
  • unhex: преобразует строку в бинарные данные (обратная операция для hex()).
  • unionvtab: объединяет похожие таблицы в одну
  • xmltojson: преобразует XML в JSON.
  • zipfile: читает и пишет zip-архивы.
  • zorder: отображает многомерные данные в одно измерение

Инкубаторные расширения тоже можно скачать.

Как подключить расширение

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

Для начала, скачайте свежий релиз. Затем подключите расширение как описано ниже.

Примеры используют расширение stats; вы можете указать любое другое. Чтобы загрузить все расширения из основного набора разом, используйте расширение sqlean.

Командная строка или IDE

Если работаете с командной строкой SQLite (sqlite.exe, она же «консоль» или CLI):

sqlite> .load ./stats
sqlite> select median(value) from generate_series(1, 99);

Если используете инструмент вроде SQLiteStudio, SQLiteSpy или DBeaver:

select load_extension('c:\Users\anton\sqlite\stats.dll');
select median(value) from generate_series(1, 99);

Примечание для пользователей macOS. Макось может блокировать неподписанные бинарные файлы и запрещать загрузку расширений. Чтобы это исправить, уберите расширение из карантина, выполнив следующую команду в терминале (предварительно замените /path/to/folder на реальный путь к каталогу, в котором находится расширение):

xattr -d com.apple.quarantine /path/to/folder/stats.dylib

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

Python

Установите пакет sqlean.py — он полностью совместим со стандартным sqlite3:

pip install sqlean.py

Все расширения из основного набора уже включены:

import sqlean as sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("select median(value) from generate_series(1, 99)")
conn.close()

Либо используйте стандартный модуль sqlite3 и загружайте расширения вручную:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension("./stats")
conn.execute("select median(value) from generate_series(1, 99)")
conn.close()

Node.js

Используйте пакет better-sqlite3:

const sqlite3 = require("better-sqlite3");
const db = new sqlite3(":memory:");
db.loadExtension("./stats");
db.exec("select median(value) from generate_series(1, 99)");
db.close();

Что дальше

Если почувствуете, что в SQLite вам не хватает какой-то функции — приходите в репозиторий sqlean, там наверняка найдется.

Я продолжаю добавлять новые расширения в инкубатор, а расширения из инкубатора рефакторю и переношу в основные. По каждому основному модулю планирую написать отдельную статью с примерами.

А если захотите поучаствовать — присылайте свои или чужие расширения.

Всем SQLite!

★ Подписывайтесь на новые заметки.