読者です 読者をやめる 読者になる 読者になる

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

Ruby Sequel MS Access

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

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

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

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

 

環境

 
また、テーブル日本語サンプルと選択クエリ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:他のデータベースで言うところのビュー