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

XML and HTML entities

Versione italiana

As everyone should know, some characters should be replaced with entities, if you want to use them in a text, within an XML or (X)HTML document. For example, since tags are usually wrapped by < and > characters , you can’t write and expect that it is simply considered as a text and printed in the browser’s window.

Usually, entities are used to escape meaningful chars. XML has five predefined entities, which are also avaible in HTML:

& &amp;
< &lt;
> &gt;
” &quot;
‘ &apos;

To be sure that the result is what you want, & characters must be replaced before others.

These are the only character that can confuse the client and invalidate a document. Within a parameter, you can even leave < and > unchanged. In other contexts, ” and ‘ can appear.

You can avoid to replace all other “weird” characters, if you use the UTF-8 character set.

In XML documents, you can also avoid using the predefined entities, as long as all matching characters only appear in a CDATA section. The syntax for CDATA sections is:

<![CDATA[ blah blah blah ]]>

CDATA sections can not contain the ]]> sequence of characters; there is no workaround for this limitation.

Enjoy!

Setting a strict SQL_MODE

Versione italiana

Developing Stored Routines, Triggers or Events for MariaDB and MySQL is not easy, because the language is not flexible and has a lot of limitations. Plus, the server tries to make things easier by hiding out errors and allowing bad practice. In my opinion, when you have to write at least 3 lines, bad practices make things esponentially harder. When the server complains you did something wrong, the problem is not in the server: it is in your code! And it remains even if you tell the server to shut down. The only solution is finding the problem and modifying your code. So, I suggest you make the server stricter. To do this, you have to modify the SQL_MODE variable.

The SQL_MODE can be used to change the SQL syntax, and make it more compatible with another DBMS. I don’t like to use syntaxes which are not the de-facto standard, but I think it is harmless.

But some flags should always be set, because they prevent the server to be too much flexible.

ERROR_FOR_DIVISION_BY_ZERO
Numbers can not be divided by zero, because the result is undefined. When you try to divide a number by zero, languages usually raise an error. And so does MariaDB, if this flag is set. Otherwise, it returns NULL. As NULL is logically correct when you want to represent an undefined value, this may be good for you. But usually it isn’t – if you are dividing a number by zero by mistake, you want to know and correct the bug!

NO_AUTO_CREATE_USER
When you GRANT rights to a user which doesn’t exist, it is created automatically, unless this flag is set. It’s not a good thing for security that you can create a user by mistake.

NO_AUTO_VALUE_ON_ZERO
This flag allowes you to insert a 0 value into an AUTO_INCREMENT field. Since 0 is a legal value, this flag should be set. But I don’t care too much about it, because changing an AUTO_INCREMENT field is a bad practice too.

NO_ENGINE_SUBSTITUTION
When you create a table, it is important to choose the right Storage Engine. If this flag is not set, and you specify a Storage Engine which doen’t exist, the server silently uses the default.

NO_ZERO_DATE
If this flag is not set, MariaDB accepts a special date value: ‘0000-00-00’. If you want to use it, you don’t need to set this flag; but it’s quite a rare situation. Otherwise it’s only a source of problems, because invalid dates are silently converted to a zero-date. Which is almost never what you want.

NO_ZERO_IN_DATE
This flag prevents non-zero dates which have zero-parts, like ‘0000-05-20’. It is a bit harder to do this by mistake, but it can happen if you compose a date string dinamically.

ONLY_FULL_GROUP_BY
When you specify a non-aggregate and non-grouped column in the SELECT clause, the result is undefined. For this reason an error should be raised, but this only happens if this flag is set.

STRICT_ALL_TABLES
This flag is very important, because if it is not set, no error is raised when you try to assign an out-of-range value (or a string which is too long). It works not only for tables, but also for variables and functions. For example, if a TINYINT SIGNED function tries to return 200, no error is raised (just a warning), and the return value is 127.

STRICT_TRANS_TABLES
See STRICT_ALL_TABLES, but this flag only works for transactional tables.

So, here’s the SQL_MODE I decided to use when I create a Stored Routine for the STK proects:

SET @@session.SQL_MODE = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES';

Here you can find a complete list of all MySQL SQL_MODE flags.

Setting the InnoDB’s Strict Mode is also a good idea, but it’s out of the purpose of this post.

Enjoy!