Yesterday, I became a Google employee.

I know that there has been quite a hiatus in my MySQL related activities but it wasn't all without good reason. The first proper holiday I have had in a few years.

Tomorrow afternoon, I hope to spend some time with Mark Callaghan ... It would be a nice break from the new-employee orientation and tutorials and hopefully a chance to meet more of my new colleagues.

Probably this coming weekend, I'll dust off the External Language SP work and perform some much needed tidy ups. It is a shame that Bazaar seems quite overtaxed by the MySQL codebase that doing a quick clone is no longer a possibility - specifying "hardlink" does speed it up but it still takes an age. For as much as we did complain at MySQL about BitKeeper, it at least did not often leave us waiting for unreasonable periods of time.

And before anyone asks.... No. I do not know what projects Google has in store for me. I expect I will find out soon enough.

Exciting times!

That week at the conference was a busy week for me - as busy as any developer meeting I have had with MySQL in recent years. I had a great time seeing old faces again and it was much like old times talking, chatting and coding with them. In particular, I spent much of my time with Patrick Galbraith, Eric Herman and Arjen Lentz.
Great progress was made: I worked with Patrick getting my ancient patch integrated into FederatedX so that it can support transactions. I also worked a bit with Arjen getting his OQGraph engine starting to answer a few simple queries. Of course, most importantly, I worked with Eric to finalize the plan for our presentation on "External Language Stored Procedures for MySQL". We also worked on getting the source repository hosted on LaunchPad and created a small team group to manage it. You can check it out here: Team Page
Now, just relaxing and chatting with people... and hopefully I will get a moment to get an updated patch out and to publish the presentation and demonstration notes. What I'd like to get done soon is to sync the External Lang repository with the current mysql-5.1 launchpad repository and maybe a small patch to support two-phase commits with FederatedX.

<sep/>remote host. Dojo provides a basic RPC client class that has been extended to provide access to JSON-RPC services and Yahoo services. It was designed so that it is easy to implement custom RPC<sep/>

I found myself thinking this evening about how someone could set about writing aggregate functions using Perl and on an idle Google search, came across this webpage entitled User-defined Aggregate Functions in DB2 Universal Database.

I wondered if a similar technique could be applied for our implementation of External Language Stored Procedures for MySQL. It turns out that the answer is: Yes.

Suppose you had the following Perl declarations in a Perl module:

our %summary=(); sub aggregate_add($$) { my ($value,$group)= @_; if (defined $value) { $summary{$group}= {value=>0.0, value2=>0.0, count=>0} if !defined $summary{$group}; my $scalar= scalar $value; $summary{$group}{value}+= $scalar; $summary{$group}{value2}+= $scalar * $scalar; $summary{$group}{count}++; } return $value; } sub aggregate_result($$) { my ($value,$group)= @_; return undef if !defined $summary{$group}; my $count= $summary{$group}{count}; my $average= $summary{$group}{value} / $count; my $sqavg= $summary{$group}{value2} / $count; my $variance= $sqavg - ($average * $average); my $stddev= sqrt $variance; return sprintf("count=%d avg=%0.3f var=%0.3f std=%0.3f", $count, $average, $variance, $stddev); }

Now we need to test this little piece of Perl magic by starting up a client session...

mysql> CREATE TABLE t1 (grp INT, a DOUBLE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,1), (2,2), (2,3), (3,4), (3,5), (3,6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE FUNCTION test.agg_result(value DOUBLE, grp INT) RETURNS CHAR(128)
    -> LANGUAGE Perl NO SQL EXTERNAL NAME 'Foo::aggregate_result';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION test.agg_add(value DOUBLE, grp INT) RETURNS DOUBLE
    -> LANGUAGE Perl NO SQL EXTERNAL NAME 'Foo::aggregate_add';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(a),
    ->        CAST(AVG(a) AS DECIMAL(7,3)) 'AVG',
    ->        CAST(VARIANCE(a) AS DECIMAL(7,3)) 'VAR',
    ->        CAST(STD(a) AS DECIMAL(7,3)) 'STD',
    ->        test.agg_result(MAX(test.agg_add(a,grp)),grp) 'TEST'
    ->        FROM t1 GROUP BY grp;
+----------+-------+-------+-------+---------------------------------------+
| COUNT(a) | AVG   | VAR   | STD   | TEST                                  |
+----------+-------+-------+-------+---------------------------------------+
|        1 | 1.000 | 0.000 | 0.000 | count=1 avg=1.000 var=0.000 std=0.000 |
|        2 | 2.500 | 0.250 | 0.500 | count=2 avg=2.500 var=0.250 std=0.500 |
|        3 | 5.000 | 0.667 | 0.816 | count=3 avg=5.000 var=0.667 std=0.816 |
+----------+-------+-------+-------+---------------------------------------+
3 rows in set (0.04 sec)

Whoot! It actually works! Note that this only works because the Perl instances are not shared between multiple threads and so all the calls to those subroutines and their variables exist within the calling thread only. This avoids any nasty synchronization headaches. I would imagine that it would not be much more difficult to do something similar with the Java plugin except that the global statistics variable would have to be a ThreadLocal instance to avoid multiple threads conflicting with each other.

I guess I shall just have to include it as a test case; at least until a concrete method to declare aggregate stored functions arrives. I would imagine that such a declaration would name existing stored procedures in order to construct the aggregate function.

Something to think about for the future.

(An unrelated side note, March is Endometriosis Awareness Month. Please spend a thought for women who suffer from this disabilitating disease... Thanks)