14.1.4 Compilation et installation de fonction définies par l'utilisateur

Files implementing UDFs must be compiled and installed on the host where the server runs. This process is described below for the example UDF file `udf_example.cc' that is included in the MySQL source distribution. This file contains the following functions:

• metaphon() returns a metaphon string of the string argument. This is something like a soundex string, but it's more tuned for English.
• myfunc_double() returns the sum of the ASCII values of the characters in its arguments, divided by the sum of the length of its arguments.
• myfunc_int() returns the sum of the length of its arguments.
• lookup() returns the IP number for a hostname.
• reverse_lookup() returns the hostname for an IP number. The function may be called with a string "xxx.xxx.xxx.xxx" or four numbers.

A dynamically-loadable file should be compiled as a sharable object file, using a command something like this:

shell> gcc -shared -o udf_example.so myfunc.cc

You can easily find out the correct compiler options for your system by running this command in the `sql' directory of your MySQL source tree:

shell> make udf_example.o

You should run a compile command similar to the one that make displays, except that you should remove the -c option near the end of the line and add -o udf_example.so to the end of the line. (On some systems, you may need to leave the -c on the command.)

Once you compile a shared object containing UDFs, you must install it and tell MySQL about it. Compiling a shared object from `udf_example.cc' produces a file named something like `udf_example.so' (the exact name may vary from platform to platform). Copy this file to some directory searched by ld, such as `/usr/lib'. On many systems, you can set the LD_LIBRARY or LD_LIBRARY_PATH environment variable to point at the directory where you have your UDF function files. The dopen manual page tells you which variable you should use on your system. You should set this in mysql.server or safe_mysqld and restart mysqld.

After the library is installed, notify mysqld about the new functions with these commands:

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";

Functions can be deleted using 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;

The CREATE FUNCTION and DROP FUNCTION statements update the system table func in the mysql database. The function's name, type and shared library name are saved in the table. You must have the insert and delete privileges for the mysql database to create and drop functions.

You should not use CREATE FUNCTION to add a function that has already been created. If you need to reinstall a function, you should remove it with DROP FUNCTION and then reinstall it with CREATE FUNCTION. You would need to do this, for example, if you recompile a new version of your function, so that mysqld gets the new version. Otherwise the server will continue to use the old version.

Active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialization is skipped and UDFs are unavailable. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)