The Party Line

Dear maintainer of Spreadsheet::ParseExcel!

Please remove the

$sheet->{MaxCol} ||= $sheet->{MinCol};

statement from the loop over spreadsheet rows in the example at the top of the module documentation. People just cut and paste this into their code, which is pointless.

This madness goes far - I even saw this code in a presentation at the local Perl Mongers group technical meeting.

Take pity on poor wasted electrons, K THX.

Being inspired by Edmund von der Burg’s talk at the recent Nordic Perl Workshop in Stockholm, Henrik, Lars, and myself started to play with Open Street Maps.

Some work-related goodness will probably come out of it. Meanwhile, we played with mapping the caches we’ve found in Stockholm during the workshop.

From this map I can deduce three things:

• we need a life;
• the Open Street Maps community in Stockholm has not reached a critical mass yet;
• central Stockholm needs more regular geocaches.

And now - back to the scheduled silence.

Over the last couple of days I had a sad opportunity to use Poul-Henning Kamp’s recoverdisk(1) utility.

Since it turned out to be a life- (well, disk-) saving device, and it is covered by the beer-ware license, I definitely owe phk a beer.

Yesterday morning I discovered that my server is down. After traveling to the server room (there is no remote console) and pretending that my index finger is square in its cross-section, my investigation showed that there is something fishy with the system disk (which is backed up but not mirrored).

Namely, there are bad blocks, and periodic scripts that run at night touch some of them, which leads to bad things.

Now, the first thing I did was to order a replacement disk. The problem was that it won’t arrive until the beginning of next week and I want the box up and running now. Even worse, during the weekend I am going to be in Stockholm for the Nordic Perl Workshop (oh, and by the way I still have a presentation to prepare), and thus won’t be able to fix things that require my presence on-site.

After asking around, I got pointed in the direction of recoverdisk(1) by Phil. Thankfully, recoverdisk /dev/ad4 told me exactly how many bad blocks there are (one) and what offsets they are at.

The next step was to make the on-disk controller to remap the block to one of the good reserve sectors on the disk. While I am sure that there are programs that will do just that, I am not aware of any that run on FreeBSD.

Besides, having the offsets, it was a trivial task to quickly create a simple one-shot program that writes something to the bad block, so that the disk will have an opportunity to remap the sector all by itself.

The only problem I had with this was that I could not open(2) the raw disk for writing while any partitions were mounted on it. I was ready to move the disk to another box to run the program there, but Flemming has helpfully told me that doing sysctl kern.geom.debugflags=16 will do the trick. And it did. Thanks, Flemming!

After this there are four more steps - run recoverdisk again to make sure everything’s fine, run fsck on all partitions, put the box online, and move the system to the new and shiny disk when it finally arrives.

While this last step will have to wait a bit more, the fact that you are reading this shows that everything else worked.

I do realize that the primary purpose of the recoverdisk(1) is to salvage the data from media that has gone hopelessly bad. Nevertheless, I think that my example shows that it is pretty darn useful in other cases as well.

Several people helped me along the way. I have not yet mentioned Lars, who did some heavy lifting, and Kristoffer for doing the network magic with subnet routing when the box was moved to another, closer location.

Lessons learned:

• remote console is useful;

• mirroring the system disk is essential.

Open question: what does phk do with all the beers he is getting when I know for a fact that he does not drink much?

No need to use a wiki or a reminder file. Just put whatever in a relevant channel. Not only yourself, but others will save it for future perusal. Pretty handy, if you ask me.

Some time ago several people (most notably skv@) ranted about including a list of changes or a link to such list in the commit message for a port update.

I thought it was a great idea and started including a link to a CPAN’s distribution Changes file in my commits some time ago.

What I did not like was that those links looked like this:

http://search.cpan.org/src/JESSE/Template-Declare-0.27/Changes

FreeBSD’s commit messages are preserved in our repository and mail archives forever, for a suitable definition of “forever”. On the other hand, CPAN authors are encouraged to clean up old and obsolete versions promptly.

Thus there is a discrepancy between expected time of life of the link in the commit message and the link contents.

While older CPAN distributions can still be found on BackPAN, it only provides links to tarballs and not individual files like Changes.

Luckily, it turns out that version-less links like

http://search.cpan.org/dist/Template-Declare/Changes

work just fine, redirecting to the most recent version of the file. This is acceptable, since Changes is expected to be a prepend-only file, so the information the commit message was trying to link to can (almost) always be found there.

A fellow former Bloglines user has asked me whether I found a way to backup Google Reader subscriptions into an OPML file from cron, as we used to do with our Bloglines accounts.

A quick search turned up this, which, from the look of it, in order for it to work requires every feed to be explicitly marked with a tag which is set up as public.

This by itself is rather cumbersome, and you have to remember to do that for every new feed you subscribe to, otherwise you’ll defeat the purpose of making periodic backups in the first place.

Luckily, there is a better solution. There is a nice little module on CPAN, WebService::Google::Reader by gray, which uses an unofficial Google Reader API to do various nifty things with your Google Reader subscription, including OPML export.

This means that after installing the module you can simply put the following command into your crontab (only command itself is shown, see crontab(5) manual page to find out what else you will want to put in there):

env GOOGLE_USERNAME=your-username-typically@gmail.com \
  GOOGLE_PASSWORD=your-user-password \
  perl -MWebService::Google::Reader -e \
  'print WebService::Google::Reader->new(
     username => $ENV{GOOGLE_USERNAME},
     password => $ENV{GOOGLE_PASSWORD})->opml' \
  > /where/to/put/greader.opml

You will have to make the above to be one long line to satisfy crontab syntax, and of course remember to use a real username, password, and the path to the resulting OPML file.

Unfortunately, the most recent version of the module (which is 0.03 at the time of this writing) has a minor bug which prevents the opml() method from working correctly. So you will need to do a little patching.

Before installing the module, edit the source file lib/WebService/Google/Reader/Constants.pm, look for a string subscribtions, and fix the spelling (finding correct spelling is left as an exercise for the reader). Then proceed installing the module as usual.

Hopefully, this step won’t be necessary in a couple of days’ time when a new version of the module is released.

If you are a FreeBSD user like myself, you may choose instead to fetch a skeleton of the port of the module. Unpack it in /usr/ports/www/ and install it as you would any other port.

I intend to add the port to the ports collection as soon as our current ports freeze is over.

Enjoy!

I finally uploaded slides of my talk about DBIx::Perlish I gave at YAPC::EU 2007.

Well, better late than never. Enjoy.

This is a post in a series about DBIx::Perlish Perl module. Previous posts in the series are linked at the bottom.

Not all queries are made to request the data; you will also want to be able to update, delete, and insert data.

So the query language that DBIx::Perlish implements also provides db_update, db_delete, and db_insert functions.

The db_update and db_delete functions are in fact very similar to db_fetch, with which you are already familiar.

Let’s start with db_update:

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u->first_name = "Ford";
   $u->last_name  = "Prefect";
};

You can see that a query sub that db_update takes has all the same elements as a query sub taken by db_fetch, with an addition of an assignment to update column values. Quite natural, eh?

One thing to remember is that you cannot use a return statement with db_update - it has no meaning!

It can become quite tedious to write all the individual assignments if the number of columns to be updated is not small. Luckily, there is a shortcut:

db_update {
   my $u : users;
   $u->id == 42;  # filter

   $u = {
      first_name => "Ford",
      last_name  => "Prefect",
      age        => $u->age + 1,
   };
};

That is, you can just use hash reference syntax.

You might have noticed that in the last example an expression involving age column was used to update it. That works as you would expect it will.

Using db_delete is equally simple.

db_delete {
   users->id < 20;
};

Using return did not make sense with db_update. This is also the case with db_delete.

Moreover, assignments which you were using with db_update make no sense neither with db_delete nor with db_fetch.

Another thing you would want to do, inserting the data into a table, is done differently. Unlike updates and deletes, inserts do not require a query language since they are so simple.

So if you want to insert a row into a table, you do it with a simple hashref:

db_insert 'users', {
   id         => 42,
   first_name => "Ford",
   last_name  => "Prefect",
};

Nothing fancy here.

To be continued.

First post, Getting rid of SQL from Perl code
Second post, A walk through an example
Third post, Many happy returns

This is a post in a series about DBIx::Perlish Perl module. Previous posts in the series are linked at the bottom.

Coming back to the original example,

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

One more thing to note about this, before moving on: when you specify more than one filtering expression, there is an implicit and between them - so that only those rows which satisfy all expressions will match.

What more the language does to be useful?

Needless to say, all the normal binary operators are supported, so your expectation that arithmetics work is not destroyed:

my $uid = 40;
my @r = db_fetch {
    users->id == $uid + 2;
}

String concatenation also works, including interpolation:

my @r = db_fetch {
    my $u : users;
    return "$u->first_name $u->last_name";
}

This last example shows that DBIx::Perlish interpolates columns represented as method calls in a string. This would normally not work in a “normal” Perl.

If you are paying attention, you can see that there is another new thing in the last example, namely, the presence of the return statement.

If there is no return statement in the query sub, all columns will be returned; it is completely analogous to SQL’s SELECT * statement.

So you use the return statement when you want to control what is returned by the query. In this case it will be the concatenation of the column first_name, a space character, and the column last_name from the table users.

How the query result is returned?

There are four possibilities; which one is chosen depends on what the return statement, if any, specifies, and what context (list or scalar) the db_fetch is being used in.

If you call db_fetch in a scalar context, you indicate that you are only interested in one row of the resulting data set. Using list context you indicate that you want all rows back.

So the context controls how many rows you want back.

On the other hand, if you specify a return statement with precisely one value, you indicate that you are only interested in one particular column of the resulting data set. The absence of the return statement or a return statement with a list of values indicates that you want several columns back.

So the return statement controls how many columns within a row you want back.

Combining two possibilities for rows with two possibilities for columns, you get four possible combinations.

Let’s illustrate these possibilities with examples.

1. Scalar context, single-value return:

my $name = db_fetch { return user->name }; print “The name of some user is $name\n”;

You get one row with one column back - a single scalar value.

1. List context, single-value return:

my @names = db_fetch { return user->name }; print “The names of all users are @names\n”;

You get an array with column values back.

1. Scalar context, more than one column returned:

my $u = db_fetch { return user->id, user->name }; print “The name of a user with id $u->{id} is $u->{name}\n”;

Please note that you get a hash reference back with the keys being the names of the columns returned.

1. Finally, the most common case of the list context with more than one column returned:

my @u = db_fetch { return user->id, user->name }; for my $u (@u) { print “$u->{id}:\t$u->{name}\n”; }

Observe that you get an array of hash references back.

Now, you might ask, what will be the names of the columns in a case like this:

my @r = db_fetch {
    my $u : users;
    return $u->id, "$u->first_name $u->last_name";
}

One of the columns is obviously “id”, but what about the other one? How do you refer to it in the result you’ve got?

The answer is - it is database-dependent and can be pretty much anything. So you ask the next question: “can I control that?”.

Yes, you can, by prepending the offending nameless column with the name you want for it in the return statement:

my @r = db_fetch {
    my $u : users;
    return $u->id, full_name => "$u->first_name $u->last_name";
}

More in the next post.

First post, Getting rid of SQL from Perl code
Second post, A walk through an example

This is a second post of a series about DBIx::Perlish Perl module.

Let us go through the example of DBIx::Perlish’s non-SQL language which I concluded the last post with:

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

I hope it is obvious to you what that snippet does, but let’s go through it anyway.

First thing to note that it deals with two database tables, users and groups. The snippet also illustrates that there are two ways to refer to a table - explicitly by name, as is done with groups, and via aliasing a table to a local variable using Perl attribute syntax. This latter method is preferable when you have to refer to a table more than once or twice. This method is also the only one with which you can make self-joins, but I’ll talk about it later.

So the above snippet could have been written as

my $uid = 42;
my @r = db_fetch {
    users->groups_id == groups->id;
    users->id == $uid;
};

as well as

my $uid = 42;
my @r = db_fetch {
    my $u : users;
    my $g : groups;

    $u->groups_id == $g->id;
    $u->id == $uid;
};

There are more things to pay attention to in the example.

You refer to the individual columns using Perl method call syntax. Since it is common to use accessor methods with Perl objects in order to get or set their properties, it does not require a stretch of imagination to see what $u->groups_id means.

The declarative semantics of the language is apparent in how you specify what results you want back from the query: individual comparison expressions are used as filters to limit an unconstrained result.

Let’s look at a simpler example:

my @r = db_fetch {
   my $u : users;
}

This will get you all rows from the users table. If you only want users with an id over a hundred, you would write:

my @r = db_fetch {
   users->id > 100;
}

If you were operating with a normal Perl array instead of a DB table, you would probably do the same filtering in one of two ways, a more familiar imperative one:

my @r;
for my $u (@users) {
   push @r, $u if $u->{id} > 100;
}

or a shorter declarative one, using grep:

my @r = grep { $_->{id} > 100 } @users;

So how the query language is used resembles declarative grep more than imperative for. But it is not news to you - after all, SQL does precisely the same!

More to follow.

This is a first installment of several posts about DBIx::Perlish Perl module.

Database handling in your program consists of queries and “everything else”.

DBI makes handling of the “everything else” part easy in Perl. It presents a unified interface to deal with a lot (although not all) idiosyncrasies of various numerous RDBMSes.

But mostly, when doing DB programming, you deal with queries. Which DBI does not help you much with.

Which are done using SQL.

SELECT * FROM users,groups
  WHERE
      users.groups_id = groups.id AND
      users.id = 42;

Which is a domain-specific language, from your point of view as a Perl programmer.

DSLs are all the rage nowadays, and I won’t dispute their advantages and usefulness.

But SQL is a very large DSL, as they go.

You are a smart programmer, so you learned SQL. You might even appreciate the fact that it is a declarative language, not unlike Prolog, which definitely adds a bit of excitement to the mundane task of writing your queries.

But every time you are writing Perl code that works with databases you need to constantly switch mental gears between Perl, the language you like (otherwise you would not be reading this), and SQL, the language very different from Perl. This is taxing. It costs you in lost productivity.

Even if you clearly separate in your code (and you should) those parts dealing with DB handling and those that are not, you still need to write those queries, and to switch languages back and forth.

At best, SQL in your Perl code severely disrupts the code flow by virtue of being a different language and thus looking very different from Perl.

my $r = $dbh->selectall_arrayref(
   "SELECT * FROM users, groups
    WHERE
      users.groups_id = groups.id AND
      users.id = ?",
   {Slice=>{}}, 42);

So you clearly have a problem that needs a solution.

One of the possible solutions is to use an object-relational mapper module such as Class::DBI, or DBIx::Class, or Jifty::DBI.

Such solutions have their merits. There are many arguments in favour of them. There are also arguments against them. I am not going to discuss them any further here, I would only like to point out that even if you use an object-relational mapper, you still cannot completely avoid writing SQL: sometimes because you need to construct a query that your mapper of choice does not support through its abstraction, and sometimes for efficiency reasons; the majority of the existing mappers do not deal well with collections, and provide you with shims to add your custom SQL code in strategic places.

Another solution, the solution which I am trying to sell you is to try to get away from using SQL as a domain-specific language in your Perl code altogether.

This involves creating yet another domain-specific language specifically for doing database queries with a (nice for Perl programmers) distinction that it actually looks very much like Perl. In fact, syntactically it is a valid Perl, altough semantically it is still a declarative language suitable for making relational database queries.

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

More on this in a future post.

I was going through the list of posts constituting the Carnival of Space week 10, clicking through to look at the topics which sounded interesting. When I went to see an argument for thinking big for the next moon rocket, I saw:

403 Forbidden

Please stop referer spam.

We have identified that you have been refered here by a known or supposed spammer.

If you feel this is an error, please bypass this message and leave us a comment about the error. We are sorry for the inconvenience.

When I tried to leave a comment informing the authors that they actually block folks coming through the host of the Carnival they were participating in, all I’ve got after pressing “submit” and filling in all the mandatory CAPTCHA hoopla was:

Invalid comment

This is almost a topic for we hates software, except that here we deal with hateful and/or clueless people instead of hateful software.

They really don’t want to be read, do they?

The wonderful “The Peace War” by Vernor Vinge has the following passage:

Jupiter and Venus blazed like lanterns, and the stars were out.

According to the book, this happens one day in April 2048, right after sunset.

The trouble is, xephem says that in April 2048 Venus is the morning star!

Am I being mean, or what?

Some time ago I’ve got two monitors connected to my workstation at work. I do not like using Xinerama, so I have, in X11 terminology, two screens, :0.0 and :0.1.

It bugged me that I had to move the mouse cursor to switch between the screens. There must be a way to do it with keyboard, I thought. Some googling revealed a wiki page about dual monitors setup, which had a reference to a little C program, dualmouse.c by David Antliff.

It worked fine if all I wanted to do was to switch from my current screen to “the other one”. But I also wanted to be able to do things like “switch to workplace 4 on screen 0″, and the program could not be used for that.

Fortunately, the wmctrl utility could do things like “switch to workplace 4 on the current screen”, so all I had to do is to modify the dualmouse.c program to support “switch to screen N” functionality, and the rest is, as they say, shell (or Perl) scripting.

The ~/.fluxbox/keys file (if you are using something else than fluxbox, you are on your own here) would have things like:

Control F1 :Exec flux2workspace 0 1
Control F2 :Exec flux2workspace 0 2
Control F3 :Exec flux2workspace 0 3
Control F4 :Exec flux2workspace 0 4

Control Shift F1 :Exec flux2workspace 1 1
Control Shift F2 :Exec flux2workspace 1 2
Control Shift F3 :Exec flux2workspace 1 3
Control Shift F4 :Exec flux2workspace 1 4

And the flux2workspace script looks like this:

#! /usr/bin/perl -w
exit unless @ARGV == 2;
system("dualmouse $ARGV[0]");
$ARGV[1]--;
$ENV{DISPLAY} = ":0.$ARGV[0]";
system("wmctrl -s $ARGV[1]");

Next time: accomplishing the same feat with x2x-controlled displays.

In zsh, define the following function:

cd-()
{
    local dir
    cd `dirname $1`
}

Then this:

cd- /usr/ports/www/linux-opera/Makefile

changes directory to /usr/ports/www/linux-opera/.

Trivial pleasure, but pleasure nevertheless.

What do we learn from this paper by google-folk Pinheiro, Weber, and Barroso?

We learn that the famed Bigtable is good for gathering time-series statistical data about servers. It’s probably better than what the rest of the world does with rrdtool, since there is no loss of granularity over time. We hear again about Mapreduce and about Sawzall. It’s all well and good, and pretty impressive.

Furthermore, we read that

We conclude that it is unlikely that SMART data alone can be effectively used to build models that predict failures of individual drives.

Is it a good science? Yeah, it’s fine, the negative result is nevertheless a result, and we can learn some important things from failures.

But then we read:

Failure rates are known to be highly correlated with drive models, manufacturers and vintages. Our results do not contradict this fact. For example, Figure 2 changes significantly when we normalize failure rates per each drive model. Most age-related results are impacted by drive vintages. However, in this paper, we do not show a breakdown of drives per manufacturer, model, or vintage due to the proprietary nature of these data.

And this, my friends, is not a good science at all.

Why does that birthday cake with a little 10 in it in the CPAN logo annoy me? It’s been only up there for a year or so. I think it makes PERFECT sense to keep it there until 2015.

There is a nice list of space launches that covers all launches made since 2004.

Unfortunately, that page does not have an RSS feed. Since about 90% of the Web I see comes through various RSS feeds nowadays, I decided to give it a go and made a little scrapper that runs from cron and generates a custom feed. You can try it at http://www.tobez.org/comic2rss/space-launches.rss. Enjoy.

The situation: tcpdump is running pretty much continuously on one of your boxes, busily snooping the traffic. The resulting pcap file is huge, and you like it that way.

The task: you want to analyze the current traffic using Wireshark, which is not installed on the box tcpdump is running on. Even X11 is not there, and again, you would like to keep it that way.

The problem: copying the whole file to your desktop machine is an option, but it will take too long. Running another instance of tcpdump and piping its output to Wireshark on your desktop machine would work, but you don’t like it, since the data is already there, and it is nice to use what you have.

The solution: download and compile the pcaptail little program. Run it with the pcap file name as the only parameter. It will behave like tail -f but on the level of individual packets. Then you can feed its output to an instance of wireshark on your desktop machine.

Enjoy. It’s an ugly hack, but you can still enjoy it.

P.S. Long time no C.

Once upon a time I was a reasonably happy user of XChat. It had a feature: you could configure it to automatically popup a new window “in your face” for private messages. This was nice, since you would never miss a message directed to you. The feature also had drawbacks:

• once a window was there, no popping up occured;
• popping up in your face was annoyingly intrusive;
• it was common to be typing something else and pressing ENTER without noticing; the :wq!’s in chat windows were numerous; parts of passwords for folks to see were not unheard of.

After some time, and following an advice from friends, I switched over to the IRC client, irssi, and never looked back.

I, as many others, run irssi in text mode (although it supposedly has a GUI, I was never tempted to try it). Moreover, I run it 24/7 under screen control on a reasonably stable and well-connected machine.

Which presents me with the problem that XChat popup windows were trying to solve: I miss stuff. There are various notifiers available for some instant messaging applications. I would not be surprized if some of those are easy to integrate with irssi. But with remote irssi? On one or more of my several desktops? I never heard of such a beast, and so I decided to write one.

The question of presentation solved itself easily: I use fluxbox window manager, which, among other things, provides support for AfterStep and WindowMaker dockapps.

Getting information about events from irssi itself turned out to be a piece of cake, thanks to the tight integration of irssi and Perl.

The question of delivering notifications from the machine irssi is being run on to one or several desktops was somewhat trickier. Initially, I thought that a very-very simple web storage system I wrote will be the ticket. It worked reasonably well, but I did not want to perform the polling more often than every 30 seconds, and I was not satisfied with the delay. The help came from an unexpected side.

Some time ago I started to read up on Erlang, and was impressed with its capabilities for concurrent and distributed processing. So I sought to re-implement ISWEST-based irssi notifier in Erlang as a sort of a novice programming project.

The end result is a piece of software called, not surprizingly, irssi-notifier, which can be downloaded here. It consists of three components.

activity-notifier

This is a Perl script which should be loaded in irssi. Copy it into ~/.irssi/scripts directory, and load it into irssi using the following command:

/script load activity-notifier.pl

You will need the nc program (netcat) installed, since I could not be bothered with more socket programming in Perl.

notifier-server

This is a small (less than a hundred lines of code) Erlang program that serves as a middle point between irssi (with activity-notifier) and your desktop. Normally, it should run on the same machine as irssi itself.

This is a server program, so it is supposed to run indefinitely.

Since I do not know (yet) how to create proper Erlang applications, it should be started like this:

$ cd irssi-notifier/notifier-server
$ erl
> c(notifier).
> notifier:start().

Then leave that Erlang instance running (background it, or something).

I intend to make this somewhat more convenient to use once I learn how to package a proper Erlang application which runs as a daemon together with Erlang runtime.

irssi-dockapp

This is a C program which actually implements the dockapp.

Compile it (a very simple Makefile is included). You will need libDockApp library installed first. Then run it like this:

./irssi-dockapp -t host.where.notifier-server.runs -p 5679

When irssi receives a private message for you or a message that it hilights (such as messages with your nick mentioned) an irregularly shaped red blob will appear in your dockapp almost instantaneously.

There is no limit on the number of simultaneous dockapp connections to the notifier server.

Enjoy!