k-holyのPHPとか諸々メモ

Webで働くk-holyがPHP(スクリプト言語)とか諸々のことをメモしていきます。ソースコードはだいたいWindowsで動かしてます。

PDOでSQLiteユーザー定義関数を使って正規表現マッチしてみる

SQLite正規表現マッチが使えるのか調べてて、公式ドキュメントの SQLite Query Language: expression http://www.sqlite.org/lang_expr.html で、LIKE, GLOB とともに REGEXP や MATCH が記載されているのを発見。
しかしながら、こんなことも書いてあります。

The REGEXP operator is a special syntax for the regexp() user function.
No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message.
If a application-defined SQL function named "regexp" is added at run-time,
that function will be called in order to implement the REGEXP operator.

http://www.sqlite.org/lang_expr.html#like より

予約語ではあっても未実装で、regexpという名前でユーザー定義関数を登録しておけばREGEXP演算子が使えるよ、ということでしょうか。

SQLite UDF」で検索すると、Cで書いてコンパイルとか色々な記事を見つけましたが、PHPでPDOの場合は PDO::sqliteCreateFunction()関数 http://jp2.php.net/manual/ja/pdo.sqlitecreatefunction.php で動的にコールバックを追加できるようです。
アプリケーションのアクセスログを題材に、ユーザー定義関数による検索を試してみました。

AccessLogクラス

いつものベタ書きコード(ながーいよ)

以下、実行結果

全件

"/articles/{記事ID}" または "/articles/{記事ID}/" へのアクセスを抽出

"/articles/search" へのアクセスを抽出

確かに、regexpという名前のユーザー定義関数でmb_ereg()を使った正規表現を実装することで、REGEXP演算子による正規表現マッチが有効になっています。

ついでに、クエリーストリングに対して特定のパラメータで抽出するためのユーザ定義関数というのも試してみました。
第1引数でカラム名、第2引数でパラメータ名、オプションの第3引数でパラメータ値を指定。1次元までの配列パラメータにも対応しています。

"/articles/search" へのアクセスのうち、"tag"パラメータを含むものを抽出

"/articles/search" へのアクセスのうち、"cat"パラメータを含むものを抽出

"/articles/search" へのアクセスのうち、"tag"パラメータに"鈴木"を含むものを抽出
"/articles/search" へのアクセスのうち、"tag"パラメータに"佐藤"を含むものを抽出

ちゃんと結果が返ってきました。
今ではURLの正規化は当たり前になってますが、可変パラメータを含むURLの検索などで威力を発揮しそうです。
あるいは、逆にURLの正規表現パターンをキーとしたマスタを作成することで、特定URLへのリクエストのみを記録したり、ページ名や表示対象データの名前などアプリケーション固有の情報を記録するような用途も考えられます。
(リクエスト毎に検索するので遅くなりますが、URLのマスタを画面からメンテナンス可能にするような要件ならばありかと…その場合、正規表現パターンのバリデーションも必要になりますが)

それにしても、SQLiteって意外と多機能で面白いですね。
サーバサイド、クライアントサイドを問わず様々な言語環境に対応していて、DropBoxなどのオンラインストレージサービスとも親和性が高いし、応用範囲は非常に広そうです。