Добавление новых функций в MySQL
Существует два способа добавления функций в MySQL:
Можно добавить функцию посредством интерфейса определяемых пользователем функций (user-definable function - UDF). Определяемые пользователем функции добавляются и удаляются динамически с помощью команд CREATE FUNCTION и DROP FUNCTION (см. раздел Синтаксис CREATE FUNCTION/DROP FUNCTION).
Можно добавить функцию как "родную" (встроенную) функцию MySQL.
"Родные" функции компилируются вместе с остальным кодом сервера mysqld и становятся постоянно доступными.
Каждый метод имеет преимущества и недостатки:
Если вы пишете определяемую пользователем функцию, то помимо сервера необходимо инсталлировать объектный файл. В этом нет необходимости при
компиляции функции вместе с сервером.
UDFы можно добавить в бинарную поставку MySQL. Для "родных" функций необходимо изменять поставку исходного кода.
При обновлении поставки MySQL можно продолжать использовать ранее инсталлированные UDFы. Для "родных" функций нужно при каждом обновлении
вносить изменения повторно.
Независимо от выбранного метода добавления новых функций, их можно использовать точно так же, как и встроенные функции, подобные ABS() или SOUNDEX().
Синтаксис CREATE FUNCTION/DROP FUNCTION
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
SONAME shared_library_name
DROP FUNCTION function_name
Определяемая пользователем функция (UDF) - это средство, позволяющее расширить MySQL за счет новой функции, которая работает подобно "родным" (встроенным) функциям MySQL, таким как ABS() и CONCAT().
AGGREGATE - новая опция для версии MySQL 3.23. AGGREGATE - функция работает точно так же, как и "родные" GROUP-функции MySQL GROUP вроде SUM или COUNT().
CREATE FUNCTION сохраняет имя, тип и имя разделяемой библиотеки функции в системной таблице mysql.func. Чтобы создавать и удалять функции, необходимо обладать привилегиями INSERT и DELETE для базы данных mysql.
Все активные функции подгружаются при каждом запуске сервера, за исключением случая, когда mysqld запускается с опцией --skip-grant-tables. Тогда инициализация UDF пропускается и UDFы недоступны (активная функция - это функция, которая была загружена посредством CREATE FUNCTION и не удалена с помощью DROP FUNCTION).
Инструкции по написанию определяемых пользователем функций находятся в разделе Добавление новых функций в MySQL. Чтобы механизм UDF работал, функции должны быть написаны на C или C++, используемая операционная система должна поддерживать динамическую загрузку и mysqld должен быть скомпилирован динамически (а не статически).
Отметим, что для того, чтобы работала AGGREGATE, таблица mysql.func должна содержать столбец type. В противном случае следует запустить сценарий mysql_fix_privilege_tables, чтобы внести нужные исправления.
Добавление новой определяемой пользователем функции
Для того чтобы работал механизм UDF, функции должны быть написаны на C или на C++, а используемая операционная система должна поддерживать динамическую загрузку. В поставку исходного кода входит файл 'sql/udf_example.cc', в котором определены пять новых функций. К этому файлу следует обращаться, если нужно узнать, как работает соглашение о вызовах UDF.
Чтобы mysqld имел возможность использовать UDF-функции, необходимо сконфигурировать MySQL с --with-mysqld-ldflags=-rdynamic. Причина здесь в том, что на многих платформах (включая Linux) можно загружать динамическую библиотеку (посредством dlopen()) из статически скомпонованной программы, получаемой при использовании --with-mysqld-ldflags=-all-static. Если есть потребность использовать UDF, которой нужно обращаться к символам из mysqld (как в примере функции methaphone из 'sql/udf_example.cc', которая использует default_charset_info), то программу необходимо компоновать с -rdynamic (обращайтесь к man dlopen).
Для каждой функции, которую предполагается использовать в командах SQL, следует определять соответствующие функции C (или C++). В дальнейшем в качестве имени для примера функции мы будем использовать имя xxx. Чтобы различать применение в SQL и C/C++, для вызова SQL-функции мы будем использовать обозначение XXX() (прописными), а xxx() (строчными) - для вызова функции C/C++.
Для реализации интерфейса для XXX() требуются следующие функции C/C++:
xxx() (обязательная)
Главная функция. Она вычисляет результат функции. Соответствие между типами SQL и возвращаемым типом функции C/C++ показано в приведенной ниже
таблице:
| Тип SQL | Тип C/C++ | STRING | char * | INTEGER | long long | REAL | double |
xxx_init() (необязательная)
Функция инициализации для xxx(). Может быть использована:
для проверки количества аргументов к XXX();
для проверки того, что аргументы имеют требуемый тип или, в противном случае, для указания MySQL приводить аргументы к нужным типам при вызове
главной функции;
для распределения всей памяти, требуемой основной функцией;
для задания максимальной длины результата;
для задания (для REAL-функций) максимального числа десятичных знаков после запятой;
для указания, может ли результатом быть NULL.
xxx_deinit() (необязательная)
Функция деинициализации для xxx(). Должна освобождать всю память, выделенную функцией инициализации.
При запуске SQL-команды XXX() MySQL вызывает функцию инициализации xxx_init(), чтобы дать ей возможность выполнить все необходимые установки, такие как проверка аргументов и распределение памяти. Если xxx_init()
возвращает ошибку, то выполнение SQL-команды прерывается с сообщением об ошибке, а главная функция и функция деинициализации не вызываются. В противном случае для каждой строки вызывается главная функция xxx(). После того как будут обработаны все строки, вызывается функция деинициализации xxx_deinit(), чтобы выполнить необходимую очистку.
Для агрегатных функций (подобных SUM()) необходимо также подготовить следующие функции:
xxx_reset() (обязательная)
Сбрасывает сумму и обрабатывает аргумент как начальное значение для новой группы.
xxx_add() (обязательная)
Добавляет аргумент к имеющейся сумме.
При использовании агрегатных UDF-функций MySQL работает следующим образом:
Вызывается xxx_init(), чтобы агрегатная функция могла распределить память, которая понадобится для хранения результатов.
Таблица сортируется в соответствии с выражением GROUP BY.
Для первой строки новой группы вызывается функция xxx_reset().
Для каждой новой строки, принадлежащей к той же группе, вызывается функция xxx_add().
Когда группа меняется, или после завершения обработки последней строки вызывается xxx() для получения итога.
Повторяются шаги 3-5, пока не будут обработаны все строки.
Вызывается xxx_deinit(), чтобы UDF могла освободить всю распределенную ею память.
Все функции должны поддерживать многопоточность (не только главная, но также и функции инициализации и деинициализации). Это означает, что непозволительно распределять какие-либо глобальные или статические переменные с изменяющимися значениями! Если требуется память, то ее следует распределять в xxx_init() и освобождать в xxx_deinit().
Последовательность вызова UDF для простых функций
Главная функция должна быть определена, как это показано здесь. Обратите внимание на то, что тип возвращаемого значения и параметры варьируются в зависимости от того, как определена SQL-функция XXX() в команде CREATE FUNCTION - как возвращающая STRING, INTEGER или REAL:
Для STRING-функций:
char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
Для INTEGER-функций:
long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
Для REAL-функций:
double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
Функции инициализации и деинициализации объявляются следующим образом:
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
Параметр initid передается всем трем функциям. Он указывает на структуру UDF_INIT, используемую для передачи информации между функциями. Поля структуры UDF_INIT перечислены ниже. Функция инициализации должна заполнять все поля, которые ей следует изменить (чтобы использовать для поля значение по умолчанию, его необходимо оставить в неизменном виде):
my_bool maybe_null
xxx_init() должна устанавливать maybe_null в 1, если xxx() может возвращать NULL. Значение по умолчанию будет 1, если хоть один аргумент объявлен как maybe_null.
unsigned int decimals
Количество знаков после запятой. По умолчанию используется максимальное количество знаков для аргументов, переданных в основную функцию.
(Например, если функции передаются 1,34, 1,345 и 1,3, то значением по умолчанию будет 3, поскольку 1,345 имеет 3 знака после запятой.
unsigned int max_length
Максимальная длина строкового результата. Значение по умолчанию зависит от типа результата функции. Для строковых функций по умолчанию используется размер наиболее длинного аргумента. Для целочисленных функций значение по умолчанию составляет 21 цифру. Для вещественных функций по умолчанию берется 13 плюс количество знаков после запятой, которое задается initid->decimals (для числовых функций длина включает знак и символ десятичной точки). Если требуется возвращать значение типа BLOB, то поле можно установить равным либо 65 Kб либо 16 Mб; эта память не распределяется, а применяется для определения того, какой использовать тип столбцов, если понадобится временно хранить данные.
char *ptr
Указатель, используемый функцией по своему усмотрению. Например, функции могут применять initid->ptr для передачи между функциями распределенной памяти. В xxx_init() память распределяется и назначается этому указателю:
initid->ptr = allocated_memory;
В xxx() и xxx_deinit() должны обращаться к initid->ptr для использования или освобождения памяти.
Последовательность вызова UDF для агрегатных функций
Ниже приведено описание функций, которые необходимо определить при создании агрегатной UDF-функции.
char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
Эта функция вызывается, когда MySQL находит первую строку в новой группе. В функции необходимо сбросить все внутренние переменные, в которых накапливаются значения, и затем установить переданный аргумент как первый аргумент в группе.
Во многих случаях это реализуется путем сброса всех переменных и последующего вызова xxx_add().
char *xxx_add(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
Эта функция вызывается для всех строк, принадлежащих к одной группе, за исключением первой. В функции к внутренней накопительной переменной следует добавить значение UDF_ARGS.
Функция xxx() должна быть объявлена точно так же, как это делается при определении простой UDF-функции (См. раздел Последовательность вызова UDF для простых функций).
Вызов этой функции происходит, когда все строки в группе обработаны. Обычно функция не должна обращаться к переменной args, а возвращаемое значение должно базироваться на внутренних накопительных переменных.
Какая бы то ни было, обработка аргументов в xxx_reset() и xxx_add() должна проводиться точно так же, как для нормальных UDF-функций (см. раздел 3 Обработка аргументов).
Организация возврата значений в xxx() эквивалентна используемой для нормальной UDF (см. раздел Возвращаемые значения и обработка ошибок).
Аргументы-указатели is_null и error одинаковы для всех вызовов xxx_reset(), xxx_add() и xxx(). Их можно использовать для запоминания того, что произошла ошибка, или когда функция xxx() должна возвращать NULL. Заметьте, что сохранять строку в *error нельзя! Это всего лишь 1-байтовый флаг!
is_null сбрасывается для каждой группы (перед вызовом xxx_reset()). error не сбрасывается никогда.
Если is_null или error окажется установленным после xxx(), MySQL вернет NULL в качестве результата групповой функции.
Обработка аргументов
Параметр args указывает на структуру UDF_ARGS, содержащую перечисленные ниже поля:
unsigned int arg_count
Количество аргументов. Это значение следует проверять в функции инициализации, если необходимо, чтобы функция вызывалась с определенным количеством аргументов. Например:
if (args->arg_count != 2)
{
strcpy(message,"XXX() requires two arguments");
return 1;
}
enum Item_result *arg_type
Тип для каждого аргумента. Возможные значения типа: STRING_RESULT, INT_RESULT и REAL_RESULT. Чтобы контролировать принадлежность аргументов к нужному типу и возвращать ошибку, если это не так, следует проверить массив arg_type в функции инициализации. Например:
if (args->arg_type[0] != STRING_RESULT ||
args->arg_type[1] != INT_RESULT)
{
strcpy(message,"XXX() requires a string and an integer");
return 1;
}
В качестве альтернативы требованию, чтобы аргументы были определенного типа, можно использовать функцию инициализации для назначения элементам arg_type выбранных типов. В этом случае MySQL будет приводить аргументы к этим типам для каждого вызова xxx(). Например, чтобы указать на приведение первых двух аргументов к строковому и целочисленному типам, выполните в xxx_init():
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
char **args
args->args передает в функцию инициализации информацию общего характера об аргументах, с которыми была вызвана функция. Для константного аргумента i args->args[i] указывает на значение аргумента (ниже приведены инструкции о том, как правильно получать доступ к значениям). Для неконстантого аргумента args->args[i] есть 0. Константный аргумент - это выражение, в котором используются только константы, вроде 3 или 4*7-2 или SIN(3.14). Неконстантный аргумент - это выражение, ссылающееся на значения, которые могут изменяться от строки к строке, такие как имена столбцов или обращения к функциям с неконстантными аргументами. Для каждого вызова главной функции args->args содержит фактические аргументы, переданные для обрабатываемой в данный момент строки.
Функции могут ссылаться на аргумент i следующим образом:
Аргумент типа STRING_RESULT передается в виде указателя на строку плюс длина, чтобы обеспечить обработку двоичных данных или данных произвольной длины. Содержимое строки доступно посредством args->args[i], а длина строки представляет собой args->lengths[i]. Не следует исходить из предположения, что символ \0 отмечает конец строки.
Для аргумента типа INT_RESULT необходимо привести args->args[i] к значению типа long long:
long long int_val;
int_val = *((long long*) args->args[i]);
Для аргумента типа REAL_RESULT необходимо привести args->args[i] к значению типа double:
double real_val;
real_val = *((double*) args->args[i]);
unsigned long *lengths
Для функции инициализации массив lengths указывает максимальную длину строки для каждого аргумента. Изменять этот массив нельзя. При каждом вызове главной функции lengths содержит фактические длины всех строковых аргументов, переданных для обрабатываемой в текущий момент строки. Для типов аргументов INT_RESULT или REAL_RESULT lengths также содержит максимальную длину аргумента (как для функции инициализации).
Возвращаемые значения и обработка ошибок
Функция инициализации должна возвращать 0, если ошибок нет, и 1 в противном случае. Если происходит ошибка, xxx_init() должна поместить сообщение об ошибке с завершающим '\0' в параметр message. Сообщение будет возвращено клиенту. Буфер сообщения имеет длину MYSQL_ERRMSG_SIZE символов, но надо стараться, чтобы сообщение не превышало 80 символов - для соответствия ширине стандартного экрана терминала.
Возвращаемое главной функцией xxx() значение является значением функции для функций long long и double. Строковые функции должны возвращать указатель на результат и помещать длину строки в аргумент length.
Эти величины следует устанавливать равными содержимому и длине возвращаемого значения. К примеру:
memcpy(result, "result string", 13);
*length = 13;
Размер буфера result, передаваемого вычислительной функции, составляет 255 байтов. Если этого достаточно для полученного результата, то о распределении памяти для результатов беспокоиться нечего.
Если строковая функция должна возвращать строку длиннее, чем 255 байтов, то для строки необходимо выделять память с помощью malloc() в функции xxx_init() или в функции xxx() и освобождать ее в функции xxx_deinit(). Указатель на распределенную память можно сохранить в поле ptr структуры UDF_INIT, чтобы в последующих вызовах xxx() использовать эту память повторно (см. раздел Последовательность вызова UDF для простых функций.
Чтобы указать в главной функции на возврат значения NULL, is_null устанавливается в 1:
*is_null = 1;
Чтобы указать в главной функции на возврат ошибки, в 1 устанавливается параметр error:
*error = 1;
Если xxx() устанавливает для какой-либо строки *error в 1, то значение функции будет NULL для этой и всех последующих строк, обрабатываемых командой, в которой вызывается XXX() (для последующих строк xxx() даже не будет вызываться). Примечание: в версиях MySQL до 3.22.10 было необходимо устанавливать как *error так и *is_null:
*error = 1;
*is_null = 1;
Компиляция и установка определяемых пользователем функций
Файлы, реализующие UDFы, должны компилироваться и устанавливаться на машине, где работает сервер. Эта процедура описана ниже для файла примеров UDF 'udf_example.cc', входящего в поставку исходного кода MySQL. Данный файл содержит следующие функции:
metaphon() возвращает metaphon-строку для строкового аргумента. Эта строка в общем напоминает soundex-строку, но более приспособлена для английского языка.
myfunc_double() возвращает отношение суммы ASCII-значений символов своих аргументов к суммарной длине аргументов.
myfunc_int() возвращает суммарную длину своих аргументов.
sequence([const int]) возвращает последовательность, начиная с заданного номера, либо с 1, если номер не задан.
lookup() возвращает IP-адрес для имени удаленного компьютера.
reverse_lookup() возвращает имя удаленного компьютера для IP-адреса. Функция может вызываться для строки "xxx.xxx.xxx.xxx" либо для четырех чисел.
Динамически загружаемый файл должен компилироваться как разделяемый объектный файл с помощью команды следующего вида:
shell> gcc -shared -o udf_example.so myfunc.cc
Корректные опции компилятора для своей системы можно легко получить, запустив следующую команду в каталоге 'sql' дерева исходных текстов MySQL:
shell> make udf_example.o
Следует выполнить команду компиляции, подобную приведенной выше make, с той разницей, что надо удалить опцию -c ближе к концу строки и добавить -o 'udf_example.so' в конце строки (в некоторых системах, возможно, -c придется оставить в команде).
После компиляции разделяемого объектного файла, содержащего UDFы, следует установить его и дать о нем знать MySQL. В результате компиляции разделяемого объектного модуля из 'udf_example.cc' получается файл с именем наподобие 'udf_example.so' (точное имя может на разных платформах может быть различным). Скопируйте этот файл в какой-нибудь просматриваемый ld каталог, вроде '/usr/lib'. Во многих системах можно устанавливать переменную окружения LD_LIBRARY или LD_LIBRARY_PATH для указания каталога, в котором размещены файлы UDF-функций. В руководстве по dlopen указывается, какую переменную следует использовать в данной системе. Необходимо сделать соответствующие установки в mysql.server или safe_mysqld и перезапустить mysqld.
После установки библиотеки следует уведомить mysqld о новых функциях следующими командами:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup
-> RETURNS STRING SONAME "udf_example.so";
mysql> CREATE AGGREGATE FUNCTION avgcost
-> RETURNS REAL SONAME "udf_example.so";
Функции могут быть удалены с помощью DROP FUNCTION:
mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;
Команды CREATE FUNCTION и DROP FUNCTION обновляют системную таблицу func в базе данных mysql. В таблицу записываются имя функции, ее тип и имя разделяемой библиотеки. Для создания и удаления функций необходимо обладать привилегиями INSERT и DELETE для базы данных mysql.
Недопустимо использовать CREATE FUNCTION для добавления функции, которая уже была создана. Если необходимо переустановить функцию, ее следует удалить с помощью DROP FUNCTION и затем переустановить посредством CREATE FUNCTION. Эти действия приходится выполнять, например, когда компилируется новая версия данной функции, и надо, чтобы mysqld получил новую версию. Иначе сервер будет продолжать пользоваться старой версией.
Активные функции подгружаются при каждом запуске сервера, за исключением случая, когда mysqld запускается с опцией --skip-grant-tables. Тогда инициализация UDF пропускается и UDFы недоступны (активная функция - это функция, которая была загружена посредством CREATE FUNCTION и не удалена с помощью DROP FUNCTION).
Добавление новых родных функции
В этом разделе приведена процедура добавления новой "родной" функции. Следует учитывать, что в бинарную поставку "родные" функции добавить невозможно, поскольку эта процедура требует изменения исходного кода MySQL. Поэтому необходимо собственноручно компилировать MySQL из поставки исходного текста. Кроме того, при переходе на другую версию MySQL (например, при выпуске новой версии) все изменения придется повторить для этой новой версии.
Чтобы добавить новую "родную" функцию MySQL, необходимо выполнить следующие действия:
Добавьте в 'lex.h' одну строку, определяющую имя новой функции в массиве sql_functions[].
Если прототип функции простой (вообще без аргументов или принимает один, два или три аргумента), то в 'lex.h' вторым аргументом в массиве sql_functions[] следует указать SYM(FUNC_ARG#) (где # количество аргументов) и добавить в 'item_create.cc' функцию, создающую объект функции. В качестве примеров можно рассмотреть ABS и create_funcs_abs(). Если прототип функции сложный (например, принимает переменное число аргументов), то следует добавить две строки в 'sql_yacc.yy'. Одна строка служит для указания препроцессору, какой символ должен определить yacc (строку следует добавить в начало файла). Затем определяются параметры функции и правило разбора simple_expr пополняется "элементом" с этими параметрами. Чтобы получить представление о том, как это делается, в качестве примера просмотрите все вхождения ATAN в 'sql_yacc.yy'.
В 'item_func.h' объявляется класс, наследуемый от Item_num_func или Item_str_func, в зависимости от того, какое значение возвращает функция - числовое или строковое.
В 'item_func.cc' добавьте одно из следующих объявлений, в зависимости от того, какая функция определяется - числовая или строковая:
double Item_func_newname::val()
longlong Item_func_newname::val_int()
String *Item_func_newname::Str(String *str)
Если объект наследуется от любого стандартного элемента (подобного Item_num_func), то, возможно, потребуется определить только одну из
перечисленных выше функций и возложить на родительский объект заботу об остальных функциях. Например, класс Item_str_func определяет функцию val(), выполняющую atof() над значением, возвращенным ::str().
Возможно, понадобится также определить следующую функцию объекта:
void Item_func_newname::fix_length_and_dec()
Эта функция должна как минимум вычислять max_length на основе переданных аргументов. max_length является максимальным количеством символов, которое может возвращать функция. Эта функция также должна устанавливать maybe_null = 0, если невозможно, чтобы главная функция возвратила значение NULL. Узнать, может ли какой-либо аргумент функции возвращать NULL, функция может путем проверки поля/переменной maybe_null аргумента. В качестве типичного примера того, как это делается, можно рассмотреть Item_func_mod::fix_length_and_dec.
Все функции должны поддерживать многопоточность (другими словами, непозволительно использовать какие-либо глобальные или статические переменные в функции без их защиты примитивами взаимного исключения).
Если желательно возвращать NULL, из ::val(), ::val_int() или ::str(), то необходимо устанавливать null_value в 1 и возвращать 0.
Для функции объекта ::str() существуют следующие дополнительные аспекты:
Аргумент String *str обеспечивает строковый буфер, который может быть использован для размещения результата (дополнительную информацию о типе String можно найти в файле 'sql_string.h').
Функция ::str() должна возвращать строку, содержащую результат, или (char*) 0, если результат NULL.
На сегодняшний день при написании всех строковых функций принято избегать какого бы то ни было распределения памяти, за исключением случаев, когда это абсолютно необходимо!
1 2 3
8 8 8
| |
*
Обратная связь