Ruby + Sequelを使って、MS Accessの選択クエリに対してSQL文(CRUD)を実行する

前回は日本語テーブル名やカラム名を持つMS Accessから、Sequelのエイリアスを使ってデータを取得しました。

ただ、毎回エイリアスを定義するのは手間なので、

• MS Accessにて、日本語カラム名に対してAS句(日本語→シングルバイト)を使用した選択クエリ*1を作成
• その選択クエリに対して、SequelでSQL文(CRUD)を実行

ができないかを試してみました。

環境

• Windows7 x64
• MS Access 2010 (accdb)
• Ruby Installer 2.0.0-p481
• Sequel 4.12.0
  • ADOを使ってMS Accessへ接続

　
また、テーブル日本語ｻﾝﾌﾟﾙと選択クエリAccdbQueryの対応は以下の通りです。

テーブル.項目名	データ型	選択クエリ名.項目名
日本語ｻﾝﾌﾟﾙ.ID	オートナンバー	AccdbQuery.ID
日本語ｻﾝﾌﾟﾙ.名前	テキスト	AccdbQuery.Name
日本語ｻﾝﾌﾟﾙ.ｺﾒﾝﾄ	テキスト	AccdbQuery.Comment

　
なお、選択クエリのSQLビューは以下の通りです。

SELECT ID, 名前 AS Name, [ｺﾒﾝﾄ] AS Comment
FROM 日本語ｻﾝﾌﾟﾙ;

検証

SELECT, INSERT, UPDATE, DELETE を試してみましたが、@@IDENTITYも含め、いずれも動作しました。

SELECT

ds = db[:AccdbQuery]
r = ds.select(:Name, :Comment).where(ID: :$n).prepare(:first).call(n: 1)
p r             #=> {:Name=>"ほげ", :Comment=>"ﾎｹﾞ"}
p r[:Name]      #=> "ほげ"
p r[:Comment]   #=> "ﾎｹﾞ"
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]

INSERT

@@IDENTITYの動作を確認するため、事前にINSERT&DELETEを行っておきます。

2.times { |i| ds.insert(Name: i.to_s) }
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>3, :Name=>"0", :Comment=>nil}, {:ID=>4, :Name=>"1", :Comment=>nil}]

ds.where{id > 2}.delete
# ds.where(Sequel.expr(:ID) > 2).delete   # これでもOK

p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]

その後、INSERTを実行してみます。

ds.insert(Name: 'Piyo', Comment: 'insert')

# @@IDENTITYを使って、INSERT時に採番されたオートナンバー型のID列を取得する
id = db['SELECT @@IDENTITY as ID From AccdbQuery'].first[:ID]
puts "\n@@IDENTITY: #{id}"  #=> @@IDENTITY: 5

p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>5, :Name=>"Piyo", :Comment=>"insert"}]

UPDATE

ds.where(ID: id).update(Name: 'ぴよ', Comment: 'Update')

p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>5, :Name=>"ぴよ", :Comment=>"Update"}]

DELETE

ds.where(ID: id).delete

p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]

検証時のコード

Gistに上げました。
Ruby + Sequelを使って、MS Accessの選択クエリからデータを取得する

データベースファイルは、SinatraToMSAccess-SampleのSample.accdb(選択クエリを追加済)を利用しました。
thinkAmi/SinatraToMSAccess-Sample · GitHub

*1:他のデータベースで言うところのビュー