SQLite3におけるREGEXP演算子

sqlite database

ちょっとした用で、SQLite3(via ODBC) を始めてみた。その用件で必要なのは項目に対する正規表現マッチング。LIKE 演算子で済ませなくもないけど、やっぱり楽したい。
SQL における正規表現マッチングに付いて調べてみると、 SQL99 にて、SIMILAR TO 演算子による正規表現のサポートがあるとのこと。だけど、SQLite は SQL92 ベース、そんなもの実装されているはずがない。標準に頼るのは諦めて各 DBMS について見ていくと、Oracle では REGEXP_LIKE 関数、SQL Server では SQL CLR によるサポート、MySQL では REGEXP 演算子、PostgreSQL では ~演算子・・・とまちまちなことが分かった。
では、SQLite は・・・というと、MySQL と同じ REGEXP 演算子が備わっていた・・・が、標準の構成だとこの演算子は機能してくれない。このことについては、公式サイトで次のように説明されている。

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 user-defined 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

つまり、REGEXP 演算子を使いたければ、ユーザ側で正規表現にマッチするか確認する関数を、SQLite に "regexp" という名前のユーザ関数として登録しなければならない。なんという丸投げ・・・。
SQLite では、load_extension 関数を用いて、外部の拡張モジュールをロードすることが出来る。拡張モジュールは、いわばユーザ関数ライブラリで、SQLite3 ODBC Driver には標準で BLOB二次元マッピング拡張(sqlite3_mod_blobtoxy.dll)、外部データ取込・出力拡張(sqlite3_mod_impexp.dll)、全文検索拡張(sqlite3_mod_fts3.dll) が付属している。これらと同様にして、正規表現マッチングを行う regexp ユーザ関数を持つ拡張モジュールを制作し、ロードすれば、お目当ての REGEXP 演算子が使えるわけだ。
付属している拡張モジュールのソースコードを参考にしながら、boost/regexを使ってやっつけてみた。

// for Visual C++
#include <boost/regex.hpp>
#include <sqlite3ext.h>
extern "C" {
	SQLITE_EXTENSION_INIT1
	static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
		if (argc >= 2) {
			const char *target  = (const char *)sqlite3_value_text(argv[1]);
			const char *pattern = (const char *)sqlite3_value_text(argv[0]);
			try {
				boost::regex ereg(pattern, boost::regex_constants::perl);
				sqlite3_result_int(context, boost::regex_search(target, ereg));
			} catch (boost::regex_error &e) {
				sqlite3_result_error(context, e.what(), 0);
			}
		}
	}
	__declspec(dllexport) int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
		SQLITE_EXTENSION_INIT2(api);
		return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8, (void*)db, regexp_func, NULL, NULL);
	}
}

boost/regex の char 特殊化版が UTF-8 を扱えたかどうかは微妙だけど当面は ASCII-7bit の範囲でしか使わないので、このままにしておくことに。
ビルドして試しに使ってみると、おお、動いた動いた。

' ---- RegExpTest.vbs ---
' Build the path to the Database file
DBPath = Replace(WScript.ScriptFullName, WScript.ScriptName, "RegExpTest.db")
' Open the connection to test DB
Set DB = CreateObject("ADODB.Connection")
DB.Open "DRIVER=SQLite3 ODBC Driver;Database=" & DBPath & ";"
' Load RegExp extension module
DB.Execute("SELECT load_extension('sqlite3_mod_regexp.dll');")
' Prepare the test data
DB.Execute "CREATE TABLE aliases (pattern, alias);"
DB.Execute "INSERT INTO aliases VALUES ('^anon_\d', 'My alias');"
' Test REGEXP operator
Set RS = DB.Execute("SELECT alias FROM aliases WHERE 'anon_193@localhost' REGEXP pattern;")
WScript.Echo RS.GetString
' Close objects
RS.Close
Set RS = Nothing
DB.Close
Set DB = Nothing

>cscript //NOLOGO RegExpTest.vbs
My alias

>

パフォーマンスはどんな感じなのか、ちょっと気になるなぁ・・・。だけど、いちいち計るのに時間掛けたくないので、これで fix にしておこう。