Productize and officially support the group_concat() SQL function.
試してみる。
>sqlite3 SQLite version 3.5.4 Enter ".help" for instructions sqlite> create table t1(c1); sqlite> insert into t1 values(1); sqlite> insert into t1 values(2); sqlite> insert into t1 values(3); sqlite> select * from t1; 1 2 3 sqlite> select group_concat(c1) from t1; 1,2,3 sqlite> select group_concat(c1+1) from t1; 2,3,4 sqlite> select group_concat(c1||0) from t1; 10,20,30 sqlite> select group_concat(c1, "-") from t1; 1-2-3 sqlite> select group_concat(c1, c1) from t1; 12233
期待通り。
もう create_function()で自作して Segmentation faultに困らされたりすることもなくなるね。(sqlite3-rubyの話)
もっとも sqlite3-rubyは一年以上のブランクを経て今年の二月に新バージョンが出てるので Segmentation faultは出なくなってると思う。下の変更点が多分そう。
2007-01-13 11:42 jamis
* Fix for use of callbacks (busy_handler, set_authorize and trace) (thanks Sylvain Joyeux, closes #2955)http://rubyforge.org/frs/shownotes.php?group_id=254&release_id=9438
二年近くの長い沈黙を破って 2007-01-13にリリースされていた。
29日に Ruby1.9をダウンロードした。添付ライブラリのRipperが、m4sugar.m4が見つからない、というエラーでコンパイルできない以外は問題なくインストール完了。RUBY_PLATFORMは i386-bccwin32。
Ruby1.8.5で動いている http://vvvvvv.sakura.ne.jp/ds14050/buch/ が Ruby1.9でも動くのか試してみると、sqlite3-rubyの Nativeドライバ、DLドライバが両方とも動かない。Nativeは当然として、DLが動かないのは Ruby1.9では ruby-dl2が dlとして添付されているから。
出てくるエラーを順番に潰す過程でやったことは定数名の置き換えが殆ど。そんなことしかできません。コールバック関数を SQLiteに渡す authorizerや create_function関連は自分で使っていないので何もしていない。DL.callbackが存在しない為にエラーが出るのは間違いない。dl2では bindを使うのだろうか？
以下は変更点のリスト
* sqlite3/driver/dl/api.rb:38 -extend ::DL::Importable +extend ::DL::Importer
単なる名称変更。
* sqlite3/driver/dl/api.rb:92 -extern "ibool sqlite3_complete(const char*)" +extern "int sqlite3_complete(const char*)" * sqlite3/driver/dl/api.rb:93 -extern "ibool sqlite3_complete16(const void*)" +extern "int sqlite3_complete16(const void*)" * sqlite3/driver/dl/driver.rb:96 -API.send( utf16 ? :sqlite3_complete16 : :sqlite3_complete, sql+"\0" ) +API.send( utf16 ? :sqlite3_complete16 : :sqlite3_complete, sql+"\0" ) != 0
iboolという返り値(戻り値?)の型が dl2では定義されていない(定義しようがない?)ので、intを bool値として受け取るのは諦めて、返り値を利用するドライバの方で非0かどうか調べる。
* sqlite3/driver/dl/driver.rb:40
-DL.sizeof("L")
+DL::SIZEOF_LONG
sizeofというメソッドは dl2の DL::Importerモジュールにもあるが使い方がわからないし、定数の方が良い。
* sqlite3/driver/dl/driver.rb:* -DL::PtrData +DL::CPtr * sqlite3/driver/dl/driver.rb:242,247,252 -result ? result.to_s : nil +result.null? ? nil : result.to_s
DL::CPtrが DL::PtrDataと完全に互換な置き換えなのかわからないが当面のエラーは消えた。
DLL関数の返値がポインタの場合は常に CPtrが返ってくるらしく、CPtrの指すアドレスが NULLの場合でも Ruby的には nilではないので「result ? result.to_s : nil」が常に result.to_sになり、ぬるぽエラーになることがある。PtrDataとは振る舞いが違う？
* lib/ruby/1.9/dl/value.rb:72
-return [arg].pack("p").unpack("l!")[0]
+return [arg.dup].pack("p").unpack("l!")[0]
* lib/ruby/1.9/dl/value.rb:74
-return [arg].pack("p").unpack("q")[0]
+return [arg.dup].pack("p").unpack("q")[0]
frozenオブジェクトを変更しようとした、ってエラーがでるので間に合わせで Rubyの添付ライブラリの方を修整。どこから frozenオブジェクトが渡されたのやら。
swigが sqlite3_api.iを基に出力する sqlite3_api_wrap.cを以下のように置換したら自分が使用している範囲では動いている。
-RSTRING()->ptr +RSTRING_PTR() -RSTRING()->len +RSTRING_LEN()
知らない。知らないよこんな便利なコマンド。
EXPLAIN sql-statement;
ならドキュメントに載ってるけど、これが返すのは
0|Integer|5|0| 1|MustBeInt|0|0| 2|MemStore|0|0| 3|IfMemZero|0|25| 4|IfMemPos|0|8| 5|Pop|1|0| ……
みたいな SQLがコンパイルされた結果の、VMが逐一実行する命令のリストだから腰を据えないと解読できない。
それに対して
EXPLAIN QUERY PLAN sql-statement;
が返すのは
0|0|TABLE MyBooks USING PRIMARY KEY
みたいな、テーブルに対する問い合わせのリスト。インデックスが使われるのかどうかもわかる。
Segmentation faultが起こったり起こらなかったり、起こったとしても(特定のパターンはあるにせよ)違う場所だったりとはっきりしないエラーに困らされた。
原因が create_aggregateで独自に定義した集約関数を使ってるからだということはわかってる。RubyForgeに関連のありそうな投稿を見つけた。
原因はリファレンスが切れて GCに回収されてしまったオブジェクトを参照しようとしてることにある、ということで良いか？ 何ともヘタレな回避策は↓。
GC.disable; db.execute(sql); GC.enable
sqlite3-rubyはもうメンテされないのかね。名前付きプレースホルダの問題も解決されないし。
sql = 'SELECT * FROM Books WHERE Title = :title;'
db.execute(sql, {'title'=>'惑星をつぐ者'}) #=> no such bind parameter 'title' とかなんとか
db.execute(sql, {':title'=>'星を継ぐもの'}) #=>(゜Д゜ )ウマー
bind_parameterのキーに普通はコロンを付けたりしないよね、多分。
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']);
}
}
というわけで、探し物が見つかったということに満足しつつ、文字列の連結は集約関数でなく ERBスクリプトで行っている現在。
データベースに対するリードオンリーアクセスを保証したりもできるのねん。
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.