/ 最近 .rdf 追記 設定 本棚

log[SQLite: 2007-12-16]



20071216()

[SQLite] 3.5.4リリースcritical bug fixより group_concat()に目がいく

Productize and officially support the group_concat() SQL function.

http://www.sqlite.org/releaselog/3_5_4.html

試してみる

>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


20070120()

[Ruby][SQLite] sqlite3-ruby-1.2.0リリース

二年近くの長い沈黙を破って 2007-01-13にリリースされていた


20061030()

[Ruby][SQLite] sqlite3-ruby-1.1.0DLドライバを Ruby-1.9.0(20061029)添付の dl2*不完全*対応

29日に Ruby1.9をダウンロドした添付ライブラリのRipperm4sugar.m4が見つからないというエラーでコンパイルできない以外は問題なくイール完了RUBY_PLATFORMi386-bccwin32

Ruby1.8.5で動いている http://vvvvvv.sakura.ne.jp/ds14050/buch/Ruby1.9でも動くのか試してみるとsqlite3-rubyNativeドライバDLドライバが両方とも動かないNativeは当然としてDLが動かないのは Ruby1.9では ruby-dl2dlとして添付されているから

出てくるエラーを順番に潰す過程でやったことは定数名の置き換えが殆どそんなことしかできませんールバック関数を SQLiteに渡す authorizercreate_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では定義されていない(定義しようがない?)のでintbool値として受け取るのは諦めて返り値を利用すドライバの方で非0かどうか調べる

* sqlite3/driver/dl/driver.rb:40
   -DL.sizeof("L")
   +DL::SIZEOF_LONG

sizeofというメソドは dl2DL::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::CPtrDL::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オブジトが渡されたのやら

 追記(2006-11-01)Nativeドライバ

swigsqlite3_api.iを基に出力する sqlite3_api_wrap.cを以下のように置換したら自分が使用している範囲では動いている

-RSTRING()->ptr
+RSTRING_PTR()
-RSTRING()->len
+RSTRING_LEN()

20060702()

[SQLite] EXPLAIN QUERY PLAN sql-statement;

知らない知らないよこんな便利なコマ

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

みたいなーブルに対する問い合わせのリインデックスが使われるのかどうかもわかる

EXPLAIN QUERY PLANを発見
http://www.sqlite.org/cvstrac/tktview?tn=1878 (SQLite CVSTrac)
SQLite 3.2.6から追加された実験的なコマドだって
http://www.sqlite.org/cvstrac/wiki?p=QueryPlans (SQLite CVSTrac)

20060608()

[Ruby][SQLite] sqlite3-rubyのバグSQLite3::Database#create_aggregate()などに注意

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のキーに普通はコロンを付けたりしないよね多分


20060401()

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

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

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

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

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

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

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-rubySQLite3::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_snilを返すこともあるので注意
  • val, sepなど集約関数の引数はコールバックの度に上書きされるので引数をそのまま func(SQLite3::Database::FunctionProxy)などブロックローカル変数以外の場所に保存すると ( ゜Д゜)マズ
  • ールバック用の step, finalizecreate_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.