UDF Documentation

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

Compatibility

The code has been tested on Ubuntu 8.04 LTS .

MySQL

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:

• ed : integer ed(string s1, string s1): returns the edit distance between s1 and s2. The function assumes that the strings have the same case (i.e., both strings are lower case or both are upper case).
• edth : boolean edth(string s1, string s1, integer th): returns true if the edit distance between s1 and s2 is smaller or equal than th. This function might be slightly faster than ed as it can stop as soon as the edit distance goes above the threshold. The function assumes that the strings have the same case (i.e., both strings are lower case or both are upper case).
• edrec : boolean edrec(string s, string rec, integer th): returns true if there is a token in rec that has an edit distance from s smaller or equal to th. The string in rec is tokenized using the following separators: " " (white space), "," (comma), and "." (dot). The function assumes that the query string is in lower case.

Bellow there is one SQL examples for each function:

mysql> SELECT ed('abc', 'ad');
2
mysql> SELECT edth('abc', 'abcd', 1);
1
mysql> SELECT edrec('ab', 'xx ad xx', 1);
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 libed.so ed.c
gcc -Wall -O3 -I/usr/include/mysql -shared -o libedth.so edth.c
gcc -Wall -O3 -I/usr/include/mysql -shared -o libedrec.so edrec.c
toolkit/src/udf/mysql/ed>

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 .

Contributors

• Rares Vernica