MySQL предоставляет широкий набор встроенных функций, которые покрывают значительную часть ежедневных задач. В ситуациях, когда необходимо реализовать что-то специфичное для вашего проекта - можно создать Хранимую Функцию (Stored Function). Однако, при всей своей гибкости, не все задачи можно легко написать на SQL. В таких ситуациях на помощь приходят User Defined Functions - компилируемые в нативный код функций загружаемых из shared library.

Создать свою UDF, в целом, не сложно надо реализовать несколько методов си-API.

Для самой простой UDF достаточно реализовать всего лишь одну функцию:

extern "C" double xxx(UDF_INIT *initid,
        UDF_ARGS *args,
        char *is_null,
        char *error) {
    return 0.0;
}

Для функций, возвращающих INTEGER или STRING сигнатура будет чуть отличаться.

Входящие аргументы можно достать из полей структуры UDF_ARGS - просто по индексу args->args[0] (количество аргументов хранится в args->arg_count, а их типы в массиве args->arg_type). Возвращаемое значение можно вернуть в MySQL - путем возвращения значения из функции :)

Чтобы вызвать нашу функцию из SQL - ее надо собрать в shared library, и подложить в каталог для плагинов (полный путь можно получить выполнив select @@plugin_dir;). После чего функцию нужно загрузить CREATE FUNCTION xxx RETURNS REAL SONAME "xxx.dylib"; После успешной загрузки, нашу функцию можно будет вызывать из консоли MySQL: SELECT xxx();

Ура! У нас есть работающая функция!

Жизненный цикл UDF

MySQL вызывает UDF-функцию на каждой строке. Если хочется сохранять какое-то состояние между вызовами функции - состояние надо где-то хранить. Создавать изменяемые глобальные переменные для UDF-функций не рекомендуется, так как UDF должны быть потоко-безопасными. MYSQL предоставляет возможность хранить разделяемое состояние за указателем UDF_INIT->ptr, а для большего удобства MySQL вызовет для нас функцию xxx_init() перед выполнением SQL statement-а и xxx_deinit()- по окончании. В этих методах мы сможем выделять и освобождать ресурсы.

extern "C" bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void xxx_deinit(UDF_INIT *intd);

Помимо аллокации ресурсов, в xxx_init() мы можем валидировать входные параметры: если мы не хотим работать с типами, с которыми функция вызвана - надо вернрнуть true - MySQL прервет выполнение SQL Statement-а и показажет message в качестве текста ошибки.

Для Агрегатных UDF-функций жизненный цикл чуть более сложный:

1. Вызывается xxx_init()

2. Выполняется запрос, строки разбиваются на группы согласно GROUP BY.

3. Для сброса накопленной статистики на первой строке группы вызывается  xxx_clear()

4. Для каждой строки в группе вызывается xxx_add()

5. В конце вызывается xxx() для получения значения агрегатной функции

6. Шаги 3-5 повторяются

7. Вызывается xxx_deinit()

Let’s code!

Попробуем реализовать агрегатную функцию рассчета квантилей. Воспользуемся алгоритмом и структурой данных t-digest ( https://github.com/tdunning/t-digest/ ) а точнее - его реализацию на языке Си( https://github.com/RedisBloom/t-digest-c ). Этот алгоритм позволяет для потока входящих значений с высокой точностью оценить квантили, при этом использую небольшое количество памяти. (Для наших целей этот алгоритм даже избыточен - т.к. мы высчитываем только один квантиль, а t-digest позволяет делать любое число запросов к накопленной статистике).

Начнем с инициализации:

#include "tdigest.h"
#include "include/mysql.h"
typedef struct {
    td_histogram_t *tdigest;
} Data;

extern "C" bool mysql_tdigest_init(
        UDF_INIT *initid,
        UDF_ARGS *args,
        char *message) {
    if(args->arg_count != 2) {
{1}     strlcpy(message, "2 args expected", MYSQL_ERRMSG_SIZE);
        return true;
    }
    // force arguments to double
{2} args->arg_type[0]=REAL_RESULT;
    args->arg_type[1]=REAL_RESULT;
{3} Data data =(Data)malloc(sizeof(Data));
    data->tdigest = td_new(100); // 100 is recommended by author
    initid->ptr =(char*)data;
    return false; // This function should return 1 if something goes wrong.
}

1. messages - указатель на буфер, куда можно вывести текст ошибки длиной до MYSQL_ERRMSG_SIZE байт. Рекомендуется ограничиваться 80-ю символвами для лучшего UX.

2. args->arg_type содержит массив из args->arg_count элементов, указывающих тип аргументов. Можно самостоятельно валидировать эти значения (если наша функция поддерживает различные типы входящих аргументов) или можно указать желаемые типы данных - MySQL сам проверит типы и по возможности приведет к нужному типу.

3. Создаем структурку, в которой будем хранить разделяемое состояние

В конце работы, надо освободить все занятые ресурсы. Напишем deinit-функцию:

extern "C" void mysql_tdigest_deinit(UDF_INIT *initid) {
    Data *data = (Data*) (initid->ptr);
    td_free(data->tdigest);
    free(data);
    initid->ptr = NULL;
}

Жизненный цикл UDF-функции отлично ложится на API библиотеки t-digest-c, реализация этих методов тоже тривиальна:

extern "C" void mysql_tdigest_clear(
        UDF_INIT *initid,
        unsigned char *is_null,
        unsigned char *error) {
    Data *data =(Data*) (initid->ptr);
    td_reset(data->tdigest);
}

extern "C" void mysql_tdigest_add(
        UDF_INIT *initid,
        UDF_ARGS *args,
        unsigned char *is_null,
        unsigned char *error) {
    Data data =(Data) (initid->ptr);
    double value = ((double) (args->args[0]));
    td_add(data->tdigest, value, 1);
}

extern "C" double mysql_tdigest(
        UDF_INIT *initid,
        UDF_ARGS *args,
        unsigned char *is_null,
        unsigned char *error) {
    Data data =(Data) (initid->ptr);
    double quantile = *((double*) (args->args[1]));
    return td_quantile(data->tdigest, quantile);

}

Собираем:

clang -dynamiclib -lm -lc -Lt-digest-c/build/src -ltdigest_static -I percona-server/bld/include src/main.cc -o mysql_tdigest.dylib

Подкладываем нашу библиотеку к MySQL:

cp mysql_tdigest.dylib /usr/local/opt/mysql/lib/plugin/

Создаем функцию:

CREATE AGGREGATE FUNCTION mysql_tdigest RETURNS REAL SONAME "mysql_tdigest.dylib";

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

mysql> SELECT avg(amount_charged), mysql_tdigest(amount_charged, 0.5),  mysql_tdigest(amount_charged, 0.9) FROM orders GROUP BY user_id LIMIT 5;
+---------------------+------------------------------------+------------------------------------+
| avg(amount_charged) | mysql_tdigest(amount_charged, 0.5) | mysql_tdigest(amount_charged, 0.9) |
+---------------------+------------------------------------+------------------------------------+
|           1661.3750 |                             1247.1 |                             3809.0 |
|           1079.3158 |                              908.0 |                             2740.0 |
|           1331.5581 |                             1280.5 |                             1991.0 |
|           2987.0000 |                             2796.5 |                             6235.0 |
|           1150.0909 |                             1289.0 |                             1630.0 |
+---------------------+------------------------------------+------------------------------------+

Удалить функцию:

DROP FUNCTION mysql_tdigest;

Вроде, не сложно =)

Прочие возможности

На самом деле никто не заставляет писать UDF на чистом Си - главное, поддерживать C calling convention. Например, можно использовать C++ wrappers ( https://jira.percona.com/browse/PS-7348 ), а если не бояться unsafe и raw-pointers - то функции на Rust FFI тоже отлично работают:

#[no_mangle]
pub unsafe extern "C" fn my_summ(
    initid: *mut UDF_INIT,
    args: *mut UDF_ARGS,
    is_null: *mut c_uchar,
    error: *mut c_uchar,
) -> f64 {
    0.0
}

Ограничения

• Не получится заставить работать в Managed Database - ни один Cloud Provider не разрешит загружать пользовательскую shared library

• для работы со Statment Based Replication наши UDF должны быть на 100% детерминированными и установленными на всех репликах. Из хороших новостей - SBR используется не часто, а UDF не требуют дополнительной сопроводительной работы - после первоначальной настройки, при последующих рестартах UDF функции будут автоматически загружены из библиотеки.

• Ограниченый набор типов входящих аргументов и возвращаемых значений

• segfault в UDF так же уронит и весь MySQL

Выводы

API довольно краток и создать UDF не сложно. UDF может стать палочкой-выручалочкой, когда других выходов расширить стандартный набор функций в MySQL не осталось. А может стать проклятием при заезде в облако. Можно надеяться, что в MySQL, как и в PostgreSQL, завезут поддержку интерпретируемых языков - Python или ECMAScript.

Ссылки

Официальная документация