LyricWiki.org used to provide the ability to retrieve lyrics via an API, which was handy.

Recently, LyricWiki effectively ceased, but the project and all the contributed lyrics so far were migrated to Wikia.

Unfortunately, it seems the record labels demanded that lyrics no longer be available via the API, and can only be served up on a web page.

Therefore, I’ve updated Lyrics::Fetcher::LyricWiki appropriately.

I had some audio tracks which weren’t tagged, but did have filenames containing the artist, title etc, so I whipped up a quick Perl script to sort them out – retag-by-filename.pl.

It takes a regular expression with named captures for track, title, artist and comment, and sets the tgs on the file as appropriate.

It makes use of Music::Tag to do the actual tagging and Getopt::Lucid to read the options supplied, and requires Perl 5.10.0 for named regex captures (and ‘say’).

A --dry-run option allows you to check that the filenames are being parsed correctly by your regex before actually writing tags.

See retag-by-filename.pl for the full details.

A task I find myself doing reasonably often when programming is producing a HTML table based on the result of a database query.

This often ends up with the same kind of boring code being written again and again, which get tedious.

For example:

print <
idfoobar
TABLESTART

my $sth = $dbh->prepare(
    "select id, foo, bar from mytable where something = 'somethingelse'"
);
$sth->execute() or die "Failed to query";

while (my $row = $sth->fetchrow_hashref) {
    print '';
    print join '', @$row{qw(id foo bar)};
    print "\n";
}
print "\n";
$sth->finish;

Not hard, but it does get tedious.

HTML::Table makes things better by taking out most of the HTML drudgery, but you still need to loop through adding rows to your table.

This is where my HTML::Table::FromDatabase comes in – it’s a subclass of HTML::Table which accepts an executed DBI statement handle, and automatically produces the table for you.

For instance:

my $sth = $dbh->prepare(
    "select id, foo, bar from mytable where something = 'somethingelse'"
);
$sth->execute() or die "Failed to query";

my $table = HTML::Table::FromDatabase->new( -sth => $sth );
$table->print;

Much easier, and HTML::Table::FromDatabase does all the tedious work.

Sometimes that won’t be quite flexible enough though; you might have something you want to do to certain columns or values before outputting them.

That’s where HTML::Table::FromDatabase’s callbacks come in handy. For a basic example, let’s say that one of the columns you’re fetching contains URLs, and you want to wrap them in anchor tags to make them clickable links. Simply done with:

 my $table = HTML::Table::FromDatabase->new(
    -sth => $sth,
    -callbacks => [
        {
            column => 'url',
            transform => sub { $_ = shift; qq[$_]; },
        },
    ],
 );

Another example – looking for all cells whose value is a number, and formatting them to two decimal places:

 my $table = HTML::Table::FromDatabase->new(
    -sth => $sth,
    -callbacks => [
        {
            value => qr/\d+/,
            transform => sub { return sprintf '%.2f', shift },
        },
    ],
 );

You can apply as many callbacks as you need.

As HTML::Table::FromDatabase is a subclass of HTML::Table, all of HTML::Table’s options can still be used to control how the generated table appears, for example:

• -class => 'classname' to give the table a specific class to help you apply CSS styling
• -border => 1 to apply borders, -padding => 3 to set cell padding
• -evenrowclass and -oddrowclass if you want to have different styling for even and odd rows (e.g. alternating row backgrounds).

The full list of options can be found in the HTML::Table documentation, I’m not going to duplicate it all here.

Currently, the row headings used in the generated table are taken from the column names in the query, but I plan to release a new version sometime soon which allows you to alias them, if you want to do so.

(The code samples in this post are intentionally kept relatively simple, omitting obvious things like connecting to the database first, error checking etc).

(This post also appears on Perlbuzz)