Get
libmemcache-1.3.x from http://people.freebsd.org/~seanc/libmemcache/ and install it. udf_memcache.c create-function-memcache.sqlCompile it with:
$ gcc -DDBUG_OFF -shared -o udf_memcache.so udf_memcache.c / -g -Wall -O2 -lmemcache `mysql_config --cflags`Copy udf_memcache.so into a directory which contains the your shared libs (e.g. /usr/local/lib).
Install the functions into MySQL by executing the create-function script:
$ mysql < create-function-memcache.sqlYou are tired of writing your UDFs for MySQL in C or never wanted to write them in C at all ? How about writing them in lua ?
LUA is
easy to learn easy to embed easy to useand not to forget lua has extensions for everything:
LuaSocket - how about some HTTP requests ? LuaSQL - connecting back to the MySQL Server ? ...UDF_Lua is registered in the MySQL Forge.
As example we want to read the real startup time of the errorlog.
-- we are run with the permissions of the mysqld -- -- let's try the read the "ready for connections" from the errorlog and look -- for the last [Note]: -- -- 061124 17:28:39 [Note] /usr/sbin/mysqld-max: ready for connections. local f = assert(io.open(params[1], "r")) local readysince = nil while true do local line = f:read() if not line then break end local match = string.match(line, "^([0-9]+ [0-9:]+) %[Note%]") if match then readysince = match end end f:close() return readysinceLet's execute it:
> select lua_file(".../ready-since.lua", "/var/lib/mysql/jan-dev.err") as ready_since; +-----------------+ | ready_since | +-----------------+ | 061125 18:10:48 | +-----------------+Yeah, that matches the string from the errorlog.
If shorter script you can also just pass the lua-code around. As usual a string is returned and the parameters are available via the params table.
]> select lua("return params[1]", 1) as echo; +------+ | echo | +------+ | 1 | +------+ 1 row in set (0.02 sec)For Aggregation functions like SUM() you have to provide a function which is called for each row (mysql_udf_aggr) and one which returns the final result (mysql_udf_result).
_G["mysql_udf_aggr"] = function (params) if nil == rows then rows = 0 end rows = rows + params[1] end _G["mysql_udf_result"] = function (params) return rows endRunning it against the world-database:
root@localhost [world]> select lua_aggr_file("/home/jan/sum.lua", population) from City; +------------------------------------------------+ | lua_aggr_file("/home/jan/sum.lua", population) | +------------------------------------------------+ | 1429559884 | +------------------------------------------------+ 1 row in set (0.02 sec) root@localhost [world]> select sum(population) from City; +-----------------+ | sum(population) | +-----------------+ | 1429559884 | +-----------------+ 1 row in set (0.01 sec)Requirements:
lua 5.1... and the usual UDF requirements
$ wget http://jan.kneschke.de/projects/mysql/udf/UDF-mysql_udf_lua-0.9.2.tar.gz $ gzip -cd UDF-mysql_udf_lua-0.9.2.tar.gz | tar xf - $ cd UDF-mysql_udf_lua-0.9.2 $ ./configure $ make ## copy to a directory which is checked by dlopen() # cp ./.libs/mysql_udf_lua.so /usr/lib/ $ mysql mysql> create function lua returns string soname 'mysql_udf_lua.so'; mysql> create function lua_file returns string soname 'mysql_udf_lua.so'; mysql> create function lua_aggr_file returns string soname 'mysql_udf_lua.so';Thanks to Hartmuts CodeGenMySQLUDF the UDF is packaged with automake/autoconf.
The parameters of the SELECT call are passed as a lua-table to the functions. The indexing for this table is starting a 1 as it is expected by lua-devs. The type between MySQL and LUA are mapped as expected
Whatever you return is either nil or converted into a string. In case of a execution error NULL will be return and the error will be logged to the mysqld-error-log.
For the UDF talk in a few hours I wanted to have a nice and hopefully useless example. It should show how the internals work and what has to be setup even to those who never have used a C-Compiler.
I chose to implement a function to send mail directly from the database which I hope will never be used. Really. It is not a good idea to move application code into the database.
#include <mysql.h> #include <stdio.h> #include <string.h> #include <stdlib.h> my_bool mail_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { initid->maybe_null = 0; return 0; } long long mail(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { char *prg; FILE *p; int i; if (args->arg_count != 2) { strcpy(error, "MAIL() needs receipient and body"); return 1; } if (args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT) { strcpy(error, "MAIL() needs receipient and body as string"); return 1; } for (i = 0; i < args->lengths[0]; i++) { char c = args->args[0][i]; if (!((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c == '@') || (c == '.') || (c >= '0' && c <= '9') || (c == '-' ) || (c == '_' ))) { strcpy(error, "the receipient contains a illegal char"); return -1; } } prg = malloc(strlen("/usr/lib/sendmail ") + args->lengths[0] + 1); strcpy(prg, "/usr/lib/sendmail "); strcat(prg, args->args[0]); p = popen(prg, "w"); free(prg); if (NULL == p) { strcpy(error, "opening pipe failed"); return -1; } fwrite(args->args[1], args->lengths[1], 1, p); fclose(p); *is_null = 0; *error = 0; return 0; }That's the code which has to be compiled with:
$ gcc -I /usr/local/mysql/include/ -shared -o udf_mail.so mail.c -Wall $ cp udf_mail.so /usr/lib/ at mysql side: mysql> create function mail returns integer soname 'udf_mail.so'; mysql> select mail('jan@kneschke.de', 'Subject: UDF Mail/r/n/r/nYou got mail'); Playing with MySQL ORDER BY RAND() groupwise max Analyzing complex queries Trees in SQL MySQL Proxy MySQL Protocol UDF SQL parser in rage(l) MySQL Proxy 0.5.0 released MySQL Proxy learns R/W Splitting MySQL Proxy and a Global Transaction ID MySQL Proxy: Query Stats MySQL Proxy: Tracking Parallel Queries MySQL Proxy: 0.6.0 released