Extending MySQL Made Easy: Plugin API FULLTEXT parsers, Storage Engines, and More

Sergei Golubchik

Plugin API is new in MySQL 5.1, so you can plugin your own API commands.

Built-in versioning
Easy to maintain and distribute
Generic — allows you to load any functionality into mysqld

Plugins can add new status variables for SHOW STATUS
For the future, plugins will allow you to add new commandline options, new server variables, and new SQL keywords.

Plugin administration:
INSTALL PLUGIN foo SONAME 'bar.so'
UNINSTALL PLUGIN foo
SHOW PLUGINS
INFORMATION_SCHEMA.PLUGINS
–plugin-dir=/path/to/dir

Plugin types:
Storage Engines
Fulltext Search Parsers
code changes text before it goes to the FULLTEXT data
can be used to search non-plaintext data formats, such as pdf, doc, mp3
can be used to parse Chinese and Japanese text.

Plugin types future:
UDFs — versioning, securyt, ease of use
Lang Modules for stores procedures (perl, php…)
Pluggable Authentication (ie, LDAP)
Fulltext Search Engines (replace the whole thing!)
Maybe new SQL commands?

Fulltext Parser plugin has to take the object, extract text, split into words, postprocess, and then the words are stored into the index. Currently the extract part does nothing because fulltext is used on strings only right now, and postprocessing is pruning out words < min length or > max length

So, a small parser plugin that allows external files to be indexes — you give mysql path.

Make a new directory (say, from_file). copy the template files for the fulltext files.

mv plugin_example.c from_file.c
it’s a Makefile.am, so change the libdir and the SOURCES:w
Change configure.in AC_INIT file to use from_file

look for mysql_declare_plugin — it contains
type of plugin
descriptor (what’s different for different types)
name
author
description
init function (on load)
de-init function (on unload)
version
status variables

Chapter 28.2 in MySQL 5.1 documentation has a complete walk-through of all the structures.

automake using your new .am file, and make install, and then load the plugin.

on http://forge.mysql.com you can find plugins at Database software -> MySQL specific -> Plugins

SHOW PLUGINS shows name, status (ie, active), type (Storage Engine or fulltext parser) and library (filename, if blank, it’s built in).

To use,
CREATE TABLE t1 (file text, fulltext(file) with parser from_file);
insert into t1 values('etc/passwd'),('/etc/services');
select * from t1; shows we have filenames only in the table.
select * from t1 where match file against ('root'); will give the result of the filename.

If you try to uninstall a plugin that is on an open table, it will have a status of “deleted” but the table that’s open will still use it. flush tables or a closed connection, and now your table is invalid. 🙂 So be careful when uninstalling plugins to find the tables using them FIRST, drop the tables, and then uninstall.

This plugin does not load the data in the file every time a query runs. This plugin should be able to handle a load_file() for a filename OR a filename. If you change the file and need to reindex, you have to do REPAIR TABLE.

——————————
I loved having the example, and this stuff seems so easy to just implement. Sure, it’s the featureset itself that’s difficult, but …

Sergei Golubchik

Plugin API is new in MySQL 5.1, so you can plugin your own API commands.

Built-in versioning
Easy to maintain and distribute
Generic — allows you to load any functionality into mysqld

Plugins can add new status variables for SHOW STATUS
For the future, plugins will allow you to add new commandline options, new server variables, and new SQL keywords.

Plugin administration:
INSTALL PLUGIN foo SONAME 'bar.so'
UNINSTALL PLUGIN foo
SHOW PLUGINS
INFORMATION_SCHEMA.PLUGINS
–plugin-dir=/path/to/dir

Plugin types:
Storage Engines
Fulltext Search Parsers
code changes text before it goes to the FULLTEXT data
can be used to search non-plaintext data formats, such as pdf, doc, mp3
can be used to parse Chinese and Japanese text.

Plugin types future:
UDFs — versioning, securyt, ease of use
Lang Modules for stores procedures (perl, php…)
Pluggable Authentication (ie, LDAP)
Fulltext Search Engines (replace the whole thing!)
Maybe new SQL commands?

Fulltext Parser plugin has to take the object, extract text, split into words, postprocess, and then the words are stored into the index. Currently the extract part does nothing because fulltext is used on strings only right now, and postprocessing is pruning out words < min length or > max length

So, a small parser plugin that allows external files to be indexes — you give mysql path.

Make a new directory (say, from_file). copy the template files for the fulltext files.

mv plugin_example.c from_file.c
it’s a Makefile.am, so change the libdir and the SOURCES:w
Change configure.in AC_INIT file to use from_file

look for mysql_declare_plugin — it contains
type of plugin
descriptor (what’s different for different types)
name
author
description
init function (on load)
de-init function (on unload)
version
status variables

Chapter 28.2 in MySQL 5.1 documentation has a complete walk-through of all the structures.

automake using your new .am file, and make install, and then load the plugin.

on http://forge.mysql.com you can find plugins at Database software -> MySQL specific -> Plugins

SHOW PLUGINS shows name, status (ie, active), type (Storage Engine or fulltext parser) and library (filename, if blank, it’s built in).

To use,
CREATE TABLE t1 (file text, fulltext(file) with parser from_file);
insert into t1 values('etc/passwd'),('/etc/services');
select * from t1; shows we have filenames only in the table.
select * from t1 where match file against ('root'); will give the result of the filename.

If you try to uninstall a plugin that is on an open table, it will have a status of “deleted” but the table that’s open will still use it. flush tables or a closed connection, and now your table is invalid. 🙂 So be careful when uninstalling plugins to find the tables using them FIRST, drop the tables, and then uninstall.

This plugin does not load the data in the file every time a query runs. This plugin should be able to handle a load_file() for a filename OR a filename. If you change the file and need to reindex, you have to do REPAIR TABLE.

——————————
I loved having the example, and this stuff seems so easy to just implement. Sure, it’s the featureset itself that’s difficult, but …

Comments are closed.