脳log[20060401] create_function()が強力。俺の探してたのはこれだ。

♪ [Ruby][SQLite] create_function()が強力。俺の探してたのはこれだ。

SQLite3の提供する集約関数は avg, count, max, min, sum, totalが全てで、どれも数値を引数にとる。sumが与えられた数値の合計を返すように、与えられた文字列を全て連結して返す集約関数(MySQLの GROUP_CONCATがまさしくそれ)がないものかと探していた。それが無いなら無いで SELECT, JOIN, UNION, CASEといった標準的なものを使って、特定の列の値を(行をまたいで)連結したりできないものかと考えたけど、行と行の独立は破れなかった。

話は変わって、これ↓は昨日見つけた目から鱗ものの文書。

http://www.geocities.jp/mickindex/database/idx_database.html

対象読者として次のような項目が挙げられている。

• なぜ"関係"モデルという抽象的な用語を使うのか分からない。"表"モデルでいいじゃない。
• なぜ「= NULL」ではなく「IS NULL」と書かねばならないのか分からない。
• E.F.Codd、C.J.Date、J.Celko、F.Pascal の本を読んだことがない。
• IN述語とEXISTS述語では、IN の方が直観的に分かりやすいから好きだ。
• IN述語、EXISTS述語、LIKE述語・・・「述語」って何だ？
• テーブル設計のときは滅多に NOT NULL制約を付けない。しかもそれが大罪であることの自覚がない。
• SQL で CASE式を使ったことがない。

「INは使ったことあるけど EXISTSは知らない」「何故 = NULLと書いたものが IS NULLと同じ結果を返さないのか分からない」「 CASE？そんなんあった？」「DEFAULT '' は多用するけど、NOT NULLは付けてない。付けるべき理由があるなら知りたいよ」と、冒頭からがっちりハートを鷲掴み。

このサイトの文書がどれも興味深く、有用なのはさておいて、CASEの存在を今まで知らなかったことに少なからずショックを受けた。SQLiteの本家サイトにもちゃんと記述されているというのに。(→Query Language Understood by SQLite: expression)

そこで改めて SQLiteでできることをおさらいしてみたところ発見されたのが create_function。(→C/C++ Interface For SQLite Version 3)

C/C++ APIだから Rubyから使うには dl を使わなあかんのかと思ったら、何と SQLiteといつもセットで利用しているsqlite3-rubyの SQLite3::Databaseオブジェクトにはその名も create_aggregateなんてメソッドが存在するのですねえ。一体今までどこに目を付けていたのかと……。

そんなわけで、無いなら作ってしまえ文字列連結集約関数〜♪。

require 'sqlite3'
database = SQLite3::Database.new('hoge.db');

# concatという不定数の引数をとる集約関数を作成。
# 第一引数(val)は連結される文字列。
# 第二引数(sep)は valと valの間に挿入されるセパレータ。
# 第三引数(sortval)は valを連結する前に並び替えたい場合にソートキーとして利用される文字列/数値。(省略されたり NULLの場合はソートしない)
# 第四引数(desc)はソートの昇順/降順を切り替える。(省略/NULL=>昇順, その他=>降順)
database.create_aggregate(name='concat', arity=-1){
	step {|func, val, sep, sortval, desc|
		func['separator'] = sep.to_s;
		func['sortdesc'] = !(desc.nil? || desc.null?);
		func['needsort'] = (func['needsort'] || !(sortval.nil? || sortval.null?));
		func['sortvaltype'] ||= (sortval.nil? || sortval.null?) ? nil : {:int=>:to_i, :float=>:to_f, :blob=>:to_blob, :text=>:to_s}[sortval.type];
		func['array'] ||= [];
		func['array'].push([ (sortval.nil? || sortval.null?) ? nil : sortval.send(func['sortvaltype']), val.to_s ]);
	}
	finalize {|func|
		arr = (func['array'] || []);
		arr = arr.sort_by{|x| x.nil? ? {:to_i=>0, :to_f=>0.0, :to_s=>''}[func['sortvaltype']] : x.first.dup } if(func['needsort']);
		arr.reverse! if(func['sortdesc']);
		func.result = arr.map{|x| x.last  }.join(func['separator']);
	}
}

• val, sepなど集約関数の引数は SQLite3::Valueのインスタンス。SQLite3::Value#to_sは nilを返すこともあるので注意。
• val, sepなど集約関数の引数はコールバックの度に上書きされるので、引数をそのまま func(SQLite3::Database::FunctionProxy)など、ブロックローカル変数以外の場所に保存すると ( ゜Д゜)マズー
• コールバック用の step, finalizeを create_aggregateに渡す方法は Rubyの新文法ではなく sqlite3-rubyがコールバック関数を定義しやすいようにしてくれているだけ。
• dupがないとソートした後で例外が発生する理由は？
• スピードを考えたら(頻繁に使うクエリで)こんなの使っちゃダメ。

というわけで、探し物が見つかったということに満足しつつ、文字列の連結は集約関数でなく ERBスクリプトで行っている現在。

 authorizerを使えば

データベースに対するリードオンリーアクセスを保証したりもできるのねん。

CGI経由で渡された SQLも安全に実行できそうじゃね？

#!ruby -T4
eval(ENV['QUERY_STRING'])

と同程度かそれ以上に。

http://www.sqlite.org/capi3ref.html#sqlite3_set_authorizer から引用しておく。

The intent of this routine is to allow applications to safely execute user-entered SQL. An appropriate callback can deny the user-entered SQL access certain operations (ex: anything that changes the database) or to deny access to certain tables or columns within the database.