LoginSignup
11
12

More than 5 years have passed since last update.

@pocariinしくみ製作所株式会社

rspecでpl/sqlのテストをする

Last updated at Posted at 2014-03-12

背景

　plsqlのprocedureやfunctionを大量に書く機会があって、その時にどうせならきちんとテスト出来る状態をつくりたいなと思って色々探してみたところ、plsql　unitなるものもあるようですが、テストコードでまでpl/sqlを書きたくないと思い、rspecでできないか色々調べてみたところ、結構いい感じにテストできることがわかったのでそのメモ。

やりたいこと

  • pl/sqlのprocedure、functionをテストしたい
  • テスト結果の検証やテストデータの事前準備などでActiveRecordを使って楽したい

ゴール

こんな風にかけると楽だなーというのを目指して作りました。

describe 'pl/sql test' do
  describe 'hoge_function' do
    it '引数を2倍した値を返すこと' do
      expect(hoge_function(5)).to eq 10
    end
  end
  describe 'foo_procedure' do
    let(:tab1) { Table1.find("key_of_table1") }
    it 'ABLE1.fieldをTABLE2.fieldにupdateすること' do
      foo_procedure()
      expect(Table2.find("key_of_table2").field).to eq tab1.field
      #テスト後にはロールバックされる
    end
  end
end

使用するgem

  • ruby-plsql

plsqlのコードをあたかもrubyのコードのように書けるruby-plsqlというgemがあるのでこれを利用します。

  • ruby-oci8

rubyからoracleに接続するためにruby-oci8も利用します。

  • active_record

これは説明不要ですね。

  • Oracle enhanced adapter for ActiveRecord

active_recordからoracleにアクセスするときに上のruby-oci8を拡張したらしいこちらのadapterが必要なそうなのでコレも使います。

インストール

ruby-plsql, active_record、Oracle enhanced adapter for ActiveRecordは普通にgem installするだけですが、ruby-oci8は公式サイトにあるバイナリパッケージの入れ方にあるように一旦バイナリパッケージをダウンロードしてからインストールしました。

その他の事前準備

Oracleは無償で利用できる、Oracle Database Express Edition 11g Release 2を使って試しています。
ここでちょっとハマったのが、Windows版のこの無償oracleは32bit版しか用意されておらず、手元のWindowsが64bit版、rubyも64bit版だったせいなのかOCI8で接続できませんでした。

linux版は幸い64bitのがあるのでvmwareにCentOSを入れてそこにインストールして回避しています。

ruby-plsql単体でのサンプル

#coding: Windows-31J

require 'ruby-plsql'

#ruby-plsqlをrequireすると自動的に「plsql」というオブジェクトが定義されるので
#それのconnectionにOCI8のconnectionを設定する
#接続文字列の書式はいろいろありますが、ここでは簡易接続でvmwareで動いているCentOSのIPアドレスとポートサービス名をしてしいます。ここは環境次第で書き換えます
plsql.connection = OCI8.new('scott', 'tiger', '192.168.152.129:1521/xe')

#普通にsqlも発行できる
p plsql.select('select * from dual')
#=>[{:dummy=>"X"}]

#sql関数もplsqlのメソッドのように実行できる
p plsql.trim('  hoge  ')
#=>"hoge"

#executeで任意のsqlも実行可能
plsql.execute(<<EOS)
create or replace function hoge_func (
  arg in number
) return varchar2 is
begin
  return 'argは' || arg || 'です';
end;

EOS

#上で定義したhoge_funcがplsqlのメソッドとして定義され
#ruby側での8がOracleのNumber型、戻り値のVARCHAR2がrubyのStringとしてマッピングされ
#通常の関数呼び出しのように実行することができる。
puts plsql.hoge_func(8)
#=>argは8です

plsql.execute('drop function hoge_func')

plsql.execute(<<EOS)
create or replace procedure hoge_proc (
  arg in number,
  ret_1 out number,
  ret_2 out number
) is
begin
  ret_1 := arg * 2;
  ret_2 := arg * 3;
end;
EOS

#なんとoutパラメータまで戻り値にしてくれます。
#outパラメータを自動で認識してruby側から呼び出すときは
#inパラメータだけで呼び出せて、outパラメータ名をkeyとするHashで
#戻り値を返してくれます。
p plsql.hoge_proc(3)
#=>{:ret_1=>6, :ret_2=>9}

plsql.execute('drop procedure hoge_proc')

うん。使いやすい。これとactive_recordを連携してrspecを実行できるようにします。

ActiveRecordを単独で使う

railsのrspecでActiveRecordを使う例はいろいろwebで見れるんですが、ActiveRecord単体で使っている例は少ないのでそれもメモ。

ActiveRecordを単体で使う場合自分でコネクションを貼る必要があります。railsのdatabase.ymlに書く内容を手動でActiveRecord::Base.establish_connectionに渡してやります。

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter:  'oracle_enhanced',
  host:     '192.168.152.129',
  username: 'scott',
  password: 'tiger',
  database: 'xe'
)

class Dual < ActiveRecord::Base
  self.table_name = "dual"
end

p Dual.all
#=>#<ActiveRecord::Relation [#<Dual dummy: "X">]>

で、もう一点、今回はrailsは全然関係無いシステムなので、複合主キーのテーブルだらけです。
そこで、ActiveRecordで複合主キーを使えるようにするcomposite_primary_keysというgemも入れておきます。

すると下記のような複合主キーのテーブルもActiveRecordで扱えるようになります。

SQL> create table table1 (
  2    key1 number,
  3    key2 number,
  4    value1 varchar2(10),
  5    CONSTRAINT pk_table1 PRIMARY KEY(key1, key2)
  6  );

表が作成されました。

SQL> insert into table1 values (0, 0, 'hoge');

1行が作成されました。

SQL> commit;

コミットが完了しました。

このTABLE1テーブルがある前提で

require 'active_record'
require 'composite_primary_keys'

ActiveRecord::Base.establish_connection(
  #～略～
)

class Table1 < ActiveRecord::Base
  self.table_name = "table1"
  self.primary_keys = :key1, :key2 #primary_keysにキーカラムのシンボルを渡す
end

rec = Table1.find(0, 0)
p [rec.key1.to_i, rec.key2.to_i, rec.value1]
#=>[0, 0, "hoge"]

とActiveRecordから使用できるようになります。

ruby-plsqlとActiveRecordの連携

コネクション自体はAcitveRecordで貼るのでそのコネクションをruby-plsqlに渡すのですが、ruby-plsqlで定義されるplsqlにactiverecord_classというメソッドが定義されていて、下記のようにするとActiveRecordのコネクションを共有できます。

plsql.activerecord_class = ActiveRecord::Base

これでrspecでActiveRecordとruby-plsqlを使う準備が出来ました。

Rspec側の準備

基本的に各テストケースでテストを実行した後はロールバックして欲しいので、aroundでテストケースをトランザクションで包みます。下記の用になります。

describe 'test' do
  #aroundで各テストケース（ex)の実行をtransactionブロックで包みます。
  around do |ex|
    ActiveRecord::Base.transaction do
      plsql.activerecord_class = ActiveRecord::Base
      ex.run
      raise ActiveRecord::Rollback
    end
  end

  it 'any case' do
    #test 実装
  end
end

ただこれを各specファイルに毎回書くorコピペするのはなかなかタルい作業なので、helperなどで、shared_contextとして定義しておき、各specファイルでそれをinclude_contextして使うと便利です。
まず、ActiveRecordのトランザクション管理用のユーティリティを作ります。

active_record_helper.rb
require 'active_record'

class TransactionHelper
  def initialize(config)
    @config = config
    ActiveRecord::Base.establish_connection(@config)
  end

  def with_transaction
     ActiveRecord::Base.connection_pool.with_connection do
      ActiveRecord::Base.transaction do
        yield
      end
    end
  end

  def with_rollback_transaction
    with_transaction do
      begin
        yield
        raise ActiveRecord::Rollback
      end
    end
  end
end

次にこのユーティリティを使ってshared_contextをspec_helperに定義します。

spec_helper.rb
require 'ruby-plsql'

require_relative 'active_record_helper'

shared_context :each_example_with_rollback_transaction do
  around do |example|
    helper = TransactionHelper.new(
      adapter:  'oracle_enhanced',
      host:     '192.168.152.129',
      username: 'scott',
      password: 'tiger',
      database: 'xe'
    )
    helper.with_rollback_transaction do
      plsql.activerecord_class = ActiveRecord::Base
      example.run
    end
  end
end

また今回ActiveRecordのモデルはplsqlのテスト用の便利クラス扱いなので、
モデルに特にロジックもなく、テストで使うテーブル達を定義するだけにしています。

#coding: Windows-31J

require 'composite_primary_keys'

#Model定義用のユーティリティ
def define_model(table_name, keys)
  model_class = Class.new(ActiveRecord::Base)
  model_class.module_eval do
    self.table_name = table_name.downcase.to_s
    if keys.size == 1
      self.primary_key = keys[0]
    else
      self.primary_keys = *keys
    end
  end
  Object.const_set(table_name.to_s, model_class)
end

define_model :Table1, [:key1, :key2]

これもspec_helperでrequireしておきます。

spec_helper.rb
require 'ruby-plsql'

require_relative 'active_record_helper'
require_relative 'models' #追加

#以下省略

これでテストを書けるようになりました。

テストケース実装

まずテストケースを書いてみます。

spec.rb
#coding: Windows-31J

require_relative 'spec_helper'

describe 'pl/sql test' do
  include_context :each_example_with_rollback_transaction

  describe 'hoge_function' do
    it '引数を2倍した値を返すこと' do
      expect(plsql.hoge_function(5)).to eq 10
    end
  end
  describe 'foo_procedure' do
    before do
      target = Table1.new
      target.key1 = 1
      target.key2 = 2
      target.value1 = 'hoge'

      target.save
    end

    it 'Table1のvalue1をpiyoにすること' do
      plsql.foo_procedure
      expect(Table1.find(1, 2).value1).to eq "piyo"
    end
  end
end

spec実行

rspec spec.rb

当然全部落ちます

FF

Failures:

  1) pl/sql test hoge_function 引数を2倍した値を返すこと
     Failure/Error: expect(plsql.hoge_function(5)).to eq 10
     ArgumentError:
       No database object 'HOGE_FUNCTION' found
     # ./spec.rb:10:in `block (3 levels) in <top (required)>'
     #～スタックトレースは省略～

  2) pl/sql test foo_procedure Table1のvalueをpiyoにすること
     Failure/Error: plsql.foo_procedure
     ArgumentError:
       No database object 'FOO_PROCEDURE' found
     # ./spec.rb:25:in `block (3 levels) in <top (required)>'
     #～スタックトレースは省略～

Finished in 2.59 seconds
2 examples, 2 failures

Failed examples:

rspec ./spec.rb:9 # pl/sql test hoge_function 引数を2倍した値を返すこと
rspec ./spec.rb:24 # pl/sql test foo_procedure Table1のvalueをpiyoにすること

hoge_functionもfoo_procedureも作ってないので、No database object 'FOO_PROCEDURE' foundとでてます。

次にそれぞれ実装してみます。

SQL> create or replace function hoge_function (
  2    arg in number
  3  ) return number is
  4  begin
  5    return arg * 2;
  6  end;
  7  /

ファンクションが作成されました。

SQL>
SQL> create or replace procedure foo_procedure
  2  is
  3  begin
  4    update TABLE1
  5    set
  6      value1 = 'piyo'
  7    where
  8      key1 = 1 and
  9      key2 = 2
 10    ;
 11  end;
 12  /

プロシージャが作成されました。

spec再実行

..

Finished in 2.63 seconds
2 examples, 0 failures

すべてテストが通りました。
ロールバックされていることを確認するために、もう一度実行してみます。
（Table1にinsertした内容が残っていたらエラーになる）

..

Finished in 2.65 seconds
2 examples, 0 failures

問題なくロールバックされているようです。

上記の内容でもうちょっとモジュール化した内容をgitにあげているので、リンクをつけておきます。
https://github.com/pocari/plsql_rspec

11
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
12