utMySQL - Unit Testing for MySQL

utMySQL

utMySQL is a unit test frame work for MySQL V 5 using stored procedures.

utMysql is an Open Source project, using the GNU General Public License (GPL) and is operating system independent.
Downloads are located on the sourceforge project page: http://sourceforge.net/projects/utmysql/
The utMySQL home page is located here: http://utmysql.sourceforge.net/

Stored procedure in MySQL V5.0 are very useful in removing the database query layer from server side scripting. It is entirely possible to develop and maintain the database layer separately from the server side code.

Installation
Test harness
Additional notes
Install the example.

Installation

To install unit testing for MySQL run the 'utMySQL_pkg_TestSuite.sql' script file in your chosen database (make sure you have the correct privileges for creating stored procedures and that you ARE actually in a database). Four procedure are installed by utMySQL:

utMySQL_pkg_TestSuite()
utMySQL_pkg_SelfTest()
utAssert_eq()
utAssert_notEq()

Two optional user defined procedures are:

ut_SetUp()
ut_TearDown()

Test Harness

utMySQL_pkg_TestSuite()
This stored procedure will look for ALL procedures (not functions) in the current database schema (expect for MySQL V5.0.0 where procedures are considered global to ALL databases) that start with the the letters "ut_" and execute them as unit tests cases under the utMySQL framework.

If the procedures ut_SetUp() is present it will be called before each unit test case is executed.
If the procedure ut_TearDown() is present it will be called after each unit test case is executed.

utMySQL_pkg_SelfTest()
This procedure executes transparently prior to ANY test cases being executed, it checks that utMySQL is working as expected.
In the event of an assertion failure during this process utMySQL will stop, none of the test cases will be executed.

utAssert_eq()
An assertion procedure testing for equality between the expected result and the actual result, in the following form:

 CALL utAssert_eq( sAssertionMessage, xExpectedResult, xActualResult );

E.g.

 CALL utAssert_eq( 'Check that user name is "BADGER"', 'BADGER', UPPER( 'badger' ) );

If the assertion fails an error message is display.

utAssert_notEq()
An assertion procedure testing for non-equality between the expected result and the actual result, in the following form:

 CALL utAssert_notEq( sAssertionMessage, xExpectedResult, xActualResult );

E.g.

 CALL utAssert_notEq( 'Check that a Badger is not a Weasel', 'Badger', 'Weasel' );

If the assertion fails an error message is display.

Additional Notes

Nasty hack needed to get at the result set of a standard SELECT query, requires the redirection of results into a temporary table for unit test to access them (MySQL does not allow access to results set once generated, functions cannot return CURSOR types either). This is not difficult to deal with by creating an identical duplication of the SELECT query redirecting the results into a temporary table. The temporary table is only required during unit testing, not in a live environment.
Alternatively you could choose to tests these parts in the server side scripting instead, (which of course would mean you are not be able to test the database in isolation).
When using InnoDB tables in MySQL use of SAVEPOINT's and ROLLBACK's helps considerably.
For best display of assertion results adjust the number of columns in your console (bash, dos prompt etc) should be set to 120 or more, otherwise it can become difficult to read.
If MySQL cannot find a particular procedure or function execution of utMySQL is unceremoniously stopped. For this reason it is often better to execute a freshly written unit test case individually prior to running the test suite.

Packages do not currently exist in MySQL (time of writing V5.0.16). utMySQL uses an underscore in the procedure names to represent a package. E.g.

 utMySQL_pkg_TestSuite

Where utMySQL is the package name and TestSuite the actual procedure name. This may at first glance appear wasteful (why should I have to type so much!), however the stored procedure name space soon becomes crowded and difficult to manage without. Hopefully future versions of MySQL will have support for:

Accessing the Results Set from store procedures, (SELECT * FROM result_set).
Name and parameter mangling so more than one stored procedure can have the same name.
Packages.
Default values assignable to procedure parameters.
LIMIT query directive to accept variables

As this will make the database easier to maintain.

Install the example.

Navigate to the "utMySQL/example" directory from the command prompt and start the MySql Monitor. Create a new database in MySQL, making sure you have create procedure privileges.

mysql> create database utmysql_test;
Query OK, 1 row affected (0.02 sec)

mysql> use utmysql_test;
Database changed

mysql> show tables;
Empty set (0.00 sec)

From the MySQL Monitor run the "full_install.sql" script located in the "install" directory. This will produce the following output:

mysql> source install/full_install.sql
+---------------------------------+
| Started                         |
+---------------------------------+
| Creating utMySQL example tables |
+---------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------------------------+
| Started                      |
+------------------------------+
| Installing example packages. |
+------------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------------+
| Started                       |
+-------------------------------+
| Installing example unit tests |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------------+
| Started            |
+--------------------+
| Installing utMySQL |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------------------------+
| Tables_in_utmysql_test  |
+-------------------------+
| article                 |
| article_audit_trail     |
| author                  |
| configuration_parameter |
+-------------------------+
4 rows in set (0.00 sec)

+-----------------------------------------------+--------------+
| routine_name                                  | routine_type |
+-----------------------------------------------+--------------+
| Article_pkg_addArticle                        | FUNCTION     |
| Article_pkg_createArticleAudit                | FUNCTION     |
| Article_pkg_getArticles                       | PROCEDURE    |
| Author_pkg_addAuthor                          | FUNCTION     |
| Author_pkg_getAuthors                         | PROCEDURE    |
| Configuration_pkg_getConfigurationParamter    | FUNCTION     |
| Configuration_pkg_setConfigurationParamter    | PROCEDURE    |
| ManageSchema_pkg_articleAuditTrailData        | PROCEDURE    |
| ManageSchema_pkg_articleData                  | PROCEDURE    |
| ManageSchema_pkg_authorData                   | PROCEDURE    |
| ManageSchema_pkg_clearSchema                  | PROCEDURE    |
| ManageSchema_pkg_configurationParamterData    | PROCEDURE    |
| ManageSchema_pkg_installUnitTestData          | PROCEDURE    |
| utAssert_eq                                   | PROCEDURE    |
| utAssert_notEq                                | PROCEDURE    |
| utMySQL_pkg_SelfTest                          | PROCEDURE    |
| utMySQL_pkg_TestSuite                         | PROCEDURE    |
| ut_Article_pkg_addArticle                     | PROCEDURE    |
| ut_Article_pkg_createArticleAudit             | PROCEDURE    |
| ut_Article_pkg_getArticles                    | PROCEDURE    |
| ut_Author_pkg_addAuthor                       | PROCEDURE    |
| ut_Author_pkg_getAuthors                      | PROCEDURE    |
| ut_Configuration_pkg_getConfigurationParamter | PROCEDURE    |
| ut_Configuration_pkg_setConfigurationParamter | PROCEDURE    |
+-----------------------------------------------+--------------+
24 rows in set (0.02 sec)

mysql>

Once run successfully this will create:

4 tables
20 procedures
4 functions

You can re-run the install script at any time.

To run a single test case in isolation:

mysql> CALL ut_Article_pkg_getArticles();
Query OK, 0 rows affected (0.14 sec)

The fact that no assertion messages appear indicate that the tests have all passed. To cause an assertion failure let us change the expected result of a test in the ut_Article_pkg. Changing the assertion 'Check first article in list, title field.' in the getArticles() test case:

    CALL utAssert_eq( 'Check first article in list, title field.'
                    , 'CHEESE shortage looms'
                    , sTitle );

To:

    CALL utAssert_eq( 'Check first article in list, title field.'
                    , 'Gasp!'
                    , sTitle );

Save and recompile the script by:

mysql> source tests/ut_Article_pkg.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Run test case again:

mysql> CALL ut_Article_pkg_getArticles();
+----------------+-------------------------------------------+-----------------+-----------------------+
| Unit Test Name | utAssert_eq Message                       | Expected Result | Actual Result         |
+----------------+-------------------------------------------+-----------------+-----------------------+
| NULL           | Check first article in list, title field. | Gasp!           | CHEESE shortage looms |
+----------------+-------------------------------------------+-----------------+-----------------------+
1 row in set (0.13 sec)

Query OK, 0 rows affected, 1 warning (0.16 sec)

This time we can see that an Assertion has occurred... Four pieces of information are display:

Unit Test Name (which is NULL when you run an individual test case).
The type of assertion failure (either utAssert_eq or utAssert_notEq)
The Expected Result (in this case "Gasp!")
The Actual Result (which is "CHEESE shortage looms")

To run a the entire test suite:

mysql> CALL utMySQL_pkg_TestSuite( );
+------------------------------------+
| Starting MySQL Test Suite          |
+------------------------------------+
| utmysql_test : 2006-10-20 22:39:13 |
+------------------------------------+
1 row in set (0.02 sec)

+---------------------------+
| Running unit test:        |
+---------------------------+
| ut_Article_pkg_addArticle |
+---------------------------+
1 row in set (0.05 sec)

+-----------------------------------+
| Running unit test:                |
+-----------------------------------+
| ut_Article_pkg_createArticleAudit |
+-----------------------------------+
1 row in set (0.22 sec)

+----------------------------+
| Running unit test:         |
+----------------------------+
| ut_Article_pkg_getArticles |
+----------------------------+
1 row in set (0.33 sec)

+------------------------------+-------------------------------------------+-----------------+-----------------------+
| Unit Test Name               | utAssert_eq Message                       | Expected Result | Actual Result         |
+------------------------------+-------------------------------------------+-----------------+-----------------------+
| ut_Article_pkg_getArticles() | Check first article in list, title field. | Gasp!           | CHEESE shortage looms |
+------------------------------+-------------------------------------------+-----------------+-----------------------+
1 row in set (0.36 sec)

+-------------------------+
| Running unit test:      |
+-------------------------+
| ut_Author_pkg_addAuthor |
+-------------------------+
1 row in set (0.38 sec)

+--------------------------+
| Running unit test:       |
+--------------------------+
| ut_Author_pkg_getAuthors |
+--------------------------+
1 row in set (0.39 sec)

+-----------------------------------------------+
| Running unit test:                            |
+-----------------------------------------------+
| ut_Configuration_pkg_getConfigurationParamter |
+-----------------------------------------------+
1 row in set (0.41 sec)

+-----------------------------------------------+
| Running unit test:                            |
+-----------------------------------------------+
| ut_Configuration_pkg_setConfigurationParamter |
+-----------------------------------------------+
1 row in set (0.42 sec)

+-------------------+--------------------+
| Total Assertions: | Assertions Failed: |
+-------------------+--------------------+
| 105               | 1                  |
+-------------------+--------------------+
1 row in set (0.44 sec)

+-------------+
| Time Taken: |
+-------------+
| 1s          |
+-------------+
1 row in set (0.48 sec)

Query OK, 0 rows affected (0.50 sec)

As you can see it indicates which database the tests where started in, what time it started, which unit tests have started running and that our assertion is still failing (because we haven't fixed it yet). At the bottom is a total of the number of assertions and if any failed assertions:

+-------------------+--------------------+
| Total Assertions: | Assertions Failed: |
+-------------------+--------------------+
| 105               | 1                  |
+-------------------+--------------------+

Time to fix the assertion in ut_Article_pkg_getArticles(), recompile and run the test suite again:

mysql> CALL utMySQL_pkg_TestSuite( );
+------------------------------------+
| Starting MySQL Test Suite          |
+------------------------------------+
| utmysql_test : 2006-10-20 22:42:25 |
+------------------------------------+
1 row in set (0.01 sec)

+---------------------------+
| Running unit test:        |
+---------------------------+
| ut_Article_pkg_addArticle |
+---------------------------+
1 row in set (0.05 sec)

+-----------------------------------+
| Running unit test:                |
+-----------------------------------+
| ut_Article_pkg_createArticleAudit |
+-----------------------------------+
1 row in set (0.19 sec)

+----------------------------+
| Running unit test:         |
+----------------------------+
| ut_Article_pkg_getArticles |
+----------------------------+
1 row in set (0.31 sec)

+-------------------------+
| Running unit test:      |
+-------------------------+
| ut_Author_pkg_addAuthor |
+-------------------------+
1 row in set (0.36 sec)

+--------------------------+
| Running unit test:       |
+--------------------------+
| ut_Author_pkg_getAuthors |
+--------------------------+
1 row in set (0.38 sec)

+-----------------------------------------------+
| Running unit test:                            |
+-----------------------------------------------+
| ut_Configuration_pkg_getConfigurationParamter |
+-----------------------------------------------+
1 row in set (0.39 sec)

+-----------------------------------------------+
| Running unit test:                            |
+-----------------------------------------------+
| ut_Configuration_pkg_setConfigurationParamter |
+-----------------------------------------------+
1 row in set (0.41 sec)

+-------------------+--------------------+
| Total Assertions: | Assertions Failed: |
+-------------------+--------------------+
| 105               | 0                  |
+-------------------+--------------------+
1 row in set (0.42 sec)

+-------------+
| Time Taken: |
+-------------+
| 0s          |
+-------------+
1 row in set (0.42 sec)

This time no failures. (You did save and recompile right?)

OK the tests passed, so what!?
In rounding off this example, all the tests have passed - now what? The tests have passed our expected out comes. This, of course, does not mean there are no errors in the code. If errors are found new tests should be written to expose them, then fix the code.
To actually use the functionality created firstly we need to call the SchemaManager to clear the database and install some useful test data. Obviously we would not expect to see or install the SchemaManager in a live environment, but as this is a test environment it is present.

mysql> CALL SchemaManager_pkg_clearSchema();
Query OK, 0 rows affected (0.02 sec)

mysql> CALL SchemaManager_pkg_installUnitTestData();
Query OK, 1 row affected (0.09 sec)

Calling these two procedures first clears all the tables in the schema, the second installs some test data.
We are now (finally!) ready to call our first stored procedure that will get all the articles (actually the latest 10) in the database:

mysql> CALL Article_pkg_getArticles( FALSE );
+---------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+----------+-------------------+
| title                           | summary                                                 | text                                                                                                                                                                                                                         | sticky | dateCreated         | language | authorDisplayName |
+---------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+----------+-------------------+
| CHEESE shortage looms           | CHEESE shortage hits Hastings, citizens panic.          | Offical say a CHEESE shortage is to engulf the town of Hastings, citizens roam streets in mobs.                                                                                                                              |      0 | 2006-09-13 13:15:16 | English  | Badg              |
| Weasel seen looting Crypt       | A weasel was seen breaking into The Crypt.              | An mysterious (and unknown) Weasel was seen breaking and entering into a local pub The Crypt in Battle. No items were stolen, except for the kitchens cheese supplies. crumbs where found, owners and local Police baffled.. |      1 | 2006-09-13 13:14:15 | English  | Weas              |
| Badger on Cheese eating rampage | Badger goes bezerk and eats all the cheese in Hastings. | An unknown Badger went bezerk today in Hastings, eating all the cheese. Some witnesses suggest he was wearing a mask..                                                                                                       |      0 | 2006-09-13 13:13:13 | English  | Badg              |
+---------------------------------+---------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+----------+-------------------+
3 rows in set (0.02 sec)
Query OK, 0 rows affected (0.05 sec)

To list all the authors:

mysql> CALL Author_pkg_getAuthors( FALSE );
+------+------------+-------------+--------------------+
| id   | name       | displayName | emailAddress       |
+------+------------+-------------+--------------------+
|    1 | Mr. Badger | Badg        | Badger@example.com |
|    2 | Mr. Weasel | Weas        | Weasel@example.com |
|    3 | The Editor | [Ed]        | Editor@example.com |
+------+------------+-------------+--------------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

All queries have now been removed from server side scripts and can be modified and tested independently. It may be tempting to call these stored procedures directly from the server side scripts, however it is best to introduce another layer, server objects, which are the only objects that connect to the database and feed the data into the server side scripts. This has three immediate benefits:

The server objects can be mocked in the unit testing of scripts, decoupling any dependencies on the database easily.
If the database layer changes dramatically then only the server object needs to change.
Helps provide database abstraction if your software requires to run on different database types.

Also variable binding for parameters should be used (when both reading and writing to the database), reducing the chance of sql code injections.

Unit Testing for MySQL (V 1.0.1) by Warren Willmey 2006