27.3.4.5. Compiling and Installing User-Defined 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
sql/udf_example.cc that is included in
the MySQL source distribution.
The immediately following instructions are for Unix.
Instructions for Windows are given later in this section.
The udf_example.cc 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 a
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 either with a
single string argument of the form
'xxx.xxx.xxx.xxx' or with 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 udf_example.cc
If you are using gcc, you should be able to
create udf_example.so with a simpler
command:
shell> make udf_example.so
You can easily determine 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.)
After 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
the server's plugin directory. This directory is given by the
value of the plugin_dir system variable.
(Note: This a change in MySQL
5.1. For earlier versions of MySQL, the shared object can be
located in any directory that is searched by your system's
dynamic linker.)
On some systems, the ldconfig program that
configures the dynamic linker does not recognize a shared
object unless its name begins with lib. In
this case you should rename a file such as
udf_example.so to
libudf_example.so.
On Windows, you can compile user-defined functions by using
the following procedure:
You need to obtain the BitKeeper source repository for
MySQL 5.1. See
Section 2.8.3, “Installing from the Development Source Tree”.
In the source repository, look in the
VC++Files/examples/udf_example
directory. There are files named
udf_example.def,
udf_example.dsp, and
udf_example.dsw there.
In the source repository, look in the
sql directory. Copy the
udf_example.cc from this directory to
the VC++Files/examples/udf_example
directory and rename the file to
udf_example.cpp.
Open the udf_example.dsw file with
Visual Studio VC++ and use it to compile the UDFs as a
normal project.
After the shared object file has been installed, notify
mysqld about the new functions with these
statements:
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';
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;
mysql> DROP FUNCTION avgcost;
The CREATE FUNCTION and DROP
FUNCTION statements update the
func system table 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 previously 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 continues to use the old version.
An active function is one that has been loaded with
CREATE FUNCTION and not removed with
DROP FUNCTION. All 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.
