View on GitHub

mysql-unit

Testing framework for stored routines in MySQL

+--------------+---------------+---------+
| Installation | Documentation | Samples |
+--------------+---------------+---------+
1 row in set (0.00 sec)

Download this project as a .zip file Download this project as a tar.gz file

<< Previous chapter (Testing results) | Next chapter (Framework restrictions) >>

 

Built-in routines

To make testing process easier, mysql-unit provides set of built-in functions, which may be used in expressions for tests. Together with testing procedures (see API page) they are built-in routines, which will be installed with mysql-unit.

Built-in functions set consists of three general parts - assert functions, DDL checkers and misc functions. This separation is mostly convention, but it may be useful for structuring inside user-defined checkers and assertions.

Functions index:

Assertion Functions

Assertion function are used for regular assertion checks. Assertion is expectation that some expression has certain value. And, if it hasn't, then SQL Exception 80000 is thrown.

Assertion functions are:

ASSERT

Signature: INTEGER ASSERT(expr VARCHAR(255), val VARCHAR(255))

Description: Basic assertion function. Assert that expr is equal to val. The expr may be valid expression, it will be evaluated by MySQL like any other expression before equality check. However, that expression must not be enclosed by quotes, because that will be treated as string literal.

Examples:

mysql> SELECT ASSERT("foo" REGEXP "[oa]$", 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT ASSERT(2+2, 5) AS test;
ERROR 1644 (80000): Assertion failed

 

 

ASSERT_FALSE

Signature: INTEGER ASSERT_FALSE(expr VARCHAR(255))

Description: Assert that expr is FALSE. Equality is checked with integer type-casting.

Examples:

mysql> SELECT ASSERT_FALSE(10-10) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT ASSERT_FALSE(LOCATE('foo', 'foobar')) AS test;
ERROR 1644 (80000): Assertion that expression is FALSE, failed

 

 

ASSERT_TRUE

Signature: INTEGER ASSERT_TRUE(expr VARCHAR(255))

Description: Assert that expr is TRUE. Equality is checked with integer type-casting.

Examples:

mysql> SELECT ASSERT_TRUE(CHAR_LENGTH('bar')) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT ASSERT_TRUE('') AS test;
ERROR 1644 (80000): Assertion that expression is TRUE, failed

 

DDL Checkers

All DDL (Data Definition Language) checkers are functions, which allow to check, if the corresponding DDL unit - table, view, e.t.c - exists. There is checker for each type of DDL unit and common function, which expects type of DDL unit as a parameter. However, if DDL unit type is unknown, SQL Exception 80500 will be thrown.

CHECK_DDL

Signature: INTEGER CHECK_DDL(unit_type VARCHAR(255), unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of unit_name with type unit_type in unit_db[.unit_table]. Here:

existence

unit_type

Examples:

mysql> SELECT CHECK_DDL('FUNCTION', DATABASE(), '', 'ASSERT', 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL('VIEW', DATABASE(), '', 'baz', 1) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.05 sec)

 

mysql> SELECT CHECK_DDL('SEQUENCE', DATABASE(), '', 'foo', 0) AS test;
ERROR 1644 (80500): Specified DDL unit is not yet supported

 

 

CHECK_DDL_COLUMN

Signature: INTEGER CHECK_DDL_COLUMN(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of column unit_name in unit_db.unit_table. Here:

existence

Examples:

mysql> SELECT CHECK_DDL_COLUMN(DATABASE(), 'TEST_PROCEDURE_ARGUMENTS', 'test_id', 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_COLUMN(DATABASE(), 'TEST_PROCEDURE_ASSERTIONS', 'test_id', 0) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_COLUMN_EXISTS

Signature: INTEGER CHECK_DDL_COLUMN_EXISTS(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255))

Description: Check if column unit_name exists in unit_db.unit_table. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

Examples:

mysql> SELECT CHECK_DDL_COLUMN_EXISTS('', 'TEST_PROCEDURE_ASSERTIONS', 'id') AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

 

mysql> SELECT CHECK_DDL_COLUMN_EXISTS('', 'some-table', 'id') AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_FUNCTION

Signature: INTEGER CHECK_DDL_FUNCTION(unit_db VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of function unit_name in unit_db. Here:

existence

This function can not be used on MySQL standard functions since they are not part of environment, but part of DBMS engine instead.

Examples:

mysql> SELECT CHECK_DDL_FUNCTION(DATABASE(), 'ASSERT_FALSE', 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_FUNCTION(DATABASE(), 'ASSERT_TRUE', 0) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_FUNCTION_EXISTS

Signature: INTEGER CHECK_DDL_FUNCTION_EXISTS(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Check if function unit_name exists in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

This function can not be used on MySQL standard functions since they are not part of environment, but part of DBMS engine instead.

Examples:

mysql> SELECT CHECK_DDL_FUNCTION_EXISTS('', 'ASSERT') AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_FUNCTION_EXISTS('', 'some-function') AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_PROCEDURE

Signature: INTEGER CHECK_DDL_PROCEDURE(unit_db VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of procedure unit_name in unit_db. Here:

existence

Examples:

mysql> SELECT CHECK_DDL_PROCEDURE(DATABASE(), 'TEST_PROCEDURE', 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_PROCEDURE(DATABASE(), 'bar', 1) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_PROCEDURE_EXISTS

Signature: INTEGER CHECK_DDL_PROCEDURE_EXISTS(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Check if procedure unit_name exists in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

Examples:

mysql> SELECT CHECK_DDL_PROCEDURE_EXISTS('', 'ASSERT') AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_PROCEDURE_EXISTS('', 'TEST_FUNCTION_BY_ASSERT') AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_TABLE

Signature: INTEGER CHECK_DDL_TABLE(unit_db VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of table unit_name in unit_db. Here:

existence

Examples:

mysql> SELECT CHECK_DDL_TABLE(DATABASE(), 'TEST_FUNCTION_ASSERTIONS', 1) as test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_TABLE(DATABASE(), 'test_function_assertions', 1) as test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

 

 

CHECK_DDL_TABLE_EXISTS

Signature: INTEGER CHECK_DDL_TABLE_EXISTS(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Check if table unit_name exists in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

Examples:

mysql> SELECT CHECK_DDL_TABLE_EXISTS(DATABASE(), 'TEST_PROCEDURE_ASSERTIONS') as test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

 

mysql> SELECT CHECK_DDL_TABLE_EXISTS(DATABASE(), 'Test_Procedure_Assertions') as test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_TRIGGER

Signature: INTEGER CHECK_DDL_TRIGGER(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of trigger unit_name in unit_db.unit_table. Here:

existence

Examples:

mysql> SELECT CHECK_DDL_TRIGGER(DATABASE(), 'debug', 'testOne', 1) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_TRIGGER(DATABASE(), 'debug', 'testTwo', 1) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

 

 

CHECK_DDL_TRIGGER_EXISTS

Signature: INTEGER CHECK_DDL_TRIGGER_EXISTS(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255))

Description: Check if trigger unit_name exists in unit_db.unit_table. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

Examples:

mysql> SELECT CHECK_DDL_TRIGGER_EXISTS('', 'debug', 'testOneTrigger') AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_TRIGGER_EXISTS('', 'debug', 'testTwoTrigger') AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

 

CHECK_DDL_VIEW

Signature: INTEGER CHECK_DDL_VIEW(unit_db VARCHAR(255), unit_name VARCHAR(255), existence INT)

Description: Check existence of view unit_name in unit_db. Here:

existence

Examples:

mysql> SELECT CHECK_DDL_VIEW(DATABASE(), 'foo', 0) AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT CHECK_DDL_VIEW(DATABASE(), 'bar', 1) AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.02 sec)

 

 

CHECK_DDL_VIEW_EXISTS

Signature: INTEGER CHECK_DDL_VIEW_EXISTS(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Check if view unit_name exists in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage.

Examples:

mysql> SELECT CHECK_DDL_VIEW_EXISTS('', 'baz') AS test;
+------+
| test |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

 

mysql> SELECT !CHECK_DDL_VIEW_EXISTS('', 'feo') AS test;
+------+
| test |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

 

DDL Getters

While DDL checkers are used for checking if the corresponding DDL entity exists, it still is not enough to provide all information about DDL. Because, for example, some procedure test may want to check not only that specific column was created, but also if that column has certain type. For such purposes there is set of functions, which return information about DDL structures.

GET_COLUMN_COMMENT

Signature: VARCHAR GET_COLUMN_COMMENT(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255))

Description: Get comments for column unit_name in unit_db.unit_table. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified column does not exists, this function will throw 80700 SQL Exception.

Examples:

mysql> SELECT GET_COLUMN_COMMENT('', 'debug', 'val') AS value;
+-------+
| value |
+-------+
|       |
+-------+
1 row in set (0.00 sec)

 

mysql> SELECT GET_COLUMN_COMMENT('', 'debug', 'id');
ERROR 1644 (80700): Column `test`.`debug`.`id` does not exist

 

 

GET_COLUMN_FIELD

Signature: VARCHAR GET_COLUMN_FIELD(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255), unit_field VARCHAR(255))

Description: Get unit_field for column unit_name in unit_db.unit_table. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified column does not exists, this function will throw 80700 SQL Exception. For unit_field any arbitrary column DDL property, which is supported by MySQL, is applicable.

Examples:

mysql> SELECT GET_COLUMN_FIELD('', 'debug', 'expr', 'IS_NULLABLE') AS value;
+-------+
| value |
+-------+
| YES   |
+-------+
1 row in set (0.00 sec)

 

mysql> SELECT GET_COLUMN_FIELD('', 'debug', 'expr', 'PRIVILEGES') AS value;
+---------------------------------+
| value                           |
+---------------------------------+
| select,insert,update,references |
+---------------------------------+
1 row in set (0.01 sec)

 

 

GET_COLUMN_TYPE

Signature: VARCHAR GET_COLUMN_TYPE(unit_db VARCHAR(255), unit_table VARCHAR(255), unit_name VARCHAR(255))

Description: Get type for column unit_name in unit_db.unit_table. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified column does not exists, this function will throw 80700 SQL Exception.

Examples:

mysql> SELECT GET_COLUMN_TYPE('', 'debug', 'expr') AS value;
+--------------+
| value        |
+--------------+
| varchar(255) |
+--------------+
1 row in set (0.01 sec)

 

mysql> SELECT GET_COLUMN_TYPE('', 'debug', 'id') AS value;
ERROR 1644 (80700): Column `test`.`debug`.`id` does not exist

 

 

GET_TABLE_COMMENT

Signature: VARCHAR GET_TABLE_COMMENT(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Get comments for table unit_name in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified table does not exists, this function will throw 80700 SQL Exception.

Examples:

mysql> SELECT GET_TABLE_COMMENT('', 'debug') AS value;
+-------+
| value |
+-------+
|       |
+-------+
1 row in set (0.01 sec)

 

mysql> SELECT GET_TABLE_COMMENT('', 'some-table') AS value;
ERROR 1644 (80700): Table `test`.`some-table` does not exist

 

 

GET_TABLE_ENGINE

Signature: VARCHAR GET_TABLE_ENGINE(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Get storage-engine for table unit_name in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified table does not exists, this function will throw 80700 SQL Exception.

Examples:

mysql> SELECT GET_TABLE_ENGINE('', 'debug') AS value;
+--------+
| value  |
+--------+
| InnoDB |
+--------+
1 row in set (0.02 sec)

 

mysql> SELECT GET_TABLE_ENGINE('', 'some-other-table') AS value;
ERROR 1644 (80700): Table `test`.`some-other-table` does not exist

 

 

GET_TABLE_FIELD

Signature: VARCHAR GET_TABLE_FIELD(unit_db VARCHAR(255), unit_name VARCHAR(255), unit_field VARCHAR(255))

Description: Get unit_field for table unit_name in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified table does not exists, this function will throw 80700 SQL Exception. For unit_field any arbitrary table DDL property, which is supported by MySQL, is applicable.

Examples:

mysql> SELECT GET_TABLE_FIELD('', 'debug', 'ROW_FORMAT') AS value;
+---------+
| value   |
+---------+
| Compact |
+---------+
1 row in set (0.00 sec)

 

mysql> SELECT GET_TABLE_FIELD('', 'debug', 'TABLE_COLLATION') AS value;
+-------------------+
| value             |
+-------------------+
| latin1_swedish_ci |
+-------------------+
1 row in set (0.00 sec)

 

 

GET_VIEW_FIELD

Signature: VARCHAR GET_VIEW_FIELD(unit_db VARCHAR(255), unit_name VARCHAR(255), unit_field VARCHAR(255))

Description: Get unit_field for view unit_name in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified table does not exists, this function will throw 80700 SQL Exception. For unit_field any arbitrary view DDL property, which is supported by MySQL, is applicable.

Examples:

mysql> SELECT GET_VIEW_FIELD('', 'view_one', 'CHECK_OPTION') AS value;
+-------+
| value |
+-------+
| NONE  |
+-------+
1 row in set (0.00 sec)

 

mysql> SELECT GET_VIEW_FIELD('', 'view_one', 'SECURITY_TYPE') AS value;
+---------+
| value   |
+---------+
| DEFINER |
+---------+
1 row in set (0.01 sec)

 

 

GET_VIEW_IS_UPDATABLE

Signature: VARCHAR GET_VIEW_IS_UPDATABLE(unit_db VARCHAR(255), unit_name VARCHAR(255))

Description: Get updatable flag for view unit_name in unit_db. Note, that for this function it is possible to pass empty string "" as a unit_db - that will be equal to current database usage. If specified table does not exists, this function will throw 80700 SQL Exception.

Examples:

mysql> SELECT GET_VIEW_IS_UPDATABLE('', 'view_one') AS value;
+-------+
| value |
+-------+
| YES   |
+-------+
1 row in set (0.01 sec)

 

mysql> SELECT GET_VIEW_IS_UPDATABLE('', 'view_two') AS value;
ERROR 1644 (80700): View `test`.`view_two` does not exist

 

Misc Functions

There is also misc functions, which provide some additional information about mysql-unit. This functions normally would not be used in testing expressions, but, however, may be useful for debugging.

MYSQL_UNIT_VERSION

Signature: VARCHAR MYSQL_UNIT_VERSION()

Description: Get version for installed instance of mysql-unit.

Examples:

mysql> SELECT MYSQL_UNIT_VERSION();
+----------------------+
| MYSQL_UNIT_VERSION() |
+----------------------+
| 0.3-beta             |
+----------------------+
1 row in set (0.00 sec)