Back to Index

UDF Documentation

This document describes the use of the User Defined Functions (UDF) provided in the Flamingo Toolkit.


The code has been tested on Ubuntu 8.04 LTS .


The UDFs discussed bellow are provided for the MySQL database software.

Edit Distance

The module provides the implementation of three UDF functions based on edit distance (aka Levenshtein distance ) function:

Bellow there is one SQL examples for each function:

mysql> SELECT ed('abc', 'ad');
mysql> SELECT edth('abc', 'abcd', 1);
mysql> SELECT edrec('ab', 'xx ad xx', 1);

The boolean values are returned as 0 and 1.

The implementations are in C language and are according to the UDF specifications for the MySQL database software. Each function is defined in a separate file called function_name .c. Besides the actual functions there are additional helper functions defined (some of them required by the MySQL UDF specification). For each function there is an function_name .sql file which shows how to CREATE and DROP the function from MySQL and plenty of examples of using that function.

Compilation and Installation

The following packages are needed for compiling the code: gcc, make, mysql-server, and libmysqlclient-dev. They can be installed using:

> sudo apt-get install gcc make mysql-server libmysqlclient-dev

A Makefile is provided for compiling and testing the code. To compile the code do the following:

> cd toolkit/src/udf/mysql/ed
toolkit/src/udf/mysql/ed> make
gcc -Wall -O3 -I/usr/include/mysql -shared -o ed.c
gcc -Wall -O3 -I/usr/include/mysql -shared -o edth.c
gcc -Wall -O3 -I/usr/include/mysql -shared -o edrec.c

The Makefile creates a shared library file for each function. Now the libraries need to be copied in /usr/lib/mysql/plugin/ so that MySQL sees then and MySQL needs to be restarted:

toolkit/src/udf/mysql/ed> sudo cp libed*.so /usr/lib/mysql/plugin/
toolkit/src/udf/mysql/ed> sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                 [ OK ] 
 * Starting MySQL database server mysqld                                 [ OK ] 
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

Now we can add the functions to MySQL and test them. To do that we can connect to MySQL and Copy-and-Paste the SQL statements provided for each function in their respective .sql file. Alternatively, we can use:

toolkit/src/udf/mysql/ed> make test
cat ed.sql edth.sql edrec.sql | mysql --database=mysql --password
Enter password:

By default we can type ENTER when prompted for MySQL password. In case the current user is not a valid MySQL user or it does not have privileges to install UDFs, you can add --user=root to the mysql line and provide the MySQL root password. The output will show what function was called, which parameters were used, and, on the next line, the returned value of the function.

For more details about the UDFs in MySQL see MySQL Manual - 20.2. Adding New Functions to MySQL .