Использование mysql функций в Sphinx

Мигрируя с одного сервера на другой, я увидел mysql таблицу с кешем некоторых данных, которая занимала ~ 500 Гб. Вспомнив, что давно хотел прикрутить поиск по кешу с помощью Sphinx решил не откладывать и приступил к делу..

Sphinx, для обычных задач, довольно просто конфигурируется и все работает. Однако, в моем случае, я столкнулся с рядом трудностей, о которых и пойдет речь.

Для начала, в таблице кеша данные были сериализированны, т.е. были примерно в таком формате:

a:2:{s:7:"titleHL";s:13:"Article title";s:6:"descHL";s:40:"Article <strong>highlighted</strong>text";}

понятное дело, что это не совсем подходит для поиска. Конечно, можно и по такому полю делать поиск, но мне нужен был поиск именно по обычному тексту: title + description. Кроме того, нужно было исключить тег <strong> из текста, иначе он бы мешал при поиске.

Насколько мне известно, mysql не умеет делать unserialize строки как и strip_tags , что вполне логично. В связи с этим, у меня был выбор: написать плагин для mysql либо попробовать обойтись строковыми функциями, я выбрал второй вариант.

Пока что, сосредотачиваемся на выборке данных и забываем про Sphinx.

Разбиваем задачу на части:

• Сделать десериализацию для определенных значений массива (у меня это titleHL и descHL)
• Вырезать определенные теги
• Склеить нужные значения (titleHL+descHL) и на выходе получить выборку вида: id, content

Открываем любой редактор позволяющий выполнять SQL и начинаем писать. У меня получился такой код:

DROP FUNCTION IF EXISTS fn_get_serialized_val;
DROP FUNCTION IF EXISTS fn_strip_tag;
DROP FUNCTION IF EXISTS fn_get_content;

DELIMITER $$

CREATE FUNCTION fn_get_serialized_val(var VARCHAR(24), serialized TEXT) RETURNS TEXT
BEGIN
    DECLARE ret TEXT;
    DECLARE marker VARCHAR(16);
    DECLARE ln INT;
    SET marker = CONCAT('s:',CHAR_LENGTH(var),':"',var,'";s:');
    SET ret = SUBSTRING(serialized, LOCATE(marker, serialized)+CHAR_LENGTH(marker));
    SET ln = CAST(SUBSTRING(ret, 1, LOCATE(':', ret)-1) AS UNSIGNED);
    SET ret = SUBSTRING(ret, 1+CHAR_LENGTH(ln)+2, ln);
    RETURN ret;
END$$

CREATE FUNCTION fn_strip_tag(tag VARCHAR(24), source TEXT) RETURNS TEXT
BEGIN
    RETURN REPLACE(REPLACE(source,CONCAT('<',tag,'>'),' '),CONCAT('</',tag,'>'),' ');
END$$

CREATE FUNCTION fn_get_content(description TEXT) RETURNS TEXT
BEGIN
    DECLARE title TEXT;
    DECLARE descr TEXT;
    SET title = fn_strip_tag('strong', fn_get_serialized_val('titleHL', description));
    SET descr = fn_strip_tag('strong', fn_get_serialized_val('descHL', description));
    RETURN CONCAT(title,'. ',descr);
END$$

DELIMITER ;

SELECT id, fn_get_content(description) as content FROM data WHERE id=1;

DROP FUNCTION IF EXISTS fn_get_serialized_val;
DROP FUNCTION IF EXISTS fn_strip_tag;
DROP FUNCTION IF EXISTS fn_get_content;

вот для такой таблицы:

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `data` (`id`, `description`) VALUES
(1, 'a:2:{s:7:\"titleHL\";s:13:\"Article title\";s:6:\"descHL\";s:40:\"Article <strong>highlighted</strong>text\";}');

Следующее, что нужно сделать - это перенести всё это в конфигурацию Sphinx-а.

Все достаточно просто, у Sphinx-а, есть два конфигурационных параметра: sql_query_pre и sql_query_post, с их помощью мы и объявляем все то, что мы подготовили.

Код конфига будет чуть ниже, а я расскажу про проблему которая и послужила толчком к написанию этой статьи. Итак, написав и отладив код в SQL редакторе, я перенес его в конфиг Sphinx-а. Однако при попытке запуска индексации:

indexer --config /etc/sphinx/site.com/sphinx.conf --all

я получил ошибку:

ERROR: index 'ind_site': sql_query_pre[0]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER $$' at line 1

MySQL не хотела принимать DELIMITER не под каким соусом. Я долго тупил и искал в чем же проблема. Как оказалось все достаточно просто и это не проблема Sphinx-а, как я полагал увидев ошибку.  Вот ответ c stackoverflow на вопрос: How to execute mysql command DELIMITER, который мне все объяснил:

You probably don't need to change the delimiter.

The delimiter is needed in the CLI to tell where the SQL statement ends, because the CLI is going to keep reading and executing more statements until you tell it to stop (e.g., with exit or Control-D). But what it actually reads is just a stream of characters; it somehow needs to figure out where one statement ends and the next starts. That's what the delimiter does.

In PHP, each function call executes one statement. There can't be multiple statements in one function call, so there is no need for a way to delimit them. The statement is the entire string. This is true of the old mysql_query as well as the newer mysqli_query and PDO. Of course, there is mysqli_multi_query if you really want to pass multiple queries to one function.

In the case of a stored procedure/trigger/function/etc., there can be multiple statements, but that's handled by MySQL itself (and is always ;, AFAIK). So as far as PHP is concerned, that's still one statement.

The delimiter setting you're seeing in phpMyAdmin is probably being used to split statements apart, and is probably being done in PHP code. It has to do this because it is accepting user input consisting of multiple statements, but must pass only one statement per function call. (I haven't checked the phpMyAdmin code to be completely sure of this).

для тех кто плохо понимает английский - проблема в том, что DELIMITER не надо указывать в уже разделенных командах, а в конфигурации Sphinx-а, это именно так.

В итоге, я удалил указание DELIMITER-а в запросах, в конфиге и тогда всё заработало. Вот так те же запросы выглядят в конфиге Sphinx-a:

source src_site {
...
sql_query_pre = DROP FUNCTION IF EXISTS fn_get_serialized_val;
sql_query_pre = DROP FUNCTION IF EXISTS fn_strip_tag;
sql_query_pre = DROP FUNCTION IF EXISTS fn_get_content;

sql_query_pre = CREATE FUNCTION fn_get_serialized_val(var VARCHAR(24), serialized TEXT) RETURNS TEXT \
                BEGIN \
                    DECLARE ret TEXT; \
                    DECLARE marker VARCHAR(16); \
                    DECLARE ln INT; \
                    SET marker = CONCAT('s:',CHAR_LENGTH(var),':"',var,'";s:'); \
                    SET ret = SUBSTRING(serialized, LOCATE(marker, serialized)+CHAR_LENGTH(marker)); \
                    SET ln = CAST(SUBSTRING(ret, 1, LOCATE(':', ret)-1) AS UNSIGNED); \
                    SET ret = SUBSTRING(ret, 1+CHAR_LENGTH(ln)+2, ln); \
                    RETURN ret; \
                END

sql_query_pre = CREATE FUNCTION fn_strip_tag(tag VARCHAR(24), source TEXT) RETURNS TEXT \
                BEGIN \
                    RETURN REPLACE(REPLACE(source,CONCAT('<',tag,'>'),' '),CONCAT('</',tag,'>'),' '); \
                END

sql_query_pre = CREATE FUNCTION fn_get_content(description TEXT) RETURNS TEXT \
                BEGIN \
                    DECLARE title TEXT; \
                    DECLARE descr TEXT; \
                    SET title = fn_strip_tag('strong', fn_get_serialized_val('titleHL', description)); \
                    SET descr = fn_strip_tag('strong', fn_get_serialized_val('descHL', description)); \
                    RETURN CONCAT(title,'. ',descr); \
                END

sql_query_post = DROP FUNCTION IF EXISTS fn_get_serialized_val;
sql_query_post = DROP FUNCTION IF EXISTS fn_strip_tag;
sql_query_post = DROP FUNCTION IF EXISTS fn_get_content;

sql_query      = SELECT id, fn_get_content(description) as content FROM data
sql_query_info = SELECT * FROM data WHERE id=$id
...
}

Тут удалять функции в sql_query_pre не обязательно, т.к. они удаляются в конце, но я решил все таки оставить, чтобы пример для отладки был более полный.

После этого выполняем индексацию:

indexer --config /etc/sphinx/site.com/sphinx.conf --all

И можем проверять поиск:

search --config /etc/sphinx/site.com/sphinx.conf --index indsite -a and

Очередная задача решена 🙂