Testing Your MySQL Stored Procedures with MyTAP

If your MySQL database interfaces with applications or is used to generate reports then chances are good that it contains some massive stored procedures, just brimming with intensive logic and calculation code.  In that case, you had better set up some type of unit testing framework so that you can verify that your SQL code is doing what it should.  This article will introduce MyTAP, a unit-test suite based on the Test Anything Protocol (TAP). It’s the brainchild of David E. Wheeler, who based his creation on the more general database testing tool called pgTAP. 

Installing MyTAP

MyTAP is free to download from GitHub.  To install it, just extract the archive to a folder and add the MyTap database to your MySQL server by issuing the following command:

mysql -u root < mytap.sql

That will create the tap database, which contains a couple of tables and a whole lot of stored functions.

Always Have a Plan

The main purpose of a plan is to tell MyTAP how many tests your script is going to run.

To declare a plan, call the plan() function, passing in the number of tests to run:

SELECT tap.plan( 5 );

You can calculate the number of tests, if you need to:

SELECT plan(COUNT(*)) FROM mytable;

For those times that you aren’t sure how many tests you want to run, you can call the no-arguments no_plan() method:

CALL tap.no_plan();

It’s always preferable to have a plan over not having one, so try to avoid using no_plan() as much as possible. 

Here’s a sample test script:

-- Start a transaction.
BEGIN;

-- Turn off safe updates.
SET SQL_SAFE_UPDATES=0;

-- Plan the tests.
SELECT tap.plan(1);

-- Run the tests.
-- The following variable would normally be populated
 -- from the stored proc or function that you’re testing.
SET @testvalue = 'test';
SELECT tap.ok( @testvalue LIKE 'test', '@testvalue compare' );

-- Finish the tests and clean up.
CALL tap.finish();
ROLLBACK;

Some points to keep in mind:

• Be sure to turn off safe updates before calling any tap functions as these will fail otherwise.

• Always provide a test name or description to the test functions so that you can identify them in the output.
• At the end of your script, include a call to tap.finish() so that MyTAP can output diagnostics about failures and/or discrepancies between the planned number of tests and the number actually run.

To run the above test, save it to a .sql file and execute it from the mysql command line interface:

E: >mysql -u root --batch --raw --skip-column-names --unbuffered --database company < testscript.sql

1..1
ok 1 - @testvalue compare

Although the MyTAP docs include the use of the –execute flag before the .sql file, it produced errors for me.  For MySQL 5.5, the ‘<’ redirection symbol produced the desired result.

Canned Tests

The purpose of any testing framework is to match some calculated result against a predefined expectation.  MyTap includes several standard test functions that accommodate different kinds of comparisons against various data types.

ok()

The simplest of the predefined tests is the ok() function. It accepts an expression that evaluates to either true or false; a value of true signifies that the test succeeded, while false means that it failed:

ok( boolean, description )

Here are a few examples:

SELECT tap.ok( 9 < 10,        'numeric comparison' );
SELECT tap.ok( 'pie' ~ '^p',  'regex comparison' );
SELECT tap.ok( London,        concat(name, ' city London' ))
FROM cities;

On failure, ok() will produce some diagnostics:

SELECT tap.ok( @retval < 10, 'numeric comparison' );

not ok 1 - numeric comparison
#     Failed test 1: "numeric comparison"

Moreover, a NULL calculated value will automatically fail the test and append an additional diagnostic:

not ok 1 - numeric comparison
#     Failed test 1: "numeric comparison"
#     (test result was NULL)

eq() and not_eq()

If ok() compares an expression to “== true”, then eq() and not_eq() compare their two arguments using “=” and “<>”, respectively, to determine whether the test succeeds or fails:

SELECT tap.eq( my_func(), 1, 'my_func_eq_test' );

SELECT tap.not_eq( get_id(), '', 'get_id() not empty string test' );

While the same results can be achieved using the ok() function, eq() and not_eq() produce better diagnostics on failure because, unlike ok(), eq() and not_eq() can distinguish between the two individual values.  These are thus reported in the diagnostics:

SELECT tap.eq( 'Audi', 'Corvette', 'Car comparison test' );

Will produce something like this:

# Failed test 7:  "Car comparison test"
#         have: Audi
#         want: Corvette

Similar to eq() and not_eq(), matches() and doesnt_match() compares @this to that using a regex (i.e., /^that/):

SELECT matches( @this, '^that', @description );
SELECT doesnt_match( @this, '^that', @description );

For simple pattern matching, alike() matches a test value against the SQL LIKE pattern matcher, while unalike() matches a test value against NOT LIKE:

SELECT alike( @this, 'that%', @description );
SELECT unalike( @this, 'that%', @description );

Schema Tests

MyTAP also contains tests for checking your database schema.  One such test is called has_table():

has_table( database, table, description )

This function tests whether or not a table exists in a database. If you want to test for a table in the current database, the DATABASE() function will do the trick:

SELECT has_table(DATABASE(), 'cars', 'Check for table cars');

Writing Your Own Tests

All MyTAP tests are written as MySQL functions, so it’s really quite easy to add your own.  Here’s a test that checks for empty values:

DROP FUNCTION IF EXITS is_empty
DELIMITER //
CREATE FUNCTION is_empty (test_string TEXT, descr TEXT)
RETURNS TEXT
BEGIN
    IF test_string IS NULL || LENGTH(TRIM(test_string)) = 0 THEN
        RETURN ok(1, descr);
    END IF;
    RETURN concat(ok( 0, descr ), 'n', diag(concat(
           ' supplied string was not empty.'
    )));
END //

DELIMITER ;

Calling MyTAP’s ok() function – or any function that ultimately calls ok() – guarantees that the output is properly formatted and recorded in the database for diagnostics at the end of the test script. You can also provide your own diagnostics by appending them to ok()’s output  as done above.

Conclusion

One of the things that I really like about MyTAP is that you don’t need any additional software or tools to run it; everything is contained in the tap MySQL database. But that doesn’t mean that there aren’t any other tools out there.  In fact, there is a TAP Harness called my_prove that apparently simplifies running the tests from the command line.  I’m considering giving it a try in the near future.

See all articles by Rob Gravelle