Unit testing in MySQL

By | August 4, 2012

Unit Testing in MySQL

Stored procedure in MySQL 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. To complete this seperation Unit Testing directly in stored procedures makes just sense.

The test database

The stored procedures which are been tested in this example will use the employees sample database which from Patrick Crews and Giuseppe Maxia.
If you want to follow this example you can download the database here

If you want to get more information about the employee database visit: Employees introduction

The Unit testing framework

We will use the utmysql unit testing framework for MySQL. Download the zip and
extract it somewhere to import it to mysql.

Run the sql script “src/utMySQL_pkg_TestSuite.sql” in the database where you want to run the unit tests.

mysql -u test -p testdatabase < src/utMySQL_pkg_TestSuite.sql

The Script will create four new stored procedures which we will use for the unit testing:
- utMySQL_pkg_TestSuite()
- utMySQL_pkg_SelfTest()
- utAssert_eq()
- utAssert_notEq()

Test of the installation

To test the installation connect to the database and run the stored procedure utMySQL_pkg_SelfTest. If all is correct installed you should got a result like this:

mysql -u test -p testdatabase
call utMySQL_pkg_SelfTest();
0 row(s) affected, 1 warning(s):
1265 Data truncated for column 'dTestDate1' at row 1

Create the stored procedure which will be tested

CREATE PROCEDURE `changeTitle`(in_emp_no INT, in_new_title VARCHAR(50))
    DECLARE last_title_date DATE;
    DECLARE new_date_to DATE;
    SELECT to_date FROM titles WHERE emp_no = in_emp_no ORDER BY to_date DESC LIMIT 1
    INTO last_title_date ;
    SET new_date_to=date_add(last_title_date,INTERVAL 5 YEAR);
    INSERT INTO titles (emp_no, title, from_date, to_date)
    VALUES (in_emp_no, in_new_title, last_title_date, new_date_to);
END ;;

Check the stored procedure if it is working like we expect.

SELECT * FROM titles WHERE emp_no = 10008;
SELECT * FROM titles WHERE emp_no = 10008;
10008	Assistant Engineer	1998-03-11	2000-07-31
CALL changeTitle(10008,'New Title');
SELECT * FROM titles WHERE emp_no = 10008;
10008	Assistant Engineer	1998-03-11	2000-07-31
10008	NEW Title	2000-07-31	2005-07-31

Create and run the test case

CREATE DEFINER=`root`@`%` PROCEDURE `ut_employee_pkg_changeTitle`()
    DECLARE l_new_count INT;
    DECLARE l_old_count INT;
    SELECT COUNT(*) FROM titles WHERE emp_no=1008 INTO l_old_count;
    CALL changeTitle(10008,'New Title');
    SELECT COUNT(*) FROM titles WHERE emp_no=1008 INTO l_new_count;
    CALL utAssert_eq( 'Check if the emp got 1 title more', l_old_count+1 , l_new_count );

Run the single test calling the stored procedure:

mysql&gt; CALL ut_employee_pkg_changeTitle;
Query OK, 0 ROWS affected (0.13 sec)

Or run all the unit tests of the suit (all stored procedures with the name ut_%).

mysql&gt; CALL utMySQL_pkg_TestSuite;
| Starting MySQL Test Suite       |
| employees : 2012-08-03 12:10:45 |
1 ROW IN SET (0.21 sec)
| Running unit test:          |
| ut_employee_pkg_changeTitle |
1 ROW IN SET (0.33 sec)
| Total Assertions: | Assertions Failed: |
|                 1 |                  0 |
1 ROW IN SET (0.34 sec)
| TIME Taken: |
| 0s          |
1 ROW IN SET (0.41 sec)
Query OK, 0 ROWS affected, 1 warning (0.41 sec)

[amazon_carousel widget_type="SearchAndAdd" width="600" height="200" title="Books" market_place="" shuffle_products="False" show_border="False" keywords="Mysql, stored procedure" browse_node="" search_index="Books" /]

Leave a Reply