RDBMSでサーチエンジン風演算子をサポートした全文検索を行う方法

Google風検索式をSQLやIBLの式に変換するPHPライブラリSearchWordsSQLの紹介。(2013.12.20)
接頭辞コールバックの章を追加。(2013.12.29)

はじめに

　RDBMSを使ってデータを保存しているWebアプリケーションで、検索機能を実装したいとする。そういう場合、テーブル上に一定以上に長い文字列を格納する列があるならば、その列については、部分一致での検索ができるようにしたいことが多い。いわゆる全文検索である。
　素朴なRDBMSだと、文字列の部分一致検索ではインデックスが使えないため、実用的な速度で検索を行うことが難しい。しかし最近のRDBMSには、別途全文検索に特化したインデックスを作成し、それを利用して全文検索する機能を備えているものが多くなっている。狭い意味での全文検索機能は、これらを使用すれば比較的簡単に実装することができる。
　例えば、PostgreSQLでは、pg_bigmなどの拡張モジュールを導入することで、全文検索用のインデックスを作成することができるようになる。この方式では、検索の際のSQL文として標準のLIKE演算子を使える。例えばテーブルのBookTextという列に対して「吾輩」という文字列で検索したいならば、「WHERE BookText LIKE '%吾輩%'」のようなWHERE句を与えてやればいいわけである。
　ここまではいいのだが、問題は、これができたというので、欲を出して次にGoogleのような複数の単語や演算子からなる複合検索をサポートしようとすると、急に話が難しくなるということである。例えば、検索式として「吾輩 OR (猫 -クロネコ) -小暮 」が与えられたら、「WHERE (BookText LIKE '%吾輩%' OR (BookText LIKE '%猫%' AND NOT BookText LIKE '%クロネコ%')) AND NOT BookText LIKE '%小暮%'」を作りたい。しかしANDとORの結合の優先順位がSQLとGoogleの検索式の間で異なることもあって、単純に正規表現による置換などで変換するのはなかなか面倒そうである。
　また、MySQLの場合、ブール全文検索(Implied Boolean Logic、略してIBL)という検索式の指定方式がある。これを使うと複合検索式をそのまま渡せるのだが、残念ながらGoogle式の表現とは違っているところがあって、やはり変換は必須となる。IBLの文法についてのMySQLのマニュアルでの説明には曖昧と思われるところがあるが、おそらく上の例をこの方式で表現すると、「+(吾輩 (+猫 -クロネコ)) -小暮」といった形になるはずである。これはこれで変換が簡単とは思えない。

　以上のような問題を解決するため、複合検索式をSQLなどに簡単に変換してくれるPHPベースのライブラリとして、SearchWordsSQLがあるので、以下それをご紹介する。

インストール

　SearchWordsSQLはPHPのライブラリなので、まずはPHPの実行環境が必要である。PHPのバージョンは5.3以降なら問題なく動作する。ライブラリを利用するユーザー側コードも、PHPで実装する。
　GitHubの同ライブラリのプロジェクトサイトからzipをダウンロードし、中のSearchWordsSQL.phpをPHPのインクルードパスのどこかに置く。
　APIのドキュメントが欲しいときは、phpDocumentorをインストールしてからSearchWordsSQL.phpのあるディリクトリにmakedoc.shを置いて実行する(UNIX系環境のみ)という手もあるが、むしろオンライン版を参照するのが手っ取り早いだろう。

使用方法の基礎

使用できる検索式

　SearchWordsSQLで対応しているGoogle風の検索式の文法は、具体的には次のようなものである。

　なお、GoogleはカッコやORを複雑に組み合わせたような検索式には対応していないが、このライブラリではきちんとSQLに変換してくれる。

LIKEを使用する場合

　SQLのLIKE演算子を使った検索を行いたい場合、検索式を変換して実行する典型的なコードは次のようになる(<?php ?>は省略した)。

    require_once 'SearchWordsSQL.php';
    $searchwords = $_GET['q'];
    $sb = new \SearchWordsSQL\SQLBuilder("Body LIKE ?", "\SearchWordsSQL\SQLLikeValueCallback");
    $res = $sb->Build($searchwords);
    if ($sb->isComplement()) die;
    $db = new PDO("pgsql:server=localhost");
    $st = $db->prepare("SELECT * FROM BookText WHERE ${res['SQL']}");
    $st->execute($res['value']);

　まず1行目で、require_onceでライブラリを読み込んでいる。2行目は、URLのqというパラメータで検索式が渡されてくると想定して、$searchwordsにそれを代入している。ここまでは準備作業である。

　3行目がポイントとなる部分である。ここでは\SearchWordsSQL\SQLBuilderクラスのインスタンスを生成している。SQLBuilderクラスは実際に変換を行うためのクラスである。このクラス名はグローバル空間でなくSearchWordsSQL名前空間に存在するので、このように\で区切って名前空間名も指定する必要がある。
　コンストラクタの第一引数には、一つ一つの検索語に対応するSQLの式を、プレースホルダ(通常は「?」だが、使用するライブラリによっては別の文字列を使用することもある)を用いたパラメータ化クエリの形式で指定する。このライブラリは、単純にLIKE演算子を用いた以外の式も生成できるように、一つ一つの検索語をここで指定された文字列で置き換え、それらをユーザーが指定した検索式の文構造に応じてSQLの演算子であるAND、OR、NOTやカッコで組み合わせた論理式(boolean型の式)を出力する仕組みとなっている。また、Webから入力を受け取る場合、それをRDBMS側に受け渡すにはパラメータ化クエリを使用するのが常識であり、このライブラリもその流儀に従っている。SQL実行の際は、このパラメータ部分に実際の個々の検索語が代入されることになる。
　今回はテーブルのBody列に対してLIKE演算子を用いて検索したいという想定で、"Body LIKE ?"と指定する。例えば検索式が「吾輩 猫」のように指定された場合、このコードから最終的に生成されるのは「(Body LIKE ? AND Body LIKE ?)」のようなものになる。一つ目のプレースホルダには「吾輩」に相当する語が、二つ目には「猫」に相当する語が入ることになる。後述するように、これらの値も、後にライブラリ側から渡されるようになっている。
　コンストラクタの第二引数は、値の変換用コールバック関数を指定する。今のプレースホルダに入る値についての説明について疑問に思われた読者もおられると思うが、LIKE演算子の右側のオペランドとして指定するものは、単なる検索語ではまずい。例えば、「吾輩」を検索したいなら、オペランドとしては'吾輩'でなく'%吾輩%'を指定しなければならない。また、もし「%」などが検索語に入っているなら、それをエスケープする必要もある。そういった処理を行うのが、この値の変換用コールバック関数である。これは本来ユーザー側が実装するのであるが、SQL標準のLIKE演算子用のコールバック関数については、ライブラリ側に標準で用意されており、ここではそれを利用している。\SearchWordsSQL\SQLLikeValueCallbackがそれである。なお、PHPの場合、引数として関数を渡す方法としては、function() { }の構文を用いた匿名関数を渡す方法のほかに、ここでしているように関数名を文字列として渡す方法がある。
　第二引数は省略可能だが、省略すると後になんら変換が行われないままの検索語が渡されてくることになる。自前の変換関数を実装することが必要となるような場合には、実際には、コールバック関数は使わないでおいて、値を取得した後にユーザー側コードで勝手に変換するというやり方もあり得るが、SQLLikeValueCallbackを利用するなら、ここでのコードのようにした方が便利だろう。

　4行目は、検索式を渡して実際に変換処理を行う部分である。変換結果は、後述のようにSQL文だけを含むのではないので、配列として返ってくる。

　5行目では、検索式が補集合の形式になっているときに処理を中止する。補集合の形式になっている検索式というのは、例えば「-猫」とだけ指定されているようなもので、これはしばしば検索結果が巨大になるので、負荷などの面で弾きたいことが多い。Build()を呼んだ後にisComplement()を呼ぶと、そのような形式になっているかどうかを教えてくれる(Build()で返ってくる結果の配列の方にはこの情報は入っていない)。ただし、APIドキュメントでは「estimate」となっているので、確実に検出できるものではないと考えた方がいいかもしれない。いずれにせよ、総件数が多すぎるなどで負荷がかかり過ぎることは、これ以外の場合にもあり得るので、真面目に作るなら別途考えておく必要がある問題だろう。

　6行目は、PDOを使用してデータベースに接続している。ここではPostgreSQLに接続している。

　7行目は、SQL文をプリペアしている。ここで分かるように、4行目で返ってきた結果の配列には、'SQL'をキーとする要素に、WHERE句に利用できる変換後のSQLの式が文字列として格納されている。それを使ってここでSELECT文を組み立てている。前述のように、式はプレースホルダを含むので、実行するには必ずプリペアする必要がある。

　8行目は、プリペアした文に実パラメータを与えて実行している。ここで分かるように、パラメータとして与える値は、4行目で返ってきた配列の、'value'をキーとする要素に、配列の形で格納されている。もちろん、その要素として格納されている値は、3行目で指定した値の変換用コールバックで変換済みの値である。

　以上で、検索式をSQLの式として実行することができた。

　なお、本稿のサンプルコードではエラー処理を簡単のために省略しているが、検索式に不正な式を指定してBuild()を呼ぶと\InvalidArgumentExceptionが投げられるので、本番コードではキャッチする必要がある。

IBLを使用する場合

　MySQLのIBLを使用する場合の典型的コードは、次のようになる。

    require_once 'SearchWordsSQL.php';
    $searchwords = $_GET['q'];
    $sb = new \SearchWordsSQL\SQLBuilder("");
    $res = $sb->Build($searchwords);
    if ($sb->isComplement()) die;
    $db = new PDO("mysql:server=localhost");
    $st = $db->prepare("SELECT * FROM BookText WHERE MATCH (Body) AGAINST (? IN BOOLEAN MODE)");
    $st->execute(array($res['IBL']));

　基本的な流れはSQLのLIKEの場合と似ているが、SQLの式を組み立てる必要がないので、3行目ではコンストラクタを呼び出すときにSQLの式として空文字列を指定している。また、7行目ではMATCH AGAINSTの構文を使用し、IBLの式が入る部分をパラメータ化している。8行目で、結果の配列の'IBL'というキーの要素を渡している。ここにSQLの場合同様、IBLの式が文字列で格納されている。
　5行目はSQLのLIKEの場合と同じなのだが、IBLを使用する場合、ひとつ注意点がある。検索式で「-」が指定されたとき、SearchWordsSQLは、それをそのままIBLの「-」に変換するが、これらの間には微妙に意味の違いがある。検索式の「-」は「～を含まない」の意味だが、IBLの「-」は、正確に言うと、その他の検索語から生成された結果から、これらの単語を含むものを除外するという意味である。そこで、もし「-猫」とだけ指定された場合、SQLのLIKEの例では「猫」という文字列を含まないものが検索されるSQLが正しく生成されるのに対し、このIBLの例では、結果が何も返ってこないIBLが生成されることになるのである。したがって、IBLの場合、isComplement()からtrueが返されたときは、必ずエラー扱いとしなければならない。

IBLとSQL論理式を併用する場合

　MySQLを使用する場合、SQLの論理式とIBLを併用することもできる。例えば、入力された検索語を本文だけでなくISBN列に対しても完全一致で検索させたいという場合、次のように書ける。

    require_once 'SearchWordsSQL.php';
    $searchwords = $_GET['q'];
    $sb = new \SearchWordsSQL\SQLBuilder("ISBN = ?");
    $res = $sb->Build($searchwords);
    if ($sb->isComplement()) die;
    $db = new PDO("mysql:server=localhost");
    $st = $db->prepare("SELECT * FROM BookText WHERE MATCH (Body) AGAINST (? IN BOOLEAN MODE) OR ${res['SQL']}");
    $st->execute(array_merge(array(), array($res['IBL']), $res['value']));

　なお、このようなSQLでクエリプランがどうなるかについては慎重に検討することを勧める。

$res['hit']

　Build()から返される配列の内容のうち、ここまでキーが'SQL'のもの、'value'のもの、'IBL'のものについて説明したが、残りのあと一つとして、'hit'のものが存在する。ここには、ヒットする単語となり得るものの配列が格納される。ヒットする単語となり得るものというのは、検索式として指定された中にある各検索語のうち、-が付いていない単語のことである。検索結果のハイライト化処理などを実装するのに便利だろう。

テキストを正規化する

　以上で説明したコードで生成されるような単純なSQL文では、使用している全文検索ライブラリにもよるが、文字の正規化などが十分に行われないので、実用に供するには少々不便であることが多い。実用システムでは、例えば、半角と全角を同一のものとみなしたり、約物や空白類を除去したりといった正規化を行う関数をUDFとして作成し、それを利用して全文検索を行うようにするといいだろう。
　例えば、PostgreSQLでpg_bigmを使用して全文検索を行う場合、次のようなUDFとインデックスを作成しておく。


CREATE OR REPLACE FUNCTION NormalizeText(t varchar) RETURNS varchar IMMUTABLE AS $$
DECLARE
	separators text := '{}()。、!！?？（）｛｝「」『』【】〈〉＜＞＝〔〕«»〖〗[]《》〘〙｟｠⁽⁾₍₎“”‘’━';
BEGIN

	RETURN ja_normalize(
		translate(
			translate(t, 'ABCDEFGHIJKLMNOPQRSTUVWXYZＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ　 ・∀','abcdefghijklmnopqrstuvwxyzａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚ')
			, separators, repeat(' ', length(separators))
		)
	);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION NormalizeTextForLike(t varchar) RETURNS varchar IMMUTABLE AS $$
BEGIN
	t := NormalizeText(t);
	t := replace(t, '\', '\\');
	t := replace(t, '%', '\%');
	t := replace(t, '_', '\_');
	t := replace(t, '*', '%');
	return '%' || t || '%';
END;
$$ LANGUAGE plpgsql;

CREATE INDEX Book_BookText_text_idx ON Book USING gin ( NormalizeText(Body) gin_bigm_ops);

　なお、ここでja_normalizeはtsearch系日本語全文検索用モジュールであるtextsearch_jaに用意されている関数で、全角半角の正規化を行うものである。ここではこのモジュールの検索機能自体は使用しないものの、ja_normalize関数だけは使用するために、textsearch_jaもインストールしておく。
　検索を行うPHP側のコードは次のようになる。

    require_once 'SearchWordsSQL.php';
    $searchwords = $_GET['q'];
    $sb = new \SearchWordsSQL\SQLBuilder("NormalizeText(Body) LIKE NormalizeTextForLike(?)");
    $res = $sb->Build($searchwords);
    if ($sb->isComplement()) die;
    $db = new PDO("pgsql:server=localhost");
    $st = $db->prepare("SELECT * FROM BookText WHERE ${res['SQL']}");
    $st->execute($res['value']);

　一番初めに紹介したコードと3行目だけが違っている。第一引数に指定したSQL式では、正規化したBody列(のインデックス)のテキストと、同じように正規化した検索語とをマッチングしている。第二引数については、LIKEのために値を変換するのはUDF側で行うので、SQLLikeValueCallbackの指定は必要なく、省略している。

　NormalizeTextをさらに強化すれば、一層便利な検索を実現できるだろう。 

接頭辞コールバック

　先日公開されたv0.2からは、接頭辞コールバックが使用できるようになった。接頭辞コールバックは、Googleで言うところの「site:tec.jpn.ph」などのような接頭辞を指定することによる特殊検索に対応するためのコールバック関数である。SQLBuilderのコンストラクタの第4引数にキー付配列の形で指定する。
　例えば、著者名の検索のために「author:」を指定するようにしたいなら、以下のようにする。

    require_once 'SearchWordsSQL.php';
    $searchwords = $_GET['q'];
    $sb = new \SearchWordsSQL\SQLBuilder("NormalizeText(Body) LIKE NormalizeTextForLike(?)", null, null, array(
        'author:' => function ($v) {
            $a = mb_substr($v, mb_strlen('author:'));
            return array(
                'SQL'    => 'author LIKE ?',
                'value'  => \SearchWordsSQL\SQLLikeValueCallback($a)
            );
        }
    ));
    $res = $sb->Build($searchwords);
    if ($sb->isComplement()) die;
    $db = new PDO("pgsql:server=localhost");
    $st = $db->prepare("SELECT * FROM BookText WHERE ${res['SQL']}");
    $st->execute($res['value']);

　違いは3行目からのコンストラクタ呼び出しの部分だけである。前述のように、第4引数に配列を渡しており、'author:'をキーとする要素に接頭辞コールバックを指定している。「author:」から始まる単語が見つかったときにこれが呼び出される。引数にはその接頭辞自体も含めた単語が渡されてくるので、その後ろの部分を取り出しているのが5行目である。6行目以降を見るとわかるように、このコールバックでは、他のコールバックと異なり、値だけでなくその部分に相当するSQL式も含めた配列を返さなければならない。これにより、コンストラクタの第一引数に指定されたSQLに拘泥することなく、柔軟にSQLを生成することができる。

　なお、接頭辞の対象となる単語にスペースを含みたいときは、"author:村上 春樹"のようにダブルクォートでくくって指定すればよい。その際、コールバック関数に渡ってくるのは、ダブルクォートを取り去った後の文字列である。ただこのことは、今のところ、接頭辞になっている文字列をエスケープする手段がないということでもある。したがって、接頭辞の文字列を何にするかは慎重に検討すべきである。