GNU Info

(mysql.info)UDF compiling

---

Prev: UDF return values Up: Adding UDF

---

Enter node , (file) or (file)node

Compiling and Installing User-definable Functions
.................................................

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.

   * `sequence([const int])' returns an sequence starting from the given
     number or 1 if no number has been given.

   * `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
`dlopen' 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'.)