This is extension for PostgreSQL which allows you:
- Connect to SphinxSearch / ManticoreSearch;
- Execute any search queries and get results as PostgreSQL table;
- Get Meta information after each Sphinx search query.
The SphinxSearch client message protocol is the same as mysql, so, you need:
mysql-clientdevelopment package (ormariadb-clientdevelopment package)postgresqldevelopment packagegcccompilermakeutility
Typical installation procedure may look like this:
$ git clone https://github.com/dimv36/sphinxlink.git
$ cd sphinxlink
$ make
$ sudo USE_PGXS=1 make install
$ psql DB -c "CREATE EXTENSION sphinxlink;"
For connection/disconnection to (from) SphinxSearch server use those functions:
sphinx_connect(conname text, host text DEFAULT '127.0.0.1', port integer DEFAULT 9306)
sphinx_disconnect(conname text)
e.g.:
SELECT sphinx_connect('myconn', '192.168.1.1');
SELECT sphinx_disconnect('myconn');
To get already opened connections use function sphinx_connections():
sphinx_connections(conname text, OUT host text, OUT integer port)
e.g.:
SELECT * FROM sphinx_connections();
If you have already opened connection, use function sphinx_query:
sphinx_query(conname text, query text)
e.g.:
SELECT docid FROM sphinx_query('myconn', 'SELECT docid FROM my_index WHERE MATCH(''Something&interesting'')' AS ss (docid integer);
If you want that extension automatically opens connection to SphinxSearch and execute query, use function sphinx_query_params:
sphinx_query(host text, port integer, query text)
e.g.:
SELECT docid FROM sphinx_query_params('192.168.1.1', 9306, 'SELECT docid FROM my_index WHERE MATCH(''Something&interesting'')' AS ss (docid integer);
To get query stat, use functions sphinx_meta() and sphinx_meta_params():
sphinx_meta(conname text)
sphinx_meta_params(host text, port integer)
Those function return execution stats of last query as TABLE(varname text, value text)
Extension also provides functions sphinx_query() and sphinx_query_params() with extra parameter match_clause:
sphinx_query(conname text, query text, match_clause text)
sphinx_query_params(host text, port integer, query text, match_clause text)
Those functions have deal with parametrized queries in query argument, e.g.:
SELECT docid FROM my_index WHERE MATCH(?)
with match_clause='Something&interesting' will be executed as:
SELECT docid FROM my_index WHERE MATCH('Something&interesting')
Full example:
SELECT * FROM sphinx_query('conn', 'SELECT docid FROM my_index WHERE MATCH(?)', 'Something&interesting') AS ss (docid integer);
SELECT * FROM sphinx_query_params('127.0.0.1', 9306, 'SELECT docid FROM my_index WHERE MATCH(?)', 'Something&interesting') AS ss (docid integer);
Dmitry Voronin [email protected]