Advanced MySQL Features - High Performance MySQL - page 296

Unlike stored functions written in SQL, UDFs cannot currently read
and write tables—at least, not in the same transactional context as the
statement that calls them. This means they're more helpful for pure
computation, or interaction with the outside world. MySQL is gaining
more and more possibilities for interaction with resources outside of the
server. The functions Brian Aker and Patrick Galbraith have created to
communicate with memcached ( http://tangent.org/586/Memcached
_Functions_for_MySQL.html ) are a good example of how this can be
done with UDFs.
If you use UDFs, check carefully for changes between MySQL versions when you up-
grade, because they might need to be recompiled or even changed to work correctly
with the new MySQL server. Also make sure your UDFs are absolutely thread-safe,
because they execute within the MySQL server process, which is a pure multithreaded
environment.
There are good libraries of prebuilt UDFs for MySQL, and many good examples of how
to implement your own. The biggest repository of UDFs is at http://www.mysqludf.org .
The following is the code for the NOW_USEC() UDF we'll use to measure replication speed
in Chapter 10 :
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <stdio.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>
extern "C" {
my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
char *now_usec(
UDF_INIT *initid,
UDF_ARGS *args,
char *result,
unsigned long *length,
char *is_null,
char *error);
}
my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
return 0;
}
char *now_usec(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error) {
struct timeval tv;
struct tm* ptm;
char time_string[20]; /* e.g. "2006-04-27 17:10:52" */
char *usec_time_string = result;
time_t t;
/* Obtain the time of day, and convert it to a tm struct. */
gettimeofday (&tv, NULL);
t = (time_t)tv.tv_sec;