Если при работе с SQLite вам встречалась ситуация, когда не нашлось нужного функционала, то добро пожаловать под кат. И нет, хранимые процедуры добавить нельзя.

Для запуска примеров потребуется компилятор С, исходники SQLite и немного знаний C.
Рекомендуется сначала смотреть код под спойлером, а потом читать текст под ним.

Скалярные функции

Знакомство с расширениями лучше начать с добавления своей функции, которая по входному параметру, возвращает значение.

Функция square, возвращающая для числа его квадрат, для NULL - NULL и ошибку иначе

Для работы с входящими параметрами сначала желательно определить их тип (один из пяти) посредством sqlite3_value_type, а потом вызывать соответсвующую функцию sqlite3_value_(text/int/etc) для получения значения уже определенного типа. Чтобы вернуть результат, надо воспользоваться подходящей sqlite3_result_(text/int/etc). Вернуть можно и ошибку. И, конечно, не стоит забывать про NULL.

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

Функция odd, возвращающая 0 - для четных строк и 1 для нечетных

Для передачи значений используются вспомогательные переменные внутри контекста ctx. Для получения N-го контекстного значения используется sqlite3_get_auxdata(ctx, N).

Подключение функций

Добавить функции можно

либо непосредственно к экземпляру (соединению) базы

либо поместив функции в расширение (.dll, .so) и загрузив его

В обоих случаях добавление производится вызовом sqlite3_create_function.

Третий аргумент — число параметров, принимаемых функцией. Благодаря ему, проверку по числу аргументов argc в функциях расширениях можно не делать. Если его значение -1, то число принимаемых аргументов может быть любым. Четвертый аргумент — флаги, характеризующие режим работы функции. SQLITE_DETERMENISTIC означает, что результат функции зависит только от её параметров, и потому такие функции можно использовать для индексирования, проверок CHECK и в вычисляемых колонках.

Возвращать ошибку из функции не очень хорошо, поскольку обычно в таком случае выдача строк будет прекращена на строке вызвавшей ошибку. Для неподдерживаемых значений лучшим вариантом будет возвращать NULL.

Обратите внимание, что в расширении макросы SQLITE_EXTENSION_INIT1 и SQLITE_EXTENSION_INIT2(pApi) должны быть расположены в самом начале файла и функции экспорта, соответственно. Чтобы расширение могло загрузиться, в имени экспортируемой функции (entry point) не следует использовать подчеркивания и цифры: sqlite3_scalar2_init, sqlite3_my_scalar_init — нет, sqlite3_scalar_init — да, а также имя расширения должно совпадать с центральной частью имени функции (scalar).

В одном файле расширения можно реализовать произвольный набор функций. К примеру, json1 содержит 13 скалярных, 2 агрегатные и 2 table-valued функции.

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

Полученное расширение можно загрузить в SQLite CLI командой .load <имя файла> или в любой популярный менеджер SQLite.

UTF-8 (лирическое отступление)

Для написания функций, работающих со строками, надо иметь в виду, что по умолчанию строки в SQLite используют кодировку UTF-8 с переменным количеством байт для кодирования символа (от 1 до 4): 123xyz — 6 байт, 123эюя — 9 байт, т.е. по 2 байта на каждый символ кириллицы. Само кодирование длины символа весьма простое: если двоичное представление байта имеет вид 0xxxxxxx, то символ занимает 1 байт, 110xxxxx — 2 байта (этот и следующий), 1110xxxx — 3 байта и 11110xxx — 4 байта. А если 10xxxxxx, то значит, что этот байт используется в кодирование текущего символа. К примеру строка «яz» в бинарном виде имеет вид: 11010001 10001111 01111010 и по первому байту следует, что размер первого символа 2 байта, а второго — один.

К примеру, в реализации функции length в исходниках SQLIte, считается общее число байт z и число не стартовых байт z0, т.е. имеющих вид 10xxxxxx, а результат — это z - z0.

Агрегатные функции

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

Сумма всех чисел - aggsum

Нахождение среднего - aggavg

В вышеприведенных примерах нет отдельной обработки NULL-значений, которые интерпретируются как 0, что не совпадает со стандартным поведением, не учитывающим такие значения

with t as (select 1 a union select null)
select avg(a), aggavg(a) from t; --> 1, 0.5

Виртуальные таблицы

Механизм виртуальных таблиц, позволяет работать с внешними данными, так как они были бы обычной таблицей. В качестве отправной точки для создания своей виртуальной таблицы документация рекомендует использовать шаблон, где есть небольшое описание происходящего.

В минимальной реализации (достаточной в большинстве случаев), когда данные доступны только для чтения и некритично, что для поиска используется полный просмотр таблицы, необходимо реализовать поля xConnect, xDisconnect, xBestIndex для самой таблицы и xOpen, xClose, xFilter, xNext, xEof, xRowid (необязательно, если у таблицы есть первичный ключ) и xColumn для работы курсора (где вместо x — имя расширения) в структуре sqlite3_module и потом передать её в sqlite3_create_module.

Виртуальная таблица vtab, содержащая 2 колонки и 10 строк

Модуль расширяет (subclass) стандартные структуры sqlite3_vtab для таблицы и sqlite3_vtab_cursor для курсора своими полями (iRowid). xBestIndex совместно с xFilter используется планировщиком SQLite при создании плана запросов, использующих эту таблицу. Методы курсора вызываются примерно в следующем порядке: xOpen, xFilter (здесь данные подготавливаются и курсор сбрасывается в начальную позицию), ЦИКЛ-ПО-СТРОКАМ: xEof (проверка на конец данных), xNext (переход на следующую строку), xColumn (перебор всех колонок в текущей строке курсора и возвращение их значений) КОНЕЦ-ЦИКЛА, xClose.

Проверить работу расширения можно запросом:

select * from vtab;

Однако вызов:

create virtual table t using vtab;

вернет ошибку no such module: vtab, потому что не были заданы xCreate и xDestroy, используемые при create virtual table t и drop table t. Если эти поля совпадают с xConnect и xDisconnect, то модуль можно использовать как table-valued функцию (первый запрос). Если же они не заданы, как в примере, то такие таблицы схемо-независимы (eponymous), т.е. main.vtab; и temp.vtab; одно и то же. Примеры таких таблиц dbstat и pragma_table_info. В них, чтобы указать схему, надо использовать скрытые колонки, которые можно получить через pragma table_xinfo('t')

select * from dbstat where schema = 'temp';
select * from pragma_table_info where arg = 't' and schema = 'temp';

Виртуальная таблица vtab, выводящая файл построчно

В отличии от предыдущего примера, сильно изменилась функция xBestIndex, поскольку теперь надо искать с учетом колонки path, и если сразу вернуть SQLITE_OK, то xFilter не получит входящих параметров (что именно делает xBestIndex я до конца так и не разобрался). Чтение данных происходит не в xConnect или xOpen, как можно было бы ожидать, а в xFilter, поскольку какой именно файл будет читаться до момента вызова функции не известно.

Недостаток table-valued таблиц в том, что их структура зафиксирована в расширении, т.к. оптимизатору её нужно знать во время формирования плана запроса, и потому, к примеру, невозможно создать расширение вида select * from vtab('any.csv'). Для таких случаев, предполагается использование create virtual table.

Не-table-valued виртуальная таблица с N-колонками и 10-ю строками

Чтобы запретить использование виртуальной таблицы как table-valued в данном случае в xCreate используется обертка для метода xConnect. Для xDestroy подобное не требуется. Обычно же xCreate и xDestroy должны содержать код, выполняемый при создании и удалении виртуальной таблицы. Так, для FTS-таблиц, в базе создаются и удаляются вспомогательные индексные таблицы, необходимые для работы поиска.

При вызове:

create virtual table t using vtab(4)

передаваемые параметры в xConnect будут: имя модуля — vtab, схема — main, имя создаваемой таблицы — t, и последний параметр, задающий число столбцов, — 4.

VFS расширения

Для обеспечения портируемости SQLite использует специальные VFS-расширения (в основном для работы с файловой системой, откуда видимо и пошло сокращение), когда стандартные способы не поддерживаются, напр. на микроконтроллерах. Также расширения такого типа могут использоваться для работы с зашифрованными файлами SQLite, как к примеру это делает SQLite3 Multiple Ciphers, учета статистики обращения к диску, логирования обращений к блокам и так далее. В качестве стартового примера можно посмотреть демонстрационное расширение.

Дополнительные материалы

Application-Defined SQL Functions — оф. документация по функциям
The Virtual Table Mechanism Of SQLite — оф. документация по виртуальным таблицам
The SQLite OS Interface or «VFS» — оф. документация по VFS
Книга Jay A. Kreibich — Using SQLite. Еще актуальна.
Расширения, поддерживаемые разработчиками SQLite (и уровень выше)

Как написать расширение для SQLite в примерах