Roland Bouman's blog

Common Schema: dependencies routines

2011-12-01T20:17:00.005+01:00

Are you a MySQL DBA? Checkout the common_schema project by Oracle Ace Shlomi Noach.

The common_schema is an open source MySQL schema that packs a number of utility views, functions and stored procedures. You can use these utilities to simplify MySQL database administration and development. Shlomi just released revision 178, and I'm happy and proud to be working together with Shlomi on this project.

Among the many cool features created by Shlomi, such as foreach, repeat_exec and exec_file, there are a few %_dependencies procedures I contributed:

get_event_dependencies(schema_name, event_name)

get_routine_dependencies(schema_name, routine_name)

get_sql_dependencies(sql, default_schema)

get_view_dependencies(schema_name, view_name)

All these procedures return a resultset that indicates which schema objects are used by the object identified by the input parameters. Here are a few examples that should give you an idea:


mysql> call common_schema.get_routine_dependencies('common_schema', 'get_routine_dependencies');
+---------------+----------------------+-------------+--------+
| schema_name   | object_name          | object_type | action |
+---------------+----------------------+-------------+--------+
| common_schema | get_sql_dependencies | procedure   | call   |
| mysql         | proc                 | table       | select |
+---------------+----------------------+-------------+--------+
2 rows in set (0.19 sec)

Query OK, 0 rows affected (0.19 sec)

mysql> call common_schema.get_routine_dependencies('common_schema', 'get_sql_dependencies');
+---------------+-------------------+-------------+--------+
| schema_name   | object_name       | object_type | action |
+---------------+-------------------+-------------+--------+
| common_schema | _get_sql_token    | procedure   | call   |
| common_schema | _sql_dependencies | table       | create |
| common_schema | _sql_dependencies | table       | drop   |
| common_schema | _sql_dependencies | table       | insert |
| common_schema | _sql_dependencies | table       | select |
+---------------+-------------------+-------------+--------+
5 rows in set (1.59 sec)

Of course, there's always a lot to be desired. The main shortcomings as I see it now is that the dependencies are listed only one level deep: that is, the dependencies are not recursively analyzed. Another problem is that there is currently nothing to calculate reverse dependencies (which would arguably be more useful).

The good news is, this is all open source, and your contributions are welcome! If you're interested in the source code of these routines, checkout the common_schema project, and look in the common_schema/routines/dependencies directory.

If you'd like to add recursive dependencies, or reverse dependencies, then don't hesitate and contribute. If you have a one-off contribution that relates directly to these dependencies routines, then it's probably easiest if you email me directly, and I'll see what I can do to get it in. If you are interested in more long term contribution, it's probably best if you write Shlomi, as he is the owner of the common_schema project.

You can even contribute without implementing new features or fixing bugs. You can simply contribute by using the software and find bugs or offer suggestions to improve it. If you found a bug, or have an idea for an improvement or an entirely new feature, please use the issue tracker.

For now, enjoy, and untill next time.

MySQL Hacks: Preventing deletion of specific rows

2011-10-21T15:00:00.002+02:00

Recently, someone emailed me:

I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?

Frankly, I usually refer people that write me these things to a public forum, but this time I felt like giving it a go. I figured it would be nice to share my solution and I'm also curious if others found other solutions still.

(Oh, I should point out that I haven't asked what the underlying reasons are for this somewhat extraordinary requirement. I normally would do that if I would be confronted with sucha a requirement in a professional setting. In this case I'm only interested in finding a crazy hack)

Attempt 1: Re-insert deleted rows with a trigger

My first suggestion was:

Raising the error won't help you achieve your goal: as soon as you raise the error, the statement will either abort (in case of a non-transactional table) or rollback all row changes made up to raising the error (in case of a transactional table)

Although I find the requirement strange, here's a trick you could try:

write a AFTER DELETE FOR EACH ROW trigger that re-inserts the rows back into the table in case the condition DEPT_ID = 10 is met.

Hope this helps...

Alas, I should've actually tried it myself before replying, because it doesn't work. If you do try it, a DELETEresults in this runtime error:

Can't update table 'emp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

This is also known as "the mutating table problem".

Attempt 2: Re-insert deleted rows into a FEDERATED table

As it turns out, there is a workaround that meets all of the original demands. The workaround relies on the FEDERATED storage engine, which we can use to trick MySQL into thinking we're manipulating a different table than the one that fires the trigger. My first attempt went something like this:


CREATE TABLE t (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_id INT,
    INDEX(dept_id)
);

CREATE TABLE federated_t (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_id INT,
    INDEX(dept_id)
)
ENGINE FEDERATED
CONNECTION = 'mysql://root@localhost:3306/test/t';

DELIMITER //

CREATE TRIGGER adr_t
AFTER DELETE ON t
FOR EACH ROW
IF old.dept_id = 10 THEN
    INSERT INTO t_federated
    VALUES  (old.id, old.dept_id);
END IF;
//

DELIMITER ;

So the idea is to let the trigger re-insert the deleted rows back into the federated table, which in turn points to the original table that fired the trigger to fool MySQL into thinking it isn't touching the mutating table. Although this does prevent one from deleting any rows that satisfy the DEPT_ID = 10 condition, it does not work as intended:


mysql> INSERT INTO t VALUES (1,10), (2,20), (3,30);
Query OK, 3 rows affected (0.11 sec)

mysql> DELETE FROM t;
ERROR 1159 (08S01): Got timeout reading communication packets

mysql> SELECT * FROM t;
+----+---------+
| id | dept_id |
+----+---------+
|  1 |      10 |
|  2 |      20 |
|  3 |      30 |
+----+---------+
3 rows in set (0.00 sec)

At this point I can only make an educated guess about the actual underlying reason for this failure. It could be that the deletion is locking the rows or even the table, thereby blocking the insert into the federated table until we get a timeout. Or maybe MySQL enters into an infinite loop of deletions and insertions until we hit a timeout. I didn't investigate, so I don't know, but it seems clear this naive solution doesn't solve he problem.

Attempt 3: Deleting from the FEDERATED table and re-inserting into the underlying table

It turns out that we can solve it with a FEDERATED table by turning the problem around: Instead of manipulating the original table, we can INSERT and DELETE from the FEDERATED table, and have an AFTER DELETE trigger on the FEDERATED table re-insert the deleted rows back into the original table:


DROP TRIGGER adr_t;

DELIMITER //

CREATE TRIGGER adr_federated_t
AFTER DELETE ON federated_t
FOR EACH ROW
IF old.dept_id = 10 THEN
    INSERT INTO t
    VALUES  (old.id, old.dept_id);
END IF;
//

DELIMITER ;

Now, the DELETE does work as intended:


mysql> DELETE FROM federated_t;
Query OK, 3 rows affected (0.14 sec)

mysql> SELECT * FROM federated_t;
+----+---------+
| id | dept_id |
+----+---------+
|  1 |      10 |
+----+---------+
1 row in set (0.00 sec)

Of course, to actually use this solution, one would grant applications access only to the federated table, and "hide" the underlying table so they can't bypass the trigger by deleting rows directly from the underlying table.

Now, even though this solution does seem to fit the original requirements, I would not recommend it for several reasons:

It uses the FEDERATED storage engine, which hasn't been well supported. For that reason, it isn't enabled by default, and you need access to the MySQL configuration to enable it, limiting the applicability of this solution. Also, you could run into some nasty performance problems with the FEDERATED storage engine
The solution relies on a trigger. In MySQL, triggers can really limit performance
Perhaps the most important reason is that this solution performs "magic" by altering the behaviour of SQL statements. Arguably, this is not so much the fault of the solution as it is of the original requirement.

An Alternative without relying on magic: a foreign key constraint

If I were to encounter the original requirement in a professional situation, I would argue that we should not desire to alter the semantics of SQL commands. If we tell the RDBMS to delete all rows from a table, it should either succeed and result in all rows being deleted, or it should fail and fail completely, leaving the data unchanged.

So how would we go about implementing a solution for this changed requirement?

We certainly could try the approach that was suggested in the original request: create a trigger that raises an exception whenever we find the row should not be deleted. However, this would still rely on a trigger (which is slow). And if you're not on MySQL 5.5 (or higher), you would have to use one of the ugly hacks to raise an exception.

As it turns out, there is a very simple solution that does not rely on triggers. We can create a "guard table" that references the table we want to protect using a foreign key constraint:


mysql> CREATE TABLE t_guard (
    ->     dept_id INT PRIMARY KEY,
    ->     FOREIGN KEY (dept_id)
    ->         REFERENCES t(dept_id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t_guard values (10);
Query OK, 1 row affected (0.08 sec)

mysql> DELETE FROM t;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t_guard`, CONSTRAINT `t_guard_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `t` (`dept_id`))
mysql> DELETE FROM t WHERE dept_id != 10;
Query OK, 2 rows affected (0.05 sec)

(Like in the prior example with the federated table, the guard table would not be accessible to the application, and the "guard rows" would have to be inserted by a privileged user)

Finally: what a quirkyy foreign key constraint!

You might have noticed that there's something quite peculiar about the foreign key constraint: typically, foreign key constraints serve to relate "child" rows to their respective "parent" row. To do that, the foreign key would typically point to a column (or set of columns) that make up either the primary key or a unique constraint in the parent table. But in this case, the referenced column dept_id in the t table is contained only in an index which is not unique. Strange as it may seem, this is allowed by MySQL (or rather, InnoDB). In this particular case, this flexibility (or is it a bug?) serves us quite well, and it allows us to guard many rows in the t table with dept_id = 10 with just one single row in the guard table.

Fighting Spam: Word Verification

2011-10-07T08:50:00.002+02:00

Hi All,

this is a quick note to let you know that from now on, commenters on this blog will need to complete a word verification (captcha) step.

Personally, I regret to have to take this measure. Let me explain why I'm doing it anyway.

Since 3 months or so, moderating comments on this blog is becoming a real drag due to a surge in anonymous spam. While bloggers spam detection is quite good, I still get notificaton mails prompting me to moderate. I feel this is consuming more of my time than it's worth.

Except for requiring word verification, other policies (or lack thereof) are still in effect: all comments are moderated, but anyone can comment, even anonymously. In practice, all real comments get published - even negative or derogatory ones (should I receive them).

Sorry for the convenience, but I hope you'll understand.

Re-implementing udf_init_error in MySQL 5.5 and up

2011-08-24T22:40:00.005+02:00

To whom it may concern -

Today, I received an email from a user of the udf_init_error UDF (which resides in the lib_mysqludf_udf library). The purpose of this UDF is to generate an error condition, which can be used to abruptly terminate a trigger or stored procedure. As such it is a workaround for bug #11661. This is all described extensively in my now ancient article here.

The user wrote me because of a problem experienced in MySQL 5.5:

...calling
select udf_init_error('Transaction Cannot Be Done Because....');
will return user friendly error message:
Transaction Cannot Be Done Because....
. But in MySQL 5.5, it returns
Can't initialize function 'udf_init_error; Transaction Cannot Be Done Because....
The Can't initialize function 'udf_init_error; bit is so annoying! How can I get rid of that?

I explained that the UDF still works like it should; it's just that at some point during the 5.0 lifecycle, the format of the error message was changed. (I can't recall exactly which version that was, but I did file bug #38452 that describes this issue).

Anyway, I suggested to move away from using the udf_init_error() UDF, and port all dependent code to use the SIGNAL syntax instead, which was introduced in MySQL 5.5. (For a friendly introduction to using the SIGNAL syntax, please check out one of my prior articles).

Unfortunately, for this particular user this would not be an easy task:

The use of SIGNAL did come to my mind, but the implementation is not easy. I have thousands of stored routines to modify. Besides, I'm already satisfied with what the UDF does.

On the one hand, It makes me happy to hear the udf_init_error() UDF served him so well that he wrote so many routines that rely on it; on the other hand, I feel bad that this is holding him back from upgrading to MySQL 5.5.

For everybody that is in this same position, I'd like to suggest the following solution: simply re-implement udf_init_error() as a stored SQL function that uses the SIGNAL functionality instead. The error message returned to the client will not be exactly the same as in the olden MySQL 5.0 days, but at least there will not be an annoying complaint about a UDF that cannot be initialized.

Here's a very simple example that illustrates how to do it:

CREATE FUNCTION udf_init_error(
   p_message VARCHAR(80)
)
RETURNS INTEGER
DETERMINISTIC
NO SQL
BEGIN
   DECLARE err CONDITION FOR SQLSTATE '45000';
   SIGNAL err SET MESSAGE_TEXT = p_message;
   RETURN 1;
END;

I hope this helps.

Proposals for Codebits.EU

2011-08-15T09:45:00.000+02:00

Codebits is an annual 3-day conference about software and, well, code. It's organized by SAPO and this year's edition is to be held on November 10 thru 12 at the Pavilhão Atlântico, Sala Tejo in Lisbon, Portugal.

I've never attended SAPO Codebits before, but I heard good things about it from Datacharmer Giuseppe Maxia. The interesting thing about the way this conference is organized is that all proposals are available to the public, which can also vote for the proposals. This year's proposals are looking very interesting already, with high quality proposals from Giuseppe about database replication with Tungsten replicator, Pentaho's chief of data integration Matt Casters about Kettle (aka Pentaho data integration), and Pedro Alves from webdetails who will be talking about "Big Data" analysis and dashboarding work he did for the Mozilla team.

There are many more interesting talks, and you should simply check out the proposals for yourself and give a thumbs up or a thumbs down according to whether you'd like see a particular proposal at the conference. I decided to send in a few proposals as well:

MQL-to-SQL: a JSON-based query language for RDBMS access from AJAX applications

DataVault and Anchor Modeling: Methods for auditable and agile Data Warehousing

Xmla4js: Online Analytical Processing and Business Intelligence for web applications

So, if you like what you see here, take a minute to vote and shape this codebits conference. I'm hoping to meet you there!

Regarding the MySQL Conference and Expo 2012

2011-08-12T11:59:00.007+02:00

Last week, Baron Schwartz announced the Percona Live MySQL Conference and Expo 2012.

Percona organized MySQL related conferences and seminars before, and from what I've heard, with considerable success and to satisfaction of its attendees, and there's one coming up in London in October 2011. But arguably, last week's announcement is quite different from the prior Percona conferences. It's different, because it seeks to replace the annual O'Reilly MySQL Conference and Expo.

Everyone that has read the announcement will have no trouble recognizing it as a replacement, since it reads:

We all know that the entire MySQL community has been waiting to see if there will be a MySQL conference next year in the traditional date and location. To the best of our knowledge, no one else was planning one, so we decided to keep the tradition alive.

If you're still in doubt:

The conference title contains the phrase: "MySQL Conference and Expo".

It's to be held in the Hyatt Regency Hotel in Santa Clara, which has been the venue for the O'Reilly MySQL Conference and Expo since at least 2005.

It's scheduled to take place midway April, exactly like the O'Reilly conferences used to be.

The scope of the conference encompasses the entire "eco-system" - whatever that is: Developers and DBAs; tools and techniques; tutorials, talks and BOFs. It's about users, but also explicitly about companies and businesses.

Immediately following the announcement, bloggers from the MySQL community - all of which I respect, and consider friends of mine - started posting their opinions:

Kaj Arnö, EVP Products of SkySQL: "Santa Clara MySQL Conference 2012: Unity or division?"

Giuseppe Maxia, member of the IOUG's (Independent Oracle User Group) MySQL Council: "Call for disclosure on MySQL Conference 2012"

Sheeri Cabral, sr. DBA and Community Liason at PalominiDB: "Disclosure: Truth About MySQL 2012 Conference Planning"

Henrik Ingo: "The MySQL conference is back for 2012, courtesy of Percona"

Marco Tusa: "A Missed Opportunity?"

Monty Widenius of Monty Program ab: "What is happening with the MySQL conference?"

(In this list, I tried to maintain the affiliation of these bloggers as appropriate and relevant as possible. Please let me know if you feel I wrongly associated someone with a particular company or organizational body)

Except for Henrik's post, all of these express a negative attitude towards Percona's announcement. The critique focuses on a few themes:

Giuseppe and Sheeri express similar thoughts. They recall how Baron Schwartz and Peter Zaitsev (both from Percona, and now initiating the 2012 conference) criticized the O'Reilly MySQL Conference and Expo 2008 edition for increasingly becoming an event focused at business and vendors, rather than at users (see here and here). There seems to be a hidden accusation that now, only a few years later, Baron and Peter are "guilty" of organizing a business-oriented conference themsevles.

Also both Sheeri and Giuseppe's posts express the concern that Oracle might not allow any of its MySQL engineers and architects to speak at the conference. This would arguably make it a less interesting conference as Oracle is a major -if not the main- contributor to both MySQL and InnoDB.

All bloggers argue that organizing a conference of this scale should not be the effort of a single company. In particular Kaj Arnö and Monty Widenius allude to the possibility of O'Reilly organizing the conference again, just like the way things used to be. They both explicitly include a list of the major companies contributing to MySQL which they envision should help drive such a conference.

The main concern here is that when a single company organizes this event, it will be their event. In other words, it will not be neutral. There is serious concern for unfair competition, as the organizing company gets to decide or exert greater influence on which talks are approved, and how talks are scheduled against each other

Although I understand the critique, I do not agree with it. I hope I'm not offending any of my friends, but I think none of these seemingly sensible arguments against Percona organizing the MySQL Conference have true merit.

But before I explain, I think it's interesting to observe that nobody seems to assume it as a given that O'Reilly would be organizing another MySQL Conference and Expo. Monty comes closest to saying something about it:

The reason for my state of mind is that although there have been rumors about discontinuance of the O'Reilly arranged conference there hasn't been any announcement about this.

In fact, I have been working with O'Reilly to try to setup next year's O'Reilly MySQL conference with the intention of having it 'exactly like before', even if Oracle would not participate.

So basically, because O'Reilly didn't say they weren't going to do one, it might be possible, right :) I tend to look at it differently: It means exactlty nothing when someone, O'Reilly included, didn't announce something. The way I see it, O'Reilly has nothing to gain by announcing that they will not be organizing another MySQL Conference. Similarly, they've got nothing to lose by not announcing they aren't.

In the end, organizing conferences is one of O'Reilly's business activities. The mere fact that they've been organizing one during the previous years does not bestow any special responsibility upon them to inform potential attendees and sponsors that they are discontinuing such an activity.

It's interesting that Monty mentions he was working together with O'Reilly on it. I have no reason to doubt it, but I do suspect that whatever was in the works, it was probably not going to happen at the traditional location and at the traditional time window. Silicon Valley Conference centers are busy places, and need to be reserved well in advance - starting to work on it less than three quarters in advance can probably not be considered "well in advance" for an event of this scale.

Now, here are my arguments as to why I do not share the opinions I mentioned above:

If you read Peter and Baron's posts from way back in 2008 (I included the links already, but here and here they are again), you will notice that they were not in fact criticizing the O'Reilly MySQL Conference and Expo at all. They simply recognized there was a gap and felt there should also be a community-driven conference. In fact, Baron initiated such an event, the Open SQL Camp. That turned out to be such a great success that others started organizing OpenSQL Camps too.

Now, if you read Baron's announcement for the Percona MySQL Conference and Expo, you'll notice that precisely because the tables are turned, they now feel the need to maintain a business-driven MySQL conference. They simply recognized that now there is the risk of a gap as far as a business-driven MySQL conference is concerned. In other words, there is no question of should this be a business-driven event or a community-driven event. Both kinds of events are needed, and the business one wasn't being taken care of, neither by O'Reilly, nor by Oracle.

The concern that Oracle might not allow its engineers to attend a conference that is organized by a competitor seems reasonable. But it assumes that they would allow it if it was a vendor-neutral conference, or at least a conference that could be perceived as such. To those that have been involved to some extent in the organization of the 2010 and 2011 editions of the MySQL Conference, it should be no secret that Oracle's participation hasn't been exactly eager. Just listen to Tim O'Reilly's own talk at the MySQL 2010 conference. If that doesn't convince you, look at the sponsor list for the 2011 edition: no Oracle. And if that still doesn't convince you - Last year it was very unclear whether Oracle was sending any delegation at all. Only at a very late stage did we receive proposals from Oracle.

To be clear, I am not blaming Oracle for not wishing to participate in a particular conference. They have their own strategy and they are entitled to execute that however they see fit, even if that includes not sponsoring or speaking at a major MySQL conference. I'm just arguing that whether or not such a conference is organized by a vendor neutral party does not seem to be a part of Oracle's consideration. There is in my opinion absolutely no guarantee that Oracle would participate if things really would be like they used to, and O'Reilly and not Percona would be organizing another edition.

The final matter I'd like to discuss is the idea of all major MySQL contributing companies on working togehter to organize a conference. Although I think that's a very sympathetic idea, it doesn't seem very realistic to me. Or at least, it doesn't seem realistic that this would lead to a Santa Clara conference in April 2012. So maybe this is something all involved parties should discuss for the years to come.

My final remarks are that in the end, I am mostly happy that at least one party is willing to take the up-front risk in securing the venue. Percona has announced that, just like O'Reilly, they want to set up a board of community members to drive the program. I think other companies have legitimate concerns over vendor neutrality, but I have no reason to doubt that Baron, and by extension, Percona are doing whatever they can to safeguard that.

What is left is Percona's company name in the conference title. While Percona is likely to use that to drive their own business, this is not really different from all pre-2010 MySQL conferences, where MySQL AB and then Sun used it to drive theirs. That does not mean it excludes competitors using the conference to their advantage to pursue their business interests. There has always been room for competing vendors, and arguably that is what made the event not just *a* MySQL conference, but *the* MySQL conference.

I understand that not everybody is happy about how things are going now, and it would be great if all companies that feel they have a stake here collaborate in the future. But for now, I'm really happy there will be a 2012 edition, and I thank Percona for organizing it. I will definitely send in proposals as soon as the call for papers is open, and I hope everybody that feels they have something to talk about or present will do the same. I really believe this can be a conference exactly like it was before, with the only difference that it's organized by Percona, and not O'Reilly.

Working with namespaces and namespace prefixes in XSLT 1.0

2011-06-22T00:13:00.004+02:00

To whom it may concern -

I'm currently developing a Xslt 1.0 stylesheet for analysis of XML Schema documents. As part of this work, I developed a couple of templates for working with namespace names and prefixes, and I like to share them via this post. The code is not incredibly hard or advanced, but it gets the job done and it may save you some time if you need something similar.

get-local-name: Return the local name part of a given QName. function in XPath 2.0
get-prefix: Return the prefix part of a given QName.
get-ns-name: Return the namespace name associated to the given prefix.
get-ns-prefix: Return a prefix that can be used to denote the given namespace name.
resolve-ns-identifier: Return the namespace name for a given QName prefix

Before I discuss the code, I want to make a few remarks:

This s all about Xslt 1.0 and its query langue XPath 1.0. All these things can be solved much more conveniently in XPath 2.0, and hence in Xslt 2.0 because that builds on Xpath 2.0 (and not XPath 1.0 like Xslt 1.0 does)
If you're planning to use this in a web-browser, and you want to target Firefox, your're out of luck. Sorry. Firefox is a greatt browser, but unlike Chrome, Opera and even Internet Explorer, it doesn't care enough about Xslt to fix bug #94270, which has been living in their bug tracker as long as August 2001 (nope - I didn't mistype 2011, that's 2001 as in almost a decade ago)

get-local-name

Return the local name part of a given QName. This is functionally equivalent to the fn:local-name-from-QName

<!-- get the last substring after the *last* colon (or he argument if no colon) -->
<xsl:template name="get-local-name">
    <xsl:param name="qname"/>
    <xsl:choose>
        <xsl:when test="contains($qname, ':')">
            <xsl:call-template name="get-local-name">
                <xsl:with-param name="qname" select="substring-after($qname, ':')"/>
            </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$qname"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

get-prefix

Return the prefix part of a given QName. This is functionally equivalent to the fn:prefix-from-QName function in XPath 2.0

<!-- get the substring before the *last* colon (or empty string if no colon) -->
<xsl:template name="get-prefix">
    <xsl:param name="qname"/>
    <xsl:param name="prefix" select="''"/>
    <xsl:choose>
        <xsl:when test="contains($qname, ':')">
            <xsl:call-template name="get-prefix">
                <xsl:with-param name="qname" select="substring-after($qname, ':')"/>
                <xsl:with-param name="prefix" select="concat($prefix, substring-before($qname, ':'))"/>
            </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="$prefix"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

get-ns-name

Return the namespace name associated to the given prefix. This is functionally equivalent to the fn:namespace-uri-for-prefix function in XPath 2.0. The main difference is that this template does the lookup against the namespace definitions that are in effect in the current context, whereas the XPath 2.0 function allows the element which is used as context to be passed in as argument.

<!-- get the namespace uri for the namespace identified by the prefix in the parameter -->
<xsl:template name="get-ns-name">
    <xsl:param name="ns-prefix"/>
    <xsl:variable name="ns-node" select="namespace::node()[local-name()=$ns-prefix]"/>
    <xsl:value-of select="$ns-node"/>
</xsl:template>

get-ns-prefix

Return a prefix that can be used to denote the given namespace name. This template is complementary to the get-ns-name template. This template assumes only one prefix will be defined for each namespace. The namspace is resolved against the current context.

<!-- get the namespace prefix for the namespace name parameter -->
<xsl:template name="get-ns-prefix">
    <xsl:param name="ns-name"/>
    <xsl:variable name="ns-node" select="namespace::node()[.=$ns-name]"/>
    <xsl:value-of select="local-name($ns-node)"/>
</xsl:template>

resolve-ns-identifier

Return the namespace name for a given QName prefix (be it a namespace prefix or a namspace name). This template is useful to generically obtain a namespace name when feeding it the prefix part of a QName. If the prefix happens to be a namespace name, then that is returned, but if it happens to be a namespace prefix, then a lookup is performed to return the namspace name. This template also looks at the namspaces in effect in the current context.

<!-- return the namespace name -->
<xsl:template name="resolve-ns-identifier">
    <xsl:param name="ns-identifier"/>
    <xsl:choose>
        <xsl:when test="namespace::node()[.=$ns-identifier]">
            <xsl:value-of select="$ns-identifier"/>
        </xsl:when>
        <xsl:when test="namespace::node()[local-name()=$ns-identifier]">
            <xsl:value-of select="namespace::node()[local-name()=$ns-identifier]"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:message terminate="yes">
                Error: "<xsl:value-of select="$ns-identifier"/>" is neither a valid namespace prefix nor a valid namespace name.
            </xsl:message>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

HPCC vs Hadoop at a glance

2011-06-18T10:22:00.004+02:00

Yesterday I noticed this tweet by Andrei Savu: . This prompted me to read the related GigaOM article and then check out the HPCC Systems website.

If you're too lazy to read the article or visit that website:

HPCC (High Performance Computing Cluster) is a massive parallel-processing computing platform that solves Big Data problems. The platform is now Open Source!

HPCC Systems compares itself to Hadoop, which I think is completely justified in terms of functionality. Its product originated as a homegrown solution of LexisNexis Risk Solutions allowing its customers (banks, insurance companies, law enforcment and federal government) to quickly analyze billions of records, and as such it has been in use for a decade or so. It is now open sourced, and I already heard an announcement that Pentaho is its major Business Intelligence Partner.

Based on the limited information a made a quick analysis, which I emailed to the HPCC Systems CTO, Armando Escalante. My friend Jos van Dongen said it was a good analysis and told me I should post it. Now, I don't really have time to make a nice blog post out of it, but I figured it can't hurt to just repeat what I said in my emails. So here goes:

Just going by the documentation, I see a two real unique selling points in HPCC Systems as compared to Hadoop:

Real-time query performance (as opposed to only analytic jobs). HPCC offers two difference setups, labelled Thor and Roxie. Functionalitywise, Thor should be compared to a Map/Reduce cluster like Hadoop: it's good for doing fairly long running analyses on large volumes of data. Roxie is a different beast, and designed to offer fast data access, supporting ad-hoc real-time queries
Integrated toolset (as opposed to hodgepodge of third party tools). We're talking about an IDE, job monitoring, code repository, scheduler, configuration manager, and whatnot. This really looks like like big productivity boosters, which may make Big Data processing a lot more accessible to companies that don't have the kind of development teams required to work with Hadoop.

(there may be many more benefits, but these are just the ones I could clearly distill from the press release and the website)

Especially for Business Intelligence, Roxie maybe a big thing. If real-time Big Data queries could be integrated with Business Intelligence OLAP and reporting tools, then this is certainly a big thing. I can't disclose the details but I have trustworthy information that integration with Pentaho's Analysis Engine, the Mondrian ROLAP engine is underway and will be available as an Enterprise feature.

A few things that look different but which may not matter too much when looking at HPCC and Hadoop from a distance:

ECL, the "Enterprise Control Language", which is a declarative query language (as opposed to just Map/Reduce). This initially seems like a big difference but Hadoop has tools like pig and sqoop and hive. Now, it could be that ECL is vastly superior to these hadoop tools, but my hunch is you'd have to be careful in how you position that. If you choose a head-on strategy in promoting ECL as opposed to pig, then the chances are that people will just spend their energy in discovering the things that pig can do and ECL cannot (not sure if those features actually exist, but that is what hadoop fanboys will look for), and in addition, the pig developers might simply clone the unique ECL features and the leveling of that playing field will just be a matter of time. This does not mean you shouldn't promote ECL - on the contrary, if you feel it is a more productive language than pig or any other hadoop tool, then by all means let your customers and prospects know. Just be careful and avoid downplaying the hadoop equivalents because that strategy could backfire.

Windows support. It's really nice that HPCC Systems is available for Microsoft Windows, it makes that a lot easier for Microsoft shops (and there are a lot of them). That said, customers that really have a big-data problem will solve it no matter what their internal software policies are. So they'd happily start running hadoop on linux if that solves their problems.
Maturity. On paper HPCC looks more mature than hadoop. It's hard to tell how much that matters though because hadoop has all the momentum. People might choose for hadoop because they anticipate that the maturity will come thanks to the sheer number of developers committing to that platform.

The only thing I can think of where HPCC looks like it has a disadvantage as compared to Hadoop is adoption rate and licensing. I hope these will prove not to be significant hurdles for HPCC, but I think that these might be bigger problems then they seem. Especially the AGPL licensing seems problematic to me.

The AGPL is not well regarded by anyone I know - not in the open source world. The general idea seems to be that even more than plain GPL3 it restricts how the software may be used. If the goal of open sourcing HPCC is to gain mindshare and a developer community (something that hadoop has done and is doing extremely well) then a more permissive license is really the way to go.

If you look at products like MySQL but also Pentaho - they are both very strongly corporately led products. The have a good number of users, but few contributions from outside the company, and this is probably due to a combination of GPL licensing and the additional requirement for handing over the copyright of any contributions to the company. Hence these products don't really benefit from an open source development model (or at least not as much as they could). For these companies, Open source may help initially to gain a lot of users, but those are in majority the users that just want a free ride: conversion rates to enterprise edition customers are quite low. It might be enough to make a decent buck, but eventually you'll hit a cap on how far you can grow. I'm not saying this is bad - you only need to grow as much as you have to, but it is something to be aware of.

Contrast this to Hadoop. The have a Apache 2.0 permissive license, and this results in many individuals but also companies contributing to the project. And there are still companies like Cloudera that manage to make a good living off of the services around their distribution of Hadoop. You don't lose the ability to develop add-ons either with this model - apache 2.0 allows all that. The difference with GPL (and AGPL) of course is that it allows this also to other users and companies. So the trick to stay on top in this model is to simply offer the best product (as opposed to being the sole holder of the copyright to he code).

Anyway - that is it for now - I hope this is helpful.

Cleaning webpages with Pentaho Data Integration and JTidy

2011-05-30T17:50:00.001+02:00

Here's an issue I've come across multiple times: I need to scrape HTML websites to extract data. Pentaho Data Integration (kettle) has lots of functionality on-board to make this an easy process, except one: it does not support reading data directly from HTML.

In this short post, I provide a simple tip to clean HTML pages and convert them to XML so you can extract its data using the conventional "Get data from XML" step. The solution hinges on two ingredients:

JTtidy, the Java port of the popuar HTML Tidy program, which is an open source HTML cleaning program originally created by Dave Ragget

The user-defined Java Class step, which lets us write a small snippet of Java that we need to interface with JTidy

Standard Kettle tools for Webservices

Kettle is really good at fetching data from the web and extracting data from webservices, be they in a SOAP/XML, REST/JSON or RSS flavor. (There is an extensive chapter on this subject in Pentaho Kettle Solutions). But when you're dealing with plain old HTML, things can get pretty hairy.

If you're lucky, the page may be in XHTML, and in that case it's worth trying the Get Data from XML step. However, quite often a webpage that claims to be XHTML is not well-formed XML, and even if it is, Kettle does not understand things like   entities, which are valid in XHTML, but not in plain XML. And of course, more often than not, you're not lucky, and XHTML represents only a minor fraction of all the web pages out there.

Workaround: JavaScript string manipulation

In the past, I usually worked around these issues. In practice, some quick and dirty string manipulation using the Modified Javascript Value step and some built-in indexOf(), substring and replace() functions go a long way.

In most cases I don't really need the entire web page, but only a <table>, <ul> or <ol> element in the <body>. Excising only the interesting sections out of the page using plain string manipulation will often get rid of most of the cruft that prevents the data from being treated as XML. For example, if we only need to get the rows from a table with a particular id attribute, we can use a JavaScript snippet like this:


//table we're looking for
var startHandle = "<table class=\"lvw\" cellpadding=0 cellspacing=0>";
var startPosition= html.indexOf(startHandle);
//look beyond the start tag to lose the invalid unquoted attributes
startPosition += startHandle.length;

//find where this table ends (lucky us, no nested table elements :)
var endHandle = "</table>";
var endPosition = html.indexOf(endHandle, startPosition);

//make a complete table fragment out of it again
var table = "<table>" + html.substring(startPosition, endPosition + endHandle.length);

//replace nbsp entities, empty unclosed img elements, and value-less nowrap attributes
table = table.replace(/&nbsp;|<img[^>]>|nowrap/ig, "");

There are of course no guarantees that the sections you cut out like that are in fact well-formed XML, but in my experience it's often worth a try.

A better way: using JTidy

While the JavaScript workaround may just work for your particular case, it certainly has disadvantages. Sometimes it may just be not so simple to clean the HTML with plain string manipulation. And of course there is a performance issue too - the JavaScript step can be quite slow.

Fortunately, there is a better way.

Using a user-defined Java Class step we can have JTidy do the dirty work of cleaning the HTML and converting it to XML, which we can then process in a sane way with Kettle's Get Data from XML step.

We need to do two things to make this work: first, you have to download JTidy, unzip it, and place the jtidy-r938.jar in the libext directory, which resides immediately in your kettle installation directory. (note that if you were running spoon, you need to restart it before it will be picked up). Second, you need a little bit of glue code for the User-defined Java class step so Kettle can use the Tidy class inside the jar. With some help from the pentaho wiki and the JTidy JavaDoc documentation, I came up with the following Java snippet to make it work:


import org.w3c.tidy.Tidy;
import java.io.StringReader;
import java.io.StringWriter;

protected Tidy tidy;

public boolean init(StepMetaInterface stepMetaInterface, StepDataInterface stepDataInterface)
{
    //create and configure a Tidy instance
    tidy = new Tidy();
    tidy.setXmlOut(true);
    return parent.initImpl(stepMetaInterface, stepDataInterface);
}

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
    Object[] r;
    //Get row from incoming stream.
    //Bail out if its not there.
    if ((r = getRow()) == null) {
       setOutputDone();
       return false;
    }

    //read the value of the html input field
    //the html field happens to be the 5th field in the stream, 
    //because java arrays start at 0, we use index 4 to reference it
    StringReader html = new StringReader((String)r[4]);

    //use tidy to parse html to xml
    StringWriter xml = new StringWriter();
    tidy.parse(html, xml);

    //assign the xml to the output row
    //note we simply overwrite the original html field from the input row.
    r[4] = xml.toString();

    //push the output row to the outgoing stream.
    putRow(data.outputRowMeta, r);
    return true;
}

(Tip: for more examples and background information on the user-defined java class step, check out the excellent blog posts by Slawomir Chodnicki, Matt Casters and the video walk-through by Dein Einspanjer)

The big advantage of using Tidy is that you can be sure that the result is well-formed XML. In addition, you can have JTidy report on any errors or warnings, which makes it much more robust than any ad-hoc string manipulation you can come up with.

Check out this excellent article by Shlomi Noach!!

2011-05-18T14:05:00.003+02:00

Check out this excellent article by Shlomi Noach!!

Really - my life is much happier now, and as a bonus I got a free set of steak knives and even lost 20 pounds. (and MySQL!)

Managing kettle job configuration

2011-05-10T10:55:00.005+02:00

Over time I've grown a habit of making a configuration file for my kettle jobs. This is especially useful if you have a reusable job, where the same work has to be done but against different conditions. A simple example where I found this useful is when you have separate development, testing and production environments: when you're done developing your job, you transfer the .kjb file (and its dependencies) to the testing environment. This is the easy part. But the job still has to run within the new environment, against different database connections, webservice urls and file system paths.

Variables

In the past, much has been written about using kettle variables, parameters and arguments. Variables are the basic features that provide the mechanism to configure the transformation steps and job entries: instead of using literal configuration values, you use a variable reference. This way, you can initialize all variables to whatever values are appropriate at that time, and for that environment. Today, I don't want to discuss variables and variable references - instead I'm just focussing on how to manage the configuration once you already used variable references inside your your jobs and transformations.

Managing configuration

To manage the configuration, I typically start the main job with a set-variables.ktr transformation. This transformation reads configuration data from a config.properties file and assigns it to the variables so any subsequent jobs and transformations can access the configration data through variable references. The main job has one parameter called ${CONFIG_DIR} which has to be set by the caller so the set-variables.ktr transformation knows where to look for its config.properties file:

Reading configuration properties

The config.properties file is just a list of key/value pairs separated by an equals sign. Each key represents a variable name, and the value the appropriate value. The following snippet should give you an idea:

#staging database connection
STAGING_DATABASE=staging
STAGING_HOST=localhost
STAGING_PORT=3351
STAGING_USER=staging
STAGING_PASSWORD=$74g!n9

The set-variables.ktr transformation reads it using a "Property Input" step, and this yields a stream of key/value pairs:

Pivoting key/value pairs to use the "set variables" step

In the past, I used to set the variables using the "Set variables" step. This step works by creating a variable from selected fields in the incoming stream and assigning the field value to it. This means that you can't just feed the stream of key/value pairs from the property input step into the set variables step: the stream coming out of the property input step contains multiple rows with just two fields called "Key" and "value". Feeding it directly into the "Set variables" step would just lead to creating two variables called Key and Value, and they would be assigned values multiple times for all key/value pairs in the stream. So in order to meaningfully assign variable, I used to pivot the stream of key/value pairs into a single row having one field for each key in the stream using the "Row Denormaliser" step:

As you can see in the screenshot, "Key" is the key field: the value of this field is scanned to determine in which output fields to put the corresponding value. There are no fields that make up a grouping: rather, we want all key/value pairs to end up in one big row. Or put another way, there is just one group comprising all key/value pairs. Finally, the grid below specifies for each distinct value of the "Key" field to which output field name it should be mapped, and in all cases, we want the value of the "Value" field to be stored in those fields.

Drawbacks

There are two important drawbacks to this approach:

The "Row Normaliser" uses the value of the keys to map the value to a new field. This means that we have to type the name of each and every variable appearing in the config.properties file. So you manually need to keep he config.propeties and the "Denormaliser" synchronized, and in practice it's very easy to make mistakes here.
Due to the fact that the "Row Denormaliser" step literally needs to know all variables, the set-variables.ktr transformation becomes specific for just one particular project.

Given these drawbacks, I seriously started to question the usefulness of a separate configuration file: because the set-variables.ktr transformation has to know all variables names anyway, I was tempted to store the configration values themselves also inside the transformation (using a "generate rows" or "data grid" step or something like that), and "simply" make a new set-variables.ktr transformation for every environment. Of course, that didn't feel right either.

Solution: Javascript

As it turns out, there is in fact a very simple solution that solves all of these problems: don't use the "set variables" step for this kind of problem! We still need to set the variables of course, but we can conveniently do this using a JavaScript step. The new set-variables.ktr transformation now looks like this:

The actual variable assignemnt is done with Kettle's built-in setVariable(key, value, scope). The key and value from the incoming stream are passed as arguments to the key and value arguments of the setVariable() function. The third argument of the setVariable() function is a string that identifies the scope of the variable, and must have one of the following values:

"s" - system-wide

"r" - up to the root

"p" - up to the parent job of this transormation

"g" - up to the grandparent job of this transormation

For my purpose, I settle for "r".

The bonus is that this set-variables.ktr is less complex than the previous one and is now even completely independent of the content of the configuration. It has become a reusable transformation that you can use over and over.

NoSQL support lands in JasperSoft

2011-01-26T11:40:00.007+01:00

JasperSoft, one of the leading open source BI suites just announced it is delivering connectors for a range of so-called NoSQL databases. The big names are all there: Cassandra, MongoDB, Riak, HBase, CouchDB, Neo4J, Infinispan, VoltDB and Redis.

I used to explain to people that the lack of SQL support in NoSQL databases poses a challenge for traditional Business Intelligence tools, because those all talk either SQL or MDX (and maybe some XQuey/XPath). With this development, this is no longer true, and I want to congratulate JasperSoft in spearheading this innovation.

I still have a number of reservations though. Although I personally value the ability to report on data in my NoSQL database, I think its usefulness will hava a number of limitations that are worth consideration.

Admittedly I am not an expert in the NoSQL database field, but as far my knowledge goes, both the dynamo-style key/value stores like Riak, and the Bigtable-style hashtable stores like HBase and Cassandra can basically do 2 types of read operations: fetch a single object by key, or scan everything. The fetched object can be complex and contain a lot of data, and it would certainly be nice if you could run a report on that. The scan everything operation doesn't seem that useful at the report level: for all but trivial cases, you need considerable logic to make this scan useful, and I don't think a report is the right place for this. Apart from that, if the NoSQL solution was put in place because of the large data volume, then the report itself would probably need to be executed on a cluster just to achieve acceptable response time. I may be wrong but I don't think JasperReports supports that.

So, for a full scan of those NoSQL databases, connectors at the data integration end seem more appropriate. I think the integration of Hadoop with Pentaho data integration (a.k.a Kettle) is a step in the right direction, but of course only applicable if you're a Hadoop user.

Another point is data quality. Typically reporting is done on a data warehouse or reporting environment where the data quality is kept in check by processing the raw data with a data integration and quality tools. Directly reporting on any operational database can be problematic because you skip those checks. Because the NoSQL databases offer virtually no constraints, those checks are even more important. So to me this seems like another reason why NoSQL connectivity is more useful in the data integration tools.

JasperSoft also offers connectivity for the MongoDB and CouchDB docmentstores. I think that for raw reporting on the actual source documents, the same reservations apply as I mentioned in relation to the dynamo and Bigtable style solutions. But, there may be a few more possibilities here, at least for CouchDB

CouchDB has a feature called views, which allows you to "query" the raw documents using a map/reduce job. I can certainly see why it'd be useful to build a report on top of that. Of course, you would still have to implement the logic to do a useful scan, and you would still have to deal with data quality issues, but you can do it in the map/reduce job, which seems a more appropriate place to handle this than a report.

All in all, I think this is a promising development, and I should probably get my feet wet and try it out myself. But for now, I would recommend to keep it out of the wrecking tentacles of unaware business users :)

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part III

2011-01-07T02:30:00.004+01:00

This is the third article in a series providing background information to my talk for the MySQL User's conference, entitled MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications.

In the first installment, I introduced freebase, an open shared database of the world's knowledge and its JSON-based query language, the Metaweb Query Language (MQL, pronounced Mickle). In addition, I discussed the JSON data format, its syntax, its relationship with the de-facto standard client side browser scripting language JavaScript, and its increasing relevance for modern AJAX-based webapplications.

The second installment provides a brief introduction to MQL as database query language, and how it compares to the de-facto standard query language for relational database systems (RDBMS), the Structured Query Language (SQL). I argued that MQL has some advantages over SQL, in particular for programming modern webapplications. I mentioned the following reasons:

Since MQL is JSON, and JSON is JavaScript, it's a more natural fit for modern AJAX applications
MQL is almost trivial to parse, making it much easier to write tool such as editors, but also to implement advanced authorization policies
MQL is easy to generate: the structure of MQL queries is mirrored by their results. A fragment of the result can be easily augmented with a subquery making it easy to subsequently drill down into the retrieved dataset
MQL is more declarative than SQL. Both attributes and relationships are represented as JSON object properties and one need not and cannot specify join conditions to combine data from different types of objects. The "cannot" is actually A Good Thing because it means one cannot make any mistakes.
MQL is more focussed on just the data. It largely lacks functions to transform data retrieved from the database forcing application developers to do data processing in the application or middleware layer, not in the database.

In this article, I want to discuss common practices in realizing data access for applications, especially web applications, and how database query languages like SQL and MQL fit in there.

Web Application Data Access Practices

After reading the introduction of this article, one might get the idea that I hate relational databases and SQL. I don't, I love them both! It's just that when developing database applications, especially for the web, SQL isn't helping much. Or rather, it's just one tiny clog in a massive clockwork that has to be set up again and again. Let me explain...

The Data Access Problem

It just happens to be the case that I'm an application developer. Typically, I develop rich internet and intranet applications and somewhere along the line, a database is involved for storing and retrieving data. So, I need to put "something" in place that allows the user to interact with a database via the web browser.
The way I write that I need "something" so the user can interact with the database, it seems like it's just one innocent little thing. In reality, "something" becomes a whole bunch of things that need to work together:

Browsers don't speak database wire protocols - they speak HTTP to back-end HTTP servers. No matter what, there is going to be some part that is accessible via HTTP that knows how to contact the database server. Examples of solutions to this part of the problem are Common Gateway Interface (CGI) programs, server-side scripting languages like PHP or Perl (which are often themselves implemented as a CGI program) or in the case of Java, specialized Servlet classes
The component at the HTTP server that mediates between web browser and database is going to require a protocol: a set of rules that determine how a URI, a HTTP method and parameters can lead to executing a database command. Examples of approaches to design such a protocol are Remote Procedure Calls (RPC) and Representational State Transfer (REST)
There's the way back too: the application running in the web browser is going to have to understand the results coming back from the database. For data, a choice has to be made for a particular data exchange format, typically eXtensible Markup Language or JSON
The user interface at the browser end need not only understand the protocol we invented for the data exchange, Ideally it should also guide the user and be able to validate whatever data the user is going to feed it. In other words, the user interface needs to have the metadata concerning the data exchange interface.

The Webservice Solution

A typical way to tackle the data access problem is:

analyze the functionality of the application, categorizing it into a series of clear and isolated actions
identify which data flows from application to database and back for each action
decide on a data representation, and a scheme to identify actions and parameters
create one or more programs in Java, Perl, PHP, Python, Ruby or whatever fits your stack that can execute the appropriate tasks and process the associated data flows to implement the actions

For web applications, the program or programs developed in this way are typically webservices that run as part of the process of the HTTP server. The client gets to do a HTTP request, to a particular URI, using the right HTTP method, and the right parameters. The service gets to process the parameters, execute tasks such as accessing a database, and finally, sending back a HTTP response, which typically contains data requested by the application.

Development Dynamics

The problem with the webservice approach is that it isn't very flexible. It presumes the application's functionality and hence the actions are quite well-defined. Although this looks reasonable on paper, in reality development tends to be quite evolutionary.

Typically, the core functionality of applications is quite well defined, but often a lot of additional functionalities are required. Although we can pretend these could be known in advance if only we'd spend more time designing and planning in advance, in practice, they often aren't. It may seem sad, but in many cases, the best way to find out is simply to start developing, and find out along the way. Agile is the latest buzzword that captures some of these development dynamics, but there have been other buzzwords for it in the past, such as RAD (rapid application development) and DSDM (dynamic systems development method).

The problem with this approach is that it requires a lot of going back-and-forth between front- and back-end development tasks: whenever the front-end wants to develop a new feature, it is often dependent upon the back-end offering a service for it. Front-end and back-end developers often are not the same people, so what we get is front-end development cycles having to wait on back-end development cycles to complete. Or in case front-end and back-end developers are the same person, they are constantly switching between tool sets and development environments.

In part this is because front-end development is usually done in JavaScript, and although server-side JavaScript is gaining ground, the server-side is still dominated mainly by Java, PHP, C++ and ASP.NET. But it's not just a programming language problem - developing a client, and especially a frond-end for end-users, presumes a very different mindset than developing a back-end process. Front-end development should focus on usability and quality user-experience; back-end development should focus on robustness, reliability, availability, scalability and performance. Although some of these aspects influence each other, in practice, front-end development is simply a different cup of tea than back-end development.

A Simple Plan: Building a Query Service

There is a very simple solution that would largely solve the data access problem without dealing with the inefficiencies of the recurring development process: If you could build a single service that can accept any parameters, understand them, and somehow return an appropriate result, we would never have to add functionality to the service itself. Instead, the front end application would somehow have to construct the right parameters to tell the service what it wants whenever the need arises.

This sounds almost like magic, right? So we must be kidding, right? Well, we're not kidding, and it's not magic either; it's more like a cheap parlour trick.
As the title of this section suggests, a query service fits this bill.

It would be very easy to build a single service that accepts a query as a parameter, and returns its result as response. And seriously, it's not that strange an idea: many people use between one and perhaps ten or twenty different services exactly like this everyday, multiple times...and it's called a search engine.

Can't we use something like that to solve our database access problem? Well, we could. But actually, someone beat you to it already.

DBSlayer, a Webservice for SQL Queries

A couple of years ago, The New York Times released DBSlayer. DBSlayer (DataBase accesS layer) is best described as a HTTP server that acts as a database proxy. It accepts regular SQL queries via a parameter in a regular HTTP GET request, and sends a HTTP response that contains the resulting data as JSON. It currently supports only MySQL databases but announcements were made that support was planned for other database products too. DBSlayer is actually a bit more than just a database access layer, as it also supports simple failover and round-robin request distribution, which can be used to scale out database requests. But I mention it here, because it implements exactly the kind of query service that would appear to solve all the aforementioned problems.

Or would it?

Every web developer and every database administrator should realize immediately that it's not a good idea. At least, not for internet-facing applications anyway. The DBSlayer developers documented that themselves quite clearly:

Access to the DBSlayer can be controlled via firewalls; the DBSlayer should never be exposed to the outside world.

... and ...

The account DBSlayer uses to access the MySQL database should not be allowed to execute dangerous operations like dropping tables or deleting rows. Ideally, the account would only be able to run selects and/or certain stored procedures.

So there's the rub: it may be very easy and convenient from the application development point of view, but it is a horrendous idea when you think about security. A general purpose SQL query service is simply too powerful.

If a web application accidentally allows arbitrary SQL to be executed, it would be called an SQL injection vulnerability, and it would be (or at least, should be) treated as a major breach of security. Creating a service that offers exactly that behavior as a feature doesn't lessen the security concerns at all.

What about a MQL query service

In this article I tried to explain the problems that must be solved in order to arrange and manage data access for web applications. The key message is that we need to create a service that provides data access. But in doing so, we have to balance between security, functionality and flexibility.

It is fairly easy to create a webservice that exactly fulfills a particular application requirement, thus ensuring security and manageability. However, this will usually be a very inflexible service, and it will need lots of maintenance to keep up with change in application requirements. It's also easy to create a webservice that is at least as powerful as the underlying database: this would be a database proxy over HTTP, just like DBSlayer. Although it will likely never need to change since it simply passes requests on to the back-end database, it is very hard to secure it in a way that would allow external requests from possibly malignant users.

I believe that an MQL webservice actually does offer the best of both worlds, without suffering from the disadvantages. A MQL query service will be flexible enough for most web applications - MQL queries are only limited by the underlying data model, not by the set of application-specific actions designed for one particular purpose. At the same time, it will be relatively easy to efficiently analyze MQL queries and apply policies to prevent malicious use. For example, checking that a MQL query doesn't join more than X tables is quite easy.

In the forthcoming installment, I will explore the concept of a MQL webservice in more detail, and I will explain more about the MQL-to SQL project. As always, I'm looking forward to your comments, suggestions and critique so don't hesitate to leave a comment.

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part II

2011-01-07T00:40:00.010+01:00

This is the second article in a series to provide some background to my talk for the MySQL User's conference.
The conference will be held April 11-14 2011 in the Hyatt Regency hotel in Santa Clara, California.

Abstract: MQL is a JSON-based database query language that has some very interesting features as compared to SQL, especially for modern (AJAX) web-applications. MQL is not a standard database query language, and currently only natively supported by Freebase. However, with MQL-to-SQL, a project that provides a SQL adapter for MQL, you can run MQL queries against any RDBMS with a SQL interface.

The my previous post, I covered some background information on modern web applications, JavaScript and JSON.
The topic of this installment is the MQL database query language and how it compares to SQL. In a third article, I will discuss the mql-to-sql project,
which implements a MQL service for relational database systems.

MQL Queries

A MQL query is either a JSON object, or a JSON array of objects. In fact, the sample object we discussed in previous section about JSON is nearly a valid MQL query. For now, consider the following JSON representation of the chemical element Oxygen:

{
  "type": "/chemistry/chemical_element",
  "name": "Oxygen",
  "symbol": 'O',
  "atomic_number": 8,
  "ionization_energy": 13.6181,
  "melting_point": -2.1835e+2,
  "isotopes": []
}

The difference with the example from my previous post is that here, we added a key/value pair at the top, "type": "/chemistry/chemical_element", and we use an empty array ([])as value for the "isotopes" key.

So how is this a query? Didn't we claim JSON is a data format? So isn't this by definition data? And if this is data, how can it be a query?

Well, the answer is: yes, JSON is a data format, and so, yes: by definition, it must be data. But the paradox how some data can also be a query is quite easily solved once you realize that there are some pieces missing from the data. In this particular case, we left the "isotopes" array empty, whereas Oxygen has a bunch of isotopes in the real world.

This is how a JSON object can be a query:

By specifying values that describe known facts about an object, we define a filter or access path that can be used to find data in some database. For example, the JSON fragment above states that there exists an object of the type "/chemistry/chemical_element" which has the name "Oxygen", and the symbol "O", among other characterisics
By specifying special placeholders like null or an empty array or object (like [] and {} respectively), we define what data should be retrieved from the database so it can be returned in the result.

Executing a query with the Freebase MQL query editor

The easiest way to execute the query above is by using the Freebase Query Editor. Here's a screenshot:

In the screenshot, the left upper side is the area where you can type or paste your MQL query. You can then press the Run button, which is located below the query area on its right side.

When you hit the Run button, the query is sent to a special webservice called the mqlread service. The results appear in the results area on the right.

MQL Query Results

In the Text tab of the results area of the MQL query editor, you can see the raw response. It should be something like this:

{
  "code": "/api/status/ok",
  "result": {
    "atomic_number": 8,
    "ionization_energy": 13.6181,
    "isotopes": [
      "Oxygen-15",
      "Oxygen-16",
      "Oxygen-17",
      "Oxygen-18",
      "Oxygen-28",
      "Oxygen-19",
      "Oxygen-20",
      "Oxygen-23",
      "Oxygen-25",
      "Oxygen-24",
      "Oxygen-13",
      "Oxygen-22",
      "Oxygen-26",
      "Oxygen-21",
      "Oxygen-14",
      "Oxygen-27",
      "Oxygen-12"
    ],
    "melting_point": -218.35,
    "name": "Oxygen",
    "symbol": "O",
    "type": "/chemistry/chemical_element"
  },
  "status": "200 OK",
  "transaction_id": "cache;cache01.p01.sjc1:8101;2010-05-15T03:08:10Z;0004"
}

As you can see, the response is again a JSON object. The outermost object is the result envelope, which contains the actual query result assigned to the result property, as well as a few other fields with information about how the request was fulfilled. (Actually, when we hit the Run button, our MQL query was also first embedded into a query envelope before it was sent to the mqlread service, but let's worry about those details later.

Query by Example, or filling-in-the blanks

If you compare the actual query result assigned to the "result" property of the result envelope with the original query, you will notice that they are largely the same: at least, the keys of query object and result object all match up, and so do the values, except the one for the "isotopes" key: whereas the "isotopes" property was an empty array in the query, in the result it contains an array filled with elements, each representing a particular isotope of the Oxygen element. These array elements were retrieved from the Oxygen entry stored in Freebase, which was found by matching the properties like "name": "Oxygen" and "atomic_number": 8.

This illustrates an important concept of MQL queries: the query result mirrors the structure of the query itself. Wherever the query contains a special placeholder value, such as an empty array ([]), a null value or an empty object ({}), the query engine will "fill in the blanks" with data that is retrieved from the database.

Another way of putting it is to say a MQL query is a query by example.

To hammer this concept down further, consider the following similar query. In this case, it contains even more special placeholder values (namely, the null values for all but the "name" property:

{
  "type": "/chemistry/chemical_element",
  "name": "Oxygen",
  "symbol": null,
  "atomic_number": null,
  "ionization_energy": null,
  "melting_point": null,
  "isotopes": []
}

If you execute this query, you get a result that is essentially the same as the previous one. Because chemcical elements are identified by name (there are more properties that can identify a chemical element, name is just one of them), this query will match the same object in Freebase. This query specifies null for almost all other properties, and because null is a special placeholder for scalar values, the query engine responds by retrieving the values for those keys and returns them in the result.

Differences between MQL and SQL

Even this simple example reveals a lot about the differences between MQL and SQL.

Symmetry between Query and Result

In the previous example, we just saw that the query and its result have a similar structure. It's like the query is mirrored in the result. In SQL, this is very different. Let's see how different.

Assume for a minute we would have a relational database with a schema called "chemistry" and a table called "chemical_element". We could write an SQL query like this:

SELECT  name
,       symbol
,       atomic_number
,       ionization_energy
,       melting_point
FROM    chemistry.chemical_element
WHERE   name = 'Oxygen'

...and the result would look something like this:


+--------+--------+---------------+-------------------+---------------+
| name   | symbol | atomic_number | ionization_energy | melting_point |
+--------+--------+---------------+-------------------+---------------+
| Oxygen | O      |             8 |           13.6181 |       -218.35 | 
+--------+--------+---------------+-------------------+---------------+

Even if we forget for a moment that this particular SQL query doesn't handle retrieving the isotopes, the difference between the query and the result is striking - the structure of the SQL query, which is basically a piece of text, has very little to do with the structure of the result, which is unmistakenly tabular.

Everybody knows that SQL was designed for relational databases, so we probably shouldn't be too surprised that the result of the SQL query has a tablular form. But why does the query have to be text? Couldn't the query have been tabular too, leaving blanks where we'd expect to retrieve some data?

Lest I lose the proponents of SQL and the relational model here: don't get me wrong - I realize that an SQL query isn't just "a piece of text; rather SQL attempts to express facts about database state using a practical form of relational algebra, and the result represents the set that satisfies the facts stipulated by the query. And as we shall see later on, MQL actually has a few operator constructs that resemble the ones found in SQL. But for now the message is: MQL queries and their results look a lot like one another; SQL queries and their results do not.

Relational focus

I already mentioned the MQL is expressed in JSON, and we saw how both query and result are structured as objects. SQL results are always tabular. Superficially, this seems like the right thing to do for a query language that is supposed to work on and for relational database systems. But is it really that natural?

Many textbooks and courses about relational databases and SQL spend much time and effort on the topic of normalization, and rightly so! One of the great achievements of the relational model and normalization is that it helps to minimize or even eliminate data integrity problems that arise in non-relational storage structures and their inherent redundancy.

A typical textbook or course may start with a modelling exercise with some real-world, unnormalized (sub 1NF) data as input. The first mental step is to normalize that data to the first normal form (1NF), by splitting off multivalued attributes (a single data item with a list of values) and their big brother, repeating groups (a "table-inside-a-table"), to separate tables, on and on until all repeating groups and multi-valued attributes are eliminated. In the next phases of normalization, different forms of data redundancy are removed, moving up to even higher normal forms, typically finishing at the third normal form (3NF) or the Boyce-Codd normal form (BCNF).

The result of this normalization process is an overview of all separate independent sets of data, with their keys and relationships. From there, it's usually a small step to a physical database design. For example, in our Oxygen example, the list of isotopes is a multi-valued attribute. In a proper relational design, the isotopes would typically end up in a separate table of their own, with a foreign key pointing to a table containing the chemical elements.

The textbook usually continues with a crash course in SQL, and chances are the SELECT syntax is the first item on the menu. Within a chapter or two, you'll learn that although normalized storage is great for data maintenance, it isn't great for data presentation. Because applications and end users care a lot about data presentation, you'll learn how to use the JOIN operator to combine the results from related database tables. Usually the goal of that exercise is to end up with a result set that is typically 1NF, or at least, has a lower normal form than the source tables.

Don't get me wrong: this is great stuff! The relational model isn't fighting redundant data: its fighting data integrity issues, and the lack of control that results from storing redundant data. By first decomposing the data into independent sets for storage, and then using things like JOIN operations to re-combine them, SQL offers a good method to master data integrity issues, and to deliver consistent, reliable data to applications and end-users.

It's just a pity SQL forgot to finish what it started. Recall that the text-book modeling exercise started by eliminating repeating groups and multi-valued attributes to achieve 1NF. What SQL queries should we use to transform the data back to that format?

SQL turns out to be so single-mindedly focused on the relational model that it simply can't return sub-1NF, unnormalized data. Much like the square from flatland can't comprehend the sphere from spaceland, SQL simply hasn't got a clue about nested data structures, like multi-valued attributes and repeating groups.

Somewhere along the way, SQL forgot that the text-book course started with real-world, unnormalized data, full of repeating groups and multivalued attributes.

This represents quite a share of challenges for database application development. One class of software solutions that deal with solving this problem are the so-called object-relational mappers (ORM). It would not do enough credit to the ORM's to claim that their only purpose is to solve this problem, but it's definitely a major problem they take care of.

Parsing Queries

Everyone that has tried it knows that it isn't exactly trivial to write a fast yet fully functional SQL parser. Being able to parse SQL is a requirement for tools like query editors and report builders, but also for proxies and monitoring tools.

Parsing MQL on the other hand is almost trivially simple. At the application level, this would in theory make it quite easy to implement advanced access policies and limit the complexity of the queries on a per user or role basis.

Generating Queries

One of the things I like about MQL is that applications have to do a lot less work to formulate a query that drills down into some detail of a previously returned data set. For example, from the the result of our query about Oxygen, we just learned that there is an isotope called "Oxygen-16". Suppose we want to know more about that particular isotope, say, its relative abundance, and whether it's stable or not.

With SQL, we would have to construct a new algebraic expression that somehow combines the set of chemical elements with the set of isotopes. Although we would certainly need some data from the tabular result obtained from the previous query, we have little hope of actually re-using the query itself - at the application level, the SQL query is most likely just a piece of text, and it's probably not worth it to use string manipulation to forge a new query out of it.

Here's an example which shows the parts that should be added to accommodate this requirement, just to show that such a change isn't localized to just one spot in the original query:

SELECT  e.name
,       e.symbol
,       e.atomic_number
,       e.ionization_energy
,       e.melting_point
,       i.name
,       i.natural_abundance
,       i.stable
FROM    chemistry.chemical_element e
INNER JOIN chemistry.isotope i ON e.name = e.element_name
WHERE   e.name = 'Oxygen'
AND     i.name = 'Oxygen-16'

I won't let my head explode over the string manipulation code required to change the original query into this one. If you like, post clever solutions as a comment to this post :)

With MQL, this task is considerably easier. The query and the result have a high degree of correspondence. In our application, both would typically be represented as objects or structs. In most programming languages, it is trivial to go from the original Oxygen Query to an augmented form that retrieves the details about the isotope "Oxygen-16". This is especially true for JavaScript, where we'd simply write something like:

//execute the query and obtain the result.
//note: in a real application this would typically be an asynchronous request
//to the mqlread service which would accept and return JSON strings.
//For simplicity sake we pretend we have a mqlRead function that can accept
//a regular JavaScript object literal, convert it into a JSON string, and
//call the mqlread service synchronously, parse the the JSON query result
//into a JavaScript object and return that to the caller.

var queryResult = mqlRead({
  type: "/chemistry/chemical_element",
  name: "Oxygen",
  symbol: null,
  atomic_number: null,
  ionization_energy: null,
  melting_point: null,
  isotopes: []
});
 
//assign a subquery for "Oxygen-16" to the isotopes property of the queryResult.
//Remember, the queryResult has the same structure as the original query, just
//with the null's and the empty arrays filled with data.

queryResult.isotopes = {
  name: "Oxygen-16",
  natural_abundance: null,
  stable: null
};

//execute the modified query:

queryResult = mqlRead(queryResult);

(In the first example where we discussed the Oxygen query, you might've noticed that in the query result, the isotopes member was an array of strings, each representing a particular isotope. So naturally, you might be tempted to think that the value of the isotopes property is an array of strings. However, this is not quite the case. Rather, the isotopes property stands for the relationship between elements and its isotopes. Due to the form of the original query (having the empty array for the isotopes property), the MQL query engine responds by listing the default property of the related isotopes. In freebase, name is a special property and typically that's used as default property. So in that previous query result, the isotopes were merely represented only by their name. In the example above however, we assign a single object literal to the isotopes property which identifies one particular isotope. Because the isotopes property represents a relationship, the query should be read: "find me the oxygen element and the related isotope with the name Oxygen-16", and not "find me the oxygen element that has Oxygen-16 as its isotope".)

Of course, it's entirely possible to design data structures to hold all the data you need to generate your SQL queries (a query model). You can easily come up with something that would allow such a change to be made just as easy. But you need at least one extra step to generate the actual SQL string to send to the database. And of course, you need some extra steps again to extract data from the resultset for forging more queries.

In MQL, the effort to go from query to result and back are about as minimal as it can get. This results in less application code, which tends to be easier to understand.

SQL is declarative, but MQL is more so

When discussing its merits as a programming language, it is often mentioned that SQL is declarative rather than procedural. Often this is presented as an advantage: with a declarative language like SQL, we get to focus on the results we want, whereas a procedural language would force us to code all kinds of details about how these results should be obtained. Or so the story goes.

I won't deny SQL is declarative. For example, I don't need to spell out any particular data access algorithm required to find the Oxygen element and it's isotopes, I just write:

SELECT      e.name
,           e.symbol
,           e.atomic_number
,           e.ionization_energy
,           e.melting_point
,           i.name
,           i.natural_abundance
,           i.stable
FROM        chemistry.chemical_element         e
INNER JOIN  chemistry.chemical_element_isotope i
ON          e.atomic_number = i.atomic_number
WHERE       e.name = 'Oxygen'

But still: in order to successfully relate the chemical_element and chemical_element_isotope tables, I need to spell out that the values in chemical_element_isotope's atomic_number column have to be equal to the value in the atomic_number column of chemical_element. Come to think of it, how can I know the relationship is built on atomic_number, and not on symbol or name? And heaven forbid we accidentally compare the wrong columns, or forget one of the join conditions...

Now compare it to the equivalent MQL query:

{
  "type": "/chemistry/chemical_element",
  "name": "Oxygen",
  "symbol": null,
  "atomic_number": null,
  "ionization_energy": null,
  "melting_point": null,
  "isotopes": [{
    "name": null,
    "natural_abundance": null,
    "stable": null
  }]
}

The SQL query may be declarative, but compared to the MQL query, it requires a lot more knowledge of the underlying data model. All we had to do in MQL, is specify an isotopes property, and list whatever we want to retrieve from the corresponding isotope instances. The only way we can mess up the MQL query is when we specify the wrong property names, in which case our query would simply fail to execute. In the SQL query, we could've been mistaken about which columns to compare, and get no result at all, or worse, a rubbish result. And with just a bit of ill luck, we can accidentally cause a cartesian product. Just for the hell of it, spot the error in the following SQL statement:

SELECT      e.name
,           e.symbol
,           e.atomic_number
,           e.ionization_energy
,           e.melting_point
,           i.name
,           i.natural_abundance
,           i.stable
FROM        chemistry.chemical_element         e
INNER JOIN  chemistry.chemical_element_isotope i
ON          e.atomic_number = e.atomic_number
WHERE       e.name = 'Oxygen'

Computational Completeness

Everybody with some experience in SQL programming knows that SQL is much more than a database query language. Even standard SQL is chock-full of operators and functions that allow you to build complex expressions and calculations. In fact, most SQL dialects support so many functions and operators that the manual needs at least a separate chapter to cover them. Algebra, Encryption, String formatting, String matching, Trigonometry: these are just a few categories of functions you can find in almost any SQL dialect I heard of.

By contrast, MQL is all about the data. MQL defines a set of relational operators, but their function and scope is limited to finding objects, not doing calculations on them. There is exactly one construct in MQL that resembles a function, and it is used for counting the number of items in a result set.

Personally, I think MQL would be better if it had a few more statistical or aggregate constructs like count. But overall, my current thinking is that the fact that MQL lacks the function-jungle present in most RDBMS-es is actually A Good Thing(tm). At the very least, it ensures queries stay focused on the data, and nothing but the data.

Next Time

In this article I discussed the basics of the MQL query language, and I compared SQL and MQL on a number of accounts. In the next installment, I will discuss how this relates to developing data access services for web applications.

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part I

2011-01-06T22:10:00.011+01:00

Yesterday, I wrote about how I think this year's MySQL conference will differ from prior editions. I also wrote that I will attend and that I will be speaking on MQL-to-SQL.

I promised I would explain a little bit more background about my talk, so here's the first installment.

Abstract: MQL is a JSON-based database query language that has some very interesting features as compared to SQL, especially for modern (AJAX) web-applications. MQL is not a standard database query language, and currently only natively supported by Freebase. However, with MQL-to-SQL, a project that provides a SQL adapter for MQL, you can run MQL queries against any RDBMS with a SQL interface.

This article covers mostly background information on modern web applications, JavaScript and JSON. This background information should help you understand why a JSON-based database query language is a good fit for modern web applications. A following installment will discuss the MQL database query language and how it relates to SQL. A third article will cover the mql-to-sql project itself.

MQL, a JSON-based database query language

MQL (pronounced as Mickle) is an abbreviation of Metaweb Query Language, which is the JSON-based database query language natively supported by Freebase. If you're unfamiliar with the terms Metaweb and Freebase, see this quote from Wikipedia about Metaweb:

Metaweb Technologies, Inc. is a United States company based in San Francisco that is developing Freebase, described as an "open, shared database of the world's knowledge".

...and this quote about Freebase:

On March 3, 2007 Metaweb publicly announced Freebase, described by the company as "an open shared database of the world's knowledge," and "a massive, collaboratively-edited database of cross-linked data." Often understood as a wikipedia-turned-database, Freebase provides an interface that allows non-programmers to fill in structured, or 'meta-data', of general information, and to categorize or connect data items in meaningful, or 'semantic' ways.

So, Freebase is a database of just about everything (think of it as a machine readable version of wikipedia), and MQL is its query language.

As a quick primer on the MQL query language, here's a simple MQL query (taken from the Freebase manual)

{
  "query": {
    "type":"/music/artist",
    "name":"The Police",
    "album":[]
  }
}

...and here's the result:

{
  "status": "200 OK", 
  "code": "/api/status/ok", 
  "transaction_id":"cache;cache01.p01.sjc1:8101;2008-09-18T17:56:28Z;0029",
  "result": {
    "type": "/music/artist", 
    "name": "The Police",
    "album": [
      "Outlandos d'Amour", 
      "Reggatta de Blanc", 
      "Zenyatta Mondatta", 
      "Ghost in the Machine", 
      "Synchronicity"
    ]
  }
}

I won't discuss these samples just yet - the MQL query language is the topic of the next installment. This is just a quick sample to give you an idea what MQL queries and their results look like. If you're didn't recognize the syntax already - read on, the JSON syntax is covered in the next section of this article.

The collection of data accumulated in Freebase is impressive - you should check it out sometime. However, this post is not so much about Freebase - it's the MQL query language I want to discuss here.

JSON

MQL is based on JSON (pronounced as Jason), which is an abbreviation of JavaScript Object Notation. If you're not familiar with JSON, hang on and read all of this section - it provides just enough background to understand why it is highly relevant for modern web-applications, and it provides a good-enough description of the JSON syntax for you to read and write MQL queries. You might also want to check out the Wikipedia entry on JSON for an objective overview.

If you are already familiar with JSON, then you can safely skip through to the next section. But please do take a minute to review the JSON object code sample - it is used as basis for developing simple MQL queries in the next article in this series.

Now, back to JSON.

What is JSON?

JSON is a data exchange format. Syntactically, JSON is a proper subset of JavaScript, the de-facto web browser scripting language. The JSON JavaScript subset is defined in such a way that it can express JavaScript values and objects of arbitrary complexity. However, JSON cannot be used to create an executable program - all dynamic, executable elements (functions) have been stripped away.

Because JSON literally is JavaScript (just not the full set), JavaScript programs can easily read and write data expressed as JSON. For this reason, JSON is often characterized as a JavaScript object serialization format.

An excellent description of the JSON syntax can be found at the JSON homepage. But if you don't feel like reading up on the details of JSON, no worries - you just need to remember a few JSON features to read and write MQL queries:

3 scalar* data types:
- strings - for example: "Oxygen", or 'O'
- numbers - like 8 (integer), 13.6181 (float) and -2.1835e+2 (float)
- booleans - true and false are the only possible boolean values
2 composite data types:
- arrays - an ordered comma-separated list of values, enclosed in square braces: ["Oxygen-16", "Oxygen-17", "Oxygen-18"]. The individual values in an array are usually referred to as elements
- objects - an unordered comma-separated list of key/value pairs (which are uniquely named items), enclosed in curly braces:
```
{
  "name": "Oxygen",
  "symbol": 'O',
  "atomic_number": 8,
  "ionization_energy": 13.6181,
  "melting_point": -2.1835e+2,
  "isotopes": ["Oxygen-16", "Oxygen-17", "Oxygen-18"]
}
```
  The key/value pairs of an object are usually called properties or members. Note that name and value are separated by a colon (:). Keys are strings, and have to be quoted. Values can be of any of the data types described above, including arrays (like the "isotopes" property in the example above) and objects.
A special null-value (which actually constitutes a data type of its own)

(* I should point out that JavaScript is an object-oriented language. At runtime, all data types described above, including the array and "scalar" types, are in fact objects. Objects are by definition composite, and not scalar. However, the list above is about JSONs syntactical constructs, not about their runtime JavaScript representation)

As you can see, JSON provides a few very simple rules. Yet it allows you to build data structures of arbitrary complexity: the object example above illustrates how you can bundle a collection of named values together to represent structures of a higher order. In this particular case, we denoted an object that represents the chemical element Oxygen. And, we needn't have stopped here: we could've added more key/value pairs to represent other complex properties of the element Oxygen, representing things like the person who first discovered it, a list of well known substances that contain Oxygen and so on and so forth.

AJAX and JSON

I already mentioned something about JSON in relation to JavaScript and modern web applications. By modern web applications, I mean web applications that offer a rich and highly interactive user interface that is based on AJAX (An Acronym for Asynchronous JavaScript And XML) technology.

A key feature of AJAX is the usage of client-side JavaScript code for maintaining non-blocking background communication with the web server. This is typically done using a specialized object called the XMLHttRequest. AJAX applications also tend to use JavaScript to dynamically change the contents or appearance of the page (a technique called dynamic HTML or DHTML). This allows web developers to create applications that can avoid a relatively slow page reload most of the time, making the application appear more responsive.

Going by the meaning of the AJAX acronym, you may be under the impression that AJAX applications are all about using asynchronous XML data exchange using the XMLHttpRequest, and thus the relevance or need for JSON might be lost on you. I would say that, yes, you're not wrong: AJAX is often implemented by using the XMLHttpRequest object to communicate with the server using XML messages. But for several reasons, JSON is gaining popularity as data exchange format instead of XML, and techniques like JSONP are used in addition to the XMLHttpRequest object. This technique actually has an advantage over using the XMLHttpRequest because it can be used to do requests to services that reside on another domain than the current web page, whereas this is not allowed with the XMLHttpRequest (at least, not by default and not without explicitly asking the user for confirmation). This makes JSONP a great tool for creating mash-up applications.

I'm sure you're not surprised to hear from me that the internet offers many places where you can get your XML vs JSON brawl on - I'm not particularly interested in that discussion, and I don't feel anybody has to choose sides. The point I want to bring across is that for AJAX applications, JSON is a respected data exchange format. It is excellently supported by both web browsers as well as popular AJAX frameworks, and companies like Amazon, YAHOO! and Google deliver more and more webservices that use JSON as data exchange format.

Next time

Now that you've seen how JSON works, and how it relates to AJAX web applications, you're ready to take a look at MQL queries. This is the topic of the next blog post.

Speaking at the MySQL conference 2011

2011-01-06T01:43:00.006+01:00

I just received a confirmation that my presentation proposal for the MySQL user conference 2011 was accepted! The title for my proposal is MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications, and it covers pretty much everything implied by the title.

As always, the Hyatt Regency Hotel in Santa Clara, California serves as the venue. The conference will be held from April 11-14. Except for the venue and period, I think this year's conference will bear few similarities to previous editions. Let me try and explain.

This year's theme is "MySQL, the ecosystem and Beyond". This means that the conference is using MySQL as an anchor for a myriad of topics which are of interest to a large majority of MySQL users. This explicitly leaves room for subjects that may not be directly related to the MySQL product proper.

So, not only products with a direct link to MySQL, such as drizzle and MariaDB are covered; NoSQL databases like CouchDB, MongoDB and Cassandra are quite well represented and the conference committee actively reached out to the PostgreSQL community to submit proposals. Traditional topics like scalability, performance and tuning remain strongly present, just like high availability, failover, and replication. As always, some of the world experts in this field will be speaking. In addition, infrastructural topics like virtualization and cloud computing are well represented (but of course, especially with regard to database management); One of the things I'm thrilled about is the presence of developer and applicattion centric topics like GIS, rapid application development, and object relational mapping.

Just take a look at the full schedule to get a taste of what this event will be offering. Personally, I think it's a great setup, and I'm happy and honored to attend!

In a series of upcoming blog posts, I plan to explain some of the subject matter regarding my own talk. But for now, I just want to tell you that I think this is going to be a great conference! I'm looking forward to attending a lot of high quality sessions, and meeting world leading experts in the MySQL and open source database ecosystem. I hope to see you there!

Substituting variables in Kettle Parameter values

2010-12-18T02:43:00.007+01:00

Kettle (a.k.a. Pentaho Data Integration) jobs and transformations offers support for named parameters (as of version 3.2.0). Named parameters form a special class of ordinary kettle variables and are intended to clearly and explicitly define for which variables the caller should supply a value.

One of my pet projects, the pentaho auto-documentation solution kettle-cookbook, uses two named parameters called INPUT_DIR and OUTPUT_DIR. These allow you to specify the directory that contains the BI content that is to be documented (such as kettle transformation and job files, action sequence files and mondrian schema files), and the directory to store the generated documentation.

Several kettle-cookbook users ran into problems attempting to use variable references in the values they supplied for the INPUT_DIR and OUTPUT_DIR variables. In this case, the variables referenced in the supplied parameter values would be set by adding entries in kettle.properties file. I just committed revision 64 of kettle-cookbook which should fix this problem. In this article I briefly discuss the solution, as I think it may be useful to other kettle users.

Substituting Kettle Variable References

Kettle doesn't automatically substitute variable references in parameter values (nor in ordinary variable values). So, if you need to support variable references inside parameter values, you have to substitute the variables yourself.

Variable substitution in Kettle 4.01 and up

As of Kettle version 4.01, the Calculator step supports a calculation type called "variable substitution in string A" that is intended exactly for that purpose. I have tested this but unfortunately in 4.01 it doesn't seem to work, at least not for the built-in variable ${Internal.Transformation.Filename.Directory} which I used in my test. In the latest stable version, Kettle 4.10 it does work as advertised, I would recommend using this method if you're a user of Kettle 4.10 (or later).

Variable substitution in earlier Kettle versions

I have committed myself to making kettle-cookbook work on kettle 3.2.0, as my sources tell me that this is still an often-used version in many production environments. I'm even prepared to make kettle-cookbook work on Kettle versions earlier than 3.20, should there be sufficient demand for that. Anyway, the bottom line is, these versions do not support the "variable substitution in string A" calculation in the Calculator step, so you have to resort to a little trick.

A Kettle 3.2.0 transformation to substitute variables in parameters

For kettle-cookbook, I added a single transformation called substitute-variables-in-parameters.ktr as the first transformation of the main job.

The substitute-variables-in-parameters.ktr transformation uses a "Get Variables" step to read the values of the INPUT_DIR and OUTPUT_DIR parameters. The values are then processed by a javascript function which substitutes all variable references with their values. Finally, a "Set Variables" step overwrites the original value of the variables with their replaced value.

The code for the JavaScript step is shown below:


function replace_variables(text){
    var re = /\$\{([^\}]+)\}|%%([^%]+)%%/g,
        match, from = 0,
        variable_name, variable_value, 
        replaced_text = ""
    ;

    while ((match = re.exec(text)) !== null) {
        variable_name  = match[1] ? match[1] : match[2];
        variable_value = getVariable(variable_name, "");
        replaced_text += text.substring(from, match.index);
        replaced_text += variable_value;
        from = match.index + match[0].length;
    }
    replaced_text += text.substring(from, text.length);
    return replaced_text;
}

var replaced_input_dir = replace_variable(input_dir);
var replaced_output_dir = replace_variable(output_dir);

The script first defines function replace_variables(text) which accepts the parameter value, and returns the substituted value. Then it calls the function, applying it to the input_dir and output_dir fields from the incoming stream. These fields originate in the preceding "Get variables" step which assigns them the value of the INPUT_DIR and OUTPUT_DIR variables. The output of the replace_variables() function is assigned to the replaced_input_dir and replaced_output_dir javascript variables, which leave the JavaScript step as fields of the outgoing stream. In the final "Set variables" step, the replaced_input_dir and replaced_output_dir fields are used to overwrite the original value of the INPUT_DIR and OUTPUT_DIR values.

The `replace_variables()` function

Let's take a closer look at the replace_variables() function.

The heart of the function is formed by a while loop that executes a javascript regular expression called re that matches variable references.

The regular expression itself is defined in the top of the function:


    var re = /\$\{([^\}]+)\}|%%([^%]+)%%/g,

It's intention is to recognize variable references of the form ${NAME} and %%NAME%%. The part of the pattern for the name is enclosed in parenthesis to form a capturing group. As we shall see later on, this allows us to extract the actual name of the referenced variable. The trailing g indicates that the pattern should be matched anywhere in the string. This is necessary because we want to replace all variable references in the input text, not just the first one.

The regular expression object is used to drive the while loop by calling its exec() method. In case of a match, the exec() returns an array that describes the text matched by the regular expression. If there's no match, exec() returns null


    while ((match = re.exec(text)) !== null) {
       ...
    }

If there is a match, we first extract the variable name:


variable_name  = match[1] ? match[1] : match[2];

The first entry of the match array (at index 0) is the text that was matched by the pattern as a whole. The array contains subsequent elements for each capturing group in the regular expression. Because our regular expression re has 2 capturing groups, the match array contains two more elements. If the variable is of the form ${NAME}, the element at index=1 contains the variable name. If it's of the form %%NAME%%, it will be contained in the element at index=2.

Once we have the variable name, we can use the getVariable() javascript function to obtain its value:


        variable_value = getVariable(variable_name, "");

The getVariable() is not a standard javascript function, but supplied by the kettle javascript step.

To perform the actual substitution, we take the substring of the original text up to the location where the variable reference was matched. This location is conveniently supplied by the match array:


        replaced_text += text.substring(from, match.index);

Right after that location, we need to put the variable value instead of its name:


        replaced_text += variable_value;

The last action in the loop is to remember to location right behind the last replaced variable reference, so we can pick up at the right location in the original value the next time we match a variable:


        from = match.index + match[0].length;

Right after the loop, we need to copy the final piece of original text occurring right behind the last variable reference to yield the complete replaced text:


    replaced_text += text.substring(from, text.length);

Odds and Ends

While the substitute-variables-in-parameters.ktr transformation works great for its intended purpose, substituting variables in the known parameters INPUT_DIR and OUTPUT_DIR, it is not really applicable beyond kettle cookbook. What you'd really want to have is a job that replaces variables in all parameters, not just those that are known in advance.

As it turns out this is actually almost trivial to achieve, however to solution is a bit too long-winded for this post. If anyone is interested in such a solution, please post a comment and let me know, and I'd be happy to provide it.

UPDATE: A solution that substitutes all variable references occurring in the parameter values of the containing job is now available at the kettle exchange area in the Pentaho wiki.

Parameterizing SQL statements in the Kettle Table Input step: Variables vs Parameters

2010-12-09T09:25:00.006+01:00

I get this question on a regular basis, so I figured I might as well blog it, in the hope it will be useful for others. Here goes:

Let's say that I want to delete all records that match an id from a set of tables. The table names come in as rows into the Execute SQL Script step (check execute for every row). Next I write:
DELETE FROM {table_name} WHERE id = {identifier}
as the SQL to execute. In the parameters grid at the bottom right, I have two fields: table_name and identifier. What is the syntax for substituting the table_name and identifier parameters in the sql script?

(Although this particular question focuses on the "Execute SQL Script" step, it also applies to the "Table Input" step, and probably a few more steps I can't recall right now.)

The parameters grid can be used for prepared statement value placeholders. In the SQL statement these placeholders are denoted as questionmarks (?). These are positional parameters: they get their value from those fields in the incoming stream that are entered in the parameters grid, in order.
Here's an example of the correct usage of these placeholders:

DELETE FROM myTable WHERE id = ?

Here, the ? in the WHERE clause will be bound to the value of the first field from the incoming stream entered in the parameters grid. Because there is only one such placeholder, there can be only one field in the parameters grid.

An important thing to realize is that these parameters can only be used to parameterize value expressions. So, this kind of parameter does not work for identifiers, nor do they work for structural elements of the SQL statement, such as keywords. So this kind of parameter cannot be used to parameterize the table name which seems to be the intention in the original example posed in the question.

There is a way to parameterize the structural elements of the SQL statement as well as the parameters. You can apply variable substitution to the SQL statetment.

Kettle Variables can be defined by a Set Variables step, or by specifying parameters at the transformation level. They get their value from "the environment": for example, parameters get their value initially when the transformation is started, and regular variables are typically set somewhere in the job that is calling your transformation.

In text fields, including the SQL textarea of the Table input step or the Execute SQL Script step, you denote those variables with this syntax: ${VARIABLE_NAME}. So to parameterize the table name we could use something like this:

DELETE FROM ${TABLE_NAME}

In order to force kettle to apply variable substitution to the SQL statement, you have to check the "variable substitution" checkbox. If this checkbox is checked, then all variables are simply substituted with their (string)value during transformation initialization. This is a lot like the way macro's are substituted by the pre-processor in C/C++ code.

When comparing variables with parameters, two important things should be mentioned here:

Unlike value placeholders, variables can be used to manipulate any aspect of the SQL statement, not just value expressions. The variable value will simply become the text that makes up the SQL statement, it is your responsibility it results in a syntactically valid and correct SQL statement.

Variables are evaluated once during transformation initalization. So if you want to vary the variable value, you'll have to call the transformation again for the change to take effect. For the same reasons, you cannot set the value of a variable and read it within the same transformation: setting the variable value occurs at runtime, but evaluating it occurs at initialization time.

Finally, here's a screenshot that summarizes these different ways to parameterize SQL statements in kettle:

If you want to read more about this topic, it's covered in both our books Pentaho Solutions and Pentaho Kettle Solutions. Another title you might be interested in is Maria Roldan's Pentaho 3.2 Data Integration: Beginner's Guide.

Back to blogging....

2010-08-11T01:05:00.002+02:00

It has been a while since I posted on my blog - in fact, I believe this is the first time ever that more than one month passed between posts since I started blogging. There are a couple of reasons for the lag:

Matt Casters, Jos van Dongen and me have spent a lot of time finalizing our forthcoming book, Pentaho Kettle Solutions (Wiley, ISBN: 978-0-470-63517-9). The book is currently being produced, and should be available according to schedule in early September 2010. If you're interested, you might like to read one of my earlier posts that explains the organization and outline of the book.

(I should point out that we have reorganized the outline as the project progressed, so the final result will not have all the chapters mentioned in that post. We do however cover most of the topics mentioned.)

I have been checking out Quipu, a promising Open Source data warehouse management solution. Quipu provides a repository-based extensible code-generator that allows you to generate and maintain a data warehouse based on the Data Vault model. One of the things I want to do in the short term is write templates that allows it to work for MySQL, and after that, I want to see if I can get Quipu to generate Kettle Jobs and transformations.

I have been working on a couple of software projects. Two of them are currently available as open source on google code:

mql-to-sql
This allows you to use the Metaweb Query Language (MQL) to query a RDBMS. If you're wondering what this is all about: MQL is the query language used by Freebase (which is a collaborative "database-of-everything" or "wikipedia-gone-database").

While Freebase is interesting in its own right, I am particularly enthused about the MQL query language. I feel that MQL is an exceptionally good solution for flexible, expressive and secure data access for modern (AJAX) Web applications. Even though MQL was not developed with relational database systems in mind, I think it is a pretty good fit.

Anyway, this is very much a work in progress, and I appreciate your feedback on it. If you're interested, you can read a bit more about my take on RDBMS data access for web applications and MQL on the mql-to-sql project home page. I have also put up an online demo that allows you to query the sakila MySQL sample database using MQL.

kettle-cookbook
This is a project that provides auto-documentation for Kettle (a.k.a. Pentaho Data Integration).

The project consists of a bunch of Kettle Jobs and transformations (as well as some XSLT stylesheets) that extract data from Kettle Jobs and transformation and transform it into a collection of human-readable HTML documents along with a table of contents. The resulting documentation looks and feels a bit like JavaDoc documentation.

If all goes well, I will be presenting kettle-cookbook in a Pentaho web seminar, which is currently scheduled for September 15, 2010

I've been enjoying 4 weeks of vacation (I started working this week). There's not much to tell about that, other than that it was great spending a lot of time with my family. I plan to do this more often, and now that Kettle Solutions is finished I should be able to find more time to do it.

I've been looking at two emerging HTML5 APIs for client-side structured storage, the Web SQL Database API and the Indexed DB API.

I have developed a few thoughts about the ongoing debate (see this and article for some background) about which one is better and I see a role for something like MQL here too. I will probably write something about this in the next few weeks

Yesterday, I got wind of the JS1k contest! Basically, the challenge is to write an interesting standalone JavaScript demo program that must be no larger than 1024 bytes. It is amazing and inspiring to see what people manage to do with a modern browser in 1k of JavaScript code.

I decided to try it myself, and you can find my submission here: An interactive SQL query tool for the Web SQL DB API.

Essentially, you get a textarea where you can enter arbitrary SQLite queries, and button to execute the SQL, and a result area that will print a feedback message and a result table (if applicable). As a bonus, there's a button to get a listing of the available database objects (using a query on sqlite_master) and an explain button to show the query plan of the current SQL statement.

The demo works on recent versions of Google Chrome, Apple Safari and Opera. It can run offline, and does not require any plugins. I should say that I expect my submission will be rejected by the judges since the demo is not functional on Mozilla Firefox, which is a requirement. (That is, the script will detect that the Web SQL Database API is not supported and print a message to that effect). However, it was still fun to try my hand at it.

Ok - that's it for now. I will try and post more regularly and write about these and other things in the near future. Don't hesitate to leave a comment if you have any questions or suggestions.

A small issue of SQL standards

2010-05-26T21:45:00.009+02:00

From a functional perspective, the core SQL support in all major and minor RDBMS-es is reasonably similar. In this light, it's sometimes quite disturbing to find how some very basic things work so differently across different products. Consider this simple statement:

SELECT  'a' /* this is a comment */ 'b'
FROM    onerow

What should the result be? (You can assume that onerow is an existing table that contains one row)

It turns out popular RDBMS-es mostly disagree with one another.

In Oracle XE, we get this:

SELECT  'a' /* comment */ 'b'
                         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

PostgreSQL 8.4 also treats it as a syntax error, and thus seems compatible with Oracle's behavior:

ERROR:  syntax error at or near "'b'"
LINE 1: SELECT  'a' /* this is a comment */ 'b'

In Microsoft SQL Server 2008 we get:

b
-
a

(1 rows affected)

As you can see, MS SQL treats the query as SELECT 'a' AS b FROM onerow.

Finally, in MySQL 5.1, we get:

+----+
| a  |
+----+
| ab |
+----+
1 row in set (0.00 sec)

So in MySQL, its as if the comment isn't there at all, and as if the string literals 'a' and 'b' are actually just one string literal 'ab'.

So what does the SQL standard say? In my copy of the 2003 edition, I find this (ISO/IEC 9075-2:2003 (E) 5.3 <literal>, page 145):

Syntax Rules
1) In a <character string literal> or <national character string literal>, the sequence:
<quote> <character representation>... <quote> <separator> <quote> <character representation>... <quote>
is equivalent to the sequence
<quote> <character representation>... <character representation>... <quote>

If we lookup the definition of <separator>, it reads:

<separator> ::= { <comment> | <white space> }...

So in this case, MySQL does the "right" thing, and basically ignores the comment, treating 'a' and 'b' as a single string constant 'ab'.

UPDATE 1: As always, the devil is in the details. And trust me, the SQL standard has many of them (details that is - I'll leave it up to the reader to decide for the devils, although I have a suspicion in a particular direction). Read on, and make sure to read Nick's comment on this post - it turns out PostgreSQL seems to behave exactly according to the standard in this case.

UPDATE 2: Serg also posted a comment citing yet another part of the standard that states that all comments implicitly count as a newline. This would mean that there doesn't have to be a literal newline character in or following the comment. In this case, my original remark that MySQL got it right would hold again.

I should state that I think very highly of both Nick and Serg, and as far as I am concerned, they're both right. I can't help but seeing this as yet more support for my statement that the SQL standard is so complex it is almost or perhaps completely impossible to get it right.

Do you find this too bold? If so, I'd really love to hear your thoughts on it. Please help us solve this paradox, I only want to understand what the standard really says.

If you try the same thing with a single line comment, all products mentioned react the same as with the initial query, except for PostgreSQL, which now treats the query according to the standard.

Now don't get me wrong. This post is not designed to bash or glorify any of the products mentioned. I think all of them are great in their own way. I am quite aware that although MySQL happens to adhere to the standard here, it violates it in other places. Finally, I should point out that I don't have a specific opinion on what the right behavior should be. I just want it to be the same on all platforms.

At the same time, I realize that for SQL it's probably too late - up to an extent, incompatibility is considered normal, and database professionals tend to be specialized in particular products anyway. So I'm not holding my breath for the grand unification of SQL dialects.

When I encountered this issue, I did have to think about that other rathole of incompatibilities I have to deal with professionally, which is web-browsers. An interesting development there is the HTML 5 specification, which actually defines an algorithm for parsing HTML - even invalid HTML. This is quite different from the approach taken by most standards, which typically define only an abstract grammar, but leave the implementation entirely up to the vendors. In theory, providing parsing instructions as detailed as done in HTML 5 should make it easier to create correct parsers, and hopefully this will contribute to a more robust web.

Anyway. That was all. Back to work...

UPDATE: I just heard that Sybase (unsurprisingly) behaves similar to MS SQL for this query (that is, query is valid, and returns 'a' in a column called b). I checked SQLite myself, which is also in that camp.

Nick also pointed out that LucidDB also provides a standard compliant implementation, in other words, it behaves exactly like PostgreSQL for this particular query. However, Julian, who was and is closely involved in LucidDB agrees with Serg that the comment should probably count as a newline, and filed a bug for LucidDB.

I checked Firebird 2.1.3, and they are in the Oracle camp: in both cases, the query gives a syntax error.

MySQL, Oracle and NoSQL: In the grand scheme...

2010-05-25T11:00:00.001+02:00

...NoSQL is just larger than a fly's dropping, and MySQL and Oracle are more alike than either of their respective fanboys would like to admit.

Courtesy of Google trends:

I guess I won't be changing my career just yet.

UPDATE: I tried a few terms for "Microsoft SQL Server" before posting (SQL Server, MS SQL) but found none that came up with what I felt like was a realistic volume (they are all much, much lower than I expected). @MarkGStacey suggested trying "SQL 2008", "SQL 2005" and "SQL 2000", and those return much better results indeed (though still much lower than MySQL or Oracle). Anyway - I'd love to have some way of bunching up all those terms and have Google Trends show them as one trend, but I haven't figured out a way to do that. If you know how, please drop a line at let me know.

I'll adjust the blog if I find a more satisfactory solution.

MySQL: The maximum value of an integer

2010-05-04T20:15:00.001+02:00

Did you ever have the need to find the maximum value of an integer in MySQL? Yeah, me neither. Anyway, some people seem to need this, and this is what I came up with:


SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 AS max_int_unsigned
,      ~0 >> 40 AS max_mediumint_unsigned
,      ~0 >> 48 AS max_smallint_unsigned
,      ~0 >> 56 AS max_tinyint_unsigned
,      ~0 >> 1  AS max_bigint_signed
,      ~0 >> 33 AS max_int_signed
,      ~0 >> 41 AS max_mediumint_signed
,      ~0 >> 49 AS max_smallint_signed
,      ~0 >> 57 AS max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

In case you're wondering how it works, read up on what the tilde (~) does: it peforms a bitwise negation. In other words, if flips bits that are 1 to 0, and vice versa. So, ~0 means, set all the bits to 1, because in the integer one 0, all the bits are a binary 0. Now, in MySQL, at runtime, there is only one integer type, which is an 8-byte integer value or a bigint. Therefore, ~0 is by definition the largest possible integer value.

MySQL defines a family of integer types for storage: bigint (8 bytes), int (4 bytes), mediumint (3 bytes), smallint (2 bytes) and tinyint (1 byte). To find the maximum values of those types, we can use the right-bitshift operator >> to push the most-significant bits at the left side of ~0 down to the right, for the appropriate number of bytes to get the maximum values of the other integer flavors. So,


int type: big                                int      medium    small    tiny
bit #:    64       56       48       40       32       24       16        8      1  
~0       = 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 18446744073709551615
~0 >> 32 = 00000000 00000000 00000000 00000000 11111111 11111111 11111111 11111111 = 4294967295
~0 >> 40 = 00000000 00000000 00000000 00000000 00000000 11111111 11111111 11111111 = 16777215
~0 >> 48 = 00000000 00000000 00000000 00000000 00000000 00000000 11111111 11111111 = 65535
~0 >> 56 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 11111111 = 255

Now, for each of the integer flavors, MySQL lets you define them to be either signed or unsigned. This is implemented using a so-called sign bit. The sign bit is the most significant bit (so, bit #64 in a bigint, bit #32 in an int, and so on and so forth). If the sign bit equals 0, the integer is positive and if it equals 1, the integer is negative. So, to get the maximum values for the signed integer flavors, we can use the same recipe, we just need to push the bits even one more bit to the right, like so:


int type: big                                int      medium    small    tiny
bit #:    64       56       48       40       32       24       16        8      1  
~0 >>  1 = 01111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 9223372036854775807
~0 >> 33 = 00000000 00000000 00000000 00000000 01111111 11111111 11111111 11111111 = 2147483647
~0 >> 41 = 00000000 00000000 00000000 00000000 00000000 01111111 11111111 11111111 = 8388607
~0 >> 49 = 00000000 00000000 00000000 00000000 00000000 00000000 01111111 11111111 = 32767
~0 >> 57 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 01111111 = 127

Restoring XML-formatted MySQL dumps

2010-04-20T01:35:00.001+02:00

To whom it may concern -

The mysqldump program can be used to make logical database backups. Although the vast majority of people use it to create SQL dumps, it is possible to dump both schema structure and data in XML format. There are a few bugs (#52792, #52793) in this feature, but these are not the topic of this post.

XML output from mysqldump

Dumping in XML format is done with the --xml or -X option. In addition, you should use the --hex-blob option otherwise the BLOB data will be dumped as raw binary data, which usually results in characters that are not valid, either according to the XML spec or according to the UTF-8 encoding. (Arguably, this is also a bug. I haven't filed it though.)

For example, a line like:


mysqldump -uroot -pmysql -X --hex-blob --databases sakila

dumps the sakila database to the following XML format:


<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <database name="sakila">
    <table_structure name="actor">
      <field Field="actor_id" Type="smallint(5) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
      <field Field="first_name" Type="varchar(45)" Null="NO" Key="" Extra="" />
      <field Field="last_name" Type="varchar(45)" Null="NO" Key="MUL" Extra="" />
      <field Field="last_update" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" />
      <key Table="actor" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="actor_id" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
      <key Table="actor" Non_unique="1" Key_name="idx_actor_last_name" Seq_in_index="1" Column_name="last_name" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
      <options Name="actor" Engine="InnoDB" Version="10" Row_format="Compact" Rows="200" Avg_row_length="81" Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="233832448" Auto_increment="201" Create_time="2009-10-10 10:04:56" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>
    <table_data name="actor">
      <row>
        <field name="actor_id">1</field>
        <field name="first_name">PENELOPE</field>
        <field name="last_name">GUINESS</field>
        <field name="last_update">2006-02-15 03:34:33</field>
      </row>

...many more rows and table structures...

  </database>
</mysqldump>

I don't want to spend too much time discussing why it would be useful to make backups in this way. There are definitely a few drawbacks - for example, for sakila, the plain SQL dump, even with --hex-blob is 3.26 MB (3.429.358 bytes), whereas the XML output is 13.7 MB (14,415,665 bytes). Even after zip compression, the XML formatted dump is still one third larger than the plain SQL dump: 936 kB versus 695 kB.

Restoring XML output from mysqldump

A more serious problem is that MySQL doesn't seem to offer any tool to restore XML formatted dumps. The LOAD XML feature, kindly contributed by Erik Wetterberg could be used to some extent for this purpose. However, this feature is not yet available (it will be available in the upcoming version MySQL 5.5), and from what I can tell, it can only load data - not restore tables or databases. I also believe that this feature does not (yet) provide any way to properly restore hex-dumped BLOB data, but I really should test it to know for sure.

Anyway.

In between sessions of the past MySQL users conference I cobbled up an XSLT stylesheet that can convert mysqldump's XML output back to SQL script output. It is available under the LGPL license, and it is hosted on google code as the mysqldump-x-restore project. To get started, you need to download the mysqldump-xml-to-sql.xslt XSLT stylesheet. You also need a command line XSLT processor, like xsltproc. This utility is part of the Gnome libxslt project, and is included in packages for most linux distributions. There is a windows port available for which you can download the binaries.

Assuming that xsltproc is in your path, and the XML dump and the mysqldump-xml-to-sql.xslt are in the current working directory, you can use this command to convert the XML dump to SQL:


xsltproc mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

On Unix-based systems you should be able to directly pipline the SQL into mysql using


mysql -uroot -pmysql < `xsltproc mysqldump-xml-to-sql.xslt sakila.xml`

The stylesheet comes with a number of options, which can be set through xsltproc's --stringparam option. For example, setting the schema parameter to N will result in an SQL script that only contains DML statements:


xsltproc --stringparam schema N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

Setting the data option to N will result in an SQL script that only contains DDL statements:


xsltproc --stringparam data N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

. There are additional options to control how often a COMMIT should be issued, whether to add DROP statements, whether to generate single row INSERT statements, and to set the max_allowed_packet size.

What's next?

Nothing much really. I don't really recommend people to use mysqldump's XML output. I wrote mysqldump-x-restore for those people that inherited a bunch of XML formatted dumps, and don't know what to do with them. I haven't thouroughly tested it - please file a bug if you find one. If you actually think it's useful and you want more features, please let me know, and I'll look into it. I don't have much use for this myself, so if you have great ideas to move this forward, I'll let you have commit access.

That is all.

MySQL Conference 2010 Presentation: Optimizing Stored Routines

2010-04-14T17:20:00.002+02:00

Yesterday I delivered my presentation for the MySQL User Conference and Expo 2010: Optimizing MySQL Stored Routines. If you are interested in the slides, you can find them on both the MySQL conference site as well as on slideshare.net. Here's the abstract of my presentation so you can decide if this is interesting for you:

MySQL stored routines (functions, procedures, triggers and events) can be useful. But many casually written stored routines are unnecessarily slow. The main reason is that MySQL does not apply even simple code optimizations to stored routine code. Many developers are not aware of this, and as a result, write stored routine code that can quite easily be tuned, increasing performance by 50%-100% by only applying very straightforward code optimizations.

It was very pleased to see so many people attend: I had the impression that MySQL stored routines are quite impopular, due to performance issues, and a syntax that is often regarded as "clunky", so I didn't expect more than about 20 people to show up. Much to my pleasure, the ballroom was filled for about two-thirds, and I estimate there were 70-something people in the room.

A quick survey of the audience indicated that all of them were in fact using stored routines in production, so I assume they didn't show up out of morbid curiosity :) Interestingly, only few people reported performance issues. It would be interesting to do more research to find out what people are in fact doing with MySQL stored routines. Among yesterday's attendees, there were people using MySQL stored routines for managing user privileges, processing astronomical data, and checking complex dynamic business rules. To be sure - these were all different users - not just one isolated fanatic going wild with stored routines.

Co-incidentally, Domas Mituzas from facebook also mentioned stored routines in his presentation on high concurrency MySQL as a way to reduce the lock gap when performing multiple changes in a single transaction. I'm just saying - perhaps MySQL stored routines aren't that bad at all, they just need more love and dedication from the MySQL developers so they can mature and gain wider applicability.

Recently, I already wrote about a recent improvement in MySQL 5.5, the long anticipated SIGNAL /RESIGNAL syntax. I hope more improvements will follow soon now the dust is settling after Oracle's acquisition of Sun. After hearing Edward Screven unfold Oracle's strategy for MySQL in yesterday's keynote, I can tell you without reservation that I am quite optimistic :)

Anyway - that is all for now. Two days of conference ahead :)

MySQL: Partition-wise backups with mysqldump

2010-03-30T11:00:00.005+02:00

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.

How it works

The script works by querying the information_schema.PARTITIONS system view to generate an appropriate expression for mysqldump's --where option. The generated command also redirects the output to a file with this name pattern:

<schema>.<table>.<partition-name>.sql

For example, for this table (taken from the MySQL reference manual):

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

the script generates the following commands:

mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) < 1960" test members > test.members.p0.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1960 and  YEAR(joined) < 1970" test members > test.members.p1.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1970 and  YEAR(joined) < 1980" test members > test.members.p2.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1980 and  YEAR(joined) < 1990" test members > test.members.p3.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1990 and  YEAR(joined) < 18446744073709551615" test members > test.members.p4.sql

Tip: in order to obtain directly executable output from the mysql command line tool, run the script with the --skip-column-names (or -N) option.

Features

Currently, the script supports the following partitioning methods:

HASH

LIST

RANGE

Limitations

The LINEAR HASH method is currently not supported, but I may implement that in the future.

Currently I do not have plans to implement the KEY and LINEAR KEY partitioning methods, but I may reconsider if and when I have more information about the storage-engine specific partitioning functions used by these methods.

Finally, I should point out that querying the information_schema.PARTITIONS table is dog-slow. This may not be too big of an issue, however it is pretty annoying. If anybody has some tips to increase performance, please let me know.

Acknowledgements

Thanks to André for posing the problem. I had a fun hour of procrastination to implement this, and it made me read part of the MySQL reference manual on partitioning.

I also would like to thank Giuseppe Maxia (the Datacharmer) for providing valuable feedback. If you're interested in either partitioning or the mysql command line, you should visit his tutorials at the MySQL conference, april 12-15, 2010.

Greatest N per group: top 3 with GROUP_CONCAT()

2010-03-19T02:45:00.003+01:00

In my opinion, one of the best things that happened to Planet MySQL lately, is Explain Extended, a blog by Alex Bolenok (also known as Quassnoi on Stackoverflow).

I never had the pleasure of meeting Alex in person, but his articles are always interesting and of high quality, and the SQL wizardry he pulls off is downright inspiring. I really feel humbled by the creativity of some of his solutions and his apparent experience with multiple RDBMS products.

Alex' most recent post is about aggregation, and finding a top 3 based on the aggregate:

In MySQL I have a table called meanings with three columns: word, meaning, person. word has 16 possible values, meaning has 26. A person assigns one or more meanings to each word. In the sample above, person 1 assigned two meanings to word 2. There will be thousands of persons. I need to find the top three meanings for each of the 16 words, with their frequencies. Is it possible to solve this with a single MySQL query?

Alex presents a solution that uses GROUP_CONCAT basically as a poor man's windowing function, a technique I have described on several occasions in the past for ranking, median and percentile solutions in MySQL.

Now, Alex' solution is very clever and there are some elements that I think are very creative. That said, I think his solution can be improved still. Normally I wouldn't write a blog about it, and simply leave a comment on his blog, but his blog supports comments only for general articles, which is why I present it here:


SELECT  word
,       CONCAT(
            SUBSTRING_INDEX(
                GROUP_CONCAT(meaning ORDER BY num DESC), ',', 1
            )
        ,   ' ('
        ,   SUBSTRING_INDEX(
                GROUP_CONCAT(num ORDER BY num DESC), ',', 1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank1
,       CONCAT(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(meaning ORDER BY num DESC), ',', 2
                ), ',', -1
            )
        ,   ' ('
        ,   SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(num ORDER BY num DESC), ',', 2
                ), ',', -1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank2
,       CONCAT(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(meaning ORDER BY num DESC), ',', 3
            ), ',', -1)
        ,   ' ('
        ,   SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(num ORDER BY num DESC), ',', 3
                ), ',', -1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank3
FROM    (
        SELECT      word, meaning, COUNT(*) num
        FROM        t_meaning m
        GROUP BY    word,meaning
        ) a
GROUP BY word

This gives me output like this:


+------+--------------+--------------+--------------+
| word | rank1        | rank2        | rank3        |
+------+--------------+--------------+--------------+
|    1 | 16 (3.9728%) | 17 (3.9648%) | 12 (3.9632%) |
|    2 | 9 (3.9792%)  | 10 (3.9632%) | 20 (3.9328%) |
|    3 | 20 (3.9744%) | 13 (3.968%)  | 1 (3.9648%)  |
|    4 | 26 (3.952%)  | 7 (3.9456%)  | 17 (3.9424%) |
|    5 | 9 (4.008%)   | 21 (3.9824%) | 20 (3.936%)  |
|    6 | 19 (3.9504%) | 10 (3.9488%) | 13 (3.9408%) |
|    7 | 23 (4.0464%) | 12 (3.976%)  | 19 (3.9648%) |
|    8 | 23 (4.0112%) | 3 (4.0096%)  | 8 (3.9328%)  |
|    9 | 10 (4.016%)  | 19 (3.984%)  | 15 (3.9616%) |
|   10 | 10 (4.0304%) | 14 (3.9344%) | 11 (3.9312%) |
|   11 | 16 (3.9584%) | 6 (3.9296%)  | 19 (3.9232%) |
|   12 | 7 (3.9968%)  | 1 (3.9392%)  | 26 (3.9264%) |
|   13 | 8 (4.048%)   | 25 (3.9712%) | 23 (3.9616%) |
|   14 | 16 (3.9936%) | 26 (3.9632%) | 4 (3.9536%)  |
|   15 | 22 (4.0608%) | 12 (4.0048%) | 1 (3.9632%)  |
|   16 | 14 (4.0032%) | 18 (3.9712%) | 4 (3.9488%)  |
+------+--------------+--------------+--------------+
16 rows in set (0.63 sec)

On my laptop, my solution is about 30% faster than the one presented by Alex. Personally I think mine is easier to understand too, but that is a matter of taste.

Anyway, I'm just posting this to share my solution - I do not intend to downplay the one presented by Alex. Instead, I invite everyone interested in SQL, MySQL and PostgreSQL to keep an eye on Alex' blog as well as his excellent answers on Stackoverflow. He's an SQL jedi master in my book :)

Of course, if you have a better solution to crack this problem in MySQL, please leave a comment. I'd love to hear what other people are doing to cope with these kinds of queries.

Writing another book: Pentaho Kettle Solutions

2010-03-14T21:35:00.007+01:00

Last year, at about this time of the year, I was well involved in the process of writing the book Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL" for Wiley. To date, "Pentaho Solutions" is still the only all-round book on the open source Pentaho Business Intelligence suite.

It was an extremely interesting project to participate in, full of new experiences. Although the act of writing was time consuming and at times very trying for me as well as my family, it was completely worth it. I have none but happy memories of the collaboration with my full co-author Jos van Dongen, our technical editors Jens Bleuel, Jeroen Kuiper, Tom Barber and Tomas Morgner, several of the Pentaho Developers, and last but not least, the team at Wiley, in particular Robert Elliot and Sara Shlaer.

When the book was finally published, late August 2009, I was very proud - as a matter of fact, I still am :) Both Jos and I have been rewarded with a lot of positive feedback, and so far, book sales are meeting the expectations of the publisher. We've had mostly positive reviews on places like Amazon, and elsewhere on the web. I'd like to use this opportunity to thank everybody that took the time to review the book: Thank you all - it is very rewarding to get this kind of feedback, and I appreciate it enourmously that you all took the time to spread the word. Beer is on me next time we meet :)

Announcing "Pentaho Kettle Solutions"

In the autumn of 2009, just a month after "Pentaho Solutions" was published, Wiley contacted Jos and me to find out if we were interested in writing a more specialized book on ETL and data integration using Pentaho. I felt honoured, and took the fact that Wiley, an experienced and well-reknowned publisher in the field of data warehousing and business intelligence, voiced interested in another Pentaho book by Jos an me as a token of confidence and encouragement that I value greatly. (For Pentaho Solutions, we heard that Wiley was interested, but we contacted them.) At the same time, I admit I had my share of doubts, having the memories of what it took to write Pentaho Solutions still fresh in my mind.

As it happens, Jos and I both attended the 2009 Pentaho Community Meeting, and there we seized the opportunity to talk to Matt Casters, chief Pentaho Data Integration and founding developer of Kettle (a.k.a. Pentaho Data Integration). Both Jos and I didn't expect Matt to be able to free up any time in his ever busy schedule to help us to write the new book. Needless to say, he made us both very happy when he rather liked the idea, and expressed immediate interest in becoming a full co-author!

Together, the three of us made a detailed outline and wrote a formal proposal for Wiley. Our proposal was accepted in December 2009, and we have been writing since, focusing on the forthcoming Kettle version, Kettle 4.0 . The tentative title of the book is Pentaho Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration. It is planned to be published in September 2010, and it will have approximately 750 pages.

Our working copy of the outline is quite detailed but may still change in the future, which is why I won't publish it here until we finished our first draft of the book. I am 99% confident that the top level of the outline is stable, and I have no reservation in releasing that already:

Part I: Getting Started
- ETL Primer
- Kettle Concepts
- Installation and Configuration
- Sample ETL Solution

Part II: ETL Subsystems
- Overview of the 34 Subsystems of ETL
- Data Extraction
- Cleansing and Conforming
- Handling Dimension Tables
- Fact Tables
- Loading OLAP Cubes

Part III: Management and Deployment
- Testing and Debugging
- Scheduling and Monitoring
- Versioning and Migration
- Lineage and Auditing
- Securing your Environment
- Documenting

Part IV: Performance and Scalability
- Performance Tuning
- Parallization and Partitioning
- Dynamic Clustering in the Cloud
- Realtime and Streaming data

Part V: Integrating and Extending Kettle
- Pentaho BI Integration
- Third-party Kettle Integration
- Extending Kettle

Part VI: Advanced Topics
- Webservices and Web APIs
- Complex File Handling
- Data Vault Management
- Working with ERP Systems

Feel free to ask me any questions about this new book. If you're interested, stay tuned - I will probably be posting 2 or 3 updates as we go.

MySQL - the best stored routine is the one you don't write

2010-02-17T23:00:00.002+01:00

At Fosdem 2010, already two weeks ago, I had the pleasure of hearing Geert van der Kelen explain the work he has been doing on connecting MySQL and Python. I don't know anything about Python, but anybody that has the courage, perseverance and coding skills to create an implementation of the the MySQL wire protocol from scratch is a class-A programmer in my book. So, I encourage everyone that needs MySQL connectivity for Python programs to check out Geert's brainchild, MySQL Connector/Python.

In relation to MySQL Connector/Python, I just read a post from Geert about how he uses the MySQL information_schema to generate some Python code. In this particular case, he needs the data from the COLLATIONS table to maintain a data structure that describes all collations supported by MySQL.

For some reasons that I cannot fathom, Geert needed to generate a structure for each possible collation, not just the ones for which the COLLATIONS table contains a row. To do this, he wrote a stored procedure that uses a cursor to loop through the COLLATIONS table. In the loop, he detects it whenever there's a gap in the sequence of values from the ID column, and then starts a new loop to "fill the gaps". For each iteration of the outer cursor loop, a piece of text is emitted that conforms to the syntax of a Python tuple describing the collation, and each iteration of the inner loop generates the text None, a Python built-in constant.

The final result of the procedure is a snippet of Python code shown below (abbreviated):


..
("cp1251","cp1251_bulgarian_ci"), # 14
("latin1","latin1_danish_ci"), # 15
("hebrew","hebrew_general_ci"), # 16
None,
("tis620","tis620_thai_ci"), # 18
("euckr","euckr_korean_ci"), # 19
..

In the final code, these lines are themselves used to form yet another tuple:


desc = (
    None,
    ("big5","big5_chinese_ci"), # 1
    ("latin2","latin2_czech_cs"), # 2
    ("dec8","dec8_swedish_ci"), # 3
    ("cp850","cp850_general_ci"), # 4
..

This is excellent use of the information schema! However, I am not too thrilled about using a stored routine for this. Enter my fosdem talk about refactoring stored routines.

In this case, performance is not really an issue, so I won't play that card. But many people that do need well-performing stored procedures might start out like Geert and write a cursor loop, and perhaps do some looping inside that loop. One of the big take-aways in my presentation is to become aware of the ways that you can avoid a stored procedure. Geerts procedure is an excellent candidate to illustrate the point. As a bonus, I'm adding the code that is necessary to generate the entire snippet, not just the collection of tuples inside the outer pair of parenthesis.

So, here goes:


set group_concat_max_len := @@max_allowed_packet;

select      concat('desc = (',
                group_concat('\n   '
                ,   if( collations.id is null, 'None',   
                        concat('(', '"', character_set_name, '"',
                            ',', '"', collation_name, '"', ')')
                    )
                ,   if(ids.id=255, '', ','), ' #', ids.id
                order by ids.id
                separator ''    
                ), '\n)'
            )
from       (select (t0.id<<0) + (t1.id<<1) + (t2.id<<2)
            +      (t3.id<<3) + (t4.id<<4) + (t5.id<<5)
            +      (t6.id<<6) + (t7.id<<7)            id
            from   (select 0 id union all select 1) t0
            ,      (select 0 id union all select 1) t1
            ,      (select 0 id union all select 1) t2
            ,      (select 0 id union all select 1) t3
            ,      (select 0 id union all select 1) t4
            ,      (select 0 id union all select 1) t5
            ,      (select 0 id union all select 1) t6
            ,      (select 0 id union all select 1) t7) ids
left join   information_schema.collations on ids.id = collations.id;

This query works first by generating 256 rows having id's ranging from 0 to 255. (I think I recall Alexander Barkov mentioning that this is currently the maximum number of collations that MySQL supports - perhaps I am wronge there). This is done by cross-joining a simple derived table that generates two rows:


(select 0 id union all select 1)

So, one row that yields 0, and one that yields 1. By cross-joining 8 of these derived tables, we get 2 to the 8th power rows, which equals 256. In the SELECT-list, I use the left bitshift operator << to shift the original 0 and 1 0, 1, 2 and so on up to 7 positions. By then adding those values together, we fill up exactly one byte, and gain all possible values from 0 through 255:


(select (t0.id<<0) + (t1.id<<1) + (t2.id<<2)
 +      (t3.id<<3) + (t4.id<<4) + (t5.id<<5)
 +      (t6.id<<6) + (t7.id<<7)            id
 from   (select 0 id union all select 1) t0
 ,      ...                              t1
 ,      ...
 ,      (select 0 id union all select 1) t7) ids

Once we have this, the rest is straightforward - all we have to do now is use a LEFT JOIN to find any collations from the information_schema.COLLATIONS table in case the value of its ID column matches the value we computed with the bit-shifting jiggery-pokery. For the matching rows, we use CONCAT to generate a Python tuple describing the collation, and for the non-matching rows, we generate None:


if( collations.id is null, 'None',   
    concat('(', '"', character_set_name, '"',
           ',', '"', collation_name, '"', ')')
)

The final touch is a GROUP_CONCAT that we use to bunch these up into a comma separated list that is used as entries for the outer tuple. As always, you should set the value of the group_concat_max_len server variable to a sufficiently high value to hold the contents of the generated string, and if you want to be on the safe side and not run the risk of getting a truncated result, you should use max_allowed_packet.

I have the honour of speaking at the MySQL user conference, april 12-15 later this year. There, I will be doing a related talk called Optimizing MySQL Stored Routines. In this talk, I will explain how stored routines impact performance, and provide some tips on how you can avoid them, but also on how to improve your stored procedure code in case you really do need them.

Easter Eggs for MySQL and Kettle

2010-01-27T18:10:00.000+01:00

To whom it may concern,

A MySQL stored function to calculate easter day

I uploaded a MySQL forge snippet for the f_easter() function. You can use this function in MySQL statements to calculate easter sunday for any given year:


mysql>  select f_easter(year(now()));
+-----------------------+
| f_easter(year(now())) |
+-----------------------+
| 2010-04-04            |
+-----------------------+
1 row in set (0.00 sec)

Anonymous Gregorian algorithm

To implement it, I simply transcribed the code of the "Anonymous Gregorian algorithm" from wikipedia's Computus article.

You might ask yourself: "how does it work?". Frankly, I don't know. Much like a tax form, I treat the calculation as a black box. But, it's wikipedia, so it must be right, right?

A Javascript snippet to calculate easter day

I also transcribed the algorithm to javascript, so I could use it in Kettle (a.k.a. Pentaho Data Integration). Of course, nothing should stop you from using it for another environment, such as a webpage.

I don't have a proper place to host that code, so I'm listing it here:


//Script to calculate Easter day
//according to the "Anonymous Gregorian algorithm" 
function easterDay(year) {
     var a = year % 19,
     b = Math.floor(year / 100),
     c = year % 100,
     d = Math.floor(b / 4),
     e = b % 4,
     f = Math.floor((b + 8) / 25),
     g = Math.floor((b - f + 1) / 3),
     h = (19 * a + b - d - g + 15) % 30,
     i = Math.floor(c / 4),
     k = c % 4,
     L = (32 + 2 * e + 2 * i - h - k) % 7,
     m = Math.floor((a + 11 * h + 22 * L) / 451),
     n = h + L - 7 * m + 114;
     return new Date(year,
                  Math.floor(n / 31) - 1,
                  (n % 31) + 1);
}

easter = easterDay(year);

To use this in your kettle transformations, create a stream with an field of the Integer type called year. The year field should take on the value of some year. In the step, create one output field of the Date type to take on the value of the easter script variable. (For usage outside kettle, just use the easterDay() function as you see fit)

Nice, but so what?

The thought may have crossed your mind: "So what, who cares - why should I ever want to know when it's easter day?"

Apparently, if you think like that, you don't like eggs very much. That's ok - I don't blame you. But I happen to like eggs, and people in the egg business like people that like eggs like me so they can sell them more eggs. In fact, they like selling eggs so much, that it makes a big difference to them whether their business intelligence reports say: "On March 22, 2008, we sold 10 times more eggs than on February 22 and May 22 of the same year" as compared to "In 2008, on the day before Easter, we only sold half the amount of eggs as compared to the day before Easter in 2009".

In order to report these facts, special events and holidays like easter are stored in a date dimension. (I wrote about creating a localized date dimension, a date dimension that speaks your language some time ago)

So there you go: you could use these solutions in order to build a date dimension that understands easter. The nice thing about easter is that it can be used to derive a whole bunch of other Christian holidays, like good friday, ascension, and pentecost, and in many western countries, these will be special days with regard to the normal course of business. I leave all these as an exercise to the reader, but trust me - calculating easter is the key to a solving a lot of these problems.

Validating MySQL data entry with triggers: A quick look at the SIGNAL syntax

2009-12-15T17:45:00.001+01:00

The latest MySQL 5.5 milestone release offers support for an ANSI/ISO standard feature called the SIGNAL syntax. You can use this syntax inside stored routines (including triggers) to raise an error condition which can be used to invoke specific error handling, or otherwise abort the stored routine. In addition, you can use the SIGNAL syntax to convey information about what went wrong, which may be used by the caller to handle the error.

I have written about MySQL data entry validation procedures in the past. At the time, MySQL did not support any proper means to raise an error condition inside a stored routine, and one had to work around that by deliberatly causing a runtime error, for example by referring to a non-existent table, setting a non-nullable column to null, or ]Yting a specially crafted UDF. In this artcle, I'm taking a closer look at how to implement more robust data entry validation in MySQL using the SIGNAL syntax.

Triggers

For those of you that are unfamiliar with the subject, MySQL offers support for triggers as of version 5.0. Triggers are stored routines that are executed automatically right before or after data change events like a row being inserted, updated or deleted. Because triggers are executed as part of the SQL statement (and its containing transaction) causing the row change event, and because the trigger code has direct access to the changed row, you could in theory use them to correct or reject invalid data.

Example data validation problem

Let's take a quick look at the following example. Suppose you have a table called person to store data about persons:


CREATE TABLE person (
    id              INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
,   first_name      VARCHAR(64) NOT NULL    
,   last_name       VARCHAR(64) NOT NULL
,   initials        VARCHAR(8)
)

Let's consider a simple validation method for the initials column:

We require the first letter of the value for the initials column to match the first letter of the value of the first_name column.

In addition, we require that the values for the initials column consists of uppercase letters separated by periods.

In order to implement this, we design the following algorithm:

If the value for first_name is NULL, we do nothing. The NOT NULL table constraint will prevent the data from being entered anyway, so further attempts at validation or correction are pointless.

If the value for initials is NULL, we correct it by automatically filling in the first character of the value for first_name.

If the values for first_name as well as initials are both not NULL, we require that the first character of the value for first_name equals the first character of the value for initials.

Finally, we use a regular expression to check if the value for initials matches the desired pattern of uppercase letters separated by periods.

A data validation stored procedure

Let's start by creating a stored procedure to perform this algorithm. Here's the code for the p_validate_initials procedure which validates and possibly corrects the initials value based on the value for first_name:


DELIMITER go

CREATE PROCEDURE p_validate_initials(
    IN      p_first_name    VARCHAR(64)
,   INOUT   p_initials      VARCHAR(64) 
)
DETERMINISTIC                                                                   -- same arguments yield same result, always
NO SQL                                                                          -- does not execute SQL statements, only procedural logic
_main: BEGIN

    DECLARE WARN_CORRECTED_INITIALS CONDITION FOR SQLSTATE '01000';
    DECLARE ERR_INITIALS_DONT_MATCH_FIRSTNAME CONDITION FOR SQLSTATE '45000';
    DECLARE ERR_INITIALS_ILLFORMATTED CONDITION FOR SQLSTATE '45000';

    IF      p_first_name IS NULL THEN
        LEAVE _main;                                                            -- nothing to validate
    ELSEIF   p_initials IS NULL THEN                                            -- initials are NULL, correct:
        SET p_initials := CONCAT(LEFT(p_first_name, 1), '.');                   -- take the first letter of first_name
        SIGNAL WARN_CORRECTED_INITIALS                                          -- warn about the corrective measure
        SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';
    ELSEIF   BINARY LEFT(p_first_name, 1) != LEFT(p_initials, 1) THEN           -- initials don't match first_name
        SIGNAL ERR_INITIALS_DONT_MATCH_FIRSTNAME                                -- raise an error
        SET MESSAGE_TEXT = 'The first letter of the value for initials does not match the first letter of the value for first_name';
    END IF;
    IF NOT p_initials REGEXP '^([A-Z][.])+$' THEN                               -- if initials don't match the correct pattern
        SIGNAL ERR_INITIALS_ILLFORMATTED                                        -- raise an error
        SET MESSAGE_TEXT = 'The value for initials must consist of upper case letters separated by periods.';
    END IF;
END;
go

DELIMITER ;

Let's take a look at how this procedure works.

How to issue warnings

First, let's pass NULL for the initials to see if they are properly corrected:


mysql> set @initials := null;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_validate_initials('Roland', @initials);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @initials;
+-----------+
| @initials |
+-----------+
| R.        |
+-----------+
1 row in set (0.00 sec)

Note that executing the procedure placed the correct value into the @initials user-defined variable. Also note that a warning was issued.

In this case, the procedure ran through the following branch of the first IF statement:


    ...
    ELSEIF   p_initials IS NULL THEN
        SET p_initials := CONCAT(LEFT(p_first_name, 1), '.');
        SIGNAL WARN_CORRECTED_INITIALS
        SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';
    ELSEIF ...

The warning is caused by the SIGNAL statement (which appears in bold text in the snippet above). The general syntax for the SIGNAL statement is:


SIGNAL condition_value
    [SET signal_information [, signal_information] ...]
    
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name

signal_information:
    condition_information_item = simple_value_specification

So, in this case, we used the condition name WARN_CORRECTED_INITIALS as condition_value. This condition is declared in the top of the procedure:


    DECLARE WARN_CORRECTED_INITIALS CONDITION FOR SQLSTATE '01000';

Basically, condition declarations like these serve simply to tie a human readable name to otherwise obscure SQLSTATE values. As per the SQL standard, SQLSTATE values are strings of 5 digits. The prefix 01 indicates a warning.

(Condition declarations like these are not only useful to clarify the meaning of your SIGNAL statements, you can also use them to declare error HANDLERs)

An alternative syntax for SIGNAL allows you to directly refer to the SQLSTATE without explicitly declaring a CONDITION. So, if you feel that declaring explicit conditions is too much trouble, you can also omit that and write:


        SIGNAL SQLSTATE '01000' 
            ...

(However, I like using explicit condition names better because it does a better job of explaining the intention of the code.)

Conveying `SIGNAL` context information

The SIGNAL statement also features a SET-clause which is used to convey signal information. In our example the set clause was:


        SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';

As you can see, the SET-clause in the example contains an assignment. In the context of the SIGNAL statemntt, such an assignment is referred to as signal_information. The left hand side of the assignments must be one of the predefined condition_information_items. The SET-clause can have multiple of these signal_information items which can be used to capture and communicate program state to the client program.

In the case of the example we can demonstrate how this works using the MySQL command-line client. By issuing a SHOW WARNINGS statement, we can see the message text was conveyed with with the signal_information item:


mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message                                                                         |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1644 | The value for initials must consist of upper case letters separated by periods. |
+-------+------+---------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Unfortunately, MySQL does not currently support a way for any calling stored routines to capture the signal_information items. They are currently only available in the APIs you use to communicate with MySQL, so you can capture them in your application code.

(A bug has been filed to ask for the ability to refer to signal information items in stored routines. This should become available whenever MySQL implements a DIAGNOSTICS feature)

Predefined condition information items

I just mentioned that the left-hand side of the signal_information item assignment must be one of the predefined condition_information_items. These are dictated by the standard, and although MySQL allows all of the standard condition_information_items, only two of them are currently relevant: MESSAGE_TEXT and MYSQL_ERRNO. We already illustrated using MESSAGE_TEXT. The MYSQL_ERRNO is a non-standard condition information item that can be used to convey custom error codes.

This leaves currently three variables to convey information about the context of the SIGNAL statement:

SQLSTATE: available in the C API as mysql_sqlstate()

MYSQL_ERRNO: available in the C API as mysql_errno()

MESSAGE_TEXT: available in the C API as mysql_error()

(You should be able to obtain the information also in PHP through the corresponding mysqli_, pdo_ and mysql_ functions.)

How to issue errors

We just discussed how to cause your stored routine to issue warnings. Issuing errors is exactly the same process, it just relies on a different class of SQLSTATE values (as determined by the code prefix). Let's see the errors in action:


mysql> set @initials := 'r';
Query OK, 0 rows affected (0.00 sec)

mysql> call p_validate_initials('Roland', @initials);
ERROR 1644 (45000): The first letter of the value for initials does not match the first letter of the value for first_name

In this case, the stored routine ran through the last branch of the first IF statement:


    ELSEIF   BINARY LEFT(p_first_name, 1) != LEFT(p_initials, 1) THEN
        SIGNAL ERR_INITIALS_DONT_MATCH_FIRSTNAME
        SET MESSAGE_TEXT = 'The first letter of the value for initials does not match the first letter of the value for first_name';
    END IF;

As you can see, the syntax for the actual SIGNAL statement is exactly similar to what we saw in the example illustrating warnings. The most important difference is that in this case, the condition that is being signalled is declared with a SQLSTATE value of 45000:


    DECLARE ERR_INITIALS_DONT_MATCH_FIRSTNAME CONDITION FOR SQLSTATE '45000';

The SQLSTATE code 45000 is a special and indicates a general user-defined exception.

Using the procedure in a trigger

All we need to do now is create our triggers on the person table that call the procedure to perform the actual validation. We need to apply the validation when data is inserted into the table, but also when data is updated. If it turns out the data is invalid, we need to reject the change. For this reason, we want to create triggers that fire before the data change is applied to the table.

So, to enforce validation, we need two triggers: one that fires BEFORE INSERT events, and one that fires BEFORE UPDATE events. Because the validation process itself is the same regardless of the type of change event, both triggers can call the p_validate_initials procedure to perform the actual validation. This allows us to write (and maintain!) the validation logic only once, and reuse it whenever we need it.


DELIMITER go

CREATE TRIGGER bir_person 
BEFORE INSERT ON person
FOR EACH ROW
BEGIN
    CALL p_validate_initials(
        NEW.first_name
    ,   NEW.initials 
    );
END;
go

CREATE TRIGGER bur_person 
BEFORE UPDATE ON person
FOR EACH ROW
BEGIN
    CALL p_validate_initials(
        NEW.first_name
    ,   NEW.initials 
    );
END;
go

DELIMITER ;

A quick check indicates that data validation is now enforced as intended:


mysql> INSERT INTO person (id, first_name, last_name, initials)
    -> VALUES (2, 'Roland', 'Bouman', 'r');
ERROR 1644 (45000): The first letter of the value for initials does not match the first letter of the value for first_name

mysql> INSERT INTO person (id, first_name, last_name, initials)
    -> VALUES (2, 'Roland', 'Bouman', 'R');
ERROR 1644 (45000): The value for initials must consist of upper case letters separated by periods.

mysql> INSERT INTO person (id, first_name, last_name, initials)
    -> VALUES (2, 'Roland', 'Bouman', NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1642 | Corrected NULL value for initials to match value for first_name. |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Final words

For more information on MySQL 5.5, check out Giuseppe Maxia's article on Getting Started with MySQL 5.5. Detailed information on the SIGNAL syntax is available in the reference manual here: http://dev.mysql.com/doc/refman/5.5/en/signal-resignal.html.

Pentaho Data Integration: Javascript Step Performance

2009-11-16T02:30:00.008+01:00

I just read a post from Vincent Teyssier on cleaning strings using the javascript capabilities of Pentaho Data Integration (also known as Kettle) and Talend.

In this post, I am looking at a few details of Vincent's approach to using Javascript in his transformation. I will present a few modifications that considerably improve performance of the Javascript execution. Some of these improvements are generic: because they apply to the use of the javascript language, they are likely to improve performance in both Talend as well as Kettle. Other improvements have to do with the way the incoming and outgoing record streams are bound to the javascript step in Kettle.

Original Problem

The problem described by Vincent is simple enough: for each input string, return the string in lower case, except for the initial character, which should be in upper case. For example: vIncEnt should become Vincent.

Vincent illustrates his solution using Pentaho Data Integration's "Modified Javascript Value" step. He uses it to execute the following piece of code:


//First letter in uppercase, others in lowercase
var c = Input.getString().substr(0,1);
if (parseInt(Input.getString().length)==1)
{
    var cc = upper(c);
}
else
{
    var cc = upper(c) + lower(Input.getString().slice(1));
}

(The original post explains that one should be able to execute the code with minimal modification in Talend. While I don't have much experience with that tool, I think the proper step to use in that case is the tRhino step. Both tools use an embedded Rhino engine as javascript runtime, but I can imagine that there are slight differences with regard to binding the input and output fields and the support for built-in functions. Please feel free and leave a comment if you can provide more detailed information with regard to this matter.)

In the script, Input is the string field in the incoming stream that is to be modified, and cc is added to the output stream, containing the modified value. For some reason, the original example uses the javascript step in compatibility mode, necessitating expressions such as Input.getString() to obtain the value from the field.

I used the following transformation to test this script:

The transformation uses a Generate Rows step to generate 1 million rows having a single String type field with the default value vIncEnt. The rows are processed by the Modified Javascript Value step, using the original code and compatibility mode like described in Vincent's original post. Finally, I used a Dummy step. I am not entirely sure the dummy ste has any effect on the performance of the javascript step, but I figured it would be a good idea to ensure the output of the script is actually copied to an outgoing stream.

On my laptop, using Pentaho Data Integration 3.2, this transformation takes 21.6 seconds to complete, and the Javascript step processes the rows at a rate of 46210.7 rows/second.

Caching calls to getString()

Like I mentioned, the original transformation uses the Javascript step in compatibility mode. Compatibility mode affects the way the fields of the stream are bound to the javascript step. With compatibility mode enabled, the step behaves like it did in Kettle 2.5 (and earlier versions): fields from the input stream are considered to be objects, and a special getter method is required to obtain their value. This is why we need an expression like Input.getString() to obtain the actual value.

The first improvement I'd like to present is based on simply caching the return value from the getter method. So instead of writing Input.getString() all the time, we simply write a line like this:


var input = Input.getString();

Afterwards, we simply refer only to input instead of Input.toString(). With this modifcation, the script becomes:


//First letter in uppercase, others in lowercase
var input = Input.getString();
var c = input.substr(0,1);
if (parseInt(input.length)==1)
{
var cc = upper(c);
}
else
{
var cc = upper(c) + lower(input.slice(1));
}

(Note that input and Input are two different things here: Input refers to the field object from the incoming record stream, and input refers to a global javascript variable which we use to cache the return value from the getString() method of the Input field object.)

If you compare this code to the original, you will notice that although this modified example adds an assignment to cache the value, it saves at least one call to the getString() method in the generic case. However, because the input value used in the example is longer than one character, it also saves another call done in the else branch of the if statement. So all in all, we can avoid two calls to getString() in this example.

This may not seem like that big a deal, but still, this improvement allows the javascript step to process rows at a rate of 51200.6 rows per second, which is an improvement of about 11%. Scripts that would have more than two calls to the getter method would benefit even more from this simple improvement.

Disabling Compatibility mode

The compatibility mode is just that: a way to stay compatible with the old Kettle 2.5 behaviour. While this is useful to ensure your old transformations don't break, you really should consider not using it for new transformations.

When disabling compatibility mode, you will need to change the script. In compatibility mode, the names of the fields from the input stream behave like variables that point to the field objects. With compatibility mode disabled, fieldnames still behave like variables, but now they point to the actual value of the field, and not the field object. So we need to change the script like this:


var c = Input.substr(0,1);
var cc;
if (parseInt(Input.length)==1){
    cc = upper(c);
} 
else {
    cc = upper(c) + lower(Input.slice(1));
}

As you can see, we don't need to use the getSting() method anywhere anymore, and this also makes our first improvement obsolete. Personally, I feel this is an improvement code-wise. In addition, the transformation now performs considerably better: now it takes 14,8 seconds, and the javascript step is processing 67159,1 rows per second, which 30% better than the previous solution, and 45% better than the original.

Eliminating unncessary code

The fastest code is the code you don't execute. The original script contains a call to the javascript built-in parseInt() function which is applied to the length property of Input:


if (parseInt(Input.length)==1){
...snip...
}

The intended usage of parseInt() is to parse strings into integer values. Because the type of the length property of a string is already an integer, the call to parseInt() is simply redundant, and can be removed without any issue. This cuts down execution time to 12.8 seconds, and the Javascript step is now processing at a rate of 75204,9 rows per second: an improvement of 12% as compared to the previous improvement, and 63% as compared to the original.

Optimizing the flow

Although it may look like we optimized the original javascript as much as we could, there is still room for improvement. We can rewrite the if statements using the ternary operator, like so:


var cc = Input.length==0 
        ? ""
        : Input.length==1 
        ? Input.toUpperCase()
        : Input.substr(0,1).toUpperCase() 
        + Input.substr(1).toLowerCase() 
;

(Note that I am now using the toLowerCase() and toUpperCase() methods of the javascrpt String object in favor of the kettle built-in lower() and upper() functions.)
Not everybody may appreciate this code-wise, as it may appear a lot less explcit than the original if logic. In its defense, the approach of this solution has a more functional feel (as opposed to the procedural logic of the prior examples), which may feel more natural for the problem at hand. Regardless of any code-maintenance or aesthetic arguments, this code is actually slightly faster: It takes 12.3 seconds total, and the javascript step is processing 80301,9
rows per second, which is a 7% improvement as compared to the previous solution, and a 74% improvement as compared to the original.

Not using Javascript at all

The Javascript step can be very useful. But always keep in mind that it really is a general purpose scripting device. With the javascript step, you can do loops, open files, write to databases and whatnot. Do we really need all this power to solve the original problem? Especially if you are proficient in Javascript, it may be somewhat of a challenge to find better ways to solve the problem at hand, but really - it is often worth it.

First, let us realize that the original problem does not presume a particularly difficult transformation. We just need "something" that takes one input value, and returns one output value. We don't need any side effects, like writing to a file. We also don't need to change the grain: every input row is matched by exactly one output row, which is similar in layout to the output row, save for the addition of a field to hold the transformed value.

When discussing the previous solution, I already hinted that it was more "functional" as compared to the more "procedural" examples before that. We will now look at a few solutions that are also functional in nature:

The Formula step

So, basically, we need to write a function. The Formula step lets you combine several built-in functions in about the same manner as you can in spreadsheet programs like open office and Microsoft Excel. Using the formula step we can enter the following formula:


UPPER(LEFT([Input];1)) & LOWER(MID([Input];2;LEN([Input])))

If, like me, your eyes are bleading now, you might appreciate this formatted overview of this calculation:


    UPPER(
        LEFT(
            [Input]
        ;   1
        )
    ) 
&   LOWER(
        MID(
            [Input]
        ;   2
        ;   LEN([Input])
        )
    )

This solution takes 8.5 seconds to complete, and the formula step is processing rows at a rate of 117868.9 per second, which is 47% better than the previous solution, and 155% better than the original (!!!)

The Calculator step

While not as flexible as the Formula step, the Calculator step offers a reasonable range of often used functions, and has the advantage of often being faster than the formula step. In this case, we're lucky, and we can set up two calculations: one "LowerCase of a string A" to convert the input value entirely to lower case, and then a "First letter of each word in capital of a string A". By feeding the output of the former into the latter, we get the desired result:

(To be fair, because the calculation will actually add a capital to every word in the input, the result will actually be different as compared to any of the other transformations. However, in many cases, you might be able to guarantee that there is actually one word in the input, or otherwise, it may be considered desirable to capitalize all words.)

This transformation complets in just 6.5 seconds, and the calculator processes rows at a rate of 155327,7 per second. This is 32% better than the previous solution and 236% better than the original.

User-defined Java Expression

The final kicker is the user-defined java expression step. The user-defined java expression step allows you to write a java expression, which is compiled while the transformation is initialized. The expression I used is quite like the last javascript solution I discussed, except that we have to use methods of the Java String object (and not the JavaScript string object)


Input.length()==0?"":Input.length()==1?Input.toUpperCase():Input.substring(0,1).toUpperCase() + Input.substring(1).toLowerCase()

The result is truly amazing: The transformation completes in just 3.1 seconds, with the user-defined Java expression step processing at a rate of 324886,2 rows per second. This is 109% faster than the previous solution, and 603% faster than the original.

Conclusion

Javascript is a powerful device in data intergration transfomations, but it is quite slow. Consider replacing the javascript step with either the formula step, the calculator step or the user-defined Java expression step. Depending on your requirements, there may be other steps that deliver the fuunctionality you need.

If you really do need javascript, and you are using Pentaho Data Integration, consider disabling the compatibility mode. On the other hand, if you do need the compatibility mode, be sure to avoid repeated calls the getter methods of the field objects to obtain the value. Instead, call the getter methods just once, and use global script variables to cache the return value.

Summary

Here's a summary of the measurements:


    Transformation   |  Rows per second
    -----------------+-----------------
    Original         |   46201,7
    Cache getString()|   51200,6
    No Compatmode    |   67159,1
    no parseInt()    |   75204,9
    Optimize flow    |   80301,9
    Formula          |  117868,9
    Calculator       |  155327,7
    Java Expression  |  324886,2

...and here, a bar chart showing the results:

Final thoughts

One of the things I haven't looked at in detail is adding more parallelism. By simply modifying the number of copies of the transforming step, we can use more cores/processors, but this is an excellent subject for a separate blog post.

UPDATE
Daniel Einspanjer from Mozilla Coorp. created a 30 min. video demonstrating this hands-on! He adds a few very interesting approaches to squeeze out even more performance.

Calpont opens up: InfiniDB Open Source Analytical Database (based on MySQL)

2009-10-27T14:54:00.004+01:00

Open source business intelligence and data warehousing are on the rise!

If you kept up with the MySQL Performance Blog, you might have noticed a number of posts comparing the open source analytical databases Infobright, LucidDB, and MonetDB. LucidDB got some more news last week when Nick Goodman announced that the Dynamo Business Intelligence Corporation will be offering services around LucidDB, branding it as DynamoDB.

Now, to top if off, Calpont has just released InfiniDB, a GPLv2 open source version of its analytical database offering, which is based on the MySQL server.

So, let's take a quick look at InfiniDB. I haven't yet played around with it, but the features sure look interesting:

Column-oriented architecture (like all other analytical database products mentioned)

Transparent compression

Vertical and horizontal partitioning: on top of being column-oriented, data is also partitioned, potentially allowing for less IO to access data.

MVCC and support for high concurrency. It would be interesting to see how much benefit this gives when loading data, because this is usually one of the bottle necks for column-oriented databases

Support for ACID/Transactions

High performance bulkloader

No specialized hardware - InfiniDB is a pure software solution that can run on commidity hardware

MySQL compatible

The website sums up a few more features and benefits, but I think this covers the most important ones.

Calpont also offers a closed source enterprise edition, which differs from the open source by offering support for multi-node scale-out support. By that, they do not mean regular MySQL replication scale-out. Instead, the enterprise edition features a true distributed database architecture which allows you to divide incoming requests across a layer of so-called "user modules" (MySQL front ends) and "performance modules" (the actual workhorses that partition, retrieve and cache data). In this scenario, the user modules break the queries they recieve from client applications into pieces, and send them to one or more performance modules in a parallel fashion. The performance modules then retrieve the actual data from either their cache, or from the disk, and sends those back to the user modules which re-assemble the partial and intermediate results to the final resultset which is sent back to the client. (see picture)

Given the MySQL compatibility and otherwise similar features, I think it is fair to compare the open source InfiniDB offering to the Infobright community edition. Interesting differences are that InfiniDB supports all usual DML statements (INSERT, DELETE, UPDATE), and that InfiniDB offers the same bulkloader in both the community edition as well as the enterprise edition: Infobright community edition does not support DML, and offers a bulk loader that is less performant than the one included in its enterprise edition. I have not heard of an InfoBright multi-node option, so when comparing the enterprise edition featuresets, that seems like an advantage too in Calpont's offering.

Please understand that I am not endorsing one of these products over the other: I'm just doing a checkbox feature list comparison here. What it mostly boils down to, is that users that need an affordable analytical database now have even more choice than before. In addition, it adds a bit more competition for the vendors, and I expect them all to improve as a result of that. These are interesting times for the BI and data warehousing market :)

MySQL: Another Ranking trick

2009-09-15T15:15:00.003+02:00

I just read SQL: Ranking without self join, in which Shlomi Noach shares a nice MySQL-specific trick based on user-defined variables to compute rankings.

Shlomi's trick reminds me somewhat of the trick I came across little over a year ago to caclulate percentiles. At that time, several people pointed out to me too that using user-defined variables in this way can be unreliable.

The problem with user-defined variables

So what is the problem exaclty? Well, whenever a query assigns to a variable, and that same variable is read in another part of the query, you're on thin ice. That's because the result of the read is likely to differ depending on whether the assignment took place before or after the read. Not surprising when you think about it - the whole point of variable assignment is to change its value, which by definition causes a different result when subsequently reading the variable (unless you assigned the already assigned value of course, duh...).

Now watch that previous statement clearly - the word subsequently is all-important.

See, that's the problem. The semantics of a SQL SELECT statement is to obtain a (tabular) resultset - not specifying an algorithm to construct that resultset. It is the job of the RDBMS to figure out an algorithm and thus, you can't be sure in what order individual expressions (including variable evaluation and assignment) are executed.

The MySQL manual states it like this:

The order of evaluation for user variables is undefined and may change based on the elements contained within a given query. In SELECT @a, @a := @a+1 ..., you might think that MySQL will evaluate @a first and then do an assignment second, but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may change the order of evaluation.

The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed.

So what good are these variables anyway?

On the one hand, this looks really lame: can't MySQL just figure out the correct order of doing the calulations? Well, that is one way of looking at it. But there is an equally valid reason not to do that. If the calculations would influence execution order, it would drastically lessen the number of ways that are available to optimize the statement.

This begs the question: Why is it possible at all to assign values to the user-defined variables? The answer is quite simple: you can use it to pass values between statetments. My hunch is the variables were created in the olden days to overcome some limitations resulting from the lack of support for subqueries. Having variables at least enables you to execute a query and assign the result temporarily for use in a subsequent statement. For example, to find the student with the highest score, you can do:


mysql> select @score:=max(score) from score;
+--------------------+
| @score:=max(score) |
+--------------------+
|                 97 |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from score where score = @score;
+----------+--------------+-------+
| score_id | student_name | score |
+----------+--------------+-------+
|        2 | Gromit       |    97 |
+----------+--------------+-------+
1 row in set (0.03 sec)

There is nothing wrong with this approach - problems start arising only when reading and writing the same variable in one and the same statement.

Another way - serializing the set with `GROUP_CONCAT`

Anyway, the percentile post I just linked to contains another solution for that problem that relies on GROUP_CONCAT. It turns out we can use the same trick here.

(Some people may like to point out that using GROUP_CONCAT is not without issues either, because it may truncate the list in case the pre-assigned string buffer is not large enough. I wrote about dealing with that limitation in several places and I remain recommending to set the group_concat_max_len server variable to the value set for the max_packet_size server variable like so:

SET @@group_concat_max_len := @@max_allowed_packet;

)

The best way to understand how it works is to think of the problem in a few steps. First, we make an ordered list of all the values we want to rank. We can do this with GROUP_CONCAT like this:


mysql> SELECT  GROUP_CONCAT(
    ->             DISTINCT score
    ->             ORDER BY score  DESC
    ->         )                   AS scores
    -> FROM    score
    -> ;
+-------------+
| scores      |
+-------------+
| 97,95,92,85 |
+-------------+
1 row in set (0.00 sec)

Now that we have this list, we can use the FIND_IN_SET function to look up the position of any particlar value contained in the list. Because the list is ordered in descending order (due to the ORDER BY ... DESC), and contains only unique values (due to the DISTINCT), this position is in fact the rank number. For example, if we want to know the rank of all scores with the value 92, we can do:


mysql> SELECT FIND_IN_SET(92, '97,95,92,85')
+--------------------------------+
| FIND_IN_SET(92, '97,95,92,85') |
+--------------------------------+
|                              3 |
+--------------------------------+
1 row in set (0.00 sec)

So, the answer is 3 because 92 is the third entry in the list.

(If you're wondering how it's possible that we can pass the integer 92 as first argument for FIND_IN_SET: the function expects string arguments, and automatically converts whichever non-string typed value we pass to a string. In the case of the integer 92, it is silently converted to the string '92')

Of course, we are't really interested in looking up ranks for individual numbers one at a time; rather, we'd like to combine this with a query on the scores table that does it for us. Likewise, we don't really want to manually supply the list of values as a string constant, we want to substitute that with the query we wrote to generate that list.
So, we get:


mysql> SELECT score_id, student_name, score
    -> ,      FIND_IN_SET(
    ->            score
    ->        ,  (SELECT  GROUP_CONCAT(
    ->                        DISTINCT score
    ->                        ORDER BY score  DESC
    ->                    )
    ->            FROM    score)
    ->        ) as rank
    -> FROM   score;
+----------+--------------+-------+------+
| score_id | student_name | score | rank |
+----------+--------------+-------+------+
|        1 | Wallace      |    95 |    2 |
|        2 | Gromit       |    97 |    1 |
|        3 | Shaun        |    85 |    4 |
|        4 | McGraw       |    92 |    3 |
|        5 | Preston      |    92 |    3 |
+----------+--------------+-------+------+
5 rows in set (0.00 sec)

Alternatively, if you think that subqueries are for the devil, you can rewrite this to a CROSS JOIN like so:


SELECT      score_id, student_name, score
,           FIND_IN_SET(
                score
            ,   scores
            ) AS rank
FROM        score
CROSS JOIN (SELECT  GROUP_CONCAT(
                       DISTINCT score
                       ORDER BY score  DESC
                    ) AS scores
            FROM    score) scores

Now that we have a solutions, lets see how it compares to Shlomi's original method. To do this, I am using the payment table from the sakila sample database.

First, Shlomi's method:


mysql> SELECT   payment_id
    -> ,        amount
    -> ,        @prev := @curr
    -> ,        @curr := amount
    -> ,        @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
    -> FROM     sakila.payment
    -> ,       (SELECT @curr := null, @prev := null, @rank := 0) sel1
    -> ORDER BY amount DESC;
+------------+--------+----------------+-----------------+------+
| payment_id | amount | @prev := @curr | @curr := amount | rank |
+------------+--------+----------------+-----------------+------+
|        342 |  11.99 |           NULL |           11.99 |    1 |
.        ... .  ..... .          ..... .           ..... .    . .
|      15456 |   0.00 |           0.00 |            0.00 |   19 |
+------------+--------+----------------+-----------------+------+
16049 rows in set (0.09 sec)

Wow! It sure is fast :) Now, the GROUP_CONCAT solution, using a subquery:


mysql> SELECT payment_id, amount
    -> ,      FIND_IN_SET(
    ->            amount
    ->        ,  (SELECT  GROUP_CONCAT(
    ->                        DISTINCT amount
    ->                        ORDER BY amount  DESC
    ->                    )
    ->            FROM    sakila.payment)
    ->        ) as rank
    -> FROM   sakila.payment
+------------+--------+------+
| payment_id | amount | rank |
+------------+--------+------+
|          1 |   2.99 |   15 |
.          . .   .... .   .. .
|      16049 |   2.99 |   15 |
+------------+--------+------+
16049 rows in set (0.14 sec)

(In case you're wondering why the results are different, this is because the result set for Shlomi's solution is necessarily ordered by ascending rank (or descending amount - same difference. To obtain the identical result, you need to add an ORDER BY clause to my query. But since the point was to calculate the ranks, I didn't bother. Of course, adding an ORDER BY could slow things down even more.)

Quite a bit slower, bummer. But at leastt we can't run into nasties with the user variables anymore. For this data set, I get about the same performance with the CROSS JOIN, but I should warn that I did not do a real benchmark.

Conclusion

Don't fall into the trap of reading and writing the same user-defined variable in the same statement. Although it seems like a great device and can give you very good performance, you cannot really control the order of reads and writes. Even if you can, you must check it again whenever you have reason to believe the query will be solved differently by the server. This is of course the case whenever you upgrade the server. But also seemingly harmless changes like adding an index to a table may change the order of execution.

Almost all cases where people want to read and write to the same user variables within the same query, they are dealing with a kind of serialization problem. They are trying to maintain state in a variable in order to use it across rows. In many cases, the right way to do that is to use a self-join. But this may not always be feasible, as pointed out in Shlomi's original post. For example, rewriting the payment rank query using a self join is not going to make you happy.

Often, there is a way out. You can use GROUP_CONCAT to serialize a set of rows. Granted, you need at least one pass for that, and another one to do something useful with the result, but this still a lot better than dealing with semi-cartesian self join issues.

EU Should Protect MySQL-based Special Purpose Database Vendors

2009-09-12T18:30:00.000+02:00

In my recent post on the EU antitrust regulators' probe into the Oracle Sun merger I did not mention an important class of stakeholders: the MySQL-based special purpose database startups. By these I mean:

Kickfire

Infobright

Calpont

ScaleDB

I think it's safe to say the first three are comparable in the sense that they are all analytical databases: they are designed for data warehousing and business intelligence applications. ScaleDB might be a good fit for those applications, but I think it's architecture is sufficiently different from the first three to not call it an analytical database.

For Kickfire and Infobright, the selling point is that they are offering a relatively cheap solution to build large data warehouses and responsive business intelligence applications. (I can't really find enough information on Calpoint pricing, although they do mention low total cost of ownership.) An extra selling point is that they are MySQL compatible, which may make some difference for some customers. But that compatibility is in my opinion not as important as the availability of a serious data warehousing solution at a really sharp price.

Now, in my previous post, I mentioned that the MySQL and Oracle RDBMS products are very different, and I do not perceive them as competing. Instead of trying to kill the plain MySQL database server product, Oracle should take advantage of a huge opportunity to help shape the web by being a good steward, leading ongoing MySQL development, and in addition, enable their current Oracle Enterprise customers to build cheap LAMP-based websites (with the possibility of adding value by offering Oracle to MySQL data integration).

For these analytical database solutions, things may be different though.

I think these MySQL based analytical databases really are competitive to Oracle's Exadata analytical appliance. Oracle could form a serious threat to these MySQL-based analytical database vendors. After the merger, Oracle would certainly be in a position to hamper these vendors by resticting the non-GPL licensed usage of MySQL.
In a recent ad, Oracle vouched to increase investments in developing Sun's hardware and operating system technology. And this would eventually put them in an even better position to create appliances like Exadata, allowing them to ditch an external hardware partner like HP (which is their Exadata hardware partner).

So, all in all, in my opinion the EU should definitely take a serious look at the dynamics of the analytical database market and decide how much impact the Oracle / Sun merger could have on this particular class of MySQL OEM customers. The rise of these relatvely cheap MySQL-based analytical databases is a very interesting development for the business intelligence and data warehousing space in general, and means a big win for customers that need affordable datawarhousing / business intelligence. It would be a shame if it would be curtailed by Oracle. After the merger, Oracle sure would have the means and the motive, so if someone needs protection, I think it would be these MySQL-based vendors of analytical databases.

As always, these are just my musing and opinions - speculation is free. Feel free to correct me, add applause or point out my ignorance :)

MySQL a factor in EU's decision

2009-09-03T14:10:00.002+02:00

I just read Björn Schotte's post on the activities of the European Union antitrust regulators concerning the intended takeover of Sun Microsystems by Oracle.

Björn mentions a news article that cites EU Competition Commissioner Neelie Kroes saying that the commission has the obligation to protect the customers from reduced choice, higher costs or both. But to me, this bit is not the most interesting. Later on the article reads:

The Commission said it was concerned that the open source nature of Sun's MySQL database might not eliminate fully the potential for anti-competitive effects.

With both Oracle's databases and MySQL competing directly in many sectors of the database market, MySQL is widely expected to represent a greater competitive constraint as it becomes increasingly functional, the EU executive said.

In other words, the commission is working to protect the MySQL users :)

Personally, I (and many other MySQL community members) don't fear for the future of MySQL as a product. But I do think it is justified to worry about customers that are now paying Sun for some licensed usage of MySQL, most notably OEM customers and a bunch of Enterprise users.

Ever since the news was disclosed concerning the intention of Oracle to acquire Sun, it has been speculated that Oracle my try to "upsell" the Oracle RDBMS to current MySQL enterprise users. However I don't think that that would be the brightest of moves. I did a bit of speculation myself back in April in response to questions put forward in the SSWUG newsletter.

I maintain the opinions I stated there:

MySQL / Oracle are completely different beasts and customers realize this, and most likely Oracle does so too. People running MySQL for web related applications won't move to Oracle. Period. Oracle may be able to grab some customers that use MySQL for data warehousing, but I think that even in these cases a choice for Infobright or Kickfire makes more sense.

Not all problems are database problems - if Oracle does a decent job of supporting and developing MySQL, they may become a respectable enough partner for current (larger) MySQL users to help them solve other problems such as systems integration.

Instead of looking at the benefits for MySQL customers of using Oracle, look at the benefits for Oracle customers using MySQL. Suddenly Oracle can offer support for the most popular webstack in the world - Now all these enterprise customers running expensive Oracle installations can finally build cheap websites based on MySQL and even get support from Oracle on connecting their backend Enterprise Oracle instances to the MySQL web front ends.

It's not all about the products. Open Source adds a whole new dynamic to the development process. I'm not just talking about outside developers that offer new features and code patches, as this does not happen too often. There's more to it than code though

In all successful open source projects I know there is a very lively culture of users engaging with developers and voicing their opinion on what is good and what is not so good. There is a very real chance for the user to influence the direction of the development process (although this does not mean everybody gets what they want in equal amounts). Conversely this provides a great opportunity for the development organization to learn about what the users really need and wish for.

In short, Oracle may want to use Sun/MySQL to learn how to do better business with more empowered users.

Of course, its all just my opinion - speculation is free. So you should feel free too to post your ideas on the matter. Go ahead and leave a comment ;)

Roland Bouman's blog goes i18n (Powered by Google Translate)

2009-09-03T04:25:00.000+02:00

Now that Pentaho Solutions is in print, and the first few copies are finding its way towards the readers, I felt like doing something completely unrelated. So, I hacked up a little page translation widget, based on the Google Language API. You can see the result in the top of the left sidebar of my blog right now:

Using it is very simple: just pick the language of choice, and the page (text and some attributes like alt and title) will be translated. Pick the first entry in the list to see the original language again.

This all happens inline by dynamic DOM manipulation, without having to reload the page. I tested it on Chrome 2, Firefox 3.5, Opera 10, Safari 4 and Internet Explorer 6 and 8. So far, it seems to work for all these browsers.

Personally, I feel that the user experience you get with this widget is superior to what you would get with the google translation gadget. In addition, it is pretty easy to to configure the Translator class .

The code to add this to your page is in my opinion reasonably simple:


    <!-- add a placeholder for the user interface -->
    <div id="toolbar"><div>

    <!-- Include script that defines the Translator class -->
    <script type="text/javascript" src="Translator-min.js"></script>
    <!-- Instantiate a translator, have it create its gui and render to placeholder -->
    <script type="text/javascript">
        var translator = new Translator();
        var gui = translator.createGUI(null, "Language");
        document.getElementById("toolbar").appendChild(gui);
    </script>

This really is all the code you need - there are no dependencies on external Javascript frameworks. If you don't need or like the gui, you can of course skip the gui placeholder code as well as the second script and interract with the Translator object programmatically.

The minified javascript file is about 7k, which is not too bad in my opinion. I haven't worried too much about optimizations, and I think it should be possible to cut down on codesize.

Another thing I haven't focused on just now is integration with frameworks - on the contrary I made sure you can use it standalone. But in order to do that, I had to write a few methods to facilitate DOM manipulation and JSON parsing, and its almost certain you will find functions like that are already in your framework.

Anyway, readers, I'd like to hear from you...is this auseful feature on this blog? Would you like to use it on your own blog? If there's enough people that want it, I will make it available on google code or something like that.

"Pentaho Solutions": copies hit the mail

2009-08-22T00:40:00.003+02:00

Hi!

Just a few hours ago, I arrived home after a very quiet and peaceful two-week holiday with my family. It was great! I didn't bring a computer on purpose. I brought a mobile phone, but didn't answer that on purpose too :) Result: absolute relaxation, with lots of time to hike, cycle, and read, and occasional visits to musea and historic sites. Bliss :)

Anyway, now that the bags are unpacked, and the kids are asleep, it's time to face the dragon better known as my inbox. What I found brought a big smile to my face:

Yes - it's true!! Copies of my and Jos' book Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL have hit the mail, and at least one copy has reached the Pentaho office.

Pentaho-ers, thanks for your kind email, and thanks for a great product!

I haven't received one myself (yet), and it will probably take some time still to ship the books to Europe. But it's certainly good to see a physical proof of our work.

Anyway - if you are expecting a copy of the book because you pre-ordered one, or if I or Jos promised you a copy, fear not, it should be heading your way. I hope you like it - Enjoy :)

OSCON 2009 Presentation "Taming your Data: Practical Data Integration Solutions with Kettle" now online

2009-07-24T01:16:00.003+02:00

I just delivered my OSCON 2009 presentation "Taming your data: Practical Data Integration Solutions with Kettle".

I think it went pretty well, and I got good responses from the audience. I did have much more material than time, and I probably should have proposed to do a tutorial instead. Maybe next year :)

Anyway, you can find the presentation and the examples on the OSCON 2009 website. The slides are available in pdf format. There's also a zip file that contains the presentation as well as the kettle sample transformations and jobs.

My OSCON 2009 Session: Taming your Data...

2009-07-18T01:53:00.005+02:00

Yes!

Finally, it's there: In a few hours, I will be flying off to San Franscisco to attend OSCON 2009 in San Jose, California. This is the first time I'm attending, and I'm tremendously excited to be there! The sessions look very promising, and I'm looking forward to seeing some excellent speakers. I expect to learn a lot.

I'm also very proud and feel honoured to have the chance to deliver a session myself. It's called Taming Your Data: Practical Data Integration Solutions with Kettle.

Unsurprisingly, I will be talkig a lot about Kettle, a.k.a. Pentaho Data Integration. Recently, I talked about Kettle too at the MySQL user's conference, and more recently, at a MySQL university session. Those sessions were focused mainly on how Kettle can help you load a data warehouse.

But...there's much more to this tool than just data warehousing, and in this session, I will be exploring rougher grounds, like making sense of raw imdb text files, loading and generating XML, clustering and more. This session will also be much more hands on demonstration than the Sakila sessions. If you're interested and you are also attending, don't hesitate to drop by! I'm looking forward to meeting you :)

And...because the topic of the session kind of relates to my upcoming book, "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL" (ISBN: 978-0-470-48432-6, 600+ pages, list price $50.00), my publisher Wiley decided to throw in a litte extra. Yup, that's right - I've got discount coupons for the book, so if you are interested in picking up a copy, or if you just want to give one away to a friend or colleague, come find me at my session (or somewhere else on OSCON) and I'll make sure you'll get one. Thanks Wiley!!

Anyway - I'm hoping to meet you there: see you soon!!!

open-msp-viewer: Free XSLT utilities to render MS Project files as HTML web pages

2009-07-14T01:07:00.004+02:00

For my day job, I've been working on a few things that allow you to render Microsoft Project 2003 projects on a web page.

The code I wrote for my work is proprietary, and probably not directly useful for most people. But I figured that at least some of the work might be useful for others, so I wrote an open source version from scratch and I published that as the open-msp-viewer project on google code. If you like, check out the code and give it a spin.

It works by first saving the project in the MS Project XML format using standard MS Project functionality (Menu \ Save As..., then pick .XML) and then applying an XSLT transformation to generate HTML.

Currently, the project includes an xslt stylesheet that renders MS Project XML files as a Gantt chart. To give you a quick idea, Take a look at these screenshots:

and

The web gantt chart is rendered in a HTML 4.01 variant, CSS 2.1 and uses javascript to allow the user to collapse and/or expand individual tasks in the work breakdown structure. Currently, the HTML does not validate due to a few custom attributes I introduced to support dynamic collapsing/expanding the chart with javascript. In addition, the xslt transform process introduces the msp namespace into the result document, which results in a validation error

You can either associate the xslt stylesheet directly with the MS Project XML file, or you can use an external tool like xsltproc.

In the trunk/xml subdirectory, you can find a couple of sample projects in xml format that already have the stylesheet association. I have tested these in IE8, Chrome 2, Safari 4 and Firefox 3.5, and it works well in all these browsers. In the trunk/html directory, you'll find HTML output as created by xsltproc.

In the future, more xslt stylesheets may be added to support alternative views. Things that I think I will add soon are a resources list and a calendar view.

Enjoy, and let me know if you find a bug or would like to contribute.

WTF? Apple favicon on the Windows update site?

2009-07-12T12:55:00.002+02:00

I just noticed the weirdest thing while visiting the windows update site:

As you can see, the shortcut icon is actually an Apple icon...WTF?!! I looked in the code, and this is the code that sets the favicon:

<link rel='shortcut icon' href='shared/images/banners/favicon.ico' type='image/x-icon'/>

When I navigate directly to http://update.microsoft.com/windowsupdate/v6/shared/images/banners/favicon.ico I get the normal icon, that is to say, this: .When I look with IE6, I actually see that icon as favicon...so the question is, what's up with the Apple icon in IE8?! Do I have some virus or malware that has modified IE8? I googled for the problem, but can't find any references...am I the only one? Anybody else experiencing this?

Starring Sakila: MySQL university recording, slides and materials available onMySQLForge

2009-07-03T09:13:00.002+02:00

Hi!

Yesterday I had the honour of presenting my mini-bi/datawarehousing tutorial "Starring Sakila" for MySQL University. I did a modified version of the presentation I did together with Matt Casters at the MySQL user's conference 2009. The structure of the presentation is still largely the same, although I condensed various bits, and I added practical examples of setting up the ETL process and creating a Pentaho Analysis View (OLAP pivot table) on top of a Mondrian Cube.

The slides, session recording, and materials such as SQL script, pentaho data integration jobs and transformations, and Sakila Rentals Cube for Mondrian are all available here on MySQL Forge.

Copyright Notice

Presentation slides, and materials such as SQL script, pentaho data integration jobs and transformations, and Sakila Rentals Cube for Mondrian are all Copyright Roland Bouman. Feel free to download and learn from it. But please do not distribute the materials yourself - instead, point people to the wiki page to get their own copy of the materials. Personal use of the files is allowed. Use these materials for creating training materials of using these materials as training materials is explicitly not allowed without written prior consent. (Just mail me at roland dot bouman at gmail dot com if you would like to use the materials for such purposes, and we can work something out.)

MySQL Stored Functions: Impact of DECLARE HANDLER on Performance

2009-06-18T01:00:00.000+02:00

Hi again!

Just a moment ago, I read this post by Peter Gulutzan. In this post, Peter explains a little trick that allows you to capture the SQL state in a variable whenever an error occurs in your MySQL stored routine code.

MySQL `CONDITION`s and `HANDLER`s

For the uninitiated: in MySQL stored routines, you can declare HANDLERs which are pieces of code that are executed only in case a particular CONDITION occurs. This device serves the same purpose as a try ... catch block which is supported in many popular programming languages like Java, JavaScript, C++, PHP5 and C#.

Now, one of the long-standing problems with MySQL HANDLERs - the inability to explicitly raise a CONDITION - has recently been solved by implementing the SIGNAL syntax. The most important issue that remains is the inability to discover exactly which error occurred. You need this especially when you are writing a generic 'catch all' HANDLER (for example, on that is triggered in response to SQLEXCEPTION) and you want to write information regarding the error to a log.

Peter's Trick to Capture SQLSTATE

To cut a long story short, Peter's solution is based on writing a HANDLER for all known CONDITIONs in advance. Here's a fragment of his code to explain:

BEGIN
  ...
  DECLARE EXIT HANDLER FOR SQLSTATE '01000' BEGIN SET @e='01000'; RESIGNAL; END;
  ...
  DECLARE EXIT HANDLER FOR SQLSTATE 'XAE09' BEGIN SET @e='XAE09'; RESIGNAL; END;

  ...remainder of code...

END;

As Peter points out, it's tedious, but it works. (That is to say, it works better than not having anything)

Performance?

Now, one particular paragraph in Peter's post caught my eye:

I added 38 DECLARE EXIT HANDLER statements at the start of my procedure, just after the variable declarations. These lines are always the same for any procedure. They’re not executed unless an error happens so I don’t worry about speed.

I respect Peter a great deal - if he's got something to say you do well to listen and take his word for it. However, this time I was curious to find out if I could measure the effect of the HANDLER code at all.

Method

The code I used is a simplification of Peter's code. I tested it on MySQL 5.1 because I was just interested in the impact of a DECLARE HANDLER statement. In my case, the BEGIN...END block of the handler does not contain the RESIGNAL statement, and my function does not drop a table but simply returns 1. This is important, as none of the HANDLERs is ever triggered by my code.

Last week I wrote how seemingly small changes in MySQL stored routine code can have a surprisingly large impact on performance. In that particular case, I already had a hunch about which things could be improved. In this case, I just didn't know so I created a series of functions with 2, 4, 8, 16, 32 and 38 DECLARE HANDLER statements, and again I used the BENCHMARK() function to measure the time it takes to execute it 100,000 times. I did warm-up runs, and repeated the measurement 5 times for each function variant.

Results

The graph below summarizes my observations:

The squares in the graph are the measurements - each one represents a version of the function. Along the horizontal axis you see the number of DECLARE HANDLER statements in that particular version of the function. The number of seconds it took to execute the function 100,000 times on my laptop using BENCHMARK() is on the vertical axis.
As you can see, there seems to be a linear relationship between the number of DECLARE HANDLER statements and the time it takes to execute the function. This is in itself a surprise. I mean, I would expect a little overhead per DECLARE statement when the function is compiled initially. After that, it is cached at the session level, and beyond that point I would not expect statements that do not execute to have any impact.

So how badly do the HANDLER declarations slow our function down? Well, I measured an average of 0.38 seconds for 2, and an average of 0.55 seconds for 38 DECLARE HANDLER statements respectively. The difference, 0.18 seconds is a little less than 50% of the function variant with 2 DECLARE HANDLER statements, and a little more than 30% of the function having 38 DECLARE HANDLER statements.

Conclusion

To be fair, the function I tested doesn't actually do anything, and if your function or stored procedure does some real processing, the overhead may be neglectable. However, you can clearly see that even just declaring a handler has a measurable negative impact on performance. The essence of Peter's trick is to actually always write a DECLARE HANDLER for each possible condition and to do this for each stored routine. You will certainly suffer a peformance hit for small functions, esp. if they get called a lot.

MySQL: Refactoring a Stored Function

2009-06-10T20:45:00.004+02:00

Hi All!

I was just reading PlanetMySQL and noticed Mike Hillyer's recent post on a user-friendly age function for MySQL. Basically, this function accepts two DATETIME values and returns an indication of the time between the two dates in the form of a human-readable string. For example:

mysql> select  TimeDiffUnits('2001-05-01', '2002-01-01')
+-------------------------------------------+
| TimeDiffUnits('2001-05-01', '2002-01-01') |
+-------------------------------------------+
| 8 Months                                  |
+-------------------------------------------+
1 row in set (0.00 sec)

Just for fun, I decided to try and refactor it, and I'm writing this to share the results. Now for a little disclaimer. The purpose of this post is not to bash Mike or belittle his code: I consider him a friend, and I respect him and his skills a lot. The point is to show how seemingly small differences in MySQL stored function code can have quite an impact on performance. The good news is that there is a rationale behind all this - I did not refactor based on trial and error. I hope I can shed some light on that in the remainder of this post when discussing the individual improvements.

Summary

I changed three pieces of code in Mike's original function. Each of these changes help to increase performance just a little bit, and because none of the changes alter the overall structure of the original code, I'm inclined to consider them as improvements.I'm including a graph here:

The Y-axis of the graph represents the average (N=5) number of seconds it took to run

SELECT BENCHMARK(100000, TimeDiffUnits('2001-01-01', '2002-01-01'))

The original function is at the left, and each bar at the right represents one step of refactoring. All in all, at the far right, you can see that the final result is a function with exactly the same behavior which runs about 70% faster than the original :-).

About the Measurements

I should clarify a thing or two to explain the measurments I did.

First of all, BENCHMARK() is usually frowned upon as a general benchmarking tool. However, in this case the measured function is completely computational in nature, and I think that it isn't too bad to get a rough idea of relative performance.

Second, the actual code that is being measured, TimeDiffUnits('2001-01-01', '2002-01-01') is in fact a worst-case^* scenario for this particular function, and it's quite likely that testing it with real data does not automatically result in 70% performance increase.

(^*: It is quite literally a worst-case scenario because the input values are such that the CASE-statement used within the function has to mull through all branches before finding a matching branch)

Finally I should point out that the vast majority of database performance problems have to do with IO or resource contention, and are not computational in nature. So chances are that you can use none of the information in the remainder of this post to improve your performance problem. (You can always stop reading now of course ;-)

That said, if you use MySQL stored functions, it can't hurt to be aware of their performance issues, and it is not too hard to make a habit of writing the fastest possible code. In many cases, you will find that the fastest solution is also the cleanest, shortest, and most maintainable one.

Now, without further ado - how I refactored that function...

Original Function

First, meet the original function as it appeared on Mike's blog.

CREATE FUNCTION TimeDiffUnits (old DATETIME, new DATETIME)RETURNS CHAR(50) 
DETERMINISTIC NO SQL
BEGIN
  DECLARE diff INTEGER;
  SET diff = UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);
  CASE
    WHEN (diff < 3600)     THEN RETURN CONCAT(FLOOR(diff / 60) , ' Minutes');
    WHEN (diff < 86400)    THEN RETURN CONCAT(FLOOR(diff / 3600), ' Hours');
    WHEN (diff < 604800)   THEN RETURN CONCAT(FLOOR(diff / 86400), ' Days');
    WHEN (diff < 2592000)  THEN RETURN CONCAT(FLOOR(diff / 604800), ' Weeks');
    WHEN (diff < 31536000) THEN RETURN CONCAT(FLOOR(diff / 2592000), ' Months');
    ELSE                        RETURN CONCAT(FLOOR(diff / 31536000), ' Years');
  END CASE;
END;

This is pretty straight-forward:

First, the time between the input datetime values is computed as a number of seconds. This is done by converting the input values to a number of seconds using the UNIX_TIMESTAMP function. Then, the value for the old datetime is subtracted from the new datetime. The result is assigned to the diff variable where it is stored for later use.

Second, a searched CASE-statement is used to determine the order of magnitude: Minutes, Hours, and so on up to Years. For example, if the number of seconds is less than 3600 (which is an hour, 60 seconds times 60 minutes) the WHEN branch is entered to calculate a number of minutes

The WHEN branches of the CASE-statement calculate how many of the selected units (minutes, hours, etc.) fit into the elapsed time calculated in step 1. Using CONCAT(), this is used to create a nice human-readable string, which is immediately returned from the function.

On my laptop, BENCHMARK(1000000, TimeDiffUnits('2001-01-01', '2002-01-01')) takes between 2.91 and 3.05 seconds.

Step1: Using `DEFAULT` instead of `SET`

The first thing I did was getting rid of the assignment to diff:

SET diff = UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);

Instead, I used the DECLARE statement of diff to assign the elapsed time right away using the DEFAULT clause:

DECLARE diff INTEGER DEFAULT UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);

For completeness here's the modified function:

CREATE FUNCTION TimeDiffUnits1 (old DATETIME, new DATETIME)RETURNS CHAR(50)
DETERMINISTIC NO SQL
BEGIN
  DECLARE diff INTEGER DEFAULT UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);
  CASE
    WHEN (diff < 3600)     THEN RETURN CONCAT(FLOOR(diff / 60) , ' Minutes');
    WHEN (diff < 86400)    THEN RETURN CONCAT(FLOOR(diff / 3600), ' Hours');
    WHEN (diff < 604800)   THEN RETURN CONCAT(FLOOR(diff / 86400), ' Days');
    WHEN (diff < 2592000)  THEN RETURN CONCAT(FLOOR(diff / 604800), ' Weeks');
    WHEN (diff < 31536000) THEN RETURN CONCAT(FLOOR(diff / 2592000), ' Months');
    ELSE                        RETURN CONCAT(FLOOR(diff / 31536000), ' Years');
  END CASE;
END;

This is not a big deal right? Well it isn't. I mean, this is certainly not a big change and I think the code is still just as clear. Did this gain us some performance? Well, just a bit. For me, SELECT BENCHMARK(100000, TimeDiffUnits1('2001-01-01', '2002-01-01')); takes between 2.89 and 2.98 seconds to run. This is about 2% better than the original. Admittedly, next to nothing, but considering that we casually eliminated only one assignment, I think it is rather good!

Take-away: Don't assign when you don't have to. Each local variable declaration is an implicit assignment - use it if you can.

Step 2: Using `DIV` instead of float division and `FLOOR()`

The second change I introduced is a bit larger than the previous one. To compute the number of elapsed units, the original code uses the division operator (/). This uses floating point arithmetic, and to get a nice integer result, the division is wrapped inside the FLOOR() function. In fact, this is a pattern that I have observed earlier in other code (yes, I'm guilty too :(), and I wrote about it in the past.

As it turns out, we don't need the division operator to perform division. At least, not in this case. MySQL provides the DIV operator, which is designed to perform integer division. This is great news for two reasons:

No time is wasted converting the numbers to floating point values to perform the calculation

Because the result of DIV is also an integer, we don't need FLOOR to convert back to integer again.

So, for example, this:

WHEN (diff < 31536000) THEN RETURN CONCAT(FLOOR(diff / 2592000), ' Months');

Can be rewritten to

WHEN (diff < 31536000) THEN RETURN CONCAT(diff DIV 2592000, ' Months');

. This should be faster, and its less code too. Here's the modified function for completeness:

CREATE FUNCTION TimeDiffUnits2 (old DATETIME, new DATETIME)RETURNS CHAR(50)
DETERMINISTIC NO SQL
BEGIN
  DECLARE diff INTEGER DEFAULT UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);
  CASE
    WHEN (diff < 3600)     THEN RETURN CONCAT(diff DIV 60 , ' Minutes');
    WHEN (diff < 86400)    THEN RETURN CONCAT(diff DIV 3600, ' Hours');
    WHEN (diff < 604800)   THEN RETURN CONCAT(diff DIV 86400, ' Days');
    WHEN (diff < 2592000)  THEN RETURN CONCAT(diff DIV 604800, ' Weeks');
    WHEN (diff < 31536000) THEN RETURN CONCAT(diff DIV 2592000, ' Months');
    ELSE                        RETURN CONCAT(diff DIV 31536000, ' Years');
  END CASE;
END;

After the modification, BENCHMARK(100000, TimeDiffUnits2('2001-01-01', '2002-01-01')) takes between 2.61 and 2.72 seconds to run on my laptop. This is about 11% faster than the original, and about 9% faster than my first improvement.

Take-away: If you are doing division, think a minute about what data types you are using. Do you really need float arithmetic? If you don't, then don't use the division operator (/), simply use DIV instead. It may be less known, but it is more explicit and can give you some extra performance. If you are using FLOOR, ask yourself why you are throwing away fractional digits. There are a bunch of cases where you just need to format something that is intrinsically fractional, but if you can't care less about the fractional numbers, chances are you can chuck the FLOOR away and simply avodi the fractional numbers by using straight integer division.

Step 3: Using the CASE operator instead of the CASE statement

Finally, I changed the CASE-statement and replaced it with a CASE-operator instead.

Now, I have seen on numerous occasions that people are confused about CASE, so here's a quick overview, just to get it completely straight:

The `CASE`-statement

The CASE-statement is not the same as the CASE-operator.
The CASE-statement is a program-flow control statement, and is allowed in stored routines (procedures, functions, triggers and events), but not in regular SQL statements.
The CASE-statement is used to choose between a number of alternative code paths. Each WHEN branch must contain at least one statement, and may contain multiple statements. Note that statements that appear in the WHEN branch of a CASE-statement are always followed by a semi-colon statement terminator.
The CASE-statement is syntactically terminated using the keywords END CASE. Note that END CASE is typically followed by a semi-colon that acts as the statement terminator for the CASE-statement. (The only exception is when the CASE statement appears as top-level statement in a stored routine, in which case the semi-colon is allowed but not required.

The `CASE`-operator

The CASE-operator is not the same as the CASE-statement.
The CASE-operator (aka CASE-expression) is a value-expression. It is allowed in almost all places where you can use a value. So, you can use the CASE-operator in regular SQL statements as well as in stored routines.
The WHEN-branches of the CASE-operator must contain a single value-expression (which may itself be composite). The WHEN-branches of the CASE-operator can thus not contain statements, and cannot contain multiple expressions - it just wouldn't make sense because the CASE-operator evaluates to a single value, just like any other expression. Because statements are not allowed in the WHEN branches of CASE-operators, there are never terminating semi-colons inside these WHEN branches.
The CASE-expression is terminated with the END keyword. Note that this is different fromt the terminator of the CASE-statement which is END CASE
Also, note that a CASE-expression does not itself have a semi-colon as statement terminator for the simple reason that it is not a statement. Of course, it is possible for a CASE-expression to appear at the end of a statement. In this case, there will be a semi-colon statement terminator immediately after the END of the CASE-expression, but it is important to realize that that semi-colon terminates the statement that contains the CASE-expression - it does not terminate the CASE-expression itself.

Simple `CASE` and searched `CASE`

So, I just discussed two different occurrences of CASE: as statement, and as operator/expression. Now to complicate things even more, each of these can appear in two different forms, namely the simple and the searched CASE, making up a total of 4 different CASE variants. Fortunately, I wrote about the simple and searched CASE earlier in another article.

`CASE`-statement versus `CASE`-expression

As you can see in the original code, Mike uses a CASE-statement. Each of the WHEN-branches contains a single RETURN statement that passes the return value to the caller. With a small modification, we can rewrite this to a single RETURN statement that uses a CASE-expression to pick the right value. The result is shown below:


CREATE FUNCTION TimeDiffUnits3(old DATETIME, new DATETIME) RETURNS char(50)
NO SQL DETERMINISTIC
BEGIN
  DECLARE diff INTEGER DEFAULT UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);
  RETURN CASE
    WHEN (diff < 3600)     THEN CONCAT(diff div 60 , ' Minutes')
    WHEN (diff < 86400)    THEN CONCAT(diff div 3600, ' Hours')
    WHEN (diff < 604800)   THEN CONCAT(diff div 86400, ' Days')
    WHEN (diff < 2592000)  THEN CONCAT(diff div 604800, ' Weeks')
    WHEN (diff < 31536000) THEN CONCAT(diff div 2592000, ' Months')
    ELSE                        CONCAT(diff div 31536000, ' Years')
  END;
END

While testing, I found that BENCHMARK(100000, TimeDiffUnits3('2001-01-01', '2002-01-01')) takes between 1.69 and 1.78 seconds to run. That is a 70% improvement over the original, and a 66% and 53% improvement with regard to the prior versions respectively.

Personally, I am curious why this is such a big improvement, and my guess is that this can be explained by assuming that a CASE statement is chopped up into many byte-code instructions which are each executed individually and sequentially, whereas the CASE-operator is written as a single C-function. At any rate, I think this was rather worth it, and personally I feel the final solution is a bit cleaner than the original.

Take-away: If you need some choice structure, think about what you are choosing. Are you deciding between different code paths, or are you picking between values? If you are picking values, simply write a CASE-expression. It's more explicit, and it is a lot faster than a CASE-statement. Another thing to consider:
do you need multiple statements in the branches, or can you get by with a single statement? If you can get by with a single statement, and it is always a RETURN or a SET-statement, then you can rewrite it to a CASE expresion.

Conclusion

Because the changes I made are really quite small, I think this mostly shows that MySQL stored function compilation is poorly optimized (if at all). I am not a compiler expert but my gut feeling is that most of the optimizations could have been done automatically.

Finally

If you are interested in refactoring MySQL stored functions and procedures, you might also like a few other articles I wrote on the subject:

Refactoring Cursors

Refactoring UNION

Refactoring information schema queries

OpenCommunityCamp 2009

2009-05-26T21:46:00.003+02:00

OpenCommunityCamp 2009 is near! OpenCommunityCamp is a true Camp: there's no registration, its completely free, and geeks and geekettes will be spending the evenings around the campfire, and sleep in tents beneath the starry skies of Oegstgeest, the Netherlands.

If you can find the time, I'd be glad to meet you there, 26th July through 2nd August 2009.

I'll be speaking on Open Source Business Intelligence, but there's a lot more going on.

Hope to see you there!

Completed Draft for the "Pentaho Solutions" Book

2009-05-21T21:58:00.003+02:00

Yes!

Last night, I completed the draft of "Pentaho Solutions", which is a book I'm writing together with my friend and colleague Jos van Dongen for Wiley.

(Actually, the full title is: "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL")

Here's an overview of the contents, just to give you an idea what we have been doing:

Part I: Getting Started, Prerequisites, Installation and Configuration and Overview
- Chapter 1: Quick Start: Pentaho PCI Examples
- Chapter 2: Prerequisites
- Chapter 3: Server Installation and Configuration
- Chapter 4: The Pentaho BI Stack

Part II: Business Case, Dimensional Modeling, Data Warehouse and Data Mart Design
- Chapter 5: Example Business Case: World Class Movies
- Chapter 6: Data warehouse primer
- Chapter 7: Modeling the Business: Logical Design using Star Schemas
- Chapter 8: The Data Mart Design Process

Part III: ETL and Data Integration
- Chapter 9: Pentaho Data Integration
- Chapter 10: Designing Pentaho Data Integration Solutions
- Chapter 11: Deploying Pentaho Data Integration Solutions

Part IV: Business Intelligence Applications
- Chapter 12: The Metadata Layer
- Chapter 13: Reporting using Jfree
- Chapter 14: Scheduling, Subscription and Bursting
- Chapter 15: OLAP Solutions using Pentaho Analysis Services
- Chapter 16: Data Mining with Weka
- Chapter 17: Building Dashboards

So - Jos and I will be processing comments from the copy and tech editors for the remainder of this month, and then, if all goes well this is going to result in a book with about 550 pages which should be available in the first week of September (2009). You can actually pre-order a copy already on Amazon and benefit from the special 37%(!) pre-order discount:

I admit that it has been way more work than I thought, and I'm very glad we have reached this milestone. That said, I really am quite happy with what we've written so far, and I am convinced we will be delivering a valuable book for everybody that is interested in getting started with Business Intelligence, or just Pentaho, or both. Although Jos and I did the writing, we got great support from Pentaho developers as well as various community members, and this really was a huge help to us. (I don't want to name names now because I would risk leaving someone out accidentally - We'll make sure to get a full list of acknowledgements)

As a project I think things have developed pretty good too. We experienced some delay with respect to our original schedule, but to be frank I expected that would happen when we started this. All in all, we are two weeks behind, but on a +6 months period of time, I don't think that's too bad. I really should mention that so far, we received excellent coaching and advice from our contacts at Wiley, Bob Elliot and Sara Shlaer. Thanks! and on to the last few bits of work...

Update: I forgot to mention - we are covering the new and upcoming version of the Pentaho platform, Pentaho 3. However, tools within the platform follow their own version scheme so a single version number doesn't say that much (for example, the data integration chapters focus on Pentaho Data Integration 3.2)

Wanna discuss Microsoft Sharepoint?

2009-05-18T11:38:00.005+02:00

Currently, I'm developing something that requires integration with Microsoft Sharepoint.

If you're considering to do the same, and are used to standard HTML, CSS and javascript technologies like object-detection and namespacing, I seriously advise you to have some painkillers in reach, as well as some hard liquor to flush them down.

If you'd like to discuss my point of view, please use this function from core.js, kindly provided by the open minded crew that is responsible for the web development disaster better known as Microsoft Sharepoint:


function Discuss(strUrl)
{
 var L_IE5upRequired_Text="'Discuss' requires a Windows SharePoint Services-compatible application and Microsoft Internet Explorer 6.0 or greater.";
 if (browseris.ie5up && browseris.win32)
  window.parent.location.href=strUrl;
 else
  alert(L_IE5upRequired_Text);
}

Thank you.

Not so random slashdot comment Gem

2009-04-21T15:31:00.001+02:00

Java 8 will replace String with String2, which will treat empty string and null the same.

By by characterZer0 (138196), on Monday April 20, @08:44AM

More fun visualizing MySQL Database Size

2009-03-26T12:11:00.002+01:00

Hi again!

As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like this:

http://chart.apis.google.com/chart?cht=bhs&chbh=19,2,2&chs=653x459&chtt=sakila%20Size%20(MB)&chco=4D89F9,C6D9FD&chd=t:0.0156,0.0156,0.0156,0.0156,0.0156,0.0469,0.0625,0.0625,0.0781,0.0781,0.1875,0.1875,0.1875,0.1163,0.1719,1.5156,1.5156|0.0000,0.0000,0.0000,0.0156,0.0313,0.0156,0.0156,0.0313,0.0156,0.0469,0.0781,0.0781,0.0781,0.2002,0.1875,0.6094,1.2031&chds=0,2.7188&chxt=y,x&chxl=0:|rental%20(InnoDB)|payment%20(InnoDB)|inventory%20(InnoDB)|film_text%20(MyISAM)|films%20(InnoDB)|film_actor%20(InnoDB)|film%20(InnoDB)|customer%20(InnoDB)|staff%20(InnoDB)|address%20(InnoDB)|film_category%20(InnoDB)|city%20(InnoDB)|store%20(InnoDB)|actor%20(InnoDB)|language%20(InnoDB)|country%20(InnoDB)|category%20(InnoDB)|1:|0|2.72MB&chm=N*f2*,000000,0,-1,11|N*f2*,000000,1,-1,11

The graph looks like this: Here's the script I used:

SET @maxpixels:=300000;
SET @barwidth:=19;
SET @spacebetweenbars:=2;
SET @spacebetweengroups:=2;
SET @totalbarwidth:=@barwidth+(2*@spacebetweenbars)+(2*@spacebetweengroups);
SET @megabytes:=1024*1024;
SET @decimals:=2;

SELECT      CONCAT(
                 'http://chart.apis.google.com/chart'
            ,    '?cht=bhs'
            ,    '&chbh=',@barwidth,',',@spacebetweenbars,',',@spacebetweengroups
            ,    '&chs=', @maxpixels div (COUNT(*) * @totalbarwidth),'x', COUNT(*) * @totalbarwidth
            ,    '&chtt=', table_schema, ' Size (MB)' 
            ,    '&chco=4D89F9,C6D9FD'
            ,    '&chd=t:',    GROUP_CONCAT(data_length / @megabytes ORDER BY (data_length+index_length))
            ,    '|', GROUP_CONCAT(index_length / @megabytes ORDER BY (data_length+index_length))
            ,    '&chds=' ,0, ',', MAX(data_length+index_length)/@megabytes
            ,    '&chxt=y,x'
            ,    '&chxl=0:|', GROUP_CONCAT(table_name, ' (', engine,')' ORDER BY (data_length + index_length) DESC separator '|')
            ,         '|1:|', 0, '|', ROUND(MAX(data_length+index_length) / @megabytes, @decimals), 'MB'
            ,    '&chm=N*f',@decimals,'*,000000,0,-1,11|N*f',@decimals,'*,000000,1,-1,11'
            )
FROM        information_schema.tables
WHERE       table_schema = SCHEMA()
AND         table_type   = 'BASE TABLE'
GROUP BY    table_schema

I'm not really satisfied yet...I keep hitting limitations w/re to google charts. I built a little bit of logic that will ensure the resulting picture is within the upper limit of 300000 pixels. I just found out there is another limitation that says the chart height can't exceed 1000 pixels. I'm going to stop looking at this for a while, and maybe later on I will come up with some works-most-of-the-time kind of logic to control this.

Feel free to drop me a line if you have some ideas regarding this.

A Faster MySQL Database Size Google Chart

2009-03-26T12:01:00.000+01:00

Abstract - As described by Walter Heck, MySQL database size can be visualized using Google Charts. With a minor code improvement the URL for the chart can be obtained twice as fast. With some more modification, the number of lines can be cut down resulting in a function that is half as long.

Hi!

It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of course family life.

A month ago or so I read a couple of posts about using the Google chart API to visualize database size. Although I personally would not consider using Google Charts (in its current form) for serious application monitoring applications, I am quite charmed by its ease of use and availability. Time to give it a try myself.

I inspected the PL/SQL code provided by Alex Gorbachev, and the MySQL code by Walter Heck, as well as the improved implementation by Ruturaj Vartak.

Although I applaud both Walter and Ruturaj's efforts in porting this code, I think their code can be improved still. In this short article I'd like to illustrate how a minor modification can double performance.

Code Analysis

Let's take a brief moment to analyze Walter's original code. I will cite a few fragments of his code. (I have made some simplifications and removed some distractions to make it as easy as possible to understand the logic of the code. If you find an error in my citation, please consider the possibility that it is my doing, not Walter's).

His program takes the form of a MySQL stored function. It accepts a few parameters to configure the chart (chart type and size) and outputs a fully functional URL which can be used to retrieve a .png image that shows the size (in MB) of all databases managed by the MySQL server. This is the function signature:

CREATE FUNCTION FNC_GOOGRAPH_DB_SIZE(
  p_chart_type CHAR,
  p_height INT,
  p_width INT
) RETURNS varchar(255)

The stored function is implemented by retrieving size metadata from the information_schema.TABLES table. The size of each table's data and indexes are then added and summed per database. The results are traversed using a cursor loop.

Here's the cursor declaration:

DECLARE c_schema_sizes cursor FOR
SELECT    t.table_schema
,         SUM(t.data_length + t.index_length) / 1024 / 1024
FROM      information_schema.tables t
GROUP BY  t.table_schema

Note that the figure is immediately represented as megabytes by dividing twice by 1024. (In other words, divide by 1024 to make kilobytes of the raw bytes, then divide by 1024 again to make megabytes out of kilobytes.)

During iteration, a list of database names and a list of database sizes are built through string concatenation. These are required to supply the data series and labels to the chart.

Only the code for constructing the data series is shown here:

/* Get the percentage of the total size as the graph's data */
IF v_chart_data = '' THEN
    SET v_chart_data = 
        ROUND(v_data_length_sum / v_data_length_total, 2) * 100;
ELSE
    SET v_chart_data = CONCAT(v_chart_data,',',
        ROUND(v_data_length_sum / v_data_length_total, 2) * 100);
END IF;

Note that instead of making a list of actual sizes, a percentage is taken. Keep that in mind, we'll discuss this in more detail in the next section.

Finally, the actual URL is built, using the list of database sizes as the data series and the list of database names as data labels. Here's the URL construction code:

SET v_url = 'http://chart.apis.google.com/chart?';
SET v_url = CONCAT(v_url, 'cht=', p_chart_type);
SET v_url = CONCAT(v_url, '&chs=', p_width , 'x', p_height);
SET v_url = CONCAT(v_url, '&chtt=Database Sizes (MB)');
SET v_url = CONCAT(v_url, '&chl=', v_chart_labels);
SET v_url = CONCAT(v_url, '&chd=t:', v_chart_data);
SET v_url = CONCAT(v_url, '&chdl=', v_legend_labels);

About Google Chart Data Formats

We just mentioned that the data points are not simply concatenated - rather, data size per database is expressed as a percentage of the total size of all databases. This is not just some arbitrary choice - it is in fact required by the Google Chart API. Here's a quote from the Overview of data formats:

Before you can create a chart you must encode your data into a form that is understood by the Chart API. Use one of the following formats:
Text encoding uses a string of positive floating point numbers from zero to one hundred.
...

For this reason, the code divides everything by v_data_length_total and multiplies by 100. In our code analysis, we did not discuss v_data_length_total because it was not really essential for the logic of the program. This is how it is computed:

SELECT  ROUND(SUM(t.data_length + t.index_length) / 1024 / 1024) 
INTO    v_data_length_total
FROM    information_schema.tables t

Note that this query is very similar to the query used in the cursor declaration. In both cases, the information_schema.TABLES table is queried to calculate the combined size of table data and indexes. The main difference is grouping: in the cursor declaration a GROUP BY table_schema was used to calculate the size per database. In this case, grouping is absent and the size is calculated across databases, i.e. the combined size of all tables and indexes in the entire server.

Text encoding with Data Scaling

Now, as it turns out, the text-endoding used by this code comes in two flavours:

Text encoding with data scaling uses a string of positive and negative floating point numbers in combination with a scaling parameter.

So, as an alternative to 'pre-scaling' the values in the data series to a 100-point scale, we can also opt to let Google do the work, provided we pass this scale factor.

Because the code to calculate the scale factor is already present, we can simply remove the scaling computation inside the loop, and add the scale factor to the URL like so:


    -- inside loop: remove percentage calulation:
      IF v_chart_data = '' THEN
        SET v_chart_data = ROUND(v_data_length_sum,2);
      ELSE
        SET v_chart_data = concat(v_chart_data, ',', ROUND(v_data_length_sum, 2));
      END IF;

    -- outside loop: add scaling parameter to URL:
SET v_url = CONCAT(v_url, '&chds=0,', v_data_length_total);

However, we can do better than that.

An alternative method to calculate the scaling factor

The problem with the current solution is that it has to do a separate query to obtain the total size. We already noticed that both used queries are quite similar, save for the grouping. Because we are already looping through all results, we can try to calculate the total ourselves. This would allow us to avoid doing the SELECT...INTO statement altogether.

We would need to add proper initialization for the v_data_length_total variable and add the following line inside the loop:

SET v_data_length_total := v_data_length_total + v_data_length_sum;

When comparing performance before and after this change, we can observe a dramatic change. On my laptop, the original function takes anywhere between 45 and 50 seconds. After incorporating these changes, the time is slashed by two and the function takes 'only' 22 to 24 seconds.

The fact that the second solution is twice as fast (~23 seconds instead of ~46) is not a coincidence: it's because we're doing half the number of queries (1 instead of 2). Don't get me wrong - this is still very slow. But this comes all down to poor information_schema performance. In this particular case, there is not much we can do to improve the code further to gain performance.

More performance improvements?

I am very much convinced the bottleneck in database size chart function is the query on the information_schema.TABLES table. To be more exact, it's the fact that we're accessing the DATA_LENGTH and INDEX_LENGTH columns. Just watch this:

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|     1576 |
+----------+
1 row in set (0.08 sec)

mysql> select count(data_length) from information_schema.tables;
+--------------------+
| count(data_length) |
+--------------------+
|               1563 |
+--------------------+
1 row in set (25.98 sec)

mysql> select count(index_length) from information_schema.tables;
+---------------------+
| count(index_length) |
+---------------------+
|                1563 |
+---------------------+
1 row in set (25.41 sec)

As you can see, simply accessing DATA_LENGTH and/or INDEX_LENGTH causes the query to be slow.

Notice also that the time spent to execute this stand-alone query is about the same as it takes for the improved function to complete. Basically, this tells us the function spends all its time performing the query - timewise, the contribution of the remainder of the function, such as cursor traversal, creating the value lists and building the URL is simply negligible.

This means that we simply can't improve the performance of the database size chart function unless we can improve the performance of this query. Because we have no other general way of obtaining index and data size, this is the end of the line.

Other Improvements

Even though we probably can't improve the performance of Walter's function anymore, I still think it's possible to improve the code.

I guess this is kind of a pet peeve of mine, but I dislike using MySQL cursors. There's a lot of syntax involved to set them up. Usually, they can be avoided anyway.

MySQL Cursors are also pretty slow. This is something you really start to notice when traversing tens of thousands of rows. Not that that really matters for building Google Chart URLs: Most likely, you will hit a limitation in either chart size or URL length, so you can't really gain a lot of performance by eliminating cursors for this particular purpose.

Still, I think that eliminating the cursor will help to make the code less complex, so lets try anyway.

Building Lists: `GROUP_CONCAT()`

We just explained how the cursor was used to build various lists of values. As it happens, MySQL supports the GROUP_CONCAT() function, which was designed especially for that purpose. GROUP_CONCAT() is an aggregate function, just like COUNT(), MIN() and MAX().

Like other aggregate functions, GROUP_CONCAT() can produce a single summary result on a group of rows. It does so by first concatenating its arguments for each row in the group, and then concatenating the per-row result for the entire group of rows, optionally separating row results using some separator.

Now, there is an important limitation with this function that causes many people to avoid GROUP_CONCAT altogether. Here's the relevant text from the manual:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

Basically, this says that if you don't take proper precautions, the lists you generate with GROUP_CONCAT may be truncated. This is obviously bad news! However, there is a very simple workaround, which is also hinted at in the documentation:

The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

So, the workaround is simple: By assigning the value of max_allowed_packet to group_concat_max_len wil allow the longest possible list of values. You may argue that this might still not long enough. However, that is a mooit point. Just read up on max_allowed_packet:

The maximum size of one packet or any generated/intermediate string.

In other words, no MySQL string will ever exceed its length beyond max_allowed_packet - the same limit holds for any other method of concatenating strings within MySQL, including cursor loops.

A cursor-less database size chart function

Without further ado, this is how I would write the database size chart function:

CREATE FUNCTION  f_dbsize_google_chart(
    p_chart_type ENUM('bhs','p')
,   p_width      MEDIUMINT UNSIGNED
,   p_height     MEDIUMINT UNSIGNED
)
RETURNS LONGTEXT
READS SQL DATA
BEGIN
    DECLARE v_sum_size, v_max_size DOUBLE;
    DECLARE v_size_series, v_size_labels LONGTEXT;

    -- store current group_concat_max_len so we can reset
    DECLARE v_group_concat_max_len BIGINT UNSIGNED DEFAULT @@group_concat_max_len;

    -- ensure group_concat capacity
    SET @@group_concat_max_len := @@max_allowed_packet;

    -- get the database size
    SELECT  ROUND(SUM(size),2), MAX(size)
    ,       GROUP_CONCAT(
                ROUND(size,2)
                ORDER BY size, table_schema
            )
    ,       GROUP_CONCAT(
                table_schema 
                ORDER BY size, table_schema
                SEPARATOR '|'
            )
    INTO    v_sum_size, v_max_size
    ,       v_size_series, v_size_labels
    FROM    (
            SELECT      table_schema
            ,           SUM(data_length + index_length) / 1024 / 1024 size
            FROM        information_schema.tables 
            WHERE       table_type = 'BASE TABLE'
            GROUP BY    table_schema
            ) a;

    -- restore original group_concat_max_len
    SET @@group_concat_max_len := v_group_concat_max_len;

    -- build URL
    RETURN CONCAT(
        'http://chart.apis.google.com/chart'
    ,   '?cht='   , p_chart_type
    ,   '&chs='   , p_width, 'x', p_height
    ,   '&chds=0,', v_max_size
    ,   '&chd=t:' , v_size_series
    ,   '&chdl='  , v_size_labels
    ,   '&chl='   , replace(v_size_series, ',', '|')
    ,   '&chtt=MySQL Database Size (', v_sum_size, 'MB)'
    );
END;

Here's a quick summary that points out some differences with regard to the original code:

Instead of accepting CHAR(1) for the chart type, an ENUM('bhs','p') is used to restrict the value to a listed type.
Instead returning a varchar(3000), this function returns LONGTEXT, effectively leaving it to the Google chart API to report a URL length limitation
Instead of a cursor, we use a single SELECT..INTO statement.
The query in the SELECT...INTO statement uses a subquery in the FROM clause which is functionally equivalent to the actual cursor in the original code. A small but important improvement is the addition of a condition to restrict the result only to base tables. This automatically excludes the information_schema and databases that contain only views
The outer query is functionally equivalent to the cursor loop and uses one GROUP_CONCAT() expression to obtain a list of values, and one GROUP_CONCAT() expression to obtain a list of labels. Here, we also calculate the scaling factor by taking the MAX of the database size.
To work around the truncation problem with GROUP_CONCAT, we set group_concat_max_len the the maximum practical value. We don't just set max_group_concat_len and leave it at that. We restore its original value at the end of the stored function.

Woot! MySQL bug 11661 fixed, finally...

2009-03-04T20:43:00.004+01:00

Yeah...title says it all.

Bug bug #11661, Raising Exceptions from within stored procedures: Support for SIGNAL statement.

I can't recall the exact moment of filing this bug, because I filed a lot of them. However, this one is special to me because I filed it when I was in the process of getting involved with MySQL and its community. I remember it as a very intense period of learning, meeting other community members online (mostly through forums.mysql.com and blogging on a regular basis.

A lot has happened since then, and if you ask me, most of it is good stuff. The only thing that casts somewhat of a shadow is that it took so long to fix this. But I don't want to whine about it. Rather, I'd like to use this opportunity and extend my big, BIG THANK YOU!! to Marc Alff who has been working very hard on implementing this feature. I should also mention Peter Gulutzan, who's enduring attention to quality and efforts for making MySQL comply more to the SQL Standard have made MySQL a better product.

Thanks Guys! I'm looking forward to using this and other features in MySQL 6.0.

Rare find...

2009-02-21T02:50:00.001+01:00

First time in my lifetime....

And indeed, about a minute later all is back to normal.

My respect goes out to the architects and admins of wikipedia that somehow manage to keep this large scale operation running so smoothly. It's truly amazing.

Exporting a Kettle Repository to Files

2009-02-20T16:10:00.001+01:00

Hi All!

Today I'd like to announce KREX, a small solution I put together to export a Kettle (a.k.a. Pentaho Data Integration) Repository to individual transformation (.ktr) and and job (.kjb) files.

The idea to create this was inspired by this thread on the pentaho forums, started by kandrews. He (she?) wrote:

Has anyone ever been able to export a PDI repository and convert it somehow into regular non-repository .kjb & .ktr files? If you have done this already or this functionality already exists please let me know.

My initial thoughts are possibly an XLS translation against the XML from the repository export. Thoughts?

Well, I hope this helps! Enjoy en let me know if its useful. Be advised that in the same thread, Matt Casters already revealed that the functionality to do this will soon be built into PDI, but until then this may be of use.

To start using KREX,

checkout the repository or download the Job and Transformation files to your file system.

Open the main Job file export_repository_to_files.kjb using Pentaho Data Integration 3.2's spoon (Currently a Milestone 1 release)

Configure the Set Source Repository Step in the set_source_repo_and_target_directory transformation to match the repository you want to export

Run the main job file (export_repository_to_files.kjb)

If all goes well, you should now have a directory called pdi_repo_export in your home directory which contains a subdirectory named after your exported repository containing the directory tree with the .ktr and .kjb files.

Here's a quick screenshot of the main job, just to give you an idea:
The heart of the job is formed by the very last transformation, which does the actual legwork of extracting and saving the individual transformations:

The steps before that are mainly configuration and ensuring that the directory tree that is to contain the files is created before we attempt to write any files.

If you have any suggestions or comments, I welcome you to post them here. If you are trying to use KREX but run into an issue, please use the KREX issuelist.

If you are looking for more tips and trick with kettle and Pentaho in general, stay tuned. The "Building Pentaho Solutions" book I'm writing for Wiley together with Jos van Dongen will contain tons and tons of practical tips and solutions, and explain many of its technologies and concepts in thorough detail.

Cheers and until next time,

Roland

Roland Bouman's blog

Common Schema: dependencies routines

MySQL Hacks: Preventing deletion of specific rows

Attempt 1: Re-insert deleted rows with a trigger

Attempt 2: Re-insert deleted rows into a FEDERATED table

Attempt 3: Deleting from the FEDERATED table and re-inserting into the underlying table

An Alternative without relying on magic: a foreign key constraint

Finally: what a quirkyy foreign key constraint!

Fighting Spam: Word Verification

Re-implementing udf_init_error in MySQL 5.5 and up

Proposals for Codebits.EU

Regarding the MySQL Conference and Expo 2012

Working with namespaces and namespace prefixes in XSLT 1.0

get-local-name

get-prefix

get-ns-name

get-ns-prefix

resolve-ns-identifier

HPCC vs Hadoop at a glance

Cleaning webpages with Pentaho Data Integration and JTidy

Standard Kettle tools for Webservices

Workaround: JavaScript string manipulation

A better way: using JTidy

Check out this excellent article by Shlomi Noach!!

Managing kettle job configuration

Variables

Managing configuration

Reading configuration properties

Pivoting key/value pairs to use the "set variables" step

Drawbacks

Solution: Javascript

NoSQL support lands in JasperSoft

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part III

Web Application Data Access Practices

The Data Access Problem

The Webservice Solution

Development Dynamics

A Simple Plan: Building a Query Service

DBSlayer, a Webservice for SQL Queries

What about a MQL query service

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part II

MQL Queries

Executing a query with the Freebase MQL query editor

MQL Query Results

Query by Example, or filling-in-the blanks

Differences between MQL and SQL

Symmetry between Query and Result

Relational focus

Parsing Queries

Generating Queries

SQL is declarative, but MQL is more so

Computational Completeness

Next Time

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part I

MQL, a JSON-based database query language

JSON

What is JSON?

AJAX and JSON

Next time

Speaking at the MySQL conference 2011

Substituting variables in Kettle Parameter values

Substituting Kettle Variable References

Variable substitution in Kettle 4.01 and up

Variable substitution in earlier Kettle versions

A Kettle 3.2.0 transformation to substitute variables in parameters

The replace_variables() function

Odds and Ends

Parameterizing SQL statements in the Kettle Table Input step: Variables vs Parameters

Back to blogging....

A small issue of SQL standards

MySQL, Oracle and NoSQL: In the grand scheme...

MySQL: The maximum value of an integer

Restoring XML-formatted MySQL dumps

XML output from mysqldump

Restoring XML output from mysqldump

What's next?

MySQL Conference 2010 Presentation: Optimizing Stored Routines

MySQL: Partition-wise backups with mysqldump

How it works

Features

The `replace_variables()` function

Conveying `SIGNAL` context information

Another way - serializing the set with `GROUP_CONCAT`

MySQL `CONDITION`s and `HANDLER`s

Step1: Using `DEFAULT` instead of `SET`

Step 2: Using `DIV` instead of float division and `FLOOR()`

The `CASE`-statement

The `CASE`-operator

Simple `CASE` and searched `CASE`

`CASE`-statement versus `CASE`-expression