STK/Unit Manual

About this manual

License

The owner of this manual is Federico Razzoli, also creator of STK/Unit.
This manual is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

Terminology

The following terms are used in this document:

  • Assert: an assertion which must be satisfied to pass the Base Test.
  • Exception: SQL Error or Warning.
  • Expectation: sometimes a Base Test passes if it terminates with an Exception.
  • Base Test (BT): a single Stored Routine which is expected to contain one or more Asserts.
  • Test Case (TC): a database which contains one or more Base Tests.
  • Test Suite (TS): a Stored Routine which is supposed to execute one or more TCs.
  • Magic Routines (MR): Stored Routines which are automagically executed by STK/Unit during the testing process.
  • Test Run (TR): a single execution of a Test Case.
  • Test Results: Output of a Test Run, written in a table in the stk_unit database.
  • Obsolete Results: A Test Result is obsolete when a most recent result exists for the same TC or TS

STK/Unit Overview

License

The owners of STK/Unit are Federico Razzoli, also owner of this manual.
STK/Unit is licensed under a GNU Affero General Public License, version 3. You should find it in the COPYING file.

System Requirements

STK/Unit is being mainly developed and test with the latest stable version of MariaDB. The best supported versions are currently 5.5 and 10.0. STK/Unit works on MariaDB 5.3, 5.2, 5.1, with some small flaws, which are documented in the "Limitarions" paragraph.

STK/Unit also works with MySQL 5.1 (with the same minor problems as MariaDB 5.1 and .2) and, since 1.0-rc3, 5.5 and 5.6.

Should work with MySQL 5.0 with few deletions from the code, but it's not really tested yet - in the future, that old version will be fully supported.

Percona Server should also work fine, as long as corresponding MySQL version is supported.

InnoDB (or Percona XtraDB) must not be disabled; on MariaDB, Aria is used, too. No other plugins or Storage Engines are required. The operating system is irrilevant.

Server Configuration

You must be sure that the unique_checks system variable is OFF, when you call config_set(). You can still run your tests with unique_checks set to ON, but if config_set() is invoked, that variable must be set to OFF before the call.

The local and global values of the following variables do not affect the STK/Unit (but can still affect the tests you write):

  • storage_engine
  • sql_mode
  • sql_warnings
  • innodb_strict_mode
  • autocommit
  • foreign_key_checks (since 1.0-rc4)
  • max_sp_recursion_depth

Risks

The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

STK/Unit should always run tests in a development environment, not in production. This allows tests to modify data or database structures without worries. Special attention should be payed when using the assert_sql_ok() Routine, which executes an arbitrary statement.

At the time of this writing, we know of no bugs that could cause serious harm to users.

The authoritative source for updated information is always the online issue tracking system. Issues that affect this tool will be marked as such. You can see a list of such issues at the following URL: https://bugs.launchpad.net/stk-unit

Downloads

STK/Unit project is hosted by Launchpad. You can download the packages or clone the repository from the project home: https://launchpad.net/stk-unit

Tests

STK/Unit is not scientifically tested yet, because it cannot completely test itself. That achievement will probably be in version 2.0.

At present, STK/Unit is tested by the stk_unit TS, which calls the following TCs:

test_stk_unit
Some core Routines are tested, but not all.
test_stk_unit_assertions
Tests that all asserts trigger a Pass when they should, but Fails are not tested.

What is STK/Unit?

STK/Unit is a Unit Testing framework for MariaDB, MySQL and Percona Server. It is inspired by JUnit and SimpleTest. STK/Unit is designed to test any object in a Database: not only Stored Programs, but also Tables, Views, Virtual Columns, etc.

STK/Unit supports Test Cases and Test Suites. Base Tests can trigger Passes, Fails, and Exceptions.

STK/Unit writes the test results into tables. Those results can easily be read using some views. Another convenient way to read the results is getting them as a string, which can be formatted as plain text or HTML.

Notes about older STK/Unit versions:

  • Before STK/Unit 1.0rc-2, MySQL was not supported at all.
  • In STK/Unit 1.0rc2, MySQL 5.1 worked if you replaced 'Aria' with 'MyISAM'.
  • Before STK/Unit 1.0rc-3, MySQL MySQL 5.1, 5.5 and 5.6 was not supported.

STK/Unit Installation

How to install

First, you have to decide what to install, and then you execute the file(s) which contains what you want to install.

Choosing what you want

In the sql directory, there are stk_unit.sql and meta.sql files. They batch files containing the SQL statements (CREATE PROCEDURE, etc) needed to create the STK/Unit framework in your server. No other files are required for STK/Unit to properly work.

In the sql/test directory, there are 2 subdirectories containings files which can be used to test (parts of) STK/Unit:

  • suite dir
    • stk_unit.sql launches all Test Cases
  • case dir
    • test_stk_unit.sql tests some core Stored Routines
    • test_assertions.sql tests all asserts, passing them some conditions that are known to be TRUE

These files are only required if you suspect you hit a bug in STK/Unit and want to check it, or if you want to modify STK/Unit's code (and possibly share your changes with the world). However, they also may serve as an example for creating Test Cases and Test Suites.

Executing SQL files

Installing an SQL file means to execute it.

You can use your favorite GUI (phpMyAdmin, HeidiSQL…). Just copy the contents of the file(s) into the program, and click run/execute/whatever.

Or you can use the command line client.

On a GNU/Linux shell, execute:
mysql < file_name.sql

On Microsoft DOS prompt, execute:
mysql -e "source file_name.sql"

Older versions

Since 1.0-rc3, no further action is necessary to install STK/Unit on MySQL. Befor 1.0-rc3, it was necessary to replace all occurrences of 'Aria' with 'MyISAM' (2 occurrences; all text editors should support automatic replacing).

How to uninstall

To uninstall STK/Unit, just DROP the following DBs:

  • stk_unit
  • stk_suite
  • test_stk_unit - if you installed this test
  • test_stk_unit_assertions - if you installed this test

You may also want to DROP the meta_schema database, but it is meant to be used by any tool, not just STK/Unit. So, keeping it is recommended.

Note that deleting stk_suite you will delete all the Test Suites you created.

Usage

Namespaces

All STK projects use __stk_ prefix for all their identifiers. STK/Unit adds an additional subprefix: u_. That prefixes are used for:

  • Databases
  • User variables
  • Prepared Statements
  • Names of the locks acquired with GET_LOCK()

How to get information about existing tests

There are some VIEWs in stk_unit that follow the style of the information_schema virtual database, and can be used to get info about existing test objects:

  • test_case - contains information about existing Test Cases
  • test_suite - contains information about existing Test Suites
  • base_test - contains information about existing Base Tests
  • test_case_helpers - contains information about Stored Routines which are located in a TC but is not a BT. They can be Magic

Routines, Stored Routines used by some BTs, or other Stored Routines which for some reason are not recognized as BTs (for example, because their names don't start with 'test_').

How to run an existing Test Case

To run a TC, use the test_case_run() Stored Procedure:

void stk_unit.test_case_run(tc_name)

tc_name is the name of the TC (database) you want to run.

You can try with test_stk_unit_assertions, if you installed it:

CALL stk_unit. test_case_run('test_stk_unit_assertions');

How to run an existing Test Suite

To run a TS, use the test_suite_run() Stored Procedure:

void stk_unit.test_suite_run(ts_name)

ts_name is the name of the TS (Stored Procedure in the stk_suite database) you want to run.

You can try the stk_unit TS, if you installed it:

CALL stk_unit.test_suite_run('stk_unit');

How to check the results from a Test Run

After a Test Run, usually you want to check the Test Results. You can use one of the convenient Routines which return a descriptive string, which can be easily read from every client; or you can examine the results in details, querying the results VIEWs in the stk_unit database.

Using string Routines

The most convenient and fast way to read the results of a TC or a TS, from any client (including the mysql client), is calling the test_case_report() or test_suite_report() Routines:

void test_case_report(IN tc_name, INOUT results)
void test_suite_report(IN tc_name, INOUT results)

The results variable is dinamically composed and it's queried within the procedure, so your client should show it automatically. If it doesn't, just SELECT the variable.

The results variable contains:

  • A summary of the results: number of Passes, number of Fails, and number of Exceptions.
  • The details about the Fails and the Exceptions, if any.

The results are shown as text if the out_format Configuration Option is set to 'text' (default), or in HTML if it's set to 'html'.

If you only want to get the summary (not the details about Fail and Exceptions)you can use:

string test_run_summary(`p_tr_type` CHAR(2), `p_tr_name` CHAR(64))

p_tr_type must be 'tc' for Test Cases or 'ts' for Test Suites (case insensitive).
p_tr_name is the name of the TC or TS.

Understanding human-readable output

The test_case_summary() and test_suite_summary() functions return a human-readable string similar to the following:

Test Suite: stk_unit
Id: 2
Completed: YES
89 passes, 0 fails, 0 exceptions

The first line contains the type (Test Suite) and the name (stk_unit) of the test. The second line contains the test id. The third line tell wether the test has been completed or not. If it is not, it is possible that it is still running, but it also could have been interrupted. Finally, the last line tells the number of passes, fails and exceptions. Normally, if Fails or Exceptions are more than 0, the developers work is not finished.

The above example is also part of the test_case_report() and test_suite_report() Routines, as well as tc() and ts() shortcuts. But, if there are some errors, these Routines show datails about what gone wrong, not just a numeric summary. Here's an example:

Test Suite: my_suite
Id: 3
Completed: YES
88 passes, 2 fails, 1 exceptions

FAIL: `test_example_tc`.`test_this_fails_twice` [3] - This is expect to fail :)
FAIL: `test_example_tc`.`test_this_fails_twice` [5] - Custom comment
EXCEPTION: `test_another_tc`.`test_example_exception` [3] - Uncatched Exception

After the summary, there is a blank line. Then, all lines contain details about a Fail or an Exception. The first information is 'FAIL' or 'EXCEPTION'. Then, there is the name of the TC in which the problem occurred. Then, there is the name of the BT (Stored Procedure) in which the problem occurred. To identify the statement which triggered a Fail or Exception, the last two information are used.

If the problem is a Fail, the tester needs to know which assertion failed. The number within square brackets is the assertion's progressive number (starting from 1). After the dash, there is a comment which should provide additional details which are useful to find the bug. It can be a default comment, or it can be a message written by the test author.

If the problem is an Exception, the progressive number is the number of assertions which triggered a Pass + 1. So, in the above example, the Exception occurred after the third assertion. The comment at the end of the line tells wether the problem is always 'Uncatched Exception'.

Finally, a Fail message could be 'Expected Exception'. In that name it is not caused by an assertion, so its progressive number should be used as Exceptions progressive numbers.

Querying results views

You can find a summary of the last executed Test Run in the stk_unit.last_test_summary VIEW. If you do not use a GUI, this is the fastest way to check if all tests passed. There, you can find the following BIGINT UNSIGNED fields:

  • total: Number of executed Asserts. Unexpected Exceptions are counted here, as well as Expected Exceptions which have not been thrown.
  • passed: Number of passed Asserts. Of course, you will hope that this value equals to Total.
  • failed: Failed Asserts. Expected Exceptions which have not been thrown are counted here. 0 is the best possible value.
  • exceptions: Unexpected Exceptions. 0 is the best possible value.

If Failes or Exceptions are > 0, probably you want to know what was wrong. You will need to check the stk_unit.last_test_results VIEW or stk_unit.last_test_results_bad (which is usually more usable, as it only includes Failed tests and Exceptions). There, you will find the following fields:

  • id: Primary Key.
  • timestamp: a timestamp of the moment when the Base Test execution was written into a table.
  • test_run: id of the Test Run.
  • test_case: TC name.
  • test_name: Base Test name.
  • assert_num: Since every Base Test could execute more than 1 assert, this prog id helps you to understand which Assert matches a given record.
  • results: Base Test result. May be one of: 'fail', 'pass' or 'exception'.
  • msg: If the Assert failed or is an Exception, in this field you may find a debug message written by the TC's author.

There are some similar VIEWs which allows you to see more data: stk_unit.recent_test_summary, stk_unit.recent_test_results and stk_unit.recent_test_results_bad. Their fields are the same that are in the last_* VIEWs. However, the recent_* VIEWs contain data about the most recent Test Run of every existing Test Cases. This means that, even if you execute test_assertions and test_stk_unit several times, in recent_* VIEWs you will only see the last execution of both the TCs.

TODO: describe all missing tables/views

Shortcuts

If you want to read the results on your client in a human-readable form, there are two shortcuts you can use:

void tc(IN tc_name) -- for Test Cases
void ts(IN ts_name) -- for Test Suite

They are short forms for test_*_run() + test_*_show(). But in case you want to read the results from the views, or if you don't want to read the results now, don't use these shortcuts to avoid useless operations.

In some contexts, '' (empty string) and NULL, if passed as parameters, mean 'last executed TC' or 'last executed TS'.

  • test_case_run('') means 'run the last executed TC again'. This works even if you executed a TS, after the last TC.
  • test_suite_run('') means 'run the last executed TS again'. This works even if you executed a TC, after the last TS.
  • test_case_report('') means 'get the report for the last executed TC'.
  • test_suite_report('') means 'get the report for the last executed TS'.
  • test_case_summary('') means 'get the summary for the last executed TC'.
  • test_suite_summary('') means 'get the summary for the last executed TS'.

It would be easy to implement the same feature for obsolete results cleaning Routines. However, we don't want our users to delete old but useful TRs by mistake. It's probably better that, if one need to manually delete obsolete TRs (which is not a common situation), she needs to explicitly write its name.

How to clean obsolete test results

A test result for a TC or a TS is considered as obsolete when a most recent result exists for that TC or TS. Obsolete results can usually be deleted to make tables smaller, thought you can keep them if they have some meaning for you. For this reason, you can let STK/Unit to automatically delete obsolete results (auto_clean), or you can manually delete them manually.

auto_clean

The auto_clean operation is performed every time you run a TC or a TS, after all testing operations.

To have all obsolete TRs automatically deleted, set the auto_clean Configuration Option to '2'. Please note that, if you preserved some old TRs, by setting auto_clean to '2' you will delete old TRs. If this is not what you want, you can set auto_clean to '1', instead.

You can have all versions of a TR deleted every time it is executed. To do so, you can set auto_clean to '1'. Everytime you will execute a TR, its old versions will be deleted, but other obsolete TRs will survive.

Set auto_increment to '0' to disable.

Default value is '2'.

Usually, you set auto_clean globally. However, there are 2 scenarios in which you may want to set this option both globally and within a TC.

  • You may want to delete most of your obsolete TRs, but preserve obsolete results of a single (set of) TC. In this case, you can set auto_clean to '1' globally, but set it to '0' within one TC, in the before_all_tests MR. This will preserve that TC's obsolete results. Please, note that the auto_clean should never be set to '2' in this case, nor globally nor locally, even if '2' is the default value.
  • You may want to preserve most of your obsolete TRs, but delete obsolete results of a single (set of) TC. In this case, you can set auto_clean to '0' globally, but set it to '1' within one TC, in the before_all_tests MR. This will delete that TC's obsolete results.

Manual cleaning

This Procedure deletes all results:

void results_clean_all();

This Procedure only deletes obsolete versions of a specified TR:

void results_clean_tr(tr_type, tr_name);

tr_type is the type of TR: 'tc' or 'ts' (case insensitive). tr_name is the name of the TC or TS.

How to write a new Test Case

A Test Case is a database containing BTs. Its name should start with 'test_' (lowercase).

A Base Test is a Stored Procedure contained in a Test Case. When a TC is run, all its BTs are automatically run. If a TC does not contain any BTs, an error is produced (not supported before MariaDB 5.5).

A TC can contain Stored Routines that are not BTs: they can be Magic Routines or routines called by some BTs. For a Stored Routine to be recognized as a BT, it must:

  • have a name starting with 'test_' (lowercase);
  • be a Stored Procedure (not a function);
  • accept no parameters (this check is not performed before MariaDB 5.5, but trying to execute the TC will return an error).

A BT can perform any number of Asserts, each will trigger a Pass or a Fail. A BT can also terminate with an Exception.

How to write a new Test Suite

Test Suites are Stored Procedures which don't accept any parameter, defined in the stk_suite database. Their name should never start with a '_' character, to avoid possible conflicts with predefined TSs. A TS can call any number of TCs and perform any auxiliary operation, if necessary.

There is a predefined TS called '_all', which calls all existing TCs.

In the future, more predefined TSs could be added, and their names will always start with '_'.

Magic Routines

Test Cases may contain some Magic Routines (MR) which are automagically called during a Test Run:

set_up(), tear_down()
set_up() is called before each Base Test, and tear_down() is called after each Base Test. Tipically, they insert or restore some test data which could be modified by all the Base Tests.

before_all_tests(), after_all_tests()
before_all_tests() is called only once before running the Test Case, and after_all_tests() is called only once at the end of the Test Case. before_all_tests() can prepare test data which will never be modified by Base Tests, and therefore need not to be prepared many times by set_up(). after_all_tests() can be used to delete those data. They can also be used to modify and restore some MariaDB server variables.

Magic Routines are substantially what in xUnit terminology is called fixtures. But the STK/Unit MRs before_all_tests() and after_all_tests() don't match any xUnit fixtures.

Please, note that MR's can not use asserts and expectation, and they shouldn't generate errors which they don't handle by themselves.

Expectations

Only one Expectation can be set by a BT. If you try to set more than 1 Expectations, an error will be produced (since MariaDB 5.5).
There are 2 types of Expectations: 'ignore' and 'expect'.

  • ignore: Means that the BT can terminate with an error. If it happens, no Exception is added to the Test Results. Simply, it is not relevant.
  • expect: Means that the BT must terminate with an error. If it does not happen, an Exception will be added to the Test Results.

In any case, the Exception (even if it is not expected) will not stop the Test Run.

Note: If you handle the Exception within the tested method or within the BT (unless you SIGNAL/RESIGNAL it), the Exception will not satisfy any Expectation.

Currently there is not an easy way to expect a particular Exception (based on SQLSTATE, error code, error message…), so you can only except/ignore an Exception of any type. There are 2 Stored Procedures to do so:

void ignore_all_exceptions()

void expect_any_exception()

TODO: explain how to use assertTrue to check if a particular error happens

Asserts

Asserts are Stored Procedures, located in the stk_unit database. All Asserts have msg as their last argument. If they fail, msg will be written in the Test Results.

Note that asserts do not handle error conditions. For example, if you use assert_row_exists() and the specified table does not exists, then an Exception will be triggered, not a Fail.

pass()

Triggers a pass. Makes sense after some operations, if you only want to test that they do not produce an error.

fail(CHAR(255) msg)

Triggers a fail, with the specified message. Makes sense within an IF, but this is not a good way to trigger a fail.

assert_true(TEXT val, CHAR(255) msg)

Passes if val is <> 0.

assert_false(TEXT val, CHAR(255) msg)

Passes if val is = 0.

void assert_null(TEXT val, CHAR(255) msg)

Passes if val IS NULL.

void assert_not_null(TEXT val, CHAR(255) msg)

Passes if val IS NOT NULL.

void assert_equals(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 = val2.

void assert_not_equals(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 <> val2.

void assert_almost_equal(val1 DOUBLE, val2 DOUBLE, prec TINYINT UNSIGNED, msg CHAR(255))

Passes if val1 and val2 are equal until a given decimal cipher. The decimal precision is determined by prec.

void assert_not_almost_equal(val1 DOUBLE, val2 DOUBLE, prec TINYINT UNSIGNED, msg CHAR(255))

Passes if val1 and val2 are different until a given decimal cipher. The decimal precision is determined by prec.

void assert_length(txt TEXT, charlen MEDIUMINT SIGNED, msg CHAR(255))

Passes if txt is charlen characters long.

void assert_not_length(txt TEXT, charlen MEDIUMINT SIGNED, msg CHAR(255))

Passes if txt is not charlen characters long.

void assert_length_between(txt TEXT, charlen_min MEDIUMINT SIGNED, charlen_max MEDIUMINT UNSIGNED, msg CHAR(255))

Passes if txt has a length (in characters) between charlen_min and charlen_max.

void assert_length_not_between(txt TEXT, charlen_min MEDIUMINT SIGNED, charlen_max MEDIUMINT UNSIGNED, msg CHAR(255))

Passes if txt has a length (in characters) which is not between charlen_min and charlen_max.

void assert_like(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 LIKE val2.

void assert_not_like(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 NOT LIKE val2.

void assert_like_with_escape(TEXT val1, TEXT val2, CHAR(1) chr_esc, CHAR(255) msg)

Passes if val1 LIKE val2 ESCAPE chr_esc.

void assert_not_like_with_escape(TEXT val1, TEXT val2, CHAR(1) chr_esc, CHAR(255) msg)

Passes if val1 NOT LIKE val2 ESCAPE chr_esc.

void assert_regexp(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 REGEXP val2.

void assert_not_regexp(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 NOT REGEXP val2.

void assert_regexp_binary(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 REGEXP BINARY val2.

void assert_not_regexp_binary(TEXT val1, TEXT val2, CHAR(255) msg)

Passes if val1 NOT REGEXP BINARY val2.

void assert_table_exists(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if table tab exists in db. Can be used to test installation Routinesor scripts.

void assert_table_not_exists(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if table tab does not exist in db. Can be used to test uninstallation Routines or scripts.

void assert_view_exists(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if view tab exists in db. Can be used to test installation Routines or scripts.

void assert_view_not_exists(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if view tab does not exist in db. Can be used to test uninstallation Routines or scripts.

void assert_routine_exists(IN db CHAR(64), IN sr_name CHAR(64), IN msg CHAR(255))

Passes if Routine sr_name exists in db. Can be used to test installation scripts.

void assert_routine_not_exists(IN db CHAR(64), IN sr_name CHAR(64), IN msg CHAR(255))

Passes if Routine sr_name does not exist in db. Can be used to test uninstallation scripts.

void assert_event_exists(IN db CHAR(64), IN ev CHAR(64), IN msg CHAR(255))

Passes if Event ev exists in db. Can be used to test installation scripts.

void assert_event_not_exists(IN db CHAR(64), IN ev CHAR(64), IN msg CHAR(255))

Passes if Event ev does not exist in db. Can be used to test uninstallation scripts.

void assert_trigger_exists(IN db CHAR(64), IN trig CHAR(64), IN msg CHAR(255))

Passes if Trigger trig exists for table tab in db. Can be used to test installation Routines or scripts.

void assert_trigger_not_exists(IN db CHAR(64), IN trig CHAR(64), IN msg CHAR(255))

Passes if Trigger trig does not exist in db. Can be used to test uninstallation Routines or scripts.

void assert_column_exists(IN db CHAR(64), IN tab CHAR(64), IN col CHAR(64), IN msg CHAR(255))

Passes if column col exists in table tab in db. Can be used to test upgrade Routines or scripts.

void assert_column_not_exists(IN db CHAR(64), IN tab CHAR(64), IN col CHAR(64), IN msg CHAR(255))

Passes if column col does not exist in table tab in db. Can be used to test upgrade Routines or scripts.

void assert_row_exists(IN db CHAR(64), IN tab CHAR(64), IN col CHAR(64), IN val TEXT, IN msg CHAR(255))

Passes if the specified col = val exists in table tab in db. Can be used to test data insertion.

void assert_row_not_exists(IN db CHAR(64), IN tab CHAR(64), IN col CHAR(64), IN val TEXT, IN msg CHAR(255))

Passes if the specified col = val does not exist in table tab in db. Can be used to test data deletion.

void assert_rows_count(IN db CHAR(64), IN tab CHAR(64), IN num BIGINT, IN msg CHAR(255)

Passes if table tab in db contains exactly num rows. Can be used to test data insertion.

void assert_table_empty(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if table tab in db is empty. It's a shortcut for assert_rows_count() with num parameter set to 0. Can be used to test data cleaning.

void assert_table_not_empty(IN db CHAR(64), IN tab CHAR(64), IN msg CHAR(255))

Passes if table tab in db contains at least one row. Can be used to test data insertion.

assert_field_count_distinct(db, tab, col, num, msg)

Passes if specified col in tab in db has num DISTINCT values.

assert_field_min(db, tab, col, num, msg)

Passes if MAX(col) in tab in db returns num.

assert_field_max(db, tab, col, num, msg)

Passes if MIN(col) in tab in db returns num.

assert_field_avg(db, tab, col, num, msg)

Passes if AVG(col) in tab in db returns num.

assert_sql_ok(stmt, msg)

stmt is a SQL statement, passed as a string. It will be executed as a Prepared Statement, so be sure you are passing a command which can be PREPAREd, and which doesn't have side effects.
Starting from 1.0-rc2, if stmt is in the form 'SELECT …', it will be automatically transformed to 'DO (SELECT …);', to avoid sending an extra resultset to the client. However, this transformation cannot be done with SHOW statements, or administrative statements which generate a resutset and support prepared statements. If generating an extra resultset is a problem for you, you should avoid passing those statements to assert_sql_ok() - which shouldn't be a problem.
The assert passes if passed SQL statement generates no error.

Configuring STK/Unit

There are some Configuration Options you can set to modify the behavior of STK/Unit. All Configuration Options exist at 2 levels: a global level, and a TC-specific level. If you modify an option manually, you change its behavior at a global level. If you change it within a TC, you modify the option only for the current TC.

To modify an option, you can use:

void config_set(option_name, option_value)

option_name is the name of the option. option_value is its value, passed as a ASCII string. For pseudo-boolean values, possible values are '0', '1', 'on', 'off', 'yes', 'no', 'true', 'false'; '' and NULL are not valid pseudo-booleans, because they can easily be used by mistake. All values (string or boolean) are case-insensitive. If option_name is not an existing option, an error is generated.

Avaible Configuration Options are:

dbug
Allowed values: pseudo-booleans; if it's true, print some debug info in a table, and stop execution if an error is generated by STK/Unit's code, or a MR.
show_err
Allowed values: pseudo-booleans; stop execution if an error is generated by a TS or TC's code, but not by a MR.
out_format
Allowed values: 'text', 'html'; it's the format for the output of test_case_show(), test_suite_show() and test_run_show() functions. Note that setting show_err to 1 has no effect with servers older than 5.4, because RESIGNAL statement is not supported.
auto_clean
Automatically delete obsolete TRs. See "auto_clean" paragraph for details. Allowed values are: '0' (as string), '1' and '2'. Default is '2'.

Writing your own asserts

Writting a new Assert is easy: just copy and modify stk_unit.assertTrue(), which is the simplest assert (or another one).

We recomend that your assert Procedures call stk_unit.assert() passing a "boolean" value. This way you can be sure that Passes and Fails will be propertly written to the Test Results.

Your asserts can be located in any database you like. However, we recomend you not use stk_unit to avoid possible conflicts in future versions.

If you want to share your asserts with the world, feel free to contact us and let us know. We will add a link to your library.

STK/Unit Errors

All errors generated by STK/Unit should have SQLSTATE = '45000'. If an error has a different SQLSTATE, it is directly generated by the server and probably unexpected/unhandled.

Error messages start with the following pattern:
[db_name.routine_name]

In STK/Unit, server's default Error Code for '45000' SQLSTATE was used. Since STK/Unit 1.1, the following rules are followed for Error Codes.

Error Codes used by STK start with 31000. Error Codes used by STK/Unit lie between 31200 and 31299.

Here's the list:

  • 31201: Test Case not found
  • 31202: Test Suite not found
  • 31203: No Base Tests found

Limitations, Design Deficencies and Known Issues

Things that we may decide to fix, if someone ask for it:

  • A TS cannot call another TS (or itself)
  • unique_checks system variable must be OFF, at least when you call config_set(). See System Configuration paragraph.

Things are not likely to be fixed:

  • Before MariaDB 5.5, DDL assertions (*_exists, *_not_exists) work, but a few of them generate an exra-exception. We are not sure wether the problem is in STK/Unit or in the server.
  • Test Case names, Test Suite names, and Base Test names have a maximum length of 64 chars (like most MariaDB/MySQL identifiers).
  • STK/Unit uses a few User Variables and Prepared Statements. To avoid name conflicts, avoid the '__stk_' prefix for your Prepared Statements that are defined in the database objects being tested. (STK/Unit uses __stk_u_, but other STK prokects use other prefixes which start with __stk_)

What now?

Check the following documents:


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