Approach for Oracle view unit testing with Rspec

A View in Oracle and in other database systems is simply the representation of a SQL statement (select query). It provides access to a subset of columns from one or more tables.

In a rails migration, when you incorporate a view definition which represents data from multiple tables with multiple joins and multiple conditions, how will you ensure that the view is fetching the data that it is supposed to fetch? How will you test the view logic?

And here, Unit Testing plays a very important role. Today, I am going to tell you the simple approach that we can follow for unit testing the view definition with Rspec.

First, with rails convention, you will create the model class (which maps to the view name) extending ActiveRecord::Base.
You will then set up the test data as you generally set for testing other model methods. (You can use factory_girl or simple model methods)
The purpose is to unit test each and every column data of the view according to the joins and the conditions. It all depends on the view logic that you have written according to your business requirement. The view definition can include decode statements, group by/ order by clauses, oracle aggregator functions e.g. sum, max, etc., function calls from the view sql, if-else logic to name a few.

With this, you have all the unit level requirement specs ready for your view which you can validate by running your Rspecs. Simple but succinct ! Isn't it ?

Installing windows platform specific gem libxml-ruby with Bundler

I was facing some issue in installing 'libxml-ruby' gem on my windows machine with bundler. The entry in the Gemfile was :-

gem 'libxml-ruby', '1.1.3', :require => 'libxml'

This works perfect on unix machines, but fails on windows machine (which is my local development platform) during building native libraries.

The DevKit has already been set up on my machine. So I tried doing gem install first :-


So, I mentioned the platform while doing gem install and it worked as below :-

D:\>gem install libxml-ruby -v=1.1.3 --platform x86-mswin32-60
Temporarily enhancing PATH to include DevKit...
Building native extensions. This could take a while...
Successfully installed libxml-ruby-1.1.3-x86-mswin32-60
1 gem installed
Installing ri documentation for libxml-ruby-1.1.3-x86-mswin32-60...
Installing RDoc documentation for libxml-ruby-1.1.3-x86-mswin32-60...
D:\>

However, with bundle install, it gave the same error on the windows machine : ERROR: Failed to build gem native extension.
I tried specifying Gemfile :platforms option - 'mswin', 'mingw' for Windows platform, but it did not work.

Finally, the :path parameter rescued me out of the pain. With :path parameter, you can specify that a gem is located in a particular location on the file system. The ':path' option requires that the directory in question either contains a '.gemspec' for the gem, or that you specify an explicit version that bundler should use. So the bundler uses the gem from the source specified in the path and it resolved my issue with installing libxml-ruby gem with bundler on windows machine. The steps that I followed :-

(1) Download the libxml-ruby-1.1.3-x86-mswin32-60.gem gem from http://rubyforge.org/frs/?group_id=494 and do gem install
OR
gem install libxml-ruby -v=1.1.3 --platform x86-mswin32-60

(2)From app_root>gem unpack libxml-ruby-1.1.3-x86-mswin32-60

(3) Gemfile.lock :-

gem 'libxml-ruby-1.1.3-x86-mswin32-60', '1.1.3', :require => 'libxml', :path => 'vendor/gems/libxml-ruby-1.1.3-x86-mswin32-60'

(4) Now, when you execute bundle install, it will bundle the gem from the source specified in the :path -

Using libxml-ruby-1.1.3-x86-mswin32-60 (1.1.3) from source at vendor/gems/libxml-ruby-1.1.3-x86-mswin32-60

Moving on to Bundler to manage my Rails 2.3.9 app's gem dependencies

Recently I faced some gem dependency issues on one of our servers where we have a good amount of rails applications. (more Rails 2.3.x apps as compared to a couple of Rails 3.0.x apps). (This was a cruise box used by all apps to execute rspecs and generate metric_fu reports after you commit your code - Continuous Integration concept :-)

My app was using Rails 2.3.9, and due to some reason, I did not get a chance earlier to move my application to use Bundler for managing all the gem dependencies. Finally, I made it :-)

I followed 2 links :-
http://gembundler.com/
http://gouravtiwari.blogspot.com/2011/03/bundler-with-rails-222.html

Since then, I have not faced any gem dependency issues on any of the boxes.
So what are you waiting for ? If you haven't done it till date, do it now ... or you will spend (waste?) time in resolving weird gem dependency issues ... :-)

A good learning for me !

Avoid oracle sequence during ActiveRecord model record insert

I was writing rspecs for one ActiveRecord model in my project and was connecting to different schema in which the corresponding mapping table was defined. The schema object (tables) definition was a legacy code, what I meant to say is I used the already existing object definition from that schema which was not using oracle sequence. There was no primary key for that table as well.

I was setting up the data for that table in my rspec using ActiveRecord insert.

e.g. AnotherSchema::TableObject.create(:ts => 123, :proj_code => "ABC", ...)

But, in Rails, when you try to insert the record with ActiveRecord Model, it always looks for a sequence, either a default one (Table_Object_seq) or explicitly defined one with set_sequence_name in the Model class.

So, it threw error as expected :-
OCIError: ORA-02289: sequence does not exist: select "Table_Object_seq
".nextval id from dual

I could have defined a sequence on this table only in the test database, but I googled around for a way to avoid oracle sequence in a model and I got this link :-
http://www.dixis.com/?p=127

I followed the suggestion and it worked for me :-)

What I did was :-

Monkeypatching OracleEnhanced Adapter's next_sequence_value method :-


and adding set_sequence_name to the Model class :-

set_sequence_name 'autogenerated'

It avoided the default oracle sequence look up while inserting new records with Rails ActiveRecord methods which solved my problem.