In my last post on this subject, I outlined some of the options available when unit testing PLSQL, and discussed why I decided to do my testing with Ruby instead of using one of the custom PLSQL tools. In this post, I am going to explore in more detail how to get up an running with some basic PLSQL testing.

Plain Old Test::Unit

Ruby has countless testing tools available, from the old default Test::Unit to BDD test frameworks like Cucumber, RSpec and probably quite a few I have never heard of.

I have always been happy with Test::Unit as it is simple and easily extended. Tests are created by asserting that values returned from the code under test meet some defined criteria in the test. The details are best explained using examples.

A Simple Test Class

To create a test class, you need to create a class which sub classes Test::Unit::TestCase. It must have a setup and teardown method followed by any number of methods that start with test_. These are the methods that make up the unit tests that will be executed, eg:

require 'test/unit'

class ExampleTest < Test::Unit::TestCase

  def setup
  end

  def teardown
  end

  def test_it_is_working
    assert(1)
  end
end

This test suite contains a single test, with only 1 assertion, asserting that 1 is true. Not very useful, but it allows us to test the setup is good. To run the test, save it in a file and run it with Ruby (or JRuby):

$ jruby example_test.rb
Loaded suite example_test
Started
.
Finished in 0.007 seconds.

1 tests, 1 assertions, 0 failures, 0 errors

Testing Database Code

To make this test suite more useful, we need to get it connected to the database. There is a native Ruby driver known as OCI8, but it has some limitations, and can be challenging to get to work on some platforms, so I decided to use JDBC, which mandates the use of JRuby.

As soon as you starting trying to make JDBC calls to the database, you will quickly find yourself writing a lot of repetitive code, making the tests overly verbose and not very Ruby like. To get around that, a small abstraction layer is required, called SimpleOracleJDBC.

You won't write too many tests before you find a need to stage data in tables to setup tests - you could just insert this data with SQL, but once again it becomes very verbose - the Data Factory gem can help with this.

I have already shown a simple example of the test framework built into Ruby, but it is missing a few features that would be useful for database testing. They can be added with the PLSQLUnitTest gem.

Setup

Install JRuby

To get started, first you need to install JRuby. MRI Ruby will not work for the upcoming examples as I use JDBC to connect to the database. Get the version for your platform from the download page. As I write this, the current version is 1.7.3 - if there is a newer version use it, anything newer than 1.7.3 should work.

If you are installing on Windows, the path of least resistance is to take the the version that is called 'Windows Executable+JRE'. After installing, open up a command prompt and ensure jruby is available on the command line:

$ jruby -v
jruby 1.7.3 (1.9.3p385) 2013-02-21 dac429b on Java HotSpot(TM) 64-Bit Server VM 1.6.0_33-b03 [Windows 7-amd64]

If you get a message indicating the jruby command cannot be found, they you have a problem with PATH settings.

Install the Gems

The Ruby programming environment distributes libraries in packages called gems. The three gems I mentioned above are libraries to help when unit testing PLSQL code, so we need to install them. The PLSQL Unit Test gem has a dependency on the other two, so installing it will automatically install the others:

$ jgem install plsql_unit_test

Install The ojdbc6 Drivers

The final step is to add the Oracle JDBC drivers, known as ojdbc6.jar. You can get them from Oracle - currently they are available here, but if that link does not work, just google for ojdbc6 download to find the download link.

Copy the downloaded jar file into the jruby lib directory - on my Windows install, this is at C:/jruby-1.7.3/lib

A Simple Database Test

Now that everything is installed, we need to make sure it works by creating a simple test.

Create a new folder and open a new file called test_helper.rb. This file will contain some code to connect to the database and bootstrap the test environment, all we need in it for now is the following:

require 'rubygems'
require 'plsql_unit_test'

PLSQLUnitTest::Setup.connect('sodonnel',         # user
                             'sodonnel',         # password
                             'local11gr2.world', # service
                             'localhost',        # IP address
                             '1521')             # port

The first two lines load the gem package manager and then the PLSQL Unit Test gem we installed earlier.

The final line connects to the database - change the connection details to point at your own database.

Now we are in a position to actually create a test. Open a new file in the same directory called firsttesttest.rb, and put the following code in it:

require './test_helper'

class FirstTest < Test::Unit::TestCase

  def test_db_connection_works
    query = @@db_interface.execute_sql("select 1 from dual")
    results = query.all_array

    assert_equal(1, results.length, 'There should be one row in the result set')
    assert_equal(1, results[0][0], 'The first and only row in the result set should contain the value 1') 
  end

end

There is not much code to understand here. The first line pulls in the test_helper file we added in the previous step which will also connect to the database.

Next we define a Ruby class for our test, called FirstTest. It inherits from Test::Unit::TestCase so we can make use of the built in test suite features.

Finally we create a method that contains code to do some actual testing.

The first thing to notice in the test method is the @@dbinterface variable. This was setup by the call to PLSQLUnitTest::Setup.connect in the testhelper file. In Ruby, this is known as a class instance variable - it is like a global variable and will be shared by all test classes in the test suite.

The @@db_interface variable contains an instance of SimpleOracleJDBC::Interface. It is worthwhile having a look at the documentation for this class on rubygems.org to learn how to execute SQL, functions and procedures and retrieve results.

Using the database interface, we execute the simple sql statement:

select 1 from dual

We would expect this to return a single row that contains a single column containing the value 1, and that is what the two assert_equal lines do.

To run this test, simply pass the name of the test file to jruby:

$ jruby firsttest_test.rb
Run options:

# Running tests:

.

Finished tests in 0.125000s, 8.0000 tests/s, 16.0000 assertions/s.

1 tests, 2 assertions, 0 failures, 0 errors, 0 skips

The output above shows our test passed. Try changing the 1 to 2 in one of the assert_equal lines to see what a failure would look like:

$ jruby firsttest_test.rb
Run options:

# Running tests:

F

Finished tests in 0.210000s, 4.7619 tests/s, 4.7619 assertions/s.

  1) Failure:
test_db_connection_works(FirstTest) [firsttest_test.rb:15]:
There should be one row in the result set.
<2> expected but was
<1>.

1 tests, 1 assertions, 1 failures, 0 errors, 0 skips

Testing a Procedure or Function

The test above is not particularly useful - its purpose is more to prove the testing environment is correctly setup. To get a little more advanced, we can test a simple function:

create or replace function test_func(i_input in varchar2)
  return varchar2
is
begin
  if i_input is null then
    return 'NULL';
  else
    return i_input;
  end if;
end;
/ 

This function doesn't do much - if a null value is passed in, it returns the string 'NULL', otherwise it return the value that was passed in. There are really only two test cases:

• If the function receives a null input, return the string NULL
• If the function receives a non null value, return the value

Open a new file in the test folder called testfunctest.rb, paste in the following code:

require './test_helper.rb'

class TestFuncTest < Test::Unit::TestCase

  def setup
    @proc_call = @@db_interface.prepare_call("
      begin
        :output := test_func(:input);
      end;")
  end

  def teardown
    @proc_call.close
  end

  def test_null_input_returns_null
    @proc_call.execute([String, nil, :out], [String, nil, :out])
    assert_equal("NULL", @proc_call[1])
  end

  def test_non_null_input_returns_null
    @proc_call.execute([String, nil, :out], "Hello")
    assert_equal("Hello", @proc_call[1])
  end
end

The first thing to notice, is that the setup and teardown methods have been introduced. When Ruby runs a test, it actually calls three methods:

• First the setup method is called
• Then the test_* method is called
• Finally the rollback method is called

This cycle is repeated for each test method in the class. In this example, we can use the setup method to prepare a call to the function, complete with two bind variables - one for the return value and one for the input value.

Then in the test methods we bind the input, execute the function and then assert the return value is as expected.

$ jruby test_func_test.rb
Run options:

# Running tests:

..

Finished tests in 0.141000s, 14.1844 tests/s, 14.1844 assertions/s.

2 tests, 2 assertions, 0 failures, 0 errors, 0 skips

Next Time

These few examples are good enough to set the scene on how PLSQL and database queries can be tested using JRuby. I have skimmed over the usage of Simple Oracle JDBC as it is best to consult the manual for the gem instead of repeating the information here.

In the next post, I will explore using my Data Factory gem to generate test data, and how a set of test files can be run with a single JRuby call to test an entire application.