damlev

damlev copied to clipboard

Damerau-Levenshtein Distance UDF for MySQL Supports upper bounding for fast searching and UTF-8 case insensitive throught iconv.

Copyright (C) 2013 Diego Torres

Implementing

https://github.com/torvalds/linux/blob/8a72f3820c4d14b27ad5336aed00063a7a7f1bef/tools/perf/util/levenshtein.c

Redistribute as you wish, but leave this information intact.

Compiling

Run make.    It will try to autodetect architecture and generate 32 or x86_64 bit
version. Enable testing with "make debug". An executable instead of
a library will be built. Execute it to run some tests regarding
memory allocation and levenshtein core calculations.

Errors you may get when compiling:

In file included from ./src/damlevlim.c:16:0: ./src/damlevlim.h:26:23: fatal error: my_global.h: No such file or directory #include <my_global.h> ^ compilation terminated. Makefile:30: recipe for target 'mysqldamlevlim' failed make: *** [mysqldamlevlim] Error 1

Development libraries from mysql are missing.
Do "sudo apt-get install libmysqlclient-dev"

/usr/include/gnu/stubs.h:7:27: fatal error: gnu/stubs-32.h: No such file or directory

You're missing the 32 bit libc dev package. This is answered here:

http://stackoverflow.com/questions/7412548/gnu-stubs-32-h-no-such-file-or-directory

- On Ubuntu it's called libc6-dev-i386 - do "sudo apt-get install"
libc6-dev-i386. See below for extra instructions for Ubuntu 12.04.
- On Red Hat distros, the package name is glibc-devel.i686 (Thanks to
David Gardner's comment)
- On CentOS 5.8, the package name is glibc-devel.i386 (Thanks to JimKleck's comment)
- On CentOS 6.3, the package name is glibc-devel.i686.
- On SLES it's called glibc-devel-32bit - do zypper in glibc-devel-32bit

Loading UDFs in MySQL STEP 1

Copy compiled udf to plugin dir. To know where exactly, ask MySQL:

mysql> SHOW VARIABLES LIKE 'plugin_dir';

and use result directory.

To modify plugin_dir to point to a custom location:

my.cnf: plugin_dir = /usr/lib/mysql/plugin

Don't forget to restart MySQL!!

Loading UDFs in MySQL STEP 2

Bind the library to an UDF inside MySQL (only do it once,
survives MySQL reboot). Depending your MySQL binaries (32
or 64 bits), insert the right library.

mysql> DROP FUNCTION IF EXISTS damlevlim;

mysql> CREATE FUNCTION damlevlim RETURNS INT SONAME 'mysqldamlevlim.so';

Check if UDF was loaded OK.

mysql> SELECT * FROM mysql.func;
+-----------+-----+-------------------+----------+
| name      | ret | dl                | type     |
+-----------+-----+-------------------+----------+
| damlevlim |   2 | mysqldamlevlim.so | function |
+-----------+-----+-------------------+----------+
1 row in set (0.00 sec)

Using damlevlim

mysql> select damlevlim();
ERROR: DAMLEVLIM() requires three arguments (string, string, int)

First string is compared against second string. Compare only the first
int chars (upper bound).

mysql> select damlevlim('coche', 'çóche', 5);
+--------------------------------+
| damlevlim('coche', 'çòche', 5) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

Further reading about MySql, UTF8 and charset encoding

http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

How to search for multibyte characters in a column

Use MySql CHAR_LENGTH function to find rows with multi-byte characters:

mysql> SELECT name FROM users WHERE LENGTH(name) != CHAR_LENGTH(name);

How to search for strange multibyte characters in a column

(don't remember source of original tip)
Use MySql HEX and PHP bin2hex functions

mysql> SELECT name, HEX(name) FROM users;

Get the result back into PHP, and run a bin2hex on the string, compare
it to MySQL’s hex of that same string.