Unit testing stored procedures with Junit

Anyone who has used an automated unit testing framework such as Junit knows just how life-changing an automated test suite can be.   Once you've  experienced validating that recent changes have not broken old code, or discovering subtle bugs via junit that would otherwise have remained undetected  with the press of a button, you naturally want to have this capability in all your programming environments.

Guisseppe Maxia has written a few stored procedure snippets to assist with automated unit testing of MySQL routines.  Unfortunately, the MySQL stored procedure language itself does not have the necessary abilities to fully implement the sort of unit testing we would like.  In particular, the inability for a stored procedure to capture the result sets generated by another stored procedure prevents a stored procedure from fully unit testing another.  

So I decided that - for me - Junit offered the best solution.  I created an extension to the Junit class that contains some assertions useful when unit testing stored procedure and extended my PlainSql JDBC wrapper classes to allow a stored procedure to return a single object that contains all its result sets and the values of OUT or INOUT parameters.  

If you're not familiar with Java and/or, you might feel that this solution is not for you.  However, the amount of java programming you need to do is very minimal and GUI environments such as Eclipse make it very easy to set up.  

Writing a stored procedure test case

 The procedure is pretty similar to a standard Junit test case, except that instead of extending junit.framwork.TestCase, you extend com.syncsoft.plainsql.SpTestCase.  You need a global PSconnection object (from my JDBC wrappers), so declare that as a private class variable:

import java.sql.Connection;
import java.sql.DriverManager;

import com.syncsoft.plainsql.PSconnection;
import com.syncsoft.plainsql.ProcedureResult;
import com.syncsoft.plainsql.RowSet;
import com.syncsoft.plainsql.SpTestCase;

public class SakilaTest extends SpTestCase {

    PSconnection conn;


You will almost certainly want to create a setUp() method to create your connection and probably a tearDown() method that can rollback any changes you've made.  Most database testing requires that the data be consistent between runs so you'll often want to perform tests that always undo any changes they might make:

    protected void setUp() throws Exception {
        super.setUp();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection myConnection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/sakila?user=root&password=secret");
        conn = new PSconnection(myConnection);
    }   

    protected void tearDown() throws Exception {
        super.tearDown();
        conn.rollback();
    }

Now you can write a test.  Consider the stored procedure here (customer_report.sql) that runs a report against the Sakila sample database .  The procedure takes 3 arguments - 2 of them OUT parameters - and returns 2 result sets. We want to make sure that when it's run it returns the correct number of result sets with the correct column names and the expected data.   For each unit test, we simply create a method starting with the string "test":

    public void testCustomerReport() throws Exception {
 

We start off by creating some variables representing the values of the parameters to the procedure.  We don't have to worry too much about OUT variables here, since the values of out variables will end up in the ProcedureResult object.

        int activeRentals = 0;
        int totalRentals = 0;
        int customerId = 431; // A "known" customer id


We execute the stored procedure and create a ProcedureResults object called results,  by using the executeProc method of the PSconnection class.  We supply the input and output variables together with the name of the stored procedure: 

        ProcedureResult results = conn.executeProc("customer_report", 431,
                activeRentals, totalRentals);

Everything retunred by the stored procedure call, including values of OUT or INOUT parameter and all of the result sets are contained within the results object.  Various assertions allow us to fail the test if the number of result sets are not as expected, if column names are not found, or if expected data values do not exist in the results.  We can also check the values of the OUT parameters:

        assertRowSetCount("Should be two result sets", results, 2);
        assertColumnCount("First rowset should have 3 columns", results, 1, 3);
        assertHasColumn("First rowset should contain column firstname",
                results, 1, "first_name");
        assertRowCount("First rowset should have exactly one row", results, 1,
                1);
        assertHasColumnValue("First result firstname should equal Joel",
                results, 1, "first_name", "JOEL");

        assertColumnCount("Second result set should have 4 columns", results,
                2, 4);
        assertHasColumn("Second result should include column 'title'", results,
                2, "title");

        assertParameterValue("2nd parameter should return 1", results, 2, 1);
        assertParameterValue("3rd parameter should return 23", results, 3, 23);

    }

Some of these checks - the number and names of columns for instance - will be independent of the state of the database, while others will be dependent of values in database tables.  Generally,  it's wise to somehow reset your database before preforming unit tests that rely on data state.  

Unit testing the database state

Another procedure (return_film) updates a rental record when a video is returned.  To write a test for this, we probably want to directly check the contents of the database before and after the procedure is executed.  This test does that, again using the PlainSql classes:

    public void testReturnVideo() throws Exception {

        int CustomerId = 431; // A "known" customer id
        int RentalId = 13587; // A "known" rental id

        String sql = "SELECT * FROM rental " + " WHERE customer_id=?"
                + "   AND rental_id=?" + "   AND return_date IS NULL";

        // Should be a single film out on loan prior to update
        RowSet r1 = conn.fetchAll(sql, CustomerId, RentalId);
        conn.getLogger().info(r1.toString());
        assertRowCount("Should be a single row before video return", r1, 1);

        // Return the film
        conn.executeProc("return_film", CustomerId, RentalId);

        // Now there should be no film out on loan
        RowSet r2 = conn.fetchAll(sql, CustomerId, RentalId);
        assertRowCount("Should be no rows after video return", r2, 0);

    }

Junit has a built-in IDE for running tests and observing the results, but I always use Junit in Eclipse.  In Eclipse,  you get a interface like the one below showing you which tests passed and which succeeded and a stack trace sho2ing where the failures occured.  Note the message from the assertion failure indicating that after returning our film there are still rows being returned for that customer/rental with a null RETURN_DATE.  There must be something wrong either with the data or the procedure (in this case I simply modified the assertion to force it to fail):

Junit in eclipse

The full text for the Salika testunit example is here.  

Automated testing for databases

At the MySQL users conference, Scott Ambler challanged us to adopt more agile techniques for data modeling and database maintenance.  In particular,  he asked how many had automated  regression tests for the database - very few of us did.  While it may seem awkward to use the Java Junit framework to construct tests for MySQL database and or stored proceudres,  it is far better  to use whatever tools are available rather than to avoid automated database testing altogether.  Junit certainly has the neccessary tools to perform automated testing of stored procedures and many of these routines could be used to test the database in general.  I'm definitely going to make it my habit to contsturct unit tests for new stored procedures from now on.

My friend and colleague Steve Feuerstein has developed a fairly sophisticated unit testing framework for Oracle stored procedures -  you can find out more about that here.  

References

There's a good article on getting started with Junit here.  
The JavaDoc for my PlainSql classes.
JavaDoc for the SpUnitTest class (lists all of the exceptions you can call).
A zip containing the PlainSql classes, the particular Connector/J jar and other dependencies

Requirements


This particular implementation has some pretty strict version requirements:

JDK 1.5
Junit. I'm using 3.8.1
MySQL Connector/J version 3.1.2+.  I needed the ParameterMetaData class to be working, which meant that I had to pick up a recent 3.1 snapshot
My PlainSql classes and supporting files.

Everything you need other than the JDK and Junit  is in this zip file.