Writing tests

Before continue reading, make sure that you've read test storage structure page. In this section there is an overview of how to create your tests with using testing storage. Currently, this could be done only in manual mode (so, by executing usual SQL statements), but in future the corresponding API will be added.

In mysql-unit repository, there is a sample tests file, which is called tests.dummy_proc.sql. It will create some tests for procedure, which is called DUMMY_PROC (if you want, you may check out it's creation sql here). Here are comments to that file:

-- Tests for dummy procedure
-- dummy_proc should:
-- 1. Create table in current database with name as passed first argument
-- 2. Add second argument to created table as a column
-- 3. Set type of column as third argument

This is about description of what DUMMY_PROC should do. From that follows, that to write desired tests, we need:

• Add the record into TEST_PROCEDURE_ASSERTIONS table. If the call is supposed to be error, then add is_error flag together with expected error_code
• Add arguments into TEST_PROCEDURE_ARGUMENTS table. Our procedure has three arguments, so that should be three records per one test in that table
• Finally, add some checks into TEST_PROCEDURE_RESULTS table. That may be custom expressions, DDL checks e t.c.

In the file, that linked above, there is only one test for procedure DUMMY_PROC. Let's see what it is:

1. First thing to do. We just adding the record to main table, which tells: "This is a record for testing procedure 'dummy_proc' and this call should not throw any error".

INSERT INTO TEST_PROCEDURE_ASSERTIONS
(`procedure_name`, `is_error`, `error_code`)
VaLUES
('dummy_proc', 0, NULL);

2. We need to capture, what is our id, because it will be referenced from child tables:

SET @test_id_tmp_mysql_unit:=LAST_INSERT_ID();

3. Next, our procedure call should have some arguments. The statement below corresponds to call: CALL DUMMY_PROC("foo", "bar", "VARCHAR(255)"):

INSERT INTO TEST_PROCEDURE_ARGUMENTS
(`test_id`, `argument_value`)
VALUES
(@test_id_tmp_mysql_unit, '"foo"'),
(@test_id_tmp_mysql_unit, '"bar"'),
(@test_id_tmp_mysql_unit, '"VARCHAR(255)"');

4. Finally, we want to add a check, if our table was created and check that created table has the corresponding column:

INSERT INTO TEST_PROCEDURE_RESULTS
(`test_id`, `ref_expression`, `ref_value`, `ref_is_error`, `ref_error_code`)
VALUES
(@test_id_tmp_mysql_unit, 'CHECK_DDL_TABLE_EXISTS()', 1, 1, '1318'),
(@test_id_tmp_mysql_unit, 'CHECK_DDL_COLUMN_EXISTS("", "foo", "bar")', 1, 0, NULL);

What will actually happen, when test procedure will run:

Well, here we are:

mysql> CALL TEST_PROCEDURE_VERBOSE('dummy_proc');
Query OK, 0 rows affected (0.46 sec)

All tests passed, because:

• First expression is incorrect, because CHECK_DDL_TABLE_EXISTS expects 3 parameters (check the corresponding manual page). However, our check has is_error flag set together with proper error_code (which is 1318 for this exception)
• Second expression is correct. Since our call was successful (we've passed correct arguments), the table was created and the column in it too. Therefore, test passed, and, since it's the last test and last check for our procedure, the system returned success tests passing result.

Function tests are easy, because they affect only one table, TEST_FUNCTION_ASSERTIONS. So writing tests for functions is nothing more than just executing INSERT statements on that table. Let's test mysql built-in function DATABASE(). Our current database is "test", so:

1. Simple success test:

INSERT INTO TEST_FUNCTION_ASSERTIONS 
(`function_name`, `expression`, `value`, `is_error`, `error_code`) 
VALUES 
('DATABASE', 'DATABASE()', 'test', 0, NULL);

2. More complicated expression:

INSERT INTO TEST_FUNCTION_ASSERTIONS 
(`function_name`, `expression`, `value`, `is_error`, `error_code`) 
VALUES 
('DATABASE', 'CONCAT(CHAR(73), " am ", DATABASE(), "er") REGEXP "^I"', '1', 0, NULL);

3. And invalid expression:

INSERT INTO TEST_FUNCTION_ASSERTIONS 
(`function_name`, `expression`, `value`, `is_error`, `error_code`) 
VALUES 
('DATABASE', 'DATABASE("foo")', '', 1, "1064");

That's it for now, run our tests:

mysql> CALL TEST_FUNCTION('DATABASE');
ERROR 1644 (80300): Test id < 3 > : expectation of specific ERROR CODE failed.

Wait, what? This seems like something weird happened. Look here:

mysql> SELECT DATABASE('foo');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''foo')' at line 1

This clearly states that error is "1064", which we specified on third step. This is a bug in mysql-unit! Actually - it isn't. To understand, what is wrong, we first should check testing trace:

mysql> CALL TEST_FUNCTION_VERBOSE('DATABASE');
+---------+-----------------+------------+---------------+--------------------------+------------------------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_id | test_expression | test_value | test_is_error | test_error_code_expected | test_error_code_thrown | test_error_message_thrown                                             | test_trace                                                                                                                                                                                                             |
+---------+-----------------+------------+---------------+--------------------------+------------------------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       3 | DATABASE("foo") |            |             1 | 1064                     | 1243                   | Unknown prepared statement handler (eval_mysql_unit) given to EXECUTE | Test id < 3 > : expectation that expression ( DATABASE("foo") ) throws ERROR CODE 1064, failed. Actual ERROR CODE thrown is "1243" with message: Unknown prepared statement handler (eval_mysql_unit) given to EXECUTE |
+---------+-----------------+------------+---------------+--------------------------+------------------------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

So, that's it. We've faced one of restrictions for mysql-unit. It uses prepared statements to evaluate dynamic SQL in expression, and they will fail if the SQL contains syntax errors. I.e.: preparation of statement will fail, then unknown statement handler will be passed to execution and then error 1243 will occur. In this section, it's just a brief overview of this corner case, but mysql-unit has such restrictions, you can check them on the restrictions page. The outcome here is: testing built-in MySQL functions in general is a bad idea.

4. Ok, fix the "error":

mysql> UPDATE TEST_FUNCTION_ASSERTIONS SET error_code='1243' WHERE id=3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now, run tests:

mysql> CALL TEST_FUNCTION_VERBOSE('DATABASE');
Query OK, 0 rows affected (0.01 sec)

All is "well", but this has little sense. In fact, last test will not test DATABASE() function. Logically, it will check that MySQL will throw specific error on event, when non-prepared statement is executed. So, nothing common with our function testing. Again - you should not use mysql-unit for testing built-in functions. They are supposed to be correct.