STK/Unit 1.0 Release Candidate 3 released

2013-04-05

STK/Unit 1.0 Release Candidate 3 is out!

STK/Unit 1.0 Release Candidate 3 should be a stable version. STK/Unit main author uses the software since 2010, but heavily recoded when he decided to make it public. All old tests he wrote still work.

Since the first public release, rc1, we only received come private feedback. That has been valuable, because helped us to identify bugs and flaws. We really wish to receive some public feedback before declaring STK/Unit production-ready.

This RC was not planned. However, we were able to expand the list of supported MySQL and MariaDB versions with a very low delta from the previous release. This should help us to get some user feedbacks, and we believe that we are not introducing any bugs.

Now, supported platforms are:

  • MariaDB 10.0, 5.5
  • MariaDB 5.1, 5.2, 5.3 (with some documented issues: a few DDL-specific assert functions could generate obscure exceptions)
  • MySQL 5.6, 5.5
  • MySQL 5.1 (with same problems MariaDB 5.1 has)

Here is a list of changes in 1.0 Release Candidate 3:

  • Fix bug #1162515 (test_stk_unit aborts on MySQL 5.5)
  • Modifying SQL files for installation on Oracle MySQL is no longer necessary
  • Commented BTs in test_stk_unit_assertions contained errors
  • Minor code cleaning

Documentation and Downloads for STK/Unit and others STK tools to come, are avaible from here:
http://stk.wikidot.com/

The public Mailing List can be found here:
https://launchpad.net/~stk-discuss

The STK team encourage you to try STK/Unit in your databases, report any bugs you may find, ask for help in the list when needed, and let us know any comments. Your feedback is valuable for us!

The STK Team

Advertisements

Quoting MySQL & MariaDB identifiers

Versione italiana

MySQL/MariaDB identifiers are names for databases, tables, columns, etc. They can be quoted with `backticks` (AKA backquotes), and in that case they can contain characters which are normally illegal for identifiers (even spaces or the backtick itself), or they can be reserved words. Both quoting and not quoting cause some problems.

If you don’t quote names you will need to avoid illegal chars and reserved words – which is a good practice, anyway. But when you upgrade MariaDB, the new version could add some reserved words.

If you quote names, you should be sure to do it everywhere. It is a good practice, but if a developer doesn’t use backticks (or forgets to do it), he may see strange errors.

If you use keywords as identifiers and don’t quote them, you will probably receive an error 1064 (SQLSTATE 42000):

mysql> SELECT * FROM insert;
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 'insert' at line 1

The bad identifier is usually the first word in the reported string (see example above), but can also be the last word before the beginning of the reported code part:

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

In theory, a query involving unquoted identifiers which are reserved words, could even do something different from what you think and report no errors.

Another caveat: even when you quote all identifiers, avoid using the same name for a column and for a local variable in a Stored Routine. While MariaDB understands clauses like: WHERE `id` = `id`, this can cause a lot of confusion.

SQL tools

Here are 2 Stored Routines that can probably be useful. For both the routines, I’ll include a Base Test for STK/Unit. The Stored Routines will go into a DB called lib, so the BTs will go into a Test Case called test_lib.

The first Function quotes an identifier for inclusion in a SQL string (which can be PREPAREd and EXECUTEd). It’s similar to the QUOTE() built-in function, that works for strings.

CREATE FUNCTION `lib`.`quote_name`(`id` TEXT)
    RETURNS TEXT
    DETERMINISTIC
    NO SQL
    LANGUAGE SQL
    COMMENT 'Return a quoted identifier (if NULL, id is empty)'
BEGIN
    IF `id` IS NULL THEN
        RETURN '``';
    ELSE
        RETURN CONCAT('`', REPLACE(`id`, '`', '``'), '`');
    END IF;
END;
 
CREATE PROCEDURE `test_lib`.`test_quote_name`()
    LANGUAGE SQL
    COMMENT 'Test quote_name()'
BEGIN
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x'), '`x`', 'Incorrect quoting');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x`y'), '`x``y`', 'Incorrect escape');
 
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(''), '``', 'Empty name expected');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(NULL), '``', 'For NULL value, empty name should be returned');
END;

If you need to verify a single name on the fly, the easiest way is probably:
SELECT 0 AS [name];
The following Procedure, however, is meant to be used inside Stored Programs. It can be used to know if a string is a valid name. It executes the above query (as a Prepared Statement, because it must be composed dynamically) and checks wether an error is generated.

CREATE PROCEDURE `lib`.`is_valid_name`(IN `id` TEXT, OUT `ret` BOOL)
    NO SQL
    LANGUAGE SQL
    COMMENT 'set `ret` to TRUE if id is valid name, else FALSE'
BEGIN
    -- error in query
    DECLARE EXIT HANDLER
        FOR 1064
    BEGIN
        SET `ret` = FALSE;
    END;
 
    SET @sql_query = CONCAT('DO (SELECT 0 AS ', `id`, ');');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
    SET `ret` = TRUE;
END;
 
CREATE PROCEDURE `test_lib`.`test_is_valid_name`()
	LANGUAGE SQL
	COMMENT 'Test quote_name()'
BEGIN
    CALL `lib`.`is_valid_name`('valid_name', @r);
    CALL `stk_unit`.`assert_true`(@r, 'Specified name is valid');
 
    CALL`lib`.`is_valid_name`('SELECT', @r);
    CALL `stk_unit`.`assert_false`(@r, 'SELECT is a keyword');
    CALL `lib`.`is_valid_name`('a a', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Valid names cant contain spaces');
    CALL `lib`.`is_valid_name`('', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Empty name is not valid');
END;

STK/Unit 1.0 Release Candidate 1 released

Annuncio italiano

STK/Unit 1.0 Release Candidate 1 is out!

STK stands for SQL ToolKit. It’s a family of proects for MariaDB, MySQL and Percona Server. STK/Unit is the first STK project that has been publicly release; more tools will come in the next future. The long-term purpose of STK is making SQL programming much easier and reliable on MariaDB and her sisters.

STK/Unit is a Unit Test framework for MariaDB, entirely written in SQL and inspired by SimpleTest and JUnit. Test Cases and Test Suites written by the user can set a test environment and check that all operations work as expected. The results can be retrieved as a human-readable string, in HTML format, or examined in the tables they are stored in. Both developers and database administrators can benefit from such tests.

Errors in applications can be originated by errors in databases. STK/Unit is designed to mainly test active structures: Stored Routines, Triggers, integrity constraints and Views. But also Tables must use the correct datatypes, column sizes and character sets to be able to contain data from the Real World. And DBMS updates, new plugins or even configuration changes can break the complex, delicate logics of a relational database. But a good set of tests can show any problem as soon as it raises!

STK/Unit is still under development and is expanding the list of supported platform; currently, the folloing are supported:
* MariaDB 5.5 and 10.0 – work good
* MariaDB 5.3, 5.2, 5.1 – with documented minor problems
* MySQL 5.1 – using MyISAM place of Aria, with minor problems (undocumented)

Documentation and Downloads for STK/Unit and others STK tools to come, are avaible from here:
http://stk.wikidot.com/

The public Mailing List can be found here:
https://launchpad.net/~stk-discuss

The STK team encourage you to try STK/Unit in your databases, report any bugs you may find, ask for help in the list when needed, and let us know any comments. Your feedback is valuable for us!

The STK Team