FEDERATED

Using MySQL FEDERATED engine you can connect to a local or remote Manticore instance from MySQL/MariaDB and perform search queries.

Using FEDERATED

An actual Manticore query cannot be used directly with FEDERATED engine and must be "proxied" (sent as a string in a column) due to limitations of the FEDERATED engine and the fact that Manticore implements custom syntax like the MATCH clause.

To search via FEDERATED, you would need to create first a FEDERATED engine table. The Manticore query will be included in a query column in the SELECT performed over the FEDERATED table.

Creating FEDERATED compatible MySQL table:

SQL
📋
CREATE TABLE t1
(
    id          INTEGER UNSIGNED NOT NULL,
    year        INTEGER NOT NULL,
    rating      FLOAT,
    query       VARCHAR(1024) NOT NULL,
    INDEX(query)
) ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://FEDERATED@127.0.0.1:9306/DB/movies';
Response
Query OK, 0 rows affected (0.00 sec)

Query FEDERATED compatible table:

SQL
📋
SELECT * FROM t1 WHERE query='SELECT * FROM movies WHERE MATCH (\'pie\')';
Response
+----+------+--------+------------------------------------------+
| id | year | rating | query                                    |
+----+------+--------+------------------------------------------+
|  1 | 2019 |      5 | SELECT * FROM movies WHERE MATCH ('pie') |
+----+------+--------+------------------------------------------+
1 row in set (0.04 sec)

The only fixed mapping is query column. It is mandatory and must be the only column with an index attached.

The Manticore index that is linked via FEDERATED must be an index with storage (plain or RealTime).

FEDERATED table should have columns with same names as remote Manticore index attributes as will be bound to attributes provided in Manticore result set by name, however might map not all attributes but only some of them. Arbitrary expression from query select list which name "hides" index attribute will be used at result set.

Manticore server identifies query from FEDERATED client by user name "FEDERATED". CONNECTION string parameter should be used to specify searchd host, SQL port and indexes for queries issued using this table. Connection string syntax is as follows:

CONNECTION="mysql://FEDERATED@HOST:PORT/DB/INDEXNAME"

Since Manticore doesn't have the concept of database, the DB string can be random as it will be ignored by Manticore, but MySQL requires a value in the CONNECTION string definition. As seen in the example, full SELECT SQL query should be put into a WHERE clause against column query.

Only SELECT statement is supported, not INSERT, REPLACE, UPDATE, DELETE.

FEDERATED tips

One very important note that it is much more efficient to allow Manticore to perform sorting, filtering and slicing the result set than to raise max matches count and use WHERE, ORDER BY and LIMIT clauses on MySQL side. This is for two reasons. First, Manticore does a number of optimizations and performs better than MySQL on these tasks. Second, less data would need to be packed by searchd, transferred and unpacked between Manticore and MySQL.

JOINs can be performed between FEDERATED table and other MySQL tables. This can be used to retrieve information that is not stored in the Manticore index.

SQL
📋
Query to JOIN MySQL based table with FEDERATED table served by Manticore:
SELECT t1.id, t1.year, comments.comment FROM t1 JOIN comments ON t1.id=comments.post_id WHERE query='SELECT * FROM movies WHERE MATCH (\'pie\')';
Response
+----+------+--------------+
| id | year | comment      |
+----+------+--------------+
|  1 | 2019 | was not good |
+----+------+--------------+
1 row in set (0.00 sec)

UDFs and Plugins

Manticore can be extended with user defined functions, or UDFs for short, like this:

SELECT id, attr1, myudf (attr2, attr3+attr4) ...

You can load and unload UDFs dynamically into searchd without having to restart the server, and use them in expressions when searching, ranking, etc. Quick summary of the UDF features is as follows.

  • UDFs can take integer (both 32-bit and 64-bit), float, string, MVA, or PACKEDFACTORS() arguments.
  • UDFs can return integer, float, or string values.
  • UDFs can check the argument number, types, and names during the query setup phase, and raise errors.

We do not yet support aggregation functions. In other words, your UDFs will be called for just a single document at a time and are expected to return some value for that document. Writing a function that can compute an aggregate value like AVG() over the entire group of documents that share the same GROUP BY key is not yet possible. However, you can use UDFs within the builtin aggregate functions: that is, even though MYCUSTOMAVG() is not supported yet, AVG(MYCUSTOMFUNC()) should work alright!

UDFs have a wide variety of uses, for instance:

  • adding custom mathematical or string functions;
  • accessing the database or files from within Manticore;
  • implementing complex ranking functions.

Plugins

Plugins provides more possibilities to extend searching functionality. Currently they may be used to calculate custom ranking, and also to tokenize documents and queries.

Here's the complete plugin type list.

  • UDF plugins (which are just UDFs, but since they're plugged, they're also called 'UDF plugin')
  • ranker plugins
  • indexing-time token filter plugins
  • query-time token filter plugins

This section discusses writing and managing plugins in general; things specific to writing this or that type of a plugin are then discussed in their respective subsections.

So, how do you write and use a plugin? Four-line crash course goes as follows:

  • create a dynamic library (either .so or.dll), most likely in C or C++;
  • load that plugin into searchd using CREATE PLUGIN;
  • invoke it using the plugin specific calls (typically using this or that OPTION).
  • to unload or reload a plugin use DROP PLUGIN and RELOAD PLUGINS respectively.

Note that while UDFs are first-class plugins they are nevertheless installed using a separate CREATE FUNCTION statement. It lets you specify the return type neatly so there was especially little reason to ruin backwards compatibility and change the syntax.

Dynamic plugins are supported in threads and thread_pool workers. Multiple plugins (and/or UDFs) may reside in a single library file. So you might choose to either put all your project-specific plugins in a single common big library; or you might choose to have a separate library for every UDF and plugin; that is up to you.

Just as with UDFs, you want to include src/sphinxudf.h header file. At the very least, you will need the SPH_UDF_VERSION constant to implement a proper version function. Depending on the specific plugin type, you might or might not need to link your plugin with src/sphinxudf.c. However, all the functions implemented in sphinxudf.c are about unpacking the PACKEDFACTORS() blob, and no plugin types are exposed to that kind of data. So currently, you would never need to link with the C-file, just the header would be sufficient. (In fact, if you copy over the UDF version number, then for some of the plugin types you would not even need the header file.)

Formally, plugins are just sets of C functions that follow a certain naming pattern. You are typically required to define just one key function that does the most important work, but you may define a bunch of other functions, too. For example, to implement a ranker called "myrank", you must define myrank_finalize() function that actually returns the rank value, however, you might also define myrank_init(), myrank_update(), and myrank_deinit() functions. Specific sets of well-known suffixes and the call arguments do differ based on the plugin type, but _init() and _deinit() are generic, every plugin has those. Hint: for a quick reference on the known suffixes and their argument types, refer to sphinxplugin.h, we define the call prototypes in the very beginning of that file.

Despite having the public interface defined in ye good old good pure C, our plugins essentially follow the object-oriented model. Indeed, every _init() function receives a void ** userdata out-parameter. And the pointer value that you store at (*userdata) location is then be passed as a 1st argument to all the other plugin functions. So you can think of a plugin as class that gets instantiated every time an object of that class is needed to handle a request: the userdata pointer would be its this pointer; the functions would be its methods, and the _init() and _deinit() functions would be the constructor and destructor respectively.

Why this (minor) OOP-in-C complication? Well, plugins run in a multi-threaded environment, and some of them have to be stateful. You can't keep that state in a global variable in your plugin. So we have to pass around a userdata parameter anyway to let you keep that state. And that naturally brings us to the OOP model. And if you've got a simple, stateless plugin, the interface lets you omit the _init() and _deinit() and whatever other functions just as well.

To summarize, here goes the simplest complete ranker plugin, in just 3 lines of C code.

// gcc -fPIC -shared -o myrank.so myrank.c
#include "sphinxudf.h"
int myrank_ver() { return SPH_UDF_VERSION; }
int myrank_finalize(void *u, int w) { return 123; }

And this is how you use it:

mysql> CREATE PLUGIN myrank TYPE 'ranker' SONAME 'myrank.dll';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, weight() FROM test1 WHERE MATCH('test') OPTION ranker=myrank('');
+------+----------+
| id   | weight() |
+------+----------+
|    1 |      123 |
|    2 |      123 |
+------+----------+
2 rows in set (0.01 sec)

Listing plugins

SHOW PLUGINS

Displays all the loaded plugins and UDFs. "Type" column should be one of the udf, ranker, index_token_filter, or query_token_filter. "Users" column is the number of thread that are currently using that plugin in a query. "Extra" column is intended for various additional plugin-type specific information; currently, it shows the return type for the UDFs and is empty for all the other plugin types.

Example
📋
SHOW PLUGINS;
Response
+------+----------+----------------+-------+-------+
| Type | Name     | Library        | Users | Extra |
+------+----------+----------------+-------+-------+
| udf  | sequence | udfexample.dll | 0     | INT   |
+------+----------+----------------+-------+-------+
1 row in set (0.00 sec)