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.
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:
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.
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.
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,
a test value against the SQL
LIKE pattern matcher, while unalike
a test value against NOT
SELECT alike( @this, 'that%', @description ); SELECT unalike( @this, 'that%', @description );
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.
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.