STK/Unit Faq

This is just a FAQ/cookbook. See also the complete documentation, or the quickstart tutorial.

What is STK?

STK stands for SQL ToolKit. SKT is a family of tools which aim to make Stored Programs easier to develope, easier to debug, easier to mantain, more stable e more performant. Some of these projects are libraries of Stored Programs, others are external tools.

What is STK/Unit?

STK/Unit is a Unit Testing framework written in pure SQL.

What is that weird license, AGPL3?

STK/Unit's license is GNU Affero GPL, version 3. It is very similar to GPLv3. Here are the differences that STK/Unit users should be aware of:

  • If you install STK/Unit on your server and make it avaible for your users, you must make sure that they can see all changes you make to the STK/Unit's code.
  • There are no restrictions for the PHP/Java/whatever applications which act as a frontend for STK/Unit.

What platforms are supported?

STK/Unit supports all versions of MariaDB, and MySQL 5.1 and newer. Percona should work, as long as the matching MySQL version works.

STK/Unit is mainly developed on MariaDB. Production and development versions are always supported. There are some minor issues on MariaDB 5.1, 5.2 and 5.3, and MySQL 5.1.

Refer to the documentation for more detailed information.

What can be tested with STK/Unit?

Al kind of objects that can be created in a Database can be tested: Tables, Views, Foreign Keys, Triggers, Stored Routines, Virtual Columns, UDF's, Storage Engines…
Not only developers can benefit from this libraries, but also DBA's can test upgrades and configuration changes.

What type of tests can I perform with STK/Unit?

STK/Unit, as the name suggests, is for Unit Testing. It's executed as a SQL program within the database server, so it cannot do Integration Tests or System Tests.

Unit Testing is, by definition, Black Box Testing. But when the STK/DBUG project will be released, you will be able to mix Black Box and White Box Testings.

How do I install STK/Unit?

Base installation:

  • Start mysqld
  • Make sure that SQL_MODE is empty.
  • path/to/mysql -u <username> -p<password> < path/to/stk_unit.sql

To install tests (useful if you want to verify a bug in STK/Unit, or help developing any STK tool), you can install all or some files in the test directory.

Please, show me a hello_world()

First, let's create a Test Case (TC). A TC is a database which contains Base Tests (BT).
BT's are Stored Procedures whose name starts with 'test_'. BT's can trigger any number of Passes and Fails using the assert_* Procedures.

CREATE DATABASE my_test;

CREATE PROCEDURE test_hello_world()
BEGIN
    CALL `stk_unit`.assert_true(@a = 'Hello World!',
            '@a doesnt greet the world... how rude!'
        );
END;

Now, let's start the TC:

CALL stk_unit.test_case_run('my_test');

To see the result, we can query one of the commodity View in stk_unit (like last_test_summary), or issue:

SELECT stk_unit.test_case_show('my_test');

Please, show me a Test Suite example

Test Suites (TS's) are Stored Procedures defined in the stk_suite DB. They activate a set of TC's. Adding a new TS is usually trivial. Here's an example:

CREATE PROCEDURE `stk_suite`.my_app()
BEGIN
    CALL `stk_unit`.`test_case_run`('test_blog');
    CALL `stk_unit`.`test_case_run`('test_forum');
END;

How do I test Tables and Columns?


You can test that a column is able to contain realistic data: IE, it isn't too short, and it uses the proper character set. Here's an example:

CREATE PROCEDURE test_tab_t1()
BEGIN
    DECLARE str_in TEXT DEFAULT 'Слободан';
    DECLARE str_out TEXT DEFAULT '';
    INSERT INTO t1 SET surname = str_in;
    SELECT surname FROM t1 WHERE surname = str_in INTO str_out;
    CALL `stk_unit`.assert_equals(str_in, str_out,
            'Looks like surname is using a charset which cant handle slavic surnames'
        );
END;

If you want to test that a field only conains UNIQUE values, you can use this assertion:

CALL `stk_unit`.assert_equals(0,
            EXISTS (SELECT COUNT(*) FROM tab GROUP BY col HAVING COUNT(*) > 1),
            'col contains duplicate values'
        );

How do I test Views?

Often Views have ON/WHERE/HAVING clauses, which include some rows and exclude other rows. You may want to check that those clauses are not too inclusive and not too exclusive:

INSERT INTO t1 (...) VALUES (...);
CALL `stk_unit`.assert_true(NOT EXISTS (SELECT ...), 'This row should be included in the view');
CALL `stk_unit`.assert_true(EXISTS (SELECT ...), 'This row should not be included in the view');

Some Views are writeable, other are read-only. But as the database gets more complex, some Views need to be re-defined. And you may want to write a test to make sure that writeable Views remain writeable after you redefine them:

-- if the view is read-only, an exception will be triggered
INSERT INTO my_view (...) VALUES (...);

Sometimes Views are used to generate computed values on demand, so you may want to check the paragraph on Virtual Columns.

How do I test Virtual Columns?

A Virtual Column's value is always computed using other columns, and it's always deterministic. So, Virtual Columns can always be tested. Example:

CREATE PROCEDURE test_virtual()
BEGIN
    DECLARE t_price TEXT DEFAULT 1000;
    DECLARE t_tax TEXT DEFAULT 210;
    -- insert new product and its price
    INSERT INTO article SET price = price;
    -- check if the 'tax' virtual column contains the correct value
     CALL `stk_unit`.assert_equals(t_tax, (SELECT tax FROM article WHERE id = LAST_INSERT_ID()), NULL);
END;

How do I test Triggers?

Suppose you have a table t1 which contains documents, and a table t2 which is similar to the desktop's "recycle bin". When you issue a DELETE to drop a row from t1, a Trigger copies that row to t2. At least, this is what should happen; so you want to use STK/Unit to test that the Trigger properly works.

CREATE PROCEDURE test_trigger_trg1()
BEGIN
    DECLARE str_in TEXT DEFAULT 'Blah, blah, blah...';
    DECLARE str_out TEXT DEFAULT '';
    -- insert the row into c1
    INSERT INTO t1 SET c1 = str_in;
    -- fire the Trigger
    DELETE FROM t1 WHERE c1 = str_in;
    -- check that the Trigger copied the row
    SELECT c1 FROM t2 WHERE c1 = str_in INTO str_out;
    CALL `stk_unit`.assert_equals(str_in, str_out,
            'Trigger trg1 didnt copy the row!'
        );
    -- also, check that the Trigger didnt stop the DELETE from being executed on t1
    SELECT count(*) FROM t1 WHERE c1 = str_in INTO str_out;
    CALL `stk_unit`.assert_equals(str_out, 0,
            'WTF... the row in t1 is still there!'
        );
END;

Sometimes Triggers are used to add a computed value to the table they belong to, so you may want to check the paragraph about Virtual Columns.

How do I test Stored Routines?

Tesing a Stored Function is easy. Suppose you developed a UDF which returns a factorial:

CALL `stk_unit`.assert_equals(factorial(5), 120, NULL);

Let's pass NULL as debug message, because default message is good in this case.

Many Stored Procedures don't return any value, but you can test their side effectes, like INSERTs or changes in session variables.

How do I test UDF's?

Testing UDF's is exactly the same as testing Stored Funcions. Suppose you developed a UDF which returns a factorial:

CALL `stk_unit`.assert_equals(factorial(5), 120, NULL);

Let's pass NULL as debug message, because default message is good in this case.

How do I test Storage Engines?

Probably you want to use a different Storage Engine for some of your tables. Or maybe you are a Storage Engine developer. In the first case, you can put in a test the statements you perform on that table. In the second case you can write a test containing the operations that your SE is supposed to support (basic commands, use of index, etc).

Is there an easy way to check if my application is correctly installed?

Of course! Syppose that your install script should create a table and a view, and insert 3 rows in the table. You can use these assertions:

CALL stk_unit.test_assert_table_exists('my_db', 'my_tab', 'Table has not been created');
CALL stk_unit.assert_view_exists('my_db', 'my_view', 'View has not been created');
CALL stk_unit.assert_row_count('my_db', 'my_tab', 3, 'Rows have not been inserted');

Similar assertion procedures are avaible to test Triggers, Stored Procedures, Events, etc.

To test an upgrade script, you may want to check if a column (doesn't) exists:

CALL stk_unit.assert_column_exists('my_db', 'my_tab', 'new_column', 'my_tab has not been altered');

Also, you can test uninstall scripts:

CALL stk_unit.test_assert_table_not_exists('my_db', 'my_tab', 'Table has not been dropped');

How do I test server upgrades and configuration changes?

First, you should extensively test your databases via proper TCs. If you do this, everytime you upgrade the server, upgrade a plugin, or make a change in configuration, you can run the _all TS (which runs all existing TCs) and check that everything works as expected.

What are Pro's and Con's of STK/Unit compared to MyTAP?

MyTAP is a good software, but it's older and simpler than STK/Unit. However, we'll try to list STK/Unit con's too. If our potential users tell us that those are important, we'll develope what thet need.

Cons:

  • MyTAP's output follows the TAP protocol
  • MyTAP seem to work better with older versions
  • MyTAP syntax is less verbose

Pros:

  • STK/Unit is fault-tolerant, MyTAP is not
  • With STK/Unit you can test that some behaviours raise (not) an error/warning
  • STK/Unit knows what a Test Suite is
  • STK/Unit's output is more human-readable, and HTML format can be used
  • STK/Unit has many high-level assert procedures
  • STK/Unit can be configured
  • Test results are written in SQL tables which can be queried by an application.

What can not be done with STK/Unit?

Known limitations:

  • STK/Unit is not a debugger
  • White box testing can not be done
  • STK/Unit uses only 1 thread. Can't test concurrency, high load problems, etc
  • There is not a reliable way to fire an Event, so you can test an Event. However the Event's code can be moved into a Stored Procedure! The Procedure will be called by the Event and tested by STK/Unit. It isn't a dirty solution.
  • Doesn't make coffee. However we know that such a feature would be appreciate by developers and DBA's, so we'll try to implement it :)

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License